<?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: SearchTermModel.php, v 1.0 2013/07/14 14:14:14
class SearchTermModel extends Model {
    public function listSeachTerms($params = array()) {
        extract($params);
        $fields = array(
            'query' => 'queryText',
            'result' => 'numResults',
            'users' => 'popularity',
            'synonym' => 'synonymFor',
            'categoryID' => 'categoryID',
            'category' => 'productCategoryName',
            'redirect' => 'redirect',
            'related' => 'relatedSearchTerm',
            'terms' => 'displayInTerms',
            'status' => 'queryStatus',
            'updated' => 'updatedAt'
        );
        if (isset($getFields) && $getFields != '') {
            $selectfields = $getFields;
        } else {
            $selectfields = "*";
        }
        $languageID = $languageIDs['Content'];
        $sql        = "
			SELECT 
				SQL_CALC_FOUND_ROWS
				" . $selectfields . "
				,  
				(
				  SELECT 
					GROUP_CONCAT(gloCatTag.languageID SEPARATOR  ',' )
				  FROM 
					" . DB_PREFIX . "global_catalog_tags AS gloCatTag
				  WHERE 
					gloCatTag.tagName = catQry.queryText
				) AS tagCount
			FROM 
					" . DB_PREFIX . "catalogsearch_query AS catQry
				LEFT JOIN
					" . DB_PREFIX . "product_category_details AS productCatDet
						ON	(
							productCatDet.productCategoryID = catQry.categoryID
							AND
							productCatDet.languageID='" . $languageID . "'
						)
			WHERE
				1
				AND
				catQry.languageID='" . $languageID . "'
		";
        if (isset($queryStatus) && $queryStatus != '') {
            $sql .= "
				AND
				queryStatus='" . strtoupper($queryStatus) . "'
			";
        } else {
            $sql .= "
				AND
				queryStatus!='D'
			";
        }
        if (isset($queryText) && $queryText != '') {
            $sql .= "
				AND 
				queryText like '%" . $this->escape($queryText) . "%'
			";
        }
        if (!empty($synonymFor)) {
            $sql .= "
				AND 
				synonymFor like '%" . $this->escape($synonymFor) . "%'
			";
        }
        if (isset($categoryID) && $categoryID >= 0) {
            $sql .= "
				AND 
				catQry.categoryID =  '" . (int) $categoryID . "'
			";
        }
        if (!empty($numResults) && $numResults >= 0) {
            $sql .= "
				AND 
				numResults >='" . (int) $numResults . "'
			";
        }
        if (!empty($popularity) && $popularity >= 0) {
            $sql .= "
				AND 
				popularity >='" . (int) $popularity . "'
			";
        }
        if (!empty($searchFromDate) && !empty($searchToDate)) {
            $sql .= "
				AND 
				DATE_FORMAT(updatedAt,'%Y-%m-%d') BETWEEN '" . $searchFromDate . "' AND '" . $searchToDate . "'
			";
        }
        if (isset($queryStatus) && in_array($queryStatus, array(
            'A',
            'I',
            'D'
        ))) {
            $sql .= "
				AND 
				queryStatus ='" . $queryStatus . "'
			";
        }
        if (isset($searchQuery) && $searchQuery != '') {
            $sql .= "
				AND	
					(
					queryText like '%" . $this->escape($searchQuery) . "%'
					AND
					displayInTerms ='Y'
					)
			";
        }
        $sql .= "
			ORDER BY
		";
        if (isset($sortby) && $sortby != '') {
            $sql .= "
				" . $fields[$sortby] . " " . $orderby . ",
			";
        }
        $sql .= " popularity DESC ";
        if (isset($limitTo) && $limitTo > 0) {
            $sql .= "
			LIMIT 
				" . $limitFrom . " , " . $limitTo . "";
        }
        return $this->ObjDatabase->readValues($sql);
    }
    public function getSeachTerms($params = array()) {
        extract($params);
        $languageID = $languageIDs['Current'];
        $sql        = "
			SELECT 
				* 
			FROM 
				" . DB_PREFIX . "catalogsearch_query
			WHERE
				queryStatus!='D'
				
				AND
				languageID='" . $languageID . "'";
        if (isset($categoryID) && $categoryID >= 0) {
            $sql .= "
				AND
				categoryID='" . (int) $categoryID . "'
			";
        }
        if (isset($queryID) && ($queryID > 0)) {
            $sql .= "
				AND
				queryID='" . $queryID . "'
			";
        }
        if (isset($queryText) && $queryText != '') {
            $sql .= "
				AND
				queryText='" . $this->escape($queryText) . "'
			";
        }
        return $this->ObjDatabase->readValue($sql);
    }
    public function getRelatedSearchKeywords($params = array()) {
        extract($params);
        $languageID = $languageIDs['Current'];
        $sql        = "
			SELECT 
				* 
			FROM 
				" . DB_PREFIX . "catalogsearch_query
			WHERE
				queryStatus!='D'
				AND
				languageID='" . $languageID . "'
				AND
				queryText like '%" . $this->escape($queryText) . "%'
				AND
				queryText!='" . $this->escape($queryText) . "'
				AND 
				numResults>0
			ORDER BY
				popularity DESC
			";
        return $this->ObjDatabase->readValues($sql);
    }
    public function checkKeywordExist($params = array()) {
        extract($params);
        $languageID = $languageIDs['Current'];
        $sql        = "
			SELECT 
				EXISTS(
					SELECT 
						* 
					FROM 
						" . DB_PREFIX . "catalogsearch_query
					WHERE
						queryStatus!='D'
						AND
						categoryID='" . (int) $categoryID . "'
						AND
						queryText='" . $this->escape($queryText) . "'
						AND
						languageID='" . $languageID . "'";
        if (isset($queryID) && $queryID > 0) {
            $sql .= "
						AND
						queryID!='" . $queryID . "'";
        }
        $sql .= "
				) AS EXIST
		";
        $result = $this->ObjDatabase->readValueSlave($sql);
        return $result['EXIST'];
    }
    public function insert($params = array()) {
        extract($params);
        $queryID = isset($queryID) ? $queryID : 0;
        if (!$queryID > 0) {
            $fields['queryText']         = isset($fields['queryText']) ? $this->escape($fields['queryText']) : '';
            $fields['synonymFor']        = isset($fields['synonymFor']) ? $this->escape($fields['synonymFor']) : '';
            $fields['relatedSearchTerm'] = isset($fields['relatedSearchTerm']) ? $this->escape($fields['relatedSearchTerm']) : '';
            $sql                         = "
				INSERT INTO " . DB_PREFIX . "catalogsearch_query
					(
					" . implode(",", array_keys($fields)) . "
					)
				VALUES
					(
					'" . implode("','", array_values($fields)) . "'
					)";
        } else {
            $sql = "
				UPDATE 
					" . DB_PREFIX . "catalogsearch_query
				SET
					" . implode(",", $set) . "
				WHERE
					queryID='" . $queryID . "'";
        }
        $this->ObjDatabase->setQuery($sql);
        if (!$queryID > 0) {
            $queryID = $this->ObjDatabase->getInsertId();
        }
        return $queryID;
    }
    public function changeStatus($params) {
        extract($params);
        $languageID = $languageIDs['Current'];
        $sql        = "
			UPDATE
				" . DB_PREFIX . "catalogsearch_query
			SET
				queryStatus='" . $queryStatus . "'
			WHERE
				queryID in (" . implode(",", $queryIDs) . ")
				AND
				languageID='" . $languageID . "'
			";
        $this->ObjDatabase->setQuery($sql);
    }
    //To add caltalog tags
    public function saveGlobalCatalogTags($languageIDs) {
        $customQuery = " languageID= '" . $languageIDs['Current'] . "'";
        $customQuery .= (isset($_POST['tagID']) && $_POST['tagID'] != '') ? " AND tagID != '" . $_POST['tagID'] . "'" : '';
        $nameExists = $this->ObjDatabase->checkExist('global_catalog_tags', 'tagName', $_POST['tagName'], $customQuery);
        if ($nameExists == "YES")
            return 'exists';
        if (!empty($_POST['tagID'])) {
            $sql = "	UPDATE " . DB_PREFIX . "global_catalog_tags
					SET
						tagName='" . $this->escape($_POST['tagName']) . "'
					WHERE
						tagID = " . $_POST['tagID'];
        } else {
            $sql = "	INSERT INTO " . DB_PREFIX . "global_catalog_tags (
						tagName,
						languageID
					)
					VALUES 
					(
						'" . $this->escape($_POST['tagName']) . "',
						'" . $languageIDs['Current'] . "'
					)";
        }
        $this->ObjDatabase->setQuery($sql);
        return 1;
    }
    public function saveSearchTermsAsTags() {
        $insert = '';
        $relang = explode(',', substr($_POST['langid'], 0, -1));
        $sql    = "	INSERT INTO " . DB_PREFIX . "global_catalog_tags (
						tagName,
						languageID
					)
					VALUES ";
        for ($z = 0; $z < count($relang); $z++) {
            $customQuery = " languageID= '" . $relang[$z] . "'";
            $nameExists  = $this->ObjDatabase->checkExist('global_catalog_tags', 'tagName', $_POST['tagName'], $customQuery);
            if ($nameExists != "YES") {
                $insert = 1;
                $sql .= " (
							'" . $this->escape($_POST['tagName']) . "',
							'" . $relang[$z] . "'
						)";
                $sql .= ",";
            }
        }
        if ($insert == 1) {
            $sql = substr($sql, 0, -1);
            $this->ObjDatabase->setQuery($sql);
        } else
            return 1;
    }
    //To list all the catalog tags
    public function listGlobalCatalogTags($languageIDs, $searchTagName = '') {
        $sql = "SELECT 
					* 
				FROM 
					" . DB_PREFIX . "global_catalog_tags 
				WHERE 
					languageID= '" . $languageIDs['Current'] . "'";
        if ($searchTagName != '')
            $sql .= " 	AND	tagName LIKE '%" . $this->escape($searchTagName) . "%'";
        $sql .= " ORDER BY
					tagID DESC
			"; //echo $sql;
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    //To delete the catalog tags
    public function deleteGlobalCatalogTags() {
        $sql = "DELETE 
				FROM 
					" . DB_PREFIX . "global_catalog_tags
				WHERE
					tagID = " . $_POST['tagID'];
        $this->ObjDatabase->setQuery($sql);
    }
}
