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


    
function update_i_owe_you_advances($id, $max_batch_no, $total_advances_paid)
{
    
    $sql = "UPDATE ".TB_PREF."i_owe_you 
            SET 
                advances_batch_no=".db_escape($max_batch_no)." ,
                advance_amount = ".db_escape($total_advances_paid)."    
            WHERE id=".db_escape($id)." AND
                    batch_complete = 0";

    db_query($sql, "Could not update_i_owe_you_advances");    
    
    return db_num_affected_rows();
}


function get_i_owe_yous_for_advances_entry($from_date, $to_date, $float_id = 0)
{  
    $user_id = $_SESSION['wa_current_user']->user;

        $sql= "SELECT 
                ou.*,               
                st.value_name type_name,
                pm.description project_description,
                pf.name float_name ,
                '' batch_approval_status, 
                em.last_name,
                pa.amount planned_amount 
            FROM ".TB_PREF."i_owe_you ou 
                    INNER JOIN ".TB_PREF."projects_master as pm ON 
                            ou.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."user_data_access_groups as udag ON 
                            pm.dag_id = udag.dag_id 
                    LEFT JOIN ".TB_PREF."list_values st ON 
                            ou.type = st.id AND st.list_code = 'PETTY_CASH_TYPES' 
                    LEFT JOIN ".TB_PREF."petty_cash_floats pf  ON 
                            ou.float_id = pf.id 
                    LEFT JOIN ".TB_PREF."employee_master em ON
                            ou.employee_code = em.employee_code  
                    LEFT JOIN ".TB_PREF."planned_i_owe_you_advances pa ON
                            ou.id = pa.iou_id  

            WHERE  ou.batch_complete = 0 AND  
                    ou.amount > ou.advance_amount + ou.payments_total AND 
                    ou.approval_status = ".AS_APPROVED." AND 
                    udag.user_id = ".db_escape($user_id)." AND
                    ou.tran_date >=".db_escape(date2sql($from_date))." AND ou.tran_date <=".db_escape(date2sql($to_date));
        
//        if($float_id > 0){
            $sql .= " AND
                    ou.float_id =".db_escape($float_id);
//        }
        
        $sql .= " ORDER BY ou.batch_no, ou.id, tran_date";
    

    return db_query($sql,"Could not get_i_owe_you_advances");	
}


function get_iou_advances_max_batch_no()
{
    $sql = "SELECT MAX(batch_no) max_batch_no 
            FROM ".TB_PREF."i_owe_you_advances";           
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function add_i_owe_you_advances_header($advance_batch_no, $total_amount, $batch_date_)
{  
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');         

    $sql = "INSERT INTO ".TB_PREF."i_owe_you_advances (
                    batch_no, amount, tran_date,
                    created_by, created_on )
            VALUES (".db_escape($advance_batch_no).", ".db_escape($total_amount).", ".db_escape(date2sql($batch_date_)).", 
                    ".db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql, "Could not insert i owe you advance header.");
    
    return db_num_affected_rows();
}


function get_i_owe_you_advances_paid_total($iou_id)
{
    $sql_sum = "SELECT SUM(amount) total_amount  
                FROM ".TB_PREF."i_owe_you_advances_details  
                WHERE iou_id=".db_escape($iou_id);
    
    $result = db_query($sql_sum, "Could not get_i_owe_you_advances_paid_total");    
    $advance_sum = db_fetch($result);  
    
    if(isset($advance_sum['total_amount']) && $advance_sum['total_amount'] > 0){
        $total_amount = $advance_sum['total_amount'];
    }
    else{
        $total_amount = 0;
    }
    
    return $total_amount;
}

function add_to_i_owe_you_advances_details($batch_no, $iou_id, $amount, $float_id)
{
    $sql = "INSERT INTO ".TB_PREF."i_owe_you_advances_details (
                    batch_no, iou_id, 
                    amount, float_id)
            VALUES (".db_escape($batch_no).", ".db_escape($iou_id).", 
                    ".db_escape($amount).", ".db_escape($float_id).")";
    
    db_query($sql, "Could not add_to_i_owe_you_advances_details.");
    
}

function add_planned_i_owe_you_advance($iou_id, $advance_amount)
{
    $sql = "INSERT INTO ".TB_PREF."planned_i_owe_you_advances (
                    iou_id, amount)
            VALUES (".db_escape($iou_id).", ".db_escape($advance_amount).")";
    
    db_query($sql, "Could not add_planned_i_owe_you_advance.");
}

function delete_planned_i_owe_you_advances()
{
    $sql = "TRUNCATE table ".TB_PREF."planned_i_owe_you_advances";
    
    db_query($sql, "Could not add_planned_i_owe_you_advance.");
}


function update_i_owe_you_advance_float_reimbursement_amount($id, $reimbursed_amount, $row_state)
{
    $sql = "UPDATE ".TB_PREF."i_owe_you_advances_details 
            SET reimbursed_amount = $reimbursed_amount ,
                row_state =". db_new_row_state($row_state)."  
            WHERE amount >= ".db_escape($reimbursed_amount)." AND 
                    id =".db_escape($id)." AND 
                    row_state =".db_escape($row_state);
    
    db_query($sql, "update_i_owe_you_advance_float_reimbursement_amount");  
    return db_num_affected_rows();    
    
}

?>