<?php

/**
 * Class that operate on table 'sizecolorstoredetail'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2019-08-25 13:50
 */
class SizecolorstoredetailMySqlExtDAO extends SizecolorstoredetailMySqlDAO {

    public function queryByProductidEX($productId, $queryString = '') {
        $sql = "SELECT sizecolorstoredetail.*,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid =" . $productId . " " . $queryString . " order by sizecolorstoredetail.id asc";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getSumProductQuantityInAllStores($productId) {
        $sql = "SELECT sizecolorstoredetail.id,sizecolorstoredetail.productid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid,sizecolorstoredetail.parcode,sum(quantity) as quantity
                ,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid =" . $productId . "
                    group by sizecolorstoredetail.productid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
                order by sizecolorstoredetail.id asc";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getSumProductSizeColorQuantityInAllStores($productId, $sizeid, $colorid) {
        $sql = "SELECT sizecolorstoredetail.id,sizecolorstoredetail.productid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid,sizecolorstoredetail.parcode,sum(quantity) as quantity
                ,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid =$productId and sizecolorstoredetail.sizeid = $sizeid and sizecolorstoredetail.colorid = $colorid
                group by sizecolorstoredetail.productid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid";
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryByProductidAndStoreIdEX($productId, $storeId, $queryString = '') {
        $sql = "SELECT sizecolorstoredetail.*,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid =" . $productId . " and storeid=" . $storeId . " " . $queryString . " order by sizecolorstoredetail.id asc";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function deleteProductSizeColorWhereIdNotIn($productid, $sizeColorExist) {
        $sql = "DELETE FROM sizecolorstoredetail WHERE productid = $productid and id not in($sizeColorExist)";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getIdByProductStoreSizeAndColorEX($productid, $storeid, $sizeid, $colorid) {
        $sql = "SELECT sizecolorstoredetail.id
                FROM sizecolorstoredetail
                WHERE productid = $productid and storeid = $storeid and sizeid = $sizeid and colorid = $colorid ";
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getIdByProductStoreSizeAndColorEXNew($productid, $store, $sizeid, $colorid) {
        $sql = "SELECT sizecolorstoredetail.id
                FROM sizecolorstoredetail
                WHERE productid = $productid and $store and sizeid = $sizeid and colorid = $colorid ";
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getIdByProductStoreSizeAndColorEX2($productid, $storeid, $sizeid, $colorid) {
        $sql = "SELECT *
                FROM sizecolorstoredetail
                WHERE productid = $productid and storeid = $storeid and sizeid = $sizeid and colorid = $colorid ";
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function getParcodeByProductSizeAndColorEX($productid, $sizeid, $colorid) {//same parcode in any store
        $sql = "SELECT sizecolorstoredetail.*,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid = $productid and sizeid = $sizeid and colorid = $colorid ";
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function getParcodeByProductSizeAndColorEXList($productid, $sizeid, $colorid) {//same parcode in any store
        $sql = "SELECT sizecolorstoredetail.*,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid = $productid and sizeid = $sizeid and colorid = $colorid ";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getParcodeByProductSizeAndColorAndStoreEX($productid, $sizeid, $colorid, $storeid) {//same parcode in any store
        $sql = "SELECT sizecolorstoredetail.*,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE productid = $productid and sizeid = $sizeid and colorid = $colorid and storeid = $storeid ";
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadEX($id) {
        $sql = 'SELECT sizecolorstoredetail.*,size.name as sizeName,color.name as colorName
                FROM sizecolorstoredetail
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE sizecolorstoredetail.id = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function queryByParcodeEX($value) {
        $sql = 'SELECT sizecolorstoredetail.*,product.productName,productcat.productCatName,size.name as sizeName,color.name as colorName,product.productDescription,
                product.selldiscountpercent,product.sellpercenttype
                FROM sizecolorstoredetail
                join product on product.productId = sizecolorstoredetail.productid
                join productcat on productcat.productCatId = product.productCatId
                left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                left join sizecolor as color on color.id=sizecolorstoredetail.colorid
                WHERE sizecolorstoredetail.parcode = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getRow($sqlQuery);
    }

    public function queryAllGeneralQueryString($queryString = '') {//,size.name as sizeName,color.name as colorName
        $sql = "SELECT sizecolorstoredetail.*, CONCAT_WS('/',product.productName,size.name,color.name) as productName,product.productCatId,store.storeName,
		product.productSellUnitPrice, product.productBuyPrice,
		product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
                product.lastbuyprice_withTax,product.meanbuyprice_withTax
                FROM sizecolorstoredetail
                join sizecolor as size on size.id=sizecolorstoredetail.sizeid
                join sizecolor as color on color.id=sizecolorstoredetail.colorid
                JOIN product ON  sizecolorstoredetail.productid = product.productId
		JOIN store ON  sizecolorstoredetail.storeid = store.storeId
                WHERE 1 $queryString";
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function checkParcode($parcode, $queryString = '') {
        $sql = 'SELECT count(*) FROM sizecolorstoredetail WHERE parcode = ? ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($parcode);
        return $this->querySingleResult($sqlQuery);
    }

    public function updateSizeColorParcodeInAllStores($parcode, $productid, $sizeid, $colorid) {
        $sql = 'UPDATE sizecolorstoredetail SET parcode = "' . $parcode . '"
                        WHERE productid = ' . $productid . ' and sizeid = ' . $sizeid . ' and colorid = ' . $colorid . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateExcelInfo($scdExcelParcode, $productid, $sizeid, $colorid, $storeid) {
        $sql = 'UPDATE sizecolorstoredetail SET scdExcelParcode = "' . $scdExcelParcode . '"
			WHERE productid = ' . $productid . ' and sizeid = ' . $sizeid . ' and colorid = ' . $colorid . ' and storeid = ' . $storeid . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatebuyprice($buyprice, $productid, $sizeid, $colorid, $storeid) {
        $sql = 'UPDATE sizecolorstoredetail SET buyprice = "' . $buyprice . '"
			WHERE productid = ' . $productid . ' and sizeid = ' . $sizeid . ' and colorid = ' . $colorid . ' and storeid = ' . $storeid . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

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

    public function queryByParcodeInSimple($parcodes) {
        $sql = "SELECT distinct productId,sizeid,colorid,parcode
                FROM sizecolorstoredetail
                where parcode in ($parcodes)";

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

    public function checkIfProductHasSizecolor($value) {
        $sql = 'SELECT count(*) FROM sizecolorstoredetail WHERE productid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->querySingleResult($sqlQuery);
    }

}

?>