<?php

namespace App\Http\Controllers\penjualan;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Session;
use DB;
use Excel;

class LaporanController 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); 
        });
    }

    public function index(){
    	// return view('penjualan.LaporanList');
        if (Session::get('id_lokasi') == 0 || Session::get('id_lokasi') == "") {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->get();
            $data['lokasi_semua'] = 'show';
        } else if (Session::get('id_lokasi') == 1 && Session::get('kode_group') == 5 || Session::get('kode_group') == 8 || Session::get('kode_group') == 9) {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->get();
            $data['lokasi_semua'] = 'show';
        } else if (Session::get('id_lokasi') != 1 || Session::get('id_lokasi') != 0 || Session::get('id_lokasi') != "" && Session::get('kode_group') == 5 || Session::get('kode_group') == 8 || Session::get('kode_group') == 9) {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->where('id_lokasi', Session::get('id_lokasi'))->get();
            $data['lokasi_semua'] = 'hide';
        } else if (Session::get('id_lokasi') != 1 || Session::get('id_lokasi') != 0 || Session::get('id_lokasi') != "" && Session::get('kode_group') != 5 || Session::get('kode_group') != 8 || Session::get('kode_group') != 9) {
            $data['lokasi_produksi'] = DB::table("ms_lokasi_produksi")->where('id_lokasi', Session::get('id_lokasi'))->get();
            $data['lokasi_semua'] = 'hide';
        }
        
        $data['pelanggan'] = DB::table("ms_pelanggan")->get();
        $data['kategori']=DB::table("ms_kategori_barang")->where('induk_kb','=','0')->get();
        
        $data['url_laporan'] = url("laporan_penjualan/cetak_laporan");

        return view('penjualan.PenjualanLaporan', $data);
    }

    public function index2(){
        return view('penjualan.LaporanList');
    }

    public function get_sub_kategori(Request $request)
    {
        $input = $request->all();
        $sub_kategori = DB::table("ms_kategori_barang")->where([ ["induk_kb", "=", $input['id_kelompok']] ])->get();

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

    public function cetak_laporan_kelompok(Request $request)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);

        $data['judul'] = "Laporan Penjualan Per Kelompok";
        $data['kategori'] = DB::table("ms_kategori_barang AS kb")
                        ->selectRaw("kb.*, k.nama_kelompok")
                        ->join("ms_kelompok AS k", "k.id_kelompok", "=", "kb.id_kelompok")
                        ->where([ ["kb.id_kb", "=", $input['kategori'] ] ])
                        ->first();

        $detail = $request->get('detail');
        switch ($detail) {
            case '1':
                $data['judul'] = "Laporan Penjualan Per Kelompok";
                $laporan = DB::table('pen_penjualan_barang AS pjb')
                        ->join("pen_penjualan AS pj", "pj.id_penjualan", "=", "pjb.id_penjualan")
                        ->join("ms_pelanggan AS pg", "pg.id_pelanggan", "=", "pj.id_pelanggan")
                        ->join("ms_barang AS mb", "mb.id", "=", "pjb.id_barang")
                        ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "mb.id_kb")
                        ->where([ ["kb.induk_kb", "=", $input['kategori']] ])
                        ->whereBetween('pj.tanggal_faktur', array($tgl_awal, $tgl_akhir))
                        ->selectRaw("pjb.*,
                            pj.tanggal_faktur,
                            pj.no_faktur,
                            pj.sufix_no_faktur,
                            pg.nama AS nama_pelanggan,
                            pj.mata_uang AS mata_uang,
                            pj.kurs_jual AS kurs_jual,
                            SUM(pjb.jml_barang*pjb.harga_satuan) AS nominal_jumlah, 
                            SUM(pjb.jml_barang*pjb.potongan) AS nominal_potongan, 
                            SUM(CASE WHEN pj.pajak=1 THEN (pjb.harga_satuan-pjb.potongan)*pjb.jml_barang*10/100 ELSE 0 END) AS nominal_pajak")
                        ->groupBy("pj.id_penjualan")
                        ->orderByRaw("pj.tanggal_faktur, CAST(pj.no_faktur AS UNSIGNED)");
                // $page = 'LaporanJualKelompokFaktur';
                $page = 'LaporanJualKelompokFakturCell';
                break;
            case '2':
                $data['judul'] = "Laporan Penjualan Per Kelompok Barang";
                $laporan = DB::table('pen_penjualan_barang AS pjb')
                        ->join("pen_penjualan AS pj", "pj.id_penjualan", "=", "pjb.id_penjualan")
                        ->join("ms_barang AS mb", "mb.id", "=", "pjb.id_barang")
                        ->join("ms_kategori_barang AS skb", "skb.id_kb", "=", "mb.id_kb")
                        ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "skb.induk_kb")
                        ->join("ms_satuan AS ms", "ms.id_satuan", "=", "pjb.id_satuan")
                        ->whereBetween('pj.tanggal_faktur', array($tgl_awal, $tgl_akhir))
                        ->selectRaw("pjb.*,
                            kb.id_kelompok,
                            skb.nama AS sub_kategori,
                            kb.nama AS kategori,
                            mb.kode_barang,
                            mb.nama_barang,
                            SUM(pjb.jml_barang) AS jumlah_qty,
                            ms.nama_satuan")
                        ->orderByRaw("SUM(pjb.jml_barang) DESC")
                        ->groupBy("pjb.id_barang");
                $page = 'LaporanJualKelompokBarang';
                break;
            default:
                # code...
                break;
        }

    //  print_r($data);
        if($lokasi!=0){
            $laporan->where("pj.lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("pj.cv", $label);
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        // DB::enableQueryLog();
        $data['data'] = $laporan->get();
        // dd(DB::getQueryLog());

        // dd($data);
        $html = view('penjualan.'.$page)->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }

    function get_barang(){
        $barang = DB::table('ms_barang AS b')
                ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "b.id_kb")
                ->where([ ["kb.id_kelompok", "=", 4] ])->get();

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

    public function cetak_laporan_barang(Request $request)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);

        $data['judul'] = "Laporan Penjualan Per Barang";
        $data['barang'] = DB::table("ms_barang AS b")
                        ->join("ms_kategori_barang AS skb", "skb.id_kb", "=", "b.id_kb")
                        ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "skb.induk_kb")
                        ->selectRaw("b.*,
                            skb.id_kelompok,
                            kb.nama AS kategori,
                            skb.nama AS sub_kategori")
                        ->where("b.id", "=", $input['barang'])->first();

        $laporan = DB::table("pen_penjualan_barang AS pjb")
                        ->join("pen_penjualan AS pj", "pj.id_penjualan", "=", "pjb.id_penjualan")
                        ->join("ms_pelanggan AS pg", "pg.id_pelanggan", "=", "pj.id_pelanggan")
                        ->join("ms_barang AS mb", "mb.id", "=", "pjb.id_barang")
                        ->join("ms_kategori_barang AS skb", "skb.id_kb", "=", "mb.id_kb")
                        ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "skb.induk_kb")
                        ->join("ms_satuan AS ms", "ms.id_satuan", "=", "pjb.id_satuan")
                        ->where("pjb.id_barang", "=", $input['barang'])
                        ->whereBetween('pj.tanggal_faktur', array($tgl_awal, $tgl_akhir))
                        ->selectRaw("pjb.*,
                            skb.id_kelompok,
                            skb.id_kb AS id_sub_kategori,
                            skb.nama AS sub_kategori,
                            kb.id_kb AS id_kategori,
                            kb.nama AS kategori,
                            pj.tanggal_faktur,
                            pj.no_faktur,
                            pj.sufix_no_faktur,
                            pj.id_pelanggan,
                            pg.nama AS nama_pelanggan,
                            ms.nama_satuan,
                            pj.mata_uang AS mata_uang,
                            pj.kurs_jual AS kurs_jual,
                            SUM(pjb.jml_barang) AS jumlah_qty, 
                            SUM(pjb.harga_satuan) AS jumlah_harga, 
                            SUM(CASE WHEN pj.pajak=1 THEN (pjb.harga_satuan-pjb.potongan)*10/100 ELSE 0 END) AS jumlah_pajak,
                            SUM(pjb.jml_barang*pjb.harga_satuan) AS nominal_jumlah, 
                            SUM(pjb.jml_barang*pjb.potongan) AS nominal_potongan, 
                            SUM(CASE WHEN pj.pajak=1 THEN (pjb.harga_satuan-pjb.potongan)*pjb.jml_barang*10/100 ELSE 0 END) AS nominal_pajak")
                        ->groupBy(["pj.id_penjualan", "pjb.jml_barang", "pjb.id_jual_barang"])
                        ->orderByRaw("pg.nama, pj.tanggal_faktur, CAST(pj.no_faktur AS UNSIGNED), pj.id_pelanggan");

        $page = 'LaporanJualBarang';

        //  print_r($data);
        if($lokasi!=0){
            $laporan->where("pj.lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("pj.cv", $label);
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['data'] = $laporan->get();

        // dd($data);
        $html = view('penjualan.'.$page)->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }

    public function cetak_laporan_kategori_excel(Request $request)
    {
        $input = $request->all();
        
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);

        $barang = DB::table("ms_barang AS b")
                ->join("ms_kategori_barang AS skb", "skb.id_kb", "=", "b.id_kb")
                ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "skb.induk_kb")
                ->selectRaw("b.*,
                    skb.id_kelompok,
                    kb.nama AS kategori,
                    skb.nama AS sub_kategori");
        if($input['kategori']!=0){
            $barang->where("kb.id_kb", $input['kategori']);
        }
        if($input['sub_kategori']!=0){
            $barang->where("skb.id_kb", $input['sub_kategori']);
        }
        $barang = $barang->first();
        // dd($barang);
        
        if($input['kategori']!=0){
            $set_nama_barang = $barang->kategori;
        }
        if($input['sub_kategori']!=0){
            $set_nama_barang = $barang->sub_kategori;
        }

        $data['judul'] = strtoupper(strtolower("Laporan Penjualan ".$set_nama_barang));

        $group_barang_penjualan = DB::table('pen_penjualan_barang AS pjb')
                        ->join("pen_penjualan AS pj", "pj.id_penjualan", "=", "pjb.id_penjualan")
                        ->join("ms_barang AS mb", "mb.id", "=", "pjb.id_barang")
                        ->join("ms_kategori_barang AS skb", "skb.id_kb", "=", "mb.id_kb")
                        ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "skb.induk_kb")
                        ->selectRaw("mb.*, SUM(pjb.jml_barang) AS total_qty")
                        ->whereBetween('pj.tanggal_faktur', array($tgl_awal, $tgl_akhir))
                        ->groupBy("pjb.id_barang")
                        ->orderBy('total_qty', 'desc')
                        ->orderBy('nama_barang', 'asc');

        // DB::enableQueryLog();
        $laporan = DB::table('pen_penjualan_barang AS pjb')
                        ->join("pen_penjualan AS pj", "pj.id_penjualan", "=", "pjb.id_penjualan")
                        ->join("ms_pelanggan AS pg", "pg.id_pelanggan", "=", "pj.id_pelanggan")
                        ->join("ms_barang AS mb", "mb.id", "=", "pjb.id_barang")
                        ->join("ms_kategori_barang AS skb", "skb.id_kb", "=", "mb.id_kb")
                        ->join("ms_kategori_barang AS kb", "kb.id_kb", "=", "skb.induk_kb")
                        ->whereBetween('pj.tanggal_faktur', array($tgl_awal, $tgl_akhir))
                        ->selectRaw("pjb.*,
                            skb.id_kelompok,
                            skb.id_kb AS id_sub_kategori,
                            skb.nama AS sub_kategori,
                            kb.id_kb AS id_kategori,
                            kb.nama AS kategori,
                            pj.tanggal_faktur,
                            pj.no_faktur,
                            pj.sufix_no_faktur,
                            pj.id_pelanggan,
                            pg.nama AS nama_pelanggan,
                            pj.mata_uang AS mata_uang,
                            pj.kurs_jual AS kurs_jual,
                            SUM(pjb.jml_barang) AS jumlah_qty, 
                            SUM(pjb.harga_satuan) AS jumlah_harga, 
                            SUM(CASE WHEN pj.pajak=1 THEN (pjb.harga_satuan-pjb.potongan)*10/100 ELSE 0 END) AS jumlah_pajak,
                            SUM(pjb.jml_barang*pjb.harga_satuan) AS nominal_jumlah, 
                            SUM(pjb.jml_barang*pjb.potongan) AS nominal_potongan, 
                            SUM(CASE WHEN pj.pajak=1 THEN (pjb.harga_satuan-pjb.potongan)*pjb.jml_barang*10/100 ELSE 0 END) AS nominal_pajak")
                        ->groupBy("pjb.harga_satuan","pjb.id_barang")
                        ->orderByRaw("kb.id_kb, skb.id_kb, pjb.id_barang");

        $page = 'LaporanJualBarangExcel';

        //  print_r($data);
        if($input['kategori']!=0){
            $laporan->where("kb.id_kb", $input['kategori']);
            $group_barang_penjualan->where("kb.id_kb", $input['kategori']);
        }

        if($input['sub_kategori']!=0){
            $laporan->where("skb.id_kb", $input['sub_kategori']);
            $group_barang_penjualan->where("skb.id_kb", $input['sub_kategori']);
        }

        if($lokasi!=0){
            $laporan->where("pj.lokasi", $lokasi);
            $group_barang_penjualan->where("pj.lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("pj.cv", $label);
            $group_barang_penjualan->where("pj.cv", $label);
        }

        $group_barang_penjualan = $group_barang_penjualan->get();
        $data_barang_penjualan = array_reduce($group_barang_penjualan->toArray(), function($carry, $item){
            $item->child = [];
            $carry[ $item->id ] = $item;
            return $carry;
        });

        $laporan = $laporan->get();
        foreach ($laporan as $key => $value) {
            $data_barang_penjualan[$value->id_barang]->child[] = $value;
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['data'] = $data_barang_penjualan;

        // dd(DB::getQueryLog());
        // return view("penjualan.".$page, $data);

        Excel::create("Laporan Penjualan Sarung", function($excel) use ($page, $data){
            $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
                // $sheet->setWidth("A", 5);
                // $sheet->mergeCells("A1:B1");
                // $sheet->row(1, ["CV. KETJUBUNG"]);
                // $sheet->row(2, ["LAPORAN PENJUALAN SARUNG"]);
                // $sheet->row(3, ["Periode", "-"]);

                // $sheet->row(4, [
                //     "No",
                //     "Jenis Sarung",
                //     "QTY",
                //     "Harga",
                //     "Jumlah",
                //     "Diskon",
                //     "Netto",
                // ]);
                $sheet->loadView("penjualan.".$page, $data);
            });
        })->export("xlsx");

        // return view("penjualan.LaporanJualBarangExcel");
    }

    function cetak_detail(Request $request){
    	require('fpdf181/Mc_table.php');
    	$data = array();
    	if($_POST){
    		$tgl_awal = date('Y-m-d', strtotime($request->get('tanggal_awal')));
	    	$tgl_akhir = date('Y-m-d', strtotime($request->get('tanggal_akhir')));
	    	$jenis = $request->get('jenis');

	    	$data['periode'] = date_range($tgl_awal, $tgl_akhir);

	    	switch ($jenis) {
	    		case '1':
	    			$data['data'] = DB::table('view_laporan_penjualan')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->get();
	    			$page = 'LaporanJualdetail';
	    			break;
	    		case '2':
	    			$data['data'] = DB::table('view_laporan_penjualan')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->select(DB::raw("*, sum(harga_satuan) total_satuan, sum(jml_barang) total_jml_barang, sum(potongan) total_potongan"))->groupBy(array('id_pelanggan'))->get();
	    			$page = 'LaporanJualrekap';
	    			break;
	    		default:
	    			# code...
	    			break;
	    	}
    	}else{
    		$page = 'LaporanJualdetail';
    		$data['data'] = array();
    		$data['periode'] = date_range($tgl_awal, $tgl_akhir);
    	}

    	$html = view('penjualan.'.$page)->with('data',$data);
		return response($html)->header('Content-Type', 'application/pdf');
    }

    public function langganan(){
    	return view('penjualan.LaporanJuallanggananList');
    }

    function get_langganan(){
    	$d_data = DB::table('ms_pelanggan');

        if($d_data->count() > 0){
            foreach($d_data->get() as $d){
                $arr[] = array('id_pelanggan' => $d->id_pelanggan,
                                'nama_pelanggan' => $d->nama);
            }
        }else{
            $arr = array();
        }
        return response()->json($arr);
    }

    function get_detail_langgananrekap(Request $request){
    	$where = '';
    	$id_pelanggan = $request->get('id_pelanggan');
    	if($id_pelanggan != 0){
    		$where = "AND pp.id_pelanggan = '$id_pelanggan'";
    	}

    	$tgl_awal = date('Y-m-d', strtotime($request->get('tgl_awal')));
    	$tgl_akhir = date('Y-m-d', strtotime($request->get('tgl_akhir')));
    	$status = $request->get('status');

    	$PDO = DB::connection('mysql')->getPdo();
    	if($status == '1'){
            $billingStmt = $PDO->prepare("select pp.*, mp.nama as nama_pelanggan, sum(ppb.jml_barang) as total_jmlbarang, sum(ppb.harga_satuan) as total_satuan,sum(ppb.potongan) as diskon, sum((ppb.harga_satuan*ppb.jml_barang)-ppb.potongan) as piutang, sum(ppb.harga_satuan*ppb.jml_barang) as penjualan 
    			from pen_penjualan pp join pen_penjualan_barang ppb on pp.id_penjualan=ppb.id_penjualan join ms_pelanggan mp
    			on pp.id_pelanggan=mp.id_pelanggan where pp.tanggal BETWEEN '$tgl_awal' and '$tgl_akhir' 
    			$where group by pp.no_faktur"
    		);
        }else{
        	$billingStmt = $PDO->prepare("select pp.tanggal,pp.no_faktur,pp.id_pelanggan,ppb.*, mp.nama as nama_pelanggan, mp.id_pelanggan as id_pelanggan, mb.nama_barang 
    			from pen_penjualan pp join pen_penjualan_barang ppb on pp.id_penjualan=ppb.id_penjualan 
    			join ms_pelanggan mp on pp.id_pelanggan=mp.id_pelanggan
    			join ms_barang mb on ppb.id_barang=mb.id where pp.tanggal BETWEEN '$tgl_awal' and '$tgl_akhir' $where");
        }
        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
        return response()->json($usersBills);
    }

    function cetak_detail_langgananrekap(Request $request){
    	require('fpdf181/Mc_table.php');
    	$data = array();
    	if($_POST){
    		$id_pelanggan = $request->get('pelanggan');
    		$tgl_awal = date('Y-m-d', strtotime($request->get('tanggal_awal')));
	    	$tgl_akhir = date('Y-m-d', strtotime($request->get('tanggal_akhir')));
	    	$jenis = $request->get('laporan_status');
	    	$pelanggan = DB::table("ms_pelanggan")->where("id_pelanggan",$id_pelanggan)->select('nama')->first();
	    	$data['pelanggan'] = $pelanggan->nama; 
	    	$data['periode'] = date_range($tgl_awal, $tgl_akhir);

	    	switch ($jenis) {
	    		case '1':
	    			$data['data'] = DB::table('view_laporan_penjualan_langganan')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->where('id_pelanggan',$id_pelanggan)->groupBy(array('no_faktur'))->get();
	    			$page = 'LaporanJuallanggananRekap';
	    			break;
	    		case '2':
	    			$data['data'] = DB::table('view_laporan_penjualan')->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->get();
	    			$page = 'LaporanJuallanggananDetail';
	    			break;
	    		default:
	    			# code...
	    			break;
	    	}
    	}else{
    		$page = 'LaporanJuallanggananDetail';
    		$data['data'] = array();
    		$data['periode'] = date_range($tgl_awal, $tgl_akhir);
    		$pelanggan = DB::table("ms_pelanggan")->where("id_pelanggan",$id_pelanggan)->select('nama')->first();
	    	$data['pelanggan'] = $pelanggan->nama;
    	}

    	$html = view('penjualan.'.$page)->with('data',$data);
		return response($html)->header('Content-Type', 'application/pdf');
    }

    function checkRetur($id_penjualan, $param, $tgl_awal, $tgl_akhir)
    {
        $retur_penjualan = DB::table('a_pen_penjualan_retur')->where('id_penjualan', $id_penjualan)->whereBetween('tanggal', array($tgl_awal, $tgl_akhir))->count();

        if ($retur_penjualan > 0) {
            $retur = DB::table('a_pen_penjualan_retur_groupby_penjualan')->where('id_penjualan', $id_penjualan);

            if ($retur->count() > 0) {
                return $retur->first()->$param;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    function getPenjualan($id_penjualan, $param, $get=null)
    {
        $penjualan = DB::table('view_laporan_penjualan')->where('id_penjualan', $id_penjualan);

        if ($get == "SUM") {
            return $penjualan->select(DB::raw('SUM('.$param.') as '.$param))->groupBy('id_penjualan')->first()->$param;
        } else if ($get == "FIRST") {
            return $penjualan->first()->$param;
        } else if ($param == "HARGAJUAL") {
            if ($penjualan->count() > 0) {
                $harga_jual = 0;
                foreach ($penjualan->get() as $key => $value) {
                    $harga_jual += $penjualan->get()[$key]->harga_satuan*$penjualan->get()[$key]->jml_barang;
                }
            } else {
                $harga_jual = 0;
            }
            return $harga_jual;
        }
    }

    function getPenjualanRetur($id_penjualan, $param)
    {
        $penjualan_retur = DB::table('a_pen_penjualan_retur_barang')->where('id_penjualan', $id_penjualan);

        if ($param == "HARGARETUR") {
            if ($penjualan_retur->count() > 0) {
                $harga_retur = 0;
                foreach ($penjualan_retur->get() as $key => $value) {
                    $harga_retur += $penjualan_retur->get()[$key]->harga_satuan*$penjualan_retur->get()[$key]->jml_retur;
                }
            } else {
                $harga_retur = 0;
            }
            return $harga_retur;
        }
    }

    public function cetak_laporan_pelanggan(Request $request)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua
        $tgl_awal = date_db($input['tanggal_awal']);
        $tgl_akhir = date_db($input['tanggal_akhir']);

        switch ($input['jenis_laporan']) {
            case '0':
                $laporan = DB::table('view_laporan_penjualan_pelanggan')->whereBetween('tanggal_faktur', array($tgl_awal, $tgl_akhir))->where('id_pelanggan',$input['id_pelanggan'])->orderByRaw("tanggal_faktur, CAST(no_faktur AS UNSIGNED)");
                $row_data = [];
                if ($laporan->count() > 0) {
                    foreach ($laporan->get() as $key => $value) {
                        if ($this->checkRetur($laporan->get()[$key]->id_penjualan, "nominal_total", $tgl_awal, $tgl_akhir) != 0) {
                            $qty_penjualan = $this->getPenjualan($laporan->get()[$key]->id_penjualan, "jml_barang", "SUM");
                            $qty_retur = $this->checkRetur($laporan->get()[$key]->id_penjualan, "total_barang", $tgl_awal, $tgl_akhir);
                            $qty = $qty_penjualan-$qty_retur;
                            $potongan = $this->getPenjualan($value->id_penjualan, "potongan", "FIRST");
                            $harga_jual = $this->getPenjualan($value->id_penjualan, "HARGAJUAL");
                            $harga_retur = $this->getPenjualanRetur($value->id_penjualan, "HARGARETUR");

                            $penjualan = $harga_jual-$harga_retur;
                            $diskon = $potongan*$qty;
                            $piutang = $penjualan-$diskon;
                        } else {
                            $penjualan = $value->penjualan;
                            $diskon = $value->diskon;
                            $piutang = $value->piutang;
                        }
                        $row_data[] = array(
                            'tanggal'   => $value->tanggal,
                            'no_faktur' => $value->no_faktur,
                            'sufix_no_faktur' => $value->sufix_no_faktur,
                            'keterangan' => $value->keterangan,
                            'kurs_jual' => $value->kurs_jual,
                            'mata_uang' => $value->mata_uang,
                            'penjualan' => $penjualan,
                            'diskon'    => $diskon,
                            'piutang'   => $piutang
                        );
                    }
                }

                $retur = DB::table('a_pen_penjualan_retur as rtr')
                ->join('a_pen_penjualan as pjl', 'pjl.id_penjualan', '=', 'rtr.id_penjualan')
                ->where('rtr.id_pelanggan', $input['id_pelanggan'])
                ->whereBetween('rtr.tanggal_retur', array($tgl_awal, $tgl_akhir))
                ->select('rtr.tanggal_retur', 'rtr.no_retur', 'rtr.sufix_no_retur', 'pjl.no_faktur', 'pjl.sufix_no_faktur', 'rtr.keterangan', 'rtr.total_barang', 'rtr.nominal_total', 'rtr.kurs_jual');

                // $page = 'LaporanJuallanggananRekap';
                $page = 'LaporanJualLanggananRekapCell';
                break;
            case '1':
                $faktur = DB::table('view_laporan_penjualan_pelanggan')->whereBetween('tanggal_faktur', array($tgl_awal, $tgl_akhir))->where('id_pelanggan',$input['id_pelanggan'])->orderByRaw("tanggal_faktur, CAST(no_faktur AS UNSIGNED)");
                $data['faktur'] = $faktur->get();
                
                $laporan = DB::table('view_laporan_penjualan_pelanggan_detail')->join('ms_satuan', 'view_laporan_penjualan_pelanggan_detail.id_satuan', '=', 'ms_satuan.id_satuan')->whereBetween('tanggal_faktur', array($tgl_awal, $tgl_akhir))->where('id_pelanggan',$input['id_pelanggan'])->orderByRaw("tanggal_faktur, CAST(no_faktur AS UNSIGNED)");

                $row_data = [];
                if ($laporan->count() > 0) {
                    foreach ($laporan->get() as $key => $value) {
                        $row_data[] = array(
                            'id_penjualan'  => $value->id_penjualan,
                            'jml_barang'    => $value->jml_barang,
                            'harga_satuan'  => $value->harga_satuan,
                            'potongan'      => $value->potongan,
                            'no_faktur'     => $value->no_faktur,
                            'sufix_no_faktur'=> $value->sufix_no_faktur,
                            'tanggal_faktur'=> $value->tanggal_faktur,
                            'nama_barang'   => $value->nama_barang,
                            'satuan'        => $value->satuan,
                            'kurs_jual'     => $value->kurs_jual,
                            'mata_uang'     => $value->mata_uang
                        );
                    }
                }

                $retur = DB::table('a_pen_penjualan_retur as rtr')
                ->join('a_pen_penjualan as pjl', 'pjl.id_penjualan', '=', 'rtr.id_penjualan')
                ->join('a_pen_penjualan_retur_barang as rtr_brg', 'rtr_brg.id_penjualan', '=', 'rtr.id_penjualan')
                ->join('ms_satuan', 'rtr_brg.id_satuan', '=', 'ms_satuan.id_satuan')
                ->where('rtr.id_pelanggan', $input['id_pelanggan'])
                ->whereBetween('rtr.tanggal_retur', array($tgl_awal, $tgl_akhir))
                ->select('rtr.tanggal_retur', 'rtr.no_retur', 'rtr.sufix_no_retur', 'pjl.no_faktur', 'pjl.sufix_no_faktur', 'rtr_brg.jml_retur', 'rtr_brg.harga_satuan', 'rtr_brg.potongan', 'rtr_brg.total_retur', 'rtr_brg.id_penjualan', 'rtr.kurs_jual', 'rtr_brg.nama_barang', 'ms_satuan.satuan', 'rtr.mata_uang');

                $page = 'LaporanJuallanggananDetail';
                // $page = 'LaporanJualLanggananDetailCell';
                break;
            default:
                # code...
                break;
        }

        if($lokasi!=0){
            $laporan->where("lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("cv", $label);
        }

        $data['periode'] = date_db($tgl_awal, true)." s/d ".date_db($tgl_akhir, true);
        $data['pelanggan'] = DB::table("ms_pelanggan")->where("id_pelanggan", $input['id_pelanggan'])->first();
        // $data['data'] = $laporan->get();
        $data['data'] = $row_data;
        $data['retur'] = $retur->get();
        // dd($data);
        $view = "penjualan/".$page;
        $html = view($view)->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }

    public function cetak_laporan_jatuhtempo(Request $request)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua

        $data['judul'] = "Daftar Jatuh Tempo Tagihan Piutang";
        $periode = explode("-", $input['tanggal_tempo']);
        $data['periode'] = bln_tostr($periode[0])." ".$periode[1];
        
        $laporan = DB::table('view_laporan_penjualan_pelanggan AS lp')
                ->join("view_penjualan_bayar AS pb", function($join){
                    $join->on("pb.id_penjualan", "=", "lp.id_penjualan")
                        ->where("pb.kode_sebagai", "=", 2);
                })
                ->whereRaw("MONTH(lp.tanggal_tempo) = '$periode[0]' AND YEAR(lp.tanggal_tempo) = '$periode[1]'")
                // ->orderByRaw("lp.tanggal_faktur, CAST(lp.no_faktur AS UNSIGNED)")
                ->orderByRaw("lp.tanggal_tempo")
                ->selectRaw("lp.*, pb.total_bayar");

        if($lokasi!=0){
            $laporan->where("lp.lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("lp.cv", $label);
        }

        if($input['id_pelanggan']!=0){
            $laporan->where("lp.id_pelanggan", $input['id_pelanggan']);
        }

        $data['data'] = $laporan->get();

        // dd($data);
        // $page = "LaporanJualJatuhtempo";
        $page = "LaporanJualJatuhTempoCell";
        $view = "penjualan/".$page;
        $html = view($view)->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }

    public function cetak_laporan_jatuhtempo_excel(Request $request)
    {
        $input = $request->all();
        // dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua

        $data['judul'] = "Daftar Jatuh Tempo Tagihan Piutang";
        $periode = explode("-", $input['tanggal_tempo']);
        $data['periode'] = bln_tostr($periode[0])." ".$periode[1];
        
        $laporan = DB::table('view_laporan_penjualan_pelanggan AS lp')
                ->join("view_penjualan_bayar AS pb", function($join){
                    $join->on("pb.id_penjualan", "=", "lp.id_penjualan")
                        ->where("pb.kode_sebagai", "=", 2);
                })
                ->whereRaw("MONTH(lp.tanggal_tempo) = '$periode[0]' AND YEAR(lp.tanggal_tempo) = '$periode[1]'")
                // ->orderByRaw("lp.tanggal_faktur, CAST(lp.no_faktur AS UNSIGNED)")
                ->orderByRaw("lp.tanggal_tempo")
                ->selectRaw("lp.*, pb.total_bayar");

        if($lokasi!=0){
            $laporan->where("lp.lokasi", $lokasi);
        }

        if($label!=3){
            $laporan->where("lp.cv", $label);
        }

        if($input['id_pelanggan']!=0){
            $laporan->where("lp.id_pelanggan", $input['id_pelanggan']);
        }

        $data['data'] = $laporan->get();

        // dd($data);
        // $page = "LaporanJualJatuhtempo";
        $page = "LaporanJualJatuhTempoExcel";
        
        Excel::create("Laporan Penjualan Sarung", function($excel) use ($page, $data){
            $excel->sheet("Sheet 1", function($sheet) use ($page, $data){
                $sheet->loadView("penjualan.".$page, $data);
            });
        })->export("xlsx");
    }

    public function cetak_laporan_keseluruhan(Request $request)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();
        dd($input);
        $lokasi = $input['lokasi']; // 0=>semua
        $label = $input['label']; // 3=>semua

        // dd($view);
        $data = [];
        $html = view($view)->with('data',$data);
        return response($html)->header('Content-Type', 'application/pdf');
    }
}
