File: /home/mostafedeg/public_html/erp/models/mysql/ext/StoreMySqlExtDAO.class.php
<?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
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
group by store.storeId)
)as tempTable
group by storeId'; //order by store.storeId desc
$sqlQuery = new SqlQuery($sql);
return $this->getList($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);
}
}
?>