koperasi/module/http/excel/rekapitulasi-backup-baru.php

<?php

use alhimik1986\PhpExcelTemplator\PhpExcelTemplator;
use alhimik1986\PhpExcelTemplator\params\ExcelParam;
use alhimik1986\PhpExcelTemplator\params\CallbackParam;
use alhimik1986\PhpExcelTemplator\setters\CellSetterArrayValueSpecial;

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 Rekapitulasi {

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

    public static function do($tahun, $bulan, $type, $gol = "01"){
        $tht = date('Y-m-t', strtotime( $tahun.'-'.$bulan ) );
        $tht2 = date('Y-m', strtotime( $tahun.'-'.$bulan ) );
        $bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
        ini_set('display_  231564524452errors', 1);
        $file = SETUP_PATH.'excel/rekapitulasi-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
        if(base64_decode($type) !=  'all'){
            $filex = SETUP_PATH.'/excel/rekapitulasi2.xlsx';
        }else{
            $filex = SETUP_PATH.'/excel/rekapitulasi.xlsx';
            if($gol == "02"){
                $filex = SETUP_PATH.'/excel/rekapitulasi3.xlsx';
            }
        }
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        $kodeSwithch = ", m.jurubayar";
        $namatagar = "JURU BAYAR";
        $gts = " AND c.status = 1";
        
        if($gol == "02"){
            $gts = " AND b.status = 1";
            $namatagar = "GOLONGAN";
            $kodeSwithch = ", a.rekapgol jurubayar";
        }

        $jubar = "jurubayar nama";
        $groupby = " GROUP BY jurubayar ORDER BY jurubayar ASC";
        
        if(base64_decode($type) !=  'all'){
           $jubar = "nama";
           $groupby = " GROUP BY kode HAVING jurubayar = '".base64_decode($type)."' ORDER BY kode ASC";
        }

        $qr = "
        SELECT 
            '={no} - 5' no
            ,'=SUM(D{no}:H{no})' jum1
            ,'=SUM(J{no}:P{no})' jum2
            ,'=Q{no}+I{no}' jum3
            , jurubayar
            , $jubar
            , sum(sisa) reg
            , sum(pokok) pokok
            , sum(jasa) bunga
            , sum(swp) swp
            , sum(manasuka) sm
            , sum(tk) tk
            , sum(sp) sp
            , sum(sw) sw
            , sum(bw) bw
            , sum(pt) pk
            , sum(usp1) usp1
            , sum(usp2) usp2
            , sum(usp3) usp3
        FROM (
        SELECT 
            m.kode
            ,a.nama
            $kodeSwithch
            ,ifnull(sum(if(m.kodesp = 'reg', (
                SELECT pokok tot from mpinjam where faktur = m.faktur
            ),0)),0) pinjaman
            ,ifnull(sum(if(m.kodesp = 'reg', m.ke,0)),0) ke
            ,ifnull(sum(if(m.kodesp = 'reg', (
                SELECT pokok tot from mpinjam where faktur = m.faktur
            ),0)),0) - ( ifnull(sum(if(m.kodesp = 'reg', m.ke,0)),0) * ifnull(sum(if(m.kodesp = 'reg', m.pokok,0)),0) ) sisa
            ,ifnull(sum(if(m.kodesp = 'reg', (
                SELECT lama from mpinjam where faktur = m.faktur
            ),0)),0) masa
            ,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 = 'swp', jumlah,0)),0) swp
            ,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 = 'sp', jumlah,0)),0) sp
            ,ifnull(sum(if(m.kodesp = 'sw', jumlah,0)),0) sw
            ,ifnull(sum(if(m.kodesp = 'bw', jumlah,0)),0) bw
            ,ifnull(sum(if(m.kodesp = 'pt', m.pokok,0)),0) pt
            ,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
        FROM mtagihan m 
        LEFT JOIN anggota a ON m.kode = a.kode
        LEFT JOIN mrekapgol b ON a.rekapgol = b.kode
        LEFT JOIN mjurubayar c ON m.jurubayar = c.kode
        WHERE m.tgl LIKE '$tht2%' $gts GROUP BY m.kode
        ) a $groupby
        ";
        
        // echo "<pre>";
        // echo $qr;
        // die();

        $data = DB::query_result_object($qr);
        $dataParse = [];
        $newPar = new Param();

        $dataParse['no'] = (new self)->toArray2($data, "no"); 
        $newPar->add('no', $dataParse);

        $dataParse['nama'] = (new self)->toArray2($data, "nama"); 
        $newPar->add('nama', $dataParse);
        
        $dataParse['swp'] = (new self)->toArray2($data, "swp"); 
        $newPar->add('swp', $dataParse);
        
        $dataParse['reg'] = (new self)->toArray2($data, "reg"); 
        $newPar->add('reg', $dataParse);

        $dataParse['usp1'] = (new self)->toArray2($data, "usp1"); 
        $newPar->add('usp1', $dataParse);

        $dataParse['usp2'] = (new self)->toArray2($data, "usp2"); 
        $newPar->add('usp2', $dataParse);

        $dataParse['usp3'] = (new self)->toArray2($data, "usp3"); 
        $newPar->add('usp3', $dataParse);

        $dataParse['pk'] = (new self)->toArray2($data, "pk"); 
        $newPar->add('pk', $dataParse);

        $dataParse['bw'] = (new self)->toArray2($data, "bw"); 
        $newPar->add('bw', $dataParse);

        $dataParse['sw'] = (new self)->toArray2($data, "sw"); 
        $newPar->add('sw', $dataParse);

        $dataParse['sp'] = (new self)->toArray2($data, "sp"); 
        $newPar->add('sp', $dataParse);

        $dataParse['tk'] = (new self)->toArray2($data, "tk"); 
        $newPar->add('tk', $dataParse);

        $dataParse['sm'] = (new self)->toArray2($data, "sm"); 
        $newPar->add('sm', $dataParse);

        $dataParse['pokok'] = (new self)->toArray2($data, "pokok"); 
        $newPar->add('pokok', $dataParse);

        $dataParse['bunga'] = (new self)->toArray2($data, "bunga"); 
        $newPar->add('bunga', $dataParse);

        $dataParse['jum1'] = (new self)->toArray2($data, "jum1"); 
        $newPar->add('jum1', $dataParse);

        $dataParse['jum2'] = (new self)->toArray2($data, "jum2"); 
        $newPar->add('jum2', $dataParse);

        $dataParse['jum3'] = (new self)->toArray2($data, "jum3"); 
        $newPar->add('jum3', $dataParse);
       
        $newPar->single('time', strtoupper($bulanData[ $bulan - 1 ]) ." ". $tahun );
        $newPar->single('jubar', strtoupper(base64_decode($type)) );
        $newPar->single('jubar', strtoupper(base64_decode($type)) );
        $newPar->single('tagar', $namatagar );

        // $dataParse['swp'] = (new self)->toArray2($data, "swp"); 
        // $newPar->add('swp', $dataParse);

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

}