<?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: BrandModel, v 1.0 2014/05/09 14:07:48
//All functions 
//
class BrandModel extends Model {
    public function listBrand($params = array(),$type='') {
        extract($params);
        $currentLanguageID = (isset($languageIDs['Content']) && $languageIDs['Content'] > 0) ? $languageIDs['Content'] : $languageIDs['Current'];
        $selectFields      = isset($selectFields) ? $selectFields : '';
        if (isset($limitTo))
            $selectFields .= " SQL_CALC_FOUND_ROWS ";
        $sql = " 
		SELECT 
			" . $selectFields . " ";
        if (isset($fields) && $fields != '') {
            $sql .= "
				" . $fields . "
			";
        } else {
            $sql .= "
			brand.`brandID`, 
			`brandName`, 
			`brandKey`, 
			`brandIcon`, 
			`status`, 
			`createdDate`,
			`brandDescription`, 
			`metaTitle`, 
			`metaDescription`,";
            if (isset($enableProductCount) && $enableProductCount) {
                $sql .= "
			(
				SELECT 
					COUNT(1)
				FROM 
					" . DB_PREFIX . "products
				WHERE
					productStatus!='Deleted'
					AND 
					brandID = brand.brandID
			) AS productCount,";
            }
            $sql .= "
			`searchKeywords`";
        }
        $sql .= "	
		FROM 
				" . DB_PREFIX . "brands AS brand
			INNER JOIN 
				" . DB_PREFIX . "brand_details AS brandDet
					ON	(
						brand.brandID = brandDet.brandID
						AND
						brandDet.languageID = '" . $currentLanguageID . "'
					)
	
		WHERE
			1
		";
        if (isset($excludeBrands) && !empty($excludeBrands)) {
            $sql .= "
					AND
					brand.brandID NOT IN ('" . implode("','", $excludeBrands) . "')
					";
        }
        if($type!='All') {
            $sql .= " AND brand.parentID<=0 ";
        }
        if (isset($brandKey) && $brandKey != '') {
            $sql .= "
					AND
					brandKey like '%" . $this->escape($brandKey) . "%'
					";
        }
        if (isset($brandIcon) && $brandIcon != '') {
            $sql .= "
					AND
					brandIcon ='" . $brandIcon . "'
					";
        }
        if (isset($status) && $status != '') {
            $sql .= "
					AND
					status ='" . $status . "'
					";
        } else {
            $sql .= "
					AND
					status !='D'
					";
        }
        if (isset($brandName) && $brandName != '') {
            $sql .= "
					AND
					brandName like '%" . $this->escape($brandName) . "%'
					";
        }
        if (isset($metaTitle) && $metaTitle != '') {
            $sql .= "
					AND
					metaTitle like '%" . $this->escape($metaTitle) . "%'
					";
        }
        if (isset($metaDescription) && $metaDescription != '') {
            $sql .= "
					AND
					metaDescription ='" . $this->escape($metaDescription) . "'
					";
        }
        if (isset($brandDescription) && $brandDescription != '') {
            $sql .= "
					AND
					brandDescription ='" . $this->escape($brandDescription) . "'
					";
        }
        if (isset($alphabet) && $alphabet != '') {
            $sql .= "
					AND
					brandName like  '" . $this->escape($alphabet) . "%'
					";
        }
        if (isset($searchProductCategory) && !empty($searchProductCategory)) {
            $sql .= "
				AND
				brand.brandID IN (
					SELECT 
						brandID
					FROM
						" . DB_PREFIX . "brand_featured_categories
					WHERE
						categoryID IN ('" . implode("','", $searchProductCategory) . "')
				)
				
			";
        }
		if (isset($currentBrandID) && $currentBrandID > 0) {
            $sql .= "
					AND
					brand.brandID !='" . $currentBrandID . "'
					";
        }
        if (isset($orderby) && $sortby) {
            $sortby = (substr($sortby, 0, 5) == 'sort_') ? substr($sortby, 5) : $sortby;
            $sql .= "
			ORDER BY
			" . $sortby . " " . $orderby;
        }
        if (isset($limitTo)) {
            $sql .= " 
			LIMIT  
				" . $limitFrom . ",
				" . $limitTo . "
			";
        }//echo $sql;
        return $this->ObjDatabase->readValuesSlave($sql);
    }
    public function listBrandBasedOnAlpabets($params = array()) {
        extract($params);
        $alphabet          = isset($alphabet) ? $alphabet : '';
        $limit             = isset($limit) ? $limit : '';
        $currentLanguageID = (isset($languageIDs['Content']) && $languageIDs['Content'] > 0) ? $languageIDs['Content'] : $languageIDs['Current'];
        if ($alphabet != '') {
            $alphabets = array(
                $alphabet
            );
        } else {
           /* if($languageIDs['Current']==2) {        
                $alphabets = array("?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?","?");
                foreach ($alphabets as $itemAlphabet) {
                    ini_set('mbstring.substitute_character', "none"); 
echo $text= mb_convert_encoding($itemAlphabet, 'UTF-8', 'UTF-8');
                   echo " ".utf8_decode($itemAlphabet);exit;
                }
            } else {
                $alphabets = range('A', 'Z');
            }*/
            if($languageIDs['Current']==2) {                
                $alphabets = explode(",",$arLetters);                
            } else {
                $alphabets = range('A', 'Z');
            }
        }
        
        $query = array();
        foreach ($alphabets as $itemAlphabet) {
            if ($alphabet == '') {
                $limit = " LIMIT 5 ";
            }
            $sql = " 
				(
					SELECT 
						upper('" . $itemAlphabet . "') as alphabet
						" . $fields . "
					FROM 
							" . DB_PREFIX . "brands AS brand
						INNER JOIN 
							" . DB_PREFIX . "brand_details AS brandDet
								ON	(
									brand.brandID = brandDet.brandID
									AND
									brandDet.languageID = '" . $currentLanguageID . "'
								)
					WHERE
						status='A'
						AND
						brandName like  '" . $itemAlphabet . "%' ";
            
            if ($list_brands_having_products == 'Yes') {
                $currDate = (isset($currDateTime) && $currDateTime != "") ? "'" . $currDateTime . "'" : "'" . date('Y-m-d') . "'";
                $sql .= "
					AND
						brand.brandID IN (
							SELECT 
								brandID 
							FROM 
									" . DB_PREFIX . "products AS product
								INNER JOIN 
									" . DB_PREFIX . "business 	AS bus 
										ON (
											product.businessID = bus.businessID 
											AND 
											bus.businessStatus = 'Active'	
									)								
							WHERE 
								product.productStatus ='Active'
								AND (
									product.productStartTime <= " . $currDate . " 
									AND
									(
										product.productEndTime = '0000-00-00'
										OR
										product.productEndTime >=	" . $currDate . "
									)
								)";
                if (isset($catIDAndSubcategories) && $catIDAndSubcategories != '') {
                    $sql .= "
						AND
						product.productID IN 	(
							SELECT 
								productID
							FROM 
									" . DB_PREFIX . "product_to_category 
							WHERE
								productCategoryID IN (" . $catIDAndSubcategories . ")
						)
					";
                }
                $sql .= "
						)
				";
            }
            $sql .= "		
					ORDER BY 
						brandName
					" . $limit . "
				)
			";
            array_push($query, $sql);
        }
        
        $sql = implode(" UNION ", $query);
       
        switch ($readType) {
            case 'getFields':
                return $this->ObjDatabase->getFields($sql);
            case 'readValues':                 
                return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
        }
    }
    public function getBrand($params = array()) {
        extract($params);
        $currentLanguageID = (isset($languageIDs['Content']) && $languageIDs['Content'] > 0) ? $languageIDs['Content'] : $languageIDs['Current'];
        $selectFields      = isset($selectFields) ? $selectFields : '';
        $sql               = " 
			SELECT 
				" . $selectFields . "
				brand.`brandID`, 
				`brandName`, 
				`brandKey`, 
				`brandIcon`, 
				`status`, 
				`createdDate`,
				`brandDescription`, 
				`metaTitle`, 
				`metaDescription`, 
                                `shippingCharge`,
				`searchKeywords`,
				parentID
			FROM 
					" . DB_PREFIX . "brands AS brand
				INNER JOIN 
					" . DB_PREFIX . "brand_details AS brandDet
						ON	(
							brand.brandID = brandDet.brandID
							AND
							brandDet.languageID = '" . $currentLanguageID . "'
						)
		
			WHERE
				1";
        if (isset($brandID) && $brandID > 0) {
            $sql .= "
				AND
				brand.brandID = '" . $brandID . "'
			";
        }
        if (isset($brandKey) && $brandKey != '') {
            $sql .= "
				AND
				brandKey = '" . $this->escape($brandKey) . "'
			";
        }
        if (isset($brandName) && $brandName != '') {
            $sql .= "
					AND
					LOWER(brandName) like '%" . $this->escape(strtolower($brandName)) . "%'
					";
        }
        $sql .= " 
			AND
				status!='D'
			LIMIT  
				1
		";
        
        return $this->ObjDatabase->readValue($sql, MYSQLI_ASSOC);
    }
    public function getSubBrandIDs($brandID) {
        $sql = " SELECT	GROUP_CONCAT(brandID) as brandIDs  FROM " . DB_PREFIX . "brands WHERE parentID='".$brandID."' ";
        return $this->ObjDatabase->readValue($sql);
    }
    public function getFeaturedCategories($brandID) {
        $sql = "
			SELECT 
				categoryID
			FROM
				" . DB_PREFIX . "brand_featured_categories
			WHERE 
				brandID = '" . $brandID . "'
		";
        return $this->ObjDatabase->getFields($sql);
    }
    public function getFeaturedCategoryDetails($brandID, $languageIDs) {
        $sql = "
			SELECT 
				productCatDet.productCategoryName
			FROM
					" . DB_PREFIX . "brand_featured_categories AS brandFeaCat
				INNER JOIN
					" . DB_PREFIX . "product_category_details AS productCatDet
						ON	(
							productCatDet.productCategoryID = brandFeaCat.categoryID
							AND
							languageID='" . $languageIDs['Content'] . "'
						)
			WHERE 
				brandID = '" . $brandID . "'
		";
        return $this->ObjDatabase->getFields($sql);
    }
    public function changeStatus($params = array()) {
        extract($params);
        $sql = "
		UPDATE  
			" . DB_PREFIX . "brands 
		SET 
			status = '" . $status . "' 
		WHERE
			brandID IN ('" . implode("','", $brandIDs) . "')
		";
        $this->ObjDatabase->setQuery($sql);
    }
    public function insert($languageIDs, $brandID = 0) {
        $brandKey = $this->escape($_POST['brandKey']);
        $brandIcn = $this->escape($_POST['brandIcon']);
        if (!empty($_POST['brandIcon']) && !empty($_POST['brandIcon_temp'])) {
            $brandIcon = $brandIcn[0] . "/" . $brandIcn[1] . "/" . $brandIcn;
        } else if (!empty($_POST['brandIcon']) && empty($_POST['brandIcon_temp'])) {
            $brandIcon = $brandIcn;
        } else {
            $brandIcon = '';
        }
        $_POST['brandID'] = isset($_POST['brandID']) ? $_POST['brandID'] : '';
        if ($brandID > 0) {
            $sql = "
				UPDATE 
					" . DB_PREFIX . "brands 
				SET
					brandKey = '" . $brandKey . "',
					brandIcon = '" . $brandIcon . "',
					status = '" . $this->escape($_POST['status']) . "',
					shippingCharge = '" . $this->escape($_POST['shippingCharge']) . "',
					parentID = '" . $this->escape($_POST['parentID']) . "'
				WHERE
					brandID = '" . $brandID . "'
				";
            $this->ObjDatabase->setQuery($sql);
            $sql = "
				UPDATE 
					" . DB_PREFIX . "brand_details 
				SET
					brandName = '" . $this->escape($_POST['brandName']) . "',
					metaTitle = '" . $this->escape($_POST['metaTitle']) . "',
					metaDescription = '" . $this->escape($_POST['metaDescription']) . "',
					searchKeywords = '" . $this->escape($_POST['searchKeywords']) . "',
					brandDescription = '" . $this->escape($_POST['brandDescription']) . "'
				WHERE
					brandID = '" . $brandID . "'
					AND
					languageID = '" . $languageIDs['Content'] . "'
			";
            $this->ObjDatabase->setQuery($sql);
            $sql = "
				DELETE FROM	
					" . DB_PREFIX . "brand_featured_categories 
				WHERE
					brandID = '" . $brandID . "'
			";
            $this->ObjDatabase->setQuery($sql);
        } else {
            $sql = "
				INSERT INTO " . DB_PREFIX . "brands 
					(
					brandID,brandKey,brandIcon,status,shippingCharge,parentID
					)
				VALUES
					(
					'" . $this->escape($_POST['brandID']) . "',
					'" . $brandKey . "',
					'" . $brandIcon . "',
					'" . $this->escape($_POST['status']) . "',
					'" . $this->escape($_POST['shippingCharge']) . "',
					'" . $this->escape($_POST['parentID']) . "'
					)
				";
            $this->ObjDatabase->setQuery($sql);
            $brandID = $this->ObjDatabase->getInsertId();
            foreach ($languageIDs['All'] as $languageID) {
                $tempArr[] = "(
					'" . $brandID . "',
					'" . $languageID . "',
					'" . $this->escape($_POST['brandName']) . "',
					'" . $this->escape($_POST['metaTitle']) . "',
					'" . $this->escape($_POST['metaDescription']) . "',
					'" . $this->escape($_POST['searchKeywords']) . "',
					'" . $this->escape($_POST['brandDescription']) . "')";
            }
            $sql = "
				INSERT INTO " . DB_PREFIX . "brand_details 
					(
					brandID,
					languageID,
					brandName,
					metaTitle,
					metaDescription,
					searchKeywords,
					brandDescription
					)
				VALUES" . implode(",", $tempArr);
            $this->ObjDatabase->setQuery($sql);
        }
        $tempArr              = array();
        $_POST['categoryIDs'] = isset($_POST['categoryIDs']) ? $_POST['categoryIDs'] : '';
        if (!empty($_POST['categoryIDs'])) {
            foreach ($_POST['categoryIDs'] as $categoryID) {
                if ($categoryID > 0) {
                    $tempArr[] = "(
						'" . $brandID . "',
						'" . $categoryID . "'
						)";
                }
            }
        }
        $sql = "
				INSERT INTO	" . DB_PREFIX . "brand_featured_categories 
					(
					brandID,
					categoryID
					)
				VALUES " . implode(",", $tempArr);
        $this->ObjDatabase->setQuery($sql);
        return $brandID;
    }
    public function listFeaturedBrands($params = array()) {
        extract($params);
        $currDate          = (isset($currDateTime) && $currDateTime != "") ? "'" . $currDateTime . "'" : "'" . date('Y-m-d') . "'";
        $currentLanguageID = (isset($languageIDs['Content']) && $languageIDs['Content'] > 0) ? $languageIDs['Content'] : $languageIDs['Current'];
        $sql               = "
			SELECT 
				brand.brandKey,
				brandDet.brandName
			FROM 
					" . DB_PREFIX . "brand_featured_categories  AS brandFetCat
				INNER JOIN
					" . DB_PREFIX . "brands AS brand
						ON	(
							brand.brandID = brandFetCat.brandID
							AND 
							status ='A'
						)
				INNER JOIN
					" . DB_PREFIX . "brand_details AS brandDet
						ON	(
							brandDet.brandID = brand.brandID
							AND 
							brandDet.languageID ='" . $currentLanguageID . "'
						)
		
			WHERE
				brandFetCat.categoryID = '" . $categoryID . "'
				AND
				brand.brandID!='" . $brandID . "'";
        if ($list_brands_having_products == 'Yes') {
            $sql .= "
				AND
				brand.brandID IN (
					SELECT 
						brandID 
					FROM 
							" . DB_PREFIX . "products AS product
						INNER JOIN 
							" . DB_PREFIX . "business 	AS bus 
								ON (
									product.businessID = bus.businessID 
									AND 
									bus.businessStatus = 'Active'	
							)								
					WHERE 
						product.productStatus ='Active'
						AND (
							product.productStartTime <= " . $currDate . " 
							AND
							(
								product.productEndTime = '0000-00-00'
								OR
								product.productEndTime >=	" . $currDate . "
							)
						)";
            if (isset($selCategoryID) && $selCategoryID != '') {
                $sql .= "		
						AND
						product.productID IN 	(
							SELECT 
								productID
							FROM 
									" . DB_PREFIX . "product_to_category 
							WHERE
								productCategoryID IN (" . $selCategoryID . ")
						)";
            }
            $sql .= "			
				)
				
			";
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    public function checkBrandKeyExist($brandKey, $brandID = '') {
        $sql = "
			SELECT EXISTS(
				SELECT 
					1
				FROM 
					" . DB_PREFIX . "brands
				WHERE 
					brandKey = '" . $brandKey . "'";
        if ($brandID > 0) {
            $sql .= "
					AND 
					brandID != '" . $brandID . "'
			";
        }
        $sql .= "
				AND 
					status!='D'
				LIMIT 
					1
			) AS exist
		";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['exist'];
    }
    public function getBrandKeyById($brandID) {
        return $this->ObjDatabase->getField("SELECT 
					brandKey
				FROM 
					" . DB_PREFIX . "brands
				WHERE 
					brandID = '" . $brandID . "'");
    }
}
