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

        //$DateStart = date("Y-m-d");
        $DateStart = "2015-12-31";

        // update qty base null
        $ssql3="UPDATE txstock_out_det_setengah_jadi SET ffformula = 1 WHERE  ffformula = 0;
                UPDATE txstock_out_det_setengah_jadi SET fnqty_base = fnqty * ffformula WHERE fnqty_base IS NULL OR fnqty_base='';
                UPDATE txstock_in_det SET ffformula = 1 WHERE  ffformula = 0;
                UPDATE txstock_in_det SET fnqty_base = fnqty * ffformula WHERE fnqty_base IS NULL OR fnqty_base='';
                                ";

        mysql_query($ssql3);
        
	$ssql=" SELECT mm.ftitem_code,bb.ftitem_name,mm.ftwarehouse_code,ww.ftwarehouse_name,mm.ftlocation_code,
                SUM(mm.fnmasuk) AS fnmasuk,SUM(mm.fnkeluar) AS fnkeluar, SUM(fnmasuk) - SUM(fnkeluar) AS fnsaldo, bb.fnqty_available
                FROM (

                SELECT m1.ftcompany_name,'01' AS fttype,'{$DateStart}' 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_setengah_jadi a,txinnitial_stock_det_setengah_jadi b
                WHERE a.ftstock_in_no=b.ftstock_in_no
                AND a.fnstatus<>3 AND a.fnstatus<>9 AND a.ftstock_type_code = 'invsj'
                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='invsj' AND /*a.ftcategory_code=0 and */
                a.fdtrans_date<'{$DateStart}'
                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_setengah_jadi a,txstock_out_det_setengah_jadi b
                WHERE a.ftstock_out_no=b.ftstock_out_no
                AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                a.fdtrans_date<'{$DateStart}'
                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_setengah_jadi a,txstock_mutation_det_setengah_jadi b
                WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                a.fdtrans_date<'{$DateStart}'
                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_setengah_jadi a,txstock_mutation_det_setengah_jadi b
                WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                a.fdtrans_date<'{$DateStart}'
                GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to

                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_setengah_jadi a,txstock_adjustment_det_setengah_jadi b
                WHERE a.ftstock_adjustment_no=b.ftstock_adjustment_no AND
                a.fdtrans_date<'{$DateStart}'  AND a.fnstatus=2
                GROUP BY  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                ) m
                CROSS JOIN tscompany_info m1
                -- WHERE
                -- m.ftwarehouse_code LIKE pwarehouse AND
                -- m.ftitem_code LIKE 'BEAR. 1201 NSK'
                GROUP BY m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code,m1.ftcompany_name

                UNION ALL
                SELECT m1.ftcompany_name,'02' AS fttype,m.fdtrans_date,m.fttransaction,m.fttrans_no,m.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 a.fdtrans_date, CONCAT('',d.fttrans_status_name) AS  fttransaction, a.ftstock_in_no AS fttrans_no,IFNULL(c.ftsupplier_name,'') AS ftcompany,
                b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,SUM(fnqty_base) AS fnmasuk,0 AS fnkeluar
                FROM txstock_in_hdr a
                INNER JOIN txstock_in_det b ON a.ftstock_in_no=b.ftstock_in_no
                LEFT JOIN tlsupplier c ON a.ftstock_in_company=c.ftsupplier_code
                LEFT JOIN tstransaction_status d ON a.ftcategory_code=d.fntrans_status_code AND d.fntrans_group_code=3
                WHERE
                a.fnstatus<>3 AND a.fnstatus<>9 AND a.ftstock_type_code='invsj' AND /*a.ftcategory_code=0  and */
                a.fdtrans_date>='{$DateStart}' AND a.fdtrans_date<='{$DateStart}'
                GROUP BY a.fdtrans_date,a.ftstock_in_no, a.ftstock_in_company,b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code

                UNION ALL
                SELECT a.fdtrans_date, CONCAT('',d.fttrans_status_name) AS  fttransaction, CASE WHEN IFNULL(a.ftdocument_no,'')<>'' THEN a.ftdocument_no ELSE a.ftstock_out_no END    AS fttrans_no,IFNULL(c.ftsupplier_name,'')  AS ftcompany,
                b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 AS fnmasuk,SUM(fnqty_base)  AS fnkeluar
                FROM txstock_out_hdr_setengah_jadi a
                INNER JOIN txstock_out_det_setengah_jadi b ON a.ftstock_out_no=b.ftstock_out_no
                LEFT JOIN tlsupplier c ON a.ftstock_out_company=c.ftsupplier_code
                LEFT JOIN tstransaction_status d ON a.ftcategory_code=d.fntrans_status_code AND d.fntrans_group_code=4
                WHERE
                a.fnstatus<>3 AND a.fnstatus<>9 AND
                a.fdtrans_date>='{$DateStart}' AND a.fdtrans_date<='{$DateStart}'
                GROUP BY a.fdtrans_date,a.ftstock_out_no,b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code

                UNION ALL
                SELECT a.fdtrans_date, 'Stock Mutasi' AS  fttransaction, a.ftstock_mutation_no AS fttrans_no,'' AS ftcompany,
                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_setengah_jadi a,txstock_mutation_det_setengah_jadi b
                WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                a.fdtrans_date>='{$DateStart}' AND a.fdtrans_date<='{$DateStart}'
                GROUP BY  a.fdtrans_date, a.ftstock_mutation_no ,b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from

                UNION ALL
                SELECT a.fdtrans_date, 'Stock Mutasi' AS  fttransaction, a.ftstock_mutation_no AS fttrans_no,'' AS ftcompany,
                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_setengah_jadi a,txstock_mutation_det_setengah_jadi b
                WHERE a.ftstock_mutation_no=b.ftstock_mutation_no
                AND a.fnstatus<>3 AND a.fnstatus<>9 AND
                a.fdtrans_date>='{$DateStart}' AND a.fdtrans_date<='{$DateStart}'
                GROUP BY  a.fdtrans_date,a.ftstock_mutation_no,b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to

                UNION ALL
                SELECT a.fdtrans_date, 'Stock Penyesuaian' AS  fttransaction, a.ftstock_adjustment_no AS fttrans_no,'' AS ftcompany,
                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_setengah_jadi a,txstock_adjustment_det_setengah_jadi b
                WHERE a.ftstock_adjustment_no=b.ftstock_adjustment_no AND
                a.fdtrans_date>='{$DateStart}' AND a.fdtrans_date<='{$DateStart}'  AND a.fnstatus=2
                GROUP BY  a.fdtrans_date, a.ftstock_adjustment_no,b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                ) m
                CROSS JOIN tscompany_info m1
                -- WHERE
                -- m.ftwarehouse_code LIKE pwarehouse AND
                -- m.ftitem_code LIKE 'BEAR. 1201 NSK'
                GROUP BY m1.ftcompany_name,m.fdtrans_date,m.fttransaction,m.fttrans_no,m.ftcompany,m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code

                ) mm
                INNER JOIN tlstock_det_setengah_jadi bb ON mm.ftitem_code=bb.ftitem_code AND mm.ftlocation_code=bb.ftlocation_code
                    AND mm.ftwarehouse_code=bb.ftwarehouse_code AND mm.fnsaldo <> bb.fnqty_available
                INNER JOIN tlwarehouse ww ON mm.ftwarehouse_code=ww.ftwarehouse_code
                WHERE fnsaldo <> fnqty_available 
                GROUP BY mm.ftitem_code,mm.ftwarehouse_code,mm.ftlocation_code";
        
	$rs1=mysql_query($ssql);

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

	while($r1=mysql_fetch_assoc($rs1)) {


                    $ssql2="update tlstock_det_setengah_jadi set fnqty_available={$r1['fnsaldo']}
                                where ftitem_code='{$r1['ftitem_code']}'
                                        and ftwarehouse_code = '{$r1['ftwarehouse_code']}'
                                        AND ftlocation_code = '{$r1['ftlocation_code']}'
                                                    ";

                    mysql_query($ssql2);
                    //pr("sql 2 : {$ssql2}");

	}

        

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