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


function get_sql_for_petty_cash_inquiry($from_date, $to_date, $type_id, $project_id, $float_id, $company_code, $employee_code, $iou_id, $batch_no)
{  
	$sql= "SELECT 
                ph.id,
                ph.batch_no,
                ph.company_code,
                pm.description project_description,                
                pf.name float_name,
                pctn.value_name type_name,
                ph.amount,
                ph.tran_date,
                ph.comments,    
                ph.batch_complete,
                ph.employee_code,
                b.approval_status batch_approval_status,
                b.complete 
            FROM ".TB_PREF."petty_cash_header ph  
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            ph.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON    
                            ph.float_id = pf.id 
                    INNER JOIN ".TB_PREF."list_values pctn ON 
                            ph.type = pctn.id AND pctn.list_code = 'PETTY_CASH_TYPES' 
                    LEFT JOIN ".TB_PREF."i_owe_you_batches b ON 
                            ph.batch_no = b.batch_no 
                    LEFT JOIN group_companies gc ON 
                            ph.company_code = gc.company_code 
            WHERE 1=1 ";
  
        if ($iou_id > 0 || $batch_no > 0){
            if ($iou_id > 0)
            {
                $sql .= " AND ph.id = ".  db_escape($iou_id);
            }
            if ($batch_no > 0)
            {
                $sql .= " AND b.batch_no = ".  db_escape($batch_no);
            }
        }
        else{ 
            if ($employee_code != "")
            {
                $sql .= " AND ph.employee_code = ".db_escape($employee_code);
            }

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

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

            if ($float_id > 0)
            {
                    $sql .= " AND ph.float_id = ".  db_escape($float_id);
            }

            if ($company_code != '')
            {
                    $sql .= " AND ph.company_code = ".  db_escape($company_code);
            }
        
            $sql .= " AND ph.tran_date >=".db_escape(date2sql($from_date))."AND ph.tran_date <=".db_escape(date2sql($to_date));
        }
        
	$sql .=" ORDER BY ph.id, ph.tran_date";
        
	return $sql;
}

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

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

function get_petty_cash_float_before_totals($float_id, $from_date_, $to_date_) {  
    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
    
    $sql = "SELECT SUM(amount) total                  
            FROM ".TB_PREF."petty_cash_float_trans 
            WHERE float_id = ".db_escape($float_id)." 
                AND tran_date > ".db_escape($from_date)." 
                AND tran_date < ".db_escape($to_date);
        
        $result = db_query($sql, "get_petty_cash_float_before_totals");  
        return db_result_value($result, 'total');    
}

function get_petty_cash_float_inquiry($from_date,$to_date, $filter_type, $filering_trans_no, $float_id)
{  
	$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 db_query($sql, 'get_petty_cash_float_inquiry');
}


