File: /home/mostafedeg/public_html/erp/models/mysql/ext/BillsproductsMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'billsproducts'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2016-06-21 11:00
*/
class BillsproductsMySqlExtDAO extends BillsproductsMySqlDAO {
public function queryByBillIdAndDeleted($billId, $deleted) {
$sql = 'SELECT * FROM billsproducts WHERE billid = ? and deleted = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($billId);
$sqlQuery->setNumber($deleted);
return $this->getList($sqlQuery);
}
public function getproducts($billId, $deleted) {
$sql = 'SELECT billsproducts.*,product.productName as deleted ,productCatId as service
FROM billsproducts
join product on product.productId = billsproducts.productid
WHERE billid = ? and deleted = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($billId);
$sqlQuery->setNumber($deleted);
return $this->getList($sqlQuery);
}
public function loadProduct($id) {
$sql = 'SELECT * FROM billsproducts WHERE id = ' . $id;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT billsproducts.*,bills.clientid,client.clientname,product.productName as productName,product.productBuyPrice as deleted,productcat.productCatName,product.productCatId
FROM billsproducts
left join bills on bills.id = billsproducts.billid
left join client on client.clientid = bills.clientid
left join product on product.productId = billsproducts.productid
left join productcat on productcat.productCatId = product.productCatId
' . $queryString . ' AND bills.deleted=0 order by billsproducts.productid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function billWithProducts($queryStringBill) {
$sql = 'SELECT billsproducts.*,product.productName as productName,product.productBuyPrice as deleted
FROM billsproducts
join bills on bills.id = billsproducts.billid
join client on client.clientid = bills.clientid
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 = billsproducts.productid
WHERE 1 ' . $queryStringBill . ' AND billsproducts.deleted = 0 ';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral2($queryString, $clientid) {
$sql = 'SELECT client.clientname as clientname , billsproducts.*,product.productName as productName,product.productBuyPrice as deleted FROM billsproducts join bills on bills.id = billsproducts.billid join product on product.productId = billsproducts.productid join client on client.clientid = bills.clientid ' . $queryString . ' AND client.clientid = ' . $clientid . ' AND bills.deleted=0 order by billsproducts.productid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral3($queryString, $productid) {
$sql = 'SELECT client.clientname as clientname , client.clientid as clientid , billsproducts.*,product.productName as productName,product.productBuyPrice as deleted FROM billsproducts join bills on bills.id = billsproducts.billid join product on product.productId = billsproducts.productid join client on client.clientid = bills.clientid ' . $queryString . ' AND product.productId = ' . $productid . ' AND bills.deleted=0 order by billsproducts.productid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneralSimple($queryString) {
$sql = 'SELECT billsproducts.productno,bills.id,bills.billdate as note,clientname as deleted
FROM billsproducts
join bills on bills.id = billsproducts.billid
join client on bills.clientid = client.clientid
' . $queryString . '
AND bills.deleted=0 order by billsproducts.productid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBillNoOfPecies($billid) {
$sql = 'SELECT sum(billsproducts.productno) as deleted
FROM billsproducts WHERE billid = ' . $billid;
$sqlQuery = new SqlQuery($sql);
//echo $sql;
return $this->getRow($sqlQuery);
}
public function queryStoreAndBillsProducts($queryString) {
$sql = 'SELECT
billsproducts.billid,
billsproducts.productid,
billsproducts.productno
FROM billsproducts
JOIN storereport
ON storereport.storereportmodelid = billsproducts.billid
where storereport.storereporttype = 1 ' . $queryString . ' ';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $startDate, $endDate) {
$sql = 'SELECT billsproducts.productprice as sellbilldetailprice,bills.billdate as sellbilldetaildate,2 as billtype ,
product.productName,
productcat.productCatName, bills.id
FROM billsproducts
JOIN bills
ON billsproducts.billid = bills.id
JOIN product
ON billsproducts.productid = product.productId
JOIN productcat
ON productcat.productCatId=product.productCatId
WHERE billsproducts.productid = ' . $ProductId . '
AND bills.clientid = ' . $ClientId . '
AND bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"
AND billsproducts.deleted = 0
order by billsproducts.id desc';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $productid) {
$sql = 'SELECT
billsproducts.productno,
billsproducts.productprice,
billsproducts.producttotalprice,
bills.netbillvalue,
bills.discountvalue,
bills.discounttype,
product.productBuyPrice,
billsproducts.lastbuyprice,
billsproducts.meanbuyprice
FROM billsproducts
JOIN bills
ON billsproducts.billid = bills.id
JOIN product
ON billsproducts.productid = product.productId
where bills.deleted = 0
and bills.billdate >= "' . $startDate . '" and bills.billdate <= "' . $endDate . '"
and billsproducts.productid in(' . $productid . ')';
$sqlQuery = new SqlQuery($sql);
//print('<br>'.$sql.'<br>');
return $this->getList($sqlQuery);
}
public function queryWithSellBillIdforproft($id) {
$sql = 'SELECT billsproducts.*, product.productName, product.productBuyPrice,productcat.productCatName,productcat.buydiscount,productcat.discounttype
FROM billsproducts
JOIN product ON billsproducts.productid = product.productId
join productcat on productcat.productCatId=product.productCatId
WHERE billsproducts.billid = ' . $id . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updatePrices_f_optic($lastbuyprice, $meanbuyprice, $detailId) {
$sql = 'UPDATE billsproducts SET lastbuyprice = ' . $lastbuyprice . ',meanbuyprice = ' . $meanbuyprice . '
where id = ' . $detailId . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function load_billproducts_and_bills_by_productid($where) {
$sql = 'SELECT billsproducts.productprice as productprice,billsproducts.productno as productno , billsproducts.lastbuyprice as lastbuyprice ,
billsproducts.billid,bills.netbillvalue as netbillvalue,bills.discountvalue as discountvalue , product.productBuyPrice as productBuyPrice
FROM billsproducts
JOIN bills ON billsproducts.billid = bills.id
JOIN product ON product.productId = billsproducts.productid
WHERE ' . $where . '
AND billsproducts.deleted = 0
order by billsproducts.id desc';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function get_total_sellcost($startDate, $endDate) {
$sql = 'SELECT billsproducts.productid as productid , billsproducts.productno as productno , product.productBuyPrice as productBuyPrice,
billsproducts.lastbuyprice as lastbuyprice , billsproducts.meanbuyprice as meanbuyprice
FROM billsproducts
JOIN bills ON billsproducts.billid = bills.id
JOIN product ON product.productId = billsproducts.productid
WHERE date(bills.billdate) >= "' . $startDate . '" and date(bills.billdate) <= "' . $endDate . '"
AND billsproducts.deleted = 0
order by billsproducts.id desc';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function deleteIDS($ids) {
$sql = 'DELETE FROM billsproducts WHERE id in(' . $ids . ')';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
//# m7md 21/11/2017
public function queryWithProductIdAndClientIdAndUnitExtNew($where) {
$sql = 'SELECT billsproducts.productprice as sellbilldetailprice,bills.billdate as sellbilldetaildate,2 as billtype ,
product.productName,
productcat.productCatName, bills.id , client.clientname
FROM billsproducts
JOIN bills
ON billsproducts.billid = bills.id
JOIN product
ON billsproducts.productid = product.productId
JOIN productcat
ON productcat.productCatId=product.productCatId
join client on client.clientid = bills.clientid
WHERE ' . $where . '
billsproducts.deleted = 0
order by billsproducts.id desc';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
}
?>