koperasi/module/http/excel/anggota.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 Anggota {

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

    public static function do($tahun=""){
        $tht = date('Y-m-t', strtotime( $tahun.'-'.$bulan ) );
        ini_set('display_errors', 1);
        $file = SETUP_PATH.'excel/anggota-'.$tahun.'.xlsx';
        $filex = SETUP_PATH.'/excel/jmanggota.xlsx';
        define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);

        $x = "";
        $xa = " lawl ";
        $xb = " pawl ";
        $xj = " (lawl) + pawl ";
        for ($i=1; $i <= 4; $i++) { 
            $xa .= " + ml$i - kl$i ";
            $xb .= " + mp$i - kp$i ";
            $xj .= " + ml$i - kl$i + mp$i - kp$i ";
            $x .= "
            , ml$i
            , mp$i
            , ml$i + mp$i jmlm$i
            , kl$i
            , kp$i
            , kl$i + kp$i jmlk$i
            , $xa l$i
            , $xb p$i
            , $xj jm$i
            ";
        }


        $qr = "
        SELECT 
        '={no}-4' `no` 
        , jurubayar
        , lawl
        , pawl
        , (lawl) + pawl jmawl
        $x
        FROM (
        SELECT 
            jurubayar
            , sum(if(bulan = 'lawl', total, 0)) lawl
            , sum(if(bulan = 'pawl', total, 0)) pawl
            , sum(if(bulan = 'ml1', total, 0)) ml1
            , sum(if(bulan = 'mp1', total, 0)) mp1
            , sum(if(bulan = 'kl1', total, 0)) kl1
            , sum(if(bulan = 'kp1', total, 0)) kp1
            
            , sum(if(bulan = 'ml2', total, 0)) ml2
            , sum(if(bulan = 'mp2', total, 0)) mp2
            , sum(if(bulan = 'kl2', total, 0)) kl2
            , sum(if(bulan = 'kp2', total, 0)) kp2
            
            , sum(if(bulan = 'ml3', total, 0)) ml3
            , sum(if(bulan = 'mp3', total, 0)) mp3
            , sum(if(bulan = 'kl3', total, 0)) kl3
            , sum(if(bulan = 'kp3', total, 0)) kp3
            
            , sum(if(bulan = 'ml4', total, 0)) ml4
            , sum(if(bulan = 'mp4', total, 0)) mp4
            , sum(if(bulan = 'kl4', total, 0)) kl4
            , sum(if(bulan = 'kp4', total, 0)) kp4
        FROM (
        (
        SELECT jurubayar, 'masuk' status, lower(concat(jk,'awl')) bulan, sum(total) total  FROM (
        (SELECT 
            rekapgol jurubayar
            , 'masuk' status
            , jk
            , count(*) total 
        FROM 
        anggota WHERE masuk < '$tahun-01-01' 
        AND masuk IS NOT NULL 
        GROUP BY jk, jurubayar
        )
        UNION ALL 
        (
        SELECT 
            rekapgol jurubayar
            , 'keluar' status
            , jk
            , count(*) * -1 total  
        FROM 
        anggota WHERE keluar < '$tahun-01-01' 
        AND keluar IS NOT NULL 
        GROUP BY jk, jurubayar
        )
        ) a GROUP BY jk, jurubayar
        )
        UNION ALL
        (SELECT 
            jurubayar
            , 'masuk' status
            , lower( concat('m',jk
            ,if(
                bulan <= 3, '1', if(bulan > 3 
                AND bulan <= 6, '2',  if(bulan > 6 
                AND bulan <= 9, '3', '4') )
            ))) bulan
            ,count(1) total
        FROM (
            SELECT rekapgol jurubayar, jk, month(masuk) bulan 
            FROM anggota WHERE masuk LIKE '$tahun%' 
        ) a
        GROUP BY jurubayar, jk, bulan ORDER BY bulan ASC, jurubayar ASC)
        UNION ALL
        (
        SELECT 
            jurubayar
            , 'keluar' status
            , lower( concat('k',jk
            ,if(
                bulan <= 3, '1', if(bulan > 3 
                AND bulan <= 6, '2',  if(bulan > 6 
                AND bulan <= 9, '3', '4') )
            ))) bulan
            ,count(1) total
        FROM (
            SELECT rekapgol jurubayar, jk, month(keluar) bulan 
            FROM anggota WHERE keluar LIKE '$tahun%' 
        ) a
        GROUP BY jurubayar, jk, bulan ORDER BY bulan ASC, jurubayar ASC
        )
        ) a GROUP BY jurubayar
        ) a
        ";
        // echo $qr;
        // die();
        $data = DB::query_result_object($qr);
        
        $dataParse = [];
        $newPar = new Param();

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

        $name = ["lawl", "pawl", "jmawl"];

        for ($i=1; $i <= 4; $i++) { 
            $name[] = "ml$i";
            $name[] = "mp$i";
            $name[] = "jmlm$i";
            $name[] = "kl$i";
            $name[] = "kp$i";
            $name[] = "jmlk$i";
            $name[] = "l$i";
            $name[] = "p$i";
            $name[] = "jm$i";
        }

        $dataParse[ 'jurubayar' ] = (new self)->toArray2($data, 'jurubayar'); 
        $newPar->add( 'jurubayar' , $dataParse);
        $newPar->single( 'th1' , date('Y') -1 );
        $newPar->single( 'th2' , date('Y'));
        foreach ($name as $key => $nm) {
            $dataParse[ $nm ] = (new self)->toArray2($data, $nm); 
            $newPar->add( $nm , $dataParse);
        }

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

    public static function view(){
        return View::render('laporan/anggota');
    }

    function exist($dataArray, $property, $condition, $value) {
        foreach ($dataArray as $item) {
            if (isset($item[$property]) && $item[$property] === $value) {
                return true;
            }
        }
        return false;
    }


    public function transformArray($dataArray, $groupKeys, $transformKeys) {
        $result = [];

        $group = [];
        foreach ($dataArray as $key => $value) {
            $value = (array) $value;
            if((new self)->exist($group, $groupKeys, $value[$groupKeys]) === false){
                $group[] = [
                    $groupKeys => $value[$groupKeys]
                ];
            }
        }
        return $group;
    }


}