<?php
        session_cache_expire(1440);
        session_start();
        session_name("insys");
        include "include/config.php";
        include "include/functions.php";
        include "class/UctVw.php";

        $DateStart = '2013-12-01';
        //$DateEnd = '2013-12-31';
        $DateEnd = date("Y-m-d");
        
        $ssql=" SELECT x1.ftitem_code,x1.ftwarehouse_code, x1.ftlocation_code
                    FROM  txstock_adjustment_det_bahan_baku x1, txstock_adjustment_hdr_bahan_baku x3
                    WHERE x3.fnstatus=2 AND x1.ftstock_adjustment_no=x3.ftstock_adjustment_no
                            AND x3.fdtrans_date >= '{$DateStart}' AND x3.fdtrans_date <= '{$DateEnd}'
                    GROUP BY x1.ftitem_code,x1.ftwarehouse_code, x1.ftlocation_code
                    ORDER BY x3.ftstock_adjustment_no ";

	$rs1=mysql_query($ssql);

        pr("sql 1 : {$ssql}");

	while($r1=mysql_fetch_assoc($rs1)) {
            $ssql2=" SELECT x1.fnid, x3.ftstock_adjustment_no, x3.fdtrans_date,x1.ftitem_code,x1.ftwarehouse_code, x1.ftlocation_code,
                        fnqty_available, fnqty_opname, fnqty, fnqty_base  FROM  txstock_adjustment_det_bahan_baku x1, txstock_adjustment_hdr_bahan_baku x3
                        WHERE x3.fnstatus=2 AND x1.ftstock_adjustment_no=x3.ftstock_adjustment_no
                                AND x3.fdtrans_date >= '{$DateStart}' AND x3.fdtrans_date <= '{$DateEnd}'
                                AND x1.ftitem_code='{$r1['ftitem_code']}'
                                and x1.ftwarehouse_code = '{$r1['ftwarehouse_code']}'
                                AND x1.ftlocation_code = '{$r1['ftlocation_code']}'
                        ORDER BY x3.ftstock_adjustment_no DESC LIMIT 1 ";

            $rs2=mysql_query($ssql2);

            // pr("sql 2 : {$ssql2}");
          
            while($r2=mysql_fetch_assoc($rs2)) {
            // $id=RetRstField($ssql);

                $ssql3=" SELECT mm.ftitem_code,mm.ftwarehouse_code,mm.ftlocation_code,
                            SUM(mm.fnmasuk) AS fnmasuk,SUM(mm.fnkeluar) AS fnkeluar, SUM(fnmasuk) - SUM(fnkeluar) AS fnsaldo
                            FROM (

                            SELECT m1.ftcompany_name,'01' AS fttype,'{$r2['fdtrans_date']}' AS fdtrans_date, 'Saldo Awal' AS fttransaction,'' AS fttrans_no,'' AS ftcompany,
                            m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code,SUM(m.fnmasuk) AS fnmasuk,SUM(m.fnkeluar) AS fnkeluar,SUM(m.fnmasuk)-SUM(m.fnkeluar) AS fnsaldo
                            FROM (
                            SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,SUM(fnqty_base) AS fnmasuk,0 AS fnkeluar
                            FROM txinnitial_stock_hdr_bahan_baku a,txinnitial_stock_det_bahan_baku b
                            WHERE a.ftstock_in_no=b.ftstock_in_no
                            AND a.fnstatus<>3 AND a.fnstatus<>9 AND a.ftstock_type_code = 'invbb'
                            GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                            UNION ALL

                            SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,SUM(fnqty_base) AS fnmasuk,0 AS fnkeluar
                            FROM txstock_in_hdr a,txstock_in_det b
                            WHERE a.ftstock_in_no=b.ftstock_in_no
                            AND a.fnstatus<>3 AND a.fnstatus<>9   AND a.ftstock_type_code='invbb' AND /*a.ftcategory_code=0 and */
                            a.fdtrans_date<='{$r2['fdtrans_date']}'
                            GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code

                            UNION ALL
                            SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 AS fnmasuk,SUM(fnqty_base)  AS fnkeluar
                            FROM txstock_out_hdr_bahan_baku a,txstock_out_det_bahan_baku b
                            WHERE a.ftstock_out_no=b.ftstock_out_no
                            AND a.fnstatus<>3 AND a.fnstatus<>9  AND
                            a.fdtrans_date<='{$r2['fdtrans_date']}'
                            GROUP BY b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code

                            UNION ALL
                            SELECT b.ftitem_code,b.ftwarehouse_from AS ftwarehouse_code,b.ftlocation_from AS ftlocation_code,0 AS fnmasuk,SUM(fnqty_base) AS fnkeluar
                            FROM txstock_mutation_hdr_bahan_baku a,txstock_mutation_det_bahan_baku b
                            WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                            AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                            a.fdtrans_date<='{$r2['fdtrans_date']}'
                            GROUP BY  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from

                            UNION ALL
                            SELECT b.ftitem_code,b.ftwarehouse_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 AS fnkeluar
                            FROM txstock_mutation_hdr_bahan_baku a,txstock_mutation_det_bahan_baku b
                            WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                            AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                            a.fdtrans_date<='{$r2['fdtrans_date']}'
                            GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to

                            UNION ALL
                            SELECT b.ftitem_code,b.ftwarehouse_from AS ftwarehouse_code,b.ftlocation_from AS ftlocation_code,0 AS fnmasuk,SUM(fnqty_base) AS fnkeluar
                            FROM txstock_mutation_hdr_bbtobj a,txstock_mutation_det_bbtobj b
                            WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                            AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                            a.fdtrans_date<='{$r2['fdtrans_date']}'
                            GROUP BY  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from

                            UNION ALL
                            SELECT b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,
                            CASE WHEN SUM(fnqty_base)>0 THEN SUM(fnqty_base) ELSE 0 END AS fnmasuk,
                            CASE WHEN SUM(fnqty_base)<0 THEN SUM(fnqty_base)* -1 ELSE 0 END AS fnkeluar
                            FROM txstock_adjustment_hdr_bahan_baku a,txstock_adjustment_det_bahan_baku b
                            WHERE a.ftstock_adjustment_no=b.ftstock_adjustment_no AND
                            a.fdtrans_date<='{$r2['fdtrans_date']}'  AND a.fnstatus=2 AND
                            a.ftstock_adjustment_no <> '{$r2['ftstock_adjustment_no']}'
                            GROUP BY  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                            ) m
                            CROSS JOIN tscompany_info m1
                            WHERE
                            m.ftwarehouse_code = '{$r2['ftwarehouse_code']}' AND
                            m.ftlocation_code = '{$r2['ftlocation_code']}' AND
                            m.ftitem_code = '{$r2['ftitem_code']}'
                            GROUP BY m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code,m1.ftcompany_name

                            ) mm
                            GROUP BY mm.ftitem_code,mm.ftwarehouse_code,mm.ftlocation_code";

                $rs3=mysql_query($ssql3);
                
                // pr("sql 3 : {$ssql3}");

                if (!$rs3) {
                    $ssql4="update txstock_adjustment_det_bahan_baku set fnqty_available=0 ,
                                fnqty=fnqty_opname ,
                                fnqty_base=fnqty_opname
                                where fnid='{$r2['fnid']}'";

                    mysql_query($ssql4);
                    // pr("sql 4 : {$ssql4}");

                } else {

                    while($r3=mysql_fetch_assoc($rs3)) {

                        $ssql4="update txstock_adjustment_det_bahan_baku set fnqty_available={$r3['fnsaldo']} ,
                                    fnqty=fnqty_opname - {$r3['fnsaldo']} ,
                                    fnqty_base=fnqty_opname - {$r3['fnsaldo']}
                                    where fnid='{$r2['fnid']}'";

                        mysql_query($ssql4);
                        // pr("sql 4 : {$ssql4}");

                    }
                }
            }
	}

        pr("*** SELESAI ***");
        
?>