<?php
/**********************************************************************
***********************************************************************/
function get_supplier_opening_balance($supplier_id, $to)
{
	$to = date2sql($to);

    $sql = "SELECT SUM(IF(".TB_PREF."supp_trans.type IN ( ".ST_SUPPINVOICE.",".ST_BANKDEPOSIT.",".ST_SUBCONTRACTOR_INVOICE.",".ST_UTILITY_BILLS.",".ST_SUPPLIER_TRANSPORT_INVOICES."),
        (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS charges,
    	SUM(IF(".TB_PREF."supp_trans.type IN( ".ST_BANKPAYMENT.",".ST_SUPPAYMENT.",".ST_SUPPCREDIT.",".ST_SUBCONTRACTOR_CREDIT_NOTE.",".ST_UTILITY_BILL_CREDIT_NOTE."),
    	(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS credits,
		SUM(".TB_PREF."supp_trans.alloc) AS Allocated,
		SUM(IF(".TB_PREF."supp_trans.type IN ( ".ST_SUPPINVOICE.",".ST_BANKDEPOSIT.",".ST_SUBCONTRACTOR_INVOICE.",".ST_UTILITY_BILLS.",".ST_SUPPLIER_TRANSPORT_INVOICES."),
		(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc),
		(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))) AS OutStanding
		FROM ".TB_PREF."supp_trans
    	WHERE ".TB_PREF."supp_trans.tran_date < '$to'
		AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' GROUP BY supplier_id";

    $result = db_query($sql,"No transactions were returned");
    return db_fetch($result);
}


function get_supplier_transactions($supplier_id, $from_, $to_) {
    $from = date2sql($from_);
    $to = date2sql($to_);

        $sql_1 = "SELECT st.type, st.trans_no, st.reference, st.tran_date, st.due_date, 
                    (st.ov_amount + st.ov_gst + st.ov_discount) TotalAmount, st.alloc Allocated, 
                    ((st.type = ".ST_SUPPINVOICE.") AND st.due_date < '$to') OverDue
                FROM ".TB_PREF."supp_trans st 
                WHERE st.tran_date >= ".db_escape($from)." AND st.tran_date <= ".db_escape($to)."  
                    AND st.supplier_id = ".db_escape($supplier_id)." AND st.ov_amount != 0";

        $sql_2 = "SELECT ".db_escape(ST_PURCHORDER)." type, po.order_no trans_no, po.reference, po.ord_date tran_date, '' due_date, 
                    (po.total - po.staxes) TotalAmount, po.alloc Allocated, '' OverDue
                FROM ".TB_PREF."purch_orders po 
                WHERE po.ord_date >= ".db_escape($from)." AND po.ord_date <= ".db_escape($to)."  
                    AND po.supplier_id = ".db_escape($supplier_id)." 
                    AND po.alloc > 0 ";
    
        $sql = "($sql_1) UNION ($sql_2)";
        
        $sql .= " ORDER BY tran_date, type, trans_no";
    
        return db_query($sql, "get_supplier_transactions");
}


function get_latest_cheque_print_details($type,$trans_no)
{
            $sql = "SELECT cpg.*,
                        ba.bank_account_number,
                         bn.value_name bank_name,
                        crsn.value_name crossing_name   
                    FROM ".TB_PREF."cheque_printing_log cpg
                    LEFT JOIN ".TB_PREF."bank_accounts ba ON 
                            cpg.bank_account = ba.id
                    INNER JOIN ".TB_PREF."list_values bn ON 
                            ba.bank_id = bn.id         
                    LEFT JOIN ".TB_PREF."list_values crsn ON 
                            cpg.crossing_id = crsn.id 
                    WHERE cpg.trans_no =".db_escape($trans_no)." AND  cpg.type = ".db_escape($type) ;
            
            
            $sql .= " ORDER BY cpg.id DESC LIMIT 1";
             
     $result = db_query($sql,"Could not cheque_printing details");        
     return db_fetch($result);
}


function get_supplier_transaction_allocation_to_within_period($tran_type, $tran_no, $from_date_, $to_date_) {

    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);

        if ($tran_type == ST_PURCHORDER) {
            $sql = "SELECT SUM(sa.amt) allocated 
                    FROM ".TB_PREF."supp_allocations sa 
                        JOIN ".TB_PREF."purch_orders po  
                            ON sa.trans_no_to = po.order_no  
                    WHERE sa.trans_type_to = ".db_escape($tran_type)." 
                        AND sa.trans_no_to = ".db_escape($tran_no)." 
                        AND po.ord_date >= ".db_escape($from_date)." 
                        AND po.ord_date <= ".db_escape($to_date);
            
            $sql = "SELECT SUM(sa.amt) allocated 
                    FROM ".TB_PREF."purch_orders po  
                        JOIN ".TB_PREF."supp_allocations sa 
                            ON sa.trans_no_to = po.order_no  
                        JOIN ".TB_PREF."supp_trans st2  
                            ON sa.trans_type_from = st2.type 
                                AND sa.trans_no_from = st2.trans_no 
                    WHERE sa.trans_type_to = ".db_escape($tran_type)." 
                        AND po.order_no = ".db_escape($tran_no)." 
                        AND st2.tran_date >= ".db_escape($from_date)." 
                        AND st2.tran_date <= ".db_escape($to_date);

//        } elseif ($tran_type == ST_UTILITY_BILLS) {
//            $sql = "SELECT SUM(sa.amt) allocated 
//                    FROM ".TB_PREF."supp_allocations sa 
//                        JOIN ".TB_PREF."supp_trans st 
//                            ON sa.trans_type_to = st.type 
//                                AND sa.trans_no_to = st.trans_no  
//                    WHERE sa.trans_type_to = ".db_escape($tran_type)." 
//                        AND sa.trans_no_to = ".db_escape($tran_no)." 
//                        AND st.tran_date >= ".db_escape($from_date)." 
//                        AND st.tran_date <= ".db_escape($to_date);
//            
        } else {
            
            $sql = "SELECT SUM(sa.amt) allocated 
                    FROM ".TB_PREF."supp_allocations sa 
                        JOIN ".TB_PREF."supp_trans st 
                            ON sa.trans_type_to = st.type 
                                AND sa.trans_no_to = st.trans_no  
                    WHERE sa.trans_type_to = ".db_escape($tran_type)." 
                        AND sa.trans_no_to = ".db_escape($tran_no)." 
                        AND st.tran_date >= ".db_escape($from_date)." 
                        AND st.tran_date <= ".db_escape($to_date);
            
            $sql = "SELECT SUM(sa.amt) allocated 
                    FROM ".TB_PREF."supp_trans st 
                        JOIN ".TB_PREF."supp_allocations sa 
                            ON st.type = sa.trans_type_to   
                                AND st.trans_no = sa.trans_no_to  
                        JOIN ".TB_PREF."supp_trans st2  
                            ON sa.trans_type_from = st2.type 
                                AND sa.trans_no_from = st2.trans_no  
                    WHERE st.type = ".db_escape($tran_type)." 
                        AND st.trans_no = ".db_escape($tran_no)." 
                        AND st2.tran_date >= ".db_escape($from_date)." 
                        AND st2.tran_date <= ".db_escape($to_date);
        }
                    

    $result = db_query($sql,"get_supplier_transaction_allocation_to_within_period");
    return db_result_value($result, 'allocated');
}

function get_supplier_transaction_allocation_from_within_period($tran_type, $tran_no, $from_date_, $to_date_) {

    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
    
        $sql = "SELECT SUM(sa.amt) allocated 
                FROM ".TB_PREF."supp_allocations sa 
                    JOIN ".TB_PREF."supp_trans st 
                        ON sa.trans_type_from = st.type 
                            AND sa.trans_no_from = st.trans_no  
                WHERE sa.trans_type_from = ".db_escape($tran_type)." 
                    AND sa.trans_no_from = ".db_escape($tran_no)." 
                    AND st.tran_date >= ".db_escape($from_date)." 
                    AND st.tran_date <= ".db_escape($to_date);
    
        $sql_1 = "SELECT SUM(sa.amt) allocated 
                FROM ".TB_PREF."supp_trans st 
                    JOIN ".TB_PREF."supp_allocations sa 
                        ON st.type = sa.trans_type_from  
                            AND st.trans_no = sa.trans_no_from 
                    JOIN ".TB_PREF."supp_trans st2  
                        ON sa.trans_type_to = st2.type 
                            AND sa.trans_no_to = st2.trans_no  
                WHERE st.type = ".db_escape($tran_type)." 
                    AND st.trans_no = ".db_escape($tran_no)." 
                    AND st2.tran_date >= ".db_escape($from_date)." 
                    AND st2.tran_date <= ".db_escape($to_date);
        
        $sql_2 = "SELECT SUM(sa.amt) allocated 
                FROM ".TB_PREF."supp_trans st 
                    JOIN ".TB_PREF."supp_allocations sa 
                        ON st.type = sa.trans_type_from  
                            AND st.trans_no = sa.trans_no_from 
                    JOIN ".TB_PREF."purch_orders po  
                        ON sa.trans_no_to = po.order_no  
                            AND sa.trans_type_to = ".db_escape(ST_PURCHORDER)." 
                WHERE st.type = ".db_escape($tran_type)." 
                    AND st.trans_no = ".db_escape($tran_no)." 
                    AND po.ord_date >= ".db_escape($from_date)." 
                    AND po.ord_date <= ".db_escape($to_date);

        $sql = "SELECT SUM(allocated) allocated FROM (($sql_1) UNION ($sql_2)) tmp";
        
    $result = db_query($sql,"get_supplier_transaction_allocation_from_within_period");
    return db_result_value($result, 'allocated');
}