<?php

use NN\Route;
use NN\Session;
use NN\Module\DB;
use NN\Module\DD;
use NN\Link;
use NN\Files;
use NN\load;
use NN\Module\View;
use NN\Module\Uuid as id;
use NN\Webs;
use NN\Bug;
use NN\Module\crontab;
use NN\Module\Help;
use NN\Module\Datatable;
use NN\Enc;
use NN\Post;

$route->add('/api/kartukekayaan/anggota/{faktur}',  function($kode){
    ini_set('display_errors', 1);
    $kode = base64_decode($kode);
    $pjlist = [
        ["id"=>"REG","text"=>"USP Reguler"],["id"=>"KHUSUS","text"=>"USP KHUSUS"],["id"=>"USP1","text"=>"USP 1 (Pembiayaan Elektronik)"],["id"=>"USP2","text"=>"USP 2 (Pembiayaan Sepeda Motor)"],["id"=>"USP3","text"=>"USP 3 (Pembiayaan Haji/ Umroh)"],["id"=>"NON","text"=>"PINJAMAN ANGGOTA NON GAJI"]
    ];
    $thn = [
            ["id"=>"01","text"=>"Januari"],
            ["id"=>"02","text"=>"Februari"],
            ["id"=>"03","text"=>"Maret"],
            ["id"=>"04","text"=>"April"],
            ["id"=>"05","text"=>"Mei"],
            ["id"=>"06","text"=>"Juni"],
            ["id"=>"07","text"=>"Juli"],
            ["id"=>"08","text"=>"Agustus"],
            ["id"=>"09","text"=>"September"],
            ["id"=>"10","text"=>"Oktober"],
            ["id"=>"11","text"=>"November"],
            ["id"=>"12","text"=>"Desember"]
    ];

    $user = DB::query_result_object_row("SELECT 
    a.nama 
    , a.kode 
    , a.prima 
    , a.telp 
    , a.masuk
    , b.lokasi unitkerja
    FROM anggota a 
    LEFT JOIN munitkerja b ON b.kode = a.unitkerja
    WHERE 
    a.kode = '$kode' ");

    $jp = [["id"=>"REG","text"=>"USP Reguler"],["id"=>"KHUSUS","text"=>"USP KHUSUS"],["id"=>"USP1","text"=>"USP 1 (Pembiayaan Elektronik)"],["id"=>"USP2","text"=>"USP 2 (Pembiayaan Sepeda Motor)"],["id"=>"USP3","text"=>"USP 3 (Pembiayaan Haji/ Umroh)"],["id"=>"NON","text"=>"PINJAMAN ANGGOTA NON GAJI"]];

    $ds = [];

    // data area
    //simpanan
    $sm = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SM' AND tgl < '".date("Y")."-01-01' AND kode = '$kode')
            ,0) total
        ")->total;
        
        $sp = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SP' AND tgl < '".date("Y")."-01-01' AND kode = '$kode')
            ,0) total
        ")->total;

        $sw = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SW' AND tgl < '".date("Y")."-01-01' AND kode = '$kode')
            ,0) total
        ")->total;

        $swp = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SWP' AND tgl < '".date("Y")."-01-01' AND kode = '$kode')
            ,0) total
        ")->total;

        $bw = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'BW' AND tgl < '".date("Y")."-01-01' AND kode = '$kode')
            ,0) total
        ")->total;

        $tk = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'TK' AND tgl < '".date("Y")."-01-01' AND kode = '$kode')
            ,0) total
        ")->total;

        $pt = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum( kredit ) * - 1  FROM totsl WHERE 
                kredit <> 0 
                AND kodesc = '$kode'
                AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-01-01"))."'
            )
            ,0) total
        ")->total + DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(ptg.lunas) FROM ptg
                LEFT JOIN totsl ON totsl.faktur = ptg.fkt WHERE totsl.kodesc = '$kode' AND ptg.tgl <= '".date("Y-m-t",strtotime(date('Y')."-01-01"))."'
                )
            ,0) total
        ")->total ;

        // pinjaman
        $pj = [
            ["kode"=>"reguler", "name" => "REG"]
            ,["kode"=>"khusus", "name" => "KHUSUS"]
            ,["kode"=>"usp1", "name" => "USP1"]
            ,["kode"=>"usp2", "name" => "USP2"]
            ,["kode"=>"usp3", "name" => "USP3"]
        ];

    $xx = [];
    $xx['th'] = '00';
    $xx['pokok'] = $sp + 0;
    $xx['wajib'] = $sw + 0;
    $xx['manasuka'] = $sm + 0;
    $xx['wp'] = $swp + 0;
    $xx['bw'] = $bw + 0;
    $xx['tk'] = $tk + 0;
    $xx['konsumsi'] = $pt * -1 + 0;
    $xx['jml1'] = $sp + $sw + $sm + $swp;
    foreach ($pj as $key => $pjd) {
        $pjd = (object) $pjd;
        $c = "
            SELECT ifnull((SELECT sum(pokok) - ifnull(ags.tt,0) total FROM mpinjam
            LEFT JOIN ( SELECT fkt,sum((pokok + bunga))  tt FROM mags WHERE tgl < '".date("Y")."-01-01' GROUP BY fkt )
            ags ON ags.fkt = mpinjam.faktur
            WHERE kode = '$kode' AND tgl < '".date("Y")."-01-01' AND kodesp = '$pjd->name'),0) total
        ";
        // echo "<br>----<br>";
        // echo $c;
        // echo "<br>----<br>";
        $sw = DB::query_result_object_row($c)->total;
        $xx[$pjd->kode] = $sw + 0;
    }
    $ds[] = $xx;

    foreach ($thn as $key => $th) {
        $th = (object) $th;

        // simpanan

        $sm = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SM' AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' AND kode = '$kode')
            ,0) total
        ")->total;
        
        $sp = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SP' AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' AND kode = '$kode')
            ,0) total
        ")->total;

        $sw = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SW' AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' AND kode = '$kode')
            ,0) total
        ")->total;

        $bw = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'BW' AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' AND kode = '$kode')
            ,0) total
        ")->total;

        $tk = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'TK' AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' AND kode = '$kode')
            ,0) total
        ")->total;

        $swp = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(if(dk = 'D',jumlah, jumlah * -1)) total FROM 
                msimpan WHERE kodesp = 'SWP' AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' AND kode = '$kode')
            ,0) total
        ")->total;

        $pt = DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum( kredit ) * - 1  FROM totsl WHERE 
                kredit <> 0 
                AND kodesc = '$kode'
                AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."'
            ),0) total
        ")->total + DB::query_result_object_row("
            SELECT ifnull((
                SELECT sum(ptg.lunas) FROM ptg
                LEFT JOIN totsl ON totsl.faktur = ptg.fkt WHERE totsl.kodesc = '$kode' AND ptg.tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."'
                )
            ,0) total
        ")->total;

        // pinjaman
        $xx = [];
        $xx['th'] = $th->id;
        $xx['pokok'] = $sp + 0;
        $xx['wajib'] = $sw + 0;
        $xx['manasuka'] = $sm + 0;
        $xx['wp'] = $swp + 0;
        $xx['bw'] = $bw + 0;
        $xx['tk'] = $tk + 0;
        $xx['konsumsi'] = $pt * -1 + 0;
        $xx['jml1'] = $sp + $sw + $sm + $swp;
        foreach ($pj as $key => $pjd) {
            $pjd = (object) $pjd;
            $c = "
                SELECT
                ifnull((
                    SELECT
                        sum(
                        round( pokok )) total 
                    FROM
                        mpinjam
                    WHERE
                        kode = '$kode' 
                        AND tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."' 
                        AND kodesp = '$pjd->name' 
                        ),
                0 
                ) - ifnull((
                    SELECT sum(( mags.pokok )) tt FROM mags
                    LEFT JOIN mpinjam m ON m.faktur = mags.fkt
                    WHERE mags.tgl <= '".date("Y-m-t",strtotime(date('Y')."-".$th->id."-01"))."'
                    AND mags.kode = '$kode'
                    AND m.kodesp = '$pjd->name'
                ),0) total
            ";
            // echo "<br>----<br>";
            // echo $c;
            // echo "<br>----<br>";
            $wx = DB::query_result_object_row($c)->total;
            $xx[$pjd->kode] = $wx + 0;

        }
        $ds[] = $xx;
    }

    $data = [
        "no" => $user->prima + 0,
        "noinduk" => $user->kode,
        "nama" => $user->nama,
        "unitkerja" => $user->unitkerja,
        "telp" => $user->telp,
        "th" => date('Y', strtotime($user->masuk)),
        "data" => $ds,
    ];

    if(count($_POST) == 0){
        DD::view($data);
    }else{
        echo json_encode($data);
    }
})
->cors('all')
->use('vendor/autoload.php')
->use('module/db.php')
->use('module/dd.php')
->use('module/perusahaan.php');