<?php


    function base_stok($all_where = ''){
        $var = 'SELECT A.*, CASE WHEN sum(masuk - keluar) % 1 = 0 THEN sum(masuk - keluar) ELSE ROUND(sum(masuk - keluar), 2) END AS stok
                    FROM (
                        SELECT v.id, v.id_barang, v.kode_barang, v.nama_barang, 

                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.jumlah*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.jumlah*mps.jumlah_hasil ELSE v.jumlah END AS jumlah,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.masuk*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.masuk*mps.jumlah_hasil ELSE v.masuk END AS masuk,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.keluar*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.keluar*mps.jumlah_hasil ELSE v.keluar END AS keluar,

                        -- v.jumlah, v.masuk, v.keluar,

                        v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, 

                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.id_satuan ELSE v.id_satuan END AS id_satuan,
                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.nama_satuan ELSE v.nama_satuan END AS nama_satuan,

                        v.id_corak, v.nama_corak, v.id_motif, v.nama_motif,
                        -- v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder END AS id_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder END AS no_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder_barang END AS id_workorder_barang,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder_barang END AS no_workorder_barang,

                        -- v.id_kualitas, v.nama_kualitas, 
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_kualitas END AS id_kualitas,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.nama_kualitas END AS nama_kualitas,

                        v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, 


                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.id_perubahansatuan END AS id_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.nama_perubahansatuan END AS nama_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.jumlah_rencana END AS jumlah_rencana,

                        v.tanggal_full
                        FROM (
                            SELECT * FROM view_penerimaan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_transfer  B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_keluar B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_masuk B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_penjualan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_opname B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_rubahspk B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_keluarkemasan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_masukkemasan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_inv_saldoawal B '.$all_where.'
                            UNION ALL 
                            SELECT * FROM view_kembali B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_penggunaanbarang B '.$all_where.'
                        ) v

                        LEFT JOIN ms_gudang AS mg ON mg.id_gudang = v.id_gudang
                        LEFT JOIN ms_perubahansatuan mps ON mps.id_proses = v.id_proses
                        AND mps.id_satuanawal = v.id_satuan
                        AND mps.id_motif_barang = v.id_corak
                        AND mps.id_motif = v.id_motif
                        AND mps.id_lokasi = mg.id_lokasi
                        LEFT JOIN ms_satuan AS ms_rubah ON ms_rubah.id_satuan = mps.id_satuanperubahan
                    ) A GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana';
        return $var;
    }

    function base_stok_masuk($groupby = '', $where = ''){
        if($groupby == ''){
            $groupby = ', A.id_corak, A.id_motif';
        }else{
            $groupby = '';
        }

        //array
        $where_1 = "";
        $where_2 = "";
        if($where != ''){
            if(isset($where[0])){
                $where_1 = " AND ".$where[0];
            }

            if(isset($where[1])){
                $where_2 = " AND ".$where[1];
            }
        }

        $var = 'SELECT A.*, CASE WHEN sum(keluar - masuk) % 1 = 0 THEN sum(keluar - masuk) ELSE ROUND(sum(keluar - masuk), 2) END AS stok
        FROM (
            SELECT v.id id_barangkeluar, v.id_barangkeluar_barang,  v.id_barang, v.kode_barang, v.nama_barang,CASE WHEN pbh.id_keluarhasil IS NOT NULL AND pbh.id_satuanhasil != 0 AND pbh.hasil != 0 THEN pbh.hasil ELSE v.jumlah END AS jumlah,
                v.masuk, CASE WHEN pbh.id_keluarhasil IS NOT NULL AND pbh.id_satuanhasil != 0 AND pbh.hasil != 0 THEN pbh.hasil ELSE v.keluar END AS keluar, v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, CASE WHEN pbh.id_keluarhasil IS NOT NULL AND pbh.id_satuanhasil != 0 AND pbh.hasil != 0 THEN ms.id_satuan ELSE v.id_satuan END AS id_satuan,
                CASE WHEN pbh.id_keluarhasil IS NOT NULL AND pbh.id_satuanhasil != 0 AND pbh.hasil != 0 THEN ms.nama_satuan ELSE v.nama_satuan END AS nama_satuan, v.id_corak, v.nama_corak, v.id_motif, v.nama_motif, v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, v.id_kualitas, v.nama_kualitas, v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, v.id_perubahansatuan, v.nama_perubahansatuan, v.jumlah_rencana, v.tanggal_proses, v.keterangan
            FROM view_edit_keluar as v
                JOIN pro_barangkeluar as pb ON v.id = pb.id_barangkeluar
                LEFT JOIN pro_barangkeluar_hasil AS pbh ON pb.id_barangkeluar = pbh.id_barangkeluar
                AND v.id_barangkeluar_barang = pbh.id_barangkeluar_barang
                LEFT JOIN ms_satuan AS ms ON ms.id_satuan = pbh.id_satuanhasil  
            WHERE pb.status != 0 '.$where_1.'
            UNION ALL
            SELECT pb.id_barangkeluar, v.id id_barangkeluar_barang, v.id_barang, v.kode_barang, v.nama_barang, v.jumlah, v.jumlah as masuk, 0 keluar ,v.id_gudang, v.nama_gudang, pb.tanggal, CASE WHEN mp.parent = 0 THEN "" ELSE mp.id_proses END AS id_proses, CASE WHEN mp.parent = 0 THEN "" ELSE mp.nama_proses END AS nama_proses, v.id_komposisi, v.nama_komposisi, v.id_satuan, v.nama_satuan, v.id_corak, v.nama_corak, v.id_motif, v.nama_motif, v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, v.id_kualitas, v.nama_kualitas, v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, v.id_perubahansatuan, v.nama_perubahansatuan, v.jumlah_rencana, "" tanggal_proses, "" keterangan
            FROM view_edit_masuk as v
            JOIN pro_barangmasuk as pb ON v.id_barangmasuk = pb.id_barangmasuk
            JOIN pro_barangkeluar as pbk ON pb.id_barangkeluar = pbk.id_barangkeluar
            LEFT JOIN ms_proses as mp ON pbk.id_proses = mp.id_proses
            WHERE pb.status != 0 '.$where_2.'
        ) A 
        GROUP BY A.id_barangkeluar, A.id_barang, A.id_komposisi, A.id_satuan, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa '.$groupby; // , A.id_perubahansatuan, A.jumlah_rencana
        return $var;
    }

    function base_stok_masuk_multi(){
        $var = 'SELECT A.*, CASE WHEN sum(keluar - masuk) % 1 = 0 THEN sum(keluar - masuk) ELSE ROUND(sum(keluar - masuk), 2) END AS stok
        FROM (
        SELECT v.id_barangkeluar_kemasan id_barangkeluar_kemasan, v.id_barangkeluar_kb, v.id_barang, v.kode_barang, v.nama_barang, v.jumlah, v.masuk, v.keluar, v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, v.id_satuan, v.nama_satuan, v.id_corak, v.nama_corak, v.id_motif, v.nama_motif, v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, v.id_kualitas, v.nama_kualitas, v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, v.id_perubahansatuan, v.nama_perubahansatuan, v.jumlah_rencana, v.keterangan
            FROM view_edit_barangkeluar_kemasan as v
            JOIN pro_barangkeluar_kemasan as pbk ON v.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
            WHERE pbk.status != 0
            UNION ALL
            SELECT pb.id_barangkeluar_kemasan, "" id_barangkeluar_kb, v.id_barang, v.kode_barang, v.nama_barang, v.jumlah, v.jumlah as masuk, 0 keluar ,v.id_gudang, v.nama_gudang, pb.tanggal, CASE WHEN mp.parent = 0 THEN "" ELSE mp.id_proses END AS id_proses, CASE WHEN mp.parent = 0 THEN "" ELSE mp.nama_proses END AS nama_proses, v.id_komposisi, v.nama_komposisi, v.id_satuan, v.nama_satuan, v.id_corak, v.nama_corak, v.id_motif, v.nama_motif, v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, v.id_kualitas, v.nama_kualitas, v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, v.id_perubahansatuan, v.nama_perubahansatuan, v.jumlah_rencana, "" keterangan
            FROM view_edit_barangmasuk_kemasan as v
            JOIN pro_barangmasuk_kemasan as pb ON v.id_barangmasuk_kemasan = pb.id_barangmasuk_kemasan
            JOIN pro_barangkeluar_kemasan as pbk ON pb.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
            LEFT JOIN ms_proses as mp ON pbk.id_proses = mp.id_proses
            WHERE pb.status != 0              
            ) A
            GROUP BY A.id_barangkeluar_kemasan, A.id_barang, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif'; //, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana

        return $var;
    }

    function base_stok_tenun($where = ''){
        $var = "SELECT vm.*
                FROM view_masuk as vm
                JOIN pro_barangmasuk as pbm ON vm.id = pbm.id_barangmasuk
                JOIN pro_barangkeluar as pkk ON pbm.id_barangkeluar = pkk.id_barangkeluar $where";
        return $var;
    }

    function stok_barang_kembali($id){
        $var = "SELECT A.*, CASE WHEN sum(keluar - masuk) % 1 = 0 THEN sum(keluar - masuk) ELSE ROUND(sum(keluar - masuk), 2) END AS stok
                FROM (
                    SELECT v.*, mk2.id_kb, mk2.nama sub_kategori, mk.id_kb id_sub, mk.nama kategori, v.id_barangkeluar_barang id_bb
                    FROM view_edit_keluar as v
                    JOIN ms_barang as mb ON v.id_barang = mb.id
                    LEFT JOIN ms_kategori_barang as mk ON mb.id_kb = mk.id_kb
                    LEFT JOIN ms_kategori_barang as mk2 ON mk.induk_kb = mk2.id_kb
                    WHERE v.id = '$id'
                    UNION ALL
                    SELECT v.*, mk4.id_kb, mk4.nama sub_kategori, mk3.id_kb, mk3.nama kategori, pk.id_barangkeluar id_bb
                    FROM view_edit_kembali as v
                    JOIN pro_kembali as pk ON v.id_kembali = pk.id_kembali
                    JOIN ms_barang as mb2 ON v.id_barang = mb2.id
                    LEFT JOIN ms_kategori_barang as mk3 ON mb2.id_kb = mk3.id_kb
                    LEFT JOIN ms_kategori_barang as mk4 ON mk3.induk_kb = mk4.id_kb
                    WHERE pk.id_barangkeluar = '$id' AND pk.jenis = '1'
                ) A
                GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana";
        return $var;
    }

    function stok_barang_kembali_kel($id){
    

        $var = "SELECT A.*, CASE WHEN sum(keluar - masuk) % 1 = 0 THEN sum(keluar - masuk) ELSE ROUND(sum(keluar - masuk), 2) END AS stok
                FROM (
                    SELECT v.*, mk2.id_kb, mk2.nama sub_kategori, mk.id_kb id_sub, mk.nama kategori, v.id_barangkeluar_kb id_bb
                            FROM view_edit_barangkeluar_kemasan as v
                            JOIN ms_barang as mb ON v.id_barang = mb.id
                            LEFT JOIN ms_kategori_barang as mk ON mb.id_kb = mk.id_kb
                            LEFT JOIN ms_kategori_barang as mk2 ON mk.induk_kb = mk2.id_kb
                            WHERE v.id_barangkeluar_kemasan = '$id'
                            UNION ALL
                            SELECT v.id_kembali_barang, v.id_kembali, v.id_barang, v.kode_barang, v.nama_barang, v.jumlah, v.masuk, v.keluar, v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, v.inisial_komposisi, v.id_satuan, v.nama_satuan, v.id_corak, v.nama_corak, v.id_motif, v.nama_motif,     v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, v.id_kualitas, v.nama_kualitas, v.id_kualitas_dudul, v.nama_kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, v.id_perubahansatuan, v.nama_perubahansatuan, v.jumlah_rencana, v.keterangan, v.status, mk4.id_kb, mk4.nama sub_kategori, mk3.id_kb, mk3.nama kategori, pk.id_barangkeluar id_bb
                            FROM view_edit_kembali as v
                            JOIN pro_kembali as pk ON v.id_kembali = pk.id_kembali
                            JOIN ms_barang as mb2 ON v.id_barang = mb2.id
                            LEFT JOIN ms_kategori_barang as mk3 ON mb2.id_kb = mk3.id_kb
                            LEFT JOIN ms_kategori_barang as mk4 ON mk3.induk_kb = mk4.id_kb
                            WHERE pk.id_barangkeluar = '$id' AND pk.jenis = '2'
                ) A
                GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana";
        return $var;
    }

    function base_stok_awal(){
        $var = 'SELECT A.*, "0" as a_masuk, "0" as a_keluar, CASE WHEN sum(masuk - keluar) % 1 = 0 THEN sum(masuk - keluar) ELSE ROUND(sum(masuk - keluar), 2) END AS a_saldo_awal /*CASE WHEN masuk > 0 THEN ROUND(masuk,2) WHEN keluar > 0 THEN ROUND(keluar,2) ELSE 0 END AS stok*/
                    FROM (
                        SELECT v.id, v.id_barang, v.kode_barang, v.nama_barang, 

                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.jumlah*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.jumlah*mps.jumlah_hasil ELSE v.jumlah END AS jumlah,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.masuk*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.masuk*mps.jumlah_hasil ELSE v.masuk END AS masuk,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.keluar*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.keluar*mps.jumlah_hasil ELSE v.keluar END AS keluar,

                        -- v.jumlah, v.masuk, v.keluar,

                        v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, 

                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.id_satuan ELSE v.id_satuan END AS id_satuan,
                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.nama_satuan ELSE v.nama_satuan END AS nama_satuan,

                        v.id_corak, v.nama_corak, v.id_motif, v.nama_motif,
                        
                        -- v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, 
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder END AS id_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder END AS no_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder_barang END AS id_workorder_barang,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder_barang END AS no_workorder_barang,

                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_kualitas END AS id_kualitas,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.nama_kualitas END AS nama_kualitas, 
                        v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, 


                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.id_perubahansatuan END AS id_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.nama_perubahansatuan END AS nama_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.jumlah_rencana END AS jumlah_rencana,

                        v.tanggal_full
                        FROM (
                            SELECT * FROM view_penerimaan
                            UNION ALL
                            SELECT * FROM view_transfer 
                            UNION ALL
                            SELECT * FROM view_keluar
                            UNION ALL
                            SELECT * FROM view_masuk
                            UNION ALL
                            SELECT * FROM view_penjualan
                            UNION ALL
                            SELECT * FROM view_opname
                            UNION ALL
                            SELECT * FROM view_rubahspk
                            UNION ALL
                            SELECT * FROM view_keluarkemasan
                            UNION ALL
                            SELECT * FROM view_masukkemasan
                            UNION ALL
                            SELECT * FROM view_inv_saldoawal
                            UNION ALL 
                            SELECT * FROM view_kembali
                            UNION ALL
                            SELECT * FROM view_penggunaanbarang
                        ) v

                        LEFT JOIN ms_gudang AS mg ON mg.id_gudang = v.id_gudang
                        LEFT JOIN ms_perubahansatuan mps ON mps.id_proses = v.id_proses
                        AND mps.id_satuanawal = v.id_satuan
                        AND mps.id_motif_barang = v.id_corak
                        AND mps.id_motif = v.id_motif
                        AND mps.id_lokasi = mg.id_lokasi
                        LEFT JOIN ms_satuan AS ms_rubah ON ms_rubah.id_satuan = mps.id_satuanperubahan
                    ) A GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana';
        return $var;
    }

    function base_stok_awal2(){
        $var = 'SELECT A.*, A.masuk as a_masuk, A.keluar as a_keluar , "0" as a_saldo_awal
                    FROM (
                        SELECT v.id, v.id_barang, v.kode_barang, v.nama_barang, 

                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.jumlah*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.jumlah*mps.jumlah_hasil ELSE v.jumlah END AS jumlah,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.masuk*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.masuk*mps.jumlah_hasil ELSE v.masuk END AS masuk,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.keluar*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.keluar*mps.jumlah_hasil ELSE v.keluar END AS keluar,

                        -- v.jumlah, v.masuk, v.keluar,

                        v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, 

                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.id_satuan ELSE v.id_satuan END AS id_satuan,
                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.nama_satuan ELSE v.nama_satuan END AS nama_satuan,

                        v.id_corak, v.nama_corak, v.id_motif, v.nama_motif, 
                        -- v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, 
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder END AS id_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder END AS no_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder_barang END AS id_workorder_barang,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder_barang END AS no_workorder_barang,

                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_kualitas END AS id_kualitas,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.nama_kualitas END AS nama_kualitas,

                        v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, 


                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.id_perubahansatuan END AS id_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.nama_perubahansatuan END AS nama_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.jumlah_rencana END AS jumlah_rencana,

                        v.tanggal_full
                        FROM (
                            SELECT * FROM view_penerimaan
                            UNION ALL
                            SELECT * FROM view_transfer 
                            UNION ALL
                            SELECT * FROM view_keluar
                            UNION ALL
                            SELECT * FROM view_masuk
                            UNION ALL
                            SELECT * FROM view_penjualan
                            UNION ALL
                            SELECT * FROM view_opname
                            UNION ALL
                            SELECT * FROM view_rubahspk
                            UNION ALL
                            SELECT * FROM view_keluarkemasan
                            UNION ALL
                            SELECT * FROM view_masukkemasan
                            UNION ALL
                            SELECT * FROM view_inv_saldoawal
                            UNION ALL 
                            SELECT * FROM view_kembali
                            UNION ALL
                            SELECT * FROM view_penggunaanbarang
                        ) v

                        LEFT JOIN ms_gudang AS mg ON mg.id_gudang = v.id_gudang
                        LEFT JOIN ms_perubahansatuan mps ON mps.id_proses = v.id_proses
                        AND mps.id_satuanawal = v.id_satuan
                        AND mps.id_motif_barang = v.id_corak
                        AND mps.id_motif = v.id_motif
                        AND mps.id_lokasi = mg.id_lokasi
                        LEFT JOIN ms_satuan AS ms_rubah ON ms_rubah.id_satuan = mps.id_satuanperubahan
                    ) A ';
        return $var;
    }

    function base_stok_user($id_gudang, $all_where = ''){
        $where = '';
        if($id_gudang != 0){
            $where = " WHERE A.id_gudang IN ($id_gudang)";
        }
        $var = 'SELECT A.*, CASE WHEN sum(masuk - keluar) % 1 = 0 THEN sum(masuk - keluar) ELSE ROUND(sum(masuk - keluar), 2) END AS stok
                    FROM (
                        SELECT v.id, v.id_barang, v.kode_barang, v.nama_barang, 

                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.jumlah*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.jumlah*mps.jumlah_hasil ELSE v.jumlah END AS jumlah,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.masuk*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.masuk*mps.jumlah_hasil ELSE v.masuk END AS masuk,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND v.id_proses = 33 THEN v.keluar*v.jumlah_rencana WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN v.keluar*mps.jumlah_hasil ELSE v.keluar END AS keluar,

                        -- v.jumlah, v.masuk, v.keluar,

                        v.id_gudang, v.nama_gudang, v.tanggal, v.id_proses, v.nama_proses, v.id_komposisi, v.nama_komposisi, 

                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.id_satuan ELSE v.id_satuan END AS id_satuan,
                        CASE WHEN mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN ms_rubah.nama_satuan ELSE v.nama_satuan END AS nama_satuan,

                        v.id_corak, v.nama_corak, v.id_motif, v.nama_motif, 
                        -- v.id_workorder, v.no_workorder, v.id_workorder_barang, v.no_workorder_barang, 
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder END AS id_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder END AS no_workorder,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_workorder_barang END AS id_workorder_barang,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.no_workorder_barang END AS no_workorder_barang,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.id_kualitas END AS id_kualitas,
                        CASE WHEN v.id_proses = "37" THEN "" ELSE v.nama_kualitas END AS nama_kualitas,

                        v.id_kualitas_dudul, v.kualitas_dudul, v.id_label, v.nama_label, v.id_kemasan, v.nama_kemasan, v.id_kemasansatuan, v.nama_kemasansatuan, v.id_kemasansepuluh, v.nama_kemasansepuluh, v.id_kemasanseratus, v.nama_kemasanseratus, v.id_cap, v.nama_cap, v.id_kualitas_proses, v.nama_kualitas_proses, v.id_kualitas_periksa, v.nama_kualitas_periksa, 


                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.id_perubahansatuan END AS id_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.nama_perubahansatuan END AS nama_perubahansatuan,
                        CASE WHEN v.id_perubahansatuan != "" AND v.jumlah_rencana != "" AND mps.id_perubahansatuan IS NOT NULL AND v.id_proses = 33 THEN "" ELSE v.jumlah_rencana END AS jumlah_rencana,

                        v.tanggal_full
                        FROM (
                            SELECT * FROM view_penerimaan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_transfer  B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_keluar B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_masuk B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_penjualan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_opname B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_rubahspk B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_keluarkemasan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_masukkemasan B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_inv_saldoawal B '.$all_where.'
                            UNION ALL 
                            SELECT * FROM view_kembali B '.$all_where.'
                            UNION ALL
                            SELECT * FROM view_penggunaanbarang B '.$all_where.'
                        ) v

                        LEFT JOIN ms_gudang AS mg ON mg.id_gudang = v.id_gudang
                        LEFT JOIN ms_perubahansatuan mps ON mps.id_proses = v.id_proses
                        AND mps.id_satuanawal = v.id_satuan
                        AND mps.id_motif_barang = v.id_corak
                        AND mps.id_motif = v.id_motif
                        AND mps.id_lokasi = mg.id_lokasi
                        LEFT JOIN ms_satuan AS ms_rubah ON ms_rubah.id_satuan = mps.id_satuanperubahan
                    ) A '.$where.'
                    GROUP BY A.id_barang, A.id_gudang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana';
        return $var;
    }

    function groupby_basestok($group){
        $var = " ".$group.".id_barang, ".$group.".id_gudang, ".$group.".id_proses, ".$group.".id_komposisi, ".$group.".id_satuan, ".$group.".id_corak, ".$group.".id_motif, ".$group.".id_workorder_barang, ".$group.".id_kualitas, ".$group.".id_kualitas_dudul, ".$group.".id_label, ".$group.".id_kemasan, ".$group.".id_kemasansatuan, ".$group.".id_kemasansepuluh, ".$group.".id_kemasanseratus, ".$group.".id_cap, ".$group.".id_kualitas_proses, ".$group.".id_kualitas_periksa, ".$group.".id_perubahansatuan, ".$group.".jumlah_rencana";

        return $var;
    }

    function barangkeluar_barang($id){
    	$barang = DB::table('pro_barangkeluar_barang AS pbb')->join('ms_barang AS mb', 'pbb.id_barang', 'mb.id')->join('ms_gudang AS mg', 'pbb.id_gudang', 'mg.id_gudang')->leftjoin('ms_warnakomposisi as mwk', 'pbb.warna', 'mwk.id_komposisi')->leftJoin('ms_motif_barang as mmb', 'pbb.id_motif_barang', 'mmb.id_motif_barang')->leftJoin('ms_motif as mm', 'pbb.id_motif', 'mm.id_motif')->leftjoin('ms_kualitas as mk', 'pbb.id_kualitas', 'mk.id_kualitas')->leftJoin('ms_barang as mb2', 'pbb.id_label', 'mb2.id')->leftJoin('ms_barang as mb3', 'pbb.id_kemasan', 'mb3.id')->leftJoin('ms_barang as mb4', 'pbb.id_kemasansatuan', 'mb4.id')->leftJoin('ms_barang as mb5', 'pbb.id_kemasansepuluh', 'mb5.id')->leftJoin('ms_barang as mb6', 'pbb.id_kemasanseratus', 'mb6.id')->leftJoin('ms_barang as mb7', 'pbb.id_cap', 'mb7.id')->leftjoin('ms_kualitas as mk2', 'pbb.id_kualitas_proses', 'mk2.id_kualitas')->leftjoin('ms_kualitas as mk3', 'pbb.id_kualitas_periksa', 'mk3.id_kualitas')->leftjoin('ms_satuan as ms', 'pbb.satuan_jumlah', 'ms.id_satuan')->select(DB::raw('pbb.*, mb.kode_barang, mb.nama_barang, mg.id_gudang, mg.nama_gudang, CASE WHEN mwk.id_komposisi IS NULL THEN "" ELSE  mwk.id_komposisi END AS id_komposisi, CASE WHEN mwk.nama IS NULL THEN "" ELSE  mwk.nama END AS nama_komposisi, CASE WHEN ISNULL(mwk.inisial) THEN "" ELSE mwk.inisial END AS inisial_komposisi, CASE WHEN mmb.id_motif_barang IS NULL THEN "" ELSE mmb.id_motif_barang END AS id_motif_barang, CASE WHEN mmb.motif_keterangan IS NULL THEN "" ELSE mmb.motif_keterangan END AS motif_keterangan, CASE WHEN mmb.motif_keterangan IS NULL THEN "" ELSE mmb.motif_keterangan END AS nama_corak, CASE WHEN mm.id_motif IS NULL THEN "" ELSE mm.id_motif END AS id_motif, CASE WHEN mm.nama_motif IS NULL THEN "" ELSE mm.nama_motif END AS nama_motif, CASE WHEN mk.id_kualitas = 0 THEN "" ELSE mk.id_kualitas END AS id_kualitas, CASE WHEN mk.nama_kualitas IS NULL THEN "" ELSE mk.nama_kualitas END AS nama_kualitas, CASE WHEN pbb.id_kualitas_dudul = 0 THEN "" ELSE pbb.id_kualitas_dudul END AS id_kualitas_dudul, CASE WHEN pbb.id_kualitas_dudul = 0 THEN "" WHEN pbb.id_kualitas_dudul = 1 THEN "Baik" ELSE "Jelek" END AS kualitas_dudul, CASE WHEN pbb.id_label = 0 THEN "" ELSE pbb.id_label END AS id_label, CASE WHEN mb2.nama_barang IS NULL THEN "" ELSE mb2.nama_barang END AS nama_label, CASE WHEN pbb.id_kemasan = 0 THEN "" ELSE pbb.id_kemasan END AS id_kemasan, CASE WHEN mb3.nama_barang IS NULL THEN "" ELSE mb3.nama_barang END AS nama_kemasan, CASE WHEN pbb.id_kemasansatuan = 0 THEN "" ELSE pbb.id_kemasansatuan END AS id_kemasansatuan, CASE WHEN mb4.nama_barang IS NULL THEN "" ELSE mb4.nama_barang END AS nama_kemasansatuan, CASE WHEN pbb.id_kemasansepuluh = 0 THEN "" ELSE pbb.id_kemasansepuluh END AS id_kemasansepuluh, CASE WHEN mb5.nama_barang IS NULL THEN "" ELSE mb5.nama_barang END AS nama_kemasansepuluh, CASE WHEN pbb.id_kemasanseratus = 0 THEN "" ELSE pbb.id_kemasanseratus END AS id_kemasanseratus, CASE WHEN mb6.nama_barang IS NULL THEN "" ELSE mb6.nama_barang END AS nama_kemasanseratus, CASE WHEN pbb.id_cap = 0 THEN "" ELSE pbb.id_cap END AS id_cap, CASE WHEN mb7.nama_barang IS NULL THEN "" ELSE mb7.nama_barang END AS nama_cap, CASE WHEN pbb.id_kualitas_proses = 0 THEN "" ELSE pbb.id_kualitas_proses END AS id_kualitas_proses, CASE WHEN pbb.id_kualitas_proses = 0 THEN "" WHEN pbb.id_kualitas_proses = 1 THEN "Jelek" END AS nama_kualitas_proses, CASE WHEN pbb.id_kualitas_proses = 0 THEN "" ELSE mk2.id_kualitas END AS id_kualitas_proses, CASE WHEN pbb.id_kualitas_proses = 0 THEN "" ELSE mk2.nama_kualitas END AS nama_kualitas_proses, CASE WHEN pbb.id_kualitas_periksa = 0 THEN "" ELSE mk3.id_kualitas END AS id_kualitas_periksa, CASE WHEN pbb.id_kualitas_periksa = 0 THEN "" ELSE mk3.nama_kualitas END AS nama_kualitas_periksa, CASE WHEN ms.id_satuan IS NULL THEN "" ELSE ms.id_satuan END AS id_satuan, CASE WHEN ms.nama_satuan IS NULL THEN "" ELSE  ms.nama_satuan END AS nama_satuan'))->where('pbb.id_barangkeluar', $id);
    	return $barang;
    }

    function get_barangkeluar_barang($id){
    	return DB::table('view_barangkeluar_barang')->where('id_barangkeluar', $id);
    }

    function get_barangkeluar_byorder(){
    	$d_data = DB::table('view_keluar as vk')->leftjoin('pro_barangkeluar as pb', 'vk.id', 'pb.id_barangkeluar')->leftjoin('ms_rekanan as mr', 'pb.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_pegawai as mp', 'pb.id_pegawai', 'mp.id_pegawai')->select(DB::raw('vk.*, CASE WHEN pb.id_rekanan = 0 THEN "" ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN pb.id_rekanan = 0 THEN "" ELSE mr.nama END AS nama_rekanan, CASE WHEN pb.id_pegawai = 0 THEN "" ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN pb.id_pegawai = 0 THEN "" ELSE mp.nama_pegawai END AS nama_pegawai'));
    	return $d_data;
    }

    function get_detailtenun(){
        $d_data = DB::table('pro_barangkeluar as pb')->leftjoin('ms_rekanan as mr', 'pb.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->leftjoin('ms_pegawai as mp', 'pb.id_pegawai', 'mp.id_pegawai')->leftjoin('ms_proses as mpr', 'pb.id_proses', 'mpr.id_proses')->leftjoin('pem_workorder as pw', 'pb.id_workorder', 'pw.id_workorder')->leftjoin('pem_workorder_barang as pwb', 'pb.id_workorder_barang', 'pwb.id_workorder_barang')->leftjoin('ms_lokasi_produksi as mlp', 'pb.id_lokasi', 'mlp.id_lokasi')->select(DB::raw(' pb.id_barangkeluar, MIN(pb.tanggal) tanggal, MAX(pb.tanggal_perkiraan) tanggal_perkiraan, pb.id_rekanan, mr.nama nama_rekanan, mrk.nama nama_kelompok, pb.id_pegawai, mp.nama_pegawai, pb.id_proses, mpr.nama_proses, pb.id_workorder, pw.nomor no_workorder, pb.id_workorder_barang, pwb.no_workorder no_workorder_barang, pb.id_lokasi, mlp.nama_lokasi, pb.status, pb.status_beem, pb.tanggal_beem, pb.status_workorder, pb.noAuto, pb.noAuto_ext, pb.id_lokasi'))->groupBy('pb.id_proses', 'pb.id_workorder', 'pb.id_workorder_barang', 'mr.id_rekanan', 'mp.id_pegawai');
        return $d_data;
    }    

    function get_barangkeluar_verif(){
        $d_data = DB::table('pro_barangmasuk AS pbm')->join('pro_barangkeluar AS pbk', 'pbm.id_barangkeluar', 'pbk.id_barangkeluar')->leftJoin('ms_rekanan AS mr', 'pbk.id_rekanan', 'mr.id_rekanan')->leftJoin('ms_pegawai AS mp', 'pbk.id_pegawai', 'mp.id_pegawai')->join('ms_proses AS mpr', 'pbk.id_proses', 'mpr.id_proses')->leftJoin('pem_workorder as pw', 'pbk.id_workorder', 'pw.id_workorder')->leftJoin('pem_workorder_barang as pwb', 'pbk.id_workorder_barang', 'pwb.id_workorder_barang')->leftjoin('ms_mesin as mm', 'pbk.id_mesin', 'mm.id_mesin')->orderBy('pbm.status', 'asc')->orderBy('pbm.tanggal', 'desc')->select(DB::raw('pbm.id_barangmasuk, pbm.tanggal, pbk.tanggal_perkiraan, pbm.status, pbm.noAuto, pbm.noAuto_ext, pbk.id_barangkeluar, mr.nama nama_rekanan, mp.nama_pegawai, mpr.nama_proses, pw.id_workorder, pw.nomor nomor_workorder, pwb.id_workorder_barang, pwb.no_workorder nomor_workorder_barang, mm.nama nama_mesin, GROUP_CONCAT(pbb.status) AS group_status, LOCATE("0", GROUP_CONCAT(pbb.status)) AS is_0, @rownum  := @rownum  + 1 AS rownum'));

        return $d_data;
    }

    function get_barangkeluar_byorderAtm(){
        $d_data = DB::table('view_keluar as vk')->leftjoin('pro_barangkeluar as pb', 'vk.id', 'pb.id_barangkeluar')->leftjoin('ms_rekanan as mr', 'pb.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_mesin as mp', 'pb.id_mesin', 'mp.id_mesin')->leftjoin('pem_workorder_barang AS pwb', 'pb.id_workorder_barang', 'pwb.id_workorder_barang')->leftjoin('pem_workorder AS pw', 'pwb.id_workorder', 'pw.id_workorder')->select(DB::raw('vk.*, CASE WHEN pb.id_rekanan = 0 THEN "" ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN pb.id_rekanan = 0 THEN "" ELSE mr.nama END AS nama_rekanan, CASE WHEN pb.id_mesin = 0 THEN "" ELSE mp.id_mesin END AS id_mesin, CASE WHEN pb.id_mesin = 0 THEN "" ELSE mp.nama END AS nama_mesin, CASE WHEN pw.id_workorder IS NULL THEN "" ELSE pw.id_workorder END AS id_workorder_bb, CASE WHEN pw.id_workorder IS NULL THEN "" ELSE pw.nomor END AS no_workorder_bb, CASE WHEN pwb.id_workorder_barang IS NULL THEN "" ELSE pwb.id_workorder_barang END AS id_workorder_barang_bb, CASE WHEN pwb.id_workorder_barang IS NULL THEN "" ELSE pwb.no_workorder END AS no_workorder_barang_bb'));
        return $d_data;
    }

    function get_detailtenunAtm(){
        $d_data = DB::table('pro_barangkeluar as pb')->leftjoin('ms_rekanan as mr', 'pb.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->leftjoin('ms_mesin as mp', 'pb.id_mesin', 'mp.id_mesin')->leftjoin('ms_proses as mpr', 'pb.id_proses', 'mpr.id_proses')->leftjoin('pem_workorder as pw', 'pb.id_workorder', 'pw.id_workorder')->leftjoin('pem_workorder_barang as pwb', 'pb.id_workorder_barang', 'pwb.id_workorder_barang')->leftjoin('ms_lokasi_produksi as mlp', 'pb.id_lokasi', 'mlp.id_lokasi')->select(DB::raw(' pb.id_barangkeluar, MIN(pb.tanggal) tanggal, MAX(pb.tanggal_perkiraan) tanggal_perkiraan, pb.id_rekanan, mr.nama nama_rekanan, mrk.nama nama_kelompok, pb.id_mesin, mp.nama as nama_mesin, pb.id_proses, mpr.nama_proses, pb.id_workorder, pw.nomor no_workorder, pb.id_workorder_barang, pwb.no_workorder no_workorder_barang, pb.id_lokasi, mlp.nama_lokasi, pb.status, pb.status_beem, pb.tanggal_beem, pb.status_workorder, pb.noAuto, pb.noAuto_ext, CASE WHEN pw.id_workorder IS NULL THEN "" ELSE pw.id_workorder END AS id_workorder_bb, CASE WHEN pw.id_workorder IS NULL THEN "" ELSE pw.nomor END AS no_workorder_bb, CASE WHEN pwb.id_workorder_barang IS NULL THEN "" ELSE pwb.id_workorder_barang END AS id_workorder_barang_bb, CASE WHEN pwb.id_workorder_barang IS NULL THEN "" ELSE pwb.no_workorder END AS no_workorder_barang_bb'))->groupBy('pb.id_proses', 'mp.id_mesin', "pb.id_workorder_barang"); //, 'pb.id_workorder', 'pb.id_workorder_barang'
        return $d_data;
    }

    function get_barangkeluar_byorderAll(){
        $d_data = DB::table('view_keluar as vk')->leftjoin('pro_barangkeluar as pb', 'vk.id', 'pb.id_barangkeluar')->leftjoin('ms_rekanan as mr', 'pb.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_mesin as mp', 'pb.id_mesin', 'mp.id_mesin')->leftjoin('ms_pegawai as mpi', 'pb.id_pegawai', 'mpi.id_pegawai')->select(DB::raw('vk.*, CASE WHEN pb.id_rekanan = 0 THEN "" ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN pb.id_rekanan = 0 THEN "" ELSE mr.nama END AS nama_rekanan, CASE WHEN pb.id_mesin = 0 THEN "" ELSE mp.id_mesin END AS id_mesin, CASE WHEN pb.id_mesin = 0 THEN "" ELSE mp.nama END AS nama_mesin, CASE WHEN pb.id_pegawai = 0 THEN "" ELSE mpi.id_pegawai END AS id_pegawai, CASE WHEN pb.id_pegawai = 0 THEN "" ELSE mpi.nama_pegawai END AS nama_pegawai'));
        return $d_data;
    }

    function get_detailtenunAll(){
        $d_data = DB::table('pro_barangkeluar as pb')->leftjoin('ms_rekanan as mr', 'pb.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->leftjoin('ms_pegawai as mpi', 'pb.id_pegawai', 'mpi.id_pegawai')->leftjoin('ms_mesin as mp', 'pb.id_mesin', 'mp.id_mesin')->leftjoin('ms_proses as mpr', 'pb.id_proses', 'mpr.id_proses')->leftjoin('pem_workorder as pw', 'pb.id_workorder', 'pw.id_workorder')->leftjoin('pem_workorder_barang as pwb', 'pb.id_workorder_barang', 'pwb.id_workorder_barang')->leftjoin('ms_lokasi_produksi as mlp', 'pb.id_lokasi', 'mlp.id_lokasi')->select(DB::raw(' pb.id_barangkeluar, MIN(pb.tanggal) tanggal, MAX(pb.tanggal_perkiraan) tanggal_perkiraan, pb.id_rekanan, mr.nama nama_rekanan, mrk.nama nama_kelompok, pb.id_mesin, mp.nama as nama_mesin, pb.id_pegawai, mpi.nama_pegawai, pb.id_proses, mpr.nama_proses, pb.id_workorder, pw.nomor no_workorder, pb.id_workorder_barang, pwb.no_workorder no_workorder_barang, pb.id_lokasi, mlp.nama_lokasi, pb.status, pb.status_beem, pb.tanggal_beem, pb.status_workorder, pb.noAuto, pb.noAuto_ext'))->groupBy('pb.id_proses', 'pb.id_workorder', 'pb.id_workorder_barang');
        return $d_data;
    }

    function get_barangkeluar_proses(){
        $id_proses = Session::get('id_proses');
        $id_lokasi = Session::get('id_lokasi');
        $where = '';
        $where_arr = array();
        if($id_proses != 0){
            $where_arr[] = " pb.id_proses IN ($id_proses) ";
        }

        if($id_lokasi != 0){
            $where_arr[] = "pb.id_lokasi = ".$id_lokasi."";
        }

        if($id_lokasi != 0 || $id_proses != 0){
            $where = implode(" AND ", $where_arr);
            $where .= " AND ";
        }

        $d_data = "SELECT pb.id_barangkeluar, pb.tanggal,
                CASE WHEN ISNULL(mr.id_rekanan) THEN '' ELSE mr.id_rekanan END AS id_rekanan,
                CASE WHEN ISNULL(mr.nama) THEN '' ELSE mr.nama END AS nama_rekanan,
                CASE WHEN ISNULL(mp.id_pegawai) THEN '' ELSE mp.id_pegawai END AS id_pegawai,
                CASE WHEN ISNULL(mp.nama_pegawai) THEN '' ELSE mp.nama_pegawai END AS nama_pegawai,
                CASE WHEN ISNULL(mm.id_mesin) THEN '' ELSE mm.id_mesin END AS id_mesin,
                CASE WHEN ISNULL(mm.nama) THEN '' ELSE mm.nama END AS nama_mesin,
                CASE WHEN ISNULL(mpr.id_proses) THEN '' ELSE mpr.id_proses END AS id_proses,
                CASE WHEN ISNULL(mpr.nama_proses) THEN '' ELSE mpr.nama_proses END AS nama_proses,
                CASE WHEN ISNULL(pw.id_workorder) AND ISNULL(pwb.id_workorder_barang) THEN '' WHEN ISNULL(pw.id_workorder) THEN (
                 SELECT pw.id_workorder
                 FROM pem_workorder as pw
                 JOIN pem_workorder_barang as pwb2 ON pw.id_workorder = pwb2.id_workorder_barang
                 WHERE pwb2.id_workorder_barang = pwb.id_workorder_barang
                ) ELSE pw.id_workorder END AS id_workorder,
                CASE WHEN ISNULL(pw.id_workorder) AND ISNULL(pwb.id_workorder_barang) THEN '' WHEN ISNULL(pw.id_workorder) THEN (
                 SELECT pw.nomor
                 FROM pem_workorder as pw
                 JOIN pem_workorder_barang as pwb2 ON pw.id_workorder = pwb2.id_workorder_barang
                 WHERE pwb2.id_workorder_barang = pwb.id_workorder_barang
                ) ELSE pw.id_workorder END AS no_workorder,
                CASE WHEN ISNULL(pwb.id_workorder_barang) THEN '' ELSE pwb.id_workorder_barang END AS id_workorder_barang,
                CASE WHEN ISNULL(pwb.no_workorder) THEN '' ELSE pwb.no_workorder END AS no_workorder_barang,
                CASE WHEN ISNULL(mlp.id_lokasi) THEN '' ELSE mlp.id_lokasi END AS id_lokasi,
                CASE WHEN ISNULL(mlp.nama_lokasi) THEN '' ELSE mlp.nama_lokasi END AS nama_lokasi,
                pb.tanggal_perkiraan, pb.noAuto, pb.noAuto_ext, '1' jenis
                FROM pro_barangkeluar as pb
                LEFT JOIN pro_barangmasuk as pbm ON pb.id_barangkeluar = pbm.id_barangkeluar
                LEFT JOIN ms_rekanan as mr ON mr.id_rekanan = pb.id_rekanan
                LEFT JOIN ms_pegawai as mp ON mp.id_pegawai = pb.id_pegawai
                LEFT JOIN ms_mesin as mm ON mm.id_mesin =  pb.id_mesin
                JOIN ms_proses as mpr ON mpr.id_proses = pb.id_proses
                LEFT JOIN pem_workorder as pw ON pw.id_workorder = pb.id_workorder
                LEFT JOIN pem_workorder_barang as pwb ON pwb.id_workorder_barang = pb.id_workorder_barang
                LEFT JOIN ms_lokasi_produksi as mlp ON mlp.id_lokasi = pb.id_lokasi
                WHERE $where pb.status IN (1, 3) -- OR pbm.status = '0'
                GROUP BY pb.id_barangkeluar
                UNION ALL
                SELECT pb.id_barangkeluar_kemasan, pb.tanggal,
                CASE WHEN ISNULL(mr.id_rekanan) THEN '' ELSE mr.id_rekanan END AS id_rekanan,
                CASE WHEN ISNULL(mr.nama) THEN '' ELSE mr.nama END AS nama_rekanan,
                CASE WHEN ISNULL(mp.id_pegawai) THEN '' ELSE mp.id_pegawai END AS id_pegawai,
                CASE WHEN ISNULL(mp.nama_pegawai) THEN '' ELSE mp.nama_pegawai END AS nama_pegawai,
                '' id_mesin,
                '' nama_mesin,
                CASE WHEN ISNULL(mpr.id_proses) THEN '' ELSE mpr.id_proses END AS id_proses,
                CASE WHEN ISNULL(mpr.nama_proses) THEN '' ELSE mpr.nama_proses END AS nama_proses,
                '' id_workorder,
                '' no_workorder,
                '' id_workorder_barang,
                '' no_workorder_barang,
                CASE WHEN ISNULL(mlp.id_lokasi) THEN '' ELSE mlp.id_lokasi END AS id_lokasi,
                CASE WHEN ISNULL(mlp.nama_lokasi) THEN '' ELSE mlp.nama_lokasi END AS nama_lokasi,
                pb.tanggal_perkiraan, pb.noAuto, pb.noAuto_ext, '2' jenis
                FROM pro_barangkeluar_kemasan as pb
                LEFT JOIN pro_barangmasuk_kemasan as pbk ON pb.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
                LEFT JOIN ms_rekanan as mr ON mr.id_rekanan = pb.id_rekanan
                LEFT JOIN ms_pegawai as mp ON mp.id_pegawai = pb.id_pegawai
                JOIN ms_proses as mpr ON mpr.id_proses = pb.id_proses
                LEFT JOIN ms_lokasi_produksi as mlp ON mlp.id_lokasi = pb.id_lokasi
                WHERE $where pb.status IN (1, 3) -- OR pbk.status = '0'
                GROUP BY pb.id_barangkeluar_kemasan";

        return $d_data;
    }

    function get_kembali($where = ''){
        $id_proses = Session::get('id_proses');
        $id_lokasi = Session::get('id_lokasi');
        // if($id_proses != 0){
        //     $where[] = " pbk.id_proses IN ($id_proses) ";
        // }

        // if($id_lokasi != 0){
        //     $where[] = "pbk.id_lokasi = ".$id_lokasi."";
        // }

        // if($id_lokasi != 0 || $id_proses != 0){
        //     $where = implode(" AND ", $where);
        //     $wheres .= " WHERE ".$where;
        // }

        $d_data = "SELECT pk.*, pbk.tanggal tanggal_keluar, mp.id_pegawai, mp.nama_pegawai, mr.id_rekanan, mr.nama nama_rekanan, mm.id_mesin, mm.nama nama_mesin, mpr.id_proses, mpr.nama_proses, pbk.noAuto, pbk.noAuto_ext, pbk.tanggal_perkiraan, CASE WHEN ISNULL(pwb.id_workorder_barang) THEN '' ELSE pwb.id_workorder_barang END AS id_workorder_barang, CASE WHEN ISNULL(pwb.no_workorder) THEN '' ELSE pwb.no_workorder END AS no_workorder_barang
            FROM pro_kembali as pk
            LEFT JOIN pro_kembali_barang AS pkb ON pk.id_kembali = pkb.id_kembali
            LEFT JOIN pro_barangkeluar as pbk ON pk.id_barangkeluar = pbk.id_barangkeluar AND pk.jenis = '1'
            LEFT JOIN ms_rekanan as mr ON pbk.id_rekanan = mr.id_rekanan
            LEFT JOIN ms_pegawai as mp ON pbk.id_pegawai = mp.id_pegawai
            LEFT JOIN ms_mesin as mm ON pbk.id_mesin = mm.id_mesin
            LEFT JOIN pem_workorder_barang as pwb ON pbk.id_workorder_barang = pwb.id_workorder_barang
            LEFT JOIN ms_proses as mpr ON pbk.id_proses = mpr.id_proses $where
            UNION ALL
            SELECT pk.*, pbk.tanggal tanggal_keluar, mp.id_pegawai, mp.nama_pegawai, mr.id_rekanan, mr.nama nama_rekanan, '' id_mesin, '' nama_mesin, mpr.id_proses, mpr.nama_proses, pbk.noAuto, pbk.noAuto_ext, pbk.tanggal_perkiraan, '' id_workorder_barang, '' no_workorder_barang
            FROM pro_kembali as pk
            LEFT JOIN pro_kembali_barang AS pkb ON pk.id_kembali = pkb.id_kembali
            JOIN pro_barangkeluar_kemasan as pbk ON pk.id_barangkeluar = pbk.id_barangkeluar_kemasan AND pk.jenis = '2'
            LEFT JOIN ms_rekanan as mr ON pbk.id_rekanan = mr.id_rekanan
            LEFT JOIN ms_pegawai as mp ON pbk.id_pegawai = mp.id_pegawai
            JOIN ms_proses as mpr ON pbk.id_proses = mpr.id_proses $where";

        return $d_data;
    }

    function get_mutasi($where){
        $query = "SELECT A.*
                    FROM (
                        SELECT vp.* , CONCAT(pp.no_faktur, pp.sufix_no_faktur) ket_1, mp.nama ket_2, 'Pembelian' ket_3, '' ket_4
                        FROM view_penerimaan vp
                        JOIN pem_pembelian pp ON vp.id = pp.id_pembelian
                        JOIN ms_penyedia mp ON pp.id_penyedia = mp.id_penyedia
                        UNION ALL
                        SELECT vt.*, CONCAT(it.no_faktur, it.faktur) ket_1, vt.nama_gudang ket_2, 'Transfer Keluar' ket_3, '' ket_4
                        FROM view_transferkeluar vt
                        JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
                        UNION ALL
                        SELECT vt.*, CONCAT(it.no_faktur, it.faktur) ket_1, vt.nama_gudang ket_2, 'Transfer Masuk' ket_3, '' ket_4
                        FROM view_transfermasuk vt
                        JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
                        UNION ALL
                        SELECT vk.*, CONCAT(pbk.noAuto, pbk.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Bon Bahan' ket_3, mpr.nama_proses ket_4
                        FROM view_keluar vk
                        JOIN pro_barangkeluar pbk ON vk.id = pbk.id_barangkeluar
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vm.*, CONCAT(pbm.noAuto, pbm.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Laporan Hasil' ket_3, mpr.nama_proses ket_4
                        FROM view_masuk vm
                        JOIN pro_barangmasuk pbm ON vm.id = pbm.id_barangmasuk
                        JOIN pro_barangkeluar pbk ON pbm.id_barangkeluar = pbk.id_barangkeluar
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vp.*, CONCAT(pp.no_faktur, pp.sufix_no_faktur) ket_1, mp.nama ket_2, 'Penjualan' ket_3, '' ket_4
                        FROM view_penjualan vp
                        JOIN pen_penjualan pp ON vp.id = pp.id_penjualan
                        JOIN ms_pelanggan mp ON pp.id_pelanggan = mp.id_pelanggan
                        UNION ALL
                        SELECT vo.*, io.keterangan ket_1, vo.nama_gudang ket_2, 'Stok Opname' ket_3, '' ket_4
                        FROM view_opname vo
                        JOIN inv_opname as io ON vo.id = io.id_opname
                        UNION ALL
                        SELECT vr.*, ir.keterangan ket_1, vr.nama_gudang ket_2, 'Perubahan SPK Keluar' ket_3, '' ket_4
                        FROM view_rubahspk_keluar as vr
                        JOIN inv_rubahspk as ir ON vr.id = ir.id_rubahspk
                        UNION ALL
                        SELECT vr.*, ir.keterangan ket_1, vr.nama_gudang ket_2, 'Perubahan SPK Masuk' ket_3, '' ket_4
                        FROM view_rubahspk_masuk as vr
                        JOIN inv_rubahspk as ir ON vr.id = ir.id_rubahspk
                        UNION ALL
                        SELECT vk.*, CONCAT(pbk.noAuto, pbk.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Bon Bahan Multi' ket_3, mpr.nama_proses ket_4
                        FROM view_keluarkemasan as vk
                        JOIN pro_barangkeluar_kemasan as pbk ON vk.id = pbk.id_barangkeluar_kemasan
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses as mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vm.*, CONCAT(pbm.noAuto, pbm.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Laporan Hasil Multi' ket_3, mpr.nama_proses ket_4
                        FROM view_masukkemasan as vm
                        JOIN pro_barangmasuk_kemasan as pbm ON vm.id = pbm.id_barangmasuk_kemasan
                        JOIN pro_barangkeluar_kemasan as pbk ON pbm.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vi.*, '' ket_1, '' ket_2, 'Saldo Awal' ket_3, '' ket_4
                        FROM view_inv_saldoawal as vi
                        JOIN inv_saldoawal as isa ON vi.id = isa.id
                        UNION ALL
                        SELECT vke.*, '' ket_1, '' ket_2, 'Pengembalian Barang' ket_3, '' ket_4
                        FROM view_kembali as vke
                        JOIN pro_kembali as pk ON vke.id = pk.id_kembali
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang
                    $where
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal";
        return $query;
    }

    function get_list_produksi($where){
        $query = "SELECT A.*
                    FROM (
                        SELECT vp.*
                        FROM view_keluar_all vp
                        UNION ALL
                        SELECT vm.*
                        FROM view_masuk_all vm
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang
                    $where
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal";
        return $query;
    }


    // -------------------- Kemasan ----------------------
    function get_keluarkemasan_byid(){
        $d_data = DB::table('pro_barangkeluar_kemasan as pbk')->leftjoin('ms_pegawai as mp', 'pbk.id_pegawai', 'mp.id_pegawai')->leftjoin('ms_rekanan as mr', 'pbk.id_rekanan', 'mr.id_rekanan')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->leftJoin('ms_proses as mpr', 'pbk.id_proses', 'mpr.id_proses')->leftjoin('ms_lokasi_produksi as mlp', 'pbk.id_lokasi', 'mlp.id_lokasi')->select(DB::raw('pbk.*, mp.nama_pegawai, mr.nama nama_rekanan, mrk.nama nama_kelompok, mpr.nama_proses, mlp.nama_lokasi, pbk.noAuto, pbk.noAuto_ext'));

        return $d_data;
    }
    // -------------------- Kemasan ----------------------

    //--------------------- Middleware Session --------------------------
    function middleware_session($url){
            $kode_group = Session::get('kode_group');
            $d_menu = DB::table("tb_menu as a")->join("tb_role as b","a.kode_menu","b.kode_menu")->where([['a.link_menu',$url],['b.kode_group',$kode_group],['b.status_role','1'],['active','1']])->orderBy('sort_menu');
            $kondisi = $d_menu->count();
            return $kondisi;
    }

    function middleware_session_user($url){
            $kode_group = Session::get('kode_group');
            $kode_user = Session::get('kode_user');
            $d_menu = DB::table("tb_menu as a")->join("tb_role as b","a.kode_menu","b.kode_menu")->where([['link_menu',$url],['kode_group',$kode_group],['status_role','1'],['active','1']])->orderBy('sort_menu');
            $d_user = DB::table("tb_user")->where([['kode_user',$kode_user],['kode_group',$kode_group],['is_aktif','1']]);
            $kondisi = Session::get('decode') <> 1 && $d_menu->count() == 0 && $d_user->count() == 0;
            return $kondisi;
    }
    //--------------------- End Middleware Session --------------------------

    //--------------------- Jurnal Umum --------------------------
    function jurnal_umum_1(){
        $q_jurnal_pembelian = DB::table("jurnal_pembelian")
            ->selectRaw("`jurnal_pembelian`.`rows` AS `rows`,
            `jurnal_pembelian`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_pembelian`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_pembelian`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_pembelian`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_pembelian`.`saldo_normal` AS `saldo_normal`,
            `jurnal_pembelian`.`parent` AS `parent`,
            `jurnal_pembelian`.`id_ref` AS `id_ref`,
            `jurnal_pembelian`.`nomor_ref` AS `nomor_ref`,
            `jurnal_pembelian`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_pembelian`.`tanggal` AS `tanggal`,
            `jurnal_pembelian`.`keterangan` AS `keterangan`,
            1 AS `kurs_jual`,
            `jurnal_pembelian`.`nominal` AS `nominal`,
            `jurnal_pembelian`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_pembelian' AS `keterangan_lain`");

        $q_jurnal_orderservice = DB::table("jurnal_orderservice")
            ->selectRaw("
                `jurnal_orderservice`.`rows` AS `rows`,
                `jurnal_orderservice`.`id_perkiraan` AS `id_perkiraan`,
                `jurnal_orderservice`.`kd_rek_1` AS `kd_rek_1`,
                `jurnal_orderservice`.`kode_perkiraan` AS `kode_perkiraan`,
                `jurnal_orderservice`.`nama_perkiraan` AS `nama_perkiraan`,
                `jurnal_orderservice`.`saldo_normal` AS `saldo_normal`,
                `jurnal_orderservice`.`parent` AS `parent`,
                `jurnal_orderservice`.`id_ref` AS `id_ref`,
                `jurnal_orderservice`.`nomor_ref` AS `nomor_ref`,
                `jurnal_orderservice`.`tanggal_ref` AS `tanggal_ref`,
                `jurnal_orderservice`.`tanggal` AS `tanggal`,
                `jurnal_orderservice`.`keterangan` AS `keterangan`,
                1 AS `kurs_jual`,
                `jurnal_orderservice`.`nominal` AS `nominal`,
                `jurnal_orderservice`.`tipe_transaksi` AS `tipe_transaksi`,
                `jurnal_orderservice`.`keterangan_lain` AS `keterangan_lain`
            ");

        $q_jurnal_penjualan = DB::table("jurnal_penjualan")
            ->selectRaw("`jurnal_penjualan`.`rows` AS `rows`,
            `jurnal_penjualan`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_penjualan`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_penjualan`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_penjualan`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_penjualan`.`saldo_normal` AS `saldo_normal`,
            `jurnal_penjualan`.`parent` AS `parent`,
            `jurnal_penjualan`.`id_ref` AS `id_ref`,
            `jurnal_penjualan`.`nomor_ref` AS `nomor_ref`,
            `jurnal_penjualan`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_penjualan`.`tanggal` AS `tanggal`,
            `jurnal_penjualan`.`keterangan` AS `keterangan`,
            `jurnal_penjualan`.`kurs_jual` AS `kurs_jual`,
            `jurnal_penjualan`.`nominal` AS `nominal`,
            `jurnal_penjualan`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_penjualan' AS `keterangan_lain`");

        $q_jurnal_akun_kas_bank = DB::table(DB::raw("(".jurnal_akun_kas_bank()->toSql().") AS jurnal_akun_kas_bank"))
            ->selectRaw("`jurnal_akun_kas_bank`.`rows` AS `rows`,
            `jurnal_akun_kas_bank`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_akun_kas_bank`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_akun_kas_bank`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_akun_kas_bank`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_akun_kas_bank`.`saldo_normal` AS `saldo_normal`,
            `jurnal_akun_kas_bank`.`parent` AS `parent`,
            `jurnal_akun_kas_bank`.`id_ref` AS `id_ref`,
            `jurnal_akun_kas_bank`.`nomor_ref` AS `nomor_ref`,
            `jurnal_akun_kas_bank`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_akun_kas_bank`.`tanggal` AS `tanggal`,
            `jurnal_akun_kas_bank`.`keterangan` AS `keterangan`,
            `jurnal_akun_kas_bank`.`kurs_jual` AS `kurs_jual`,
            `jurnal_akun_kas_bank`.`nominal` AS `nominal`,
            `jurnal_akun_kas_bank`.`tipe_transaksi` AS `tipe_transaksi`,
            `jurnal_akun_kas_bank`.`keterangan_lain` AS `keterangan_lain`");
            
        $query = $q_jurnal_pembelian->unionAll($q_jurnal_orderservice)->unionAll($q_jurnal_penjualan)->unionAll($q_jurnal_akun_kas_bank);

        return $query;
    }

    function jurnal_akun_kas_bank()
    {
        $q_jurnal_sumber_dana = DB::table("jurnal_sumber_dana")
            ->selectRaw("`jurnal_sumber_dana`.`rows` AS `rows`,
            `jurnal_sumber_dana`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_sumber_dana`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_sumber_dana`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_sumber_dana`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_sumber_dana`.`saldo_normal` AS `saldo_normal`,
            `jurnal_sumber_dana`.`parent` AS `parent`,
            `jurnal_sumber_dana`.`id_ref` AS `id_ref`,
            `jurnal_sumber_dana`.`nomor_ref` AS `nomor_ref`,
            `jurnal_sumber_dana`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_sumber_dana`.`tanggal` AS `tanggal`,
            `jurnal_sumber_dana`.`keterangan` AS `keterangan`,
            1 AS `kurs_jual`,
            `jurnal_sumber_dana`.`nominal` AS `nominal`,
            `jurnal_sumber_dana`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_sumber_dana' AS `keterangan_lain`");

        $q_jurnal_pembelian_bayar = DB::table("jurnal_pembelian_bayar")
            ->selectRaw("`jurnal_pembelian_bayar`.`rows` AS `rows`,
            `jurnal_pembelian_bayar`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_pembelian_bayar`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_pembelian_bayar`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_pembelian_bayar`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_pembelian_bayar`.`saldo_normal` AS `saldo_normal`,
            `jurnal_pembelian_bayar`.`parent` AS `parent`,
            `jurnal_pembelian_bayar`.`id_ref` AS `id_ref`,
            `jurnal_pembelian_bayar`.`nomor_ref` AS `nomor_ref`,
            `jurnal_pembelian_bayar`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_pembelian_bayar`.`tanggal` AS `tanggal`,
            `jurnal_pembelian_bayar`.`keterangan` AS `keterangan`,
            1 AS `kurs_jual`,
            `jurnal_pembelian_bayar`.`nominal` AS `nominal`,
            `jurnal_pembelian_bayar`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_pembelian_bayar' AS `keterangan_lain`");

        $q_jurnal_penjualan_bayar = DB::table("jurnal_penjualan_bayar")
            ->selectRaw("`jurnal_penjualan_bayar`.`rows` AS `rows`,
            `jurnal_penjualan_bayar`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_penjualan_bayar`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_penjualan_bayar`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_penjualan_bayar`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_penjualan_bayar`.`saldo_normal` AS `saldo_normal`,
            `jurnal_penjualan_bayar`.`parent` AS `parent`,
            `jurnal_penjualan_bayar`.`id_ref` AS `id_ref`,
            `jurnal_penjualan_bayar`.`nomor_ref` AS `nomor_ref`,
            `jurnal_penjualan_bayar`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_penjualan_bayar`.`tanggal` AS `tanggal`,
            `jurnal_penjualan_bayar`.`keterangan` AS `keterangan`,
            `jurnal_penjualan_bayar`.`kurs_jual` AS `kurs_jual`,
            `jurnal_penjualan_bayar`.`nominal` AS `nominal`,
            `jurnal_penjualan_bayar`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_penjualan_bayar' AS `keterangan_lain`");

        $q_jurnal_orderservice_bayar = DB::table("jurnal_orderservice_bayar")
            ->selectRaw("`jurnal_orderservice_bayar`.`rows` AS `rows`,
            `jurnal_orderservice_bayar`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_orderservice_bayar`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_orderservice_bayar`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_orderservice_bayar`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_orderservice_bayar`.`saldo_normal` AS `saldo_normal`,
            `jurnal_orderservice_bayar`.`parent` AS `parent`,
            `jurnal_orderservice_bayar`.`id_ref` AS `id_ref`,
            `jurnal_orderservice_bayar`.`nomor_ref` AS `nomor_ref`,
            `jurnal_orderservice_bayar`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_orderservice_bayar`.`tanggal` AS `tanggal`,
            `jurnal_orderservice_bayar`.`keterangan` AS `keterangan`,
            1 AS `kurs_jual`,
            `jurnal_orderservice_bayar`.`nominal` AS `nominal`,
            `jurnal_orderservice_bayar`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_orderservice_bayar' AS `keterangan_lain`");

        $q_jurnal_upahproduksi = DB::table("jurnal_upahproduksi")
            ->selectRaw("`jurnal_upahproduksi`.`rows` AS `rows`,
            `jurnal_upahproduksi`.`id_perkiraan` AS `id_perkiraan`,
            `jurnal_upahproduksi`.`kd_rek_1` AS `kd_rek_1`,
            `jurnal_upahproduksi`.`kode_perkiraan` AS `kode_perkiraan`,
            `jurnal_upahproduksi`.`nama_perkiraan` AS `nama_perkiraan`,
            `jurnal_upahproduksi`.`saldo_normal` AS `saldo_normal`,
            `jurnal_upahproduksi`.`parent` AS `parent`,
            `jurnal_upahproduksi`.`id_ref` AS `id_ref`,
            `jurnal_upahproduksi`.`nomor_ref` AS `nomor_ref`,
            `jurnal_upahproduksi`.`tanggal_ref` AS `tanggal_ref`,
            `jurnal_upahproduksi`.`tanggal` AS `tanggal`,
            `jurnal_upahproduksi`.`keterangan` AS `keterangan`,
            1 AS `kurs_jual`,
            `jurnal_upahproduksi`.`nominal` AS `nominal`,
            `jurnal_upahproduksi`.`tipe_transaksi` AS `tipe_transaksi`,
            'jurnal_upah_produksi' AS `keterangan_lain`");

        $query = $q_jurnal_sumber_dana->unionAll($q_jurnal_pembelian_bayar)->unionAll($q_jurnal_penjualan_bayar)->unionAll($q_jurnal_orderservice_bayar)->unionAll($q_jurnal_upahproduksi);

        return $query;
    }
    //--------------------- End Jurnal Umum --------------------------

    function get_list_produksi2($where){
        /*$query = "SELECT A.*, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, 
                    CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna,
                    CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                    FROM (
                        SELECT vp.*, '' as ket_1, '' as ket_2, 'Bon Bahan' as ket_3, vp.nama_proses as ket_4
                        FROM view_keluar_all vp
                        UNION ALL
                        SELECT vm.*, '' as ket_1, '' as ket_2, 'Laporan Hasil' as ket_3, vm.nama_proses as ket_4
                        FROM view_masuk_all vm
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang
                    LEFT JOIN pro_barangkeluar_barang AS pbb ON A.id_detail=pbb.id
                    LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                    $where
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal";*/
        $query = "SELECT A.*
                    FROM (
                        SELECT vp.id, vp.id_barang, vp.kode_barang, vp.nama_barang, vp.jumlah, vp.masuk, vp.keluar, vp.id_gudang, vp.nama_gudang, vp.tanggal, vp.id_proses, vp.nama_proses, vp.id_komposisi, vp.nama_komposisi, vp.id_satuan, vp.nama_satuan, vp.id_corak, vp.nama_corak, vp.id_motif, vp.nama_motif, vp.id_workorder, vp.no_workorder, vp.id_workorder_barang, vp.no_workorder_barang, vp.id_kualitas, vp.nama_kualitas, vp.id_kualitas_dudul, vp.kualitas_dudul, vp.id_label, vp.nama_label, vp.id_kemasan, vp.nama_kemasan, vp.id_kemasansatuan, vp.nama_kemasansatuan, vp.id_cap, vp.nama_cap, vp.id_kualitas_proses, vp.nama_kualitas_proses, vp.id_kualitas_periksa, vp.nama_kualitas_periksa, vp.id_perubahansatuan, vp.nama_perubahansatuan, vp.id_kemasansepuluh, vp.nama_kemasansepuluh, vp.id_kemasanseratus, vp.nama_kemasanseratus, vp.jumlah_rencana, '' as ket_1, '' as ket_2, 'Bon Bahan' as ket_3, mp.nama_proses as ket_4, vp.nilai_awal, '' ket_5, vp.nilai_akhir, vp.id_detail, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_keluar_all vp
                        LEFT JOIN pro_barangkeluar_barang AS pbb ON vp.id_detail=pbb.id
                        JOIN pro_barangkeluar AS pb ON pbb.id_barangkeluar=pb.id_barangkeluar
                        JOIN ms_proses AS mp ON pb.id_proses=mp.id_proses
                        LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                        UNION ALL
                        SELECT vm.id, vm.id_barang, vm.kode_barang, vm.nama_barang, vm.jum_barang AS jumlah, vm.masuk, vm.keluar, vm.id_gudang, vm.nama_gudang, vm.tanggal, vm.id_proses, vm.nama_proses, vm.id_komposisi, vm.nama_komposisi, vm.id_satuan, vm.nama_satuan, vm.id_corak, vm.nama_corak, vm.id_motif, vm.nama_motif, vm.id_workorder, vm.no_workorder, vm.id_workorder_barang, vm.no_workorder_barang, vm.id_kualitas, vm.nama_kualitas, vm.id_kualitas_dudul, vm.kualitas_dudul, vm.id_label, vm.nama_label, vm.id_kemasan, vm.nama_kemasan, vm.id_kemasansatuan, vm.nama_kemasansatuan, vm.id_kemasansepuluh, vm.nama_kemasansepuluh, vm.id_kemasanseratus, vm.nama_kemasanseratus, vm.id_cap, vm.nama_cap, vm.id_kualitas_proses, vm.nama_kualitas_proses, vm.id_kualitas_periksa, vm.nama_kualitas_periksa, vm.id_perubahansatuan, vm.nama_perubahansatuan, vm.jumlah_rencana,'' as ket_1, '' as ket_2, 'Laporan Hasil' as ket_3, vm.nama_proses as ket_4, '' ket_5, vm.nilai_awal, vm.nilai_akhir, vm.id_detail, mk.id_komposisi AS id_warna, mk.nama AS nama_warna, mk.inisial AS inisial_warna
                        FROM view_masuk_all vm
                        LEFT JOIN ms_warnakomposisi AS mk ON vm.id_komposisi=mk.id_komposisi
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang
                    $where
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal";
        return $query;
    }

    function get_list_produksi3(){
        $query = "SELECT D.*
                    FROM (
                        SELECT vp.id, vp.id_barang, vp.kode_barang, vp.nama_barang, vp.jumlah, vp.masuk, vp.keluar, vp.id_gudang, vp.nama_gudang, vp.tanggal, vp.id_proses, vp.nama_proses, vp.id_komposisi, vp.nama_komposisi, vp.id_satuan, vp.nama_satuan, vp.id_corak, vp.nama_corak, vp.id_motif, vp.nama_motif, vp.id_workorder, vp.no_workorder, vp.id_workorder_barang, vp.no_workorder_barang, vp.id_kualitas, vp.nama_kualitas, vp.id_kualitas_dudul, vp.kualitas_dudul, vp.id_label, vp.nama_label, vp.id_kemasan, vp.nama_kemasan, vp.id_kemasansatuan, vp.nama_kemasansatuan, vp.id_cap, vp.nama_cap, vp.id_kualitas_proses, vp.nama_kualitas_proses, vp.id_kualitas_periksa, vp.nama_kualitas_periksa, vp.id_perubahansatuan, vp.nama_perubahansatuan, vp.id_kemasansepuluh, vp.nama_kemasansepuluh, vp.id_kemasanseratus, vp.nama_kemasanseratus, vp.jumlah_rencana, '' as ket_1, '' as ket_2, 'Bon Bahan' as ket_3, mp.nama_proses as ket_4, vp.nilai_awal, '' ket_5, vp.nilai_akhir, vp.id_detail, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_keluar_all vp
                        LEFT JOIN pro_barangkeluar_barang AS pbb ON vp.id_detail=pbb.id
                        JOIN pro_barangkeluar AS pb ON pbb.id_barangkeluar=pb.id_barangkeluar
                        JOIN ms_proses AS mp ON pb.id_proses=mp.id_proses
                        LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                        UNION ALL
                        SELECT vm.id, vm.id_barang, vm.kode_barang, vm.nama_barang, vm.jum_barang AS jumlah, vm.masuk, vm.keluar, vm.id_gudang, vm.nama_gudang, vm.tanggal, vm.id_proses, vm.nama_proses, vm.id_komposisi, vm.nama_komposisi, vm.id_satuan, vm.nama_satuan, vm.id_corak, vm.nama_corak, vm.id_motif, vm.nama_motif, vm.id_workorder, vm.no_workorder, vm.id_workorder_barang, vm.no_workorder_barang, vm.id_kualitas, vm.nama_kualitas, vm.id_kualitas_dudul, vm.kualitas_dudul, vm.id_label, vm.nama_label, vm.id_kemasan, vm.nama_kemasan, vm.id_kemasansatuan, vm.nama_kemasansatuan, vm.id_kemasansepuluh, vm.nama_kemasansepuluh, vm.id_kemasanseratus, vm.nama_kemasanseratus, vm.id_cap, vm.nama_cap, vm.id_kualitas_proses, vm.nama_kualitas_proses, vm.id_kualitas_periksa, vm.nama_kualitas_periksa, vm.id_perubahansatuan, vm.nama_perubahansatuan, vm.jumlah_rencana,'' as ket_1, '' as ket_2, 'Laporan Hasil' as ket_3, vm.nama_proses as ket_4, '' ket_5, vm.nilai_awal, vm.nilai_akhir, vm.id_detail, mk.id_komposisi AS id_warna, mk.nama AS nama_warna, mk.inisial AS inisial_warna
                        FROM view_masuk_all vm
                        LEFT JOIN ms_warnakomposisi AS mk ON vm.id_komposisi=mk.id_komposisi
                    ) D
                    JOIN ms_gudang as mg ON mg.id_gudang = D.id_gudang
                    LEFT JOIN pro_barangkeluar_barang AS pbb ON D.id_detail=pbb.id
                    LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                    ORDER BY D.id_proses, D.id_komposisi, D.id_satuan, D.id_corak, D.id_motif, D.id_workorder_barang, D.id_kualitas, D.id_kualitas_dudul, D.id_label, D.id_kemasan, D.id_kemasansatuan, D.id_kemasansepuluh, D.id_kemasanseratus, D.id_cap, D.id_kualitas_proses, D.id_kualitas_periksa, D.id_perubahansatuan, D.jumlah_rencana, D.tanggal";

        return $query;
    }

    function get_mutasiproduksi($where){
        $query = "SELECT A.*, mkb2.id_kb as id_kategori, mkb2.nama as nama_kategori, mkb.id_kb as id_subkategori, mkb.nama as nama_subkategori
                    FROM (
                        SELECT vp.id, vp.id_barang, vp.kode_barang, vp.nama_barang, vp.jumlah, vp.masuk, vp.keluar, vp.id_gudang, vp.nama_gudang, vp.tanggal, vp.id_proses, vp.nama_proses, vp.id_komposisi, vp.nama_komposisi, vp.id_satuan, vp.nama_satuan, vp.id_corak, vp.nama_corak, vp.id_motif, vp.nama_motif, vp.id_workorder, vp.no_workorder, vp.id_workorder_barang, vp.no_workorder_barang, vp.id_kualitas, vp.nama_kualitas, vp.id_kualitas_dudul, vp.kualitas_dudul, vp.id_label, vp.nama_label, vp.id_kemasan, vp.nama_kemasan, vp.id_kemasansatuan, vp.nama_kemasansatuan, vp.id_cap, vp.nama_cap, vp.id_kualitas_proses, vp.nama_kualitas_proses, vp.id_kualitas_periksa, vp.nama_kualitas_periksa, vp.id_perubahansatuan, vp.nama_perubahansatuan, vp.id_kemasansepuluh, vp.nama_kemasansepuluh, vp.id_kemasanseratus, vp.nama_kemasanseratus, vp.jumlah_rencana, '' as ket_1, '' as ket_2, 'Bon Bahan' as ket_3, mp.nama_proses as ket_4, '' ket_5, vp.nilai_awal, vp.nilai_akhir, vp.id_detail, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna, pbb.keterangan as ket_proses, vp.id_proses_sekarang, vp.nama_proses_sekarang
                        FROM view_keluar_all vp
                        LEFT JOIN pro_barangkeluar_barang AS pbb ON vp.id_detail=pbb.id
                        JOIN pro_barangkeluar AS pb ON pbb.id_barangkeluar=pb.id_barangkeluar
                        JOIN ms_proses AS mp ON pb.id_proses=mp.id_proses
                        LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                        UNION ALL
                        SELECT vm.id, vm.id_barang, vm.kode_barang, vm.nama_barang, vm.jum_barang AS jumlah, vm.masuk, vm.keluar, vm.id_gudang, vm.nama_gudang, vm.tanggal, vm.id_proses, vm.nama_proses, vm.id_komposisi, vm.nama_komposisi, vm.id_satuan, vm.nama_satuan, vm.id_corak, vm.nama_corak, vm.id_motif, vm.nama_motif, vm.id_workorder, vm.no_workorder, vm.id_workorder_barang, vm.no_workorder_barang, vm.id_kualitas, vm.nama_kualitas, vm.id_kualitas_dudul, vm.kualitas_dudul, vm.id_label, vm.nama_label, vm.id_kemasan, vm.nama_kemasan, vm.id_kemasansatuan, vm.nama_kemasansatuan, vm.id_kemasansepuluh, vm.nama_kemasansepuluh, vm.id_kemasanseratus, vm.nama_kemasanseratus, vm.id_cap, vm.nama_cap, vm.id_kualitas_proses, vm.nama_kualitas_proses, vm.id_kualitas_periksa, vm.nama_kualitas_periksa, vm.id_perubahansatuan, vm.nama_perubahansatuan, vm.jumlah_rencana,'' as ket_1, '' as ket_2, 'Laporan Hasil' as ket_3, vm.nama_proses as ket_4, '' ket_5, vm.nilai_awal, vm.nilai_akhir, vm.id_detail, mk.id_komposisi AS id_warna, mk.nama AS nama_warna, mk.inisial AS inisial_warna, pbb.keterangan as ket_proses, vm.id_proses_sekarang, vm.nama_proses_sekarang
                        FROM view_masuk_all vm
                        LEFT JOIN ms_warnakomposisi AS mk ON vm.id_komposisi=mk.id_komposisi
                        LEFT JOIN pro_barangmasuk_barang AS pbb ON vm.id_detail=pbb.id
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang
                    JOIN ms_barang as mb ON mb.id = A.id_barang
                    JOIN ms_kategori_barang as mkb ON mb.id_kb = mkb.id_kb
                    JOIN ms_kategori_barang as mkb2 ON mkb.induk_kb = mkb2.id_kb
                    $where
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal";
        return $query;
    }

    function get_mutasigudang($where){
        $query = "SELECT A.*, mkb2.id_kb as id_kategori, mkb2.nama as nama_kategori, mkb.id_kb as id_subkategori, mkb.nama as nama_subkategori
                    FROM (
                        SELECT vi.*, '' ket_1, '' ket_2, 'Saldo Awal' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vi.id_komposisi IS NULL OR vi.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vi.id_komposisi IS NULL OR vi.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vi.id_komposisi IS NULL OR vi.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, '' ket_proses
                        FROM view_inv_saldoawal AS vi
                        JOIN inv_saldoawal AS isa ON vi.id = isa.id
                        LEFT JOIN ms_warnakomposisi AS mk ON vi.id_komposisi=mk.id_komposisi
                        UNION ALL
                        SELECT vp.* , CONCAT(pp.no_faktur, pp.sufix_no_faktur) ket_1, mp.nama ket_2, CONCAT('Pembelian dari : ',mp.nama) ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna, '' ket_proses
                        FROM view_penerimaan vp
                        JOIN pem_pembelian pp ON vp.id = pp.id_pembelian
                        JOIN ms_penyedia mp ON pp.id_penyedia = mp.id_penyedia
                        UNION ALL
                        SELECT vt.id, vt.id_barang, vt.kode_barang, vt.nama_barang, vt.jum_barang*(vt.nilai_akhir/vt.nilai_awal) jumlah, 0 masuk, vt.keluar*(vt.nilai_akhir/vt.nilai_awal) keluar, vt.id_gudang, vt.nama_gudang, vt.tanggal, vt.id_proses, vt.nama_proses, vt.id_komposisi, vt.nama_komposisi, vt.id_satuan, vt.nama_satuan, vt.id_corak, vt.nama_corak, vt.id_motif, vt.nama_motif, vt.id_workorder, vt.no_workorder, vt.id_workorder_barang, vt.no_workorder_barang, vt.id_kualitas, vt.nama_kualitas, vt.id_kualitas_dudul, vt.kualitas_dudul, vt.id_label, vt.nama_label, vt.id_kemasan, vt.nama_kemasan, vt.id_kemasansatuan, vt.nama_kemasansatuan, vt.id_kemasansepuluh, vt.nama_kemasansepuluh, vt.id_kemasanseratus, vt.nama_kemasanseratus, vt.id_cap, vt.nama_cap, vt.id_kualitas_proses, vt.nama_kualitas_proses, vt.id_kualitas_periksa, vt.nama_kualitas_periksa, vt.id_perubahansatuan, vt.nama_perubahansatuan, vt.jumlah_rencana, vt.tanggal_full, CONCAT(it.no_faktur, it.faktur) ket_1, vt.nama_gudang ket_2, 'Transfer Keluar' ket_3, '' ket_4, vt.keterangan as ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, vt.keterangan as ket_proses
                        FROM view_transferkeluar_all vt
                        JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
                        LEFT JOIN ms_warnakomposisi AS mk ON vt.id_komposisi=mk.id_komposisi
                        UNION ALL
                        SELECT vt.id, vt.id_barang, vt.kode_barang, vt.nama_barang, vt.jum_barang*(vt.nilai_akhir/vt.nilai_awal) jumlah, vt.masuk*(vt.nilai_akhir/vt.nilai_awal) masuk, vt.keluar*(vt.nilai_akhir/vt.nilai_awal) keluar, vt.id_gudang, vt.nama_gudang, vt.tanggal, vt.id_proses, vt.nama_proses, vt.id_komposisi, vt.nama_komposisi, vt.id_satuan, vt.nama_satuan, vt.id_corak, vt.nama_corak, vt.id_motif, vt.nama_motif, vt.id_workorder, vt.no_workorder, vt.id_workorder_barang, vt.no_workorder_barang, vt.id_kualitas, vt.nama_kualitas, vt.id_kualitas_dudul, vt.kualitas_dudul, vt.id_label, vt.nama_label, vt.id_kemasan, vt.nama_kemasan, vt.id_kemasansatuan, vt.nama_kemasansatuan, vt.id_kemasansepuluh, vt.nama_kemasansepuluh, vt.id_kemasanseratus, vt.nama_kemasanseratus, vt.id_cap, vt.nama_cap, vt.id_kualitas_proses, vt.nama_kualitas_proses, vt.id_kualitas_periksa, vt.nama_kualitas_periksa, vt.id_perubahansatuan, vt.nama_perubahansatuan, vt.jumlah_rencana, vt.tanggal_full, CONCAT(it.no_faktur, it.faktur) ket_1, vt.nama_gudang ket_2, 'Transfer Masuk' ket_3, '' ket_4, vt.keterangan as ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, vt.keterangan as ket_proses
                        FROM view_transfermasuk_all vt
                        JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
                        LEFT JOIN ms_warnakomposisi AS mk ON vt.id_komposisi=mk.id_komposisi
                        UNION ALL
                        SELECT vp.id, vp.id_barang, vp.kode_barang, vp.nama_barang, vp.jumlah, vp.masuk, vp.keluar, vp.id_gudang, vp.nama_gudang, vp.tanggal, vp.id_proses, vp.nama_proses, vp.id_komposisi, vp.nama_komposisi, vp.id_satuan, vp.nama_satuan, vp.id_corak, vp.nama_corak, vp.id_motif, vp.nama_motif, vp.id_workorder, vp.no_workorder, vp.id_workorder_barang, vp.no_workorder_barang, vp.id_kualitas, vp.nama_kualitas, vp.id_kualitas_dudul, vp.kualitas_dudul, vp.id_label, vp.nama_label, vp.id_kemasan, vp.nama_kemasan, vp.id_cap, vp.nama_cap, vp.id_kualitas_proses, vp.nama_kualitas_proses, vp.id_kualitas_periksa, vp.nama_kualitas_periksa,  vp.id_kemasansatuan, vp.nama_kemasansatuan, vp.id_kemasansepuluh, vp.nama_kemasansepuluh, vp.id_kemasanseratus, vp.nama_kemasanseratus, vp.id_perubahansatuan, vp.nama_perubahansatuan, vp.jumlah_rencana, vp.tanggal_full, '' as ket_1, '' as ket_2, 'Bon Bahan' as ket_3, mp.nama_proses as ket_4, '' ket_5, vp.nilai_awal, vp.nilai_akhir, vp.id_detail, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna, pbb.keterangan as ket_proses
                        FROM view_keluar_all vp
                        LEFT JOIN pro_barangkeluar_barang AS pbb ON vp.id_detail=pbb.id
                        JOIN pro_barangkeluar AS pb ON pbb.id_barangkeluar=pb.id_barangkeluar
                        JOIN ms_proses AS mp ON pb.id_proses=mp.id_proses
                        LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                        UNION ALL
                        SELECT vm.id, vm.id_barang, vm.kode_barang, vm.nama_barang, vm.jum_barang AS jumlah, vm.masuk, vm.keluar, vm.id_gudang, vm.nama_gudang, vm.tanggal, vm.id_proses, vm.nama_proses, vm.id_komposisi, vm.nama_komposisi, vm.id_satuan, vm.nama_satuan, vm.id_corak, vm.nama_corak, vm.id_motif, vm.nama_motif, vm.id_workorder, vm.no_workorder, vm.id_workorder_barang, vm.no_workorder_barang, vm.id_kualitas, vm.nama_kualitas, vm.id_kualitas_dudul, vm.kualitas_dudul, vm.id_label, vm.nama_label, vm.id_kemasan, vm.nama_kemasan, vm.id_kemasansatuan, vm.nama_kemasansatuan, vm.id_kemasansepuluh, vm.nama_kemasansepuluh, vm.id_kemasanseratus, vm.nama_kemasanseratus, vm.id_cap, vm.nama_cap, vm.id_kualitas_proses, vm.nama_kualitas_proses, vm.id_kualitas_periksa, vm.nama_kualitas_periksa, vm.id_perubahansatuan, vm.nama_perubahansatuan, vm.jumlah_rencana, vm.tanggal_full, '' as ket_1, '' as ket_2, 'Laporan Hasil' as ket_3, vm.nama_proses as ket_4, '' ket_5, vm.nilai_awal, vm.nilai_akhir, vm.id_detail, mk.id_komposisi AS id_warna, mk.nama AS nama_warna, mk.inisial AS inisial_warna, pbb.keterangan as ket_proses
                        FROM view_masuk_all vm
                        LEFT JOIN ms_warnakomposisi AS mk ON vm.id_komposisi=mk.id_komposisi
                        LEFT JOIN pro_barangmasuk_barang AS pbb ON vm.id_detail=pbb.id
                        UNION ALL
                        SELECT vp.*, CONCAT(pp.no_faktur, pp.sufix_no_faktur) ket_1, mp.nama ket_2, 'Penjualan' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna, '' ket_proses
                        FROM view_penjualan vp
                        JOIN pen_penjualan pp ON vp.id = pp.id_penjualan
                        JOIN ms_pelanggan mp ON pp.id_pelanggan = mp.id_pelanggan
                        UNION ALL
                        SELECT vo.*, io.keterangan ket_1, vo.nama_gudang ket_2, 'Stok Opname' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vo.id_komposisi IS NULL OR vo.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vo.id_komposisi IS NULL OR vo.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vo.id_komposisi IS NULL OR vo.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, '' ket_proses
                        FROM view_opname vo
                        JOIN inv_opname as io ON vo.id = io.id_opname
                        LEFT JOIN ms_warnakomposisi AS mk ON vo.id_komposisi = mk.id_komposisi
                        UNION ALL
                        SELECT vr.*, ir.keterangan ket_1, vr.nama_gudang ket_2, 'Perubahan SPK Keluar' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, '' ket_proses
                        FROM view_rubahspk_keluar as vr
                        JOIN inv_rubahspk as ir ON vr.id = ir.id_rubahspk
                        LEFT JOIN ms_warnakomposisi AS mk ON vr.id_komposisi = mk.id_komposisi
                        UNION ALL
                        SELECT vr.*, ir.keterangan ket_1, vr.nama_gudang ket_2, 'Perubahan SPK Masuk' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, '' ket_proses
                        FROM view_rubahspk_masuk as vr
                        JOIN inv_rubahspk as ir ON vr.id = ir.id_rubahspk
                        LEFT JOIN ms_warnakomposisi AS mk ON vr.id_komposisi = mk.id_komposisi
                        UNION ALL
                        SELECT vk.*, CONCAT(pbk.noAuto, pbk.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Bon Bahan Multi' ket_3, mpr.nama_proses ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna, '' ket_proses
                        FROM view_keluarkemasan as vk
                        JOIN pro_barangkeluar_kemasan as pbk ON vk.id = pbk.id_barangkeluar_kemasan
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses as mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vm.*, CONCAT(pbm.noAuto, pbm.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Laporan Hasil Multi' ket_3, mpr.nama_proses ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna, '' ket_proses
                        FROM view_masukkemasan as vm
                        JOIN pro_barangmasuk_kemasan as pbm ON vm.id = pbm.id_barangmasuk_kemasan
                        JOIN pro_barangkeluar_kemasan as pbk ON pbm.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vke.*, '' ket_1, '' ket_2, 'Pengembalian Barang' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna, '' ket_proses
                        FROM view_kembali as vke
                        JOIN pro_kembali as pk ON vke.id = pk.id_kembali
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang 
                    JOIN ms_barang as mb ON mb.id = A.id_barang
                    JOIN ms_kategori_barang as mkb ON mb.id_kb = mkb.id_kb
                    JOIN ms_kategori_barang as mkb2 ON mkb.induk_kb = mkb2.id_kb               
                    $where
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal, mkb2.id_kb";
        return $query;
    }

    function get_mutasirekap($where, $groupBy, $sum){
        $query = "SELECT A.*, mkb2.id_kb as id_kategori, mkb2.nama as nama_kategori, mkb.id_kb as id_subkategori, mkb.nama as nama_subkategori
                    $sum 
                    FROM (
                        SELECT vi.*, '' ket_1, '' ket_2, 'Saldo Awal' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vi.id_komposisi IS NULL OR vi.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vi.id_komposisi IS NULL OR vi.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vi.id_komposisi IS NULL OR vi.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_inv_saldoawal AS vi
                        JOIN inv_saldoawal AS isa ON vi.id = isa.id
                        LEFT JOIN ms_warnakomposisi AS mk ON vi.id_komposisi=mk.id_komposisi
                        UNION ALL
                        SELECT vp.* , CONCAT(pp.no_faktur, pp.sufix_no_faktur) ket_1, mp.nama ket_2, CONCAT('Pembelian dari : ',mp.nama) ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna
                        FROM view_penerimaan vp
                        JOIN pem_pembelian pp ON vp.id = pp.id_pembelian
                        JOIN ms_penyedia mp ON pp.id_penyedia = mp.id_penyedia
                        UNION ALL
                        SELECT vt.id, vt.id_barang, vt.kode_barang, vt.nama_barang, vt.jum_barang*(vt.nilai_akhir/vt.nilai_awal) jumlah, 0 masuk, vt.keluar*(vt.nilai_akhir/vt.nilai_awal) keluar, vt.id_gudang, vt.nama_gudang, vt.tanggal, vt.id_proses, vt.nama_proses, vt.id_komposisi, vt.nama_komposisi, vt.id_satuan, vt.nama_satuan, vt.id_corak, vt.nama_corak, vt.id_motif, vt.nama_motif, vt.id_workorder, vt.no_workorder, vt.id_workorder_barang, vt.no_workorder_barang, vt.id_kualitas, vt.nama_kualitas, vt.id_kualitas_dudul, vt.kualitas_dudul, vt.id_label, vt.nama_label, vt.id_kemasan, vt.nama_kemasan, vt.id_kemasansatuan, vt.nama_kemasansatuan, vt.id_kemasansepuluh, vt.nama_kemasansepuluh, vt.id_kemasanseratus, vt.nama_kemasanseratus, vt.id_cap, vt.nama_cap, vt.id_kualitas_proses, vt.nama_kualitas_proses, vt.id_kualitas_periksa, vt.nama_kualitas_periksa, vt.id_perubahansatuan, vt.nama_perubahansatuan, vt.jumlah_rencana, CONCAT(it.no_faktur, it.faktur) ket_1, vt.nama_gudang ket_2, 'Transfer Keluar' ket_3, '' ket_4, vt.keterangan as ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_transferkeluar_all vt
                        JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
                        LEFT JOIN ms_warnakomposisi AS mk ON vt.id_komposisi=mk.id_komposisi
                        UNION ALL
                        SELECT vt.id, vt.id_barang, vt.kode_barang, vt.nama_barang, vt.jum_barang*(vt.nilai_akhir/vt.nilai_awal) jumlah, vt.masuk*(vt.nilai_akhir/vt.nilai_awal) masuk, vt.keluar*(vt.nilai_akhir/vt.nilai_awal) keluar, vt.id_gudang, vt.nama_gudang, vt.tanggal, vt.id_proses, vt.nama_proses, vt.id_komposisi, vt.nama_komposisi, vt.id_satuan, vt.nama_satuan, vt.id_corak, vt.nama_corak, vt.id_motif, vt.nama_motif, vt.id_workorder, vt.no_workorder, vt.id_workorder_barang, vt.no_workorder_barang, vt.id_kualitas, vt.nama_kualitas, vt.id_kualitas_dudul, vt.kualitas_dudul, vt.id_label, vt.nama_label, vt.id_kemasan, vt.nama_kemasan, vt.id_kemasansatuan, vt.nama_kemasansatuan, vt.id_kemasansepuluh, vt.nama_kemasansepuluh, vt.id_kemasanseratus, vt.nama_kemasanseratus, vt.id_cap, vt.nama_cap, vt.id_kualitas_proses, vt.nama_kualitas_proses, vt.id_kualitas_periksa, vt.nama_kualitas_periksa, vt.id_perubahansatuan, vt.nama_perubahansatuan, vt.jumlah_rencana, CONCAT(it.no_faktur, it.faktur) ket_1, vt.nama_gudang ket_2, 'Transfer Masuk' ket_3, '' ket_4, vt.keterangan as ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vt.id_komposisi IS NULL OR vt.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_transfermasuk_all vt
                        JOIN inv_transfergudang it ON vt.id = it.id_transfergudang
                        LEFT JOIN ms_warnakomposisi AS mk ON vt.id_komposisi=mk.id_komposisi
                        UNION ALL
                        SELECT vp.id, vp.id_barang, vp.kode_barang, vp.nama_barang, vp.jumlah, vp.masuk, vp.keluar, vp.id_gudang, vp.nama_gudang, vp.tanggal, vp.id_proses, vp.nama_proses, vp.id_komposisi, vp.nama_komposisi, vp.id_satuan, vp.nama_satuan, vp.id_corak, vp.nama_corak, vp.id_motif, vp.nama_motif, vp.id_workorder, vp.no_workorder, vp.id_workorder_barang, vp.no_workorder_barang, vp.id_kualitas, vp.nama_kualitas, vp.id_kualitas_dudul, vp.kualitas_dudul, vp.id_label, vp.nama_label, vp.id_kemasan, vp.nama_kemasan,vp.id_cap, vp.nama_cap, vp.id_kualitas_proses, vp.nama_kualitas_proses, vp.id_kualitas_periksa, vp.nama_kualitas_periksa, vp.id_kemasansatuan, vp.nama_kemasansatuan, vp.id_kemasansepuluh, vp.nama_kemasansepuluh, vp.id_kemasanseratus, vp.nama_kemasanseratus, vp.id_perubahansatuan, vp.nama_perubahansatuan, vp.jumlah_rencana, '' as ket_1, '' as ket_2, 'Bon Bahan' as ket_3, mp.nama_proses as ket_4, '' ket_5, vp.nilai_awal, vp.nilai_akhir, vp.id_detail, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_keluar_all vp
                        LEFT JOIN pro_barangkeluar_barang AS pbb ON vp.id_detail=pbb.id
                        JOIN pro_barangkeluar AS pb ON pbb.id_barangkeluar=pb.id_barangkeluar
                        JOIN ms_proses AS mp ON pb.id_proses=mp.id_proses
                        LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna=mk.id_komposisi
                        UNION ALL
                        SELECT vm.id, vm.id_barang, vm.kode_barang, vm.nama_barang, vm.jum_barang AS jumlah, vm.masuk, vm.keluar, vm.id_gudang, vm.nama_gudang, vm.tanggal, vm.id_proses, vm.nama_proses, vm.id_komposisi, vm.nama_komposisi, vm.id_satuan, vm.nama_satuan, vm.id_corak, vm.nama_corak, vm.id_motif, vm.nama_motif, vm.id_workorder, vm.no_workorder, vm.id_workorder_barang, vm.no_workorder_barang, vm.id_kualitas, vm.nama_kualitas, vm.id_kualitas_dudul, vm.kualitas_dudul, vm.id_label, vm.nama_label, vm.id_kemasan, vm.nama_kemasan, vm.id_kemasansatuan, vm.nama_kemasansatuan, vm.id_kemasansepuluh, vm.nama_kemasansepuluh, vm.id_kemasanseratus, vm.nama_kemasanseratus, vm.id_cap, vm.nama_cap, vm.id_kualitas_proses, vm.nama_kualitas_proses, vm.id_kualitas_periksa, vm.nama_kualitas_periksa, vm.id_perubahansatuan, vm.nama_perubahansatuan, vm.jumlah_rencana,'' as ket_1, '' as ket_2, 'Laporan Hasil' as ket_3, vm.nama_proses as ket_4, '' ket_5, vm.nilai_awal, vm.nilai_akhir, vm.id_detail, mk.id_komposisi AS id_warna, mk.nama AS nama_warna, mk.inisial AS inisial_warna
                        FROM view_masuk_all vm
                        LEFT JOIN ms_warnakomposisi AS mk ON vm.id_komposisi=mk.id_komposisi
                        LEFT JOIN pro_barangmasuk_barang AS pbb ON vm.id_detail=pbb.id
                        UNION ALL
                        SELECT vp.*, CONCAT(pp.no_faktur, pp.sufix_no_faktur) ket_1, mp.nama ket_2, 'Penjualan' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna
                        FROM view_penjualan vp
                        JOIN pen_penjualan pp ON vp.id = pp.id_penjualan
                        JOIN ms_pelanggan mp ON pp.id_pelanggan = mp.id_pelanggan
                        UNION ALL
                        SELECT vo.*, io.keterangan ket_1, vo.nama_gudang ket_2, 'Stok Opname' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vo.id_komposisi IS NULL OR vo.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vo.id_komposisi IS NULL OR vo.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vo.id_komposisi IS NULL OR vo.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_opname vo
                        JOIN inv_opname as io ON vo.id = io.id_opname
                        LEFT JOIN ms_warnakomposisi AS mk ON vo.id_komposisi = mk.id_komposisi
                        UNION ALL
                        SELECT vr.*, ir.keterangan ket_1, vr.nama_gudang ket_2, 'Perubahan SPK Keluar' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_rubahspk_keluar as vr
                        JOIN inv_rubahspk as ir ON vr.id = ir.id_rubahspk
                        LEFT JOIN ms_warnakomposisi AS mk ON vr.id_komposisi = mk.id_komposisi
                        UNION ALL
                        SELECT vr.*, ir.keterangan ket_1, vr.nama_gudang ket_2, 'Perubahan SPK Masuk' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN vr.id_komposisi IS NULL OR vr.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna
                        FROM view_rubahspk_masuk as vr
                        JOIN inv_rubahspk as ir ON vr.id = ir.id_rubahspk
                        LEFT JOIN ms_warnakomposisi AS mk ON vr.id_komposisi = mk.id_komposisi
                        UNION ALL
                        SELECT vk.*, CONCAT(pbk.noAuto, pbk.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Bon Bahan Multi' ket_3, mpr.nama_proses ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna
                        FROM view_keluarkemasan as vk
                        JOIN pro_barangkeluar_kemasan as pbk ON vk.id = pbk.id_barangkeluar_kemasan
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses as mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vm.*, CONCAT(pbm.noAuto, pbm.noAuto_ext) ket_1, CASE WHEN mp.nama_pegawai IS NULL THEN CONCAT(mr.nama , '( Rekanan )') ELSE CONCAT(mp.nama_pegawai , '( Pegawai )') END AS ket_2, 'Laporan Hasil Multi' ket_3, mpr.nama_proses ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna
                        FROM view_masukkemasan as vm
                        JOIN pro_barangmasuk_kemasan as pbm ON vm.id = pbm.id_barangmasuk_kemasan
                        JOIN pro_barangkeluar_kemasan as pbk ON pbm.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
                        LEFT JOIN ms_pegawai mp ON pbk.id_pegawai = mp.id_pegawai
                        LEFT JOIN ms_rekanan mr ON pbk.id_rekanan = mr.id_rekanan
                        LEFT JOIN ms_proses mpr ON pbk.id_proses = mpr.id_proses
                        UNION ALL
                        SELECT vke.*, '' ket_1, '' ket_2, 'Pengembalian Barang' ket_3, '' ket_4, '' ket_5, '' nilai_awal, '' nilai_akhir, '' id_detail,'' id_warna, '' nama_warna, '' inisial_warna
                        FROM view_kembali as vke
                        JOIN pro_kembali as pk ON vke.id = pk.id_kembali
                    ) A
                    JOIN ms_gudang as mg ON mg.id_gudang = A.id_gudang 
                    JOIN ms_barang as mb ON mb.id = A.id_barang
                    JOIN ms_kategori_barang as mkb ON mb.id_kb = mkb.id_kb
                    JOIN ms_kategori_barang as mkb2 ON mkb.induk_kb = mkb2.id_kb               
                    $where 
                    $groupBy
                    ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal, mkb2.id_kb
                    ";
        return $query;
    }


    function get_mutasirekap2($where, $groupBy, $sum){
        $d_query = "SELECT A.* $sum FROM v_mutasi AS A $where $groupBy";
        return $d_query;
    }

    function get_mutasi_saldoawal($where1, $where2, $on_where, $concat){
        $query = "SELECT A.*, CASE WHEN C.stok IS NULL THEN 0 ELSE C.stok END AS stok $concat
                FROM v_mutasi as A
                LEFT JOIN 
                (
                    SELECT B.*, CASE WHEN sum(B.masuk - B.keluar) % 1 = 0 THEN sum(B.masuk - B.keluar) ELSE ROUND(sum(B.masuk - B.keluar), 2) END AS stok
                    FROM v_mutasi as B
                    $where2
                ) C $on_where
                $where1";

        return $query;
    }

    function get_mutasi_detail($where, $concat){
        $d_query = "SELECT A.* , '0' AS stok $concat FROM v_mutasi AS A $where";
        return $d_query;
    }

    function get_mutasitenun_saldoawal($where1, $where2, $on_where, $concat){
        $d_query = "SELECT A.*, CASE WHEN C.stok IS NULL THEN 0 ELSE C.stok END AS stok $concat
                FROM v_mutasi as A
                LEFT JOIN 
                (
                    SELECT B.*, CASE WHEN sum(B.masuk - B.keluar) % 1 = 0 THEN sum(B.masuk - B.keluar) ELSE ROUND(sum(B.masuk - B.keluar), 2) END AS stok
                    FROM v_mutasi as B
                    $where2
                ) C $on_where
                $where1";
        return $d_query;
    }

    function get_mutasitenun_detail($where, $concat){
        $d_query = "SELECT A.*, '0' AS stok $concat FROM v_mutasi AS A $where"; //view_mutasitenun
        return $d_query; 
    }


    function get_mutasitenun_rekap($where, $groupBy, $sum){
        $d_query = "SELECT A.* $sum FROM view_mutasitenun AS A $where $groupBy";
        return $d_query;
    }


    function semua_hasil(){
        $d_query = DB::table("view_masuk_all as v")->join("pro_barangmasuk as pbm", "v.id", "pbm.id_barangmasuk")->join("pro_barangkeluar as pbk", "pbm.id_barangkeluar", "pbk.id_barangkeluar")->leftjoin("ms_pegawai as mp", "pbk.id_pegawai", "mp.id_pegawai")->leftjoin("ms_rekanan as mr", "pbk.id_rekanan", "mr.id_rekanan")->leftjoin("ms_rekanan_kelompok as mrk", "mr.id_kelompok", "mrk.id_kelompok")->leftjoin("ms_lokasi_produksi as mlp", "pbk.id_lokasi", "mlp.id_lokasi")->leftjoin('ms_warnakomposisi as mwk', 'v.id_komposisi', 'mwk.id_komposisi')->select(DB::raw("v.*, pbm.id_barangkeluar, CASE WHEN mp.id_pegawai IS NULL THEN '' ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.nama IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN mrk.id_kelompok IS NULL THEN '' ELSE mrk.id_kelompok END AS id_kelompok, CASE WHEN mrk.nama IS NULL THEN '' ELSE mrk.nama END AS nama_kelompok, mlp.id_lokasi, mlp.nama_lokasi, pbm.noAuto, pbm.noAuto_ext, mwk.inisial inisial_komposisi"));

        return $d_query;
    }

    function semua_hasil_kemasan(){
        $d_query = DB::table("view_masukkemasan_all as v")->join("pro_barangmasuk_kemasan as pbm", "v.id", "pbm.id_barangmasuk_kemasan")->join("pro_barangkeluar_kemasan as pbk", "pbm.id_barangkeluar_kemasan", "pbk.id_barangkeluar_kemasan")->leftjoin("ms_pegawai as mp", "pbk.id_pegawai", "mp.id_pegawai")->leftjoin("ms_rekanan as mr", "pbk.id_rekanan", "mr.id_rekanan")->leftjoin("ms_rekanan_kelompok as mrk", "mr.id_kelompok", "mrk.id_kelompok")->leftjoin("ms_lokasi_produksi as mlp", "pbk.id_lokasi", "mlp.id_lokasi")->leftjoin('ms_warnakomposisi as mwk', 'v.id_komposisi', 'mwk.id_komposisi')->select(DB::raw("v.*, pbm.id_barangkeluar_kemasan, CASE WHEN mp.id_pegawai IS NULL THEN '' ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.nama IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN mrk.id_kelompok IS NULL THEN '' ELSE mrk.id_kelompok END AS id_kelompok, CASE WHEN mrk.nama IS NULL THEN '' ELSE mrk.nama END AS nama_kelompok, mlp.id_lokasi, mlp.nama_lokasi, pbm.noAuto, pbm.noAuto_ext, mwk.inisial inisial_komposisi "));

        return $d_query;
    }

    function get_mutasiview($where = ''){
        $query = "SELECT *, ms.satuan as inisial_satuan
            FROM v_mutasi as A
            JOIN ms_satuan as ms ON A.id_satuan = ms.id_satuan
            $where
            ORDER BY A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.tanggal_full, A.id_kategori";

        return $query;
    }

    function get_saldoawal($where1, $where2){
        $query = "SELECT A.*, CASE WHEN C.stok IS NULL THEN 0 ELSE C.stok END AS stok, CASE WHEN C.stok IS NULL THEN 0 ELSE 1 END AS jenis,  ms.satuan as inisial_satuan
                FROM v_mutasi as A
                LEFT JOIN 
                (
                    SELECT B.*, CASE WHEN sum(B.masuk - B.keluar) % 1 = 0 THEN sum(B.masuk - B.keluar) ELSE ROUND(sum(B.masuk - B.keluar), 2) END AS stok
                    FROM v_mutasi as B
                    $where2
                ) C ON A.id_kategori = C.id_kategori AND A.id_barang = C.id_barang AND A.id_gudang = C.id_gudang AND A.id_proses = C.id_proses AND A.id_komposisi = C.id_komposisi AND A.id_satuan = C.id_satuan AND A.id_corak = C.id_corak AND A.id_motif = C.id_motif AND A.id_workorder = C.id_workorder AND A.id_workorder_barang = C.id_workorder_barang AND A.id_kualitas_dudul = C.id_kualitas_dudul AND A.id_label = C.id_label AND A.id_kemasan = C.id_kemasan AND A.id_kualitas = C.id_kualitas AND A.id_kemasansatuan = C.id_kemasansatuan AND A.id_kemasanseratus = C.id_kemasanseratus AND A.id_cap = C.id_cap AND A.id_kualitas_proses = C.id_kualitas_proses AND A.id_kualitas_periksa = C.id_kualitas_periksa AND A.id_perubahansatuan = C.id_perubahansatuan AND A.jumlah_rencana = C.jumlah_rencana
                JOIN ms_satuan as ms ON C.id_satuan = ms.id_satuan
                $where1";

        return $query;
    }

    function get_produksiview($where, $orderBy = ''){
    
        $query = "SELECT A.*
                FROM (
                    SELECT *
                    FROM v_produksi 
                    
                    UNION ALL
                    
                    SELECT *, '' id_mesin, '' nama_mesin
                    FROM v_produksi_kemasan

                    UNION ALL

                    SELECT *
                    FROM view_opnameproduksi

                    UNION ALL
                    SELECT *
                    FROM view_kembali_all AS v
                    WHERE v.id_proses_sekarang != ''
                ) A
                $where
                ORDER BY $orderBy A.tanggal_full, A.id_proses, A.id_komposisi, A.id_satuan, A.id_corak, A.id_motif, A.id_workorder_barang, A.id_kualitas, A.id_kualitas_dudul, A.id_label, A.id_kemasan, A.id_kemasansatuan, A.id_kemasansepuluh, A.id_kemasanseratus, A.id_cap, A.id_kualitas_proses, A.id_kualitas_periksa, A.id_perubahansatuan, A.jumlah_rencana, A.id_kategori";

        return $query;
    }

    function get_produksiawal($where1, $where2, $on = '', $where3 = ''){
        $query = "SELECT A.*, CASE WHEN C.stok IS NULL THEN 0 ELSE C.stok END AS stok, CASE WHEN C.stok IS NULL THEN 0 ELSE 1 END AS jenis
            FROM (SELECT *
                    FROM v_produksi
                    UNION ALL
                    SELECT v.*
                    FROM view_opnameproduksi v
                    JOIN (SELECT * FROM ms_proses WHERE jenis = 0) mp ON v.id_proses_sekarang = mp.id_proses
                    UNION ALL
                    SELECT *
                    FROM view_kembali_all AS v
                    WHERE v.id_proses_sekarang != ''
                    ) AS A
            LEFT JOIN (
                SELECT B.*, CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END AS stok
                FROM (SELECT *
                    FROM v_produksi
                    UNION ALL
                    SELECT v.*
                    FROM view_opnameproduksi v
                    JOIN (SELECT * FROM ms_proses WHERE jenis = 0) mp ON v.id_proses_sekarang = mp.id_proses
                    UNION ALL
                    SELECT *
                    FROM view_kembali_all AS v
                    WHERE v.id_proses_sekarang != ''
                    ) B
                $where2
            ) C ON A.id_kategori = C.id_kategori AND A.id_barang = C.id_barang AND A.id_proses_sekarang = C.id_proses_sekarang AND A.id_satuan = C.id_satuan AND A.id_corak = C.id_corak AND A.id_motif = C.id_motif AND A.id_workorder = C.id_workorder AND A.id_workorder_barang = C.id_workorder_barang AND A.id_kualitas_dudul = C.id_kualitas_dudul AND A.id_label = C.id_label AND A.id_kemasan = C.id_kemasan AND A.id_kualitas = C.id_kualitas AND A.id_kemasansatuan = C.id_kemasansatuan AND A.id_kemasanseratus = C.id_kemasanseratus AND A.id_cap = C.id_cap AND A.id_kualitas_proses = C.id_kualitas_proses AND A.id_kualitas_periksa = C.id_kualitas_periksa  $on
                $where1

                UNION ALL

                SELECT A.*, CASE WHEN C.stok IS NULL THEN 0 ELSE C.stok END AS stok, CASE WHEN C.stok IS NULL THEN 0 ELSE 1 END AS jenis
            FROM (SELECT *, '' id_mesin, '' nama_mesin
                    FROM v_produksi_kemasan
                    UNION ALL
                    SELECT v.*
                    FROM view_opnameproduksi v
                    JOIN (SELECT * FROM ms_proses WHERE jenis = 1) mp ON v.id_proses_sekarang = mp.id_proses) AS A
            LEFT JOIN (
                SELECT B.*, CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END AS stok
                FROM (SELECT *, '' id_mesin, '' nama_mesin
                    FROM v_produksi_kemasan
                    UNION ALL
                    SELECT v.*
                    FROM view_opnameproduksi v
                    JOIN (SELECT * FROM ms_proses WHERE jenis = 1) mp ON v.id_proses_sekarang = mp.id_proses) B
                $where2
            ) C ON A.id_kategori = C.id_kategori AND A.id_barang = C.id_barang AND A.id_proses_sekarang = C.id_proses_sekarang AND A.id_satuan = C.id_satuan AND A.id_corak = C.id_corak AND A.id_motif = C.id_motif AND A.id_workorder = C.id_workorder AND A.id_workorder_barang = C.id_workorder_barang AND A.id_kualitas_dudul = C.id_kualitas_dudul AND A.id_label = C.id_label AND A.id_kemasan = C.id_kemasan AND A.id_kualitas = C.id_kualitas AND A.id_kemasansatuan = C.id_kemasansatuan AND A.id_kemasanseratus = C.id_kemasanseratus AND A.id_cap = C.id_cap AND A.id_kualitas_proses = C.id_kualitas_proses AND A.id_kualitas_periksa = C.id_kualitas_periksa  $on
                $where1";

        return $query;
    }

    function rekap_bon($select = '', $groupBy){
        $query = "SELECT v.*, CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, 
        CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.nama END AS nama_warna, 
        CASE WHEN pbb.warna IS NULL OR pbb.warna = 0 THEN '' ELSE mk.inisial END AS inisial_warna,
        pbb.keterangan ket_proses, pb.noAuto, pb.noAuto_ext, CASE WHEN pp.id_pegawai IS NULL THEN '' ELSE pp.id_pegawai END AS id_pegawai, CASE WHEN pp.nama_pegawai IS NULL THEN '' ELSE pp.nama_pegawai END AS nama_pegawai, CASE WHEN mkel.id_kelompok IS NULL THEN '' ELSE mkel.id_kelompok END AS id_kelompok, CASE WHEN mkel.id_kelompok IS NULL THEN '' ELSE mkel.nama END AS nama_kelompok,CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN ms.id_mesin IS NULL THEN '' ELSE ms.id_mesin END AS id_mesin, CASE WHEN ms.nama IS NULL THEN '' ELSE ms.nama END AS nama_mesin, CASE WHEN mlp.id_lokasi IS NULL THEN '' ELSE mlp.id_lokasi END AS id_lokasi, CASE WHEN mlp.nama_lokasi IS NULL THEN '' ELSE mlp.nama_lokasi END AS nama_lokasi $select
            FROM view_keluar_all AS v
            JOIN pro_barangkeluar AS pb ON v.id = pb.id_barangkeluar
            LEFT JOIN ms_pegawai AS pp ON pb.id_pegawai = pp.id_pegawai
            LEFT JOIN ms_rekanan AS mr ON mr.id_rekanan = pb.id_rekanan
            LEFT JOIN ms_mesin AS ms ON pb.id_mesin = ms.id_mesin
            JOIN pro_barangkeluar_barang AS pbb ON pbb.id = v.id_detail
            LEFT JOIN ms_warnakomposisi AS mk ON pbb.warna = mk.id_komposisi
            LEFT JOIN ms_gudang AS mg ON v.id_gudang = mg.id_gudang
            LEFT JOIN ms_lokasi_produksi AS mlp ON mg.id_lokasi = mlp.id_lokasi
            LEFT JOIN ms_rekanan_kelompok AS mkel ON mr.id_kelompok = mkel.id_kelompok
            $groupBy";

        return $query;
    }

    function rekap_bonmulti($select, $groupBy){
        $query = "SELECT v.*, CASE WHEN pbb.id_komposisi IS NULL OR pbb.id_komposisi = 0 THEN '' ELSE mk.id_komposisi END AS id_warna, CASE WHEN pbb.id_komposisi IS NULL OR pbb.id_komposisi = 0 THEN '' ELSE mk.nama END AS nama_warna, CASE WHEN pbb.id_komposisi IS NULL OR pbb.id_komposisi = 0 THEN '' ELSE mk.inisial END AS inisial_warna, pbb.keterangan ket_proses, pb.noAuto, pb.noAuto_ext, CASE WHEN pp.id_pegawai IS NULL THEN '' ELSE pp.id_pegawai END AS id_pegawai, CASE WHEN pp.nama_pegawai IS NULL THEN '' ELSE pp.nama_pegawai END AS nama_pegawai, CASE WHEN mkel.id_kelompok IS NULL THEN '' ELSE mkel.id_kelompok END AS id_kelompok, CASE WHEN mkel.id_kelompok IS NULL THEN '' ELSE mkel.nama END AS nama_kelompok, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN ms.id_mesin IS NULL THEN '' ELSE ms.id_mesin END AS id_mesin, CASE WHEN ms.nama IS NULL THEN '' ELSE ms.nama END AS nama_mesin, CASE WHEN mlp.id_lokasi IS NULL THEN '' ELSE mlp.id_lokasi END AS id_lokasi, CASE WHEN mlp.nama_lokasi IS NULL THEN '' ELSE mlp.nama_lokasi END AS nama_lokasi $select
            FROM view_keluarkemasan_all v
            JOIN pro_barangkeluar_kemasan_barang AS pbb ON pbb.id_barangkeluar_kb = v.id_detail
            JOIN pro_barangkeluar_kemasan AS pb ON pbb.id_barangkeluar_kemasan = pb.id_barangkeluar_kemasan
            LEFT JOIN ms_warnakomposisi AS mk ON pbb.id_komposisi = mk.id_komposisi
            LEFT JOIN ms_pegawai AS pp ON pb.id_pegawai = pp.id_pegawai
            LEFT JOIN ms_rekanan AS mr ON mr.id_rekanan = pb.id_rekanan
            LEFT JOIN ms_gudang AS mg ON v.id_gudang = mg.id_gudang
            LEFT JOIN ms_rekanan_kelompok AS mkel ON mr.id_kelompok = mkel.id_kelompok
            LEFT JOIN ms_lokasi_produksi AS mlp ON mg.id_lokasi = mlp.id_lokasi
            LEFT JOIN ms_mesin AS ms ON pb.id_mesin = ms.id_mesin $groupBy";

        return $query;
    }

    function rekap_hasil($select, $groupBy){
        $query = "SELECT v.*, CASE WHEN mwk.id_komposisi IS NULL OR mwk.id_komposisi = 0 THEN '' ELSE mwk.id_komposisi END AS id_warna, CASE WHEN mwk.id_komposisi IS NULL OR mwk.id_komposisi = 0 THEN '' ELSE mwk.nama END AS nama_warna, CASE WHEN mwk.id_komposisi IS NULL OR mwk.id_komposisi = 0 THEN '' ELSE mwk.inisial END AS inisial_warna, pbb.keterangan ket_proses, pbm.noAuto, pbm.noAuto_ext, CASE WHEN mp.id_pegawai IS NULL THEN '' ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN mm.id_mesin IS NULL THEN '' ELSE mm.id_mesin END AS id_mesin, CASE WHEN mm.nama IS NULL THEN '' ELSE mm.nama END AS nama_mesin, mlp.id_lokasi, mlp.nama_lokasi $select
            FROM view_masuk_all as v 
            JOIN pro_barangmasuk as pbm on v.id = pbm.id_barangmasuk 
            JOIN pro_barangmasuk_barang AS pbb ON v.id_detail = pbb.id
            JOIN pro_barangkeluar as pbk on pbm.id_barangkeluar = pbk.id_barangkeluar 
            LEFT JOIN ms_pegawai as mp on pbk.id_pegawai = mp.id_pegawai 
            LEFT JOIN ms_rekanan as mr on pbk.id_rekanan = mr.id_rekanan 
            LEFT JOIN ms_rekanan_kelompok as mrk on mr.id_kelompok = mrk.id_kelompok 
            LEFT join ms_lokasi_produksi as mlp on pbk.id_lokasi = mlp.id_lokasi 
            LEFT join ms_warnakomposisi as mwk on v.id_komposisi = mwk.id_komposisi
            LEFT JOIN ms_mesin AS mm ON pbk.id_mesin = mm.id_mesin $groupBy";

        return $query;
    }

    function rekap_hasilmulti($select, $group){
        $query = "SELECT v.*, CASE WHEN mwk.id_komposisi IS NULL OR mwk.id_komposisi = 0 THEN '' ELSE mwk.id_komposisi END AS id_warna, CASE WHEN mwk.id_komposisi IS NULL OR mwk.id_komposisi = 0 THEN '' ELSE mwk.nama END AS nama_warna, CASE WHEN mwk.id_komposisi IS NULL OR mwk.id_komposisi = 0 THEN '' ELSE mwk.inisial END AS inisial_warna, pbb.keterangan ket_proses, pbm.noAuto, pbm.noAuto_ext, CASE WHEN mp.id_pegawai IS NULL THEN '' ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN mm.id_mesin IS NULL THEN '' ELSE mm.id_mesin END AS id_mesin, CASE WHEN mm.nama IS NULL THEN '' ELSE mm.nama END AS nama_mesin, mlp.id_lokasi, mlp.nama_lokasi $select
            FROM view_masukkemasan_all as v 
            JOIN pro_barangmasuk_kemasan as pbm on v.id = pbm.id_barangmasuk_kemasan
            JOIN pro_barangmasuk_kemasan_barang AS pbb ON v.id_detail = pbb.id_barangmasuk_kb
            JOIN pro_barangkeluar_kemasan as pbk on pbm.id_barangkeluar_kemasan = pbk.id_barangkeluar_kemasan
            LEFT JOIN ms_pegawai as mp on pbk.id_pegawai = mp.id_pegawai 
            LEFT JOIN ms_rekanan as mr on pbk.id_rekanan = mr.id_rekanan 
            LEFT JOIN ms_rekanan_kelompok as mrk on mr.id_kelompok = mrk.id_kelompok 
            LEFT join ms_lokasi_produksi as mlp on pbk.id_lokasi = mlp.id_lokasi 
            LEFT join ms_warnakomposisi as mwk on v.id_komposisi = mwk.id_komposisi
            LEFT JOIN ms_mesin AS mm ON pbk.id_mesin = mm.id_mesin $group";

        return $query;
    }

    function rekap_tanpahasil($select, $group){
        $query = "SELECT p.id_upahproduksi_lainnya id, pu.id id_detail, pu.id_upah id_barang, '' kode_barang, pu.keterangan nama_barang, pu.jumlah jum_barang, pu.jumlah masuk, '0' keluar, '' id_gudang, '' nama_gudang, p.tanggal_hasil tanggal, 
            mp.id_proses, mp.nama_proses, '' id_komposisi, '' nama_komposisi, '' nilai_awal, '' nilai_akhir, msat.id_satuan, msat.nama_satuan, '' id_corak, '' nama_corak, '' id_motif, '' nama_motif, '' id_workorder, '' no_workorder, '' id_workorder_barang, '' no_workorder_barang, '' id_kualitas, '' nama_kualitas, '' id_kualitas_dudul, '' kualitas_dudul, '' id_label, '' nama_label, '' id_kemasan, '' nama_kemasan, '' id_kemasansatuan, '' nama_kemasansatuan, '' id_kemasansepuluh, '' nama_kemasansepuluh, '' id_kemasanseratus, '' nama_kemasanseratus, '' id_cap, '' nama_cap, '' id_kualitas_proses, '' nama_kualitas_proses, '' id_kualitas_periksa, '' nama_kualitas_periksa, '' id_perubahansatuan, '' nama_perubahansatuan, '' jumlah_rencana, mp.id_proses id_proses_sekarang, mp.nama_proses nama_proses_sekarang, p.tanggal_full, '' id_warna, '' nama_warna, '' inisial_warna, '' ket_proses, p.nomor noAuto, '' noAuto_ext, 
                CASE WHEN mpeg.id_pegawai IS NULL THEN '' ELSE mpeg.id_pegawai END AS id_pegawai,
                CASE WHEN mpeg.id_pegawai IS NULL THEN '' ELSE mpeg.nama_pegawai END AS nama_pegawai,
                CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan,
                CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.nama END AS nama_rekanan,
                '' id_mesin, '' nama_mesin, mlp.id_lokasi, mlp.nama_lokasi $select
            FROM pro_upahproduksi_lainnya AS p
            JOIN pro_upahproduksi_lainnya_barang AS pu ON p.id_upahproduksi_lainnya = pu.id_upahproduksi_lainnya
            LEFT JOIN ms_proses AS mp ON p.id_proses = mp.id_proses
            LEFT JOIN ms_pegawai AS mpeg ON p.id_pegawai = mpeg.id_pegawai
            LEFT JOIN ms_rekanan AS mr ON p.id_rekanan = mr.id_rekanan
            LEFT JOIN ms_lokasi_produksi AS mlp ON p.id_lokasi = mlp.id_lokasi
            LEFT JOIN ms_satuan AS msat ON pu.id_satuan = msat.id_satuan $group";

        return $query;
    }

    function get_habisbom($select, $where){
        $query = "SELECT
                CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai,
                CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.nama END AS nama_rekanan,
                    CASE WHEN mrk.nama IS NULL THEN '' ELSE mrk.nama END AS nama_kelompok,
                CASE WHEN mm.nama IS NULL THEN '' ELSE mm.nama END AS nama_mesin,
                CASE WHEN mw.id_komposisi IS NULL THEN '' ELSE mw.inisial END AS inisial_komposisi $select
                FROM pem_workorder_barang AS pwb
                JOIN pro_barangkeluar AS pb ON pb.id_workorder_barang = pwb.id_workorder_barang
                JOIN view_keluar_all AS v ON v.id = pb.id_barangkeluar
                LEFT JOIN ms_pegawai AS mp ON mp.id_pegawai = pb.id_pegawai
                LEFT JOIN ms_rekanan AS mr ON mr.id_rekanan = pb.id_rekanan
                LEFT JOIN ms_rekanan_kelompok AS mrk ON mrk.id_kelompok = mr.id_kelompok
                LEFT JOIN ms_mesin AS mm ON mm.id_mesin = pb.id_mesin
                LEFT JOIN ms_warnakomposisi AS mw ON mw.id_komposisi = v.id_komposisi
                LEFT JOIN ms_gudang AS mg ON mg.id_gudang = v.id_gudang
                LEFT JOIN ms_lokasi_produksi AS mlp ON mg.id_lokasi = mlp.id_lokasi
                $where
                ";
        return $query;
    }

    function get_rekaptenun($select, $where){
        $query = "SELECT B.*, CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END AS stok $select
            FROM v_produksi B
            $where";

        return $query;
    }

    function get_mutasitenun($where_sekir, $where_hasil, $where_sisa, $where_detail, $select_all = '', $select_sekir = '', $select_detail = '', $groupBy_all = '', $orderBy = ""){
        if($orderBy == ""){
            $orderBy = " ORDER BY B.id_sort, B.id_sort_detail, B.tanggal_full";
        }
        $query = "SELECT B.* $select_all
                    FROM (
                        SELECT v.*, 
                        CASE WHEN SUM(A.hasil_stok) IS NULL THEN '0' ELSE SUM(A.hasil_stok) END AS sisa_setor,
                            CONCAT(
                            CASE WHEN v.id_proses_sekarang != '' THEN v.id_proses_sekarang ELSE '0' END,
                        --    CASE WHEN v.id_motif != '' THEN v.id_motif ELSE '0' END, 
                            CASE WHEN v.id_workorder_barang != '' THEN v.id_workorder_barang ELSE '0' END
                            ) AS id_sort,
                            '0' id_sort_detail, 0 jum_akhir, 0 jum_masuk, 0 jum_keluar, v.id_perubahansatuan id_satuan_hasil $select_sekir
                        FROM (SELECT * FROM v_produksi WHERE id_proses_sekarang = 37 UNION ALL SELECT * FROM view_opnameproduksi WHERE id_proses_sekarang = 37) v
                        LEFT JOIN 
                        (
                        
                        SELECT B.*, CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END AS stok, (CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END * CASE WHEN B.jumlah_rencana = '' THEN '20' ELSE B.jumlah_rencana END) hasil_stok
                        FROM (SELECT * FROM v_produksi WHERE id_proses_sekarang = 37 UNION ALL SELECT * FROM view_opnameproduksi WHERE id_proses_sekarang = 37) B
                        $where_sekir
                        
                        UNION ALL
                        
                        SELECT B.*, CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END AS stok, CASE WHEN sum(B.keluar - B.masuk) % 1 = 0 THEN sum(B.keluar - B.masuk) ELSE ROUND(sum(B.keluar - B.masuk), 2) END AS hasil_stok
                        FROM (SELECT * FROM v_produksi UNION ALL SELECT * FROM view_opnameproduksi) B
                        $where_hasil
                        
                        ) A ON A.id_proses_sekarang = v.id_proses_sekarang AND A.id_motif = v.id_motif AND A.id_workorder_barang = v.id_workorder_barang 
                            AND A.id_rekanan = v.id_rekanan AND A.id_pegawai = v.id_pegawai
                        $where_sisa

                        UNION ALL
                        
                        SELECT A.*, 0 sisa_setor,
                        CONCAT(
                        CASE WHEN A.id_proses_sekarang != '' THEN A.id_proses_sekarang ELSE '0' END,
                    --    CASE WHEN A.id_motif != '' THEN A.id_motif ELSE '0' END, 
                        CASE WHEN A.id_workorder_barang != '' THEN A.id_workorder_barang ELSE '0' END
                        ) AS id_sort,
                        '1' id_sort_detail, CASE WHEN A.jumlah != 0 AND A.id_proses = 24 AND A.id_perubahansatuan != '' AND A.jumlah_rencana != '' THEN A.jumlah*A.jumlah_rencana WHEN A.id_proses NOT IN (37) AND pbh.id_keluarhasil IS NOT NULL THEN pbh.hasil WHEN A.id_proses = 37 THEN A.jumlah ELSE 0 END AS jum_akhir,
                        CASE WHEN A.masuk != 0 AND A.id_proses = 37 THEN A.masuk ELSE 0 END AS jum_masuk,
                        CASE WHEN A.keluar != 0 AND A.id_proses = 24 AND A.id_perubahansatuan != '' AND A.jumlah_rencana != '' THEN A.keluar*A.jumlah_rencana WHEN A.id_proses NOT IN (37) AND pbh.id_keluarhasil IS NOT NULL THEN pbh.hasil WHEN A.id_proses = 37 THEN A.keluar ELSE 0 END AS jum_keluar,
                        CASE WHEN A.id_proses = 24 AND A.id_perubahansatuan != '' THEN A.id_perubahansatuan WHEN A.id_proses NOT IN (37) AND pbh.id_keluarhasil IS NOT NULL THEN pbh.id_satuanhasil ELSE A.id_satuan END AS id_satuanhasil $select_detail
                        FROM (SELECT * FROM v_produksi WHERE id_proses_sekarang = 37 UNION ALL SELECT * FROM view_opnameproduksi WHERE id_proses_sekarang = 37) A
                        LEFT JOIN pro_barangkeluar_hasil AS pbh ON pbh.id_barangkeluar = A.id
                            AND pbh.id_barangkeluar_barang = A.id_detail
                        LEFT JOIN ms_satuan AS ms ON pbh.id_satuanhasil = ms.id_satuan
                        $where_detail
                    ) B $groupBy_all
                    $orderBy";

        return $query;
    }

    function get_prosestenun($where, $order = '', $select = ''){
        $query = "SELECT C.*, 
                
                (SUM(C.sekir_keluar) - SUM(C.jumlah_setor+C.sekir_masuk)) sisa,
                CASE WHEN ms.id_satuan IS NULL THEN 'sarung' ELSE ms.satuan END AS inisial_satuan $select
                FROM (
                    SELECT B.*,
                
                    SUM(CASE WHEN B.id_proses = 24 AND B.jumlah_rencana != '' AND B.keluar > 0 THEN B.jumlah_rencana WHEN B.id_proses = 24 AND B.jumlah_rencana = '' THEN B.keluar*20 ELSE 0 END) AS sekir_keluar,
                    SUM(CASE WHEN B.id_proses = 24 AND B.jumlah_rencana != '' AND B.masuk > 0 THEN B.masuk*B.jumlah_rencana WHEN B.id_proses = 24 AND B.jumlah_rencana = '' THEN B.masuk*20 ELSE 0 END) AS sekir_masuk,
                    CASE WHEN B.id_proses = 37 THEN SUM(B.masuk - B.keluar) ELSE '0' END AS jumlah_setor,
                    
                    CASE WHEN B.id_perubahansatuan IS NULL OR B.id_perubahansatuan = '' THEN 11 ELSE B.id_perubahansatuan END AS id_satuanhasil
                    FROM (
                        SELECT * FROM v_produksi WHERE id_proses_sekarang = 37 
                        UNION ALL 
                        SELECT * FROM view_opnameproduksi WHERE id_proses_sekarang = 37
                        UNION ALL
                        SELECT * FROM view_kembali_all WHERE id_proses_sekarang = 37
                    ) B
                    $where
                ) AS C
                LEFT JOIN ms_satuan AS ms ON C.id_satuanhasil = ms.id_satuan
                LEFT JOIN ms_rekanan_kelompok AS mrk ON C.id_rekanan = mrk.id_kelompok
                GROUP BY C.id_workorder_barang, C.id_proses_sekarang
                $order
                "; //ORDER BY C.nama_motif, C.nama_komposisi, C.no_workorder_barang
        return $query;
    }

    function get_prosestenun_detail($select, $where){
        $query = "SELECT B.* $select
                   FROM (
                    SELECT * FROM v_produksi WHERE id_proses_sekarang = 37 
                      UNION ALL 
                      SELECT * FROM view_opnameproduksi WHERE id_proses_sekarang = 37
                      UNION ALL
                      SELECT * FROM view_kembali_all WHERE id_proses_sekarang = 37
                        ) B
                    $where";
        return $query;
    }

    function get_prosesnontenun($where_inside = '', $where_outside = ''){
        $query = "SELECT B.*
                    FROM (
                        SELECT * FROM v_produksi $where_inside
                            UNION ALL 
                            SELECT * FROM view_opnameproduksi $where_inside
                            UNION ALL
                            SELECT * FROM view_kembali_all $where_inside
                        ) B
                    $where_outside";
        return $query;
    }

    function get_sisa_produksilain($where = '', $where2 = ''){

        $d_query = "SELECT CASE WHEN A.id_pegawai IS NULL THEN '' ELSE A.id_pegawai END AS id_pegawai, CASE WHEN A.nama_pegawai IS NULL THEN '' ELSE A.nama_pegawai END AS nama_pegawai, CASE WHEN A.id_rekanan IS NULL THEN '' ELSE A.id_rekanan END AS id_rekanan, CASE WHEN A.sum_masuk IS NULL THEN 0 ELSE SUM(A.sum_masuk) END AS sum_masuk, CASE WHEN A.bayar IS NULL THEN 0 ELSE SUM(A.bayar) END AS bayar
                    FROM (
                    SELECT CASE WHEN mp.id_pegawai IS NULL THEN '' ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.nama IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN mpr.id_proses IS NULL THEN '' ELSE mpr.id_proses END AS id_proses, CASE WHEN mpr.id_proses IS NULL THEN '' ELSE mpr.nama_proses END AS nama_proses, SUM(v.jum_barang) sum_masuk, 0 bayar
                    FROM view_masuk_all AS v
                    JOIN pro_barangmasuk AS pb ON v.id = pb.id_barangmasuk
                    JOIN pro_barangkeluar AS pbk ON pb.id_barangkeluar = pbk.id_barangkeluar
                    LEFT JOIN ms_pegawai AS mp ON pbk.id_pegawai = mp.id_pegawai
                    LEFT JOIN ms_rekanan AS mr ON pbk.id_rekanan = mr.id_rekanan 
                    LEFT JOIN ms_proses AS mpr ON pbk.id_proses = mpr.id_proses $where
                    GROUP BY v.id_proses, v.id_satuan, mp.id_pegawai, mr.id_rekanan
                    UNION ALL
                    SELECT CASE WHEN mp.id_pegawai IS NULL THEN '' ELSE mp.id_pegawai END AS id_pegawai, CASE WHEN mp.nama_pegawai IS NULL THEN '' ELSE mp.nama_pegawai END AS nama_pegawai, CASE WHEN mr.id_rekanan IS NULL THEN '' ELSE mr.id_rekanan END AS id_rekanan, CASE WHEN mr.nama IS NULL THEN '' ELSE mr.nama END AS nama_rekanan, CASE WHEN mpr.id_proses IS NULL THEN '' ELSE mpr.id_proses END AS id_proses, CASE WHEN mpr.id_proses IS NULL THEN '' ELSE mpr.nama_proses END AS nama_proses, 0 sum_masuk, SUM(p2.jumlah) bayar
                    FROM pro_upahproduksi_lainnya AS p
                    JOIN pro_upahproduksi_lainnya_barang AS p2 ON p.id_upahproduksi_lainnya = p2.id_upahproduksi_lainnya
                    LEFT JOIN ms_pegawai AS mp ON p.id_pegawai = mp.id_pegawai
                    LEFT JOIN ms_rekanan AS mr ON p.id_rekanan = mr.id_rekanan
                    LEFT JOIN ms_proses AS mpr ON mpr.id_proses = p.id_proses $where2
                    GROUP BY p.id_proses, mp.id_pegawai, mr.id_rekanan
                    ) A
                    GROUP BY A.id_proses, A.id_pegawai, A.id_rekanan";
        return $d_query;
    }


    // ----------------------- PENGAMBILAN BARANG PRODUKSI -------------------------------------
    function get_barangproduksi($jenis = '', $id_barangmasuk = ''){
        $id_user = Session::get('kode_user');
        //jenis 0 = baru, 1 hapus
        $d_masuk = _detailmasuk($id_barangmasuk)->where("pbb.id_barangmasuk", $id_barangmasuk)->get();
        $c_masuk = $d_masuk->count();

        if($jenis == 0){
            $simpan_detail = array();
            $edit_detail = array();
            $id_edit = array();

            $id_lokasi = ""; $nama_proses = ""; $nama_lokasi = "";
            $id_pengeluaran = ''; $tgl_full = "";
            $pengeluaran_count = 0;
            if($c_masuk > 0){ // jika laporan hasil ditemukan
                foreach($d_masuk as $d){
                    $d_keluar = _detail($d);
                    $c_keluar = $d_keluar->get()->count();

                    if($c_keluar > 0){
                        $pengeluaran_count += $c_keluar;
                    }

                    $d_gunabarang = DB::table("inv_pengeluaran_barang")->where("id_barangmasuk_barang", $d->id)->get();
                    $c_last_data = $d_gunabarang->count();
                    $id_lokasi = $d->id_lokasi;
                    $nama_lokasi = $d->nama_lokasi;
                    $nama_proses = $d->nama_proses;
                    $tgl_full = $d->tanggal_full;
                    

                    if($c_last_data > 0){
                        $id_pengeluaran = $d_gunabarang->where("id_barangmasuk_barang", $d->id)->first()->id_pengeluaran;
                        if($c_keluar > 0){
                            $d_data = $d_keluar->first();
                            $jum_ = ($d->jumlah/$d_data->jumlah)*$d_data->jumlahkeluar;

                            $edit_detail[] = ["id_barang" => $d_data->id_barangkeluar,
                                            "id_gudang" => $d_data->id_gudang,
                                            "jumlah" => $jum_,
                                            "id_satuan" => $d_data->id_satuankeluar,
                                            "id_barangmasuk_barang" => $d->id];
                        $id_edit[] = $d->id;
                        }

                        // echo $d->id." edit";
                    }else{
                        //jika data baru dan master pengeluaran ditemukan
                        if($c_keluar > 0){
                            $d_data = $d_keluar->first();
                            $jum_ = ($d->jumlah/$d_data->jumlah)*$d_data->jumlahkeluar;

                            $simpan_detail[] = ["id_barang" => $d_data->id_barangkeluar,
                                            "id_gudang" => $d_data->id_gudang,
                                            "jumlah" => $jum_,
                                            "keterangan" => $nama_proses,
                                            "id_satuan" => $d_data->id_satuankeluar,
                                            "id_barangmasuk_barang" => $d->id];
                        }

                        // echo $d->id." tambah";
                    }

                    
                }

                $simpan_utama = ["id_lokasi" => $id_lokasi,
                                    "tanggal_full" => $tgl_full,
                                    "keterangan" => "Penggunaan Barang Produksi ".$nama_proses." di ".$nama_lokasi,
                                    "id_user" => $id_user,
                                    "jenis" => 1];
                

                // print_r($simpan_detail);
                // print_r("--- \n");
                // print_r($edit_detail);
                // print_r("\n");

                if(count($simpan_detail) > 0){
                    if($id_pengeluaran == '' && $pengeluaran_count > 0){
                        $id_pengeluaran = DB::table("inv_pengeluaran")->insertGetId($simpan_utama);
                    }
                    foreach($simpan_detail as $id => $d){
                        $simpan_detail[$id] += ["id_pengeluaran" => $id_pengeluaran];
                    }
                    
                    DB::table('inv_pengeluaran_barang')->insert($simpan_detail);
                }

                if(count($edit_detail) > 0){
                    if($id_pengeluaran == ''){
                        $id_pengeluaran = DB::table("inv_pengeluaran")->insertGetId($simpan_utama);
                    }

                    foreach($edit_detail as $id => $d){
                        DB::table("inv_pengeluaran_barang")->where("id_barangmasuk_barang", $id_edit[$id])->update($d);
                    }
                }

            }
            
        }else{
            $id_bm = $d_masuk->pluck('id_barangmasuk')->toArray();

            if(count($id_bm) > 0){
                
                $id_pengeluaran = array_unique($id_bm);
                
                DB::table("inv_pengeluaran")->whereIn("id_pengeluaran", $id_pengeluaran)->delete();
                DB::table("inv_pengeluaran_barang")->whereIn("id_pengeluaran", $id_pengeluaran)->delete();
            }

        }
    }

    function _detail($data = array()){
        $where = array();
        $w = '';
        if(isset($data->id_proses)){
            $where[] = " v.id_proses = '".$data->id_proses."' ";

            if($data->id_proses != '24'){
                $where[] = " v.id_barang = '".$data->id_barang."' ";
            }
        }

        if(isset($data->id_lokasi)){
            $where[] = " v.id_lokasi = '".$data->id_lokasi."' ";
        }

        if(isset($data->id_motif_barang)){
            $where[] = " v.id_corak = ".$data->id_motif_barang." ";
        }

        if(isset($data->id_motif)){
            $where[] = " v.id_motif = '".$data->id_motif."' ";
        }

        $where[] = " v.tanggal <= '".date("Y-m-d")."' ";
        $where[] = " v.is_aktif = '1' ";
        $w = " WHERE ".implode(" AND ", $where);

        $d_next = DB::table(DB::raw("(SELECT * FROM view_penggunaan_produksi AS v $w) as v"))
                ->leftjoin(DB::raw("(SELECT * FROM view_penggunaan_produksi AS v $w) as v2"), function($join){
                    $join->on("v.id_lokasi", "v2.id_lokasi");
                    $join->on("v.id_barang", "v2.id_barang");
                    $join->on("v.id_proses", "v2.id_proses");
                    $join->on("v.id_corak", "v2.id_corak");
                    $join->on("v.id_motif", "v2.id_motif");
                    $join->on("v.id_komposisi", "v2.id_komposisi");
                    $join->on("v.id_satuan", "v2.id_satuan");
                    $join->on("v.id_barangkeluar", "v2.id_barangkeluar");
                    $join->on("v.id_satuankeluar", "v2.id_satuankeluar");
                    $join->on("v.tanggal", "<", "v2.tanggal");
                    $join->whereRaw("v2.id IS NULL");

                    // if(isset($data->id_proses)){
                    //     if($data->id_proses != 24){
                            
                    //     }
                    // }
                })->select(DB::raw("v.*"));

        return $d_next;
    }

    function _detailmasuk(){
        $return = DB::table("pro_barangmasuk AS pb")->join("pro_barangmasuk_barang AS pbb", "pb.id_barangmasuk", "pbb.id_barangmasuk")->join("pro_barangkeluar AS pk", "pb.id_barangkeluar", "pk.id_barangkeluar")->leftjoin("ms_lokasi_produksi AS mlp", "pk.id_lokasi", "mlp.id_lokasi")->leftjoin("ms_proses AS mp", "pk.id_proses", "mp.id_proses")->select(DB::raw("pk.id_proses, pk.id_lokasi, mlp.nama_lokasi, pb.tanggal_full, pbb.*, mp.nama_proses")); //->groupby("pbb.id_barang", "pbb.id_satuan", "pbb.id_motif", "pbb.id_motif_barang"); , SUM(pbb.jumlah) total

        return $return;
    }
    // ----------------------- PENGAMBILAN BARANG PRODUKSI -------------------------------------

