<?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: OrderModel.php, v 1.0 2011/06/14 20:52:11
// 
class OrderModel extends Model {
    var $orderID = "";
    var $userID = "", $orderNumber = 0;
    var $messages, $errors;
    var $itemsCount = 0;
    var $productID = 0;
    var $subtotalAmount = 0;
    var $totalAmount = 0;
    var $promoCode = "";
    var $promoDiscountAmount = 0;
    var $promoDiscountValue = 0;
    var $promoDiscountType = "";
    var $taxCountryId = 0;
    var $taxStateId = 0;
    var $taxAmount = 0;
    var $taxRates = array("0" => array("multiplier" => 1, "tax_rate" => 0, "taxDescription" => ""));
    var $orderCharityID = 0;
    var $charityAmount = 0;
    //payment
    var $paymentIsRealtime = "";
    var $paymentMethodId = "";
    var $paymentMethodName = "";
    var $paymentGatewayId = "";
    var $userCreditAmount = "";
    var $orderCreditAmount = "";
    //shipping
    var $shippingCountryId = "";
    var $grossShippingRate = 0;
    var $languageIDs;
    function __construct($languageIDs = array()) {
        parent::__construct();
        if (!empty($languageIDs)) {
            $this->languageIDs = $languageIDs;
        }
    }
    //FOR SET ORDERID AND USERID FROM CONTROLLER
    public function setOrder($orderID, $userID = "", $userCreditAmount = "", $promoCode = "") {
        $this->orderID          = $this->orderID > 0 ? $this->orderID : $orderID;
        $this->userID           = $this->userID > 0 ? $this->userID : $userID;
        $this->userCreditAmount = $userCreditAmount;
        $this->promoCode        = $this->promoCode > 0 ? $this->promoCode : $promoCode;
        return $this->orderID;
    }
    //SET ORDER NUMBER WHEN COMPLETE THE ORDER
    public function setOrderNumber() {
        $sql            = " SELECT 
						MAX(CAST(orderNumber AS UNSIGNED))+1 
					FROM 
						" . DB_PREFIX . "order_masters";
        $resOrderNumber = $this->ObjDatabase->readValue($sql);
        $orderNumber    = ($resOrderNumber[0] > 0) ? $resOrderNumber[0] : 1;
        return $orderNumber;
    }
    //GET THE SETTINGS VALUE
    public function getSettingsValue($settingsKey) {
        $sql              = " SELECT 
						settingsValue 
					FROM 
						" . DB_PREFIX . "settings  
					WHERE 
						settingsKey = '" . $settingsKey . "' ";
        $resSettingsValue = $this->ObjDatabase->readValue($sql);
        return $resSettingsValue['settingsValue'];
    }
    //GET ORDER ITEMS BY ORDERID
    public function getOrderItemsByOrderID($orderID = '', $orderGiveAsGift = '', $productID = '', $productOptionID = '', $allRecords = '', $orderItemID = '') {
        $sql = "SELECT 
						* 
					FROM 
						" . DB_PREFIX . "order_items 
					WHERE  ";
        if ($orderItemID > 0) {
            $sql .= " orderItemID= '" . (int) $orderItemID . "' ";
        }
        if ($orderID > 0) {
            $sql .= " masterOrderID= '" . (int) $orderID . "' ";
        }
        if ($orderGiveAsGift != '') {
            $sql .= " AND orderGiveAsGift = '$orderGiveAsGift' ";
        }
        if ($productID != '') {
            $sql .= " AND productID  =	'" . (int) $productID . "' ";
        }
        if ($productOptionID != '') {
            $sql .= " AND productOptionID =	'" . (int) $productOptionID . "' ";
        }
        if ($allRecords == '') {
            $sql .= " GROUP BY 
							masterOrderID";
            $resSelectExistingItems = $this->ObjDatabase->readValue($sql);
        } else {
            $resSelectExistingItems = $this->ObjDatabase->readValues($sql);
        }
        return $resSelectExistingItems;
    }
    //GET ORDER DEAL DETAILS
    public function getOrderedProductDetails($productID, $productOptionID = '', $defaultLanguageID = '') {
        $sql = " SELECT
						product.*,
						productDet.*,
						productOpt.productOptionID,
						productOpt.productID,
						productOpt.productOptionRegularPrice,
						productOpt.productOptionProductPrice,
						COALESCE(productOpt.productOptionQuantity,0) AS productOptionQuantity,
						productOpt.productQuantityUnlimited,
						productOpt.productOptionStatus,
						productOpt.productOptionDefault,
						productOptDet.*
		 			FROM 
		 				" . DB_PREFIX . "products AS product
					LEFT JOIN 
						" . DB_PREFIX . "product_details AS productDet 
						ON 
						(product.productID = productDet.productID)
					LEFT JOIN 
						" . DB_PREFIX . "product_options AS productOpt 
						ON 
						(productOpt.productID = productDet.productID)
					LEFT JOIN 
						" . DB_PREFIX . "product_option_details AS productOptDet 
						ON
						(productOpt.productOptionID=productOptDet.productOptionID AND productOptDet.languageID=productDet.languageID )
					WHERE 
						product.productID = '" . (int) $productID . "' ";
        if ($defaultLanguageID != '') {
            $sql .= " AND productDet.languageID 	= 	'" . $defaultLanguageID . "' ";
        }
        if ($productOptionID != '') {
            $sql .= " AND productOpt.productOptionID 	= 	'" . (int) $productOptionID . "'";
        }
        $resSelectProductDetails = $this->ObjDatabase->readValue($sql);
        return $resSelectProductDetails;
    }
    // GET ORDER ITEMS
    public function getOrderedItems($languageIDs, $orderID = '', $orderItemID = '', $defaultLanguageID = '', $languageIDsOthers = '', $masterOrderID = '') {
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        $sql               = " SELECT 
						* 
					FROM 
						" . DB_PREFIX . "order_items AS ordIte ";
        if (isset($masterOrderID)) {
            $sql .= " LEFT JOIN 
							" . DB_PREFIX . "order_masters ordMas 
							ON 
							(ordMas.masterOrderID=ordIte.masterOrderID)";
        }
        $sql .= " LEFT JOIN 
						" . DB_PREFIX . "products AS product 
						ON 
						(ordIte.productID=product.productID)
					LEFT JOIN 
						" . DB_PREFIX . "product_details AS productDet 
						ON 
						(product.productID=productDet.productID)
					LEFT JOIN 
						" . DB_PREFIX . "product_options  AS productOpt 
						ON 
						(ordIte.productOptionID = productOpt.productOptionID)
					LEFT JOIN 
						" . DB_PREFIX . "product_option_details AS productOptDet 
						ON 
						(productOpt.productOptionID=productOptDet.productOptionID 
						AND productOptDet.languageID = '" . $currentLanguageID . "')
					WHERE 
						1  ";
        if (isset($masterOrderID)) {
            $sql .= " AND ordMas.masterOrderID= '" . (int) $masterOrderID . "'";
        }
        if ($orderItemID != '') {
            $sql .= " AND ordIte.orderItemID ='" . (int) $orderItemID . "' ";
        }
        if ($currentLanguageID != '') {
            $sql .= " AND productDet.languageID 	= 	'" . $currentLanguageID . "' ";
        }
        $sql .= " GROUP BY
						 ordIte.orderItemID";
        if ($orderItemID != '') {
            $resSelectOrderedItems = $this->ObjDatabase->readValue($sql);
        } else {
            $resSelectOrderedItems = $this->ObjDatabase->readValues($sql);
        }
        return $resSelectOrderedItems;
    }
    public function getOrderItemDetails($orderItemID = '', $orderID = '') {
        $sql = " SELECT 
						ordIte.*,
						ordMas.*
					FROM 
						" . DB_PREFIX . "order_items ordIte
					LEFT JOIN 
						" . DB_PREFIX . "order_masters ordMas
						ON ordMas.masterOrderID=ordIte.masterOrderID 
					LEFT JOIN 
						" . DB_PREFIX . "products product
						ON product.productID=ordIte.productID
					WHERE 
						1";
        if ($orderItemID > 0) {
            $sql .= " AND ordIte.orderItemID='" . (int) $orderItemID . "' ";
        }
        if ($orderID > 0) {
            $sql .= " AND ordMas.masterOrderID='" . (int) $orderID . "' ";
        }
        $resSelectOrderItemDetails = $this->ObjDatabase->readValue($sql);
        return $resSelectOrderItemDetails;
    }
    public function getOrderedUserDetails($userID, $orderID = '') {
        $sql            = " SELECT  
						* 
					FROM 
						" . DB_PREFIX . "users 
					WHERE 
						userID ='" . $userID . "'";
        $resUserDetails = $this->ObjDatabase->readValue($sql);
        if ($orderID != '') {
            $userFullName = $resUserDetails['userFirstName'] . " " . $resUserDetails['userLastName'];
        }
        return $resUserDetails;
    }
    //UPDATE DEAL TIPPED DETAILS SHOWN IN HOME PAGE
    public function savaProductTippedDetails($productID) {
        $sql                       = " SELECT 
						product.productMinQntyReqToTipp,
						product.productTippedDate,
						SUM(soldQuantity) AS productOrdersCount 
					FROM 
						" . DB_PREFIX . "products AS product
					LEFT JOIN 
						" . DB_PREFIX . "product_options AS productOpt
						ON product.productID = productOpt.productID
					WHERE 
						product.productID='" . (int) $productID . "'";
        $resSelectMinQntyReqToTipp = $this->ObjDatabase->readValue($sql);
        if ($resSelectMinQntyReqToTipp['productOrdersCount'] >= $resSelectMinQntyReqToTipp['productMinQntyReqToTipp']) {
            if ($resSelectMinQntyReqToTipp['productTippedDate'] == '' || $resSelectMinQntyReqToTipp['productTippedDate'] == '0000-00-00 00:00:00') {
                $sql                 = " UPDATE 
								" . DB_PREFIX . "products AS product 
							SET 
								product.productTippedDate =NOW() 
							WHERE 
								product.productID = '" . (int) $productID . "'";
                $resUpdateTippedDate = $this->ObjDatabase->setQuery($sql);
            }
        }
        return $resUpdateTippedDate;
    }
    //GET ORDER DETAILS
    public function getOrderDetails($orderID = '', $stateDetails = '', $languageIDs = '', $masterOrderID = '',$businessID=0,$returnDetails="") {
        /* if($returnDetails=="Yes"){
            $sqlRMAAppend =   ",rmaReturnProducts.reasonID,rmaReturns.actionID";
            $sqlRMAJoin   =   " LEFT JOIN " . DB_PREFIX . "rma_returns rmaReturns ON (rmaReturns.masterOrderID = ordMas.masterOrderID)
                                LEFT JOIN " . DB_PREFIX . "rma_return_products rmaReturnProducts ON 
                                (rmaReturnProducts.returnID = rmaReturns.returnID)";
        }else{
            $sqlRMAAppend =   "";
            $sqlRMAJoin   =   "";
        } */
		$sqlRMAAppend =   "";
		$sqlRMAJoin   =   "";
        if ($stateDetails == '') {
            if ($masterOrderID > 0) {
                $sql = " SELECT 
								ordMas.*,
								ordMas.orderType,
								SUM(ordMas.shippingAmount) AS shippingAmount,
								SUM(ordMas.orderOtherServiceCharge) AS orderOtherServiceCharge,
								SUM(ordMas.orderTaxAmount) AS orderTaxAmount,
								IF(ordMas.userID > 0 ,CONCAT(usr.userFirstName,' ',usr.userLastName),CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,
								IF(ordMas.userID > 0, usr.userEmail,ordMas.customer_email) AS customerEmail
                                                                $sqlRMAAppend
							FROM 
								" . DB_PREFIX . "order_masters ordMas
								LEFT JOIN 
						" . DB_PREFIX . "users usr 
						ON usr.userID=ordMas.userID
                                                $sqlRMAJoin
							WHERE 
								ordMas.masterOrderID ='" . $this->escape($masterOrderID) . "'";
        if ($businessID >0) {
            $sql .= " AND ordMas.businessID='" . $businessID . "' ";
        }								
								
            } else {
                $sql = " SELECT 
		   						ordMas.* 
		   					FROM 
		   						" . DB_PREFIX . "order_masters ordMas 
							WHERE 
								ordMas.masterOrderID ='" . $this->escape($orderID) . "'";
        if ($businessID >0) {
            $sql .= " AND ordMas.businessID='" . $businessID . "' ";
        }								
            }
        } else {
            $sql = " SELECT 
							ordMas.* 
			 			FROM 
			 				" . DB_PREFIX . "order_masters ordMas
			 			WHERE 
			 				ordMas.masterOrderID='" . $this->escape($orderID) . "' ";
        if ($businessID >0) {
            $sql .= " AND ordMas.businessID='" . $businessID . "' ";
        }							
			 $sql .= 		"	GROUP BY 
			 				ordMas.masterOrderID";
        }
        $resOrderDetails = $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
        return $resOrderDetails;
    }
    public function getRecentOrderBillingInfo($orderID = '', $userID = '') {
        $sql = " SELECT 
						ordMas.* 
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					WHERE 
						ordMas.userID='" . $userID . "' ";
        if ($orderID != '') {
            $sql .= " AND ordMas.masterOrderID!='" . $orderID . "' ";
        }
        $sql .= " ORDER BY 
						ordMas.masterOrderID DESC LIMIT 0,1 ";
        $resOrderDetails = $this->ObjDatabase->readValueSlave($sql);
        return $resOrderDetails;
    }
    //GET CART ITEMS
    public function getOrderItems($orderID = '', $masterOrderID = '', $idOnly = 'No') {
        if ($idOnly == 'Yes') {
            $sql = " SELECT  
							ordIte.productID ";
        } else {
            $sql = " SELECT 
							ordIte.*,
							product.productImage as imagePath,
                                                        product.sellAsBatch,
                                                        product.batchType,
                                                        product.qtyPerBatch";
        }
        $sql .= " FROM 
						" . DB_PREFIX . "order_items ordIte 
					LEFT JOIN
						" . DB_PREFIX . "products product 
						ON (product.productID=ordIte.productID) ";
        $sql .= " WHERE ";
        if ($masterOrderID > 0) {
            $sql .= " ordIte.masterOrderID ='" . (int) $masterOrderID . "'";
        } else {
            if (strpos($orderID, ','))
                $sq .= " ordIte.masterOrderID IN (" . $orderID . ")";
            else
                $sql .= " ordIte.masterOrderID ='" . (int) $orderID . "'";
        }
        $resOrderItems = $this->ObjDatabase->readValuesSlave($sql);
        return $resOrderItems;
    }
    //GET BASIC ORDER DETAILS
    public function getBasicOrderDetails($languageIDs, $orderID) {
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        $sql               = " SELECT 
						ordMas.orderStatusDate,
						ordMas.billingFirstName,
						ordMas.billingLastName,
						productDet.productTitle
					FROM 
						" . DB_PREFIX . "order_masters  AS ordMas
					LEFT JOIN 
						" . DB_PREFIX . "order_items  AS ordIte 
						ON(ordMas.masterOrderID	= ordIte.masterOrderID)
					LEFT JOIN 
						" . DB_PREFIX . "product_details AS productDet 
						ON(ordIte.productID	= productDet.productID)
					WHERE 
						productDet.languageID = '" . $defaultLanguageID . "'
						AND ordMas.masterOrderID 	 =	'" . (int) $orderID . "'
					GROUP BY 
						ordMas.masterOrderID";
        $resOrderItems     = $this->ObjDatabase->readValue($sql);
        return $resOrderItems;
    }
    public function saveGiftCouponOrder($POST, $userArray = '') {
        $orderType              = 'coupon';
        $userID                 = (isset($POST['userID']) ? $POST['userID'] : 0);
        $orderSubtotal          = $POST['couponAmount'];
        $orderUserCreditAmount  = (isset($POST['orderUserCreditAmount']) ? $POST['orderUserCreditAmount'] : 0);
        $orderUserCreditAmount  = ($orderUserCreditAmount > $orderSubtotal) ? $orderSubtotal : $orderUserCreditAmount;
        $paymentSettingsGroupID = (isset($POST['paymentSettingsGroupID']) ? $POST['paymentSettingsGroupID'] : 0);
        $orderPaymentMethodName = (isset($POST['orderPaymentMethodName']) ? $POST['orderPaymentMethodName'] : '');
        $PaymentType            = $POST['PaymentType'];
        $paymentStatus          = $POST['paymentStatus'];
        $orderStatus            = $POST['orderStatus'];
        $billingAddress         = serialize(array(
            'billingFirstName' => $this->escape($POST['billingFirstName']),
            'billingLastName' => $this->escape($POST['billingLastName']),
            'billingAddress' => $this->escape($POST['billingAddress']),
            'billingAddress1' => $this->escape($POST['billingAddress1']),
            'billingCity' => $this->escape($POST['billingCity']),
            'billingProvince' => (isset($POST['billingProvince']) ? $this->escape($POST['billingProvince']) : ''),
            'billingZip' => $this->escape($POST['billingZip']),
            'billingState' => (isset($POST['billingState']) ? $this->escape($POST['billingState']) : 0),
            'billingCountry' => $this->escape($POST['billingCountry']),
            'billingPhone' => $this->escape($POST['userCountrycode']).'-'.$this->escape($POST['billingPhone']),
            'billingEmail' => $this->escape($POST['billingEmail'])
        ));
        if ($orderUserCreditAmount >= $orderSubtotal) {
            $orderTotalAmount = 0;
        } else {
            $orderTotalAmount = $orderSubtotal - $orderUserCreditAmount;
        }
        $orderQty          = 1;
        $resUserGetDetails = $this->getOrderedUserDetails($userID);
        if (!empty($resUserGetDetails) && $resUserGetDetails['userAdress1'] == '' && $resUserGetDetails['userCity'] == '') {
            $sql                  = " UPDATE 
							" . DB_PREFIX . "users 
						SET
							userAdress1='" . $this->escape($POST['billingAddress']) . "',
							userAdress2='" . $this->escape($POST['billingAddress1']) . "',
							userCity='" . $this->escape($POST['billingCity']) . "',
							stateID='" . (isset($POST['billingState']) ? $this->escape($POST['billingState']) : 0) . "',
							userProvince='" . (isset($POST['billingProvince']) ? $this->escape($POST['billingProvince']) : '') . "',
							countryID='" . $this->escape($POST['billingCountry']) . "',
							userZip='" . $this->escape($POST['billingZip']) . "',
							userPhone='" . $this->escape($POST['userCountrycode']).'-'.$this->escape($POST['billingPhone']) . "'
						WHERE 
							userID='" . $userID . "'";
            $resupdateUserAccount = $this->ObjDatabase->setQuery($sql);
        }
        $POST['couponOrderID'] = (isset($POST['couponOrderID']) ? $POST['couponOrderID'] : 0);
        if ($POST['couponOrderID'] <= 0) {
            $sql             = " INSERT INTO " . DB_PREFIX . "order_masters
						(
							orderType,
							orderTotalAmount,
							orderSubtotal,
							orderQty,
							orderUserCreditAmount,
							paymentSettingsGroupID,
							orderPaymentMethodName,
							paymentType,
							billingAddress,
							paymentStatus,
							orderStatus,
							userID,
							orderCreatedDate,
							orderStatusDate,
							orderRemoved,
                                                        userCurrencyID,
                                                        userCurrencyExcRate
						)
						VALUES
						(
							'" . $orderType . "',
							" . $orderTotalAmount . ",
							" . $orderSubtotal . ",
							" . $orderQty . ",
							" . (int) $orderUserCreditAmount . ",
							" . (int) $paymentSettingsGroupID . ",
							'" . $orderPaymentMethodName . "',
							'" . $PaymentType . "',
							'" . $this->escape($billingAddress, false, false) . "',
							'" . $paymentStatus . "',
							'" . $orderStatus . "',
							" . $userID . ",
							NOW(),
							NOW(),
							'No',
                                                        '" . $this->escape($POST['userCurrencyID']) . "',
                                                        '" . $this->escape($POST['userCurrencyExcRate']) . "'
						)";
            $resInsertMaster = $this->ObjDatabase->setQuery($sql);
            $masterOrderID   = $this->ObjDatabase->getInsertId();
            $sql             = "UPDATE " . DB_PREFIX . "order_masters SET orderNumber = '" . $masterOrderID . "' WHERE masterOrderID=" . $masterOrderID . "";
            $update          = $this->ObjDatabase->setQuery($sql);
            return $masterOrderID;
        }
    }
    public function saveUserTransaction($userID, $transactionAmount = '0', $transactionDescription = '', $transactionType = '', $transactionStatus = '') {
        $resInsertUserTransaction = array();
        if ($userID != '') {
            $transactionType          = ($transactionType == '') ? 'Debit' : $transactionType;
            $transactionStatus        = ($transactionStatus == '') ? 'Active' : $transactionStatus;
            $sql                      = " INSERT INTO " . DB_PREFIX . "users_transactions
							(
								userID,
								transactionType,
								transactionDate,
								transactionAmount,
								transactionDescription,
								transactionStatus
							)
							VALUES
							(
								" . $userID . ",
								'" . $transactionType . "',
								NOW(),
								'" . number_format($transactionAmount, 5, '.', '') . "',
								'" . $transactionDescription . "',
								'" . $transactionStatus . "'
							)";
            $resInsertUserTransaction = $this->ObjDatabase->setQuery($sql);
        }
        return $resInsertUserTransaction;
    }
    public function updateCouponOrderNumber($masterOrderID) {
        $orderNumber            = $this->setOrderNumber();
        $sql                    = " UPDATE 
						" . DB_PREFIX . "order_masters 
					SET 
						orderNumber = '" . $orderNumber . "'  
					WHERE  
						masterOrderID= '" . (int) $masterOrderID . "'";
        $resUpdateCouponOrderNo = $this->ObjDatabase->setQuery($sql);
        return $orderNumber;
    }
    public function deleteGiftCouponOrder($orderID = '') {
        $sql                     = " DELETE FROM  
						" . DB_PREFIX . "order_masters 
					WHERE 
						masterOrderID='" . $orderID . "' 
						AND orderType='coupon'";
        $rsDeleteGiftCouponOrder = $this->ObjDatabase->setQuery($sql);
        return true;
    }
    public function saveReferralPurchase($referralID, $masterOrderID) {
        $sql                      = " INSERT INTO " . DB_PREFIX . "referral_purchases
						(
							referrerID,
							purchaseDate,
							masterOrderID
						)
						VALUES
						(
							'" . $referralID . "',
							Now(),
							'" . $masterOrderID . "'
						)";
        $rsInsertReferralPurchase = $this->ObjDatabase->setQuery($sql);
        return $rsInsertReferralPurchase;
    }
    public function updateUsersReferral($userID = '', $masterOrderID = '', $purchaseStatus = '', $referralAmount = '', $referralStatus = '') {
        $getReferredByUser = $this->getReferredByUser($userID);
        if ($getReferredByUser['referralStatus'] != 'Active' && $getReferredByUser['referralStatus'] != '') {
            $referralID = $getReferredByUser['referrerID'];
            $referredBy = $getReferredByUser['referedBY'];
            $sql        = " UPDATE 
							" . DB_PREFIX . "users_referrals 
						SET 
							referedTo='" . $userID . "' ";
            if ($purchaseStatus != '') {
                $sql .= " ,purchaseStatus='" . $purchaseStatus . "' ";
            }
            if ($referralAmount != '') {
                $sql .= " ,referralAmount='" . $referralAmount . "'";
            }
            if ($referralStatus != '') {
                $sql .= " ,referralStatus='" . $referralStatus . "', 
							referalRegActiveDate=Now() ,
							LastActDate=Now()";
            }
            $sql .= " WHERE 
							referedTo=" . (int) $userID . " ";
            if ($referralID != '') {
                $sql .= " AND referrerID=" . (int) $referralID . " ";
            }
            $sql .= "LIMIT 1 ";
            $rsUpdateUsersReferral = $this->ObjDatabase->setQuery($sql);
            if ($rsUpdateUsersReferral) {
                if ($purchaseStatus != '') {
                    $this->saveReferralPurchase($referralID, $masterOrderID);
                } elseif ($referralStatus == 'Active') {
                    $getOrderNumber  = $this->getOrderDetails('', '', '', $masterOrderID);
                    $transactionDesc = 'lbl_amount_credited' . ':' . $getOrderNumber['orderNumber'];
                    $this->saveUserTransaction($referredBy, $getReferredByUser['referralAmount'], $transactionDesc, 'Credit');
                    
                       // exit;
        }
            }
            
            
        }
        $referralID = isset($referralID) ? $referralID : '';
        return $referralID;
    }
    //UPDATE PURCHASE CURRENCY
    public function getReferredByUser($referedTo = '', $referralID = '', $user = '') {
        $sql = " SELECT 
						* 
					FROM 
						" . DB_PREFIX . "users_referrals 
					WHERE 
						1=1 ";
        if ($referralID != '') {
            $sql .= " AND referrerID='" . $referralID . "' ";
        }
        if ($referedTo != '') {
            $sql .= " AND referedTo='" . $referedTo . "' ";
        }
        $rsSelectReferredByUser = $this->ObjDatabase->readValue($sql);
        if ($user == 'Yes') {
            return $rsSelectReferredByUser['referedBY'];
        } else {
            return $rsSelectReferredByUser;
        }
    }
    //UPDATE PURCHASE CURRENCY
    public function updatePurchaseCurrency($masterOrderID, $purchasedCurrency) {
        $sql                      = " UPDATE 
						" . DB_PREFIX . "order_masters 
					SET 
						purchasedCurrency='" . $purchasedCurrency . "' 
					WHERE 
						masterOrderID='" . (int) $masterOrderID . "' ";
        $rsUpdatePurchaseCurrency = $this->ObjDatabase->setQuery($sql);
        return true;
    }
    public function listCallBackReqs($params) {
        extract($params);
        $languageIDs            = isset($languageIDs) ? $languageIDs : array();
        $languageIDs['Current'] = isset($languageIDs['Current']) ? $languageIDs['Current'] : 0;
        $languageIDs['Default'] = isset($languageIDs['Default']) ? $languageIDs['Default'] : 0;
        $limitFrom              = isset($limitFrom) ? $limitFrom : 0;
        $limitTo                = isset($limitTo) ? $limitTo : '';
        $currentLanguageID      = $languageIDs['Current'];
        $sql      = " SELECT 
						SQL_CALC_FOUND_ROWS 
						req.*,brandDet.brandName,
						optDet.productOptionName       
                                                    FROM 
                                                            " . DB_PREFIX . "callback_requests  req 
                                                   
                                                    LEFT JOIN 
                                                            " . DB_PREFIX . "products product 
                                                            ON product.productID=req.productID                                                     
                                                    LEFT JOIN 
                                                            " . DB_PREFIX . "product_options productOpt 
                                                            ON productOpt.productID=product.productID                                                            
                                                    LEFT JOIN 
                                                            " . DB_PREFIX . "product_option_details optDet 
                                                            ON (optDet.productOptionID=productOpt.productOptionID
                                                            AND optDet.languageID = '" . $currentLanguageID . "')
                                                    LEFT JOIN 
						" . DB_PREFIX . "brands AS brand
							ON	(
								brand.brandID = req.brandID
							)                     
                                                    LEFT JOIN 
						" . DB_PREFIX . "brand_details AS brandDet
							ON	(
								brandDet.brandID = brand.brandID
								AND
								brandDet.languageID = '" . $currentLanguageID . "'
							)            
                                                    where 1 ";
        
        
        if ($productID != '') {
            $sql .= " AND req.productID='" . (int) $productID . "' ";
        } 
        if ($brandID != '') {
            $sql .= " AND req.brandID='" . (int) $brandID . "' ";
        } 
        if ($productKey != '') {
            $sql .= " AND optDet.productOptionName LIKE '%" . $this->escape($productKey) . "%' ";
        }  
        if ($userEmail != '') {
            $sql .= " AND req.userEmail LIKE '%" . $this->escape($this->encryptDecrypt("encrypt",$userEmail)) . "%' ";
        }  
        if ($userName != '') {
            //$sql .= " AND CONCAT(req.userFirstName,' ',req.userLastName) LIKE '%" . $this->escape($userName) . "%' ";      
            $sql .= " AND (req.userFirstName LIKE '%" . $this->escape($this->encryptDecrypt("encrypt",$userName)) . "%' or req.userLastName LIKE '%" .  $this->escape($this->encryptDecrypt("encrypt",$userName)) . "%') ";      
        }        
        if ($sortBy) {
            if($sortBy=='scustomer') {
                $sortBy = " CONCAT(req.userFirstName,' ',req.userLastName)";
            }
            $sql .= " ORDER BY " . $sortBy . " ";
        } else {
            $sql .= " ORDER BY req.requestID ";
        }
        if ($orderAs) {
            $sql .= "  $orderAs";
        } else {
            $sql .= "  DESC";
        }
        //IF PAGINATION REQUIRED	
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        
        if ($export) {
            return $this->ObjDatabase->readValuesObject($sql, MYSQLI_ASSOC);
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    public function getRequestDetails($requestID='',$languageIDs=array()) {
        if($requestID>0) {
           $currentLanguageID      = $languageIDs['Current'];
           $sql      = " SELECT 
						SQL_CALC_FOUND_ROWS 
						req.*,
						optDet.productOptionName,brandDet.brandName,
                                                product.productID,
                                                productOpt.optionSKU
                                                    FROM 
                                                            " . DB_PREFIX . "callback_requests  req 
                                                   
                                                    LEFT JOIN 
                                                            " . DB_PREFIX . "products product 
                                                            ON product.productID=req.productID                                                     
                                                    LEFT JOIN 
                                                            " . DB_PREFIX . "product_options productOpt 
                                                            ON productOpt.productID=product.productID	
                                                    LEFT JOIN 
                                                            " . DB_PREFIX . "product_option_details optDet 
                                                            ON (optDet.productOptionID=productOpt.productOptionID
                                                            AND optDet.languageID = '" . $currentLanguageID . "')
                                                     LEFT JOIN 
                                                        " . DB_PREFIX . "brands AS brand
							ON	(
								brand.brandID = req.brandID
							)                     
                                                    LEFT JOIN 
                                                        " . DB_PREFIX . "brand_details AS brandDet
							ON	(
								brandDet.brandID = brand.brandID
								AND
								brandDet.languageID = '" . $currentLanguageID . "'
							)                   
                                                    where 1 ";
        
        
            if ($requestID != '') {
                $sql .= " AND req.requestID='" . (int) $requestID . "' ";
            }
            return $this->ObjDatabase->readValue($sql);
        }
    }
    //FETCHING ORDERS
    public function listOrders($params) {
        extract($params);
        $languageIDs            = isset($languageIDs) ? $languageIDs : array();
        $languageIDs['Current'] = isset($languageIDs['Current']) ? $languageIDs['Current'] : 0;
        $languageIDs['Default'] = isset($languageIDs['Default']) ? $languageIDs['Default'] : 0;
        $limitFrom              = isset($limitFrom) ? $limitFrom : 0;
        $limitTo                = isset($limitTo) ? $limitTo : '';
        $orderNumber            = isset($orderNumber) ? $this->escape($orderNumber) : '';
        $corderNumber            = isset($corderNumber) ? $this->escape($corderNumber) : '';
        $fromDate               = isset($fromDate) ? $fromDate : '';
        $toDate                 = isset($toDate) ? $toDate : '';
        $searchBusiness         = isset($searchBusiness) ? $this->escape($searchBusiness) : '';
        $searchShowroom         = isset($searchShowroom) ? $this->escape($searchShowroom) : '';
        $orderStatus            = isset($orderStatus) ? $orderStatus : '';
        $productKey             = isset($productKey) ? $this->escape($productKey) : '';
        $searchProductTypes     = isset($searchProductTypes) ? $this->escape($searchProductTypes) : '';
        $certificateName        = isset($certificateName) ? $this->escape($certificateName) : '';
        $paymentStatus          = isset($paymentStatus) ? $paymentStatus : '';
        $userID                 = isset($userID) ? $userID : '';
        $orderAs                = isset($orderAs) ? $orderAs : '';
        $productID              = isset($productID) ? $productID : '';
        $shipmentStatus         = isset($shipmentStatus) ? $shipmentStatus : '';
        $langIds                = isset($langIds) ? $langIds : array();
        $sortBy                 = (isset($sortBy)) ? $sortBy : '';
        $productTypeKey         = (isset($productTypeKey)) ? $this->escape($productTypeKey) : '';
        $orderID                = (isset($orderID)) ? $this->escape($orderID) : '';
        $charityID              = (isset($charityID)) ? $this->escape($charityID) : '';
        $groupBy                = (isset($groupBy)) ? $this->escape($groupBy) : '';
        $orderItemID            = (isset($orderItemID)) ? $this->escape($orderItemID) : '';
        $masterOrderID          = (isset($masterOrderID)) ? $this->escape($masterOrderID) : '';
        $export                 = (isset($export)) ? $this->escape($export) : '';
        $shippingSetting        = (isset($shippingSetting)) ? $shippingSetting : '';
        $paymentmethod          = (isset($paymentmethod)) ? $paymentmethod : '';
        $orderType              = isset($orderType) ? $orderType : '';
        $showBus                = isset($showBus) ? $showBus : '';
        $orderSource            = isset($orderSource) ? $orderSource : '';
        $brandID                = isset($brandID) ? $brandID : '';
        if ($groupBy == 'orderItem') {
            $groupBy = 'ordIte.orderItemID';
        }
        if ($sortBy == "sorderid") {
            $sortBy = "ordMas.masterOrderID ";
        } else if ($sortBy == "scustomer") {
            $sortBy = "usr.userFirstName";
        } else if ($sortBy == "squantity") {
            $sortBy = "ItemQuantity";
        } else if ($sortBy == "stotal") {
            $sortBy = "ordMas.orderTotalAmount";
        } else if ($sortBy == "ssdate") {
            $sortBy = "ordMas.orderStatusDate 	";
        } else if ($sortBy == "shipdate") {
            $sortBy = "ordIte.shipmentDate 	";
        } else if ($sortBy == "sorderdate") {
            $sortBy = "ordMas.orderCreatedDate 	";
        } else if ($sortBy == "stitle") {
            $sortBy = "productDet.productTitle 	";
        } else if ($sortBy == "spstatus") {
            $sortBy = "ordMas.paymentStatus 	";
        } else if ($sortBy == "sostatus") {
            $sortBy = "ordMas.orderStatus 	";
        } else {
            $sortBy = "ordMas.masterOrderID ";
        }
        $orderID       = (is_array($orderID)) ? implode(',', $orderID) : $orderID;
        $orderItemID   = (is_array($orderItemID)) ? implode(',', $orderItemID) : $orderItemID;
        $masterOrderID = (is_array($masterOrderID)) ? implode(',', $masterOrderID) : $masterOrderID;
        
        if ($shipmentStatus == 'Delivered' || $shipmentStatus == 'Dispatched' || $shipmentStatus == 'Shipped') {
            if ($orderStatus == '')
                $orderStatus = 'Any';
            $orderStatusDate = "ordIte.shipmentDate";
        } else if ($shipmentStatus == 'Pending') {
            if ($orderStatus == '')
                $orderStatus = 'Any';
            $orderStatusDate = "ordMas.orderCreatedDate";
        } else {
            $orderStatusDate = "ordMas.orderStatusDate";
        }
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        if ($certificateName != '') {
            $certificateName = $this->escape($certificateName);
        }
        if ($orderNumber != '') {
            $orderNumber = $this->escape($orderNumber);
        }
         if ($corderNumber != '') {
            $corderNumber = $this->escape($corderNumber);
        }
        if ($productKey != '') {
            $productKey = $this->escape($productKey);
        }
        $fromDate = $fromDate ? $fromDate : '';
        $toDate   = $toDate ? $toDate : '';
        $busFields = '';
        if($showBus=='Y') {
            $busFields = 'busDet.businessName,bus.api_id,';
        }
        $sql      = " SELECT 
						SQL_CALC_FOUND_ROWS 
						ordMas.*,
						IF(ordMas.userID > 0 ,CONCAT(usr.userFirstName,' ',usr.userLastName),CONCAT(ordMas.subtitle,ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,ordMas.subtitle,ordMas.billingFirstName,
		 				SUM(ordIte.orderItemQuantity) as ItemQuantity,
		 				" . $orderStatusDate . " orderStatusDate,".$busFields."
		 				product.*, 
		 				usr.userFirstName, 
		 				usr.userLastName , 
		 				usr.userEmail, 
		 				product.productID,								 
		 				ordMas.shippingAddress AS shippedAddress,
		 				ordMas.billingAddress,
						paySet.paySetGroupLabel,
                                                showroomDets.name AS showroomName,brandDet.brandName, 
                                                ";
        $sql .= " (SELECT CAST(GROUP_CONCAT(ships.shippingID) AS CHAR) 
									FROM 
										" . DB_PREFIX . "shipments as ships 
									WHERE 
										ships.masterOrderID=ordMas.masterOrderID)
							as shippedIDs,	";
        if ($shipmentStatus == 'Pending') {
            $sql .= " (SELECT sum(quantity) 
									FROM 
										" . DB_PREFIX . "shipments as shi 
									LEFT JOIN " . DB_PREFIX . "shipment_items as shiIte 
										ON(shiIte.shippingID=shi.shippingID) 
									WHERE 
										shi.masterOrderID=ordMas.masterOrderID)
							as shippedQuantity,	";
        }
        $sql .= " 	ordIte.shippingSetting AS masterShippingSetting
					FROM 
		 				" . DB_PREFIX . "order_masters  ordMas 
		 			LEFT JOIN 
		 				" . DB_PREFIX . "order_items ordIte 
		 				ON ordMas.masterOrderID=ordIte.masterOrderID
		 			LEFT JOIN 
		 				" . DB_PREFIX . "products product 
		 				ON product.productID=ordIte.productID 
					LEFT JOIN 
		 				" . DB_PREFIX . "payment_settings_group paySet 
		 				ON paySet.paySetGroupID=ordMas.paymentSettingsGroupID ";
         if($showBus=='Y') {
                     $sql	.= " LEFT JOIN 
								".DB_PREFIX."business bus 
								ON (bus.businessID = ordMas.businessID)
							LEFT JOIN 
								".DB_PREFIX."business_details busDet 
								ON (busDet.businessID = bus.businessID 
								AND busDet.languageID=".(int)$currentLanguageID.")	";
                 }
        if ($productKey != '') {
            $sql .= " 
		 			LEFT JOIN 
		 				" . DB_PREFIX . "product_options productOpt 
		 				ON productOpt.productOptionID=ordIte.productOptionID	
					LEFT JOIN 
						" . DB_PREFIX . "product_option_details optDet 
						ON (optDet.productOptionID=ordIte.productOptionID
						AND optDet.languageID = '" . $currentLanguageID . "')
		 			LEFT JOIN 
		 				" . DB_PREFIX . "product_combination proComb 
		 				ON proComb.combinationHash=ordIte.combinationHash		
					LEFT JOIN 
			 				" . DB_PREFIX . "product_details productDet 
			 				ON (productDet.productID=product.productID 
			 				AND productDet.languageID = '" . $currentLanguageID . "')";
        }
        $sql .= "  LEFT JOIN 
		 				" . DB_PREFIX . "showrooms showroom
                        ON showroom.showroomID  = ordMas.showroomID   ";
        $sql .= "  LEFT JOIN 
		 				" . DB_PREFIX . "showroom_details showroomDets
                        ON  ( showroom.showroomID  = showroomDets.showroomID  AND showroomDets.languageID = '" . $currentLanguageID . "') ";
        $sql .= "  LEFT JOIN 
		 				" . DB_PREFIX . "users usr 
                        ON usr.userID  = ordMas.userID   
                     LEFT JOIN 
						" . DB_PREFIX . "brands AS brand
							ON	(
								brand.brandID = ordMas.brandID
							)                     
                                                    LEFT JOIN 
						" . DB_PREFIX . "brand_details AS brandDet
							ON	(
								brandDet.brandID = brand.brandID
								AND
								brandDet.languageID = '" . $currentLanguageID . "'
							)    
                    ";
        if ($searchProductTypes != '') {
            $sql .= "			LEFT JOIN 
		 				" . DB_PREFIX . "product_types productTyp 
		 				ON (product.productTypeID = productTyp.productTypeID) ";
        }
        $sql .= "		WHERE 
		 				ordMas.orderType='product'
		 				AND ordMas.orderNumber!=''
		 				 
		 				AND ordMas.orderRemoved='No' ";
        if ($productID != '') {
            $sql .= " AND ordIte.productID='" . (int) $productID . "' ";
        }
        if ($searchShowroom != '') {
            $sql .= " AND ordMas.showroomID='" . (int) $searchShowroom . "' ";
        }
        if ($orderNumber != '') {
            $sql .= " AND lower(ordMas.orderNumber) =lower('" . $orderNumber . " ')";
        }
        if ($corderNumber != '') {
            $sql .= " AND ordMas.extraData LIKE '%" . $corderNumber . "%' ";
        }
        if ($brandID != '') {
            $sql .= " AND ordMas.brandID='" . (int) $brandID . "' ";
        }
        if ($masterOrderID != '') {
            $sql .= " AND ordMas.masterOrderID IN (" . $masterOrderID . ")";
        }
        if ($orderItemID != '') {
            $sql .= " AND ordIte.orderItemID IN (" . $orderItemID . ")";
        }
        if ($fromDate != '' && $toDate != '') {
            if ($shipmentStatus == 'Delivered' || $shipmentStatus == 'Dispatched' || $shipmentStatus == 'Shipped') {
                $sql .= " AND ( DATEDIFF('" . $fromDate . "',ordIte.shipmentDate )<=0 
						AND DATEDIFF('" . $toDate . "',ordIte.shipmentDate )>=0 )";
            } else if ($shipmentStatus == 'Pending') {
                $sql .= " AND ( DATEDIFF('" . $fromDate . "',ordMas.orderCreatedDate)<=0 
		   		 			AND DATEDIFF('" . $toDate . "',ordMas.orderCreatedDate)>=0 )";
            } else {
                $sql .= " AND ( DATEDIFF('" . $fromDate . "',ordMas.orderStatusDate)<=0 
		  		 			AND DATEDIFF('" . $toDate . "',ordMas.orderStatusDate)>=0 )";
            }
        } else if ($fromDate != '') {
            if ($shipmentStatus == 'Delivered' || $shipmentStatus == 'Dispatched' || $shipmentStatus == 'Shipped') {
                $sql .= " AND ( DATEDIFF( '" . $fromDate . "',ordIte.shipmentDate)=0 ) ";
            } else if ($shipmentStatus == 'Pending') {
                $sql .= " AND ( DATEDIFF( '" . $fromDate . "',ordMas.orderCreatedDate)=0 ) ";
            } else {
                $sql .= " AND ( DATEDIFF( '" . $fromDate . "',ordMas.orderStatusDate)=0 ) ";
            }
        } else if ($toDate != '') {
            if ($shipmentStatus == 'Delivered' || $shipmentStatus == 'Dispatched' || $shipmentStatus == 'Shipped') {
                $sql .= " AND (  DATEDIFF('" . $toDate . "',ordIte.shipmentDate)=0)  ";
            } else if ($shipmentStatus == 'Pending') {
                $sql .= " AND (  DATEDIFF('" . $toDate . "',ordMas.orderCreatedDate)=0)  ";
            } else {
                $sql .= " AND (  DATEDIFF('" . $toDate . "',ordMas.orderStatusDate)=0)  ";
            }
        }
        if ($searchBusiness) {
            $searchBusiness = preg_replace('/[^:0-9]|[:,;\']$/s', "", $searchBusiness);
            $searchBusiness = implode("','", explode(":", $searchBusiness));
            $sql .= " AND ordMas.businessID IN ('" . $searchBusiness . "')";
        }
        if ($productKey != '') {
            $sql .= " AND (productDet.productTitle LIKE '%" . $productKey . "%'
							OR productOpt.optionSKU LIKE '%" . $productKey . "%'
							OR optDet.productOptionName LIKE '%" . $productKey . "%'
							OR proComb.combinationSKU LIKE '%" . $productKey . "%'
							)";
        }
        if ($searchProductTypes != '') {
            $searchProductTypes = preg_replace('/[^:0-9]|[:,;\']$/s', "", $searchProductTypes);
            $searchProductTypes = implode("','", explode(":", $searchProductTypes));
            $sql .= " AND ordIte.productID=product.productID 
						AND product.productTypeID IN ('" . $searchProductTypes . "')";
        }
//        if ($orderStatus == 'Any') {
//            $sql .= " AND ordMas.orderStatus!='New' ";
//        } elseif ($orderStatus != '') {
//            $sql .= " AND ordMas.orderStatus='" . $orderStatus . "'";
//        } else {
//            $sql .= " AND ordMas.orderStatus='Process' ";
//        }
        if ($orderStatus == 'Any') {
            $sql .= " AND ordMas.dynamicOrderStatus!='New' AND ordMas.dynamicOrderStatus!=''  ";
        } elseif ($orderStatus != '') {
            $sql .= " AND ordMas.dynamicOrderStatus='" . $orderStatus . "'";
        } else {
            if($merchantOrderStatus=='pending'){
                $sql .= " AND (ordMas.invoiceNumber='' AND  ordMas.dynamicOrderStatus='po-placed')";
            }
            else if($merchantOrderStatus=='shipped'){
                $sql .= " AND ( ordMas.dynamicOrderStatus='invoice-created' OR"
                        . "  ordMas.dynamicOrderStatus='qc-completed' OR ordMas.dynamicOrderStatus='order-ready' "
                        . " OR  ordMas.dynamicOrderStatus='ready-to-ship' OR  ordMas.dynamicOrderStatus='order-shipped' OR ordMas.dynamicOrderStatus='order-delivered') ";
            }else if($merchantOrderStatus=='completed'){
                $sql .= " AND (ordMas.dynamicOrderStatus='order-completed' OR ordMas.dynamicOrderStatus='completed')";
            }else if ($merchantOrderStatus=='order-verified'){
                $sql .= " AND (ordMas.dynamicOrderStatus='order-verified' AND ordMas.invoiceNumber='')  ";
            }
            else if($merchantOrderStatus!=''){
                    $sql .= " AND ordMas.dynamicOrderStatus='".$merchantOrderStatus."' ";
            }
            else{
           // $sql .= " AND ordMas.orderStatus!='New' AND ordMas.dynamicOrderStatus!='New' AND ordMas.dynamicOrderStatus!='' ";
            $sql .= " AND ordMas.orderStatus!='New' AND ordMas.dynamicOrderStatus!='New' AND ordMas.dynamicOrderStatus!='' ";
            }
        }
        if ($shipmentStatus == 'Shipped') {
            $sql .= " AND (ordIte.shipmentStatus='Delivered' OR ordIte.shipmentStatus='Dispatched') ";
        } else if ($shipmentStatus == 'Pending') {
            $sql .= " AND (ordMas.orderQty > IFNULL((SELECT sum(quantity) 
								FROM 
									" . DB_PREFIX . "shipments as shi 
								LEFT JOIN " . DB_PREFIX . "shipment_items as shiIte 
									ON(shiIte.shippingID=shi.shippingID) 
								WHERE 
									shi.masterOrderID=ordMas.masterOrderID),0)
							) ";
        } else if ($shipmentStatus != '') {
            $sql .= " AND ordIte.shipmentStatus='" . $shipmentStatus . "'";
        } {
        }
        if ($orderType == 'gu') {
            $sql .= " AND ordMas.userID = 0 ";
        } else if ($orderType == 'regi') {
            $sql .= " AND ordMas.userID > 0 ";
        }
        if ($certificateName != '') {
            $sql .= " AND IF(ordMas.userID > 0 ,
					CONCAT(usr.userFirstName,' ',usr.userLastName),
					(ordMas.billingFirstName)) 
					LIKE '%" . $this->encryptDecrypt("encrypt",$certificateName) . "%' ";
            /* $sql	.= " AND ( usr.userFirstName LIKE '%" . $certificateName . "%' 
             * CONCAT(ordMas.subtitle,ordMas.billingFirstName,' ',ordMas.billingLastName)
            OR usr.userLastName LIKE
            '%" . $certificateName . "%' 
            OR CONCAT(usr.userFirstName,' ',usr.userLastName) 
            LIKE '%" . $certificateName . "%' 
            OR ordMas.billingFirstName LIKE '%" . $certificateName . "%' 
            OR ordMas.billingLastName LIKE
            '%" . $certificateName . "%' 
            OR CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName) 
            LIKE '%" . $certificateName . "%') "; */
        }
        if (strpos($paymentStatus, '|') !== false) {
            $statuses = explode('|', $paymentStatus);
            $stString = '(';
            $or       = '';
            foreach ($statuses as $status) {
                $stString .= $or . " ordMas.paymentStatus='" . $status . "' ";
                $or = 'OR';
            }
            $stString .= ')';
            $sql .= " AND " . $stString;
        } elseif ($paymentStatus != '') {
            $sql .= " AND ordMas.paymentStatus='" . $paymentStatus . "'";
        }
        if ($userID > 0) {
            $sql .= " AND ordMas.userID='" . $userID . "'";
        }
        if ($shippingSetting) {
            $sql .= " AND ordIte.shippingSetting='" . $shippingSetting . "'";
        }
        if ($paymentmethod > 0) {
            $sql .= " AND ordMas.paymentSettingsGroupID=" . $paymentmethod . "";
        }
        if($orderSource!=''){
             $sql .= " AND ordMas.orderSource='".$orderSource."' ";
        }
        if ($groupBy != '') {
            $sql .= " GROUP BY " . $groupBy;
        } else {
            $sql .= " GROUP BY ordMas.masterOrderID  ";
        }
        if ($sortBy) {
            $sql .= " ORDER BY " . $sortBy . " ";
        } else {
            $sql .= " ORDER BY ordMas.orderNumber ";
        }
        if ($orderAs) {
            $sql .= "  $orderAs";
        } else {
            $sql .= "  DESC";
        }
        //IF PAGINATION REQUIRED	
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        if ($export) {
            return $this->ObjDatabase->readValuesObject($sql, MYSQLI_ASSOC);
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    //CHANGE ORDER STATUS
    function changeOrderStatus($POST) {
        $orderChangeStatus = $POST['orderChangeStatus'];
        $orderIDs          = implode(",", $POST['checkOrderIDs']);
        $paymentStatus     = (isset($POST['paymentStatus'])) ? $POST['paymentStatus'] : '';
        if ($orderChangeStatus != '') {
            $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							orderStatus='" . $orderChangeStatus . "' ";
            if ($paymentStatus) {
                $sql .= ", paymentStatus = '" . $paymentStatus . "' ";
            }
            $sql .= " WHERE 
							masterOrderID IN (" . $orderIDs . ") ";
            $resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql);
            return $resUpdateOrderStatus;
        }
        return false;
    }
    //CHANGE PAYMENT STATUS
    function changePaymentStatus($POST, $orderID = '', $masterOrderID = '') {
        $fields        = '';
        $fields1       = '';
        $orderStatus   = $POST['orderStatus'];
        $paymentStatus = $POST['paymentStatus'];
        if ($masterOrderID > 0) {
            $sql = " UPDATE 
							" . DB_PREFIX . "order_masters 
						SET ";
            $fields .= ((($POST['orderStatus']) != "") ? ((($fields != "") ? "," : "") . "orderStatus='" . $orderStatus . "'") : (""));
            $fields .= ((($POST['paymentStatus']) != "") ? ((($fields != "") ? "," : "") . "paymentStatus='" . $paymentStatus . "'") : (""));
            if ($fields != '')
                $fields .= ",";
            $fields .= " orderStatusDate=NOW()";
            $fields .= " WHERE 
							masterOrderID ='" . $masterOrderID . "' ";
            $resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql . $fields);
        }
        return $resUpdateOrderStatus;
    }
    //CHANGE MASTER PAYMENT STATUS
    public function changeMasterPaymentStatus($POST, $masterOrderID) {
        if ($resUpdateOrderStatus) {
            $sql = " UPDATE 
							" . DB_PREFIX . "order_masters 
						SET ";
            $fields .= ((($POST['orderStatus']) != "") ? ((($fields != "") ? "," : "") . "orderStatus='" . $orderStatus . "'") : (""));
            $fields .= ((($POST['paymentStatus']) != "") ? ((($fields != "") ? "," : "") . "paymentStatus='" . $paymentStatus . "'") : (""));
            if ($fields != '')
                $fields .= ",";
            $fields .= " orderStatusDate=NOW()";
            $fields .= " WHERE 
							masterOrderID =" . $orderID . " ";
            $resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql . $fields);
        }
    }
    //GET USER ORDER DETAILS
    function getUserOrderDetails($languageIDs, $masterOrderID, $userID = 0) {
        $sql = " SELECT 
						ordMas.*
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					WHERE 
						ordMas.masterOrderID='" . $masterOrderID . "' ";
        if ($userID > 0) {
            $sql .= " AND ordMas.userID='" . $userID . "' ";
        }
        $sql .= " GROUP BY 
						ordMas.orderNumber ASC";
        $resOrderDetails = $this->ObjDatabase->readValueSlave($sql);
        return $resOrderDetails;
    }
    //GET ORDER DEAL DETAILS
    public function getOrderProductDetails($languageIDs, $orderID = '', $userID = 0, $orderStatus = "", $masterOrderID = '', $settingsProductReview = 'No',$businessID='') {
        $languageID        = $languageIDs['Current'];
        $defaultLanguageID = $languageIDs['Default'];
        $sql               = " SELECT 
						ordMas.masterOrderID,
						ordMas.purchasedCurrency,
						ordMas.businessID,
						ordMas.orderStatus,
                                                ordMas.blueEx_CN,
                                                ordMas.delivery_CN,
                                                ordMas.order_code,
                                                ordMas.paymentSettingsGroupID,
                                                ordMas.userCurrencyID,
                                                ordMas.userCurrencyExcRate,
						ordIte.*,
						productDet.productTitle,
						ordMas.orderNumber,
						ordMas.cityID,
						productTyp.productTypeKey,
						productOpt.*,
						busDet.businessName,
						product.productReturnable,
						product.productReturnPeriod,
						product.productDispatchTime,
						product.productImage,product.freeProductsCount,
                                                product.sellAsBatch,
                                                product.batchType,
                                                product.qtyPerBatch  ";
        if ($settingsProductReview == 'Yes') {
            $sql .= " ,( CASE WHEN ( SELECT count(productRev.ProductReviewID) FROM " . DB_PREFIX . "product_reviews AS productRev WHERE productRev.productID = ordIte.productID AND productRev.userID = '" . $userID . "' AND productRev.reviewStatus !='Deleted' ) > 0 THEN 1 ELSE 0 END) AS haveProductReview 				";
        }
        $sql .= "	FROM 
						" . DB_PREFIX . "order_masters ordMas
					LEFT JOIN  
						" . DB_PREFIX . "order_items ordIte 
						ON ordMas.masterOrderID=ordIte.masterOrderID
					LEFT JOIN 
						" . DB_PREFIX . "products product 
						ON product.productID=ordIte.productID
					LEFT JOIN  
						" . DB_PREFIX . "product_details productDet 
						ON 	(productDet.productID=product.productID AND productDet.languageID = '" . $languageID . "')
					LEFT JOIN 
						" . DB_PREFIX . "product_options productOpt 
						ON (product.productID = productOpt.productID AND ordIte.productOptionID=productOpt.productOptionID)
					LEFT JOIN 
						" . DB_PREFIX . "product_types productTyp 
						ON product.productTypeID = productTyp.productTypeID 
					LEFT JOIN 
						" . DB_PREFIX . "business_details busDet
						ON( busDet.businessID=ordMas.businessID AND busDet.languageID='" . $languageID . "')";
        if ($orderID > 0) {
            $sql .= " WHERE ordMas.masterOrderID='" . $orderID . "' ";
        } else if ($masterOrderID > 0) {
            $sql .= " WHERE ordMas.masterOrderID='" . $masterOrderID . "' ";
        }
        if ($userID > 0) {
            $sql .= " AND ordMas.userID='" . $userID . "' ";
        }
       if ($businessID > 0) {
            $sql .= " AND ordMas.businessID='" . $businessID . "' ";
        }
        if ($orderStatus != "") {
            $sql .= " AND (ordMas.orderStatus!='New' ) ";
        }
        $sql .= " GROUP BY 
						ordIte.orderItemID  ";
        
        $resOrderProductDetails = $this->ObjDatabase->readValuesSlave($sql, MYSQLI_ASSOC);
        return $resOrderProductDetails;
    }
    //LIST USER ORDERS
    public function listUserOrders($params) {
        extract($params);
        $limitFrom         = isset($limitFrom) ? $limitFrom : 0;
        $limitTo           = isset($limitTo) ? $limitTo : '';
        $orderStatus       = isset($orderStatus) ? $orderStatus : '';
        $userID            = isset($userID) ? $userID : '';
        $orderType         = isset($orderType) ? $orderType : '';
        $languageIDs       = isset($languageIDs) ? $languageIDs : array();
        $orderNumber       = isset($orderNumber) ? $this->escape($orderNumber) : '';
        $languageID        = isset($languageIDs['Current']) ? $languageIDs['Current'] : 0;
        $defaultLanguageID = isset($languageIDs['Default']) ? $languageIDs['Default'] : 0;
        $sql               = " 
					SELECT 
						SQL_CALC_FOUND_ROWS 
						ordMas.masterOrderID,
						ordMas.orderNumber,
						ordMas.orderTotalAmount,
						ordMas.purchasedCurrency,
                                                ordMas.userCurrencyID,
                                                ordMas.userCurrencyExcRate,
                                                ordMas.billingPhone,
						ordIte.shipmentStatus,
                                                ordIte.productOptionName,
                                                ordMas.redeemDiscountAmount,";
        if ($section != 'adminDashboard') {
            $sql .= "						
						ordMas.orderType,
						ordMas.orderStatus,";
        }
        if ($section == 'adminDashboard') {
            $sql .= "						
						ordMas.orderQty,
						ordMas.userID,
						IF(ordMas.userID > 0 ,CONCAT(usr.userFirstName,' ',usr.userLastName),CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,ordMas.billingFirstName,ordMas.subtitle,
						ordMas.customer_email,
						ordMas.billingAddress,
						productTitle,";
        }
        if ($section == 'userPurchaseList') {
            $sql .= "	
						ordMas.orderPromoDiscountAmount,
						ordMas.orderPaymentMethodName,
						ordMas.orderSubtotal,
						ordMas.orderOtherServiceCharge,
						ordMas.orderUserCreditAmount,
						ordMas.orderTaxAmount,
						ordMas.shippingAmount,paySet.paySetGroupKey,ordMas.paymentSettingsGroupID,ordMas.userID,ship.shippingStatus,ship.delivered_date,";
        }        
        if ($section != 'adminDashboard' && $languageID > 0) {
            $sql .= "
						busDet.businessName,";
        }
        
        $sql .= "				
						ordMas.orderCreatedDate,
                                                ordMas.dynamicOrderStatus,
                                                ordMas.invoiceNumber,
                                                ordMas.invoiceCreatedDate
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					LEFT JOIN 
						" . DB_PREFIX . "order_items AS ordIte
						ON ordIte.masterOrderID = ordMas.masterOrderID
					LEFT JOIN 
						" . DB_PREFIX . "users usr 
						ON usr.userID=ordMas.userID";
        if ($section == 'userPurchaseList') {
                $sql .= "	         LEFT JOIN " . DB_PREFIX . "payment_settings_group paySet 
		 				ON paySet.paySetGroupID=ordMas.paymentSettingsGroupID ";
                $sql .= "	         LEFT JOIN " . DB_PREFIX . "shipments ship 
		 				ON ship.masterOrderID=ordMas.masterOrderID ";
                
        }
        if ($section == 'adminDashboard' && $languageID > 0) {
            $sql .= "	         LEFT JOIN " . DB_PREFIX . "product_details prodDet 
		 				ON prodDet.productID=ordIte.productID
							AND prodDet.languageID='" . (int) $languageID . "' ";
        }
        if ($section != 'adminDashboard' && $languageID > 0) {
            $sql .= "
					LEFT JOIN 
						" . DB_PREFIX . "business_details busDet 
						ON( busDet.businessID=ordMas.businessID
						AND busDet.languageID='" . (int) $languageID . "')";
        }
        $sql .= "			
					WHERE 
						ordMas.orderNumber!=''";
        if ($orderStatus != '') {
            $sql .= "
						AND (ordMas.orderStatus='Process' 
							OR ordMas.orderStatus='Completed') ";
        } else {
            $sql .= "
						AND (ordMas.orderStatus!='New' ) ";
        }
        if ($orderType != "") {
            $sql .= "
						AND ordMas.orderType =  '" . $orderType . "'";
        }
        if ($userID > 0) {
            $sql .= "
						AND ordMas.userID='" . (int) $userID . "'";
        }
        if ($orderNumber != '') {
            $sql .= "
						AND ordMas.orderNumber='" . $orderNumber . "'";
        }
        $sql .= " 
					GROUP BY 
						ordMas.masterOrderID	
					ORDER BY 
						ordMas.masterOrderID DESC ";
        //IF PAGINATION REQUIRED	
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
          //  echo $sql;
        return $this->ObjDatabase->readValuesSlave($sql);
    }
    //GET ORDER IDS
    public function getOrderIDs($status = '', $masterOrderID = '') {
        $sql = " SELECT 
						masterOrderID 
					FROM 
						" . DB_PREFIX . "order_masters 
					WHERE ";
        if ($status)
            $sql .= " orderStatus='" . $status . "'";
        if ($masterOrderID)
            $sql .= " masterOrderID='" . $masterOrderID . "'";
        return $this->ObjDatabase->readValues($sql);
    }
    //GET TODAYS ORDER
    public function getTodaysOrder() {
        $sql = " 
					SELECT 
						COUNT(ordMas.masterOrderID) as orderCount 
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					WHERE 
						(ordMas.orderStatus='Process' OR ordMas.orderStatus='Completed') 
						AND ordMas.orderNumber!=''
						AND (ordMas.paymentStatus='Received' OR ordMas.paymentStatus='Pending') 
						AND DATE_FORMAT(ordMas.orderCreatedDate,'%Y-%m-%d')='" . date('Y-m-d') . "'
						AND ordMas.orderType  =  'product'";
        return $this->ObjDatabase->getField($sql);
    }
	//GET THIS MONTHS ORDER
    public function getThisMonthsOrder() {
        $sql = " 
					SELECT 
						COUNT(ordMas.masterOrderID) as monthCount 
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					WHERE 
						(ordMas.orderStatus='Process' OR ordMas.orderStatus='Completed') 
						AND ordMas.orderNumber!=''
						AND (ordMas.paymentStatus='Received' OR ordMas.paymentStatus='Pending') 
						AND MONTH(ordMas.orderCreatedDate) = MONTH(CURRENT_DATE()) AND YEAR(ordMas.orderCreatedDate) = YEAR(CURRENT_DATE())
						AND ordMas.orderType  =  'product'";
        return $this->ObjDatabase->getField($sql);
    }
	//GET TOTAL ORDER
    public function getTotalOrder() {
        $sql = " 
					SELECT 
						COUNT(ordMas.masterOrderID) as orderCount 
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					WHERE 
						(ordMas.orderStatus='Process' OR ordMas.orderStatus='Completed') 
						AND ordMas.orderNumber!=''
						AND (ordMas.paymentStatus='Received' OR ordMas.paymentStatus='Pending') 
						AND ordMas.orderType  =  'product'";
        return $this->ObjDatabase->getField($sql);
    }
    //GET CAPTURE AMOUNT
    public function getCaptureAmount() {
        $sql = " 
					SELECT 
						SUM(convertedMasterOrderTotal) as orderTotalAmount 
					FROM 
						" . DB_PREFIX . "order_masters AS ordMas
					WHERE 
						ordMas.paymentStatus='Received' 
                        AND (ordMas.orderStatus='Process' OR ordMas.orderStatus='Completed')
						AND DATE_FORMAT(ordMas.orderCreatedDate,'%Y-%m-%d')='" . date('Y-m-d') . "'
						AND ordMas.orderType = 'product'";
        return $this->ObjDatabase->getField($sql);
    }
    //update order language ID
    public function updateOrderLanguageID($masterOrderID, $currentLanguageID) {
        $sql                  = " UPDATE 
						" . DB_PREFIX . "order_masters 
					SET 
						languageID='" . $currentLanguageID . "'  
					WHERE 
						masterOrderID='" . (int) $masterOrderID . "' ";
        $resUpdateOrderLangID = $this->ObjDatabase->setQuery($sql);
        return true;
    }
    public function getOrderLanguageIDs($orderID = '', $masterOrderID = '') {
        $resSelectOrderLangID    = $this->getOrderDetails($orderID, '', '', $masterOrderID);
        $orderLangIDs            = array();
        $orderLangIDs['Default'] = $resSelectOrderLangID['languageID'];
        $orderLangIDs['Current'] = $resSelectOrderLangID['languageID'];
        return $orderLangIDs;
    }
    public function updateOrderProductDetails($languageIDs, $orderID = '', $masterOrderID = '') {
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        $orderItems        = $this->getOrderItems($orderID, $masterOrderID);
        foreach ($orderItems as $orderItemsKey => $orderItemsVal) {
            $option            = $orderItemsVal['productOptionID'];
            $orderItemID       = $orderItemsVal['orderItemID'];
            $sql               = " SELECT 
							* 
						FROM 
							" . DB_PREFIX . "product_options AS productOpt
						LEFT JOIN 
							" . DB_PREFIX . "product_option_details AS productOptDet 
							ON (productOpt.productOptionID = productOptDet.productOptionID
							AND IF
								(
									EXISTS 
									(	
										SELECT 
											*  
										FROM 
											`" . DB_PREFIX . "product_option_details` 
										WHERE 
											productOptionID = productOpt.productOptionID 
											AND languageID='" . $currentLanguageID . "'
									), 
								productOptDet.languageID = '" . $currentLanguageID . "', 
								IF
								(
									EXISTS 
									(
										SELECT 
											*  
										FROM 
											`" . DB_PREFIX . "product_option_details` 
										WHERE 
											productOptionID = productOpt.productOptionID 
											AND languageID='" . $defaultLanguageID . "'
									)
								, productOptDet.languageID = '" . $defaultLanguageID . "', 
								productOptDet.languageID = '1'
								)))
						WHERE 
							productOpt.productOptionID = '" . $option . "'";
            $resProduct        = $this->ObjDatabase->readValue($sql);
            $productOptionName = $this->escape(html_entity_decode($resProduct["productOptionName"]));
            $sql               = " UPDATE 
							" . DB_PREFIX . "order_items
						SET 
							productOptionName = '" . $productOptionName . "'
						WHERE 
							orderItemID	= '" . $orderItemID . "' 
							AND orderID = '" . (int) $orderID . "'";
            $resUpdateItems    = $this->ObjDatabase->setQuery($sql);
        }
    }
    //to get substring
    public function replacestring($startPoint, $endPoint, $newText, $source) {
        return preg_replace('#(' . preg_quote($startPoint) . ')(.*)(' . preg_quote($endPoint) . ')#si', '$1' . $newText . '$3', $source);
    }
    //GET USER SHIPPING DETAILS
    public function getUserShippingDetails($userID, $stateDetails = '', $languageIDs = '') {
        if ($languageIDs != '') {
            $defaultLanguageID = $languageIDs['Default'];
            $currentLanguageID = $languageIDs['Current'];
        } else {
            $defaultLanguageID = '';
            $currentLanguageID = '';
        }
        if ($stateDetails == '') {
            $sql = " SELECT 
							* 
						FROM 
							" . DB_PREFIX . "user_shipping 
						WHERE 
							userID ='" . $userID . "'";
        } else {
            $sql = " SELECT 
							usrShi.*,
							addFor.*,
							staDet.stateName,
							couDet.countryName,
							CONCAT_WS(
								'~*',
								shippingFname,
								shippingLname,
								shippingAddress1,
								shippingAddress2,
								shippingCity,
								shippingProvince,
								shippingZip,
								shippingStateID,
								shippingCountryID
							) AS shippingAdress 
						FROM 
							" . DB_PREFIX . "user_shipping AS usrShi
						LEFT JOIN 
							" . DB_PREFIX . "state_details AS staDet 
							ON staDet.stateID=usrShi.shippingStateID ";
            if ($currentLanguageID != '') {
                $sql .= " AND staDet.languageID ='" . $currentLanguageID . "'";
            }
            $sql .= " LEFT JOIN 
							" . DB_PREFIX . "countries cou 
							ON cou.countryID=usrShi.shippingCountryID
						LEFT JOIN 
							" . DB_PREFIX . "address_formats addFor 
							ON addFor.addressFormatID=cou.addressFormatID
						LEFT JOIN 
							" . DB_PREFIX . "country_details AS couDet 
							ON couDet.countryID=usrShi.shippingCountryID ";
            if ($currentLanguageID != '') {
                $sql .= " AND couDet.languageID ='" . $currentLanguageID . "'";
            }
            $sql .= " WHERE 
							userID='" . $userID . "'";
        }
        $resUserShippingDetails = $this->ObjDatabase->readValue($sql);
        return $resUserShippingDetails;
    }
    //Multiple user shipping address Starts Here..
    //INSERT USER SHIPPING DETAILS
    public function InsertUserShippingInfo($shippingDet, $usersID,$typeret='') {
        extract($shippingDet);	
         $status='';
        $userPhone=($shippingPhone !='')?$shippingPhone:''; 
        if ($userShippingID > 0) {
            $makerimary = '';
            if (isset($makeAddressAsPrimary) && $makeAddressAsPrimary == 'Yes') {
                $makerimary = ",addressPrimary='" . $makeAddressAsPrimary . "'";
                $sql        = " UPDATE 
								" . DB_PREFIX . "user_shipping 
							SET addressPrimary='No' 
						WHERE 
							userShippingID!='" . $userShippingID . "' 
							AND  userID='" . $usersID . "'";
                $this->ObjDatabase->setQuery($sql);
            }
            
            $sql = " UPDATE 
							" . DB_PREFIX . "user_shipping 
						SET
							shippingFname='" . $shippingFname . "',
							shippingLname='" . $shippingLname . "',
							shippingAddress1='" . $shippingAddress1 . "',
							shippingAddress2='" . $shippingAddress2 . "',
							shippingCity='" . $shippingCity . "',
							shippingZip='" . $shippingZip . "',
							shippingCountryID='" . $shippingCountryID . "',
							shippingStateID='" . $shippingstateID . "',
							shippingProvince='" . $shippingProvince . "',
                            shippingPhone='" .$userCountrycode.'-'. $shippingPhone . "'
							" . $makerimary . "
						WHERE 
							userShippingID='" . $userShippingID . "'";
            $status='updated';
           
        } else {
            $sql                     = " SELECT 
							CASE 
							EXISTS
							(
								SELECT  
									* 
								FROM 
									" . DB_PREFIX . "user_shipping 
								WHERE 
									`userID`='" . $usersID . "' 
									AND 
									`addressStatus`!='Deleted'
							) 
							WHEN 
								1 THEN 'No' 
							ELSE 
								'Yes' 
							END 
							as makePrimary";
            $resOrderShippingPrimary = $this->ObjDatabase->readValue($sql);
            $makerimary              = $resOrderShippingPrimary['makePrimary'];
            if (isset($makeAddressAsPrimary) && $makeAddressAsPrimary == 'Yes') {
                $makerimary = 'Yes';
                $sql        = " UPDATE 
								" . DB_PREFIX . "user_shipping 
							SET 
								addressPrimary='No' 
							WHERE 
								userID='" . $usersID . "'";
                $this->ObjDatabase->setQuery($sql);
            }
            $sql = " INSERT INTO " . DB_PREFIX . "user_shipping
							(
								userID,
								shippingFname,
								shippingLname,
								shippingAddress1,
								shippingAddress2,
								shippingCity,
								shippingZip,
								shippingCountryID,
								shippingStateID,
								shippingProvince,
								addressPrimary,
                                shippingPhone
							)
							VALUES
							(
								'" . $usersID . "',
								'" . $shippingFname . "',
								'" . $shippingLname . "',
								'" . $shippingAddress1 . "',
								'" . $shippingAddress2 . "',
								'" . $shippingCity . "',
								'" . $shippingZip . "',
								'" . $shippingCountryID . "',
								'" . $shippingstateID . "',
								'" . $shippingProvince . "',
								'" . $makerimary . "',
                                '" .$userCountrycode.'-'. $shippingPhone . "'
							)";
            
           $status='new';
            
        }
        $this->ObjDatabase->setQuery($sql);
        if($status=='new') {
            $userShippingID = $this->ObjDatabase->getInsertId();
            if($typeret!='') {
                return $userShippingID;
    }
        }        
        return $status;
    } 
    //GET SHIPPING RATES
    public function getShippingRateDetails($productID) {
        $sql              = " SELECT 
						* 
					FROM 
						" . DB_PREFIX . "shipping_rates 
					WHERE 
						productID='" . $productID . "'";
        $resShippingRates = $this->ObjDatabase->readValue($sql);
        return $resShippingRates;
    }
    //UPDATE PAYMENT NOTES
    public function updatePaymentNotes($POST, $masterOrderID) {
        $fields       = '';
        $PaymentNotes = $POST['PaymentNotes'];
        $sql          = " UPDATE 
						" . DB_PREFIX . "order_masters 
					SET 
						paymentNotes='" . $PaymentNotes . "'";
        $fields .= " WHERE 
						masterOrderID ='" . (int) $masterOrderID . "' ";
        $resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql . $fields);
        return $resUpdateOrderStatus;
    }
    //CHANGE SHIPMENT STATUS
    function changeShipmentStatus($POST, $orderItemID = '', $source = 'admin') {
        $sql                  = " SELECT 
						orderItemQuantity 
					FROM 
						" . DB_PREFIX . "order_items ordIte 
					WHERE 
						orderItemID='" . $orderItemID . "'";
        $resOrderItemQuantity = $this->ObjDatabase->readValue($sql);
        $orderItemQuantity    = $resOrderItemQuantity['orderItemQuantity'];
        $sql                  = " SELECT 
						sum(quantity) as quantity
					FROM 
						" . DB_PREFIX . "shipment_items shiIte 
					WHERE 
						orderItemID='" . $orderItemID . "'";
        $resQuantity          = $this->ObjDatabase->readValue($sql);
        $quantity             = $resQuantity['quantity'];
        if ($orderItemQuantity <= $quantity) {
            if ($orderItemID != '') {
                if ($this->checkIsOrderActive($orderItemID)) {
                    $shipmentStatus = $POST['shipmentStatus'];
                    $careerDetails  = $this->escape($POST['CareerDetails']);
                    $sql            = " UPDATE 
									" . DB_PREFIX . "order_items 
								SET ";
                    $fields         = '';
                    if ($careerDetails != '') {
                        $fields .= " carrierDetails='" . $careerDetails . "'";
                    }
                    if ($shipmentStatus != '') {
                        if ($fields != '') {
                            $fields .= ", ";
                        }
                        $fields .= " shipmentStatus='" . $shipmentStatus . "', shipmentDate  = NOW()";
                    }
                    if ($fields != '') {
                        $sql .= $fields;
                        $sql .= " WHERE 
									orderItemID ='" . (int) $orderItemID . "' ";
                        if ($source == 'business') {
                            $sql .= " AND 
									shippingSetting ='business' ";
                        }
                        $resUpdateShipStatus = $this->ObjDatabase->setQuery($sql);
                    }
                    return $resUpdateShipStatus;
                }
            }
        }
    }
    function selectCareerDetails($orderID) {
        $sql                    = " SELECT 
						* 
					FROM 
						" . DB_PREFIX . "order_masters 
					WHERE 
						masterOrderID='" . (int) $orderID . "'";
        $resSelectCareerDetails = $this->ObjDatabase->readValue($sql);
        return $resSelectCareerDetails;
    }
    //NOW DEAL BEGIN
    //For checking if the session city id existing for the purchased product [Now product]
    function checkCityIdExistForProduct($cityID, $currProductID) {
        $sql       = " SELECT 
						cityID 
					FROM 
						" . DB_PREFIX . "product_locations 
					WHERE 
						productID ='" . $currProductID . "' 
						AND cityID='" . $cityID . "'";
        $resCityID = $this->ObjDatabase->readValue($sql);
        return $resCityID;
    }
    // NOW DEAL END	
    public function updateOrderHold($masterOrderID, $orderHold = 'No') {
        $sql          = " UPDATE 
	 				" . DB_PREFIX . "order_masters 
				SET 
					orderHold = '" . $orderHold . "' 
				WHERE 
					masterOrderID= '" . (int) $masterOrderID . "'";
        $resHoldOrder = $this->ObjDatabase->setQuery($sql);
        return $resHoldOrder;
    }
    public function get_string_between($string, $start, $end) {
        $string = " " . $string;
        $ini    = strpos($string, $start);
        if ($ini == 0)
            return "";
        $ini += strlen($start);
        $len = strpos($string, $end, $ini) - $ini;
        return substr($string, $ini, $len);
    }
    //GET DEAL TYPE OF PRODUCTS
    function getProductTypeOfProducts($productTypeKey) {
        $sql                  = " SELECT 
	  	 				productTypeID 
	  	 			FROM 
	  	 				" . DB_PREFIX . "product_types 
	  	 			WHERE 
	  	 				productTypeKey='" . $productTypeKey . "' 
	  	 				AND productTypeStatus='Active'";
        $resSelectProductType = $this->ObjDatabase->readValues($sql);
        return $resSelectProductType;
    }
    //GET PRODUCT ORDER DETAILS
    function getProductOrderDetails($languageIDs, $orderID = "", $orderItemID = "", $masterOrderID = "") {
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        $sql               = " SELECT 
						ordIte.*,
						ordMas.shippingAmount,
						ordMas.orderTotalAmount,
						dd.productTitle,
						ordMas.*,
						do.* ,
						bus.*,
						busDet.businessName,
						CONCAT_WS('~*',
							busLocDet.locAddress1,
							busLocDet.locAddress2,
							busLocDet.locCity,
							busLocDet.locProvince,
							busLoc.locZip,
							busLoc.locStateID,
							busLoc.locCountryID
						) AS address,
						
                                                busShiHisCom.comment,	
						busShiHis.trackingNumber
					FROM 
						" . DB_PREFIX . "order_masters ordMas 
					LEFT JOIN 
						" . DB_PREFIX . "order_items ordIte 
						ON ordMas.masterOrderID=ordIte.masterOrderID
					LEFT JOIN 
						" . DB_PREFIX . "products d 
						ON d.productID=ordIte.productID
					LEFT JOIN 
						" . DB_PREFIX . "product_details dd 
						ON (ordIte.productID = dd.productID AND dd.languageID = '" . $currentLanguageID . "')
					LEFT JOIN 
						" . DB_PREFIX . "product_options do 
						ON (d.productID = do.productID AND ordIte.productOptionID=do.productOptionID) 
					LEFT JOIN " . DB_PREFIX . "business bus
						ON bus.businessID=ordMas.businessID
					LEFT JOIN " . DB_PREFIX . "business_details busDet
						ON busDet.businessID=ordMas.businessID
					LEFT JOIN 
						" . DB_PREFIX . "business_locations busLoc
							ON(
								bus.businessID=busLoc.businessID
							  )
					LEFT JOIN 
						" . DB_PREFIX . "business_location_details busLocDet
							ON(
								busLoc.businessLocID = busLocDet.businessLocID 
								AND busLocDet.languageID = '" . $currentLanguageID . "'
							  )	
					LEFT JOIN (SELECT * FROM 
						" . DB_PREFIX . "shipments busShiHis
						WHERE 1
						AND trackingNumber<>'' ORDER BY shippingID DESC) busShiHis ON busShiHis.masterOrderID=ordMas.masterOrderID
					LEFT JOIN (SELECT * FROM 
						" . DB_PREFIX . "shipments busShiHisCom
						WHERE 1
						AND busShiHisCom.comment<>'' ORDER BY shippingID DESC) busShiHisCom ON busShiHisCom.masterOrderID=ordMas.masterOrderID				  
					WHERE isLocationBasic='Yes' ";
        if ($orderID != '') {
            $sql .= " AND ordMas.masterOrderID IN (" . $orderID . ") ";
        }
        if ($orderItemID != '' || is_array($orderItemID)) {
            if (is_array($orderItemID)) {
                $orderItemIDs = implode(',', $orderItemID);
                $sql .= " AND ordIte.orderItemID IN (" . $orderItemIDs . ") ";
            } else
                $sql .= " AND ordIte.orderItemID='" . $orderItemID . "' ";
        }
        if ($masterOrderID != '') {
            $sql .= " AND ordMas.masterOrderID IN (" . $masterOrderID . ") ";
        }
        $sql .= " GROUP BY 
						ordIte.orderItemID  ";
        $resOrderProductDetails = $this->ObjDatabase->readValues($sql);
        return $resOrderProductDetails;
    }
    //SAVE SHIPPING HISTORY
    function saveShippingHistory($POST, $orderItemID, $source = 'admin') {
        $ShipmentID = $POST['ShipmentID'];
        if ($source == 'business') {
            $sql                      = " SELECT 
						shippingSetting
					FROM 
						" . DB_PREFIX . "order_items ordIte
					WHERE 
						ordIte.orderItemID='" . $orderItemID . "'
					LIMIT 0,1";
            $resSelectshippingSetting = $this->ObjDatabase->readValue($sql);
            $shippingSettings         = $resSelectshippingSetting['shippingSetting'];
            $proceed                  = ($shippingSettings == 'business') ? 'Yes' : 'No';
        } else {
            $proceed = 'Yes';
        }
        if ($proceed == 'Yes') {
            $sql              = " INSERT INTO " . DB_PREFIX . "shipment_items
							(
								shippingID,
								orderItemID,
								quantity
							)
							VALUES
							(
								'" . $ShipmentID . "',
								'" . $orderItemID . "',
								'" . $POST['shippingQuantity'] . "'
							)";
            $resInsertHistory = $this->ObjDatabase->setQuery($sql);
            return true;
        } else {
            return false;
        }
    }
    //SAVE SHIPMENT
    function saveShipMent($POST) {
        $comment          = $this->escape($POST['comment']);
        $masterOrderID    = $POST['masterOrderID'];
        $createdBy        = ($POST['createdBy'] != '') ? $POST['createdBy'] : 'admin';
        $sql              = " INSERT INTO " . DB_PREFIX . "shipments
						(
							activityDate,
							comment,
							masterOrderID,
							shippingStatus,
							trackingNumber,
							carrier,
							service_type,
							gross_weight,
							shipment_created_date,
							createdBy,
							shipment_origin_details,
							shipment_destination_address,	
							pickup_created_date,
							pickup_date_time,
							pickup_closing_time,
							package_details,
							drop_off_type,
							delivered_date,							
							delivered_comment,
							delivered_notify_customer,
							dispatch_date,
							dispatch_exp_delivery_date,
							dispatch_status_details,
							dispatch_notify_customer,
							extra,
							other_logistic_partner,
							other_tracking_url
						)
						VALUES
						(
							NOW(),
							'" . $comment . "',
							'" . $masterOrderID . "',
							'" . $POST['shipmentStatus'] . "',
							'" . $this->escape($POST['trackingNumber']) . "',
							'" . $POST['sm_main'] . "',
							'" . $POST['realtime_sm_services'] . "',
							'" . $POST['grossWeight'] . "',
							NOW(),
							'" . $createdBy . "',
							'" . $POST['shipmentOriginDetails'] . "',
							'" . $POST['shipmentDestination'] . "',
							" . $POST['pickup_created_time'] . ",
							'" . $POST['pickupDate'] . "',
							'" . $POST['pickup_closing_time'] . "',
							'" . $POST['packageType'] . "',
							'" . $POST['dropOffType'] . "',
							'" . $POST['deliveredDate'] . "',							
							'" . $this->escape($POST['deliveredComment']) . "',
							'" . $POST['deliveredNotify'] . "',
							'" . $POST['dispatchDate'] . "',
							'" . $POST['dispatchExpDate'] . "',
							'" . $this->escape($POST['dispatchStatusDetails']) . "',
							'" . $POST['dispatchNotify'] . "',
							'" . $POST['extraData'] . "',
							'" . $this->escape($POST['partnerName']) . "',
							'" . $this->escape($POST['trackingUrl']) . "'
						)";
        $resInsertHistory = $this->ObjDatabase->setQuery($sql);
        $shipmentID       = $this->ObjDatabase->getInsertId();
        return $shipmentID;
    }
    //LIST SHIPPING HISTORY
    function getShippingHistory($masterOrderID = '', $orderItemID = 0, $limit = 0, $languageIDs = array(),$businessID=0,$type='') {
        $languageID = isset($languageIDs['Current']) ? $languageIDs['Current'] : '';
        $sql        = " SELECT 
					ship.*,
					shipMeth.settingsGroupXmlLabel as logisticPartner,
					shiIte.quantity as quantity
				FROM 
					" . DB_PREFIX . "shipments  ship
				LEFT JOIN " . DB_PREFIX . "shipping_methods shipMeth 
					ON (ship.carrier = shipMeth.settingsGroupID)
				LEFT JOIN 
					" . DB_PREFIX . "shipment_items shiIte 
					ON (shiIte.shippingID=ship.shippingID)				
				LEFT JOIN 
					" . DB_PREFIX . "order_masters ordMas 
					ON (ordMas.masterOrderID=ship.masterOrderID)
				LEFT JOIN 
					" . DB_PREFIX . "order_items ordIte 
					ON (shiIte.orderItemID=ordIte.orderItemID)";
        if ($masterOrderID > 0) {
            $sql .= "WHERE 
							ordMas.masterOrderID='" . $masterOrderID . "' ";
        }
        if ($orderItemID > 0) {
            $sql .= " AND shiIte.orderItemID='" . $orderItemID . "' ";
        }
       if (isset($businessID) && $businessID > 0) {
            $sql .= " AND ordMas.businessID='" . $businessID . "' ";
        }		
        $sql .= " GROUP BY  ship.shippingID ORDER BY 
					ship.masterOrderID DESC ,
					ship.shippingID DESC ";
        if ($limit > 0) {
            $sql .= " LIMIT 0," . $limit . " ";
        }
        if($type!='') {
            $resSelectHistory = $this->ObjDatabase->readValue($sql);
            return $resSelectHistory;
        } else {
            $resSelectHistory = $this->ObjDatabase->readValuesSlave($sql);
        }
        if (count($resSelectHistory) > 0) {
            foreach ($resSelectHistory as $key => $array) {
                $sql                             = " SELECT 
					shiIte.*,
					ordIte.*,
					busDet.businessName,
					deaOpt.optionSKU,
					deaDet.productTitle
				FROM 
					" . DB_PREFIX . "shipments  ship
				LEFT JOIN 
					" . DB_PREFIX . "shipment_items shiIte 
					ON (shiIte.shippingID=ship.shippingID)
				LEFT JOIN 
					" . DB_PREFIX . "order_masters ordMas 
					ON (ordMas.masterOrderID=ship.masterOrderID)
				LEFT JOIN 
					" . DB_PREFIX . "order_items ordIte 
					ON (shiIte.orderItemID=ordIte.orderItemID) 
				LEFT JOIN 
					" . DB_PREFIX . "products dea 
					ON (dea.productID=ordIte.productID)
				LEFT JOIN 
					" . DB_PREFIX . "product_details deaDet
					ON (deaDet.productID=dea.productID AND deaDet.languageID='" . $languageID . "')	
				LEFT JOIN 
					" . DB_PREFIX . "product_options deaOpt 
					ON (deaOpt.productOptionID=ordIte.productOptionID)	
				LEFT JOIN 
					" . DB_PREFIX . "business_details busDet 
					ON (busDet.businessID=ordMas.businessID AND busDet.languageID='" . $languageID . "')	
				WHERE 
					shiIte.shippingID='" . $array['shippingID'] . "' 
				GROUP BY shiIte.shipmentItemID";
                $resSelectHistoryItems           = $this->ObjDatabase->readValuesSlave($sql);
                $resSelectHistory[$key]['items'] = $resSelectHistoryItems;
            }
        }
        return $resSelectHistory;
    }
    public function getOrderItemShippingHistory($orderItemID = 0, $statusArray = array()) {
        $sql = " SELECT 
	   				ship.*,
	   				shiIte.*
	   			FROM 
	   				" . DB_PREFIX . "shipments  ship
				LEFT JOIN 
	   				" . DB_PREFIX . "shipment_items shiIte 
	   				ON (shiIte.shippingID=ship.shippingID)				
	   			LEFT JOIN 
	   				" . DB_PREFIX . "order_masters ordMas 
	   				ON (ordMas.masterOrderID=ship.masterOrderID)
				LEFT JOIN 
					" . DB_PREFIX . "order_items ordIte 
					ON (shiIte.orderItemID=ordIte.orderItemID) 
				WHERE 
					shiIte.orderItemID='" . $orderItemID . "' AND ";
        if (count($statusArray) > 0) {
            $sql .= " ship.shippingStatus IN ('" . implode("','", $statusArray) . "') ";
        }
        $sql .= " GROUP BY shiIte.shippingID ORDER BY 
	   				shiIte.orderItemID DESC ,
	   				shiIte.shippingID DESC ";
        if (isset($limit) && $limit > 0) {
            $sql .= " LIMIT 0," . $limit . " ";
        }
        $resSelectHistory = $this->ObjDatabase->readValues($sql);
        return $resSelectHistory;
    }
    //Get last order by a user
    public function getlastOrderByUser($userID, $type, $includeOrderHold = 'No', $checkDate = 'No') {
        $sql = " 
					SELECT 
						ordMas.orderUserCreditAmount,
						ordMas.orderHold,
						ordMas.orderNumber
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					WHERE 
						ordMas.userID='" . $userID . "' 
						AND ordMas.orderType='" . $type . "'";
        if ($includeOrderHold == 'Yes') {
            $sql .= " 
						AND ordMas.orderHold='Yes' 
						AND ordMas.orderStatus='New'";
        }
        if ($checkDate == 'Yes') {
            $sql .= "
						AND ordMas.orderCreatedDate >= NOW( ) - INTERVAL 10 
MINUTE";
        }
        $sql .= " 
					ORDER BY 
						ordMas.masterOrderID DESC 
					LIMIT 0,1";
        $resSelectOrders = $this->ObjDatabase->readValue($sql);
        return $resSelectOrders;
    }
    //GET DEAL TITLES OF ORDERID
    public function getOrderProductTitles($languageIDs, $masterOrderID) {
        $currLanguageID   = $languageIDs['Current'];
        $sql              = " SELECT 
						GROUP_CONCAT(DISTINCT productDet.productTitle) AS productTitle
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					LEFT JOIN 
						" . DB_PREFIX . "order_items ordIte 
						ON ordMas.masterOrderID=ordIte.masterOrderID
					LEFT JOIN 
						" . DB_PREFIX . "products product 
						ON ordIte.productID=product.productID
					LEFT JOIN 
						" . DB_PREFIX . "product_details productDet 
						ON productDet.productID=product.productID 
						AND productDet.languageID='" . $currLanguageID . "'
					WHERE 
						ordMas.masterOrderID='" . $masterOrderID . "' ";
        $resProductTitles = $this->ObjDatabase->readValue($sql);
        return $resProductTitles;
    }
    //Get Revenue details
    public function getTotalSalesAmount() {
        $sql = " 
					SELECT 
						SUM(convertedMasterOrderTotal) AS salesAmount
					FROM 
						" . DB_PREFIX . "order_masters ordMas 
					WHERE 
						(ordMas.paymentStatus='Received' OR ordMas.paymentStatus='Pending') 
						AND  
						(ordMas.orderStatus='Completed' OR ordMas.orderStatus='Process')
						AND ordMas.orderType  =  'product'";
        return $this->ObjDatabase->getField($sql);
    }
    public function changeMasterStatus($POST, $masterOrderID) {
        if ($masterOrderID > 0) {
            $sql = " UPDATE 
							" . DB_PREFIX . "order_masters 
						SET ";
            $fields .= ($POST['masterOrderStatus'] != '') ? " orderStatus='" . $POST['masterOrderStatus'] . "' ," : "";
            $fields .= ($POST['masterPaymentStatus'] != '') ? " paymentStatus='" . $POST['masterPaymentStatus'] . "' ," : "";
            $fields .= " orderStatusDate=NOW() ";
            $fields .= " WHERE masterOrderID ='" . $masterOrderID . "' ";
            $resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql . $fields);
        }
    }
    // checking order is active or not
    public function checkIsOrderActive($orderItemID) {
        $sql            = " SELECT 
						ordMas.paymentStatus,
						ordMas.orderStatus
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					JOIN 
						" . DB_PREFIX . "order_items ordIte 
						ON (ordMas.masterOrderID=ordIte.masterOrderID)
					WHERE 
						ordIte.orderItemID='" . $orderItemID . "'";
        $resCheckActive = $this->ObjDatabase->readValue($sql);
        if (($resCheckActive['orderStatus'] != 'Process' && $resCheckActive['orderStatus'] != 'Completed') || ($resCheckActive['paymentStatus'] != 'Pending' && $resCheckActive['paymentStatus'] != 'Received')) {
            return FALSE;
        } else {
            return TRUE;
        }
    }
    // shipment status pending order items
    public function checkPendingProducts($masterOrderID) {
        $sql        = " SELECT 
						ordIte.orderItemID
					FROM 
						" . DB_PREFIX . "order_items ordIte
					JOIN 
						" . DB_PREFIX . "order_masters ordMas 
						ON (ordMas.masterOrderID=ordIte.masterOrderID)
					WHERE 
						ordMas.masterOrderID='" . $masterOrderID . "' 
						AND ordIte.shipmentStatus='Pending'";
        $resPending = $this->ObjDatabase->readValues($sql, MYSQLI_NUM);
        return count($resPending);
    }
    //Function to update sold quantity
    public function updateSoldQuantity($masterOrderID, $newStatus, $currStatus) {
        $returnArray             = array();
        $returnVar               = 0;
        $validOrderStatusArray   = array(
            'Process',
            'Completed'
        );
        $validPaymentStatusArray = array(
            'Pending',
            'Received'
        );
        if ($masterOrderID > 0) {
            $newStatusClass  = "";
            $currStatusClass = "";
            if ($currStatus['masterPaymentStatus'] == $newStatus['masterPaymentStatus'] && $currStatus['masterOrderStatus'] == $newStatus['masterOrderStatus'])
                return $returnArray;
            if (in_array($newStatus['masterOrderStatus'], $validOrderStatusArray) && in_array($newStatus['masterPaymentStatus'], $validPaymentStatusArray))
                $newStatusClass = "valid";
            else
                $newStatusClass = "invalid";
            if (in_array($currStatus['masterOrderStatus'], $validOrderStatusArray) && in_array($currStatus['masterPaymentStatus'], $validPaymentStatusArray))
                $currStatusClass = "valid";
            else
                $currStatusClass = "invalid";
            if ($newStatusClass == $currStatusClass)
                return $returnArray;
            //Select item quantity 
            /*$sql                 = "SELECT
						ordIte.productOptionID,
						ordIte.productID,
						SUM(ordIte.orderItemQuantity) AS orderItemQuantity,
						productOpt.productOptionQuantity,
						productOpt.productQuantityUnlimited,
                                                products.InventoryTrackLevel,
                                                ordIte.combinationHash
					FROM
						" . DB_PREFIX . "order_items ordIte
					LEFT JOIN " . DB_PREFIX . "order_masters ordMas
						ON ordMas.masterOrderID = ordIte.masterOrderID
					LEFT JOIN " . DB_PREFIX . "product_options productOpt						
						ON productOpt.productOptionID = ordIte.productOptionID	
                                        LEFT JOIN " . DB_PREFIX . "products products						
						ON products.productID = ordIte.productID	
					WHERE
						ordMas.masterOrderID = '" . $masterOrderID . "'
					GROUP BY 
						ordIte.productOptionID";*/
            $sql                 = "SELECT
						ordIte.productOptionID,
						ordIte.productID,
						ordIte.orderItemQuantity,
						productOpt.productOptionQuantity,
						productOpt.productQuantityUnlimited,
                                                products.InventoryTrackLevel,
                                                proCom.combinationQty, 
                                                ordIte.combinationHash
					FROM
						" . DB_PREFIX . "order_items ordIte
					LEFT JOIN " . DB_PREFIX . "order_masters ordMas
						ON ordMas.masterOrderID = ordIte.masterOrderID
					LEFT JOIN " . DB_PREFIX . "product_options productOpt						
						ON productOpt.productOptionID = ordIte.productOptionID	
                                        LEFT JOIN " . DB_PREFIX . "products products						
						ON products.productID = ordIte.productID	
                                        LEFT JOIN ".DB_PREFIX."product_combination AS proCom ON (proCom.productID=ordIte.productID AND proCom.status='A' and proCom.combinationHash=ordIte.combinationHash)                        
					WHERE
						ordMas.masterOrderID = '" . $masterOrderID . "'";
            $resProductOptionIds = $this->ObjDatabase->readValues($sql);
            if (!empty($resProductOptionIds)) {
                // Valid Updation
                if ((in_array($newStatus['masterOrderStatus'], $validOrderStatusArray)) && (in_array($newStatus['masterPaymentStatus'], $validPaymentStatusArray))) {
                    foreach ($resProductOptionIds as $resProductoption) {
                        if ($resProductoption['InventoryTrackLevel']=='Product' && $resProductoption['productQuantityUnlimited'] < 1 && $resProductoption['productOptionQuantity'] >= 0 && $resProductoption['productOptionQuantity'] < $resProductoption['orderItemQuantity']) {
                            $returnArray['success'] = 'No';
                            return $returnArray; // Quantity Insufficient
                        }else if ($resProductoption['InventoryTrackLevel']=='Options' && $resProductoption['combinationQty'] < $resProductoption['orderItemQuantity']) {
                            $returnArray['success'] = 'No';
                            return $returnArray; // Quantity Insufficient
                        }
                    }
                    foreach ($resProductOptionIds as $resProductoption) {
                        //Decrement the Quantity , Increment Sold Quantity
                        if($resProductoption['InventoryTrackLevel']=='Options') {
                            $sql                = " UPDATE 
										" . DB_PREFIX . "product_combination
									SET 
										combinationQty = (( (CAST(combinationQty AS  UNSIGNED INTEGER)) 
                                            - ( CAST('" . (int) $resProductoption['orderItemQuantity'] . "' AS  UNSIGNED INTEGER))))
									WHERE 
										combinationHash = " . $resProductoption['combinationHash'] . " 
										LIMIT 1";//AND combinationQty >0 LIMIT 1
                        
                            //echo $sql;		
                            $this->ObjDatabase->setQuery($sql);
                        }
                        $sql = " UPDATE " . DB_PREFIX . "product_options SET ";
                        if($resProductoption['InventoryTrackLevel']=='Product') {
                            $sql .= " productOptionQuantity = (((CAST(productOptionQuantity AS  UNSIGNED INTEGER)) 
                                        -  (  CAST('" . (int) $resProductoption['orderItemQuantity'] . "' AS  UNSIGNED INTEGER)))),";                                        
                            //$sql .= " productOptionQuantity = IF(productOptionQuantity>0,productOptionQuantity - " . (int) $resProductoption['orderItemQuantity'] . ",''),";
                        }
                        $sql .= " soldQuantity = soldQuantity + " . (int) $resProductoption['orderItemQuantity'] . " ";
                        $sql .= " WHERE productOptionID = '" . $resProductoption['productOptionID'] . "'";
                        $this->ObjDatabase->setQuery($sql);
                        $sql = " UPDATE 
										" . DB_PREFIX . "products
									SET 
										productSoldQuantity = productSoldQuantity + " . (int) $resProductoption['orderItemQuantity'] . "	
									WHERE 
										productID = '" . $resProductoption['productID'] . "'";
                        $this->ObjDatabase->setQuery($sql);
                        
                        
                        
                        $returnArray['success'] = 'Yes';
                    }
                    
                } else // Invalid Updation
                    {
                    foreach ($resProductOptionIds as $resProductoption) {
                        //Increment the Quantity , Decrement Sold Quantity
                        if($resProductoption['InventoryTrackLevel']=='Options') {
                            $sql                = " UPDATE 
										" . DB_PREFIX . "product_combination
									SET 
										combinationQty = (( (CAST(combinationQty AS  UNSIGNED INTEGER)) 
                                            + ( CAST('" . (int) $resProductoption['orderItemQuantity'] . "' AS  UNSIGNED INTEGER))))
									WHERE 
										combinationHash = " . $resProductoption['combinationHash'] . " 
										LIMIT 1";//AND combinationQty >0 LIMIT 1
                        
                            //echo $sql;		
                            $this->ObjDatabase->setQuery($sql);
                        }
                        $sql = " UPDATE " . DB_PREFIX . "product_options SET ";
                        if($resProductoption['InventoryTrackLevel']=='Product') {
                            $sql .= " productOptionQuantity = (((CAST(productOptionQuantity AS  UNSIGNED INTEGER)) 
                                        +  (  CAST('" . (int) $resProductoption['orderItemQuantity'] . "' AS  UNSIGNED INTEGER)))),";                                        
                            //$sql .= " productOptionQuantity = IF(productOptionQuantity>0,productOptionQuantity - " . (int) $resProductoption['orderItemQuantity'] . ",''),";
                        }
                        $sql .= " soldQuantity = soldQuantity - " . (int) $resProductoption['orderItemQuantity'] . " ";
                        $sql .= " WHERE productOptionID = '" . $resProductoption['productOptionID'] . "'";
                        $this->ObjDatabase->setQuery($sql);                        
                        $sql = " UPDATE 
										" . DB_PREFIX . "products
									SET 
										productSoldQuantity = productSoldQuantity - " . (int) $resProductoption['orderItemQuantity'] . "	
									WHERE 
										productID = '" . $resProductoption['productID'] . "'";
                        $this->ObjDatabase->setQuery($sql);
                        $returnArray['success'] = 'Yes';
                    }
                }
            }
        }
        return $returnArray;
    }
    public function getShippingCarriers() {
        $sql         = "SELECT
						carrierName,
						carrierName
					FROM
						" . DB_PREFIX . "shipping_carriers shiCar
					WHERE
						shiCar.status = 'A'";
        $resCarriers = $this->ObjDatabase->readValues($sql);
        return $resCarriers;
    }

    public function destroyShippingHistory($shippingID) {
        $sql = "DELETE 
				FROM
					" . DB_PREFIX . "shipments 
				WHERE 
					shippingID='" . $shippingID . "'";
        $this->ObjDatabase->setQuery($sql);
        $sql           = " SELECT 
						orderItemID 
					FROM 
						" . DB_PREFIX . "shipment_items 
					WHERE 
						shippingID='" . $shippingID . "'";
        $resOrderItems = $this->ObjDatabase->readValues($sql);
        $sql           = "DELETE 
				FROM
					" . DB_PREFIX . "shipment_items 
				WHERE 
					shippingID='" . $shippingID . "'";
        $this->ObjDatabase->setQuery($sql);
        if (count($resOrderItems) > 0) {
            foreach ($resOrderItems as $key => $array) {
                $sql                  = " SELECT 
								orderItemQuantity 
							FROM 
								" . DB_PREFIX . "order_items ordIte 
							WHERE 
								orderItemID='" . $array['orderItemID'] . "'";
                $resOrderItemQuantity = $this->ObjDatabase->readValue($sql);
                $orderItemQuantity    = $resOrderItemQuantity['orderItemQuantity'];
                $sql                  = " SELECT 
								sum(quantity) as quantity
							FROM 
								" . DB_PREFIX . "shipment_items shiIte 
							WHERE 
								orderItemID='" . $array['orderItemID'] . "'";
                $resQuantity          = $this->ObjDatabase->readValue($sql);
                $quantity             = $resQuantity['quantity'];
                if ($orderItemQuantity > $quantity) {
                    $sql                 = " UPDATE 
									" . DB_PREFIX . "order_items 
								SET 
									carrierDetails='',
									shipmentStatus='Pending', 
									shipmentDate  = ''
								WHERE 
									orderItemID ='" . (int) $array['orderItemID'] . "'";
                    $resUpdateShipStatus = $this->ObjDatabase->setQuery($sql);
                }
            }
        }
        return true;
    }
    public function getShipmentDetails($shipmentID = 0, $languageIDs, $type = 'view') {
        $languageID = $languageIDs['Current'];
        $sql        = " SELECT 
	   				ship.*,
					ordMas.billingAddress,
					ordMas.shippingAddress,
					ordMas.extraData,
                                        ordMas.orderOtherServiceCharge,
                                        ordMas.orderConvenientFee,
                                        ordMas.orderPromoDiscountAmount,
                                        ordMas.orderPromoCode,
                                        ordMas.orderUserCreditAmount,
                                        ordMas.orderTaxAmount,
                                        ordMas.shippingAmount,
                                        ordMas.purchasedCurrency,
										ordMas.userCurrencyID,
										ordMas.userCurrencyExcRate,
                                        ordMas.orderSubtotal,
                                        ordMas.orderTotalAmount,
					ordMas.orderStatus,
                                        ordMas.dynamicOrderStatus,
                                        ordMas.redeemDiscountAmount,
                                        lan.languageDirection,
					ordMas.orderPaymentMethodName,
					ordMas.orderCreatedDate,
					ordMas.orderNumber,
                                        bus.businessID,  
					busDet.businessName,
					bus.businessFax,
					bus.businessWebsite,
					bus.businessEmail,
					bus.businessPhone,
					CONCAT_WS('~*',
							busLocDet.locAddress1,
							busLocDet.locAddress2,
							busLocDet.locCity,
							busLocDet.locProvince,
							busLoc.locZip,
							busLoc.locStateID,
							busLoc.locCountryID
						) AS address,
					shipMeth.settingsGroupXmlLabel AS logisticPartner
	   			FROM 
	   				" . DB_PREFIX . "shipments  ship
				LEFT JOIN " . DB_PREFIX . "shipping_methods shipMeth
							ON ship.carrier = shipMeth.settingsGroupID
	   			LEFT JOIN 
	   				" . DB_PREFIX . "order_masters ordMas 
	   				ON (ordMas.masterOrderID=ship.masterOrderID)
				LEFT JOIN 
					" . DB_PREFIX . "business bus 
					ON (bus.businessID=ordMas.businessID)	
                                LEFT JOIN 
					" . DB_PREFIX . "languages lan 
					ON (lan.languageID=ordMas.languageID)	         
				LEFT JOIN 
					" . DB_PREFIX . "business_details busDet 
					ON (busDet.businessID=bus.businessID AND busDet.languageID='" . $languageID . "')
				LEFT JOIN 
					" . DB_PREFIX . "business_locations busLoc
					ON(
						bus.businessID=busLoc.businessID
					)
				LEFT JOIN 
					" . DB_PREFIX . "business_location_details busLocDet
					ON(
						busLoc.businessLocID = busLocDet.businessLocID 
						AND busLocDet.languageID = '" . $languageID . "'
					)	
				WHERE ";
        $sql .= ($type == 'view') ? " ship.shippingID='" . $shipmentID . "'" : " ship.shippingID IN (" . $shipmentID . ") ";
        $sql .= " GROUP BY ship.shippingID ";
        $resSelectHistory = ($type == 'view') ? $this->ObjDatabase->readValue($sql) : $this->ObjDatabase->readValues($sql);
        if ($type == 'view') {
            $sql                       = " SELECT 
						shiIte.*,
						ordIte.*,
						busDet.businessName,
						ordIte.shippingSetting,
						deaOpt.optionSKU
					FROM 
						" . DB_PREFIX . "shipments  ship
					LEFT JOIN 
						" . DB_PREFIX . "shipment_items shiIte 
						ON (shiIte.shippingID=ship.shippingID)
					LEFT JOIN 
						" . DB_PREFIX . "order_masters ordMas 
						ON (ordMas.masterOrderID=ship.masterOrderID)
					LEFT JOIN 
						" . DB_PREFIX . "order_items ordIte 
						ON (shiIte.orderItemID=ordIte.orderItemID) 
					LEFT JOIN 
						" . DB_PREFIX . "product_options deaOpt 
						ON (deaOpt.productOptionID=ordIte.productOptionID)
					LEFT JOIN 
						" . DB_PREFIX . "business_details busDet 
						ON (busDet.businessID=ordMas.businessID AND busDet.languageID='" . $languageID . "')	
					WHERE 
						shiIte.shippingID='" . $shipmentID . "' 
					GROUP BY shiIte.shipmentItemID";
            $resSelectHistoryItems     = $this->ObjDatabase->readValues($sql);
            $resSelectHistory['items'] = $resSelectHistoryItems;
        } else {
            if (count($resSelectHistory) > 0) {
                foreach ($resSelectHistory as $key => $array) {
                    $sql                             = " SELECT 
						shiIte.*,
						ordIte.*,
						busDet.businessName,
						deaOpt.optionSKU,
						deaDet.productTitle,
						deaOptDet.productOptionName
					FROM 
						" . DB_PREFIX . "shipments  ship
					LEFT JOIN 
						" . DB_PREFIX . "shipment_items shiIte 
						ON (shiIte.shippingID=ship.shippingID)
					LEFT JOIN 
						" . DB_PREFIX . "order_masters ordMas 
						ON (ordMas.masterOrderID=ship.masterOrderID)
					LEFT JOIN 
						" . DB_PREFIX . "order_items ordIte 
						ON (shiIte.orderItemID=ordIte.orderItemID) 
					LEFT JOIN 
						" . DB_PREFIX . "products dea 
						ON (dea.productID=ordIte.productID)
					LEFT JOIN 
						" . DB_PREFIX . "product_details deaDet
						ON (deaDet.productID=dea.productID AND deaDet.languageID='" . $languageID . "')	
					LEFT JOIN 
						" . DB_PREFIX . "product_options deaOpt 
						ON (deaOpt.productOptionID=ordIte.productOptionID)
					LEFT JOIN 
						" . DB_PREFIX . "product_option_details deaOptDet
						ON (deaOptDet.productOptionID=deaOpt.productOptionID AND deaOptDet.languageID='" . $languageID . "')	
					LEFT JOIN 
						" . DB_PREFIX . "business_details busDet 
						ON (busDet.businessID=ordMas.businessID AND busDet.languageID='" . $languageID . "')	
					WHERE 
						shiIte.shippingID='" . $array['shippingID'] . "' 
					GROUP BY shiIte.shipmentItemID";
                    $resSelectHistoryItems           = $this->ObjDatabase->readValues($sql);
                    $resSelectHistory[$key]['items'] = $resSelectHistoryItems;
                }
            }
        }
        return $resSelectHistory;
    }
    //FETCHING ORDERS
    public function listShipments($params = array()) {
        extract($params);
        $groupBy             = ($groupBy == 'orderItem') ? 'ordIte.orderItemID' : $groupBy;
        $trackingNumber      = (isset($trackingNumber)) ? $trackingNumber : '';
        $shippingMethod      = (isset($shippingMethod)) ? $shippingMethod : '';
        $shippingServiceType = (isset($shippingServiceType)) ? $shippingServiceType : '';
        if ($sortBy == "sorderid") {
            $sortBy = "ordMas.masterOrderID";
        } else if ($sortBy == "scustomer") {
            $sortBy = "usr.userFirstName";
        } else if ($sortBy == "squantity") {
            $sortBy = "ItemQuantity";
        } else if ($sortBy == "stotal") {
            $sortBy = "ordMas.orderTotalAmount";
        } else if ($sortBy == "ssdate") {
            $sortBy = "ordMas.orderStatusDate 	";
        } else if ($sortBy == "shipdate") {
            $sortBy = "ordIte.shipmentDate 	";
        } else if ($sortBy == "sorderdate") {
            $sortBy = "ordMas.orderCreatedDate 	";
        } else if ($sortBy == "stitle") {
            $sortBy = "productDet.productTitle 	";
        } else if ($sortBy == "spstatus") {
            $sortBy = "ordMas.paymentStatus 	";
        } else if ($sortBy == "sostatus") {
            $sortBy = "ordMas.orderStatus 	";
        } else if ($sortBy == "sshipmentid") {
            $sortBy = " shi.shippingID ";
        } else {
            $sortBy = " shi.shippingID ";
        }
        $orderID           = isset($orderID) ? $orderID : '';
        $orderID           = (is_array($orderID)) ? implode(',', $orderID) : $orderID;
        $orderItemID       = (is_array($orderItemID)) ? implode(',', $orderItemID) : $orderItemID;
        $masterOrderID     = (is_array($masterOrderID)) ? implode(',', $masterOrderID) : $masterOrderID;
        $orderStatus       = ($orderStatus == '') ? 'Any' : $orderStatus;
        $orderStatusDate   = "shi.activityDate";
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        $certificateName   = ($certificateName != '') ? $this->escape($certificateName) : '';
        $orderNumber       = ($orderNumber != '') ? $this->escape($orderNumber) : '';
        $productKey        = ($productKey != '') ? $this->escape($productKey) : '';
        $fromDate          = $fromDate ? $fromDate : '';
        $toDate            = $toDate ? $toDate : '';
        $manifest = isset($manifest)?$manifest:'';
        $sql               = " SELECT 
						SQL_CALC_FOUND_ROWS
						shi.*,
						shi.shippingStatus currentShippingStatus,
						ordMas.*,
						sum(shiIte.quantity) as quantity,
						IF(ordMas.userID > 0 ,
							CONCAT(usr.userFirstName,' ',usr.userLastName),
							CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,
		 				SUM(ordIte.orderItemQuantity) as ItemQuantity,
		 				" . $orderStatusDate . " orderStatusDate,
		 				product.*, 
		 				usr.userFirstName, 
		 				usr.userLastName , 
		 				usr.userEmail, 
		 				product.productID,								 
		 				ordMas.shippingAddress AS shippedAddress,
		 				ordMas.billingAddress,
						shipMeth.settingsGroupXmlLabel AS logisticPartner,
						(SELECT shipSer.settingsGroupXmlLabel FROM " . DB_PREFIX . "shipping_methods shipSer
							WHERE shipSer.settingsGroupID = shi.service_type) AS shippingService
		 			FROM 
						" . DB_PREFIX . "shipments  shi
					LEFT JOIN " . DB_PREFIX . "shipping_methods shipMeth
							ON shi.carrier = shipMeth.settingsGroupID

					LEFT JOIN	
		 				" . DB_PREFIX . "shipment_items  shiIte
						ON shiIte.shippingID=shi.shippingID	
					LEFT JOIN	
		 				" . DB_PREFIX . "order_masters  ordMas
						ON ordMas.masterOrderID=shi.masterOrderID						
		 			LEFT JOIN 
		 				" . DB_PREFIX . "order_items ordIte 
		 				ON ordIte.orderItemID=shiIte.orderItemID
		 			LEFT JOIN 
		 				" . DB_PREFIX . "products product 
		 				ON product.productID=ordIte.productID ";
        if ($productKey != '') {
            $sql .= " 
		 			LEFT JOIN 
		 				" . DB_PREFIX . "product_options productOpt 
		 				ON productOpt.productOptionID=ordIte.productOptionID	
					LEFT JOIN 
						" . DB_PREFIX . "product_option_details optDet 
						ON (optDet.productOptionID=ordIte.productOptionID
						AND optDet.languageID = '" . $currentLanguageID . "')
		 			LEFT JOIN 
		 				" . DB_PREFIX . "product_combination proComb 
		 				ON proComb.combinationHash=ordIte.combinationHash		
					LEFT JOIN 
			 				" . DB_PREFIX . "product_details productDet 
			 				ON (productDet.productID=product.productID 
			 				AND productDet.languageID = '" . $currentLanguageID . "')";
        }
        $sql .= " LEFT JOIN 
		 				" . DB_PREFIX . "users usr 
		 				ON usr.userID=ordMas.userID
		 			LEFT JOIN 
		 				" . DB_PREFIX . "product_types productTyp 
		 				ON (product.productTypeID = productTyp.productTypeID)
		 			WHERE 
		 				ordMas.orderType='product'
		 				AND ordMas.orderNumber!='' 
		 				AND ordMas.orderRemoved='No' ";
        if (isset($productID) && $productID != '') {
            $sql .= " AND ordIte.productID='" . (int) $productID . "' ";
        }
        if ($orderNumber != '') {
            $sql .= " AND ordMas.orderNumber ='" . $orderNumber . " '";
        }
        if ($masterOrderID != '') {
            $sql .= " AND ordMas.masterOrderID IN (" . $masterOrderID . ")";
        }
        if ($orderItemID != '') {
            $sql .= " AND ordIte.orderItemID IN (" . $orderItemID . ")";
        }
        if ($fromDate != '' && $toDate != '') {
            $sql .= " AND date(shi.activityDate) BETWEEN '" . $fromDate . "' AND '" . $toDate . "' ";
        } else if ($fromDate != '') {
            $sql .= " AND date(shi.activityDate) = '" . $fromDate . "' ";
        } else if ($toDate != '') {
            $sql .= " AND date(shi.activityDate) = '" . $toDate . "' ";
        }
        if ($searchBusiness) {
            $searchBusiness = preg_replace('/[^:0-9]|[:,;\']$/s', "", $searchBusiness);
            $searchBusiness = implode("','", explode(":", $searchBusiness));
            $sql .= " AND ordMas.businessID IN ('" . $searchBusiness . "')";
        }
        if ($productKey != '') {
            $sql .= " AND (productDet.productTitle LIKE '%" . $productKey . "%'
							OR productOpt.optionSKU LIKE '%" . $productKey . "%'
							OR optDet.productOptionName LIKE '%" . $productKey . "%'
							OR proComb.combinationSKU LIKE '%" . $productKey . "%'
							)";
        }
        if ($orderStatus == 'Any') {
           // $sql .= " AND ordMas.orderStatus!='New' ";
        } elseif ($orderStatus != '') {
            $sql .= " AND ordMas.orderStatus='" . $orderStatus . "'";
        } else {
            $sql .= " AND ordMas.orderStatus='Process' ";
        }
        if ($shipmentStatus != 'Shipped' && $shipmentStatus != '') {
            $sql .= " AND shi.shippingStatus='" . $shipmentStatus . "'";
        }
        if ($shippingMethod != '') {
            $sql .= " AND shi.carrier='" . $shippingMethod . "'";
        }
        if ($shippingServiceType != '') {
            $sql .= " AND shi.service_type='" . $shippingServiceType . "'";
        }
        if ($trackingNumber != '') {
            $sql .= " AND shi.trackingNumber ='" . $this->escape($trackingNumber) . "'";
        }
        if ($certificateName != '') {
            $sql .= " AND ( usr.userFirstName LIKE '%" . $certificateName . "%' 
						OR usr.userLastName LIKE
						'%" . $certificateName . "%' 
						OR CONCAT(usr.userFirstName,' ',usr.userLastName) 
						LIKE '%" . $certificateName . "%' ) ";
        }
        if (strpos($paymentStatus, '|') !== false) {
            $statuses = explode('|', $paymentStatus);
            $stString = '(';
            $or       = '';
            foreach ($statuses as $status) {
                $stString .= $or . " ordMas.paymentStatus='" . $status . "' ";
                $or = 'OR';
            }
            $stString .= ')';
            $sql .= " AND " . $stString;
        } elseif ($paymentStatus != '') {
            $sql .= " AND ordMas.paymentStatus='" . $paymentStatus . "'";
        }
        if ($userID > 0) {
            $sql .= " AND ordMas.userID='" . $userID . "'";
        }
        if ($shippingSetting) {
            $sql .= " AND ordIte.shippingSetting='" . $shippingSetting . "'";
        }
        if($manifest!=''){
             $sql .= " AND manifestCreated='".$manifest."' ";
        }
        if ($groupBy != '') {
            $sql .= " GROUP BY " . $groupBy;
        } else {
            $sql .= " GROUP BY shi.shippingID  ";
        }
        if ($sortBy) {
            $sql .= " ORDER BY " . $sortBy . " ";
        } else {
            $sql .= " ORDER BY shi.shippingID ";
        }
        if ($orderAs) {
            $sql .= "  $orderAs";
        } else {
            $sql .= "  DESC";
        }
        if (isset($limitTo) && $limitTo != '') {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        if (isset($export)) {
            return $this->ObjDatabase->readValuesObject($sql, MYSQLI_ASSOC);
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    public function getSameTrasactionOrder($parentOrderID = 0, $masterOrderID = 0) {
        if ($parentOrderID > 0) {
            $sql = " SELECT 
							ordMas.masterOrderID,
							ordMas.orderNumber
						FROM 
							" . DB_PREFIX . "order_masters ordMas
						WHERE 
							masterOrderID ='" . $parentOrderID . "' OR (masterOrderID!=" . $masterOrderID . " AND parentOrderID=" . $parentOrderID . ")";
            return $this->ObjDatabase->readValues($sql);
        } else {
            $sql = " SELECT 
							ordMas.masterOrderID,
							ordMas.orderNumber
						FROM 
							" . DB_PREFIX . "order_masters ordMas
						WHERE 
							parentOrderID ='" . $masterOrderID . "'";
            return $this->ObjDatabase->readValues($sql);
        }
    }
    public function updateGuestUsersReferral($referralID = '', $masterOrderID = '', $purchaseStatus = '', $referralAmount = '', $referralStatus = '') {
        $getReferredByUser = $this->getReferredByUser(0, $referralID);
        if ($getReferredByUser['referralStatus'] != 'Active' && $getReferredByUser['referralStatus'] != '') {
            $referralID = $getReferredByUser['referrerID'];
            $referredBy = $getReferredByUser['referedBY'];
            $sql        = " UPDATE 
							" . DB_PREFIX . "users_referrals 
						SET 
							referedTo='0' ";
            if ($purchaseStatus != '') {
                $sql .= " ,purchaseStatus='" . $purchaseStatus . "' ";
            }
            if ($referralAmount != '') {
                $sql .= " ,referralAmount='" . $referralAmount . "'";
            }
            if ($referralStatus != '') {
                $sql .= " ,referralStatus='" . $referralStatus . "', 
							referalRegActiveDate=Now() ,
							LastActDate=Now()";
            }
            $sql .= " WHERE 
							referedTo=0 ";
            if ($referralID != '') {
                $sql .= " AND referrerID=" . (int) $referralID . " ";
            }
            $sql .= "LIMIT 1 ";
            $rsUpdateUsersReferral = $this->ObjDatabase->setQuery($sql);
            if ($rsUpdateUsersReferral) {
                if ($referralStatus == '') {
                    $this->saveReferralPurchase($referralID, $masterOrderID);
                } elseif ($referralStatus == 'Active') {
                    $getOrderNumber  = $this->getOrderDetails('', '', '', $masterOrderID);
                    $transactionDesc = 'lbl_amount_credited' . ':' . $getOrderNumber['orderNumber'];
                    $this->saveUserTransaction($referredBy, $getReferredByUser['referralAmount'], $transactionDesc, 'Credit');
                }
            }
        }
        return $referralID;
    }
    //LIST GUEST USER ORDERS
    public function listGuestUserOrders($limitFrom = 0, $limitTo = "", $orderStatus = '', $userID = '', $orderType = '', $languageIDs = array(), $orderNumber = 0, $guestEmail) {
        $languageID        = isset($languageIDs['Current']) ? $languageIDs['Current'] : 0;
        $defaultLanguageID = isset($languageIDs['Default']) ? $languageIDs['Default'] : 0;
        $sql               = " 
					SELECT 
						SQL_CALC_FOUND_ROWS 
						ordMas.masterOrderID,
						ordMas.orderNumber,
						ordMas.orderType,
						ordMas.orderCreatedDate,
						ordMas.orderStatus,
						ordMas.orderTotalAmount,
						ordMas.purchasedCurrency,
						ordMas.orderQty,
						ordMas.userID,
						ordMas.orderPromoDiscountAmount,
						ordMas.orderPaymentMethodName,
						ordMas.orderSubtotal,
						ordMas.orderOtherServiceCharge,
						ordMas.orderUserCreditAmount,
						ordMas.orderTaxAmount,
						ordMas.shippingAmount,
						IF(ordMas.userID > 0 ,CONCAT(usr.userFirstName,' ',usr.userLastName),CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,
						ordIte.shipmentStatus,
                                                ordMas.dynamicOrderStatus,
                                                ordMas.invoiceNumber,
                                                ordMas.invoiceCreatedDate
						";
        if ($languageID > 0) {
            $sql .= "
						, busDet.businessName ";
        }
        $sql .= "				
					FROM 
						" . DB_PREFIX . "order_masters ordMas
					LEFT JOIN 
						" . DB_PREFIX . "order_items AS ordIte
						ON ordIte.masterOrderID = ordMas.masterOrderID
					LEFT JOIN 
						" . DB_PREFIX . "users usr 
						ON usr.userID=ordMas.userID";
        if ($languageID > 0) {
            $sql .= "
					LEFT JOIN 
						" . DB_PREFIX . "business_details busDet 
						ON( busDet.businessID=ordMas.businessID
						AND busDet.languageID=" . (int) $languageID . ")";
        }
        $sql .= "			
					WHERE 
						ordMas.orderNumber!='' 
						
						";
        if ($orderStatus != '') {
            $sql .= " AND (ordMas.orderStatus='Process' 
							OR ordMas.orderStatus='Completed') ";
        } else {
            $sql .= " AND (ordMas.orderStatus!='New' ) ";
        }
        if ($orderType != "") {
            $sql .= " AND ordMas.orderType =  '" . $orderType . "'";
        }
        /* if ( ! $userID){
        $sql	.= " AND ordMas.userID=0";
        } */
        if ($guestEmail != "") {
            $sql .= " AND ordMas.customer_email='" . $guestEmail . "'";
        }
        if ($orderNumber != '') {
            $sql .= " AND ordMas.orderNumber='" . $orderNumber . "'";
        }
        $sql .= " 
					GROUP BY 
						ordMas.masterOrderID	
					ORDER BY 
						ordMas.masterOrderID DESC ";
        //IF PAGINATION REQUIRED	
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        return $this->ObjDatabase->readValues($sql);
    }
    public function getOrderCustomerDetails($orderID, $emailID = '') {
        $sql = " SELECT 
				ordMas.customer_email,
				ordMas.userID,
				ordMas.masterOrderID
			FROM 
				" . DB_PREFIX . "order_masters ordMas 
			WHERE ordMas.orderNumber = '" . $this->escape($orderID) . "' ";
        if ($emailID != '') {
            $sql .= " AND ordMas.customer_email = '" . $emailID . "'";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function saveOrderTrackKey($params = array()) {
        extract($params);
        $sql = " INSERT INTO " . DB_PREFIX . "track_keys
						(
							object_id,
							object_string,
							object_key,
							object_type,
							ttl
						)
						VALUES
						(
							'" . $objectID . "',
							'" . $customerEmailID . "',
							'" . $key . "',
							'" . $trackType . "',
							UNIX_TIMESTAMP(NOW())
						)";
        $this->ObjDatabase->setQuery($sql);
    }
    public function isEmailPresentInOrder($customerEmailID) {
        $sql     = " SELECT 
					ordMas.masterOrderID
					 FROM 
						" . DB_PREFIX . "order_masters ordMas 
					WHERE ordMas.customer_email = '" . $customerEmailID . "' 
					ORDER BY ordMas.masterOrderID DESC ";
      
        $results = $this->ObjDatabase->readValue($sql);
        return $results['masterOrderID'];
    }
    public function getTrackingDetails($trackKey) {
        $sql = " SELECT * 
					 FROM 
						" . DB_PREFIX . "track_keys trkKey 
					WHERE trkKey.object_key = '" . $trackKey . "' ";
        return $this->ObjDatabase->readValue($sql);
    }
    public function deleteTrackingDetails($UrlTimeout, $objectType) {
        $sql = " DELETE
						FROM " . DB_PREFIX . "track_keys
					WHERE (UNIX_TIMESTAMP(NOW()) - dp_track_keys.ttl) > " . $UrlTimeout . " 
					 AND object_type='" . $objectType . "'";
        $this->ObjDatabase->setQuery($sql);
    }
    public function setInitialInvoiceNumber($invoice_number = 0) {
        $sql    = " SELECT 
						masterOrderID
					FROM 
						" . DB_PREFIX . "order_masters
					WHERE 
						masterOrderID >=" . $invoice_number;
        $result = $this->ObjDatabase->readValue($sql);
        if ($result['masterOrderID'] > 0) {
            return false;
        } else {
            $sql    = "ALTER TABLE " . DB_PREFIX . "order_masters AUTO_INCREMENT = " . $invoice_number;
            $update = $this->ObjDatabase->setQuery($sql);
            return true;
        }
    }
    public function updateShipment($postData) {
        if ($postData) {
            echo '<pre>';
            print_r($postData);
            exit;
        }
        $sql = " UPDATE 
								" . DB_PREFIX . "discounts_promo 
							SET 
								promoStatus='Used' 
							WHERE 
								promoCode='" . $promoCode . "'";
        $resResetPromo   = $this->ObjDatabase->setQuery($sql);
    }
    function updateBlueExPickUpCN($blueEx_CN='',$single='',$orderID='')
	{
            if($single=='') {
                $orderIDs		  	= implode(",", $POST['checkOrderIDs']);
            } else {
                $orderIDs                   = $orderID;
            }            
            if ($blueEx_CN!='')
            {
                $sql	= " UPDATE 
							".DB_PREFIX."order_masters
						SET blueEx_CN = '" . $blueEx_CN . "' ";
                        
                        
                       
			$sql	.= " WHERE 
							masterOrderID IN (" . $orderIDs . ") ";
                       
			$resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql);
			
            }
            return true;
        }
    function changeCodOrderStatus($POST,$single='',$orderID='')
	{
           
		$orderChangeStatus 	= $POST['changeStatus'];
                if($single=='') {
                    $orderIDs		  	= implode(",", $POST['checkOrderIDs']);
                } else {
                    $orderIDs                   = $orderID;
                }
		$paymentStatus	 	= (isset($POST['paymentOrderStatus'])) ? $POST['paymentOrderStatus'] : '';
                $blueEx_CN	 	= (isset($POST['blueEx_CN'])) ? $POST['blueEx_CN'] : '';
                $delivery_CN	 	= (isset($POST['delivery_CN'])) ? $POST['delivery_CN'] : '';
                $order_code	 	= (isset($POST['order_code'])) ? $POST['order_code'] : '';
                if ($orderChangeStatus != '' || $paymentStatus!='' || $blueEx_CN!='' || $delivery_CN!='' || $order_code!='')
		{
			$sql	= " UPDATE 
							".DB_PREFIX."order_masters
						SET productType='Product' ";
                        
                        if($orderChangeStatus!='') {                        
                            $sql	.= " , merchant_order_status='" . $orderChangeStatus . "' ";
                        }
                        /*if($orderChangeStatus=='Cancelled') {                            
                            //$sql	.= ", orderStatus = '" . $orderChangeStatus . "' ";
                        }*/
			if ($paymentStatus!='')
			{
				$sql	.= ", paymentStatus = '" . $paymentStatus . "' ";
			}
                        if ($blueEx_CN!='')
			{
				$sql	.= ", blueEx_CN = '" . $blueEx_CN . "' ";
			}
                        if ($delivery_CN!='')
			{
				$sql	.= ", delivery_CN = '" . $delivery_CN . "' ";
			}
                        if ($order_code!='')
			{
				$sql	.= ", order_code = '" . $order_code . "' ";
			}
			$sql	.= " WHERE 
							masterOrderID IN (" . $orderIDs . ") ";
                       
			$resUpdateOrderStatus = $this->ObjDatabase->setQuery($sql);
			return true;
		}
		return true;
	}
        function getShippingStatus($masterOrderID) {
            $sql                =   "SELECT * from ".DB_PREFIX."shipments where masterOrderID=".$masterOrderID." and shippingStatus='Delivered' ";
            $resSelectHistory   =   $this->ObjDatabase->readValue($sql);            
            return $resSelectHistory;           
        }
        public function updateCODFee($codFee,$masterOrderID) {
            if($masterOrderID>0) {
                $sql = "update ".DB_PREFIX."order_masters set orderConvenientFee='".$codFee."' where masterOrderID='".$masterOrderID."'";
                $res = $this->ObjDatabase->setQuery($sql);
		
            }
            return true;
        }
        public function checkFreeOrder($masterOrderID) {
            $sql                =   "SELECT orderItemID from ".DB_PREFIX."order_items where masterOrderID=".$masterOrderID." and freeProduct='No' ";
            $resValues          =   $this->ObjDatabase->readValue($sql);            
            return !empty($resValues) ? 0:1;   
        }
        function getDispatchTime($masterOrderID)
        {
            $sql    = " SELECT 
                                        ordIte.extra 
                                FROM 
                                        ".DB_PREFIX."order_items ordIte 
                                WHERE 
                                        ordIte.masterOrderID='" . $masterOrderID . "' ";
            $resOrderDetails = $this->ObjDatabase->readValue($sql);
            return $resOrderDetails;
       }
     public function getOrderItemsApp($orderID = '', $masterOrderID = '', $idOnly = 'No') {
        if ($idOnly == 'Yes') {
            $sql = " SELECT  
							ordIte.productID ";
        } else {
            $sql = " SELECT 
							ordIte.*,
							product.productImage as imagePath,
                                                        shipment.dispatch_date,
                                                        shipment.delivered_date,
                                                        shipment.dispatch_exp_delivery_date, 
                                                        product.productDispatchTime";
        }
        $sql .= " FROM 
             
						" . DB_PREFIX . "order_masters ordMas
                                                    LEFT JOIN
						" . DB_PREFIX . "order_items ordIte 
                                                    ON ordIte.masterOrderID = ordMas.masterOrderID
					LEFT JOIN
						" . DB_PREFIX . "products product 
						ON (product.productID=ordIte.productID) 
                                        LEFT JOIN 
						" . DB_PREFIX . "shipments shipment 
						ON shipment.masterOrderId=ordMas.masterOrderID";
        $sql .= " WHERE ";
        if ($masterOrderID > 0) {
            $sql .= " ordIte.masterOrderID ='" . (int) $masterOrderID . "'";
        } else {
            if (strpos($orderID, ','))
                $sq .= " ordIte.masterOrderID IN (" . $orderID . ")";
            else
                $sql .= " ordIte.masterOrderID ='" . (int) $orderID . "'";
        }
        $sql .= " GROUP BY ordIte.orderItemID";
        $resOrderItems = $this->ObjDatabase->readValuesSlave($sql);
        return $resOrderItems;
    }
    public function updateOrderViewStatus($masterOrderID) {
        if($masterOrderID>0) {
            $sql = "update ".DB_PREFIX."order_masters set orderViewed='Yes' where masterOrderID='".$masterOrderID."'";
            $res = $this->ObjDatabase->setQuery($sql);
        }
        return true;
    }   
     //CHANGE ORDER STATUS
    function changeDynamicOrderStatus($params) {
        extract($params);
        $append = ',orderStatusDate=NOW()';
        if ($orderChangeStatus != '') {
            if($orderChangeStatus=='order-completed' || $orderChangeStatus=='completed'){
                $append .= ", orderStatus='Completed' ";
            }
             if ($paymentStatus) {
                $append .= ", paymentStatus = '" . $paymentStatus . "' ";
            }
            $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							dynamicOrderStatus='" . $orderChangeStatus . "' ".$append;
            $sql .= " WHERE 
							masterOrderID ='" . $masterOrderID . "' ";
            $this->ObjDatabase->setQuery($sql);
            
            // UPDATE ORDER STATUS LOG
            $sql = "INSERT INTO " . DB_PREFIX . "order_status_log( `masterOrderID`,adminUserID, `dynamicOrderStatus`,  `comments`) "
                    . " VALUES ('" . $masterOrderID . "','".$adminUserID."' ,'" . $orderChangeStatus . "' ,'".$this->escape($comments)."') ";
            $this->ObjDatabase->setQuery($sql);
            //update QC
            if($orderChangeStatus=='qc-completed'){
              $sql = "UPDATE " . DB_PREFIX . "order_qc SET status='completed' WHERE masterOrderID='" . $masterOrderID . "' ";  
              $this->ObjDatabase->setQuery($sql);
            }
            return true;
        }
        return false;
    }
    function getOrderStatusLog($masterOrderID) 	
   {
        $sql = "SELECT ordStaLog.*,"
                . " CONCAT(admUse.adminUserFirstName,' ',admUse.adminUserLastName) AS adminName "
                . " FROM " . DB_PREFIX . "order_status_log ordStaLog "
                . " LEFT JOIN " . DB_PREFIX . "admin_users admUse ON "
                . " admUse.adminUserID = ordStaLog.adminUserID "
                . "  WHERE masterOrderID='" . $masterOrderID . "' ORDER BY logID DESC ";
        return $this->ObjDatabase->readValues($sql);            
   }
    public function updateInvoiceDetails($ID,$invoice_number='')
   {
	   $invoicenumber = isset($invoice_number)?$invoice_number:$ID;
        $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							invoiceNumber='" . $invoicenumber . "', "
                . " invoiceCreatedDate=NOW() ";
            $sql .= " WHERE 
							masterOrderID='" . $ID . "' ";
            return $this->ObjDatabase->setQuery($sql);
   }
   function updateManifestNumber($ID,$mn)
    {
        $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							manifestNumber='" . $this->escape($mn) . "' ";
            $sql .= " WHERE 
							masterOrderID='" . $ID . "' ";
            return $this->ObjDatabase->setQuery($sql);
    }
    //GET ORDER DEAL DETAILS
    public function getOrderBasicDetails( $masterOrderID = '') {
        $sql               = " SELECT 
                                                ordMas.invoiceNumber,
                                                ordMas.orderNumber,
						ordIte.extra,
                                                ordMas.dynamicOrderStatus";
     
        $sql .= "	FROM 
						" . DB_PREFIX . "order_masters ordMas
					LEFT JOIN  
						" . DB_PREFIX . "order_items ordIte 
						ON ordMas.masterOrderID=ordIte.masterOrderID
					";
        if ($masterOrderID > 0) {
            $sql .= " WHERE ordMas.masterOrderID='" . $masterOrderID . "' ";
        }
        $sql .= " GROUP BY 
						ordIte.orderItemID  ";
        return $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
    }
    public function getOrderFullDetails( $masterOrderID = '',$businessID=0) {
        //,
	//						SUM(ordIte.metalWeight) AS metalWeightSum
         $sql = " SELECT 
							ordMas.* ,                                                      
							SUM(ordIte.orderItemQuantity) AS orderItemQuantitySum
			 			FROM 
			 				" . DB_PREFIX . "order_masters ordMas
                                                LEFT JOIN  
						" . DB_PREFIX . "order_items ordIte 
                                                    ON ordMas.masterOrderID=ordIte.masterOrderID                                               
			 			WHERE 
			 				ordMas.masterOrderID='" . $this->escape($masterOrderID) . "' ";
        if ($businessID >0) {
            $sql .= " AND ordMas.businessID='" . (int)$businessID . "' ";
        }							
			 $sql .= 		"	GROUP BY 
			 				ordMas.masterOrderID";
		$orderDetails = $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
                //ordIte.metalWeight,
        $sql = " SELECT 
							                            ordIte.orderItemID,
                                                        ordIte.productOptionName,
                                                        ordIte.orderItemQuantity,
                                                        
                                                        ordIte.shipmentStatus
			 			FROM
						" . DB_PREFIX . "order_items ordIte                                                    
						LEFT JOIN " . DB_PREFIX . "products product
							ON 
						(ordIte.productID=product.productID)
			 			WHERE 
			 				ordIte.masterOrderID='" . $this->escape($masterOrderID) . "' ";
        $orderDetails['items'] = $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        return $orderDetails;
    }
     public function getSmsTemplate($template_id = '',$languageIDs) {
        $sql               = " SELECT 
                                    smsTemplateContent
     
                               FROM 
                                    " . DB_PREFIX . "sms_template st
				INNER JOIN 	" . DB_PREFIX . "sms_template_details std ON (st.smsTempID = std.smsTempID AND languageID = '".$languageIDs['Current']."')
				WHERE st.smsTempID ='" . $template_id . "'"; 
        return $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
    }
	
   public function getOrderInvoiceBasicDetails($masterOrderID)
   {
       $sql = " SELECT 
								ordMas.languageID,
								ordMas.userID,
								ordMas.orderNumber,
								ordMas.businessID,
								ordMas.shippingAddress,
								ordMas.billingAddress,
								ordMas.masterOrderID,
								ordMas.orderCreatedDate,
								ordMas.orderSubtotal,
								ordMas.orderTotalAmount,								
								ordMas.orderNumber,
								ordMas.invoiceNumber,
								ordMas.userCurrencyID,
                                ordMas.userCurrencyExcRate,
                                ordMas.orderConvenientFee,
                                ordMas.redeemDiscountAmount,
                                ordMas.invoiceCreatedDate,
								ordMas.dynamicOrderStatus,
								ordMas.paymentStatus,
                                ordMas.channelInvoiceNumber,
								ordMas.orderUserCreditAmount,
								ordMas.orderPromoDiscountAmount,
								ordMas.purchasedCurrency ,								
								ordMas.orderType,
								ordMas.shippingAmount,
                                paySetGrp.paySetGroupLabel As paymentMethod,
								SUM(ordMas.shippingAmount) AS shippingAmount,
								SUM(ordMas.orderOtherServiceCharge) AS orderOtherServiceCharge,
								SUM(ordMas.orderTaxAmount) AS orderTaxAmount,
								IF(ordMas.userID > 0 ,CONCAT(usr.userFirstName,' ',usr.userLastName),CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,
								IF(ordMas.userID > 0, usr.userEmail,ordMas.customer_email) AS customerEmail,                                                                
                                                               
                                                                ordMas.orderSource,
                                                                ordMas.mPlaceOrderID,
                                                                bus.gstNumber,
                                                                bus.businessPhone,
                                                                ship.trackingNumber,
                                                                shipMeth.settingsGroupXmlLabel AS logisticPartner,
                                                                CONCAT_WS('~*',
                                                                    bus.businessCFirstName,
                                                                    bus.businessCLastName,
                                                                    busLocDet.locAddress1,
                                                                    busLocDet.locAddress2,
                                                                    busLocDet.locCity,
                                                                    busLocDet.locProvince,
                                                                    busLoc.locZip,
                                                                    busLoc.locStateID,
                                                                    busLoc.locCountryID
                                                            ) AS businessAddress
							FROM 
								" . DB_PREFIX . "order_masters ordMas
                             ";
        $sql .= "           LEFT JOIN " . DB_PREFIX . "business bus 
                                                            ON bus.businessID=ordMas.businessID
                                                            
							";
        
       $sql .= "  LEFT JOIN 
		   		" . DB_PREFIX . "business_locations busLoc 
					ON (
						 busLoc.businessID=bus.businessID 
						 AND busLoc.isLocationBasic='Yes'
					   )";
	$sql .= "	   LEFT JOIN 
		   		" . DB_PREFIX . "business_location_details busLocDet 
					 ON( 
					 	 busLocDet.businessLocID=busLoc.businessLocID AND busLocDet.languageID=ordMas.languageID
						 )";
        $sql .= "	   LEFT JOIN 
	   				" . DB_PREFIX . "shipments ship 
	   				ON (ship.masterOrderId=ordMas.masterOrderID)";
        $sql .= "	   LEFT JOIN " . DB_PREFIX . "shipping_methods shipMeth
							ON ship.carrier = shipMeth.settingsGroupID";
        $sql .= "           LEFT JOIN " . DB_PREFIX . "payment_settings_group paySetGrp
                                                            ON paySetGrp.paySetGroupID=ordMas.paymentSettingsGroupID
							";
        $sql .= "			LEFT JOIN 
               " . DB_PREFIX . "users usr 
               ON usr.userID=ordMas.userID
                       WHERE 
                               ordMas.masterOrderID ='" . $this->escape($masterOrderID) . "'";
        return  $this->ObjDatabase->readValue($sql);
   }
   public function getOrderInvoiceDetails($masterOrderID,$languageID=1)
   {
       $sql = " SELECT 
								ordIte.extra,
								ordIte.orderItemPrice,
								ordIte.orderItemQuantity,
								ordIte.orderItemSubtotal,
								ordIte.originalSubtotal,
								productOptDet.productOptionName as optionName,
								productDet.productTitle,								
								productOpt.optionSKU,
								product.hsnCode
							FROM 
								 " . DB_PREFIX . "order_items ordIte      ";
        $sql .= "INNER JOIN 
								" . DB_PREFIX . "products product 
								ON (product.productID = ordIte.productID)
							INNER JOIN  
								" . DB_PREFIX . "product_details productDet 
								ON (productDet.productID = product.productID 
								AND productDet.languageID='".(int)$languageID."')	
							INNER JOIN  
								" . DB_PREFIX . "product_options productOpt
								ON (productOpt.productOptionID = ordIte.productOptionID) 	
							INNER JOIN  
								" . DB_PREFIX . "product_option_details productOptDet 
								ON (productOptDet.productOptionID = ordIte.productOptionID 
								AND productOptDet.languageID='".(int)$languageID."') 
                                                       
							";
        $sql .= "		
                       WHERE 
                               ordIte.masterOrderID ='" . $this->escape($masterOrderID) . "'";
        return  $this->ObjDatabase->readValues($sql);
   }
   public function getNextInvoiceNumber($businessID)
   {
        $sql = " SELECT  MAX(invoiceNumber) FROM
                                                     " . DB_PREFIX . "order_masters
                                             WHERE  invoiceNumber!='' AND invoiceNumber LIKE '%AU%'";
        if($businessID>0){
           $sql .= " AND businessID='".$businessID."' "; 
        }
        $sql .= " ORDER BY 
							invoiceCreatedDate DESC LIMIT 1 ";
        $lastInvoice =   $this->ObjDatabase->getField($sql);
	   if(strpos( $lastInvoice,"AU")!==false){
		    $number = explode("AU",$lastInvoice);
			$number_inc = abs($number[1])+1;
	   } else {
		    $number_inc = '1';
	   }
	   $formatted_value = sprintf("%06d", $number_inc);
	   return "AU".$formatted_value;
   }
   public function listInvoices($params)
   {	 
        extract($params);
        $languageIDs            = isset($languageIDs) ? $languageIDs : array();
        $languageIDs['Current'] = isset($languageIDs['Current']) ? $languageIDs['Current'] : 0;
        $limitFrom              = isset($limitFrom) ? $limitFrom : 0;
        $limitTo                = isset($limitTo) ? $limitTo : '';
        $orderNumber            = isset($orderNumber) ? $this->escape($orderNumber) : '';
        $fromDate               = isset($fromDate) ? $fromDate : '';
        $toDate                 = isset($toDate) ? $toDate : '';
        $searchBusiness         = isset($searchBusiness) ? $this->escape($searchBusiness) : '';        
        $masterOrderID          = (isset($masterOrderID)) ? $this->escape($masterOrderID) : '';
                
        $masterOrderID = (is_array($masterOrderID)) ? implode(',', $masterOrderID) : $masterOrderID;
        $orderStatusDate = "ordMas.invoiceCreatedDate";
        $currentLanguageID = $languageIDs['Current'];
       
        $fromDate = $fromDate ? $fromDate : '';
        $toDate   = $toDate ? $toDate : '';
        $busFields = '';
        if($showBus=='Y') {
            $busFields = 'busDet.businessName,bus.api_id,';
        }
        $sql      = " SELECT 
						SQL_CALC_FOUND_ROWS 
						ordMas.*,
						IF(ordMas.userID > 0 ,CONCAT(usr.userFirstName,' ',usr.userLastName),CONCAT(ordMas.billingFirstName,' ',ordMas.billingLastName)) AS fullName,
		 				SUM(ordIte.orderItemQuantity) as ItemQuantity,
		 				" . $orderStatusDate . " invoiceCreatedDate,".$busFields."
		 				product.*, 
		 				usr.userFirstName, 
		 				usr.userLastName , 
		 				usr.userEmail, 
		 				product.productID,								 
		 				ordMas.shippingAddress AS shippedAddress,
		 				ordMas.billingAddress,
						paySet.paySetGroupLabel,";          
        $sql .= " 	ordIte.shippingSetting AS masterShippingSetting
					FROM 
		 				" . DB_PREFIX . "order_masters  ordMas 
		 			LEFT JOIN 
		 				" . DB_PREFIX . "order_items ordIte 
		 				ON ordMas.masterOrderID=ordIte.masterOrderID
		 			LEFT JOIN 
		 				" . DB_PREFIX . "products product 
		 				ON product.productID=ordIte.productID 
					LEFT JOIN 
		 				" . DB_PREFIX . "payment_settings_group paySet 
		 				ON paySet.paySetGroupID=ordMas.paymentSettingsGroupID ";
         if($showBus=='Y') {
                     $sql	.= " LEFT JOIN 
								".DB_PREFIX."business bus 
								ON (bus.businessID = ordMas.businessID)
							LEFT JOIN 
								".DB_PREFIX."business_details busDet 
								ON (busDet.businessID = bus.businessID 
								AND busDet.languageID=".(int)$currentLanguageID.")	";                 }        
        $sql .= "  LEFT JOIN 
		 				" . DB_PREFIX . "users usr 
                        ON usr.userID  = ordMas.userID   ";       
        $sql .= "		WHERE 
		 				ordMas.orderType='product'
		 				AND ordMas.orderNumber!=''		 				 
		 				AND ordMas.orderRemoved='No' 
                                                AND invoiceNumber!='' 
                                                AND invoiceCreatedDate!='' 
                                                AND invoiceCreatedDate!='0000-00-00 00:00:00'";
        if ($masterOrderID != '') {
            $sql .= " AND ordMas.masterOrderID IN (" . $masterOrderID . ")";
        }
        if ($fromDate != '' && $toDate != '') {
                $sql .= " AND ( DATEDIFF('" . $fromDate . "',ordMas.invoiceCreatedDate)<=0 
		  		 			AND DATEDIFF('" . $toDate . "',ordMas.invoiceCreatedDate)>=0 )";
        } else if ($fromDate != '') {
                $sql .= " AND ( DATEDIFF( '" . $fromDate . "',ordMas.invoiceCreatedDate)=0 ) ";
        } else if ($toDate != '') {
                $sql .= " AND (  DATEDIFF('" . $toDate . "',ordMas.invoiceCreatedDate)=0)  ";
        }
        if ($searchBusiness) {
            $searchBusiness = preg_replace('/[^:0-9]|[:,;\']$/s', "", $searchBusiness);
            $searchBusiness = implode("','", explode(":", $searchBusiness));
            $sql .= " AND ordMas.businessID IN ('" . $searchBusiness . "')";
        }
        
        $sql .= " GROUP BY ordMas.masterOrderID  ";
        $sql .= " ORDER BY ordMas.invoiceCreatedDate ";
		$sql .= "  DESC";
      
        //IF PAGINATION REQUIRED	
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }       
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);    
   }
   public function exeSql($sql) {
	   if($this->ObjDatabase->setQuery($sql)) {
		   return 1;
	   } else {
		   return 0;
	   }
   }
   public function exeReadSql($sql,$type='') {
           if($type=='') {
                $result = $this->ObjDatabase->readValue($sql);
           } else {
                $result = $this->ObjDatabase->readValues($sql);
           }
           return $result;	   
   }
   public function getStateIDByName($stateName, $languageIDs) {
        $sql = " SELECT S.stateID FROM 
						" . DB_PREFIX . "states S
					LEFT JOIN " . DB_PREFIX . "state_details SD
					ON S.stateID=SD.stateID
					WHERE 
						SD.languageID = '" . $languageIDs['Current'] . "' 
						AND stateName LIKE '%" . $stateName . "%'
													LIMIT 1";
        return $this->ObjDatabase->getField($sql);
    }
    //Get settings GroupID
    public function getShipMentID($key) {
        $sql = " SELECT  settingsGroupID 
                        FROM " . DB_PREFIX . "shipping_methods 
                        WHERE  settingsGroupKey = '".$key."'";
        return $this->ObjDatabase->getField($sql);
    }
    public function updateAddressOrderEdit($ordDet){
        $sql = " UPDATE  " . DB_PREFIX . "order_masters "
                . " SET "
                . "billingAddress = '" . $this->escape($ordDet['billingAddress'], false, false) . "',
		shippingAddress= '" . $this->escape($ordDet['shippingAddress'], false, false) . "',"
                . "invoiceNumber='' "
                . " WHERE masterOrderID='".(int)($ordDet['masterOrderID'])."' ";
        return $this->ObjDatabase->setQuery($sql);
    }

    public function createOrder($order = array(), $orderNumerReuired = 'Yes') {
        $parentOrderID = $order['parentOrderID'];
        if (count($order['items']) > 0) {
            foreach ($order['items'] as $key => $items) {
                $businessID               = $key;
                $orderData                = $items;
                $orderBasedSerializedData = $this->escape(serialize($orderData['orderBasedUnserializedData']));
            }
        }
        $languageID = (isset($order['languageID']) && $order['languageID'] > 0) ? $order['languageID'] : 1;
        if ($order['userID'] < 1) {
            $billingDetails   = unserialize($order['billingAddress']);
            $billingFirstName = $this->escape($billingDetails['billingFirstName'], false, false);
            $billingLastName  = $this->escape($billingDetails['billingLastName'], false, false);
            $customerEmail    = $this->escape($order['userCheckoutEmail'], false, false);
            $guestOrder       = 'Yes';
        } else {
            $billingFirstName = '';
            $billingLastName  = '';
            $customerEmail    = $this->escape($order['userEmail'], false, false);
            $guestOrder       = 'No';
        }
		$specialInstructionMessage    = isset($order['specialInstructionMessage'])?$order['specialInstructionMessage']:'';
		$orderSource				  = isset($order['orderSource'])?$order['orderSource']:'web';
		//$metalWeight				  = $order['metalWeight'];
		
        $sql            = " INSERT INTO " . DB_PREFIX . "order_masters
					(
						parentOrderID,
						orderTotalAmount,
						orderSubtotal,
						promoID,
						orderPromoCode,
						orderPromoDiscountAmount,
						orderUserCreditAmount,
						paymentSettingsGroupID,
						orderPaymentMethodName,
						paymentType,
						billingAddress,
						shippingAddress,
						netpayOrderID,
						netpayPaymentType,
						orderHold,
						purchasedCurrency,
						languageID,
						transactionID,
						orderCreatedDate,
						orderStatusDate,
						orderQty,
						orderStatus,
						dynamicOrderStatus,
						mPlaceOrderStatus,
						userID,
						paymentStatus,
						orderType,
						orderSource,
						mPlaceOrderID,
						convertedMasterOrderSubtotal,
						convertedOrderPromoDiscountAmount,
						convertedOrderUserCreditAmount,
						convertedMasterOrderTotal,
						
						orderTaxAmount,
						orderCommissionAmount,
						orderPaymentGatewayCharge,
						orderOtherServiceCharge,
						orderRemoved,
						shippingAmount,
						businessID,
						businessPayment,
						convertedBusinessPayment,
						extraData,
						billingFirstName,
						billingLastName,
						customer_email,
						guest_order,
						specialInstructionMessage
					)
					VALUES
					(
						'" . $parentOrderID . "',
						'" . $order['orderTotalAmount'] . "',
						'" . $order['orderSubtotal'] . "',
						'" . $order['promoID'] . "',
						'" . $order['orderPromoCode'] . "',
						'" . $order['orderPromoDiscountAmount'] . "',
						'" . $order['userCreditAmount'] . "',
						'" . $order['paymentSettingsGroupID'] . "',
						'" . $order['orderPaymentMethodName'] . "',
						'" . $order['paymentType'] . "',
						'" . $this->escape($order['billingAddress'], false, false) . "',
						'" . $this->escape($order['shippingDeliveryAddress'], false, false) . "',
						'" . $order['netpayOrderID'] . "',
						'" . $order['netpayPaymentType'] . "',
						'" . $order['orderHold'] . "',
						'" . $order['currencyID'] . "',
						'" . $languageID . "',
						'0',
						NOW(),
						NOW(),
						'" . $order['masterOrderQty'] . "',
						'" . $order['orderStatus'] . "',
						'" . $order['dynamicOrderStatus'] . "',
						'" . $order['mPlaceOrderStatus'] . "',
						'" . $order['userID'] . "',
						'" . $order['paymentStatus'] . "',
						'product',
						'" . $orderSource . "',
						'" . $order['mPlaceOrderID'] . "',
						'" . $order['convertedMasterOrderSubTotalAmount'] . "',
						'" . $order['convertedOrderPromoDiscountAmount'] . "',
						'" . $order['convertedOrderUserCreditAmount'] . "',
						'" . $order['convertedMasterOrderTotalAmount'] . "',
						'" . $orderData['orderTaxAmount'] . "',
						'" . $orderData['orderCommissionAmount'] . "',
						0,
						'" . $orderData['orderOtherServiceCharge'] . "',
						'No',
						'" . $orderData['shippingAmount'] . "',
						'" . $businessID . "',
						'" . $orderData['businessPayment'] . "',
						'" . $orderData['convertedBusinessPayment'] . "',
						'" . $orderBasedSerializedData . "',
						'" . $billingFirstName . "',
						'" . $billingLastName . "',
						'" . $customerEmail . "',
						'" . $guestOrder . "',
						'" . $specialInstructionMessage . "'
					)";
        $resInsertItem  = $this->ObjDatabase->setQuery($sql);
        $OrderMsterID   = $this->ObjDatabase->getInsertId();
        $invoice_prefix = $order['invoice_prefix'];
        $invoice_number = $invoice_prefix . $OrderMsterID;
        $sql            = "UPDATE " . DB_PREFIX . "order_masters SET orderNumber = '" . $invoice_number . "' WHERE masterOrderID=" . $OrderMsterID . "";
        $update         = $this->ObjDatabase->setQuery($sql);
        if (count($order['items']) > 0) {
            foreach ($order['items'] as $key => $items) {
                if (count($items['subItem']) > 0) {
                    foreach ($items['subItem'] as $key => $subItems) {
                        $unser                      = unserialize($subItems['itemDetails']);
                        $shippingMethod             = $unser['shippingMethod']['methodName'];
                        $unser['productOptionName'] = $this->decodeSplChar($unser['productOptionName']);
                        $serializedData             = $this->escape($subItems['itemDetails'], false, false);
                        $sql                        = " INSERT INTO " . DB_PREFIX . "order_items
									(
										masterOrderID,
										productID,
										productOptionID,
										orderItemPrice,
										orderItemQuantity,
										orderItemSubtotal,
										orderGiveAsGift,
										productOptionName,
										addedTime,
										extra,
										shipmentStatus,
										shipmentDate,
										carrierDetails,
										combinationHash,
										shippingMethod,
										shippingSetting,
										taxSetting,
										freeProduct,
										originalSubtotal
									) 
									VALUES 
									(
										'" . $OrderMsterID . "',
										'" . $subItems['productID'] . "',
										'" . $subItems['productOptionID'] . "',
										'" . $subItems['orderItemPrice'] . "',
										'" . $subItems['orderItemQuantity'] . "',
										'" . $subItems['orderItemSubtotal'] . "',
										'" . $subItems['orderGiveAsGift'] . "',
										'" . $this->escape($unser['productOptionName'], false, false) . "',
										NOW(),
										'" . $serializedData . "',
										'Pending',
										'',
										'',
										'" . $subItems['combinationHash'] . "',
										'" . $shippingMethod . "',
										'" . $subItems['shippingSetting'] . "',
										'" . $subItems['taxSetting'] . "',
										'" . $subItems['freeProduct'] . "',
										'" . $subItems['actualPrice'] * $subItems['orderItemQuantity'] . "'
									)";
                        $resInsertItem              = $this->ObjDatabase->setQuery($sql);
                    }
                }
            }
        }
        return $OrderMsterID; 
    }
	public function cronReadValues($sql) {
            $res		= $this->ObjDatabase->readValues($sql);
            return $res;
	}
	public function cronReadValue($sql) {
		$res		= $this->ObjDatabase->readValue($sql);
		return $res;
	}
	 public function cronSetQuery($sql,$type='') {
		$res		= $this->ObjDatabase->setQuery($sql);
		if($type!='') {
			$OrderMsterID       = $this->ObjDatabase->getInsertId();
			return $OrderMsterID;
		} else {
			return true;
		}
	}
    public function getOrderStatusTitle($orderStatusKey,$languageIDs) {
        $sql = " SELECT  orderStatusTitle  
                        FROM " . DB_PREFIX . "order_status orderstatus
                          JOIN  " . DB_PREFIX . "order_status_details AS ordstaDet    
                                    ON orderstatus.orderStatusID=ordstaDet.orderStatusID
                                    AND ordstaDet.languageID='".$languageIDs['Current']."'
                        WHERE  orderStatusKey = '".$orderStatusKey."'";
        return $this->ObjDatabase->getField($sql);
    }
    public function getTotalDiamondWeight($productID) {
        $sql = "SELECT ROUND( SUM( totalCaratWeight ) , 2 ) as totalCaratWeight FROM  " . DB_PREFIX . "product_diamonds WHERE productID ='".$productID."' GROUP BY productID ";
        return $this->ObjDatabase->getField($sql);
    }
	function changeMPlaceOrderStatus($params) {
        extract($params);
        $append = '';
        if ($orderChangeStatus != '') {
            if($orderChangeStatus=='order-completed'){
                $append .= ", orderStatus='Completed' ";
            }
            $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							mPlaceOrderStatus='" . $orderChangeStatus . "' ".$append;
            $sql .= " WHERE 
							masterOrderID ='" . $masterOrderID . "' 
							AND 
							mPlaceOrderID ='" . $mPlaceOrderID . "' 
							";				
            $this->ObjDatabase->setQuery($sql);
            
            // UPDATE ORDER STATUS LOG
            $sql = "INSERT INTO " . DB_PREFIX . "order_status_log( `masterOrderID`,adminUserID, `dynamicOrderStatus`,  `comments`) "
                    . " VALUES ('" . $masterOrderID . "','".$adminUserID."' ,'" . $orderChangeStatus . "' ,'".$this->escape($comments)."') ";
            $this->ObjDatabase->setQuery($sql);
            //update QC
            if($orderChangeStatus=='qc-completed'){
              $sql = "UPDATE " . DB_PREFIX . "order_qc SET status='completed' WHERE masterOrderID='" . $masterOrderID . "' ";  
              $this->ObjDatabase->setQuery($sql);
            }
            return true;
        }
        return false;
    }
	public function getCountryStateID($stateCode,$countryID,$languageIDs) {
        $sql = " SELECT stateID FROM 
						" . DB_PREFIX . "states 
					WHERE 
						stateShortName = '" . $stateCode . "'
						AND 
						countryID = '" . $countryID . "'
													LIMIT 1";
        return $this->ObjDatabase->getField($sql);
    }
	function checkMPlaceOrderIdExist($mPlaceOrderID,$type='flipkart') {
        $sql = " SELECT masterOrderID FROM 
						" . DB_PREFIX . "order_masters 
					WHERE 
						mPlaceOrderID = '" . $mPlaceOrderID . "'
                                                AND orderSource='".$type."'
					LIMIT 1";
        return $this->ObjDatabase->getField($sql);
    }
    public function getOrderDispatchDate($orderMasterID)
    {
        $sql = "SELECT updatedDate FROM " . DB_PREFIX . "order_status_log 
                WHERE dynamicOrderStatus='order-shipped' 
                AND masterOrderID='".$orderMasterID."' ";
        return $this->ObjDatabase->getField($sql);
    }
    public function changeMWSMplaceOrderStatus($mPlaceOrderStatus,$dynamicOrderStatus,$masterOrderID)
    {
        $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							dynamicOrderStatus='" . $dynamicOrderStatus . "',
                                                        mPlaceOrderStatus='" . $mPlaceOrderStatus . "'    ";
            $sql .= " WHERE 
							masterOrderID ='" . $masterOrderID . "' ";
            $this->ObjDatabase->setQuery($sql);
            if($mPlaceOrderStatus=='Shipped'){
                $shipmentStatus = 'Dispatched';
            } else {
                $shipmentStatus = '';
            }
            if($shipmentStatus!=''){
                $sql = " UPDATE 
                                                             " . DB_PREFIX . "order_items
                                                     SET 
                                                             shipmentStatus='" . $shipmentStatus . "', shipmentDate=NOW()  ";
                 $sql .= " WHERE 
                                                             masterOrderID ='" . $masterOrderID . "' ";
                 $this->ObjDatabase->setQuery($sql); 
            }
             // UPDATE ORDER STATUS LOG
            $sql = "INSERT INTO " . DB_PREFIX . "order_status_log( `masterOrderID`,adminUserID, `dynamicOrderStatus`,  `comments`) "
                    . " VALUES ('" . $masterOrderID . "','1' ,'" . $dynamicOrderStatus . "' ,'Updated automatically by cron services') ";
            $this->ObjDatabase->setQuery($sql);
    }
    public function changeMPlaceOrderShipmentStatus($orders, $shipmentStatus)
    {
        foreach ($orders as $mPlaceOrderID){
            $sql = " UPDATE 
							" . DB_PREFIX . "order_items
						SET 
                                                carrierDetails='Easy Ship',
							shipmentStatus='" . $shipmentStatus . "', shipmentDate=NOW()  ";
            $sql .= " WHERE 
							masterOrderID IN (
                                                        SELECT masterOrderID 
                                                        FROM " . DB_PREFIX . "order_masters 
                                                            WHERE mPlaceOrderID = '" . $mPlaceOrderID . "'
                                                            AND orderSource='amazon' ) ";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    function updateMPlaceOrderStatus($params) {
        extract($params);
        $append = '';
        if ($orderStatusKey != '') {
                $append .= ", dynamicOrderStatus='".$orderStatusKey."' ";
        }      
             $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							mPlaceOrderStatus='" . $attributes['status'] . "' ".$append;
            $sql .= " WHERE 
							orderSource ='" . $source . "' 
							AND 
							mPlaceOrderID ='" . $orderItemId . "' 
							";		
            $this->ObjDatabase->setQuery($sql);
        $sql = "SELECT masterOrderID FROM " . DB_PREFIX . "order_masters 
                WHERE orderSource ='" . $source . "' 
                AND mPlaceOrderID='".$orderItemId."' ";
        $masterOrderID	= $this->ObjDatabase->getField($sql);			
		// UPDATE ORDER STATUS LOG
            $sql = "INSERT INTO " . DB_PREFIX . "order_status_log( `masterOrderID`,adminUserID, `dynamicOrderStatus`,  `comments`)  VALUES ('" . $masterOrderID . "','1' ,'" . $orderStatusKey . "' ,'Updated automatically by notification service') ";
            $this->ObjDatabase->setQuery($sql);	
    }
    function updateChannelInvoiceNumber($params) {
        extract($params);
            
             $sql = " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							channelInvoiceNumber='" . $channelInvoiceNumber."'
							WHERE 
							masterOrderID ='" . $masterOrderID . "' 
							";		
            $this->ObjDatabase->setQuery($sql);
	
    } 	
    function updateTrackingDetails($params) {
        extract($params);
        $orderSource = isset($orderSource)?$orderSource:'flipkart';    
            $sql = " UPDATE 
							" . DB_PREFIX . "order_items
						SET 
							trackingNumber='" . $trackingNumber."',
							carrierDetails='" . $carrierDetails."'
							WHERE 
							masterOrderID IN (
                                                        SELECT masterOrderID 
                                                        FROM " . DB_PREFIX . "order_masters 
                                                            WHERE mPlaceOrderID = '" . $mPlaceOrderID . "'
                                                            AND orderSource='".$orderSource."' )
							";		
            $this->ObjDatabase->setQuery($sql);
	
    }
public function updateTransaction($data)
{
    $response = 'Status: ' . $this->escape($data['transaction_status']) . ", Code: " .$this->escape($data['status_code']) ;
    $sql = "UPDATE " . DB_PREFIX . "payment_transactions SET 
            midtransResponse='".serialize($data)."',
            extra='".$response."',
            paymentResponse=   '".$this->escape($data['transaction_status'])."',
            transactionID= '".$this->escape($data['transaction_id'])."'
            WHERE masterOrderID=  '".(int)$data['order_id']."'  ";
    return $this->ObjDatabase->setQuery($sql);
}
public function getOrderCustomerdet($masterOrderID)
{
    $sql               = " SELECT 
                                                ordMas.customer_email,
                                                ordMas.orderNumber,
                                                ordMas.userID
						FROM 
						" . DB_PREFIX . "order_masters ordMas	";
        if ($masterOrderID > 0) {
            $sql .= " WHERE ordMas.masterOrderID='" .(int) $masterOrderID . "' ";
        }
        $sql .= " LIMIT 1  ";
        return $this->ObjDatabase->readValueSlave($sql, MYSQLI_ASSOC);
}
public function bulkUpdateStatus($adminUserID) {
    $orderStatus = $_POST['bulkOrderStatus'];
    $paymentStatus = $_POST['bulkPaymentStatus'];
    
    $sqllog = "INSERT INTO " . DB_PREFIX . "order_status_log( `masterOrderID`,adminUserID, `dynamicOrderStatus`,  `comments`) VALUES ";
    foreach($_POST['bulkOrderIDs'] as $key=>$masterOrderID){
        $sql = '';
        $append ='';
            if ($orderStatus) {
                $append .= "dynamicOrderStatus = '" . $orderStatus . "' ";
            }
            if($orderStatus=='order-completed' || $orderStatus=='completed'){
                $append .= ", orderStatus='Completed' ";
            }
            if ($paymentStatus) {
                if ($orderStatus) $append .= ", ";
                $append .= " paymentStatus = '" . $paymentStatus . "' ";
            }
            $sql .= " UPDATE 
							" . DB_PREFIX . "order_masters
						SET 
							 ".$append;
            $sql .= " WHERE 
							masterOrderID ='" . $masterOrderID . "'; ";
            
            $this->ObjDatabase->setQuery($sql);
            // UPDATE ORDER STATUS LOG
            $sqllog .= ($key>0)?",":'';
            $sqllog .= "  ('" . $masterOrderID . "','".$adminUserID."' ,'" . $orderStatus . "' ,'".$this->escape('bulk update')."')";
    }
    //echo $sql;exit;
    if($key>0){        
        $this->ObjDatabase->setQuery($sqllog);
        return true;
    }
    return false;
}
public function getReturnorderID($masterOrderID){
    $sql               = " SELECT 
                         returnID
                        FROM 
                        " . DB_PREFIX . "rma_returns rmareturns 
                        WHERE masterOrderID = '".$masterOrderID."'";
       
        $sql .= " LIMIT 1  ";
       
        return $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
}
function getMPlaceOrderStatus($mPlaceOrderID,$type='amazon') {
        $sql = " SELECT dynamicOrderStatus FROM 
						" . DB_PREFIX . "order_masters 
					WHERE 
						mPlaceOrderID = '" . $mPlaceOrderID . "'
                                                AND orderSource='".$type."'
					LIMIT 1";
        return $this->ObjDatabase->getField($sql);
    }

     public function updateFlipOrderAddress($order,$itemID){
        
        $sql=" UPDATE `".DB_PREFIX."order_masters` SET `billingAddress`='".$order['billingAddress']."' , `shippingAddress`='".$order['shippingDeliveryAddress']."'  WHERE `mPlaceOrderID`='".$itemID."'  ";
           $this->ObjDatabase->setQuery($sql);
    }
    
    public function updateOrderFile($filename,$orderID){
        $sql=" UPDATE `".DB_PREFIX."order_masters` SET `orderFileName`='".$filename."'   WHERE `masterOrderID`='".$orderID."'  ";
           $this->ObjDatabase->setQuery($sql);
    }
}