File: /home/mostafedeg/public_html/erp/models/mysql/ext/ProductcatMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'productcat'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class ProductcatMySqlExtDAO extends ProductcatMySqlDAO {
public function queryAllnothaveunit() {
$sql = 'SELECT productCatId FROM productcat where
productCatId not in(SELECT DISTINCT (
`productcatid`
)
FROM `productcatunit` ) ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getProductCatParents() {
$sql = 'SELECT *
FROM productcat
WHERE productCatParent = 0
AND conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getCategoriesWithoutProducts($productCatid = 0) {
$sql = 'SELECT productcat.*
FROM productcat
WHERE productCatId NOT IN (SELECT productCatId FROM product where conditions=0)
AND productcat.conditions = 0 and productCatId !=' . $productCatid . '
AND productcat.isOptic !=2';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getCategoryAndParentByCatId($catid) {
$sql = 'SELECT child.*, parent.productCatName as parentName
FROM productcat as child
left join productcat as parent
on child.`productCatParent` = parent.productCatId
WHERE child.productCatId = ?';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($catid);
return $this->getRow($sqlQuery);
}
public function getChilds($parentid, $serviceQS = '') {
$sql = 'SELECT *
FROM productcat
WHERE productCatParent = ?
and isOptic != 2 ' . $serviceQS . ' and productcat.conditions = 0';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($parentid);
return $this->getList($sqlQuery);
}
public function queryByParentExt($productCatId, $queryString = '') {
$sql = 'SELECT *
FROM productcat
WHERE productCatParent = ' . $productCatId . '
AND conditions = 0 ' . $queryString . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByParentExt2($productCatId) {
$sql = 'SELECT *
FROM productcat
WHERE productCatParent = ?
AND conditions !=2';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($productCatId);
return $this->getList($sqlQuery);
}
public function queryByConditionLimited($value, $start, $end) {
$sql = 'SELECT * FROM productcat WHERE conditions = ?
ORDER BY productCatId DESC limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryAllLimited($start, $end) {
$sql = 'SELECT * FROM productcat ORDER BY productCatId DESC limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllForShow() {
$sql = 'SELECT child.*, parent.productCatName as parentName
FROM productcat child
left join productcat parent on child.`productCatParent` = parent.productCatId
where child.isOptic != 2 ORDER BY child.productCatId DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateConditions($productcat) {
$sql = 'UPDATE productcat SET productCatDate = ?, userId = ?, conditions = ? WHERE productCatId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($productcat->productCatDate);
$sqlQuery->setNumber($productcat->userId);
$sqlQuery->setNumber($productcat->conditions);
$sqlQuery->setNumber($productcat->productCatId);
return $this->executeUpdate($sqlQuery);
}
public function returndelete($productCatid) {
$sql = 'UPDATE productcat SET conditions = 0 WHERE productCatId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($productCatid);
return $this->executeUpdate($sqlQuery);
}
public function updateExcelInfo($catExcelid, $productCatid) {
$sql = 'UPDATE productcat SET catExcelid = ' . $catExcelid . ' WHERE productCatId = ' . $productCatid . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
//query all product categories that have no child categories
public function queryAllChildren() {
$sql = "SELECT DISTINCT(a.productCatId) , a.*
FROM productcat a, productcat b
WHERE a.conditions = 0
AND a.productCatId <> b.productCatParent
AND b.conditions = 0
AND a.productCatParent <> 0
ORDER BY a.productCatId DESC ";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//query all product categories that have no child categories
public function queryAllnothavechildren($queryString = '') {
$sql = "SELECT *
FROM `productcat`
WHERE `productCatId` NOT
IN (
SELECT DISTINCT (
`productCatParent`
)
FROM productcat
)
and `conditions` =0
" . $queryString . "
ORDER BY productcat.productCatId DESC ";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllChildrenExt2() {
$sql = "SELECT DISTINCT(a.productCatId) , a.*
FROM productcat a, productcat b
WHERE a.conditions = 0
AND a.productCatId <> b.productCatParent
AND b.conditions = 0
ORDER BY a.productCatId DESC ";
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
//query last product categories in the hierarcy
public function queryAllChildrenExt() {
$sql = "SELECT DISTINCT(a.productCatId) , a.*
FROM productcat a, productcat b
WHERE a.conditions=0
AND a.productCatId <> b.productCatParent
AND b.conditions=0
AND a.productCatParent <> 0
AND a.productCatId NOT IN (
SELECT productCatParent
FROM productcat
WHERE conditions = 0
AND productCatParent <> 0
)
ORDER BY a.productCatId DESC ";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//query last product categories in the hierarcy
public function queryAllChildrenwithparentExt($productCatParent) {
$sql = "SELECT DISTINCT(a.productCatId) , a.*
FROM productcat a, productcat b
WHERE a.conditions=0
AND a.productCatId <> b.productCatParent
AND b.conditions=0
AND a.productCatParent <> 0
AND a.productCatId NOT IN (
SELECT productCatParent
FROM productcat
WHERE conditions = 0
AND productCatParent <> 0
)
and a.productCatParent =" . $productCatParent . "
ORDER BY a.productCatId DESC ";
// print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithParentAndCondition($productcatid) {
$sql = 'SELECT * FROM productcat where productCatParent = ' . $productcatid . ' and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductcatParentWithCondition() {
$sql = 'SELECT * FROM productcat where productCatParent = 0 and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductcatInProduct() {
$sql = 'SELECT * FROM productcat where productcat.productCatId IN (SELECT product.productCatId FROM product) and productcat.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductcatInProductmain() {
$sql = 'SELECT * FROM productcat where productcat.productCatParent = 0
and productcat.productCatId in (SELECT DISTINCT (`productCatParent`)
FROM `productcat` ) and productcat.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithNameAndId($productCatName, $productCatId, $limit) {
$sql = 'SELECT * FROM productcat
where productCatName LIKE "%' . $productCatName . '%"
and productCatId = ' . $productCatId . '
and conditions = 0 limit ' . $limit . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT child.*, parent.productCatName as parentName
FROM productcat child
left join productcat parent on child.`productCatParent` = parent.productCatId
WHERE child.productCatId = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function queryByParentAndProductcatNameExt($parent, $productCatName) {
$sql = 'SELECT *
FROM productcat
WHERE productCatParent = ?
AND productCatName = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($parent);
$sqlQuery->set($productCatName);
return $this->getList($sqlQuery);
}
public function queryByConditionEX($value) {
$sql = 'SELECT * FROM productcat WHERE conditions = ? and isOptic != 2';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByProductCatNameEX($value, $catid) {
$sql = 'SELECT * FROM productcat WHERE productCatName = ? and productCatParent=' . $catid;
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function getIdsOfCatsLike($productCatName) {
$sql = 'SELECT productcat.productCatId FROM productcat
where productCatName LIKE "%' . $productCatName . '%"
and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//# m7md
public function productcat_prices($catid) {
$sql = 'SELECT buypricereal , buytotal , buyhalf , buypart, price4,price5,price6,price7,price8
FROM productcat WHERE productCatId = ?';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($catid);
return $this->getRow($sqlQuery);
}
public function queryByProductCatParentLimited($value) {
$sql = 'SELECT productcat.productCatId FROM productcat WHERE productCatParent = ? limit 1';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryAllIn($ids) {
$sql = "SELECT productCatId,productCatName FROM productcat WHERE productCatId in($ids)";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getCategoriesCount() {
$sql = 'SELECT count(productCatId) as productCatId
FROM productcat where isOptic != 2';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllOrderedLimitedForCURL($start, $end, $sqlQuery = '') {
$sql = 'SELECT productcat.*
FROM productcat where isOptic != 2 ' . $sqlQuery . ' order by productCatId asc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllForCURLSyncAndLock() {
$sql = 'SELECT productcat.*,onlinetempcategory.edited
FROM productcat
join onlinetempcategory on onlinetempcategory.catid = productcat.productCatId
where isOptic != 2 FOR UPDATE';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getExcelInfo($queryString) {
$sql = 'SELECT productcat.productCatId,productcat.catExcelid, productcat.productCatName
FROM productcat
' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function get3categoriesOne($startDate, $endDate) {
$sql = 'SELECT productcat.productCatId, productcat.productCatName ,sellbilldetail.sellbilldetailcatid ,sellbilldetail.sellbilldetaildate ,sum(sellbilldetail.sellbilldetailprice) as catExcelid
from productcat
join sellbilldetail on productcat.productCatId = sellbilldetail.sellbilldetailcatid
where sellbilldetail.sellbilldetailcatid IN (SELECT category_id FROM resturantcategory WHERE id IN (1,2,3))
and sellbilldetail.sellbilldetaildate >= "' . $startDate . '" and sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
group By productcat.productCatId ORDER BY `productcat`.`productCatId` DESC limit 3';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllQueryStringSimple($queryString = '', $limit = '') {
$sql = 'SELECT * FROM productcat
where conditions = 0 ' . $queryString . $limit;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}