<?php

/**
 * Class that operate on table 'buypriceshistorybook'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2021-09-29 22:15
 */
class BuypriceshistorybookMySqlExtDAO extends BuypriceshistorybookMySqlDAO {

    public function insertOrUpdatePlusEX($obj) {
        //must have UNIQUE constraint on the "combination" of the columns
        //ALTER TABLE `buypriceshistorybook` ADD UNIQUE( `theDate`, `storeId`, `productid`, `sizeid`, `colorid`);
        $sql = "INSERT INTO buypriceshistorybook (theDate, storeId, productid, sizeid, colorid, buyprice, buyQuantity, sellQuantity, del, userid, sysDate)
                    VALUES ('" . $obj->theDate . "', $obj->storeId, $obj->productid, $obj->sizeid, $obj->colorid
                    , $obj->buyprice, $obj->buyQuantity, $obj->sellQuantity, $obj->del, $obj->userid, '" . $obj->sysDate . "')
                ON DUPLICATE KEY UPDATE buyprice = $obj->buyprice,
                        buyQuantity =buyQuantity+$obj->buyQuantity, sellQuantity = sellQuantity+$obj->sellQuantity,
                        del = $obj->del,userid = $obj->userid,sysDate = '" . $obj->sysDate . "' ";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function insertOrUpdatePlusEXBuyPriceNoUpdate($obj) {
        //must have UNIQUE constraint on the "combination" of the columns
        //ALTER TABLE `buypriceshistorybook` ADD UNIQUE( `theDate`, `storeId`, `productid`, `sizeid`, `colorid`);
        $sql = "INSERT INTO buypriceshistorybook (theDate, storeId, productid, sizeid, colorid, buyprice, buyQuantity, sellQuantity, del, userid, sysDate)
                    VALUES ('" . $obj->theDate . "', $obj->storeId, $obj->productid, $obj->sizeid, $obj->colorid
                    , $obj->buyprice, $obj->buyQuantity, $obj->sellQuantity, $obj->del, $obj->userid, '" . $obj->sysDate . "')
                ON DUPLICATE KEY UPDATE buyQuantity =buyQuantity+$obj->buyQuantity, sellQuantity = sellQuantity+$obj->sellQuantity,
                        del = $obj->del,userid = $obj->userid,sysDate = '" . $obj->sysDate . "' ";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function insertOrUpdateMinusEX($obj) {
        //must have UNIQUE constraint on the "combination" of the columns
        //ALTER TABLE `buypriceshistorybook` ADD UNIQUE( `theDate`, `storeId`, `productid`, `sizeid`, `colorid`);
        $sql = "INSERT INTO buypriceshistorybook (theDate, storeId, productid, sizeid, colorid, buyprice, buyQuantity, sellQuantity, del, userid, sysDate)
                    VALUES ('" . $obj->theDate . "', $obj->storeId, $obj->productid, $obj->sizeid, $obj->colorid
                    , $obj->buyprice, $obj->buyQuantity, $obj->sellQuantity, $obj->del, $obj->userid, '" . $obj->sysDate . "')
                ON DUPLICATE KEY UPDATE buyprice = $obj->buyprice,
                        buyQuantity =buyQuantity-$obj->buyQuantity, sellQuantity = sellQuantity-$obj->sellQuantity,
                        del = $obj->del,userid = $obj->userid,sysDate = '" . $obj->sysDate . "' ";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function insertOrUpdateMinusEXBuyPriceNoUpdate($obj) {
        //must have UNIQUE constraint on the "combination" of the columns
        //ALTER TABLE `buypriceshistorybook` ADD UNIQUE( `theDate`, `storeId`, `productid`, `sizeid`, `colorid`);
        $sql = "INSERT INTO buypriceshistorybook (theDate, storeId, productid, sizeid, colorid, buyprice, buyQuantity, sellQuantity, del, userid, sysDate)
                    VALUES ('" . $obj->theDate . "', $obj->storeId, $obj->productid, $obj->sizeid, $obj->colorid
                    , $obj->buyprice, $obj->buyQuantity, $obj->sellQuantity, $obj->del, $obj->userid, '" . $obj->sysDate . "')
                ON DUPLICATE KEY UPDATE buyQuantity =buyQuantity-$obj->buyQuantity, sellQuantity = sellQuantity-$obj->sellQuantity,
                        del = $obj->del,userid = $obj->userid,sysDate = '" . $obj->sysDate . "' ";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function plusSellQuantityByValue($changeInQuantity, $id) {
        $sql = "UPDATE buypriceshistorybook set sellQuantity = sellQuantity+$changeInQuantity where id = $id";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function minusSellQuantityByValue($changeInQuantity, $id) {
        $sql = "UPDATE buypriceshistorybook set sellQuantity = sellQuantity-$changeInQuantity where id = $id";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function plusBuyQuantityByValue($changeInQuantity, $id) {
        $sql = "UPDATE buypriceshistorybook set buyQuantity = buyQuantity+$changeInQuantity where id = $id";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function minusBuyQuantityByValue($changeInQuantity, $id) {
        $sql = "UPDATE buypriceshistorybook set buyQuantity = buyQuantity-$changeInQuantity where id = $id";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getBuyPriceOfFirstAvailableSellQuantity($storeId, $productid, $sizeid, $colorid, $queryString = '') {
        $sql = "SELECT *
                FROM buypriceshistorybook
                where del=0 and storeId=$storeId and productid=$productid and sizeid=$sizeid and colorid=$colorid
                and buyQuantity > sellQuantity and buyQuantity > 0 " . $queryString . "
                order by theDate asc limit 1";
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

}

?>