<?php

function get_sql_for_lc_inquiry($lc_id =null,$lc_reference =null,
                                    $from = null, $to = null, 
                                    $bank_id = null, $supplier_id = null)
{

        $sql = "SELECT lc.id,
                    lc.name,                    
                    lc.description,
                    supp.supp_name,
                    cf.name  credit_facility_name,
                    lv.value_name bank_name,
                    ba.bank_account_number,
                    lc.* 
        
                FROM ".TB_PREF."letters_of_credit lc  
                        LEFT JOIN ".TB_PREF."suppliers supp ON 
                                lc.supplier_id = supp.supplier_id  
                        LEFT JOIN ".TB_PREF."credit_facilities cf ON 
                                lc.credit_facility_id = cf.id 
                        LEFT JOIN ".TB_PREF."list_values lv ON
                                lv.id = lc.bank_id 
                        LEFT JOIN ".TB_PREF."bank_accounts ba ON
                                lc.bank_account_id = ba.id 
                WHERE 1=1 ";
        
        if($from != null)
        {
            $sql .= " AND lc.start_date >= ".db_escape(date2sql($from)); 
        }
        
        if($to != null)
        {
            $sql .= " AND lc.start_date <= ".db_escape(date2sql($to)); 
        }
        
        if($lc_id > 0)
        {
            $sql .= " AND lc.id = ".db_escape($lc_id);            
        }
        
        
        if(strlen($lc_reference) > 0)
        {
            $sql .= " AND lc.name LIKE ".db_escape('%'.$lc_reference.'%');            
        }

        if($bank_id > 0)
        {
            $sql .= " AND lc.bank_id = ".db_escape($bank_id);           
        }        

        if($supplier_id > 0)
        {
            $sql .= " AND lc.supplier_id = ".db_escape($supplier_id);           
        } 
        
        $sql .= " ORDER BY lc.id ";   
	
        return $sql;
}


function view_letters_of_credit($lc_id)
{
    $sql = "SELECT lc.*,
                    s.supp_name,
                    cf.name credit_facility_name,
                    ba.bank_account_name,
                    lv.value_name payable_type_name,
                    li.value_name delivery_type_name           

            FROM ".TB_PREF."letters_of_credit lc
                LEFT JOIN ".TB_PREF."suppliers s ON
                    s.supplier_id = lc.supplier_id
                LEFT JOIN ".TB_PREF."credit_facilities cf ON
                    lc.credit_facility_id = cf.id 
                LEFT JOIN ".TB_PREF."bank_accounts ba ON
                    lc.bank_account_id = ba.id
                LEFT JOIN ".TB_PREF."list_values lv ON
                    lc.payable_type_id = lv.id
                LEFT JOIN ".TB_PREF."list_values li ON
                     lc.delivery_type_id = li.id
                    
            WHERE 
                lc.id=".db_escape($lc_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}

?>
