<?php

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

    public function queryAllnothaveunit()
    {
        $sql = 'SELECT productCatId FROM productcat where
                                            productCatId not in(SELECT DISTINCT (
			`productcatid`
			)
			FROM  `productcatunit`  ) ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getProductCatParents()
    {
        $sql = 'SELECT *
		FROM productcat
		WHERE productCatParent = 0
		AND conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getCategoriesWithoutProducts()
    {
        $sql = 'SELECT productcat.*
		FROM productcat
		WHERE productCatId NOT IN (SELECT productCatId FROM  product where conditions=0)
		AND productcat.conditions = 0
                AND productcat.isOptic !=2';


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

    public function getCategoryAndParentByCatId($catid)
    {
        $sql = 'SELECT child.*, parent.productCatName as parentName
		FROM productcat as child
				left join productcat as parent
				on child.`productCatParent` = parent.productCatId
				WHERE child.productCatId = ?';
        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($catid);

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

    public function getChilds($parentid, $serviceQS = '')
    {
        $sql = 'SELECT *
		FROM productcat
		WHERE productCatParent = ?
                and isOptic != 2 ' . $serviceQS . ' and productcat.conditions = 0';
        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($parentid);

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

    public function queryByParentExt($productCatId, $queryString = '')
    {
        $sql = 'SELECT *
		FROM productcat
		WHERE productCatParent = ' . $productCatId . '
		AND conditions = 0 ' . $queryString . ' ';
        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function queryByParentExt2($productCatId)
    {
        $sql = 'SELECT *
		FROM productcat
		WHERE productCatParent = ?
                AND conditions !=2';
        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($productCatId);
        return $this->getList($sqlQuery);
    }

    public function queryByConditionLimited($value, $start, $end)
    {
        $sql = 'SELECT * FROM productcat WHERE conditions = ?
		ORDER BY productCatId DESC  limit ' . $start . ', ' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryAllLimited($start, $end)
    {
        $sql = 'SELECT * FROM productcat ORDER BY productCatId DESC  limit ' . $start . ', ' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllForShow()
    {
        $sql = 'SELECT child.*, parent.productCatName as parentName
                FROM productcat child
                left join productcat parent on child.`productCatParent` = parent.productCatId
                where child.isOptic != 2 ORDER BY child.productCatId DESC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateConditions($productcat)
    {
        $sql = 'UPDATE productcat SET  productCatDate = ?, userId = ?, conditions = ? WHERE productCatId = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($productcat->productCatDate);
        $sqlQuery->setNumber($productcat->userId);
        $sqlQuery->setNumber($productcat->conditions);

        $sqlQuery->setNumber($productcat->productCatId);
        return $this->executeUpdate($sqlQuery);
    }

    public function returndelete($productCatid)
    {
        $sql = 'UPDATE productcat SET conditions = 0 WHERE productCatId = ?';
        $sqlQuery = new SqlQuery($sql);

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

    public function updateExcelInfo($catExcelid, $productCatid)
    {
        $sql = 'UPDATE productcat SET catExcelid = ' . $catExcelid . ' WHERE productCatId = ' . $productCatid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    //query all product categories that have no child categories
    public function queryAllChildren()
    {
        $sql = "SELECT DISTINCT(a.productCatId) , a.*
		 FROM productcat a, productcat b
		 WHERE a.conditions = 0
		 AND a.productCatId <> b.productCatParent
		 AND b.conditions = 0
		 AND a.productCatParent <> 0
		 ORDER BY a.productCatId DESC ";


        $sqlQuery = new SqlQuery($sql);

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

    //query all product categories that have no child categories
    public function queryAllnothavechildren($queryString = '')
    {
        $sql = "SELECT *
			FROM  `productcat`
			WHERE  `productCatId` NOT
			IN (

			SELECT DISTINCT (
			`productCatParent`
			)
			FROM productcat
			)
			and `conditions` =0
                        " . $queryString . "
		 ORDER BY productcat.productCatId DESC ";


        $sqlQuery = new SqlQuery($sql);

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

    public function queryAllChildrenExt2()
    {
        $sql = "SELECT DISTINCT(a.productCatId) , a.*
		 FROM productcat a, productcat b
		 WHERE a.conditions = 0
		 AND a.productCatId <> b.productCatParent
		 AND b.conditions = 0
		 ORDER BY a.productCatId DESC ";
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    //query last product categories in the hierarcy
    public function queryAllChildrenExt()
    {
        $sql = "SELECT DISTINCT(a.productCatId) , a.*
		 FROM productcat a, productcat b
		 WHERE a.conditions=0
		 AND a.productCatId <> b.productCatParent
		 AND b.conditions=0
		 AND a.productCatParent <> 0
         AND a.productCatId NOT IN (
              SELECT productCatParent
			  FROM productcat
			  WHERE conditions = 0
			  AND productCatParent <> 0
           )
		 ORDER BY a.productCatId DESC  ";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //query last product categories in the hierarcy
    public function queryAllChildrenwithparentExt($productCatParent)
    {
        $sql = "SELECT DISTINCT(a.productCatId) , a.*
		 FROM productcat a, productcat b
		 WHERE a.conditions=0
		 AND a.productCatId <> b.productCatParent
		 AND b.conditions=0
		 AND a.productCatParent <> 0
         AND a.productCatId NOT IN (
              SELECT productCatParent
			  FROM productcat
			  WHERE conditions = 0
			  AND productCatParent <> 0
           )
		   and a.productCatParent =" . $productCatParent . "
		 ORDER BY a.productCatId DESC  ";


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

    public function queryWithParentAndCondition($productcatid)
    {
        $sql = 'SELECT * FROM productcat where productCatParent = ' . $productcatid . ' and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryProductcatParentWithCondition()
    {
        $sql = 'SELECT * FROM productcat where productCatParent = 0 and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function queryProductcatInProductmain()
    {
        $sql = 'SELECT * FROM productcat where productcat.productCatParent  = 0
		 and productcat.productCatId in (SELECT DISTINCT (`productCatParent`)
             FROM  `productcat`  )  and productcat.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithNameAndId($productCatName, $productCatId, $limit)
    {
        $sql = 'SELECT * FROM productcat
		where productCatName LIKE "%' . $productCatName . '%"
		and productCatId = ' . $productCatId . '
		and conditions = 0 limit ' . $limit . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadExt($id)
    {
        $sql = 'SELECT child.*, parent.productCatName as parentName
                FROM productcat child
                left join productcat parent on child.`productCatParent` = parent.productCatId
                WHERE child.productCatId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getList($sqlQuery);
    }

    public function queryByParentAndProductcatNameExt($parent, $productCatName)
    {
        $sql = 'SELECT *
		FROM productcat
		WHERE productCatParent = ?
		AND productCatName = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($parent);
        $sqlQuery->set($productCatName);
        return $this->getList($sqlQuery);
    }

    public function queryByConditionEX($value)
    {
        $sql = 'SELECT * FROM productcat WHERE conditions = ? and isOptic != 2';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByProductCatNameEX($value, $catid)
    {
        $sql = 'SELECT * FROM productcat WHERE productCatName = ? and productCatParent=' . $catid;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        //print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function getIdsOfCatsLike($productCatName)
    {
        $sql = 'SELECT productcat.productCatId FROM productcat
		where productCatName LIKE "%' . $productCatName . '%"
		and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //# m7md
    public function productcat_prices($catid)
    {
        $sql = 'SELECT buypricereal , buytotal , buyhalf , buypart, price4,price5,price6,price7,price8
		FROM productcat WHERE productCatId = ?';
        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($catid);

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

    public function queryByProductCatParentLimited($value)
    {
        $sql = 'SELECT productcat.productCatId FROM productcat WHERE productCatParent = ? limit 1';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryAllIn($ids)
    {
        $sql = "SELECT productCatId,productCatName FROM productcat WHERE productCatId in($ids)";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getCategoriesCount()
    {
        $sql = 'SELECT count(productCatId) as productCatId
		FROM productcat where isOptic != 2';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllOrderedLimitedForCURL($start, $end, $sqlQuery = '')
    {
        $sql = 'SELECT productcat.*
		FROM productcat where isOptic != 2 ' . $sqlQuery . ' order by productCatId asc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function queryAllForCURLSyncAndLock()
    {
        $sql = 'SELECT productcat.*,onlinetempcategory.edited
		FROM productcat
                join onlinetempcategory on onlinetempcategory.catid = productcat.productCatId
                where isOptic != 2 FOR UPDATE';
        $sqlQuery = new SqlQuery($sql);

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

    public function getExcelInfo($queryString)
    {
        $sql = 'SELECT productcat.productCatId,productcat.catExcelid, productcat.productCatName
		FROM productcat
                ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function get3categoriesOne($startDate, $endDate)
    {
        $sql = 'SELECT productcat.productCatId, productcat.productCatName ,sellbilldetail.sellbilldetailcatid ,sellbilldetail.sellbilldetaildate ,sum(sellbilldetail.sellbilldetailprice) as catExcelid
                    from productcat
                    join sellbilldetail on productcat.productCatId = sellbilldetail.sellbilldetailcatid
                    where sellbilldetail.sellbilldetailcatid IN (SELECT category_id FROM resturantcategory WHERE id IN (1,2,3))
                    and sellbilldetail.sellbilldetaildate >= "' . $startDate . '" and sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
                    group By productcat.productCatId ORDER BY `productcat`.`productCatId` DESC limit 3';

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

    public function queryAllQueryStringSimple($queryString = '', $limit = '')
    {
        $sql = 'SELECT * FROM productcat
		where conditions = 0 ' . $queryString . $limit;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }
}
