01. Receipt (Pelunasan Tunai)

Berikut adalah kode receipt untuk pembayaran tunai : 

/**
* Digunakan di proses receipt - tunai
*
* @param String $ckode_customer
* @param Date $the_date no sql date, dd-mm-yyyy
*/
public static function get_piutang_customer_tunai($ckode_depot=array(),$the_date="") {
    print_r($ckode_depot); exit ;
$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
$query = "SELECT cnofaktur, MIN(dtanggal) dtanggal, CAST(SUM(nsales+ndebit) AS DECIMAL(12,2)) ndebit, CAST(SUM(nreceipt+ncredit) AS DECIMAL(12,2)) ncredit, CAST(sum(nsales+ndebit-nreceipt-ncredit) AS DECIMAL(12,2)) nbalance
      FROM (
            SELECT 'BG' ctrans, cnofaktur, '' cno_receipt, MAX(dtanggal) dtanggal, ckode_customer, 0 nsales, namount ndebit, 0 nreceipt, 0 ncredit
            FROM tbl_cust_saldo 
            WHERE SUBSTR(ckode_customer,1,3) IN(".utilities::pdo_param_repeat(":ckode_depot",count($ckode_depot)).") 
            GROUP BY cnofaktur, ckode_customer, namount
                UNION
            SELECT 'SL' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, sh.ckode_customer, IFNULL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0)    
            nsales, 0 ndebit, 0 nreceipt, 0 ncredit    
            FROM tbl_sales_h sh,
              (SELECT sh.cnofaktur, IFNULL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit 
               FROM tbl_sales_h sh,tbl_sales_d sd
               WHERE sh.cnofaktur=sd.cnofaktur AND SUBSTR(sh.ckode_customer,1,3) IN(".utilities::pdo_param_repeat(":ckode_depot",count($ckode_depot)).")
               GROUP BY sh.cnofaktur) sd
            WHERE sh.cnofaktur=sd.cnofaktur AND SUBSTR(sh.ckode_customer,1,3) IN(".utilities::pdo_param_repeat(":ckode_depot",count($ckode_depot)).") 
            GROUP BY sh.cnofaktur, sh.dtanggal, sh.ckode_customer
                UNION
            SELECT 'RC' ctrans, rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from ckode_customer, 0 nsales, 0 ndebit, IFNULL(SUM(rd.ncredit- 
            rd.ndebit),0) nreceipt, 0 ncredit 
            FROM tbl_cash_h rh,tbl_cash_d rd
            WHERE rh.cno_receipt=rd.cno_receipt AND rh.cfrom='C' AND SUBSTR(rh.ckode_from,1,3) IN(".utilities::pdo_param_repeat(":ckode_depot",count($ckode_depot)).") 
            GROUP BY rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from
                UNION
            SELECT 'AR' ctrans, ah.cnofaktur, '' cno_receipt, ah.dtanggal ,ah.ckode_customer, 0 nsales, sum(IF(ah.ctype='D',ad.ndebit,IF 
            (ah.ctype='C',ad.ncredit*-1,0))) ndebit, 0 nreceipt, 0 ncredit
             FROM tbl_ar_h ah, tbl_ar_d ad
            WHERE ah.cno_ar=ad.cno_ar AND SUBSTR(ah.ckode_customer,1,3) IN(".utilities::pdo_param_repeat(":ckode_depot",count($ckode_depot)).")
         GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_customer
      ) as inner_query
      GROUP BY inner_query.cnofaktur
      HAVING (MIN(inner_query.dtanggal)<=:dtanggal AND
              SUM(inner_query.nsales+inner_query.ndebit-inner_query.nreceipt-inner_query.ncredit)>0)
      ORDER BY inner_query.dtanggal" ;

$stmt = $pdo->prepare($query);

$j = 0 ;
foreach($ckode_depot as $key=>$value) {
$params["ckode_depot".$j++] = $value ;
}
$j = 0 ;
foreach($ckode_depot as $key=>$value) {
$params["ckode_depot".$j++] = $value ;
}
$j = 0 ;
foreach($ckode_depot as $key=>$value) {
$params["ckode_depot".$j++] = $value ;
}
$j = 0 ;
foreach($ckode_depot as $key=>$value) {
$params["ckode_depot".$j++] = $value ;
}
$j = 0 ;
foreach($ckode_depot as $key=>$value) {
$params["ckode_depot".$j++] = $value ;
}

$params["dtanggal"]  =  date::date2sql($the_date) ; 

$stmt->execute($params);

$result = $stmt->fetchAll();

return $result ;
}


Bagian query pertama, yaitu tbl_cust_saldo, yaitu query berikut :

            SELECT 'BG' ctrans, cnofaktur, '' cno_receipt, MAX(dtanggal) dtanggal, ckode_customer, 0 nsales, namount ndebit, 0 nreceipt, 0 ncredit
            FROM tbl_cust_saldo 
            WHERE SUBSTR(ckode_customer,1,3) IN(".utilities::pdo_param_repeat(":ckode_depot",count($ckode_depot)).") 
            GROUP BY cnofaktur, ckode_customer, namount

Untuk sementara dihapus, krn tbl_cust_saldo belum digunakan dan sepertinya banyak data 'sampah' dari mulai dari periode 2001 s/d 2005.

---Update : Query diatas tetap dimasukkan, tetapi harus dipejelas apa fungsion tbl_cust_saldo ini.


Comments