<?php
/**********************************************************************

	Released under the terms of the GNU General Public License, GPL,
***********************************************************************/
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Stock Check Sheet
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/inventory/includes/inventory_db.inc");
include_once($path_to_root . "/includes/db/manufacturing_db.inc");
include_once($path_to_root . "/payroll/includes/db/employees_db.inc");
include_once($path_to_root . "/payroll/includes/db/payroll_run_db.inc");
include_once($path_to_root . "/includes/db/module_settings_db.inc");

//----------------------------------------------------------------------------------------------------

print_etf_six_month_report();

function get_gross_salary_total($payroll_run_id, $component)
{
        $sql = "SELECT value gross_salary_total  
                FROM ".TB_PREF."payroll_run_details 
                WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND 
                        employee_code = '' AND 
                        component = ".db_escape($component); 
                         
   	$result = db_query($sql, "Gross salary total could not be retrieved");

        $gross_salary = db_fetch($result);
        if($gross_salary){
            return $gross_salary['gross_salary_total'];
        }
        else{
            return 0;
        }
}


function get_payroll_run_name($payroll_run_id)
{
    $payroll_run = get_payroll_run_details($payroll_run_id);
    if($payroll_run){
        return $date_payroll_month = date('F',strtotime($payroll_run['period_start_date']));
        
    }
    else{
        return "";
    }     
}

function get_employee_epf_salary_components($payroll_run_id,$employee)
{
        $sql = "SELECT * 
                FROM ".TB_PREF."payroll_run_details 
                WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND component IN ('SALARY_FOR_EPF','ETF 3%')";
        if($employee)
        {
        $sql .=" AND employee_code=".db_escape($employee);
        }      
        
   	return $result = db_query($sql, "ETF salary components could not be retrieved");

}



function get_emp_info($employee)
{
        $sql = "SELECT * FROM ".TB_PREF."employee_master WHERE  employee_code=".db_escape($employee);
                    
                       
	$result = db_query($sql,"an employee could not be retreived");
   	

        return db_fetch($result);
}

function draw_table_row_lines($rep)
{
        $v1 = $rep->row + $rep->lineHeight * 1;
        $v2 = $rep->row - 2;
        $v4 = $rep->row ;
        
        $h0 = $rep->cols[0]-5;
        $h1 = $rep->cols[1]-5;
        $h2 = $rep->cols[2]-5;
        $h3 = $rep->cols[3]-5;
        $h4 = $rep->cols[4]+5;
        $h5 = $rep->cols[5]+5;
        $h6 = $rep->cols[6]+5;
        $h7 = $rep->cols[7]+5;
        $h8 = $rep->cols[8]+5;
        $h9 = $rep->cols[9]+5;
        $h10 = $rep->cols[10]+5;
        $h11 = $rep->cols[11]+5;
        $h12 = $rep->cols[12]+5;
        $h13 = $rep->cols[13]+5;
        $h14 = $rep->cols[14]+5;
        $h15 = $rep->cols[15]+5;
        $h16 = $rep->cols[16]+5;
        $rep->LineTo($h0, $v2, $h16, $v2);
        
        $rep->LineTo($h0, $v1, $h0, $v2);
        $rep->LineTo($h1, $v1, $h1, $v2);
        $rep->LineTo($h2, $v1, $h2, $v2);
        $rep->LineTo($h3, $v1, $h3, $v2);
        $rep->LineTo($h4, $v1, $h4, $v2);
        $rep->LineTo($h5, $v1, $h5, $v2);
        $rep->LineTo($h6, $v1, $h6, $v2);
        $rep->LineTo($h7, $v1, $h7, $v2);
        $rep->LineTo($h8, $v1, $h8, $v2);
        $rep->LineTo($h9, $v1, $h9, $v2);
        $rep->LineTo($h10, $v1, $h10, $v2);
        $rep->LineTo($h11, $v1, $h11, $v2);
        $rep->LineTo($h12, $v1, $h12, $v2);
        $rep->LineTo($h13, $v1, $h13, $v2);
        $rep->LineTo($h14, $v1, $h14, $v2);
        $rep->LineTo($h15, $v1, $h15, $v2);
        $rep->LineTo($h16, $v1, $h16, $v2);
}

function draw_page_total_row_lines($rep)
{
     $v1 = $rep->row + $rep->lineHeight * 1;
        $v2 = $rep->row - 2;
        $v4 = $rep->row ;
        
        $h0 = $rep->cols[0]-5;
        $h1 = $rep->cols[1]-5;
        $h2 = $rep->cols[2]-5;
        $h3 = $rep->cols[3]-5;
        $h4 = $rep->cols[4]+5;
        $h5 = $rep->cols[5]+5;
        $h6 = $rep->cols[6]+5;
        $h7 = $rep->cols[7]+5;
        $h8 = $rep->cols[8]+5;
        $h9 = $rep->cols[9]+5;
        $h10 = $rep->cols[10]+5;
        $h11 = $rep->cols[11]+5;
        $h12 = $rep->cols[12]+5;
        $h13 = $rep->cols[13]+5;
        $h14 = $rep->cols[14]+5;
        $h15 = $rep->cols[15]+5;
        $h16 = $rep->cols[16]+5;
        $rep->LineTo($h0, $v2, $h16, $v2);
        
        $rep->LineTo($h0, $v1, $h0, $v2);
        $rep->LineTo($h3, $v1, $h3, $v2);
        $rep->LineTo($h4, $v1, $h4, $v2);
        $rep->LineTo($h5, $v1, $h5, $v2);
        $rep->LineTo($h6, $v1, $h6, $v2);
        $rep->LineTo($h7, $v1, $h7, $v2);
        $rep->LineTo($h8, $v1, $h8, $v2);
        $rep->LineTo($h9, $v1, $h9, $v2);
        $rep->LineTo($h10, $v1, $h10, $v2);
        $rep->LineTo($h11, $v1, $h11, $v2);
        $rep->LineTo($h12, $v1, $h12, $v2);
        $rep->LineTo($h13, $v1, $h13, $v2);
        $rep->LineTo($h14, $v1, $h14, $v2);
        $rep->LineTo($h15, $v1, $h15, $v2);
        $rep->LineTo($h16, $v1, $h16, $v2);
}

function get_patroll_employees($payroll_run_id,$employee_code)
{
	$sql = "SELECT DISTINCT employee_code FROM ".TB_PREF."payroll_run_details WHERE payroll_run_id =  ".db_escape($payroll_run_id);
        if($employee_code !='')
        {
            $sql .= " AND employee_code=".db_escape($employee_code);
                    
        }
                
	return $result = db_query($sql,"Payroll employees could not be retreived");

	
}
function add_to_report_totals(&$rpt_totals,$empl_etf3,$total_earnings)
 {
    foreach ($rpt_totals as $key => $value) 
    {
   
        $rpt_totals[$key] += $total_earnings;
        $rpt_totals[$key + 1 ] += $empl_etf3;
    
    
    }
 }
function get_etf6_month_info($te_comp,$contrib_comp,&$rep,&$row_total,$col1,&$page_totals,$employee_details,$payroll_id)
{
    $print =true;
    $epf_3_total = $epf_total = 0;
                

    $empl_etf3=0;
    $total_earnings=0;
    $res = get_employee_epf_salary_components($payroll_id,$employee_details['employee_code']); 
    
    while($employee_salary = db_fetch($res))
    {

       if($employee_salary['component']== $contrib_comp)
       {
           $empl_etf3=doubleval($employee_salary['value']);

       }
       if($employee_salary['component']== $te_comp)
       {
           $total_earnings=doubleval($employee_salary['value']);

       }

    }
   
        $rep->TextCol($col1, $col1+1, number_format($total_earnings,2),2);
        $rep->TextCol($col1 + 1, $col1 + 2, number_format($empl_etf3,2),2);
       
        if($col1 == 6)
        {
            $page_totals[6] += $total_earnings;
            $page_totals[7] += $empl_etf3;
        }
        if($col1 == 8)
        {
            $page_totals[8] += $total_earnings;
            $page_totals[9] += $empl_etf3;
        }
         if($col1 == 10)
        {
            $page_totals[10] += $total_earnings;
            $page_totals[11] += $empl_etf3;
        }
         if($col1 == 12)
        {
            $page_totals[12] += $total_earnings;
            $page_totals[13] += $empl_etf3;
        }
         if($col1 == 14)
        {
            $page_totals[14] += $total_earnings ;
            $page_totals[15] += $empl_etf3;
        
//            $page_totals[16] += $tot_contribution;
        }
       
//    add_to_report_totals($page_totals,$empl_etf3,$total_earnings);


$row_total += $empl_etf3;
//$tot_contribution += $row_total;
//$tot_contribution += $row_total;
//$earn_total += $total_earnings;
                    
         if($col1 == 4)
        {
            $page_totals[4] += $total_earnings;
            $page_totals[5] += $empl_etf3;
           
        }
    
    
}
function get_component_name($te_comp_id)
{
   
    $sql = "SELECT value_code  from ".TB_PREF."list_values WHERE id =  ".db_escape($te_comp_id);
    $result = db_query($sql,"Could not find List value name.");
    return db_fetch($result);
}

function get_payroll_run_ids($payroll_run_id)
{
        $sql ="SELECT pr.id
           FROM ".TB_PREF."payroll_runs pr
               INNER JOIN ".TB_PREF."list_values lv ON
                   lv.id = pr.payroll_id AND lv.value_code='MAIN'
           WHERE pr.id >= ". db_escape($payroll_run_id)." order by id limit 6";
    
         return $result = db_query($sql, "Payroll Run Id could not be retrieved");
         
       
}
function initialize_additions_array(&$arr)
 {
     $arr['4'] =0;
     $arr['5']=0;
     $arr['6']=0;
     $arr['7']=0;
     $arr['8']=0;
     $arr['9']=0;
     $arr['10']=0;
     $arr['11']=0;
     $arr['12']=0;
     $arr['13']=0;
     $arr['14']=0;
     $arr['15']=0;
     $arr['16']=0;
 }
 
 function print_last_footer(&$rep)
 {
     $epf_reg_no = get_module_parameter_value("payroll", "epf_reg_no");               
                //$rep->Line($rep->row-2,1);
                $rep->Font('bold');
                $rep->NewLine(2);
                $rep->Text($rep->leftMargin + 10,"EMPLOYER'S REGISTRATION NO :");
                $rep->Font('');
                $rep->Text($rep->leftMargin + 200,$epf_reg_no);
                $rep->NewLine(2);
                $rep->Font('bold');
                $rep->Text($rep->leftMargin + 10,"NAME & ADDRESS OF EMPLOYER :");
                  $rep->Font('');
                $rep->Text($rep->leftMargin + 200,$rep->company['coy_name']);
                $rep->NewLine();
                $rep->TextWrapLines(230,$rep->leftMargin + 500,$rep->company['postal_address']);
//                $rep->NewLine();
                  $rep->Font('bold');
                $rep->Text($rep->leftMargin + 10,"TELEPHONE NO :");
                  $rep->Font('');
                $rep->Text($rep->leftMargin + 200,$rep->company['phone']);
                $rep->NewLine(2);
                  $rep->Font('bold');
                $rep->Text($rep->leftMargin + 10,"FAX NO :");
                  $rep->Font('');
                 $rep->Text($rep->leftMargin + 200,$rep->company['fax']);
                  
                 $rep->NewLine();
                 $rep->Font('bold');
                $rep->TextWrapLines(400,750,"I Certify that all the particulars given above are correct and that no part of the 
contributions that should be paid by us has been deducted from any employees' earnings.");
                $rep->Font('');
                $rep->NewLine(4);
                $rep->Text(400,"--------------------------------------");
                $rep->Text(650,"--------------------------------------");
                $rep->NewLine(2);
                $rep->Font('bold');
                $rep->Text(400,"Date ");
                $rep->Text(650,"Signature of Employer and Rubber Stamp");
                $rep->Font('bold');
                $rep->NewLine();
                $rep->Font();
 }
 
 function print_footer(&$rep,$start_row,$page_totals)
{
    $rep->row = $start_row;
                    $rep->NewLine();
                    $rep->fontSize += 1;
                    $rep->font('b');
                    $rep->TextCol(0, 3, "Page Total");
                    
                    $rep->fontSize -= 1;
                    $rep->TextCol(3, 4, number_format($page_totals[16],2),2);
                    $rep->TextCol(4, 5, number_format($page_totals[4],2),2);
                    $rep->TextCol(5, 6, number_format($page_totals[5],2),2);
                    $rep->TextCol(6, 7, number_format($page_totals[6],2),2);
                    $rep->TextCol(7, 8, number_format($page_totals[7],2),2);
                    $rep->TextCol(8, 9, number_format($page_totals[8],2),2);
                    $rep->TextCol(9, 10,number_format($page_totals[9],2),2);
                    $rep->TextCol(10, 11, number_format($page_totals[10],2),2);
                    $rep->TextCol(11, 12, number_format($page_totals[11],2),2);
                    $rep->TextCol(12, 13, number_format($page_totals[12],2),2);
                    $rep->TextCol(13, 14, number_format($page_totals[13],2),2);
                    $rep->TextCol(14, 15,number_format($page_totals[14],2),2);
                    $rep->TextCol(15, 16, number_format($page_totals[15],2),2);
                    $rep->font('');
                    draw_page_total_row_lines($rep);
                    
}

function get_payroll_employees($payroll_run_id, $from_employee = '')
{
	$sql = " SELECT DISTINCT prd.employee_code 
                 FROM ".TB_PREF."payroll_run_details prd, 
                        ".TB_PREF."payroll_runs pr   
                 WHERE prd.payroll_run_id = pr.id AND 
                        
                        prd.employee_code <> '' AND 
                        pr.id = ".db_escape($payroll_run_id)." AND prd.employee_code >= ".db_escape($from_employee);
        
	
    
	return db_query($sql, "Could not retreive payroll employess");
    //pr.status = 'R' AND --- to where clause
}

function get_employees_for_etf_report($payroll_run_ids, $etf_comp,$contri_comp)
{
        
	$sql = " SELECT DISTINCT prd.employee_code
                 FROM ".TB_PREF."payroll_run_details prd
                 WHERE prd.payroll_run_id IN(".$payroll_run_ids.") AND component IN ('$etf_comp', '$contri_comp')";
        
       
	return db_query($sql, "Could not retreive payroll employess");
}

function print_etf_six_month_report()
{
    global $path_to_root, $pic_height;
        
    //return;
        $from_payroll_run_id = $_POST['PARAM_0'];
        $te_comp_id = $_POST['PARAM_1'];
        $contrib_comp_id = $_POST['PARAM_2'];
        $destination = $_POST['PARAM_3'];

	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	//include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	$orientation = 'L';
        $component = get_module_parameter_value("payroll", "epf_reference_salary_total"); //"SALARY_TOTAL_2";

        $cols = array(0,110, 140, 195, 240, 300, 355,410,470,530,590,650,710,770,830,890,950);
        $headers = array("NAME OF MEMBER","MEMB. NO",'NIC NO ','TOTAL CONTRIBUTION','TOTAL EARNING Rs.','TOTAL CONTRIBUTN. Rs.','TOTAL EARNINGS Rs.',
                         'TOTAL CONTRIBUTION Rs.','TOTAL EARNINGS Rs.','TOTAL CONTRIBUTION Rs.','TOTAL EARNINGS Rs.','TOTAL CONTRIBUTION Rs.','TOTAL EARNINGS Rs.',
                         'TOTAL CONTRIBUTION Rs.','TOTAL EARNINGS Rs.','TOTAL CONTRIBUTION Rs.');
        $aligns = array('left','left','left','right', 'right', 'right', 'right', 'right','right', 'right', 'right', 'right','right', 'right', 'right', 'right');

   	$rep = new FrontReport(_('ETF 6 Months Report'), "ETF6MonthsReport", 'LEGAL', 9, $orientation);

    
                $rep->SetHeaderType('form_977');
                $rep->Font();
                
                $payroll_runs_header = get_payroll_run_ids($from_payroll_run_id);
                $r = 0;
                $payroll_run_ids ='';
                while($payroll_head = db_fetch($payroll_runs_header))
                {
                    
                        $payroll_run_ids .= $payroll_head['id'].',';
                        $month = get_payroll_run_name($payroll_head['id']);
                        
                        $params[$r] = $month;
                            
                        $r++;
                        
                        
                    
                }
                
                if (count($params) != 6) {
                    $months_count = count($params);
                    for ($i = 0; $months_count > $i; $i++) {
                        $params[$r + $i] = 'No Payroll Run';
                    }
                }
                
                $rep->Info($params, $cols, $headers, $aligns);
//                $rep->pageNumber =1;
                $rep->NewPage();
                $content_start_row = $rep->row;  
                
                $etf_comp_value_code = $contri_comp_value_code = '';
                $etf_comp = get_component_name($te_comp_id);
                    if (isset($etf_comp['value_code'])) {
                        $etf_comp_value_code = $etf_comp['value_code'];
                    }
                
                $contri_comp = get_component_name($contrib_comp_id);
                    if (isset($contri_comp['value_code'])) {
                        $contri_comp_value_code = $contri_comp['value_code'];
                    }
                
                $payroll_run_ids =substr($payroll_run_ids, 0, -1); 
                $page_totals= array();
                initialize_additions_array($page_totals);
                 $employee_data= get_employees_for_etf_report($payroll_run_ids, $etf_comp_value_code, $contri_comp_value_code);
                $epf_3_total=$tot_contribution =0;
                
                while ($employee_details = db_fetch($employee_data))
                {
                     if(strlen($employee_details['employee_code']) == 0)
                     {
                         continue;
                     }
                    $emp_details=get_emp_info($employee_details['employee_code']);
                    $employee_name = $emp_details['last_name']." ".$emp_details['initials'];
                        
//                     $rep->FontSize -= 4;
                        $rep->TextCol(0, 1, $employee_name,-5);
                        $rep->TextCol(1, 2, $emp_details['epf_no']);
                        $rep->TextCol(2, 3, $emp_details['nic'],-6);
//                        $rep->FontSize += 4;
                    
                     $col1= 4;
                  
                       $payroll_runs = get_payroll_run_ids($from_payroll_run_id);
                       while($payroll_run_line = db_fetch($payroll_runs))
                       {
 
                             
                             get_etf6_month_info($etf_comp_value_code, $contri_comp_value_code,$rep,$epf_3_total,$col1,$page_totals,$employee_details,$payroll_run_line['id']);
                             
                             $col1 +=2;

                             draw_table_row_lines($rep);
                             
                       }
                 
                    
                    $rep->TextCol(3, 4, number_format($epf_3_total,2));
                    $tot_contribution = $epf_3_total;
                    
                       $page_totals[16] += $tot_contribution; 
                 
                      $epf_3_total=0;
                    $rep->NewLine();
                    if(100 > $rep->row - $rep->bottomMargin )
                    {
                         print_footer($rep,$rep->row + $rep->lineHeight * 1,$page_totals);
                         initialize_additions_array($page_totals);
                         $tot_contribution = 0;
                        $rep->NewPage();

                       

                    }
                    
                   
                }
               
                print_footer($rep,$rep->row + $rep->lineHeight * 1,$page_totals);
                 initialize_additions_array($page_totals);
                         $tot_contribution = 0;
                
                $rep->Font();
//                draw_table_row_lines($rep);
             if(220 < $rep->row - $rep->bottomMargin)
             {
                 print_last_footer($rep);
                
             }
        else
        {
            $rep->doc_page_no =0;
            $rep->NewPage();
            print_last_footer($rep);
        }


	
	$rep->NewLine();
    $rep->End();
}

?>
