File: /home/mostafedeg/public_html/erp/models/mysql/ext/BuybilldetailMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'buybilldetail'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class BuybilldetailMySqlExtDAO extends BuybilldetailMySqlDAO {
public function queryQuantityWithProduct($productId) {
$sql = 'SELECT Sum(buybilldetail.buybilldetailquantity) as sumQuantity
FROM buybilldetail JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.conditions = 0
and buybilldetail.buybilldetailproductid = ' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryQuantityWithProductAndDate($productId, $startDate, $endDate) {
$sql = 'SELECT Sum(buybilldetail.buybilldetailquantity) as sumQuantity
FROM buybilldetail JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.conditions = 0
and buybilldetail.buybilldetailproductid = ' . $productId . '
and buybill.buybilldate >= "' . $startDate . '" and buybill.buybilldate <= "' . $endDate . '"';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryQuantityWithProductAndDate2($productId, $startDate, $endDate, $queryString = '') {
// $sql = 'SELECT sum( buybilldetail.buybilldetailquantity )as buybilldetailquantity , buybilldetail.productunitid as productunitid
// FROM buybilldetail
// JOIN buybill ON buybilldetail.buybillid = buybill.buybillid
// where
// buybill.conditions = 0 and buybilldetail.buybilldetailcatid = ' . $productId . '
// and buybill.buybilldate >= "' . $startDate . '" and buybill.buybilldate <= "' . $endDate . '"';
$sql = 'SELECT buybilldetail.buybilldetailproductid as buybilldetailproductid ,
sum( buybilldetail.buybilldetailquantity )as buybilldetailquantity , buybilldetail.productunitid as productunitid
FROM buybilldetail
JOIN buybill ON buybilldetail.buybillid = buybill.buybillid
where buybilldetail.buybilldetailproductid = ' . $productId . ' and buybilldetail.buybilldetaildate >= "' . $startDate . '" and buybilldetail.buybilldetaildate <= "' . $endDate . '"
' . $queryString . '
group by ( buybilldetail.buybilldetailproductid ) ';
$sqlQuery = new SqlQuery($sql);
//print_r($sql);
return $this->getList($sqlQuery);
}
public function queryByDateAndProductCat($startDate, $endDate, $catId) {
$sql = 'SELECT buybilldetail.*, product.productName, SUM(buybilldetailquantity) as totQty
FROM buybilldetail , product
WHERE product.productCatId =' . $catId . '
AND buybilldetail.buybilldetaildate >= "' . $startDate . '" AND buybilldetail.buybilldetaildate <= "' . $endDate . '"
AND buybilldetail.buybilldetailproductid = product.productId
GROUP BY buybilldetail.buybilldetailproductid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryProductDetailsByDate($startDate, $endDate, $proId) {
$sql = 'SELECT buybilldetail.*, product.productName
FROM buybilldetail , product
WHERE buybilldetailproductid =' . $proId . '
AND buybilldetail.buybilldetaildate >= "' . $startDate . '" AND buybilldetail.buybilldetaildate <= "' . $endDate . '"
AND buybilldetail.buybilldetailproductid = product.productId
GROUP BY buybilldetail.buybilldetailproductid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByBuybillidExt($value) {
$sql = 'SELECT buybilldetail.*, product.productName, unit.unitName ,unit.unitid ,productunit.productnumber ,productcat.productCatName
,size.name as sizeName,color.name as colorName
FROM buybilldetail JOIN product
ON buybilldetail.buybilldetailproductid = product.productId left JOIN productunit
ON buybilldetail.productunitid = productunit.productunitid
left JOIN unit
ON unit.unitId = productunit.unitid
JOIN productcat
ON productcat.productCatId = product.productCatId
left join sizecolor as size on size.id = buybilldetail.sizeid
left join sizecolor as color on color.id = buybilldetail.colorid
WHERE buybillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditions($startDate, $endDate) {
$sql = 'SELECT buybilldetail.*
FROM buybilldetail JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.conditions = 0
and buybill.buybilldate
>= "' . $startDate . '" and buybill.buybilldate <= "' . $endDate . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsAndProductId($startDate, $endDate, $ProductId) {
$sql = 'SELECT buybilldetail.*
FROM buybilldetail JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.conditions = 0
and buybill.buybilldate
>= "' . $startDate . '" and buybill.buybilldate <= "' . $endDate . '"
and buybilldetailproductid =' . $ProductId . '';
$sqlQuery = new SqlQuery($sql);
//print_r($sql.'<br>');
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsAndProductIdandstor($startDate, $endDate, $ProductId, $storid) {
$sql = 'SELECT buybilldetail.*
FROM buybilldetail JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.conditions = 0
and buybill.buybillstoreid =' . $storid . '
and buybill.buybilldate
>= "' . $startDate . '" and buybill.buybilldate <= "' . $endDate . '"
and buybilldetailproductid =' . $ProductId . '';
$sqlQuery = new SqlQuery($sql);
//print_r($sql.'<br>');
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsAndProductIdandstorNew($queryString) {
$sql = 'SELECT buybilldetail.*
FROM buybilldetail JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.conditions = 0
' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
//print_r($sql.'<br>');
return $this->getList($sqlQuery);
}
public function countProductInBuyBill($buybillId) {
$sql = 'SELECT SUM(buybilldetailquantity) as totalQty
FROM buybilldetail
JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
where buybill.buybillid = ' . $buybillId . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
//
public function queryAllbyproductunitid($productunitid) {
$sql = 'SELECT * FROM buybilldetail where productunitid=' . $productunitid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllnothaveuintid() {
$sql = 'SELECT * FROM buybilldetail where buybilldetail.productunitid not in(
SELECT productunitid FROM `productunit` )';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateproductunit($buybilldetail) {
$sql = 'UPDATE buybilldetail SET productunitid = ? WHERE buybilldetailid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($buybilldetail->productunitid);
$sqlQuery->setNumber($buybilldetail->buybilldetailid);
return $this->executeUpdate($sqlQuery);
}
public function updatequantityBefore($quantitybefore, $buybilldetailid) {
$sql = 'UPDATE buybilldetail SET quantitybefore = ' . (float) $quantitybefore . ' WHERE buybilldetailid = ' . $buybilldetailid . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getProductOrderInBillDetails($buybillid, $buybilldetailid, $productid) {
$sql = "SELECT count(buybilldetailid)
FROM buybilldetail
WHERE buybilldetailid < $buybilldetailid
and buybillid = $buybillid
and buybilldetailproductid = $productid
order by buybilldetailid asc"; //-- = and buybill.store //store is fixed in each bill
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryWithProductIdAndSupplierIdAndUnitExt($ProductId, $supplierId, $productunitid, $startDate, $endDate) {
$sql = 'SELECT buybilldetail.buybilldetailprice, buybilldetail.buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, buybilldate, buybill.buybillid
FROM buybilldetail
JOIN buybill
ON buybilldetail.buybillid = buybill.buybillid
JOIN product
ON buybilldetail.buybilldetailproductid = product.productId
JOIN productunit
ON buybilldetail.productunitid = productunit.productunitid
JOIN unit
ON unit.unitId = productunit.unitid
JOIN productcat
ON productcat.productCatId=product.productCatId
WHERE buybilldetail.buybilldetailproductid = ' . $ProductId . '
AND buybilldetail.productunitid = ' . $productunitid . '
AND buybill.buybillsupplierid = ' . $supplierId . '
AND buybill.buybilldate >= "' . $startDate . '" AND buybill.buybilldate <= "' . $endDate . '"
AND buybill.conditions = 0
order by buybilldetailid desc';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function getsumdiscount($buybillid) {
$sql = 'SELECT sum(discountvalue) FROM buybilldetail where buybillid=' . $buybillid . '';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryByBuybilldetailproductidNoService($value) {
$sql = 'SELECT * FROM buybilldetail join product on product.productId = buybilldetail.buybilldetailproductid WHERE buybilldetailproductid = ? and isService = 0 ORDER BY `buybilldetail`.`buybilldetailid` DESC limit 1';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function updatePrices_f($tableName, $lastbuyprice, $meanbuyprice, $lastbuyprice_withDiscount, $meanbuyprice_withDiscount, $lastbuyprice_withTax, $meanbuyprice_withTax, $lastbuyprice_withDiscountAndTax, $columName, $detailId) {
$qs = '';
if ($lastbuyprice_withTax)
$qs .= ', lastbuyprice_withTax = ' . $lastbuyprice_withTax;
if ($meanbuyprice_withTax)
$qs .= ', meanbuyprice_withTax = ' . $meanbuyprice_withTax;
if ($lastbuyprice_withDiscountAndTax)
$qs .= ', lastbuyprice_withDiscountAndTax = ' . $lastbuyprice_withDiscountAndTax;
$sql = 'UPDATE ' . $tableName . ' SET lastbuyprice = ' . $lastbuyprice . ' , meanbuyprice = ' . $meanbuyprice . ', lastbuyprice_withDiscount = ' . $lastbuyprice_withDiscount . ', meanbuyprice_withDiscount = ' . $meanbuyprice_withDiscount . '
' . $qs . '
where ' . $columName . ' = ' . $detailId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getLastBuyPriceOfProductBillNotDel($buybilldetailproductid) {
$sql = 'SELECT *
FROM buybilldetail
JOIN buybill ON buybilldetail.buybillid = buybill.buybillid
where buybilldetailproductid = ' . $buybilldetailproductid . '
and buybill.conditions = 0
ORDER BY `buybilldetail`.`buybilldetailid` DESC limit 1';
$sqlQuery = new SqlQuery($sql);
//$sqlQuery->setNumber($value);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function getbuybilldetail($buybillid) {
$sql = 'SELECT * FROM buybilldetail
where buybillid = ' . $buybillid . ' ';
$sqlQuery = new SqlQuery($sql);
//$sqlQuery->setNumber($value);
//print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function queryBillNoOfPecies($billid) {
$sql = 'SELECT sum(buybilldetail.buybilldetailquantity) as note
FROM buybilldetail WHERE buybillid = ' . $billid;
$sqlQuery = new SqlQuery($sql);
//echo $sql . "<br/>";
return $this->getRow($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT buybilldetail.*,product.productName as productName,unit.unitId as unitid,unit.unitName as unitName,productcat.productCatName,product.productCatId,product.logo,buybill.buybilltotalbill as Parcode,buybill.buybillaftertotalbill as note,buybill.userid
FROM buybilldetail
join productunit on productunit.productunitid = buybilldetail.productunitid
join unit on unit.unitId = productunit.unitid
join buybill on buybill.buybillid = buybilldetail.buybillid
join product on product.productId = buybilldetail.buybilldetailproductid
join productcat on productcat.productCatId = product.productCatId
' . $queryString . '
AND buybill.conditions = 0 order by buybilldetail.buybilldetailproductid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneralSimple($queryString) {
$sql = 'SELECT buybilldetail.buybilldetailquantity,buybilldetail.productunitid,buybill.buybillid,buybill.buybilldate,suppliername as discountvalue,storeName as note
FROM buybilldetail
join buybill on buybill.buybillid = buybilldetail.buybillid
join supplier on buybill.buybillsupplierid = supplier.supplierid
join store on buybill.buybillstoreid = store.storeId
' . $queryString . ' AND buybill.conditions = 0
AND (buybill.deletedbuyid=0 || buybill.deletedbuyid is null) order by buybilldetail.buybilldetailproductid';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getLastBuyDataOfProduct($productId, $sizeid, $colorid) {
$sql = 'SELECT buybilldetail.*
FROM buybilldetail
JOIN buybill ON (buybill.buybillid = buybilldetail.buybillid and buybill.conditions = 0)
WHERE buybilldetailproductid = ' . $productId . ' and sizeid=' . $sizeid . ' and colorid=' . $colorid . ' order by buybilldetailid desc limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryPriceWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid) {
$sql = 'SELECT buybilldetail.buybilldetailprice, buybilldate
FROM buybilldetail
join buybill on buybill.buybillid = buybilldetail.buybillid
WHERE buybilldetail.buybilldetailproductid = ' . $ProductId . '
AND buybilldetail.productunitid = ' . $productunitid . '
AND buybill.buybillsupplierid = ' . $ClientId . '
AND buybill.conditions = 0
order by buybilldetailid desc limit 1';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function buyOnlyAllBills($buyBillIdStart = 0, $buyAndRetBillIdStart = 0, $queryString = '') {
$sql = 'select buybillid,buybilldetailid,buybilldetailproductid,buybilldetailquantity,buybilldetailprice,buybilldetailtotalprice,productnumber,buybilldate,buybillsysdate,buybilltotalbill,buybilldiscount,buybilldiscountrype,discountvalue,billnameid,quantitybefore,sizeid,colorid,rowtaxval,payedtax,currencyId,probuyDiscountPer,typeOfBill
from
((
SELECT buybilldetail.buybillid,buybilldetailid,buybilldetailproductid,buybilldetailquantity,buybilldetailprice,buybilldetailtotalprice,productnumber,buybilldate,buybillsysdate,buybilltotalbill,buybilldiscount,buybilldiscountrype,discountvalue,billnameid,quantitybefore,sizeid,colorid,rowtaxval,payedtax,currencyId,probuyDiscountPer,1 as typeOfBill
FROM buybilldetail
join buybill on buybill.buybillid = buybilldetail.buybillid
join productunit on productunit.productunitid = buybilldetail.productunitid
WHERE buybill.conditions = 0
and buybilldetailid > ' . $buyBillIdStart . '
)UNION ALL(
SELECT buyandruternbilldetail.buybillid,buybilldetailid,buybilldetailproductid,buybilldetailquantity,buybilldetailprice,buybilldetailtotalprice,productnumber,buybilldate,buybillsysdate,buybilltotalbill,buybilldiscount,buybilldiscountrype,discountvalue,billnameid,quantitybefore,sizeid,colorid,rowtaxval,payedtax,currencyId,0 as probuyDiscountPer,2 as typeOfBill
FROM buyandruternbilldetail
JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
join productunit on productunit.productunitid = buyandruternbilldetail.productunitid
where buyandruternbilldetail.billtype =0 and buyandruternbill.conditions = 0
and buybilldetailid > ' . $buyAndRetBillIdStart . '
)) as temp
' . $queryString . '
order by buybilldetailid asc
'; //order by buybilldate asc
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function buyOnlyAllBillsLatPricesBefore($queryString = '') {
$sql = 'select buybilldetailid,buybilldetailproductid,buybilldate,typeOfBill,buybilldetailprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
from
((
SELECT buybilldetailid,buybilldetailproductid,buybilldate,1 as typeOfBill,buybilldetailprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
FROM buybilldetail
join buybill on buybill.buybillid = buybilldetail.buybillid
join productunit on productunit.productunitid = buybilldetail.productunitid
WHERE buybill.conditions = 0
)UNION ALL(
SELECT buybilldetailid,buybilldetailproductid,buybilldate,2 as typeOfBill,buybilldetailprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
FROM buyandruternbilldetail
JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
join productunit on productunit.productunitid = buyandruternbilldetail.productunitid
where buyandruternbilldetail.billtype =0 and buyandruternbill.conditions = 0
)) as temp
' . $queryString . '
'; //order by buybilldate asc
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getOverallAverage($queryStringBuy = '', $queryStringBuyAndRet = '', $queryStringAll = '') {
$sql = 'select buybilldetailproductid,ROUND((sum(bast)/sum(makam)),2) as overallAverage
from (
select buybilldetailproductid,(prototal - (billDiscountVal * (prototal / billTotalBeforeDiscount))) as bast , finalquantity as makam
from
((
SELECT buybilldetailid,buybilldetailproductid,1 as typeOfBill,(productnumber*buybilldetailquantity) as finalquantity
,buybilldetailtotalprice as prototal,buybilltotalbill as billTotalBeforeDiscount
,if(buybilldiscountrype = 0 ,buybilldiscount,((buybilldiscount/100)*buybilltotalbill)) as billDiscountVal
FROM buybilldetail
join buybill on buybill.buybillid = buybilldetail.buybillid
join productunit on productunit.productunitid = buybilldetail.productunitid
WHERE buybill.conditions = 0
' . $queryStringBuy . '
)UNION ALL(
SELECT buybilldetailid,buybilldetailproductid,2 as typeOfBill,(productnumber*buybilldetailquantity) as finalquantity
,buybilldetailtotalprice as prototal,buybilltotalbill as billTotalBeforeDiscount
,if(buybilldiscountrype = 0 ,buybilldiscount,((buybilldiscount/100)*buybilltotalbill)) as billDiscountVal
FROM buyandruternbilldetail
JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
join productunit on productunit.productunitid = buyandruternbilldetail.productunitid
where buyandruternbilldetail.billtype =0 and buyandruternbill.conditions = 0
' . $queryStringBuyAndRet . '
)) as temp
' . $queryStringAll . '
) as final
group by buybilldetailproductid'; //order by buybilldate asc
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getproductpricebuy($productId, $storereportmodelid) {
$sql = 'SELECT buybilldetail.*
FROM buybilldetail
WHERE buybilldetailproductid = ' . $productId . ' and buybillid=' . $storereportmodelid;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getproductBuyPrices($productId) {
$sql = 'SELECT buybilldetail.*,buybill.buybilldate,supplier.suppliername as note
FROM buybilldetail
JOIN buybill ON (buybill.buybillid = buybilldetail.buybillid and buybill.conditions = 0)
JOIN supplier ON supplier.supplierid = buybill.buybillsupplierid
WHERE buybilldetailproductid = ' . $productId . ' order by buybilldetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}