<?php

namespace App\Http\Controllers\master;

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

class PerkiraanDetailController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    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()
    {
        //
    }

    public function cetakRincianTransaksiPDF(Request $request, $id)
    {
        require('fpdf181/Mc_table.php');
        $input = $request->all();

        if ($input['detail']==1) {
            
            if (sizeof($input) > 0) {
                $tanggal = [
                    "tanggal_awal"=>implode("-", array_reverse(explode("-", $input['tanggal_awal']))),
                    "tanggal_akhir"=>implode("-", array_reverse(explode("-", $input['tanggal_akhir']))),
                ];

                $whereRincian = [
                    ['id_perkiraan', '=', $id],
                    ['tanggal_ref', '>=', $tanggal['tanggal_awal']],
                    ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]
                ];

                $saldo_awal_akun = DB::table('jurnal_perkiraan_saldo_awal')->where('id_perkiraan', $id)->sum('nominal');

                $nominal_transaksi_sebelum_tanggal_awal = DB::table('jurnal_umum')->where('id_perkiraan', $id)->where('tanggal_ref', '<', $tanggal['tanggal_awal'])->selectRaw("SUM(CASE WHEN tipe_transaksi = 'D' THEN nominal ELSE 0 END) - SUM(CASE WHEN tipe_transaksi = 'K' THEN nominal ELSE 0 END) AS nominal_transaksi")->first()->nominal_transaksi;

                $data['saldo_awal'] = (float) $saldo_awal_akun + (float) $nominal_transaksi_sebelum_tanggal_awal;

                $data['transaksi'] = arr_jurnal_umum_1([], $whereRincian);

                $data['judul'] = "Laporan Rincian Perkiraan (Transaksi)";

                $data['periode'] = $input['tanggal_awal']." s/d ".$input['tanggal_akhir'];

                $data['akun'] = DB::table('ms_perkiraan')->where('id', $id)->select('kode_perkiraan', 'nama_perkiraan')->first();

                $data['param'] = 'TRANSAKSI';

                $html = view('master.CetakRincianPerkiraan')->with('data',$data);
                return response($html)->header('Content-Type', 'application/pdf');
            } else {
                return redirect()->back();
            }
        } else if ($input['detail'] == 2) {
            return $this->cetakRincianAkunPDF($id, $input);
        }
    }

    private function cetakRincianAkunPDF($id, $input)
    {
        if(sizeof($input) > 0){
            $whereRincian = [
                ['dari_akun', '=', $id]
            ];
            $tanggal = [
                "tanggal_awal"=>implode("-", array_reverse(explode("-", $input['tanggal_awal']))),
                "tanggal_akhir"=>implode("-", array_reverse(explode("-", $input['tanggal_akhir']))),
            ];
            $whereRincian = array_merge($whereRincian, [['tanggal_ref', '>=', $tanggal['tanggal_awal']], ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]]);

            $saldo_awal_akun = DB::table('jurnal_perkiraan_saldo_awal')->where('id_perkiraan', $id)->sum('nominal');

            $nominal_transaksi_sebelum_tanggal_awal = DB::table('jurnal_umum')->where('id_perkiraan', $id)->where('tanggal_ref', '<', $tanggal['tanggal_awal'])->selectRaw("SUM(CASE WHEN tipe_transaksi = 'D' THEN nominal ELSE 0 END) - SUM(CASE WHEN tipe_transaksi = 'K' THEN nominal ELSE 0 END) AS nominal_transaksi")->first()->nominal_transaksi;

            $data['saldo_awal'] = (float) $saldo_awal_akun + (float) $nominal_transaksi_sebelum_tanggal_awal;

            $data['transaksi'] = arr_history_account([], $whereRincian);

            $data['judul'] = "Laporan Rincian Perkiraan (Akun)";

            $data['periode'] = $input['tanggal_awal']." s/d ".$input['tanggal_akhir'];

            $data['akun'] = DB::table('ms_perkiraan')->where('id', $id)->select('kode_perkiraan', 'nama_perkiraan')->first();

            $data['param'] = 'AKUN';

            $html = view('master.CetakRincianPerkiraan')->with('data',$data);
                return response($html)->header('Content-Type', 'application/pdf');
        } else {
            return redirect()->back();
        }
    }

    public function rincianAkun(Request $request, $id)
    {
        $input = $request->all();
        
        if($input['detail']==1){
            $arr_href = [
                "jurnal_pembelian"=>url("pembelian/edit_harga"),
                "jurnal_orderservice"=>url("order_service/edit"),
                "jurnal_sewa"=>url("sewa/edit"),
                "jurnal_penjualan"=>url("penjualan/edit"),
                "jurnal_penjualan_retur"=>url("penjualan/retur_penjualan/edit"),
                "jurnal_penjualan_retur_nonfaktur"=>url("penjualan/retur-penjualan-nonfaktur/edit"),
                "jurnal_sumber_dana"=>url("sumberdana/transaksi/pengeluaran/edit"),
                "jurnal_sales_contract_tagihan"=>url("/"),
                "jurnal_pembelian_bayar"=>url("pembelian/pembayaran/edit/"),
                "jurnal_penjualan_bayar"=>url("/"),
                "jurnal_piutangawal_bayar"=>url("/"),
                "jurnal_orderservice_bayar"=>url("/"),
                "jurnal_sewa_bayar"=>url("/"),
                "jurnal_upah_produksi_pegawai"=>url("upahproduksi/edit/"),
                "jurnal_upah_produksi_rekanan"=>url("upahproduksi/edit-rekanan/"),
                "jurnal_upahtenun"=>url("/"),
                "jurnal_kasbon_pegawai"=>url("piutang_pegawai/"),
                "jurnal_kasbon_rekanan"=>url("piutang_rekanan/"),
                "jurnal_penerimaan_pembayaran_kasbon_pegawai"=>url("/"),
                "jurnal_penerimaan_pembayaran_kasbon_rekanan"=>url("/"),
                "jurnal_rtp"=>url("retur-tagihan-piutang/edit/"),
                "jurnal_rtpa"=>url("retur-tagihan-piutang-awal/edit/"),
                "lainnya"=>"#",
            ];
            $whereRincian = [
                    ['id_perkiraan', '=', $id],
                ];
            if(sizeof($input)>0){
                $tanggal = [
                    "tanggal_awal"=>implode("-", array_reverse(explode("-", $input['tanggal_awal']))),
                    "tanggal_akhir"=>implode("-", array_reverse(explode("-", $input['tanggal_akhir']))),
                ];
                $whereRincian = array_merge($whereRincian, [['tanggal_ref', '>=', $tanggal['tanggal_awal']], ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]]);
            }

            // Ini opsi pertama memakai helper
            $data = arr_jurnal_umum_1([], $whereRincian);
            $data = new Collection($data);

            // Opsi ke-2 bisa pake ini
            // $data = DB::table('jurnal_umum')->where('id_perkiraan', $id)->whereBetween('tanggal_ref', [$tanggal['tanggal_awal'], $tanggal['tanggal_akhir']])->orderBy('tanggal_ref', 'asc');

            return Datatables::of($data)
                ->addIndexColumn()
                ->addColumn('tanggal_ref', function($data){
                    return tgl_full($data->tanggal_ref, "");
                })
                ->editColumn("nomor_ref", function($data) use ($arr_href){
                    if ($data->keterangan_lain == "jurnal_pembelian_bayar") {
                        return '<a href="javascript:void(0)" onclick="showModalPembBayar('.$data->id_ref.')">'.$data->nomor_ref.'</a>';
                    } else if ($data->keterangan_lain == "jurnal_sales_contract_tagihan") {
                        return "<a href='".$arr_href[$data->keterangan_lain]."/".$data->id_ref."/edit'>$data->nomor_ref</a>";
                    } else if ($data->keterangan_lain == "jurnal_penjualan_bayar") {
                        return '<a href="javascript:void(0)" onclick="showModalPenjBayar('.$data->id_ref.')">'.$data->nomor_ref.'</a>';
                    } else if ($data->keterangan_lain == "jurnal_piutangawal_bayar") {
                        return '<a href="javascript:void(0)" onclick="showModalPiutangAwalBayar('.$data->id_ref.')">'.$data->nomor_ref.'</a>';
                    } else if ($data->keterangan_lain == "jurnal_orderservice_bayar") {
                        return '<a href="javascript:void(0)" onclick="showModalOrderServiceBayar('.$data->id_ref.')">'.$data->nomor_ref.'</a>';
                    } else if ($data->keterangan_lain == "jurnal_sewa_bayar") {
                        return '<a href="javascript:void(0)" onclick="showModalSewaBayar('.$data->id_ref.')">'.$data->nomor_ref.'</a>';
                    } else if ($data->keterangan_lain == "jurnal_upahtenun") {
                        return $data->nomor_ref;
                    } else if ($data->keterangan_lain == "jurnal_kasbon_pegawai") {
                        return "<a href='".$arr_href[$data->keterangan_lain]."/".$data->id_ref."/edit'>$data->nomor_ref</a>";
                    } else if ($data->keterangan_lain == "jurnal_kasbon_rekanan") {
                        return "<a href='".$arr_href[$data->keterangan_lain]."/".$data->id_ref."/edit'>$data->nomor_ref</a>";
                    } else if ($data->keterangan_lain == "jurnal_penerimaan_pembayaran_kasbon_pegawai") {
                        $kasbon_pegawai = DB::table('keu_kasbon_pegawai_bayar')->where('id', $data->id_ref)->first();
                        return "<a href='".url('piutang_pegawai/'.$kasbon_pegawai->id_kasbon_pegawai.'/bayar?edit=true&idbayar='.$data->id_ref)."'>".$data->nomor_ref."</a>";
                    } else if ($data->keterangan_lain == "jurnal_penerimaan_pembayaran_kasbon_rekanan") {
                        $kasbon_rekanan = DB::table('keu_kasbon_rekanan_bayar')->where('id', $data->id_ref)->first();
                        return "<a href='".url('piutang_rekanan/'.$kasbon_rekanan->id_kasbon_rekanan.'/bayar?edit=true&idbayar='.$data->id_ref)."'>".$data->nomor_ref."</a>";
                    } else {
                        return "<a href='".$arr_href[$data->keterangan_lain]."/".$data->id_ref."'>$data->nomor_ref</a>";
                    }
                })
                ->addColumn("debet", function($data){
                    if($data->tipe_transaksi=="debet" || $data->tipe_transaksi=="D"){
                        return $data->nominal;
                    }
                    return 0;
                })
                ->addColumn("kredit", function($data){
                    if($data->tipe_transaksi=="kredit" || $data->tipe_transaksi=="K"){
                        return $data->nominal;
                    }
                    return 0;
                })
                ->addColumn("saldo", function($data){
                    if($data->tipe_transaksi=="debet" || $data->tipe_transaksi=="D"){
                        $debet = $data->nominal;
                    } else {
                        $debet = 0;
                    }

                    if($data->tipe_transaksi=="kredit" || $data->tipe_transaksi=="K"){
                        $kredit = $data->nominal;
                    } else {
                        $kredit = 0;
                    }

                    if ($data->saldo_normal == "D") {
                        return $debet - $kredit;
                    } else if ($data->saldo_normal == "K") {
                        return $kredit - $debet;
                    }
                })
                ->make(true);

        }else if($input['detail']==2){
            return $this->rincianDetail($id, $input);

        }
    }

    private function rincianDetail($id, $input)
    {
        $whereRincian = [
                ['dari_akun', '=', $id],
            ];
        
        if(sizeof($input)>0){
            $tanggal = [
                "tanggal_awal"=>implode("-", array_reverse(explode("-", $input['tanggal_awal']))),
                "tanggal_akhir"=>implode("-", array_reverse(explode("-", $input['tanggal_akhir']))),
            ];
            $whereRincian = array_merge($whereRincian, [['tanggal_ref', '>=', $tanggal['tanggal_awal']], ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]]);
        }

        // $data = DB::table("histori_akun")->where($whereRincian)->orderByRaw("tanggal_ref DESC, dari_akun ASC");
        $data = arr_history_account([], $whereRincian);
        // $data = DB::table(DB::raw("(".arr_history_account([])->toSql().") AS j"))->where($whereRincian)->orderByRaw("tanggal_ref ASC")->get();
        $data = new Collection($data);

        return Datatables::of($data)
            ->addIndexColumn()
            ->addColumn("tanggal_ref", function($data) {
                return tgl_full($data->tanggal_ref, "");
            })
            ->editColumn("nomor_ref", function($data) {
                $ke_akun = [
                        "kode_perkiraan"=>$data->ke_akun_kode_perkiraan,
                        "nama_perkiraan"=>$data->ke_akun_nama_perkiraan,
                    ];
                return format_akun_perkiraan($ke_akun);
            })
            ->addColumn("debet", function($data){
                if($data->dari_akun_tipe_id_akun=="debet" || $data->dari_akun_tipe_id_akun=="D"){
                    return $data->nominal_transaksi;
                }
                return 0;
            })
            ->addColumn("kredit", function($data){
                if($data->dari_akun_tipe_id_akun=="kredit" || $data->dari_akun_tipe_id_akun=="K"){
                    return $data->nominal_transaksi;
                }
                return 0;
            })
            ->addColumn("saldo", function($data){
                    if($data->dari_akun_tipe_id_akun=="debet" || $data->dari_akun_tipe_id_akun=="D"){
                        $debet = $data->nominal_transaksi;
                    } else {
                        $debet = 0;
                    }

                    if($data->dari_akun_tipe_id_akun=="kredit" || $data->dari_akun_tipe_id_akun=="K"){
                        $kredit = $data->nominal_transaksi;
                    } else {
                        $kredit = 0;
                    }

                    return $debet - $kredit;
                })
            ->make(true);
    }

    public function getPembayaranHutang($id_transaksi = null)
    {
        $d_data = DB::table('pem_pembelian_bayar as pb')
                ->join('pem_pembelian as p', 'p.id_pembelian', 'pb.id_pembelian')
                ->join('ms_penyedia as mp', 'mp.id_penyedia', 'p.id_penyedia')
                ->where('pb.id_transaksi_bayar', $id_transaksi)
                ->select('pb.id_belibayar', 'pb.id_pembelian', 'p.no_faktur', 'p.sufix_no_faktur', 'mp.nama', 'pb.jum_bayar');

        return Datatables::of($d_data)
                ->addColumn("nofaktur", function($d_data){
                    return $d_data->no_faktur.$d_data->sufix_no_faktur;
                })
                ->addColumn("penyedia", function($d_data){
                    return $d_data->nama;
                })
                ->addColumn("jum_bayar", function($d_data){
                    return rupiah2($d_data->jum_bayar);
                })
                ->addColumn("aksi", function($d_data){
                    return '<a href="'.url('pembelian/pembayaran/edit/'.$d_data->id_belibayar.'/'.$d_data->id_pembelian).'" class="btn btn-sm btn-warning"><i class="fa fa-pencil"></i></a>';
                })
                ->make(true);
    }

    public function getPembayaranPiutang($id_transaksi = null)
    {
        $d_data = DB::table('pen_penjualan_bayar as pb')
                ->join('pen_penjualan as p', 'p.id_penjualan', 'pb.id_penjualan')
                ->join('ms_pelanggan as mp', 'mp.id_pelanggan', 'p.id_pelanggan')
                ->where('pb.id_transaksi_bayar', $id_transaksi)
                ->select('pb.id_jualbayar', 'pb.id_penjualan', 'p.no_faktur', 'p.sufix_no_faktur', 'mp.nama', 'pb.jum_bayar', 'p.mata_uang');

        return Datatables::of($d_data)
                ->addColumn("nofaktur", function($d_data){
                    return $d_data->no_faktur.$d_data->sufix_no_faktur;
                })
                ->addColumn("pelanggan", function($d_data){
                    return $d_data->nama;
                })
                ->addColumn("jum_bayar", function($d_data){
                    return rupiah2($d_data->jum_bayar);
                })
                ->addColumn("aksi", function($d_data){
                    return '<a href="'.url('penjualan/terima_tagihan?id='.$d_data->id_penjualan.'&idjualbayar='.$d_data->id_jualbayar.'&currency='.$d_data->mata_uang.'&as=piutang_penjualan&edit=true').'" class="btn btn-sm btn-warning"><i class="fa fa-pencil"></i></a>';
                })
                ->make(true);
    }

    public function getPembayaranPiutangAwal($id_transaksi = null)
    {
        $d_data = DB::table('pen_piutangawal_bayar as pb')
                ->join('pen_piutangawal as p', 'p.id', 'pb.id_piutangawal')
                ->join('ms_pelanggan as mp', 'mp.id_pelanggan', 'p.id_pelanggan')
                ->where('pb.id_transaksi_bayar', $id_transaksi)
                ->select('pb.id', 'pb.id_piutangawal', 'pb.id_transaksi_bayar', 'p.no_faktur', 'p.sufix_no_faktur', 'mp.nama', 'pb.jum_bayar');

        return Datatables::of($d_data)
                ->addColumn("nofaktur", function($d_data){
                    return $d_data->no_faktur.$d_data->sufix_no_faktur;
                })
                ->addColumn("pelanggan", function($d_data){
                    return $d_data->nama;
                })
                ->addColumn("jum_bayar", function($d_data){
                    return rupiah2($d_data->jum_bayar);
                })
                ->addColumn("aksi", function($d_data){
                    return '<a href="'.url('penjualan/terima_tagihan?id='.$d_data->id_piutangawal.'&id_transaksi='.$d_data->id_transaksi_bayar.'&as=piutang_awal&edit=true').'" class="btn btn-sm btn-warning"><i class="fa fa-pencil"></i></a>';
                })
                ->make(true);
    }

    public function getPembayaranOrderService($id_transaksi = null)
    {
        $d_data = DB::table('serv_orderservice_bayar as osb')
                ->join('serv_orderservice as os', 'os.id', 'osb.id_orderservice')
                ->join('ms_penyedia as mp', 'mp.id_penyedia', 'os.id_penyedia')
                ->where('osb.id_transaksi_bayar', $id_transaksi)
                ->select('osb.id', 'osb.id_orderservice', 'os.no_nota', 'os.sufix_no_nota', 'mp.nama', 'osb.jum_bayar');

        return Datatables::of($d_data)
                ->addColumn("nonota", function($d_data){
                    return $d_data->no_nota.$d_data->sufix_no_nota;
                })
                ->addColumn("penyedia", function($d_data){
                    return $d_data->nama;
                })
                ->addColumn("jum_bayar", function($d_data){
                    return rupiah2($d_data->jum_bayar);
                })
                ->addColumn("aksi", function($d_data){
                    return '<a href="'.url('order_service/pembayaran/edit/'.$d_data->id.'/'.$d_data->id_orderservice).'" class="btn btn-sm btn-warning"><i class="fa fa-pencil"></i></a>';
                })
                ->make(true);
    }

    public function getPembayaranSewa($id_transaksi = null)
    {
        $d_data = DB::table('pem_sewa_bayar as psb')
                ->join('pem_sewa as ps', 'ps.id', 'psb.id_sewa')
                ->join('ms_penyedia as mp', 'mp.id_penyedia', 'ps.id_penyedia')
                ->where('psb.id_transaksi_bayar', $id_transaksi)
                ->select('psb.id', 'psb.id_sewa', 'ps.no_faktur', 'mp.nama', 'psb.jumlah_bayar');

        return Datatables::of($d_data)
                ->addColumn("penyedia", function($d_data){
                    return $d_data->nama;
                })
                ->addColumn("jumlah_bayar", function($d_data){
                    return rupiah2($d_data->jumlah_bayar);
                })
                ->addColumn("aksi", function($d_data){
                    return '<a href="'.url('sewa/pembayaran/edit/'.$d_data->id.'/'.$d_data->id_sewa).'" class="btn btn-sm btn-warning"><i class="fa fa-pencil"></i></a>';
                })
                ->make(true);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  \App\Perkiraan  $perkiraan
     * @return \Illuminate\Http\Response
     */
    public function show(Request $request, $id)
    {
        //
        $data['jurnal_rincian'] = Perkiraan::find($id);
        $data['akun'] = Perkiraan::where(["used"=>1])->get();
        $data['detail'] = ($request->get('detail') == "") ? 1 : $request->get('detail');
        // dd($data);
        return view("master.PerkiraanRincianList", $data);
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  \App\Perkiraan  $perkiraan
     * @return \Illuminate\Http\Response
     */
    public function edit(Perkiraan $perkiraan)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \App\Perkiraan  $perkiraan
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, Perkiraan $perkiraan)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  \App\Perkiraan  $perkiraan
     * @return \Illuminate\Http\Response
     */
    public function destroy(Perkiraan $perkiraan)
    {
        //
    }

    public function cetak_excel(Request $request)
    {
        $input = $request->all();

        if ($input['detail']==1) {
            if (sizeof($input) > 0) {
                $tanggal = [
                    "tanggal_awal"=>implode("-", array_reverse(explode("-", $input['tanggal_awal']))),
                    "tanggal_akhir"=>implode("-", array_reverse(explode("-", $input['tanggal_akhir']))),
                ];

                $whereRincian = [
                    ['id_perkiraan', '=', $input['id_akun']],
                    ['tanggal_ref', '>=', $tanggal['tanggal_awal']],
                    ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]
                ];

                $saldo_awal_akun = DB::table('jurnal_perkiraan_saldo_awal')->where('id_perkiraan', $input['id_akun'])->sum('nominal');

                $nominal_transaksi_sebelum_tanggal_awal = DB::table('jurnal_umum')->where('id_perkiraan', $input['id_akun'])->where('tanggal_ref', '<', $tanggal['tanggal_awal'])->selectRaw("SUM(CASE WHEN tipe_transaksi = 'D' THEN nominal ELSE 0 END) - SUM(CASE WHEN tipe_transaksi = 'K' THEN nominal ELSE 0 END) AS nominal_transaksi")->first()->nominal_transaksi;

                $data['saldo_awal'] = (float) $saldo_awal_akun + (float) $nominal_transaksi_sebelum_tanggal_awal;

                $data['transaksi'] = arr_jurnal_umum_1([], $whereRincian);

                $data['judul'] = "Laporan Rincian Perkiraan (Transaksi)";

                $data['periode'] = $input['tanggal_awal']." s/d ".$input['tanggal_akhir'];

                $data['akun'] = DB::table('ms_perkiraan')->where('id', $input['id_akun'])->select('kode_perkiraan', 'nama_perkiraan')->first();

                Excel::create("Rincian Perkiraan (Transaksi)", function($excel) use ($data)
                {
                    $excel->sheet("Rincian Perkiraan", function($sheet) use ($data){
                        $sheet->mergeCells('A1:B1');
                        $sheet->row(1, ["Laporan Rincian Perkiraan (Transaksi)"]);
                        $sheet->row(2, ["Akun", "(".$data['akun']->kode_perkiraan.") ".$data['akun']->nama_perkiraan]);
                        $sheet->row(3, ["Tanggal", $data['periode']]);
                        $sheet->row(4, []);
                        $sheet->row(5, [
                            "No",
                            "Tanggal",
                            "No. Referensi",
                            "Debet",
                            "Kredit",
                            "Saldo"
                        ]);

                        $sheet->row(6, [
                            1,
                            "",
                            "SALDO AWAL",
                            "",
                            "",
                            $data['saldo_awal']
                        ]);

                        $sub_saldo = $data['saldo_awal'];
                        $_key = 7;
                        $no = 2;
                        $debet = 0;
                        $kredit = 0;
                        $total_debet = 0;
                        $total_kredit = 0;
                        $saldo = 0;

                        foreach ($data['transaksi'] as $key => $value) {
                            if ($value->tipe_transaksi=="debet" || $value->tipe_transaksi=="D") {
                                $sub_saldo += $value->nominal;
                                $debet = $value->nominal;
                            } else {
                                $sub_saldo -= $value->nominal;
                                $kredit = $value->nominal;
                            }

                            $sheet->row($key+7, [
                                $no,
                                date_db($value->tanggal_ref, true, 2),
                                $value->nomor_ref,
                                $debet,
                                $kredit,
                                $sub_saldo
                            ]);

                            $total_debet += $debet;
                            $total_kredit += $kredit;

                            $no++;
                            $_key = $key+7+1;
                        }

                        $sisa_saldo = $sub_saldo;

                        $sheet->row($_key, [
                            "",
                            "",
                            "JUMLAH",
                            $total_debet,
                            $total_kredit,
                            $sisa_saldo
                        ]);
                    });
                })->export("xlsx");
            } else {
                return redirect()->back();
            }
        } else {
            if(sizeof($input) > 0){
                $whereRincian = [
                    ['dari_akun', '=', $input['id_akun']]
                ];
                $tanggal = [
                    "tanggal_awal"=>implode("-", array_reverse(explode("-", $input['tanggal_awal']))),
                    "tanggal_akhir"=>implode("-", array_reverse(explode("-", $input['tanggal_akhir']))),
                ];
                $whereRincian = array_merge($whereRincian, [['tanggal_ref', '>=', $tanggal['tanggal_awal']], ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]]);

                $saldo_awal_akun = DB::table('jurnal_perkiraan_saldo_awal')->where('id_perkiraan', $input['id_akun'])->sum('nominal');

                $nominal_transaksi_sebelum_tanggal_awal = DB::table('jurnal_umum')->where('id_perkiraan', $input['id_akun'])->where('tanggal_ref', '<', $tanggal['tanggal_awal'])->selectRaw("SUM(CASE WHEN tipe_transaksi = 'D' THEN nominal ELSE 0 END) - SUM(CASE WHEN tipe_transaksi = 'K' THEN nominal ELSE 0 END) AS nominal_transaksi")->first()->nominal_transaksi;

                $data['saldo_awal'] = (float) $saldo_awal_akun + (float) $nominal_transaksi_sebelum_tanggal_awal;

                $data['transaksi'] = arr_history_account([], $whereRincian);

                $data['judul'] = "Laporan Rincian Perkiraan (Akun)";

                $data['periode'] = $input['tanggal_awal']." s/d ".$input['tanggal_akhir'];

                $data['akun'] = DB::table('ms_perkiraan')->where('id', $input['id_akun'])->select('kode_perkiraan', 'nama_perkiraan')->first();

                Excel::create("Rincian Perkiraan (Transaksi)", function($excel) use ($data)
                {
                    $excel->sheet("Rincian Perkiraan", function($sheet) use ($data){
                        $sheet->mergeCells('A1:B1');
                        $sheet->row(1, ["Laporan Rincian Perkiraan (Akun)"]);
                        $sheet->row(2, ["Akun", "(".$data['akun']->kode_perkiraan.") ".$data['akun']->nama_perkiraan]);
                        $sheet->row(3, ["Tanggal", $data['periode']]);
                        $sheet->row(4, []);
                        $sheet->row(5, [
                            "No",
                            "Tanggal",
                            "Akun",
                            "Debet",
                            "Kredit",
                            "Saldo"
                        ]);

                        $sheet->row(6, [
                            1,
                            "",
                            "SALDO AWAL",
                            "",
                            "",
                            $data['saldo_awal']
                        ]);

                        $sub_saldo = $data['saldo_awal'];
                        $_key = 7;
                        $no = 2;
                        $debet = 0;
                        $kredit = 0;
                        $total_debet = 0;
                        $total_kredit = 0;
                        $saldo = 0;

                        foreach ($data['transaksi'] as $key => $value) {
                            if ($value->dari_akun_tipe_id_akun=="debet" || $value->dari_akun_tipe_id_akun=="D") {
                                $sub_saldo += $value->nominal_transaksi;
                                $debet = $value->nominal_transaksi;
                            } else {
                                $sub_saldo -= $value->nominal_transaksi;
                                $kredit = $value->nominal_transaksi;
                            }

                            $sheet->row($key+7, [
                                $no,
                                date_db($value->tanggal_ref, true, 2),
                                "(".$value->ke_akun_kode_perkiraan.") ".$value->ke_akun_nama_perkiraan,
                                $debet,
                                $kredit,
                                $sub_saldo
                            ]);

                            $total_debet += $debet;
                            $total_kredit += $kredit;

                            $no++;
                            $_key = $key+7+1;
                        }

                        $sisa_saldo = $sub_saldo;

                        $sheet->row($_key, [
                            "",
                            "",
                            "JUMLAH",
                            $total_debet,
                            $total_kredit,
                            $sisa_saldo
                        ]);
                    });
                })->export("xlsx");
            } else {
                return redirect()->back();
            }
        }
    }

    public function cetak_excel_old(Request $request)
    {
        $input = $request->all();
        if($input['detail']==1){
            $whereRincian = [
                    ['id_perkiraan', '=', $input['id_akun']],
                ];
            $akun = DB::table("ms_perkiraan")->where([["id", "=", $input['id_akun'] ]])->first();

            if(sizeof($input)>0){
                $tanggal = [
                    "tanggal_awal"=>date_db($input['tanggal_awal'], false, 2),
                    "tanggal_akhir"=>date_db($input['tanggal_akhir'], false, 2),
                ];
                $whereRincian = array_merge($whereRincian, [['tanggal_ref', '>=', $tanggal['tanggal_awal']], ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]]);
            }
            $data = arr_jurnal_umum_1([], $whereRincian);
            // $data = DB::table(DB::raw("(".jurnal_umum_1()->toSql().") AS j"))->where($whereRincian)->orderByRaw("tanggal_ref ASC, nomor_ref ASC")->get();
            // $data = AllJurnal::where($whereRincian)->orderByRaw("tanggal_ref ASC, id_ref ASC")->get();
            // dd($data);

            // EXCEL transaksi
            Excel::create("Rincian Perkiraan (Transaksi) - $input[id_akun] - ".$input['tanggal_akhir'], function($excel) use ($input, $akun, $data)
            {
                $excel->sheet("Rincian", function($sheet) use ($input, $akun, $data){
                    $sheet->mergeCells('A1:B1');
                    $sheet->row(1, ["Laporan Rincian Akun"]);
                    $sheet->row(2, ["Akun", format_akun_perkiraan($akun)]);
                    $sheet->row(3, ["Tanggal", "$input[tanggal_awal] s/d $input[tanggal_akhir]"]);
                    $sheet->row(4, []);
                    $sheet->row(5, [
                        "No",
                        "Tanggal",
                        "No. Referensi",
                        "Keterangan",
                        "Debet",
                        "Kredit"
                    ]);
                    foreach ($data as $key => $value) {
                        // $ke_akun = [
                        //         "kode_perkiraan"=>$value->ke_akun_kode_perkiraan,
                        //         "nama_perkiraan"=>$value->ke_akun_nama_perkiraan,
                        //     ];

                        $debet = 0;
                        if($value->tipe_transaksi=="debet" || $value->tipe_transaksi=="D"){
                            $debet = $value->nominal;
                        }

                        $kredit = 0;
                        if($value->tipe_transaksi=="kredit" || $value->tipe_transaksi=="K"){
                            $kredit = $value->nominal;
                        }

                        // if ($value->saldo_normal == "D") {
                        //     $saldo =  $debet - $kredit;
                        // } else if ($data->saldo_normal == "K") {
                        //     $saldo =  $kredit - $debet;
                        // }

                        $sheet->row($key+6, [
                            $key+1,
                            date_db($value->tanggal_ref, true, 2),
                            $value->nomor_ref,
                            $value->keterangan,
                            $debet,
                            $kredit
                        ]);
                    }
                });
            })->export("xlsx");

        }else if($input['detail']==2){
            $whereRincian = [
                    ['dari_akun', '=', $input['id_akun']],
                ];
            $akun = DB::table("ms_perkiraan")->where([["id", "=", $input['id_akun'] ]])->first();
            
            if(sizeof($input)>0){
                $tanggal = [
                    "tanggal_awal"=>date_db($input['tanggal_awal'], false, 2),
                    "tanggal_akhir"=>date_db($input['tanggal_akhir'], false, 2),
                ];
                $whereRincian = array_merge($whereRincian, [['tanggal_ref', '>=', $tanggal['tanggal_awal']], ['tanggal_ref', '<=', $tanggal['tanggal_akhir']]]);
            }

            // $data = DB::table("histori_akun")->where($whereRincian)->orderByRaw("tanggal_ref ASC, id_ref ASC")->get();
            $data = arr_history_account([], $whereRincian);

            // EXCEL akun
            Excel::create("Rincian Transaksi Akun (Akun) - $input[id_akun] - ".$input['tanggal_akhir'], function($excel) use ($input, $akun, $data)
            {
                $excel->sheet("Rincian", function($sheet) use ($input, $akun, $data){
                    $sheet->mergeCells('A1:B1');
                    $sheet->row(1, ["Laporan Rincian Akun"]);
                    $sheet->row(2, ["Akun", format_akun_perkiraan($akun)]);
                    $sheet->row(3, ["Tanggal", "$input[tanggal_awal] s/d $input[tanggal_akhir]"]);
                    $sheet->row(4, []);
                    $sheet->row(5, [
                        "No",
                        "Tanggal",
                        "No. Referensi",
                        "Keterangan",
                        "Debet",
                        "Kredit"
                    ]);
                    foreach ($data as $key => $value) {
                        $ke_akun = [
                                "kode_perkiraan"=>$value->ke_akun_kode_perkiraan,
                                "nama_perkiraan"=>$value->ke_akun_nama_perkiraan,
                            ];

                        $debet = 0;
                        if($value->dari_akun_tipe_id_akun=="debet" || $value->dari_akun_tipe_id_akun=="D"){
                            $debet = $value->nominal_transaksi;
                        }

                        $kredit = 0;
                        if($value->dari_akun_tipe_id_akun=="kredit" || $value->dari_akun_tipe_id_akun=="K"){
                            $kredit = $value->nominal_transaksi;
                        }

                        // $saldo = $debet - $kredit;

                        $sheet->row($key+6, [
                            $key+1,
                            date_db($value->tanggal_ref, true, 2),
                            format_akun_perkiraan($ke_akun),
                            $value->keterangan,
                            $debet,
                            $kredit
                        ]);
                    }
                });
            })->export("xlsx");
        }
    }
}
