<?php

use alhimik1986\PhpExcelTemplator\PhpExcelTemplator;
use alhimik1986\PhpExcelTemplator\params\ExcelParam;
use alhimik1986\PhpExcelTemplator\params\CallbackParam;
use alhimik1986\PhpExcelTemplator\setters\CellSetterArrayValueSpecial;
use PhpOffice\PhpSpreadsheet\IOFactory;

use NN\files;
use NN\Post;
use NN\Session;
use NN\Module\DB;
use NN\Module\Help;
use NN\Link;
use NN\Module\DD;
use NN\Enc;
use NN\module\View;
use xls\Param;

class Excel{

    public function akun($year = "", $app = null, $status = null){
        $data = [];
        $data["kode"] = [];
        $data["dk"] = [];
        $data["no"] = [];
        $data["nama"] = [];
        $data["sad"] = [];
        $data["sak"] = [];

        $condition = "";
        if($app != null){
            $condition = " WHERE acc.app = '$app' ";
        }

        $cond2 = "";
        $name = "acc.nama";
        $kode = "acc.kode";
        if($status == "u"){
            $kode = "acc.main kode";
            $name = "acc.nama_main nama";
            $cond2 = " GROUP BY acc.main ";
        }else{
            $kode = "acc.kode";
            $name = "acc.nama";
            $cond2 = " GROUP BY acc.kode ";
        }

        $acc = DB::query_result_object("SELECT 
            $kode
            , acc.dk
            , $name 
            , sum(ifnull(sa.debit,0)) sad 
            , sum(ifnull(sa.kredit,0)) sak 
            , sum(ifnull(j.debit,0)) jd 
            , sum(ifnull(j.kredit,0)) jk 
            , sum(ifnull(f.debit,0)) fd 
            , sum(ifnull(f.kredit,0)) fk 
            , sum(ifnull(m.debit,0)) md 
            , sum(ifnull(m.kredit,0)) mk 
            , sum(ifnull(a.debit,0)) ad 
            , sum(ifnull(a.kredit,0)) ak 
            , sum(ifnull(me.debit,0)) med 
            , sum(ifnull(me.kredit,0)) mek 
            , sum(ifnull(jn.debit,0)) jnd 
            , sum(ifnull(jn.kredit,0)) jnk 
            , sum(ifnull(ju.debit,0)) jud 
            , sum(ifnull(ju.kredit,0)) juk 
            , sum(ifnull(ag.debit,0)) agd 
            , sum(ifnull(ag.kredit,0)) agk 
            , sum(ifnull(s.debit,0)) sd 
            , sum(ifnull(s.kredit,0)) sk 
            , sum(ifnull(o.debit,0)) od 
            , sum(ifnull(o.kredit,0)) ok 
            , sum(ifnull(na.debit,0)) nd 
            , sum(ifnull(na.kredit,0)) nk 
            , sum(ifnull(de.debit,0)) ded 
            , sum(ifnull(de.kredit,0)) dek 
        FROM acc
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl < '$year-01-01'
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl < '$year-01-01'
            ) a GROUP BY kode 
        ) sa ON sa.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-01%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-01%'
            ) a GROUP BY kode 
        ) j ON j.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-02%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-02%'
            ) a GROUP BY kode 
        ) f ON f.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-03%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-03%'
            ) a GROUP BY kode 
        ) m ON m.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-04%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-04%'
            ) a GROUP BY kode 
        ) a ON a.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-05%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-05%'
            ) a GROUP BY kode 
        ) me ON me.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-06%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-06%'
            ) a GROUP BY kode 
        ) jn ON jn.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-07%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-07%'
            ) a GROUP BY kode 
        ) ju ON ju.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-08%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-08%'
            ) a GROUP BY kode 
        ) ag ON ag.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-09%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-09%'
            ) a GROUP BY kode 
        ) s ON s.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-10%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-10%'
            ) a GROUP BY kode 
        ) o ON o.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-11%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-11%'
            ) a GROUP BY kode 
        ) na ON na.kode = acc.kode
        LEFT JOIN (
            SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
                SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-12%' 
                UNION ALL
                SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-12%'
            ) a GROUP BY kode 
        ) de ON de.kode = acc.kode
        $condition
        $cond2
        ");
        foreach ($acc as $key => $d) {
            $dtot = 0;
            $ktot = 0;
            $data["no"][] = $key + 1;
            $data["dk"][] = $d->dk;
            $data["kode"][] = $d->kode;
            $data["nama"][] = $d->nama;
            $data["sad"][] = $d->sad;
            $dtot += $d->sad;
            $data["sak"][] = $d->sak;
            $ktot += $d->sak;
            $data["jd"][] = $d->jd; 
            $dtot += $d->jd; 
            $data["jk"][] = $d->jk; 
            $ktot += $d->jk; 
            $data["fd"][] = $d->fd; 
            $dtot += $d->fd; 
            $data["fk"][] = $d->fk; 
            $ktot += $d->fk; 
            $data["md"][] = $d->md; 
            $dtot += $d->md; 
            $data["mk"][] = $d->mk; 
            $ktot += $d->mk; 
            $data["ad"][] = $d->ad; 
            $dtot += $d->ad; 
            $data["ak"][] = $d->ak; 
            $ktot += $d->ak; 
            $data["med"][] = $d->med; 
            $dtot += $d->med; 
            $data["mek"][] = $d->mek; 
            $ktot += $d->mek; 
            $data["jnd"][] = $d->jnd; 
            $dtot += $d->jnd; 
            $data["jnk"][] = $d->jnk; 
            $ktot += $d->jnk; 
            $data["jud"][] = $d->jud; 
            $dtot += $d->jud; 
            $data["juk"][] = $d->juk; 
            $ktot += $d->juk; 
            $data["agd"][] = $d->agd; 
            $dtot += $d->agd; 
            $data["agk"][] = $d->agk; 
            $ktot += $d->agk; 
            $data["sd"][] = $d->sd; 
            $dtot += $d->sd; 
            $data["sk"][] = $d->sk; 
            $ktot += $d->sk; 
            $data["od"][] = $d->od; 
            $dtot += $d->od; 
            $data["ok"][] = $d->ok; 
            $ktot += $d->ok; 
            $data["nd"][] = $d->nd; 
            $dtot += $d->nd; 
            $data["nk"][] = $d->nk; 
            $ktot += $d->nk; 
            $data["ded"][] = $d->ded; 
            $dtot += $d->ded; 
            $data["dek"][] = $d->dek; 
            $ktot += $d->dek; 
            $data["nmd"][] = $dtot; 
            $data["nmk"][] = $ktot; 
        }
        return $data;
    }

    public static function all($app=null, $status=null, $thn = null){
        ini_set('display_errors', 1);
        $year = date('Y');
        if($thn != null){
            $year = $thn;
        }
        $akun = (new self)->akun($year, $app, $status);
        $file = SETUP_PATH.'excel/accounting-warpeka-'.$year.'.xlsx';
        $filex = SETUP_PATH.'/excel/template.xlsx';

        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        $newPar = new Param();

        $newPar->add('no', $akun);
        $newPar->add('kode', $akun);
        $newPar->add('dk', $akun);
        $newPar->add('nama', $akun);
        $newPar->add('sad', $akun);
        $newPar->add('sak', $akun);
        $newPar->add('jd', $akun);
        $newPar->add('jk', $akun);
        $newPar->add('fd', $akun);
        $newPar->add('fk', $akun);
        $newPar->add('md', $akun);
        $newPar->add('mk', $akun);
        $newPar->add('ad', $akun);
        $newPar->add('ak', $akun);
        $newPar->add('med', $akun);
        $newPar->add('mek', $akun);
        $newPar->add('jnd', $akun);
        $newPar->add('jnk', $akun);
        $newPar->add('jud', $akun);
        $newPar->add('juk', $akun);
        $newPar->add('agd', $akun);
        $newPar->add('agk', $akun);
        $newPar->add('sd', $akun);
        $newPar->add('sk', $akun);
        $newPar->add('od', $akun);
        $newPar->add('ok', $akun);
        $newPar->add('nd', $akun);
        $newPar->add('nk', $akun);
        $newPar->add('ded', $akun);
        $newPar->add('dek', $akun);
        $newPar->add('nmd', $akun);
        $newPar->add('nmk', $akun);

        $params = $newPar->get();

        $callbacks = [
            
        ];

        // PhpExcelTemplator::saveToFile($filex, $file, $params, $callbacks);
        PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);

    }

    private function ubahFormatTanggal($tanggal = "2023-08-05") {
        $bulanIndonesia = [
            '01' => 'Januari',
            '02' => 'Februari',
            '03' => 'Maret',
            '04' => 'April',
            '05' => 'Mei',
            '06' => 'Juni',
            '07' => 'Juli',
            '08' => 'Agustus',
            '09' => 'September',
            '10' => 'Oktober',
            '11' => 'November',
            '12' => 'Desember'
        ];

        $tanggalArr = explode('-', $tanggal);
        $bulan = $bulanIndonesia[$tanggalArr[1]];
        $bulan = strtoupper($bulan);
        $tahun = $tanggalArr[0];

        return "$bulan $tahun";
    }

    public static function tagihan($kode){
        ini_set('display_errors', 1);
        $year = date('Y');
        $file = SETUP_PATH.'excel/tagihan-'.$kode.'-'.$year.'.xlsx';
        $filex = SETUP_PATH.'/excel/tagihan.xlsx';
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        // copy
        $l = DB::query_result_object("
        SELECT tgl FROM mtagihan m WHERE m.kodetagihan = '$kode' LIMIT 1
        ");


        $nama = DB::query_result_object("
            SELECT jurubayar FROM mtagihan m WHERE m.kodetagihan = '$kode' LIMIT 1
        ");

        $tgl = date('Y-m-d');
        
        $jurubayar = "";

        if( count($l) > 0 ){
            $tgl = (new self)->ubahFormatTanggal( $l[0]->tgl );
        }
        if( count($nama) > 0 ){
            $jurubayar =  $nama[0]->jurubayar;
        }

        $dataParse = [];
        $data = DB::query_result_object("SELECT 
            m.kode
            ,a.nama
            ,ifnull(sum(if(m.kodesp = 'bw', jumlah,0)),0) bw
            ,ifnull(sum(if(m.kodesp = 'sp', jumlah,0)),0) sp
            ,ifnull(sum(if(m.kodesp = 'pt', m.pokok,0)),0) pt
            ,ifnull(sum(if(m.kodesp = 'sm', jumlah,0)),0) manasuka
            ,ifnull(sum(if(m.kodesp = 'tk', jumlah,0)),0) tk
            ,ifnull(sum(if(m.kodesp = 'sw', jumlah,0)),0) sw
            ,ifnull(sum(if(m.kodesp = 'swp', jumlah,0)),0) swp
            ,ifnull(sum(if(m.kodesp = 'usp1', m.pokok + m.bunga,0)),0) usp1
            ,ifnull(sum(if(m.kodesp = 'usp2', m.pokok + m.bunga,0)),0) usp2
            ,ifnull(sum(if(m.kodesp = 'usp3', m.pokok + m.bunga,0)),0) usp3
            ,ifnull(sum(if(m.kodesp = 'reg', m.pokok,0)),0) pokok
            ,ifnull(sum(if(m.kodesp = 'reg', m.bunga,0)),0) jasa
            ,ifnull(sum(if(m.kodesp = 'reg', (
                SELECT pokok tot from mpinjam where faktur = m.faktur
            ),0)),0) pinjaman
            , if(m.ke <> 0, selisih_bulan(x.mtgl , m.tgl), m.ke) ke
            ,ifnull(sum(if(m.kodesp = 'reg', (
                SELECT lama from mpinjam where faktur = m.faktur
            ),0)),0) masa
            ,\"=C{no}-(G{no}*E{no})\" hsisa
            ,\"=SUM(G{no}:K{no})\" jmlusp
            ,\"=SUM(M{no}:S{no})\" jmlinuk
            ,\"=L{no}+T{no}\" totalall
        FROM mtagihan m 
        LEFT JOIN anggota a ON m.kode = a.kode
        LEFT JOIN tglpinjaman x ON m.faktur = x.mfkt
        WHERE m.kodetagihan = '$kode' GROUP BY m.kode");

        $newPar = new Param();
        $dataParse['kode'] = (new self)->toArray($data, "kode"); 
        $newPar->add('kode', $dataParse);
        $dataParse['nama'] = (new self)->toArray($data, "nama"); 
        $newPar->add('nama', $dataParse);
        $dataParse['bw'] = (new self)->toArray($data, "bw"); 
        $newPar->add('bw', $dataParse);
        $dataParse['sp'] = (new self)->toArray($data, "sp"); 
        $newPar->add('sp', $dataParse);
        $dataParse['pt'] = (new self)->toArray($data, "pt"); 
        $newPar->add('pt', $dataParse);
        $dataParse['manasuka'] = (new self)->toArray($data, "manasuka"); 
        $newPar->add('manasuka', $dataParse);
        $dataParse['tk'] = (new self)->toArray($data, "tk"); 
        $newPar->add('tk', $dataParse);
        $dataParse['sw'] = (new self)->toArray($data, "sw"); 
        $newPar->add('sw', $dataParse);
        $dataParse['swp'] = (new self)->toArray($data, "swp"); 
        $newPar->add('swp', $dataParse);
        $dataParse['usp1'] = (new self)->toArray($data, "usp1"); 
        $newPar->add('usp1', $dataParse);
        $dataParse['usp2'] = (new self)->toArray($data, "usp2"); 
        $newPar->add('usp2', $dataParse);
        $dataParse['usp3'] = (new self)->toArray($data, "usp3"); 
        $newPar->add('usp3', $dataParse);
        $dataParse['pinjaman'] = (new self)->toArray($data, "pinjaman"); 
        $newPar->add('pinjaman', $dataParse);
        $dataParse['masa'] = (new self)->toArray($data, "masa"); 
        $newPar->add('masa', $dataParse);
        $dataParse['pokok'] = (new self)->toArray($data, "pokok"); 
        $newPar->add('pokok', $dataParse);
        $dataParse['jasa'] = (new self)->toArray($data, "jasa"); 
        $newPar->add('jasa', $dataParse);
        $dataParse['sisa'] = (new self)->toArray($data, "hsisa"); 
        $newPar->add('sisa', $dataParse);
        $dataParse['ke'] = (new self)->toArray($data, "ke"); 
        $newPar->add('ke', $dataParse);
        $dataParse['jmlusp'] = (new self)->toArray($data, "jmlusp"); 
        $newPar->add('jmlusp', $dataParse);
        $dataParse['jmlinuk'] = (new self)->toArray($data, "jmlinuk"); 
        $newPar->add('jmlinuk', $dataParse);
        $dataParse['totalall'] = (new self)->toArray($data, "totalall"); 
        $newPar->add('totalall', $dataParse);
        $newPar->single('mounthy', $tgl);
        $newPar->single('jurubayar', strtoupper($jurubayar));

        $params = $newPar->get();

        // encopy
        $callbacks = [];
        PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);
    }

    private function toArray($data = null, $name = ""){
        $new = [];
        $start = 8;
        foreach ($data as $key => $n) {
            $e = ( array ) $n;
            $new[] = str_replace("{no}",$start+$key, $e[$name]);
        }
        return $new;
    }

    public static function test($kode){

        ini_set('display_errors', 1);
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        // copy

        $dataParse = [];
        $data = DB::query_result_object("SELECT 
            m.kode
            ,a.nama
            ,ifnull(sum(if(m.kodesp = 'reg', (
                SELECT pokok + bunga tot from mpinjam where faktur = m.faktur
            ),0)),0) pinjaman
            ,ifnull(sum(if(m.kodesp = 'bw', jumlah,0)),0) bw
            ,ifnull(sum(if(m.kodesp = 'sp', jumlah,0)),0) sp
            ,ifnull(sum(if(m.kodesp = 'pt', jumlah,0)),0) pt
            ,ifnull(sum(if(m.kodesp = 'sm', jumlah,0)),0) manasuka
            ,\"=C{no}-(G{no}*E{no})\" hsisa
        FROM mtagihan m 
        LEFT JOIN anggota a ON m.kode = a.kode
        WHERE m.kodetagihan = '$kode' GROUP BY m.kode");

        $newPar = new Param();
        $dataParse['pinjaman'] = (new self)->toArray($data, "pinjaman"); 
        $newPar->add('pinjaman', $dataParse);
        $dataParse['kode'] = (new self)->toArray($data, "kode"); 
        $newPar->add('kode', $dataParse);
        $dataParse['nama'] = (new self)->toArray($data, "nama"); 
        $newPar->add('nama', $dataParse);
        $dataParse['bw'] = (new self)->toArray($data, "bw"); 
        $newPar->add('bw', $dataParse);
        $dataParse['sp'] = (new self)->toArray($data, "sp"); 
        $newPar->add('sp', $dataParse);
        $dataParse['pt'] = (new self)->toArray($data, "pt"); 
        $newPar->add('pt', $dataParse);
        $dataParse['manasuka'] = (new self)->toArray($data, "manasuka"); 
        $newPar->add('manasuka', $dataParse);

        $params = $newPar->get();

        // encopy

        DD::view($params);
        
    }

    public static function neraca($tahun, $bulan, $type = 1, $all = ""){
        $bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
        ini_set('display_errors', 1);
        $file = SETUP_PATH.'excel/neraca-lajur-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
        $filex = SETUP_PATH.'/excel/neracalajur_tahunan.xlsx';
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        $setc = $all == "usp"? "usp" : "acc";

        $dx = DB::query_result_object_row("SELECT mainacc.nama a, acc.nama b FROM mainacc 
            LEFT JOIN acc ON mainacc.kode = acc.main
            WHERE mainacc.nama LIKE '%shu belum%' AND acc.app = '$setc'");

        $nm = "
            , acc.kode
            , acc.nama
        ";
        
        if($type == 1){
            $nm = "
                , acc.main kode
                , acc.nama_main nama
            ";
        }

        $condition = "";

        if($all == 'induk'){
            $condition = " WHERE acc.app = 'acc' ";
        }

        if($all == 'usp'){
            $condition = " WHERE acc.app = 'usp' ";
        }

        $p1 = "('PERHITUNGAN NERACA'!D{no}-'PERHITUNGAN NERACA'!E{no})";
        $p2 = "('PERHITUNGAN NERACA'!E{no}-'PERHITUNGAN NERACA'!D{no})";

        $qr = "
            SELECT 
                '={no}-6' `no`
                ,\"='PERHITUNGAN NERACA'!A{no}\" `a1`
                ,\"='PERHITUNGAN NERACA'!B{no}\" `a2`
                ,\"='PERHITUNGAN NERACA'!C{no}\" `a3`
                , if(acc.dk = 'D' AND nr = 'N', \"=IF($p1<0,0,$p1)\", \"=0\") `a4`
                , if(acc.dk <> 'D' AND nr = 'N' , \"=IF(c{no}=\\\"$dx->a\\\",({shunoo}),( $p2 ))\", IF(nr='N', \"=IF($p1<0, $p1*-1,0)\", \"=0\" ) ) `a5`
                ,\"='PERHITUNGAN NERACA'!F{no}\" `a6`
                ,\"='PERHITUNGAN NERACA'!G{no}\" `a7`
                ,\"=D{no}+F{no}\" `a8`
                ,\"=E{no}+G{no}\" `a9`
                ,\"='PERHITUNGAN NERACA'!L{no}\" `a10`
                ,\"='PERHITUNGAN NERACA'!M{no}\" `a11`
                ,\"=IF(c{no}=\\\"$dx->a\\\", 0,('PERHITUNGAN NERACA'!N{no}))\" `a12`
                ,\"=IF(c{no}=\\\"$dx->a\\\", ('PERHITUNGAN NERACA'!N{no})-({shunoo})*-1,('PERHITUNGAN NERACA'!O{no}))\" `a13`
                ,\"='PERHITUNGAN NERACA'!P{no}\" `a14`
                ,\"='PERHITUNGAN NERACA'!Q{no}\" `a15`
                ,\"=IF(c{no}=\\\"$dx->a\\\", 0,('PERHITUNGAN NERACA'!R{no}))\" `a16`
                ,\"=IF(c{no}=\\\"$dx->a\\\", ('PERHITUNGAN NERACA'!R{no})-({shunoo})*-1,('PERHITUNGAN NERACA'!S{no}))\" `a17`
                $nm 
                , sum(ifnull(na.debet,0)) nad
                , sum(ifnull(na.kredit,0)) nak
                , sum(ifnull(nm.debet,0)) nmd
                , sum(ifnull(nm.kredit,0)) nmk
                , if(acc.nr = 'N', '=D{no}+F{no}', '=F{no}') npd
                , if(acc.nr = 'N', '=E{no}+G{no}', '=G{no}') npk
                , '=IF(H{no}>I{no},H{no}-I{no},0)' nsd
                , '=IF(I{no}>H{no},I{no}-H{no},0)' nsk
                , '0' pyd
                , '0' pyk
                , '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),(J{no}+L{no})-M{no},IF(AND(K{no}>0,F{no}=0),0,L{no})))' pnd
                , '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),0,IF(AND(K{no}>0,J{no}=0),(K{no}+M{no})-L{no},M{no})))' pnk
                , '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),N{no},0)' pld
                , '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),O{no},0)' plk
                , '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),N{no},0)' ned
                , '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),O{no},0)' nek
            FROM acc
            LEFT JOIN (
                SELECT a.kode, sum(a.debet) debet, sum(a.kredit) kredit FROM (   
                    SELECT 
                        b.kode,
                        if(b.dk = 'D', total, 0) debet,
                        if(b.dk = 'K', total, 0) kredit
                    FROM bbtahunan b 
                    LEFT JOIN acc ON acc.kode = b.kode
                    WHERE tahun = '".($tahun-1)."' AND acc.nr = 'N'
                    ".( ($tahun*1) > 1 ? "
                        UNION ALL
                        SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
                            SELECT
                                rekdebit kode
                                , sum(debit) debet
                                , 0 kredit
                            FROM lap_bb 
                            LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE 
                            tgl >= '$tahun-01-01' AND tgl < '$tahun-$bulan-01'
                            GROUP BY rekdebit
                            UNION ALL
                            SELECT
                                rekkredit kode
                                , 0 debet
                                , sum(kredit) kredit
                            FROM lap_bb 
                            LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE 
                            tgl >= '$tahun-01-01' AND tgl < '$tahun-$bulan-01'
                            GROUP BY rekkredit
                        ) a GROUP BY a.kode
                    
                    ": "")."
                ) a GROUP BY a.kode
            ) na ON na.kode = acc.kode
            LEFT JOIN (
                SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
                    SELECT
                        rekdebit kode
                        , sum(debit) debet
                        , 0 kredit
                    FROM lap_bb 
                    LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE 
                    tgl >= '$tahun-$bulan-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
                    GROUP BY rekdebit
                    UNION ALL
                    SELECT
                        rekkredit kode
                        , 0 debet
                        , sum(kredit) kredit
                    FROM lap_bb 
                    LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE 
                    tgl >= '$tahun-$bulan-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
                    GROUP BY rekkredit
                ) a GROUP BY a.kode
            ) nm ON nm.kode = acc.kode
            $condition
            GROUP BY kode
            ORDER BY acc.kode ASC
        ";
        // echo "<pre>";
        // echo $qr;
        // die();
        $data = DB::query_result_object($qr);
        $endlaba = count($data) + 7 + 1;
        $startlaba = 7;
        foreach($data as $ceklaba){
            if( intval( substr( $ceklaba->kode,0,1) ) < 4 ){
                $startlaba++;
            }
        };
        
        $dataParse = [];
        $newPar = new Param();
        $newPar2 = new Param();
        $dataParse['no'] = (new self)->toArray2($data, "no"); 
        $newPar->add('no', $dataParse);
        $dataParse['kode'] = (new self)->toArray2($data, "kode"); 
        $newPar->add('kode', $dataParse);
        $dataParse['nama'] = (new self)->toArray2($data, "nama"); 
        $newPar->add('nama', $dataParse);
        $dataParse['nad'] = (new self)->toArray2($data, "nad"); 
        $newPar->add('nad', $dataParse);
        $dataParse['nak'] = (new self)->toArray2($data, "nak"); 
        $newPar->add('nak', $dataParse);
        $dataParse['nmd'] = (new self)->toArray2($data, "nmd"); 
        $newPar->add('nmd', $dataParse);
        $dataParse['nmk'] = (new self)->toArray2($data, "nmk"); 
        $newPar->add('nmk', $dataParse);
        $dataParse['npd'] = (new self)->toArray2($data, "npd"); 
        $newPar->add('npd', $dataParse);
        $dataParse['npk'] = (new self)->toArray2($data, "npk"); 
        $newPar->add('npk', $dataParse);
        $dataParse['nsd'] = (new self)->toArray2($data, "nsd"); 
        $newPar->add('nsd', $dataParse);
        $dataParse['nsk'] = (new self)->toArray2($data, "nsk"); 
        $newPar->add('nsk', $dataParse);
        $dataParse['pyd'] = (new self)->toArray2($data, "pyd"); 
        $newPar->add('pyd', $dataParse);
        $dataParse['pyk'] = (new self)->toArray2($data, "pyk"); 
        $newPar->add('pyk', $dataParse);
        $dataParse['pnd'] = (new self)->toArray2($data, "pnd"); 
        $newPar->add('pnd', $dataParse);
        $dataParse['pnk'] = (new self)->toArray2($data, "pnk"); 
        $newPar->add('pnk', $dataParse);
        $dataParse['pld'] = (new self)->toArray2($data, "pld"); 
        $newPar->add('pld', $dataParse);
        $dataParse['plk'] = (new self)->toArray2($data, "plk"); 
        $newPar->add('plk', $dataParse);
        $dataParse['ned'] = (new self)->toArray2($data, "ned"); 
        $newPar->add('ned', $dataParse);
        $dataParse['nek'] = (new self)->toArray2($data, "nek"); 
        $newPar->add('nek', $dataParse);
        $newPar->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
        $newPar2->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
        // $newPar2->single('shu', "=SUM('PERHITUNGAN NERACA'!E$startlaba:E$endlaba)-SUM('PERHITUNGAN NERACA'!D$startlaba:D$endlaba)" );
        $newPar->single('shu', "=SUM('PERHITUNGAN NERACA'!E$startlaba:E$endlaba)-SUM('PERHITUNGAN NERACA'!D$startlaba:D$endlaba)+'PERHITUNGAN NERACA'!E".($startlaba-1)."-'PERHITUNGAN NERACA'!D".($startlaba-1)."" );
        
        for ($ix = 1; $ix <= 17; $ix++) {
            $dataParse['a'.$ix] = (new self)->toArray2($data, "a".$ix, $startlaba, $endlaba, 1); 
            $newPar2->add('a'.$ix, $dataParse);
        }
        // echo $endlaba;
        // die();
        
        $lastDates = date("t/m/Y" , strtotime($tahun."-".$bulan."-01"));
        function ubahFormatTanggal($tanggal) {
            // Array bulan dalam bahasa Indonesia
            $bulan = array(
                1 => 'Januari',
                2 => 'Februari',
                3 => 'Maret',
                4 => 'April',
                5 => 'Mei',
                6 => 'Juni',
                7 => 'Juli',
                8 => 'Agustus',
                9 => 'September',
                10 => 'Oktober',
                11 => 'November',
                12 => 'Desember'
            );

            // Pecah tanggal menjadi array
            $tanggalArray = explode('/', $tanggal);
            // Ambil elemen tanggal, bulan, dan tahun
            $tanggal = $tanggalArray[0];
            $bulanIndex = (int)$tanggalArray[1];
            $tahun = $tanggalArray[2];
            // Ubah bulan menjadi teks
            $bulanTeks = $bulan[$bulanIndex];
            // Format ulang tanggal
            $tanggalFormatBaru = $tanggal . ' ' . $bulanTeks . ' ' . $tahun;
            return $tanggalFormatBaru;
        }

        $newPar->single('date', ubahFormatTanggal($lastDates) );

        $params = $newPar->get();
        $params2 = $newPar2->get();
        // encopy
        $callbacks = [];
        $events = [];
        $spreadsheet = IOFactory::load($filex);
        
        $templateVarsArr = $spreadsheet->getSheet(1)->toArray();
        $sheet1 = $spreadsheet->getSheet(1);
        PhpExcelTemplator::renderWorksheet($sheet1, $templateVarsArr, $params, $callbacks, $events);
        
        $sheet2 = $spreadsheet->getSheet(0);
        $templateVarsArr2 = $spreadsheet->getSheet(0)->toArray();
        PhpExcelTemplator::renderWorksheet($sheet2, $templateVarsArr2, $params2, $callbacks, $events);
        
        PhpExcelTemplator::outputSpreadsheetToFile($spreadsheet, $file);
    }
    
    // backup code neraca
    public static function neraca_bc($tahun, $bulan, $type = 1, $all = ""){
        $bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
        ini_set('display_errors', 1);
        $file = SETUP_PATH.'excel/neraca-lajur-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
        $filex = SETUP_PATH.'/excel/neracalajur.xlsx';
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        $nm = "
            , acc.kode
            , acc.nama
        ";
        if($type == 1){
            $nm = "
                , acc.main kode
                , acc.nama_main nama
            ";
        }

        $condition = "";

        if($all == 'induk'){
            $condition = " WHERE acc.app = 'acc' ";
        }

        if($all == 'usp'){
            $condition = " WHERE acc.app = 'usp' ";
        }

        $qr = "
            SELECT 
                '={no}-6' `no`
                ,\"='PERHITUNGAN NERACA'!A{no}\" `a1`
                ,\"='PERHITUNGAN NERACA'!B{no}\" `a2`
                ,\"='PERHITUNGAN NERACA'!C{no}\" `a3`
                , if(acc.dk = 'D' AND nr = 'N', \"='PERHITUNGAN NERACA'!D{no}-'PERHITUNGAN NERACA'!E{no}\", \"=0\") `a4`
                , if(acc.dk <> 'D' AND nr = 'N' , \"='PERHITUNGAN NERACA'!E{no}-'PERHITUNGAN NERACA'!D{no}\", \"=0\") `a5`
                ,\"='PERHITUNGAN NERACA'!F{no}\" `a6`
                ,\"='PERHITUNGAN NERACA'!G{no}\" `a7`
                ,\"=D{no}+F{no}\" `a8`
                ,\"=E{no}+G{no}\" `a9`
                ,\"='PERHITUNGAN NERACA'!L{no}\" `a10`
                ,\"='PERHITUNGAN NERACA'!M{no}\" `a11`
                ,\"='PERHITUNGAN NERACA'!N{no}\" `a12`
                ,\"='PERHITUNGAN NERACA'!O{no}\" `a13`
                ,\"='PERHITUNGAN NERACA'!P{no}\" `a14`
                ,\"='PERHITUNGAN NERACA'!Q{no}\" `a15`
                ,\"='PERHITUNGAN NERACA'!R{no}\" `a16`
                ,\"='PERHITUNGAN NERACA'!S{no}\" `a17`
                $nm 
                , sum(ifnull(na.debet,0)) nad
                , sum(ifnull(na.kredit,0)) nak
                , sum(ifnull(nm.debet,0)) nmd
                , sum(ifnull(nm.kredit,0)) nmk
                , if(acc.nr = 'N', '=D{no}+F{no}', '=F{no}') npd
                , if(acc.nr = 'N', '=E{no}+G{no}', '=G{no}') npk
                , '=IF(H{no}>I{no},H{no}-I{no},0)' nsd
                , '=IF(I{no}>H{no},I{no}-H{no},0)' nsk
                , '0' pyd
                , '0' pyk
                , '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),(J{no}+L{no})-M{no},IF(AND(K{no}>0,F{no}=0),0,L{no})))' pnd
                , '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),0,IF(AND(K{no}>0,J{no}=0),(K{no}+M{no})-L{no},M{no})))' pnk
                , '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),N{no},0)' pld
                , '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),O{no},0)' plk
                , '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),N{no},0)' ned
                , '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),O{no},0)' nek
            FROM acc
            LEFT JOIN (
                SELECT a.kode, sum(a.debet) debet, sum(a.kredit) kredit FROM (   
                    SELECT 
                        b.kode,
                        if(b.dk = 'D', total, 0) debet,
                        if(b.dk = 'K', total, 0) kredit
                    FROM bbtahunan b 
                    LEFT JOIN acc ON acc.kode = b.kode
                    WHERE tahun = '".($tahun-1)."' AND acc.nr = 'N'
                    ".( ($tahun*1) > 1 ? "
                        UNION ALL
                        SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
                            SELECT
                                rekdebit kode
                                , sum(debit) debet
                                , 0 kredit
                            FROM lap_bb 
                            LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE 
                            tgl >= '$tahun-01-01' AND tgl < '$tahun-$bulan-01'
                            GROUP BY rekdebit
                            UNION ALL
                            SELECT
                                rekkredit kode
                                , 0 debet
                                , sum(kredit) kredit
                            FROM lap_bb 
                            LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE 
                            tgl >= '$tahun-01-01' AND tgl < '$tahun-$bulan-01'
                            GROUP BY rekkredit
                        ) a GROUP BY a.kode
                    
                    ": "")."
                ) a GROUP BY a.kode
            ) na ON na.kode = acc.kode
            LEFT JOIN (
                SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
                    SELECT
                        rekdebit kode
                        , sum(debit) debet
                        , 0 kredit
                    FROM lap_bb 
                    LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE 
                    tgl >= '$tahun-$bulan-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
                    GROUP BY rekdebit
                    UNION ALL
                    SELECT
                        rekkredit kode
                        , 0 debet
                        , sum(kredit) kredit
                    FROM lap_bb 
                    LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE 
                    tgl >= '$tahun-$bulan-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
                    GROUP BY rekkredit
                ) a GROUP BY a.kode
            ) nm ON nm.kode = acc.kode
            $condition
            GROUP BY kode
            ORDER BY acc.kode ASC
        ";
        // echo "<pre>";
        // echo $qr;
        $data = DB::query_result_object($qr);
        
        $endlaba = count($data) + 7;
        $startlaba = 7;
        foreach($data as $ceklaba){
            if( intval( substr( $ceklaba->kode,0,1) ) < 4 ){
                $startlaba++;
            }
        };
        
        $dataParse = [];
        $newPar = new Param();
        $newPar2 = new Param();
        $dataParse['no'] = (new self)->toArray2($data, "no"); 
        $newPar->add('no', $dataParse);
        $dataParse['kode'] = (new self)->toArray2($data, "kode"); 
        $newPar->add('kode', $dataParse);
        $dataParse['nama'] = (new self)->toArray2($data, "nama"); 
        $newPar->add('nama', $dataParse);
        $dataParse['nad'] = (new self)->toArray2($data, "nad"); 
        $newPar->add('nad', $dataParse);
        $dataParse['nak'] = (new self)->toArray2($data, "nak"); 
        $newPar->add('nak', $dataParse);
        $dataParse['nmd'] = (new self)->toArray2($data, "nmd"); 
        $newPar->add('nmd', $dataParse);
        $dataParse['nmk'] = (new self)->toArray2($data, "nmk"); 
        $newPar->add('nmk', $dataParse);
        $dataParse['npd'] = (new self)->toArray2($data, "npd"); 
        $newPar->add('npd', $dataParse);
        $dataParse['npk'] = (new self)->toArray2($data, "npk"); 
        $newPar->add('npk', $dataParse);
        $dataParse['nsd'] = (new self)->toArray2($data, "nsd"); 
        $newPar->add('nsd', $dataParse);
        $dataParse['nsk'] = (new self)->toArray2($data, "nsk"); 
        $newPar->add('nsk', $dataParse);
        $dataParse['pyd'] = (new self)->toArray2($data, "pyd"); 
        $newPar->add('pyd', $dataParse);
        $dataParse['pyk'] = (new self)->toArray2($data, "pyk"); 
        $newPar->add('pyk', $dataParse);
        $dataParse['pnd'] = (new self)->toArray2($data, "pnd"); 
        $newPar->add('pnd', $dataParse);
        $dataParse['pnk'] = (new self)->toArray2($data, "pnk"); 
        $newPar->add('pnk', $dataParse);
        $dataParse['pld'] = (new self)->toArray2($data, "pld"); 
        $newPar->add('pld', $dataParse);
        $dataParse['plk'] = (new self)->toArray2($data, "plk"); 
        $newPar->add('plk', $dataParse);
        $dataParse['ned'] = (new self)->toArray2($data, "ned"); 
        $newPar->add('ned', $dataParse);
        $dataParse['nek'] = (new self)->toArray2($data, "nek"); 
        $newPar->add('nek', $dataParse);
        $newPar->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
        $newPar2->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
        $newPar2->single('shu', "=SUM('PERHITUNGAN NERACA'!E$startlaba:E$endlaba)-SUM('PERHITUNGAN NERACA'!D$startlaba:D$endlaba)" );
        
        for ($ix = 1; $ix <= 17; $ix++) {
            $dataParse['a'.$ix] = (new self)->toArray2($data, "a".$ix); 
            $newPar2->add('a'.$ix, $dataParse);
        }
        
        $lastDates = date("t/m/Y" , strtotime($tahun."-".$bulan."-01"));
        function ubahFormatTanggal($tanggal) {
            // Array bulan dalam bahasa Indonesia
            $bulan = array(
                1 => 'Januari',
                2 => 'Februari',
                3 => 'Maret',
                4 => 'April',
                5 => 'Mei',
                6 => 'Juni',
                7 => 'Juli',
                8 => 'Agustus',
                9 => 'September',
                10 => 'Oktober',
                11 => 'November',
                12 => 'Desember'
            );

            // Pecah tanggal menjadi array
            $tanggalArray = explode('/', $tanggal);
            // Ambil elemen tanggal, bulan, dan tahun
            $tanggal = $tanggalArray[0];
            $bulanIndex = (int)$tanggalArray[1];
            $tahun = $tanggalArray[2];
            // Ubah bulan menjadi teks
            $bulanTeks = $bulan[$bulanIndex];
            // Format ulang tanggal
            $tanggalFormatBaru = $tanggal . ' ' . $bulanTeks . ' ' . $tahun;
            return $tanggalFormatBaru;
        }

        $newPar->single('date', ubahFormatTanggal($lastDates) );

        $params = $newPar->get();
        $params2 = $newPar2->get();
        // encopy
        $callbacks = [];
        $events = [];
        $spreadsheet = IOFactory::load($filex);
        
        $templateVarsArr = $spreadsheet->getSheet(1)->toArray();
        $sheet1 = $spreadsheet->getSheet(1);
        PhpExcelTemplator::renderWorksheet($sheet1, $templateVarsArr, $params, $callbacks, $events);
        
        $sheet2 = $spreadsheet->getSheet(0);
        $templateVarsArr2 = $spreadsheet->getSheet(0)->toArray();
        PhpExcelTemplator::renderWorksheet($sheet2, $templateVarsArr2, $params2, $callbacks, $events);
        
        PhpExcelTemplator::outputSpreadsheetToFile($spreadsheet, $file);
    }

    public static function neraca_tahunan($tahun, $bulan, $type = 1, $all = ""){
        $bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
        ini_set('display_errors', 1);
        $file = SETUP_PATH.'excel/neraca-lajur-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
        $filex = SETUP_PATH.'/excel/neracalajur_tahunan.xlsx';
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        $setc = $all == "usp"? "usp" : "acc";

        $dx = DB::query_result_object_row("SELECT mainacc.nama a, acc.nama b FROM mainacc 
            LEFT JOIN acc ON mainacc.kode = acc.main
            WHERE mainacc.nama LIKE '%shu belum%' AND acc.app = '$setc'");

        $nm = "
            , acc.kode
            , acc.nama
        ";
        if($type == 1){
            $nm = "
                , acc.main kode
                , acc.nama_main nama
            ";
        }

        $condition = "";
        $condition2 = "";
        $codename = "";

        if($all == 'induk'){
            $condition = " WHERE acc.app = 'acc' ";
            $condition2 = " AND acc.app = 'acc' ";
            $codename = "acc";
        }

        if($all == 'usp'){
            $condition = " WHERE acc.app = 'usp' ";
            $condition2 = " AND acc.app = 'usp' ";
            $codename = "usp";
        }

        $p1 = "('PERHITUNGAN NERACA'!D{no}-'PERHITUNGAN NERACA'!E{no})";
        $p2 = "('PERHITUNGAN NERACA'!E{no}-'PERHITUNGAN NERACA'!D{no})";

        $qr = "
            SELECT 
                '={no}-6' `no`
                ,\"='PERHITUNGAN NERACA'!A{no}\" `a1`
                ,\"='PERHITUNGAN NERACA'!B{no}\" `a2`
                ,\"='PERHITUNGAN NERACA'!C{no}\" `a3`
                , if(acc.dk = 'D' AND nr = 'N', \"=IF($p1<0,0,$p1)\", \"=0\") `a4`
                , if(acc.dk <> 'D' AND nr = 'N' , \"=IF(c{no}=\\\"$dx->a\\\", ('PERHITUNGAN NERACA'!E{no}-'PERHITUNGAN NERACA'!D{no})+({shuno}),('PERHITUNGAN NERACA'!E{no}-'PERHITUNGAN NERACA'!D{no}))\", \"=IF($p1<0, $p1*-1,0)\") `a5`
                ,\"='PERHITUNGAN NERACA'!F{no}\" `a6`
                ,\"='PERHITUNGAN NERACA'!G{no}\" `a7`
                ,\"=D{no}+F{no}\" `a8`
                ,\"=E{no}+G{no}\" `a9`
                ,\"='PERHITUNGAN NERACA'!L{no}\" `a10`
                ,\"='PERHITUNGAN NERACA'!M{no}\" `a11`
                ,\"=IF(c{no}=\\\"$dx->a\\\", ('PERHITUNGAN NERACA'!N{no})-({shuno}),('PERHITUNGAN NERACA'!N{no}))\" `a12`
                ,\"='PERHITUNGAN NERACA'!O{no}\" `a13`
                ,\"='PERHITUNGAN NERACA'!P{no}\" `a14`
                ,\"='PERHITUNGAN NERACA'!Q{no}\" `a15`
                ,\"=IF(c{no}=\\\"$dx->a\\\", ('PERHITUNGAN NERACA'!R{no})-({shuno}),('PERHITUNGAN NERACA'!R{no}))\" `a16`
                ,\"='PERHITUNGAN NERACA'!S{no}\" `a17`
                $nm 
                , sum(ifnull(na.debet,0)) nad
                , sum(ifnull(na.kredit,0)) nak
                , sum(ifnull(nm.debet,0)) nmd
                , sum(ifnull(nm.kredit,0)) nmk
                , if(acc.nr = 'N', '=D{no}+F{no}', '=F{no}') npd
                , if(acc.nr = 'N', '=E{no}+G{no}', '=G{no}') npk
                , '=IF(H{no}>I{no},H{no}-I{no},0)' nsd
                , '=IF(I{no}>H{no},I{no}-H{no},0)' nsk
                , '0' pyd
                , '0' pyk
                , '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),(J{no}+L{no})-M{no},IF(AND(K{no}>0,F{no}=0),0,L{no})))' pnd
                , '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),0,IF(AND(K{no}>0,J{no}=0),(K{no}+M{no})-L{no},M{no})))' pnk
                , '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),N{no},0)' pld
                , '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),O{no},0)' plk
                , '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),N{no},0)' ned
                , '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),O{no},0)' nek
            FROM acc
            LEFT JOIN (
                SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
                SELECT
                    rekdebit kode
                    , sum(debit) debet
                    , 0 kredit
                FROM lap_bb 
                LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE tgl < '$tahun-01-01' AND acc.nr = 'N'
                GROUP BY rekdebit
                UNION ALL
                SELECT
                    rekkredit kode
                    , 0 debet
                    , sum(kredit) kredit
                FROM lap_bb 
                LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE tgl < '$tahun-01-01' AND acc.nr = 'N'
                GROUP BY rekkredit
                UNION ALL
                SELECT getKodeSHU('usp') kode, 0 debet, sum(kredit) - sum(debet) kredit FROM (
                    SELECT
                            rekdebit kode
                            , sum(debit) debet
                            , 0 kredit
                    FROM lap_bb 
                    LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE tgl < '$tahun-01-01' AND acc.nr <> 'N' AND acc.app='$codename'
                    GROUP BY rekdebit
                    UNION ALL
                    SELECT
                            rekkredit kode
                            , 0 debet
                            , sum(kredit) kredit
                    FROM lap_bb 
                    LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE tgl < '$tahun-01-01' AND acc.nr <> 'N' AND acc.app='$codename'
                    GROUP BY rekkredit
                    ) a 
            ) a GROUP BY a.kode
            ) na ON na.kode = acc.kode
            LEFT JOIN (
                SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
                SELECT
                    rekdebit kode
                    , sum(debit) debet
                    , 0 kredit
                FROM lap_bb 
                LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE 
                tgl >= '$tahun-01-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
                GROUP BY rekdebit
                UNION ALL
                SELECT
                    rekkredit kode
                    , 0 debet
                    , sum(kredit) kredit
                FROM lap_bb 
                LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE 
                tgl >= '$tahun-01-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
                GROUP BY rekkredit
                ) a GROUP BY a.kode
            ) nm ON nm.kode = acc.kode
            $condition
            GROUP BY kode
            ORDER BY acc.kode ASC
        ";
        // echo "<pre>";
        // echo $qr;
        // die();
        $data = DB::query_result_object($qr);
        $endlaba = count($data) + 7;
        $startlaba = 7;
        foreach($data as $ceklaba){
            if( intval( substr( $ceklaba->kode,0,1) ) < 4 ){
                $startlaba++;
            }
        };
        $dataParse = [];
        $newPar = new Param();
        $newPar2 = new Param();
        $dataParse['no'] = (new self)->toArray2($data, "no"); 
        $newPar->add('no', $dataParse);
        $dataParse['kode'] = (new self)->toArray2($data, "kode"); 
        $newPar->add('kode', $dataParse);
        $dataParse['nama'] = (new self)->toArray2($data, "nama"); 
        $newPar->add('nama', $dataParse);
        $dataParse['nad'] = (new self)->toArray2($data, "nad"); 
        $newPar->add('nad', $dataParse);
        $dataParse['nak'] = (new self)->toArray2($data, "nak"); 
        $newPar->add('nak', $dataParse);
        $dataParse['nmd'] = (new self)->toArray2($data, "nmd"); 
        $newPar->add('nmd', $dataParse);
        $dataParse['nmk'] = (new self)->toArray2($data, "nmk"); 
        $newPar->add('nmk', $dataParse);
        $dataParse['npd'] = (new self)->toArray2($data, "npd"); 
        $newPar->add('npd', $dataParse);
        $dataParse['npk'] = (new self)->toArray2($data, "npk"); 
        $newPar->add('npk', $dataParse);
        $dataParse['nsd'] = (new self)->toArray2($data, "nsd"); 
        $newPar->add('nsd', $dataParse);
        $dataParse['nsk'] = (new self)->toArray2($data, "nsk"); 
        $newPar->add('nsk', $dataParse);
        $dataParse['pyd'] = (new self)->toArray2($data, "pyd"); 
        $newPar->add('pyd', $dataParse);
        $dataParse['pyk'] = (new self)->toArray2($data, "pyk"); 
        $newPar->add('pyk', $dataParse);
        $dataParse['pnd'] = (new self)->toArray2($data, "pnd"); 
        $newPar->add('pnd', $dataParse);
        $dataParse['pnk'] = (new self)->toArray2($data, "pnk"); 
        $newPar->add('pnk', $dataParse);
        $dataParse['pld'] = (new self)->toArray2($data, "pld"); 
        $newPar->add('pld', $dataParse);
        $dataParse['plk'] = (new self)->toArray2($data, "plk"); 
        $newPar->add('plk', $dataParse);
        $dataParse['ned'] = (new self)->toArray2($data, "ned"); 
        $newPar->add('ned', $dataParse);
        $dataParse['nek'] = (new self)->toArray2($data, "nek"); 
        $newPar->add('nek', $dataParse);
        $newPar->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
        $newPar2->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
        $newPar->single('shu', "=SUM('PERHITUNGAN NERACA'!E$startlaba:E$endlaba)-SUM('PERHITUNGAN NERACA'!D$startlaba:D$endlaba)" );
        
        for ($ix = 1; $ix <= 17; $ix++) {
            $dataParse['a'.$ix] = (new self)->toArray2($data, "a".$ix, $startlaba, $endlaba);
                $newPar2->add('a'.$ix, $dataParse);
        }

        // die();
        
        $lastDates = date("t/m/Y" , strtotime($tahun."-".$bulan."-01"));
        function ubahFormatTanggal($tanggal) {
            // Array bulan dalam bahasa Indonesia
            $bulan = array(
                1 => 'Januari',
                2 => 'Februari',
                3 => 'Maret',
                4 => 'April',
                5 => 'Mei',
                6 => 'Juni',
                7 => 'Juli',
                8 => 'Agustus',
                9 => 'September',
                10 => 'Oktober',
                11 => 'November',
                12 => 'Desember'
            );

            // Pecah tanggal menjadi array
            $tanggalArray = explode('/', $tanggal);
            // Ambil elemen tanggal, bulan, dan tahun
            $tanggal = $tanggalArray[0];
            $bulanIndex = (int)$tanggalArray[1];
            $tahun = $tanggalArray[2];
            // Ubah bulan menjadi teks
            $bulanTeks = $bulan[$bulanIndex];
            // Format ulang tanggal
            $tanggalFormatBaru = $tanggal . ' ' . $bulanTeks . ' ' . $tahun;
            return $tanggalFormatBaru;
        }

        $newPar->single('date', ubahFormatTanggal($lastDates) );

        $params = $newPar->get();
        $params2 = $newPar2->get();
        // encopy
        $callbacks = [];
        $events = [];
        $spreadsheet = IOFactory::load($filex);
        
        $templateVarsArr = $spreadsheet->getSheet(1)->toArray();
        $sheet1 = $spreadsheet->getSheet(1);
        PhpExcelTemplator::renderWorksheet($sheet1, $templateVarsArr, $params, $callbacks, $events);
        
        $sheet2 = $spreadsheet->getSheet(0);
        $templateVarsArr2 = $spreadsheet->getSheet(0)->toArray();
        PhpExcelTemplator::renderWorksheet($sheet2, $templateVarsArr2, $params2, $callbacks, $events);
        
        PhpExcelTemplator::outputSpreadsheetToFile($spreadsheet, $file);
    }

    private function toArray2($data = null, $name = "", $a="", $b="", $h=0){
        $new = [];
        $start = 7;
        foreach ($data as $key => $n) {
            $e = ( array ) $n;
            $c = str_replace("{no}",$start+$key, $e[$name]);
            $d = str_replace("{shuno}","SUM('PERHITUNGAN NERACA'!E$a:E$b)-SUM('PERHITUNGAN NERACA'!D$a:D$b)", $c);
            if($h=1){
                $d = str_replace("{shunoo}","'PERHITUNGAN NERACA'!E$b:E$b", $c);
            }
            $new[] = $d;
        }
        $secondCheck= array_map(function($n) use ($a,$b) {
            return str_replace("{shuno}","SUM('PERHITUNGAN NERACA'!E$a:E$b)-SUM('PERHITUNGAN NERACA'!D$a:D$b)", $n);
        }, $new);
        return $secondCheck;
    }

}