File: /home/mostafedeg/public_html/erp/models/mysql/ext/SellandruternbilldetailMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'sellandruternbilldetail'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-29 13:54
*/
class SellandruternbilldetailMySqlExtDAO extends SellandruternbilldetailMySqlDAO {
public function loadlastpriceforclient($sellbillid) {
$sql = 'SELECT * FROM sellandruternbilldetail WHERE sellbillid = ' . $sellbillid . ' order by sellbilldetailid desc limit 1 ';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithSellBillIdAndSellType($sellbillid, $selltype) {
$sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName
,size.name as sizeName,color.name as colorName,productserailid,productserial.don,productunit.productnumber
FROM sellandruternbilldetail JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId
left JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid
left JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
left join store
on store.storeid=sellandruternbilldetail.storeid
left join sizecolor as size on size.id = sellandruternbilldetail.sizeid
left join sizecolor as color on color.id = sellandruternbilldetail.colorid
left join productserial on (productserial.serialnumber=sellandruternbilldetail.parcode and productserial.productid=sellandruternbilldetail.sellbilldetailproductid and sellandruternbilldetail.sizeid=productserial.sizeid and sellandruternbilldetail.colorid=productserial.colorid)
WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
and sellandruternbilldetail.selltype =' . $selltype . '
order by sellandruternbilldetail.sellbilldetailid asc ';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSellBillIdAndSellTypeSimple($sellbillid, $selltype) {
$sql = 'SELECT *
FROM sellandruternbilldetail
WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
and sellandruternbilldetail.selltype =' . $selltype . '';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSellBillIdwithoutSellType($sellbillid) {
$sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName
FROM sellandruternbilldetail JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId
left JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid
left JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
left join store
on store.storeid=sellandruternbilldetail.storeid
WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSellBillIdAndSellTypeforproft($sellbillid) {
$sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName,productcat.discounttype,productcat.buydiscount,productunit.productnumber
FROM sellandruternbilldetail JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId
left JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid
left JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
left join store
on store.storeid=sellandruternbilldetail.storeid
WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSellBillIdAndSellTypeandstor($sellbillid, $selltype, $storid) {
$sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName
FROM sellandruternbilldetail JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
left join store
on store.storeid=sellandruternbilldetail.storeid
WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
and sellandruternbilldetail.storeid = ' . $storid . '
and sellandruternbilldetail.selltype =' . $selltype . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsType($startDate, $endDate, $selltype) {
$sql = 'SELECT sellandruternbilldetail.*
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsTypeExt($startDate, $endDate, $selltype) {
$sql = 'SELECT sellandruternbilldetail.sellbilldetailproductid,
sellandruternbilldetail.sellbilldetailquantity,
sellandruternbilldetail.productunitid,
sellandruternbilldetail.buyprice,
sellandruternbilldetail.buydiscount,
sellandruternbilldetail.buydiscounttype,
sellandruternbilldetail.lastbuyprice,
sellandruternbilldetail.meanbuyprice,
sellandruternbilldetail.lastbuyprice_withDiscount,
sellandruternbilldetail.meanbuyprice_withDiscount,
sellandruternbilldetail.lastbuyprice_withTax,sellandruternbilldetail.meanbuyprice_withTax,
sellandruternbilldetail.lastbuyprice_withDiscountAndTax,
product.overAllAveragePrice as generalPrice
FROM sellandruternbilldetail
JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsTypeAndProductId($startDate, $endDate, $selltype, $ProductId) {
$sql = 'SELECT sellandruternbilldetail.*
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellbillandrutern.sellbilldate
>= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
and sellandruternbilldetail.sellbilldetailproductid =' . $ProductId . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsTypeAndProductIdExt($startDate, $endDate, $selltype, $ProductId) {
$sql = 'SELECT
sellandruternbilldetail.sellbilldetailquantity,
sellandruternbilldetail.sellbilldetailprice,
sellandruternbilldetail.sellbilldetailtotalprice,
sellandruternbilldetail.discountvalue,
sellandruternbilldetail.productunitid,
sellandruternbilldetail.buyprice,
sellandruternbilldetail.lastbuyprice,
sellandruternbilldetail.meanbuyprice,
sellandruternbilldetail.lastbuyprice_withDiscount,
sellandruternbilldetail.meanbuyprice_withDiscount,
sellandruternbilldetail.lastbuyprice_withTax,
sellandruternbilldetail.meanbuyprice_withTax,
sellandruternbilldetail.lastbuyprice_withDiscountAndTax,
sellandruternbilldetail.buydiscount,
sellandruternbilldetail.buydiscounttype,
sellbillandrutern.sellbillprice,
sellbillandrutern.sellbilldiscount,
sellbillandrutern.sellbilldiscounttype,
sellbillandrutern.returnsellbillprice,
sellbillandrutern.sellbilldate as note
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellbillandrutern.sellbilldate
>= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
and sellandruternbilldetail.sellbilldetailproductid in(' . $ProductId . ')';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsTypeAndProductIdExtNew($selltype, $queryString) {
$sql = 'SELECT
sellandruternbilldetail.sellbilldetailquantity,
sellandruternbilldetail.sellbilldetailprice,
sellandruternbilldetail.sellbilldetailtotalprice,
sellandruternbilldetail.discountvalue,
sellandruternbilldetail.productunitid,
sellandruternbilldetail.buyprice,
sellandruternbilldetail.buydiscount,
sellandruternbilldetail.buydiscounttype,
sellbillandrutern.sellbillprice,
sellbillandrutern.sellbilldiscount,
sellbillandrutern.sellbilldiscounttype,
sellbillandrutern.returnsellbillprice
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
' . $queryString . '
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsTypeAndProductIdExtandstor($startDate, $endDate, $selltype, $ProductId, $storid) {
$sql = 'SELECT
sellandruternbilldetail.sellbilldetailquantity,
sellandruternbilldetail.sellbilldetailprice,
sellandruternbilldetail.sellbilldetailtotalprice,
sellandruternbilldetail.discountvalue,
sellandruternbilldetail.productunitid,
sellandruternbilldetail.buyprice,
sellandruternbilldetail.buydiscount,
sellandruternbilldetail.buydiscounttype,
sellbillandrutern.sellbillprice,
sellbillandrutern.sellbilldiscount,
sellbillandrutern.sellbilldiscounttype
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellbillandrutern.sellbilldate
>= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
and sellandruternbilldetail.sellbilldetailproductid =' . $ProductId . '
and (sellandruternbilldetail.storeid=' . $storid . ' or sellbillandrutern.sellbillstoreid)
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsTypeAndProductIdExtandstorNew($selltype, $queryString) {
$sql = 'SELECT
sellandruternbilldetail.sellbilldetailquantity,
sellandruternbilldetail.sellbilldetailprice,
sellandruternbilldetail.sellbilldetailtotalprice,
sellandruternbilldetail.discountvalue,
sellandruternbilldetail.productunitid,
sellandruternbilldetail.buyprice,
sellandruternbilldetail.buydiscount,
sellandruternbilldetail.buydiscounttype,
sellbillandrutern.sellbillprice,
sellbillandrutern.sellbilldiscount,
sellbillandrutern.sellbilldiscounttype
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
' . $queryString . '
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryQuantityWithProductAndSellType($productId, $selltype) {
$sql = 'SELECT Sum(sellandruternbilldetail.sellbilldetailquantity) as sumQuantity
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellandruternbilldetail.sellbilldetailproductid =' . $productId . '';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryQuantityWithProductAndSellTypeAndDate($productId, $selltype, $startDate, $endDate) {
$sql = 'SELECT Sum(sellandruternbilldetail.sellbilldetailquantity) as sumQuantity
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
and sellandruternbilldetail.selltype =' . $selltype . '
and sellandruternbilldetail.sellbilldetailproductid =' . $productId . '
and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid, $selltype) {
$sql = 'SELECT sellandruternbilldetail.*, product.productName,
unit.unitName ,productcat.productCatName, sellbilldate
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
AND sellandruternbilldetail.productunitid = ' . $productunitid . '
AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
AND sellbillandrutern.conditions = 0
AND sellandruternbilldetail.selltype =' . $selltype . '
order by sellbilldetailid desc limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryPriceWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid, $selltype) {
$QS = '';
if ($productunitid > 0) {
$QS = 'AND sellandruternbilldetail.productunitid = ' . $productunitid . '';
}
$sql = 'SELECT sellandruternbilldetail.sellbilldetailprice, sellbilldate,discountvalue,discounttype
FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
' . $QS . '
AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
AND sellbillandrutern.conditions = 0
AND sellandruternbilldetail.selltype =' . $selltype . '
order by sellbilldetailid desc limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getNoOfBillsWithProductIdAndClientId($ProductId, $ClientId, $selltype) {
$sql = 'SELECT count( distinct sellbillandrutern.sellbillid)
FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
AND sellbillandrutern.conditions = 0
AND sellandruternbilldetail.selltype =' . $selltype . ' limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $productunitid, $selltype, $startDate, $endDate) {
$sql = 'SELECT sellandruternbilldetail.sellbilldetailprice, sellandruternbilldetail.pricetype, sellandruternbilldetail.sellbilldetaildate, product.productName, unit.unitName ,productcat.productCatName, sellbilldate, sellbillandrutern.sellbillid
,1 as billtype FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId
JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid
JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
AND sellandruternbilldetail.productunitid = ' . $productunitid . '
AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
AND sellbillandrutern.conditions = 0
AND sellandruternbilldetail.selltype =' . $selltype . '
AND sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '"
order by sellbilldetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function countProductInSellBill($sellBillId) {
$sql = 'SELECT SUM(sellbilldetailquantity) as totalQTY
FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellandruternbilldetail.sellbillid = ' . $sellBillId . '
AND sellandruternbilldetail.selltype = 0
AND sellbillandrutern.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
//emaan
public function countProductInSellBillExt($sellBillId) {
$sql = 'SELECT SUM(sellbilldetailquantity) as totalQTY
FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellandruternbilldetail.sellbillid = ' . $sellBillId . '
AND sellandruternbilldetail.selltype = 0';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function countProductInReturnSellBill($sellBillId) {
$sql = 'SELECT SUM(sellbilldetailquantity) as totalQTY
FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellandruternbilldetail.sellbillid = ' . $sellBillId . '
AND sellandruternbilldetail.selltype = 1';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllsellstor($sellbillid) {
$sql = 'SELECT distinct(sellandruternbilldetail.storeid) ,store.storeName
FROM sellandruternbilldetail
join store
on store.storeid=sellandruternbilldetail.storeid
WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
';
// print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllnothaveuintid() {
$sql = 'SELECT * FROM sellandruternbilldetail where sellandruternbilldetail.productunitid not in(
SELECT productunitid FROM `productunit` )';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateproductunit($productunitid, $sellbilldetailid) {
$sql = 'UPDATE sellandruternbilldetail SET productunitid = ' . $productunitid . ' WHERE sellbilldetailid = ' . $sellbilldetailid . '';
//print_r('<br>'.$sql);
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryAllnothavestor($start, $end) {
$sql = 'SELECT * FROM sellandruternbilldetail where storeid =0 LIMIT ' . $start . ' , ' . $end . ' ';
///print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updatestoreid($storeid, $sellbilldetailid) {
$sql = 'UPDATE sellandruternbilldetail SET storeid = ' . $storeid . ' WHERE sellbilldetailid = ' . $sellbilldetailid . '';
//print_r('<br>'.$sql);
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getsumdiscount($sellbillid, $selltype) {
$sql = 'SELECT sum(discountvalue) FROM sellandruternbilldetail where sellbillid=' . $sellbillid . ' and selltype = ' . $selltype . '';
//print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllGeneral($queryString) {
if (strpos($queryString, "where") === false) { // no where use it before and that is coming without where
$queryString = " where 1 " . $queryString;
}
$sql = 'SELECT sellandruternbilldetail.*,sellbillandrutern.sellbillclientid as clientid,client.clientname,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note,productcat.productCatName,product.productCatId,sellbillandrutern.userid
FROM sellandruternbilldetail
left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
left join client on client.clientid = sellbillandrutern.sellbillclientid
left join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
left join productcat on productcat.productCatId = product.productCatId
' . $queryString . ' AND sellbillandrutern.conditions = 0
order by sellandruternbilldetail.sellbilldetailproductid'; //AND sellbillandrutern.deletedsellid=0
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function sellbillandreturnsellbillWithProducts($queryString) {
$sql = 'SELECT sellandruternbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
join client on client.clientid = sellbillandrutern.sellbillclientid
join goverarea on goverarea.clientareaid = client.clientareaid
join clientarea on clientarea.id = client.clientareaid
join government on government.governmetid = goverarea.governmentid
join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
WHERE 1 ' . $queryString . ' AND sellbillandrutern.conditions = 0 ';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneralPriceType($queryString) {
$sql = 'SELECT sellandruternbilldetail.*,product.productName as productName,unit.unitId as unitid,unit.unitName as unitName
FROM sellandruternbilldetail
join productunit on productunit.productunitid = sellandruternbilldetail.productunitid
join unit on unit.unitId = productunit.unitid
join sellbillandrutern on sellbillandrutern.sellbillid = sellandruternbilldetail.sellbillid
join product on product.productId = sellandruternbilldetail.sellbilldetailproductid ' . $queryString . '
AND sellbillandrutern.conditions = 0 order by sellandruternbilldetail.sellbilldetailproductid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral2($queryString, $clientid) {
$sql = 'SELECT client.clientname as clientname , sellandruternbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
join client on client.clientid = sellbillandrutern.sellbillclientid
' . $queryString . ' AND sellbillandrutern.conditions = 0
AND client.clientid = ' . $clientid . '
AND sellbillandrutern.deletedsellid=0 order by sellandruternbilldetail.sellbilldetailproductid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral3($queryString, $productid) {
$sql = 'SELECT client.clientname as clientname , client.clientid as clientid, sellandruternbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note
FROM sellandruternbilldetail JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
join client on client.clientid = sellbillandrutern.sellbillclientid
' . $queryString . ' AND sellbillandrutern.conditions = 0
AND product.productId = ' . $productid . '
AND sellbillandrutern.deletedsellid=0 order by sellandruternbilldetail.sellbilldetailproductid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneralSimple($queryString) {
$sql = 'SELECT sellandruternbilldetail.sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbillandrutern.sellbillid,sellbillandrutern.sellbilldate,clientname as discountvalue,storeName as note
FROM sellandruternbilldetail
JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
join client on sellbillandrutern.sellbillclientid = client.clientid
join store on sellandruternbilldetail.storeid = store.storeId
' . $queryString . ' AND sellbillandrutern.conditions = 0
AND sellbillandrutern.deletedsellid=0 order by sellandruternbilldetail.sellbilldetailproductid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBillNoOfPecies($billid) {
$sql = 'SELECT sum(sellandruternbilldetail.sellbilldetailquantity) as note
FROM sellandruternbilldetail WHERE sellbillid = ' . $billid;
$sqlQuery = new SqlQuery($sql);
//echo $sql . "<br/>";
return $this->getRow($sqlQuery);
}
//# m7md 21/11/2017
public function queryWithProductIdAndClientIdAndUnitExtNew($where) {
$sql = 'SELECT sellandruternbilldetail.sellbilldetailprice, sellandruternbilldetail.pricetype, sellandruternbilldetail.sellbilldetaildate, product.productName, unit.unitName ,productcat.productCatName, sellbilldate, sellbillandrutern.sellbillid
,1 as billtype , client.clientname , sellbillandrutern.sellQuantity as quantity
FROM sellandruternbilldetail
JOIN sellbillandrutern
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
JOIN product
ON sellandruternbilldetail.sellbilldetailproductid = product.productId
JOIN productunit
ON sellandruternbilldetail.productunitid = productunit.productunitid
JOIN unit
ON unit.unitId = productunit.unitid
join productcat
on productcat.productCatId=product.productCatId
join client on client.clientid = sellbillandrutern.sellbillclientid
WHERE ' . $where . '
sellandruternbilldetail.selltype = 0
order by sellbilldetailid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllNotDel($queryString) {
$sql = 'SELECT sellandruternbilldetail.*,sellbillandrutern.sellbilldate
FROM sellandruternbilldetail
JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
where sellbillandrutern.conditions = 0
' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBySellbillidEX($sellbillid, $queryString = '') {
$sql = 'SELECT * FROM sellandruternbilldetail WHERE sellbillid = ' . $sellbillid . ' ' . $queryString . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryreturnandSellBill($productId) {
$sql = 'SELECT sellandruternbilldetail.*,sellbillandrutern.sellbillclientid as clientid,client.clientname,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcodeTotal,sellbillandrutern.sellbillaftertotalbill as note,productcat.productCatName,product.productCatId,sellbillandrutern.userid
FROM sellandruternbilldetail
left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
left join client on client.clientid = sellbillandrutern.sellbillclientid
left join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
left join productcat on productcat.productCatId = product.productCatId
where sellandruternbilldetail.sellbilldetailproductid = ' . $productId . ' AND sellbillandrutern.conditions = 0
order by sellandruternbilldetail.sellbilldetailproductid'; //AND sellbillandrutern.deletedsellid=0
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function SellAndReturnDETAILSCats($sellandreturndate = '') {
$sql = 'SELECT productcat.productCatId,productcat.productCatName,sellbillandrutern.sellbilltotalbill as parcodeTotal,sellbillandrutern.sellbillaftertotalbill as note,sellandruternbilldetail.* FROM productcat
left join sellandruternbilldetail on productcat.productCatId = sellandruternbilldetail.sellbilldetailcatid
left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid where sellbillandrutern.conditions = 0 ' . $sellandreturndate . '';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function SellAndReturnDETAILSClients($sellandreturndate = '') {
$sql = 'SELECT client.clientid,client.clientname as clientname,sellbillandrutern.sellbillclientid,sellandruternbilldetail.*,sellbillandrutern.sellbilltotalbill as parcodeTotal,sellbillandrutern.sellbillaftertotalbill as note
FROM client
left JOIN sellbillandrutern ON sellbillandrutern.sellbillclientid = client.clientid
left JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid WHERE sellandruternbilldetail.sellbillid IS NOT NULL ' . $sellandreturndate . ' ';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
}
?>