<?php
/***************************************************************************
 *                                                                          *
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *                                                                          *
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program.                                                    *
 *                                                                          *
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.            *
 ****************************************************************************/
//$Id: PapInventoryModel.php, v 1.0 2013/03/14 14:14:14
class PapInventoryModel extends Model 
{
    public function getOrderList($languageIDs,$viewImageUrl,$slotDate,$slotIDLists,$floorIDLists,$orderStatus="",$productID=0,$productOptionID=0,$websiteID=0,$storeID=0,$paymentStatus="",$notTakeCancelOrder=""){
        
        $slotDate                   =               isset($slotDate) ? $slotDate : "";
        $slotIDLists                =               isset($slotIDLists) ? $slotIDLists : "";
        $floorIDLists               =               isset($floorIDLists) ? $floorIDLists : "";
        $orderStatus                =               isset($orderStatus) ? $orderStatus : "";
        $paymentStatus              =               isset($paymentStatus) ? $paymentStatus : "";
        
        $orderLists                 =               array();
        if($slotDate!="" && $slotIDLists!="" && $websiteID>0 && $storeID>0){
            
            $sqlSelect              =               "SELECT ordItem.productID as product_id,
                                                            ordItem.productOptionID as product_option_id,
                                                            CASE 
                                                                WHEN productDet.productTitle!=productOptDet.productOptionName
                                                                    THEN 
                                                                        CONCAT(productDet.productTitle,' - ',productOptDet.productOptionName,' ',productOptDet.packageContains) 
                                                                ELSE 
                                                                        CONCAT(productOptDet.productOptionName,' ',productOptDet.packageContains)
                                                            END AS product_name,
                                                            productOpt.optionSKU as product_sku,
                                                            productOpt.erpID as erpID,
                                                            measurement.class AS product_unit,
                                                            SUM(ordItem.orderItemQuantity) as ordered_quantity
                                                            FROM ".DB_PREFIX."order_items ordItem
                                                            INNER JOIN ".DB_PREFIX."order_masters ordMas ON
                                                                (ordMas.masterOrderID = ordItem.masterOrderID)
                                                            INNER JOIN ".DB_PREFIX."products product ON
                                                                (product.productID = ordItem.productID)
                                                            INNER JOIN ".DB_PREFIX."product_details productDet ON
                                                                (productDet.productID = product.productID
                                                                AND productDet.languageID = '".$languageIDs['Current']."')
                                                            INNER JOIN ".DB_PREFIX."product_options productOpt ON
                                                                (productOpt.productOptionID = ordItem.productOptionID)
                                                            INNER JOIN ".DB_PREFIX."product_option_details productOptDet ON
                                                                (productOptDet.productOptionID = productOpt.productOptionID
                                                                 AND productOptDet.languageID = '".$languageIDs['Current']."')
                                                            LEFT JOIN ".DB_PREFIX."measurement_class AS measurement ON 
                                                                (product.measurementWeight = measurement.classID) 
                                                            WHERE ordMas.deliveryDate = '".$this->escape($slotDate)."'
                                                            AND ordMas.deliveryTimeslot IN ('".implode("','",$this->escape(explode(",",$slotIDLists)))."')";
            if($paymentStatus!=""){
                $sqlSelect         .=                   " AND ordMas.paymentStatus IN ('".implode("','",$this->escape(explode(",",$paymentStatus)))."')";
            }
            if($orderStatus!=""){
                $sqlSelect         .=                   " AND ordMas.dynamicOrderStatus IN ('".implode("','",$this->escape(explode(",",$orderStatus)))."')";
            }
            if($productID>0){
                $sqlSelect         .=                   " AND ordItem.productID = '".$this->escape_int($productID)."'";
            }
            if($productOptionID>0){
                $sqlSelect         .=                   " AND ordItem.productOptionID = '".$this->escape_int($productOptionID)."'";
            }
            if($floorIDLists!=""){
                $sqlSelect         .=                   " AND product.floor_id IN ('".implode("','",$this->escape(explode(",",$floorIDLists)))."')";
            }
            if($websiteID>0){
                $sqlSelect         .=                   " AND ordMas.website_id = '".$this->escape_int($websiteID)."'";
            }
            if($storeID>0){
                $sqlSelect         .=                   " AND ordMas.store_id = '".$this->escape_int($storeID)."'";
            }
            if($notTakeCancelOrder=="Yes"){
                $sqlSelect         .=                   " AND ordMas.pap_order_status != 'pending_payment_cancellation'";
            }
            $sqlSelect             .=                   " GROUP BY ordItem.productOptionID
                                                          ORDER BY ordMas.orderCreatedDate ASC,ordItem.orderItemID ASC";
            //echo $sqlSelect; exit;
            
            $orderLists             =               $this->ObjDatabase->readValues($sqlSelect,MYSQLI_ASSOC);
        }
        return $orderLists;
    }
    
	public function saveConsolidatedOrders($orderList = array(),$storeID,$slotDate,$slotID,$adminUserID) {
        
		
        $sql            = " INSERT INTO " . DB_PREFIX . "pap_consolidation
					(
						timeSlotID,
						store_id,
						deliveryDate,
						consolidation_status,
						created_time,
						adminUserID
					)
					VALUES
					(
						'" . $this->escape_int($slotID) . "',
						'" . $this->escape_int($storeID) . "',
						'" . $this->escape($slotDate) . "',
						'in_progress',
						NOW(),
						'" . $this->escape($adminUserID) . "'
						
					)";
        $resInsertItem  = $this->ObjDatabase->setQuery($sql);
        $OrderConsolidationID   = $this->ObjDatabase->getInsertId();
        
        if (count($orderList) > 0) {
            foreach ($orderList as $key => $items) {
                        
                        $sql                        = " INSERT INTO " . DB_PREFIX . "pap_consolidated_items
									(
										consolidation_id,
										productID,
										productName,
										productOptionID,
										productOptionSKU,
										erpID,
										productUnit,
										order_quantity,
										available_quantity,
										confirmation_type,
										adminUserID,
										created_time,
										updated_time
									) 
									VALUES 
									(
										'" . $this->escape_int($OrderConsolidationID) . "',
										'" . $this->escape_int($items['product_id']) . "',
										'" . $this->escape($items['product_name']) . "',
										'" . $this->escape_int($items['product_option_id']) . "',
										'" . $this->escape($items['product_sku']) . "',
										'" . $this->escape($items['erpID']) . "',
										'" . $this->escape($items['product_unit']) . "',
										'" . $this->escape($items['ordered_quantity']) . "',
										0,
										'none',
										'" . $this->escape($adminUserID) . "',
										NOW(),
										NOW()
									)";
                        $resInsertItem              = $this->ObjDatabase->setQuery($sql);
            }
        }
        return $OrderConsolidationID; 
    }
    
    public function checkConsolidation($storeID,$slotDate,$slotID){
        $sqlSelect                  =" SELECT COUNT(consolidation_id) as cntOrder 
                                        FROM ".DB_PREFIX."pap_consolidation 
                                        WHERE deliveryDate = '".$this->escape($slotDate)."'
                                        AND store_id = '".$this->escape_int($storeID)."'
                                        AND timeSlotID = '".$this->escape_int($slotID)."'";
        //echo $sqlSelect; exit;
        return $this->ObjDatabase->getField($sqlSelect);
    }
	public function getConsolidatedOrderList($storeID,$slotDate,$slotID,$productID=0,$productOptionID=0,$floorIDLists=""){
        
        $slotDate                   =               isset($slotDate) ? $slotDate : "";
        $slotID                		=               isset($slotID) ? $slotID : "";
        
        $orderLists                 =               array();
        if($slotDate!="" && $slotID!="" && $storeID>0){
            
            $sqlSelect              =               "SELECT cosItms.productID as product_id,
                                                            cosItms.productOptionID as product_option_id,
                                                            cosItms.productName AS product_name,
                                                            cosItms.productOptionSKU as product_sku,
                                                            cosItms.erpID as erpID,
                                                            cosItms.productUnit AS product_unit,
                                                            cosItms.order_quantity AS ordered_quantity,
                                                            cosItms.available_quantity,
															CASE 
                                                                WHEN cosItms.confirmation_type='none' 
                                                                    THEN 
                                                                        'no' 
                                                                ELSE 
                                                                        'yes' 
                                                            END AS is_inventory_confirmed,
                                                            cosItms.confirmation_type AS inventory_confirmation_type,
															CASE 
                                                                WHEN cosItms.confirmation_type='none' 
                                                                    THEN 
                                                                        '' 
                                                                ELSE 
                                                                        CONCAT('Confirmed by ',admUsr.adminUserFirstName,' at ',DATE_FORMAT(cosItms.updated_time, '%l %p, %e %M %Y'))
                                                            END AS confirmation_details
                                                            FROM ".DB_PREFIX."pap_consolidation AS Cons
                                                            LEFT JOIN ".DB_PREFIX."pap_consolidated_items AS cosItms ON 
                                                                (Cons.consolidation_id = cosItms.consolidation_id) 
                                                            INNER JOIN ".DB_PREFIX."products product ON
                                                                (product.productID = cosItms.productID)
                                                            LEFT JOIN ".DB_PREFIX."admin_users admUsr ON 
                                                                (admUsr.adminUserID = cosItms.adminUserID) 
                                                            WHERE Cons.deliveryDate = '".$this->escape($slotDate)."'
                                                            AND Cons.timeSlotID = '".$this->escape_int($slotID)."' 
                                                            AND Cons.store_id = '".$this->escape_int($storeID)."' 
                                                            AND Cons.consolidation_status = 'in_progress' 
															";
            if($productID>0){
                $sqlSelect         .=                   " AND cosItms.productID = '".$this->escape_int($productID)."'";
            }
            if($productOptionID>0){
                $sqlSelect         .=                   " AND cosItms.productOptionID = '".$this->escape_int($productOptionID)."'";
            }
            if($floorIDLists!=""){
                $sqlSelect         .=                   " AND product.floor_id IN ('".implode("','",$this->escape(explode(",",$floorIDLists)))."')";
            }
            $sqlSelect             .=   " ORDER BY cosItms.consolidated_item_id ASC";
            //echo $sqlSelect; exit;
            
            $orderLists             =               $this->ObjDatabase->readValues($sqlSelect,MYSQLI_ASSOC);
        }
        return $orderLists;
    }
	public function saveInventoy($storeID,$slotDate,$slotID,$adminUserID,$productID,$productOptionID,$confirmation_type,$available_quantity,$replacement_1,$replacement_2) {
        
		$sqlSelect  = " SELECT cosItms.consolidated_item_id,cosItms.order_quantity
						FROM ".DB_PREFIX."pap_consolidation AS Cons
						LEFT JOIN ".DB_PREFIX."pap_consolidated_items AS cosItms ON 
							(Cons.consolidation_id = cosItms.consolidation_id) 
						WHERE Cons.deliveryDate = '".$this->escape($slotDate)."'
							AND Cons.timeSlotID = '".$this->escape_int($slotID)."' 
							AND Cons.store_id = '".$this->escape_int($storeID)."' 
							AND productID=" . $this->escape_int($productID) . "
							AND productOptionID=" . $this->escape_int($productOptionID) . "
						";

        $resCons =  $this->ObjDatabase->readValues($sqlSelect,MYSQLI_ASSOC);

		$consolidated_item_id = $resCons[0]['consolidated_item_id'];
		if($confirmation_type=='available'){
			$available_quantity = $resCons[0]['order_quantity'];
		}else if($confirmation_type=='none'){
			$available_quantity = 0;
		}else{
			$available_quantity = $available_quantity;
		}
		if($confirmation_type!='deducted_and_replaced'){
			$this->deleteConsReplacement($consolidated_item_id);
		}

		$sql    = " UPDATE 
						" . DB_PREFIX . "pap_consolidated_items 
					SET
						available_quantity='" . $this->escape_int($available_quantity) . "',
						confirmation_type='" . $this->escape($confirmation_type) . "',
						adminUserID='" . $this->escape_int($adminUserID) . "',
						updated_time=NOW()
					WHERE 
						productID=" . $this->escape_int($productID) . "
						AND productOptionID=" . $this->escape_int($productOptionID) . "
						AND consolidated_item_id=" . $this->escape_int($consolidated_item_id) . "
						";
            $resupdateConsldItems = $this->ObjDatabase->setQuery($sql);
        if ($resupdateConsldItems) {
			if($confirmation_type=='deducted_and_replaced'){
				if($replacement_1){
					$this->insertConsReplacement($replacement_1,$consolidated_item_id);
				}
				if($replacement_2){
					$this->insertConsReplacement($replacement_2,$consolidated_item_id);
				}
			}
		return 1; 
		}
		else{
			return 0;
		}
        
    }
	public function deleteConsReplacement($consolidated_item_id = '') {
		$sql= " DELETE FROM  
						" . DB_PREFIX . "pap_consolidated_item_replacement 
				WHERE 
					consolidated_item_id='" . $this->escape_int($consolidated_item_id) . "'";
        $rsDeleteConsReplacement = $this->ObjDatabase->setQuery($sql);
        //return true;
	}
	public function insertConsReplacement($replacement=array(),$consolidated_item_id) {

		 $sql    = " INSERT INTO " . DB_PREFIX . "pap_consolidated_item_replacement
					(
						consolidated_item_id,
						productID,
						productOptionID,
						available_quantity,
						original_item_base_unit,
						replacement_item_base_unit
					) 
					VALUES 
					(
						'" . $this->escape_int($consolidated_item_id) . "',
						'" . $this->escape_int($replacement['product_id']) . "',
						'" . $this->escape($replacement['product_option_id']) . "',
						'" . $this->escape_int($replacement['available_quantity']) . "',
						'" . $this->escape($replacement['original_item_base_unit']) . "',
						'" . $this->escape($replacement['replacement_item_base_unit']) . "'
					)";
        $resInsertItem              = $this->ObjDatabase->setQuery($sql);	
	}
	public function searchProducts($searchKeyword='',$languageIDs,$storeID,$limitFrom,$limitTo){
		
		$limitFrom		= ($limitFrom >0) ? $limitFrom : 0;
		$limitTo		= ($limitTo >0) ? $limitTo : 50;
		$searchKeyword	= $this->escape($searchKeyword);
		$now			=	date('Y-m-d');
		$resProducts    =  array();
        if($searchKeyword!="" && $storeID>0){
		$sql	= " SELECT 
					product.productID as product_id,
                    productOpt.productOptionID as product_option_id,
					CASE 
						WHEN productDet.productTitle!=productOptDet.productOptionName
							THEN 
								CONCAT(productDet.productTitle,' - ',productOptDet.productOptionName,' ',productOptDet.packageContains) 
						ELSE 
								CONCAT(productOptDet.productOptionName,' ',productOptDet.packageContains)
					END AS product_name,
					productOpt.optionSKU as product_sku,
					productOpt.erpID as erpID,
					measurement.class AS product_unit
				FROM 
					" . DB_PREFIX . "products   				AS product 
				LEFT JOIN
					" . DB_PREFIX . "product_details 			AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " .(int) $languageIDs['Current'] . "
						)
				
				LEFT JOIN 
					".DB_PREFIX."product_options AS productOpt 
						ON (
							product.productID = productOpt.productID
							AND
							productOpt.productOptionStatus = 'Active'
						)	
				LEFT JOIN 
					" . DB_PREFIX . "product_option_details AS productOptDet 
						ON (
							productOpt.productOptionID = productOptDet.productOptionID 
							AND 
							productOptDet.languageID = " .(int) $languageIDs['Current'] . "
						)
				LEFT JOIN  
					" . DB_PREFIX . "product_to_website_store AS prodWebStore
						ON (
							prodWebStore.productOptionID = productOpt.productOptionID  
						)
				LEFT JOIN 
					".DB_PREFIX."measurement_class AS measurement 
						ON (product.measurementWeight = measurement.classID)
				WHERE
						product.productStatus = 'Active' 
					AND (
						product.productStartTime <= NOW() 
						AND
						(
							product.productEndTime = '0000-00-00'
							OR
							product.productEndTime >=	'".$now."'
						)
					) ";
			if($storeID>0){
                $sql         .=                   " AND prodWebStore.store_id = '".$this->escape_int($storeID)."'";
            }
			$sql 	.= " 
				AND (
					productDet.productTitle LIKE '%".$searchKeyword."%' COLLATE utf8_general_ci
					OR productOptDet.productOptionName LIKE '%".$searchKeyword."%' COLLATE utf8_general_ci
					OR productOpt.erpID LIKE '%".$searchKeyword."%' COLLATE utf8_general_ci
				) 
				
			GROUP BY 
				productOpt.productOptionID 
			ORDER BY 
				product.productID ASC ";
	
		if ($limitTo >= '0')
		{
			$sql 	.= " LIMIT  " . $this->escape_int($limitFrom) . "," . $this->escape_int($limitTo) . " ";
		}
		//echo $sql; exit;
			$resProducts = $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
		}
		
		return $resProducts;
	}
	public function checkOrderWindow($params){ 
		extract($params);
		$sqlmain = "SELECT 
					TIMESTAMPDIFF(
					SECOND,
					CAST('".date('Y-m-d H:i:s')."' AS DATETIME), 
					CAST(DATE_SUB(STR_TO_DATE(CONCAT('".$slotDate."',' ',TS.startTime), '%Y-%m-%d %l:%i %p' ), INTERVAL CAST(tod.hoursPriorOrder AS DECIMAL(10,2)) * CAST(60 AS DECIMAL(10,4)) MINUTE) AS DATETIME)
					) AS remainingTime
					FROM `dp_timeslot_override_days` tod LEFT JOIN dp_timeslots TS ON ( TS.timeSlotID = tod.timeSlotID ) WHERE tod.`day` = '".$day."' AND TS.status = 'A' AND tod.subsiteID = '".$storeID."'  AND tod.timeSlotID = '".$slotID."'";

	$result = $this->ObjDatabase->readValue($sqlmain);
	return $result;
	}
        
    public function unassignPreviousRacks(){
        $currDate           =           date("Y-m-d");
        $sqlUpdate          =           "UPDATE ".DB_PREFIX."pap_rack SET masterOrderID='0',status='free' WHERE status='occupied' AND DATE(updated_time)<'".$currDate."'";
        $this->ObjDatabase->setQuery($sqlUpdate);
    }
    public function checkOrderWindowNew($params){ 
        extract($params);
        $sqlmain = "SELECT 
                    TIMESTAMPDIFF(
                    SECOND,
                    CAST('".date('Y-m-d H:i:s')."' AS DATETIME), 
                    CAST(DATE_SUB(STR_TO_DATE(CONCAT('".$slotDate."',' ',TS.startTime), '%Y-%m-%d %l:%i %p' ), INTERVAL CAST(tod.hoursPriorOrder AS DECIMAL(10,2)) * CAST(60 AS DECIMAL(10,4)) MINUTE) AS DATETIME)
                    ) AS remainingTime
                    FROM `dp_timeslot_override_days` tod LEFT JOIN dp_timeslots TS ON ( TS.timeSlotID = tod.timeSlotID ) WHERE tod.`day` = '".$day."' AND TS.status = 'A' AND tod.subsiteID = '".$storeID."' ";
        if(!empty($warehouseslot)){
            $sqlmain .= "    AND ( ";
            $warehousequery = '';
            foreach($warehouseslot as $key => $val) {
                if($warehousequery == '')
                    $warehousequery .= " ( tod.warehouseID = ".(int)$val['warehouse_id']." AND tod.timeSlotID = ".(int)$val['slot_id']." ) ";
                else
                    $warehousequery .= "OR ( tod.warehouseID = ".(int)$val['warehouse_id']."  AND tod.timeSlotID = ".(int)$val['slot_id']." ) ";
            }
            $sqlmain .= $warehousequery;
            $sqlmain .= "    ) ";
        }
        // echo $sqlmain;exit();
        $result = $this->ObjDatabase->readValue($sqlmain);
        return $result;
    }
    public function checkConsolidationNew($storeID,$slotDate,$warehouseslot){
        $sqlSelect                  =" SELECT COUNT(consolidation_id) as cntOrder 
                                        FROM ".DB_PREFIX."pap_consolidation 
                                        WHERE deliveryDate = '".$this->escape($slotDate)."'
                                        AND store_id = '".$this->escape_int($storeID)."' ";
        if(!empty($warehouseslot)){
            $sqlSelect .= "    AND ( ";
            $warehousequery = '';
            foreach($warehouseslot as $key => $val) {
			  if($key>0)
					continue;
                if($warehousequery == '')
                    $warehousequery .= " ( warehouseID = ".(int)$val['warehouse_id']." AND timeSlotID = ".(int)$val['slot_id']." ) ";
                else
                    $warehousequery .= "OR ( warehouseID = ".(int)$val['warehouse_id']."  AND timeSlotID = ".(int)$val['slot_id']." ) ";
            }
            $sqlSelect .= $warehousequery;
            $sqlSelect .= "    ) ";
        }
        // echo $sqlSelect; exit;
        return $this->ObjDatabase->getField($sqlSelect);
    }
    public function getOrderListNew($languageIDs,$viewImageUrl,$slotDate,$warehouseslot,$floorIDLists,$orderStatus="",$productID=0,$productOptionID=0,$websiteID=0,$storeID=0,$paymentStatus="",$notTakeCancelOrder=""){
        
        $slotDate                   = isset($slotDate) ? $slotDate : "";
        $floorIDLists               = isset($floorIDLists) ? $floorIDLists : "";
        $orderStatus                = isset($orderStatus) ? $orderStatus : "";
        $paymentStatus              = isset($paymentStatus) ? $paymentStatus : "";
        $orderLists                 = array();
        if($slotDate!="" && !empty($warehouseslot) && $websiteID>0 && $storeID>0){
            
            $sqlSelect              =               "SELECT ordItem.productID as product_id,
                                                            ordItem.productOptionID as product_option_id,
                                                            CASE 
                                                                WHEN productDet.productTitle!=productOptDet.productOptionName
                                                                    THEN 
                                                                        CONCAT(productDet.productTitle,' - ',productOptDet.productOptionName,' ',productOptDet.packageContains) 
                                                                ELSE 
                                                                        CONCAT(productOptDet.productOptionName,' ',productOptDet.packageContains)
                                                            END AS product_name,
                                                            productOpt.optionSKU as product_sku,
                                                            productOpt.erpID as erpID,
                                                            measurement.class AS product_unit,
                                                            SUM(ordItem.orderItemQuantity) as ordered_quantity
                                                            FROM ".DB_PREFIX."order_items ordItem
                                                            INNER JOIN ".DB_PREFIX."order_masters ordMas ON
                                                                (ordMas.masterOrderID = ordItem.masterOrderID)
                                                            INNER JOIN ".DB_PREFIX."products product ON
                                                                (product.productID = ordItem.productID)
                                                            INNER JOIN ".DB_PREFIX."product_details productDet ON
                                                                (productDet.productID = product.productID
                                                                AND productDet.languageID = '".$languageIDs['Current']."')
                                                            INNER JOIN ".DB_PREFIX."product_options productOpt ON
                                                                (productOpt.productOptionID = ordItem.productOptionID)
                                                            INNER JOIN ".DB_PREFIX."product_option_details productOptDet ON
                                                                (productOptDet.productOptionID = productOpt.productOptionID
                                                                 AND productOptDet.languageID = '".$languageIDs['Current']."')
                                                            LEFT JOIN ".DB_PREFIX."measurement_class AS measurement ON 
                                                                (product.measurementWeight = measurement.classID) 
                                                            WHERE ordMas.deliveryDate = '".$this->escape($slotDate)."' ";
            if(!empty($warehouseslot)){
                $sqlSelect .= "    AND ( ";
                $warehousequery = '';
                foreach($warehouseslot as $key => $val) {
                    if($warehousequery == '')
                        $warehousequery .= " ( ordMas.warehouseID = ".(int)$val['warehouse_id']." AND ordMas.deliveryTimeslot = ".(int)$val['slot_id']." ) ";
                    else
                        $warehousequery .= "OR ( ordMas.warehouseID = ".(int)$val['warehouse_id']."  AND ordMas.deliveryTimeslot = ".(int)$val['slot_id']." ) ";
                }
                $sqlSelect .= $warehousequery;
                $sqlSelect .= "    ) ";
            }
            if($paymentStatus!=""){
                $sqlSelect         .=                   " AND ordMas.paymentStatus IN ('".implode("','",$this->escape(explode(",",$paymentStatus)))."')";
            }
            if($orderStatus!=""){
                $sqlSelect         .=                   " AND ordMas.dynamicOrderStatus IN ('".implode("','",$this->escape(explode(",",$orderStatus)))."')";
            }
            if($productID>0){
                $sqlSelect         .=                   " AND ordItem.productID = '".$this->escape_int($productID)."'";
            }
            if($productOptionID>0){
                $sqlSelect         .=                   " AND ordItem.productOptionID = '".$this->escape_int($productOptionID)."'";
            }
            if($floorIDLists!=""){
                $sqlSelect         .=                   " AND product.floor_id IN ('".implode("','",$this->escape(explode(",",$floorIDLists)))."')";
            }
            if($websiteID>0){
                $sqlSelect         .=                   " AND ordMas.website_id = '".$this->escape_int($websiteID)."'";
            }
            if($storeID>0){
                $sqlSelect         .=                   " AND ordMas.store_id = '".$this->escape_int($storeID)."'";
            }
            if($notTakeCancelOrder=="Yes"){
                $sqlSelect         .=                   " AND ordMas.pap_order_status != 'pending_payment_cancellation'";
            }
            $sqlSelect             .=                   " GROUP BY ordItem.productOptionID
                                                          ORDER BY ordMas.orderCreatedDate ASC,ordItem.orderItemID ASC";
            //echo $sqlSelect; exit;
            
            $orderLists             =               $this->ObjDatabase->readValues($sqlSelect,MYSQLI_ASSOC);
        }
        return $orderLists;
    }
    public function saveConsolidatedOrdersNew($orderList = array(),$storeID,$slotDate,$warehouseslot,$adminUserID) {
        if(!empty($warehouseslot)){
            foreach($warehouseslot as $key => $val) {
			  if($key>0)
					continue;
                $sql = " INSERT INTO " . DB_PREFIX . "pap_consolidation
                    (  timeSlotID,
                        warehouseID,
                        store_id,
                        deliveryDate,
                        consolidation_status,
                        created_time,
                        adminUserID
                    )
                    VALUES
                    (
                        '" . $this->escape_int($val['slot_id']) . "',
                        '" . $this->escape_int($val['warehouse_id']) . "',
                        '" . $this->escape_int($storeID) . "',
                        '" . $this->escape($slotDate) . "',
                        'in_progress',
                        NOW(),
                        '" . $this->escape($adminUserID) . "'
                        
                    )";
                // echo $sql;exit();
                $resInsertItem          = $this->ObjDatabase->setQuery($sql);
                $OrderConsolidationID   = $this->ObjDatabase->getInsertId();
                if (count($orderList) > 0) {
                    foreach ($orderList as $key => $items) {      
                        $sql                        = " INSERT INTO " . DB_PREFIX . "pap_consolidated_items
                                    (
                                        consolidation_id,
                                        productID,
                                        productName,
                                        productOptionID,
                                        productOptionSKU,
                                        erpID,
                                        productUnit,
                                        order_quantity,
                                        available_quantity,
                                        confirmation_type,
                                        adminUserID,
                                        created_time,
                                        updated_time
                                    ) 
                                    VALUES 
                                    (
                                        '" . $this->escape_int($OrderConsolidationID) . "',
                                        '" . $this->escape_int($items['product_id']) . "',
                                        '" . $this->escape($items['product_name']) . "',
                                        '" . $this->escape_int($items['product_option_id']) . "',
                                        '" . $this->escape($items['product_sku']) . "',
                                        '" . $this->escape($items['erpID']) . "',
                                        '" . $this->escape($items['product_unit']) . "',
                                        '" . $this->escape($items['ordered_quantity']) . "',
                                        0,
                                        'none',
                                        '" . $this->escape($adminUserID) . "',
                                        NOW(),
                                        NOW()
                                    )";
                        $resInsertItem              = $this->ObjDatabase->setQuery($sql);
                    }
                }
            }
        }
        return $OrderConsolidationID; 
    }
    public function getConsolidatedOrderListNew($storeID,$slotDate,$warehouseslot,$productID=0,$productOptionID=0,$floorIDLists=""){
        
        $slotDate     =               isset($slotDate) ? $slotDate : "";
        $orderLists   =               array();
        if($slotDate!="" && !empty($warehouseslot) && $storeID>0){
            
            $sqlSelect              =               "SELECT cosItms.productID as product_id,
                                                            cosItms.productOptionID as product_option_id,
                                                            cosItms.productName AS product_name,
                                                            cosItms.productOptionSKU as product_sku,
                                                            cosItms.erpID as erpID,
                                                            cosItms.productUnit AS product_unit,
                                                            cosItms.order_quantity AS ordered_quantity,
                                                            cosItms.available_quantity,
                                                            CASE 
                                                                WHEN cosItms.confirmation_type='none' 
                                                                    THEN 
                                                                        'no' 
                                                                ELSE 
                                                                        'yes' 
                                                            END AS is_inventory_confirmed,
                                                            cosItms.confirmation_type AS inventory_confirmation_type,
                                                            CASE 
                                                                WHEN cosItms.confirmation_type='none' 
                                                                    THEN 
                                                                        '' 
                                                                ELSE 
                                                                        CONCAT('Confirmed by ',admUsr.adminUserFirstName,' at ',DATE_FORMAT(cosItms.updated_time, '%l %p, %e %M %Y'))
                                                            END AS confirmation_details
                                                            FROM ".DB_PREFIX."pap_consolidation AS Cons
                                                            LEFT JOIN ".DB_PREFIX."pap_consolidated_items AS cosItms ON 
                                                                (Cons.consolidation_id = cosItms.consolidation_id) 
                                                            INNER JOIN ".DB_PREFIX."products product ON
                                                                (product.productID = cosItms.productID)
                                                            LEFT JOIN ".DB_PREFIX."admin_users admUsr ON 
                                                                (admUsr.adminUserID = cosItms.adminUserID) 
                                                            WHERE Cons.deliveryDate = '".$this->escape($slotDate)."'
                                                            AND Cons.store_id = '".$this->escape_int($storeID)."' 
                                                            AND Cons.consolidation_status = 'in_progress' 
                                                            ";
            if(!empty($warehouseslot)){
                $sqlSelect .= "    AND ( ";
                $warehousequery = '';
                foreach($warehouseslot as $key => $val) {
			   if($key>0)
						continue;
                    if($warehousequery == '')
                        $warehousequery .= " ( Cons.warehouseID = ".(int)$val['warehouse_id']." AND Cons.timeSlotID = ".(int)$val['slot_id']." ) ";
                    else
                        $warehousequery .= "OR ( Cons.warehouseID = ".(int)$val['warehouse_id']."  AND Cons.timeSlotID = ".(int)$val['slot_id']." ) ";
                }
                $sqlSelect .= $warehousequery;
                $sqlSelect .= "    ) ";
            }
            if($productID>0){
                $sqlSelect         .=                   " AND cosItms.productID = '".$this->escape_int($productID)."'";
            }
            if($productOptionID>0){
                $sqlSelect         .=                   " AND cosItms.productOptionID = '".$this->escape_int($productOptionID)."'";
            }
            if($floorIDLists!=""){
                $sqlSelect         .=                   " AND product.floor_id IN ('".implode("','",$this->escape(explode(",",$floorIDLists)))."')";
            }
            $sqlSelect             .=   " ORDER BY cosItms.consolidated_item_id ASC";
            //echo $sqlSelect; exit;
            
            $orderLists             =               $this->ObjDatabase->readValues($sqlSelect,MYSQLI_ASSOC);
        }
        return $orderLists;
    }
    public function saveInventoyNew($storeID,$slotDate,$warehouseslot,$adminUserID,$productID,$productOptionID,$confirmation_type,$available_quantity,$replacement_1,$replacement_2) {
        
        $sqlSelect  = " SELECT cosItms.consolidated_item_id,cosItms.order_quantity
                        FROM ".DB_PREFIX."pap_consolidation AS Cons
                        LEFT JOIN ".DB_PREFIX."pap_consolidated_items AS cosItms ON 
                            (Cons.consolidation_id = cosItms.consolidation_id) 
                        WHERE Cons.deliveryDate = '".$this->escape($slotDate)."' 
                            AND Cons.store_id = '".$this->escape_int($storeID)."' 
                            AND productID=" . $this->escape_int($productID) . "
                            AND productOptionID=" . $this->escape_int($productOptionID) . "";
        if(!empty($warehouseslot))
        {
          $sqlSelect .= "    AND ( ";
          $warehousequery = '';
          foreach($warehouseslot as $key => $val)
              {
                  if($warehousequery == '')
                    $warehousequery .= " ( Cons.warehouseID = ".(int)$val['warehouse_id']." AND Cons.timeSlotID = ".(int)$val['slot_id']." ) ";
                else
                    $warehousequery .= "OR ( Cons.warehouseID = ".(int)$val['warehouse_id']."  AND Cons.timeSlotID = ".(int)$val['slot_id']." ) ";
              }
          $sqlSelect .= $warehousequery;
          $sqlSelect .= "    ) ";
        }
        $resCons =  $this->ObjDatabase->readValues($sqlSelect,MYSQLI_ASSOC);

        $consolidated_item_id = $resCons[0]['consolidated_item_id'];
        if($confirmation_type=='available'){
            $available_quantity = $resCons[0]['order_quantity'];
        }else if($confirmation_type=='none'){
            $available_quantity = 0;
        }else{
            $available_quantity = $available_quantity;
        }
        if($confirmation_type!='deducted_and_replaced'){
            $this->deleteConsReplacement($consolidated_item_id);
        }

        $sql    = " UPDATE 
                        " . DB_PREFIX . "pap_consolidated_items 
                    SET
                        available_quantity='" . $this->escape_int($available_quantity) . "',
                        confirmation_type='" . $this->escape($confirmation_type) . "',
                        adminUserID='" . $this->escape_int($adminUserID) . "',
                        updated_time=NOW()
                    WHERE 
                        productID=" . $this->escape_int($productID) . "
                        AND productOptionID=" . $this->escape_int($productOptionID) . "
                        AND consolidated_item_id=" . $this->escape_int($consolidated_item_id) . "
                        ";
            $resupdateConsldItems = $this->ObjDatabase->setQuery($sql);
        if ($resupdateConsldItems) {
            if($confirmation_type=='deducted_and_replaced'){
                if($replacement_1){
                    $this->insertConsReplacement($replacement_1,$consolidated_item_id);
                }
                if($replacement_2){
                    $this->insertConsReplacement($replacement_2,$consolidated_item_id);
                }
            }
        return 1; 
        }
        else{
            return 0;
        }
        
    }
}