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

 // Prepare Insert GL
function UpdateStockDetail () {        
    //$DateStart = date("Y-m-d");
    //$DateStart = "2019-12-31";
    $DateStart = date("Y")."-12-31";
    
    if ($_SESSION['username'] == 'admin') {
        pr("*** STARTING UPDATE : S/D {$DateStart} ***");
    }
    
    // update qty base null
    $ssql3="UPDATE txstock_out_det SET ffformula = 1 WHERE  ffformula = 0;
           UPDATE txstock_out_det 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);

    // Item Detail dibandingkan ke transaksi
    $ssql=" SELECT bb.ftitem_code,mm.ftitem_code as ftitem_code_transaksi,bb.ftitem_name,bb.ftwarehouse_code,ww.ftwarehouse_name,bb.ftlocation_code,
                    ROUND(SUM(mm.fnmasuk),2) AS fnmasuk,ROUND(SUM(mm.fnkeluar),2) AS fnkeluar, ROUND(SUM(fnmasuk),2) - ROUND(SUM(fnkeluar),2) AS fnsaldo, 
                    ROUND(bb.fnqty_available,2)
            FROM tlstock_det bb 
            LEFT JOIN (

                SELECT '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,ROUND(SUM(m.fnmasuk),2) AS fnmasuk,ROUND(SUM(m.fnkeluar),2) AS fnkeluar,
                        (ROUND(SUM(m.fnmasuk),2)-ROUND(SUM(m.fnkeluar),2)) 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 a,txinnitial_stock_det b
                    WHERE a.ftstock_in_no=b.ftstock_in_no AND a.fnstatus<>3 AND a.fnstatus<>9
                    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='inv' 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 a,txstock_out_det 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 a,txstock_mutation_det 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 a,txstock_mutation_det 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_to AS ftwarehouse_code,b.ftlocation_to AS ftlocation_code,SUM(fnqty_base)  AS fnmasuk,0 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<='{$DateStart}'
                    GROUP BY b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to

                    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_sptobj a,txstock_mutation_det_sptobj 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 a,txstock_adjustment_det 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
                -- 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

                /* 
                UNION ALL
                SELECT '02' AS fttype,m.fdtrans_date,m.fttransaction,m.fttrans_no,m.ftcompany,
                        m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code,ROUND(SUM(m.fnmasuk),2) AS fnmasuk,ROUND(SUM(m.fnkeluar),2) AS fnkeluar,
                        (ROUND(SUM(m.fnmasuk),2)-ROUND(SUM(m.fnkeluar),2)) 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='inv' 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 a
                     INNER JOIN txstock_out_det 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 a,txstock_mutation_det 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 a,txstock_mutation_det 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 Mutasi Bahan Baku' 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_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>='{$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 Mutasi Sparepart' 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_sptobj a,txstock_mutation_det_sptobj 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 a,txstock_adjustment_det 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
                -- WHERE
                -- m.ftwarehouse_code LIKE pwarehouse AND
                --  m.ftitem_code LIKE 'ELB ELASTIC1 1/4 555AW'
                GROUP BY m.fdtrans_date,m.fttransaction,m.fttrans_no,m.ftcompany,m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code
                */ 
           
            ) mm
            ON mm.ftitem_code=bb.ftitem_code AND mm.ftlocation_code=bb.ftlocation_code AND mm.ftwarehouse_code=bb.ftwarehouse_code
            INNER JOIN tlwarehouse ww ON mm.ftwarehouse_code=ww.ftwarehouse_code
            WHERE ROUND(fnsaldo,2) <> ROUND(fnqty_available,2) OR mm.ftitem_code IS NULL  
            GROUP BY bb.ftitem_code,mm.ftitem_code,bb.ftwarehouse_code,bb.ftlocation_code";

    $rs1=mysql_query($ssql);

    if ($_SESSION['username'] == 'admin') {
        pr("sql 1 : {$ssql}");
    }
    
    $counter = 0 ;
    while($r1=mysql_fetch_assoc($rs1)) {
        $ssql2="update tlstock_det 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);
        $counter = $counter + 1;
        //pr("sql 2 : {$ssql2}");

    }

    if ($_SESSION['username'] == 'admin') {
        pr("*** SELESAI UPDATE STOCK : {$counter} rows ***");
    }
    
}    
