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

function get_sql_for_i_owe_you_batch_expenses_inquiry($batch_no, $project_id, $float_id, $from_date, $to_date)
{
    $sql = "SELECT iob.*,
                    pm.description project_description,
                    pcf.name float_name

            FROM ".TB_PREF."i_owe_you_batches iob
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    iob.project_id = pm.project_id
                    
                LEFT JOIN ".TB_PREF."petty_cash_floats pcf ON
                    iob.float_id = pcf.id

            WHERE 1 = 1 ";
    
    if($batch_no > 0){
        $sql .= " AND iob.batch_no=".db_escape($batch_no);
    }
    else{
        if($project_id > 0){
            $sql .= " AND iob.project_id=".db_escape($project_id);
        }

        if($float_id > 0){
            $sql .= " AND iob.float_id=".db_escape($float_id);
        }
        
        $sql .= " AND iob.tran_date >=".db_escape(date2sql($from_date))." AND iob.tran_date <=".db_escape(date2sql($to_date));
    }
    
    return $sql;          
  
}
        
   
function get_i_owe_you_expense_details_inquiry($iou_id, $petty_expn_id, $batch_no, $project_id, $description, $company_code, $employee_code, $from_date, 
                                               $to_date, $expense_category_id, $expense_item_id, $expense_type_id) {
    $trans_type_iou = ST_I_OWE_YOU;
    $trans_type_pet = ST_PETTY_CASH;
    
    $sql = "(SELECT ex.id, ex.iou_batch_no, ex.expense_category_id, ex.expense_item_id, ex.expence_date, 
                ex.bill_no, ex.expence_description, ex.approved_amount, 
                pt.description task_description, 
                dsn.value_name document_status_name,   
                etn.value_name expense_type_name,
                pcsr.stock_id, $trans_type_iou type,
                iouh.company_code, iouh.employee_code, pm.description project_name  
             FROM ".TB_PREF."i_owe_you_expenses ex 
                JOIN ".TB_PREF."i_owe_you_batches ioub    
                    ON ex.iou_batch_no = ioub.batch_no   
                JOIN ".TB_PREF."i_owe_you iouh    
                    ON ioub.batch_no = iouh.batch_no   
                JOIN ".TB_PREF."projects_master pm     
                    ON iouh.project_id = pm.project_id    
                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."petty_cash_stock_item_receipt_details pcsr  
                    ON ex.iou_batch_no = pcsr.source_trans_no  
                        AND ex.id = pcsr.source_detail_id  
                        AND pcsr.source_type = ".db_escape(ST_I_OWE_YOU_BATCH)." 
                        AND pcsr.type = ".db_escape(ST_PETTY_CASH_ITEM_RECEIPT)." 
             WHERE ex.expence_date >= ".db_escape($from_date)." 
                AND ex.expence_date <= ".db_escape($to_date);     
    
                if ($iou_id > 0 && $batch_no > 0) {
                    $sql .= " AND (iouh.id = ".db_escape($iou_id)." OR iouh.batch_no = ".db_escape($batch_no).")";   
                } else {
                    if ($iou_id > 0) {
                        $sql .= " AND iouh.id = ".db_escape($iou_id);   
                    }
                    if ($batch_no > 0) {
                        $sql .= " AND iouh.batch_no = ".db_escape($batch_no);   
                    }
                }
                if ($project_id > 0) {
                    $sql .= " AND iouh.project_id = ".db_escape($project_id);   
                }
                if (strlen($description) > 0) {
                    $sql .= " AND ex.expence_description LIKE '%$description%'";   
                }
                if (strlen($company_code) > 0) {
                    $sql .= " AND iouh.company_code = ".db_escape($company_code);     
                }
                if (strlen($employee_code) > 0) {
                    $sql .= " AND iouh.employee_code = ".db_escape($employee_code);     
                }
                if ($expense_category_id > 0) {
                    $sql .= " AND ex.expense_category_id = ".db_escape($expense_category_id);   
                }
                if (($expense_category_id == EC_STOCK_ITEM_RECEIPT) && (strlen($expense_item_id) > 2)) {
                    $sql .= " AND pcsr.stock_id = ".db_escape($expense_item_id);   
                } else {
                    if ($expense_item_id > 0) {
                        $sql .= " AND ex.expense_item_id = ".db_escape($expense_item_id);   
                    }
                }
                if ($expense_type_id > 0) {
                    $sql .= " AND ex.expense_type_id = ".db_escape($expense_type_id);   
                }

                $sql .= ")";
            
	        
    $sql_1 = "(SELECT ex.id, ex.iou_batch_no, ex.expense_category_id, ex.expense_item_id, ex.expence_date, 
                ex.bill_no, ex.expence_description, ex.approved_amount, 
                pt.description task_description, 
                dsn.value_name document_status_name,   
                etn.value_name expense_type_name,
                pcsr.stock_id, $trans_type_pet type, 
                pch.company_code, pch.employee_code, pm.description project_name    
              FROM ".TB_PREF."i_owe_you_expenses ex 
                JOIN ".TB_PREF."petty_cash_header pch    
                    ON ex.iou_batch_no = pch.batch_no   
                JOIN ".TB_PREF."projects_master pm     
                    ON pch.project_id = pm.project_id  
                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."petty_cash_stock_item_receipt_details pcsr  
                    ON ex.iou_batch_no =  pcsr.source_trans_no  
                        AND ex.id =  pcsr.source_detail_id  
                        AND pcsr.source_type = ".db_escape(ST_PETTY_CASH)." 
                        AND pcsr.type = ".db_escape(ST_PETTY_CASH_ITEM_RECEIPT)."
              WHERE ex.expence_date >= ".db_escape($from_date)." 
                AND ex.expence_date <= ".db_escape($to_date);     
    
                if ($petty_expn_id > 0 && $batch_no > 0) {
                    $sql_1 .= " AND (pch.id = ".db_escape($petty_expn_id)." OR pch.batch_no = ".db_escape($batch_no).")";   
                } else {
                    if ($petty_expn_id > 0) {
                        $sql_1 .= " AND pch.id = ".db_escape($petty_expn_id);   
                    }
                    if ($batch_no > 0) {
                        $sql_1 .= " AND pch.batch_no = ".db_escape($batch_no);   
                    }
                }
                if ($project_id > 0) {
                    $sql_1 .= " AND pch.project_id = ".db_escape($project_id);   
                }
                if (strlen($description) > 0) {
                    $sql_1 .= " AND ex.expence_description LIKE '%$description%'";   
                }
                if (strlen($company_code) > 0) {
                    $sql_1 .= " AND pch.company_code = ".db_escape($company_code);     
                }
                if (strlen($employee_code) > 0) {
                    $sql_1 .= " AND pch.employee_code = ".db_escape($employee_code);     
                }
                if ($expense_category_id > 0) {
                    $sql_1 .= " AND ex.expense_category_id = ".db_escape($expense_category_id);   
                }
                if (($expense_category_id == EC_STOCK_ITEM_RECEIPT) && (strlen($expense_item_id) > 2)) {
                    $sql_1 .= " AND pcsr.stock_id = ".db_escape($expense_item_id);   
                } else {
                    if ($expense_item_id > 0) {
                        $sql_1 .= " AND ex.expense_item_id = ".db_escape($expense_item_id);   
                    }
                }
                if ($expense_type_id > 0) {
                    $sql_1 .= " AND ex.expense_type_id = ".db_escape($expense_type_id);   
                } 
                
                $sql_1 .= ")";
    
            $sql_union = "$sql UNION $sql_1";
            
            $sql_union .= " ORDER BY iou_batch_no";
	        
	return db_query($sql_union, "get_i_owe_you_expense_details_inquiry");	
}
 