<?php

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

    public function queryProductWithStoreId($storeId) {
        $sql = 'SELECT storereport. * , product.productName
				FROM  storereport
				JOIN product ON storereport.productid = product.productId
				WHERE storeid = "' . $storeId . '"
				AND tablename =  "sellbillController.php"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductId($productid) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId

			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.productid = ' . $productid . '
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithStoreId($storeid) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.storeid = ' . $storeid . '
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDate($startDate, $endDate) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductAndStoreAndDate($productid, $storeid, $startDate, $endDate) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName,  product.productCatId
			FROM storereport JOIN product
			ON  storereport.productid = product.productId JOIN store
			ON  storereport.storeid = store.storeId
			where product.conditions = 0 and store.conditions = 0
			and  storereport.productid = ' . $productid . '
			and  storereport.storeid = ' . $storeid . '
			and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductAndStoreAndDateandorder($productid, $storeid, $startDate, $endDate, $order, $queryString = '') {
        $sql = 'SELECT  CONCAT(  `tablename` ,  `storereportmodelid` , productquantity,`storereporttype` ) AS finalstring,storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName,  product.productCatId
                        ,size.name as sizeName,color.name as colorName
			FROM storereport JOIN product
			ON  storereport.productid = product.productId JOIN store
			ON  storereport.storeid = store.storeId

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

			where product.conditions = 0 and store.conditions = 0
			and  storereport.productid = ' . $productid . '
                            ' . $queryString . '
			and  storereport.storeid = ' . $storeid . '
			and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			order by storereportid ' . $order . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductAndStoreAndDateandorderbure($productid, $storeid, $startDate, $endDate, $order, $queryString = '') {
        $sql = 'SELECT  CONCAT(  `tablename` ,  `storereportmodelid` , productquantity,`storereporttype` ) AS finalstring,storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName,  product.productCatId
                        ,size.name as sizeName,color.name as colorName
			FROM storereport JOIN product
			ON  storereport.productid = product.productId JOIN store
			ON  storereport.storeid = store.storeId

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

			where product.conditions = 0 and store.conditions = 0
			and  storereport.productid = ' . $productid . '
                            ' . $queryString . '
			and  storereport.storeid = ' . $storeid . '
			and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			order by storereportdate ' . $order . ',  storereportid ' . $order . '';


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

    public function getBalanceBefore($startDate, $productid, $storeid) {

        $sql = 'SELECT  storereport.*, product.productName,  product.productCatId
			FROM storereport JOIN product
			ON  storereport.productid = product.productId JOIN store
			ON  storereport.storeid = store.storeId
			where product.conditions = 0 and store.conditions = 0
			and  storereport.productid = ' . $productid . '
			and  storereport.storeid = ' . $storeid . '
			and  storereport.storereportdate < "' . $startDate . '"
			 ORDER BY `storereport`.`storereportid` DESC';

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

    public function queryWithOrder() {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getLastQuantityBefore($productId, $storeId) {
        $sql = 'SELECT productquantity
			FROM storereport
			WHERE productid = ' . $productId . '
			AND storeid = ' . $storeId . '
			ORDER BY storereportid DESC
			limit 1';
        $sqlQuery = new SqlQuery($sql);
        print($sql);
        return $this->querySingleResult($sqlQuery);
    }

    /////////////////////تسوية عجز مخزن////////////////
    public function queryWithModelid() {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithModelidLimited($start, $end) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithStoreIdAndModelId($storeid) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName  ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
		 on
		 productcat.productCatId = product.productCatId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.storeid = ' . $storeid . '
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithStoreIdAndModelIdLimited($start, $end, $storeid) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.storeid = ' . $storeid . '
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductIdAndModelId($productid) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName  ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			   join productcat
				 on
				 productcat.productCatId = product.productCatId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.productid = ' . $productid . '
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductIdAndModelIdLimited($start, $end, $productid) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
			 and  storereport.productid = ' . $productid . '
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }


     public function queryAllData($queryString = '') {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName  ,productcat.productCatName, employeename as userid
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat on productcat.productCatId = product.productCatId
			  join user on user.userid = storereport.userid
			 where product.conditions = 0 and store.conditions = 0
	 		 '.$queryString .'
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }


    public function queryWithDateAndModelId($startDate, $endDate) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName  ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			 where product.conditions = 0 and store.conditions = 0
	 		 and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndModelIdLimited($start, $end, $startDate, $endDate) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			 where product.conditions = 0 and store.conditions = 0
	 		 and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			 and  storereport.storereportmodelid = -1
			 order by storereportid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

/////////////////////////////////////////////////////////////////
//////////////////////////////  Product Report ////////////////////


    public function queryWithProductAndStoreAndDateExt($productid, $storeid, $startDate, $endDate) {
        $sql = 'SELECT storereport.*, product.productName,
			product.productSellUnitPrice, productcat.productCatName, store.storeName
			FROM storereport
			JOIN product
			ON  storereport.productid = product.productId
			JOIN productcat
			ON productcat.productCatId = product.productCatId
			JOIN store
			ON  storereport.storeid = store.storeId
			where product.conditions = 0 and store.conditions = 0
			and  storereport.productid = ' . $productid . '
			and  storereport.storeid = ' . $storeid . '
			and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
			order by storereportid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getProductQuantityBeforeConditions($productid, $modelid, $tablename) {
        $sql = "SELECT productbefore
                FROM storereport
                WHERE storereportmodelid = $modelid and tablename= '" . $tablename . "'
                and productid= $productid
                order by `storereportid` asc"; //and storeid = $storeid i am using it for buy bill which store is fixed for all products... no need for the condition
        // -- and conditions = 0
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

//////////////////////////////////////////////////////////////////


    public function getLastInventoryDateByProductIdAndStoreId($productId, $storeId, $queryString = '') {
        $sql = 'SELECT storereport.storereportdate
			FROM storereport
			where storereport.productid = ' . $productId . '
			AND storereport.storeid = ' . $storeId . '
			AND storereport.tablename = "inventoryController.php"
                        ' . $queryString . '
			order by storereportid desc
			limit 1';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getlatestInventoryProducts($productId, $date, $today) {
        $sql = 'SELECT storereport.storereportdate
			FROM storereport
			where storereport.productid = ' . $productId . '
			AND storereport.tablename = "inventoryController.php"
			AND storereport.storereportdate >= "' . $date . '" AND storereport.storereportdate <= "' . $today . '"';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAlllastdeal($startDate, $productId, $storeid) {
        $sql = 'SELECT * FROM storereport where

		productid=' . $productId . ' and storereportdate < "' . $startDate . '"
		and storeid=' . $storeid . '
		order by storereportdate desc ,storereportid desc
		limit 1';

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

    public function queryAlllastdealNew($queryString) {
        $sql = 'SELECT * FROM storereport where 1
		' . $queryString . '
		order by storereportid asc
		limit 1';

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

    public function loadWithtablenamexandtype($catid, $type, $tablename1, $tablename, $tablename2) {
        $sql = 'SELECT sum(storereport.productquantity) as productquantity,storereport.storereporttype,
			 product.productName,
			 store.storeName ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			where storereport.productid =' . $catid . '
			and storereport.storereporttype =' . $type . '

			and product.conditions = 0 and store.conditions = 0
			AND(storereport.tablename = "' . $tablename1 . '" OR storereport.tablename = "' . $tablename . '"
			OR storereport.tablename = "' . $tablename2 . '")

			group by storereport.productid


			';

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

    public function queryWithtablenamex($catid, $startDate, $endDate, $tablename, $tablename1, $tablename2) {
        $sql = 'SELECT storereport.productid,
			 product.productName,product.productCatId,storereport.productquantity,
			 store.storeName ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			where product.productCatId =' . $catid . '
			and storereport.storereportdate >= "' . $startDate . '" AND storereport.storereportdate <= "' . $endDate . '"
			and (storereport.tablename = "' . $tablename1 . '" OR storereport.tablename = "' . $tablename . '"
			OR storereport.tablename = "' . $tablename2 . '")
			and product.conditions = 0 and store.conditions = 0
			group by storereport.productid

			';

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

    public function getlatestInventoryProductsWithStoreId($productId, $storeId, $date, $today, $queryString = '') {
        $sql = 'SELECT storereport.storereportdate
			FROM storereport
			where storereport.productid = ' . $productId . '
			AND storereport.tablename = "inventoryController.php"
			AND storereport.storeid = ' . $storeId . '
			AND storereport.storereportdate >= "' . $date . '" AND storereport.storereportdate <= "' . $today . '"
                        ' . $queryString . ' ';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllqueryString($queryString) {
        $sql = 'SELECT storereport.*, product.productName FROM storereport
		        JOIN product
	           ON  storereport.productid = product.productId   ' . $queryString . ' order by storereportdate asc ,storereportid asc  ';


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

    public function queryWithtablenameforcatrate($catid, $startDate, $endDate, $tablename, $tablename1, $tablename2, $tablename3, $tablename4) {
        $sql = 'SELECT storereport.productid,
			 product.productName,product.productCatId,storereport.productquantity,
			 store.storeName ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			where product.productCatId =' . $catid . '
			and storereport.storereportdate >= "' . $startDate . '" AND storereport.storereportdate <= "' . $endDate . '"
			and (storereport.tablename = "' . $tablename1 . '" OR storereport.tablename = "' . $tablename . '"
			OR storereport.tablename = "' . $tablename2 . '" OR storereport.tablename = "' . $tablename3 . '" OR storereport.tablename = "' . $tablename4 . '")
			and product.conditions = 0 and store.conditions = 0
			group by storereport.productid

			';


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

    public function loadWithtablenamexandtypeforcatrate($catid, $type, $tablename1, $tablename, $tablename2, $tablename3, $tablename4) {
        $sql = 'SELECT sum(storereport.productquantity) as productquantity,storereport.storereporttype,
			 product.productName,
			 store.storeName ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			where storereport.productid =' . $catid . '
			and storereport.storereporttype =' . $type . '

			and product.conditions = 0 and store.conditions = 0
			AND(storereport.tablename = "' . $tablename1 . '" OR storereport.tablename = "' . $tablename . '"
			OR storereport.tablename = "' . $tablename2 . '" OR storereport.tablename = "' . $tablename3 . '" OR storereport.tablename = "' . $tablename4 . '")

			group by storereport.productid


			';

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

    public function queryAllStoreReportTypeEqualOne($queryString) {
        $sql = 'SELECT sum(storereport.productquantity) as productquantity FROM storereport
		        JOIN product
	           ON  storereport.productid = product.productId   AND storereport.storereporttype = 1  ' . $queryString . ' group by storereport.productid order by storereportdate asc ,storereportid asc  ';


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

    public function queryAllStoreReportTypeEqualZero($queryString) {
        $sql = 'SELECT sum(storereport.productquantity) as productquantity FROM storereport
		        JOIN product
	           ON  storereport.productid = product.productId   AND storereport.storereporttype = 0  ' . $queryString . ' group by storereport.productid order by storereportdate asc ,storereportid asc  ';


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

    public function getQuantityOfFirstPeriod($queryString) {
        $sql = 'SELECT storereport.*, product.productName FROM storereport
		        JOIN product
	           ON  storereport.productid = product.productId   ' . $queryString . ' order by storereportdate asc ,storereportid asc limit 1  ';


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

    //# m7md 25/11/2017
    public function selectPlusValues($productId, $storeId, $startDate, $endDate) {

        $sql = 'SELECT sum(`productquantity`)  as productquantity

                FROM `storereport`

                WHERE storereporttype = 1 and `productid` = ' . $productId . ' and `storeid` = ' . $storeId . ' and `storereportdate` >= "' . $startDate . '" and `storereportdate` <= "' . $endDate . '"

                group by productid ';

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

    //# m7md  25/11/2017
    public function selectMinsValues($productId, $storeId, $startDate, $endDate) {

        $sql = 'SELECT sum(`productquantity`)  as productquantity

                FROM `storereport`

                WHERE storereporttype = 0 and `productid` = ' . $productId . ' and `storeid` = ' . $storeId . ' and `storereportdate` >= "' . $startDate . '" and `storereportdate` <= "' . $endDate . '"

                group by productid ';

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

    public function insertEX($storereport) {
        $sql = 'INSERT INTO storereport (productid, storeid, productquantity, storereporttype, storereportmodelid, processname, tablename, productbefore, productafter, userid, storereportdate,unittype) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($storereport->productid);
        $sqlQuery->setNumber($storereport->storeid);
        $sqlQuery->setNumber($storereport->productquantity);
        $sqlQuery->setNumber($storereport->storereporttype);
        $sqlQuery->setNumber($storereport->storereportmodelid);
        $sqlQuery->set($storereport->processname);
        $sqlQuery->set($storereport->tablename);
        $sqlQuery->setNumber($storereport->productbefore);
        $sqlQuery->setNumber($storereport->productafter);
        $sqlQuery->setNumber($storereport->userid);
        $sqlQuery->set($storereport->storereportdate);
        $sqlQuery->setNumber($storereport->unittype);


        // print_r("<pre>");
        //print_r($storereport);
        // print_r("<pre>");
        $id = $this->executeInsert($sqlQuery);
        $storereport->storereportid = $id;
        return $id;
    }

    public function queryWithProductAndStoreAndDateandorderMo($productid, $query) {
        $sql = 'SELECT  CONCAT(  `tablename` ,  `storereportmodelid` , productquantity,`storereporttype` ) AS finalstring,storereport.*, product.productName,
			product.productSellUnitPrice,  store.storeName,  product.productCatId
			FROM storereport
                        JOIN product ON  storereport.productid = product.productId
                        JOIN store ON  storereport.storeid = store.storeId
			where product.conditions = 0 and store.conditions = 0
			and  storereport.productid = ' . $productid . '
			' . $query . '';


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

    public function getStoreDeficit($productId, $storeId, $fromDate, $toDate) {
        $sql = 'SELECT storereport.*
			FROM storereport
			where storereport.productid = ' . $productId . '
			AND (storereport.tablename = "inventoryController.php" or storereport.tablename = "settlementstoreController.php")
			AND storereport.storeid = ' . $storeId . '
			AND storereport.storereportdate >= "' . $fromDate . '"
                        AND storereport.storereportdate <= "' . $toDate . '" ';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

	public function loadWithtablenamexandtypeforcatrateWithDate($catid, $type, $tablename1, $tablename, $tablename2, $tablename3, $tablename4,$startDate,$endDate) {
        $sql = 'SELECT sum(storereport.productquantity) as productquantity,storereport.storereporttype,
			 product.productName,
			 store.storeName ,productcat.productCatName
			FROM storereport JOIN product
			 ON  storereport.productid = product.productId JOIN store
			 ON  storereport.storeid = store.storeId
			  join productcat
				 on
				 productcat.productCatId = product.productCatId
			where storereport.productid =' . $catid . '
			and storereport.storereporttype =' . $type . '

			and product.conditions = 0 and store.conditions = 0
			and `storereportdate` >= "' . $startDate . '" and `storereportdate` <= "' . $endDate . '"
			AND(storereport.tablename = "' . $tablename1 . '" OR storereport.tablename = "' . $tablename . '"
			OR storereport.tablename = "' . $tablename2 . '" OR storereport.tablename = "' . $tablename3 . '" OR storereport.tablename = "' . $tablename4 . '")
			group by storereport.productid';

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

}
