<?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;
$user_id = trim(ew_RemoveXSS(ew_StripSlashes($_SESSION['user_session'])));
// datatable column index  => database column name
$columns = array(
	0 => 'ID',
	1 => 'MAILREAD',
	2 => 'ID',
	3 => 'MAILFROM',
	4 => 'MAILTO',
	5 => 'MAILSUBJECT',
	6 => 'DATECREATED',
);

// getting total number records without any search

if ($_SESSION[EW_SESSION_USER_LEVEL]==-999 || $_SESSION[EW_SESSION_USER_LEVEL]==-1 || $_SESSION[EW_SESSION_USER_LEVEL]==50){
    $sql = "SELECT ID
              FROM MAILBOX
             WHERE (MAILFROM IN (SELECT USERID 
                               FROM WEBUSER
                              WHERE USERLEVEL IN (-999, -1, 50)         
                            ) OR
                      MAILTO IN (SELECT USERID 
                                   FROM WEBUSER
                                  WHERE USERLEVEL IN (-999, -1, 50)         
                            )
                   )
               AND PARENTID IS NULL
           ";
} else {
    $sql = "SELECT ID
              FROM MAILBOX
             WHERE (MAILFROM='$user_id' OR 
                    MAILTO  ='$user_id' OR
                  MAILTO  = 'everyone')
               AND PARENTID IS NULL
           ";
}
$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'])){
  if ($_SESSION[EW_SESSION_USER_LEVEL]==-999 || $_SESSION[EW_SESSION_USER_LEVEL]==-1 || $_SESSION[EW_SESSION_USER_LEVEL]==50){    
      $where = " WHERE (MAILFROM IN (SELECT USERID 
                                       FROM WEBUSER
                                      WHERE USERLEVEL IN (-999, -1, 50)         
                                    ) OR
                          MAILTO IN (SELECT USERID 
                                       FROM WEBUSER
                                      WHERE USERLEVEL IN (-999, -1, 50)         
                                    )
                        )
                   AND PARENTID IS NULL      
                   AND (MAILFROM like '%".$req['search']['value']."%'
                    OR MAILSUBJECT like '%".$req['search']['value']."%')

              ";
  } else {
      $where = " WHERE (MAILFROM='$user_id' OR 
                        MAILTO  ='$user_id' OR
                        MAILTO  = 'everyone')
                   AND PARENTID IS NULL      
                   AND (MAILFROM like '%".$req['search']['value']."%'
                    OR MAILSUBJECT like '%".$req['search']['value']."%')

              ";    
  }
} else {
  if ($_SESSION[EW_SESSION_USER_LEVEL]==-999 || $_SESSION[EW_SESSION_USER_LEVEL]==-1 || $_SESSION[EW_SESSION_USER_LEVEL]==50){    
      $where = " WHERE (MAILFROM IN (SELECT USERID 
                                       FROM WEBUSER
                                      WHERE USERLEVEL IN (-999, -1, 50)         
                                    ) OR
                          MAILTO IN (SELECT USERID 
                                       FROM WEBUSER
                                      WHERE USERLEVEL IN (-999, -1, 50)         
                                    )
                        )
                   AND PARENTID IS NULL ";
  } else {
      $where = " WHERE (MAILFROM='$user_id' OR 
                        MAILTO  ='$user_id' OR
                        MAILTO  = 'everyone')
                   AND PARENTID IS NULL";
  }
}


// retrieve MAILBOX table
$sql  =  "SELECT  ID
                 ,MAILREAD
                 ,MAILFROM
                 ,MAILTO
                 ,MAILSUBJECT
                 ,DATECREATED
            FROM MAILBOX
         "
        . $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


if (isset($req['order'][1]['column']) && !empty($req['order'][1]['column'])){
    $sql.=              ", "
                      . $columns[$req['order'][1]['column']] // column to sort
                      . " "
                      . $req['order'][1]['dir'];             // sort order desc/asc
}

$sql .=                 " 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();

  $childNewMail = 0;
  $mailid       = $row["ID"         ];
  $mailread     = $row["MAILREAD"   ];
  $mailfrom     = $row["MAILFROM"   ];
  $mailto       = $row["MAILTO"     ];
  $mailsubject  = $row["MAILSUBJECT"];
  $datecreated  = $row["DATECREATED"];

  //Check if reply child thread (parentid<>null) any new mail (mailread=0), if yes, consider new mail
  if ($_SESSION[EW_SESSION_USER_LEVEL]==-999 || $_SESSION[EW_SESSION_USER_LEVEL]==-1 || $_SESSION[EW_SESSION_USER_LEVEL]==50){
      $sql  =  "SELECT MAILREAD
                  FROM MAILBOX
                 WHERE (MAILFROM IN (SELECT USERID 
                                       FROM WEBUSER
                                      WHERE USERLEVEL IN (-999, -1, 50)         
                                    ) OR
                          MAILTO IN (SELECT USERID 
                                       FROM WEBUSER
                                      WHERE USERLEVEL IN (-999, -1, 50)         
                                    )
                       )
                   AND PARENTID=$mailid
                   AND MAILREAD=0
                 ORDER BY MAILREAD
               ";
  } else {
      $sql  =  "SELECT MAILREAD
                  FROM MAILBOX
                 WHERE (MAILTO  ='$user_id' OR
                        MAILTO  = 'everyone')
                   AND PARENTID=$mailid
                   AND MAILREAD=0
                 ORDER BY MAILREAD
               ";    
  }
  $stmt2 = $DB_con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
  $stmt2->execute();
  $childNewMail = $stmt2->rowCount();

  // Check whether has any child record
  $sql  =  "SELECT MAILREAD
              FROM MAILBOX
             WHERE PARENTID=$mailid
           ";
  $stmt3 = $DB_con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
  $stmt3->execute();
  $hasChildRecord = $stmt3->rowCount();



  if ($mailread==1){
      if ($childNewMail==0){
          $mailread = "<i class=\"fa fa-envelope-open-o text-yellow\" aria-hidden=\"true\"></i>";
      } else {
          $mailread = "<i class=\"fa fa-envelope text-yellow\" aria-hidden=\"true\"></i>";
          //<div style='display: none;'>10</div>
      }
  } else {
      if ($hasChildRecord==0){
          if ($mailfrom==$user_id){
              $mailread = "<i class=\"fa fa-envelope-open-o text-yellow\" aria-hidden=\"true\"></i>";
          } else {
              $mailread = "<i class=\"fa fa-envelope-open text-yellow\" aria-hidden=\"true\"></i>";
          }
      } else {
          if ($childNewMail==0){
              $mailread = "<i class=\"fa fa-envelope-open-o text-yellow\" aria-hidden=\"true\"></i>";
          } else {
              $mailread = "<i class=\"fa fa-envelope text-yellow\" aria-hidden=\"true\"></i>";
          }
      }
  }

  if (strtolower($mailto)=='everyone'){
      $mailread = "<i class=\"fa fa-star text-yellow\"  aria-hidden=\"true\"></i>";
  }

  $mailfrom     = $row["MAILFROM"];
  $mailfromlink = "<a href=\"mailbox-read.php?id=$mailid\">$mailfrom</a>";

	$nestedData[] = $mailid;
	$nestedData[] = $mailread;
  $nestedData[] = $mailid;
	$nestedData[] = $mailfromlink;
	$nestedData[] = $mailto;
	$nestedData[] = $mailsubject;
	$nestedData[] = $datecreated;

	$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;

?>
