<?php
    Getsecurity('rekapitulasi_barang');
/**
 * PHP Template.
 */
    $objAxpDB->ViewName("Daftar Rekapitulasi barang");   
    $pdate_from=date("'Y-m-01'");
    $pdate_end=date("'Y-m-d'");
    $ssql="	select m1.ftitem_code,m1.ftitem_name,m1.ftstock_group_code,m2.ftstock_group_name,
                ifnull(sum(m4.fnsaldo),0) as fnsaldo_awal,
                ifnull(sum(m5.fnmasuk),0) as fnmasuk,
                ifnull(sum(m5.fnkeluar),0) as fnkeluar,
                ifnull(sum(m6.fnsaldo),0) as fnsaldo_akhir
        from tlstock_hdr m1 inner join 
                tlstock_group m2 on m1.ftstock_group_code=m2.ftstock_group_code 
        left join (	
                /* stock awal */
                select 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 (
                        /*stock masuk */
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,sum(fnqty) 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<>9 and
                                a.fdtrans_date<{$pdate_from}
                        group by b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                        /*stock keluar */
                        union all
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 as fnmasuk,sum(fnqty)  as fnkeluar
                        from txstock_out_hdr a,txstock_out_det b
                        where a.ftstock_out_no=b.ftstock_out_no 
                                and a.fnstatus<>9  and
                                a.fdtrans_date<{$pdate_from}
                        group by b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                        /*mutasi */
                        /* keluar */
                        union all
                        select b.ftitem_code,b.ftwarehouse_from as ftwarehouse_code,b.ftlocation_from as ftlocation_code,0 as fnmasuk,sum(fnqty) as fnkeluar
                        from txstock_mutation_hdr a,txstock_mutation_det b
                        where a.ftstock_mutation_no=b.ftstock_mutation_no 
                                and a.fnstatus<>9 and
                                a.fdtrans_date<{$pdate_from}
                        group by  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from 
                        /*masuk */
                        union all
                        select b.ftitem_code,b.ftwarehouse_to as ftwarehouse_code,b.ftlocation_to as ftlocation_code,sum(fnqty)  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<>9 and
                                a.fdtrans_date<{$pdate_from}
                        group by b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
                        /*penyesuaian*/
                        union all
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,
                            CASE WHEN SUM((fnqty) > 0) THEN SUM(fnqty) ELSE 0 END AS fnmasuk,
                            CASE WHEN SUM((fnqty) < 0) THEN SUM(fnqty) * -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<{$pdate_from} and a.fnstatus = 2
                        group by  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                ) m
                group by m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code
        ) m4
        on m1.ftitem_code=m4.ftitem_code
        left join (
                /* current transaksi */		
                select 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 (
                        /*stock masuk */
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,sum(fnqty) 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 <> 9 and
                                (a.fdtrans_date >= {$pdate_from} and a.fdtrans_date <= {$pdate_end})
                        group by b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                        /*stock keluar */
                        union all
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 as fnmasuk,sum(fnqty)  as fnkeluar
                        from txstock_out_hdr a,txstock_out_det b
                        where a.ftstock_out_no=b.ftstock_out_no 
                                and a.fnstatus <> 9 and
                                (a.fdtrans_date >= {$pdate_from} and a.fdtrans_date <= {$pdate_end})
                        group by b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                        /*mutasi */
                        /* keluar */
                        union all
                        select b.ftitem_code,b.ftwarehouse_from as ftwarehouse_code,b.ftlocation_from as ftlocation_code,0 as fnmasuk,sum(fnqty) as fnkeluar
                        from txstock_mutation_hdr a,txstock_mutation_det b
                        where a.ftstock_mutation_no=b.ftstock_mutation_no 
                                and a.fnstatus <> 9 and
                                (a.fdtrans_date >= {$pdate_from} and a.fdtrans_date <= {$pdate_end})
                        group by  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from 
                        /*masuk */
                        union all
                        select b.ftitem_code,b.ftwarehouse_to as ftwarehouse_code,b.ftlocation_to as ftlocation_code,sum(fnqty)  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 <> 9 and
                                (a.fdtrans_date >= {$pdate_from} and a.fdtrans_date <= {$pdate_end})
                        group by b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
                        /*penyesuaian*/
                        union all
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,
                            CASE WHEN SUM((fnqty) > 0) THEN SUM(fnqty) ELSE 0 END AS fnmasuk,
                            CASE WHEN SUM((fnqty) < 0) THEN SUM(fnqty) * -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 >= {$pdate_from} and a.fdtrans_date <= {$pdate_end}) and a.fnstatus = 2
                        group by  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                ) m
                group by m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code
        ) m5
        on m1.ftitem_code=m5.ftitem_code 
        
        left join (
                /* stock akhir */	
                select 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 (
                        /*stock masuk */
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,sum(fnqty) 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<>9 and
                                a.fdtrans_date<={$pdate_end} 
                        group by b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                        /*stock keluar */
                        union all
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,0 as fnmasuk,sum(fnqty)  as fnkeluar
                        from txstock_out_hdr a,txstock_out_det b
                        where a.ftstock_out_no=b.ftstock_out_no 
                                and a.fnstatus<>9 and
                                a.fdtrans_date<={$pdate_end} 
                        group by b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code
                        /*mutasi */
                        /* keluar */
                        union all
                        select b.ftitem_code,b.ftwarehouse_from as ftwarehouse_code,b.ftlocation_from as ftlocation_code,0 as fnmasuk,sum(fnqty) as fnkeluar
                        from txstock_mutation_hdr a,txstock_mutation_det b
                        where a.ftstock_mutation_no=b.ftstock_mutation_no 
                                and a.fnstatus<>9 and
                                a.fdtrans_date<={$pdate_end} 
                        group by  b.ftitem_code,b.ftwarehouse_from,b.ftlocation_from 
                        /*masuk */
                        union all
                        select b.ftitem_code,b.ftwarehouse_to as ftwarehouse_code,b.ftlocation_to as ftlocation_code,sum(fnqty)  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<>9 and
                                a.fdtrans_date<={$pdate_end} 
                        group by b.ftitem_code,b.ftwarehouse_to,b.ftlocation_to
                        /*penyesuaian*/
                        union all
                        select b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code,
                            CASE WHEN SUM((fnqty) > 0) THEN SUM(fnqty) ELSE 0 END AS fnmasuk,
                            CASE WHEN SUM((fnqty) < 0) THEN SUM(fnqty) * -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<={$pdate_end} and a.fnstatus = 2
                        group by  b.ftitem_code,b.ftwarehouse_code,b.ftlocation_code 
                ) m
                group by m.ftitem_code,m.ftwarehouse_code,m.ftlocation_code
        ) m6 on m1.ftitem_code=m6.ftitem_code 
        cross join tscompany_info m7
        group by m7.ftcompany_name,m1.ftitem_code,m1.ftitem_name,m1.ftstock_group_code,m2.ftstock_group_name";
     
    $ssql1="SELECT A.fnid,B.* 
          FROM tlstock_hdr A,($ssql) B
          WHERE A.ftitem_code=B.ftitem_code";
          
    $objAxpDB->ViewSQL($ssql1);
    
    $objAxpDB->ViewCaption("ID,Kode Barang, Nama barang, Stock Group Code , Stock group Name, Saldo Awal, Qty Masuk, Qty Keluar, Saldo Akhir ");
    
    $objAxpDB->Process();
    $objAxpDB->ButtonShow("tutup, cetak_daftar, export_excel ");
    $objAxpDB->WriteHtmlTable();
?>
