koperasi/module/http/excel/kekayaanbaru.php

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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;

if (!function_exists('array_cond')) {
    function array_cond(array $data, string $search = '', string $name = '')
    {
        if ($search != '') {
            $search = strtolower($search);

            return array_filter($data, function ($dat) use ($name, $search) {
                if (is_object($dat)) {
                    $g = $dat->{$name} ?? null;
                    $numcek = 0;
                    if ($g !== null) {
                        $g = strtolower(is_numeric($g) ? strval($g) : $g);
                        if ($numcek == 0 && $g == $search) {
                            $numcek = 1;
                        }
                    }
                    if ($numcek == 1) {
                        return $dat;
                    }
                } else {
                    if ($dat !== null) {
                        $dat = strtolower(is_numeric($dat) ? strval($dat) : $dat);
                        if ($dat == $search) {
                            return $dat;
                        }
                    }
                }
            });
        } else {
            return $data;
        }
    }
}

class Kekayaanbaru {

    public static function getData($tahun){
        $data['anggota'] = DB::query_result_object("SELECT * FROM anggota a ORDER BY jurubayar DESC, kode DESC");
        $data['simp'] = DB::query_result_object("SELECT kode, nama FROM jpinjam WHERE typetransaksi = 'simpanan' AND rw <> '-'");
        $data['simplast'] = DB::query_result_object("
SELECT kode, kodesp, sum(jumlah) jumlah FROM (
SELECT 
kode
, kodesp
, month(tgl) bulan
, sum(jumlah) jumlah 
FROM msimpan WHERE tgl < '$tahun-01-01' 
AND faktur LIKE 'BKM%'
GROUP BY kode, kodesp
UNION ALL
SELECT kode, kodesp, bulan, sum(jumlah) jumlah FROM (
SELECT 
kode
, kodesp
, month(tgl) bulan
, sum(jumlah * -1) jumlah 
FROM msimpan WHERE tgl < '$tahun-01-01' 
AND faktur LIKE 'BKK%'
GROUP BY kode, kodesp
UNION ALL
SELECT kode, kodesp, bulan, jumlah FROM (
	SELECT kodesc kode, 'BW' kodesp, lpad( month(tgl), 2,0) bulan, sum(debet) * -1 jumlah FROM totsl 
	WHERE debet <> 0 AND tgl < '$tahun-01-01' AND faktur NOT LIKE 'FS%'
	GROUP BY kode, bulan
) a
) a GROUP BY a.kode, a.kodesp, a.bulan
) a GROUP BY a.kode, a.kodesp
        ");
        $data['simpanan'] = DB::query_result_object("
            SELECT kode, kodesp, bulan, sum(jumlah) jumlah FROM (
            SELECT 
            kode
            , kodesp
            , month(tgl) bulan
            , sum(jumlah) jumlah 
            FROM msimpan WHERE tgl like '$tahun%' 
            AND faktur LIKE 'BKM%'
            GROUP BY bulan, kode, kodesp
            UNION ALL
            SELECT a.kode, a.kodesp, a.bulan, sum(a.jumlah) jumlah FROM (
                SELECT 
                    kode
                    , kodesp
                    , month(tgl) bulan
                    , sum(jumlah * -1) jumlah 
                    FROM msimpan WHERE tgl like '$tahun%' 
                    AND faktur LIKE 'BKK%' AND kodesp <> 'TK'
                GROUP BY bulan, kode, kodesp	
                UNION ALL 
                SELECT kode, kodesp, bulan, jumlah FROM (
                        SELECT kodesc kode, 'BW' kodesp, month(tgl) bulan, sum(debet) * -1 jumlah FROM totsl 
                        WHERE debet <> 0 AND tgl LIKE '$tahun%' AND faktur NOT LIKE 'FS%'
                        GROUP BY kode, bulan
                ) a
            ) a GROUP BY a.kode, a.kodesp, a.bulan
        ) a GROUP BY a.kode, a.kodesp, a.bulan
        ");
        $data['pinjaman'] = DB::query_result_object("SELECT * FROM (
            SELECT tr, kode, kodesp, sum(mpinjam.pokok) pokok, b.pokok angpokok, b.bunga angbunga FROM mpinjam
            LEFT JOIN (
                    SELECT m.faktur, sum(mags.pokok) pokok, sum(mags.bunga) bunga FROM mags
                    LEFT JOIN mpinjam m ON m.faktur = mags.fkt GROUP BY faktur
            ) b ON b.faktur = mpinjam.faktur
            GROUP BY kode, kodesp, tr
        ) a ");
        $data['tk'] = DB::query_result_object("
                SELECT 
                kode
                , kodesp
                , month(tgl) bulan
                , sum(jumlah) jumlah 
                FROM msimpan WHERE tgl like '$tahun%' 
                AND faktur LIKE 'BKK%' AND kodesp = 'TK'
                GROUP BY bulan, kode, kodesp
        ");
        return $data;
    }

    public static function output($spreadsheet = null){
        if($spreadsheet){
            $filename = 'hello world.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
    
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
        }
    }

    public static function do($thn=null){
        View::render('kekayaan2', [
            "total" => json_encode(DB::query_result_object("
                SELECT kode, kodesp, `status`, sum(jumlah) jumlah FROM (
                SELECT 
                kode
                , kodesp
                , IF(left(faktur,3) = 'BKM', 'masuk', 'keluar') `status`
                , sum(IF(left(faktur,3) = 'BKM', jumlah, jumlah*-1)) jumlah
                FROM msimpan
                GROUP BY kode, kodesp
                UNION ALL
                SELECT kode, kodesp, 'masuk' status, jumlah FROM (
                    SELECT kodesc kode, 'BW' kodesp, sum(debet) * -1 jumlah FROM totsl 
                    WHERE debet <> 0 AND faktur NOT LIKE 'FS%'
                    GROUP BY kode
                ) a
                ) a GROUP BY kode, kodesp
            ")),
            "tahun" => $thn? $thn : date('Y'),
            "data" => self::getData($thn? $thn : date('Y'))
        ]);
    }

    public static function print(){
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();
        $activeWorksheet->setCellValueByColumnAndRow(2,1, 'Hello World !');
        
    }



}