<?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: UserModel.php, v 1.0 2013/03/19 20:52:11
//All functions - user add, edit, list subscribe etc.
//
class UserModel extends Model {
    // Static part of userReferralID
    private $userReferralIDLabel = "ref";
    //Function to Return Users List    with search criteria
    public function listUsers($limitFrom = 0, $limitTo = "", $userFullName = "", $fromDate = "", $toDate = "", $userEmailID = "", $userReferralID = "", $status = '', $sortOrder = '', $userType = "", $sortby = '', $orderby = '', $from = "", $usergroupID = '', $export = '', $userPhone = "",$userSourceOfReg='') {
        $sql = "	
					SELECT ";
        if ($limitTo) {
            $sql .= " 	
						SQL_CALC_FOUND_ROWS ";
        }
        $sql .= "  		
						usr.userID,
						usr.userFirstName,
						usr.userLastName, 
						usr.userPhone, 						
						usr.userEmail,";
        if (!$from) {
            $sql .= "	
						usr.userAdress1, 
						usr.userAdress2, 
						usr.userCity,cnrtyDet.countryName,
						IF(
							usr.stateID>0,
							staDet.stateName,
							usr.userProvince
						) AS states,
						usr.userZip,";
        }
        if ($export != "Yes") {
            if ($from)
                $sql .= "
						usr.userLoginType,";
            if ($from == 'listing') {
                $sql .= " 							
						(
							SELECT 
								DISTINCT
								SUM( 
									IF(
										transactionType='Credit', 
										(transactionamount),
										(transactionamount)*-1 
									)
								)
							FROM 
								" . DB_PREFIX . "users_transactions  
							WHERE 
								userID=usr.userID 
								AND transactionStatus='Active' 
						)  AS transactionAvailableBalance, 
						usr.userStatus,  
						usr.userLastAccessed,";
            }
        }
        if ($export != "Yes" || $sortby == "totalproducts") {
            if ($from != 'dashboard') {
                $sql .= "
						( 
							SELECT
								COUNT(DISTINCT masterOrderID)						
							FROM 
								" . DB_PREFIX . "order_masters  
							WHERE 
								userID=usr.userID
								AND (paymentStatus='Received' || paymentStatus='Pending')
								AND (orderStatus='Completed' OR orderStatus='Process')
						) AS productPurchasedCount,";
            }
        }
        $sql .= "
						usr.userRegistredDate 	,
                                                usr.userSourceOfReg
					FROM 
						" . DB_PREFIX . "users usr ";
        if (!$from) {
            $sql .= " 	
					LEFT JOIN 
						" . DB_PREFIX . "country_details cnrtyDet 
							ON 
							(
								usr.countryID= cnrtyDet.countryID
								AND cnrtyDet.languageID=1
							)
					LEFT JOIN 
						" . DB_PREFIX . "state_details  staDet
							ON  
							(
								usr.stateID=staDet.stateID
								AND staDet.languageID=1
							)";
        }
        $sql .= " 	
					WHERE 
						usr.userStatus!='Deleted' ";
        if ($userFullName != '') {
            $sql .= " 	
						AND ( 
							usr.userFirstName LIKE '%" . $this->escape($userFullName, FALSE) . "%' 
							OR
                            usr.userLastName LIKE '%" . $this->escape($userFullName, FALSE) . "%' 
							OR 
							CONCAT(usr.userFirstName,' ',usr.userLastName) LIKE '%" . $this->escape($userFullName, FALSE) . "%' 
							) ";
        }
        if ($fromDate != '' && $toDate != '') {
            $sql .= " 	
						AND ( 
							DATEDIFF('" . $fromDate . "',usr.userRegistredDate)<=0 
							AND
                            DATEDIFF('" . $toDate . "',usr.userRegistredDate)>=0 
							)";
        } else if ($fromDate != '') {
            $sql .= " 	
						AND ( 
							DATEDIFF( '" . $fromDate . "',usr.userRegistredDate)<=0 
							) ";
        } else if ($toDate != '') {
            $sql .= " 	
						AND	(  
							DATEDIFF('" . $toDate . "',usr.userRegistredDate)>=0
							)  ";
        }
        if (isset($userEmailID) && $userEmailID != '') {
            $sql .= " 	
						AND usr.userEmail LIKE '%" . $this->escape($userEmailID) . "%' ";
        }
         if (isset($userPhone) && $userPhone != '') {
            $sql .= " 	
						AND usr.userPhone LIKE '%" . $this->escape($userPhone) . "%' ";
        }
        if ($userReferralID != '') {
            $sql .= " 	
						AND usr.userReferralID  LIKE '%" . $this->escape($userReferralID) . "%' ";
        }
        if ($status != '') {
            $sql .= " 	
						AND usr.userStatus='" . $status . "'";
        }
        if ($usergroupID != '') {
            $sql .= " 	
						AND usr.usergroupID='" . $usergroupID . "'";
        }
        if ($userType != '') {
            $sql .= " 	
						AND usr.userLoginType='" . $userType . "'";
        }
        if ($userSourceOfReg != '') {
            $sql .= " 	
						AND usr.userSourceOfReg='" . $userSourceOfReg . "'";
        }
        $orderby = (strtolower($orderby) == "desc") ? "DESC" : "ASC";
        switch ($sortby) {
            case 'username':
                $sql .= " 
					ORDER BY  
						usr.userFirstName " . $orderby . "";
                break;
            case 'regiserdate':
                $sql .= " 
					ORDER BY 
						usr.userID " . $orderby . "";
                break;
            case 'totalproducts':
                $sql .= " 
					ORDER BY 
						productPurchasedCount " . $orderby . "";
                break;
            default:
                $sql .= " 
					ORDER BY  
						usr.userID  DESC ";
        }
        //IF PAGINATION REQUIRED
        if ($limitTo) {
            $sql .= " 
					LIMIT  
						" . $limitFrom . "," . $limitTo . "";
        }
        if ($export == 'Yes') {
            return $this->ObjDatabase->readValuesObject($sql, MYSQLI_ASSOC);
        } else {
            return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        }
    }
    //Insert or update subscribers
    public function saveCitySubscribers($POST) {
        $cityID         = isset($POST['cityID']) ? $POST['cityID'] : '';
        $subscEmailID   = $this->escape($POST['subscEmailID']);
        $contactPhone   = $this->escape($POST['contactPhone']);
        $POST['submitPopEmail']= isset($POST['submitPopEmail'])?$POST['submitPopEmail']:'';
        $languageID     = $POST['languageID'];
        $subscStatus    = 'Subscribed';
        $userID         = 0;
        // check site users table
        $sql            = "	SELECT 
					userID
				FROM 
					" . DB_PREFIX . "users 
				WHERE  
					userStatus!='Deleted' 
					AND 
					userEmail='" . $subscEmailID . "' ";
        $resUserDetails = $this->ObjDatabase->readValue($sql);
        if ($resUserDetails) {
            $userID = $resUserDetails['userID'];
        }
        if ($subscEmailID != '') {
            $sql      = "
				SELECT 
					sub.subscID ,subscStatus
				FROM 
						`" . DB_PREFIX . "subscribers` sub
				WHERE 
					subscEmailID= '" . $subscEmailID . "' ";
            $resEmail = $this->ObjDatabase->readValue($sql);
        }
        $remoteAddress = $this->getIpAddress();
        if ($resEmail['subscID'] > 0) {
            $subscID = $resEmail['subscID'];
            $sql     = "
				UPDATE 
					`" . DB_PREFIX . "subscribers` 
				SET 
					`userID`='" . $userID . "' ";
            if ($resEmail['subscStatus'] == 'Unsubscribed') {
                $sql .= ",
						 unsubscrDate='0000-00-00 00:00:00',
						 subscribedDate=NOW(),
						languageID='" . $languageID . "' ,
						subscStatus='Subscribed',
						`remoteAddress`='" . $remoteAddress . "'";
            }
            if ($contactPhone)
                $sql .= "
					,`contactPhone`='" . $contactPhone . "' ";
             if ($POST['submitPopEmail']!='')
				$sql .= "
					,`subscribed_category`='" . $POST['submitPopEmail'] . "' ";
            $sql .= " 
				WHERE 
					subscID='" . $subscID . "'";
            $this->ObjDatabase->setQuery($sql);
        } else {
            $sql = "
				INSERT INTO `" . DB_PREFIX . "subscribers`
                    (
					`subscEmailID`,
					`userID`, 
					`contactPhone`, 
					`subscStatus`,
					`languageID`,
					`remoteAddress`,
                                        `subscribed_category`
					)
				VALUES
					(
					'" . $subscEmailID . "',
					'" . $userID . "',
					'" . $contactPhone . "',
					'" . $subscStatus . "',
					'" . $languageID . "',
					'" . $remoteAddress . "',
                                        '".$POST['submitPopEmail']."'
					)";
            $this->ObjDatabase->setQuery($sql);
            $subscID = $this->ObjDatabase->getInsertId();
        }
        return array(
            $subscID,
            $userID
        );
    }
    //End of function
    //Function to Returns city list
    function getCityListed($city_list) {
        $sql = "	SELECT 
						cit.cityID,
						citDet.cityName 
					FROM 
						" . DB_PREFIX . "cities AS cit,
						`" . DB_PREFIX . "city_details` AS citDet
					WHERE 
						citDet.cityID=cit.cityID 
						AND 
						cit.cityID NOT IN(" . $city_list . ") 
						AND  
						cit.cityStatus='Active' 
					GROUP BY 
						cit.cityID 
					ORDER BY 
						citDet.cityName ASC ";
        return $this->ObjDatabase->readValues($sql);
    }
    //End of function
    public function isSubscriberEmailExists($emailID = '') {
        if ($emailID != '') {
            $sql = "	SELECT 
							COUNT(subscID) AS subCount 
						FROM 
							" . DB_PREFIX . "subscribers
						WHERE 
							subscEmailID='" . $this->escape($emailID) . "' 
							AND subscStatus='Subscribed' ";
            return $this->ObjDatabase->getField($sql);
        }
        return 0;
    }
    //Function to Update subscriber status to subscribed/Unsubscribed
    public function unSubscribeUser() {
        $subscEmailID = '';
        if (isset($_POST['unSubscEmailID'])) {
            if (is_array($_POST['unSubscEmailID'])) {
                $subscEmailID = "'" . implode("','", $_POST['unSubscEmailID']) . "'";
            } else {
                $subscEmailID = "'" . $this->escape($_POST['unSubscEmailID']) . "'";
            }
            if (isset($_POST['subscStatus']) && $_POST['subscStatus'] == 'Subscribed') {
                $subscStatus  = 'Subscribed';
                $unsubscrDate = ',subscribedDate = NOW()';
            } else {
                $subscStatus  = 'Unsubscribed';
                $unsubscrDate = ' ,unsubscrDate = NOW() ';
            }
        }
        if ($subscEmailID != '') {
            $sql = "	UPDATE 
							" . DB_PREFIX . "subscribers
						SET 
							subscStatus='" . $subscStatus . "' " . $unsubscrDate . "
						WHERE  
							subscEmailID IN(" . $subscEmailID . ")";
            return $this->ObjDatabase->setQuery($sql);
        }
        return false;
    }
    //End of function
    //Function to Return user details
    public function getUserDetails($userID = 0, $emailID = "", $status = "", $userName = "", $userPassword = "", $languageIDs = "") {
        $languageIDs['Current'] = isset($languageIDs['Current']) ? $languageIDs['Current'] : '';
        $languageIDs['Content'] = isset($languageIDs['Content']) ? $languageIDs['Content'] : '';
        if ($userID <= 0 && $emailID == '')
            return array();
        $sql = "SELECT 
						usr.*, 
						rest.roleID, 
						usrGroDet.userGroupName,
						DATE_FORMAT(
							usr.userRegistredDate, 
							'%d, %M %Y %r'
						) as formatedRegDate, 
						DATE_FORMAT(
							usr.userLastAccessed, 
							'%d, %M %Y %r'
						) as formatedLastAccessed, 
						count(usrRef.referrerID) as refCount, 
						SUM(usrRef.referralAmount) as TotreferralAmount, 
						cntryDet.countryName, 
						staDet.stateName, 
						CONCAT_WS(
							'~*', 
							usr.userFirstName, 
							usr.userLastName, 
							usr.userAdress1, 
							usr.userAdress2, 
							usr.userCity, 
							usr.userProvince, 
							usr.userZip, 
							usr.stateID, 
							usr.countryID
						) AS userAddress 
					FROM 
							" . DB_PREFIX . "users as usr 
					LEFT JOIN
						" . DB_PREFIX . "user_group_details usrGroDet
							ON	(
								usr.userGroupID = usrGroDet.userGroupID
								AND
								usrGroDet.languageID = '" . $languageIDs['Content'] . "'
							)
					LEFT JOIN
						(
						SELECT 
							resRol.roleID, 
							userID 
						FROM 
								" . DB_PREFIX . "rest_role resRol 
							INNER JOIN 
								" . DB_PREFIX . "rest_consumer AS resCon 
									ON (
										resRol.roleID =resCon.roleID 
										AND 
										userType='customer' 
									)
					) AS rest
						ON 	(
							rest.userID =usr.userID 
						)	
					LEFT JOIN 
						`" . DB_PREFIX . "users_referrals` as usrRef 
						ON 
							usrRef.referedBY = usr.userID 
							AND usrRef.referralStatus = 'Active' 
					LEFT JOIN 
						" . DB_PREFIX . "country_details as cntryDet 
							ON 
								cntryDet.countryID = usr.countryID 
								AND 
								cntryDet.languageID =" . (int) $languageIDs['Current'] . " 
					LEFT JOIN 
						" . DB_PREFIX . "state_details as staDet 
							ON 
								staDet.stateID = usr.stateID 
								AND 
								staDet.languageID = " . (int) $languageIDs['Current'] . " 
					WHERE 
						usr.userStatus != 'Deleted' ";
        if ($userID > 0) {
            $sql .= " 	AND 
						usr.userID='" . $userID . "' ";
        }
        if ($emailID != '') {
            $sql .= " 	AND 
						usr.userEmail='" . $this->escape($emailID) . "' ";
        }
        if ($userName != '') {
            $sql .= " 	AND 
						usr.userName='" . $userName . "' ";
        }
        if ($userPassword != '') {
            $sql .= " 	AND 
						usr.userPassword='" . $userPassword . "' ";
        }
        if ($status != '') {
            $sql .= " 	AND 
						usr.userStatus='" . $status . "'";
        }
        $sql .= "			
					LIMIT 1 ";
        return $this->ObjDatabase->readValue($sql);
    }
    //End of function
    //Function to Change User Status
    public function changeUserStatus($POST, $deleteprivilage = '') {
        $status = $POST['changeStatus'];
        if ($POST['changeStatus'] == 'Deleted' && $deleteprivilage)
            $status = $POST['changeStatus'];
        else if ($POST['changeStatus'] != 'Deleted')
            $status = $POST['changeStatus'];
        else
            $status = 'Active';
        $userIDs = implode(",", $POST['checkUserIDs']);
        if ($status != '') {
            $lockCondition = '';
            if ($status == 'Active' || $status == 'Inactive') {
                $lockCondition = ",userLockedDate='0000-00-00 00:00:00',userInvalidAccessCount=0";
            }
            $sql = "	UPDATE 
						`" . DB_PREFIX . "users` 
					SET 
						userStatus='" . $status . "' " . $lockCondition . " 
					WHERE 
						userID IN (" . $userIDs . ") ";
            $this->ObjDatabase->setQuery($sql);
            return true;
        }
        return false;
    }
    //End of function
    //Function to return user's credit card information
    function getUserCCInfo($userID) {
        $sql = "	SELECT 
						* 
					FROM 
						" . DB_PREFIX . "users_cc_info 
					WHERE 
						userID=" . (int) $userID . "
					LIMIT 1";
        return $this->ObjDatabase->readValue($sql);
    }
    //End of function
    //Function to Insert or Update Userdetails
    public function saveUserDetails($POST, $userID, $editBy = '') {  
        $userFirstName       = isset($POST['userFirstName']) ? $this->escape($POST['userFirstName']) : '';
        $userLastName        = isset($POST['userLastName']) ? $this->escape($POST['userLastName']) : '';
        $userEmail           = isset($POST['userEmail']) ? $this->escape($POST['userEmail']) : '';
        $userPassword        = isset($POST['userPassword']) ? $this->generateHash(trim($POST['userPassword'])) : '';
        $userPhone           = isset($POST['userPhone']) ? $this->escape($POST['userPhone']) : '';
        $userCountrycode           = isset($POST['userCountrycode']) ? $this->escape($POST['userCountrycode']) : '';
        $userAdress1         = isset($POST['userAdress1']) ? $this->escape($POST['userAdress1']) : '';
        $userAdress2         = isset($POST['userAdress2']) ? $this->escape($POST['userAdress2']) : '';
        $userCity            = isset($POST['userCity']) ? $this->escape($POST['userCity']) : '';
        $userGroupID         = isset($POST['userGroupID']) ? $this->escape($POST['userGroupID']) : 1;
        $countryID           = isset($POST['countryID']) ? $POST['countryID'] : '';
        $userDefaultLanguage = isset($POST['userDefaultLanguage']) ? $POST['userDefaultLanguage'] : '';
        $stateID             = isset($POST['stateID']) ? $POST['stateID'] : '';
        $userProvince        = isset($POST['userProvince']) ? $this->escape($POST['userProvince']) : '';
        $userZip             = isset($POST['userZip']) ? $this->escape($POST['userZip']) : '';
        if (isset($POST['stateSelectedType']) && $POST['stateSelectedType'] == 'state') {
            $userProvince = '';
        } else {
            $stateID = 0;
        }
        if (isset($POST['userEmailNewsLetter']) && $POST['userEmailNewsLetter'] == "Yes") {
            $userNewsLetterMode = $POST['userNewsLetterMode'];
        } else {
            $userNewsLetterMode = '';
        }
        $userStatus      = isset($POST['userStatus']) ? $POST['userStatus'] : '';
        $userNewPassword = isset($POST['userNewPassword']) ? $this->generateHash(trim($POST['userNewPassword'])) : '';
        $referralID      = isset($POST['referralID']) ? $this->escape($POST['referralID']) : '';
        //pofile edit
        if (isset($POST['dobDay']) && $POST['dobDay'] && isset($POST['dobMonth']) && $POST['dobMonth'] && isset($POST['dobYear']) && $POST['dobYear'])
            $userDOB = $POST['dobDay'] . '-' . $POST['dobMonth'] . '-' . $POST['dobYear'];
        $userGender                    = isset($POST['userGender']) ? $POST['userGender'] : '';
        $userProfilePicture            = isset($POST['userProfilePicture']) ? $POST['userProfilePicture'] : '';
        $POST['userNewsletterSubscribeStatus'] = 'Yes';
        $userNewsletterSubscribeStatus = isset($POST['userNewsletterSubscribeStatus']) ? $POST['userNewsletterSubscribeStatus'] : '';
        //Credit card details
        $settingsStoreCCInfo           = isset($POST['settingsStoreCCInfo']) ? $POST['settingsStoreCCInfo'] : '';
        $firstNameonCard               = isset($POST['firstNameonCard']) ? $this->escape($POST['firstNameonCard']) : '';
        $lastNameonCard                = isset($POST['lastNameonCard']) ? $this->escape($POST['lastNameonCard']) : '';
        $cardNumber                    = isset($POST['cardNumber']) ? $this->escape($POST['cardNumber']) : '';
        $temp_cardNumber               = isset($POST['cardNumber']) ? str_replace("-", "", $this->escape($POST['cardNumber'])) : '';
        $hidCardNumber                 = isset($POST['hidCardNumber']) ? $this->escape($POST['hidCardNumber']) : '';
        $pushNotification              = isset($POST['pushNotification']) ? $POST['pushNotification'] : 'No';
        if (is_numeric($temp_cardNumber)) {
            $cardNumber = $this->encodeString($cardNumber);
        } else {
            $cardNumber = $hidCardNumber;
        }
        $cardType     = isset($POST['cardType']) ? $POST['cardType'] : '';
        $expMonth     = isset($POST['expMonth']) ? $POST['expMonth'] : '';
        $expYear      = isset($POST['expYear']) ? $POST['expYear'] : '';
        $securityCode = isset($POST['securityCode']) ? $this->escape($POST['securityCode']) : '';
        if (!empty($userProfilePicture) && !empty($POST["userProfilePicture_temp"])) {
            $ImageModel = new ImageModel();
            $ImageModel->copy_file($POST["userProfilePicture_temp"], '/' . __UPLOADS_FOLDER . 'profile/' . $userProfilePicture);
            $ImageModel->resizeImage(__ROOT_PATH . '/' . $POST["userProfilePicture_temp"], '', 93, 92);
            $ImageModel->copy_file($POST["userProfilePicture_temp"], '/' . __UPLOADS_FOLDER . 'profile/avatar/' . $userProfilePicture);
            @unlink($POST["userProfilePicture_temp"]);
        }
        $userSourceOfReg = (isset($POST['userSourceOfReg']) && $POST['userSourceOfReg']!='')?$POST['userSourceOfReg']:'site';
        $userSourceOfReg = ($userSourceOfReg=='desktop')?'site':$userSourceOfReg;
        if ($userID > 0) {
            $sql = "UPDATE 
						" . DB_PREFIX . "users 
					SET 
						`userEmail`='" . $userEmail . "',
						`userFirstName`='" . $userFirstName . "', 
						`userLastName`='" . $userLastName . "',
						`userPhone`= '" . $userPhone . "',  
						`userAdress1`='" . $userAdress1 . "', 
						`userAdress2`='" . $userAdress2 . "', 
						`userCity`= '" . $userCity . "', 
						`countryID`='" . $countryID . "' , 
						`stateID`='" . $stateID . "',
						`userProvince`= '" . $userProvince . "',
						`userGroupID`= '" . $userGroupID . "',
						`userZip`='" . $userZip . "' ";
            /*field which doesn't go as blank*/
            $sql .= ($userDefaultLanguage != "") ? ",userDefaultLanguage='" . $userDefaultLanguage . "'" : "";
            $sql .= ($pushNotification != "") ? ",pushNotification='" . $pushNotification . "'" : "";
            $sql .= (trim($POST['userNewPassword']) != "") ? ",userPassword='" . $userNewPassword . "'" : "";
            $sql .= ($POST['userStatus'] != "") ? ",`userStatus`='" . $userStatus . "' " : "";
            $sql .= ($POST['userStatus'] == "Locked" || $POST['userStatus'] == "Inactive") ? ",userLockedDate=NOW(), userInvalidAccessCount=99 " : ",userLockedDate='0000-00-00 00:00:00', userInvalidAccessCount=0";
            /*field which doesn't go as blank*/
            $sql .= isset($userDOB) ? ",userDOB='" . $userDOB . "'" : "";
            $sql .= ($userGender != "") ? ",userGender='" . $userGender . "'" : "";
            $sql .= ($userNewsletterSubscribeStatus != "") ? ",userNewsletterSubscribeStatus='" . $userNewsletterSubscribeStatus . "' " : "";
            $sql .= ($userProfilePicture != "") ? ",userProfilePicture='" . $userProfilePicture . "'" :",userProfilePicture=' ' ";            
            if ($editBy == "user") {
                $sql .= ",userLastAccessed=NOW() ";
            }
            $where         = " WHERE userID='" . $userID . "' ";
            $resUpdateUser = $this->ObjDatabase->setQuery($sql . $where);
            //insert user Credit card info
            if ($settingsStoreCCInfo == "Yes") {
                $sqlSelectUserCCInfo = $this->getUserCCInfo($userID);
                $sql                 = "INSERT INTO " . DB_PREFIX . "users_cc_info
								(
								userID,
								firstNameonCard,
								lastNameonCard,
								cardType,
								cardNumber,
								expMonth, 
								expYear,
								addedDate
								)
							VALUES
								(
								'" . $userID . "',
								'" . $firstNameonCard . "',
								'" . $lastNameonCard . "',
								'" . $cardType . "',
								'" . $cardNumber . "',
								'" . $expMonth . "',
								'" . $expYear . "',
								NOW()
								)";
                if ($sqlSelectUserCCInfo['UserCcID'] > 0) {
                    $sql = "	UPDATE 
									" . DB_PREFIX . "users_cc_info 
								SET 
									userID='" . $userID . "',
									firstNameonCard='" . $firstNameonCard . "', 
									lastNameonCard='" . $lastNameonCard . "',
                                    cardType='" . $cardType . "',
									cardNumber='" . $cardNumber . "',
									expMonth='" . $expMonth . "',
                                    expYear='" . $expYear . "'
								WHERE 
									userID=" . (int) $userID;
                }
                $this->ObjDatabase->setQuery($sql);
            }
            //end of if loop
        } else {
            $sqlEmailExist = " 	SELECT 
									userEmail 
								FROM 
									" . DB_PREFIX . "users 
								WHERE 
									userEmail= '" . $userEmail . "' 
									AND userStatus!='Deleted'";
            $resEmailExist = $this->ObjDatabase->readValues($sqlEmailExist);
            if (count($resEmailExist) != 0) {
                return false;
            }
            //insert user
            $userDefaultLanguage = isset($POST['userCurrentLanguage']) ? $POST['userCurrentLanguage'] : '';
            $userReferralID      = $this->genRandomString(7);
            if ($editBy != "admin") {
				$POST['userStatus']       = isset($POST['userStatus']) ? $POST['userStatus'] : 'Active';
            }
            $sql = "	INSERT INTO " . DB_PREFIX . "users 
								(
                                                                userSourceOfReg,
								`userEmail`, 
								`userPassword`, 
								`userFirstName`, 
								`userLastName`,
								`userPhone`, 
								`userRegistredDate`,
								`userLastAccessed`, 
								`userAdress1`, 
								`userAdress2`, 
								`userCity`, 
								`countryID`, 
								`stateID`, 
								`userProvince`, 
								`userZip`, 
								`userReferralID`, 
								`UserLoginType`, 
								`userStatus`,
								`userGroupID`,
								`userNewsletterSubscribeStatus`,
								`userDefaultLanguage`,
								`userNewsLetterMode`,
								`userProfilePicture`,pushNotification ";
            $sql .= (($POST['userStatus']) == "Locked" || $POST['userStatus'] == "Inactive") ? (",userLockedDate, userInvalidAccessCount") : '';
            $sql .= " )
                            VALUES 
								( '" . $this->escape($userSourceOfReg ). "', 
								'" . $userEmail . "', 
								'" . $userPassword . "', 
								'" . $userFirstName . "', 
								'" . $userLastName . "',
								'" .$userPhone . "', 
								CURRENT_TIMESTAMP, 
								NOW(),  
								'" . $userAdress1 . "', 
								'" . $userAdress2 . "', 
								'" . $userCity . "',
								'" . $countryID . "', 
								'" . $stateID . "', 
								'" . $userProvince . "', 
								'" . $userZip . "', 
								'" . $this->userReferralIDLabel . $userReferralID . "',
								'Site', 
								'" . $POST['userStatus'] . "',
								'1',
								'" . $userNewsletterSubscribeStatus . "',
								'" . $userDefaultLanguage . "',
								'HTML',
								'" . $userProfilePicture . "','".$pushNotification."' ";
            $sql .= (($POST['userStatus']) == "Locked" || $POST['userStatus'] == "Inactive") ? (",NOW(), 99") : '';
            $sql .= ")";
          
            $this->ObjDatabase->setQuery($sql);
            $userID = $this->ObjDatabase->getInsertId();
            if ($userID) {
                $sql               = "	SELECT 
							sub.subscID 
						FROM 
								`" . DB_PREFIX . "subscribers` AS sub 
						WHERE 
							sub.subscemailID='" . $userEmail . "' 
							AND 
							sub.subscStatus!='Deleted'";
                $resSubscriberCity = $this->ObjDatabase->readValue($sql);
                //update subscriber
                if ($resSubscriberCity['subscID'] > 0) {
                    $subscID = $resSubscriberCity['subscID'];
                    $sql     = "	UPDATE 
								`" . DB_PREFIX . "subscribers` 
							SET 
								userID='" . $userID . "' 
							WHERE 
								subscID='" . $subscID . "' ";
                    $this->ObjDatabase->setQuery($sql);
                }
                if ($referralID != '') {
                    $referralAmount = $POST['referralAmount'];
                    $this->saveUserReferralDetails($userID, $referralID, $referralAmount, $userEmail);
                }
            }
        }
        //THIRD PARTY MAIL SUBSCRIBE
        if ($userNewsletterSubscribeStatus == "Yes") {
            $_settings    = Registry::getInstance('_settings');
            //$campaignName     = $_settings['third_paty_mail_settings']['campaign_name'];
            $campaignName = (!empty($_settings['third_paty_mail_settings']['campaign_name']) ? $_settings['third_paty_mail_settings']['campaign_name'] : "");
            if ($campaignName != '') {
                $ObjMail = new ThirdPartyMailModel();
                $ObjMail->thirdPartySubscribe($userEmail);
            }
        }
        return $userID;
    }
    //End of function
    // Function to insert User Referral's Details
    function saveUserReferralDetails($userID, $referralID, $referralAmount, $customer_email = '') {
        $resUserDet         = $this->getUserByReferralID($referralID);
        $referredBy         = $resUserDet['userID'];
        $referedTo          = $userID;
        $checkGuestPurchase = $this->checkUserAlredyRefPurchasedAsGuest($customer_email);
        if ($checkGuestPurchase['refPurchaseID'] > 0) {
            return 0;
        } else {
            $sql = "INSERT INTO " . DB_PREFIX . "users_referrals 
						(
						referedBY,
						referedTo,
						referralAmount,
						purchaseStatus,
						referralStatus, 
						LastActDate
						)
					VALUES
						(
						'" . $referredBy . "',
						'" . $referedTo . "',
						'" . $referralAmount . "',
						'No',
						'Inactive',
						Now()
						) ";
            $this->ObjDatabase->setQuery($sql);
            return $this->ObjDatabase->getInsertId();
        }
    }
    //End of function
    /*check refered by exists*/
    public function checkReferedByExists($requestUrlKey) {
        $sql              = "SELECT 
					userID 
				FROM 
					" . DB_PREFIX . "users 
				WHERE 
					userReferralID ='" . $requestUrlKey . "' 
					AND 
					userStatus='Active' ";
        $resSelectReferer = $this->ObjDatabase->readValue($sql);
        return $resSelectReferer['userID'];
    }
    /*check refered by exists*/
    //Function to Return userdetails By Refferalid
    function getUserByReferralID($refID) {
        $sql = "	SELECT 
					* 
				FROM 
					" . DB_PREFIX . "users 
				WHERE 
					userReferralID='$refID' ";
        return $this->ObjDatabase->readValue($sql);
    }
    //End of function
    //Function to generate random string
    function genRandomString($length) {
        $characters   = '23456789abcdefghjklmnpqrstuvwxyz';
        $string       = '';
        $stringLength = strlen($characters) - 1;
        for ($p = 0; $p < $length; $p++) {
            $string .= $characters[mt_rand(0, $stringLength)];
        }
        return $string;
    }
    //End of function
    //Function to Return Email ids of subscribed Users based on city , status , producttype, and language
    public function listEmailSubscribers($cityID = '', $productTypeKey = '', $languageID = '') {
        $sql = "SELECT 
					distinct sub.subscEmailID 
				FROM 
						`" . DB_PREFIX . "subscribers` AS sub 
				WHERE 	
						sub.subscStatus='Subscribed' ";
        if ($languageID != '')
            $sql .= " 	AND 
						sub.languageID=" . (int) $languageID . " ";
        return $this->ObjDatabase->readValues($sql);
    }
    //End of function
    // Function to set Reset code of Active and Locked User
    public function resetPassword($emailID, $resetCode = '') {
        $sql = " 	UPDATE 
				" . DB_PREFIX . "users 
			SET  
				resetCode='" . $resetCode . "' 
			WHERE
				userEmail='" . $emailID . "'";
        $this->ObjDatabase->setQuery($sql);
        return $resetCode;
    }
    //End of function
    //Function to  Generate password
    function generatePassword($length) {
        //PASSWORD CASES [minLength,maxLength,minNumbers,minLetters,minLowerCase,minUpperCase,minSymbols,maxSymbols]
        if (PASSWORD_CRITERIA)
            $password_criterias = explode(",", PASSWORD_CRITERIA);
        $numbers        = '23456789';
        $lowerCases     = 'abcdefghjklmnpqrstuvwxyz';
        $upperCases     = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $allowedSymbols = '#_!';
        $minLength      = $password_criterias[0];
        $maxLength      = $password_criterias[1];
        $minNumbers     = $password_criterias[2];
        $minLetters     = $password_criterias[3];
        $minLowerCase   = $password_criterias[4];
        $minUpperCase   = $password_criterias[5];
        $minSymbols     = $password_criterias[6];
        $maxSymbols     = $password_criterias[7];
        $string         = '';
        if ($minNumbers > 0 && strlen($string) < $maxLength) {
            for ($p = 0; $p < $minNumbers; $p++) {
                $numRand = mt_rand(1, strlen($numbers) - 1);
                $string .= $numbers[$numRand];
            }
        }
        if ($minLowerCase > 0 && strlen($string) < $maxLength) {
            for ($p = 0; $p < $minLowerCase; $p++) {
                $lowerRand = mt_rand(1, strlen($lowerCases) - 1);
                $letterString .= $lowerCases[$lowerRand];
            }
        }
        if ($minUpperCase > 0 && strlen($string) < $maxLength) {
            for ($p = 0; $p < $minUpperCase; $p++) {
                $letterString .= $upperCases[mt_rand(0, strlen($upperCases) - 1)];
            }
        }
        $string .= (isset($letterString)) ? $letterString : '';
        if ($minSymbols > 0 && strlen($string) < $maxLength) {
            for ($p = 0; $p < $maxSymbols; $p++) {
                $string .= $allowedSymbols[mt_rand(0, strlen($allowedSymbols) - 1)];
            }
        }
        if (isset($letterString) && strlen($letterString) < $minLetters) {
            for ($p = strlen($letterString) - 1; $p < $minLetters; $p++) {
                $string .= $lowerCases[mt_rand(0, strlen($lowerCases) - 1)];
            }
        }
        if (strlen($string) < $minLength) {
            for ($p = strlen($string) - 1; $p < $minLength; $p++) {
                $string .= $lowerCases[mt_rand(0, strlen($lowerCases) - 1)];
            }
        }
        return $string;
    } // End of Generate Password
    //Function to Export Subscribers
    public function exportSubscribers($limit = '') {
        $dateFrom = (!empty($_POST['dateFrom'])) ? $_POST['dateFrom'] : '';
        $status   = (!empty($_POST['status'])) ? $_POST['status'] : '';
        $dateTo   = (!empty($_POST['dateTo'])) ? $_POST['dateTo'] . ' 23:59:59' : '';
        $sql      = "	
					SELECT  
						sub.subscEmailID,
						CONCAT(sub.subscribedDate,'|') AS subscribedDate,
						languageCode";
        if ($limit == '' ) {
            $sql .= " 	,sub.remoteAddress";
						
        }
        $sql .= " 	,
					subscStatus 
					FROM 
						" . DB_PREFIX . "subscribers as sub 
					LEFT JOIN 
						" . DB_PREFIX . "languages AS lang
						ON (sub.languageID=lang.languageID)";
        if ($limit == '' && __AFFILIATE_MODULE) {
            $sql .= " 	
					LEFT JOIN 
						" . DB_PREFIX . "affiliate_commissions affCom 
						ON sub.subscID=affCom.actionID 
						AND 
						(
							affCom.type='Subscription' 
							OR affCom.type='Imported Subscriber'
						)	
						AND affCom.status='Active' 
					LEFT JOIN 
						" . DB_PREFIX . "affiliates aff 
						ON aff.affiliateUserID=affCom.affiliateUserID";
        }
        $sql .= " 
					WHERE 
						1 ";
        if ($status != '') {
            $sql .= "	
						AND sub.subscStatus='" . $this->escape($status) . "' ";
        } else {
            $sql .= "	
						AND sub.subscStatus='Subscribed' ";
        }
        if ($dateFrom != '') {
            $sql .= " 	
						AND sub.subscribedDate >='" . $this->escape($dateFrom) . "'";
        }
        if ($dateTo != '') {
            $sql .= " 	
						AND sub.subscribedDate <='" . $this->escape($dateTo) . "'";
        }
        $sql .= " 	
					GROUP BY 
						sub.subscEmailID";
        if ($limit != '') {
            $sql .= " 
					LIMIT 0,$limit ";
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    //End of function
    //Function to Import Subscribers
    public function importSubscribers($arraySubscribers, $languageIDs) {
        $defultLanguageID   = $languageIDs['Default'];
        $result['inserted'] = 0;
        $result['existing'] = 0;
        $result['updated']  = 0;
        $result['failed']   = 0;
        $execTime           = ini_get('max_execution_time');
        set_time_limit(300);
        foreach ($languageIDs['languageIDsList'] as $lang) {
            $langCodeArray[$lang['languageCode']] = $lang['languageID'];
        }
        foreach ($arraySubscribers as $subsc) {
            $subscEmailID = $this->escape($subsc['subscEmailID']);
            if ($this->validatEmail($subscEmailID)) {
                $subscribedDates = $subsc['subscribedDate'];
                if ($subscribedDates == '') {
                    $subscribedDates = "NOW()";
                } else {
                    $dateVarChk = false;
                    if ($subscribedDates) {
                        $chkDateFormatExplode = explode(" ", $subscribedDates);
                        if ($chkDateFormatExplode) {
                            $dateChkVar = $chkDateFormatExplode[0];
                            $dateData   = explode("-", $dateChkVar);
                            if ($dateData[0] && $dateData[1] && $dateData[2]) {
                                $dateData0 = (int) $dateData[0];
                                $dateData1 = (int) $dateData[1];
                                $dateData2 = (int) $dateData[2];
                                if (checkdate($dateData1, $dateData2, $dateData0)) {
                                    $dateVarChk = true;
                                }
                            }
                        }
                    }
                    if ($dateVarChk) {
                        $subscribedDates = "'" . $subscribedDates . "'";
                    } else {
                        $subscribedDates = "NOW()";
                    }
                }
                $subDate = $subscribedDates;
                if (isset($langCodeArray[$subsc['languageCode']]) && $langCodeArray[$subsc['languageCode']] != '') {
                    $langID = $langCodeArray[$subsc['languageCode']];
                } else {
                    $langID = $defultLanguageID;
                }
                $subExist = $this->isSubscriberEmailExists($subscEmailID);
                if ($subExist == 0) {
                    $unsubExist = $this->isUnsubExists($subscEmailID);
                    if ($unsubExist == 0) {
                        $sql = "INSERT IGNORE INTO " . DB_PREFIX . "subscribers
									(
									subscEmailID,
									subscribedDate,
									languageID
									)
								 VALUES
									(
									'" . $subscEmailID . "',
									" . $subDate . ",
									'" . $langID . "'
									) ";
                        $this->ObjDatabase->setQuery($sql);
                        $result['inserted']++;
                    } else {
                        $sql = "	UPDATE 
										" . DB_PREFIX . "subscribers 
									SET 
										subscStatus='Subscribed',
										subscribedDate = NOW() 
									WHERE  
										subscEmailID='" . $subscEmailID . "'";
                        $this->ObjDatabase->setQuery($sql);
                        $result['updated']++;
                    }
                } else {
                    $result['existing']++;
                }
                // user exists checking
                $userID = $this->getUserIDBySubscribedEmail($subscEmailID);
                if ($userID > 0) {
                    $this->updateNewsletterSubscribeStatus($userID, "Yes");
                }
            } else {
                $result['failed']++;
            }
        }
        set_time_limit($execTime);
        return $result;
    }
    //End of function
    public function listLangIDs($languageCode) {
        $languageCode = str_replace('|', '\',\'', $languageCode);
        $sql          = "	SELECT 
						distinct languageID 
					FROM  
						" . DB_PREFIX . "languages 
					WHERE 
						languageCode IN ('" . $languageCode . "')";
        return $this->ObjDatabase->readValue($sql);
    }
    //End of function
    // Function to Return User available balance
    public function getAvailableBalance($userID, $status = 'Active') {
        if ($userID > 0) {
            $sql = "	SELECT 
							SUM(IF(transactionType='Credit', 
							(transactionamount),
							(transactionamount)*-1 )) AS transactionAvailableBalance 
						FROM 
							" . DB_PREFIX . "users_transactions 
						WHERE 
							userID='" . (int) $userID . "'";
            $availableAmount = $this->ObjDatabase->getField($sql);
            $availableAmount = ($availableAmount<=0)?0:$availableAmount;
            return $availableAmount;
        }
        return 0;
    }
    //End of function
    //Function to Save user Transaction details
    public function saveUserTransaction($userID, $amount, $type, $description) {
        $sql = "INSERT INTO " . DB_PREFIX . "users_transactions
					( 
					userID,
					transactionType,
					transactionDate ,
					transactionAmount,
					transactionDescription,
					transactionStatus 
					)
				VALUES 
					(
					'" . $userID . "' ,
					'" . $type . "',
					NOW(),
					'" . number_format($amount, 5, '.', '') . "',
					'" . $this->escape($description) . "',
					'Active'
					)";
        $this->ObjDatabase->setQuery($sql);
    }
    //End of function
    //Function to Check for transaction duplication
    public function checkTransactionDuplicate($userID, $amount, $type, $description) {
        $sql                  = "SELECT 
					count(*) as CNT 
				FROM 
					" . DB_PREFIX . "users_transactions
                WHERE  
					userID='" . $userID . "' 
					AND 
					transactionType = '" . $type . "' 
					AND 
					transactionAmount = '" . $amount . "' 
					AND 
					transactionDescription = '" . $description . "' 
					AND 
					transactionStatus ='Active'";
        $resSelectTransaction = $this->ObjDatabase->readValue($sql);
        if ($resSelectTransaction['CNT'] > 0)
            return false;
        else
            return true;
    }
    //End of function
    // Function to Return user transaction list
    public function listUserTransactions($userID) {
        $sql = "SELECT 
					* 
				FROM 
					" . DB_PREFIX . "users_transactions 
				WHERE 
					1  
					AND userID=" . (int) $userID . "
					AND transactionStatus='Active' 
				ORDER BY 
					userTransactionID DESC";
        return $this->ObjDatabase->readValues($sql);
    }
    //End of function
      // Function to Return user transaction list
    public function listUserTransactionsMyWallet($userID,$limitFrom,$limitTo) {
        $limitFrom         = isset($limitFrom) ? $limitFrom : 0;
        $limitTo           = isset($limitTo) ? $limitTo : '';
        $sql = "SELECT 
				SQL_CALC_FOUND_ROWS *
				FROM 
					" . DB_PREFIX . "users_transactions 
				WHERE 
					1  
					AND userID=" . (int) $userID . "
					AND transactionStatus='Active' 
				ORDER BY 
					userTransactionID DESC";
        if ($limitFrom != '' && $limitTo != '') {
            $sql .= " LIMIT  $limitFrom, $limitTo";
        }
        //echo $sql;
        return $this->ObjDatabase->readValuesSlave($sql);
    }
    //Function to Update invalid login attempt count*/
    public function updateInvalidLoginAttemptCount($userEmail, $settingsSecurityAdminBlockingAttempts, $userID = "") {
        $resGetUserDetails = $this->getUserDetails('', $userEmail, '', '', '');
        if ($resGetUserDetails['userInvalidAccessCount'] != 99) {
            $sql                        = "	UPDATE 
							" . DB_PREFIX . "users 
						SET 
							userInvalidAccessCount=userInvalidAccessCount+1,
							userLastAccessed=NOW()
						WHERE 
							userEmail='" . $this->escape($userEmail) . "' 
							AND userStatus!='Deleted'";
            $resUpdateInvalidLoginCount = $this->ObjDatabase->setQuery($sql);
            if ($resUpdateInvalidLoginCount) {
                $resGetUserDetails = $this->getUserDetails('', $userEmail, '', '', '');
                if ($resGetUserDetails['userInvalidAccessCount'] >= $settingsSecurityAdminBlockingAttempts) {
                    $sql                   = "	UPDATE 
									" . DB_PREFIX . "users 
								SET 
									userStatus='Locked',
									userLockedDate=NOW() 
								WHERE 
									userEmail='" . $this->escape($userEmail) . "' 
									AND 
									userID='" . $resGetUserDetails['userID'] . "' 
									AND (
										userStatus='Active' 
										OR 
										userStatus='Locked'
									)";
                    $resUpdateLoginAccount = $this->ObjDatabase->setQuery($sql);
                }
            }
        }
        return $resGetUserDetails['userInvalidAccessCount'];
    }
    //End of function
    //Function to Activate user account
    public function activateUserAccount($userEmail, $userid = 0) {
        $sql = "	UPDATE 
						" . DB_PREFIX . "users 
					SET 
						userInvalidAccessCount='0',
						userLockedDate='',
						userStatus='Active' 
					WHERE 
						userEmail='" . $this->escape($userEmail) . "' 
						AND userStatus!='Deleted'";
        if ($userid > 0) {
            $sql .= " 	AND userID='" . $userid . "'";
        }
        $this->ObjDatabase->setQuery($sql);
    }
    //End of function
    //Function to Save facebook user details
    public function saveFbUserDetails($userDetails,$userID='') {
        if($userID=='' || $userID<=0) {
        $userReferralID = $this->genRandomString(7);
        $sql            = "	INSERT INTO " . DB_PREFIX . "users 
						SET 
							userTypeID = 0 ,
							userGroupID = '1',
                            userName  = '" . $userDetails['id'] . "',
							userLoginType = 'Facebook',
							userStatus   =   'Inactive',
							userNewsLetterMode = 'HTML',
							userLastAccessed = NOW(), ";
        $sql .= ($userDetails['first_name'] != '') ? " userFirstName = '" . $userDetails['first_name'] . "', " : "";
        $sql .= ($userDetails['languageID'] != '') ? " userDefaultLanguage = '" . $userDetails['languageID'] . "', " : "";
        $sql .= ($userDetails['last_name'] != '') ? " userLastName = '" . $userDetails['last_name'] . "', " : "";
        $sql .= ($userDetails['email'] != '') ? " userEmail = '" . $userDetails['email'] . "', " : "";
        $sql .= ($userDetails['gender'] != '') ? " userGender = '" . $userDetails['gender'] . "', " : "";
            $sql .= ($userDetails['userPhone'] != '') ? " userPhone = '" . $userDetails['userPhone'] . "', " : "";
        $sql .= ($userDetails['birthday'] != '') ? " userDOB = '" . date_format(new DateTime($userDetails['birthday']), 'd-m-Y') . "', " : "";
        $sql .= ($userReferralID != '') ? " `userReferralID` = '" . $this->userReferralIDLabel . $userReferralID . "', " : "";
        $sql                  = rtrim($sql, ', ');
        $resInsertUserDetails = $this->ObjDatabase->setQuery($sql);
        if ($resInsertUserDetails) {
            $userID = $this->ObjDatabase->getInsertId();
            if (isset($userDetails['referralID'])) {
                $referralID     = $userDetails['referralID'];
                $referralAmount = $userDetails['referralAmount'];
                $this->saveUserReferralDetails($userID, $referralID, $referralAmount, $userDetails['email']);
            }
            if ($userID) {
                $sql = "	UPDATE 
								`" . DB_PREFIX . "subscribers` 
							SET 
								userID='" . $userID . "' 
							WHERE 
								subscEmailID='" . $userDetails['email'] . "' ";
                $this->ObjDatabase->setQuery($sql);
            }
            //$subscID = $this->saveAsSubscriber($userID, $userDetails['email'], $userPhone);
            return $userID;
        } else {
            return false;
        }
        } else {
            if($userID>0) {
                $sql = "update " . DB_PREFIX . "users set  userName  = '" . $userDetails['id'] . "' ";
                if($userDetails['first_name']!='') {
                    $sql .= " ,userFirstName = '" . $userDetails['first_name'] . "'";
                }
                if($userDetails['last_name']!='') {
                    $sql .= " ,userLastName = '" . $userDetails['last_name'] . "'";
                }
                if($userDetails['gender']!='') {
                    $sql .= " ,userGender = '" . $userDetails['gender'] . "'";
                }
                $sql .= " where userID='".$userID."'";
                $this->ObjDatabase->setQuery($sql);
            }
            return $userID;
        }
    }
    //End of function
    //Function to Update Username
    public function updateFbUserID($userID, $userName) {
        $sql = "	UPDATE 
						" . DB_PREFIX . "users 
					SET 
						userName= '" . $userName . "' 
					WHERE 
						userID = '" . $userID . "'";
        $this->ObjDatabase->setQuery($sql);
    }
    //End of function
    //Function to Update last access date
    function updateUserLastAccess($userID) {
        $sql = "	UPDATE 
						" . DB_PREFIX . "users 
					SET 
						userLastAccessed=NOW() 
					WHERE 
						userID='" . $userID . "' ";
        return $this->ObjDatabase->setQuery($sql);
    }
    //End of function
    //Function to Return List news letter subscribers
    public function listNewsletterSubscribers($status = 'Yes', $languageID = 0) {
        $sql = "SELECT 
					userID, 
					userFirstName, 
					userLastName, 
					userEmail 
				FROM 
					" . DB_PREFIX . "users 
				WHERE 
					userStatus='Active' 
					AND 
					userNewsletterSubscribeStatus = '" . $status . "'";
        if ($languageID > 0) {
            $sql .= " 
					AND 
					userDefaultLanguage='" . $languageID . "'";
        }
        return $this->ObjDatabase->readValues($sql);
    }
    /*
    //Function to Update userlanguage status
    public function userlanguageStatus($defaultlan,$deletedlan)
    {
    $sqluserlanguage = "UPDATE ".DB_PREFIX. "users SET userDefaultLanguage=".$defaultlan." WHERE userDefaultLanguage='" . $deletedlan . "' ";
    $resuserlanguage = $this->ObjDatabase->setQuery($sqluserlanguage);
    }
    */
    //Function to Unsubscribe Newsletter
    public function unsubscribeNewsletter() {
        $emailID = '';
        if (isset($_POST['emailID'])) {
            if (is_array($_POST['emailID'])) {
                $emailID = implode("','", $_POST['emailID']);
            } else {
                $emailID = $this->escape($_POST['emailID']);
            }
        }
        if ($emailID != '') {
            $sql = "UPDATE 
						" . DB_PREFIX . "users 
					SET 
						userNewsletterSubscribeStatus = 'No' 
					WHERE ";
            if (is_array($_POST['emailID'])) {
                $sql .= "userEmail IN  ('" . $emailID . "') ";
            } else {
                $sql .= "userEmail =  '" . $emailID . "' ";
            }
            return $this->ObjDatabase->setQuery($sql);
        }
        return false;
    }
    // Function to delete user subscribed product types
    public function deleteUserFollowedProductTypes($userID, $subscID = '') {
        if ($subscID == '')
            $subscID = $this->getSubscIDFromUserID($userID);
        $sql = "DELETE FROM 
					" . DB_PREFIX . "subscribed_product_types 
				WHERE 
					subscID = '" . $subscID . "'";
        $this->ObjDatabase->setQuery($sql);
    }
    //Function  update Unsubscribe Newsletter
    public function updateNewsletterSubscribeStatus($userID, $userNewsletterSubscribeStatus) {
        $sql = "UPDATE 
					" . DB_PREFIX . "users 
				SET 
					userNewsletterSubscribeStatus = '" . $userNewsletterSubscribeStatus . "' 
				WHERE 
					userID =  '" . $userID . "' ";
        return $this->ObjDatabase->setQuery($sql);
    }
    // EXTD MAIL END
    // Function to get user email , phone , newsletter status
    function getUserInfoForSubscription($userID) {
        $sql = "	SELECT 
					userEmail,
					userPhone,
					userNewsletterSubscribeStatus,
					userDefaultLanguage,
                                        followMerchantStatus
				FROM 
					`" . DB_PREFIX . "users` 
				WHERE 
					userID=" . (int) $userID . "
				LIMIT 1 ";
        return $this->ObjDatabase->readValue($sql);
    }
    // Function to save user subscribed product types
    /*
     * Used in user preference section and subscribe section
     * call from preference section only pass  $userID
     * call from subscribe section  only pass  $subscID
     */
    public function saveUserFollowedProductTypes($POST, $userID, $subscID = '') {
        $isNewSubscriber = true;
        // called from preference section
        if ($subscID == '')
            $subscID = $this->getSubscIDFromUserID($userID);
        else {
            $sql            = "	SELECT 
						* 
					FROM 
						" . DB_PREFIX . "subscribed_product_types 
					WHERE 
						subscID = '" . $subscID . "'";
            $resUserDetails = $this->ObjDatabase->readValue($sql);
            if (count($resUserDetails) > 0) {
                $isNewSubscriber = false;
            }
        }
        if ($isNewSubscriber || $userID > 0) {
            if ($userID > 0) {
                $resDeleteProductTypes = $this->deleteUserFollowedProductTypes($userID);
            } else {
                $resDeleteProductTypes = $this->deleteUserFollowedProductTypes('', $subscID);
            }
            for ($k = 0; $k < count($POST['subscriptionProductTypes']); $k++) {
                $sql = "INSERT INTO " . DB_PREFIX . "subscribed_product_types 
							(
							subscID,
							productTypeKey
							)
						VALUES 
							(
							'" . $subscID . "',
							'" . $POST['subscriptionProductTypes'][$k] . "'
							)";
                $this->ObjDatabase->setQuery($sql);
            }
            return true;
        }
    }
    // Function to save user subscribed categories
    public function saveUserFollowedCategories($POST, $userID) {
        $resDeleteProductTypes = $this->deleteUserFollowedCategories($userID);
        for ($k = 0; $k < count($POST['productCategory']); $k++) {
            $sql = "INSERT INTO " . DB_PREFIX . "users_follow_category 
						(
						userID,
						productCategoryID
						)
					VALUES 
						(
						'" . $userID . "',
						'" . $POST['productCategory'][$k] . "'
						)";
            $this->ObjDatabase->setQuery($sql);
        }
        return true;
    }
    // Function to delete user subscribed categories
    public function deleteUserFollowedCategories($userID) {
        $sql = "DELETE 	FROM 
					" . DB_PREFIX . "users_follow_category 
				WHERE 
					userID='" . $userID . "'";
        $this->ObjDatabase->setQuery($sql);
    }
    // Function get subscID of a user
    public function getSubscIDFromUserID($userID) {
        $sql     = "SELECT 
					subscID 
				FROM 
					`" . DB_PREFIX . "subscribers` 
				WHERE 
					userID=" . (int) $userID . "";
        $subscID = $this->ObjDatabase->readValue($sql);
        return $subscID['subscID'];
    }
    // EXTD MAIL END
    // Function to list user subscribed categories
    public function listUserFollowedCategories($languageIDs, $userID) {
        $sql = "SELECT 
					productCatDet.productCategoryID,
					productCatDet.productCategoryName 
				FROM 
						" . DB_PREFIX . "product_category_details productCatDet 
					JOIN 
						" . DB_PREFIX . "users_follow_category usrFolCat 
							ON 	(
								productCatDet.productCategoryID=usrFolCat.productCategoryID 
								AND 
								productCatDet.languageID='" . $languageIDs['Current'] . "'
							) 
				WHERE 
					userID='" . $userID . "'";
        return $this->ObjDatabase->readValues($sql);
    }
    // Function to save user subscribed cities
    function saveUserSubscibedCities($POST, $userID) {
        $selectedCities = explode(",", $POST['selectedCities']);
        $cityIdCsv      = $POST['selectedCities'];
        //subscribed cities
        $selectedCities = array_unique($selectedCities);
        // delete cities if not selected
        $sql            = "UPDATE 
					" . DB_PREFIX . "subscribed_cities 
				SET 
					subscStatus='Unsubscribed',unsubscrDate = NOW() 
				WHERE 
					cityID NOT IN($cityIdCsv) 
					AND 
					subscStatus='Subscribed' 
					AND 
					subscID IN (
						SELECT 
							subscID 
						FROM 
							" . DB_PREFIX . "subscribers 
						WHERE 
							userID='" . $userID . "'
						)";
        $resSelectCity  = $this->ObjDatabase->setQuery($sql);
        $resUserDetails = $this->getUserInfoForSubscription($userID);
        $subscID        = $this->saveAsSubscriber($userID, $resUserDetails['userEmail'], $resUserDetails['userPhone']);
        foreach ($selectedCities as $city) {
            if ($city > 0) {
                $sql               = "	SELECT 
							citySubscID,
							subscStatus 
						FROM 
							`" . DB_PREFIX . "subscribed_cities` 
						WHERE 
							subscID='" . $subscID . "' 
							AND 
							cityID='" . $city . "' ";
                $resSubscribedCity = $this->ObjDatabase->readValue($sql);
                if ($resSubscribedCity['citySubscID'] > 0) {
                    if ($resSubscribedCity['subscStatus'] == 'Unsubscribed') {
                        $sql = "	UPDATE   
									" . DB_PREFIX . "subscribed_cities 
								SET 
									subscStatus='Subscribed' , 
									subscribedDate=unsubscrDate = NOW(),
									unsubscrDate='0000-00-00 00:00:00',
									languageID='" . $resUserDetails['userDefaultLanguage'] . "' 
								WHERE 
									subscID='" . $subscID . "' 
									AND 
									cityID='" . $city . "'";
                        $this->ObjDatabase->setQuery($sql);
                    }
                } else {
                    $sql = "	INSERT INTO `" . DB_PREFIX . "subscribed_cities`
								(
								subscID,
								cityID,
								languageID
								)
							VALUES
								(
								'" . $subscID . "',
								'" . $city . "',
								'" . $resUserDetails['userDefaultLanguage'] . "'
								)";
                    $this->ObjDatabase->setQuery($sql);
                }
            }
        }
    }
    function saveAsSubscriber($userID, $userEmail, $userPhone = '',$languageIDs='') {
		$languageID = $languageIDs['Default'];
        $sql           = "	SELECT 
					sub.subscID 
				FROM 
						`" . DB_PREFIX . "subscribers` AS sub 
				WHERE 
					sub.subscemailID='" . $userEmail . "' 
					AND 
					sub.subscStatus!='Deleted'";
        $resSubscriber = $this->ObjDatabase->readValue($sql);
        if ($resSubscriber['subscID'] > 0) {
            $subscID = $resSubscriber['subscID'];
            $sql     = "	UPDATE 
							`" . DB_PREFIX . "subscribers` 
						SET 
							userID='" . $userID . "' ";
            if ($userPhone)
                $sql .= " 	, contactPhone='" . $userPhone . "' ";
            $sql .= "  	WHERE 
							subscID='" . $subscID . "'";
            $this->ObjDatabase->setQuery($sql);
        } else {
            $sqlInsertSubscriber = $this->ObjDatabase->setQuery("
			INSERT INTO 
			`" . DB_PREFIX . "subscribers`(
			subscEmailID,
			userID,
			contactPhone,
			languageID
			) VALUES('" . $userEmail . "','" . $userID . "','" . $userPhone . "','" . $languageID . "') ");
            $subscID             = $this->ObjDatabase->getInsertId();
        }
        return $subscID;
    }
    public function updateShippingAddress($POST, $userID) {
        $resUserShippingDetails = array();
        $shippingDet            = array();
        $shippingFname          = isset($POST['shippingFname']) ? $this->escape($POST['shippingFname']) : '';
        $shippingLname          = isset($POST['shippingLname']) ? $this->escape($POST['shippingLname']) : '';
        $shippingAddress1       = isset($POST['shippingAddress1']) ? $this->escape($POST['shippingAddress1']) : '';
        $shippingAddress2       = isset($POST['shippingAddress2']) ? $this->escape($POST['shippingAddress2']) : '';
        $shippingCity           = isset($POST['shippingCity']) ? $this->escape($POST['shippingCity']) : '';
        $shippingStateID        = isset($POST['shippingstateID']) ? $this->escape($POST['shippingstateID']) : '';
        $shippingProvince       = isset($POST['shippingProvince']) ? $this->escape($POST['shippingProvince']) : '';
        $shippingCountryID      = isset($POST['shippingCountryID']) ? $this->escape($POST['shippingCountryID']) : '';
        $shippingZip            = isset($POST['shippingZip']) ? $this->escape($POST['shippingZip']) : '';
        $selectedStateTypeField = 'shippingStateID';
        if (isset($_POST['shippingStateSelectedType']) && ($_POST['shippingStateSelectedType'] == 'shippingProvince')) {
            $shippingstateSelectedType = 'shippingProvince';
        }
        if ($userID > 0) {
            $sql = "	UPDATE 
							" . DB_PREFIX . "user_shipping 
						SET 
							shippingFname='" . $shippingFname . "',
							shippingLname='" . $shippingLname . "',
							shippingAddress1='" . $shippingAddress1 . "',
							shippingAddress2='" . $shippingAddress2 . "',
							shippingCity='" . $shippingCity . "',
							shippingStateID='" . $shippingStateID . "',
							shippingProvince='" . $shippingProvince . "',
							shippingCountryID='" . $shippingCountryID . "',
							shippingZip='" . $shippingZip . "'
						WHERE 
							userID='" . $userID . "'";
            $this->ObjDatabase->setQuery($sql);
        }
        return true;
    }
    //added for daily mail functionality @usage in cron
    function getDistinctUserLanguages($cityID) {
        if ($cityID == 'all') {
            $sql = "	SELECT 
						sub.languageID 
					FROM 
							" . DB_PREFIX . "subscribed_cities as sub 
						INNER JOIN 
							" . DB_PREFIX . "languages lan 
								ON 	(
									lan.languageID=sub.languageID 
									AND 
									lan.languageStatus='Active'
								) 
					WHERE 
						sub.languageID > 0 
					GROUP BY
						sub.languageID";
        } else {
            $sql = "	SELECT 
						sub.languageID 
					FROM 
							" . DB_PREFIX . "subscribed_cities as sub
						INNER JOIN 
							" . DB_PREFIX . "languages lan 
								ON 	(
									lan.languageID=sub.languageID 
									AND 
									lan.languageStatus='Active'
								) 
					WHERE 
						cityID='" . $cityID . "' 
						AND 
						sub.languageID > 0 
					GROUP BY 
						sub.languageID";
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    /**
     * function webhookTriggeredUnsubscribe
     * created  : 13/4/2012 : 12:56 AM
     * @author  : Sijo
     * Update unsubscribed user to database for thirdparty webhook
     * @invocked : subscribeController.php - mailChipWebhookAction()
     * @param $email,$list_id
     */
    function webhookTriggeredUnsubscribe($email, $list_id) {
        $unsubscrDate = date('Y-m-d H:i:s');
        if ($email && $list_id) {
            $sql = "UPDATE  
							" . DB_PREFIX . "subscribed_cities AS subCit
                        LEFT JOIN 
							" . DB_PREFIX . "subscribers AS sub 
								ON (sub.subscID = subCit.subscID)
						LEFT JOIN 
							" . DB_PREFIX . "cities AS cit 
								ON (cit.cityID = subCit.cityID)
					SET 
						subscStatus='Unsubscribed' , 
						unsubscrDate = '$unsubscrDate'
					WHERE  
						sub.subscEmailID ='" . $email . "'
						AND 
						cit.cityCampaignListID='" . $list_id . "' ";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function getSubscribedLanguageID($subscEmailID = '', $cityID = 0) {
        $sql = "	SELECT 
						languageID 
					FROM 
						" . DB_PREFIX . "subscribers  
					WHERE 
						subscEmailID='" . $subscEmailID . "'  ";
        return $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
    }
    // Function get user default language
    public function getUserDefaultLanguage($userID) {
        $sql = "	
			SELECT 
				userDefaultLanguage 
			FROM 
				" . DB_PREFIX . "users 
			WHERE 
				userID='" . $userID . "'";
        return $this->ObjDatabase->getField($sql);
    }
    public function getUserStatus($userID = 0,$accessToken='') {
        if ($userID > 0) {
            $sql = "	SELECT 
							userStatus 
						FROM 
							" . DB_PREFIX . "users
						WHERE 
							userID='" . $userID . "'";
            return $this->ObjDatabase->getFieldSlave($sql);
        } elseif($accessToken!='') {
            $sql	="
				SELECT tkn.userID,usr.userStatus FROM
					" . DB_PREFIX . "access_tokens AS tkn
                                LEFT JOIN " . DB_PREFIX . "users AS usr ON tkn.userID = usr.userID
				WHERE
					tkn.accessToken='".$accessToken."'";
		$result=$this->ObjDatabase->readValue($sql);
                return $result;
        }
    }
    //Function to get Basic user Details
    public function getBasicUserDetails($userID, $fields = array("*")) {
        $resultUser = array();
        if ($userID > 0) {
            $sql        = "	
						SELECT 	
							" . implode(",", $fields) . " 
						FROM 
							" . DB_PREFIX . "users
						WHERE 
							userID='" . $userID . "'";
            $resultUser = $this->ObjDatabase->readValue($sql);
        }
        return $resultUser;
    }
    //FUNCTION to save the user preference
    function savePreferLocation($POST) {
        if ($POST['placeid'] > 0) {
            $latlong = explode(",", $POST['lanLong']);
            $sql     = "	UPDATE 
							`" . DB_PREFIX . "user_places` 
						SET 
							placeAddress='" . $this->escape($POST['address']) . "',
							placeType='" . $this->escape($POST['placetype']) . "',
							placeLat='" . $POST['lat'] . "',placeLng='" . $POST['lng'] . "',
							updatedDate=CURRENT_TIMESTAMP 
						WHERE 
							placesID='" . $POST['placeid'] . "'";
            return $this->ObjDatabase->setQuery($sql);
        } else {
            $sql          = "INSERT INTO `" . DB_PREFIX . "user_places`
						(
						userID,
						placeAddress,
						placeType,
						placeLat,
						placeLng,
						createdDate,
						updatedDate
						)
					VALUES
						(
						" . $POST['userid'] . ",
						'" . $this->escape($POST['address']) . "',
						'" . $this->escape($POST['placetype']) . "',
						'" . $POST['lat'] . "',
						'" . $POST['lng'] . "',
						CURRENT_TIMESTAMP,CURRENT_TIMESTAMP
						)";
            $resLocations = $this->ObjDatabase->setQuery($sql);
            return $this->ObjDatabase->getInsertId();
        }
    }
    //FUNCTION to list the user preference places
    function listPreferLocation($userID) {
        $sql = "SELECT 
					* 
				FROM 
					" . DB_PREFIX . "user_places 
				WHERE 
					userID=" . (int) $userID;
        return $this->ObjDatabase->readValues($sql);
    }
    //FUNCTION to remove the user preference
    function deletePreferLocation($POST) {
        $sql = "DELETE FROM  
					`" . DB_PREFIX . "user_places` 
				WHERE 
					placesID=" . (int) $POST['placeid'] . "
				LIMIT 1";
        $this->ObjDatabase->setQuery($sql);
    }
    //Multiple shipping Address-----------------------------------------------------------
    function listShippingAddress($languageIDs, $limitFrom = '', $limitTo = '', $userID, $status = '',$type='') {
        if($type=='') {
            $fields = '*';
        } else {
            $fields = 'userShippingID,shippingFname,shippingLname,shippingAddress1,shippingAddress2,shippingCity,shippingZip,shippingCountryID,shippingStateID,shippingProvince,addressPrimary,addressStatus,shippingPhone';
        }
        $sql = "SELECT 
					".$fields.",cntryDet.`countryName`,staDet.`stateName`
				FROM 
					" . DB_PREFIX . "user_shipping usrShi
				LEFT JOIN 
					" . DB_PREFIX . "country_details cntryDet
					ON 
					(
						cntryDet.countryID=usrShi.shippingCountryID
						AND cntryDet.languageID=" . (int) $languageIDs['Current'] . "
					)
				LEFT JOIN 
					" . DB_PREFIX . "state_details staDet
					ON (
						staDet.stateID=usrShi.shippingStateID
						AND staDet.languageID=" . (int) $languageIDs['Current'] . "
					)	  
				WHERE 
					usrShi.userID='" . $userID . "'";
        if ($status == '')
            $sql .= " 
					AND 
					usrShi.addressStatus!='Deleted'";
        else
            $sql .= " 
					AND 
					usrShi.addressStatus='" . $status . "'";
        $sql .= "	ORDER BY 
						usrShi.addressPrimary ASC";
        
        return $this->ObjDatabase->readValues($sql);
    }
    function makeShippingAddressPrimary($userShippingID, $userID) {
        $sql = "UPDATE  
					`" . DB_PREFIX . "user_shipping` 
				SET 
					addressPrimary	= 
									CASE 
										WHEN 
											userShippingID=" . (int) $userShippingID . " THEN 'Yes' 
										ELSE 
											'No' 
									END	
				 WHERE 
					userID=" . (int) $userID . "";
        
        $this->ObjDatabase->setQuery($sql);
    }
    function deleteShippingAddress($shippingAddressID, $userID) {
        $sql = "UPDATE  
					`" . DB_PREFIX . "user_shipping` 
				SET 
					addressStatus	='Deleted'
				WHERE 
					userShippingID=" . (int) $shippingAddressID . " 
					AND `userID`=" . (int) $userID . "
				LIMIT 1";
        $this->ObjDatabase->setQuery($sql);
        $sql = "UPDATE  
					`" . DB_PREFIX . "user_shipping` 
				SET 
					`addressPrimary`='Yes' 
				WHERE 
					addressStatus!='Deleted' 
						AND 
					`userID`=" . (int) $userID . " 
				LIMIT 1";
        $this->ObjDatabase->setQuery($sql);
    }
    // FUNCTION to get dummy subscriber data
    public function getDummyData($languageIDs, $POST = '') {
        /* if ($POST['usePhoneNumber'] != '')
        {
        $dummyData = array(
        0 => array(
        'subscEmailID' => 'subscriber@mail.com',
        'contactPhone' => '001-456-867-5309',
        'subscribedDates' => '2012-12-31 18:34:32|',
        'languageCode'=> 'en'
        )
        );
        }
        else
        { */
        $dummyData = array(
            0 => array(
                'subscEmailID' => 'subscriber@mail.com',
                'subscribedDates' => '2012-12-31 18:34:32|',
                'languageCode' => 'en'
            )
        );
        //}
        return $dummyData;
    }
    public function getSubscriberCount() {
        $sql      = "	SELECT 
					count(subscID) as subCount 
				FROM 
					" . DB_PREFIX . "subscribers";
        $resCount = $this->ObjDatabase->readValue($sql);
        return $resCount['subCount'];
    }
    // Function to check user reset code
    public function checkUserResetCode($emailID, $code,$type='') {
        $sql = "	SELECT 
						* 
					FROM 
						" . DB_PREFIX . "users 
					WHERE 
						`userStatus`!='Deleted' ";
        if ($emailID != '') {
            $sql .= " AND userEmail='" . $this->escape($emailID) . "' ";
        }
        if ($code != '') {
            $sql .= " AND resetCode='" . $this->escape($code) . "' ";
        }
        $resUserDetails = $this->ObjDatabase->readValue($sql);
        if ($resUserDetails) {
            if($type=='') {
                return $resUserDetails['userStatus'];
            } else {
                return $resUserDetails;
            }
        }
    }
    //End of function
    // Function to save new password
    public function savePassword($emailID = "", $code = "", $userID = "") {
        if ($code != '') {
            $sql = " 	UPDATE 
						" . DB_PREFIX . "users 
					SET  
						userPassword='" . $this->generateHash(trim($_POST['userPassword1'])) . "',
						resetCode=''
					WHERE
						userEmail='" . $emailID . "'
						AND resetCode='" . $code . "'";
        } else {
            $sql = " 	UPDATE 
						" . DB_PREFIX . "users 
					SET  
						userPassword='" . $this->generateHash(trim($_POST['userPassword1'])) . "',
						userLastAccessed=NOW()
					WHERE
						userID='" . $userID . "'";
        }
        $this->ObjDatabase->setQuery($sql);
    }
    //End of function
    //Function to save address
    public function saveAddress($userID) {
        $userPhone    = $this->escape($_POST['userPhone']);
        $userCountrycode    = $this->escape($_POST['userCountrycode']);
        $userAdress1  = $this->escape($_POST['userAdress1']);
        $userAdress2  = $this->escape($_POST['userAdress2']);
        $userCity     = $this->escape($_POST['userCity']);
        $countryID    = $_POST['countryID'];
        $stateID      = $_POST['stateID'];
        $userProvince = isset($_POST['userProvince']) ? $_POST['userProvince'] : '';
        $userZip      = $this->escape($_POST['userZip']);
        if ($_POST['stateSelectedType'] == 'state') {
            $userProvince = '';
        } else {
            $stateID = 0;
        }
        $sql = "	UPDATE 
						" . DB_PREFIX . "users 
					SET 
						`userPhone`= '" . $userPhone . "',  
						`userAdress1`='" . $userAdress1 . "', 
						`userAdress2`='" . $userAdress2 . "', 
						`userCity`= '" . $userCity . "', 
						`countryID`='" . $countryID . "' , 
						`stateID`='" . $stateID . "',
						`userProvince`= '" . $userProvince . "',
						`userZip`='" . $userZip . "' 
					WHERE userID='" . $userID . "' ";
		$this->ObjDatabase->setQuery($sql);
    }
    //End of function
    //Function to save Credit Card
    public function saveCreditCard($userID, $userCcID = '', $oldCardNumber = '') {
        $firstNameonCard = $this->escape($_POST['firstNameonCard']);
        $lastNameonCard  = $this->escape($_POST['lastNameonCard']);
        $cardNumber      = $this->escape($_POST['cardNumber']);
        $temp_cardNumber = str_replace("-", "", $this->escape($_POST['cardNumber']));
        if (is_numeric($temp_cardNumber)) {
            $cardNumber = $this->encodeString($cardNumber);
        } else if ($cardNumber == '') {
            $cardNumber = '';
        } else {
            $cardNumber = $oldCardNumber;
        }
        $cardType = $_POST['cardType'];
        $expMonth = $_POST['expMonth'];
        $expYear  = $_POST['expYear'];
        if ($userCcID > 0) {
            $sql = "	UPDATE
								" . DB_PREFIX . "users_cc_info 
							SET
								firstNameonCard='" . $firstNameonCard . "', 
								lastNameonCard='" . $lastNameonCard . "',
								cardType='" . $cardType . "',
								cardNumber='" . $cardNumber . "',
								expMonth='" . $expMonth . "',
								expYear='" . $expYear . "'
							WHERE 
								userID=" . (int) $userID;
            $this->ObjDatabase->setQuery($sql);
        } else {
            $sql = "INSERT INTO " . DB_PREFIX . "users_cc_info
							(
							userID,
							firstNameonCard,
							lastNameonCard,
							cardType,
							cardNumber,
							expMonth, 
							expYear,
							addedDate
							)
						VALUES
							(
							'" . $userID . "',
							'" . $firstNameonCard . "',
							'" . $lastNameonCard . "',
							'" . $cardType . "',
							'" . $cardNumber . "',
							'" . $expMonth . "',
							'" . $expYear . "',
							NOW()
							)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    //End of function
    //Function to save Personal Information
    public function savePersonalInfo($userID) {
        $userFirstName       = $this->escape($_POST['userFirstName']);
        $userLastName        = $this->escape($_POST['userLastName']);
        $userDefaultLanguage = $_POST['userDefaultLanguage'];
        //$userStatus      = $_POST['userStatus'];
        if ($_POST['dobDay'] && $_POST['dobMonth'] && $_POST['dobYear'])
            $userDOB = $_POST['dobDay'] . '-' . $_POST['dobMonth'] . '-' . $_POST['dobYear'];
        $userGender         = $_POST['userGender'];
        $userProfilePicture = $_POST['userProfilePicture'];
        if (!empty($_POST["userProfilePicture_temp"])) {
            $ImageModel = new ImageModel();
            $ImageModel->copy_file($_POST["userProfilePicture_temp"], '/' . __UPLOADS_FOLDER . 'profile/' . $userProfilePicture);
            $ImageModel->resizeImage(__ROOT_PATH . '/' . $_POST["userProfilePicture_temp"], '', 93, 92);
            $ImageModel->copy_file($_POST["userProfilePicture_temp"], '/' . __UPLOADS_FOLDER . 'profile/avatar/' . $userProfilePicture);
            @unlink($POST["userProfilePicture_temp"]);
        }
        $sql = "	UPDATE 
						" . DB_PREFIX . "users 
					SET 
						`userFirstName`='" . $userFirstName . "', 
						`userLastName`='" . $userLastName . "',
						userDefaultLanguage='" . $userDefaultLanguage . "',
						userDOB='" . $userDOB . "',
						userGender='" . $userGender . "',
						userProfilePicture='" . $userProfilePicture . "',
						userLastAccessed=NOW() 
					WHERE userID='" . $userID . "' ";
        $this->ObjDatabase->setQuery($sql); //echo $sql;exit;
    }
    //End of function
    public function saveUserMessage($POST, $languageID, $businessID, $userID, $settingsMessageReview = 'Yes') {
        $status            = 'New';
        $POST['messageID'] = isset($POST['messageID']) ? $POST['messageID'] : '';
        $_POST['originID'] = isset($_POST['originID']) ? $_POST['originID'] : '';
        if ($settingsMessageReview == '')
            $status = 'Verified';
        if ($POST['messageID'] > 0) {
            $POST['message'] = $POST['replymessage'];
        }
        $sql        = "INSERT INTO `" . DB_PREFIX . "user_messages`( `type`, `subject`, `message`,
                                `sentBy`, `status`, `parentID`, `userID`,
                                `merchantID`, `productID`, `languageID`,originID)
                        VALUES('" . $this->escape($POST['type']) . "',
                            '" . $this->escape($POST['subject']) . "',
                            '" . $this->escape($POST['message']) . "',
                            '" . $POST['sentBy'] . "' , 
                            '" . $this->escape($status) . "',  
                            '" . $POST['messageID'] . "',    
                            '" . $userID . "',                                 
                            '" . $businessID . "',
                            '" . $POST['productID'] . "',
                            '$languageID',
                                '" . $_POST['originID'] . "'
                            )";
        $resMessage = $this->ObjDatabase->setQuery($sql);
        if ($POST['messageID'] > 0) {
            $sql            = "UPDATE " . DB_PREFIX . "user_messages SET status='Replied' 
                    WHERE messageID='" . $POST['messageID'] . "' ";
            $resMessgUpdate = $this->ObjDatabase->setQuery($sql);
        }
        return $resMessage;
    }
    public function listUserMessagesApp($languageIDs, $limitFrom = 0, $limitTo = '', $mode = '', $merchantID = 0, $userID = '', $list = '', $messageID = 0, $srchParamsArray = array(), $sort = '', $originalMsgId = 0,$msgarr='') {
        $currentLanguageID = $languageIDs['Current'];
        $sql               = "SELECT usrMsg.*,busDet.businessName ,CONCAT(usr.userFirstName,' ',usr.userLastName) as user
                 FROM " . DB_PREFIX . "user_messages usrMsg 
                 JOIN " . DB_PREFIX . "users usr ON usr.userID=usrMsg.userID
                 JOIN " . DB_PREFIX . "business bus ON bus.businessID=usrMsg.merchantID ";
        $sql .= " 
			LEFT JOIN  
		   		`" . DB_PREFIX . "business_details` AS busDet
					ON (
						 bus.businessID = busDet.businessID
						 AND busDet.languageID ='" . $currentLanguageID . "'
					   ) ";
        $sql .= "      WHERE 1";
        if ($merchantID > 0)
            $sql .= " AND usrMsg.merchantID='$merchantID' ";
        if ($userID > 0)
            $sql .= " AND usrMsg.userID='" . $userID . "' AND usrMsg.status!='New' ";
        if ($mode != 'admin')
            $sql .= " AND usrMsg.status!='New' ";
        if ($list == 'parent') {
            $sql .= " AND usrMsg.parentID=0 ";
        }
        if($msgarr!='' && $msgarr>0) {
            $sql .= " AND (usrMsg.originID='".$msgarr."' || (usrMsg.originID='0' && usrMsg.messageID='".$msgarr."')) ";
        }
        //search 
        if (count($srchParamsArray) > 0) {
            if (isset($srchParamsArray['busName']) && $srchParamsArray['busName'] != '') {
                $sql .= "  AND busDet.businessName LIKE '%" . addslashes($srchParamsArray['busName']) . "%'";
            }
            if (isset($srchParamsArray['prodName']) && $srchParamsArray['prodName'] != '') {
                $sql .= "  AND usrMsg.subject LIKE '%" . addslashes($srchParamsArray['prodName']) . "%'";
            }
            if (isset($srchParamsArray['type']) && $srchParamsArray['type'] != '') {
                $sql .= "  AND usrMsg.type LIKE '%" . addslashes($srchParamsArray['type']) . "%'";
            }
            if (isset($srchParamsArray['status']) && $srchParamsArray['status'] != '') {
                $sql .= "  AND usrMsg.status LIKE '%" . addslashes($srchParamsArray['status']) . "%'";
            }
            if (isset($srchParamsArray['fromDate']) && $srchParamsArray['fromDate'] != '' && isset($srchParamsArray['toDate']) && $srchParamsArray['toDate'] != '') {
                $sql .= " AND DATE_FORMAT(usrMsg.createdDate,'%Y-%m-%d')   
							BETWEEN '" . $srchParamsArray['fromDate'] . "' AND '" . $srchParamsArray['toDate'] . "'  ";
            } else if (isset($srchParamsArray['fromDate']) && $srchParamsArray['fromDate'] != '') {
                $sql .= " AND (DATE_FORMAT(usrMsg.createdDate, '%Y-%m-%d') >='" . $srchParamsArray['fromDate'] . "' ) ";
            } else if (isset($srchParamsArray['toDate']) && $srchParamsArray['toDate'] != '') {
                $sql .= " AND (DATE_FORMAT(usrMsg.createdDate, '%Y-%m-%d') <='" . $srchParamsArray['toDate'] . "' ) ";
            }
        }
        if ($sort != '') {
            $sql .= " ORDER BY usrMsg.createdDate " . $sort;
        } else {
            $sql .= " ORDER BY usrMsg.messageID ASC ";
        }
        if ($limitFrom != '' && $limitTo != '') {
            $sql .= "LIMIT  $limitFrom, $limitTo";
        }
         
        $resSelectMessages = $this->ObjDatabase->readValuesSlave($sql);
        if ($list == '' || $list == 'parent')
            return $resSelectMessages;        
        
        $arrParentIDs = $this->findParents($resSelectMessages, $originalMsgId);
        foreach ($resSelectMessages as $key => $value) {
            $menuTree[$resSelectMessages[$key]['parentID']][] = $resSelectMessages[$key];
        }
        $mainTree = $this->createTree($menuTree, $menuTree[0], $originalMsgId);
        //Show wrt ID
         //echo "<pre>ssssss";    print_r($mainTree)    ;exit;   
        if ($messageID > 0) {
            $mainTrees[] = $this->getSubmenu($mainTree, $messageID);
        } else {
            $mainTrees[] = $mainTree;
        }
        foreach($mainTrees as $keyt=>$tree) {
            $mainTrees[$keyt] = $tree;
        }
        $resultSet['mainArr']   = $mainTrees;
        $resultSet['parentIds'] = $arrParentIDs;
        //  print_r($mainTrees)    ;       exit;   
        return $resultSet;
    }
    public function listUserMessages($languageIDs, $limitFrom = 0, $limitTo = '', $mode = '', $merchantID = 0, $userID = '', $list = '', $messageID = 0, $srchParamsArray = array(), $sort = '', $originalMsgId = 0) {
        $currentLanguageID = $languageIDs['Current'];
        $sql               = "SELECT usrMsg.*,busDet.businessName ,CONCAT(usr.userFirstName,' ',usr.userLastName) as user
                 FROM " . DB_PREFIX . "user_messages usrMsg 
                 JOIN " . DB_PREFIX . "users usr ON usr.userID=usrMsg.userID
                 JOIN " . DB_PREFIX . "business bus ON bus.businessID=usrMsg.merchantID ";
        $sql .= " 
			LEFT JOIN  
		   		`" . DB_PREFIX . "business_details` AS busDet
					ON (
						 bus.businessID = busDet.businessID
						 AND busDet.languageID ='" . $currentLanguageID . "'
					   ) ";
        $sql .= "      WHERE 1";
        if ($merchantID > 0)
            $sql .= " AND usrMsg.merchantID='$merchantID' ";
        if ($userID > 0)
            $sql .= " AND usrMsg.userID='" . $userID . "' AND usrMsg.status!='New' ";
        if ($mode != 'admin')
            $sql .= " AND usrMsg.status!='New' ";
        if ($list == 'parent') {
            $sql .= " AND usrMsg.parentID=0 ";
        }
        //search 
        if (count($srchParamsArray) > 0) {
            if (isset($srchParamsArray['busName']) && $srchParamsArray['busName'] != '') {
                $sql .= "  AND busDet.businessName LIKE '%" . addslashes($srchParamsArray['busName']) . "%'";
            }
            if (isset($srchParamsArray['prodName']) && $srchParamsArray['prodName'] != '') {
                $sql .= "  AND usrMsg.subject LIKE '%" . addslashes($srchParamsArray['prodName']) . "%'";
            }
            if (isset($srchParamsArray['type']) && $srchParamsArray['type'] != '') {
                $sql .= "  AND usrMsg.type LIKE '%" . addslashes($srchParamsArray['type']) . "%'";
            }
            if (isset($srchParamsArray['status']) && $srchParamsArray['status'] != '') {
                $sql .= "  AND usrMsg.status LIKE '%" . addslashes($srchParamsArray['status']) . "%'";
            }
            if (isset($srchParamsArray['fromDate']) && $srchParamsArray['fromDate'] != '' && isset($srchParamsArray['toDate']) && $srchParamsArray['toDate'] != '') {
                $sql .= " AND DATE_FORMAT(usrMsg.createdDate,'%Y-%m-%d')   
							BETWEEN '" . $srchParamsArray['fromDate'] . "' AND '" . $srchParamsArray['toDate'] . "'  ";
            } else if (isset($srchParamsArray['fromDate']) && $srchParamsArray['fromDate'] != '') {
                $sql .= " AND (DATE_FORMAT(usrMsg.createdDate, '%Y-%m-%d') >='" . $srchParamsArray['fromDate'] . "' ) ";
            } else if (isset($srchParamsArray['toDate']) && $srchParamsArray['toDate'] != '') {
                $sql .= " AND (DATE_FORMAT(usrMsg.createdDate, '%Y-%m-%d') <='" . $srchParamsArray['toDate'] . "' ) ";
            }
        }
        if ($sort != '') {
            $sql .= " ORDER BY usrMsg.createdDate " . $sort;
        } else {
            $sql .= " ORDER BY usrMsg.parentID ASC ";
        }
        if ($limitFrom != '' && $limitTo != '') {
            $sql .= "LIMIT  $limitFrom, $limitTo";
        }
        //   echo $sql;
        $resSelectMessages = $this->ObjDatabase->readValuesSlave($sql);
        if ($list == '' || $list == 'parent')
            return $resSelectMessages;
        $arrParentIDs = $this->findParents($resSelectMessages, $originalMsgId);
        foreach ($resSelectMessages as $key => $value) {
            $menuTree[$resSelectMessages[$key]['parentID']][] = $resSelectMessages[$key];
        }
        $mainTree = $this->createTree($menuTree, $menuTree[0], $originalMsgId);
        //Show wrt ID
        // echo "<pre>ssssss";    print_r($mainTree)    ;exit;   
        if ($messageID > 0) {
            $mainTrees[] = $this->getSubmenu($mainTree, $messageID);
        } else {
            $mainTrees[] = $mainTree;
        }
        $resultSet['mainArr']   = $mainTrees;
        $resultSet['parentIds'] = $arrParentIDs;
        //  print_r($mainTrees)    ;       exit;   
        return $resultSet;
    }
    public function getSubmenu($menuArr, $id) {
        $parra = array();
        $parra = $this->loopLevelArr($menuArr, $id);
        return $parra[0];
    }
    private function loopLevelArr($arr, $id) {
        $result  = array();
        $results = '';
        foreach ($arr as $array) {
            if ($array['messageID'] == $id) {
                $result[] = $array;
                break;
            } else if (isset($array['replyMsg']) && is_array($array['replyMsg'])) {
                $results = $this->loopLevelArr($array['replyMsg'], $id);
            }
            if ($results)
                foreach ($results as $row) {
                    if (!empty($row))
                        $result[] = $row;
                }
        }
        return $result;
    }
    function findParents($array, $parentId, $reset = true) {
        static $crumbs;
        // reset path on every new lookup
        if ($reset == true) {
            $crumbs = array();
        }
        foreach ($array as $category) {
            if ($parentId == $category['messageID']) {
                // append the name of the category
                $crumbs[] = $category['messageID'];
                // if a parent exists recursively call this function until no more parent is found
                if ($category['parentID'] > 0) {
                    $this->findParents($array, $category['parentID'], false);
                }
            }
        }
        // because the function goes the path from in to out, the array needs to be reversed
        return array_reverse($crumbs);
    }
    public function findParentID($category, $parentmsgId, $reset = true,$type='') {
        static $crumbs;
        // reset path on every new lookup
        if ($reset == true) {
            $crumbs = array();
        }
        
        //foreach ($array as $category) {
            if ($category['parentID'] > 0) {
                $sql = "select parentID,messageID from " . DB_PREFIX . "user_messages where messageID='".$category['parentID']."'";  
                if($type=='Y') {
                        //echo "ASDAS";exit;
                }
                $rs  = $this->ObjDatabase->readValue($sql);
                if($category['messageID']==33 || $type=='Y') {
                   // echo $sql." ".$rs['parentID']."<br>";
                }
                if($rs['parentID']<=0) { 
                    if($type=='Y') {
                        //echo $rs['messageID']." ".$parentmsgId;
                    }
                    if($rs['messageID']==$parentmsgId) {
                         //if($type=='Y') {
                        //echo "vv ".$rs['messageID'];
                        return 1;;
                   // }
                        // return  1;//$rs['messageID'];
                    } else {
                        return 0;
                    }
                } else {
                   //echo "12 ";
                    return $parentIDTemp = $this->findParentID($rs, $parentmsgId, false,'Y');
                   // if($rs['messageID']==$parentmsgId) {
                       // return 1;;
                   // }
                    //echo "iii";
                }
            }
       // }
            
       // return 0;
        // because the function goes the path from in to out, the array needs to be reversed
        //return array_reverse($crumbs);
    }
    private function createTree(&$list, $parent, $originalMsgId) {
        $tree = array();
        if (is_array($parent)) {
            foreach ($parent as $k => $l) {
                if (isset($list[$l['parentID']]) && isset($list[$l['messageID']]) && is_array($list[$l['messageID']])) {
                    $l['replyMsg'] = $this->createTree($list, $list[$l['messageID']], $originalMsgId);
                }
                $tree[] = $l;
                if ($originalMsgId == $l['messageID'])
                    break;
            }
        }
        return $tree;
    }
    public function getMessageByID($id, $businessID = 0, $userID = 0) {
        $sql = "SELECT * FROM " . DB_PREFIX . "user_messages WHERE 1";
        if ($id > 0)
            $sql .= " AND messageID='$id' ";
        if ($businessID > 0)
            $sql .= " AND merchantID='$businessID' ";
        if ($userID > 0)
            $sql .= " AND userID='$userID' ";
        $resSelectMessages = $this->ObjDatabase->readValue($sql);
        return $resSelectMessages;
    }
    public function verifyMessage($POST, $ids = array()) {
        if (count($ids) > 0) {
            $sql        = "UPDATE " . DB_PREFIX . "user_messages  
                        SET status= '" . $this->escape($POST['status']) . "'
                        WHERE messageID IN ('" . implode("','", $ids) . "')    ";
          // echo $sql;exit;
            $resMessage = $this->ObjDatabase->setQuery($sql);
        } else {
            if ($POST['messageID'] > 0 && $POST['status'] != '') {
                $sql        = "UPDATE " . DB_PREFIX . "user_messages  
                        SET status= '" . $this->escape($POST['status']) . "'
                        WHERE messageID='" . $POST['messageID'] . "'    ";
               
                $resMessage = $this->ObjDatabase->setQuery($sql);
            }
        }
        return $resMessage;
    }
    public function changeMessageStatus($id, $sentBy) {
        if ($id > 0) {
            $sql = "UPDATE " . DB_PREFIX . "user_messages SET status='Read' 
                    WHERE messageID='" . $id . "' AND sentBy!='" . $sentBy . "' ";
            return $this->ObjDatabase->setQuery($sql);
        }
    }
    public function isUnsubExists($emailID) {
        $sql = "	
			SELECT 
				COUNT(sub.subscID) AS subCount 
			FROM 
				" . DB_PREFIX . "subscribers AS sub
			WHERE 
				sub.subscEmailID='" . $this->escape($emailID) . "' 
				AND sub.subscStatus='Unsubscribed'";
        return $this->ObjDatabase->getField($sql);
    }
    public function checkCustomerExist($customerEmail, $masterOrderID = '') {
        $sql = "	SELECT 
						count(masterOrderID) as customerCount 
					FROM 
						`" . DB_PREFIX . "order_masters`  
					WHERE 
						customer_email = '" . $customerEmail . "' ";
        if ($masterOrderID) {
            $sql .= " AND masterOrderID !='" . $masterOrderID . "' ";
        }
        $resCustomerCount = $this->ObjDatabase->readValue($sql);
        return $resCustomerCount['customerCount'];
    }
    public function checkUserExists($emailID) {
        $sql = "SELECT 
						count(usr.userID) as userCount
					FROM 
						" . DB_PREFIX . "users as usr 
					WHERE 
						usr.userStatus != 'Deleted' ";
        if ($emailID != '') {
            $sql .= " 	AND 
						usr.userEmail='" . $this->escape($emailID) . "' ";
        }
        return $this->ObjDatabase->readValue($sql);
    }
    //LIST ITEMS ADDED IN WISHLIST FOR A USER
    public function listUserWishlist($params = array()) {
        extract($params);
        $currentLanguageID = $languageIDs['Current'];
        $now               = date('Y-m-d');
        $sql               = " SELECT 
					UsrWisIte.itemID,
					product.productID,
					product.productUrl,
					product.productDefaultCurrency,
					product.productImage,
					productDet.productTitle,
					IF(product.productEndTime != '0000-00-00' AND product.productEndTime < '" . $now . "','1','0') AS productEnd
				FROM 
				  " . DB_PREFIX . "user_wishlist UsrWis
				  INNER JOIN " . DB_PREFIX . "user_wishlist_items AS UsrWisIte 
						ON (
							UsrWis.listID = UsrWisIte.listID 
						)
				LEFT JOIN 	
					" . DB_PREFIX . "products  AS product 
						ON (
							product.productID = UsrWisIte.productID 
						)
				LEFT JOIN 
					" . DB_PREFIX . "product_details AS productDet 
						ON (
							product.productID = productDet.productID 
							AND 
							productDet.languageID = " . $currentLanguageID . "
						) 
				WHERE
					product.productStatus != 'Deleted' 
					AND product.productStartTime <= '" . $now . "'  
					AND UsrWis.userID=" . $regUserID . " 
				GROUP BY product.productID 
					ORDER BY 
					UsrWisIte.addedDate DESC ";
        $resSelectProducts = $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        return $resSelectProducts;
    }
    public function listUserProductReviews($languageIDs, $userID) {
        $sql = "	SELECT 
							productDet.productTitle,
							productRev.productRating,
							productRev.reviewTitle,
							productRev.reviewText,
							productRev.reviewStatus,
							productRev.reviwedDate,
							productRev.helpfulVoteCount,
							productRev.notHelpfulVoteCount,
							productRev.ProductReviewID,
							product.productID,
							productRev.userIP,
							productRev.isCertifiedUser,
							product.productImage
							FROM 
								" . DB_PREFIX . "product_reviews AS productRev
							LEFT JOIN 
								" . DB_PREFIX . "products as product
								ON product.productID = productRev.productID
							LEFT JOIN
								" . DB_PREFIX . "product_details AS productDet
								ON productDet.productID = product.productID AND productDet.languageID = '" . $languageIDs['Current'] . "'
							WHERE
								productRev.userID	= '" . $userID . "'
								AND reviewStatus<>'Deleted'  
							GROUP BY product.productID	ORDER BY productRev.reviwedDate DESC";
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    public function isSubcEmailExists($emailID, $subscStatus = "") {
        $sql = "	SELECT 
						count(sub.subscID) as subCount 
					FROM 
							`" . DB_PREFIX . "subscribers`  AS sub
					WHERE 
						sub.subscEmailID='" . $this->escape($emailID) . "' ";
        if ($subscStatus != '')
            $sql .= "				AND 
						sub.subscStatus='" . $subscStatus . "' ";
        else
            $sql .= "				AND 
						sub.subscStatus!='Deleted' ";
        $resSubscriberCount = $this->ObjDatabase->readValue($sql);
        return $resSubscriberCount['subCount'];
    }
    public function getUserIDBySubscribedEmail($email) {
        $userID         = 0;
        $sql            = "	
			SELECT 
				userID
			FROM 
				" . DB_PREFIX . "users 
			WHERE  
				userStatus!='Deleted' 
				AND userEmail='" . $this->escape($email) . "'
			LIMIT 1 ";
        $resUserDetails = $this->ObjDatabase->readValue($sql);
        if ($resUserDetails) {
            $userID = $resUserDetails['userID'];
        }
        return $userID;
    }
    public function checkUserAlredyRefPurchasedAsGuest($customer_email = '') {
        if ($customer_email != '') {
            $sql = "	SELECT 
								refPurchaseID
							FROM 
								" . DB_PREFIX . "referral_purchases AS refPur
							INNER JOIN 
								" . DB_PREFIX . "order_masters as ordMas
								ON ordMas.masterOrderID = refPur.masterOrderID
							WHERE
								ordMas.customer_email = '" . $customer_email . "'
								LIMIT 0,1";
            return $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
        }
    }
    //Function to get user Details for login
    public function getUserDetailsForLogin($params = array()) {
        extract($params);
        $resultUser = array();
        if ($userEmail != '') {
            $sql = "	
						SELECT 	
							userID,
							userPassword,
							userStatus,
							userLockedDate,
							userGroupID,
							userFirstName,
							userLastName,
							userEmail,
							isverify,
							verify_code,
							code_send_date,
							userPhone,
							userDefaultLanguage,
							userInvalidAccessCount
						FROM 
							" . DB_PREFIX . "users
						WHERE 
							userStatus != 'Deleted'";
            if (isset($userEmail)) {
                $sql .= "
							AND userEmail='" . $this->escape($userEmail) . "' ";
            }
            $sql .= "
						LIMIT 1";
            
            $resultUser = $this->ObjDatabase->readValue($sql);
        }
        return $resultUser;
    }
    function verify($password, $hashedPassword) {
        return crypt($password, $hashedPassword) == $hashedPassword;
    }
    //Validate Email
    private function validatEmail($email = '') {
        if ($email != '') {
            $regexp = "/^[A-Z0-9._%+-]+@(?:[A-Z0-9-]+\.)+[A-Z]{2,4}$/i";
            if (preg_match($regexp, $email)) {
                return true;
            }
        }
        return false;
    }
        //Function to Save Google Plus user details
    public function savegPlusUserDetails($userDetails)
    {
        $userReferralID       = $this->genRandomString(8);
        $sql = "INSERT INTO 
					" . DB_PREFIX . "users 
				SET 
					userTypeID = 0 ,
                                        isverify = 'Y',
                                        userGroupID = '1',
					userName  = '" . $userDetails['id'] . "',
					userLoginType = 'GooglePlus',
					userStatus   =   'Active',
					userNewsLetterMode = 'HTML',
					userLastAccessed = NOW(), ";
        $sql .= ($userDetails['given_name'] != '') ? " userFirstName = '" . $userDetails['given_name'] . "', " : "";
		$sql .= ($userDetails['languageID'] != '') ? " userDefaultLanguage = '" . $userDetails['languageID'] . "', " : "";
        $sql .= ($userDetails['family_name'] != '') ? " userLastName = '" . $userDetails['family_name'] . "', " : "";
        $sql .= ($userDetails['email'] != '') ? " userEmail = '" . $userDetails['email'] . "', " : "";
        $sql .= ($userDetails['gender'] != '') ? " userGender = '" . $userDetails['gender'] . "', " : "";
        $sql .= ($userDetails['birthday'] != '') ? " userDOB = '" . date_format(new DateTime($userDetails['birthday']), 'd-m-Y') . "', " : "";
        $sql .= ($userReferralID != '') ? " `userReferralID` = '" . $this->userReferralIDLabel . $userReferralID . "', " : "";
        $sql = rtrim($sql, ', ');
        $resInsertUserDetails = $this->ObjDatabase->setQuery($sql);
        if ($resInsertUserDetails) {
            $userID = $this->ObjDatabase->getInsertId();
			//$this->saveFeedSettings($userID);
				/*INSERT SUBSCRIBER AND CITIES*/
			if($userID)
			{	
                $sql = "
					INSERT INTO `" . DB_PREFIX . "subscribers` 
						(
							subscEmailID,
							userID,
							contactPhone
						) 
					VALUES 
						(
							'" . $userDetails['email']  . "',
							'" . $userID . "',
							''
						)";
                $resEmail            = $this->ObjDatabase->setQuery($sql);
				$subscID              = $this->ObjDatabase->getInsertId();
				
			if( $POST['citySubscription'] == 'auto'){		 
			  	$subscriptionStatus	=	"Subscribed";	
			}
			else{
				 $subscriptionStatus	=	"Unsubscribed";	
			}
			$cityID         =   $this->escape($userDetails['selectedCity']);
//			 $sql = "
//				INSERT INTO `" . DB_PREFIX . "subscribed_cities` 
//					(
//						subscID,
//						cityID,
//						subscStatus,
//						subscPriority,
//						languageID
//					) 
//				VALUES 
//					(
//						'" . $subscID . "',
//						'" . $cityID . "',
//						'" . $subscriptionStatus . "',
//						1,
//						'" . $userDetails['userDefaultLanguage'] . "'
//					)";
//             $resEmail            = $this->ObjDatabase->setQuery($sql);
			/*END INSERT SUBSCRIBER AND CITIES*/	
			}
            if (isset($userDetails['referralID'])) {
                $referralID     = $userDetails['referralID'];
                $referralAmount = $userDetails['referralAmount'];
                $this->saveUserReferralDetails($userID, $referralID, $referralAmount);
            }
            return $userID;
        } else {
            return false;
        }
    }

    public function checkShippingIDByUserID($shippingID,$userID) {
        if($shippingID>0 && $userID>0) {
            $sql = "select userShippingID from " . DB_PREFIX . "user_shipping where userID='".$userID."' and userShippingID='".$shippingID."' and addressStatus!='Deleted'";            
            $res = $this->ObjDatabase->readValue($sql);            
            return !empty($res) ? 1:0;
        } else {
            return 0;
        }
    }

        //COUNTRY CODE FOR USER MOBILE
        public function loadAllCountryCode($language_ids) {
            
            $sql = "SELECT cntry.countryID, CONCAT(cntryDet.countryName,' (+',cntry.countryCode,')') AS Country
                    FROM 
                    `" . DB_PREFIX . "countries` AS cntry 
                    LEFT JOIN 
                        ".DB_PREFIX."country_details cntryDet 
                        ON cntryDet.countryID= cntry.countryID
                        AND cntryDet.languageID = '".$language_ids['Current']."'
                   WHERE cntry.countryAvailable ='Yes' 
                   AND cntry.countryCode!='' 
                   ORDER BY cntry.countryPriority,
                    cntryDet.countryName ASC";
            return $this->ObjDatabase->readValues($sql, MYSQLI_NUM);            
}
        //END FUNCTION        
        //FUNCTION FOR GET ALL USER MOBILE No VARIFd 
        public function ListVarifyMobile($params = array(),$type='')
	{   
            extract($params);            
            $sql="SELECT  userPhone
                FROM ".DB_PREFIX."users WHERE 1 
                AND userStatus='Active' ";
            if(isset($isverify)) {
                $sql.=" AND isverify='".$isverify."'";
            }
            if(!empty($userID) && $type!='') {
                $sql.=" AND userID='".$userID."'";
            }
            $sql.=" ORDER BY userID ";
           
            if($type!='') {
                return $this->ObjDatabase->getFieldSlave($sql);
            }
           return $this->ObjDatabase->getFields($sql);
        }
        //END FUNCTION
        //GET COUNTRY CODE FROM COUNTRY ID
        public function getCountryCode($countryID=0) {
            if($countryID > 0) {
                $sql = "SELECT `countryCode`  FROM `" . DB_PREFIX . "countries` WHERE countryID='".$countryID."' ORDER BY countryID ";
                $result=$this->ObjDatabase->readValue($sql, MYSQLI_ASSOC); 
                return $result['countryCode'];
            }
            return 0;
        }	
       //FUNCTION FOR GET USER MOBILE VARIFY DETAILS
        public function getVarifyMobile($userID,$timeout="")
		{
			if(!$timeout){
				$timeout=0;
			}
            $sql="SELECT userID, userPhone, verify_code, isverify, code_send_date,userLoginType,IF(DATE_ADD(code_send_date, INTERVAL $timeout MINUTE)>NOW(),1,0) expireCode
                FROM ".DB_PREFIX."users WHERE userID='".$userID."'";
            return $this->ObjDatabase->readValue($sql);            
        }
        //END FUNCTION
        //FUNCTION FOR UPDATE VERIFY MOBILE
        public function saveMobileVerify($POST=array()) {

            $userID=(isset($POST['userID']))?$POST['userID']:0;
            $send_date=(isset($POST['send_date']))?$POST['send_date']:'';
            $verify_code=(isset($POST['verify_code']))?$this->generateHash(trim($POST['verify_code'])):'';
            $isverify=(isset($POST['isverify']))?$POST['isverify']:'N';
            $userPhone=(isset($POST['userPhone']))?$POST['userPhone']:'';
            if($userID > 0) {
                $sql="UPDATE `" . DB_PREFIX . "users` SET isverify='".$isverify."'";
                $sql.=($userPhone!='')?", userPhone='".$userPhone."'":"";
                $sql.=($verify_code!='')?", verify_code='".$verify_code."'":"";
                $sql.=($send_date!='')?", code_send_date=NOW()":"";
                $sql.=" WHERE userID='".$userID."'";   
                $this->ObjDatabase->setQuery($sql);
                return 1;
            }  
            return 0;
        }	
    public function activateUserDetails($userID) {
	        
        $sql = " 	UPDATE 
				" . DB_PREFIX . "users 
			SET  
				userStatus='Active' ,
				userInvalidAccessCount='0',
				userLockedDate=''
			WHERE
				userID='" . $userID . "'";
       return  $this->ObjDatabase->setQuery($sql);
        
    }	
    public function checkUserPhoneExist($userPhoneVerify, $userID = '') {
        $sql = "
			SELECT EXISTS(
				SELECT 
					1
				FROM 
					" . DB_PREFIX . "users
				WHERE 
					userPhone = '" . $userPhoneVerify . "'";
        if ($userID > 0) {
            $sql .= "
					AND 
					userID != '" . $userID . "'
			";
        }
        $sql .= "
				AND 
					userStatus!='Deleted' 
				LIMIT 
					1
			) AS exist
		";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['exist'];
    }	
     public function updatePushNotificationStatus($userID,$pushStaus,$deviceType,$deviceToken)
     {
      $sql="  UPDATE `".DB_PREFIX."users` SET  pushNotification='".$pushStaus."'
                WHERE `userID` ='".$userID."' ";
      $result     =  $this->ObjDatabase->setQuery($sql);
      $status     = ($pushStaus=='Yes')?'enable':'disable';
      $sql="  UPDATE `".DB_PREFIX."push_devicetoken` SET  status='".$status."'
                WHERE `deviceType` ='".$deviceType."' AND `deviceToken` ='".$deviceToken."' ";   

      return   $result     =  $this->ObjDatabase->setQuery($sql);
       
}
      //Function to save address
    public function saveMobileAppBillingAddress($userID) {
        $userFirstName    = $this->escape($_POST['userFirstName']);
        $userLastName    = $this->escape($_POST['userLastName']);
        $userPhone    = $this->escape($_POST['userPhone']);
        $userAdress1  = $this->escape($_POST['userAdress1']);
        $userAdress2  = $this->escape($_POST['userAdress2']);
        $userCity     = $this->escape($_POST['userCity']);
        $countryID    = $_POST['countryID'];
        $stateID      = $_POST['stateID'];
        $userProvince = isset($_POST['userProvince']) ? $_POST['userProvince'] : '';
        $userZip      = $this->escape($_POST['userZip']);
        if ($_POST['stateSelectedType'] == 'state') {
            $userProvince = '';
        } else {
            $stateID = 0;
        }
        $sql = "	UPDATE 
						" . DB_PREFIX . "users 
					SET 
                                                `userFirstName`= '" . $userFirstName . "',
                                                `userLastName`= '" . $userLastName . "',
                                                `userPhone`= '" . $userPhone . "',  
						`userAdress1`='" . $userAdress1 . "', 
						`userAdress2`='" . $userAdress2 . "', 
						`userCity`= '" . $userCity . "', 
						`countryID`='" . $countryID . "' , 
						`stateID`='" . $stateID . "',
						`userProvince`= '" . $userProvince . "',
						`userZip`='" . $userZip . "' 
					WHERE userID='" . $userID . "' ";
	$result = 	$this->ObjDatabase->setQuery($sql);
        return $result;
    }
        //FUNCTION FOR GET USER MOBILE VARIFY DETAILS
        public function getUserPhone($userID)
		{
			
            $sql="SELECT  userPhone
                FROM ".DB_PREFIX."users WHERE userID='".$userID."'";
           
            return $this->ObjDatabase->getField($sql);            
        }
		function listSubscribers($languageIDs, $limitFrom, $limitTo, $POST = array(), $Subscribed = '', $from = "") {
        $dateFrom   = isset($POST['dateFrom']) ? $this->escape($POST['dateFrom']) : '';
        $dateTo     = isset($POST['dateTo']) ? $this->escape($POST['dateTo']) : '';
        $Subscribed = strtolower($Subscribed);
        if ($Subscribed == 'subscribed') {
            $status = 'subscribed';
        } else {
            $status = $this->escape($POST['status']);
        }
        $status  = strtolower($status);
        $emailID = isset($POST['emailID']) ? str_replace('_', '\_', $this->escape($POST['emailID'])) : '';
        $sql     = "	
					SELECT ";
        if ($limitTo != '')
            $sql .= " 
						SQL_CALC_FOUND_ROWS ";
        $sql .= "							
						sub.subscEmailID,
						sub.contactPhone,
						languageID, ";
        if ($from == "") {
            $sql .= "				
						sub.subscID,
						sub.unsubscrDate,";
        }
        $sql .= "				
						sub.subscribedDate";
        $sql .= "  	
					FROM 
						" . DB_PREFIX . "subscribers AS sub ";
        $sql .= " 	
					WHERE 
						1 ";
        if ($dateFrom != '') {
            if ($status == 'subscribed') {
                $sql .= " 
						AND 
						DATEDIFF( '" . $dateFrom . "',sub.subscribedDate) <=0";
            } else {
                $sql .= " 
						AND 
						DATEDIFF( '" . $dateFrom . "',sub.unsubscrDate) <=0";
            }
        }
        if ($dateTo != '') {
            if ($status == 'subscribed') {
                $sql .= " 
						AND 
						DATEDIFF('" . $dateTo . "',sub.subscribedDate) >=0";
            } else {
                $sql .= " 
						AND 
						DATEDIFF('" . $dateTo . "',sub.unsubscrDate) >=0";
            }
        }
        if ($emailID != '')
            $sql .= " 	AND 
						sub.subscEmailID LIKE '%" . $emailID . "%'";
        if (!empty($status) || $Subscribed == 'subscribed') {
            $sql .= " 	
						AND 
						sub.subscStatus ='" . $status . "'";
        }
        //order by unsubscribed date for Unsubscribed status				
        if ($status == "unsubscribed") {
            $sql .= "				
					ORDER BY 
						unsubscrDate DESC ";
        } else {
            $sql .= "				
					ORDER BY 
						subscID DESC ";
        }
        if ($limitTo != '') {
            $sql .= " 
					LIMIT  
						" .(int) $limitFrom . "," .(int) $limitTo . "";
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    //End of function
}
