<?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: AnalyticsModel.php, v 1.0 2013/04/18 20:52:11
//
class AnalyticsModel extends Model {
    public function getCurrentReferrer($httpUrl, $httpsURL, $checkDirect = '', $sslEnable = 0, $referer = '') {
        $httpReferer = $referer;
        if ($httpReferer) {
            $httpReferer = rtrim($httpReferer, "/") . "/";
        }
        $httpURL    = rtrim($httpUrl, "/") . "/";
        $httpsUrl   = rtrim($httpsURL, "/") . "/";
        $isIframe   = strpos($httpReferer, '/widget/ajaxWidget/');
        $iframeType = gettype($isIframe);
        if ($sslEnable == 1) {
            $settingsSiteUrl = $httpsURL;
        } else {
            $settingsSiteUrl = $httpURL;
        }
        if ($httpReferer && $httpReferer != '') {
            if ($checkDirect == 1) {
                if ($httpsURL) {
                    $httpsPos = strpos($httpReferer, $httpsURL);
                }
                if ($httpUrl) {
                    $httpPos = strpos($httpReferer, $httpUrl);
                }
                $httpType  = gettype($httpPos);
                $httpsType = gettype($httpsPos);
                if ((($httpType == 'integer' && $httpPos == 0) || ($httpsType == 'integer' && $httpsPos == 0)) && !$isIframe) {
                    $referrer = "direct";
                } else if ($isIframe > 0) {
                    $referrer = '';
                } else {
                    $referrer = $httpReferer;
                }
            } else {
                if ((strpos($httpReferer, "https") == 0) && (gettype(strpos($httpReferer, "https")) == 'integer')) {
                    $settingsSiteUrl = $httpsURL;
                } else {
                    $settingsSiteUrl = $httpURL;
                }
                $referrer = str_replace($settingsSiteUrl, '', $httpReferer);
            }
        } else {
            $referrer = "";
        }
        return $referrer;
    }
    //FUNCTION TO SAVE VISITOR DETAILS
    public function saveVisitorDetails($userType = 'guest', $languageIDs = array(), $logParams = array()) {
        $userAgent     = $_SERVER['HTTP_USER_AGENT']; //BROWSER USER AGENT		
        $remoteAddress = $this->getIpAddress(); //REMOTE IP ADDRESS
        $languageID    = $languageIDs['Current'];
        $tableName     = DB_PREFIX . "stat_info_log";
        $sql           = "	INSERT INTO
									" . $tableName . "
										(
											entryPageID,
											entryTime,
											userAgent,
											httpReferer,
											remoteAddress,
											userType,
											languageID,
											browserID,
											osID,
											countryID,
											cityID,
											searchKeyWord,
											searchEngineName,
											referrerType,
											referrerDetails,
											referrerWebSite,
											newVisit,
											platform";
        if ($logParams['visitorCookie'] > 0) {
            $sql .= ",visitorCookieID ";
        }
        $sql .= "	)
								VALUES
										(
											'" . $logParams['pageID'] . "',
											NOW(),
											'" . $this->escape($userAgent, false) . "',
											'" . $this->escape($logParams['httpReferer'], false) . "',
											'" . $remoteAddress . "',
											'" . $userType . "',
											'" . $languageID . "',
											'" . $logParams['browser'] . "',
											'" . $logParams['os'] . "',
											'" . $logParams['country'] . "',
											'" . $logParams['city'] . "',
											'" . (isset($logParams['keyWords']) ? $logParams['keyWords'] : '') . "',
											'" . (isset($logParams['searchEngine']) ? $logParams['searchEngine'] : '') . "',
											'" . (isset($logParams['refType']) ? $logParams['refType'] : '') . "',
											'" . (isset($logParams['refDetails']) ? $logParams['refDetails'] : '') . "',
											'" . (isset($logParams['nameOfHost']) ? $logParams['nameOfHost'] : '') . "',
											'" . (isset($logParams['newVisit']) ? $logParams['newVisit'] : '') . "',
											'site'";
        if ($logParams['visitorCookie'] > 0) {
            $sql .= ",'" . $logParams['visitorCookie'] . "'";
        }
        $sql .= ")";
        $this->ObjDatabase->setQuery($sql);
        $visitorID = $this->ObjDatabase->getInsertId();
        return $visitorID;
    }
    //FUNCTION TO SAVE EACH AND EVERY URLS
    public function saveURLInfo($visitorID, $pageID, $title = '') {
        if ($visitorID > 0) {
            $tableName = DB_PREFIX . "stat_url_log";
            $sql       = "	INSERT INTO
										" . $tableName . "
											(
												visitorID,
												pageID,
												visitTime,
												pageTitle
											)
									VALUES
											(
												'" . $visitorID . "',
												'" . $pageID . "',
												NOW(),
												'" . $this->escape($title, false) . "'
											)";
            $this->ObjDatabase->setQuery($sql);
        }
        return true;
    }
    public function updateVisitorType($visitorID, $userID = 0, $userType = '') {
        if ($visitorID > 0) {
            $sql = " UPDATE
							" . DB_PREFIX . "stat_info_log
						SET ";
            if ($visitorID > 0) {
                $sql .= "userID='" . $userID . "',
						loginTime=NOW() ";
                if ($userType) {
                    $sql .= " ,userType='" . $userType . "' ";
                }
            }
            $sql .= " WHERE
							visitorID=" . (int) $visitorID . "
						LIMIT 1";
            $this->ObjDatabase->setQuery($sql);
            if ($userType == 'user' && $userID > 0) {
                $visitorUserID = $this->getRegUserVisitor($userID, $visitorID);
                if ($visitorUserID > 0) {
                    $sql = " UPDATE
									" . DB_PREFIX . "stat_info_log
								SET 
									newVisit=0 
								WHERE
									visitorID=" . (int) $visitorID . "
									LIMIT 1";
                    $this->ObjDatabase->setQuery($sql);
                }
            }
        }
    }
    //FUNCTION TO RETRIVE VISITOR DETAILS
    public function getRegUserVisitor($userID = 0, $visitorID = 0) {
        if ($userID > 0) {
            $sql               = "	SELECT 
								userID
							FROM
								" . DB_PREFIX . "stat_info_log 
							WHERE
								userID=" . (int) $userID . " 
								AND
								visitorID!=" . (int) $visitorID . "
							LIMIT 1";
            $resVisitorDetails = $this->ObjDatabase->readValue($sql);
            if ($resVisitorDetails['userID'] > 0) {
                return $resVisitorDetails['userID'];
            } else {
                $sql               = "	SELECT 
									userID
								FROM
									" . DB_PREFIX . "stat_info_log_reg_users 
								WHERE
									userID=" . (int) $userID . " 
								LIMIT 1";
                $resVisitorDetails = $this->ObjDatabase->readValue($sql);
                return $resVisitorDetails['userID'];
            }
        }
    }
    public function updateVisitorLogOut($visitorID) {
        if ($visitorID > 0) {
            $sql = " UPDATE
							" . DB_PREFIX . "stat_info_log
						SET 
							logoutTime=NOW() 
						WHERE
							visitorID=" . (int) $visitorID . "
						LIMIT 1";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    //FUNCTION TO RETRIVE VISITOR DETAILS
    public function getDetails($visitorID = '') {
        $sql = "	SELECT 
							userID,
							doneSubscribe,
							orderAmount,
							cityID,
							countryID,
							browserID,
							osID
						FROM
							" . DB_PREFIX . "stat_info_log ";
        if ($visitorID) {
            $sql .= "
						WHERE
							visitorID=" . (int) $visitorID . "";
        }
        $sql .= " LIMIT 1";
        $resVisitorDetails = $this->ObjDatabase->readValue($sql);
        return $resVisitorDetails;
    }
    //FUNCTION TO UPDATE ACTION DETAILS
    public function updateVisitorActions($visitorID, $actionName, $orderAmount = 0) {
        if ($visitorID > 0) {
            $sql = "	UPDATE
								" . DB_PREFIX . "stat_info_log
							SET ";
            if ($actionName == 'order') {
                $sql .= "	orderPlaced=1,
							orderAmount='" . $orderAmount . "',
							lastorderplacedtime=NOW()";
            } else if ($actionName == 'signup') {
                $sql .= "doneSignUp=1";
            } else if ($actionName == 'subscribe') {
                $sql .= "doneSubscribe=1";
            }
            $sql .= " WHERE
							visitorID=" . (int) $visitorID . " LIMIT 1";
            $this->ObjDatabase->setQuery($sql);
        }
        return true;
    }
    public function updateCampaignDetails($campaignID = 0, $action = 'purchase', $orderAmount = 0) {
        if ($action == 'purchase') {
            $orderAmount = number_format($orderAmount, 2, '.', '');
            $sql         = " UPDATE 
									" . DB_PREFIX . "campaigns 
								SET 
									revenue=revenue+" . $orderAmount . ",purchaseCount=purchaseCount+1
								WHERE 
									campaignID ='" . $campaignID . "'";
            $resUpdate   = $this->ObjDatabase->setQuery($sql);
        } else if ($action == 'signup') {
            $orderAmount = number_format($$orderAmount, 2, '.', '');
            $sql         = " UPDATE 
									" . DB_PREFIX . "campaigns 
								SET
									registrationCount=registrationCount+1 
								WHERE 
									campaignID ='" . $campaignID . "'";
            $resUpdate   = $this->ObjDatabase->setQuery($sql);
        }
    }
    //Function to get the last updated id of the url log table (updating previous cron)
    public function getLastUpdatedID($type = 'analytics_row') {
        $sql              = "	SELECT 
							lastAffectedID 
						FROM 
							" . DB_PREFIX . "crone_execution_details 
						WHERE
							cronType='" . $type . "'
						ORDER BY
							croneID desc LIMIT 0 , 1";
        $resLastUpdatedID = $this->ObjDatabase->readValue($sql);
        return $resLastUpdatedID['lastAffectedID'];
    }
    //Function For get all url log details from the last updated record in previews cron.
    public function getUlrLogDetails($visitorID = 0, $limit) {
        $sql = " SELECT 
						*,
						DATE(entryTime) as entryDate,
						HOUR(entryTime) AS entryHour,
						DAYOFWEEK(entryTime) AS entryWeek
					FROM 
						" . DB_PREFIX . "stat_info_log vl  
					WHERE 
						vl.visitorID >=" . (int) $visitorID . "
						AND DATE(entryTime) <= DATE(DATE_SUB(NOW(), INTERVAL 0 DAY)) 
					ORDER BY 
						entryTime asc";
        if ($limit > 0) {
            $sql .= " Limit 0 , " . $limit;
        }
        $resUrlLogDetails = $this->ObjDatabase->readValues($sql);
        return $resUrlLogDetails;
    }
    public function getUlrLogDetailsSub($visitorID = 0) {
        $sql              = " SELECT 
					log.* 
				FROM 
					" . DB_PREFIX . "stat_url_log log 
				WHERE 
					log.visitorID =" . $visitorID . " 
				ORDER BY 
					urlID desc  Limit 0 , 1";
        $resUrlLogDetails = $this->ObjDatabase->readValue($sql);
        return $resUrlLogDetails;
    }
    //function for getting the distinct loged users id for a specific date
    //@usage => frontend/cron
    public function getExisitingUserIDs($date = '') {
        $sql               = "	SELECT 
								distinct(userID)
							FROM
								" . DB_PREFIX . "stat_info_log 
							LIMIT 1";
        $resVisitorDetails = $this->ObjDatabase->readValues($sql);
        return $resVisitorDetails;
    }
    //function for getting the distinct ips of gust visitors for a specific date
    //@usage => frontend/cron
    public function getExisitingIPs($date = '') {
        $sql               = "	SELECT 
								distinct(userID)
							FROM
								" . DB_PREFIX . "stat_info_log 
							LIMIT 1";
        $resVisitorDetails = $this->ObjDatabase->readValues($sql);
        return $resVisitorDetails;
    }
    //function for getting the distinct loged users id for a specific current day
    //@usage => frontend/cron
    public function getTodayUserIDs($date = '') {
        $sql               = "	SELECT 
								distinct(userID)
							FROM
								" . DB_PREFIX . "stat_info_log 
							LIMIT 1";
        $resVisitorDetails = $this->ObjDatabase->readValues($sql);
        return $resVisitorDetails;
    }
    //Function for getting all available serch engines
    public function getAllSearchEngines() {
        $sql                   = "	SELECT 
							* 
						FROM 
							" . DB_PREFIX . "search_engine";
        $resSelectSearchEngine = $this->ObjDatabase->readValues($sql);
        return $resSelectSearchEngine;
    }
    //Function to keywork and serch Engine name from httpReferrer
    public function getKeyWordAndSearchEngine($httpReferrer) {
        $resSelectSearchEngine = $this->getAllSearchEngines();
        foreach ($resSelectSearchEngine as $searchEngine) {
            if (strpos($httpReferrer, $searchEngine['searchEngineURL']) > 0) {
                $processedUrl = parse_url($httpReferrer);
                $queryString  = $processedUrl['query'];
                # split into arguments and values
                $queryString  = explode('&', $queryString);
                $args         = array(); // return array
                foreach ($queryString as $chunk) {
                    $chunk = explode('=', $chunk);
                    // it's only really worth keeping if the parameter
                    // has an argument.
                    if (count($chunk) == 2) {
                        list($key, $val) = $chunk;
                        $args[$key] = urldecode($val);
                    }
                }
                $data['keyWord']      = $args[$searchEngine['searchParameter']];
                $data['searchEngine'] = $searchEngine['searchEngineName'];
            }
        }
        $data = isset($data) ? $data : array();
        return $data;
    }
    //Function to get the page count of a particular visitor
    public function getPagecount($visitorID) {
        if ($visitorID > 0) {
            $sql                = "	SELECT 
								COUNT(*) AS pagecount 
							FROM 
								" . DB_PREFIX . "stat_url_log 
							WHERE
									visitorID=" . $visitorID;
            $resSelectPageCount = $this->ObjDatabase->readValues($sql);
            return $resSelectPageCount[0]['pagecount'];
        }
    }
    //FUNCTION TO CHECK WHETHER A VISITOR IS CRAWLER OR NOT
    public function checkForCrawlers($userAgent = '', $browser = '', $operatingSystem = '', $pageCount = '', $remoteAddress, $isaffiliate = '') {
        $crawlerSuspecedIPs = array(
            "65.52.0.56",
            "65.52.1.11",
            "65.52.0.95",
            "65.52.0.29"
        );
        $crawlerFlag        = 0;
        $isCrawler          = 0;
        foreach ($crawlerSuspecedIPs as $crawlerss) {
            if ($remoteAddress == $crawlerss) {
                $crawlerFlag = 1;
                break;
            }
        }
        if ($userAgent == HTTP_GOOGLEPLUS_USER_AGENT) {
            $isCrawler = 1;
        }
        if ($isaffiliate == 1) {
            if (($userAgent == '' || (strpos($userAgent, "http") > 0) || (strpos($userAgent, "https") > 0) || (strpos($userAgent, ".com") > 0) || (strpos($userAgent, "bot") > 0) || $crawlerFlag == 1)) {
                $isCrawler = 1;
            }
        } else {
            if (($userAgent == '' || (strpos($userAgent, "http") > 0) || (strpos($userAgent, "https") > 0) || (strpos($userAgent, ".com") > 0) || (strpos($userAgent, "bot") > 0) || $crawlerFlag == 1 || $browser == '' || $browser == 'unknown' || $operatingSystem == '' || $operatingSystem == 'unknown') && $pageCount == 1) {
                $isCrawler = 1;
            }
        }
        return $isCrawler;
    }
    public function getUserDetails($userID) {
        if ($userID > 0) {
            $sql = "SELECT 
						usr.userDOB,
						usr.userGender
					FROM 
						" . DB_PREFIX . "users as usr 
					WHERE 
						usr.userID='" . $userID . "' 
					LIMIT 1 ";
            return $this->ObjDatabase->readValue($sql);
        }
    }
    //Function to get age from date of birth 
    //@date format mysql date
    public function getAge($dateOfBirth) {
        list($y, $m, $d) = explode("-", $dateOfBirth);
        $age = date('Y') - $y;
        date('md') < $m . $d ? $age-- : null;
        return $age;
    }
    //function to update the new returing and unique vist count based on date at the time of cron
    //@usage => frontend/cron
    public function updateCounts($newVisitCount, $returningVisitCount, $returningVisitorCount, $totalVisitCount, $date) {
        $sql         = "	SELECT 
							visitorCountID 
						FROM 
							" . DB_PREFIX . "visitor_count_info 
						WHERE 
							date='" . $date . "'";
        $resTodaysID = $this->ObjDatabase->readValue($sql);
        if ($resTodaysID['visitorCountID'] > 0) {
            $sql       = "	UPDATE 
								" . DB_PREFIX . "visitor_count_info 
							SET 
								newVisitorCount=newVisitorCount+" . $newVisitCount . ",
								returningVisitorCount=returningVisitorCount+" . $returningVisitorCount . ",
								returningVisitCount=returningVisitCount+" . $returningVisitCount . ",
								totalVisitCount=totalVisitCount+" . $totalVisitCount . " 
							WHERE 

								visitorCountID =" . $resTodaysID['visitorCountID'];
            $resUpdate = $this->ObjDatabase->setQuery($sql);
        } else {
            $sql            = "	INSERT INTO 
								" . DB_PREFIX . "visitor_count_info
									(
										date,
										newVisitorCount,
										returningVisitorCount,
										returningVisitCount,
										totalVisitCount
									) 
							VALUES
									(
										'" . $date . "',
										" . $newVisitCount . ",
										" . $returningVisitorCount . ",
										" . $returningVisitCount . ",
										" . $totalVisitCount . "
									)";
            $resCreateEntry = $this->ObjDatabase->setQuery($sql);
        }
    }
    //Function for save the cron exicution details
    public function saveCronExicution($count, $urlID, $type = 'analytics_row') {
        $sql = "	DELETE 
							" . DB_PREFIX . "crone_execution_details
						WHERE
							cronType='" . $type . "'";
        //$this->ObjDatabase->setQuery($sql);
        $sql = "	INSERT INTO 
							" . DB_PREFIX . "crone_execution_details
								(
									executionTime,
									rowsAffected,
									lastAffectedID,
									cronType
								)
						VALUES 
								(
									NOW(),
									'" . $count . "',
									'" . $urlID . "',
									'" . $type . "'
								) ";
        return $this->ObjDatabase->setQuery($sql);
    }
    public function saveVisitorSearch($visitorID, $searchID) {
        $sql = "	INSERT IGNORE INTO
							" . DB_PREFIX . "catalogsearch_query_visitor
										(
											visitorID,
											queryID,
											searchTime
										)
								VALUES
										(
											'" . $visitorID . "',
											'" . $searchID . "',
											NOW()
										)";
        $this->ObjDatabase->setQuery($sql);
        $visitorSearchID = $this->ObjDatabase->getInsertId();
        return $visitorSearchID;
    }
    public function getNextVisitorCookieID() {
        $sql     = " SELECT
						MAX(visitorCookieID)+1 as nextID
					FROM 
						" . DB_PREFIX . "stat_info_log LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['nextID'];
    }
    public function getStatPageID($url) {
        $sql     = " SELECT
						pageID
					FROM 
						" . DB_PREFIX . "stat_pages
					WHERE 
						pageUrl='" . $this->escape($url, false) . "'
					LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['pageID'];
    }
    public function addStatPage($url = '', $pageName = '') {
        $sql = "	INSERT INTO
							" . DB_PREFIX . "stat_pages
										(
											pageUrl,
											pageName
										)
								VALUES
										(
											'" . $this->escape($url, false) . "',
											'" . $pageName . "'
										)";
        $this->ObjDatabase->setQuery($sql);
        $pageID = $this->ObjDatabase->getInsertId();
        return $pageID;
    }
    public function getBrowserID($browser) {
        $sql     = " SELECT
						browserID
					FROM 
						" . DB_PREFIX . "stat_browser
					WHERE 
						browserName='" . $this->escape($browser, false) . "'
					LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['browserID'];
    }
    public function addBrowser($browser = '') {
        $sql = "	INSERT INTO
							" . DB_PREFIX . "stat_browser
										(
											browserName
										)
								VALUES
										(
											'" . $this->escape($browser, false) . "'
										)";
        $this->ObjDatabase->setQuery($sql);
        $browserID = $this->ObjDatabase->getInsertId();
        return $browserID;
    }
    public function getOsID($os) {
        $sql     = " SELECT
						osID
					FROM 
						" . DB_PREFIX . "stat_os
					WHERE 
						osName='" . $this->escape($os, false) . "'
					LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['osID'];
    }
    public function addOs($os = '') {
        $sql = "	INSERT INTO
							" . DB_PREFIX . "stat_os
										(
											osName
										)
								VALUES
										(
											'" . $this->escape($os, false) . "'
										)";
        $this->ObjDatabase->setQuery($sql);
        $osID = $this->ObjDatabase->getInsertId();
        return $osID;
    }
    public function getCityID($city) {
        $sql     = " SELECT
						cityID
					FROM 
						" . DB_PREFIX . "stat_city
					WHERE 
						cityName='" . $this->escape($city, false) . "'
					LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['cityID'];
    }
    public function addCity($city = '') {
        $sql = "	INSERT INTO
							" . DB_PREFIX . "stat_city
										(
											cityName
										)
								VALUES
										(
											'" . $this->escape($city, false) . "'
										)";
        $this->ObjDatabase->setQuery($sql);
        $cityID = $this->ObjDatabase->getInsertId();
        return $cityID;
    }
    public function getCountryID($country) {
        $sql     = " SELECT
						countryID
					FROM 
						" . DB_PREFIX . "stat_country
					WHERE 
						countryName='" . $this->escape($country, false) . "'
					LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['countryID'];
    }
    public function addCountry($country = '') {
        $sql = "	INSERT INTO
							" . DB_PREFIX . "stat_country
										(
											countryName
										)
								VALUES
										(
											'" . $this->escape($country, false) . "'
										)";
        $this->ObjDatabase->setQuery($sql);
        $countryID = $this->ObjDatabase->getInsertId();
        return $countryID;
    }
    //For setting the visitores language
    //@usage => frondend/language change
    public function setVisitorLanguage($LanguageID, $visitorID) {
        if ($visitorID > 0) {
            $sqlUpdate = "update " . DB_PREFIX . "stat_info_log set languageID=" . $LanguageID . " WHERE 
			visitorID =" . $visitorID . " limit 1";
            $resUpdate = $this->ObjDatabase->setQuery($sqlUpdate);
        }
    }
    public function logRegUser($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['entryDate'] . "',
								'" . $value['visitor'] . "',
								'" . $value['userID'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['loginTimeDuration'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_log_reg_users
											(
												entryDate,
												visitor,
												userID,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												pageCount,
												loginTimeDuration
											)
									VALUES " . $value_str . "";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logRegUserEngagement($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['entryDate'] . "',
								'" . $value['visitor'] . "',
								'" . $value['userID'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['loginTimeDuration'] . "',
								'" . $value['productReviews'] . "',
								'" . $value['businessReviews'] . "',
								'" . $value['usersFollowBusiness'] . "',
								'" . $value['referralsCount'] . "',
								'" . $value['rank'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_log_reg_users_engagement
											(
												entryDate,
												visitor,
												userID,
												orderPlaced,
												doneSubscribe,
												pageCount,
												loginTimeDuration,
												productReviews,
												businessReviews,
												usersFollowBusiness,
												referralsCount,
												rank
											)
									VALUES " . $value_str . "";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logRegUserReferralEngagement($user_id, $referralsCount, $rank, $entryDate) {
        $sql = " INSERT INTO
								" . DB_PREFIX . "stat_info_log_reg_users_engagement
											(
												entryDate,
												visitor,
												userID,
												orderPlaced,
												doneSubscribe,
												pageCount,
												loginTimeDuration,
												productReviews,
												businessReviews,
												usersFollowBusiness,
												referralsCount,
												rank
											)
									VALUES 
										('" . $entryDate . "',
										'0',
										'" . $user_id . "',
										'0',
										'0',
										'0',
										'0',
										'0',
										'0',
										'0',
										'" . $referralsCount . "',
										'" . $rank . "')";
        //echo $sql;
        $this->ObjDatabase->setQuery($sql);
    }
    public function logBrowserDetails($params = array()) {
        //extract($params);
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['browserID'] . "',
								'" . $value['osID'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_browser
											(
												browserID,
												osID,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logHourDetails($params = array()) {
        //extract($params);
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['hour'] . "',
								'" . $value['weekDay'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_hour
											(
												countryID,
												cityID,
												hour,
												weekDay,
												entryDate,
												platform,
												visitor,
												newVisitor,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logHourOverviewDetails($params = array()) {
        //extract($params);
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['hour'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_hour_overview
											(
												hour,
												entryDate,
												platform,
												visitor,
												newVisitor,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    function logLocationDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['languageID'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_location
											(
												countryID,
												cityID,
												languageID,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    function logLocationOverviewDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['cityID'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_location_overview
											(
												cityID,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logTrafficDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['referrerType'] . "',
								'" . $value['referrerDetails'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic
											(
												countryID,
												cityID,
												referrerType,
												referrerDetails,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logDirectTrafficDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_direct_traffic
											(
												countryID,
												cityID,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logSearchKeywordTrafficDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['searchKeyWord'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_search_keyword
											(
												countryID,
												cityID,
												searchKeyWord,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logSearchEnginenameTrafficDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['searchEngineName'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_search_enginename
											(
												countryID,
												cityID,
												searchEngineName,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logRefWebSiteTrafficDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['referrerWebSite'] . "',
								'" . $value['entryDate'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_ref_website
											(
												countryID,
												cityID,
												referrerWebSite,
												entryDate,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBounceBrowserDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['browserID'] . "',
									'" . $value['entryDate'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_browser_bounce
											(
												countryID,
												cityID,
												browserID,
												entryDate,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBounceOsDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['osID'] . "',
									'" . $value['entryDate'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_os_bounce
											(
												countryID,
												cityID,
												osID,
												entryDate,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBouncePageDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['pageID'] . "',
									'" . $value['entryDate'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_page_bounce
											(
												countryID,
												cityID,
												pageID,
												entryDate,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBounceTrafficDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['referrerType'] . "',
									'" . $value['referrerDetails'] . "',
									'" . $value['referrerWebSite'] . "',
									'" . $value['entryDate'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_bounce
											(
												countryID,
												cityID,
												referrerType,
												referrerDetails,
												referrerWebSite,
												entryDate,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    //Yearly aggragtion
    public function logBrowserDetailsYear($params = array()) {
        //extract($params);
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['browserID'] . "',
								'" . $value['osID'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_browser_month
											(
												browserID,
												osID,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logHourDetailsYear($params = array()) {
        //extract($params);
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['hour'] . "',
								'" . $value['weekDay'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_hour_month
											(
												countryID,
												cityID,
												hour,
												weekDay,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    function logLocationDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['languageID'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_location_month
											(
												countryID,
												cityID,
												languageID,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logTrafficDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['referrerType'] . "',
								'" . $value['referrerDetails'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_month
											(
												countryID,
												cityID,
												referrerType,
												referrerDetails,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logDirectTrafficDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_direct_traffic_month
											(
												countryID,
												cityID,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logSearchKeywordTrafficDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['searchKeyWord'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_search_keyword_month
											(
												countryID,
												cityID,
												searchKeyWord,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logSearchEnginenameTrafficDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['searchEngineName'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_search_enginename_month
											(
												countryID,
												cityID,
												searchEngineName,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logRefWebSiteTrafficDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['countryID'] . "',
								'" . $value['cityID'] . "',
								'" . $value['referrerWebSite'] . "',
								'" . $value['year'] . "',
								'" . $value['month'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['pageCount'] . "',
								'" . $value['duration'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_ref_website_month
											(
												countryID,
												cityID,
												referrerWebSite,
												entryYear,
												entryMonth,
												platform,
												visitor,
												newVisitor,
												pageCount,
												duration,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											pageCount=pageCount+VALUES(pageCount),
											duration=duration+VALUES(duration),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBounceBrowserDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['browserID'] . "',
									'" . $value['year'] . "',
									'" . $value['month'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_browser_bounce_month
											(
												countryID,
												cityID,
												browserID,
												entryYear,
												entryMonth,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBounceOsDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['osID'] . "',
									'" . $value['year'] . "',
									'" . $value['month'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_os_bounce_month
											(
												countryID,
												cityID,
												osID,
												entryYear,
												entryMonth,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBouncePageDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['pageID'] . "',
									'" . $value['year'] . "',
									'" . $value['month'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_page_bounce_month
											(
												countryID,
												cityID,
												pageID,
												entryYear,
												entryMonth,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logBounceTrafficDetailsYear($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                //if($value['bounceVisitor']>0)
                //{
                $value_str .= "('" . $value['countryID'] . "',
									'" . $value['cityID'] . "',
									'" . $value['referrerType'] . "',
									'" . $value['referrerDetails'] . "',
									'" . $value['referrerWebSite'] . "',
									'" . $value['year'] . "',
									'" . $value['month'] . "',
									'" . $value['platform'] . "',
									'" . $value['visitor'] . "',
									'" . (isset($value['bounceVisitor']) ? $value['bounceVisitor'] : 0) . "',
									'" . $value['hashKey'] . "'),";
                //}				
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_info_traffic_bounce_month
											(
												countryID,
												cityID,
												referrerType,
												referrerDetails,
												referrerWebSite,
												entryYear,
												entryMonth,
												platform,
												visitor,
												bounceVisitor,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											bounceVisitor=bounceVisitor+VALUES(bounceVisitor)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function checkVisitorSearchExist($visitorID = 0) {
        $sql     = " SELECT
						visitorSearchID
					FROM 
						" . DB_PREFIX . "catalogsearch_query_visitor
					WHERE 
						visitorID='" . $visitorID . "'
					LIMIT 1";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['visitorSearchID'];
    }
    public function logVisitorSearchDetails($visitorID = 0) {
        if ($visitorID > 0) {
            $sql = " INSERT INTO
								" . DB_PREFIX . "stat_info_search
											(
												visitorID,
												pageID,
												browserID,
												osID,
												userType,
												orderPlaced,
												doneSubscribe,
												doneSignUp,
												cityID,
												countryID
											)
									SELECT
										visitorID,
										entryPageID,
										browserID,
										osID,
										userType,
										orderPlaced,
										doneSubscribe,
										doneSignUp,
										cityID,
										countryID
									FROM 	
										" . DB_PREFIX . "stat_info_log
									WHERE
										visitorID='" . $visitorID . "'";
            $this->ObjDatabase->setQuery($sql);
            $sql = " INSERT INTO
								" . DB_PREFIX . "stat_info_url_search
											(
												visitorID,
												visitTime,
												pageID,
												pageTitle
											)
									SELECT 
										visitorID,
										visitTime,
										pageID,
										pageTitle
									FROM 	
										" . DB_PREFIX . "stat_url_log
									WHERE
										visitorID='" . $visitorID . "'";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function logDateBasedOverviewDetails($params = array()) {
        if (count($params) > 0) {
            $value_str = '';
            foreach ($params as $key => $value) {
                $value_str .= "('" . $value['entryDate'] . "',
								'" . $value['visitor'] . "',
								'" . $value['newVisitor'] . "',
								'" . $value['uniqueVisitor'] . "',
								'" . $value['orderPlaced'] . "',
								'" . $value['orderAmount'] . "',
								'" . $value['doneSubscribe'] . "',
								'" . $value['doneSignUp'] . "',
								'" . $value['platform'] . "',
								'" . $value['visitDuration'] . "',
								'" . $value['pageCount'] . "',
								'" . (isset($value['bounceCount']) ? $value['bounceCount'] : 0) . "',
								'" . $value['hashKey'] . "'),";
            }
            $value_str = substr($value_str, 0, -1);
            $sql       = " INSERT INTO
								" . DB_PREFIX . "stat_count_overview
											(
												entryDate,
												visitor,
												newVisitor,
												uniqueVisitor,
												orderPlaced,
												orderAmount,
												doneSubscribe,
												doneSignUp,
												platform,
												visitDuration,
												pageCount,
												bounceCount,
												hashKey
											)
									VALUES
											" . $value_str . " 
									ON DUPLICATE KEY 
										UPDATE
											visitor=visitor+VALUES(visitor),
											newVisitor=newVisitor+VALUES(newVisitor),
											uniqueVisitor=uniqueVisitor+VALUES(uniqueVisitor),
											orderPlaced=orderPlaced+VALUES(orderPlaced),
											orderAmount=orderAmount+VALUES(orderAmount),
											doneSubscribe=doneSubscribe+VALUES(doneSubscribe),
											doneSignUp=doneSignUp+VALUES(doneSignUp),
											visitDuration=visitDuration+VALUES(visitDuration),
											pageCount=pageCount+VALUES(pageCount),
											bounceCount=bounceCount+VALUES(bounceCount)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function getUniqueVisitorUserIDCount() {
        $sql     = " SELECT
						COUNT(*) as cnt
					FROM 
						" . DB_PREFIX . "stat_info_log_reg_users
					GROUP BY
						userID";
        $results = $this->ObjDatabase->readValue($sql);
        return $results['cnt'];
    }
    public function getOverviewSumValues() {
        $sql     = " SELECT
						SUM(visitor) as visitor,
						SUM(newVisitor) as newVisitor,
						SUM(uniqueVisitor) as uniqueVisitor,
						SUM(orderPlaced) as orderPlaced,
						SUM(orderAmount) as orderAmount,
						SUM(doneSubscribe) as doneSubscribe,
						SUM(doneSignUp) as doneSignUp,
						SUM(visitDuration) as visitDuration,
						SUM(pageCount) as pageCount,
						SUM(bounceCount) as bounceCount
					FROM 
						" . DB_PREFIX . "stat_count_overview";
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getOverViewDetails() {
        $sql     = " SELECT
						SUM(visitor) as visitor,
						SUM(newVisitor) as newVisitor,
						SUM(uniqueVisitor) as uniqueVisitor,
						SUM(orderPlaced) as orderPlaced,
						SUM(orderAmount) as orderAmount,
						SUM(doneSubscribe) as doneSubscribe,
						SUM(doneSignUp) as doneSignUp,
						SUM(visitDuration) as visitDuration,
						SUM(pageCount) as pageCount,
						SUM(bounceCount) as bounceCount
					FROM 
						" . DB_PREFIX . "stat_count_overview";
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    //FUNCTION TO GET LAST EXECUTED CRONE TIME
    public function getLastCronExecutedValue() {
        $sql                     = "	SELECT 
							executionTime AS lasttym 
						FROM 
							" . DB_PREFIX . "crone_execution_details 
						WHERE 
							cronType='analytics_row' 
						ORDER BY 
							croneID 
							DESC LIMIT 0,1";
        $resLastCronXecutedValue = $this->ObjDatabase->readValue($sql);
        return $resLastCronXecutedValue['lasttym'];
    }
    public function getDateBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						SUM(newVisitor) as newVisitor,
						SUM(uniqueVisitor) as uniqueVisitor,
						SUM(orderPlaced) as orderPlaced,
						SUM(orderAmount) as orderAmount,
						SUM(doneSubscribe) as doneSubscribe,
						SUM(doneSignUp) as doneSignUp,
						SUM(visitDuration) as visitDuration,
						SUM(pageCount) as pageCount,
						SUM(bounceCount) as bounceCount,
						entryDate
					FROM 
						" . DB_PREFIX . "stat_count_overview WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= "GROUP BY
						entryDate
					ORDER BY
						entryDate ASC";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getHourBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						SUM(orderPlaced) as orderPlaced,
						SUM(doneSubscribe) as doneSubscribe,
						SUM(doneSignUp) as doneSignUp,
						entryDate,
						hour
					FROM 
						" . DB_PREFIX . "stat_info_hour_overview WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= "GROUP BY
						hour
					ORDER BY
						hour ASC";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getLocationBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						entryDate,
						log.cityID,
						city.cityName
					FROM 
						" . DB_PREFIX . "stat_info_location_overview as log 
					LEFT JOIN 
						" . DB_PREFIX . "stat_city city
					ON (city.cityID=log.cityID)	
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= " GROUP BY
						log.cityID
					ORDER BY
						visitor DESC
					LIMIT 0,6";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getBrowserBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						entryDate,
						log.browserID,
						browser.browserName
					FROM 
						" . DB_PREFIX . "stat_info_browser as log 
					LEFT JOIN 
						" . DB_PREFIX . "stat_browser browser
					ON (browser.browserID=log.browserID)	
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= " GROUP BY
						log.browserID
					ORDER BY
						visitor DESC
					LIMIT 0,6";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getReferrerWebSiteTrafficBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						referrerWebSite
					FROM 
						" . DB_PREFIX . "stat_info_traffic_ref_website as log 
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= " GROUP BY
						log.referrerWebSite
					ORDER BY
						visitor DESC
					LIMIT 0,3";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getReferrerDetailsTrafficBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						referrerDetails
					FROM 
						" . DB_PREFIX . "stat_info_traffic as log 
						WHERE referrerDetails!='' ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= " GROUP BY
						log.referrerDetails
					ORDER BY
						visitor DESC
					LIMIT 0,3";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getSearchEngineNameTrafficBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						searchEngineName
					FROM 
						" . DB_PREFIX . "stat_info_traffic_search_enginename as log 
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= " GROUP BY
						log.searchEngineName
					ORDER BY
						visitor DESC
					LIMIT 0,3";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getSearchKeywordTrafficBasedResult($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitor,
						searchKeyWord
					FROM 
						" . DB_PREFIX . "stat_info_traffic_search_keyword as log 
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $sql .= " GROUP BY
						log.searchKeyWord
					ORDER BY
						visitor DESC
					LIMIT 0,7";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getAffiliateCount($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as affiliateCount
					FROM 
						" . DB_PREFIX . "stat_info_traffic as log 
						WHERE referrerType='affiliate' ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results['affiliateCount'];
    }
    public function getTotalVisitorCount($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(visitor) as visitorCount
					FROM 
						" . DB_PREFIX . "stat_count_overview as log 
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results['visitorCount'];
    }
    public function setDummyData() {
        $sql = "update dp_stat_url_log set visitTime = visitTime - INTERVAL 1 DAY";
        $this->ObjDatabase->setQuery($sql);
        $sql = "update dp_stat_info_log set loginTime = loginTime - INTERVAL 1 DAY";
        $this->ObjDatabase->setQuery($sql);
        $sql = "update dp_stat_info_log set entryTime = entryTime - INTERVAL 1 DAY";
        $this->ObjDatabase->setQuery($sql);
    }
    //Function ti get all unique cities and countries for filter optiopns
    public function getdistinctCitiesAndCountries() {
        $sql              = "	SELECT 
							cityID,
							cityName 
						FROM 
							" . DB_PREFIX . "stat_city 
						ORDER BY 
							cityName ASC";
        $resSelectCity    = $this->ObjDatabase->readValues($sql);
        $sql              = "	SELECT
							countryID,
							countryName
						FROM 
							" . DB_PREFIX . "stat_country 
						ORDER BY 
							countryName ASC";
        $resSelectCountry = $this->ObjDatabase->readValues($sql);
        return (array(
            $resSelectCity,
            $resSelectCountry
        ));
    }
    //Function ti get all unique browser and os for filter optiopns
    public function getdistinctOsAndBrowsers() {
        $sql        = "	SELECT 
							browserID,
							browserName 
						FROM 
							" . DB_PREFIX . "stat_browser 
						ORDER BY 
							browserName ASC";
        $resBrowser = $this->ObjDatabase->readValues($sql);
        $sql        = "	SELECT
							osID,
							osName
						FROM 
							" . DB_PREFIX . "stat_os 
						ORDER BY 
							osName ASC";
        $resOs      = $this->ObjDatabase->readValues($sql);
        return (array(
            $resBrowser,
            $resOs
        ));
    }
    public function getMinYearFromData() {
        $sql              = "	SELECT
							MIN(entryYear) as entryYear
						FROM 
							" . DB_PREFIX . "stat_info_browser_month";
        $resSelectCountry = $this->ObjDatabase->readValue($sql);
        return $resSelectCountry['entryYear'];
    }
    public function getDemographicDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_location_month" : "stat_info_location";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						city.cityName,
						city.cityID,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . "stat_city  city
					LEFT JOIN 
						" . DB_PREFIX . $table_name . " as log
					ON (log.cityID=city.cityID)	
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						log.cityID
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if (isset($limit_to) && $limit_to != '') {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getCityLanguages($params = array(), $cityID) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_location_month" : "stat_info_location";
        $sql        = "SELECT
						city.languageID,
						SUM(city.visitor) as count,
						lang.languageName
					FROM 
						" . DB_PREFIX . $table_name . " city
					LEFT JOIN 
						" . DB_PREFIX . "languages as lang
					ON (lang.languageID=city.languageID)	
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if ($cityID > 0) {
            $sql .= " AND city.cityID =" . (int) $cityID;
        }
        if (isset($search_country) && count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						city.languageID
					ORDER BY 
						lang.languageName ";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_location_month" : "stat_info_location";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getHourlyDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_hour_month" : "stat_info_hour";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						hour,
						weekDay
					FROM 
						" . DB_PREFIX . $table_name . " log
					WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						log.hour,weekDay
					ORDER BY
						hour asc";
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getBrowserDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_browser_month" : "stat_info_browser";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						brw.browserName,
						brw.browserID,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisit,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . "stat_browser  brw
					LEFT JOIN 
						" . DB_PREFIX . $table_name . " as log
					ON (log.browserID=brw.browserID)	
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_browser) > 0) {
            $search_browsers = "" . implode("','", $search_browser) . "";
            $sql .= " AND log.browserID IN('" . $search_browsers . "')";
        }
        if (count($search_os) > 0) {
            $search_oss = "" . implode("','", $search_os) . "";
            $sql .= " AND osID IN('" . $search_oss . "')";
        }
        $sql .= " GROUP BY
						log.browserID
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if (isset($limit_to) && $limit_to != '') {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getBrowserTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_browser_month" : "stat_info_browser";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . "stat_browser  brw
					LEFT JOIN 
						" . DB_PREFIX . $table_name . " as log
					ON (log.browserID=brw.browserID)	
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_browser) > 0) {
            $search_browsers = "" . implode("','", $search_browser) . "";
            $sql .= " AND log.browserID IN('" . $search_browsers . "')";
        }
        if (count($search_os) > 0) {
            $search_oss = "" . implode("','", $search_os) . "";
            $sql .= " AND osID IN('" . $search_oss . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getPageBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_page_bounce_month" : "stat_info_page_bounce";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						pag.pageID,
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor,
						pag.pageName,
						pag.pageUrl
					FROM 
						" . DB_PREFIX . "stat_pages  pag
					LEFT JOIN 
						" . DB_PREFIX . $table_name . " as log
					ON (log.pageID=pag.pageID)	
						WHERE bounceVisitor>0 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        if ($group_by == 'page_name') {
            $sql .= " GROUP BY
						pag.pageName ";
        } else if ($group_by == 'url') {
            $sql .= " GROUP BY
						pag.pageUrl ";
        } else {
            $sql .= " GROUP BY
						pag.pageName ";
        }
        if (isset($sort_by_option) && $sort_by_option != '') {
            $sql .= " ORDER BY 
						" . $sort_by_option . " " . $sort_by_order . " ";
        } else {
            $sql .= " ORDER BY 
						bounceVisitor desc ";
        }
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTotalPageBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_page_bounce_month" : "stat_info_page_bounce";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getBrowserBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_browser_bounce_month" : "stat_info_browser_bounce";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						brw.browserID,
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor,
						brw.browserName
					FROM 
						" . DB_PREFIX . "stat_browser  brw
					LEFT JOIN 
						" . DB_PREFIX . $table_name . " as log
					ON (log.browserID=brw.browserID)	
						WHERE bounceVisitor>0 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						brw.browserID ";
        if (isset($sort_by_option) && $sort_by_option != '') {
            $sql .= " ORDER BY 
						" . $sort_by_option . " " . $sort_by_order . " ";
        } else {
            $sql .= " ORDER BY 
						bounceVisitor desc ";
        }
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTotalOSBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_os_bounce_month" : "stat_info_os_bounce";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getTrafficBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_bounce_month" : "stat_info_traffic_bounce";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						referrerType,
						referrerDetails,
						referrerWebSite,
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE bounceVisitor>0 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        if (isset($group_by) && $group_by == 'referrerType') {
            $sql .= " GROUP BY
						referrerType ";
        } else if (isset($group_by) && $group_by != '') {
            $sql .= " GROUP BY " . $group_by;
        } else {
            $sql .= " GROUP BY
						referrerType ";
        }
        if (isset($sort_by_option) && $sort_by_option != '') {
            $sql .= " ORDER BY 
						" . $sort_by_option . " " . $sort_by_order . " ";
        } else {
            $sql .= " ORDER BY 
						bounceVisitor desc ";
        }
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTotalTrafficBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_bounce_month" : "stat_info_traffic_bounce";
        $sql        = "SELECT
						referrerType,
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getOsBounceDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_os_bounce_month" : "stat_info_os_bounce";
        $sql        = "SELECT
						os.osID,
						SUM(visitor) as visit,
						SUM(bounceVisitor) as bounceVisitor,
						os.osName
					FROM 
						" . DB_PREFIX . "stat_os  os
					LEFT JOIN 
						" . DB_PREFIX . $table_name . " as log
					ON (log.osID=os.osID)	
						WHERE bounceVisitor>0 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						os.osID
					ORDER BY 
						bounceVisitor desc ";
        if (isset($limit_to) && $limit_to != '') {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_month" : "stat_info_traffic";
        $sql        = "SELECT
						referrerDetails,
						referrerType,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						log.referrerType, referrerDetails
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if (isset($limit_to)) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_month" : "stat_info_traffic";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getDirectTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_direct_traffic_month" : "stat_info_direct_traffic";
        $sql        = "SELECT
						'Direct' as referrerType,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if (isset($limit_to)) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getDirectTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_direct_traffic_month" : "stat_info_direct_traffic";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getSearchEnginenameTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_search_enginename_month" : "stat_info_traffic_search_enginename";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getSearchEnginenameTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_search_enginename_month" : "stat_info_traffic_search_enginename";
        $sql        = "SELECT
						'Organic Search' as referrerType,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if (isset($limit_to)) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getReferrerWebSiteTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_ref_website_month" : "stat_info_traffic_ref_website";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						referrerWebSite,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        if (isset($group_by) && $group_by == 'all') {
            $sql .= "";
        } else {
            $sql .= " GROUP BY log.referrerWebSite ";
        }
        $sql .= "  ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if (isset($limit_to)) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        if (isset($group_by) && $group_by == 'all') {
            $results = $this->ObjDatabase->readValue($sql);
        } else {
            $results = $this->ObjDatabase->readValues($sql);
        }
        return $results;
    }
    public function getReferrerWebSiteTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_ref_website_month" : "stat_info_traffic_ref_website";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getAffiliateTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_month" : "stat_info_traffic";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						referrerDetails,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE referrerType='affiliate' ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						log.referrerDetails
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getAffiliateTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_month" : "stat_info_traffic";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE referrerType='affiliate' ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getSearchEngineTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_search_enginename_month" : "stat_info_traffic_search_enginename";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						searchEngineName,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						log.searchEngineName
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getSearchEngineTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_search_enginename_month" : "stat_info_traffic_search_enginename";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getSearchKeyWordTrafficDetails($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_search_keyword_month" : "stat_info_traffic_search_keyword";
        $sql        = "SELECT
						SQL_CALC_FOUND_ROWS
						searchKeyWord,
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						SUM(orderAmount) AS revenue,
						SUM(doneSignUp) AS registration,
						SUM(doneSubscribe) AS subscription,
						SUM(orderPlaced) AS orderCount,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (isset($search_month) && count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $sql .= " GROUP BY
						log.searchKeyWord
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getSearchKeyWordTrafficTotalCounts($params = array()) {
        extract($params);
        $table_name = ($search_year > 0) ? "stat_info_traffic_search_keyword_month" : "stat_info_traffic_search_keyword";
        $sql        = "SELECT
						SUM(visitor) as visit,
						SUM(newVisitor) as newVisitor,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS avgPageVisit,
						ROUND
						(
							SUM(duration)/SUM(visitor)
						) AS avgPageVisitDuration
					FROM 
						" . DB_PREFIX . $table_name . " log
						WHERE 1 ";
        if ($search_year > 0) {
            $sql .= " AND entryYear =" . (int) $search_year . "";
            if (count($search_month) > 0) {
                $search_months = "" . implode("','", $search_month) . "";
                $sql .= " AND log.entryMonth IN('" . $search_months . "')";
            }
        } else if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        if ($platform != '') {
            $sql .= " AND platform='" . $platform . "'";
        }
        if (count($search_city) > 0) {
            $search_cities = "" . implode("','", $search_city) . "";
            $sql .= " AND log.cityID IN('" . $search_cities . "')";
        }
        if (count($search_country) > 0) {
            $search_countries = "" . implode("','", $search_country) . "";
            $sql .= " AND countryID IN('" . $search_countries . "')";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results;
    }
    public function getTopEngagedUserDetails($params = array()) {
        extract($params);
        $sql = "SELECT
						SQL_CALC_FOUND_ROWS
						SUM(visitor) as visit,
						SUM(orderAmount) AS revenue,
						SUM(orderPlaced) AS orderCount,
						SUM(doneSubscribe) AS doneSubscribe,
						SUM(pageCount) AS pageCount,
						log.userID,
						usr.userFirstName,
						usr.userLastName,
						usr.userEmail,
						usr.userPhone,
						usr.userRegistredDate,
						usr.userStatus
					FROM 
						" . DB_PREFIX . "stat_info_log_reg_users  log
					LEFT JOIN
						" . DB_PREFIX . "users as usr
					ON (usr.userID=log.userID)	
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        $sql .= " GROUP BY
						log.userID
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTopBusyUserDetails($params = array()) {
        extract($params);
        $sql = "SELECT
						SQL_CALC_FOUND_ROWS
						SUM(visitor) as visit,
						SUM(doneSubscribe) AS doneSubscribe,
						SUM(orderPlaced) AS orderCount,
						SUM(productReviews) AS productReviews,
						SUM(businessReviews) AS businessReviews,
						SUM(usersFollowBusiness) AS usersFollowBusiness,
						SUM(referralsCount) AS referralsCount,
						SUM(rank) as rank,
						ROUND
						(
							SUM(pageCount)/SUM(visitor)
						) AS pageCount,
						ROUND
						(
							SUM(loginTimeDuration)/SUM(visitor)
						) AS loginTimeDuration,
						log.userID,
						usr.userFirstName,
						usr.userLastName,
						usr.userEmail,
						usr.userPhone,
						usr.userRegistredDate,
						usr.userStatus
					FROM 
						" . DB_PREFIX . "stat_info_log_reg_users_engagement  log
					LEFT JOIN
						" . DB_PREFIX . "users as usr
					ON (usr.userID=log.userID)	
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        $sql .= " GROUP BY
						log.userID
					ORDER BY
						" . $sort_by_option . " " . $sort_by_order;
        if ($limit_to) {
            $sql .= " LIMIT  " . $limit_from . "," . $limit_to . " ";
        }
        $results = $this->ObjDatabase->readValues($sql);
        return $results;
    }
    public function getTopBusyUserTotalPoint($params = array()) {
        extract($params);
        $sql = "SELECT
						SUM(rank) as rank
					FROM 
						" . DB_PREFIX . "stat_info_log_reg_users_engagement  log
					LEFT JOIN
						" . DB_PREFIX . "users as usr
					ON (usr.userID=log.userID)	
						WHERE 1 ";
        if ($from_date != '' && $to_date != '') {
            $sql .= " AND entryDate BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }
        $results = $this->ObjDatabase->readValue($sql);
        return $results['rank'];
    }
    public function getUserProductReviewCount($userID, $date) {
        $sql    = "SELECT
						count(*) as countSum
					FROM 
						" . DB_PREFIX . "product_reviews
					WHERE 
						userID='" . (int) $userID . "'
						AND DATE(reviwedDate)='" . $date . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['countSum'];
    }
    public function getUserBusinessReviewCount($userID, $date) {
        $sql    = "SELECT
						count(*) as countSum
					FROM 
						" . DB_PREFIX . "business_reviews
					WHERE 
						userID='" . (int) $userID . "'
						AND DATE(userReviewedDate)='" . $date . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['countSum'];
    }
    public function getUserBusinessFollowCount($userID, $date) {
        $sql    = "SELECT
						count(*) as countSum
					FROM 
						" . DB_PREFIX . "users_follow_businesss
					WHERE 
						userID='" . (int) $userID . "'
						AND DATE(followDate)='" . $date . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['countSum'];
    }
    public function getUserReferralsCount($userID, $date) {
        $sql    = "SELECT
						referedBY
					FROM
						" . DB_PREFIX . "referral_purchases refPur
					INNER JOIN	
						" . DB_PREFIX . "users_referrals as ref
						ON (ref.referrerID=refPur.referrerID)
					WHERE 
						ref.referedTo='" . (int) $userID . "'
						AND DATE(refPur.purchaseDate)='" . $date . "'";
        //echo $sql;				
        $result = $this->ObjDatabase->readValue($sql);
        return $result['referedBY'];
    }
    //Function for getting the city and country tracking failed ips
    //@usage => updating from API by admin
    public function getUntrackedIps($limitFrom, $limitTo) {
        $sql = '';
        if ($limitTo) {
            $sql .= " SELECT SQL_CALC_FOUND_ROWS ";
        } else {
            $sql .= " SELECT ";
        }
        $sql .= "		DISTINCT(remoteAddress) 
					FROM 
						" . DB_PREFIX . "stat_info_log 
					WHERE 
						cityID=(SELECT cityID FROM " . DB_PREFIX . "stat_city WHERE cityName='Unknown')
						OR countryID=(SELECT countryID FROM " . DB_PREFIX . "stat_country WHERE countryName='Unknown')";
        //IF PAGINATION REQUIRED	
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . "";
        }
        $resultSet = $this->ObjDatabase->readValues($sql);
        return $resultSet;
    }
    //function for update the city and country through API
    public function updateIpRecords($ip, $city, $country) {
        $sqlUpdate = "	UPDATE 
								" . DB_PREFIX . "stat_info_log 
							SET 
								remoteAddress='" . $ip . "'";
        if ($city > 0)
            $sqlUpdate .= " ,cityID='" . $city . "'";
        if ($country > 0)
            $sqlUpdate .= " ,countryID='" . $country . "'";
        $sqlUpdate .= " WHERE 
								remoteAddress ='" . $ip . "'";
        echo $sqlUpdate;
        $resUpdate = $this->ObjDatabase->setQuery($sqlUpdate);
    }
    public function listProductsForTopProducts($languageIDs, $dateFrom, $dateTo, $merchantID = 0) {
        $defaultLanguageID = $languageIDs['Default'];
        $currentLanguageID = $languageIDs['Current'];
        $stabledateFrom    = $dateFrom;
        $stabledateTo      = $dateTo;
        $resAllViews       = array();
        if (!$dateTo || $dateTo == '') {
            $searchDateFrom = $stabledateFrom . " " . "00:00:00";
            $searchDateTo   = $stabledateFrom . " " . "23:59:59";
        } else {
            $dateFrom       = $dateFrom . " " . "00:00:00";
            $dateTo         = $dateTo . " " . "23:59:59";
            $searchDateFrom = $dateFrom;
            $searchDateTo   = $dateTo;
        }
        $sql = "	SET SESSION group_concat_max_len = 1000000";
        $this->ObjDatabase->setQuery($sql);
        $sql = " SELECT 
			sum(viewed) as viewed,
			sum(uniqueViewed) as uniqueViewed,
			sum(bought) as bought,
			sum(boughtQty) as boughtQty,
			sum(total) as totalweigtage,";
        //if ($merchantID > 0) {
            $sql .= " product.productID,";
        //}
	$sql .= " productDet.productTitle
		FROM 
			" . DB_PREFIX . "product_popularity proPop";
        //if ($merchantID > 0) {
            $sql .= " JOIN " . DB_PREFIX . "products product
							ON product.productID=proPop.productID ";
        //}
        $sql .= " LEFT JOIN
			" . DB_PREFIX . "product_details productDet
							ON(proPop.productID=productDet.productID AND productDet.languageID=" . $currentLanguageID . ")	
		WHERE 
			visitDate between '" . $searchDateFrom . "' AND '" . $searchDateTo . "' ";
        if ($merchantID > 0) {
            $sql .= " AND product.businessID='" . $merchantID . "'";
        }
        $sql .= "GROUP BY proPop.productID	";
        //echo $sql;exit;
        $resultData    = $this->ObjDatabase->readValuesObject($sql);
        $countOfresult = $resultData->num_rows;
        for ($result = 0; $result < $countOfresult; $result++) {
            $resultSet                                  = $resultData->fetch_array(MYSQLI_ASSOC);
            $sql                                        = " SELECT 
						sum(viewed) as viewed,
						sum(uniqueViewed) as uniqueViewed,
						sum(bought) as bought,
						sum(boughtQty) as boughtQty,
						productID
					FROM 
						" . DB_PREFIX . "product_popularity proPop
					WHERE 
						proPop.productID=" . $resultSet['productID'];
            //echo $sql;exit;
            $resAllDet                                  = $this->ObjDatabase->readValue($sql);
            $resAllViews[$result]['productTitle']       = $resultSet['productTitle'];
            $resAllViews[$result]['ordersinsearchspan'] = $resultSet['bought'];
            $resAllViews[$result]['allorders']          = $resAllDet['bought'];
            $resAllViews[$result]['searchspanOrdQty']   = $resultSet['boughtQty'];
            $resAllViews[$result]['allOrdqty']          = $resAllDet['boughtQty'];
            $resAllViews[$result]['totalviews']         = $resultSet['viewed'];
            $resAllViews[$result]['alltotalviews']      = $resAllDet['viewed'];
            $resAllViews[$result]['uniqueviews']        = $resultSet['uniqueViewed'];
            $resAllViews[$result]['alluniqueviews']     = $resAllDet['uniqueViewed'];
            $resAllViews[$result]['totalweigtage']      = $resultSet['totalweigtage'];
            $resAllViews[$result]['searchspanQty']      = $resultSet['bought'];
            $resAllViews[$result]['allqty']             = $resAllDet['bought'];
            $resAllViews[$result]['productID']          = $resultSet['productID'];
            if ($resAllViews[$result]['uniqueviews'] > 0) {
                $viewToBuyRatio = ($resAllViews[$result]['ordersinsearchspan'] / $resAllViews[$result]['uniqueviews']) * 100;
            }
            if (isset($viewToBuyRatio) && $viewToBuyRatio > 60) {
                $resAllViews[$result]['remarks'] = "High";
            } else if (isset($viewToBuyRatio) && $viewToBuyRatio > 30) {
                $resAllViews[$result]['remarks'] = "Stable";
            } else {
                $resAllViews[$result]['remarks'] = "Low";
            }
            //$resultSet->free;
        }
        return $resAllViews;
    }
    public function getDistinctProductTypes($languageIDs = array()) {
        $languageID = $languageIDs['Current'];
        $sql        = "	SELECT 
							DISTINCT(productTypeKey),productTypeName 
						FROM 
							" . DB_PREFIX . "product_types productTyp 
						LEFT JOIN 
							" . DB_PREFIX . "product_type_details as productTypDet
								ON
									(
										productTypDet.productTypeID=productTyp.productTypeID AND 
							productTypDet.languageID='" . $languageID . "'
									)
						GROUP BY productTypeKey";
        return $this->ObjDatabase->readValues($sql);
    }
    public function getCartDetails($languageIDs, $limitFrom = 0, $limitTo, $searchProductType = array(), $searchCurrency = array(), $searchProduct = '', $date = '', $userType = 'All', $status = 'All', $sortByOption = 'userName', $sortByOrder = 'asc', $user_id = 0) {
        $sortByOption = ($sortByOption == 'userName') ? 'userFirstName' : "orderQty";
        $languageID   = $languageIDs['Current'];
        $filter       = '';
        if (count($searchCurrency) > 0) {
            $searchCurrencies = "" . implode("','", $searchCurrency) . "";
            $filter .= " and currencyID in('" . $searchCurrencies . "')";
            $filterStatus = true;
        }
        if ($searchProduct != '') {
            $searchProduct = $this->escape($searchProduct);
            $filter .= " and (productOpt.optionSKU LIKE '%" . $searchProduct . "%' 
			OR productDet.productTitle LIKE '%" . $searchProduct . "%') ";
            $filterStatus = true;
        }
        if (strpos($date, 'to') > 0) {
            $queryString = explode('to', $date);
            $fromDate    = $queryString[0] . ' 00:00:00';
            if ($queryString[1] == date("Y-m-d")) {
                $filter .= " and addedTime between '" . $fromDate . "' and NOW()";
                $dateFiler = " addedTime between '" . $fromDate . "' and NOW()";
            } else {
                $toDate = $queryString[1] . ' 23:59:59';
                $filter .= " and addedTime between '" . $fromDate . "' and '" . $toDate . "'";
                $dateFiler = " addedTime between '" . $fromDate . "' and '" . $toDate . "'";
            }
            $filterStatus = true;
        } else if ($date != '') {
            $filter .= " and DATE(addedTime)='" . $date . "'";
            $dateFiler    = " DATE(addedTime)='" . $date . "'";
            $filterStatus = true;
        }
        if ($userType != 'All') {
            $filter .= ($userType == 'User') ? " and cart.userID >0 " : " and cart.userID=0 ";
            $filterStatus = true;
        }
        if ($status != 'All') {
            if ($status == 'Online') {
                $filter .= " and addedTime between NOW() - INTERVAL 20 MINUTE and NOW() ";
                $dateFiler = " addedTime between NOW() - INTERVAL 20 MINUTE and NOW() ";
            } else {
                $filter .= " and addedTime < NOW() - INTERVAL 20 MINUTE ";
                $dateFiler = " addedTime < NOW() - INTERVAL 20 MINUTE ";
            }
            $filterStatus = true;
        }
        if ($limitTo) {
            $paginationText = " SQL_CALC_FOUND_ROWS ";
        }
        $sql = "	SELECT " . $paginationText . " 
							cart.cartID,
							sum(orderItemQuantity) AS orderQty,
							currencyID,
							cart.userID,
							addedTime,
							userFirstName,
							userLastName,
							SUM(orderTotalAmount) AS orderTotalAmount,
							userEmail,
							userPhone
						FROM 
							" . DB_PREFIX . "cart cart
						LEFT JOIN  
							" . DB_PREFIX . "cart_items cartIte
							ON
								(cartIte.cartID=cart.cartID)
						LEFT JOIN  
							" . DB_PREFIX . "users usr
							ON
								(usr.userID=cart.userID)
						LEFT JOIN  " . DB_PREFIX . "products product
							ON
								(product.productID=cartIte.productID)
						LEFT JOIN " . DB_PREFIX . "product_options productOpt
							ON
								(productOpt.productOptionID=cartIte.productOptionID)
						LEFT JOIN 
							" . DB_PREFIX . "product_details productDet
							ON
								(productDet.productID=product.productID AND productDet.languageID=
							'" . $languageID . "' )
						WHERE 
								cart.cartID >0 " . $filter . "";
        if ($user_id > 0) {
            $sql .= " AND cart.userID=" . $user_id . " ";
        }
        if($dtype!='' && $dtype!='All') {
            if($dtype=='other') {
                $sql .= " AND (cart.deviceType='')";
            } else {
                $sql .= " AND cart.deviceType='" .$dtype. "' and cart.deviceToken!='' ";
            }
        }
       
        $sql .= " GROUP BY 
							cart.userID 
						ORDER BY
							" . $sortByOption . " 
							" . $sortByOrder . "";
        if ($limitTo) {
            $sql .= " LIMIT  " . $limitFrom . "," . $limitTo . " ";
        }
        $result = $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        return array(
            $result,
            $filter,
            $dateFiler
        );
    }
    public function getCartLastActiveDate($userID, $dateFilter = '') {
        $sql    = "	SELECT 
								addedTime 
							FROM 
								" . DB_PREFIX . "cart_items cartIte
							INNER JOIN 
								" . DB_PREFIX . "cart cart
								ON
									(cart.userID='" . $userID . "' AND cartIte.cartID=cart.cartID)
							WHERE 
								" . $dateFilter . " 
							ORDER BY 
								addedTime DESC";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['addedTime'];
    }
    public function getUserDiscountDetails($userID = 0, $userEmail = '') {
        $sql                     = "SELECT 
						cartNot.extra,
						cartNot.sendTime
					FROM 
						" . DB_PREFIX . "cart_notifications cartNot
					INNER JOIN  
						" . DB_PREFIX . "cart cart
						ON
							(cart.cartID=cartNot.cartID AND cart.userID='" . $userID . "')
					GROUP BY 
						cartNot.notificationID 
					ORDER BY
						sendTime DESC ";
        $result                  = $this->ObjDatabase->readValues($sql);
        $return['notifications'] = $result;
        $sql                     = "SELECT 
						cam.extra,
						cam.createdDate
					FROM 
						" . DB_PREFIX . "campaigns cam
					INNER JOIN  
						" . DB_PREFIX . "campaign_recipients camRec
						ON
							(camRec.campaignID=cam.campaignID AND camRec.emailID='" . $userEmail . "' AND (camRec.type='Send' OR camRec.type=''))
					WHERE
						cam.campaignType='cart'
					GROUP BY 
						cam.campaignID 
					ORDER BY
						createdDate DESC ";
        $result                  = $this->ObjDatabase->readValues($sql);
        $return['campaigns']     = $result;
        return $return;
    }
    public function getCartItemDetails($languageIDs, $userID, $filter = '') {
        $ObjCountry = new CountryModel();
        $languageID = $languageIDs['Current'];
        $sql        = "	SELECT 
									cart.*,productOpt.optionSKU
								FROM 
									" . DB_PREFIX . "cart cart
								LEFT JOIN 
									" . DB_PREFIX . "cart_items cartIte
									ON
										(cartIte.cartID=cart.cartID)
								LEFT JOIN  " . DB_PREFIX . "users usr
									ON
										(usr.userID=cart.userID)
								LEFT JOIN  
									" . DB_PREFIX . "products product
									ON
										(product.productID=cartIte.productID)
								LEFT JOIN " . DB_PREFIX . "product_options productOpt
									ON
										(productOpt.productOptionID=cartIte.productOptionID)		
								LEFT JOIN 
									" . DB_PREFIX . "product_details productDet
									ON
										(productDet.productID=product.productID AND productDet.languageID=
							'" . $languageID . "')
								WHERE 
									cart.cartID >0 
									AND cart.orderQty >0 
									AND
										cart.orderQty >0
									AND
										cart.userID='" . $userID . "' " . $filter . " 
								GROUP BY cart.cartID ";
        $result     = $this->ObjDatabase->readValues($sql);
        if (count($result) > 0) {
            foreach ($result as $key => $res) {
                $extra               = unserialize($res['extra']);
                $billingAdressFormat = '';
                if (isset($extra['userBillingAddress']) && count($extra['userBillingAddress']) > 0 && is_array($extra['userBillingAddress'])) {
                    $billingAdress                  = array_slice(array_values($extra['userBillingAddress']), 0, -2);
                    $billingAdressFormat            = $ObjCountry->getFormattedAddress($billingAdress, array(), ', ');
                    $result[$key]['billingAddress'] = $billingAdressFormat;
                }
                $result[$key]['unserialized'] = $extra;
                if (isset($extra['userShippingAddress']) && count($extra['userShippingAddress']) > 0 && is_array($extra['userShippingAddress'])) {
                    $shippingAdressFormat            = '';
                    $shippingAdress                  = array_values($extra['userShippingAddress']);
                    $shippingAdressFormat            = $ObjCountry->getFormattedAddress($shippingAdress, array(), ', ');
                    $result[$key]['shippingAddress'] = $shippingAdressFormat;
                }
                $sql                           = "	SELECT 
									extra,
									sendTime
								FROM 
									" . DB_PREFIX . "cart_notifications cartNot
								WHERE
									cartNot.cartID='" . $res['cartID'] . "'
								ORDER BY 
									sendTime DESC";
                $subResult                     = $this->ObjDatabase->readValues($sql);
                $result[$key]['notifications'] = $subResult;
                $sql                           = "	SELECT 
									cartIte.*
								FROM 
									" . DB_PREFIX . "cart_items cartIte
								WHERE
									cartIte.cartID='" . $res['cartID'] . "'
								GROUP BY 
									cartIte.cartItemID 
								ORDER BY 
									addedTime DESC";
                $subResult                     = $this->ObjDatabase->readValues($sql);
                $result[$key]['items']         = $subResult;
            }
            return $result;
        } else {
            return array();
        }
    }
    public function listCartUserEmails($userIDs, $from = 'campagain') {
        $searchUserIds = "" . implode("','", $userIDs) . "";
        if ($from == 'campagain') {
            $sql = "	SELECT 
								userEmail,
								userFirstName,
								userLastName,
								userDefaultLanguage
							FROM 
								" . DB_PREFIX . "users usr
							WHERE 
								usr.userID 
								IN 
									('" . $searchUserIds . "')";
        } else {
            $sql = "	SELECT 
								userEmail,
								userFirstName,
								userLastName,
								userDefaultLanguage
							FROM 
								" . DB_PREFIX . "users usr
							WHERE 
								usr.userID 
								IN 
									('" . $searchUserIds . "')";
        }
        $subResult = $this->ObjDatabase->readValues($sql);
        return $subResult;
    }
    public function getCartRecoveryDetails($limitFrom, $limitTo, $title = '', $date = '', $campaignID = 0) {
        $filter = '';
        if ($title != '') {
            $title = $this->escape($title);
            $filter .= " AND cam.title LIKE '%" . $title . "%'";
        }
        if (strpos($date, 'to') > 0) {
            $queryString = explode('to', $date);
            $fromDate    = $queryString[0] . ' 00:00:00';
            if ($queryString[1] == date("Y-m-d")) {
                $filter .= " AND createdDate between '" . $fromDate . "' AND NOW()";
            } else {
                $toDate = $queryString[1] . ' 23:59:59';
                $filter .= " AND createdDate between '" . $fromDate . "' 
				AND '" . $toDate . "'";
            }
        } else {
            $filter .= " AND DATE(createdDate)='" . $date . "'";
            $filterStatus = true;
        }
        if ($limitTo) {
            $paginationText = " SQL_CALC_FOUND_ROWS ";
        }
        if ($campaignID == 0) {
            $sql    = "	SELECT 
									" . $paginationText . "
									title,
									createdDate,
									userCount,
									uniqueOpenedCount,
									uniqueClickCount,
									purchaseCount,
									revenue,
									campaignID
								FROM 
									" . DB_PREFIX . "campaigns cam
								WHERE 
									campaignType='cart'  
									" . $filter . " 
								ORDER BY 
									createdDate DESC
								LIMIT  " . $limitFrom . "," . $limitTo . " ";
            $result = $this->ObjDatabase->readValues($sql);
        } else {
            $sql    = "	SELECT 
									*
								FROM 
									" . DB_PREFIX . "campaigns cam
								WHERE 
									campaignType='cart'  
									AND campaignID=" . (int) ($campaignID) . "
								ORDER BY 
									createdDate DESC";
            $result = $this->ObjDatabase->readValue($sql);
        }
        return $result;
    }
    public function clearCartEntries($userIDS, $filter = '', $languageIDs) {
        $languageID = $languageIDs['Current'];
        $cartIds    = '';
        if (count($userIDS) > 0) {
            $searchuserIDS = "" . implode("','", $userIDS) . "";
            $sql           = "	SELECT 
									cart.cartID 
								FROM 
									" . DB_PREFIX . "cart cart
								LEFT JOIN 
									" . DB_PREFIX . "cart_items cartIte
									ON
										(cartIte.cartID=cart.cartID)
								LEFT JOIN  " . DB_PREFIX . "users usr
									ON
										(usr.userID=cart.userID)
								LEFT JOIN  
									" . DB_PREFIX . "products product
									ON
										(product.productID=cartIte.productID)
								LEFT JOIN 
									" . DB_PREFIX . "product_details productDet
									ON
										(productDet.productID=product.productID AND 
										productDet.languageID='" . $languageID . "')
								WHERE 
									cart.cartID >0 
									AND
										cart.userID IN ('" . $searchuserIDS . "') " . $filter . " 
								GROUP BY cart.cartID ";
            $result        = $this->ObjDatabase->readValues($sql);
            if (count($result) > 0) {
                foreach ($result as $key => $items) {
                    $cartIds .= "'" . $items['cartID'] . "',";
                }
                $cartIds          = substr($cartIds, 0, -1);
                $sql              = "	DELETE FROM 
									" . DB_PREFIX . "cart 
								WHERE 
									cartID IN (" . $cartIds . ")";
                $resDeleteFromUrl = $this->ObjDatabase->setQuery($sql);
            }
        }
    }
    public function getTopSearchDetails($dateFrom, $dateTo, $sortBy = 'updatedAt', $orderBy = 'desc', $limitFrom = 0, $limitTo = 0) {
        $stabledateFrom = $dateFrom;
        $stabledateTo   = $dateTo;
        if (!$dateTo || $dateTo == '') {
            $searchDateFrom = $stabledateFrom . " " . "00:00:00";
            $searchDateTo   = $stabledateFrom . " " . "23:59:59";
        } else {
            $dateFrom       = $dateFrom . " " . "00:00:00";
            $dateTo         = $dateTo . " " . "23:59:59";
            $searchDateFrom = $dateFrom;
            $searchDateTo   = $dateTo;
        }
        if ($limitTo > 0) {
            $paginationText = " SQL_CALC_FOUND_ROWS ";
        }
        $sql = " SELECT 
					" . $paginationText . "
					queryText,
					categoryID,
					numResults,
					popularity,
					updatedAt,
					catSea.queryID,
					count(catSeaVis.visitorID) as visitorCount,
					lan.languageCode,
					lan.languageName
		FROM 
			" . DB_PREFIX . "catalogsearch_query catSea
		LEFT JOIN 
			" . DB_PREFIX . "catalogsearch_query_visitor catSeaVis
			ON(catSeaVis.queryID=catSea.queryID AND  catSeaVis.searchTime between '" . $searchDateFrom . "' AND '" . $searchDateTo . "')
		LEFT JOIN 
			" . DB_PREFIX . "languages lan
			ON (lan.languageID=catSea.languageID)
		WHERE 
			updatedAt between '" . $searchDateFrom . "' AND '" . $searchDateTo . "'
			AND queryStatus='A' AND popularity>0 
		GROUP BY catSea.queryID	
		ORDER BY
			" . $sortBy . " " . $orderBy . "";
        if ($limitTo > 0) {
            $sql .= " LIMIT " . $limitFrom . " , " . $limitTo;
        }
        $resultData       = $this->ObjDatabase->readValues($sql);
        /*$sqlForPagination = " SELECT 
										" . $paginationText . "
										queryText,
										numResults,
										popularity,
										updatedAt,
										catSea.queryID,
										count(catSeaVis.visitorID) as visitorCount,
										lan.languageCode
							FROM 
								" . DB_PREFIX . "catalogsearch_query catSea
							LEFT JOIN 
								" . DB_PREFIX . "catalogsearch_query_visitor catSeaVis
								ON(catSeaVis.queryID=catSea.queryID AND  catSeaVis.searchTime between '" . $searchDateFrom . "' AND '" . $searchDateTo . "')
							LEFT JOIN 
								" . DB_PREFIX . "languages lan
								ON (lan.languageID=catSea.languageID)
							WHERE 
								updatedAt between '" . $searchDateFrom . "' AND '" . $searchDateTo . "'
								AND queryStatus='A' AND popularity>0 
							GROUP BY queryText	
							ORDER BY
								" . $sortBy . " " . $orderBy . "";
        if ($limitTo > 0) {
            $sqlForPagination .= " LIMIT " . $limitFrom . " , " . $limitTo;
        }
        $temResult = $this->ObjDatabase->readValues($sqlForPagination);*/
        return $resultData;
    }
    public function getTopSearchVisitorDetails($termID, $dateFrom, $dateTo, $sortBy = 'searchTime', $orderBy = 'desc', $limitFrom, $limitTo) {
        $stabledateFrom = $dateFrom;
        $stabledateTo   = $dateTo;
        if (!$dateTo || $dateTo == '') {
            $searchDateFrom = $stabledateFrom . " " . "00:00:00";
            $searchDateTo   = $stabledateFrom . " " . "23:59:59";
        } else {
            $dateFrom       = $dateFrom . " " . "00:00:00";
            $dateTo         = $dateTo . " " . "23:59:59";
            $searchDateFrom = $dateFrom;
            $searchDateTo   = $dateTo;
        }
        if ($limitTo > 0) {
            $paginationText = " SQL_CALC_FOUND_ROWS ";
        }
        $sql = " SELECT
					" . $paginationText . "
					catSeaVis.searchTime,
					catSea.languageID,
					brw.browserName,
					os.osName as operatingSystem,
					log.userType,
					log.orderPlaced,
					log.doneSubscribe,
					log.doneSignUp,
					city.cityName as city,
					con.countryName as country,
					log.visitorID,
					count(pag.pageID) as pageCount
		FROM 
			" . DB_PREFIX . "catalogsearch_query_visitor catSeaVis
		LEFT JOIN 
			" . DB_PREFIX . "catalogsearch_query catSea
			ON(catSea.queryID=catSeaVis.queryID)
		LEFT JOIN
			" . DB_PREFIX . "stat_info_search log
			ON(log.visitorID=catSeaVis.visitorID)
		INNER JOIN " . DB_PREFIX . "stat_info_url_search pag	
			ON (pag.visitorID=log.visitorID)
		LEFT JOIN
			" . DB_PREFIX . "stat_browser brw
			ON(brw.browserID=log.browserID)
		LEFT JOIN
			" . DB_PREFIX . "stat_os os
			ON(os.osID=log.osID)
		LEFT JOIN
			" . DB_PREFIX . "stat_city city
			ON(city.cityID=log.cityID)
		LEFT JOIN
			" . DB_PREFIX . "stat_country con
			ON(con.countryID=log.countryID)		
		WHERE 
			searchTime between '" . $searchDateFrom . "' AND '" . $searchDateTo . "' 
			AND catSeaVis.queryID='" . $termID . "' 
		GROUP 
			BY catSeaVis.visitorID 
		ORDER BY
			" . $sortBy . " " . $orderBy . "";
        if ($limitTo > 0) {
            $sql .= " LIMIT " . $limitFrom . " , " . $limitTo;
        }
        $resultData = $this->ObjDatabase->readValues($sql);
        return $resultData;
    }
    public function getVisitorAllUrls($visitorID) {
        $sql        = " SELECT
					*,
					page.pageUrl as currentUrl
				FROM 
					" . DB_PREFIX . "stat_info_url_search log
				LEFT JOIN
					" . DB_PREFIX . "stat_pages page
					ON(page.pageID=log.pageID)	
				WHERE 
					log. visitorID = '" . $visitorID . "'
				ORDER BY id ASC";
        $resultData = $this->ObjDatabase->readValues($sql);
        return $resultData;
    }
    //Function for save the cron exicution details
    public function saveCronExicutionYear($count, $urlID) {
        $sql = "	DELETE 
							" . DB_PREFIX . "crone_execution_details
						WHERE
							cronType='analytics_row'";
        //$this->ObjDatabase->setQuery($sql);
        $sql = "	INSERT INTO 
							" . DB_PREFIX . "crone_execution_details
								(
									executionTime,
									rowsAffected,
									lastAffectedID,
									cronType
								)
						VALUES 
								(
									NOW(),
									'" . $count . "',
									'" . $urlID . "',
									'analytics_row'
								) ";
        return $this->ObjDatabase->setQuery($sql);
    }
    //Function For get all url log details from the last updated record in previews cron.
    public function getUlrLogDetailsYear($visitorID = 0, $table_name = '', $limit = 0) {
        if ($table_name != '') {
            $sql = " SELECT 
							*,
							YEAR(entryDate) as year,
							MONTH(entryDate) AS month,
							DAYOFWEEK(entryDate) AS entryWeek
						FROM 
							" . DB_PREFIX . $table_name . " vl
						WHERE 
							vl.id >" . (int) $visitorID . "
						ORDER BY 
							id asc";
            if ($limit > 0) {
                $sql .= " Limit 0 , " . $limit;
            }
            $result = $this->ObjDatabase->readValuesObject($sql);
            return $result;
        }
    }
    public function clearExicutedRows($visitorID = 0) {
        $sql = "	DELETE FROM 
							" . DB_PREFIX . "stat_info_log
						WHERE
							visitorID<=" . $visitorID;
        $this->ObjDatabase->setQuery($sql);
        $sql = "	DELETE FROM  
							" . DB_PREFIX . "stat_url_log
						WHERE
							visitorID<=" . $visitorID;
        $this->ObjDatabase->setQuery($sql);
    }
    //FUNCTION TO FETCH NUMBER OF PROCESSED ROWS IN URL TABLE
    public function searchRowsCountToDel() {
        $sql         = "	SELECT 
							COUNT(*) AS rowscount,
							DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) as txt
						FROM 
							" . DB_PREFIX . "catalogsearch_query_visitor 
						WHERE 
							DATE(searchTime) <= DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))";
        $resRowCount = $this->ObjDatabase->readValue($sql);
        $rowCount    = $resRowCount['rowscount'];
        return $resRowCount;
    }
    //FUNCTION TO DELETE PROCESSED URLS FROM URL TABLE
    public function deleteUnwantedSearchRows() {
        $sql = "DELETE 
					FROM 
						" . DB_PREFIX . "catalogsearch_query_visitor 
					WHERE 
						DATE(searchTime) <= DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))";
        $this->ObjDatabase->setQuery($sql);
        $sql = "DELETE 
					FROM 
						" . DB_PREFIX . "stat_info_search 
					WHERE 
						visitorID NOT IN (SELECT DISTINCT(visitorID) FROM " . DB_PREFIX . "catalogsearch_query_visitor)";
        $this->ObjDatabase->setQuery($sql);
        $sql = "DELETE 
					FROM 
						" . DB_PREFIX . "stat_info_url_search 
					WHERE 
						visitorID NOT IN (SELECT DISTINCT(visitorID) FROM " . DB_PREFIX . "stat_info_search)";
        $this->ObjDatabase->setQuery($sql);
        return true;
    }
    public function clearlogdata() {
        $sql = "TRUNCATE TABLE dp_stat_count_overview";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_browser";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_browser_bounce";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_browser_bounce_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_browser_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_direct_traffic";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_direct_traffic_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_hour";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_hour_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_hour_overview";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_location";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_location_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_location_overview";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_log";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_log_reg_users";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_log_reg_users_engagement";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_os_bounce";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_os_bounce_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_page_bounce";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_page_bounce_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_search";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_bounce";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_bounce_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_ref_website";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_ref_website_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_search_enginename";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_search_enginename_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_search_keyword";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_traffic_search_keyword_month";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_info_url_search";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_stat_url_log";
        $this->ObjDatabase->setQuery($sql);
        $sql = "TRUNCATE TABLE dp_crone_execution_details";
        $this->ObjDatabase->setQuery($sql);
    }
    public function getTotalCountsCustomer($merchantID = 0, $searchAgeArray = array(), $searchGenderArray = array(), $date = '') {
        $filterStatus = false;
        $filter       = '';
        $filterTmp    = '';
        if (is_array($searchAgeArray) && count($searchAgeArray) > 0) {
            $filter .= " AND ( ";
            if (in_array("below17", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) BETWEEN 1 AND 17 ";
            }
            if (in_array("18-24", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) BETWEEN 18 AND 24 ";
            }
            if (in_array("25-34", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) between 25 AND 34 ";
            }
            if (in_array("35-54", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) between 35 AND 54 ";
            }
            if (in_array("above55", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) >=55 ";
            }
            if (in_array("notage", $searchAgeArray)) {
                $filterTmp .= " OR usr.userDOB =''  OR usr.userDOB IS NULL";
            }
            $filter .= substr($filterTmp, 3) . " )";
            $filterStatus = true;
        }
        if (is_array($searchGenderArray) && count($searchGenderArray) > 0) {
            $filter .= " AND ";
            if (in_array('Not Specified', $searchGenderArray)) {
                $filter .= " ( userGender='' OR userGender IS NULL OR ";
            }
            $searchGender = "" . implode("','", $searchGenderArray) . "";
            $filter .= "  userGender IN('" . $searchGender . "')";
            if (in_array('Not Specified', $searchGenderArray)) {
                $filter .= " )  ";
            }
            $filterStatus = true;
        }
        if ($date != '') {
            if (strpos($date, 'to') > 0) {
                $queryString = explode(' to ', $date);
                $fromDate    = $queryString[0] . ' 00:00:00';
                if ($queryString[1] == date("Y-m-d")) {
                    $filter .= " AND orderCreatedDate BETWEEN '" . $fromDate . "' AND NOW()";
                } else {
                    $toDate = $queryString[1] . ' 23:59:59';
                    $filter .= " AND orderCreatedDate BETWEEN '" . $fromDate . "' AND '" . $toDate . "'";
                }
                $filterStatus = true;
            } else {
                $filter .= " AND DATE(orderCreatedDate)='" . $date . "'";
                $filterStatus = true;
            }
        }
        $sql = "SELECT                             
                             ordr.userID as user,
                             ordr.orderTotalAmount AS orderTotalAmount,
                             ordr.customer_email AS mailID
                        FROM " . DB_PREFIX . "order_masters ordr
                        LEFT JOIN " . DB_PREFIX . "users usr
                        ON ordr.userID=usr.userID AND userStatus!='Deleted'    
                        WHERE businessID='" . $merchantID . "' AND orderStatus!='New'";
        if ($filterStatus)
            $sql .= " AND " . substr($filter, 4);
        $resSelectLogTotalValue              = $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        $visitorCount                        = 0;
        $resSelectLogTotal['totalCustomers'] = 0;
        $resSelectLogTotal['newVisit']       = 0;
        $resSelectLogTotal['returnVisit']    = 0;
        $resSelectLogTotal['totalRevenue']   = 0;
        $resSelectLogTotal['ordersplaced']   = 0;
        $newVisit                            = 0;
        $returnVisit                         = 0;
        $totOrderAmount                      = 0;
        $uniqueArray                         = $userIDArray = array();
        $visitorCount                        = count($resSelectLogTotalValue);
        if ($visitorCount > 0) {
            foreach ($resSelectLogTotalValue as $key => $value) {
                if ($value['user'] > 0)
                    $userIDArray[] = $value['user'];
                else
                    $userIDArray[] = $value['mailID'];
                $totOrderAmount = $totOrderAmount + $value['orderTotalAmount'];
            }
            $uniqueArray = array_count_values($userIDArray);
            foreach ($uniqueArray as $id) {
                if ($id == 1) {
                    $newVisit++; //echo $valueTot['user']."<br>";
                } else
                    $returnVisit++;
            }
            unset($resSelectLogTotalValue);
        }
        $resSelectLogTotal['totalCustomers'] = $newVisit + $returnVisit;
        $resSelectLogTotal['newVisit']       = $newVisit;
        $resSelectLogTotal['returnVisit']    = $returnVisit;
        $resSelectLogTotal['ordersplaced']   = $visitorCount;
        $resSelectLogTotal['totalRevenue']   = $totOrderAmount;
        return $resSelectLogTotal;
    }
    //function to get details based on age - customer analytics - added on 07/08/2013
    public function getCustomerAgeGenderDetails($type = "object", $merchantID = 0, $searchAgeArray = array(), $searchGenderArray = array(), $date = '') {
        $filterStatus = false;
        $filter       = isset($filter) ? $filter : '';
        $filterTmp    = '';
        if (is_array($searchAgeArray) && count($searchAgeArray) > 0) {
            $filter .= " AND ( ";
            if (in_array("below17", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) BETWEEN 1 and 17 ";
            }
            if (in_array("18-24", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) BETWEEN 18 and 24 ";
            }
            if (in_array("25-34", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) BETWEEN 25 and 34 ";
            }
            if (in_array("35-54", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) BETWEEN 35 and 54 ";
            }
            if (in_array("above55", $searchAgeArray)) {
                $filterTmp .= " OR TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()) >=55 ";
            }
            if (in_array("notage", $searchAgeArray)) {
                $filterTmp .= " OR usr.userDOB =''  OR usr.userDOB IS NULL ";
            }
            $filter .= substr($filterTmp, 3) . " )";
            $filterStatus = true;
        }
        if (is_array($searchGenderArray) && count($searchGenderArray) > 0) {
            $filter .= " AND ";
            if (in_array('Not Specified', $searchGenderArray)) {
                $filter .= " ( userGender='' OR userGender IS NULL OR ";
            }
            $searchGender = "" . implode("','", $searchGenderArray) . "";
            $filter .= "  userGender IN('" . $searchGender . "')";
            if (in_array('Not Specified', $searchGenderArray)) {
                $filter .= " )  ";
            }
            $filterStatus = true;
        }
        if ($date != '') {
            if (strpos($date, 'to') > 0) {
                $queryString = explode(' to ', $date);
                $fromDate    = $queryString[0] . ' 00:00:00';
                if ($queryString[1] == date("Y-m-d")) {
                    $filter .= " AND orderCreatedDate BETWEEN '" . $fromDate . "' AND NOW()";
                } else {
                    $toDate = $queryString[1] . ' 23:59:59';
                    $filter .= " AND orderCreatedDate BETWEEN '" . $fromDate . "' AND '" . $toDate . "'";
                }
                $filterStatus = true;
            } else {
                $filter .= " AND DATE(orderCreatedDate)='" . $date . "'";
                $filterStatus = true;
            }
        }
        $sql = "SELECT 
                             IF (userGender!='',userGender,'Not Specified')  AS userSex,
                             IF (usr.userDOB!='', TIMESTAMPDIFF(YEAR, STR_TO_DATE(usr.userDOB, '%d-%m-%Y'),CURDATE()),'') AS userAge,
                              ordr.customer_email AS mailID,
                             orderCreatedDate,
                             DAYOFWEEK(orderCreatedDate) AS weekday,
                             ordr.billingAddress AS billingAddress,
                             IF (ordr.userID>0, ordr.userID,ordr.customer_email) AS userID,
                             ordr.orderTotalAmount AS orderTotalAmount
                        FROM " . DB_PREFIX . "order_masters ordr
                        LEFT JOIN " . DB_PREFIX . "users usr
                        ON ordr.userID=usr.userID AND userStatus!='Deleted'    
                        WHERE businessID='" . $merchantID . "' AND orderStatus!='New'";
        if ($filterStatus)
            $sql .= " AND " . substr($filter, 4);
        // echo $sql;exit;
        if ($type == 'object')
            $resSelectAgeGender = $this->ObjDatabase->readValuesObject($sql, MYSQLI_ASSOC);
        else
            $resSelectAgeGender = $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        return $resSelectAgeGender;
    }
}
