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

function add_i_owe_you_header($reference, $type_id, $project_id,  
                       $float_id, $company_code, $employee_code, $amount,                        
                       $tran_date, $comments, $approval_status)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."i_owe_you (
                           reference, type, project_id,  
                           float_id, company_code, employee_code, amount, tran_date, comments, 
                           approval_status,  
                           created_by, created_on) 
                VALUES ("
                    .db_escape($reference).", ".db_escape($type_id).", ".db_escape($project_id).", "
                    .db_escape($float_id). ", ".db_escape($company_code). ", ".db_escape($employee_code). ", ".db_escape($amount).", "
                    .db_escape(date2sql($tran_date)).", ".db_escape($comments).", "
                    .db_escape($approval_status).",  "
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"I Owe You could not be added");
        
        return db_insert_id();

}

function add_i_owe_you_detail($iou_id, $task_id, 
                    $amount, $comments)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."i_owe_you_details(
                    iou_id, task_id, 
                    amount, comments, 
                    created_by, created_on) 
            VALUES ("
                    .db_escape($iou_id).", ".db_escape($task_id).", "
                    .db_escape($amount). ", ".db_escape($comments).",  "
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"Could not add_i_owe_you_detail.");

}

function update_i_owe_you_header($selected_id, $float_id, $company_code, $employee_code, $amount, 
                            $type_id, $tran_date, $comments, 
                            $row_state)
        
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."i_owe_you 
                SET                     
                    amount=".db_escape($amount).",
                    float_id=".db_escape($float_id).",     
                    company_code=".db_escape($company_code).",   
                    employee_code=".db_escape($employee_code).",  
                    type=".db_escape($type_id).",
                    tran_date =".db_escape(date2sql($tran_date)).",
                    comments =".db_escape($comments).",                  
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on).",
                    row_state=". db_new_row_state($row_state)."  
                WHERE id=".db_escape($selected_id)." AND row_state=".db_escape($row_state);

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

}

function update_i_owe_you_detail($id, $amount, 
                          $task_id, $comments, 
                          $row_state)
        
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."i_owe_you_details 
                SET                     
                    amount=".db_escape($amount).",
                    task_id=".db_escape($task_id).",                   
                    comments =".db_escape($comments).",                
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on).",
                    row_state=". db_new_row_state($row_state)."  
                WHERE id=".db_escape($id)." AND row_state=".db_escape($row_state);

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

}


function update_i_owe_you_status($rec_id, $status_code)
{

        $sql = "UPDATE ".TB_PREF."i_owe_you 
                SET status_code = ".db_escape($status_code)." 
                WHERE id=".db_escape($rec_id);

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

}

function delete_i_owe_you_detail($rec_id, $row_state)
{
        $sql = "DELETE FROM ".TB_PREF."i_owe_you_details  
                WHERE id=".db_escape($rec_id)." AND 
                    row_state =".db_escape($row_state);

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

function get_i_owe_you_record($selected_id)
{    
    $sql = "SELECT  ou.*, 
                pm.description project_name   
            FROM ".TB_PREF."i_owe_you ou 
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                            ou.project_id = pm.project_id 
            WHERE id = ".db_escape($selected_id);  

    $result = db_query($sql, "I owe you could not be retrieved");    
    return db_fetch($result);
}

function view_i_owe_you_header($iou_id)
{
    $sql = "SELECT ou.*, 
                ty.value_name type_name,
                pm.description project_description,
                fl.name float_name 
            FROM ".TB_PREF."i_owe_you ou 
                    INNER JOIN ".TB_PREF."list_values ty ON  
                            ou.type = ty.id 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            ou.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."petty_cash_floats fl ON 
                            ou.float_id = fl.id 
                    
                
            WHERE ou.id = ".db_escape($iou_id);
  

    $result = db_query($sql, "Could not view_i_owe_you_header");
    return db_fetch($result);
}

function view_i_owe_you_details($iou_id)
{
    $sql = "SELECT oud.*,
                    pt.description task_description 
            FROM ".TB_PREF."i_owe_you_details oud 
                    INNER JOIN ".TB_PREF."project_tasks pt ON 
                            pt.task_id = oud.task_id 
            WHERE oud.iou_id = ".db_escape($iou_id)." 
            ORDER BY pt.task_id";
  

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

function get_i_owe_u_amount($iou_id)
{
    $sql = "SELECT amount
            FROM ".TB_PREF."i_owe_you
            WHERE id=".db_escape($iou_id); 

    $result = db_query($sql, "Could not get i owe you amount");
    return db_fetch($result);    
    
}

function get_batch_i_owe_yous($batch_no, $iou_id = 0)
{  
	$sql= "SELECT 
                ou.*,
                st.value_name type_name,
                pm.description project_description 
            FROM ".TB_PREF."i_owe_you ou, "
                .TB_PREF."list_values st, "
                .TB_PREF."projects_master pm     
            WHERE ou.type = st.id AND st.list_code = 'PETTY_CASH_TYPES' AND 
                    ou.project_id = pm.project_id AND 
                    ou.approval_status = ".db_escape(AS_APPROVED);
                    
    if($batch_no > 0  && $iou_id > 0){
        $sql .= " AND (ou.batch_no = ".db_escape($batch_no)." OR ou.id = ".db_escape($iou_id).")";
    }
    elseif($iou_id > 0){
        $sql .= " AND (ou.id = ".db_escape($iou_id).")";
    }
    else{
        $sql .= " AND (ou.batch_no = ".db_escape($batch_no).")";
    }
    
    $sql .= " ORDER BY ou.id, tran_date";
        
	return db_query($sql,"Could not get_batch_i_owe_yous");	
}

function get_i_owe_you_batch_advances_total($batch_no)
{
    $sql= "SELECT SUM(advance_amount) total_advances 
            FROM ".TB_PREF."i_owe_you 
            WHERE batch_no = ".db_escape($batch_no);
        
	$result = db_query($sql,"Could not get_i_owe_you_batch_advances_total");	
    $sum = db_fetch($result); 
    if(isset($sum['total_advances']) && $sum['total_advances'] > 0){
        return $sum['total_advances'];
    }
    else{
        return 0;
    }
    
}

function get_batch_i_owe_you_details($batch_no, $iou_id = 0)
{  
	$sql= "SELECT 
                ou.project_id, ou.company_code, ou.employee_code, ou.type, ou.tran_date, ou.batch_no, ou.id iou_id,
                oud.task_id,oud.amount,oud.id, oud.comments,
                pt.description as task_description,
                st.value_name type_name,
                pm.description project_description 
            FROM ".TB_PREF."i_owe_you ou, "
                .TB_PREF."i_owe_you_details oud, "
                .TB_PREF."list_values st, "
                .TB_PREF."projects_master pm, "
                .TB_PREF."project_tasks pt  
            WHERE ou.id = oud.iou_id AND 
                    ou.type = st.id AND st.list_code = 'PETTY_CASH_TYPES' AND 
                    ou.project_id = pm.project_id AND 
                    oud.task_id = pt.task_id AND 
                    ou.approval_status = ".db_escape(AS_APPROVED);
                    
    if($batch_no > 0  && $iou_id > 0){
        $sql .= " AND (ou.batch_no = ".db_escape($batch_no)." OR ou.id = ".db_escape($iou_id).")";
    }
    elseif($iou_id > 0){
        $sql .= " AND (ou.id = ".db_escape($iou_id).")";
    }
    else{
        $sql .= " AND (ou.batch_no = ".db_escape($batch_no).")";
    }
    
    $sql .= " ORDER BY ou.id, tran_date";
        
	return db_query($sql,"Could not get_batch_i_owe_yous");	
}




function close_i_owe_you_for_payments($id)
{
	$sql = "UPDATE ".TB_PREF."i_owe_you  
            SET closed_for_payments = 1   
            WHERE id = ".$id." AND batch_complete = 0 ";
        
    db_query($sql,"Could not close_i_owe_you_for_payments");        
}

function complete_i_owe_you($id)
{
	$sql = "UPDATE ".TB_PREF."i_owe_you  
            SET closed_for_payments = 1, 
                batch_complete = 1    
            WHERE id = ".$id." AND batch_complete = 0 ";
        
    db_query($sql,"Could not complete_i_owe_you"); 
    
    return db_num_affected_rows();
}

function complete_i_owe_you_batch($batch_no, $keep_balance = false)
{
	$sql = "UPDATE ".TB_PREF."i_owe_you_batches  
            SET complete = 1,
                balance_with_project = ".db_escape($keep_balance)."     
            WHERE complete = 0 AND 
                    (approval_status = ".AS_APPROVED." OR approval_status = ".AS_REJECTED.") AND 
                    batch_no = ".$batch_no;
        
    db_query($sql,"Could not complete_i_owe_you_batch"); 
    
    return db_num_affected_rows();
}

function get_i_owe_you_details_new($iou_id, $project_id, $boq_group_id = 0)
{
    $sql = "SELECT pt.task_id, pt.project_id, pt.description, pt.budget, pt.budget_used, pt.budget_allocated, 
                    oud.id, oud.iou_id, oud.amount, oud.required_date, oud.comments, oud.row_state  
            FROM ".TB_PREF."project_tasks pt 
                    INNER JOIN ".TB_PREF."project_task_groups ptg ON 
                            pt.task_group_id = ptg.id 
                    LEFT JOIN ".TB_PREF."project_boq_item_groups boqg ON 
                            ptg.boq_group_id = boqg.id 
                    LEFT JOIN ".TB_PREF."i_owe_you_details oud ON 
                            pt.task_id = oud.task_id AND 
                            oud.iou_id = ".db_escape($iou_id)." 
                    LEFT JOIN ".TB_PREF."i_owe_you ou ON oud.iou_id = ou.id AND 
                            ou.project_id = pt.project_id AND 
                            ou.id = ".db_escape($iou_id)." 
            WHERE pt.project_id = ".db_escape($project_id)." AND 
                    ptg.boq_group_id = ".db_escape($boq_group_id)." 
            ORDER BY pt.task_id";
    
    return db_query($sql,"Could not release_i_owe_you_payment");
}

function get_i_owe_you_details_sum($iou_id)
{
    $sql = "SELECT SUM(amount) details_total   
            FROM ".TB_PREF."i_owe_you_details oud 
            WHERE oud.iou_id = ".db_escape($iou_id);
    
    $query = db_query($sql,"Could not get_i_owe_you_details");
    
    $t = db_fetch($query);
    
    if(isset($t['details_total']) && $t['details_total'] > 0){
        return $t['details_total'];
    }
    else{
        return 0;
    }
}


function get_i_owe_you_details($iou_id)
{
    $sql = "SELECT oud.*  
            FROM ".TB_PREF."i_owe_you_details oud 
            WHERE oud.iou_id = ".db_escape($iou_id);
    
    return db_query($sql,"Could not get_i_owe_you_details");
}


function get_i_owe_you_details2($iou_id)
{
    $sql = "SELECT oud.*,
                    pt.task_code,
                    pt.description task_description,
                    boqg.id boq_group_id,
                    boqg.code boq_group_code,
                    boqg.description boq_group_description 
            FROM ".TB_PREF."i_owe_you_details oud 
                    INNER JOIN ".TB_PREF."project_tasks pt ON 
                            oud.task_id = pt.task_id 
                    INNER JOIN ".TB_PREF."project_task_groups ptg ON 
                            pt.task_group_id = ptg.id 
                    LEFT JOIN ".TB_PREF."project_boq_item_groups boqg ON 
                            ptg.boq_group_id = boqg.id 
            WHERE oud.iou_id = ".db_escape($iou_id)." 
            ORDER BY boqg.position, ptg.description";
    
    return db_query($sql,"Could not get_i_owe_you_details");
}

function add_to_petty_cash_float($tran_type, $type_no, $float_id, $amount, $tran_date)
{

    $sql = "SELECT balance FROM ".TB_PREF."petty_cash_float_trans WHERE float_id = ". db_escape($float_id)." ORDER BY id DESC LIMIT 1";
    $bq = db_query($sql,"Could not add_to_petty_cash_float");
    $b = db_fetch($bq);
    
    $balance = $b['balance'] + $amount;
    
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."petty_cash_float_trans(
                    float_id, tran_type, type_no, 
                    amount, balance, 
                    tran_date, created_on) 
            VALUES (".db_escape($float_id).", ".db_escape($tran_type).", ".db_escape($type_no).", "
                    .db_escape($amount). ", ".db_escape($balance).", "
                    .db_escape(date2sql($tran_date)).", ".db_escape($created_on).")";
    
    db_query($sql,"Could not add_to_petty_cash_float");
    
}


function get_sql_for_petty_cash_float_inquiry($from_date,$to_date, $float_id, $filter_type, $filering_trans_no)
{  
	$sql= "SELECT 
                pt.id,
                pt.tran_type,
                pt.type_no,
                pf.name,
                
                pt.tran_date,
                pt.amount,    
                pt.balance
                
            FROM " .TB_PREF."petty_cash_float_trans pt
            LEFT JOIN ".TB_PREF."petty_cash_floats pf ON
                    pf.id = pt.float_id
            WHERE pt.tran_date >=".db_escape(date2sql($from_date))."AND 
                    pt.tran_date <=".db_escape(date2sql($to_date));

    if($float_id > 0){
        $sql .= "AND pt.float_id = ". db_escape($float_id);
    }
    if($filter_type > 0){
        $sql .= "AND pt.tran_type = ". db_escape($filter_type);
    }
    if($filering_trans_no > 0){
        $sql .= "AND pt.type_no = ". db_escape($filering_trans_no);
    }
	$sql .=" ORDER BY id";
        
	return $sql;
}


function view_batch_i_owe_yous($batch_no, $iou_id = 0)
{  
	$sql= "SELECT 
                ou.*,
                st.value_name type_name,
                pm.description project_description 
            FROM ".TB_PREF."i_owe_you ou, "
                .TB_PREF."list_values st, "
                .TB_PREF."projects_master pm     
            WHERE ou.type = st.id AND st.list_code = 'PETTY_CASH_TYPES' AND 
                    ou.project_id = pm.project_id";
                    
    if($batch_no > 0  && $iou_id > 0){
        $sql .= " AND (ou.batch_no = ".db_escape($batch_no)." OR ou.id = ".db_escape($iou_id).")";
    }
    elseif($iou_id > 0){
        $sql .= " AND (ou.id = ".db_escape($iou_id).")";
    }
    else{
        $sql .= " AND (ou.batch_no = ".db_escape($batch_no).")";
    }
    
    $sql .= " ORDER BY ou.id, tran_date";
        
	return db_query($sql,"Could not get_batch_i_owe_yous");	
}


function get_i_owe_you_requess_for_approval($from_date, $to_date)
{  
    $user_id = $_SESSION['wa_current_user']->user;
    
    $sql= "SELECT 
            ou.*,
            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."approvers apr ON 
                        ou.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  ou.batch_complete = 0 AND 
                (ou.approval_status = ".AS_AWAITING_APPROVAL." OR ou.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 .=" ORDER BY ou.id, tran_date";
    
	return db_query($sql,"Could not get_i_owe_you_requess_for_approval");	
}

function update_reopned_i_owe_you($iou_id, $row_state)        
{
        $sql = "UPDATE ".TB_PREF."i_owe_you 
                SET                     
                    next_approval_group_id=".db_escape(0).",
                    batch_no=".db_escape(0).",     
                    row_state=". db_new_row_state($row_state)."  
                WHERE id=".db_escape($iou_id)." AND row_state=".db_escape($row_state);

        db_query($sql,"Could update_reopned_i_owe_you");
        
        return db_num_affected_rows();


}

function get_batch_i_owe_yous_total_task_allocation($batch_no, $task_id)
{  
	$sql= "SELECT oud.iou_id, oud.task_id, SUM(oud.amount) task_total_amount  
            FROM 0_i_owe_you ou 
                JOIN 0_i_owe_you_details oud 
                    ON ou.id = oud.iou_id 
            WHERE ou.batch_no = ".db_escape($batch_no)." AND oud.task_id = ".db_escape($task_id)." 
            GROUP BY oud.task_id ";
        
        $result = db_query($sql, "get_batch_i_owe_yous_total_task_allocation");
        
	return db_result_value($result, "task_total_amount");	
}


function update_i_owe_you_payments_total($iou_id, $payments_total, $row_state)
{
    $sql = "UPDATE ".TB_PREF."i_owe_you 
            SET payments_total=".db_escape($payments_total).", 
                row_state =".db_escape(db_new_row_state($row_state))." 
            WHERE id=".db_escape($iou_id)." 
                AND amount >= advance_amount + bank_payments + ".db_escape($payments_total)." 
                AND row_state=".db_escape($row_state);

    db_query($sql,"update_i_owe_you_payments_total");

    return db_num_affected_rows();
        

  
}



function update_cash_transaction_float_reimbursement_amount($type, $trans_no, $reimbursed_amount,$row_state)
{
    $sql = "UPDATE ".TB_PREF."petty_cash_float_trans  
            SET reimbursed_amount = $reimbursed_amount ,
                 row_state = ".db_new_row_state($row_state)."  
            WHERE ABS(amount) >= ".db_escape($reimbursed_amount)." AND 
                    tran_type =".db_escape($type)." AND 
                    type_no =".db_escape($trans_no)." AND 
                    row_state =".db_escape($row_state);
    
    db_query($sql, "update_cash_transaction_float_reimbursement_amount");  
    return db_num_affected_rows();    
    
}

function get_petty_cash_float_trans_record($float_id, $tran_type, $type_no)
{  
    
    $sql= "SELECT *                 
            FROM " .TB_PREF."petty_cash_float_trans pt
            WHERE pt.float_id = ". db_escape($float_id)." 
                AND pt.tran_type = ". db_escape($tran_type)." 
                AND pt.type_no = ". db_escape($type_no);
        
    $result = db_query($sql, "get_petty_cash_float_trans_record");  
    return db_fetch($result);    
}


function write_float_trans($trans_type, $trans_no, $person_id, $float_amount, $date_)
{
    if(!($trans_no > 0)){
        $trans_no = get_next_trans_no($trans_type);
    }
    
    add_to_petty_cash_float($trans_type, $trans_no, $person_id, $float_amount, $date_);
    
    return $trans_no;
}

function update_i_owe_you_bank_payments_total($iou_id, $iou_bank_total, $row_state)
{
    $sql = "UPDATE ".TB_PREF."i_owe_you 
            SET bank_payments=".db_escape($iou_bank_total).", 
                row_state =".db_escape(db_new_row_state($row_state))." 
            WHERE id=".db_escape($iou_id)." 
                AND amount >= advance_amount + payments_total + ".db_escape($iou_bank_total)." 
                AND row_state=".db_escape($row_state);

    db_query($sql,"update_i_owe_you_bank_payments_total");

    return db_num_affected_rows();
        

  
}