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



function get_sql_for_employee_petty_cash_outstanding_iou_payments($company_code, $employee_code, $batch_complete_filter, $from_date, $to_date,
                                                                  $date_filter_type=0)
{    
    //IOU Payments
    $sql_iou_payments = "SELECT ".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, iou.employee_code, iou.approval_status iou_status, 
                            b.approval_status batch_status, b.complete batch_complete, iou.tran_date iou_date                                   
                        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                     
                            LEFT JOIN ".TB_PREF."i_owe_you_batches b   
                                ON b.batch_no = iou.batch_no                     
                        WHERE p.paid = 1 ";
    
    if ($date_filter_type == 0) {
       $sql_iou_payments .= " AND p.payment_date >= ".db_escape($from_date)." AND p.payment_date <= ".db_escape($to_date);
    } else {
       $sql_iou_payments .= " AND iou.tran_date >= ".db_escape($from_date)." AND iou.tran_date <= ".db_escape($to_date);
    }
    
    if(strlen($employee_code) > 0){
        $sql_iou_payments .= " AND iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code);
    }
    if($batch_complete_filter == 0){
        $sql_iou_payments .= " AND iou.batch_complete = 0 ";
    }else{
        $sql_iou_payments .= " AND iou.batch_complete = 1 ";    
    }
    
    return $sql_iou_payments;
    
}


function get_sql_for_employee_petty_cash_outstanding_petty_cash_payments($company_code, $employee_code, $batch_complete_filter, $from_date, $to_date, 
                                                                         $date_filter_type=0)
{   
//Pettycash Payments
    $sql_petty_cash_payments = "SELECT ".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, iou.employee_code, 
                                    b.approval_status iou_status, b.approval_status batch_status, b.complete batch_complete, iou.tran_date iou_date         
                                FROM ".TB_PREF."i_owe_you_payments p  
                                    JOIN ".TB_PREF."petty_cash_header 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    
                                    JOIN ".TB_PREF."i_owe_you_batches b   
                                        ON b.batch_no = iou.batch_no 
                                WHERE p.paid = 1 ";
    
    if ($date_filter_type == 0) {
       $sql_petty_cash_payments .= " AND p.payment_date >= ".db_escape($from_date)." AND p.payment_date <= ".db_escape($to_date);
    } else {
       $sql_petty_cash_payments .= " AND iou.tran_date >= ".db_escape($from_date)." AND iou.tran_date <= ".db_escape($to_date);
    }
    
    if(strlen($employee_code) > 0){
        $sql_petty_cash_payments .= " AND iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code);
    } 
    if($batch_complete_filter == 0){
        $sql_petty_cash_payments .= " AND iou.batch_complete = 0 ";
    }else{
        $sql_petty_cash_payments .= " AND iou.batch_complete = 1 ";    
    }
    
    return $sql_petty_cash_payments;
    
}


function get_sql_for_employee_petty_cash_outstanding_iou_advances($company_code, $employee_code, $batch_complete_filter, $from_date, $to_date, 
                                                                  $date_filter_type=0)
{  
// 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, iou.employee_code,   
                            iou.approval_status iou_status, b.approval_status batch_status, b.complete batch_complete, iou.tran_date iou_date                
                        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                      
                            LEFT JOIN ".TB_PREF."i_owe_you_batches b   
                                ON b.batch_no = iou.batch_no                       
                        WHERE 1 = 1 ";
    
    if ($date_filter_type == 0) {
       $sql_iou_advances .= " AND a.tran_date >= ".db_escape($from_date)." AND a.tran_date <= ".db_escape($to_date);
    } else {
       $sql_iou_advances .= " AND iou.tran_date >= ".db_escape($from_date)." AND iou.tran_date <= ".db_escape($to_date);
    }
    
    if(strlen($employee_code) > 0){
        $sql_iou_advances .= " AND iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code);
    } 
    
    if($batch_complete_filter == 0){
        $sql_iou_advances .= " AND iou.batch_complete = 0 ";
    }else{
        $sql_iou_advances .= " AND iou.batch_complete = 1 ";    
    }
    
    return $sql_iou_advances;
}

function get_sql_for_employee_petty_cash_outstanding_iou_bank_payments($company_code, $employee_code, $batch_complete_filter, $from_date, $to_date, 
                                                                       $date_filter_type=0)
{    
    // 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, iou.employee_code, iou.approval_status iou_status, 
                                b.approval_status batch_status, b.complete batch_complete, iou.tran_date iou_date              
                            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                       
                                LEFT JOIN ".TB_PREF."i_owe_you_batches b   
                                    ON b.batch_no = iou.batch_no                     
                            WHERE bt.type IN(".db_escape(ST_IOU_BANKPAYMENT).",".db_escape(ST_IOU_BANKPAYMENT_RETURN).")"; 
                                
    
    if ($date_filter_type == 0) {
       $sql_iou_bank_payments .= " AND bt.trans_date >= ".db_escape($from_date)." AND bt.trans_date <= ".db_escape($to_date);
    } else {
       $sql_iou_bank_payments .= " AND iou.tran_date >= ".db_escape($from_date)." AND iou.tran_date <= ".db_escape($to_date);
    }
    
    if(strlen($employee_code) > 0){
        $sql_iou_bank_payments .= " AND iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code);
    }
    
    if($batch_complete_filter == 0){
        $sql_iou_bank_payments .= " AND iou.batch_complete = 0 ";
    }else{
        $sql_iou_bank_payments .= " AND iou.batch_complete = 1 ";    
    }

    return $sql_iou_bank_payments;
}

function get_sql_for_employee_petty_cash_outstanding_batch_expenses($company_code, $employee_code, $batch_complete_filter, $from_date, $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, iou.employee_code, 0 iou_status, 
                            b.approval_status batch_status, b.complete batch_complete, b.tran_date iou_date 
                        FROM ".TB_PREF."i_owe_you_batches b   
                            JOIN ".TB_PREF."i_owe_you iou  
                                ON b.batch_no = iou.batch_no  
                            JOIN ".TB_PREF."petty_cash_floats f   
                                ON f.id = b.float_id 
                        WHERE b.tran_date >= ".db_escape($from_date)." AND b.tran_date <= ".db_escape($to_date);
    
    if(strlen($employee_code) > 0){
        $sql_iou_expenses .= " AND iou.company_code =".db_escape($company_code)." AND iou.employee_code =".db_escape($employee_code);
    }
    
    if($batch_complete_filter == 0){
        $sql_iou_expenses .= " AND b.complete = 0 ";
    }else{
        $sql_iou_expenses .= " AND b.complete = 1 ";    
    }
    
    return $sql_iou_expenses;
}


function get_sql_for_employee_petty_cash_outstanding_petty_cash_expenses($company_code, $employee_code, $batch_complete_filter, $from_date, $to_date)
{   

//Petty Cash 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_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, h.employee_code, 0 iou_status, 
                                    b.approval_status batch_status, b.complete batch_complet, b.tran_date iou_date
                                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  
                                WHERE b.tran_date >= ".db_escape($from_date)." AND b.tran_date <= ".db_escape($to_date);
    
    if($batch_complete_filter == 0){
        $sql_petty_cash_expenses .= " AND b.complete = 0 ";
    }else{
        $sql_petty_cash_expenses .= " AND b.complete = 1 ";    
    }
    
    if(strlen($employee_code) > 0){
        $sql_petty_cash_expenses .= " AND h.company_code =".db_escape($company_code)." AND h.employee_code =".db_escape($employee_code);
    }
    
    return $sql_petty_cash_expenses;
    
}


function get_sql_for_employee_petty_cash_outstanding_transactions($company_code, $employee_code, $batch_complete_filter, $from_date, $to_date, 
                                                                  $date_filter_type=0) {

    $sql_iou_payments = get_sql_for_employee_petty_cash_outstanding_iou_payments($company_code, $employee_code, $batch_complete_filter, $from_date, 
                                                                                 $to_date, $date_filter_type);
    $sql_petty_cash_payments = get_sql_for_employee_petty_cash_outstanding_petty_cash_payments($company_code, $employee_code, $batch_complete_filter, 
                                                                                               $from_date, $to_date, $date_filter_type);
    $sql_iou_advances = get_sql_for_employee_petty_cash_outstanding_iou_advances($company_code, $employee_code, $batch_complete_filter, $from_date, 
                                                                                 $to_date, $date_filter_type);
    $sql_iou_bank_payments = get_sql_for_employee_petty_cash_outstanding_iou_bank_payments($company_code, $employee_code, $batch_complete_filter, 
                                                                                           $from_date, $to_date, $date_filter_type);
    $sql_iou_expenses = get_sql_for_employee_petty_cash_outstanding_batch_expenses($company_code, $employee_code, $batch_complete_filter, $from_date, 
                                                                                   $to_date);
    $sql_petty_cash_expenses = get_sql_for_employee_petty_cash_outstanding_petty_cash_expenses($company_code, $employee_code, $batch_complete_filter, 
                                                                                               $from_date, $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 ";    
            ." ORDER BY iou_date, iou_id, trans_date, trans_type, trans_no, batch_no ";    
        
//    $sql = $sql_petty_cash_expenses;
    
    return $sql;
}


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

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