File: /home/mostafedeg/public_html/erp/models/mysql/ext/StoredetailMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'storedetail'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class StoredetailMySqlExtDAO extends StoredetailMySqlDAO {
//update store details
public function updateStoreDetails($storeId, $productId, $productQuantity) {
$sql = 'update storedetail
set productquantity ="' . $productQuantity . '"
where productid = "' . $productId . '"
and storeid = "' . $storeId . '"';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryWithProductId($productid) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice product.productCatId, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
product.lastbuyprice_withTax,product.meanbuyprice_withTax,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and storedetail.productid = ' . $productid . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithProductIdExt($productid) {
$sql = 'SELECT storedetail.productquantity, storedetail.productid,storedetail.storeid, storedetail.storedetailid, product.productName, store.storeName
FROM storedetail
JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where storedetail.productid = ' . $productid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithProductIdAndStoreId($productid, $storeId) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName, product.productCatId
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
AND storedetail.productid = ' . $productid . '
AND storedetail.storeid = ' . $storeId . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryString($queryString, $order) {
$sql = 'SELECT product.parcode , storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName, product.productCatId,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
' . $queryString . '
order by storedetailid ' . $order . '';
// print_r("<br>" . $sql . "<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryStringFatma($queryString, $queryString2, $queryStringAll, $order) {//like queryByProductCatIdInExt in productEX
$sql = 'SELECT * from
((SELECT product.parcode,product.productId as productid, product.productName, product.productBuyPrice,product.productSellUnitPrice,product.productCatId,productcat.productCatName
,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
,product.isOptic,product.conditions,product.logo,product.productDescription,product.hasSizeAndColor
,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype
FROM product
join productcat on productcat.productCatId = product.productCatId
join storedetail on product.productId = storedetail.productid
JOIN store ON storedetail.storeid = store.storeId
WHERE product.conditions = 0
and productcat.isOptic != 2
and product.isOptic != 2
' . $queryString . '
)union(
SELECT -1 as parcode,product.productCatId as productid,product.productCatName as productName,-1 as productBuyPrice,-1 as productSellUnitPrice,cat.productCatId as productCatId,cat.productCatName as productCatName
,-1 as lastbuyprice,-1 as meanbuyprice,-1 as overAllAveragePrice,-1 as meanbuyprice_withDiscount,-1 as lastbuyprice_withDiscount
,product.isOptic,product.conditions,product.logo,product.productCatDescription as productDescription,0 as hasSizeAndColor
,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype
FROM productcat as product
join productcat as cat on cat.productCatId = product.productCatParent
join storedetail on product.productCatId = storedetail.productid
JOIN store ON storedetail.storeid = store.storeId
where product.conditions = 0
and product.isOptic = 2
' . $queryString2 . '
)) as temp
' . $queryStringAll . '
order by storedetailid ' . $order . '';
// print_r("<br>" . $sql . "<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryString2($queryString, $order) {
$sql = 'SELECT product.parcode ,
sum(storedetail.productquantity) as totQty,
storedetail.*, product.productName,
productcat.productCatName as productCatName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName, product.productCatId
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
join productcat
on product.productCatId = productcat.productCatId
where product.conditions = 0 and store.conditions = 0
' . $queryString . '
group by productcat.productCatId
order by storedetailid ' . $order . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryString2andunittype($queryString, $order, $unittype) {
$sql = 'SELECT product.productName,sum(storedetail.productquantity) as totQty,
storedetail.storedetailid,storedetail.productid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype,
productcat.productCatName as productCatName,store.storeName, product.productCatId
product.productSellUnitPrice, product.productBuyPrice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,product.meanbuyprice,
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
join productcat
on product.productCatId = productcat.productCatId
where product.conditions = 0 and store.conditions = 0
' . $queryString . ' and unittype = ' . $unittype . '
group by productcat.productCatId
order by storedetailid ' . $order . '';
// print_r("<br>" . $sql . "<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryString2andunittypeNoSumNoGroup($queryString, $order, $unittype) {
$sql = 'SELECT product.productName,product.isOptic,storedetail.*,storedetail.productquantity as totQty,
productcat.productCatName as productCatName,store.storeName, product.productCatId,productcat.productCatParent,
product.lastbuyprice,product.meanbuyprice,
product.productSellUnitPrice, product.productBuyPrice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
product.lastbuyprice_withTax,product.meanbuyprice_withTax
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
join productcat
on product.productCatId = productcat.productCatId
where product.conditions = 0 and store.conditions = 0
' . $queryString . ' and unittype = ' . $unittype . '
order by storedetailid ' . $order . '';
// print_r("<br>" . $sql . "<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryStringandunittype($queryString, $order, $unittype) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,
store.storeName, product.productCatId ,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and unittype = ' . $unittype . '
' . $queryString . '
order by storedetailid ' . $order . '';
// print_r("<br>".$sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryStringandunittypeFatma($queryString, $queryString2, $queryStringAll, $order, $unittype) {
$sql = 'SELECT * from
((SELECT product.parcode,product.productId, product.productName, product.productBuyPrice,product.productSellUnitPrice,product.productCatId,productcat.productCatName
,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount
,product.isOptic,product.conditions,product.logo,product.productDescription,product.hasSizeAndColor
,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype
FROM product
join productcat on productcat.productCatId = product.productCatId
join storedetail on product.productId = storedetail.productid
JOIN store ON storedetail.storeid = store.storeId
WHERE product.conditions = 0
and productcat.isOptic != 2
and product.isOptic != 2
and unittype = ' . $unittype . '
' . $queryString . '
)union(
SELECT -1 as parcode,product.productCatId as productId,product.productCatName as productName,-1 as productBuyPrice,-1 as productSellUnitPrice,cat.productCatId as productCatId,cat.productCatName as productCatName
,-1 as lastbuyprice,-1 as meanbuyprice,-1 as overAllAveragePrice,-1 as meanbuyprice_withDiscount,-1 as lastbuyprice_withDiscount
,product.isOptic,product.conditions,product.logo,product.productCatDescription as productDescription,0 as hasSizeAndColor
,store.storeName,storedetail.storedetailid,storedetail.storeid,storedetail.productquantity,storedetail.storedetaildate,storedetail.unittype
FROM productcat as product
join productcat as cat on cat.productCatId = product.productCatParent
join storedetail on product.productCatId = storedetail.productid
JOIN store ON storedetail.storeid = store.storeId
where product.conditions = 0
and product.isOptic = 2
and unittype = ' . $unittype . '
' . $queryString2 . '
)) as temp
' . $queryStringAll . '
order by storedetailid ' . $order . '';
// print_r("<br>".$sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryproductWithunittype($queryString, $productid, $unittype) {
$sql = 'SELECT storedetail.*
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and storedetail.productId=' . $productid . '
and unittype = ' . $unittype . '
' . $queryString . '
';
// print_r("<br>".$sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithProductCat($productCatId) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
AND product.productCatId = ' . $productCatId . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithqueryStringprice($queryString, $order, $str2) {
$sql = 'SELECT storedetail.*, product.productName, product.reviewType, store.storeName, product.productCatId,productcat.productCatName,productcat.selldiscount,productcat.discounttype
' . $str2 . '
FROM storedetail
JOIN product ON storedetail.productid = product.productId
JOIN productcat ON productcat.productCatId = product.productCatId
JOIN store ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
' . $queryString . '
order by productquantity ' . $order . '';
// print_r("<br>".$sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByProductQtyByCatId($productCatId) {
$sql = 'SELECT storedetail.productquantity, storedetail.productid, storedetail.storeid,storedetail.storedetailid, product.productName, store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
AND product.productCatId = ' . $productCatId . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithProductCatAndStoreId($productCatId, $storeid) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName, product.productCatId
FROM storedetail JOIN product
ON storedetail.productid = product.productId
JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
AND product.productCatId = ' . $productCatId . '
AND storedetail.storeid = ' . $storeid . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithStoreId($storeid) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
product.lastbuyprice_withTax,product.meanbuyprice_withTax,
store.storeName, product.productCatId
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and storedetail.storeid = ' . $storeid . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithStoreIdExt($storeid) {
$sql = 'SELECT storedetail.productquantity, storedetail.productid, storedetail.storeid, storedetail.storedetailid, product.productName, product.productCatId, store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0
and storedetail.storeid = ' . $storeid . '
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithOrder() {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
product.lastbuyprice_withTax,product.meanbuyprice_withTax,,store.storeName, product.productCatId
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithOrderExt() {
$sql = 'SELECT storedetail.productquantity, product.*
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithStoreAndProduct($productid, $storeid) {
$sql = 'SELECT storedetail.* , product.productName,
product.productSellUnitPrice, product.productBuyPrice,product.lastbuyprice,product.meanbuyprice,product.overAllAveragePrice,product.meanbuyprice_withDiscount,product.lastbuyprice_withDiscount,
product.lastbuyprice_withTax,product.meanbuyprice_withTax,
store.storeName,isService
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and storedetail.productid =' . $productid . '
and storedetail.storeid =' . $storeid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithStoreAndProduct2NoConditions($productid, $storeid) {
$sql = 'SELECT storedetail.* , product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where storedetail.productid =' . $productid . '
and storedetail.storeid =' . $storeid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithStoreIdWithoutProductsId($storeid, $productsArr) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and storedetail.storeid = ' . $storeid . '
and storedetail.productid NOT IN (' . $productsArr . ')
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateProductquantity($storedetail) {
$sql = 'UPDATE storedetail SET productquantity = ?, userid = ?, storedetaildate = ? WHERE storedetailid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($storedetail->productquantity);
$sqlQuery->setNumber($storedetail->userid);
$sqlQuery->set($storedetail->storedetaildate);
$sqlQuery->setNumber($storedetail->storedetailid);
return $this->executeUpdate($sqlQuery);
}
public function updateProductquantity2($storedetailId, $userid, $storedetaildate, $productquantityAfter, $unittype) {
$sql = 'UPDATE storedetail SET productquantity = ' . $productquantityAfter . ', userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryWithStoreIdAndParcode($storeid, $parcode) {
$sql = 'SELECT storedetail.*, product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId LEFT JOIN productserial
ON ( productserial.productid = product.productId
AND productserial.serialnumber ="' . $parcode . '"
AND productserial.productserailid
IN (
SELECT MAX(productserailid) FROM productserial
WHERE productserial.productid = product.productId
ORDER BY productserailid DESC
))
where product.conditions = 0 and store.conditions = 0
and storedetail.storeid = ' . $storeid . '
and product.parcode = "' . $parcode . '"
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithStoreIdAndProductLimit($storeid) {
$sql = 'SELECT storedetail.*, product.productName, productcat.productCatName,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
join productcat
on productcat.productCatId=product.productCatId
where product.conditions = 0 and store.conditions = 0
and storedetail.storeid = ' . $storeid . '
and storedetail.productquantity < product.limitamount
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithStoreIdAndProductandorderLimit_f($order, $queryString) {
$sql = 'SELECT storedetail.*, product.productName, productcat.productCatName,
store.storeName,product.limitamount
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
join productcat
on productcat.productCatId=product.productCatId
where product.conditions = 0 and store.conditions = 0
' . $queryString . '
and storedetail.productquantity < product.limitamount
order by storedetail.productquantity ' . $order . '';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithStoreIdAndProductandorderLimit_f2($order, $queryString) {
$sql = 'SELECT storedetail.*, product.productName, productcat.productCatName,
store.storeName,productstore.prostorelimitamount as limitamount
FROM storedetail
join productstore on (productstore.productid=storedetail.productid and productstore.storeid=storedetail.storeid)
JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
join productcat
on productcat.productCatId=product.productCatId
where product.conditions = 0 and store.conditions = 0
' . $queryString . '
and storedetail.productquantity < productstore.prostorelimitamount
order by storedetail.productquantity ' . $order . '';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithStoreIdAndProductandorderLimit($storeid, $order) {
$sql = 'SELECT storedetail.*, product.productName, productcat.productCatName,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
join productcat
on productcat.productCatId=product.productCatId
where product.conditions = 0 and store.conditions = 0
and storedetail.storeid = ' . $storeid . '
and storedetail.productquantity < product.limitamount
order by storedetailid ' . $order . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function insertEX($storedetail) {
$sql = 'INSERT INTO storedetail (productid, storeid, productquantity, userid, storedetaildate,unittype) VALUES (?, ?, ?, ?, ?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($storedetail->productid);
$sqlQuery->setNumber($storedetail->storeid);
$sqlQuery->setNumber($storedetail->productquantity);
$sqlQuery->setNumber($storedetail->userid);
$sqlQuery->set($storedetail->storedetaildate);
$sqlQuery->setNumber($storedetail->unittype);
$id = $this->executeInsert($sqlQuery);
$storedetail->storedetailid = $id;
return $id;
}
public function queryAllproductidinstor($storeid) {
$sql = 'SELECT storedetail.productid ,productquantity, product.productName,product.parcode , productcat.productCatName,
product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice FROM storedetail
JOIN product
ON storedetail.productid = product.productId
join productcat
on productcat.productCatId=product.productCatId
where storedetail.storeid =' . $storeid . '
and productquantity > 0
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllproductidinstor2($queryString) {
$sql = 'SELECT storedetail.productid ,productquantity, product.productName,product.parcode , productcat.productCatName,
product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice FROM storedetail
JOIN product
ON storedetail.productid = product.productId
join productcat
on productcat.productCatId=product.productCatId
' . $queryString . '
and productquantity > 0
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllproductidinstorSizeColor($queryString) {
$sql = 'SELECT storedetail.productid , if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity, CONCAT_WS("/",product.productName,size.name,color.name) as productName,if(sizecolorstoredetail.id is null ,product.parcode,sizecolorstoredetail.parcode) as parcode , productcat.productCatName,
product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM storedetail
JOIN product
ON storedetail.productid = product.productId
join productcat
on productcat.productCatId=product.productCatId
left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
left join sizecolor as color on color.id=sizecolorstoredetail.colorid
' . $queryString . '
and if(sizecolorstoredetail.id is null ,productquantity > 0,quantity > 0)
';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallstoredata() {
$sql = 'SELECT storedetail.*, product.productName,
product.productBuyPrice ,
product.lastbuyprice,
product.meanbuyprice,
product.lastbuyprice_withDiscount,
product.overAllAveragePrice,
product.meanbuyprice_withDiscount,store.*
FROM storedetail JOIN product
ON storedetail.productid = product.productId join store on storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getM5zonPrice($priceColumnName) {
$sql = 'SELECT ROUND( sum( productquantity * ' . $priceColumnName . ' ) ,2)
FROM storedetail
JOIN product ON storedetail.productid = product.productId
where product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryProductsInInventoryExt($queryString) {
$sql = 'SELECT storedetail.productquantity, storedetail.productid, storedetail.storeid, storedetail.storedetailid, product.productName, product.productCatId, store.storeName
FROM storedetail
JOIN product ON storedetail.productid = product.productId
JOIN productcat ON product.productCatId = productcat.productCatId
JOIN store ON storedetail.storeid = store.storeId
' . $queryString . '
and product.conditions = 0
order by storedetailid desc';
// print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductsInInventoryExt2($queryString, $limitQS = '') {
$sql = 'SELECT DISTINCT storedetail.productid , product.productName , product.productCatId
FROM storedetail
JOIN product ON storedetail.productid = product.productId
JOIN productcat ON product.productCatId = productcat.productCatId
' . $queryString . '
and product.conditions = 0
order by storedetailid desc ' . $limitQS;
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductsInInventoryExt2NoService($queryString) {
$sql = 'SELECT SQL_CACHE storedetail.productid , product.productName, product.parcode , product.productCatId, if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity,storedetail.storedetailid,storedetail.storeid,store.storeName
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM storedetail
JOIN store ON store.storeId = storedetail.storeid
JOIN product ON storedetail.productid = product.productId
JOIN productcat ON product.productCatId = productcat.productCatId
left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
left join sizecolor as color on color.id=sizecolorstoredetail.colorid
' . $queryString . '
and product.isService = 0
and product.conditions = 0
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
order by storedetailid desc';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductsInInventoryExt2NoServiceWithUnion($queryString, $queryStringSizeColor, $limitQS = '') {//productcat.productCatName
$sql = 'select SQL_CACHE *
from (
(
SELECT storedetail.productid , product.productName, product.parcode , product.procode, product.productCatId, productquantity,storedetail.storedetailid,store.storeId as storeid,store.storeName
,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
,0 as sizeName,0 as colorName
,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
FROM storedetail
JOIN store ON store.storeId = storedetail.storeid
JOIN product ON storedetail.productid = product.productId
left join sizecolorstoredetail on (sizecolorstoredetail.productId=storedetail.productid)
' . $queryString . '
and sizecolorstoredetail.id is null
and product.isService = 0
and product.conditions = 0
)
union
(
SELECT sizecolorstoredetail.productid , product.productName, product.procode, sizecolorstoredetail.parcode , product.productCatId, quantity as productquantity,0 as storedetailid,store.storeId as storeid,store.storeName
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
FROM sizecolorstoredetail
join sizecolor as size on size.id=sizecolorstoredetail.sizeid
join sizecolor as color on color.id=sizecolorstoredetail.colorid
JOIN store ON store.storeId = sizecolorstoredetail.storeid
JOIN product ON sizecolorstoredetail.productid = product.productId
' . $queryStringSizeColor . '
and product.isService = 0
and product.conditions = 0
)
) as result
GROUP BY productId,sizeid,colorid
order by productId asc
' . $limitQS . '
';
// print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductsInInventoryExt2NoServiceWithUnionNotInProductSerial($queryString, $queryStringSizeColor) {//productcat.productCatName
$sql = 'select SQL_CACHE *
from (
(
SELECT storedetail.productid , product.productName, product.parcode , product.productCatId, productquantity,storedetail.storedetailid,store.storeId as storeid,store.storeName
,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
,0 as sizeName,0 as colorName
,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
FROM storedetail
JOIN store ON store.storeId = storedetail.storeid
JOIN product ON storedetail.productid = product.productId
left join sizecolorstoredetail on (sizecolorstoredetail.productId=storedetail.productid)
left join productserial ON productserial.productid = product.productId
' . $queryString . '
and sizecolorstoredetail.id is null
and productserial.productserailid is null
and product.isService = 0
and product.conditions = 0
)
union
(
SELECT sizecolorstoredetail.productid , product.productName, sizecolorstoredetail.parcode , product.productCatId, quantity as productquantity,0 as storedetailid,store.storeId as storeid,store.storeName
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
FROM sizecolorstoredetail
join sizecolor as size on size.id=sizecolorstoredetail.sizeid
join sizecolor as color on color.id=sizecolorstoredetail.colorid
JOIN store ON store.storeId = sizecolorstoredetail.storeid
JOIN product ON sizecolorstoredetail.productid = product.productId
left join productserial ON (productserial.productid = product.productId and productserial.sizeid =size.id and productserial.colorid =color.id)
' . $queryStringSizeColor . '
and productserial.productserailid is null
and product.isService = 0
and product.conditions = 0
)
) as result
GROUP BY productId,sizeid,colorid
order by productId desc
';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getProductQuantity($productId, $storeId) {
$sql = "select * from storedetail where productid = ? and storeid = ?";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($productId);
$sqlQuery->setNumber($storeId);
//print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function queryByStoreidAndProductid($productid, $storeid) {
$sql = 'SELECT * FROM storedetail where productid =' . $productid . ' and storeid =' . $storeid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getStoreidByStoredetailid($storedetailid) {
$sql = 'SELECT storeid FROM storedetail where storedetailid= ' . $storedetailid;
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryWithStoreAndProductandunit($productid, $storeid, $unittype) {
$sql = 'SELECT storedetail.* , product.productName,
product.productSellUnitPrice, product.productBuyPrice,
store.storeName
FROM storedetail JOIN product
ON storedetail.productid = product.productId JOIN store
ON storedetail.storeid = store.storeId
where product.conditions = 0 and store.conditions = 0
and storedetail.productid =' . $productid . '
and storedetail.unittype=' . $unittype . '
and storedetail.storeid =' . $storeid . '';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryByStoreidEX($storeid, $queryString = '') {
$sql = 'SELECT storedetail.*, product.productName
FROM storedetail
JOIN product ON storedetail.productid = product.productId
WHERE storeid = ' . $storeid . $queryString;
$sqlQuery = new SqlQuery($sql);
//echo $sql;
return $this->getList($sqlQuery);
}
public function querGroupProducts($queryString) {
$sql = 'SELECT DISTINCT product.productName, product.productId , product.productCatId, storedetail.storeid,store.storeName
FROM storedetail
JOIN store ON store.storeId = storedetail.storeid
JOIN product ON storedetail.productid = product.productId
JOIN productcat ON product.productCatId = productcat.productCatId
' . $queryString . '
and product.isService = 0
and product.conditions = 0
order by storedetailid desc';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getProductQuantityInAllStores($productId, $querystore) {
$sql = 'SELECT sum(productquantity) as productid
FROM storedetail where productid = ' . $productId . ' ' . $querystore;
// print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function getProductQuantityInAllStoresSimple($productid, $queryString = '') {
$sql = 'SELECT storedetailid,storeid,productquantity
FROM storedetail
where storedetail.productid = ' . $productid . ' ' . $queryString;
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductsNotInInventoryNoServiceFatma($queryStringJoin, $queryString) {
$sql = 'SELECT DISTINCT storedetail.productid , product.productName, product.parcode, storedetail.productquantity,storedetail.storedetailid,storedetail.storeid
FROM storedetail
JOIN product ON storedetail.productid = product.productId
left join storereport on (storereport.storeid = storedetail.storeid and storereport.productid = storedetail.productid ' . $queryStringJoin . ')
' . $queryString . '
and product.isService = 0
and product.conditions = 0
order by storedetailid desc';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getproductInStores($productid, $queryString = '') {
$sql = 'SELECT storedetail.productquantity, store.storeName
FROM storedetail
JOIN store ON storedetail.storeid = store.storeId
where store.conditions = 0
and storedetail.productid = ' . $productid . '
' . $queryString . '
order by store.storeId asc';
// print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateQuantityPlusEqualORMinusEqual($storedetailId, $userid, $storedetaildate, $productquantityAfter, $unittype, $increaseOrDecrease = 0) {
if ($increaseOrDecrease == 0)
$sql = 'UPDATE storedetail SET productquantity = productquantity+' . $productquantityAfter . ', userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';
else
$sql = 'UPDATE storedetail SET productquantity = productquantity-' . $productquantityAfter . ', userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateQuantityWithSumChild($storedetailId, $userid, $storedetaildate, $unittype, $storeid, $productid) {
$sql = 'UPDATE storedetail SET productquantity = (select sum(`quantity`) from sizecolorstoredetail where `storeid` = ' . $storeid . ' and `productid` = ' . $productid . ')
, userid = ' . $userid . ', storedetaildate = "' . $storedetaildate . '" WHERE unittype = ' . $unittype . ' and storedetailid = ' . $storedetailId . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryAllByProudctnameandParcode($queryString = '') {
$sql = 'SELECT *,product.logo as productlogo , productcat.logo as productcatlogo FROM `store`
join storedetail on store.storeId = storedetail.storeid
join product on storedetail.productid = product.productId
join productcat on productcat.productCatId = product.productCatId
' . $queryString . ' and store.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getApiProductsData($queryString = '') {
$sql = 'SELECT storedetail.*,product.webApiId
from storedetail
join product on(product.productId = storedetail.productid and product.webApiId > 0 and product.conditions = 0)
where 1 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function createStoredetailInUseSP() {
require_once($currentDirectory . '../models/sql/ConnectionProperty.class.php'); //absolute path is required
$COLUMN_TYPE = R::getCell('SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "' . ConnectionProperty::getDatabase() . '" AND TABLE_NAME = "storedetail" AND COLUMN_NAME = "productquantity"');
$_SESSION['stop_checking_result'] = 1;
$sql = 'DROP PROCEDURE IF EXISTS storedetailInUse;';
$sqlQuery = new SqlQuery($sql);
$this->executeUpdate($sqlQuery);
//create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
$sql = '
CREATE PROCEDURE storedetailInUse(IN productidin INT,IN storeidin INT,IN unittypein INT,
OUT storedetailidis INT, OUT productidis INT, OUT storeidis INT, OUT productquantityis ' . $COLUMN_TYPE . ', OUT useridis INT, OUT storedetaildateis DATE, OUT unittypeis INT)
BEGIN
DECLARE in_use_flag INT DEFAULT 1;
SELECT inUse FROM storedetail WHERE productid = productidin and storeid=storeidin and unittype=unittypein INTO in_use_flag;
IF in_use_flag = 0 THEN
UPDATE storedetail SET inUse = 1 WHERE productid = productidin and storeid=storeidin and unittype=unittypein;
SELECT storedetailid, productid, storeid, productquantity, userid,storedetaildate,unittype
INTO storedetailidis, productidis, storeidis, productquantityis, useridis,storedetaildateis,unittypeis
FROM storedetail
WHERE productid = productidin and storeid=storeidin and unittype=unittypein;
ELSE
SELECT -10, -10, -10, -10, -10, -10, -10 INTO storedetailidis, productidis, storeidis, productquantityis, useridis,storedetaildateis,unittypeis;
END IF;
END
';
$sqlQuery = new SqlQuery($sql);
$affected_rows = $this->executeUpdate($sqlQuery);
unset($_SESSION['stop_checking_result']);
return $affected_rows;
}
public function callStoredetailInUseSP($productid, $storeid, $unittype = 0) {
if ($productid > 0 && $storeid > 0) {
$mysqli = new mysqli(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
##checks
$callSql = "CALL storedetailInUse($productid, $storeid, $unittype,@storedetailidis, @productidis, @storeidis, @productquantityis, @useridis,@storedetaildateis,@unittypeis)";
$myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
$txt = $callSql . ";\r\n";
fwrite($myfile, $txt);
fclose($myfile);
if (!$mysqli->query("SET @storedetailidis=-10, @productidis=-10, @storeidis=-10, @productquantityis=-10, @useridis=-10,@storedetaildateis=-10,@unittypeis=-10") || !$mysqli->query($callSql)) {
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT @storedetailidis as storedetailid, @productidis as productid, @storeidis as storeid, @productquantityis as productquantity, @useridis as userid,@storedetaildateis as storedetaildate,@unittypeis as unittype"))) {
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$row = $res->fetch_assoc();
$rowAsObj = new stdClass();
foreach ($row as $key => $value) {
$rowAsObj->$key = $value;
}
return $rowAsObj;
}
}
public function createSizeColorStoredetailInUseSP() {
require_once($currentDirectory . '../models/sql/ConnectionProperty.class.php'); //absolute path is required
$COLUMN_TYPE = R::getCell('SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "' . ConnectionProperty::getDatabase() . '" AND TABLE_NAME = "sizecolorstoredetail" AND COLUMN_NAME = "quantity"');
$_SESSION['stop_checking_result'] = 1;
$sql = 'DROP PROCEDURE IF EXISTS sizeColorStoredetailInUseSP;';
$sqlQuery = new SqlQuery($sql);
$this->executeUpdate($sqlQuery);
//create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
$sql = '
CREATE PROCEDURE sizeColorStoredetailInUseSP(IN idin INT,OUT idis INT, OUT storeidis INT, OUT productidis INT, OUT sizeidis INT,OUT coloridis INT,OUT parcodeis VARCHAR(300),
OUT buypriceis decimal(10,2),OUT sellunitpriceis decimal(10,2) ,OUT quantityis ' . $COLUMN_TYPE . ', OUT useridis INT, OUT sysdateis DATE, OUT scdExcelParcodeis VARCHAR(12))
BEGIN
DECLARE in_use_flag INT DEFAULT 1;
SELECT inUse FROM sizecolorstoredetail WHERE id = idin INTO in_use_flag;
IF in_use_flag = 0 THEN
UPDATE sizecolorstoredetail SET inUse = 1 WHERE id = idin;
SELECT id,storeid,productid,sizeid,colorid,parcode,buyprice,sellunitprice,quantity,userid,sysdate,scdExcelParcode
INTO idis, storeidis, productidis,sizeidis,coloridis,parcodeis,buypriceis,sellunitpriceis,quantityis,useridis,sysdateis,scdExcelParcodeis
FROM sizecolorstoredetail
WHERE id = idin;
ELSE
SELECT -10, -10, -10, -10, -10, -10, -10, -10, -10, -10, -10, -10 INTO idis, storeidis, productidis,sizeidis,coloridis,parcodeis,buypriceis,sellunitpriceis,quantityis,useridis,sysdateis,scdExcelParcodeis;
END IF;
END
';
$sqlQuery = new SqlQuery($sql);
$affected_rows = $this->executeUpdate($sqlQuery);
unset($_SESSION['stop_checking_result']);
return $affected_rows;
}
public function callSizeColorStoredetailInUseSP($id) {
if ($id > 0) {
$mysqli = new mysqli(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
##checks
$callSql = "CALL sizeColorStoredetailInUseSP($id,@idis,@storeidis,@productidis,@sizeidis,@coloridis,@parcodeis,@buypriceis,@sellunitpriceis,@quantityis,@useridis,@sysdateis,@scdExcelParcodeis)";
$myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
$txt = $callSql . ";\r\n";
fwrite($myfile, $txt);
fclose($myfile);
if (!$mysqli->query("SET @idis=-10,@storeidis=-10,@productidis=-10,@sizeidis=-10,@coloridis=-10,@parcodeis=-10,@buypriceis=-10,@sellunitpriceis=-10,@quantityis=-10,@useridis=-10,@sysdateis=-10,@scdExcelParcodeis=-10") || !$mysqli->query($callSql)) {
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT @idis as id,@storeidis as storeid,@productidis as productid,@sizeidis as sizeid,@coloridis as colorid,@parcodeis as parcode,@buypriceis as buyprice,@sellunitpriceis as sellunitprice,@quantityis as quantity,@useridis as userid,@sysdateis as sysdate,@scdExcelParcodeis as scdExcelParcode"))) {
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$row = $res->fetch_assoc();
$rowAsObj = new stdClass();
foreach ($row as $key => $value) {
$rowAsObj->$key = $value;
}
return $rowAsObj;
}
}
public function markStoreDetailAsNOTInUse($id = 0, $type = 0) {
if ($type == 0) {//it is normal pro
$sql = 'UPDATE storedetail SET inUse = 0 WHERE storedetailid=' . $id;
} else {//it is sizecolor pro
$sql = 'UPDATE sizecolorstoredetail SET inUse = 0 WHERE id =' . $id;
}
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
}
?>