<?php

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

//update store details
    public function updateStoreDetails($storeId, $productId, $productQuantity) {

        $sql = 'update storedetail
                set productquantity ="' . $productQuantity . '"
                where productid = "' . $productId . '"
                and storeid = "' . $storeId . '"';

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

    public function queryWithProductId($productid) {
        $sql = 'SELECT storedetail.*, product.productName,
		  product.productSellUnitPrice  product.productCatId, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
                 product.lastbuyprice_withTax,product.meanbuyprice_withTax,
		 store.storeName
		 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  storedetail.productid = ' . $productid . '
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductIdExt($productid) {
        $sql = 'SELECT storedetail.productquantity, storedetail.productid,storedetail.storeid, storedetail.storedetailid, product.productName, store.storeName
		 FROM storedetail
		 JOIN product
		 ON  storedetail.productid = product.productId
		 JOIN store
		 ON  storedetail.storeid = store.storeId
		 where storedetail.productid = ' . $productid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductIdAndStoreId($productid, $storeId) {
        $sql = 'SELECT storedetail.*, product.productName,
		  product.productSellUnitPrice, product.productBuyPrice,
		 store.storeName,  product.productCatId
		 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  storedetail.productid = ' . $productid . '
		 AND  storedetail.storeid = ' . $storeId . '
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithqueryString($queryString, $order) {
        $sql = 'SELECT  product.parcode , storedetail.*, product.productName,
		  product.productSellUnitPrice, product.productBuyPrice,
		 store.storeName,  product.productCatId,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId
		 JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . '
		 order by storedetailid ' . $order . '';

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

    public function queryWithqueryStringFatma($queryString, $queryString2, $queryStringAll, $order) {//like queryByProductCatIdInExt in productEX
        $sql = 'SELECT * from
                ((SELECT product.parcode,product.productId as productid, product.productName, product.productBuyPrice,product.productSellUnitPrice,product.productCatId,productcat.productCatName
                ,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
                ,product.isOptic,product.conditions,product.logo,product.productDescription,product.hasSizeAndColor
                ,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype

                FROM product
                join productcat	on productcat.productCatId = product.productCatId
                join storedetail on product.productId = storedetail.productid
                JOIN store ON  storedetail.storeid = store.storeId
                WHERE product.conditions = 0
                and productcat.isOptic != 2
                and product.isOptic != 2
                ' . $queryString . '

                )union(

                SELECT -1 as parcode,product.productCatId as productid,product.productCatName as productName,-1 as productBuyPrice,-1 as productSellUnitPrice,cat.productCatId as productCatId,cat.productCatName as productCatName
                ,-1 as lastbuyprice,-1 as meanbuyprice,-1 as overAllAveragePrice,-1 as meanbuyprice_withDiscount,-1 as lastbuyprice_withDiscount
                ,product.isOptic,product.conditions,product.logo,product.productCatDescription as productDescription,0 as hasSizeAndColor
                ,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype

                FROM productcat as product
                join productcat as cat on cat.productCatId = product.productCatParent
                join storedetail on product.productCatId = storedetail.productid
                JOIN store ON  storedetail.storeid = store.storeId
                where product.conditions = 0
                and product.isOptic = 2
                ' . $queryString2 . '
                )) as temp
                ' . $queryStringAll . '
                order by storedetailid ' . $order . '';

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

    public function queryWithqueryString2($queryString, $order) {
        $sql = 'SELECT product.parcode ,
                     sum(storedetail.productquantity) as totQty,
                    storedetail.*, product.productName,

                  productcat.productCatName as productCatName,
		  product.productSellUnitPrice, product.productBuyPrice,
		 store.storeName,  product.productCatId
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId
		 JOIN store
		 ON  storedetail.storeid = store.storeId
                 join productcat
                 on product.productCatId = productcat.productCatId

		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . '
                     group by productcat.productCatId
		 order by storedetailid ' . $order . '';

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

    public function queryWithqueryString2andunittype($queryString, $order, $unittype) {
        $sql = 'SELECT product.productName,sum(storedetail.productquantity) as totQty,
                 storedetail.storedetailid,storedetail.productid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype,

                  productcat.productCatName as productCatName,store.storeName,  product.productCatId
		  product.productSellUnitPrice, product.productBuyPrice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,product.meanbuyprice,

		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId
		 JOIN store
		 ON  storedetail.storeid = store.storeId
                 join productcat
                 on product.productCatId = productcat.productCatId

		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . ' and unittype = ' . $unittype . '
                     group by productcat.productCatId
		 order by storedetailid ' . $order . '';

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

    public function queryWithqueryString2andunittypeNoSumNoGroup($queryString, $order, $unittype) {
        $sql = 'SELECT product.productName,product.isOptic,storedetail.*,storedetail.productquantity as totQty,
                  productcat.productCatName as productCatName,store.storeName,  product.productCatId,productcat.productCatParent,
                  product.lastbuyprice,product.meanbuyprice,
		  product.productSellUnitPrice, product.productBuyPrice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
                  product.lastbuyprice_withTax,product.meanbuyprice_withTax

		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId
		 JOIN store
		 ON  storedetail.storeid = store.storeId
                 join productcat
                 on product.productCatId = productcat.productCatId

		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . ' and unittype = ' . $unittype . '

		 order by storedetailid ' . $order . '';

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

    public function queryWithqueryStringandunittype($queryString, $order, $unittype) {
        $sql = 'SELECT storedetail.*, product.productName,
		  product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,
		 store.storeName,  product.productCatId ,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
		 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   unittype = ' . $unittype . '
		 ' . $queryString . '
		 order by storedetailid ' . $order . '';

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

    public function queryWithqueryStringandunittypeFatma($queryString, $queryString2, $queryStringAll, $order, $unittype) {
        $sql = 'SELECT * from
                ((SELECT product.parcode,product.productId, product.productName, product.productBuyPrice,product.productSellUnitPrice,product.productCatId,productcat.productCatName
                ,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
                ,product.isOptic,product.conditions,product.logo,product.productDescription,product.hasSizeAndColor
                ,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype

                FROM product
                join productcat	on productcat.productCatId = product.productCatId
                join storedetail on product.productId = storedetail.productid
                JOIN store ON  storedetail.storeid = store.storeId
                WHERE product.conditions = 0
                and productcat.isOptic != 2
                and product.isOptic != 2
                and   unittype = ' . $unittype . '
                ' . $queryString . '

                )union(

                SELECT -1 as parcode,product.productCatId as productId,product.productCatName as productName,-1 as productBuyPrice,-1 as productSellUnitPrice,cat.productCatId as productCatId,cat.productCatName as productCatName
                ,-1 as lastbuyprice,-1 as meanbuyprice,-1 as overAllAveragePrice,-1 as meanbuyprice_withDiscount,-1 as lastbuyprice_withDiscount
                ,product.isOptic,product.conditions,product.logo,product.productCatDescription as productDescription,0 as hasSizeAndColor
                ,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype

                FROM productcat as product
                join productcat as cat on cat.productCatId = product.productCatParent
                join storedetail on product.productCatId = storedetail.productid
                JOIN store ON  storedetail.storeid = store.storeId
                where product.conditions = 0
                and product.isOptic = 2
                and   unittype = ' . $unittype . '
                ' . $queryString2 . '
                )) as temp
                ' . $queryStringAll . '
                order by storedetailid ' . $order . '';

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

    public function queryproductWithunittype($queryString, $productid, $unittype) {
        $sql = 'SELECT storedetail.*
		 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 storedetail.productId=' . $productid . '
                 and   unittype = ' . $unittype . '
		 ' . $queryString . '
		 ';

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

    public function queryWithProductCat($productCatId) {
        $sql = 'SELECT storedetail.*, product.productName,
		  product.productSellUnitPrice, product.productBuyPrice,
		 store.storeName
		 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 product.productCatId = ' . $productCatId . '
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithqueryStringprice($queryString, $order, $str2) {
        $sql = 'SELECT storedetail.*, product.productName, product.reviewType, store.storeName,  product.productCatId,productcat.productCatName,productcat.selldiscount,productcat.discounttype
		' . $str2 . '

		 FROM storedetail
                 JOIN product ON  storedetail.productid = product.productId
                 JOIN productcat ON  productcat.productCatId = product.productCatId
                 JOIN store ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . '
		 order by productquantity ' . $order . '';

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

    public function queryByProductQtyByCatId($productCatId) {
        $sql = 'SELECT storedetail.productquantity, storedetail.productid, storedetail.storeid,storedetail.storedetailid, product.productName, store.storeName
		 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 product.productCatId = ' . $productCatId . '
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductCatAndStoreId($productCatId, $storeid) {
        $sql = 'SELECT storedetail.*, product.productName,
		  product.productSellUnitPrice, product.productBuyPrice,
		 store.storeName,  product.productCatId
		 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 product.productCatId = ' . $productCatId . '
		 AND  storedetail.storeid = ' . $storeid . '
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithStoreId($storeid) {
        $sql = 'SELECT storedetail.*, product.productName,
		product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
                 product.lastbuyprice_withTax,product.meanbuyprice_withTax,
		store.storeName, product.productCatId
		 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  storedetail.storeid = ' . $storeid . '
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function queryWithOrder() {
        $sql = 'SELECT storedetail.*, product.productName,
		 product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
                 product.lastbuyprice_withTax,product.meanbuyprice_withTax,,store.storeName, product.productCatId
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 order by storedetailid desc';

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

    public function queryWithOrderExt() {
        $sql = 'SELECT storedetail.productquantity, product.*
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 order by storedetailid desc';

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

    public function queryWithStoreAndProduct($productid, $storeid) {
        $sql = 'SELECT storedetail.* , product.productName,
		 product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
                 product.lastbuyprice_withTax,product.meanbuyprice_withTax,
		  store.storeName,isService
		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  storedetail.productid =' . $productid . '
		 and storedetail.storeid =' . $storeid . '';


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

    public function queryWithStoreAndProduct2NoConditions($productid, $storeid) {
        $sql = 'SELECT storedetail.* , product.productName,
		 product.productSellUnitPrice, product.productBuyPrice,
		  store.storeName
		FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where storedetail.productid =' . $productid . '
		 and storedetail.storeid =' . $storeid . '';


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

    public function queryWithStoreIdWithoutProductsId($storeid, $productsArr) {
        $sql = 'SELECT storedetail.*, product.productName,
		 product.productSellUnitPrice, product.productBuyPrice,
		  store.storeName
		 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  storedetail.storeid = ' . $storeid . '
		 and storedetail.productid  NOT IN (' . $productsArr . ')
		 order by storedetailid desc';


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

    public function updateProductquantity($storedetail) {
        $sql = 'UPDATE storedetail SET  productquantity = ?, userid = ?, storedetaildate = ? WHERE storedetailid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($storedetail->productquantity);
        $sqlQuery->setNumber($storedetail->userid);
        $sqlQuery->set($storedetail->storedetaildate);


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

    public function updateProductquantity2($storedetailId, $userid, $storedetaildate, $productquantityAfter, $unittype) {
        $sql = 'UPDATE storedetail SET  productquantity = ' . $productquantityAfter . ', userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE  unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';
//print_r($sql);
        $sqlQuery = new SqlQuery($sql);


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

    public function queryWithStoreIdAndParcode($storeid, $parcode) {
        $sql = 'SELECT storedetail.*, product.productName,
		 product.productSellUnitPrice, product.productBuyPrice,
		  store.storeName
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId LEFT JOIN  productserial

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


		 where product.conditions = 0 and store.conditions = 0
		 and  storedetail.storeid = ' . $storeid . '
		 and product.parcode = "' . $parcode . '"

		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

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

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where product.conditions = 0 and store.conditions = 0
		 and  storedetail.storeid = ' . $storeid . '
		 and storedetail.productquantity < product.limitamount
		 order by storedetailid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithStoreIdAndProductandorderLimit_f($order, $queryString) {
        $sql = 'SELECT storedetail.*, product.productName, productcat.productCatName,
		store.storeName,product.limitamount
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . '
		 and storedetail.productquantity < product.limitamount
		 order by storedetail.productquantity ' . $order . '';
//echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithStoreIdAndProductandorderLimit_f2($order, $queryString) {
        $sql = 'SELECT storedetail.*, product.productName, productcat.productCatName,
		store.storeName,productstore.prostorelimitamount as limitamount
		 FROM storedetail
                 join productstore on (productstore.productid=storedetail.productid   and productstore.storeid=storedetail.storeid)
                 JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where product.conditions = 0 and store.conditions = 0
		 ' . $queryString . '
		 and storedetail.productquantity < productstore.prostorelimitamount
		 order by storedetail.productquantity ' . $order . '';
//echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where product.conditions = 0 and store.conditions = 0
		 and  storedetail.storeid = ' . $storeid . '
		 and storedetail.productquantity < product.limitamount
		 order by storedetailid ' . $order . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function insertEX($storedetail) {
        $sql = 'INSERT INTO storedetail (productid, storeid, productquantity, userid, storedetaildate,unittype) VALUES (?, ?, ?, ?, ?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($storedetail->productid);
        $sqlQuery->setNumber($storedetail->storeid);
        $sqlQuery->setNumber($storedetail->productquantity);
        $sqlQuery->setNumber($storedetail->userid);
        $sqlQuery->set($storedetail->storedetaildate);
        $sqlQuery->setNumber($storedetail->unittype);

        $id = $this->executeInsert($sqlQuery);
        $storedetail->storedetailid = $id;
        return $id;
    }

    public function queryAllproductidinstor($storeid) {
        $sql = 'SELECT storedetail.productid ,productquantity, product.productName,product.parcode , productcat.productCatName,
		product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice  FROM storedetail
		JOIN product
		 ON  storedetail.productid = product.productId
		 join productcat
		 on productcat.productCatId=product.productCatId
		where storedetail.storeid	=' . $storeid . '
		 and productquantity > 0
		';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllproductidinstor2($queryString) {
        $sql = 'SELECT storedetail.productid ,productquantity, product.productName,product.parcode , productcat.productCatName,
		product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice  FROM storedetail
		JOIN product
		 ON  storedetail.productid = product.productId
		 join productcat
		 on productcat.productCatId=product.productCatId
		' . $queryString . '
		 and productquantity > 0
		';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllproductidinstorSizeColor($queryString) {
        $sql = 'SELECT storedetail.productid , if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity, CONCAT_WS("/",product.productName,size.name,color.name) as productName,if(sizecolorstoredetail.id is null ,product.parcode,sizecolorstoredetail.parcode) as parcode , productcat.productCatName,
		product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
                FROM storedetail
		JOIN product
		 ON  storedetail.productid = product.productId
		 join productcat
		 on productcat.productCatId=product.productCatId

                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
		' . $queryString . '
		 and if(sizecolorstoredetail.id is null ,productquantity > 0,quantity > 0)
		';
//echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallstoredata() {
        $sql = 'SELECT storedetail.*, product.productName,
		          product.productBuyPrice ,
                  product.lastbuyprice,
                  product.meanbuyprice,
                  product.lastbuyprice_withDiscount,
                  product.overAllAveragePrice,
                  product.meanbuyprice_withDiscount,product.hasSizeAndColor,store.*
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId join store on storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 order by storedetailid desc';

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

    public function getM5zonPrice($priceColumnName) {
        $sql = 'SELECT ROUND( sum( productquantity * ' . $priceColumnName . ' ) ,2)
                FROM storedetail
                JOIN product ON  storedetail.productid = product.productId
                where product.conditions = 0';

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

    public function queryProductsInInventoryExt($queryString) {
        $sql = 'SELECT storedetail.productquantity, storedetail.productid, storedetail.storeid, storedetail.storedetailid, product.productName, product.productCatId, store.storeName
		FROM storedetail
		JOIN product ON  storedetail.productid = product.productId
		JOIN productcat	 ON  product.productCatId = productcat.productCatId
		JOIN store	 ON  storedetail.storeid = store.storeId

		 ' . $queryString . '
		 and product.conditions = 0
		 order by storedetailid desc';
//	 print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryProductsInInventoryExt2($queryString, $limitQS = '') {
        $sql = 'SELECT DISTINCT storedetail.productid , product.productName , product.productCatId
		FROM storedetail
		JOIN product ON  storedetail.productid = product.productId
		JOIN productcat	 ON  product.productCatId = productcat.productCatId
		 ' . $queryString . '
		 and product.conditions = 0
		 order by storedetailid desc ' . $limitQS;
//print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryProductsInInventoryExt2NoService($queryString) {
        $sql = 'SELECT SQL_CACHE storedetail.productid , product.productName, product.parcode , product.productCatId, if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity,storedetail.storedetailid,storedetail.storeid,store.storeName
                ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                ,size.name as sizeName,color.name as colorName
		FROM storedetail
		JOIN store ON  store.storeId = storedetail.storeid
                JOIN product ON  storedetail.productid = product.productId
		JOIN productcat	 ON  product.productCatId = productcat.productCatId


                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


		 ' . $queryString . '
                 and product.isService = 0
		 and product.conditions = 0
                 GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
		 order by storedetailid desc';
//print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryProductsInInventoryExt2NoServiceWithUnion($queryString, $queryStringSizeColor, $limitQS = '') {//productcat.productCatName
        $sql = 'select SQL_CACHE *
            from (
                    (
                        SELECT storedetail.productid , product.productName, product.parcode , product.procode, product.productCatId, productquantity,storedetail.storedetailid,store.storeId as storeid,store.storeName
                        ,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
                        ,0 as sizeName,0 as colorName
                        ,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
                        FROM storedetail
                        JOIN store ON  store.storeId = storedetail.storeid
                        JOIN product ON  storedetail.productid = product.productId
                        left join sizecolorstoredetail on (sizecolorstoredetail.productId=storedetail.productid)

                         ' . $queryString . '
                         and sizecolorstoredetail.id is null
                         and product.isService = 0
                         and product.conditions = 0
                    )
                     union
                    (
                        SELECT sizecolorstoredetail.productid , product.productName, product.procode, sizecolorstoredetail.parcode , product.productCatId, quantity as productquantity,0 as storedetailid,store.storeId as storeid,store.storeName
                        ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                        ,size.name as sizeName,color.name as colorName
                        ,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
                        FROM sizecolorstoredetail
                        join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                        join sizecolor as color on color.id=sizecolorstoredetail.colorid
                        JOIN store ON  store.storeId = sizecolorstoredetail.storeid
                        JOIN product ON  sizecolorstoredetail.productid = product.productId

                         ' . $queryStringSizeColor . '
                         and product.isService = 0
                         and product.conditions = 0
                    )
                ) as result

                GROUP BY productId,sizeid,colorid
		order by productId asc
                ' . $limitQS . '
                ';


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

    public function queryProductsInInventoryExt2NoServiceWithUnionNotInProductSerial($queryString, $queryStringSizeColor) {//productcat.productCatName
        $sql = 'select SQL_CACHE *
            from (
                    (
                        SELECT storedetail.productid , product.productName, product.parcode , product.productCatId, productquantity,storedetail.storedetailid,store.storeId as storeid,store.storeName
                        ,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
                        ,0 as sizeName,0 as colorName
                        ,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
                        FROM storedetail
                        JOIN store ON  store.storeId = storedetail.storeid
                        JOIN product ON  storedetail.productid = product.productId
                        left join sizecolorstoredetail on (sizecolorstoredetail.productId=storedetail.productid)
                        left join productserial ON productserial.productid = product.productId

                         ' . $queryString . '
                         and sizecolorstoredetail.id is null
                         and productserial.productserailid is null
                         and product.isService = 0
                         and product.conditions = 0
                    )
                     union
                    (
                        SELECT sizecolorstoredetail.productid , product.productName, sizecolorstoredetail.parcode , product.productCatId, quantity as productquantity,0 as storedetailid,store.storeId as storeid,store.storeName
                        ,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                        ,size.name as sizeName,color.name as colorName
                        ,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
                        FROM sizecolorstoredetail
                        join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                        join sizecolor as color on color.id=sizecolorstoredetail.colorid
                        JOIN store ON  store.storeId = sizecolorstoredetail.storeid
                        JOIN product ON  sizecolorstoredetail.productid = product.productId
                        left join productserial ON (productserial.productid = product.productId and productserial.sizeid =size.id and productserial.colorid =color.id)

                         ' . $queryStringSizeColor . '
                         and productserial.productserailid is null
                         and product.isService = 0
                         and product.conditions = 0
                    )
                ) as result

                GROUP BY productId,sizeid,colorid
		order by productId desc
                ';


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

    public function getProductQuantity($productId, $storeId) {
        $sql = "select *  from storedetail where productid = ? and  storeid = ?";

        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($productId);
        $sqlQuery->setNumber($storeId);

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

    public function queryByStoreidAndProductid($productid, $storeid) {
        $sql = 'SELECT * FROM storedetail where productid  =' . $productid . ' and storeid  =' . $storeid . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function getStoreidByStoredetailid($storedetailid) {
        $sql = 'SELECT storeid FROM storedetail where storedetailid= ' . $storedetailid;
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryWithStoreAndProductandunit($productid, $storeid, $unittype) {
        $sql = 'SELECT storedetail.* , product.productName,
		 product.productSellUnitPrice, product.productBuyPrice,
		  store.storeName
		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  storedetail.productid =' . $productid . '
                     and storedetail.unittype=' . $unittype . '
		 and storedetail.storeid =' . $storeid . '';


//print_r($sql);

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

    public function queryByStoreidEX($storeid, $queryString = '') {
        $sql = 'SELECT storedetail.*, product.productName
                FROM storedetail
                JOIN product  ON  storedetail.productid = product.productId
                WHERE storeid = ' . $storeid . $queryString;
        $sqlQuery = new SqlQuery($sql);
//echo $sql;
        return $this->getList($sqlQuery);
    }

    public function querGroupProducts($queryString) {
        $sql = 'SELECT DISTINCT  product.productName, product.productId , product.productCatId, storedetail.storeid,store.storeName
		FROM storedetail
		JOIN store ON  store.storeId = storedetail.storeid
                JOIN product ON  storedetail.productid = product.productId
		JOIN productcat	 ON  product.productCatId = productcat.productCatId
		 ' . $queryString . '
                 and product.isService = 0
		 and product.conditions = 0
		 order by storedetailid desc';
//print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getProductQuantityInAllStores($productId, $querystore) {
        $sql = 'SELECT sum(productquantity) as productid
		FROM storedetail where productid = ' . $productId . ' ' . $querystore;
//        print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getProductQuantityInAllStoresSimple($productid, $queryString = '') {
        $sql = 'SELECT storedetailid,storeid,productquantity
		 FROM storedetail
		 where  storedetail.productid = ' . $productid . ' ' . $queryString;
//echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryProductsNotInInventoryNoServiceFatma($queryStringJoin, $queryString) {
        $sql = 'SELECT DISTINCT storedetail.productid , product.productName, product.parcode, storedetail.productquantity,storedetail.storedetailid,storedetail.storeid
		FROM storedetail
                JOIN product ON  storedetail.productid = product.productId
                left join storereport on (storereport.storeid = storedetail.storeid and storereport.productid = storedetail.productid ' . $queryStringJoin . ')
		 ' . $queryString . '
                 and product.isService = 0
		 and product.conditions = 0
		 order by storedetailid desc';
//print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getproductInStores($productid, $queryString = '') {
        $sql = 'SELECT storedetail.productquantity, store.storeName
		FROM storedetail
		JOIN store ON  storedetail.storeid = store.storeId
		where store.conditions = 0
		and storedetail.productid = ' . $productid . '
                ' . $queryString . '
		order by store.storeId asc';
//	 print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateQuantityPlusEqualORMinusEqual($storedetailId, $userid, $storedetaildate, $productquantityAfter, $unittype, $increaseOrDecrease = 0) {
        if ($increaseOrDecrease == 0)
            $sql = 'UPDATE storedetail SET  productquantity = productquantity+' . $productquantityAfter . ', userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE  unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';
        else
            $sql = 'UPDATE storedetail SET  productquantity = productquantity-' . $productquantityAfter . ', userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE  unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';

//print_r($sql);
        $sqlQuery = new SqlQuery($sql);


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

    public function updateQuantityWithSumChild($storedetailId, $userid, $storedetaildate, $unittype, $storeid, $productid) {
        $sql = 'UPDATE storedetail SET productquantity = (select sum(`quantity`) from sizecolorstoredetail where `storeid` = ' . $storeid . ' and `productid` = ' . $productid . ')
                , userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE  unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';

//print_r($sql);
        $sqlQuery = new SqlQuery($sql);


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

    public function queryAllByProudctnameandParcode($queryString = '') {
        $sql = 'SELECT *,product.logo as productlogo , productcat.logo as productcatlogo FROM `store`
                join storedetail on store.storeId = storedetail.storeid
                join product on storedetail.productid = product.productId
                join productcat on productcat.productCatId = product.productCatId
                 ' . $queryString . ' and store.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getApiProductsData($queryString = '') {
        $sql = 'SELECT storedetail.*,product.webApiId
                 from storedetail
                 join product on(product.productId = storedetail.productid and product.webApiId > 0 and product.conditions = 0)
                 where 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function createStoredetailInUseSP() {
        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 = "storedetail" AND COLUMN_NAME = "productquantity"');


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

        $sql = 'DROP PROCEDURE IF EXISTS storedetailInUse;';
        $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 storedetailInUse(IN productidin INT,IN storeidin INT,IN unittypein INT,
OUT storedetailidis INT, OUT productidis INT, OUT storeidis INT, OUT productquantityis ' . $COLUMN_TYPE . ', OUT useridis INT, OUT storedetaildateis DATE, OUT unittypeis INT)
BEGIN
  DECLARE in_use_flag INT DEFAULT 1;
  SELECT inUse FROM storedetail WHERE productid = productidin and storeid=storeidin and unittype=unittypein INTO in_use_flag;

  IF in_use_flag = 0 THEN
    UPDATE storedetail SET inUse = 1 WHERE productid = productidin and storeid=storeidin and unittype=unittypein;
    SELECT storedetailid, productid, storeid, productquantity, userid,storedetaildate,unittype
        INTO storedetailidis, productidis, storeidis, productquantityis, useridis,storedetaildateis,unittypeis
    FROM storedetail
    WHERE productid = productidin and storeid=storeidin and unittype=unittypein;
  ELSE
    SELECT -10, -10, -10, -10, -10, -10, -10 INTO storedetailidis, productidis, storeidis, productquantityis, useridis,storedetaildateis,unittypeis;
  END IF;
END


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

    public function callStoredetailInUseSP($productid, $storeid, $unittype = 0) {
        if ($productid > 0 && $storeid > 0) {
            $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 storedetailInUse($productid, $storeid, $unittype,@storedetailidis, @productidis, @storeidis, @productquantityis, @useridis,@storedetaildateis,@unittypeis)";
            $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 @storedetailidis=-10, @productidis=-10, @storeidis=-10, @productquantityis=-10, @useridis=-10,@storedetaildateis=-10,@unittypeis=-10") || !$mysqli->query($callSql)) {
                echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }

            if (!($res = $mysqli->query("SELECT @storedetailidis as storedetailid, @productidis as productid, @storeidis as storeid, @productquantityis as productquantity, @useridis as userid,@storedetaildateis as storedetaildate,@unittypeis as unittype"))) {
                echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }
            $row = $res->fetch_assoc();
            $rowAsObj = new stdClass();
            foreach ($row as $key => $value) {
                $rowAsObj->$key = $value;
            }
            return $rowAsObj;
        }
    }

    public function createSizeColorStoredetailInUseSP() {
        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 = "sizecolorstoredetail" AND COLUMN_NAME = "quantity"');


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

        $sql = 'DROP PROCEDURE IF EXISTS sizeColorStoredetailInUseSP;';
        $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 sizeColorStoredetailInUseSP(IN idin INT,OUT idis INT, OUT storeidis INT, OUT productidis INT, OUT sizeidis INT,OUT coloridis INT,OUT parcodeis VARCHAR(300),
OUT buypriceis decimal(10,2),OUT sellunitpriceis decimal(10,2) ,OUT quantityis ' . $COLUMN_TYPE . ', OUT useridis INT, OUT sysdateis DATE, OUT scdExcelParcodeis VARCHAR(12))
BEGIN
  DECLARE in_use_flag INT DEFAULT 1;
  SELECT inUse FROM sizecolorstoredetail WHERE id = idin INTO in_use_flag;

  IF in_use_flag = 0 THEN
    UPDATE sizecolorstoredetail SET inUse = 1 WHERE id = idin;
    SELECT id,storeid,productid,sizeid,colorid,parcode,buyprice,sellunitprice,quantity,userid,sysdate,scdExcelParcode
        INTO idis, storeidis, productidis,sizeidis,coloridis,parcodeis,buypriceis,sellunitpriceis,quantityis,useridis,sysdateis,scdExcelParcodeis
    FROM sizecolorstoredetail
    WHERE id = idin;
  ELSE
    SELECT -10, -10, -10, -10, -10, -10, -10, -10, -10, -10, -10, -10 INTO idis, storeidis, productidis,sizeidis,coloridis,parcodeis,buypriceis,sellunitpriceis,quantityis,useridis,sysdateis,scdExcelParcodeis;
  END IF;
END


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

    public function callSizeColorStoredetailInUseSP($id) {
        if ($id > 0) {
            $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 sizeColorStoredetailInUseSP($id,@idis,@storeidis,@productidis,@sizeidis,@coloridis,@parcodeis,@buypriceis,@sellunitpriceis,@quantityis,@useridis,@sysdateis,@scdExcelParcodeis)";

            $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 @idis=-10,@storeidis=-10,@productidis=-10,@sizeidis=-10,@coloridis=-10,@parcodeis=-10,@buypriceis=-10,@sellunitpriceis=-10,@quantityis=-10,@useridis=-10,@sysdateis=-10,@scdExcelParcodeis=-10") || !$mysqli->query($callSql)) {
                echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }

            if (!($res = $mysqli->query("SELECT @idis as id,@storeidis as storeid,@productidis as productid,@sizeidis as sizeid,@coloridis as colorid,@parcodeis as parcode,@buypriceis as buyprice,@sellunitpriceis as sellunitprice,@quantityis as quantity,@useridis as userid,@sysdateis as sysdate,@scdExcelParcodeis as scdExcelParcode"))) {
                echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }
            $row = $res->fetch_assoc();
            $rowAsObj = new stdClass();
            foreach ($row as $key => $value) {
                $rowAsObj->$key = $value;
            }
            return $rowAsObj;
        }
    }

    public function markStoreDetailAsNOTInUse($id = 0, $type = 0) {
        if ($type == 0) {//it is normal pro
            $sql = 'UPDATE storedetail SET inUse = 0 WHERE storedetailid=' . $id;
        } else {//it is sizecolor pro
            $sql = 'UPDATE sizecolorstoredetail SET inUse = 0 WHERE id =' . $id;
        }
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}

?>