ReportManageController.php

public function exportReturPenjualan(Request $req)
    {
        // $start_date = $req->tgl_awal;
        // $end_date = $req->tgl_akhir;
        // $start_date2 = Carbon::parse($start_date)->format('Y-m-d');
        // $end_date2 = date('Y-m-d', strtotime($end_date.'+1 day'));
        $filter = $req->filter;
        $search_retur_penjualan = $req->cari_retur_penjualan;

        if($req->tgl_awal != null || $req->tgl_akhir != null){
            $start_date = $req->tgl_awal;
            $end_date = $req->tgl_akhir;
            $start_date2 = Carbon::parse($start_date)->format('Y-m-d');
            $end_date2 = date('Y-m-d', strtotime($end_date.'+1 day'));
        }else{
            $get_start1 = Transaction::first();
            $get_start2 = TransactionNonPPN::first();
            if ($get_start1->created_at < $get_start2->created_at) {
                $get_start = $get_start1->created_at;
            }else{
                $get_start = $get_start2->created_at;
            }

            $get_end1 = Transaction::latest()->first();
            $get_end2 = TransactionNonPPN::latest()->first();
            if ($get_end1->created_at > $get_end2->created_at) {
                $get_end = $get_end1->created_at;
            }else{
                $get_end = $get_end2->created_at;
            }

            $start_date = $get_start->format('Y-m-d');
            $end_date = $get_end->format('Y-m-d');
            $start_date2 = Carbon::parse($start_date)->format('Y-m-d');
            $end_date2 = date('Y-m-d', strtotime($end_date.'+1 day'));
        }

        $transaction = Transaction::whereBetween('created_at', [$start_date2, $end_date2])
        ->select('transactions.kode_transaksi')
        ->where('status', 'retur')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->orderBy('created_at', 'desc')
        ->get();
        $transactionNonPPN = TransactionNonPPN::whereBetween('created_at', [$start_date2, $end_date2])
        ->select('transaction_non_ppn.kode_transaksi')
        ->where('status', 'retur')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->orderBy('created_at', 'desc')
        ->get();
        $transaction = $transaction->concat($transactionNonPPN);
        $jumlah = count($transaction);
        // ===============================================================================
        $array = [];
        foreach ($transaction as $no => $trans) {
            array_push($array, $trans->kode_transaksi);
        }
        $uniq = array_unique($array);
        $penjualan = array_values($uniq);

        $per_transaksi = [];
        foreach ($penjualan as $i => $v) {
            $cc = Transaction::where('kode_transaksi', $v)->where('status','retur')->first();
            if(!$cc){
                $cc = TransactionNonPPN::where('kode_transaksi', $v)->where('status','retur')->first();
            }

            $bb = Customer::where('id', $cc->id_customer)->first();

            $ttl = Transaction::where('kode_transaksi', $v)->where('status','retur')->sum('total_barang');
            $ttl1 = TransactionNonPPN::where('kode_Transaksi', $v)->where('status','retur')->sum('total_barang');
            
            $jumlahBesarPpn = Transaction::join('products', 'transactions.kode_barang', '=', 'products.kode_barang')
                                        ->where('transactions.kode_transaksi', $v)
                                        ->where('transactions.status','retur')
                                        ->where('transactions.jenis_kemasan', '!=', 'Pack')
                                        ->select(DB::raw('products.*,(transactions.jumlah / products.pack) as total_jumlah'))->get();
            $jumlahBesarNonPpn = TransactionNonPPN::join('products', 'transaction_non_ppn.kode_barang', '=', 'products.kode_barang')
                                        ->where('transaction_non_ppn.kode_transaksi', $v)
                                        ->where('transaction_non_ppn.status','retur')
                                        ->where('transaction_non_ppn.jenis_kemasan', '!=', 'Pack')
                                        ->select(DB::raw('products.*,(transaction_non_ppn.jumlah / products.pack) as total_jumlah'))->get();
            $jumlahKecilPpn = Transaction::where('kode_Transaksi', $v)->where('status','retur')->where('jenis_kemasan', 'Pack')->sum('jumlah');
            $jumlahKecilNonPpn = TransactionNonPPN::where('kode_Transaksi', $v)->where('status','retur')->where('jenis_kemasan', 'Pack')->sum('jumlah');

            array_push($per_transaksi, [
                'kode_transaksi' => $v,
                'nama_customer' => ($bb != null) ? $bb->nama : 'Customer dihapus',
                'jumlah_besar' => ($jumlahBesarPpn->sum('total_jumlah') + $jumlahBesarNonPpn->sum('total_jumlah')),
                'jumlah_kecil' => ($jumlahKecilPpn + $jumlahKecilNonPpn),
                'total' => 'Rp '.number_format($ttl + $ttl1,2,',','.'),
                'kasir' => $cc->kasir,
                'created_at' => $cc->created_at->format('d/M/Y H:i:s'),
                'updated_at' => $cc->updated_at->format('d/M/Y H:i:s'),
            ]);
        }
        if($filter == 'penjualan'){
            $jumlah = count($penjualan);
        }
        // ===============================================================================

        $data_tr = Transaction::whereBetween('created_at', [$start_date2, $end_date2])
        ->where('status', 'retur')
        ->orderBy('created_at', 'desc')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->get();
        $data_trr = TransactionNonPPN::whereBetween('created_at',[$start_date2, $end_date2])
        ->where('status', 'retur')
        ->orderBy('created_at', 'desc')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->get();
        $transaksi = $data_tr->merge($data_trr);
        $transaksi = $transaksi->sortBy('created_at');
        $transaksi = $transaksi->values()->all();

        // ===================================== Jumlah Stok ==========================================

        $dos = 0;
        $pack = 0;
        $total_harga= 0;
        $total_dos = 0;
        $total_pack = 0;
        foreach ($transaksi as $key => $value) {
            $pp = Product::where('kode_barang', $value->kode_barang)->first();
            if($pp != null){
                if(strtolower($value->jenis_kemasan) == 'pack'){
                    $pack += $value->jumlah;
                    $total_pack += $value->total_barang;
                }else{
                    $dos +=  $value->jumlah / $pp->pack;
                    $total_dos += $value->total_barang;
                }
            }

            $total_harga += $value->total_barang;
        }
        $jumlah_dos = (int) $dos ;
        $jumlah_pack = (int) $pack ;

        // ==================================== ganti data transaksi ===========================================

        array_walk($transaksi, function(&$item, $key) {
            $item->kode_transaksi = $item->kode_transaksi.''.$item->luar_pulau;
            $product = Product::where('kode_barang', $item->kode_barang)->first();
            if($product != null){
                if(strtolower($item->jenis_kemasan) == 'pack'){
                    $item->jumlah = $item->jumlah;
                }else{
                    $item->jumlah = (int) $item->jumlah / $product->pack;
                }
            }else{
                 $item->jumlah = $item->jumlah;
            }
            $item->id_kasir = User::find($item->id_kasir)->nama;
            $item->id_customer = (Customer::find($item->id_customer) != null) ? Customer::find($item->id_customer)->nama : 'Customer dihapus';
            $item->created_at = $item->created_at->format('d-m-Y H:i:s');
        });
        // ===============================================================================

        $pelanggan = [];
        $jumlah_pelanggan = 0;
        foreach ($transaksi as $key => $value) {
            if(!in_array($value->id_customer, $pelanggan)){
                array_push($pelanggan, $value->id_customer);
                $jumlah_pelanggan++;
            } 
        }

        // ===============================================================================
        
        $tgl1 = Carbon::parse($start_date)->isoFormat('D MMMM Y');
        $tgl2 = Carbon::parse($end_date)->isoFormat('D MMMM Y');
        $tgl = $tgl1.' s/d '.$tgl2;
       
        return Excel::download(new ExportLaporanReturPenjualan($transaksi, $per_transaksi, $filter, $penjualan, $jumlah_dos, $jumlah_pack, $tgl, $jumlah, $jumlah_pelanggan, $total_harga, $total_dos, $total_pack), 'retur penjualan-'.now().'.xlsx');
    }
    public function printReturPenjualan(Request $req)
    {
        $search_retur_penjualan = $req->cari_retur_penjualan;
        $filter = $req->filter;
        if($req->tgl_awal != null || $req->tgl_akhir != null){
            $start_date = $req->tgl_awal;
            $end_date = $req->tgl_akhir;
            $start_date2 = Carbon::parse($start_date)->format('Y-m-d');
            $end_date2 = date('Y-m-d', strtotime($end_date.'+1 day'));
        }else{
            $get_start1 = Transaction::first();
            $get_start2 = TransactionNonPPN::first();
            if ($get_start1->created_at < $get_start2->created_at) {
                $get_start = $get_start1->created_at;
            }else{
                $get_start = $get_start2->created_at;
            }

            $get_end1 = Transaction::latest()->first();
            $get_end2 = TransactionNonPPN::latest()->first();
            if ($get_end1->created_at > $get_end2->created_at) {
                $get_end = $get_end1->created_at;
            }else{
                $get_end = $get_end2->created_at;
            }

            $start_date = $get_start->format('Y-m-d');
            $end_date = $get_end->format('Y-m-d');
            $start_date2 = Carbon::parse($start_date)->format('Y-m-d');
            $end_date2 = date('Y-m-d', strtotime($end_date.'+1 day'));
        }

        $transaction = Transaction::whereBetween('created_at', [$start_date2, $end_date2])
        ->select('transactions.kode_transaksi')
        ->where('status', 'retur')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->orderBy('created_at', 'desc')
        ->get();
        $transactionNonPPN = TransactionNonPPN::whereBetween('created_at', [$start_date2, $end_date2])
        ->select('transaction_non_ppn.kode_transaksi')
        ->where('status', 'retur')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->orderBy('created_at', 'desc')
        ->get();
        $transaction = $transaction->concat($transactionNonPPN);
        $jumlah = count($transaction);
        // ===============================================================================
        $array = [];
        foreach ($transaction as $no => $trans) {
            array_push($array, $trans->kode_transaksi);
        }
        $uniq = array_unique($array);
        $penjualan = array_values($uniq);

        $per_transaksi = [];
        foreach ($penjualan as $i => $v) {
            $cc = Transaction::where('kode_transaksi', $v)->where('status','retur')->first();
            if(!$cc){
                $cc = TransactionNonPPN::where('kode_transaksi', $v)->where('status','retur')->first();
            }

            $bb = Customer::where('id', $cc->id_customer)->first();

            $ttl = Transaction::where('kode_transaksi', $v)->where('status','retur')->sum('total_barang');
            $ttl1 = TransactionNonPPN::where('kode_Transaksi', $v)->where('status','retur')->sum('total_barang');
            
            $jumlahBesarPpn = Transaction::join('products', 'transactions.kode_barang', '=', 'products.kode_barang')
                                        ->where('transactions.kode_transaksi', $v)
                                        ->where('transactions.status','retur')
                                        ->where('transactions.jenis_kemasan', '!=', 'Pack')
                                        ->select(DB::raw('products.*,(transactions.jumlah / products.pack) as total_jumlah'))->get();
            $jumlahBesarNonPpn = TransactionNonPPN::join('products', 'transaction_non_ppn.kode_barang', '=', 'products.kode_barang')
                                        ->where('transaction_non_ppn.kode_transaksi', $v)
                                        ->where('transaction_non_ppn.status','retur')
                                        ->where('transaction_non_ppn.jenis_kemasan', '!=', 'Pack')
                                        ->select(DB::raw('products.*,(transaction_non_ppn.jumlah / products.pack) as total_jumlah'))->get();
            $jumlahKecilPpn = Transaction::where('kode_Transaksi', $v)->where('status','retur')->where('jenis_kemasan', 'Pack')->sum('jumlah');
            $jumlahKecilNonPpn = TransactionNonPPN::where('kode_Transaksi', $v)->where('status','retur')->where('jenis_kemasan', 'Pack')->sum('jumlah');

            array_push($per_transaksi, [
                'kode_transaksi' => $v,
                'nama_customer' => ($bb != null) ? $bb->nama : 'Customer dihapus',
                'jumlah_besar' => ($jumlahBesarPpn->sum('total_jumlah') + $jumlahBesarNonPpn->sum('total_jumlah')),
                'jumlah_kecil' => ($jumlahKecilPpn + $jumlahKecilNonPpn),
                'total' => 'Rp '.number_format($ttl + $ttl1,2,',','.'),
                'kasir' => $cc->kasir,
                'created_at' => $cc->created_at->format('d/M/Y H:i:s'),
                'updated_at' => $cc->updated_at->format('d/M/Y H:i:s'),
            ]);
        }
        if($filter == 'penjualan'){
            $jumlah = count($penjualan);
        }
        // ===============================================================================

        $data_tr = Transaction::whereBetween('created_at', [$start_date2, $end_date2])
        ->where('status', 'retur')
        ->orderBy('created_at', 'desc')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->get();
        $data_trr = TransactionNonPPN::whereBetween('created_at',[$start_date2, $end_date2])
        ->where('status', 'retur')
        ->orderBy('created_at', 'desc')
        ->where(function ($query) use($search_retur_penjualan){
            $query->where('kode_transaksi', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhere('nama_barang', 'like', '%' . $search_retur_penjualan . '%')
                ->orWhereHas('customer', function($query) use ($search_retur_penjualan){
                    $query->where('nama', 'like', '%'.$search_retur_penjualan.'%');
                });
        })
        ->get();
        $transaksi = $data_tr->merge($data_trr);
        $transaksi = $transaksi->sortBy('created_at');
        $transaksi = $transaksi->values()->all();

        // ===================================== Jumlah Stok ==========================================

        $dos = 0;
        $pack = 0;
        $total_harga = 0;
        $total_dos = 0;
        $total_pack = 0;
        foreach ($transaksi as $key => $value) {
            $pp = Product::where('kode_barang', $value->kode_barang)->first();
            if($pp != null){
                if(strtolower($value->jenis_kemasan) == 'pack'){
                    $pack += $value->jumlah;
                    $total_pack += $value->total_barang;
                }else{
                    $dos +=  $value->jumlah / $pp->pack;
                    $total_dos += $value->total_barang;
                }
            }

            $total_harga += $value->total_barang;
        }
        $dos = (int) $dos ;
        $pack = (int) $pack ;

        // ==================================== ganti data transaksi ===========================================

        array_walk($transaksi, function(&$item, $key) {
            $item->kode_transaksi = $item->kode_transaksi.''.$item->luar_pulau;
            $product = Product::where('kode_barang', $item->kode_barang)->first();
            if($product != null){
                if(strtolower($item->jenis_kemasan) == 'pack'){
                    $item->jumlah = $item->jumlah;
                }else{
                    $item->jumlah = (int) $item->jumlah / $product->pack;
                }
            }else{
                 $item->jumlah = $item->jumlah;
            }
            $item->id_kasir = User::find($item->id_kasir)->nama;
            $item->id_customer = (Customer::find($item->id_customer) != null) ? Customer::find($item->id_customer)->nama : 'Customer dihapus';
            $item->created_at = $item->created_at->format('d-m-Y H:i:s');
        });
        // ===============================================================================

        $pelanggan = [];
        $jumlah_pelanggan = 0;
        foreach ($transaksi as $key => $value) {
            if(!in_array($value->id_customer, $pelanggan)){
                array_push($pelanggan, $value->id_customer);
                $jumlah_pelanggan++;
            } 
        }

        // ===============================================================================
        
        $tgl1 = Carbon::parse($start_date)->isoFormat('D MMMM Y');
        $tgl2 = Carbon::parse($end_date)->isoFormat('D MMMM Y');
        $tgl = $tgl1.' s/d '.$tgl2;

        return view('report.print_laporan_retur_penjualan', compact('transaksi' ,'per_transaksi','filter', 'penjualan', 'dos', 'pack', 'tgl', 'jumlah', 'jumlah_pelanggan', 'total_harga', 'total_dos', 'total_pack'));
    }