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


function get_sql_for_petty_cash_adjustment_inquiry($from_date, $to_date, $type_id, $trans_no, $float_id, $to_float_id=0)
{  
    $sql= "SELECT a.id, 
                a.type, 
                a.trans_no, 
                a.reference, 
                a.float_id, 
                a.is_return, 
                a.tran_date, 
                a.amount, 
                a.comment,
                a.approval_status,
                f.name float_name, 
                f2.name to_float_name 
        FROM ".TB_PREF."petty_cash_float_adjustments a  
            INNER JOIN ".TB_PREF."petty_cash_floats f 
                ON f.id = a.float_id                      
            LEFT JOIN ".TB_PREF."petty_cash_floats f2  
                ON f2.id = a.to_float_id                      
        WHERE 1=1 "; 
        
        if ($trans_no > 0){
                $sql .= " AND a.trans_no = ".  db_escape($trans_no); 
        }
        else{
        
            if ($type_id > 0)
            {
                $sql .= " AND a.type = ".db_escape($type_id);
            }

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

            if ($to_float_id > 0) {
                $sql .= " AND a.to_float_id = ".db_escape($to_float_id);
            }

            $sql .= " AND a.tran_date >=".db_escape(date2sql($from_date))."AND a.tran_date <=".db_escape(date2sql($to_date));
        }
        
	$sql .=" ORDER BY tran_date DESC, a.type, a.trans_no DESC";
        
	return $sql;
}





function get_float_transactions($from_date, $to_date, $type_id, $trans_no, $float_id)
{  
	$sql= "SELECT a.id, 
                        a.type, 
                        a.trans_no, 
                        a.reference, 
                        a.float_id, 
                        a.is_return, 
                        a.tran_date, 
                        a.amount, 
                        a.comment,
                        a.approval_status,
                        f.name float_name, 
                        tf.name to_float_name 
            FROM ".TB_PREF."petty_cash_float_adjustments a  
                    INNER JOIN ".TB_PREF."petty_cash_floats f 
                        ON f.id = a.float_id                      
                    LEFT JOIN ".TB_PREF."petty_cash_floats tf 
                        ON tf.id = a.to_float_id                      
            WHERE 1=1 "; 
        if ($trans_no > 0){
                $sql .= " AND a.trans_no = ".  db_escape($trans_no); 
        }
        else{
        
            if ($type_id > 0)
            {
                $sql .= " AND a.type = ".db_escape($type_id);
            }

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

            $sql .= " AND a.tran_date >=".db_escape(date2sql($from_date))."AND a.tran_date <=".db_escape(date2sql($to_date));
        }
        
	$sql .=" ORDER BY tran_date DESC, a.type, a.trans_no DESC, a.id DESC";
        
	return db_query($sql, "get_float_transactions");
}




function get_petty_cash_float_transactions($from_date,$to_date, $type_id, $trans_no, $float_id, $to_float_id=0)
{  
    
    $sql1 = get_sql_for_petty_cash_float_transactions_adjustment($from_date, $to_date, $type_id, $trans_no, $float_id, $to_float_id);
    
    $sql2 = get_sql_for_petty_cash_float_transactions_pos_customer_payments($from_date, $to_date, $type_id, $trans_no, $float_id, $to_float_id);
    
	
    $sql =$sql1." UNION ".$sql2." ORDER BY tran_date DESC, trans_type, trans_no DESC";
    
//    $sql = $sql2;

    return db_query($sql, 'get_petty_cash_float_inquiry');
}


function get_sql_for_petty_cash_float_transactions_adjustment($from_date, $to_date, $type_id, $trans_no, $float_id, $to_float_id=0)
{  
    $sql= "SELECT a.id, 
                a.type trans_type, 
                a.trans_no, 
                a.reference, 
                a.float_id, 
                a.is_return, 
                a.tran_date, 
                a.amount, 
                a.comment,
                a.approval_status,
                f.name float_name, 
                f2.name to_float_name 
        FROM ".TB_PREF."petty_cash_float_adjustments a  
            INNER JOIN ".TB_PREF."petty_cash_floats f 
                ON f.id = a.float_id                      
            LEFT JOIN ".TB_PREF."petty_cash_floats f2  
                ON f2.id = a.to_float_id                      
        WHERE 1=1 "; 
        
        if ($trans_no > 0){
                $sql .= " AND a.trans_no = ".  db_escape($trans_no); 
        }
        else{
        
            if ($type_id > 0)
            {
                $sql .= " AND a.type = ".db_escape($type_id);
            }

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

            if ($to_float_id > 0) {
                $sql .= " AND a.to_float_id = ".db_escape($to_float_id);
            }

            $sql .= " AND a.tran_date >=".db_escape(date2sql($from_date))."AND a.tran_date <=".db_escape(date2sql($to_date));
        }
        
        
	return $sql;
}




function get_sql_for_petty_cash_float_transactions_pos_customer_payments($from_date, $to_date, $type_id, $trans_no, $float_id, $to_float_id=0) {
    $sql = "SELECT sa.id,
                sa.type trans_type,
                sa.trans_no,
                sa.reference,
                sa.float_id,
                0 is_return,
                sa.tran_date,
                sa.amount,
                sa.comments comment,
                sa.approval_status, 
                ft.name float_name,   
                '' to_float_name
            FROM ".TB_PREF."pos_customer_payments sa  
                JOIN ".TB_PREF."petty_cash_floats ft  
                    ON sa.float_id = ft.id  
            WHERE 1 = 1 ";
    
        if ($trans_no > 0) { 
                $sql .= " AND sa.trans_no = ".db_escape($trans_no); 
        } else {

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

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

            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 
            
        }
    
    return $sql;
}
