<?php

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


    public function queryByConditions($queryString = '') {
        $sql = 'SELECT * FROM store where conditions = 0 '.$queryString.' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }


    public function queryByConditionsOne($queryString = '')
    {
        $sql = 'SELECT * FROM store where conditions = 0 '.$queryString.' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }



    public function queryWithOrder() {
        $sql = 'SELECT * FROM store order by storeId desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithOrderWithQuantity() {
        $sql = 'SELECT *,sum(storedetail.productquantity) as totQty
                FROM store
                left JOIN storedetail ON  storedetail.storeid = store.storeId
                group by storedetail.storeid
                order by store.storeId desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithOrderWithQuantityAndPrice() {
        $sql = 'SELECT store.*,sum(storedetail.productquantity) as totQty,sum(storedetail.productquantity * product.lastbuyprice) as totQtyPrice
                FROM store
                left JOIN storedetail ON  storedetail.storeid = store.storeId
                left JOIN product ON  (storedetail.productid = product.productId and product.conditions = 0)
                group by store.storeId
                order by store.storeId desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function storeEvaluationAtAllPrices($queryString = '') {
        $sql = 'SELECT store.storeId,store.storeName,store.conditions
                ,ROUND(sum(storedetail.productquantity) , 2) as totQty
                ,ROUND(sum(storedetail.productquantity * product.productBuyPrice) , 2) as tot_productBuyPrice
                ,ROUND(sum(storedetail.productquantity * product.productSellAllPrice) , 2) as tot_productSellAllPrice
                ,ROUND(sum(storedetail.productquantity * product.productSellUnitPrice) , 2) as tot_productSellUnitPrice
                ,ROUND(sum(storedetail.productquantity * product.productSellHalfPrice) , 2) as tot_productSellHalfPrice
                ,ROUND(sum(storedetail.productquantity * product.lastbuyprice) , 2) as tot_lastbuyprice
                ,ROUND(sum(storedetail.productquantity * product.lastbuyprice_withDiscount) , 2) as tot_lastbuyprice_withDiscount
                ,ROUND(sum(storedetail.productquantity * product.meanbuyprice) , 2) as tot_meanbuyprice
                ,ROUND(sum(storedetail.productquantity * product.meanbuyprice_withDiscount) , 2) as tot_meanbuyprice_withDiscount
                ,ROUND(sum(storedetail.productquantity * product.overAllAveragePrice) , 2) as tot_overAllAveragePrice
                ,ROUND(sum(storedetail.productquantity * product.lastbuyprice_withTax) , 2) as tot_lastbuyprice_withTax
                ,ROUND(sum(storedetail.productquantity * product.meanbuyprice_withTax) , 2) as tot_meanbuyprice_withTax
                FROM store
                left JOIN storedetail ON  storedetail.storeid = store.storeId
                left JOIN product ON  (storedetail.productid = product.productId and product.conditions = 0)
                where 1 '.$queryString.'
                group by store.storeId
                order by store.storeId desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function storeEvaluationAtAllPricesDetailed() {
        $sql = 'select storeId,storeName,conditions
                ,ROUND(sum(totQty) , 2) as totQty,ROUND(sum(totQty_n) , 2) as totQty_n
                ,ROUND(sum(tot_productBuyPrice) , 2)as tot_productBuyPrice,ROUND(sum(tot_productBuyPrice_n) , 2) as tot_productBuyPrice_n
                ,ROUND(sum(tot_productSellAllPrice) , 2) as tot_productSellAllPrice,ROUND(sum(tot_productSellAllPrice_n) , 2) as tot_productSellAllPrice_n
                ,ROUND(sum(tot_productSellUnitPrice) , 2) as tot_productSellUnitPrice,ROUND(sum(tot_productSellUnitPrice_n) , 2) as tot_productSellUnitPrice_n
                ,ROUND(sum(tot_productSellHalfPrice) , 2) as tot_productSellHalfPrice,ROUND(sum(tot_productSellHalfPrice_n) , 2) as tot_productSellHalfPrice_n
                ,ROUND(sum(tot_lastbuyprice) , 2) as tot_lastbuyprice,ROUND(sum(tot_lastbuyprice_n) , 2) as tot_lastbuyprice_n
                ,ROUND(sum(tot_lastbuyprice_withDiscount) , 2) as tot_lastbuyprice_withDiscount,ROUND(sum(tot_lastbuyprice_withDiscount_n) , 2) as tot_lastbuyprice_withDiscount_n
                ,ROUND(sum(tot_meanbuyprice) , 2) as tot_meanbuyprice,ROUND(sum(tot_meanbuyprice_n) , 2) as tot_meanbuyprice_n
                ,ROUND(sum(tot_meanbuyprice_withDiscount) , 2) as tot_meanbuyprice_withDiscount,ROUND(sum(tot_meanbuyprice_withDiscount_n) , 2) as tot_meanbuyprice_withDiscount_n

                from (

                    (SELECT store.storeId,store.storeName,store.conditions
                    ,sum(storedetail.productquantity) as totQty
                    ,0 as totQty_n
                    ,sum(storedetail.productquantity * product.productBuyPrice) as tot_productBuyPrice
                    ,0 as tot_productBuyPrice_n
                    ,sum(storedetail.productquantity * product.productSellAllPrice) as tot_productSellAllPrice
                    ,0 as tot_productSellAllPrice_n
                    ,sum(storedetail.productquantity * product.productSellUnitPrice) as tot_productSellUnitPrice
                    ,0 as tot_productSellUnitPrice_n
                    ,sum(storedetail.productquantity * product.productSellHalfPrice) as tot_productSellHalfPrice
                    ,0 as tot_productSellHalfPrice_n
                    ,sum(storedetail.productquantity * product.lastbuyprice) as tot_lastbuyprice
                    ,0 as tot_lastbuyprice_n
                    ,sum(storedetail.productquantity * product.lastbuyprice_withDiscount) as tot_lastbuyprice_withDiscount
                    ,0 as tot_lastbuyprice_withDiscount_n
                    ,sum(storedetail.productquantity * product.meanbuyprice) as tot_meanbuyprice
                    ,0 as tot_meanbuyprice_n
                    ,sum(storedetail.productquantity * product.meanbuyprice_withDiscount) as tot_meanbuyprice_withDiscount
                    ,0 as tot_meanbuyprice_withDiscount_n
                    FROM store
                    left JOIN storedetail ON  storedetail.storeid = store.storeId
                    left JOIN product ON  (storedetail.productid = product.productId and product.conditions = 0)
                    where productquantity >= 0 and product.conditions = 0 and hasSizeAndColor != 1
                    group by store.storeId)

                    union

                    (SELECT store.storeId,store.storeName,store.conditions
                    ,0 as totQty
                    ,sum(storedetail.productquantity) as totQty_n
                    ,0 as tot_productBuyPrice
                    ,sum(storedetail.productquantity * product.productBuyPrice) as tot_productBuyPrice_n
                    ,0 as tot_productSellAllPrice
                    ,sum(storedetail.productquantity * product.productSellAllPrice) as tot_productSellAllPrice_n
                    ,0 as tot_productSellUnitPrice
                    ,sum(storedetail.productquantity * product.productSellUnitPrice) as tot_productSellUnitPrice_n
                    ,0 as tot_productSellHalfPrice
                    ,sum(storedetail.productquantity * product.productSellHalfPrice) as tot_productSellHalfPrice_n
                    ,0 as tot_lastbuyprice
                    ,sum(storedetail.productquantity * product.lastbuyprice) as tot_lastbuyprice_n
                    ,0 as tot_lastbuyprice_withDiscount
                    ,sum(storedetail.productquantity * product.lastbuyprice_withDiscount) as tot_lastbuyprice_withDiscount_n
                    ,0 as tot_meanbuyprice
                    ,sum(storedetail.productquantity * product.meanbuyprice) as tot_meanbuyprice_n
                    ,0 as tot_meanbuyprice_withDiscount
                    ,sum(storedetail.productquantity * product.meanbuyprice_withDiscount) as tot_meanbuyprice_withDiscount_n
                    FROM store
                    left JOIN storedetail ON  storedetail.storeid = store.storeId
                    left JOIN product ON  (storedetail.productid = product.productId and product.conditions = 0)
                    where productquantity < 0 and product.conditions = 0 and hasSizeAndColor != 1
                    group by store.storeId)

                )as tempTable

                group by storeId'; //order by store.storeId desc
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

public function storeEvaluationAtAllPricesDetailedNew($storeId) {
        $sql = 'select storeId,storeName,conditions
                ,ROUND(sum(totQty) , 2) as totQty,ROUND(sum(totQty_n) , 2) as totQty_n
                ,ROUND(sum(tot_productBuyPrice) , 2)as tot_productBuyPrice,ROUND(sum(tot_productBuyPrice_n) , 2) as tot_productBuyPrice_n
                ,ROUND(sum(tot_productSellAllPrice) , 2) as tot_productSellAllPrice,ROUND(sum(tot_productSellAllPrice_n) , 2) as tot_productSellAllPrice_n
                ,ROUND(sum(tot_productSellUnitPrice) , 2) as tot_productSellUnitPrice,ROUND(sum(tot_productSellUnitPrice_n) , 2) as tot_productSellUnitPrice_n
                ,ROUND(sum(tot_productSellHalfPrice) , 2) as tot_productSellHalfPrice,ROUND(sum(tot_productSellHalfPrice_n) , 2) as tot_productSellHalfPrice_n
                ,ROUND(sum(tot_lastbuyprice) , 2) as tot_lastbuyprice,ROUND(sum(tot_lastbuyprice_n) , 2) as tot_lastbuyprice_n
                ,ROUND(sum(tot_lastbuyprice_withDiscount) , 2) as tot_lastbuyprice_withDiscount,ROUND(sum(tot_lastbuyprice_withDiscount_n) , 2) as tot_lastbuyprice_withDiscount_n
                ,ROUND(sum(tot_meanbuyprice) , 2) as tot_meanbuyprice,ROUND(sum(tot_meanbuyprice_n) , 2) as tot_meanbuyprice_n
                ,ROUND(sum(tot_meanbuyprice_withDiscount) , 2) as tot_meanbuyprice_withDiscount,ROUND(sum(tot_meanbuyprice_withDiscount_n) , 2) as tot_meanbuyprice_withDiscount_n

                from (

                    (SELECT store.storeId,store.storeName,store.conditions
                    ,sum(sizecolorstoredetail.quantity) as totQty
                    ,0 as totQty_n
                    ,sum(sizecolorstoredetail.quantity * product.productBuyPrice) as tot_productBuyPrice
                    ,0 as tot_productBuyPrice_n
                    ,sum(sizecolorstoredetail.quantity * product.productSellAllPrice) as tot_productSellAllPrice
                    ,0 as tot_productSellAllPrice_n
                    ,sum(sizecolorstoredetail.quantity * product.productSellUnitPrice) as tot_productSellUnitPrice
                    ,0 as tot_productSellUnitPrice_n
                    ,sum(sizecolorstoredetail.quantity * product.productSellHalfPrice) as tot_productSellHalfPrice
                    ,0 as tot_productSellHalfPrice_n
                    ,sum(sizecolorstoredetail.quantity * product.lastbuyprice) as tot_lastbuyprice
                    ,0 as tot_lastbuyprice_n
                    ,sum(sizecolorstoredetail.quantity * product.lastbuyprice_withDiscount) as tot_lastbuyprice_withDiscount
                    ,0 as tot_lastbuyprice_withDiscount_n
                    ,sum(sizecolorstoredetail.quantity * product.meanbuyprice) as tot_meanbuyprice
                    ,0 as tot_meanbuyprice_n
                    ,sum(sizecolorstoredetail.quantity * product.meanbuyprice_withDiscount) as tot_meanbuyprice_withDiscount
                    ,0 as tot_meanbuyprice_withDiscount_n
                    FROM store
                    left JOIN sizecolorstoredetail ON  sizecolorstoredetail.storeid = store.storeId
                    left JOIN product ON  (sizecolorstoredetail.productid = product.productId and product.conditions = 0)
                    where quantity >= 0 and product.conditions = 0
                    group by store.storeId)

                    union

                    (SELECT store.storeId,store.storeName,store.conditions
                    ,0 as totQty
                    ,sum(sizecolorstoredetail.quantity) as totQty_n
                    ,0 as tot_productBuyPrice
                    ,sum(sizecolorstoredetail.quantity * product.productBuyPrice) as tot_productBuyPrice_n
                    ,0 as tot_productSellAllPrice
                    ,sum(sizecolorstoredetail.quantity * product.productSellAllPrice) as tot_productSellAllPrice_n
                    ,0 as tot_productSellUnitPrice
                    ,sum(sizecolorstoredetail.quantity * product.productSellUnitPrice) as tot_productSellUnitPrice_n
                    ,0 as tot_productSellHalfPrice
                    ,sum(sizecolorstoredetail.quantity * product.productSellHalfPrice) as tot_productSellHalfPrice_n
                    ,0 as tot_lastbuyprice
                    ,sum(sizecolorstoredetail.quantity * product.lastbuyprice) as tot_lastbuyprice_n
                    ,0 as tot_lastbuyprice_withDiscount
                    ,sum(sizecolorstoredetail.quantity * product.lastbuyprice_withDiscount) as tot_lastbuyprice_withDiscount_n
                    ,0 as tot_meanbuyprice
                    ,sum(sizecolorstoredetail.quantity * product.meanbuyprice) as tot_meanbuyprice_n
                    ,0 as tot_meanbuyprice_withDiscount
                    ,sum(sizecolorstoredetail.quantity * product.meanbuyprice_withDiscount) as tot_meanbuyprice_withDiscount_n
                    FROM store
                    left JOIN sizecolorstoredetail ON  sizecolorstoredetail.storeid = store.storeId
                    left JOIN product ON  (sizecolorstoredetail.productid = product.productId and product.conditions = 0)
                    where quantity < 0 and product.conditions = 0
                    group by store.storeId)

                )as tempTable
where storeId = '.$storeId.'
                group by storeId'; //order by store.storeId desc
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function updateConditions($store) {
        $sql = 'UPDATE store SET storeDate = ?,  conditions = ?, userId = ? WHERE storeId = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($store->storeDate);
        $sqlQuery->setNumber($store->conditions);
        $sqlQuery->setNumber($store->userId);

        $sqlQuery->setNumber($store->storeId);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryWithConditionExpectId($storeId) {
        $sql = 'SELECT * FROM store where storeId != ' . $storeId . ' and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function insertFirstStore($store, $storeDescription) {
        $sql = 'INSERT INTO store (storeId, storeName, storeDate, storeDescription, conditions, userId,treeId,treeIdBetween) VALUES (?, ?, ?, "' . $storeDescription . '", ?, ?,?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($store->storeId);
        $sqlQuery->set($store->storeName);
        $sqlQuery->set($store->storeDate);
        $sqlQuery->setNumber($store->conditions);
        $sqlQuery->setNumber($store->userId);
        $sqlQuery->setNumber((int) $store->treeId);
        $sqlQuery->setNumber((int) $store->treeIdBetween);

        $this->executeInsert($sqlQuery);
    }

    public function queryAllIn($ids, $queryString = '') {
        $sql = 'SELECT * FROM store where storeId in(' . $ids . ')' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllQueryStringSimple($queryString) {
        $sql = 'SELECT storeId,storeName FROM store ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function run($command) {
        $sql = $command;
        $sqlQuery = new SqlQuery($sql);

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

}

?>