<?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/data/simpanan',  function(){
    $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"]
    ];
    $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)"]
    ];
    $data = [];
    $data['simpanan'] = [];
    $data['pinjaman'] = [];
    $data['pj'] = [
      "pinjaman" => Help::rupiah(DB::query_result_object_row("SELECT IFNULL((SELECT sum(angsuran*lama) total FROM mpinjam),0) total")->total)
      ,"angsuran" => Help::rupiah(DB::query_result_object_row("SELECT IFNULL((SELECT sum(pokok+bunga) total FROM mags),0) total")->total)
    ];
    $data['sp'] = [
      "sp" => Help::rupiah(DB::query_result_object_row("SELECT IFNULL((SELECT sum(jumlah) total FROM msimpan WHERE dk ='D'),0) total")->total)
      ,"pb" => Help::rupiah(DB::query_result_object_row("SELECT IFNULL((SELECT sum(jumlah) total FROM msimpan WHERE dk ='K'),0) total")->total)
    ];

    foreach ($pjlist as $pj) {
      $pjd = (object) $pj;
      $sw = DB::query_result_object_row("
        SELECT ifnull((SELECT sum(angsuran * lama) - ifnull(ags.tt,0) total FROM mpinjam
        LEFT JOIN ( SELECT fkt,sum((pokok + bunga))  tt FROM mags WHERE tgl < '2023-01-01' GROUP BY fkt )
        ags ON ags.fkt = mpinjam.faktur
        WHERE kodesp = '$pjd->id'),0) total
      ")->total + 0;
      $data['pinjaman'][] = [
        "label" => $pjd->id,
        "value" => $sw
      ];
    }
    
    foreach ($thn as $tahun){
      $tahun = (object) $tahun;
      $lastdate = date("Y-m-t", strtotime(date("Y")."-".$tahun->id."-01"));
      $total = 0;
      if($tahun->id <= date("m")){
        $total = DB::query_result_object_row("
            SELECT sum(if(m.dk = 'D',m.jumlah, m.jumlah * -1)) total FROM 
            msimpan m
            where m.tgl <= '$lastdate'
        ")->total + 0;
      }
      $data['simpanan'][] = [
        "bulan" => $tahun->text,
        "total" => $total
      ];
    };
    
    if(count($_POST) == 0){
        DD::view($data);
    }else{
        echo json_encode($data);
    }
})
->cors('all')
->use('vendor/autoload.php')
->use('module/dd.php')
->use('module/help.php')
->use('module/db.php')
->use('module/perusahaan.php');