<?php

/**
 * Class that operate on table 'product'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2013-04-06 10:57
 */
class ProductMySqlExtDAO extends ProductMySqlDAO
{

    public function queryByConditionsExtLimited($value, $start, $end)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE product.conditions = ?
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function GetFinalProduct($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0
		AND product.type = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        //print_r("ddddddddddddddddddddddddd");
        return $this->getList($sqlQuery);
    }

    public function GetRawMaterials($name = '', $queryString = "")
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getFinalProductsWithProductionRate($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		JOIN productionrate
		ON productionrate.finalName = product.productId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0
                and productionrate.conditions = 0
		AND product.type = 0
                group by product.productId'; //group by is m=needed in productionExecutionController
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        //print_r("ddddddddddddddddddddddddd");
        return $this->getList($sqlQuery);
    }

    public function getFinalProductsWithProductionRateGrouped($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		JOIN productionrate
		ON productionrate.finalName = product.productId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0
                and productionrate.conditions = 0
		AND product.type = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        //print_r("ddddddddddddddddddddddddd");
        return $this->getList($sqlQuery);
    }

    public function updatesortby($productid, $sortby)
    {
        $sql = 'UPDATE product SET sortby = ' . $sortby . ' WHERE productId = ' . $productid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getFinalProductsWithOutProductionRate($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		LEFT JOIN productionrate
		ON productionrate.finalName = product.productId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0
		AND product.type = 0
		AND  productionrate.finalName IS NULL';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        //print_r("ddddddddddddddddddddddddd");
        return $this->getList($sqlQuery);
    }

    public function getFinalProductsWithOutProductionRateSC($name, $storeid, $queryString = '', $limitString = '', $controlNameSearch = 1)
    {
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
        }
        $sql = 'SELECT SQL_CACHE product.*, productcat.productCatName ,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName,product.weightedDiscount
		FROM product

		JOIN productcat
		ON product.productCatId = productcat.productCatId


		left join storedetail
		on storedetail.productid=product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid


            LEFT JOIN productionrate
		ON (productionrate.finalName = product.productId and productionrate.sizeid=sizecolorstoredetail.sizeid and productionrate.colorid=sizecolorstoredetail.colorid)


		WHERE
                ' . $queryStringNameSearch . '
		and (if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' and storedetail.unittype=0 ,sizecolorstoredetail.storeid=' . $storeid . ')
                    or product.isService = 1)

		AND product.conditions = 0
		and productcat.conditions = 0
                AND product.type = 0
		AND  productionrate.finalName IS NULL
                ' . $queryString . '
                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ' . $limitString;
        $sqlQuery = new SqlQuery($sql);
        //        print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllLimited($start, $end)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedLimitedSimple($start, $end)
    {
        $sql = 'SELECT *
		FROM product order by productId desc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedLimitedSimpleForCurlNormal($start, $end, $sqlquery = '', $joinConditionWithStore = '')
    {
        $sql = 'SELECT product.*,SUM(storedetail.productquantity) AS sumProductQuantity
		FROM product
                left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
                where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
                group by storedetail.productid
                order by productId asc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedLimitedSimpleForCurlNormal2($sqlquery = '', $joinConditionWithStore = '')
    {
        $sql = 'SELECT product.*,SUM(storedetail.productquantity) AS sumProductQuantity
		FROM product
                join onlinetempstoredetail on onlinetempstoredetail.productid = product.productId
                left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
                where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
                group by storedetail.productid
                order by productId asc FOR UPDATE';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedLimitedSimpleForCurlNormal3($limit, $sqlquery = '', $joinConditionWithStore = '')
    {
        $sql = 'SELECT product.*,SUM(storedetail.productquantity) AS sumProductQuantity
		FROM product
                left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
                where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
                group by storedetail.productid
                order by productId asc
		limit ' . $limit . ' ';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllSimpleForCurlNormalSyncAndLock($sqlquery = '', $joinConditionWithStore = '')
    {
        $sql = 'SELECT product.*,onlinetempproduct.edited,SUM(storedetail.productquantity) AS quantity
		FROM product
                join onlinetempproduct on onlinetempproduct.productid = product.productId
                left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
                where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
                group by product.productId';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedLimitedSimpleForCurlOptic($start, $end, $sqlquery = '')
    {
        $sql = 'SELECT product.*,productcat.productCatParent,productcat.productCatName,productcat.logo as catlogo
		FROM product
                join productcat on productcat.productCatId = product.productCatId
                where product.isOptic = 2 and product.conditions=0 and product.isService=0 and product.type=0
                and productcat.isOptic = 2 and productcat.conditions=0 ' . $sqlquery . '
                order by productId desc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllSimpleForCurlOpticSyncAndLock($sqlquery = '')
    {
        $sql = 'SELECT product.*,productcat.productCatParent,productcat.productCatName,productcat.logo as catlogo,onlinetempproduct.edited
		FROM product
                join productcat on productcat.productCatId = product.productCatId
                join onlinetempproduct on onlinetempproduct.productid = product.productId
                where product.isOptic = 2 and product.conditions=0 and product.isService=0 and product.type=0
                and productcat.isOptic = 2 and productcat.conditions=0 ' . $sqlquery . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedLimitedSimpleInStore($storeid, $start, $end)
    {
        $sql = 'SELECT product.*,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid

		FROM product
                join storedetail on storedetail.productid = product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId

                where if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' ,sizecolorstoredetail.storeid=' . $storeid . ')
                    GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                order by productId desc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function getProductsCount()
    {
        $sql = 'SELECT count(productId) as productId
		FROM product ';
        $sqlQuery = new SqlQuery($sql);

        return $this->getRow($sqlQuery);
    }

    //it gets the count of products that is not optic
    public function getProductsCountForCURLNormal($sqlquery = '', $joinConditionWithStore = '')
    {
        $sql = 'SELECT count(product.productId) as productId
                FROM product
                left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
                where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    //it gets the count of products that is optic from productcat table
    public function getProductsCountForCURLOptic($sqlquery)
    {
        $sql = 'select count(productCatId) as productId  FROM productcat where isOptic = 2 and conditions = 0 ' . $sqlquery;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllForShow()
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId';

        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllForShow2($queryString = '')
    {
        $sql = 'SELECT product.productId,product.productName, productcat.productCatName,product.isOptic,product.conditions,product.productCatId
                , product.productBuyPrice,product.productSellAllPrice,product.productSellHalfPrice,product.productSellUnitPrice , product.logo , product.productDescription,
                product.parcode,product.logo1,product.logo2,product.logo3,product.logo4,product.logo5,product.logo6,product.logo7,product.sortby

        FROM product
        join productcat
        on productcat.productCatId = product.productCatId
        join storedetail on product.productId = storedetail.productid
        WHERE productcat.isOptic != 2
                and product.isOptic != 2
                ' . $queryString . '

                union

                SELECT   product.productCatId as productId,product.productCatName as productName ,cat.productCatName as productCatName,product.isOptic,product.conditions,cat.productCatId as productCatId
                ,-1 as productBuyPrice ,-1 as productSellAllPrice,-1 as productSellHalfPrice,-1 as productSellUnitPrice , product.logo,product.productCatDescription as productDescription
                ,product.opticServices,-1 as logo1 ,-1 as logo2 ,-1 as logo3 ,-1 as logo4 ,-1 as logo5,-1 as logo6,-1 as logo7,-1 as sortby
                FROM productcat as product
                join productcat as cat
                on cat.productCatId = product.productCatParent
                join storedetail on product.productCatId = storedetail.productid
                where product.isOptic = 2 ' . $queryString . ' ';

        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadExt($id)
    {
        $sql = 'SELECT product.*,
                SUM(storedetail.productquantity) AS sumProductQuantity
                FROM product
		left JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ?
                group by storedetail.productid
               ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        // print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function loadExt2($id, $ids = "0")
    {
        $sql = 'SELECT product.productId,concat(productcat.productCatName ,"/",product.productName) as productName,
                SUM(storedetail.productquantity) AS sumProductQuantity
                FROM product
		JOIN storedetail
		ON storedetail.productid = product.productId
                join productcat
                on productcat.productCatId = product.productCatId
		WHERE product.productId in( ' . $id . ')
                and product.productId not in(' . $ids . ')
                group by storedetail.productid
               ';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getProductQuantity($id, $sqlQuery = "")
    {
        $sql = 'SELECT product.*,
                SUM(storedetail.productquantity) AS sumProductQuantity
                FROM product
		JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ?
                ' . $sqlQuery . '
                group by storedetail.productid
               ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        // print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function loadExtforcard($id, $storeId)
    {
        $sql = 'SELECT product.*, storedetail.productquantity
		FROM product
		JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ? and storedetail.storeId=' . $storeId . '';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        //print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function loadExtByProductId($id)
    {
        $sql = 'SELECT product.*, storedetail.productquantity
		FROM product
		JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ? ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        //print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function loadExtByProductId2($id)
    {
        $sql = 'SELECT product.*, sum(storedetail.productquantity) as productquantity
		FROM product
		JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ?
                group by product.productId';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        //print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function loadEX($id)
    {
        $sql = 'SELECT * FROM product WHERE productId = ? and conditions =0';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function updateExt($product)
    {
        $sql = 'UPDATE product SET productName = ?, productDescription = ?, productCatId = ?, conditions = ?, userId = ? WHERE productId = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($product->productName);
        $sqlQuery->setString2($product->productDescription);
        $sqlQuery->setNumber($product->productCatId);
        $sqlQuery->setNumber($product->conditions);
        $sqlQuery->setNumber($product->userId);

        $sqlQuery->setNumber($product->productId);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateWeightedDiscount($weightedDiscount, $productid)
    {
        $sql = 'UPDATE product SET weightedDiscount = ' . $weightedDiscount . ' WHERE productId = ' . $productid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByProductCatIdExt($productCatId)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		left join productcat
		on productcat.productCatId = product.productCatId
		WHERE product.productCatId = ' . $productCatId . '
                and product.conditions = 0
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdInExt($productCatId, $querystore = '', $productQuery = '')
    {
        $sql = 'SELECT SQL_CACHE product.productId,product.productName, productcat.productCatName,product.isOptic,product.hasSizeAndColor,product.conditions,product.productCatId
                , product.productBuyPrice,product.productSellAllPrice,product.productSellHalfPrice,product.productSellUnitPrice , product.logo , product.productDescription,
                product.parcode,product.logo1,product.logo2,product.logo3,product.logo4,product.logo5,product.logo6,product.logo7,product.reviewType,product.online,product.updatebyuser

		FROM product
		join productcat
		on productcat.productCatId = product.productCatId
		join storedetail on product.productId = storedetail.productid
		WHERE product.productCatId in( ' . $productCatId . ') ' . $productQuery . '
                and productcat.isOptic != 2
                and product.isOptic != 2
                ' . $querystore . '


                union

                SELECT   product.productCatId as productId,product.productCatName as productName ,cat.productCatName as productCatName,product.isOptic,0 as hasSizeAndColor,product.conditions,cat.productCatId as productCatId
                ,-1 as productBuyPrice ,-1 as productSellAllPrice,-1 as productSellHalfPrice,-1 as productSellUnitPrice , product.logo,product.productCatDescription as productDescription
                ,product.opticServices,-1 as logo1 ,-1 as logo2 ,-1 as logo3 ,-1 as logo4 ,-1 as logo5,-1 as logo6,-1 as logo7 ,-1 as reviewType ,-1 as online,-1 as updatebyuser
                FROM productcat as product
                join productcat as cat
		        on cat.productCatId = product.productCatParent
		        join storedetail on product.productCatId = storedetail.productid
                where product.productCatId in( ' . $productCatId . ')
                and product.isOptic = 2
                ' . $querystore . '';
        $sqlQuery = new SqlQuery($sql);
        //        echo $sql;
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdInExts($barcode)
    {
        $sql = 'SELECT * , product.logo
        FROM product
        join productcat
        on productcat.productCatId = product.productCatId where 1 and product.productId = ' . $barcode;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdInExts2($barcode)
    {
        $sql = 'SELECT * , product.logo
        FROM product
        join productcat
        on productcat.productCatId = product.productCatId where 1 and product.parcode = "' . $barcode . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductparentCatIdExt($productCatId)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		join productcat
		on productcat.productCatId = product.productCatId
		WHERE product.productCatId  in  (select productCatId    from productcat  where productCatParent =' . $productCatId . ' )
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdlimted($start, $end, $productCatId)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		join productcat
		on productcat.productCatId = product.productCatId
		WHERE product.productCatId = ' . $productCatId . '
		order by productId desc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    ////////////////////////////////////////////////////////////////////
    public function queryByProductCatIdNotdeleted($productCatId)
    {
        $sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity
		FROM product
		 join productcat
		on productcat.productCatId = product.productCatId
		 JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.productCatId = ' . $productCatId . '
		AND product.conditions = 0
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatnotdel($value)
    {
        $sql = 'SELECT * FROM product WHERE productCatId = ? and  product.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdNotdeletedExt($productCatId)
    {
        $sql = 'SELECT product.productid
		FROM product
		 join productcat
		on productcat.productCatId = product.productCatId
		 JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.productCatId = ' . $productCatId . '
		AND product.conditions = 0
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdNotdeletedLimited($productCatId, $start, $end)
    {
        $sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity
		FROM product
		 join productcat
		on productcat.productCatId = product.productCatId
		 JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.productCatId = ' . $productCatId . '
		AND product.conditions = 0
		order by productId desc
		limit ' . $start . ', ' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryProductsWithBuyPrice()
    {
        $sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity
		FROM product
		 join productcat
		on productcat.productCatId = product.productCatId
		 JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.conditions = 0
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    /////////////////////////////////////
    public function updateExcelInfo($proExcelid, $proExcelParcode, $productId)
    {
        $sql = 'UPDATE product SET proExcelid =' . $proExcelid . ',proExcelParcode="' . $proExcelParcode . '"  WHERE productId =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function getExcelInfo($queryString)
    {
        $sql = 'SELECT product.productId,product.productName,product.proExcelid,product.proExcelParcode
		FROM product
                ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updatehasSizeColor($hasSizeAndColorVal, $productId)
    {
        $sql = 'UPDATE product SET hasSizeAndColor =' . $hasSizeAndColorVal . ' WHERE productId =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function deletetemp($productId)
    {
        $sql = 'UPDATE product SET conditions =1 WHERE productId =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function deletefinally($productId)
    {
        $sql = 'UPDATE product SET conditions =2 WHERE productId =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function returndelete($productId)
    {
        $sql = 'UPDATE product SET conditions =0 WHERE productId =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    //used in buy bill
    public function queryAllExt()
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE product.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllProducts($querryString = '', $limitQs = '')
    {
        $sql = 'SELECT DISTINCT(product.productCatId) as productCatId, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE product.conditions = 0 and productcat.conditions = 0 ' . $querryString . $limitQs;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllProducts2()
    {
        $sql = 'SELECT DISTINCT(product.productCatId) as productCatId, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE product.conditions = 0 and productcat.conditions = 0 and productcat.isOptic != 2';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //used in buy bill
    public function queryAllCategories()
    {
        $sql = 'SELECT DISTINCT(product.productCatId), productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE productcat.conditions = 0';

        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //used in buy bill
    public function queryAllCategoriesParent()
    {
        $sql = 'SELECT DISTINCT(product.productCatId), cat.productCatName,catParent.productCatName as catParentName
		FROM product
		JOIN productcat cat ON product.productCatId = cat.productCatId
                JOIN productcat catParent ON cat.productCatParent = catParent.productCatId
		WHERE cat.conditions = 0';

        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllCategoriesParentLeftJoin()
    {
        $sql = 'SELECT DISTINCT(product.productCatId), cat.productCatName,catParent.productCatName as catParentName
		FROM product
		JOIN productcat cat ON product.productCatId = cat.productCatId
                Left JOIN productcat catParent ON cat.productCatParent = catParent.productCatId
		WHERE cat.conditions = 0 and product.conditions =0';

        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //    public function getLastLevelCats($productCatName) {
    //        $sql = 'SELECT DISTINCT(cat.productCatId), cat.productCatName
    //		FROM product
    //		right JOIN productcat cat ON product.productCatId = cat.productCatId
    //                left JOIN productcat catParent ON cat.productCatParent = catParent.productCatId
    //		WHERE cat.conditions = 0 and cat.productCatId IS NOT NULL and catParent.productCatId IS NULL and cat.productCatName LIKE "%' . $productCatName . '%"';
    //
    //        print_r($sql . '<br>');
    //        $sqlQuery = new SqlQuery($sql);
    //        return $this->getList($sqlQuery);
    //    }

    public function queryWithParcode($parcode)
    {
        $sql = 'SELECT product.*,productcat.productCatName  FROM product
                join productcat on productcat.productCatId = product.productCatId
		    where product.conditions = 0
		    and product.parcode = "' . $parcode . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithParcodeExcel($parcode)
    {
        $sql = 'SELECT product.*,productcat.productCatName  FROM product
                join productcat on productcat.productCatId = product.productCatId
		    where product.conditions = 0
		    and product.proExcelParcode = "' . $parcode . '"';
        $sqlQuery = new SqlQuery($sql);
        //echo $sql;
        return $this->getRow($sqlQuery);
    }

    public function queryWithProUnitParcode($proUnitParcode)
    {
        $sql = 'SELECT product.*,productcat.productCatName,productunit.productunitid,productunit.unitid,productunit.proUnitSellAllPrice,productunit.proUnitSellHalfPrice,productunit.proUnitSellUnitPrice,productunit.proUnitBuyPrice
                FROM product
                join productcat on productcat.productCatId = product.productCatId
                join productunit on (product.productId = productunit.productid and productunit.proUnitParcode = "' . $proUnitParcode . '" and productunit.conditions = 0)
                where product.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //echo $sql;
        return $this->getRow($sqlQuery);
    }

    public function queryWithSerialnumber($parcode)
    {
        $sql = 'SELECT product.*
		 FROM product  JOIN  productserial

		 ON  productserial.productid = product.productId
		 where   productserial.serialnumber ="' . $parcode . '"
		 AND productserial.productserailid
				IN (
					SELECT MAX(productserailid) FROM productserial
					WHERE productserial.productid = product.productId
					ORDER BY productserailid DESC
				)

		 and product.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithSerialnumberAndDon($parcode, $don)
    {
        $sql = 'SELECT product.*
		 FROM product  JOIN  productserial

		 ON  productserial.productid = product.productId
		 where   productserial.serialnumber ="' . $parcode . '"
		 AND productserial.don = ' . $don . '  AND productserial.del =0
		 AND productserial.productserailid
				IN (
					SELECT MAX(productserailid) FROM productserial
					WHERE productserial.productid = product.productId
					ORDER BY productserailid DESC
				)

		 and product.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithSerialnumberAndAvailable($parcode)
    {
        $sql = 'SELECT product.*, productserial.productserailid, productserial.don
                 ,productserial.sizeid,productserial.colorid,productserial.chassisNo,productserial.motorNo,productserial.theColor,size.name as sizeName,color.name as colorName
		 FROM product
                 JOIN  productserial ON  productserial.productid = product.productId

                 left join sizecolor as size on size.id = productserial.sizeid
                 left join sizecolor as color on color.id = productserial.colorid

		 where productserial.serialnumber = "' . $parcode . '"
		 AND productserial.don > 0  AND productserial.del =0
		 AND product.conditions = 0';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithSerialnumberEvenIfNotAvailable($parcode)
    {
        $sql = 'SELECT product.*, productserial.productserailid, productserial.don,  productcat.productCatName
                 ,productserial.sizeid,productserial.colorid,productserial.chassisNo,productserial.motorNo,productserial.theColor,size.name as sizeName,color.name as colorName
		 FROM product
                 join productcat on productcat.productCatId = product.productCatId
                 JOIN  productserial ON  productserial.productid = product.productId

                 left join sizecolor as size on size.id = productserial.sizeid
                 left join sizecolor as color on color.id = productserial.colorid

		 where productserial.serialnumber = "' . $parcode . '"
		 AND productserial.del =0
		 AND product.conditions = 0';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadByProductId($productid)
    {
        $sql = 'SELECT product.*, productcat.selldiscount, productcat.buydiscount,
				 productcat.discounttype,buypricereal
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
				 where product.productId = ' . $productid . '';

        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithCoditionsAndCatConditions()
    {
        $sql = 'SELECT product.*,  productcat.productCatName
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
				 where product.conditions = 0 and productcat.conditions =0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadProduct($id)
    {
        $sql = 'SELECT product.*, productcat.productCatName
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
		WHERE product.productId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function loadProductExt($id)
    {
        $sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity,
		storedetail.storedetailid, storedetail.storeid
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
				 JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        //print($sql);
        return $this->getRow($sqlQuery);
    }

    public function getProductCatId($productId)
    {
        $sql = 'SELECT product.productCatId
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
		WHERE product.productId = ' . $productId . '';
        $sqlQuery = new SqlQuery($sql);
        //print($sql."<br />");
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryWithProductcatIdAndConditions($productcatid)
    {
        $sql = 'SELECT product.*,  productcat.productCatName
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
				 where product.conditions = 0 and productcat.conditions =0
				 and product.productCatId =' . $productcatid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExt($name, $limit)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE  CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0
		limit ' . $limit . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimit($name, $queryString = '', $controlNameSearch = 1, $limitQS = '')
    {
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name,product.procode) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,product.procode) LIKE "%' . $name . '%" ';
        }
        $sql = 'SELECT SQL_CACHE product.*, productcat.productCatName,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                left JOIN storedetail
		ON storedetail.productid = product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
                ' . $queryStringNameSearch . '
		AND product.conditions = 0
                 and storedetail.unittype=0
		and productcat.conditions = 0 ' . $queryString . '
                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid ' . $limitQS;
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimitNoService($name, $storeidQS = '')
    {
        $sql = 'SELECT product.*, productcat.productCatName,productquantity
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                join storedetail
		on storedetail.productid=product.productId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
                AND product.isService = 0
                ' . $storeidQS . '
		and productcat.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimitNoServiceNotCollective($name, $storeidQS = '', $storeidQSJoin = '', $controlNameSearch = 1, $limitQS = '')
    {
        $storeidQS2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQS);
        $storeidQSJoin2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQSJoin);
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
        }
        $sql = '
            SELECT  SQL_CACHE * from
            ((
                    SELECT product.productId,product.productName,product.productCatId,productquantity,product.productBuyPrice,productcat.productCatName,productcat.buypricereal
                ,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
                ,"" as sizeName,"" as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                left join storedetail
		on (storedetail.productid=product.productId ' . $storeidQSJoin . ')
                left join productingredients
		on productingredients.productId=product.productId

		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
                AND product.isService = 0
                ' . $storeidQS . '
		and productcat.conditions = 0
                and product.hasSizeAndColor = 0
                and productingredients.id IS NULL

                ) union (

                    SELECT product.productId,product.productName,product.productCatId,sizecolorstoredetail.quantity as productquantity,product.productBuyPrice, productcat.productCatName,productcat.buypricereal
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                left join productingredients
		on productingredients.productId=product.productId

                left join sizecolorstoredetail on (sizecolorstoredetail.productid = product.productId ' . $storeidQSJoin2 . ')
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
                ' . $queryStringNameSearch . '
		AND product.conditions = 0
                AND product.isService = 0
                ' . $storeidQS2 . '
                and product.hasSizeAndColor = 1
		and productcat.conditions = 0
                and productingredients.id IS NULL

                )) as temp
                GROUP BY productId,sizeid,colorid ' . $limitQS;
        //echo $sql;
        // -- group by product.productId
        /* $sql = 'SELECT product.productId,product.productName,product.productCatId,productquantity,product.productBuyPrice, productcat.productCatName
          ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
          ,size.name as sizeName,color.name as colorName
          FROM product
          JOIN productcat
          ON product.productCatId = productcat.productCatId
          join storedetail
          on storedetail.productid=product.productId
          left join productingredients
          on productingredients.productId=product.productId

          left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
          left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
          left join sizecolor as color on color.id=sizecolorstoredetail.colorid

          WHERE
          CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
          AND product.conditions = 0
          AND product.isService = 0
          ' . $storeidQS . '
          and productcat.conditions = 0
          and productingredients.id IS NULL'; */
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimitNotCollective($name, $storeidQS = '', $storeidQSJoin = '', $controlNameSearch = 1, $limitQS = '')
    {
        $storeidQS2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQS);
        $storeidQSJoin2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQSJoin);
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
        }
        $sql = '
            SELECT  SQL_CACHE * from
            ((
                    SELECT product.productId,product.productName,product.productCatId,productquantity,product.productBuyPrice,productcat.productCatName,productcat.buypricereal,
                    product.hasSizeAndColor,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
                ,"" as sizeName,"" as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                left join storedetail
		on (storedetail.productid=product.productId ' . $storeidQSJoin . ')
                left join productingredients
		on productingredients.productId=product.productId

		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
                ' . $storeidQS . '
		and productcat.conditions = 0
                and product.hasSizeAndColor = 0
                and productingredients.id IS NULL

                ) union (

                    SELECT product.productId,product.productName,product.productCatId,sizecolorstoredetail.quantity as productquantity,product.productBuyPrice, productcat.productCatName,productcat.buypricereal,
                    product.hasSizeAndColor,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                left join productingredients
		on productingredients.productId=product.productId

                left join sizecolorstoredetail on (sizecolorstoredetail.productid = product.productId ' . $storeidQSJoin2 . ')
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
                ' . $queryStringNameSearch . '
		AND product.conditions = 0
                ' . $storeidQS2 . '
                and product.hasSizeAndColor = 1
		and productcat.conditions = 0
                and productingredients.id IS NULL

                )) as temp
                GROUP BY productId,sizeid,colorid ' . $limitQS;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimitdeleted($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 1';
        $sqlQuery = new SqlQuery($sql);
        print($sql);
        return $this->getList($sqlQuery);
    }

    //search in the whole path
    public function queryByNameandstoreidExtWithoutLimit2($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
        FROM product
        JOIN productcat
        ON product.productCatId = productcat.productCatId
        left join storedetail
        on storedetail.productid=product.productId
                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

        WHERE
                CONCAT_WS("/",product.parcode,product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%"
        AND product.conditions = 0
        AND productcat.conditions = 0
                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
    ';

        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimit2($name, $storeid, $queryString = '', $limitString = '', $controlNameSearch = 1)
    {
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name,product.procode) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,product.procode) LIKE "%' . $name . '%" ';
        }
        $sql = 'SELECT SQL_CACHE product.*, productcat.productCatName ,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName,product.weightedDiscount,productcat.buytotal,productcat.buyhalf,productcat.buypart,productBuyPrice
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId

		left join storedetail
		on storedetail.productid=product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
                ' . $queryStringNameSearch . '
		and (if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' and storedetail.unittype=0 ,sizecolorstoredetail.storeid=' . $storeid . ')
                    or product.isService = 1)

		AND product.conditions = 0
		and productcat.conditions = 0
                ' . $queryString . '
                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ' . $limitString;
        $sqlQuery = new SqlQuery($sql);
        // print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExtWithoutLimit2Fatma($catid, $storeString)
    {
        $sql = 'SELECT product.*, productcat.productCatName ,productquantity
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId

		join storedetail
		on storedetail.productid=product.productId

		WHERE product.productCatId = ' . $catid . '

                ' . $storeString . '

		AND product.conditions = 0
		and productcat.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameandstoreidExt($name, $limit, $storeid)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		join storedetail
		on storedetail.productid=product.productId
		WHERE (productName LIKE "%' . $name . '%"

		OR  productCatName LIKE "%' . $name . '%")
		and  storedetail.storeid=' . $storeid . '
		AND product.conditions = 0
		and productcat.conditions = 0
	';

        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    //search in the whole path
    public function queryByNameandstoreidExtWithoutLimit($name, $storeid)
    {
        $sql = 'SELECT product.*, productcat.productCatName,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		join storedetail
		on storedetail.productid=product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
                CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%"
		AND  storedetail.storeid=' . $storeid . '
		AND product.conditions = 0
		AND productcat.conditions = 0

                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
	';

        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    //search in the whole path
    public function queryByNameandstoreidExtWithoutLimitNoService($name, $storeid, $controlNameSearch = 1)
    {
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
        }
        $sql = 'SELECT SQL_CACHE product.*, productcat.productCatName,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		left join storedetail
		on storedetail.productid=product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
		' . $queryStringNameSearch . '

		AND  if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' and storedetail.unittype=0 ,sizecolorstoredetail.storeid=' . $storeid . ')
		AND product.conditions = 0
                AND product.isService = 0
		AND productcat.conditions = 0
                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
	';

        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function queryByNameandstoreidExtWithoutLimitNoService2($name, $storeid, $procat)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		join storedetail
		on storedetail.productid=product.productId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
		AND  storedetail.storeid=' . $storeid . '
                AND  productcat.productCatId =' . $procat . '
		AND product.conditions = 0
                AND product.isService = 0
		AND productcat.conditions = 0
	';

        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function updatebarcode($product)
    {
        $sql = 'UPDATE product SET  parcode = ? WHERE productId = ?';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->set($product->parcode);

        $sqlQuery->setNumber($product->productId);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateTreeId($productid, $treeId)
    {
        $sql = 'UPDATE product SET treeId = ' . $treeId . ' WHERE productId = ' . $productid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryWithParcodeExceptId($parcode, $id)
    {
        $sql = 'SELECT * FROM product
		    where product.parcode = "' . $parcode . '" and productId != ' . $id;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function updateproductBuyPrice($productId, $productBuyPrice)
    {
        $sql = 'UPDATE product SET  productBuyPrice = "' . $productBuyPrice . '" WHERE productId = ' . $productId . ' ';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function updateproductBuyAndSellPriceByValue($productId, $productBuyPrice, $diff)
    {
        $sql = 'UPDATE product SET  productBuyPrice = ' . $productBuyPrice . '
                ,productSellAllPrice=productSellAllPrice+' . $diff . ',productSellUnitPrice=productSellUnitPrice+' . $diff . ',productSellHalfPrice=productSellHalfPrice+' . $diff . '
                WHERE productId =' . $productId;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateproductBuyAndSellPriceByPercentage($productId, $productBuyPrice, $percentage)
    {
        $sql = 'UPDATE product SET  productBuyPrice = ' . $productBuyPrice . '
                ,productSellAllPrice=productSellAllPrice*' . $percentage . ',productSellUnitPrice=productSellUnitPrice*' . $percentage . ',productSellHalfPrice=productSellHalfPrice*' . $percentage . '
                WHERE productId =' . $productId;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryAllproductwithserail()
    {
        $sql = 'SELECT * FROM product where productId in(SELECT  `productid`
FROM  `productserial` )';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdEX($value)
    {
        $sql = 'SELECT product.* , productcat.productCatName
		 FROM product
		 left join productcat
		 on productcat.productCatId=product.productCatId
		  WHERE product.productCatId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAllCategories($queryString = '')
    {
        $sql = 'SELECT product.productId,product.productName , productcat.productCatName , productcat.productCatId
		 FROM product
		 left join productcat on productcat.productCatId=product.productCatId
                 where 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function checkForProductNameInOneCategory($name, $catid)
    {
        $sql = 'SELECT * FROM product WHERE productName LIKE "%' . $name . '%" AND productCatId = ' . $catid . ' and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function checkForProductNameInOneCategory2($name, $catid)
    {
        $sql = 'SELECT * FROM product WHERE productName = "' . $name . '" AND productCatId = ' . $catid . ' and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function querySumQuantityWithProductCatId($productCatId, $querysearch = '')
    {
        $sql = 'SELECT product.*, productcat.productCatName,   productcat.buydiscount,productcat.selldiscount,productcat.discounttype,
		SUM(storedetail.productquantity) AS sumProductQuantity
		FROM product JOIN productcat
		ON productcat.productCatId = product.productCatId
		JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.productCatId = ' . $productCatId . $querysearch . '
		AND product.conditions = 0
		group by storedetail.productid
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function querySumQuantity($querysearch = '')
    {
        $sql = 'SELECT product.*, productcat.productCatName,
		SUM(storedetail.productquantity) AS sumProductQuantity
		FROM product JOIN productcat
		ON productcat.productCatId = product.productCatId
		JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.conditions = 0  ' . $querysearch . '
		group by storedetail.productid';
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function querySumQuantityWithBuyPrice()
    {
        $sql = 'SELECT product.*, productcat.productCatName,
		SUM(storedetail.productquantity) AS sumProductQuantity
		FROM product JOIN productcat
		ON productcat.productCatId = product.productCatId
		JOIN storedetail
		ON storedetail.productid = product.productid
		WHERE product.conditions = 0
		group by storedetail.productid
		order by productId desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadForReport($id)
    {
        $sql = 'SELECT productSellUnitPrice, productSellAllPrice, productSellHalfPrice
		FROM product
		WHERE product.productId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function getName($id)
    {
        $sql = 'SELECT concat(product.productName,"/",productcat.productCatName) as productName FROM product JOIN productcat
        ON product.productCatId = productcat.productCatId WHERE productId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function getProductBuyPriceById($id)
    {
        $sql = 'SELECT productBuyPrice FROM product WHERE productId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryWithStoreId22($storeid, $querystring = '')
    {
        $sql = 'SELECT Distinct sum(storedetail.productquantity) as productquantity,
		store.storeName, product.productId,product.productName,sum(product.limitamount)as limitamount
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 and limitamount > productquantity
		 and  storedetail.storeid = ' . $storeid . ' ' . $querystring . '
		 group by product.productId
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallproduct()
    {
        $sql = 'SELECT Distinct sum(storedetail.productquantity) as productquantity,
		store.storeName, product.productId,product.productName,sum(product.limitamount) as limitamount
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 and limitamount >productquantity
		 group by product.productId
		 order by storedetailid desc
		  ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithParcodeandid($parcode, $productid)
    {
        $sql = 'SELECT * FROM product
		    where product.productId  != ' . $productid . '
		    and product.parcode = "' . $parcode . '"';

        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllrepateparcode($end)
    {
        $sql = 'SELECT  product.parcode ,  `parcode` , COUNT( parcode ) AS ttttt
                FROM product
                GROUP BY parcode
                HAVING ttttt >1
                limit 0,' . $end . '';

        //	print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAlldeletedproduct($end)
    {
        $sql = 'SELECT  product.*
		FROM product
		WHERE `conditions` = 1
                limit ' . $end . ',50';


        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAlldeletedproductwithid($productId)
    {
        $sql = 'SELECT  product.*
					FROM product
					WHERE `productId` = ' . $productId . '

				';


        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllnothaveunit()
    {
        $sql = 'SELECT productId FROM product where productId not in(SELECT DISTINCT (
`productid`
)
FROM  `productunit`  ) LIMIT 0 ,1000';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllnotinstor()
    {
        $sql = 'SELECT productId FROM product where productId not in(SELECT DISTINCT (
`productid`
)
FROM  `storedetail` )';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryproductbyquerystring($queryString)
    {
        $sql = 'SELECT *
                FROM product
                ' . $queryString . '     ';

        /*  $sql = 'SELECT  product.* ,  `parcode` , COUNT( parcode ) AS ttttt
          FROM product
          ' . $queryString . '
          GROUP BY parcode
          HAVING ttttt >1
          ';
         */
        //	print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function queryproductdeletedbyquerystring($queryString)
    {
        $sql = 'SELECT *
                FROM product where conditions = 1
                ' . $queryString . '     ';

        /*  $sql = 'SELECT  product.* ,  `parcode` , COUNT( parcode ) AS ttttt
          FROM product
          ' . $queryString . '
          GROUP BY parcode
          HAVING ttttt >1
          ';
         */
        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function updateDailyEntry($rid, $productId)
    {
        $sql = 'UPDATE product SET dailyentryId =' . $rid . ' WHERE productId =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function getCatProducts($catid)
    {
        $sql = 'SELECT if(sizecolorstoredetail.id is null ,product.productId,concat("hasSizeColor",product.productId,"-",sizecolorstoredetail.sizeid,"-",sizecolorstoredetail.colorid)) as productId
                ,CONCAT_WS("/",product.productName,size.name,color.name) as productName,product.isOptic,product.conditions

                FROM product


                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

                where productCatId = ' . $catid . '
                and product.conditions = 0

                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid


                union

                SELECT product.productId,CONCAT_WS("/",product.productName,productcat.productCatName) as productName,product.isOptic,product.conditions
                FROM productcat
                join product on product.productCatId = productcat.productCatId
                where productCatParent = ' . $catid . '
                and product.isOptic = 2 and product.conditions = 0'; /* SELECT product.productCatId,product.productCatName,product.isOptic,product.conditions
          FROM productcat as product */
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getSizeColor($catid)
    {
        $sql = 'SELECT product.productId,product.productName,product.isOptic,product.conditions
                FROM product
                where productCatId = ' . $catid . '
                and product.isOptic = 2';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getSizeColor2($catid, $storeid)
    {
        $sql = 'SELECT product.productId,product.productName,product.isOptic,product.conditions
                FROM product
                join storedetail
		on storedetail.productid=product.productId
                where productCatId = ' . $catid . '
                AND  storedetail.storeid=' . $storeid . '
                and product.isOptic = 2';
        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /*
      public function queryAllrepateparcode($end) {
      $sql = 'SELECT  product.* ,  `parcode` , COUNT( parcode ) AS ttttt
      FROM product
      GROUP BY parcode
      HAVING ttttt >1
      limit 0,' . $end . '';

      print_r('<br>'.$sql.'<br>');

      $sqlQuery = new SqlQuery($sql);
      return $this->getList($sqlQuery);
      }
     */

    public function queryByProductCatIdIn($IDS, $queryString = "")
    {
        $sql = 'SELECT * FROM product WHERE productCatId in (' . $IDS . ') ' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql . "<br/>");
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdInNotService($IDS)
    {
        $sql = 'SELECT * FROM product WHERE productCatId in (' . $IDS . ') and isService = 0';
        $sqlQuery = new SqlQuery($sql);
        //         print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdNotService($value)
    {
        $sql = 'SELECT * FROM product WHERE productCatId = ? and isService = 0';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdWithCount($value)
    {
        $sql = 'SELECT product.*,
        SUM(storedetail.productquantity) AS sumProductQuantity
        FROM product
        JOIN storedetail
        ON storedetail.productid = product.productId WHERE productCatId = ' . $value . ' and product.conditions = 0 group by storedetail.productid';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadProductCatNameOnly($id)
    {
        $sql = 'SELECT productcat.productCatName
        FROM product JOIN productcat
        ON product.productCatId = productcat.productCatId
        WHERE product.productId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryByProductNameEX($value, $catid)
    {
        $sql = 'SELECT *
            FROM product
            WHERE productName = ?
            and productCatId=' . $catid;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function getIdsOfProductsLike($name)
    {
        $sql = 'SELECT product.productId
		FROM product
		WHERE
		product.productName LIKE "%' . $name . '%"
		AND product.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByIdsInExt($productsIds, $storeid)
    {
        $sql = 'SELECT product.*, productcat.productCatName ,productquantity
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		join storedetail
		on storedetail.productid=product.productId
		WHERE product.productId in(' . $productsIds . ')
		and storedetail.storeid=' . $storeid . '
		AND product.conditions = 0
		and productcat.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function getPriceOfOptic($catid)
    {
        $sql = 'SELECT product.productSellAllPrice,product.productSellHalfPrice,product.productSellUnitPrice , product.productBuyPrice
                ,product.lastbuyprice,product.meanbuyprice,product.lastbuyprice_withDiscount,product.meanbuyprice_withDiscount,product.overAllAveragePrice
		FROM product
		WHERE product.productCatId =' . $catid . '
		AND product.conditions = 0
		limit 1';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryByParcodeEX($value)
    {
        $sql = 'SELECT product.*,concat(product.productName, "/",productcat.productCatName) as productName,product.productDescription
                FROM product
                join productcat on productcat.productCatId = product.productCatId
                WHERE parcode = "' . $value . '"';
        $sqlQuery = new SqlQuery($sql);
        //echo $sql;
        return $this->getList($sqlQuery);
    }

    public function loadExtNew($id)
    {
        $sql = 'SELECT product.*,
                SUM(storedetail.productquantity) AS sumProductQuantity
                FROM product
		JOIN storedetail
		ON storedetail.productid = product.productId
		WHERE product.productId = ?
                and storedetail.unittype = 0
                group by storedetail.productid
               ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        // print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function queryByNameExtWithoutLimitdeleted_2($name, $queryString)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
		WHERE
		CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
                ' . $queryString . '
		AND product.conditions = 1';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByCatInMenuEX($catId)
    {
        $sql = 'SELECT *
                FROM product
                where product.productCatId = ' . $catId . ' and inMenu = 0 and product.conditions = 0 ORDER BY product.productCatId, product.sortby ASC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdLimited($productCatId)
    {
        $sql = 'SELECT product.productId
		FROM product
		WHERE productCatId = ' . $productCatId . ' limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function isParcodeRepated($value, $query)
    {
        $sql = 'SELECT productId FROM product WHERE parcode = ? ' . $query;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryAllDistinctWithQueryString($queryString)
    {
        $sql = 'SELECT product.*
                FROM product
		where product.conditions = 0 ' . $queryString; //DISTINCT
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllDistinctWithQueryStringUnion($queryString1, $queryString2)
    {
        $sql = '(
                    SELECT product.*,0 as sizeid,0 as colorid,"" as sizeName,"" as colorName,0 as sizeColorParcode
                    FROM product
                    where product.conditions = 0 ' . $queryString1 . '
                )
                union
                (
                    SELECT product.*,sizeid,colorid,size.name as sizeName,color.name as colorName,sizecolorstoredetail.parcode as sizeColorParcode
                    FROM product
                    join sizecolorstoredetail on product.productId = sizecolorstoredetail.productid
                    left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                    left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                    where product.conditions = 0 ' . $queryString2 . '
                )
                '; //DISTINCT
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getProductIdByParcode($parcodes)
    {
        $sql = "SELECT productId,parcode FROM product where parcode in ($parcodes)";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getParcodeByProductIds($productIds)
    {
        $sql = "SELECT productId,parcode FROM product where productId in ($productIds)";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //azmy
    public function loadByidandstoreid($id, $querystore)
    {
        $sql = 'SELECT * ,productcat.productCatName,sum(productquantity) as productquantity FROM product
        JOIN productcat ON product.productCatId = productcat.productCatId
        join storedetail on product.productId = storedetail.productid WHERE product.productId = ' . $id . ' ' . $querystore;
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryByProductCatIdIncludingOptics($value)
    {
        $sql = 'SELECT * FROM product WHERE productCatId = ' . $value . '
                union
                SELECT product.*
                FROM product
                join productcat opticProduct on product.productCatId = opticProduct.productCatId
                where product.isOptic = 2 and product.conditions = 0
                and opticProduct.productCatParent = ' . $value . '';
        //var_dump($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateBulk($sql)
    {
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery, 1);
        return $affected_rows;
    }

    public function updateNullPricesToBuyPrice()
    {
        $sql = 'UPDATE product SET lastbuyprice=productBuyPrice,lastbuyprice_withDiscount=productBuyPrice,meanbuyprice=productBuyPrice,meanbuyprice_withDiscount=productBuyPrice where lastbuyprice is null';
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery, 1);
        return $affected_rows;
    }

    public function queryByNameExtWithoutLimitParcode2($name)
    {
        $sql = 'SELECT product.*, productcat.productCatName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId

		WHERE
		CONCAT_WS("/",product.parcode,productcat.productCatName) LIKE "%' . $name . '%"
		AND product.conditions = 0
		and productcat.conditions = 0
                ';
        $sqlQuery = new SqlQuery($sql);
        //        print($sql);
        return $this->getList($sqlQuery);
    }

    public function updateReview($productId)
    {
        $sql = 'UPDATE `product` SET `reviewType` = 1 where `productId` = ' . $productId;
        $sqlQuery = new SqlQuery($sql);

        return $this->executeUpdate($sqlQuery);
    }

    public function runSelectQuery($sql)
    {
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadProductbyparcod($id)
    {
        $sql = 'SELECT product.*, productcat.productCatName
				 FROM product JOIN productcat
				 ON  product.productCatId = productcat.productCatId
		WHERE product.productId like "%' . $id . '%"';
        $sqlQuery = new SqlQuery($sql);
        //        $sqlQuery->setNumber($id);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatIdEX2($productCatId, $queryString)
    {
        $sql = 'SELECT product.*,sum(productquantity) as productquantity
                FROM product
                JOIN storedetail ON  storedetail.productid = product.productId
                WHERE productCatId = ' . $productCatId . ' ' . $queryString . '
                group by product.productId';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllSimpleQueryString($queryString)
    {
        $sql = 'SELECT *
		FROM product
		WHERE 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithSizeColorData($queryString = '')
    {
        $sql = 'SELECT product.*, productcat.productCatName,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId
                left JOIN storedetail
		ON storedetail.productid = product.productId
                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
		WHERE 1 ' . $queryString . ' ';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function updateCollectiveProductsPricesAsSumOfIngridientsCost($queryString = '')
    {
        $sql = 'UPDATE product
                JOIN (
                       select productingredients.productId
                              ,sum(productnumber * quantity * productBuyPrice) as productBuyPrice
                              ,sum(productnumber * quantity * lastbuyprice) as lastbuyprice
                              ,sum(productnumber * quantity * lastbuyprice_withDiscount) as lastbuyprice_withDiscount
                              ,sum(productnumber * quantity * meanbuyprice) as meanbuyprice
                              ,sum(productnumber * quantity * meanbuyprice_withDiscount) as meanbuyprice_withDiscount
                              ,sum(productnumber * quantity * overAllAveragePrice) as overAllAveragePrice
                      from product
                      join productingredients on product.productId = productingredients.ingridientId
                      join productunit on productunit.productunitid = productingredients.unitId
                      group by productingredients.productId
                    ) as ingridientPrice ON ingridientPrice.productId =  product.productId
                SET product.productBuyPrice = ingridientPrice.productBuyPrice,
                        product.lastbuyprice = ingridientPrice.lastbuyprice,
                    product.lastbuyprice_withDiscount = ingridientPrice.lastbuyprice_withDiscount,
                    product.meanbuyprice = ingridientPrice.meanbuyprice,
                    product.meanbuyprice_withDiscount = ingridientPrice.meanbuyprice_withDiscount,
                    product.overAllAveragePrice = ingridientPrice.overAllAveragePrice';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function get3productsOne($startDate, $endDate)
    {
        $sql = 'SELECT product.productId,product.productName , storereport.storereportid, storereport.productid, storereport.storereporttype, storereport.productbefore, storereport.productafter
                    from product
                    join storereport on product.productId = storereport.productid where
                    storereport.productid IN (SELECT product_id FROM resturantcategory WHERE id IN (1,2,3))
                    and storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
                   ORDER BY `storereport`.`storereportid` DESC limit 3';

        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadSizeColor($productId, $sizeId = 0, $colorId = 0)
    {
        $sizeId = (int) $sizeId;
        $colorId = (int) $colorId;
        $productId = (int) $productId;
        $sql = 'SELECT product.*,size.name as sizeName,color.name as colorName,size.id as sizeid,color.id as colorid
                FROM product
                left join sizecolor as size on size.id=' . $sizeId . '
                left join sizecolor as color on color.id=' . $colorId . '
                WHERE product.productId = ' . $productId;
        //var_dump($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function updateQueryString($sql)
    {
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByNameExtWithoutStoreWithoutLimit($name, $queryString = '', $limitString = '', $controlNameSearch = 1)
    {
        $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name,product.procode) LIKE "%' . $name . '%" ';
        if ($controlNameSearch === 0) {
            $queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,product.procode) LIKE "%' . $name . '%" ';
        }
        $sql = 'SELECT SQL_CACHE product.*, productcat.productCatName ,if(sizecolorstoredetail.id is null ,sum(productquantity),quantity) as productquantity
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName,product.weightedDiscount,productcat.buytotal,productcat.buyhalf,productcat.buypart,productBuyPrice
		FROM product
		JOIN productcat
		ON product.productCatId = productcat.productCatId

		left join storedetail
		on storedetail.productid=product.productId

                left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid

		WHERE
                ' . $queryStringNameSearch . '

		AND product.conditions = 0
		and productcat.conditions = 0
                ' . $queryString . '
                GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ' . $limitString;
        $sqlQuery = new SqlQuery($sql);
        //    print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameExtHasColorAndSize($name, $limitQS = '')
    {
        $sql = 'SELECT product.productId,product.productCatId,product.productName,productcat.productCatName
                    FROM product
                    JOIN productcat
                    ON product.productCatId = productcat.productCatId
                    WHERE
                    CONCAT(product.productName, "/",  productcat.productCatName) LIKE "%' . $name . '%"
                    AND product.conditions = 0
                    and productcat.conditions = 0
                and product.hasSizeAndColor = 1' . $limitQS;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }
}
