<?php

namespace App\Http\Controllers\keuangan;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Fpdf;
use Excel;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Session;

class LaporanBarangProduksiController extends Controller
{
    //
    public function __construct(){      
        $this->middleware(function ($request, $next) {
        $url = request()->segment(1);
        $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'],['a.active','1']])->orderBy('a.sort_menu','asc')->count();
        if(Session::get('decode') <> 1)
         {
            Session::flash('message', 'Silahkan login dengan username dan password anda.!!');
             return redirect()->to('login');
         }
         if($d_menu == 0){
            Session::flush();
            Session::flash('message', 'Anda Tidak Punya Akses ke Halaman ini.!!');
             return redirect()->to('login');
         }
        return $next($request); 
        });

        $this->session = array('id_gudang'=>Session::get('id_gudang'), 'id_lokasi'=>Session::get('id_lokasi'), 'kode_user' => Session::get('kode_user'), 'kode_group' => Session::get('kode_group'), 'id_proses' => Session::get('id_proses'));
    }

    function index(){
        $id_lokasi = $this->session['id_lokasi'];
        $id_proses = $this->session['id_proses'];

        $data['barang'] = DB::table('ms_barang AS mb')->join('ms_kategori_barang AS mkb', 'mb.id_kb', 'mkb.id_kb')->join('ms_kategori_barang AS mkb2', 'mkb.induk_kb', 'mkb2.id_kb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, ms.id_satuan, ms.nama_satuan'))->orderBy('nama_kategori', 'asc')->orderBy('nama_sub', 'asc')->orderBy('kode_barang', 'asc')->orderBy('nama_barang', 'asc');
        $data['mesin'] = DB::table('ms_mesin')->orderby("nama");
        $data['lokasi'] = DB::table('ms_lokasi_produksi')->orderBy('nama_lokasi', 'asc');
        if($id_lokasi != 0){
            $data['lokasi'] = $data['lokasi']->where('id_lokasi', explode(", ", $id_lokasi));
        }
        $data['kategori'] = DB::table('ms_kategori_barang')->where('induk_kb','=','0');
        $data['gudang'] = DB::table('ms_gudang')->orderBy('nama_gudang', 'asc');
        $data['warna'] = DB::table('ms_warnakomposisi')->orderBy('nama', 'asc');
        $data['proses'] = DB::table('ms_proses')->where('parent', '!=', '0');
        if($id_proses != 0){
            $data['proses']->whereIn('id_proses', explode(",", $id_proses));
        }
        $data['motif'] = DB::table('ms_motif')->orderBy('nama_motif','asc');
        $data['corak'] = DB::table('ms_motif_barang')->orderBy('motif_keterangan','asc');

        $data['pegawai'] = DB::table('ms_pegawai')->orderBy('nama_pegawai', 'asc')->get();
        $data['rekanan'] = DB::table('ms_rekanan as mr')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->select(DB::raw('mr.*, mrk.nama nama_kelompok'))->orderby('mr.nama', 'asc')->get();
        $data['makelar'] = DB::table('ms_rekanan_kelompok')->orderby('nama', 'asc')->get();
        $data['mesin'] = $data['mesin']->get();
        return view('keuangan.hargabarang.HargaBarangList')->with('data', $data);
    }

    function get_barang(Request $request){
        $id_kategori = $request->get('id');
        $nama        = $request->get('nama');
        $jenis       = $request->get('jenis');

        $d_query = DB::table('ms_barang AS mb')->join('ms_kategori_barang AS mkb', 'mb.id_kb', 'mkb.id_kb')->join('ms_kategori_barang AS mkb2', 'mkb.induk_kb', 'mkb2.id_kb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, ms.id_satuan, ms.nama_satuan'))->orderBy('mkb2.nama', 'asc')->orderBy('mkb.nama', 'asc')->orderBy('kode_barang', 'asc')->orderBy('nama_barang', 'asc');
        $d_query_all = DB::table('ms_barang AS mb')->join('ms_kategori_barang AS mkb', 'mb.id_kb', 'mkb.id_kb')->join('ms_kategori_barang AS mkb2', 'mkb.induk_kb', 'mkb2.id_kb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, ms.id_satuan, ms.nama_satuan'))->orderBy('mkb2.nama', 'asc')->orderBy('mkb.nama', 'asc')->orderBy('kode_barang', 'asc')->orderBy('nama_barang', 'asc');

        if($id_kategori != '' && $id_kategori != 0){
            $d_query = $d_query->where('mkb2.id_kb','=',$id_kategori);
        }
        
        if($d_query->count() > 0 AND $id_kategori <> '' AND $jenis == 1){
            $arr['data'] = $d_query->get();
            // $arr['data_all'] = array();
            $arr['status'] = array('1');
        }else if($d_query_all->count() > 0 AND $id_kategori == '' AND $jenis == 0){
            $arr['data'] = $d_query_all->get();
            // $arr['data_all'] = array();
            $arr['status'] = array('2');
        }else{
            $arr['data'] = array();
            // $arr['data_all'] = array();
            $arr['status'] = array('0');
        }

        return response()->json($arr);
    }

    function get_bymakelar(Request $request){
        $req = $request->all();

        $id_makelar = $req['id_makelar'];

        $query = DB::table("ms_rekanan_kelompok AS mrk")->leftjoin("ms_rekanan AS mr", "mrk.id_kelompok", "mr.id_kelompok")->select(DB::raw("mr.*, mrk.id_kelompok, mrk.nama nama_rekanan"))->orderby('mr.nama')->orderby('mrk.nama');

        if($id_makelar != '' && $id_makelar != 0){
            $query->where("mrk.id_kelompok", $id_makelar);
        }

        return response()->json($query->get());

    }

    function mutasirekapbaru(Request $request){
        $req = $request->all();

        $add_where = "";

        $where = array();
        $barang = array();
        $kategori = '';
        $lokasi = "";
        $tanggal = ""; 
        $gudang = ["nama_gudang" => '', "lokasi_gudang" => ''];
        $warna = ["nama" => '', "inisial" => ''];
        $proses = '';
        $text_orderby = " ORDER BY ";
        $orderby_proses = '';
        $concat_rekap = '';
        $proses_harga = '';
        $join_harga = "";
        $join_stok = "";

        $where_harga = array();

        $jenis = 0;        
        if(isset($req['jenis'])){
            $jenis = $req['jenis'];
        }

        if(count($req) > 0){
            $add_where = "WHERE ";
        }


        if(isset($req['id_kategoribarang'])){
            if($req['id_kategoribarang'] != ''){
                $where[] = "A.id_kategori = ".$req['id_kategoribarang']." ";

                $kategori =  DB::table('ms_kategori_barang')->where('id_kb','=',$req['id_kategoribarang'])->first()->nama;
            }
        }

        if(isset($req['id_barang'])){
            if($req['id_barang'] != ''){
            $where[] = "A.id_barang = ".$req['id_barang']." ";
            
            $barang =  DB::table('ms_barang AS mb')->join('ms_kategori_barang AS mkb', 'mb.id_kb', 'mkb.id_kb')->join('ms_kategori_barang AS mkb2', 'mkb.induk_kb', 'mkb2.id_kb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, ms.id_satuan, ms.nama_satuan'))->where('mb.id', $req['id_barang'])->first();
            }       
        }
        
        if(isset($req['id_lokasi'])){
            $where[] = "A.id_lokasi = ".$req['id_lokasi']." ";
            
            $lokasi = DB::table('ms_lokasi_produksi')->where('id_lokasi', $req['id_lokasi'])->first()->nama_lokasi;
        }

        if(isset($req['tgl_awal'])){
            $tgl_awal = tgl_full($req['tgl_awal'], '2');
            $tgl_akhir = tgl_full($req['tgl_awal'], '2');
            
            $tgl_where_detail = "AND A.tanggal BETWEEN '$tgl_awal' AND '$tgl_akhir' ";

            $tgl_where_awal = "AND A.tanggal < '$tgl_awal'";

            $where_harga[] = " tanggal <= '$tgl_awal' ";

        }

        if(isset($req['id_gudang'])){
            if($req['id_gudang'] != ''){
                $where[] = "A.id_gudang = ".$req['id_gudang']." ";

                $d_gudang = DB::table('ms_gudang as mg')->join('ms_lokasi_produksi as mlp', 'mg.id_lokasi', 'mlp.id_lokasi')->where('mg.id_gudang', $req['id_gudang'])->select(DB::raw("mg.*, mlp.nama_lokasi"))->first();
                $gudang = ['nama_gudang' => $d_gudang->nama_gudang, 'lokasi_gudang' => $d_gudang->nama_lokasi];
            }
        }

        if(isset($req['id_warna'])){
            if($req['id_warna'] != ''){
                $where[] = "A.id_komposisi = ".$req['id_warna']." ";
                $d_warna = DB::table('ms_warnakomposisi')->where('id_komposisi', $req['id_warna'])->first();
                $warna = ["nama" => $d_warna->nama, "inisial" => $d_warna->inisial];
            }else{
                if(isset($req['id_gudang'])){
                    if($req['id_gudang'] == 4){
                        $where[] = "A.id_komposisi = '' ";
                        $where[] = "A.id_motif = '' ";
                        $where[] = "A.id_corak = '' "; 
                    }
                }
            }
        }

        if(isset($req['id_proses'])){
            if($req['id_proses'] != ''){

                if($req['id_proses'] == 0){
                    $where[] = "A.id_proses = '' ";
                    $proses_harga = "id_proses = ''";
                    $proses = '-';
                }else{
                    $where[] = "A.id_proses = ".$req['id_proses']." ";
                    $proses_harga = "id_proses = '".$req['id_proses']."' ";
                    $proses = DB::table('ms_proses')->where('id_proses', $req['id_proses'])->first()->nama_proses;
                }

                if(in_array($req['id_proses'], setup_benang()) || $req['id_proses'] == 0){
                    $orderby_proses = " A.nama_subkategori, A.nama_barang";
                    $groupBy = "GROUP BY A.id_barang, A.id_proses, A.id_komposisi, A.id_satuan, A.id_kategori";
                    $concat_rekap = ", CONCAT(A.nama_subkategori) id_sort";
                    
                }else if(in_array($req['id_proses'], setup_corak())){
                    if($req['id_proses'] == 24){
                        $orderby_proses = " A.nama_motif";
                        $groupBy = "GROUP BY A.id_motif, A.id_proses, A.id_satuan, A.id_kategori"; //A.id_komposisi,
                        $concat_rekap = ", CONCAT(A.nama_subkategori, CAST(A.nama_motif AS UNSIGNED)) id_sort";
                    }else{
                        $orderby_proses = " A.nama_corak";
                        $groupBy = "GROUP BY A.id_corak, A.id_proses, A.id_komposisi, A.id_satuan, A.id_kategori";
                        $concat_rekap = ", CONCAT(A.nama_subkategori, CAST(A.nama_corak AS UNSIGNED)) id_sort";
                    }
                }else{
                    $orderby_proses = " A.nama_barang, A.nama_motif";
                    $groupBy = "GROUP BY A.id_barang, A.id_motif, A.id_proses, A.id_satuan, A.id_kategori"; //A.id_komposisi,
                    $concat_rekap = ", CONCAT(A.nama_subkategori, CAST(A.nama_motif AS UNSIGNED)) id_sort";
                }

                $orderby_proses .= ", A.nama_komposisi";
                $join_harga = $this->_joinharga("v", "v2", $req['id_proses']);
                $join_stok = $this->_joinstok("A", "B", $req['id_proses']);
            }else{
                $orderby_proses = " A.nama_subkategori, A.nama_barang, A.nama_corak, A.nama_motif, A.nama_komposisi";
                $groupBy = "GROUP BY A.id_barang, A.id_corak, A.id_motif, A.id_proses, A.id_komposisi, A.id_satuan";
                $concat_rekap = ", CONCAT(A.nama_subkategori) id_sort";
                $join_harga = $this->_joinharga("v", "v2", "");
                $join_stok = $this->_joinstok("A", "B", "");
            }
        }            

        if($proses_harga != ''){
            $where_harga[] = $proses_harga;
        }

        $where_h = "";
        if(count($where_harga) > 0){
            $where_h = " AND ".implode(" AND ", $where_harga);
        }


        $data['induk'] = array("barang" => $barang, 'nama_lokasi' => $lokasi, 'tanggal_awal' => $tgl_awal, 'tanggal_akhir' => $tgl_akhir, 'nama_gudang' => $gudang['nama_gudang'], 'lokasi_gudang' => $gudang['lokasi_gudang'], 'nama_komposisi' => $warna['nama'], 'inisial_komposisi' => $warna['inisial'], 'nama_proses' => $proses, 'kategori' => $kategori);
        $data['where'] = implode(" AND ", $where);

        if($jenis == 0){
            
        }else if($jenis == 1){
            $where_detail = $add_where.$data['where'].$tgl_where_detail.$groupBy; 
            $field_sum_detail = ", SUM(A.masuk) AS jumlah_masuk, SUM(A.keluar) AS jumlah_keluar, '0' AS jumlah_awal";    
            $where_awal = $add_where.$data['where'].$tgl_where_awal.$groupBy;
            $field_sum_awal = ", '0' AS jumlah_masuk, '0' AS jumlah_keluar, SUM(A.masuk-A.keluar) AS jumlah_awal";

            $query['saldo_awal'] = get_mutasirekap2($where_awal, '', $field_sum_awal);
            $query['detail'] = get_mutasirekap2($where_detail, '', $field_sum_detail);
            $query_mutasi = implode(" UNION ALL ", $query);
            $query_rekap_mutasi[] = "SELECT A.*, mk.inisial as inisial_komposisi, SUM(A.jumlah_masuk) AS mutasi_masuk, SUM(A.jumlah_keluar) AS mutasi_keluar, SUM(A.jumlah_awal) AS mutasi_awal, ms.satuan as inisial_satuan ".$concat_rekap." , 0 id_sort_detail, SUM((A.jumlah_awal+A.jumlah_masuk)-A.jumlah_keluar) jumlah_mutasi FROM ($query_mutasi) AS A LEFT JOIN ms_warnakomposisi AS mk ON A.id_komposisi=mk.id_komposisi LEFT JOIN ms_satuan as ms ON ms.id_satuan = A.id_satuan $groupBy";
            //$query_rekap_mutasi[] = "SELECT A.*, mk.inisial as inisial_komposisi, SUM(A.jumlah_masuk) AS mutasi_masuk, SUM(A.jumlah_keluar) AS mutasi_keluar, SUM(A.jumlah_awal) AS mutasi_awal, ms.satuan as inisial_satuan ".$concat_rekap." , 1 id_sort_detail, SUM((A.jumlah_awal+A.jumlah_masuk)-A.jumlah_keluar) jumlah_mutasi FROM ($query_mutasi) AS A LEFT JOIN ms_warnakomposisi AS mk ON A.id_komposisi=mk.id_komposisi LEFT JOIN ms_satuan as ms ON ms.id_satuan = A.id_satuan GROUP BY A.id_proses, A.id_satuan, A.id_subkategori";

            $query_last = "SELECT A.*, A.nama_subkategori kategori, CASE WHEN B.id_harga IS NULL THEN '0' ELSE B.harga END AS harga_satuan, CASE WHEN B.id_harga IS NULL THEN '0' ELSE B.harga END * A.jumlah_mutasi AS jumlah_harga FROM (".implode(" UNION ALL ", $query_rekap_mutasi).") A LEFT JOIN (
                SELECT v.*
                FROM (SELECT * FROM view_hargabarang WHERE jenis = 0 ".$where_h.") AS v
                LEFT JOIN (SELECT * FROM view_hargabarang WHERE jenis = 0 ".$where_h.") AS v2 ON $join_harga
                WHERE v2.id_harga IS NULL
            ) B ON $join_stok ORDER BY A.id_sort, A.id_sort_detail, $orderby_proses";

            // join_harga v.id_barang = v2.id_barang AND v.id_proses = v2.id_proses AND v.id_corak = v2.id_corak AND v.id_motif = v2.id_motif AND v.id_satuan = v2.id_satuan AND v.jenis = v2.jenis AND v.id_komposisi = v2.id_komposisi AND v.tanggal < v2.tanggal
            
            $data['detail'] = barang_byproses(DB::select($query_last), 101, $proses);

        }else{

        }

        switch ($jenis) {
            case '0':
                $html = "MutasiRekapPerKategoriHargaCetak"; //Mutasirekapfilter2Cetak
                break;
            case '1':
                $html = "MutasiRekapPerKategoriHargaCetak";
                break;
            default:
                $html = "MutasiRekapPerKategoriHargaCetak";
                break;
        }

        // print_r($data); exit();

        if(isset($req['is_excelrekapgudang'])){
            if($req['is_excelrekapgudang'] == 1){
                $barang = 'Semua Barang';
                if($data['induk']['barang'] != false){
                    $barang = $data['induk']['barang']->nama_barang;
                }
                Excel::create("Mutasi Rekap _".$data['induk']['nama_gudang']."_".$barang."_".date_range($data['induk']['tanggal_awal'], $data['induk']['tanggal_akhir']), function($excel) use ($html, $data){
                    $excel->sheet("Sheet 1", function($sheet) use ($html, $data){
                        $sheet->loadView('keuangan.hargabarang.cetak.'.$html."_Excel", $data);
                    });
                })->export("xlsx");
                // return response(view('keuangan.hargabarang.cetak.'.$html."_Excel")->with('data', $data));
            }else{
                require('fpdf181/Mc_table.php');
                return response(view('keuangan.hargabarang.cetak.'.$html)->with('data', $data))->header('Content-Type', 'application/pdf');
            }
        }
    }

    function mutasiproduksi_(Request $request){
        $req = $request->all();

        $add_where = "";

        $wheres = array();
        $where = array();
        $where2 = array();
        $wherev = array();
        $barang = array();
        $lokasi = "";
        $tanggal = ""; 
        $gudang = ["nama_gudang" => '', "lokasi_gudang" => ''];
        $warna = ["nama" => '', "inisial" => ''];
        $proses = '';
        $motif = "";
        $corak = "";
        $arr = array();
        $group_celupA = '';
        $group_celupB = '';
        $id_proses = '';
        $tgl_awal = '';
        $where_rekap = array();
        $on = ' AND A.id_komposisi = C.id_komposisi';
        $kategori = '';
        $orderby_proses = '';
        $pegawai = '';
        $rekanan = '';
        $kelompok = '';
        $mesin = '';
        $helper_tgl_awal = '';
        $helper_tgl_between = '';
        $proses_harga = '';
        $where_harga = array();
        $where_tenun = array();
        $where_tenun = array();

        $jenis = 0;
        if(isset($req['jenis'])){
            $jenis = $req['jenis'];
        }

        if(count($req) > 0){
            $add_where = "WHERE ";
        }

        if(isset($req['id_kategoribarang'])){
            if($req['id_kategoribarang'] != ''){
                $where[] = "A.id_kategori = ".$req['id_kategoribarang']." ";
                $where_tenun[] = "B.id_kategori = ".$req['id_kategoribarang']." ";

                $kategori =  DB::table('ms_kategori_barang')->where('id_kb','=',$req['id_kategoribarang'])->first()->nama;
            }
        }

        if(isset($req['id_barang'])){
            if($req['id_barang'] != ''){
            $where[] = "A.id_barang = ".$req['id_barang']." ";
            $where_tenun[] = "B.id_barang = ".$req['id_barang']." ";
            
            $barang =  DB::table('ms_barang AS mb')->join('ms_kategori_barang AS mkb', 'mb.id_kb', 'mkb.id_kb')->join('ms_kategori_barang AS mkb2', 'mkb.induk_kb', 'mkb2.id_kb')->join('ms_satuan AS ms', 'mb.id_satuan', 'ms.id_satuan')->select(DB::raw('mb.*, mkb2.id_kb AS id_kategori, mkb2.nama nama_kategori, mkb.id_kb id_sub, mkb.nama nama_sub, ms.id_satuan, ms.nama_satuan'))->where('mb.id', $req['id_barang'])->first();
            }
        }
        
        if(isset($req['id_lokasistok'])){
            $where[] = "A.id_lokasi = ".$req['id_lokasistok']." ";
            $where_tenun[] = "B.id_lokasi = ".$req['id_lokasistok']." ";

            $lokasi = DB::table('ms_lokasi_produksi')->where('id_lokasi', $req['id_lokasistok'])->first()->nama_lokasi;
        }

        if(isset($req['tgl_awal'])){
            $tgl_awal = tgl_full($req['tgl_awal'], '2');
            $tgl_akhir = tgl_full($req['tgl_awal'], '2');
            
            // $where[] = "A.tanggal BETWEEN '$tgl_awal' AND '$tgl_akhir' ";
            $where[] = "A.tanggal <= '$tgl_awal' ";
            $where_tenun[] = "B.tanggal <= '$tgl_awal' ";
        }

        if(isset($req['id_motif'])){
            if($req['id_motif'] != ''){
                $where[] = "A.id_motif = ".$req['id_motif']." ";
                $where_tenun[] = "B.id_motif = ".$req['id_motif']." ";

                $motif = DB::table('ms_motif')->where('id_motif',$req['id_motif'])->first()->nama_motif;
            }
        }

        if(isset($req['id_corak'])){
            if($req['id_corak'] != ''){
                $where[] = "A.id_corak = ".$req['id_corak']." ";
                $where_tenun[] = "B.id_corak = ".$req['id_corak']." ";

                $corak = DB::table('ms_motif_barang')->where('id_motif_barang',$req['id_corak'])->first()->motif_keterangan;
            }
        }

        if(isset($req['id_warna'])){
            if($req['id_warna'] != ''){
                $where[] = "A.id_warna = ".$req['id_warna']." ";
                $where_tenun[] = "B.id_warna = ".$req['id_warna']." ";

                
                $d_warna = DB::table('ms_warnakomposisi')->where('id_komposisi', $req['id_warna'])->first();
                $warna = ["nama" => $d_warna->nama, "inisial" => $d_warna->inisial];

                // if(isset($req['id_proses'])){
                //     if($req['id_proses'] == '18'){
                //         $where[] = "A.id_warna = ".$req['id_warna']." ";
                //     }
                // }
            }
        }

        if(isset($req['id_proses'])){
            if($req['id_proses'] != ''){
                $where[] = "A.id_proses_sekarang = ".$req['id_proses']." ";
                $where_tenun[] = "B.id_proses_sekarang = ".$req['id_proses']." ";
                
                $proses = DB::table('ms_proses')->where('id_proses', $req['id_proses'])->first()->nama_proses;

                if($req['id_proses'] == '18'){
                    $group_celupA = $this->groupby_pro_celup('A');
                    $group_celupB = $this->groupby_pro_celup('B');
                    $on = '';
                }else{
                    $group_celupA = $this->groupby_pro('A');
                    $group_celupB = $this->groupby_pro('B');
                }

                $id_proses = $req['id_proses'];
                // $proses_harga = "id_proses = '".$req['id_proses']."' ";

                $join_harga = $this->_joinharga("v", "v2", $req['id_proses']);
                $join_stok = $this->_joinstok("A", "B", $req['id_proses']);
            }else{
                $proses = "Semua Proses";
                $group_celupA = $this->groupby_pro('A');
                $group_celupB = $this->groupby_pro('B');

                $join_harga = $this->_joinharga("v", "v2", "");
                $join_stok = $this->_joinstok("A", "B", "");
            }

            if(in_array($req['id_proses'], setup_benang())){
                $orderby_proses = " ORDER BY A.nama_subkategori, A.nama_barang";
            }else if(in_array($req['id_proses'], setup_corak())){
                $orderby_proses = " ORDER BY A.nama_corak, A.nama_motif";
            }else{

            }

            $orderby_proses .= ", A.nama_komposisi";

        }else{
            $proses = "Semua Proses";
            $group_celupA = $this->groupby_pro('A');
            $group_celupB = $this->groupby_pro('B');
        }

        if(isset($req['id_pegawai_proses'])){
            if($req['id_pegawai_proses'] != 0){
                $where[] = "A.id_pegawai = ".$req['id_pegawai_proses']." ";
                $where_tenun[] = "B.id_pegawai = ".$req['id_pegawai_proses']." ";

                $pegawai = DB::table('ms_pegawai')->where('id_pegawai', $req['id_pegawai_proses'])->first()->nama_pegawai;
            }else if($req['id_pegawai_proses'] == '0'){
                $pegawai = "Semua Pegawai";
                $where[] = "A.id_pegawai != '' ";
                $where_tenun[] = "B.id_pegawai != ''";
            }else{
                $pegawai = "";
            }
        }

        if(isset($req['id_makelar_proses'])){
            if($req['id_makelar_proses'] != 0){
                $d_petenun = DB::table('ms_rekanan as mr')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->where("mrk.id_kelompok", $req['id_makelar_proses'])->select(DB::raw("mr.*, mrk.nama nama_kelompok"))->get();
                $d_count = $d_petenun->count();
                $d_kelompok = $d_petenun->first()->nama_kelompok;
                $d_petenun = $d_petenun->pluck('id_rekanan')->toArray();
                
                $where[] = "A.id_rekanan IN ('".implode("', '", $d_petenun)."') ";
                $where_tenun[] = "B.id_rekanan IN ('".implode("', '", $d_petenun)."') ";
                
                $kelompok = $d_kelompok;
            }else if($req['id_rekanan_proses'] == 0){
                $kelompok = "Semua Makelar";
            }else{

            }
        }else{

        }

        if(isset($req['id_rekanan_proses'])){
            if($req['id_rekanan_proses'] != 0){
                $where[] = "A.id_rekanan = ".$req['id_rekanan_proses']." ";
                $where_tenun[] = "B.id_rekanan = ".$req['id_rekanan_proses']." ";
                
                $d_data = DB::table('ms_rekanan as mr')->leftjoin('ms_rekanan_kelompok as mrk', 'mr.id_kelompok', 'mrk.id_kelompok')->where('id_rekanan', $req['id_rekanan_proses'])->select(DB::raw("mr.*, mrk.nama nama_kelompok"))->first();
                $rekanan = $d_data->nama;
                if($kelompok == ''){
                    $kelompok = $d_data->nama_kelompok;
                }
            }else if($req['id_rekanan_proses'] == '0'){
                $where[] = "A.id_rekanan != '' ";

                $rekanan = "Semua Rekanan";
            }else{
                $rekanan = "";
            }  
        }

        if(isset($req['id_mesin_proses'])){
            if($req['id_mesin_proses'] != 0){
                $where[] = "A.id_mesin = ".$req['id_mesin_proses']." ";

                $where_tenun[] = "B.id_mesin = ".$req['id_mesin_proses']." ";
                $mesin = DB::table('ms_mesin')->where('id_mesin', $req['id_mesin_proses'])->first()->nama;
            }else if($req['id_mesin_proses'] == '0'){
                $mesin = "Semua Mesin";
                $where[] = "A.id_mesin != '' ";
                $where_tenun[] = "B.id_mesin != '' ";
            }else{
                $mesin = "";
            }
        }




        $data['induk'] = array("barang" => $barang, 'nama_lokasi' => $lokasi, 'tanggal_awal' => $tgl_awal, 'tanggal_akhir' => $tgl_akhir, 'nama_gudang' => $gudang['nama_gudang'], 'lokasi_gudang' => $gudang['lokasi_gudang'], 'nama_komposisi' => $warna['nama'], 'inisial_komposisi' => $warna['inisial'], 'nama_proses' => $proses, "nama_motif" => $motif, "nama_corak" => $corak, "pegawai" => $pegawai, "rekanan" => $rekanan, "kelompok" => $kelompok, "mesin" => $mesin);
        $wherev[] = "v.id_proses_sekarang = 37";
        $wherev[] = "v.id_proses = 24";
        $w1 = implode(" AND ", $where);
        $w_tenun = implode(" AND ", $where_tenun);

        
        /*Celup tidak menyertakan concat atau group warna*/

        if(isset($req['id_proses'])){
            if($req['id_proses'] == '25'){
                $group_celupA = str_replace(", A.id_corak", "", $group_celupA);
                $group_celupB = str_replace(", B.id_corak", "", $group_celupB);
            }else if($req['id_proses'] == '24'){
                $group_celupA = "GROUP BY A.id_proses_sekarang, A.id_workorder_barang";
                $group_celupB = "GROUP BY B.id_proses_sekarang, B.id_workorder_barang";
            }else if($req['id_proses'] == '41'){
                $group_celupA = str_replace(", A.id_workorder_barang", "", $group_celupA);
                $group_celupB = str_replace(", B.id_workorder_barang", "", $group_celupB);
            }else if($req['id_proses'] == '22' || $req['id_proses'] == '94'){
                $group_celupA = str_replace(", A.id_komposisi", "", $group_celupA);
                $group_celupB = str_replace(", B.id_komposisi", "", $group_celupB);
            }else if($req['id_proses'] == '37'){
                $group_celupA = str_replace(", A.id_motif", "", $group_celupA);
                $group_celupB = str_replace(", B.id_motif", "", $group_celupB);
            }else if($req['id_proses'] == '60'){ //lokal 60, server 80
                $group_celupA = str_replace("A.id_barang,", "", $group_celupA);
                $group_celupB = str_replace("B.id_barang,", "", $group_celupB);
            }
        }

        if(isset($req['id_pegawai_proses'])){
            if($req['id_pegawai_proses'] != '0'){
                $group_celupA .= ", A.id_pegawai";
                $group_celupB .= ", B.id_pegawai";
            }
        }

        if(isset($req['id_rekanan_proses'])){
            if($req['id_rekanan_proses'] != '0'){
                $group_celupA .= ", A.id_rekanan";
                $group_celupB .= ", B.id_rekanan";
            }
        }
        
        $data['where'] = $add_where.$w1.$group_celupA;      
        $data['where_tenun'] = $add_where.$w_tenun." AND B.id_proses IN (24, 37) GROUP BY B.id_proses_sekarang, B.id_motif, B.id_kategori, B.id_workorder_barang, B.id_proses ORDER BY B.id_proses, B.id_motif, B.id_komposisi";



        $data['where_mutasi'] = $add_where.$w1;

        if($proses_harga != ''){
            $where_harga[] = $proses_harga;
        }

        $where_h = "";
        if(count($where_harga) > 0){
            $where_h = " AND ".implode(" AND ", $where_harga);
        }
        
        if($jenis == 1){
            
        }else if($jenis == 2){
            $order_ = '';
            if($id_proses != 37){
                if($id_proses == 41){
                    $group_celupA = str_replace(", A.id_workorder_barang", "", $group_celupA);
                }else if($id_proses == '18'){
                    if($req['id_warna'] == '' ){
                        $group_celupA = $group_celupA.", A.id_warna";
                    //    $group_celupB = $group_celupB.", B.id_warna";
                    }
                }else if($id_proses == 24){
                    $order_ = "A.nama_proses DESC, ";
                }
                // $query_[] = "SELECT A.*, '0' AS jumlah_masuk, '0' AS jumlah_keluar, SUM(A.keluar-A.masuk) AS jumlah_awal FROM (".get_produksiview($add_where.$vr).") A ".$group_celupA." HAVING jumlah_awal IS NOT NULL";
                $query_[] = "SELECT A.*, SUM(A.masuk) AS jumlah_masuk, SUM(A.keluar) AS jumlah_keluar, '0' AS jumlah_awal FROM (".get_produksiview($add_where.$w1, $order_).") A ".$group_celupA.", A.id_proses";
                
                $query_all = "SELECT A.*, SUM(A.jumlah_masuk) AS mutasi_masuk, SUM(A.jumlah_keluar) AS mutasi_keluar, (SUM(0)+SUM(A.jumlah_keluar))-SUM(A.jumlah_masuk) sisa, ms.satuan as inisial_satuan, A.inisial_warna inisial_komposisi, A.nama_subkategori kategori FROM (".implode(" UNION ALL ", $query_).") A LEFT JOIN ms_satuan as ms ON ms.id_satuan = A.id_satuan ".$group_celupA." HAVING (SUM(0)+SUM(A.jumlah_keluar))-SUM(A.jumlah_masuk) > 0";

                $query_harga = "SELECT A.*, CASE WHEN B.id_harga IS NULL THEN '0' ELSE B.harga END AS harga_satuan, CASE WHEN B.id_harga IS NULL THEN '0' ELSE B.harga END * A.sisa AS jumlah_harga
                    FROM (".$query_all.") AS A
                    LEFT JOIN (
                        SELECT v.*
                        FROM (SELECT * FROM view_hargabarang WHERE jenis = 0 ".$where_h.") AS v
                        LEFT JOIN (SELECT * FROM view_hargabarang WHERE jenis = 0 ".$where_h.") AS v2 ON $join_harga
                        WHERE v2.id_harga IS NULL
                    ) B ON $join_stok $orderby_proses";
                
                // print_r($query_harga); exit();

                $data['detail'] = barang_byproses(DB::select($query_harga), 100, $id_proses);
                $html = "MutasiProduksiRekapCetak";

            }else{
                $order_by = " ORDER BY C.nama_motif, C.nama_komposisi, C.no_workorder_barang";
                $join_stok = str_replace("A.id_barang = B.id_barang AND", "", $join_stok);
                $query = "SELECT A.* FROM (".get_prosestenun($data['where_tenun'], $order_by, "").") A";
                $query_harga = "SELECT A.*, CASE WHEN B.id_harga IS NULL THEN '0' ELSE B.harga END AS harga_satuan, CASE WHEN B.id_harga IS NULL THEN '0' ELSE B.harga END * A.sisa AS jumlah_harga
                    FROM (".$query.") AS A
                    LEFT JOIN (
                        SELECT v.*
                        FROM (SELECT * FROM view_hargabarang WHERE jenis = 0 ) AS v
                        LEFT JOIN (SELECT * FROM view_hargabarang WHERE jenis = 0 ) AS v2 ON $join_harga
                        WHERE v2.id_harga IS NULL
                    ) B ON $join_stok";
                
                // print_r($query_harga); exit();
                
                $data['detail'] = DB::select($query_harga);
                $html = "MutasiRekapProduksiTenunCetak";
            }            
        }
        else{
            $data['detail'] = array();
            $html = "MutasiproduksiDetailCetak";
        }
        

       // print_r($data['induk']);

        // require('fpdf181/Mc_table.php');
        // return response(view('keuangan.hargabarang.cetak.'.$html)->with('data', $data))->header('Content-Type', 'application/pdf');
        
        if(isset($req['is_excelproses'])){
            if($req['is_excelproses'] == 1){
                $barang = 'Semua Barang';
                if($data['induk']['barang'] != false){
                    $barang = $data['induk']['barang']->nama_barang;
                }

                // print_r($data['detail']);
                Excel::create("Mutasi Rekap _".$data['induk']['nama_gudang']."_".$barang."_".date_range($data['induk']['tanggal_awal'], $data['induk']['tanggal_akhir']), function($excel) use ($html, $data){
                    $excel->sheet("Sheet 1", function($sheet) use ($html, $data){
                        $sheet->loadView('keuangan.hargabarang.cetak.'.$html."_Excel", $data);
                    });
                })->export("xlsx");
                
            }else{
                require('fpdf181/Mc_table.php');
                return response(view('keuangan.hargabarang.cetak.'.$html)->with('data', $data))->header('Content-Type', 'application/pdf');
            }
        }
    }

    // -------------------- Helper Laporan
    function _joinharga($var = 'v', $var2 = 'v2', $id_proses = ''){
        $d_join = "".$var.".id_barang = ".$var2.".id_barang AND ".$var.".id_proses = ".$var2.".id_proses AND ".$var.".id_corak = ".$var2.".id_corak AND ".$var.".id_motif = ".$var2.".id_motif AND ".$var.".id_satuan = ".$var2.".id_satuan AND ".$var.".jenis = ".$var2.".jenis  AND ".$var.".tanggal < ".$var2.".tanggal"; //AND ".$var.".id_komposisi = ".$var2.".id_komposisi

        if(in_array($id_proses, setup_benang()) || $id_proses == 0){
            $d_join = $d_join;
        }else if(in_array($id_proses, setup_corak())){
            if($id_proses == 24){
                $d_join = $d_join; //str_replace("", "", $d_join);
            }else{
                $d_join = $d_join; //str_replace("".$var.".id_barang = ".$var2.".id_barang AND", "", $d_join);
            }
        }else{
            $d_join = $d_join;
        }

        return $d_join;
    }

    function _joinstok($var = 'A', $var2 = 'B', $id_proses = ''){
        $d_join = "".$var.".id_barang = ".$var2.".id_barang AND ".$var.".id_proses = ".$var2.".id_proses AND ".$var.".id_corak = ".$var2.".id_corak AND ".$var.".id_motif = ".$var2.".id_motif AND ".$var.".id_satuan = ".$var2.".id_satuan "; //AND ".$var.".id_komposisi = ".$var2.".id_komposisi

        if(in_array($id_proses, setup_benang()) || $id_proses == 0){
            $d_join = $d_join;
        }else if(in_array($id_proses, setup_corak())){
            if($id_proses == 24){
                $d_join = str_replace(["".$var.".id_barang = ".$var2.".id_barang AND"], [""], $d_join);
            }else{
                $d_join = str_replace("".$var.".id_barang = ".$var2.".id_barang AND", "", $d_join);
            }
        }else{
            $d_join = $d_join;
        }

        return $d_join;
    }

    function groupby_pro_celup($huruf){
        return "GROUP BY ".$huruf.".id_barang, ".$huruf.".id_proses_sekarang, ".$huruf.".id_satuan, ".$huruf.".id_corak, ".$huruf.".id_motif, ".$huruf.".id_kategori"; 
    }

    function groupby_pro($huruf){
        return "GROUP BY ".$huruf.".id_barang,".$huruf.".id_proses_sekarang, ".$huruf.".id_komposisi, ".$huruf.".id_satuan, ".$huruf.".id_corak, ".$huruf.".id_motif, ".$huruf.".id_kategori, ".$huruf.".id_workorder_barang";
    }
}

    