<?php

/**
 * Class that operate on table 'quickprofitproduct'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2019-01-21 13:45
 */
class QuickprofitproductMySqlExtDAO extends QuickprofitproductMySqlDAO {

    public function insertOrUpdatePlusEX($obj) {
        //must have UNIQUE constraint on the "combination" of the two columns
        //ALTER TABLE quickprofitproduct ADD CONSTRAINT uq_quickprofitproduct UNIQUE(`productId`, `theDate`)
        $sql = " INSERT INTO quickprofitproduct (productId, theDate, sellVal, sellCostBuyPrice, sellCostLastBuyPrice, sellCostMeanBuyPrice, sellCostLastBuyPricewithDiscount, sellCostMeanBuyPricewithDiscount, sellCostOverAllAveragePrice, returnSellVal, returnSellCostBuyPrice, returnSellCostLastBuyPrice, returnSellCostMeanBuyPrice, returnSellCostLastBuyPricewithDiscount, returnSellCostMeanBuyPricewithDiscount, returnSellCostOverAllAveragePrice,netQuantity,netQuantityRet,sellCostLastBuyPricewithTax,sellCostMeanBuyPricewithTax,returnSellCostLastBuyPricewithTax,returnSellCostMeanBuyPricewithTax)
                    VALUES ($obj->productId, '" . $obj->theDate . "', $obj->sellVal, $obj->sellCostBuyPrice, $obj->sellCostLastBuyPrice
                    , $obj->sellCostMeanBuyPrice, $obj->sellCostLastBuyPricewithDiscount, $obj->sellCostMeanBuyPricewithDiscount, $obj->sellCostOverAllAveragePrice
                    , $obj->returnSellVal, $obj->returnSellCostBuyPrice, $obj->returnSellCostLastBuyPrice, $obj->returnSellCostMeanBuyPrice
                    , $obj->returnSellCostLastBuyPricewithDiscount, $obj->returnSellCostMeanBuyPricewithDiscount, $obj->returnSellCostOverAllAveragePrice, $obj->netQuantity, $obj->netQuantityRet, $obj->sellCostLastBuyPricewithTax, $obj->sellCostMeanBuyPricewithTax, $obj->returnSellCostLastBuyPricewithTax, $obj->returnSellCostMeanBuyPricewithTax)
                ON DUPLICATE KEY UPDATE sellVal = sellVal+$obj->sellVal, sellCostBuyPrice =sellCostBuyPrice+$obj->sellCostBuyPrice
                        , sellCostLastBuyPrice = sellCostLastBuyPrice+$obj->sellCostLastBuyPrice, sellCostMeanBuyPrice = sellCostMeanBuyPrice+$obj->sellCostMeanBuyPrice
                        , sellCostLastBuyPricewithDiscount = sellCostLastBuyPricewithDiscount+$obj->sellCostLastBuyPricewithDiscount, sellCostMeanBuyPricewithDiscount = sellCostMeanBuyPricewithDiscount+$obj->sellCostMeanBuyPricewithDiscount
                            , sellCostOverAllAveragePrice = sellCostOverAllAveragePrice+$obj->sellCostOverAllAveragePrice
                            , sellCostLastBuyPricewithTax = sellCostLastBuyPricewithTax+$obj->sellCostLastBuyPricewithTax
                            , sellCostMeanBuyPricewithTax = sellCostMeanBuyPricewithTax+$obj->sellCostMeanBuyPricewithTax
                        , returnSellVal = returnSellVal+$obj->returnSellVal, returnSellCostBuyPrice = returnSellCostBuyPrice+$obj->returnSellCostBuyPrice, returnSellCostLastBuyPrice = returnSellCostLastBuyPrice+$obj->returnSellCostLastBuyPrice
                        , returnSellCostMeanBuyPrice = returnSellCostMeanBuyPrice+$obj->returnSellCostMeanBuyPrice, returnSellCostLastBuyPricewithDiscount = returnSellCostLastBuyPricewithDiscount+$obj->returnSellCostLastBuyPricewithDiscount
                        , returnSellCostMeanBuyPricewithDiscount = returnSellCostMeanBuyPricewithDiscount+$obj->returnSellCostMeanBuyPricewithDiscount
                            , returnSellCostOverAllAveragePrice = returnSellCostOverAllAveragePrice+$obj->returnSellCostOverAllAveragePrice
                            , returnSellCostLastBuyPricewithTax = returnSellCostLastBuyPricewithTax+$obj->returnSellCostLastBuyPricewithTax
                            , returnSellCostMeanBuyPricewithTax = returnSellCostMeanBuyPricewithTax+$obj->returnSellCostMeanBuyPricewithTax
                        , netQuantity = netQuantity+$obj->netQuantity
                        , netQuantityRet = netQuantityRet+$obj->netQuantityRet";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function insertOrUpdateMinusEX($obj) {
        //must have UNIQUE constraint on the "combination" of the two columns
        //ALTER TABLE quickprofitproduct ADD CONSTRAINT uq_quickprofitproduct UNIQUE(`productId`, `theDate`)
        $sql = " INSERT INTO quickprofitproduct (productId, theDate, sellVal, sellCostBuyPrice, sellCostLastBuyPrice, sellCostMeanBuyPrice, sellCostLastBuyPricewithDiscount, sellCostMeanBuyPricewithDiscount, sellCostOverAllAveragePrice, returnSellVal, returnSellCostBuyPrice, returnSellCostLastBuyPrice, returnSellCostMeanBuyPrice, returnSellCostLastBuyPricewithDiscount, returnSellCostMeanBuyPricewithDiscount, returnSellCostOverAllAveragePrice,netQuantity,netQuantityRet,sellCostLastBuyPricewithTax,sellCostMeanBuyPricewithTax,returnSellCostLastBuyPricewithTax,returnSellCostMeanBuyPricewithTax)
                    VALUES ($obj->productId, '" . $obj->theDate . "', $obj->sellVal, $obj->sellCostBuyPrice, $obj->sellCostLastBuyPrice
                    , $obj->sellCostMeanBuyPrice, $obj->sellCostLastBuyPricewithDiscount, $obj->sellCostMeanBuyPricewithDiscount, $obj->sellCostOverAllAveragePrice
                    , $obj->returnSellVal, $obj->returnSellCostBuyPrice, $obj->returnSellCostLastBuyPrice, $obj->returnSellCostMeanBuyPrice
                    , $obj->returnSellCostLastBuyPricewithDiscount, $obj->returnSellCostMeanBuyPricewithDiscount, $obj->returnSellCostOverAllAveragePrice,$obj->netQuantity,$obj->netQuantityRet, $obj->sellCostLastBuyPricewithTax, $obj->sellCostMeanBuyPricewithTax, $obj->returnSellCostLastBuyPricewithTax, $obj->returnSellCostMeanBuyPricewithTax)
                ON DUPLICATE KEY UPDATE sellVal = sellVal-$obj->sellVal, sellCostBuyPrice =sellCostBuyPrice-$obj->sellCostBuyPrice
                        , sellCostLastBuyPrice = sellCostLastBuyPrice-$obj->sellCostLastBuyPrice, sellCostMeanBuyPrice = sellCostMeanBuyPrice-$obj->sellCostMeanBuyPrice
                        , sellCostLastBuyPricewithDiscount = sellCostLastBuyPricewithDiscount-$obj->sellCostLastBuyPricewithDiscount, sellCostMeanBuyPricewithDiscount = sellCostMeanBuyPricewithDiscount-$obj->sellCostMeanBuyPricewithDiscount
                            , sellCostOverAllAveragePrice = sellCostOverAllAveragePrice-$obj->sellCostOverAllAveragePrice
                            , sellCostLastBuyPricewithTax = sellCostLastBuyPricewithTax-$obj->sellCostLastBuyPricewithTax
                            , sellCostMeanBuyPricewithTax = sellCostMeanBuyPricewithTax-$obj->sellCostMeanBuyPricewithTax
                        , returnSellVal = returnSellVal-$obj->returnSellVal, returnSellCostBuyPrice = returnSellCostBuyPrice-$obj->returnSellCostBuyPrice, returnSellCostLastBuyPrice = returnSellCostLastBuyPrice-$obj->returnSellCostLastBuyPrice
                        , returnSellCostMeanBuyPrice = returnSellCostMeanBuyPrice-$obj->returnSellCostMeanBuyPrice, returnSellCostLastBuyPricewithDiscount = returnSellCostLastBuyPricewithDiscount-$obj->returnSellCostLastBuyPricewithDiscount
                        , returnSellCostMeanBuyPricewithDiscount = returnSellCostMeanBuyPricewithDiscount-$obj->returnSellCostMeanBuyPricewithDiscount
                            , returnSellCostOverAllAveragePrice = returnSellCostOverAllAveragePrice-$obj->returnSellCostOverAllAveragePrice
                            , returnSellCostLastBuyPricewithTax = returnSellCostLastBuyPricewithTax-$obj->returnSellCostLastBuyPricewithTax
                            , returnSellCostMeanBuyPricewithTax = returnSellCostMeanBuyPricewithTax-$obj->returnSellCostMeanBuyPricewithTax
                        , netQuantity = netQuantity-$obj->netQuantity
                        , netQuantityRet = netQuantityRet-$obj->netQuantityRet";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryAllEX($queryString) {
        $sql = "SELECT quickprofitproduct.*, CONCAT(product.productName, '/',  productcat.productCatName) as productName
                FROM quickprofitproduct
                join  product on  product.productId = quickprofitproduct.productId
                JOIN productcat	ON product.productCatId = productcat.productCatId
                where 1 $queryString";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXGroupByCatAndDate($queryString) {
        $sql = "SELECT productcat.productCatName ,theDate,sum(sellVal) as sellVal, sum(sellCostBuyPrice) as sellCostBuyPrice,
               sum(sellCostLastBuyPrice) as sellCostLastBuyPrice, sum(sellCostMeanBuyPrice) as sellCostMeanBuyPrice,
               sum(sellCostLastBuyPricewithDiscount) as sellCostLastBuyPricewithDiscount,
               sum(sellCostMeanBuyPricewithDiscount) as sellCostMeanBuyPricewithDiscount,
               sum(sellCostOverAllAveragePrice) as sellCostOverAllAveragePrice,
               sum(returnSellVal) as returnSellVal, sum(returnSellCostBuyPrice) as returnSellCostBuyPrice,
               sum(returnSellCostLastBuyPrice) as returnSellCostLastBuyPrice, sum(returnSellCostMeanBuyPrice) as returnSellCostMeanBuyPrice,
               sum(returnSellCostLastBuyPricewithDiscount) as returnSellCostLastBuyPricewithDiscount,
               sum(returnSellCostMeanBuyPricewithDiscount) as returnSellCostMeanBuyPricewithDiscount,
               sum(returnSellCostOverAllAveragePrice) as returnSellCostOverAllAveragePrice,
               sum(netQuantity) as netQuantity,
               sum(netQuantityRet) as netQuantityRet,
               sum(sellCostLastBuyPricewithTax) as sellCostLastBuyPricewithTax,
               sum(sellCostMeanBuyPricewithTax) as sellCostMeanBuyPricewithTax,
               sum(returnSellCostLastBuyPricewithTax) as returnSellCostLastBuyPricewithTax,
               sum(returnSellCostMeanBuyPricewithTax) as returnSellCostMeanBuyPricewithTax

                FROM quickprofitproduct

                join  product on  product.productId = quickprofitproduct.productId
                JOIN productcat	ON product.productCatId = productcat.productCatId

                where 1 $queryString
                group by productcat.productCatId";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function truncate() {
        $sql = "TRUNCATE quickprofitproduct";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>