<?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 . "/includes/db/module_settings_db.inc");
include_once($path_to_root . "/payroll/includes/db/payroll_run_db.inc");
include_once($path_to_root . "/admin/db/list_values_db.inc");
//----------------------------------------------------------------------------------------------------

print_salary_issue_notes_instruction_report();

function get_employee_salary_component_amount($payroll_run_id, $employee, $component)
{
        $sql = "SELECT * 
                FROM ".TB_PREF."payroll_run_details 
                WHERE payroll_run_id = ".db_escape($payroll_run_id)." AND 
                         employee_code = ".  db_escape($employee)." AND 
                         component = ".db_escape($component); 
                         
   	$result = db_query($sql, "Salary component amount could not be retrieved");

        return db_fetch($result);
}

function get_salary_hold_details($payroll_run_id, $employee)
{
    $sql = "SELECT hs.* 
                FROM ".TB_PREF."hold_salary hs
                WHERE hs.employee_code = ".db_escape($employee)." AND
                        hs.payroll_run_id = ".db_escape($payroll_run_id);
        
   	$result = db_query($sql, "hold salary  details could not be retrieved");

        return db_fetch($result);
}

function get_payroll_employees($payroll_run_id, $from_employee, $to_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); 
        
        if(strlen($from_employee) > 0){
            $sql .= " AND prd.employee_code >= ".db_escape($from_employee);
        }
        
        if(strlen($to_employee) > 0){
            $sql .= " AND prd.employee_code <= ".db_escape($to_employee);
        }
//         if($is_insentive_salaries ==0){
//            $sql .= " AND em.bank_basic_salary = ".db_escape($is_insentive_salaries);
//        }
//         if($is_insentive_salaries ==1){
//            $sql .= " AND em.bank_incentive_salary = ".db_escape($is_insentive_salaries);
//        }
        
	$sql .= " ORDER BY prd.employee_code";
	return db_query($sql, "Could not retreive payroll employess");
}
function get_employeee_details($employee)
{
        $sql = "SELECT em.*,
                       IFNULL(lv.value_name,'No Bank Entered') bank_name,
                       IFNULL(b.branch_name,'No Branch Entered') bank_branch_name
                FROM ".TB_PREF."employee_master em
                     LEFT JOIN ".TB_PREF."bank_branches b 
                                ON em.bank_branch_id=b.branch_code 
                     LEFT JOIN ".TB_PREF."list_values lv
                                ON lv.id=em.bank_id                            
                WHERE em.employee_code = ".db_escape($employee);
        
   	$result = db_query($sql, "Employee details could not be retrieved");
        return db_fetch($result);
         
}

function get_employeee_name($employee)
{
        $sql = "SELECT em.*
                FROM ".TB_PREF."employee_master em                     
                WHERE em.employee_code = ".  db_escape($employee);
        
   	$result = db_query($sql, "Employee details could not be retrieved");
        return db_fetch($result);
         
}

function get_payroll_run_information($payroll_run){
    
    	$sql = "SELECT * 
                FROM ".TB_PREF."payroll_runs  
                WHERE id = ". db_escape($payroll_run);
        
   	return $result = db_query($sql, "Payroll run details could not be retrieved");
    
    
}

function get_salary_component_information($salary_componant){
    
    	$sql = "SELECT * 
                FROM ".TB_PREF."list_values ls 
                WHERE ls.id = ". db_escape($salary_componant);
        
   	return $result = db_query($sql, "Salary components could not be retrieved");
    
    
}
//----------------------------------------------------------------------------------------------------

function print_salary_issue_notes_instruction_report()
{
    global $path_to_root, $pic_height;
        
        $payroll_run_id = $_POST['PARAM_0'];
        $from_employee = $_POST['PARAM_1'];
        $to_employee = $_POST['PARAM_2'];
        $salary_total_id_basic = $_POST['PARAM_3'];
        $salary_insentive= $_POST['PARAM_4'];
        $valid_salaries = $_POST['PARAM_5'];
        $released_salaries = $_POST['PARAM_6'];
        $hold_salaries = $_POST['PARAM_7'];
       	$destination = $_POST['PARAM_8'];
        
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");

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

        $component_basic = $component_insentive = '';
        $salary_basic = get_list_value($salary_total_id_basic);
            if (isset($salary_basic['id']) && $salary_basic['id'] > 0) {
                $component_basic = $salary_basic['value_code'];
            }
            
        $salary_insen = get_list_value($salary_insentive);
            if (isset($salary_insen['id']) && $salary_insen['id'] > 0) {
                $component_insentive = $salary_insen['value_code'];
            }
            
        $cols = array(0,20,180, 260, 360, 550, 610,700,770);
        $headers = array('#','Employee', 'Basic Salary','Incentive Salary', 'Bank Name/Branch','Bank Acc. No.', 'Banking Salary','Cash Salary');
        $aligns = array('left','left', 'right', 'right', 'center','center','right', 'right');

   	$rep = new FrontReport(_('Banking and Cash Salaries Report '), "BankingandCashSalariesReport", 'A4', 9, $orientation);

        $payroll_run_details = get_payroll_run_details($payroll_run_id);
        $payroll_end_date = $payroll_run_details['period_end_date'];
       
            if($from_employee != "")
            {
                $employee_detials = get_employeee_name($from_employee);          

                $emp_from_name = "[".$employee_detials['employee_code']."] - ".$employee_detials['initials'].
                                " ".$employee_detials['last_name']." (".$employee_detials["first_name"].") ";

            }
            else
            {
                $emp_from_name = "All";

            }
             if($to_employee != "")
            {

                $employee_detials = get_employeee_name($to_employee);          

                $emp_to_name = "[".$employee_detials['employee_code']."] - ".$employee_detials['initials'].
                                " ".$employee_detials['last_name']." (".$employee_detials["first_name"].") ";
            }
            else {
                $emp_to_name = "All";

            }
           if($payroll_run_id != "-1"){

                $result= get_payroll_run_information($payroll_run_id);
                $StC=db_fetch($result);
                $payrol_run_name=sql2date($StC['period_start_date'])." - ".sql2date($StC['period_end_date'])." - ".$StC['description'];
            }
            else{
               $payrol_run_name ="None";
            } 
            if($salary_total_id_basic != "") {

                $result= get_salary_component_information($salary_total_id_basic);
                $StC=db_fetch($result);
                $salary_component_name=$StC['value_name'];
            }
            else{
               $salary_component_name ="All";
            }  
            if($salary_insentive != "") {

                $result= get_salary_component_information($salary_insentive);
                $StC=db_fetch($result);
                $salary_component_name_insentive=$StC['value_name'];
            }
            else{
               $salary_component_name_insentive ="All";
            }
            if($valid_salaries !=0){
                $valid='Yes';
            }
            else {
                $valid='No';
            }
            if($released_salaries !=0){
                $released='Yes';
            }
            else {
                $released='No';
            }
            if($hold_salaries !=0){
                $hold='Yes';
            }
            else {
                $hold='No';
            }
            
            
                
        $params =   array( 	0 => $payrol_run_name,
                                1 => $emp_from_name,
                                2 => $emp_to_name,
                                3 => $salary_component_name,
                                4 => $salary_component_name_insentive,
                                5 => $valid,
                                6 => $released,
                                7 => $hold
                    );

        //$rep->formData['doctype'] = "form_950";
        $rep->SetHeaderType('form_972');
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
        $rep->NewPage();  

        $seq_no = 1;
                $total_banking_salary =0;                
                $total_cash_salary =0;
                $total_basic_salary =0;
                $total_insentive_salary =0;
//                $total_remain_salary =0;
//                $total_salaries=0;
        $res = get_payroll_employees($payroll_run_id, $from_employee, $to_employee);
        
        while ($payroll_employee = db_fetch($res))
        {
            $basic_salary_value = $insentive_salary_value = 0;
            $basic_salary_data = get_employee_salary_component_amount($payroll_run_id, $payroll_employee['employee_code'], $component_basic);
                if (isset($basic_salary_data['id']) && $basic_salary_data['id'] > 0) {
                    $basic_salary_value = $basic_salary_data['value'];
                }
                   
            $insentive_salary_data = get_employee_salary_component_amount($payroll_run_id, $payroll_employee['employee_code'], $component_insentive);
                if (isset($insentive_salary_data['id']) && $insentive_salary_data['id'] > 0) {
                    $insentive_salary_value = $insentive_salary_data['value'];
                }
           
            $salary_hold_details = get_salary_hold_details($payroll_run_id, $payroll_employee['employee_code']);
                    if($salary_hold_details){
                        if($salary_hold_details['hold_salary'] == 1){
                            if($hold_salaries == 1){
                                
                            }
                            else{
                                continue;
                            }
                        }
                        else{
                            if($released_salaries == 1){
                                
                            }
                            else{
                                continue;
                            }
                        }
                    }
                    else{
                        if($valid_salaries == 1){
                                
                        }
                        else{
                            continue;
                        }
                    }
                    
              
                $rep->NewLine();
                $rep->TextCol(0, 1, $seq_no);
                
                $name='';
                $emp_detils='';
                $emp_detils=get_employeee_details($payroll_employee['employee_code']);
                
                $name=$emp_detils['employee_code']." - ".$emp_detils['initials']." ".$emp_detils['last_name']." (".$emp_detils["first_name"].")";
                $rep->TextCol(1, 2, $name);              
                $rep->TextCol(2, 3, number_format($basic_salary_value,2));                 
                $rep->TextCol(3, 4, number_format($insentive_salary_value,2)); 
                $banking_salary=0;
                $cash_salary= 0;
                if($emp_detils['bank_basic_salary']!=0)
                {
                   $banking_salary =$basic_salary_value;
                }                
                if($emp_detils['bank_incentive_salary']!=0)
                {
                   $banking_salary =$insentive_salary_value;
                }
                if($emp_detils['bank_incentive_salary']!=0 && $emp_detils['bank_basic_salary']!=0)
                {
                   $banking_salary =$insentive_salary_value+$basic_salary_value;
                }
                else
                {
                    $cash_salary=$insentive_salary_value+$basic_salary_value-$banking_salary;
                }
                
                
                $rep->TextCol(4, 5, $emp_detils['bank_name']."-".$emp_detils['bank_branch_name']);
                $bank_acc=$emp_detils['bank_account_no'];
                $bank_acc_no="";
                if($bank_acc=="")
                {
                    $bank_acc_no="No Account No.";
                }
                else
                {
                    $bank_acc_no=$bank_acc;
                }
                $rep->TextCol(5, 6, $bank_acc_no);
                $rep->TextCol(6, 7, number_format($banking_salary,2));
                $rep->TextCol(7, 8, number_format($cash_salary,2));
                
                $total_basic_salary +=$basic_salary_value;
                $total_insentive_salary +=$insentive_salary_value;
                $total_banking_salary +=$banking_salary;
                $total_cash_salary +=$cash_salary;

                $seq_no++;

        }
                $rep->NewLine();
                $rep->Font('b');
                $rep->TextCol(1, 2, 'Total');
                $rep->TextCol(2, 3, number_format($total_basic_salary,2));   
                $rep->TextCol(3, 4, number_format($total_insentive_salary,2));               
                $rep->TextCol(6, 7, number_format($total_banking_salary,2));
                $rep->TextCol(7, 8, number_format($total_cash_salary,2));
                
                $rep->Font();
        

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

?>
