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


function get_sql_for_employee_petty_cash_outstanding_transactions($company_code, $employee_code, $expenses_filter, $from_date, $to_date)
{    

//IOU Payments
    $sql_iou_payments = "SELECT ".db_escape(ST_I_OWE_YOU_PAYMENT)."  trans_type, p.payment_date trans_date, p.id trans_no, iou.reference,  p.amount, iou.float_id, f.name float_name, p.iou_id, iou.project_id, iou.comments, iou.batch_no   
        FROM ".TB_PREF."i_owe_you_payments p  
            JOIN ".TB_PREF."i_owe_you iou  
                ON p.iou_id = iou.id 
                AND p.expense_source = ".db_escape(ST_I_OWE_YOU)." 
            JOIN ".TB_PREF."petty_cash_floats f   
                ON f.id = iou.float_id                     
        WHERE iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code)." 
            AND iou.batch_complete = 0 
            AND p.paid = 1 
            AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date);

//Pettycash Payments
    $sql_petty_cash_payments = "SELECT ".db_escape(ST_PETTY_CASH_PAYMENT)." trans_type, p.payment_date trans_date, p.id trans_no, iou.reference,  p.amount, iou.float_id, f.name float_name, p.iou_id, iou.project_id, iou.comments, iou.batch_no  
        FROM ".TB_PREF."i_owe_you_payments p  
            JOIN ".TB_PREF."i_owe_you iou  
                ON p.iou_id = iou.id 
                AND p.expense_source = ".db_escape(ST_PETTY_CASH)."
            JOIN ".TB_PREF."petty_cash_floats f   
                ON f.id = iou.float_id                     
        WHERE iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code)." 
            AND iou.batch_complete = 0 
            AND p.paid = 1 
            AND p.payment_date >=".db_escape($from_date)." AND p.payment_date <=".db_escape($to_date);

// IOU Advances
    $sql_iou_advances = "SELECT ".db_escape(ST_I_OWE_YOU_ADVANCES)." trans_type, a.tran_date trans_date, a.batch_no trans_no, '' reference, d.amount, d.float_id, f.name float_name, d.iou_id, iou.project_id, iou.comments, iou.batch_no  
        FROM ".TB_PREF."i_owe_you_advances_details d 
            JOIN ".TB_PREF."i_owe_you_advances a 
                ON a.batch_no = d.batch_no 
            JOIN ".TB_PREF."i_owe_you iou  
                ON d.iou_id = iou.id 
            JOIN ".TB_PREF."petty_cash_floats f   
                ON f.id = iou.float_id                     
        WHERE iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code)." 
            AND iou.batch_complete = 0 
            AND a.tran_date >=".db_escape($from_date)." AND a.tran_date <=".db_escape($to_date);

// IOU Bank Payments
    $sql_iou_bank_payments = "SELECT bt.type trans_type, bt.trans_date, bt.trans_no, bt.ref reference, bt.amount*-1 amount, 0 float_id, f.name float_name, bt.person_id iou_id, iou.project_id, iou.comments, iou.batch_no  
        FROM  ".TB_PREF."bank_trans bt 
            JOIN  ".TB_PREF."i_owe_you iou  
                ON iou.id = bt.person_id 
            JOIN ".TB_PREF."petty_cash_floats f   
                ON f.id = iou.float_id                     
        WHERE bt.type IN(".db_escape(ST_IOU_BANKPAYMENT).",".db_escape(ST_IOU_BANKPAYMENT_RETURN).") 
            AND iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code)." 
            AND iou.batch_complete = 0 
            AND bt.trans_date >=".db_escape($from_date)." AND bt.trans_date <=".db_escape($to_date);

//IOU Expenses
    
    switch ($expenses_filter) {
        case 1:
            $expenses_filter_sql = " AND b.approval_status NOT IN (".AS_REJECTED.")";
            break;
        case 2:
            $expenses_filter_sql = " AND b.approval_status IN (".AS_AWAITING_APPROVAL.",".AS_WITH_THE_APPROVER.",".AS_APPROVED.")";
            break;
        case 3:
            $expenses_filter_sql = " AND b.checked = 0 AND b.approval_status IN (".AS_AWAITING_APPROVAL.",".AS_WITH_THE_APPROVER.",".AS_APPROVED.")";
            break;

        default:
            $expenses_filter_sql = " ";
            break;
    } 
    
    $sql_iou_expenses = "SELECT DISTINCT ".db_escape(ST_I_OWE_YOU_BATCH)." trans_type, b.tran_date trans_date, 0 trans_no, '' reference, b.amount*-1 amount, b.float_id, f.name float_name, 0 iou_id, b.project_id, '' comments, b.batch_no     
        FROM ".TB_PREF."i_owe_you_batches b 
            JOIN  ".TB_PREF."i_owe_you iou  
                ON iou.batch_no = b.batch_no    
            JOIN ".TB_PREF."petty_cash_floats f   
                ON f.id = iou.float_id ";
    $sql_iou_expenses .= $expenses_filter_sql;
    $sql_iou_expenses .= " WHERE iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code)." 
            AND b.complete = 0  
            AND b.tran_date >=".db_escape($from_date)." AND b.tran_date <=".db_escape($to_date);

//Petty Cash Expenses
    $sql_petty_cash_expenses = "SELECT ".db_escape(ST_I_OWE_YOU_BATCH)." trans_type, b.tran_date trans_date, 0 trans_no, '' reference, b.amount*-1 amount, b.float_id, f.name float_name, h.id iou_id, b.project_id, h.comments, b.batch_no    
        FROM ".TB_PREF."i_owe_you_batches b 
            JOIN  ".TB_PREF."petty_cash_header h   
                ON h.batch_no = b.batch_no  
            JOIN ".TB_PREF."petty_cash_floats f   
                ON f.id = h.float_id ";
    $sql_petty_cash_expenses .= $expenses_filter_sql;
    $sql_petty_cash_expenses .= "                     
        WHERE h.company_code =".db_escape($company_code)." AND h.employee_code =".db_escape($employee_code)."  
            AND b.complete = 0  
            AND b.tran_date >=".db_escape($from_date)." AND b.tran_date <=".db_escape($to_date);
    
    $sql = $sql_iou_payments 
            ." UNION ".$sql_petty_cash_payments 
            ." UNION ".$sql_iou_advances 
            ." UNION ".$sql_iou_bank_payments 
            ." UNION ".$sql_iou_expenses 
            ." UNION ".$sql_petty_cash_expenses 
            ." ORDER BY trans_date, trans_type, trans_no, batch_no ";
    
    $sql = $sql_iou_payments;
        
    return $sql;
}

function get_employee_petty_cash_outstanding_transactions($company_code, $employee_code, $expenses_filter, $from_date, $to_date)
{
    $sql = get_sql_for_employee_petty_cash_outstanding_transactions($company_code, $employee_code, $expenses_filter, $from_date, $to_date);
    
    return db_query($sql);
    
}

function get_employee_petty_cash_opening_balance($company_code, $employee_code, $expenses_filter, $from_date, $to_date)
{
    $trans_sql = get_sql_for_employee_petty_cash_outstanding_transactions($company_code, $employee_code, $expenses_filter, $from_date, $to_date);
    
    $sql = "SELECT SUM(amount) ob_balance FROM ($trans_sql) tmp_table";
    
    $result =  db_query($sql);
    return db_result_value($result, "ob_balance");
    
}