<?php session_start();
if(!isset($_COOKIE['ADMIN_KEY']) && !isset($_COOKIE['KEY'])){
  header('location:./login');
  exit;
} else{
    require_once'../../../sw-library/sw-config.php';
    require_once'../../../sw-library/sw-function.php';

    $filterParts = [];
    $master_spp   = isset($_POST['master_spp']);
    
    if (!empty($_POST['siswa'])) {
        $siswa = htmlentities(epm_decode($_POST['siswa']??'-'));
        $filterParts[] = "user.user_id='$siswa'";
        $filter = 'WHERE ' . implode(' AND ', $filterParts);

    }else if(!empty($_POST['kelas'])) {
        $kelas = htmlentities($_POST['kelas']);
        $filterParts[] = "user.kelas='$kelas'";
        $filter = 'WHERE ' . implode(' AND ', $filterParts);
    }else{
        $filter = '';
    }


    $aColumns = ['user_id', 'nama_lengkap', 'nisn', 'kelas'];
    $sIndexColumn = "user_id";
    $sTable = "user";
    
    $gaSql['user'] = DB_USER;
    $gaSql['password'] = DB_PASSWD;
    $gaSql['db'] = DB_NAME;
    $gaSql['server'] = DB_HOST;

    $gaSql['link'] =  new mysqli($gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']);

    $sLimit = "";
    if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1')
    {
        $sLimit = "LIMIT ".mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayStart']).", ".
            mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayLength']);
    }

    $sOrder = "ORDER BY user.nama_lengkap ASC";
    if (isset($_GET['iSortCol_0']))
    {
        $sOrder = "ORDER BY user.nama_lengkap ASC";
        for ($i=0; $i<intval($_GET['iSortingCols']) ; $i++)
        {
            if ($_GET['bSortable_'.intval($_GET['iSortCol_'.$i])] == "true")
            {
                $sOrder .= $aColumns[ intval($_GET['iSortCol_'.$i])]."
                    ".mysqli_real_escape_string($gaSql['link'], $_GET['sSortDir_'.$i]) .", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY user.nama_lengkap ASC")
        {
            $sOrder = "ORDER BY user.nama_lengkap ASC";
        }
    }

    $sWhere = "$filter";
    if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
    {
        $sWhere = "WHERE (";
        for ($i=0; $i<count($aColumns); $i++)
        {
            $sWhere .= $aColumns[$i]." LIKE '%".mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'])."%' OR ";
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ')';
    }

    for ($i=0 ; $i<count($aColumns); $i++)
    {
        if (isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '')
        {
            if ($sWhere == "")
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".mysqli_real_escape_string($gaSql['link'], $_GET['sSearch_'.$i])."%' ";
        }
    }

    $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM $sTable
        $sWhere
        $sOrder
        $sLimit ";
    $rResult = mysqli_query($gaSql['link'], $sQuery);

    $sQuery = "SELECT FOUND_ROWS()";
    $rResultFilterTotal = mysqli_query($gaSql['link'], $sQuery);
    $aResultFilterTotal = mysqli_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];

    $sQuery = "SELECT COUNT(".$sIndexColumn.") FROM   $sTable";
    $rResultTotal = mysqli_query($gaSql['link'], $sQuery);
    $aResultTotal = mysqli_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];

    $output = array( 
       // "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    $no = 0;
    
    while ($aRow = mysqli_fetch_array($rResult)){$no++;
      extract($aRow);
        $row = array();

        $data_spp = $connection->query("SELECT nominal, tahun_ajaran
                FROM master_spp WHERE status='Y' AND master_spp_id='$master_spp'
                ")->fetch_assoc();
        // total pembayaran
        $total_pembayaran = $connection->query("SELECT SUM(nominal) AS total 
        FROM pembayaran 
        WHERE user_id='$aRow[user_id]' 
        AND master_spp_id='$master_spp'
        ")->fetch_assoc()['total'] ?? 0;

        // hitung tunggakan
        $jumlah_tunggakan = $data_spp['nominal'] - $total_pembayaran;
        
        
        $row[] = '<div class="text-center">'.$no.'</div>';
        $row[] = ''.($aRow['nama_lengkap']??'-').'<br><span class="badge badge-info">'.$aRow['nisn'].'</span>';
        $row[] = ($aRow['kelas']??'-');
        $row[] = ($data_spp['tahun_ajaran']);
        for($bulan=1; $bulan<=12; $bulan++){

            $query_pembayaran ="SELECT nominal FROM pembayaran WHERE user_id='$aRow[user_id]' AND bulan='$bulan' AND master_spp_id='$master_spp'";
            $result_pembayaran = $connection->query($query_pembayaran);
            if($result_pembayaran->num_rows > 0){
              $data_pembayaran = $result_pembayaran->fetch_assoc();
              $nominal_pembayaran = 'Rp '.format_angka($data_pembayaran['nominal']).'';
            }else{
              $nominal_pembayaran = '-';
            }

            $row[] = $nominal_pembayaran;
        }
        $row[] = '<b class="text-info">Rp '.format_angka($total_pembayaran).'</b>';
        $row[] = '<b class="text-danger">Rp '.format_angka($jumlah_tunggakan).'</b>';
    
        $output['aaData'][] = $row;
   
    }
    echo json_encode($output);
}