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


function get_sql_for_i_owe_you_advances_inquiry($from_date_, $to_date_, $batch_no, 
                                                $project_id, $float_id, $company_code, 
                                                $employee_code, $iou_id)
{
    $sql = "SELECT ioud.iou_id,
                iou.reference,
                iou.project_id,
                iou.tran_date iou_date,
                iou.company_code,
                iou.employee_code,
                iou.amount iou_amount,
                ioud.float_id,
                ioud.amount advance_amount,
                iouh.tran_date advance_batch_date,
                iouh.batch_no,
                pm.description project_description,
                pf.name float_name,
                gc.company_name 
            FROM ".TB_PREF."i_owe_you_advances_details ioud 
                JOIN ".TB_PREF."i_owe_you_advances iouh ON 
                    ioud.batch_no = iouh.batch_no 
                JOIN ".TB_PREF."i_owe_you iou ON 
                    ioud.iou_id = iou.id 
                JOIN ".TB_PREF."projects_master pm ON 
                    pm.project_id = iou.project_id 
                JOIN ".TB_PREF."petty_cash_floats pf ON    
                    ioud.float_id = pf.id 
                JOIN group_companies gc ON    
                    gc.company_code = iou.company_code 
            WHERE 1=1 ";
   
    if ($iou_id > 0 || $batch_no > 0){
        if($batch_no > 0){
            $sql .= " AND iouh.batch_no = ".db_escape($batch_no);
        }
        if ($iou_id > 0)
	{
		$sql .= " AND iou.id = ".  db_escape($iou_id);
	}
    }
    else{
        if ($project_id > 0)
	{
		$sql .= " AND iou.project_id = ".  db_escape($project_id);
	}
    
        if ($float_id > 0)
	{
		$sql .= " AND ioud.float_id = ".  db_escape($float_id);
	}
    
    
        
        if ($company_code != '')
	{
		$sql .= " AND iou.company_code = ".  db_escape($company_code);
	}
        
        if ($employee_code != '')
	{
		$sql .= " AND iou.employee_code = ".  db_escape($employee_code);
	}
    
        $sql .= " AND iouh.tran_date >= ".db_escape(date2sql($from_date_))." AND iouh.tran_date <= ".db_escape(date2sql($to_date_));
    }
    
    return $sql;
    
}
        
function view_i_owe_you_advances_details($batch_no)
{  
//    $sql= "SELECT 
//            ou.*,
//            st.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 st ON 
//                        ou.type = st.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.advances_batch_no = ".db_escape($batch_no)." 
//        ORDER BY fl.name, ou.id, tran_date";
        
        $sql= "SELECT 
            ou.*,
            ad.batch_no advances_batche_no,
            ad.amount batch_advance_amount,
            ad.float_reimbursement_id,
            ad.reimbursed_amount,
            ad.id iou_advance_detail_id,
            st.value_name type_name,
            pm.description project_description,
            fl.name float_name 
        FROM ".TB_PREF."i_owe_you ou 
                INNER JOIN ".TB_PREF."i_owe_you_advances_details ad ON 
                        ou.id = ad.iou_id 
                INNER JOIN ".TB_PREF."list_values st ON 
                        ou.type = st.id 
                INNER JOIN ".TB_PREF."projects_master pm ON 
                        ou.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."petty_cash_floats fl ON 
                        ad.float_id = fl.id 
        WHERE ad.batch_no = ".db_escape($batch_no)." 
        ORDER BY fl.name, ou.id, tran_date";
        
	return db_query($sql,"Could not view_i_owe_you_advances_details");	

}


function view_i_owe_you_advances($iou_id)
{
    $sql_sum = "SELECT ad.*,
                        ah.tran_date,
                        fl.name float_name 
                FROM ".TB_PREF."i_owe_you_advances_details ad 
                    JOIN ".TB_PREF."i_owe_you_advances ah  
                        ON ah.batch_no = ad.batch_no 
                    JOIN ".TB_PREF."petty_cash_floats fl 
                        ON ad.float_id = fl.id 
                WHERE ad.iou_id=".db_escape($iou_id);
    
    return db_query($sql_sum, "Could not get_i_owe_you_advances");    

}

function view_i_owe_you_advances_batch_header($batch_no)
{
    $sql_sum = "SELECT * 
                FROM ".TB_PREF."i_owe_you_advances   
                WHERE batch_no=".db_escape($batch_no);
    
    $result =  db_query($sql_sum, "view_i_owe_you_advances_batch_header"); 
    
    return db_fetch($result);
}

?>