<?php
session_start();
require_once 'db.php';
include_once "ewcfg12.php";
include_once "phpfn12.php";
include_once "class.user.php";

$user     = new USER($DB_con);

if(!$user->is_loggedin())
{
   $user->redirect('login.php');
}

// storing  request (ie, get/post) global array to a variable
$req = $_REQUEST;

// datatable column index  => database column name
$columns = array(
	0 => 'USERID',
	1 => 'USERID',
	2 => 'FULLNAME',
	3 => 'USEREMAIL',
  4 => 'USERLEVEL',
  5 => 'ACTIVATED'
);

// getting total number records without any search
$sql = "SELECT USERID
          FROM WEBUSER
         WHERE USERID <> 'admin'
       ";
$stmt = $DB_con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$totalData     = $stmt->rowCount();
$totalFiltered = $totalData;

// if there is a search parameter, $req['search']['value'] contains search parameter
if (isset($req['search']['value']) && !empty($req['search']['value'])){
    $where = "WHERE WEBUSER.USERID <> 'admin'
                AND (WEBUSER.USERID like '%".$req['search']['value']."%'
                 OR WEBUSERPROFILE.FULLNAME like '%".$req['search']['value']."%')

            ";
} else {
    $where = "WHERE WEBUSER.USERID <> 'admin'";
}


// retrieve WEBUSER table
$sql  =  "SELECT  WEBUSER.USERID
                 ,WEBUSERPROFILE.FULLNAME
                 ,WEBUSER.USEREMAIL
                 ,WEBUSER.USERLEVEL
                 ,WEBUSER.ACTIVATED
            FROM WEBUSER
            LEFT OUTER JOIN WEBUSERPROFILE
              ON WEBUSERPROFILE.USERID = WEBUSER.USERID
         "
        . $where;
$stmt = $DB_con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$totalFiltered = $stmt->rowCount();
$sql .= "  ORDER BY " . $columns[$req['order'][0]['column']] // column to sort
                      . " "
                      . $req['order'][0]['dir']              // sort order desc/asc
                      . " OFFSET "
                      . $req['start']                        // start from
                      . " ROWS FETCH NEXT "
                      . $req['length']                       // total to retrieve
                      . " ROWS ONLY ";
$stmt = $DB_con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$data = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	$nestedData=array();

  if ($row['USERLEVEL']=='0'){
    $userlevel = 'Normal';
  } else if ($row['USERLEVEL']=='-1'){
    $userlevel = 'Administrator';
  } else if ($row['USERLEVEL']=='-2'){
    $userlevel = 'Administrator (Bukan Tentera)';
  } else if ($row['USERLEVEL']=='-999'){
    $userlevel = 'System Administrator';
  } else if ($row['USERLEVEL']=='50'){
    $userlevel = 'JHEKP';
  } else {
    $userlevel = 'Undefined';
  }

  if ($row['ACTIVATED']=='0'){
    $useractivated = 'No';
  } else if ($row['ACTIVATED']=='1'){
    $useractivated = 'Yes';
  } else {
    $useractivated = 'Undefined';
  }

	$nestedData[] = $row["USERID"   ];
	$nestedData[] = $row["USERID"   ];
	$nestedData[] = $row["FULLNAME" ];
	$nestedData[] = $row["USEREMAIL"];
	$nestedData[] = $userlevel;
	$nestedData[] = $useractivated;

	$data[]       = $nestedData;

}

$json_data = array(
			"draw"            => intval( $req['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
			"recordsTotal"    => intval( $totalData ),     // total number of records
			"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
			"data"            => $data                     // total data array
			);

echo json_encode($json_data);  // send data as json format
//echo $sql;

?>
