<?php 
// array for JSON response
$response = array();
 
require_once __DIR__ . '/catchexception.php'; 
// include db connect class
require_once __DIR__ . '/db_config.php';
  
  // $paraimeino = urldecode($_POST['paraimeino']);  
  // $billdate = urldecode($_POST['parabilldate']);
  $paraimeino ='aa306fe80a7dceac';
  $billdate='1782066600000';
try{
if ($paraimeino !=""   ) { 
  $paravancodeqry="SELECT branch_devicemapcode  from tblbranchdevicemapping as a where  deviceid=$1";
  $resultmax=  pg_prepare($con,"vancodequery",$paravancodeqry);
  $data = [$paraimeino];
  $res = pg_execute($con,"vancodequery",$data);
  if (!empty($res)) {
      if (pg_num_rows($res) > 0) {   
        while ($row = pg_fetch_assoc($res)) {
            $paradevicecode= $row["branch_devicemapcode"] ;
          }
      }
  }

  $parabranchcode=0;
  $parabranchcodeqry="SELECT branchcode from tblbranchdevicemapping as a where  deviceid=$1";
  $resultmax=  pg_prepare($con,"branchcodequery",$parabranchcodeqry);
  $data = [$paraimeino];
  $res = pg_execute($con,"branchcodequery",$data);
  if (!empty($res)) {
      if (pg_num_rows($res) > 0) {   
        while ($row = pg_fetch_assoc($res)) {
            $parabranchcode= $row["branchcode"] ;
          }
      }
  }

  $sales_sessionid=-1;
  $sessionidsql="SELECT cast(coalesce(max(sessionid),'0') as integer) as sessionid from tblsales where deviceid=$1 and transactiondate=$2;";
  $resultmax=  pg_prepare($con,"sessionidsql",$sessionidsql);
  $data = [$paraimeino,$billdate];
  $res = pg_execute($con,"sessionidsql",$data);
  if (!empty($res)) {
    if (pg_num_rows($res) > 0) {   
      while ($row = pg_fetch_assoc($res)) {
          $sales_sessionid= $row["sessionid"] ;
        }
    }
  }

  $getCount=-1;
  $getCountsql="SELECT count(*) as count from tblsessionclose
    where devicecode=$1 and sessionclosedate=$2 and cast(sessionid as integer)=$3;";
  $resultmax=  pg_prepare($con,"getCountsql",$getCountsql);
  $data = [$paraimeino,$billdate,$sales_sessionid];
  $res = pg_execute($con,"getCountsql",$data);
  if (!empty($res)) {
    if (pg_num_rows($res) > 0) {   
      while ($row = pg_fetch_assoc($res)) {
          $getCount= $row["count"] ;
        }
    }
  }

  $getDayCount=-1;
  $getDayCountsql="SELECT count(*) from tbldayclose where devicecode=$1 and 
        closedate=$2 and branchcode=$3;";
  $resultmax=  pg_prepare($con,"getDayCountsql",$getDayCountsql);
  $data = [$paraimeino,$billdate,$parabranchcode];
  $res = pg_execute($con,"getDayCountsql",$data);
  if (!empty($res)) {
    if (pg_num_rows($res) > 0) {   
      while ($row = pg_fetch_assoc($res)) {
          $getDayCount= $row["count"] ;
        }
    }
  }

  if($sales_sessionid == 0){
    $sales_sessionid = $sales_sessionid+1;
  }
  $valsessionid=-1;
  if($getCount==0){
    $valsessionid = $sales_sessionid;
  }
  if($getCount > 0){
    $valsessionid = $sales_sessionid+1; 
  }
  if($getDayCount == 1){
    $valsessionid = 1;
  }

 $previousdaysales_sessionid=-1;
  $previousdaysales_sessionidsql="SELECT cast(coalesce(max(sessionid),'0') as integer) as sessionid from tblsales where deviceid=$1 and branchcode=$2;";
  $resultmax=  pg_prepare($con,"previousdaysales_sessionidsql",$previousdaysales_sessionidsql);
  $data = [$paraimeino,$parabranchcode];
  $res = pg_execute($con,"previousdaysales_sessionidsql",$data);
  if (!empty($res)) {
    if (pg_num_rows($res) > 0) {   
      while ($row = pg_fetch_assoc($res)) {
          $previousdaysales_sessionid= $row["sessionid"] ;
        }
    }
  }

  $lastTransactionDate = -1;
  $lastTransactionDatesql="SELECT transactiondate from tblsales where deviceid=$1 and branchcode=".$parabranchcode." order by createddate desc limit 1";
  $resultmax=  pg_prepare($con,"lastTransactionDatesql",$lastTransactionDatesql);
  $data = [$paraimeino];
  $res = pg_execute($con,"lastTransactionDatesql",$data);
  if (!empty($res)) {
    if (pg_num_rows($res) > 0) {   
      while ($row = pg_fetch_assoc($res)) {
          $lastTransactionDate= $row["transactiondate"] ;
        }
    }
  }

  if (empty($lastTransactionDate) || $lastTransactionDate == -1) {
    $lastTransactionDate = $billdate;
  }

 
  $sql="WITH 
sales_today AS (
    SELECT 
        MAX(transactionno) AS max_transactionno,
        MAX(COALESCE(sequenceno, voucherno::integer)) AS max_voucherno,
        MAX(transactiondate) FILTER (WHERE transactiondate = $3) AS latest_transactiondate
    FROM tblsales 
    WHERE deviceid = $1 AND transactiondate = $3
),

dayclose_latest AS (
    SELECT 
        branchcode, 
        closedate 
    FROM tbldayclose 
    WHERE devicecode = $1 
    ORDER BY createddate DESC 
    LIMIT 1
),

sales_prev_session AS (
    SELECT 
        SUM(grandtotal) AS total_sales,
        COUNT(*) AS total_count
    FROM tblsales 
    WHERE deviceid = $1 
      AND branchcode = $2
      AND transactiondate = $4
      AND cancelflag = 0 
      AND CAST(sessionid AS INTEGER) = $5
),

distinct_prev_session AS (
    SELECT DISTINCT 
        CAST(sessionid AS INTEGER) AS session_id 
    FROM tblsales 
    WHERE deviceid = $1 
      AND branchcode = $2 
      AND transactiondate = $4 
    EXCEPT
    SELECT 
        CAST(sessionid AS INTEGER) 
    FROM tblsessionclose 
    WHERE devicecode = $1 
      AND branchcode = $2 
      AND sessionclosedate = $4 
),

last_unclosed_date AS (
    SELECT 
        transactiondate 
    FROM tblsales a 
    WHERE deviceid = $1 
      AND branchcode = $2 
      AND NOT EXISTS (
          SELECT 1 
          FROM tbldayclose b 
          WHERE b.devicecode = a.deviceid 
            AND b.branchcode = a.branchcode 
            AND b.closedate = a.transactiondate
      )
    ORDER BY createddate DESC 
    LIMIT 1
)

SELECT 
    '1' AS code, 
    'sales' AS process, 
    COALESCE(s.max_transactionno, 0) + 1 AS transactionno,
    COALESCE(s.max_voucherno, 0) AS voucherno,
    COALESCE(s.latest_transactiondate, 0) AS closedate,
    0 AS salescount
FROM sales_today s

UNION ALL

SELECT 
    '333', 
    'dayclose', 
    0, 
    COALESCE(d.branchcode, 0), 
    COALESCE(d.closedate, 0), 
    0
FROM dayclose_latest d

UNION ALL

SELECT 
    '444', 
    'previous_session',
    COALESCE(p.total_sales, 0),
    COALESCE((SELECT session_id FROM distinct_prev_session LIMIT 1), 0),
    COALESCE((SELECT transactiondate FROM last_unclosed_date LIMIT 1), 0),
    COALESCE(p.total_count, 0)
FROM sales_prev_session p;
";


    $resultmax=  pg_prepare($con,"sql",$sql);
    $data = [$paraimeino,$parabranchcode, $billdate, $lastTransactionDate, $previousdaysales_sessionid];
    $res = pg_execute($con,"sql",$data);
  

  //$resultmax=  pg_prepare($con,"sql",$sql);
  //$data = [$paraimeino,$billdate];
  //$res = pg_execute($con,"sql",$data);
   // $result = mysqli_query($con,$sql);      
    
    if (!empty($res)) {
        // check for empty result
        if (pg_num_rows($res) > 0) {

      // user node
            $response["Value"] = array(); 
 
            while ($row = pg_fetch_assoc($res)) { 
                  
              $report = array();


            $report["code"] = $row["code"];
            $report["process"] = $row["process"];
            $report["transactionno"] = $row["transactionno"];
            $report["voucherno"] = $row["voucherno"];    
            $report["closedate"] = $row["closedate"];   
            $report["salescount"] = $row["salescount"];
            
              // push single product into final response array
              array_push($response["Value"], $report);
            }
            if($valsessionid >=0){
              $report["code"] = 222;
            $report["process"] = 'current_session';
            $report["transactionno"] = 0; // session close count 1 means closed 0 means not closed
            $report["voucherno"] = $valsessionid; // last session id
            $report["closedate"] = 0;
            $report["salescount"] = 0; 
            // push single product into final response array
              array_push($response["Value"], $report);
            }
            // success
            $response["success"] = 1;
 
            // echoing JSON response
            echo  json_encode($response,JSON_UNESCAPED_UNICODE);


        } else {
            // no user found
            $response["success"] = 0;
            $response["message"] = "No Report found";
 
            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // no user found
        $response["success"] = 0;
        $response["message"] = "No found";
 
        // echo no users JSON
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
     echo  json_encode($response,JSON_UNESCAPED_UNICODE);
  }
    mch_db_release($con);
  }
  //catch exception
  catch(Exception $e) {
  mch_db_release($con);
  echo 'Message: ' .$e->getMessage();
  }
?>
