<?php

function get_sql_for_bond_inquiry($bond_id =null,$bond_name =null,
                                    $from = null, $to = null, 
                                    $project_id= null, $bank_id = null)
{
        
        $sql = "SELECT b.id,                         
                        b.name, 
                        b.description,
                        pm.description project_description,
                        tn.value_name bond_type_name,                        
                        b.value,
                        b.start_date,
                        b.expiry_date,
                        b.payable,
                        sup.supp_name supplier_name,
                        dm.name customer_name,                        
                        cf.name credit_facility_name,
                        bnk.value_name bank_name,
                        b.branch_id,
                        b.bank_account_id,
                        b.credit_facility_id,
                        b.expired,
                        b.original_bond_id 
                        
                    FROM ".TB_PREF."bonds b 
                    LEFT JOIN ".TB_PREF."list_values tn ON
                            b.type_id = tn.id 
                    LEFT JOIN ".TB_PREF."debtors_master dm ON 
                            b.customer_id = dm.debtor_no 
                    LEFT JOIN ".TB_PREF."suppliers sup ON
                            b.supplier_id = sup.supplier_id 
                    LEFT JOIN ".TB_PREF."credit_facilities cf ON
                            b.credit_facility_id = cf.id 
                    LEFT JOIN ".TB_PREF."list_values bnk ON
                            b.bank_id = bnk.id 
                    LEFT JOIN ".TB_PREF."bank_branches br ON
                            b.branch_id = br.id 
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                            b.project_id = pm.project_id  
                    WHERE  1 ";
      
         if($from != null)
        {
            $sql .= " AND (b.start_date >= ".db_escape(date2sql($from))." OR b.expiry_date <= ".db_escape(date2sql($from)).")"; 
        }
        
        if($to != null)
        {
            $sql .= " AND (b.start_date <= ".db_escape(date2sql($to))." OR b.expiry_date >= ".db_escape(date2sql($to)).")"; 
        }
        
        if($bond_id > 0)
        {
            $sql .= " AND b.id = ".db_escape($bond_id);            
        }
        
        
        if(strlen($bond_name) > 0)
        {
            $sql .= " AND b.name LIKE ".db_escape('%'.$bond_name.'%');            
        }
        if($project_id > 0)
        {
            $sql .= " AND b.project_id = ".db_escape($project_id);            
        }
        if($bank_id > 0)
        {
            $sql .= " AND b.bank_id = ".db_escape($bank_id);           
        }        

        $sql .= " ORDER BY b.id ";   
	
        return $sql;
}

?>
