<?php

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

    public function queryWithOrder() {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
		 storeto.storeName as storetoName, product.productName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId JOIN product
		 ON storemovement.productid = product.productId
		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and product.conditions = 0 order by transferproductid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithOrderLimited($start, $end) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
		 storeto.storeName as storetoName, product.productName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId JOIN product
		 ON storemovement.productid = product.productId
		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and product.conditions = 0 order by transferproductid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function querybyoperationnum() {
        $sql = 'SELECT storemovement. * , storefrom.storeName AS storefromName, storeto.storeName AS storetoName
				FROM storemovement
				JOIN store storefrom ON storemovement.storeidfrom = storefrom.storeId
				JOIN store storeto ON storemovement.storeidto = storeto.storeId
				WHERE storefrom.conditions =0
				AND storeto.conditions =0
				AND operationnum >0
				GROUP BY  `operationnum` order by transferproductid desc ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallotheroperation($queryString) {
        $sql = 'SELECT storemovement. * , storefrom.storeName AS storefromName, otherstoremovement.oprationnumber,storeto.storeName AS storetoName
				FROM storemovement
				JOIN store storefrom ON storemovement.storeidfrom = storefrom.storeId
				JOIN store storeto ON storemovement.storeidto = storeto.storeId
				join otherstoremovement on otherstoremovement.id= storemovement.operationnum
				WHERE storefrom.conditions =0
				AND storeto.conditions =0
				AND operationnum > 0
				AND otherstoremovement.del=0
				' . $queryString . '
				GROUP BY  storemovement.`operationnum` order by transferproductid desc ';



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

    public function querybyoperationnumbydateold($startdate, $enddate) {
        $sql = 'SELECT storemovement. * , storefrom.storeName AS storefromName, storeto.storeName AS storetoName
				FROM storemovement
				JOIN store storefrom ON storemovement.storeidfrom = storefrom.storeId
				JOIN store storeto ON storemovement.storeidto = storeto.storeId
				WHERE storefrom.conditions =0
				AND storeto.conditions =0
				AND operationnum >0
				and storemovement.operationnum not in (SELECT DISTINCT (`deloperationnum`)  FROM  `storemovement`)
				 and transferproductdate >= "' . $startdate . '" and transferproductdate <= "' . $enddate . '"
				GROUP BY  `operationnum` order by transferproductid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryDISTINCTdel() {
        $sql = 'SELECT DISTINCT (`deloperationnum`)  FROM  `storemovement`';


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

    public function querybyoperationnumsearch($operationnum, $type) {
        $sql = 'SELECT *  FROM  `storemovement`
							 WHERE  ' . $type . ' = "' . $operationnum . '"
							GROUP BY  `operationnum` ';

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

    public function querybyoperationnumsearch3($operationnum, $type, $queryString = "", $storetype) {
        $sql = 'SELECT DISTINCT storemovement.operationnum , storemovement.transferproductdate , storemovement.conditions
                 , storefrom.storeName as storefromName,storeto.storeName as storetoName,user.employeename as userPrepared,isPrepared,storemovement.dailyentryid
                 ,recieveType,recieveUserid, u.username employeename
		 FROM storemovement
		  JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		  JOIN product
		 ON storemovement.productid = product.productId
		 join productcat
		 on
		 productcat.productCatId = product.productCatId
		 left join user on  user.userid = storemovement.useridPrepared
         left join user u on u.userid = storemovement.userid
			 WHERE  ' . $type . ' = "' . $operationnum . '"
			 and  storefrom.conditions = 0 and storeto.conditions = 0
                         ' . $queryString . '
		 and product.conditions = 0 and storetype = ' . $storetype . '

		 order by transferproductid desc

							 ';

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

    public function querybyoperationnumsearch2($operationnum, $type, $queryString = "", $storetype) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
		 storeto.storeName as storetoName,CONCAT_WS("/",product.productName,size.name,color.name) as productName,productcat.productCatName,user.employeename as comment
		 FROM storemovement
		  JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		  JOIN product
		 ON storemovement.productid = product.productId
		 join productcat
		 on
		 productcat.productCatId = product.productCatId
                JOIN  user
		 ON user.userid = storemovement.userid

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

			 WHERE  ' . $type . ' = "' . $operationnum . '"
			 and  storefrom.conditions = 0 and storeto.conditions = 0
                         ' . $queryString . '
		 and product.conditions = 0 and storetype = ' . $storetype . '

                GROUP BY storemovement.operationnum,product.productId,storemovement.sizeid,storemovement.colorid

		 order by transferproductid desc

							 ';

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

    public function queryWithDate($startDate, $endDate, $queryString = "", $storetype, $storeQuery = '') {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
		 storeto.storeName as storetoName,CONCAT_WS("/",product.productName,size.name,color.name) as productName,productcat.productCatName,user.employeename as comment
		 FROM storemovement
		  JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		  JOIN product
		 ON storemovement.productid = product.productId
		 join productcat
		 on
		 productcat.productCatId = product.productCatId
                 JOIN  user
		 ON user.userid = storemovement.userid

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

		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and product.conditions = 0
		 and transferproductdate >= "' . $startDate . '" and transferproductdate <= "' . $endDate . '"
                     ' . $queryString . ' and storetype = ' . $storetype . '
                         ' . $storeQuery . '
                 GROUP BY storemovement.operationnum,product.productId,storemovement.sizeid,storemovement.colorid

		 order by transferproductid desc ';


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

    public function queryWithDate2($startDate, $endDate, $queryString = "", $storetype, $storeQuery) {
        $sql = 'SELECT DISTINCT storemovement.operationnum , storemovement.transferproductdate , storemovement.conditions
            , storefrom.storeName as storefromName,storeto.storeName as storetoName,user.employeename as userPrepared,isPrepared,storemovement.dailyentryid
            ,recieveType,recieveUserid, u.username employeename
		FROM storemovement
                 JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
                 left join user on  user.userid = storemovement.useridPrepared
         left join user u on u.userid = storemovement.userid
		where transferproductdate >=  "' . $startDate . '" and transferproductdate <= "' . $endDate . '"
                     ' . $queryString . ' and storetype = ' . $storetype . '
                         ' . $storeQuery . '
		 order by transferproductid desc ';


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

    public function queryWithstoreTo($storeidto, $queryString = "", $storetype) {
        $sql = 'SELECT DISTINCT storemovement.operationnum , storemovement.transferproductdate , storemovement.conditions
            , storefrom.storeName as storefromName,storeto.storeName as storetoName
		FROM storemovement
                 JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		where storemovement.storeidto = ' . $storeidto . ' and storetype = ' . $storetype . '
		 order by transferproductid desc ';


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

    public function queryWithstoreFrom($storeidfrom, $queryString = "", $storetype) {
        $sql = 'SELECT DISTINCT storemovement.operationnum , storemovement.transferproductdate , storemovement.conditions
            , storefrom.storeName as storefromName,storeto.storeName as storetoName
		FROM storemovement
                 JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		where storemovement.storeidfrom = ' . $storeidfrom . ' and storetype = ' . $storetype . '
		 order by transferproductid desc ';


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

    public function queryWithstoreTo2($storeidto, $startDate, $endDate, $queryString = "", $storetype) {
        $sql = 'SELECT DISTINCT storemovement.operationnum , storemovement.transferproductdate , storemovement.conditions
            , storefrom.storeName as storefromName,storeto.storeName as storetoName
		FROM storemovement
                 JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		where transferproductdate >=  "' . $startDate . '" and transferproductdate <= "' . $endDate . '"
                     ' . $queryString . ' and  storemovement.storeidto = ' . $storeidto . ' and storetype = ' . $storetype . '
		 order by transferproductid desc ';


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

    public function queryWithstoreFrom2($storeidfrom, $startDate, $endDate, $queryString = "", $storetype) {
        $sql = 'SELECT DISTINCT storemovement.operationnum , storemovement.transferproductdate , storemovement.conditions
            , storefrom.storeName as storefromName,storeto.storeName as storetoName
		FROM storemovement
                 JOIN  store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		  JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		where transferproductdate >= "' . $startDate . '" and transferproductdate <= "' . $endDate . '"
                     ' . $queryString . ' and  storemovement.storeidfrom = ' . $storeidfrom . ' and storetype = ' . $storetype . '
		 order by transferproductid desc ';


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

    public function queryWithDateLimited($start, $end, $startDate, $endDate) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
		 storeto.storeName as storetoName, product.productName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId JOIN product
		 ON storemovement.productid = product.productId
		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and product.conditions = 0
		 and transferproductdate >= "' . $startDate . '" and transferproductdate <= "' . $endDate . '"
		 order by transferproductid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadStoremovementById($transferproductid) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName, storemovement.conditions as conditions,
		 storeto.storeName as storetoName,CONCAT_WS("/",product.productName,size.name,color.name) as productName,product.parcode as parcode
		 FROM storemovement
                 JOIN store storefrom ON storemovement.storeidfrom = storefrom.storeId
                 JOIN store storeto ON storemovement.storeidto = storeto.storeId
                 JOIN product ON storemovement.productid = product.productId

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

		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and storemovement.transferproductid =' . $transferproductid . '
		 and product.conditions = 0
                 GROUP BY storemovement.operationnum,product.productId,storemovement.sizeid,storemovement.colorid';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryStoremovementByoperationnum($operationnum, $conditions) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
                if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity,
		 storeto.storeName as storetoName,CONCAT_WS("/",product.productName,size.name,color.name) as productName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		 JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		 JOIN product
		 ON storemovement.productid = product.productId
		 join storedetail
		 on storedetail.storeid=storefrom.storeId

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

		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and  storedetail.productid=storemovement.productid
		 and storedetail.storeid=storemovement.storeidfrom
		 and storemovement.operationnum ="' . $operationnum . '"
		 and storemovement.conditions=' . $conditions . '
		 and product.conditions = 0
                 GROUP BY storemovement.operationnum,product.productId,storemovement.sizeid,storemovement.colorid';



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

    public function queryStoremovementByoperationnumold22($operationnum, $conditions) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName, user.employeename ,
		 storeto.storeName as storetoName, CONCAT_WS(" / ",product.productName,size.name,color.name) as productName,product.parcode,productcat.productCatName,
                 reciever.employeename as recieveUserName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		 JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		 JOIN product
		 ON storemovement.productid = product.productId
                JOIN  user ON user.userid = storemovement.userid
                left JOIN  user as reciever ON reciever.userid = storemovement.recieveUserid
                 join productcat on productcat.productCatId = product.productCatId

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

		 where storefrom.conditions = 0 and storeto.conditions = 0

		 and storemovement.operationnum ="' . $operationnum . '"
		 and storemovement.conditions=' . $conditions . '
		 and product.conditions = 0';



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

    public function queryStoremovementByoperationnum2($operationnum) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName,
		 storeto.storeName as storetoName, product.productName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		 JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		 JOIN product
		 ON storemovement.productid = product.productId


		 where storefrom.conditions = 0 and storeto.conditions = 0

		 and storemovement.operationnum ="' . $operationnum . '"

		 and product.conditions = 0';



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

    public function queryStoremovementByoperationnumold2($operationnum) {
        $sql = 'SELECT storemovement.*, storefrom.storeName as storefromName, storedetail.productquantity,
		 storeto.storeName as storetoName, product.productName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		 JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		 JOIN product
		 ON storemovement.productid = product.productId
		 join storedetail
		 on storedetail.storeid=storefrom.storeId
		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and  storedetail.productid=storemovement.productid
		 and storemovement.operationnum ="' . $operationnum . '"

		 and product.conditions = 0';



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

    public function loadByoperationnum($operationnum, $conditions) {
        $sql = 'SELECT storemovement.* FROM storemovement
		join otherstoremovement
		on otherstoremovement.id= storemovement.operationnum
		  where
		 storemovement.operationnum ="' . $operationnum . '"
		 and storemovement.conditions=' . $conditions . ' ';

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

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

    public function updateConditions($storemovement) {
        $sql = 'UPDATE storemovement SET  transferproductdate = ?, userid = ?, conditions = ? WHERE transferproductid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($storemovement->transferproductdate);
        $sqlQuery->setNumber($storemovement->userid);
        $sqlQuery->setNumber($storemovement->conditions);

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

    public function updateoperationnum($operationnum, $oldoperationnum) {
        $sql = 'UPDATE storemovement SET  operationnum = ' . $operationnum . ' WHERE operationnum = ' . $oldoperationnum . '';
        $sqlQuery = new SqlQuery($sql);


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

    public function updateBuyPricesHistoryBookData($buyPricesHistoryBookData, $transferproductid) {
        $sql = 'UPDATE storemovement SET  buyPricesHistoryBookData = "' . $buyPricesHistoryBookData . '" WHERE transferproductid = ' . $transferproductid . '';
        $sqlQuery = new SqlQuery($sql);


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

    public function insertEX($storemovement) {
        $sql = 'INSERT INTO storemovement (storeidfrom, storeidto, productid, transferproductamount, transferproductdate, userid, conditions,comment,operationnum ,deloperationnum,sizeid,colorid,expireDate) VALUES (?, ?, ?, ?, ?, ?, ? ,?,? ,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($storemovement->storeidfrom);
        $sqlQuery->setNumber($storemovement->storeidto);
        $sqlQuery->setNumber($storemovement->productid);
        $sqlQuery->setNumber($storemovement->transferproductamount);
        $sqlQuery->set($storemovement->transferproductdate);
        $sqlQuery->setNumber($storemovement->userid);
        $sqlQuery->setNumber($storemovement->conditions);

        $sqlQuery->set($storemovement->comment);
        $sqlQuery->set($storemovement->operationnum);
        $sqlQuery->set($storemovement->deloperationnum);
        $sqlQuery->setNumber((int) $storemovement->sizeid);
        $sqlQuery->setNumber((int) $storemovement->colorid);
        $sqlQuery->set($storemovement->expireDate);

        $id = $this->executeInsert($sqlQuery);
        $storemovement->transferproductid = $id;
        return $id;
    }

    public function loadBydeloperationnum($deloperationnum) {
        $sql = 'SELECT * FROM storemovement   where
		 storemovement.deloperationnum ="' . $deloperationnum . '"
		 group by operationnum
		 ';

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

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

    public function queryAlloperationnum($queryString = '') {
        $sql = 'SELECT DISTINCT (`operationnum`) FROM `storemovement` WHERE operationnum != 0 ' . $queryString . ' ORDER BY `operationnum` DESC ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getLastidSimple($queryString) {
        $sql = 'SELECT DISTINCT (`operationnum`) FROM `storemovement` WHERE operationnum != 0 ' . $queryString . ' ORDER BY `operationnum` DESC limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function operationData($operationnum) {
        $sql = 'SELECT * FROM storemovement WHERE operationnum = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($operationnum);
        return $this->getRow($sqlQuery);
    }

    public function getAutoIncrementValue($DBName) {
        $sql = 'SELECT `AUTO_INCREMENT`
                FROM  INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = "' . $DBName . '"
                AND   TABLE_NAME   = "storemovement" ';

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

    public function getAutoIncrementValue2($DBName) {
        $sql = 'SELECT `AUTO_INCREMENT`
                FROM  INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = "' . $DBName . '"
                AND   TABLE_NAME   = "storemovement" ';

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

    public function operationData2($operationnum) {
        $sql = 'SELECT * FROM storemovement WHERE operationnum = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($operationnum);
        return $this->getList($sqlQuery);
    }

    //# m7md
    public function loadByDateAndProduct($startDate, $endDate, $productId, $storeId) {
        $sql = '    SELECT productid , transferproductamount
                    FROM storemovement
                    WHERE productid = ' . $productId . ' and transferproductdate <= "' . $endDate . '" and transferproductdate >= "' . $startDate . '"';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByStoreidfromAndProductAndDate($storeid, $productid, $startDate, $endData) {
        $sql = 'SELECT * FROM storemovement WHERE storeidfrom = ' . $storeid . ' and productid = ' . $productid . ' and transferproductdate >= "' . $startDate . '" and transferproductdate <= "' . $endData . '" ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByStoreidtoAndProductAndDate($storeid, $productid, $startDate, $endData) {
        $sql = 'SELECT * FROM storemovement WHERE storeidto = ' . $storeid . ' and productid = ' . $productid . ' and transferproductdate >= "' . $startDate . '" and transferproductdate <= "' . $endData . '" ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //eman

    public function queryStoremovementByoperationnumCatname($operationnum, $conditions) {
        $sql = 'SELECT DISTINCT storemovement.transferproductid, storemovement.*, storefrom.storeName as storefromName, storedetail.productquantity,
		 storeto.storeName as storetoName, CONCAT_WS(" / ",product.productName,size.name,color.name) as productName,product.parcode,productcat.productCatName
		 FROM storemovement JOIN store storefrom
		 ON storemovement.storeidfrom = storefrom.storeId
		 JOIN store storeto
		 ON storemovement.storeidto = storeto.storeId
		 JOIN product
		 ON storemovement.productid = product.productId
		 join storedetail
		 on storedetail.storeid=storefrom.storeId
                 join productcat
		 on
		 productcat.productCatId = product.productCatId

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

		 where storefrom.conditions = 0 and storeto.conditions = 0
		 and  storedetail.productid=storemovement.productid
		 and storedetail.storeid=storemovement.storeidfrom
		 and storemovement.operationnum ="' . $operationnum . '"
		 and storemovement.conditions=' . $conditions . '
		 and product.conditions = 0
         group by transferproductid';



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

    //serials
    public function updateExpireDateEX($movementId, $productSerials) {
        $sql = 'UPDATE storemovement SET  expireDate = "' . $productSerials . '" WHERE transferproductid = ' . $movementId;
        //print_r('<br>'.$sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function markMoveAsPreparedByoperationnum($userid, $operationnum) {
        $sql = 'UPDATE storemovement SET  isPrepared = 1 , useridPrepared=' . $userid . ' WHERE operationnum = ' . $operationnum;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatedailyentryidByoperationnum($dailyentryid, $operationnum) {
        $sql = 'UPDATE storemovement SET  dailyentryid =' . $dailyentryid . ' WHERE operationnum = ' . $operationnum;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatedailyentryidBytransferproductid($dailyentryid, $transferproductid) {
        $sql = 'UPDATE storemovement SET  dailyentryid =' . $dailyentryid . ' WHERE transferproductid = ' . $transferproductid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateExpireDateToEX($movementId, $productSerials) {
        $sql = 'UPDATE storemovement SET  expireDateTo = "' . $productSerials . '" WHERE transferproductid = ' . $movementId;
        //print_r('<br>'.$sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function createInsertUniqueOptNumSP() {

        require_once($currentDirectory . '../models/sql/ConnectionProperty.class.php'); //absolute path is required
        $COLUMN_TYPE = R::getCell('SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "' . ConnectionProperty::getDatabase() . '" AND TABLE_NAME = "storemovement" AND COLUMN_NAME = "transferproductamount"');



        $_SESSION['stop_checking_result'] = 1;
        $_SESSION['noWriteToBackup'] = 1;

        $sql = 'DROP PROCEDURE IF EXISTS insertUniqueOptNum;';
        $sqlQuery = new SqlQuery($sql);
        $this->executeUpdate($sqlQuery);

        //create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
        $sql = '
CREATE PROCEDURE insertUniqueOptNum(IN optnum INT,IN storeidfrom INT,IN storeidto INT,IN productid INT,IN transferproductamount ' . $COLUMN_TYPE . ',IN transferproductdate DATE,IN userid INT,IN conditions INT,IN `comment` TEXT,IN `maincomment` TEXT,IN deloperationnum INT,IN sizeid INT,IN colorid INT,IN driverid INT,IN drivertimeout INT,IN driverdate datetime,IN delivererid INT,IN deliverdate datetime,IN storetype INT,IN expireDate Text,IN priceType TEXT,IN recieveType INT,OUT inserted_id INT,OUT optnum_used INT)
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE id INT DEFAULT 0;
DECLARE lastopt INT DEFAULT 0;


DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    ROLLBACK;
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
     @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
    SELECT @full_error;
    ROLLBACK;
END;



START TRANSACTION;


SELECT COUNT(*) FROM storemovement WHERE `operationnum` = optnum INTO n;
SELECT DISTINCT operationnum FROM storemovement WHERE operationnum != 0  ORDER BY operationnum DESC limit 1 INTO lastopt;
set lastopt = lastopt+1;

	IF n > 0 THEN


      INSERT INTO storemovement (storeidfrom, storeidto, productid, transferproductamount, transferproductdate, userid, conditions,comment,maincomment,operationnum ,deloperationnum,sizeid,colorid,driverid,drivertimeout,driverdate,delivererid,deliverdate,storetype,expireDate,priceType,recieveType)
                VALUES (storeidfrom, storeidto, productid, transferproductamount, transferproductdate, userid, conditions ,comment,maincomment,
                lastopt,
                deloperationnum,sizeid,colorid,driverid,drivertimeout,driverdate,delivererid,deliverdate,storetype,expireDate,priceType,recieveType);
            SELECT LAST_INSERT_ID(),lastopt INTO inserted_id,optnum_used;
ELSE

    INSERT INTO storemovement (storeidfrom, storeidto, productid, transferproductamount, transferproductdate, userid, conditions,comment,maincomment,operationnum ,deloperationnum,sizeid,colorid,driverid,drivertimeout,driverdate,delivererid,deliverdate,storetype,expireDate,priceType,recieveType)
                VALUES (storeidfrom, storeidto, productid,transferproductamount, transferproductdate, userid, conditions ,comment,maincomment
                ,optnum
                ,deloperationnum,sizeid,colorid,driverid,drivertimeout,driverdate,delivererid,deliverdate,storetype,expireDate,priceType,recieveType);
            SELECT LAST_INSERT_ID(),optnum INTO inserted_id,optnum_used;
	END IF;

COMMIT WORK; -- it is mandatory dont wait comit in php.as if sp didnot found comit before reaching the end it will rollback giving no errors at all

End;
;;

';
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery);
        unset($_SESSION['stop_checking_result']);
        unset($_SESSION['noWriteToBackup']);
        return $affected_rows;
    }

    public function callInsertUniqueOptNumSP($obj) {
        $mysqli = new mysqli(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
        if ($mysqli->connect_errno) {
            echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
        }
        ##checks

        $callSql = "CALL insertUniqueOptNum($obj->operationnum,$obj->storeidfrom,$obj->storeidto,$obj->productid,$obj->transferproductamount,'" . $obj->transferproductdate . "',$obj->userid,$obj->conditions, '" . $obj->comment . "' , '" . $obj->maincomment . "' ," . (int) $obj->deloperationnum . "," . (int) $obj->sizeid . "," . (int) $obj->colorid . "," . (int) $obj->driverid . "," . (int) $obj->drivertimeout . ",'" . $obj->driverdate . "'," . (int) $obj->delivererid . ",'" . $obj->deliverdate . "'," . (int) $obj->storetype . ",'" . $obj->expireDate . "','" . $obj->priceType . "'," . (int) $obj->recieveType . ",@id,@optnum)";

        $myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
        $txt = $callSql . ";\r\n";
        fwrite($myfile, $txt);

        fclose($myfile);


        if (!$mysqli->query("SET @id = 0,@optnum=0") || !$mysqli->query($callSql)) {
            echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }

        if (!($res = $mysqli->query("SELECT @id as id,@optnum as optnum"))) {
            //echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
            throw new Exception("Fetch failed in Stored Procedure: (" . $mysqli->errno . ") " . $mysqli->error);
        }
        $row = $res->fetch_assoc();
        //return $row['_p_out']; this to return only value directly
        $rowAsObj = new stdClass();
        foreach ($row as $key => $value) {
            $rowAsObj->$key = $value;
        }
        return $rowAsObj;
    }

    public function updateFirstPrice($price, $id) {
        $sql = 'UPDATE storemovement SET price = ' . $price . ' WHERE transferproductid = ' . $id;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}
