<?php

 function get_iou_details($employee, $from_date, $to_date, $batch_no_zero, $type, $iou_batch_no=0, $display_completed=0, $float_id=0, $project_id=0) {
    // no payments
    $sql_0 = "(SELECT iou.id, iou.batch_no, iou.tran_date, iou.amount, iou.batch_complete, pm.description project_name, 0 type, 
                0 trans_no, '' pay_date, 0 pay_amount, 0 run_id, ft.name float_name  
            FROM ".TB_PREF."i_owe_you iou 
                LEFT JOIN ".TB_PREF."i_owe_you_payments p  
                    ON p.iou_id = iou.id 
                        AND p.expense_source = ".db_escape(ST_I_OWE_YOU)." 
                        AND p.paid = 1 
                LEFT JOIN ".TB_PREF."bank_trans bt 
                    ON iou.id = bt.person_id 
                        AND bt.type IN(".db_escape(ST_IOU_BANKPAYMENT).", ".db_escape(ST_IOU_BANKPAYMENT_RETURN).")  
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = iou.project_id
                LEFT JOIN ".TB_PREF."petty_cash_floats ft  
                    ON iou.float_id = ft.id 
            WHERE iou.approval_status = ".db_escape(AS_APPROVED)." 
                AND iou.tran_date >= ".db_escape($from_date)." 
                AND iou.tran_date <= ".db_escape($to_date)."
                AND iou.approval_status IN (".AS_APPROVED.", ".AS_CLOSED.")
                AND p.id IS NULL AND bt.trans_no IS NULL ";
        
        if (!$display_completed) {
            $sql_0 .= " AND iou.batch_complete = 0 ";            
        }
        
        if(strlen($employee) > 0) {
            $sql_0 .=" AND iou.employee_code =". db_escape($employee) ;            
        }
        
        if($type != "-1") {
            $sql_0 .=" AND iou.type =". db_escape($type) ;            
        }
        
        if($batch_no_zero){
            $sql_0 .=" AND iou.batch_no = 0 ";            
        } else{
            $sql_0 .=" AND iou.batch_no > 0";            
        }
        
        if ($iou_batch_no > 0) {
            $sql_0 .=" AND iou.batch_no = ".db_escape($iou_batch_no);            
        }
        
        if ($float_id > 0) {
            $sql_0 .= " AND iou.float_id = ".db_escape($float_id);            
        }
        
        if ($project_id > 0) {
            $sql_0 .= " AND iou.project_id = ".db_escape($project_id);            
        }

        $sql_0 .="  ORDER BY iou.batch_no, iou.id )" ;
    
    //float payemnts    
    $sql_1 = "(SELECT iou.id, iou.batch_no, iou.tran_date, iou.amount, iou.batch_complete, pm.description project_name, p.expense_source type, 
                p.id trans_no, p.payment_date pay_date, p.amount pay_amount, p.id run_id, ft.name float_name  
            FROM ".TB_PREF."i_owe_you iou 
                LEFT JOIN ".TB_PREF."i_owe_you_payments p  
                    ON p.iou_id = iou.id 
                        AND p.expense_source = ".db_escape(ST_I_OWE_YOU)." 
                        AND p.paid = 1 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = iou.project_id
                LEFT JOIN ".TB_PREF."petty_cash_floats ft  
                    ON iou.float_id = ft.id 
            WHERE iou.approval_status = ".db_escape(AS_APPROVED)." 
                AND iou.tran_date >= ".db_escape($from_date)." 
                AND iou.tran_date <= ".db_escape($to_date)."
                AND iou.approval_status IN (".AS_APPROVED.", ".AS_CLOSED.")
                AND p.id > 0 ";
        
        if (!$display_completed) {
            $sql_1 .= " AND iou.batch_complete = 0 ";            
        }
        
        if(strlen($employee) > 0) {
            $sql_1 .=" AND iou.employee_code =". db_escape($employee) ;            
        }
        
        if($type != "-1") {
            $sql_1 .=" AND iou.type =". db_escape($type) ;            
        }
        
        if($batch_no_zero){
            $sql_1 .=" AND iou.batch_no = 0 ";            
        } else{
            $sql_1 .=" AND iou.batch_no > 0";            
        }
        
        if ($iou_batch_no > 0) {
            $sql_1 .=" AND iou.batch_no = ".db_escape($iou_batch_no);            
        }
        
        if ($float_id > 0) {
            $sql_1 .= " AND iou.float_id = ".db_escape($float_id);            
        }
        
        if ($project_id > 0) {
            $sql_1 .= " AND iou.project_id = ".db_escape($project_id);            
        }

        $sql_1 .="  ORDER BY iou.batch_no, iou.id )" ;
        
    
    // bank payemts    
    $sql_2 = "(SELECT iou.id, iou.batch_no, iou.tran_date, iou.amount, iou.batch_complete, pm.description project_name, bt.type, 
                bt.trans_no, bt.trans_date pay_date, bt.amount*-1 pay_amount, bt.id run_id, ft.name float_name  
            FROM ".TB_PREF."i_owe_you iou 
                LEFT JOIN ".TB_PREF."bank_trans bt 
                    ON iou.id = bt.person_id 
                        AND bt.type IN(".db_escape(ST_IOU_BANKPAYMENT).", ".db_escape(ST_IOU_BANKPAYMENT_RETURN).")  
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = iou.project_id
                LEFT JOIN ".TB_PREF."list_values dp 
                    ON dp.id = pm.department_id
                LEFT JOIN ".TB_PREF."employee_master em 
                    ON em.employee_code = iou.employee_code 
                LEFT JOIN ".TB_PREF."petty_cash_floats ft  
                    ON iou.float_id = ft.id     
            WHERE iou.approval_status = ".db_escape(AS_APPROVED)." 
                AND iou.tran_date >= ".db_escape($from_date)." 
                AND iou.tran_date <= ".db_escape($to_date)."
                AND iou.approval_status IN (".AS_APPROVED.", ".AS_CLOSED.")
                AND bt.trans_no > 0 ";
        
        if (!$display_completed) {
            $sql_2 .= " AND iou.batch_complete = 0 ";            
        }
        
        if(strlen($employee) > 0) {
            $sql_2 .=" AND iou.employee_code =". db_escape($employee) ;            
        }
        
        if($type != "-1") {
            $sql_2 .=" AND iou.type =". db_escape($type) ;            
        }
        
        if($batch_no_zero){
            $sql_2 .=" AND iou.batch_no = 0 ";            
        } else{
            $sql_2 .=" AND iou.batch_no > 0";            
        }
        
        if ($iou_batch_no > 0) {
            $sql_2 .=" AND iou.batch_no = ".db_escape($iou_batch_no);            
        }
        
        if ($float_id > 0) {
            $sql_2 .= " AND iou.float_id = ".db_escape($float_id);            
        }
        
        if ($project_id > 0) {
            $sql_2 .= " AND iou.project_id = ".db_escape($project_id);            
        }

        $sql_2 .="  ORDER BY iou.batch_no, iou.id )" ;
        
        
        $sql = "SELECT DISTINCT al.id, al.batch_no, al.tran_date, al.amount, al.batch_complete, al.project_name, al.type, al.trans_no, al.pay_date, 
                    al.pay_amount, al.run_id, al.float_name 
                FROM ( $sql_0 UNION $sql_1 UNION $sql_2 ) al 
                WHERE 1 = 1 
                ORDER BY al.id, al.tran_date, al.pay_date, al.run_id, al.trans_no";
        
        return db_query($sql, "get_iou_details");
}

function get_iou_batches_details($from_date, $to_date, $employee, $type, $display_completed, $float_id=0, $project_id=0) {
    $sql = "SELECT DISTINCT iou.batch_no  
            FROM ".TB_PREF."i_owe_you iou
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = iou.project_id
                LEFT JOIN ".TB_PREF."employee_master em 
                    ON em.employee_code = iou.employee_code    
            WHERE iou.batch_no > 0 
                    AND iou.tran_date >= ".db_escape($from_date)." 
                    AND iou.tran_date <= ".db_escape($to_date)."
                    AND iou.approval_status IN (".AS_APPROVED.",".AS_CLOSED.")";
        
        if (!$display_completed) {
            $sql .= " AND iou.batch_complete = 0 ";            
        }
    
        if (strlen($employee) > 1) {
            $sql .=" AND iou.employee_code = ".db_escape($employee) ;            
        }
        if ($type != '-1') {
            $sql .=" AND iou.type = ".db_escape($type);            
        }
        
        if ($float_id > 0) {
            $sql .= " AND iou.float_id = ".db_escape($float_id);            
        }
        
        if ($project_id > 0) {
            $sql .= " AND iou.project_id = ".db_escape($project_id);            
        }
        
        $sql .= " ORDER BY iou.batch_no";
        
        return db_query($sql, "get_iou_batches_details");
}

function  get_rep_iou_batch_record($iou_batch_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."i_owe_you_batches 
            WHERE batch_no = ".db_escape($iou_batch_no);
    
        $result = db_query($sql, "get_rep_iou_batch_record");
        return db_fetch($result);
}

function get_petty_cash_payments_summary($pet_id) {
    $sql = "SELECT SUM(amount) cash_total_payment  
            FROM ".TB_PREF."i_owe_you_payments  
            WHERE iou_id = ".db_escape($pet_id)." 
                AND paid = 1 
                AND expense_source = ".db_escape(ST_PETTY_CASH);

        $result = db_query($sql, "get_petty_cash_payments_summary");	
        return db_result_value($result, 'cash_total_payment');
}

function get_petty_cash_headers_details($employee, $from_date, $to_date, $batch_no_zero, $type, $display_completed, $float_id=0, $project_id=0) {
    $sql = "SELECT pet.*, bt.amount batch_total, bt.approval_status, bt.complete, pm.description project_name, p.expense_source type, 
                p.id trans_no, p.payment_date pay_date, p.amount pay_amount, ft.name float_name   
            FROM ".TB_PREF."petty_cash_header pet
                JOIN ".TB_PREF."i_owe_you_batches bt  
                    ON pet.batch_no = bt.batch_no 
                LEFT JOIN ".TB_PREF."i_owe_you_payments p  
                    ON p.iou_id = pet.id 
                    AND p.expense_source = ".db_escape(ST_PETTY_CASH)." 
                    AND p.paid = 1 
                JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = pet.project_id 
                LEFT JOIN ".TB_PREF."petty_cash_floats ft  
                    ON pet.float_id = ft.id 
            WHERE pet.employee_code = ".db_escape($employee)."         
                AND pet.type = ".db_escape($type)."            
                AND pet.tran_date >=". db_escape($from_date)." AND pet.tran_date <=". db_escape($to_date);
    
        if (!$display_completed) {
            $sql .= " AND pet.batch_complete = 0 ";
        }
        
        if ($float_id > 0) {
            $sql .= " AND pet.float_id = ".db_escape($float_id);            
        }
        
        if ($project_id > 0) {
            $sql .= " AND pet.project_id = ".db_escape($project_id);            
        }
        
        $sql .= " ORDER BY pet.id, pet.tran_date, p.payment_date, p.id";
        
        return db_query($sql, "get_petty_cash_headers_details");
}

function get_emp_petty_cash_ob($employee, $display_completed, $from_date, $to_date, $iou_type_id, $pet_type_id, $outstanding_calculation, $float_id, 
                               $project_id) {
    $emp_outstanding = 0;
    
    // batch zero
    $result = get_iou_details($employee, $from_date, $to_date, true, $iou_type_id, 0, $display_completed, $float_id, $project_id);
        while ($row = db_fetch($result)) {
            $emp_outstanding += $row['pay_amount'];
        }

    // batch not zero
    $iou_batches_result = get_iou_batches_details($from_date, $to_date, $employee, $iou_type_id, $display_completed, $float_id, $project_id);
        while ($iou_batches = db_fetch($iou_batches_result)) {
            $iou_batch_no = $iou_batches['batch_no'];
            
            if ($iou_batch_no == 878) {
                $xd = 'xd';
            }
            
            $iou_batch_row = get_rep_iou_batch_record($iou_batch_no);
            $iou_batch_expenses_amount = $iou_batch_row['amount'];
            
                $iou_data = get_iou_details($employee, $from_date, $to_date, false, $iou_type_id, $iou_batch_no, $display_completed, $float_id, 
                                            $project_id);
                $db_num_rows = db_num_rows($iou_data);
                    while ($iou_header = db_fetch($iou_data)) { 
                        if($outstanding_calculation == 1){
                            if ($iou_batch_row['approval_status'] == AS_APPROVED) {
                                $iou_total_payment = $iou_header['pay_amount'];
                                $emp_outstanding += $iou_total_payment;
                                $db_num_rows -= 1;
                                if ($db_num_rows == 0) {
                                    if ($iou_batch_row['approval_status'] == AS_APPROVED) {
                                        $emp_outstanding -= $iou_batch_expenses_amount;
                                    }
                                }
                            }
                        }else{ 
                            if(in_array($iou_batch_row['approval_status'], array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER, AS_APPROVED))){
                                $iou_total_payment = $iou_header['pay_amount'];
                                $emp_outstanding += $iou_total_payment;
                                $db_num_rows -= 1;
                                if ($db_num_rows == 0) {
                                    if(in_array($iou_batch_row['approval_status'], array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER, AS_APPROVED))){
                                        $emp_outstanding -= $iou_batch_expenses_amount;
                                    }
                                }
                            }
                        }
                    }
        }

    // petty cash
    $pet_data = get_petty_cash_headers_details($employee, $from_date, $to_date, false, $pet_type_id, $display_completed, $float_id, $project_id);
        while ($pet_header = db_fetch($pet_data)) {
            $payment = ($pet_header['pay_amount']);
            $batch_expences = $pet_header['batch_total']; 
            
            if($outstanding_calculation == 1){
                if ($pet_header['approval_status'] == AS_APPROVED) { 
                    $outstanding_amount = $payment - $batch_expences;
                } else {
                    $outstanding_amount = $payment;                        
                }
            }else{ 
                if(in_array($pet_header['approval_status'], array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER, AS_APPROVED))){
                    $outstanding_amount = $payment - $batch_expences;
                } else {
                    $outstanding_amount = $payment;                        
                }
            }
                        
                $emp_outstanding += $outstanding_amount; 
        }
        
        return $emp_outstanding;
}