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


function add_selected_iou_to_batch($iou_id, $current_batch_no, $new_batch_no)
{
        $sql = "UPDATE ".TB_PREF."i_owe_you 
                SET 
                    batch_no=".db_escape($new_batch_no)." 
                WHERE id=".db_escape($iou_id)." AND 
                    batch_no = ". db_escape($current_batch_no);

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

}

function add_i_owe_you_expence($batch_no, $expense_type_id, $task_id, $expence_date, $bill_no,
                               $expence_description, $amount, $expense_category_id, 
                                $expense_item_id, $project_site_id)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."i_owe_you_expenses (
                    iou_batch_no, expense_type_id, task_id, 
                    expence_date, bill_no,
                    expence_description, amount,
                    approved_amount, expense_category_id,
                    expense_item_id, project_site_id,
                    created_by, created_on)       
                VALUES ("
                    .db_escape($batch_no).", ".db_escape($expense_type_id).", ".db_escape($task_id).", "
                    .db_escape(date2sql($expence_date)).", ".db_escape($bill_no).", " 
                    .db_escape($expence_description).", ".db_escape($amount).","
                    .db_escape($amount).",".db_escape($expense_category_id).","
                    .db_escape($expense_item_id).",".db_escape($project_site_id).","
                    .db_escape($created_by).", ".db_escape($created_on).")";
                    
        
    db_query($sql,"I Owe You  could not be added");

    $new_id = db_insert_id();

    $result = get_batch_i_owe_you_expences($batch_no);
    if(db_num_rows($result) == 1){
        // This is the first expense of the batch and the current user is the true Originator of the batch.
        update_iou_batch_originator($batch_no);
    }

            
    return $new_id;
}

function update_i_owe_you_expence($selected_id, $expense_type_id, $task_id, 
                                    $expence_date, $bill_no, 
                                    $expence_description, $amount, $expense_category_id, 
                                    $expense_item_id, $project_site_id)
{
         $updated_by = $_SESSION['wa_current_user']->user;
         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."i_owe_you_expenses 
                SET 
                    expense_category_id=".db_escape($expense_category_id).",
                    expense_item_id=".db_escape($expense_item_id).",
                    expense_type_id=".db_escape($expense_type_id).",
                    task_id=".db_escape($task_id).",
                    expence_date=".db_escape(date2sql($expence_date)).",
                    bill_no=".db_escape($bill_no).",
                    expence_description=".db_escape($expence_description).",
                    amount=".db_escape($amount).",
                    approved_amount=".db_escape($amount).",
                    project_site_id=".db_escape($project_site_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($selected_id)." 
                    AND approved = 0 ";

        db_query($sql,"I Owe You  could not be updated");
        
        
        return db_num_affected_rows();
}


function update_batch_expense_detail_documents_checked_status($expense_detail_id, $list_value_id)
{
        $sql = "UPDATE ".TB_PREF."i_owe_you_expenses 
                SET document_status_id=".db_escape($list_value_id)."                         
                WHERE id=".db_escape($expense_detail_id);
        db_query($sql,"update_batch_expense_detail_documents_checked_status"); 
}


function update_i_owe_you_expence_approved_amount($selected_id, $document_status_id, $approved_amount)
{
         $updated_by = $_SESSION['wa_current_user']->user;
         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."i_owe_you_expenses 
                SET document_status_id=".db_escape($document_status_id).",
                    approved_amount=".db_escape($approved_amount).",
                    appr_amt_updated_by=".db_escape($updated_by).",
                    appr_amt_updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($selected_id)." AND 
                        approved = 0";

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


function update_batch_expences_approved($batch_no)
{
        $sql = "UPDATE ".TB_PREF."i_owe_you_expenses 
                SET approved = 1  
                WHERE iou_batch_no =".db_escape($batch_no)." AND 
                        approved = 0";

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

function delete_i_owe_u_expence($rec_id)
{
        $sql = "DELETE FROM ".TB_PREF."i_owe_you_expenses  
                WHERE id=".db_escape($rec_id)." AND 
                        approved = 0";

        db_query($sql,"I owe you  could not be deleted");
        
        return db_num_affected_rows();
}


function get_i_owe_yous_for_expenses_entry($new, $iou_id, $batch_no, $from_date, $to_date, $employee_code, $type_id, $project_id, $float_id)
{  
    $user_id = $_SESSION['wa_current_user']->user;
    
    if($new){
        $sql= "SELECT 
                    ou.*,
                    st.value_name type_name,
                    pm.description project_description,
                    pf.name float_name ,
                    '' batch_approval_status    
                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  
                WHERE  ou.batch_complete = 0 AND 
                        ou.batch_no = 0 AND 
                        ou.approval_status = ".AS_APPROVED." AND 
                        udag.user_id = ".db_escape($user_id); 
                    
            
    }
    else{
        $sql= " SELECT 
                        ou.*,
                        st.value_name type_name,
                        pm.description project_description,
                        pf.name float_name,
                        b.approval_status batch_approval_status 
                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 
                        INNER JOIN ".TB_PREF."i_owe_you_batches b ON 
                                ou.batch_no = b.batch_no 
                        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."approvers apr ON 
                                b.next_approval_group_id = apr.approval_group_id  AND 
                                udag.user_id = apr.user_id
                WHERE  b.complete = 0 AND 
                        ((b.approval_status = ".AS_NEW." OR b.approval_status = ".AS_BACK_TO_ORIGINATOR.") OR 
                        ((b.approval_status = ".AS_AWAITING_APPROVAL." OR b.approval_status = ".AS_WITH_THE_APPROVER.") AND b.next_approval_group_id = apr.approval_group_id AND udag.user_id = apr.user_id)) AND    
                        udag.user_id = ".db_escape($user_id);  
    }
    
    
    if($iou_id > 0 || $batch_no > 0){
        if($iou_id > 0){
            $sql .= " AND ou.id = ".db_escape($iou_id);
        }

        if($batch_no > 0){
            $sql .= " AND ou.batch_no = ".db_escape($batch_no);
        }
    }
    else{
            
        if(strlen($employee_code) > 0 && $employee_code != 0){
            $sql .= " AND ou.employee_code = ".db_escape($employee_code);
        }

        if($type_id > 0){
            $sql .= " AND ou.type = ".db_escape($type_id);
        }

        if(strlen($project_id) > 0){
            $sql .= " AND ou.project_id = ".db_escape($project_id);
        }

        if($float_id > 0){
            $sql .= " AND ou.float_id = ".db_escape($float_id);
        }
    
    
        $sql .= " AND ou.tran_date >= ".db_escape(date2sql($from_date))." 
                    AND ou.tran_date <= ".db_escape(date2sql($to_date)); 
    }
    

$sql .= " ORDER BY ou.batch_no, ou.id, tran_date";
        
	return db_query($sql,"Could not get_i_owe_yous_for_expenses_entry");	
}

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

    $sql_ou = "SELECT 
            ou.id,
            ou.project_id,
            ou.float_id,
            ou.employee_code,
            ou.company_code,
            ou.tran_date,
            ou.amount,
            ou.comments,
            ou.batch_no,
            st.value_name type_name,
            pm.description project_description,
            pf.name float_name   
        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 
                INNER JOIN ".TB_PREF."i_owe_you_batches b ON 
                        ou.batch_no = b.batch_no 
                INNER JOIN ".TB_PREF."approvers apr ON 
                        b.next_approval_group_id = apr.approval_group_id AND 
                        udag.user_id = apr.user_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  
        WHERE  b.complete = 0 AND 
                (b.approval_status = ".AS_AWAITING_APPROVAL." OR b.approval_status = ".AS_WITH_THE_APPROVER.")  
                AND udag.user_id = ".db_escape($user_id)." 
                AND ou.tran_date >= ".db_escape($from_date)." 
                AND ou.tran_date <= ".db_escape($to_date);  
    
    $sql_pc = "SELECT 
            pc.id,
            pc.project_id,
            pc.float_id,
            pc.employee_code,
            pc.company_code,
            pc.tran_date,
            pc.amount,
            pc.comments,
            pc.batch_no,
            st.value_name type_name,
            pm.description project_description,
            pf.name float_name   
        FROM ".TB_PREF."petty_cash_header pc 
                INNER JOIN ".TB_PREF."projects_master as pm ON 
                        pc.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."user_data_access_groups as udag ON 
                            pm.dag_id = udag.dag_id 
                INNER JOIN ".TB_PREF."i_owe_you_batches b ON 
                        pc.batch_no = b.batch_no 
                INNER JOIN ".TB_PREF."approvers apr ON 
                        b.next_approval_group_id = apr.approval_group_id AND 
                        udag.user_id = apr.user_id
                LEFT JOIN ".TB_PREF."list_values st ON 
                        pc.type = st.id AND st.list_code = 'PETTY_CASH_TYPES'  
                LEFT JOIN ".TB_PREF."petty_cash_floats pf  ON 
                        pc.float_id = pf.id  
        WHERE  b.complete = 0 AND 
                (b.approval_status = ".AS_AWAITING_APPROVAL." OR b.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                udag.user_id = ".db_escape($user_id)." 
                AND pc.tran_date >= ".db_escape($from_date)." 
                AND pc.tran_date <= ".db_escape($to_date); 
    

	$sql_pc .=" ORDER BY batch_no, tran_date";
    
    $sql = $sql_ou." UNION ".$sql_pc;
    
	return db_query($sql,"Could not get_i_owe_you_expence_batched_for_approval");	
}

function get_iou_next_batch_no()
{
    $sql= "SELECT MAX(batch_no) max_batch_no 
            FROM ".TB_PREF."i_owe_you_batches ";

	$result = db_query($sql,"Could not get_iou_next_batch_no");
    
    $batch_no = db_fetch($result);
    if($batch_no){
        return $batch_no["max_batch_no"] + 1;
    }
    else{
        return 1;
    }
}



function get_batch_i_owe_you_payments($batch_no)
{
    $sql = "SELECT py.*, 
                    iou.batch_no, 
                    oun.value_name iou_type_name 
            FROM ".TB_PREF."i_owe_you_payments py,
                    ".TB_PREF."i_owe_you iou,
                    ".TB_PREF."list_values oun  
            WHERE iou.id = py.iou_id AND 
                    iou.type = oun.id AND  
                    iou.batch_no=".db_escape($batch_no);   
            
    return db_query($sql, "Could not retrieve i owe you payment");       
    
} 

function get_batch_i_owe_you_expences($batch_no)
{
        $sql= "SELECT ex.*, 
                    pt.description task_description, 
                    dsn.value_name document_status_name,   
                    etn.value_name expense_type_name ,
                    ps.description project_site_description    
               FROM ".TB_PREF."i_owe_you_expenses ex 
                        LEFT JOIN ".TB_PREF."project_tasks pt ON 
                                ex.task_id = pt.task_id 
                        LEFT JOIN ".TB_PREF."list_values dsn ON 
                                ex.document_status_id =  dsn.id 
                        LEFT JOIN ".TB_PREF."list_values etn ON 
                                ex.expense_type_id =  etn.id 
                        LEFT JOIN ".TB_PREF."project_sites ps ON
                                ex.project_site_id = ps.id
               WHERE  ex.iou_batch_no =".db_escape($batch_no);               
	        
	return db_query($sql, "Could not get_batch_i_owe_you_expences");	
        
    
}

function get_batch_i_owe_you_valid_expences($batch_no)
{
        $sql= "SELECT ex.*, 
                    pt.description task_description, 
                    dsn.value_name document_status_name   
               FROM ".TB_PREF."i_owe_you_expenses ex 
                        INNER JOIN ".TB_PREF."i_owe_you_batches b ON  
                                ex.iou_batch_no = b.batch_no  
                        LEFT JOIN ".TB_PREF."project_tasks pt ON 
                                ex.task_id = pt.task_id 
                        LEFT JOIN ".TB_PREF."list_values dsn ON 
                                ex.document_status_id =  dsn.id 
               WHERE  ex.iou_batch_no =".db_escape($batch_no)." AND 
                        b.approval_status <> ".AS_REJECTED;
	        
	return db_query($sql, "Could not get_batch_i_owe_you_valid_expences");	
        
    
}



function get_i_owe_you_expence_line($selected_id)
{
        $sql= "SELECT * 
               FROM ".TB_PREF."i_owe_you_expenses
               WHERE id=".db_escape($selected_id);
        
	$result=db_query($sql,"Could not retrieve i owe you expences");	
        return db_fetch($result);
    
}



function get_i_owe_you_batch_expenses_total($batch_no, $expense_id=-1)
{
    $sql = "SELECT SUM(ex.amount) batch_expenses_total 
            FROM ".TB_PREF."i_owe_you_expenses ex 
            WHERE ex.iou_batch_no = ".db_escape($batch_no). " AND 
                    ex.id <> ".db_escape($expense_id);    
    
    $result = db_query($sql, "Could not get_i_owe_you_batch_expenses_total");
    $batch_ex = db_fetch($result);  
    
    if($batch_ex){
        return $batch_ex['batch_expenses_total'];
    }
    else{
        return 0;
    }
    
}

function get_i_owe_you_batch_expenses_approved_total($batch_no)
{
    $sql = "SELECT SUM(ex.approved_amount) approved_expenses_total 
            FROM ".TB_PREF."i_owe_you_expenses ex 
            WHERE ex.iou_batch_no = ".db_escape($batch_no);    
    
    $result = db_query($sql, "Could not get_i_owe_you_batch_expenses_approved_total");
    $batch_ex = db_fetch($result);  
    
    if($batch_ex){
        return $batch_ex['approved_expenses_total'];
    }
    else{
        return 0;
    }
    
}

function get_i_owe_you_batch_totals($batch_no, $excluding_iou_no=0)
{
    $sql = "SELECT SUM(amount) iou_batch_total,
                    SUM(advance_amount) iou_batch_advances_total 
            FROM ".TB_PREF."i_owe_you iou  
            WHERE iou.batch_no = ".db_escape($batch_no);  
    
    if ($excluding_iou_no > 0) {
        $sql .= " AND iou.id <> ".db_escape($excluding_iou_no); 
    }
    
    $result = db_query($sql, "Could not get_i_owe_you_batch_totals");
    return db_fetch($result);    

    
}


function get_unapproved_i_owe_you_expense_batch_nos()
{
    $sql= "SELECT DISTINCT ou.batch_no 
           FROM ".TB_PREF."i_owe_you ou,
                ".TB_PREF."i_owe_you_expenses ex 
           WHERE ou.batch_no = ex.iou_batch_no AND 
                ex.status_code IN('','O') AND 
                ou.batch_complete = 0 AND ou.batch_no > 0 AND 
                ou.approval_status = ".db_escape(AS_APPROVED)." 
           ORDER BY ou.batch_no";
        
	return db_query($sql,"Could not get_unapproved_i_owe_you_expence_batch_nos");	
    
}

function get_unapproved_i_owe_you_batch_expences($batch_no)
{
    $sql = "SELECT ex.*,
                    pt.description task_description 
            FROM ".TB_PREF."i_owe_you_expenses ex 
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            ex.task_id = pt.task_id 
            WHERE ex.iou_batch_no = ".db_escape($batch_no)." 
                    AND ex.approved = 0"; 
        
	return db_query($sql,"Could not get_unapproved_i_owe_you_batch_expences");	
    
}

function approve_i_owe_you_expense($row_id, $approved_amount, $status_code)
{
	$approved_by = $_SESSION['wa_current_user']->user;
        $approved_on = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."i_owe_you_expenses 
                SET approved_amount = ".db_escape($approved_amount).", 
                    status_code = ".db_escape($status_code).", 
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".$row_id;
	
	db_query($sql,"Could not approve i owe you");	
        
        return db_num_affected_rows();		
}

function get_approved_i_owe_you_batch_expenses_total($batch_no)
{
    $sql = "SELECT SUM(ex.approved_amount) batch_expenses_total 
            FROM ".TB_PREF."i_owe_you_expenses ex 
                JOIN ".TB_PREF."i_owe_you_batches b ON 
                    ex.iou_batch_no = b.batch_no 
            WHERE b.approval_status = ".AS_APPROVED." AND 
                    ex.approved = 1 AND  
                    ex.iou_batch_no = ".db_escape($batch_no);  
    
    $result = db_query($sql, "Could not get_approved_i_owe_you_batch_expenses_total");
    $batch_ex = db_fetch($result);  
    
    if($batch_ex){
        return $batch_ex['batch_expenses_total'];
    }
    else{
        return 0;
    }
    
}


function add_i_owe_you_batch_header($batch_no, $project_id, 
                    $float_id, $amount, $batch_advances_total, $tran_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_batches(
                    batch_no, project_id, 
                    float_id, amount, 
                    advances_total,
                    tran_date,
                    created_by, created_on, row_state)                     
                    
                VALUES ("
                    .db_escape($batch_no).", ".db_escape($project_id).", "
                    .db_escape($float_id).", ".db_escape($amount).", " 
                    .db_escape($batch_advances_total).", "
                    .db_escape($tran_date).", "
                    .db_escape($created_by).", ".db_escape($created_on).",0)";
                    
        
    db_query($sql,"Could not add_i_owe_you_batch_header");
}

function update_i_owe_you_batch_header($batch_no, $batch_total, $batch_advances_total)
{
         $updated_by = $_SESSION['wa_current_user']->user;
         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."i_owe_you_batches 
                SET amount=".db_escape($batch_total).",
                    advances_total=".db_escape($batch_advances_total).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE batch_no=".db_escape($batch_no);

        db_query($sql,"I Owe You  could not be updated");

}


function update_i_owe_you_batch_checked($batch_no)
{
        $sql = "UPDATE ".TB_PREF."i_owe_you_batches 
                SET checked = 1 
                WHERE batch_no=".db_escape($batch_no);
        db_query($sql,"update_i_owe_you_batch_checked");
}


function update_i_owe_you_batch_approved_total($batch_no, $approved_total, $row_state)
{
        $sql = "UPDATE ".TB_PREF."i_owe_you_batches 
                SET amount=".db_escape($approved_total).",
                    row_state = ".db_new_row_state($row_state)." 
                WHERE batch_no=".db_escape($batch_no)."  
                        AND (approval_status IN(".db_escape(AS_NEW).",".db_escape(AS_WITH_THE_APPROVER).",".db_escape(AS_AWAITING_APPROVAL).",".db_escape(AS_BACK_TO_ORIGINATOR).")) 
                        AND row_state = ".db_escape($row_state);
        
        db_query($sql,"I Owe You  could not be updated");

        return db_num_affected_rows();
}


function get_i_owe_you_batch_header($batch_no)
{
    $sql= "SELECT b.*, p.description project_description, f.name float_name, p.project_code 
           FROM ".TB_PREF."i_owe_you_batches b,
                ".TB_PREF."projects_master p,
                ".TB_PREF."petty_cash_floats f  
           WHERE b.project_id = p.project_id AND 
                    b.float_id = f.id AND 
                    batch_no=".db_escape($batch_no);
        
	$result=db_query($sql,"Could not get_i_owe_you_batch_header");	
    return db_fetch($result);
    
}

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

function close_i_owe_you_batch($batch_no, $row_state)
{
    $batch_completed_by = $_SESSION['wa_current_user']->user;
    $batch_completed_on = date('Y-m-d H:i:s');

	$sql = "UPDATE ".TB_PREF."i_owe_you_batches  
            SET complete = 1 ,
                    completed_by = ".db_escape($batch_completed_by).", 
                    completed_on = ".db_escape($batch_completed_on).", 
                    row_state = ".db_new_row_state($row_state)."   
            WHERE batch_no = ".$batch_no." AND batch_complete = 0 AND row_state = ".$row_state;
        
    db_query($sql,"Could not close_i_owe_you_batch");
    
    $n = db_num_affected_rows();
    
    if($n == 1){
        return array("success" => true,"tran_no" => $batch_no, "message" => "");
    }
    else{
        return array("success" => true,"tran_no" => $batch_no, "I Owe You batch $batch_no has been been updated by another user.");
    }
        
}

function update_iou_batch_originator($batch_no)
{
         $created_by = $_SESSION['wa_current_user']->user;

        $sql = "UPDATE ".TB_PREF."i_owe_you_batches 
                SET created_by=".db_escape($created_by)." 
                WHERE batch_no=".db_escape($batch_no);

        db_query($sql,"Could not update_iou_batch_originator");

}

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


function get_batch_i_owe_you_latest_expense_date($batch_no)
{
        $sql= "SELECT ex.expence_date 
               FROM ".TB_PREF."i_owe_you_expenses ex 
               WHERE  ex.iou_batch_no =".db_escape($batch_no)." 
               ORDER BY expence_date DESC LIMIT 1";               
	        
	$result = db_query($sql, "Could not get_batch_i_owe_you_latest_expense_date");	
        
        $row = db_fetch($result);
        if(isset($row['expence_date']) && strlen($row['expence_date']) > 0){
            return $row['expence_date'];
        }
        else{
            return "";
        }

}

function update_iou_batch_tran_date($batch_no, $tran_date)
{
//    if($tran_date == ""){
//        $tran_date = get_batch_i_owe_you_latest_expense_date($batch_no);
//    }
    
    $sql = "UPDATE ".TB_PREF."i_owe_you_batches 
            SET tran_date = ".db_escape($tran_date)." 
            WHERE batch_no =".db_escape($batch_no);
    
    db_query($sql, "Could not update_iou_batch_tran_date");   
    
}


function get_i_owe_you_expences_documents_not_checked_count($batch_no, $document_status_id)
{
        $sql= "SELECT COUNT(ex.id) docs_count     
               FROM ".TB_PREF."i_owe_you_expenses ex   
               WHERE  ex.iou_batch_no =".db_escape($batch_no)." 
                   AND ex.document_status_id <> ".db_escape($document_status_id);               
	        
	$result = db_query($sql, "get_i_owe_you_expences_documents_not_checked_count");	
        
        return db_result_value($result, 'docs_count');
}

function update_i_owe_you_batch_row_state($batch_no, $row_state) {
    $sql = "UPDATE ".TB_PREF."i_owe_you_batches  
            SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE batch_no = ".db_escape($batch_no)." 
                AND row_state = ".db_escape($row_state);

    db_query($sql, "update_i_owe_you_batch_row_state");
    return db_num_affected_rows();
}

function delete_i_owe_you_batch_header($batch_no, $batch_row_state) {
    $sql = "DELETE FROM ".TB_PREF."i_owe_you_batches  
            WHERE batch_no = ".db_escape($batch_no)." 
                AND row_state = ".db_escape($batch_row_state);

        db_query($sql, "delete_i_owe_you_batch_header");
        return db_num_affected_rows();
}