File: /home/mostafedeg/public_html/erp/models/mysql/ext/ProductMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'product'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class ProductMySqlExtDAO extends ProductMySqlDAO {
public function queryByConditionsExtLimited($value, $start, $end) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.conditions = ?
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function GetFinalProduct($name) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
AND product.type = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
//print_r("ddddddddddddddddddddddddd");
return $this->getList($sqlQuery);
}
public function GetRawMaterials($name = '', $queryString = "") {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getFinalProductsWithProductionRate($name) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
JOIN productionrate
ON productionrate.finalName = product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
and productionrate.conditions = 0
AND product.type = 0
group by product.productId'; //group by is m=needed in productionExecutionController
$sqlQuery = new SqlQuery($sql);
//print($sql);
//print_r("ddddddddddddddddddddddddd");
return $this->getList($sqlQuery);
}
public function getFinalProductsWithProductionRateGrouped($name) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
JOIN productionrate
ON productionrate.finalName = product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
and productionrate.conditions = 0
AND product.type = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
//print_r("ddddddddddddddddddddddddd");
return $this->getList($sqlQuery);
}
public function updatesortby($productid, $sortby) {
$sql = 'UPDATE product SET sortby = ' . $sortby . ' WHERE productId = ' . $productid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getFinalProductsWithOutProductionRate($name) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
LEFT JOIN productionrate
ON productionrate.finalName = product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
AND product.type = 0
AND productionrate.finalName IS NULL';
$sqlQuery = new SqlQuery($sql);
//print($sql);
//print_r("ddddddddddddddddddddddddd");
return $this->getList($sqlQuery);
}
public function getFinalProductsWithOutProductionRateSC($name, $storeid, $queryString = '', $limitString = '', $controlNameSearch = 1) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
}
$sql = 'SELECT SQL_CACHE product.*, productcat.productCatName ,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName,product.weightedDiscount,selldiscountpercent,sellpercenttype
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on storedetail.productid=product.productId
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
LEFT JOIN productionrate
ON (productionrate.finalName = product.productId and productionrate.sizeid=sizecolorstoredetail.sizeid and productionrate.colorid=sizecolorstoredetail.colorid)
WHERE
' . $queryStringNameSearch . '
and (if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' and storedetail.unittype=0 ,sizecolorstoredetail.storeid=' . $storeid . ')
or product.isService = 1)
AND product.conditions = 0
and productcat.conditions = 0
AND product.type = 0
AND productionrate.finalName IS NULL
' . $queryString . '
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
' . $limitString;
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function queryAllLimited($start, $end) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimple($start, $end) {
$sql = 'SELECT *
FROM product order by productId desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimpleForCurlNormal($start, $end, $sqlquery = '', $joinConditionWithStore = '') {
$sql = 'SELECT product.*,SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
group by storedetail.productid
order by productId asc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimpleForCurlNormal2($sqlquery = '', $joinConditionWithStore = '') {
$sql = 'SELECT product.*,SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
join onlinetempstoredetail on onlinetempstoredetail.productid = product.productId
left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
group by storedetail.productid
order by productId asc FOR UPDATE';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimpleForCurlNormal3($limit, $sqlquery = '', $joinConditionWithStore = '') {
$sql = 'SELECT product.*,SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
group by storedetail.productid
order by productId asc
limit ' . $limit . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllSimpleForCurlNormalSyncAndLock($sqlquery = '', $joinConditionWithStore = '') {
$sql = 'SELECT product.*,onlinetempproduct.edited,SUM(storedetail.productquantity) AS quantity
FROM product
join onlinetempproduct on onlinetempproduct.productid = product.productId
left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery . '
group by product.productId';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimpleForCurlOptic($start, $end, $sqlquery = '') {
$sql = 'SELECT product.*,productcat.productCatParent,productcat.productCatName,productcat.logo as catlogo
FROM product
join productcat on productcat.productCatId = product.productCatId
where product.isOptic = 2 and product.conditions=0 and product.isService=0 and product.type=0
and productcat.isOptic = 2 and productcat.conditions=0 ' . $sqlquery . '
order by productId desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllSimpleForCurlOpticSyncAndLock($sqlquery = '') {
$sql = 'SELECT product.*,productcat.productCatParent,productcat.productCatName,productcat.logo as catlogo,onlinetempproduct.edited
FROM product
join productcat on productcat.productCatId = product.productCatId
join onlinetempproduct on onlinetempproduct.productid = product.productId
where product.isOptic = 2 and product.conditions=0 and product.isService=0 and product.type=0
and productcat.isOptic = 2 and productcat.conditions=0 ' . $sqlquery . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedLimitedSimpleInStore($storeid, $start, $end) {
$sql = 'SELECT product.*,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
FROM product
join storedetail on storedetail.productid = product.productId
left join sizecolorstoredetail on sizecolorstoredetail.productid = product.productId
where if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' ,sizecolorstoredetail.storeid=' . $storeid . ')
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
order by productId desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getProductsCount() {
$sql = 'SELECT count(productId) as productId
FROM product ';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
//it gets the count of products that is not optic
public function getProductsCountForCURLNormal($sqlquery = '', $joinConditionWithStore = '') {
$sql = 'SELECT count(product.productId) as productId
FROM product
left JOIN storedetail ON (storedetail.productid = product.productId ' . $joinConditionWithStore . ')
where isOptic != 2 and conditions=0 and isService=0 and type=0 ' . $sqlquery;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
//it gets the count of products that is optic from productcat table
public function getProductsCountForCURLOptic($sqlquery) {
$sql = 'select count(productCatId) as productId FROM productcat where isOptic = 2 and conditions = 0 ' . $sqlquery;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllForShow() {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllForShow2($queryString = '') {
$sql = 'SELECT product.productId,product.productName, productcat.productCatName,product.isOptic,product.conditions,product.productCatId
, product.productBuyPrice,product.productSellAllPrice,product.productSellHalfPrice,product.productSellUnitPrice , product.logo , product.productDescription,
product.parcode,product.logo1,product.logo2,product.logo3,product.logo4,product.logo5,product.logo6,product.logo7,product.sortby,product.price4,product.price5,product.price6,product.price7,product.price8
FROM product
join productcat
on productcat.productCatId = product.productCatId
join storedetail on product.productId = storedetail.productid
WHERE productcat.isOptic != 2
and product.isOptic != 2
' . $queryString . '
union
SELECT product.productCatId as productId,product.productCatName as productName ,cat.productCatName as productCatName,product.isOptic,product.conditions,cat.productCatId as productCatId
,-1 as productBuyPrice ,-1 as productSellAllPrice,-1 as productSellHalfPrice,-1 as productSellUnitPrice , product.logo,product.productCatDescription as productDescription
,product.opticServices,-1 as logo1 ,-1 as logo2 ,-1 as logo3 ,-1 as logo4 ,-1 as logo5,-1 as logo6,-1 as logo7,-1 as sortby,-1 as price4,-1 as price5,-1 as price6,-1 as price7,-1 as price8
FROM productcat as product
join productcat as cat
on cat.productCatId = product.productCatParent
join storedetail on product.productCatId = storedetail.productid
where product.isOptic = 2 ' . $queryString . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT product.*,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
left JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ?
group by storedetail.productid
';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
// print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function loadExt2($id, $ids = "0") {
$sql = 'SELECT product.productId,concat(productcat.productCatName ,"/",product.productName) as productName,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
join productcat
on productcat.productCatId = product.productCatId
WHERE product.productId in( ' . $id . ')
and product.productId not in(' . $ids . ')
group by storedetail.productid
';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getProductQuantity($id, $sqlQuery = "") {
$sql = 'SELECT product.*,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ?
' . $sqlQuery . '
group by storedetail.productid
';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
// print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function loadExtforcard($id, $storeId) {
$sql = 'SELECT product.*, storedetail.productquantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ? and storedetail.storeId=' . $storeId . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
//print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function loadExtByProductId($id) {
$sql = 'SELECT product.*, storedetail.productquantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ? ';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
//print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function loadExtByProductId2($id) {
$sql = 'SELECT product.*, sum(storedetail.productquantity) as productquantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ?
group by product.productId';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
//print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function loadEX($id) {
$sql = 'SELECT * FROM product WHERE productId = ? and conditions =0';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function updateExt($product) {
$sql = 'UPDATE product SET productName = ?, productDescription = ?, productCatId = ?, conditions = ?, userId = ? WHERE productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($product->productName);
$sqlQuery->setString2($product->productDescription);
$sqlQuery->setNumber($product->productCatId);
$sqlQuery->setNumber($product->conditions);
$sqlQuery->setNumber($product->userId);
$sqlQuery->setNumber($product->productId);
return $this->executeUpdate($sqlQuery);
}
public function updateWeightedDiscount($weightedDiscount, $productid) {
$sql = 'UPDATE product SET weightedDiscount = ' . $weightedDiscount . ' WHERE productId = ' . $productid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryByProductCatIdExt($productCatId) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
left join productcat
on productcat.productCatId = product.productCatId
WHERE product.productCatId = ' . $productCatId . '
and product.conditions = 0
order by productId desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdInExt($productCatId, $querystore = '', $productQuery = '') {
$sql = 'SELECT SQL_CACHE product.productId,product.productName, productcat.productCatName,product.isOptic,product.hasSizeAndColor,product.conditions,product.productCatId
, product.productBuyPrice,product.productSellAllPrice,product.productSellHalfPrice,product.productSellUnitPrice , product.logo , product.productDescription,
product.parcode,product.logo1,product.logo2,product.logo3,product.logo4,product.logo5,product.logo6,product.logo7,product.reviewType,product.online,product.updatebyuser,product.price4,product.price5,product.price6,product.price7,product.price8
FROM product
join productcat
on productcat.productCatId = product.productCatId
join storedetail on product.productId = storedetail.productid
WHERE product.productCatId in( ' . $productCatId . ') ' . $productQuery . '
and productcat.isOptic != 2
and product.isOptic != 2
' . $querystore . '
union
SELECT product.productCatId as productId,product.productCatName as productName ,cat.productCatName as productCatName,product.isOptic,0 as hasSizeAndColor,product.conditions,cat.productCatId as productCatId
,-1 as productBuyPrice ,-1 as productSellAllPrice,-1 as productSellHalfPrice,-1 as productSellUnitPrice , product.logo,product.productCatDescription as productDescription
,product.opticServices,-1 as logo1 ,-1 as logo2 ,-1 as logo3 ,-1 as logo4 ,-1 as logo5,-1 as logo6,-1 as logo7 ,-1 as reviewType ,-1 as online,-1 as updatebyuser,-1 as price4,-1 as price5,-1 as price6,-1 as price7,-1 as price8
FROM productcat as product
join productcat as cat
on cat.productCatId = product.productCatParent
join storedetail on product.productCatId = storedetail.productid
where product.productCatId in( ' . $productCatId . ')
and product.isOptic = 2
' . $querystore . '';
$sqlQuery = new SqlQuery($sql);
// echo $sql;
return $this->getList($sqlQuery);
}
public function queryByProductCatIdInExts($barcode) {
$sql = 'SELECT * , product.logo
FROM product
join productcat
on productcat.productCatId = product.productCatId where 1 and product.productId = ' . $barcode;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdInExts2($barcode) {
$sql = 'SELECT * , product.logo
FROM product
join productcat
on productcat.productCatId = product.productCatId where 1 and product.parcode = "' . $barcode . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByProductparentCatIdExt($productCatId) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
join productcat
on productcat.productCatId = product.productCatId
WHERE product.productCatId in (select productCatId from productcat where productCatParent =' . $productCatId . ' )
order by productId desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdlimted($start, $end, $productCatId) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
join productcat
on productcat.productCatId = product.productCatId
WHERE product.productCatId = ' . $productCatId . '
order by productId desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
////////////////////////////////////////////////////////////////////
public function queryByProductCatIdNotdeleted($productCatId) {
$sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity
FROM product
join productcat
on productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.productCatId = ' . $productCatId . '
AND product.conditions = 0
order by productId desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatnotdel($value) {
$sql = 'SELECT * FROM product WHERE productCatId = ? and product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdNotdeletedExt($productCatId) {
$sql = 'SELECT product.productid
FROM product
join productcat
on productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.productCatId = ' . $productCatId . '
AND product.conditions = 0
order by productId desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdNotdeletedLimited($productCatId, $start, $end) {
$sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity
FROM product
join productcat
on productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.productCatId = ' . $productCatId . '
AND product.conditions = 0
order by productId desc
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
public function queryProductsWithBuyPrice() {
$sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity
FROM product
join productcat
on productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.conditions = 0
order by productId desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
/////////////////////////////////////
public function updateExcelInfo($proExcelid, $proExcelParcode, $productId) {
$sql = 'UPDATE product SET proExcelid =' . $proExcelid . ',proExcelParcode="' . $proExcelParcode . '" WHERE productId =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getExcelInfo($queryString) {
$sql = 'SELECT product.productId,product.productName,product.proExcelid,product.proExcelParcode
FROM product
' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updatehasSizeColor($hasSizeAndColorVal, $productId) {
$sql = 'UPDATE product SET hasSizeAndColor =' . $hasSizeAndColorVal . ' WHERE productId =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function deletetemp($productId) {
$sql = 'UPDATE product SET conditions =1 WHERE productId =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function deletefinally($productId) {
$sql = 'UPDATE product SET conditions =2 WHERE productId =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function returndelete($productId) {
$sql = 'UPDATE product SET conditions =0 WHERE productId =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
//used in buy bill
public function queryAllExt() {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryAllProducts($querryString = '', $limitQs = '') {
$sql = 'SELECT DISTINCT(product.productCatId) as productCatId, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.conditions = 0 and productcat.conditions = 0 ' . $querryString . $limitQs;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllProducts2() {
$sql = 'SELECT DISTINCT(product.productCatId) as productCatId, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.conditions = 0 and productcat.conditions = 0 and productcat.isOptic != 2';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//used in buy bill
public function queryAllCategories() {
$sql = 'SELECT DISTINCT(product.productCatId), productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE productcat.conditions = 0';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//used in buy bill
public function queryAllCategoriesParent() {
$sql = 'SELECT DISTINCT(product.productCatId), cat.productCatName,catParent.productCatName as catParentName
FROM product
JOIN productcat cat ON product.productCatId = cat.productCatId
JOIN productcat catParent ON cat.productCatParent = catParent.productCatId
WHERE cat.conditions = 0';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllCategoriesParentLeftJoin() {
$sql = 'SELECT DISTINCT(product.productCatId), cat.productCatName,catParent.productCatName as catParentName
FROM product
JOIN productcat cat ON product.productCatId = cat.productCatId
Left JOIN productcat catParent ON cat.productCatParent = catParent.productCatId
WHERE cat.conditions = 0 and product.conditions =0';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
// public function getLastLevelCats($productCatName) {
// $sql = 'SELECT DISTINCT(cat.productCatId), cat.productCatName
// FROM product
// right JOIN productcat cat ON product.productCatId = cat.productCatId
// left JOIN productcat catParent ON cat.productCatParent = catParent.productCatId
// WHERE cat.conditions = 0 and cat.productCatId IS NOT NULL and catParent.productCatId IS NULL and cat.productCatName LIKE "%' . $productCatName . '%"';
//
// print_r($sql . '<br>');
// $sqlQuery = new SqlQuery($sql);
// return $this->getList($sqlQuery);
// }
public function queryWithParcode($parcode) {
$sql = 'SELECT product.*,productcat.productCatName FROM product
join productcat on productcat.productCatId = product.productCatId
where product.conditions = 0
and product.parcode = "' . $parcode . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithParcodeExcel($parcode) {
$sql = 'SELECT product.*,productcat.productCatName FROM product
join productcat on productcat.productCatId = product.productCatId
where product.conditions = 0
and product.proExcelParcode = "' . $parcode . '"';
$sqlQuery = new SqlQuery($sql);
//echo $sql;
return $this->getRow($sqlQuery);
}
public function queryWithProUnitParcode($proUnitParcode) {
$sql = 'SELECT product.*,productcat.productCatName,productunit.productunitid,productunit.unitid,productunit.proUnitSellAllPrice,productunit.proUnitSellHalfPrice,productunit.proUnitSellUnitPrice,productunit.proUnitBuyPrice
FROM product
join productcat on productcat.productCatId = product.productCatId
join productunit on (product.productId = productunit.productid and productunit.proUnitParcode = "' . $proUnitParcode . '" and productunit.conditions = 0)
where product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//echo $sql;
return $this->getRow($sqlQuery);
}
public function queryWithSerialnumber($parcode) {
$sql = 'SELECT product.*
FROM product JOIN productserial
ON productserial.productid = product.productId
where productserial.serialnumber ="' . $parcode . '"
AND productserial.productserailid
IN (
SELECT MAX(productserailid) FROM productserial
WHERE productserial.productid = product.productId
ORDER BY productserailid DESC
)
and product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithSerialnumberAndDon($parcode, $don) {
$sql = 'SELECT product.*
FROM product JOIN productserial
ON productserial.productid = product.productId
where productserial.serialnumber ="' . $parcode . '"
AND productserial.don = ' . $don . ' AND productserial.del =0
AND productserial.productserailid
IN (
SELECT MAX(productserailid) FROM productserial
WHERE productserial.productid = product.productId
ORDER BY productserailid DESC
)
and product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithSerialnumberAndAvailable($parcode) {
$sql = 'SELECT product.*, productserial.productserailid, productserial.don
,productserial.sizeid,productserial.colorid,productserial.chassisNo,productserial.motorNo,productserial.theColor,size.name as sizeName,color.name as colorName
FROM product
JOIN productserial ON productserial.productid = product.productId
left join sizecolor as size on size.id = productserial.sizeid
left join sizecolor as color on color.id = productserial.colorid
where productserial.serialnumber = "' . $parcode . '"
AND productserial.don > 0 AND productserial.del =0
AND product.conditions = 0';
//print($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithSerialnumberEvenIfNotAvailable($parcode) {
$sql = 'SELECT product.*, productserial.productserailid, productserial.don, productcat.productCatName
,productserial.sizeid,productserial.colorid,productserial.chassisNo,productserial.motorNo,productserial.theColor,size.name as sizeName,color.name as colorName
FROM product
join productcat on productcat.productCatId = product.productCatId
JOIN productserial ON productserial.productid = product.productId
left join sizecolor as size on size.id = productserial.sizeid
left join sizecolor as color on color.id = productserial.colorid
where productserial.serialnumber = "' . $parcode . '"
AND productserial.del =0
AND product.conditions = 0';
//print($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function loadByProductId($productid) {
$sql = 'SELECT product.*, productcat.selldiscount, productcat.buydiscount,
productcat.discounttype,buypricereal
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
where product.productId = ' . $productid . '';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithCoditionsAndCatConditions() {
$sql = 'SELECT product.*, productcat.productCatName
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
where product.conditions = 0 and productcat.conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadProduct($id) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function loadProductExt($id) {
$sql = 'SELECT product.*, productcat.productCatName, storedetail.productquantity,
storedetail.storedetailid, storedetail.storeid
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
//print($sql);
return $this->getRow($sqlQuery);
}
public function getProductCatId($productId) {
$sql = 'SELECT product.productCatId
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.productId = ' . $productId . '';
$sqlQuery = new SqlQuery($sql);
//print($sql."<br />");
return $this->QuerySingleResult($sqlQuery);
}
public function queryWithProductcatIdAndConditions($productcatid) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
where product.conditions = 0 and productcat.conditions =0
and product.productCatId =' . $productcatid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExt($name, $limit) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
limit ' . $limit . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimit($name, $queryString = '', $controlNameSearch = 1, $limitQS = '') {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name,product.procode) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,product.procode) LIKE "%' . $name . '%" ';
}
$sql = 'SELECT SQL_CACHE product.*, productcat.productCatName,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName,selldiscountpercent,sellpercenttype
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left JOIN storedetail
ON storedetail.productid = product.productId
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
WHERE
' . $queryStringNameSearch . '
AND product.conditions = 0
and storedetail.unittype=0
and productcat.conditions = 0 ' . $queryString . '
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid ' . $limitQS;
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimitNoService($name, $storeidQS = '') {
$sql = 'SELECT product.*, productcat.productCatName,productquantity
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
AND product.isService = 0
' . $storeidQS . '
and productcat.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimitNoServiceNotCollective($name, $storeidQS = '', $storeidQSJoin = '', $controlNameSearch = 1, $limitQS = '') {
$storeidQS2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQS);
$storeidQSJoin2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQSJoin);
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
}
$sql = '
SELECT SQL_CACHE * from
((
SELECT product.productId,product.productName,product.productCatId,productquantity,product.productBuyPrice,productcat.productCatName,productcat.buypricereal
,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
,"" as sizeName,"" as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on (storedetail.productid=product.productId ' . $storeidQSJoin . ')
left join productingredients
on productingredients.productId=product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
AND product.isService = 0
' . $storeidQS . '
and productcat.conditions = 0
and product.hasSizeAndColor = 0
and productingredients.id IS NULL
) union (
SELECT product.productId,product.productName,product.productCatId,sizecolorstoredetail.quantity as productquantity,product.productBuyPrice, productcat.productCatName,productcat.buypricereal
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join productingredients
on productingredients.productId=product.productId
left join sizecolorstoredetail on (sizecolorstoredetail.productid = product.productId ' . $storeidQSJoin2 . ')
left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
left join sizecolor as color on color.id=sizecolorstoredetail.colorid
WHERE
' . $queryStringNameSearch . '
AND product.conditions = 0
AND product.isService = 0
' . $storeidQS2 . '
and product.hasSizeAndColor = 1
and productcat.conditions = 0
and productingredients.id IS NULL
)) as temp
GROUP BY productId,sizeid,colorid ' . $limitQS;
//echo $sql;
// -- group by product.productId
/* $sql = 'SELECT product.productId,product.productName,product.productCatId,productquantity,product.productBuyPrice, productcat.productCatName
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
left join productingredients
on productingredients.productId=product.productId
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
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
AND product.isService = 0
' . $storeidQS . '
and productcat.conditions = 0
and productingredients.id IS NULL'; */
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimitNotCollective($name, $storeidQS = '', $storeidQSJoin = '', $controlNameSearch = 1, $limitQS = '') {
$storeidQS2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQS);
$storeidQSJoin2 = str_replace('storedetail', 'sizecolorstoredetail', $storeidQSJoin);
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
}
$sql = '
SELECT SQL_CACHE * from
((
SELECT product.productId,product.productName,product.productCatId,productquantity,product.productBuyPrice,productcat.productCatName,productcat.buypricereal,
product.hasSizeAndColor,buydiscountpercent,0 as sizecolorstoredetailid,0 as sizeid,0 as colorid
,"" as sizeName,"" as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on (storedetail.productid=product.productId ' . $storeidQSJoin . ')
left join productingredients
on productingredients.productId=product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
' . $storeidQS . '
and productcat.conditions = 0
and product.hasSizeAndColor = 0
and productingredients.id IS NULL
) union (
SELECT product.productId,product.productName,product.productCatId,sizecolorstoredetail.quantity as productquantity,product.productBuyPrice, productcat.productCatName,productcat.buypricereal,
product.hasSizeAndColor,buydiscountpercent,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join productingredients
on productingredients.productId=product.productId
left join sizecolorstoredetail on (sizecolorstoredetail.productid = product.productId ' . $storeidQSJoin2 . ')
left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
left join sizecolor as color on color.id=sizecolorstoredetail.colorid
WHERE
' . $queryStringNameSearch . '
AND product.conditions = 0
' . $storeidQS2 . '
and product.hasSizeAndColor = 1
and productcat.conditions = 0
and productingredients.id IS NULL
)) as temp
GROUP BY productId,sizeid,colorid ' . $limitQS;
//echo $sql;
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimitdeleted($name) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 1';
$sqlQuery = new SqlQuery($sql);
print($sql);
return $this->getList($sqlQuery);
}
//search in the whole path
public function queryByNameandstoreidExtWithoutLimit2($name) {
$sql = 'SELECT product.*, productcat.productCatName,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on storedetail.productid=product.productId
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
WHERE
CONCAT_WS("/",product.parcode,product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%"
AND product.conditions = 0
AND productcat.conditions = 0
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimit2($name, $storeid, $queryString = '', $limitString = '', $controlNameSearch = 1) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name,product.procode) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,product.procode) LIKE "%' . $name . '%" ';
}
$sql = 'SELECT SQL_CACHE product.*, productcat.productCatName, productcat.selldiscount ,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName,product.weightedDiscount,productcat.buytotal,productcat.buyhalf,productcat.buypart,productBuyPrice,selldiscountpercent,sellpercenttype
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on storedetail.productid=product.productId
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
WHERE
' . $queryStringNameSearch . '
and (if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' and storedetail.unittype=0 ,sizecolorstoredetail.storeid=' . $storeid . ')
or product.isService = 1)
AND product.conditions = 0
and productcat.conditions = 0
' . $queryString . '
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
' . $limitString;
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtWithoutLimit2Fatma($catid, $storeString) {
$sql = 'SELECT product.*, productcat.productCatName ,productquantity
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
WHERE product.productCatId = ' . $catid . '
' . $storeString . '
AND product.conditions = 0
and productcat.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameandstoreidExt($name, $limit, $storeid) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
WHERE (productName LIKE "%' . $name . '%"
OR productCatName LIKE "%' . $name . '%")
and storedetail.storeid=' . $storeid . '
AND product.conditions = 0
and productcat.conditions = 0
';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//search in the whole path
public function queryByNameandstoreidExtWithoutLimit($name, $storeid) {
$sql = 'SELECT product.*, productcat.productCatName,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
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
WHERE
CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%"
AND storedetail.storeid=' . $storeid . '
AND product.conditions = 0
AND productcat.conditions = 0
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//search in the whole path
public function queryByNameandstoreidExtWithoutLimitNoService($name, $storeid, $controlNameSearch = 1) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName) LIKE "%' . $name . '%" ';
}
$sql = 'SELECT SQL_CACHE product.*, productcat.productCatName,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on storedetail.productid=product.productId
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
WHERE
' . $queryStringNameSearch . '
AND if(sizecolorstoredetail.id is null , storedetail.storeid=' . $storeid . ' and storedetail.unittype=0 ,sizecolorstoredetail.storeid=' . $storeid . ')
AND product.conditions = 0
AND product.isService = 0
AND productcat.conditions = 0
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByNameandstoreidExtWithoutLimitNoService2($name, $storeid, $procat) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND storedetail.storeid=' . $storeid . '
AND productcat.productCatId =' . $procat . '
AND product.conditions = 0
AND product.isService = 0
AND productcat.conditions = 0
';
//print_r($sql."<br>");
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updatebarcode($product) {
$sql = 'UPDATE product SET parcode = ? WHERE productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($product->parcode);
$sqlQuery->setNumber($product->productId);
return $this->executeUpdate($sqlQuery);
}
public function updateTreeId($productid, $treeId) {
$sql = 'UPDATE product SET treeId = ' . $treeId . ' WHERE productId = ' . $productid;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryWithParcodeExceptId($parcode, $id) {
$sql = 'SELECT * FROM product
where product.parcode = "' . $parcode . '" and productId != ' . $id;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function updateproductBuyPrice($productId, $productBuyPrice) {
$sql = 'UPDATE product SET productBuyPrice = "' . $productBuyPrice . '" WHERE productId = ' . $productId . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateproductBuyAndSellPriceByValue($productId, $productBuyPrice, $diff) {
$sql = 'UPDATE product SET productBuyPrice = ' . $productBuyPrice . '
,productSellAllPrice=productSellAllPrice+' . $diff . ',productSellUnitPrice=productSellUnitPrice+' . $diff . ',productSellHalfPrice=productSellHalfPrice+' . $diff . '
WHERE productId =' . $productId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateproductBuyAndSellPriceByPercentage($productId, $productBuyPrice, $percentage) {
$sql = 'UPDATE product SET productBuyPrice = ' . $productBuyPrice . '
,productSellAllPrice=productSellAllPrice*' . $percentage . ',productSellUnitPrice=productSellUnitPrice*' . $percentage . ',productSellHalfPrice=productSellHalfPrice*' . $percentage . '
WHERE productId =' . $productId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryAllproductwithserail() {
$sql = 'SELECT * FROM product where productId in(SELECT `productid`
FROM `productserial` )';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdEX($value) {
$sql = 'SELECT product.* , productcat.productCatName
FROM product
left join productcat
on productcat.productCatId=product.productCatId
WHERE product.productCatId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByAllCategories($queryString = '') {
$sql = 'SELECT product.productId,product.productName , productcat.productCatName , productcat.productCatId
FROM product
left join productcat on productcat.productCatId=product.productCatId
where 1 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function checkForProductNameInOneCategory($name, $catid) {
$sql = 'SELECT * FROM product WHERE productName LIKE "%' . $name . '%" AND productCatId = ' . $catid . ' and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function checkForProductNameInOneCategory2($name, $catid) {
$sql = 'SELECT * FROM product WHERE productName = "' . $name . '" AND productCatId = ' . $catid . ' and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySumQuantityWithProductCatId($productCatId, $querysearch = '') {
$sql = 'SELECT product.*, productcat.productCatName, productcat.buydiscount,productcat.selldiscount,productcat.discounttype,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product JOIN productcat
ON productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.productCatId = ' . $productCatId . $querysearch . '
AND product.conditions = 0
group by storedetail.productid
order by productId desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySumQuantity($querysearch = '') {
$sql = 'SELECT product.*, productcat.productCatName,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product JOIN productcat
ON productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.conditions = 0 ' . $querysearch . '
group by storedetail.productid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySumQuantityWithBuyPrice() {
$sql = 'SELECT product.*, productcat.productCatName,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product JOIN productcat
ON productcat.productCatId = product.productCatId
JOIN storedetail
ON storedetail.productid = product.productid
WHERE product.conditions = 0
group by storedetail.productid
order by productId desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadForReport($id) {
$sql = 'SELECT productSellUnitPrice, productSellAllPrice, productSellHalfPrice
FROM product
WHERE product.productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function getName($id) {
$sql = 'SELECT concat(product.productName,"/",productcat.productCatName) as productName FROM product JOIN productcat
ON product.productCatId = productcat.productCatId WHERE productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function getProductBuyPriceById($id) {
$sql = 'SELECT productBuyPrice FROM product WHERE productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->querySingleResult($sqlQuery);
}
public function queryWithStoreId22($storeid, $querystring = '') {
$sql = 'SELECT Distinct sum(storedetail.productquantity) as productquantity,
store.storeName, product.productId,product.productName,sum(product.limitamount)as limitamount
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 limitamount > productquantity
and storedetail.storeid = ' . $storeid . ' ' . $querystring . '
group by product.productId
order by storedetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallproduct() {
$sql = 'SELECT Distinct sum(storedetail.productquantity) as productquantity,
store.storeName, product.productId,product.productName,sum(product.limitamount) as limitamount
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 limitamount >productquantity
group by product.productId
order by storedetailid desc
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithParcodeandid($parcode, $productid) {
$sql = 'SELECT * FROM product
where product.productId != ' . $productid . '
and product.parcode = "' . $parcode . '"';
//print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllrepateparcode($end) {
$sql = 'SELECT product.parcode , `parcode` , COUNT( parcode ) AS ttttt
FROM product
GROUP BY parcode
HAVING ttttt >1
limit 0,' . $end . '';
// print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAlldeletedproduct($end) {
$sql = 'SELECT product.*
FROM product
WHERE `conditions` = 1
limit ' . $end . ',50';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAlldeletedproductwithid($productId) {
$sql = 'SELECT product.*
FROM product
WHERE `productId` = ' . $productId . '
';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllnothaveunit() {
$sql = 'SELECT productId FROM product where productId not in(SELECT DISTINCT (
`productid`
)
FROM `productunit` ) LIMIT 0 ,1000';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllnotinstor() {
$sql = 'SELECT productId FROM product where productId not in(SELECT DISTINCT (
`productid`
)
FROM `storedetail` )';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryproductbyquerystring($queryString) {
$sql = 'SELECT *
FROM product
' . $queryString . ' ';
/* $sql = 'SELECT product.* , `parcode` , COUNT( parcode ) AS ttttt
FROM product
' . $queryString . '
GROUP BY parcode
HAVING ttttt >1
';
*/
// print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function queryproductdeletedbyquerystring($queryString) {
$sql = 'SELECT *
FROM product where conditions = 1
' . $queryString . ' ';
/* $sql = 'SELECT product.* , `parcode` , COUNT( parcode ) AS ttttt
FROM product
' . $queryString . '
GROUP BY parcode
HAVING ttttt >1
';
*/
//print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function updateDailyEntry($rid, $productId) {
$sql = 'UPDATE product SET dailyentryId =' . $rid . ' WHERE productId =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getCatProducts($catid) {
$sql = 'SELECT if(sizecolorstoredetail.id is null ,product.productId,concat("hasSizeColor",product.productId,"-",sizecolorstoredetail.sizeid,"-",sizecolorstoredetail.colorid)) as productId
,CONCAT_WS("/",product.productName,size.name,color.name) as productName,product.isOptic,product.conditions
FROM product
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
where productCatId = ' . $catid . '
and product.conditions = 0
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
union
SELECT product.productId,CONCAT_WS("/",product.productName,productcat.productCatName) as productName,product.isOptic,product.conditions
FROM productcat
join product on product.productCatId = productcat.productCatId
where productCatParent = ' . $catid . '
and product.isOptic = 2 and product.conditions = 0'; /* SELECT product.productCatId,product.productCatName,product.isOptic,product.conditions
FROM productcat as product */
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getSizeColor($catid) {
$sql = 'SELECT product.productId,product.productName,product.isOptic,product.conditions
FROM product
where productCatId = ' . $catid . '
and product.isOptic = 2';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getSizeColor2($catid, $storeid) {
$sql = 'SELECT product.productId,product.productName,product.isOptic,product.conditions
FROM product
join storedetail
on storedetail.productid=product.productId
where productCatId = ' . $catid . '
AND storedetail.storeid=' . $storeid . '
and product.isOptic = 2';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
/*
public function queryAllrepateparcode($end) {
$sql = 'SELECT product.* , `parcode` , COUNT( parcode ) AS ttttt
FROM product
GROUP BY parcode
HAVING ttttt >1
limit 0,' . $end . '';
print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
*/
public function queryByProductCatIdIn($IDS, $queryString = "") {
$sql = 'SELECT * FROM product WHERE productCatId in (' . $IDS . ') ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
//print_r($sql . "<br/>");
return $this->getList($sqlQuery);
}
public function queryByProductCatIdInNotService($IDS) {
$sql = 'SELECT * FROM product WHERE productCatId in (' . $IDS . ') and isService = 0';
$sqlQuery = new SqlQuery($sql);
// print_r($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdNotService($value) {
$sql = 'SELECT * FROM product WHERE productCatId = ? and isService = 0';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdWithCount($value) {
$sql = 'SELECT product.*,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId WHERE productCatId = ' . $value . ' and product.conditions = 0 group by storedetail.productid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadProductCatNameOnly($id) {
$sql = 'SELECT productcat.productCatName
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.productId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->querySingleResult($sqlQuery);
}
public function queryByProductNameEX($value, $catid) {
$sql = 'SELECT *
FROM product
WHERE productName = ?
and productCatId=' . $catid;
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function getIdsOfProductsLike($name) {
$sql = 'SELECT product.productId
FROM product
WHERE
product.productName LIKE "%' . $name . '%"
AND product.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByIdsInExt($productsIds, $storeid) {
$sql = 'SELECT product.*, productcat.productCatName ,productquantity
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
join storedetail
on storedetail.productid=product.productId
WHERE product.productId in(' . $productsIds . ')
and storedetail.storeid=' . $storeid . '
AND product.conditions = 0
and productcat.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function getPriceOfOptic($catid) {
$sql = 'SELECT product.productSellAllPrice,product.productSellHalfPrice,product.productSellUnitPrice , product.productBuyPrice
,product.lastbuyprice,product.meanbuyprice,product.lastbuyprice_withDiscount,product.meanbuyprice_withDiscount,product.overAllAveragePrice
FROM product
WHERE product.productCatId =' . $catid . '
AND product.conditions = 0
limit 1';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getRow($sqlQuery);
}
public function queryByParcodeEX($value) {
$sql = 'SELECT product.*,concat(product.productName, "/",productcat.productCatName) as productName,product.productDescription
FROM product
join productcat on productcat.productCatId = product.productCatId
WHERE parcode = "' . $value . '"';
$sqlQuery = new SqlQuery($sql);
//echo $sql;
return $this->getList($sqlQuery);
}
public function loadExtNew($id) {
$sql = 'SELECT product.*,
SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId = ?
and storedetail.unittype = 0
group by storedetail.productid
';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
// print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function queryByNameExtWithoutLimitdeleted_2($name, $queryString) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
' . $queryString . '
AND product.conditions = 1';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryByCatInMenuEX($catId) {
$sql = 'SELECT *
FROM product
where product.productCatId = ' . $catId . ' and inMenu = 0 and product.conditions = 0 ORDER BY product.productCatId, product.sortby ASC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdLimited($productCatId) {
$sql = 'SELECT product.productId
FROM product
WHERE productCatId = ' . $productCatId . ' limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function isParcodeRepated($value, $query) {
$sql = 'SELECT productId FROM product WHERE parcode = ? ' . $query;
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function queryAllDistinctWithQueryString($queryString) {
$sql = 'SELECT product.*
FROM product
where product.conditions = 0 ' . $queryString; //DISTINCT
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllDistinctWithQueryStringUnion($queryString1, $queryString2) {
$sql = '(
SELECT product.*,0 as sizeid,0 as colorid,"" as sizeName,"" as colorName,0 as sizeColorParcode
FROM product
where product.conditions = 0 ' . $queryString1 . '
)
union
(
SELECT product.*,sizeid,colorid,size.name as sizeName,color.name as colorName,sizecolorstoredetail.parcode as sizeColorParcode
FROM product
join sizecolorstoredetail on product.productId = sizecolorstoredetail.productid
left join sizecolor as size on size.id=sizecolorstoredetail.sizeid
left join sizecolor as color on color.id=sizecolorstoredetail.colorid
where product.conditions = 0 ' . $queryString2 . '
)
'; //DISTINCT
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getProductIdByParcode($parcodes) {
$sql = "SELECT productId,parcode FROM product where parcode in ($parcodes)";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getParcodeByProductIds($productIds) {
$sql = "SELECT productId,parcode FROM product where productId in ($productIds)";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//azmy
public function loadByidandstoreid($id, $querystore) {
$sql = 'SELECT * ,productcat.productCatName,sum(productquantity) as productquantity FROM product
JOIN productcat ON product.productCatId = productcat.productCatId
join storedetail on product.productId = storedetail.productid WHERE product.productId = ' . $id . ' ' . $querystore;
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryByProductCatIdIncludingOptics($value) {
$sql = 'SELECT * FROM product WHERE productCatId = ' . $value . '
union
SELECT product.*
FROM product
join productcat opticProduct on product.productCatId = opticProduct.productCatId
where product.isOptic = 2 and product.conditions = 0
and opticProduct.productCatParent = ' . $value . '';
//var_dump($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateBulk($sql) {
$sqlQuery = new SqlQuery($sql);
$affected_rows = $this->executeUpdate($sqlQuery, 1);
return $affected_rows;
}
public function updateNullPricesToBuyPrice() {
$sql = 'UPDATE product SET lastbuyprice=productBuyPrice,lastbuyprice_withDiscount=productBuyPrice,meanbuyprice=productBuyPrice,meanbuyprice_withDiscount=productBuyPrice where lastbuyprice is null';
$sqlQuery = new SqlQuery($sql);
$affected_rows = $this->executeUpdate($sqlQuery, 1);
return $affected_rows;
}
public function queryByNameExtWithoutLimitParcode2($name) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE
CONCAT_WS("/",product.parcode,productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function updateReview($productId) {
$sql = 'UPDATE `product` SET `reviewType` = 1 where `productId` = ' . $productId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function runSelectQuery($sql) {
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadProductbyparcod($id) {
$sql = 'SELECT product.*, productcat.productCatName
FROM product JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE product.productId like "%' . $id . '%"';
$sqlQuery = new SqlQuery($sql);
// $sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function queryByProductCatIdEX2($productCatId, $queryString) {
$sql = 'SELECT product.*,sum(productquantity) as productquantity
FROM product
JOIN storedetail ON storedetail.productid = product.productId
WHERE productCatId = ' . $productCatId . ' ' . $queryString . '
group by product.productId';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllSimpleQueryString($queryString) {
$sql = 'SELECT *
FROM product
WHERE 1 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllWithSizeColorData($queryString = '') {
$sql = 'SELECT product.*, productcat.productCatName,if(sizecolorstoredetail.id is null ,productquantity,quantity) as productquantity
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left JOIN storedetail
ON storedetail.productid = product.productId
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
WHERE 1 ' . $queryString . ' ';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function updateCollectiveProductsPricesAsSumOfIngridientsCost($queryString = '') {
$sql = 'UPDATE product
JOIN (
select productingredients.productId
,sum(productnumber * quantity * productBuyPrice) as productBuyPrice
,sum(productnumber * quantity * lastbuyprice) as lastbuyprice
,sum(productnumber * quantity * lastbuyprice_withDiscount) as lastbuyprice_withDiscount
,sum(productnumber * quantity * meanbuyprice) as meanbuyprice
,sum(productnumber * quantity * meanbuyprice_withDiscount) as meanbuyprice_withDiscount
,sum(productnumber * quantity * overAllAveragePrice) as overAllAveragePrice
from product
join productingredients on product.productId = productingredients.ingridientId
join productunit on productunit.productunitid = productingredients.unitId
group by productingredients.productId
) as ingridientPrice ON ingridientPrice.productId = product.productId
SET product.productBuyPrice = ingridientPrice.productBuyPrice,
product.lastbuyprice = ingridientPrice.lastbuyprice,
product.lastbuyprice_withDiscount = ingridientPrice.lastbuyprice_withDiscount,
product.meanbuyprice = ingridientPrice.meanbuyprice,
product.meanbuyprice_withDiscount = ingridientPrice.meanbuyprice_withDiscount,
product.overAllAveragePrice = ingridientPrice.overAllAveragePrice';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->executeUpdate($sqlQuery);
}
public function get3productsOne($startDate, $endDate) {
$sql = 'SELECT product.productId,product.productName , storereport.storereportid, storereport.productid, storereport.storereporttype, storereport.productbefore, storereport.productafter
from product
join storereport on product.productId = storereport.productid where
storereport.productid IN (SELECT product_id FROM resturantcategory WHERE id IN (1,2,3))
and storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
ORDER BY `storereport`.`storereportid` DESC limit 3';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadSizeColor($productId, $sizeId = 0, $colorId = 0) {
$sizeId = (int) $sizeId;
$colorId = (int) $colorId;
$productId = (int) $productId;
$sql = 'SELECT product.*,size.name as sizeName,color.name as colorName,size.id as sizeid,color.id as colorid
FROM product
left join sizecolor as size on size.id=' . $sizeId . '
left join sizecolor as color on color.id=' . $colorId . '
WHERE product.productId = ' . $productId;
//var_dump($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function updateQueryString($sql) {
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryByNameExtWithoutStoreWithoutLimit($name, $queryString = '', $limitString = '', $controlNameSearch = 1) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,size.name,color.name,product.procode) LIKE "%' . $name . '%" ';
if ($controlNameSearch === 0) {
$queryStringNameSearch = ' CONCAT_WS("/",product.productName,productcat.productCatName,product.procode) LIKE "%' . $name . '%" ';
}
$sql = 'SELECT SQL_CACHE product.*, productcat.productCatName, productcat.selldiscount ,if(sizecolorstoredetail.id is null ,sum(productquantity),quantity) as productquantity
,sizecolorstoredetail.id as sizecolorstoredetailid,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
,size.name as sizeName,color.name as colorName,product.weightedDiscount,productcat.buytotal,productcat.buyhalf,productcat.buypart,productBuyPrice,selldiscountpercent,sellpercenttype
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
left join storedetail
on storedetail.productid=product.productId
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
WHERE
' . $queryStringNameSearch . '
AND product.conditions = 0
and productcat.conditions = 0
' . $queryString . '
GROUP BY product.productId,sizecolorstoredetail.sizeid,sizecolorstoredetail.colorid
' . $limitString;
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function queryByNameExtHasColorAndSize($name, $limitQS = '') {
$sql = 'SELECT product.productId,product.productCatId,product.productName,productcat.productCatName
FROM product
JOIN productcat
ON product.productCatId = productcat.productCatId
WHERE
CONCAT(product.productName, "/", productcat.productCatName) LIKE "%' . $name . '%"
AND product.conditions = 0
and productcat.conditions = 0
and product.hasSizeAndColor = 1' . $limitQS;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByOnlineCatIdExt($onlineCatId) {
$sql = 'SELECT product.*, onlinecat.name
FROM product
left join onlinecat
on onlinecat.id = product.onlinecatid
WHERE product.onlinecatid = ' . $onlineCatId . '
and product.conditions = 0
order by productId desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
public function queryBySearchFiltersIdExt($filterId) {
$sql = 'SELECT product.*, searchfilters.name
FROM product
left join searchfilters
on searchfilters.id = product.searchfiltersid
WHERE product.searchfiltersid = ' . $filterId . '
and product.conditions = 0
order by productId desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
}