<?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']);
//  $paraimeino ='5c1784d48b248af8'; 
 try{
if ($paraimeino !="" ) {
 

    $getbranchcode= 0;

    // get a product from products table
    $paracountqry1="SELECT branchcode  from tblbranchdevicemapping where deviceid=$1 ;";
    $resultmax=  pg_prepare($con,"paracountqry1",$paracountqry1);
    $data = [$paraimeino];
    $res = pg_execute($con,"paracountqry1",$data);
    if (!empty($res)) {
      if (pg_num_rows($res) > 0) {   
        while ($row = pg_fetch_assoc($res)) {
            $getbranchcode= $row["branchcode"] ;
          }
        }
      }

         
    // get a product from products table
   $paracountqry="SELECT count(*) as countval from tblsyncdetails where imeino=$1 and synctype='itemmaster';";
  $resultmax=  pg_prepare($con,"paracountqry",$paracountqry);
 $data = [$paraimeino];
 $res = pg_execute($con,"paracountqry",$data);
  if (!empty($res)) {
    if (pg_num_rows($res) > 0) {   
      while ($row = pg_fetch_assoc($res)) {
          $countval= $row["countval"] ;
        }
      }
    } 
    if ($countval > 0){
      // $paraquery="SELECT * from (
      //     select  *,'Insert' as  process,coalesce((select  currentprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),sellingprice) as currentprice,
      //     coalesce((select onlineprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as onlineprice,
      //     coalesce((select swiggy_price from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as swiggy_price,
      //     coalesce((select zomato_price from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as zomato_price,
      //     1 as complimentary_price 
      //     from tblitemmaster as a 
      //     where (select syncdate from tblsyncdetails where imeino=$1 and synctype='itemmaster' order by syncdate desc limit 1 ) <= createddate 
          
      //     union all  
          
      //     select distinct a.*,'Update' as  process,coalesce((select  currentprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),sellingprice) as currentprice,
      //     coalesce((select  onlineprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as onlineprice,
      //     coalesce((select swiggy_price from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as swiggy_price,
      //     coalesce((select zomato_price from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as zomato_price,
      //     1 as complimentary_price 
      //     from tblitemmaster as a   
      //     left join tblitempricelisttransaction as b on a.itemcode=b.itemcode  
      //     where (select syncdate from tblsyncdetails where imeino=$2 and synctype='itemmaster' order by syncdate desc limit 1 ) <=updateddate   or (select syncdate from tblsyncdetails where imeino=$2 and synctype='itemmaster' order by syncdate desc limit 1 ) <= b.createddate 
      //   ) as dev;";
      
      $paraquery = "WITH lastsync AS (
                        SELECT syncdate 
                        FROM tblsyncdetails 
                        WHERE imeino=$1
                          AND synctype='itemmaster'
                        ORDER BY syncdate DESC 
                        LIMIT 1
                    ),
                    latestprice AS (
                        SELECT DISTINCT ON (itemcode)
                              itemcode,
                              currentprice,
                              onlineprice,
                              swiggy_price,
                              zomato_price
                        FROM tblitempricelisttransaction
                        WHERE branchcode = $2
                        ORDER BY itemcode, autonum DESC
                    )

                    SELECT distinct * FROM (

                        -- INSERT LIST
                        SELECT 
                            a.*,
                            'Insert' AS process,
                            COALESCE(lp.currentprice, a.sellingprice) AS currentprice,
                            COALESCE(lp.onlineprice, 0) AS onlineprice,
                            COALESCE(lp.swiggy_price, 0) AS swiggy_price,
                            COALESCE(lp.zomato_price, 0) AS zomato_price,
                            1 AS complimentary_price
                        FROM tblitemmaster a
                        CROSS JOIN lastsync ls
                        LEFT JOIN latestprice lp ON lp.itemcode = a.itemcode
                        WHERE a.createddate >= ls.syncdate

                        UNION ALL

                        -- UPDATE LIST
                        SELECT 
                            a.*,
                            'Update' AS process,
                            COALESCE(lp.currentprice, a.sellingprice) AS currentprice,
                            COALESCE(lp.onlineprice, 0) AS onlineprice,
                            COALESCE(lp.swiggy_price, 0) AS swiggy_price,
                            COALESCE(lp.zomato_price, 0) AS zomato_price,
                            1 AS complimentary_price
                        FROM tblitemmaster a
                        CROSS JOIN lastsync ls
                        LEFT JOIN latestprice lp ON lp.itemcode = a.itemcode
                        LEFT JOIN tblitempricelisttransaction b ON a.itemcode=b.itemcode
                        WHERE a.updateddate >= ls.syncdate
                          OR b.createddate >= ls.syncdate

                    ) AS dev 
                    order by itemcode;";
      $resultmax=  pg_prepare($con,"paraquery",$paraquery);
      $data = [$paraimeino, $getbranchcode];
      $result = pg_execute($con,"paraquery",$data);
    }
    else{
      // $paraquery=" SELECT *,'Insert' as  process,coalesce((select  currentprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),sellingprice) as currentprice,
      //               coalesce((select  onlineprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as onlineprice 
      //               from tblitemmaster as a 
      //               where  statuscode='1'"; 
      $paraquery = "WITH latestprice AS (
                        SELECT DISTINCT ON (itemcode)
                              itemcode,
                              currentprice,
                              onlineprice,
                              swiggy_price,
                              zomato_price
                        FROM tblitempricelisttransaction
                        WHERE branchcode = $1
                        ORDER BY itemcode, autonum DESC
                    )
                    
                    SELECT distinct * FROM (

                        -- INSERT LIST
                        SELECT 
                            a.*,
                            'Insert' AS process,
                            COALESCE(lp.currentprice, a.sellingprice) AS currentprice,
                            COALESCE(lp.onlineprice, 0) AS onlineprice,
                            COALESCE(lp.swiggy_price, 0) AS swiggy_price,
                            COALESCE(lp.zomato_price, 0) AS zomato_price,
                            1 AS complimentary_price
                        FROM tblitemmaster a
                        LEFT JOIN latestprice lp ON lp.itemcode = a.itemcode
                        WHERE statuscode='1'
                        
                    ) as dev 
                    order by itemcode;";
      $resultmax=  pg_prepare($con,"paraquery",$paraquery);
      $data = [$getbranchcode];
      $result = pg_execute($con,"paraquery",$data);
    }  

    // $paraquery="SELECT *,'Insert' as  process,coalesce((select  currentprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),sellingprice) as currentprice,coalesce((select  onlineprice from tblitempricelisttransaction where branchcode ='$getbranchcode' and itemcode=a.itemcode  order by autonum desc limit 1 ),0) as onlineprice from tblitemmaster as a where  statuscode='1'  "; 
    //   $result = pg_query($con,$paraquery );
    if (!empty($result)) {
        // check for empty result
        if (pg_num_rows($result) > 0) {
      // user node
            $response["Value"] = array();
 
            while ($row = pg_fetch_assoc($result)) {        
              $report = array();
               
                $report["autonum"] = $row["autonum"];
                $report["itemcode"] = $row["itemcode"];
                $report["itemgroupcode"] = $row["itemgroupcode"];
                $report["itemname"] = $row["itemname"];
                $report["statuscode"] = $row["statuscode"];
                $report["sellingprice"] = $row["currentprice"];
                $report["onlineprice"] = $row["onlineprice"];
                $report["itemnameintamil"] = $row["itemnameintamil"]; 
                $report["makerid"] = $row["makerid"]; 
                $report["unitcode"] = $row["unitcode"]; 
                $report["createddate"] = $row["createddate"]; 
                $report["updateddate"] = $row["updateddate"]; 
                $report["process"] = $row["process"];
                $report["swiggy_price"] = $row["swiggy_price"];
                $report["zomato_price"] = $row["zomato_price"];
                $report["complimentary_price"] = $row["complimentary_price"];
           
              // 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();
}
?>
