File: /home/mostafedeg/public_html/erp/models/mysql/ext/BuyandruternbillMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'buyandruternbill'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-29 13:54
*/
class BuyandruternbillMySqlExtDAO extends BuyandruternbillMySqlDAO {
public function getSellBillsUsingQueryString($queryString1) {
$sql = 'SELECT buyandruternbill.*
FROM buyandruternbill
' . $queryString1 . '
AND buyandruternbill.deletedbuyid=0 ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function insertEX($buyandruternbill) {
$sql = 'INSERT INTO buyandruternbill (buybillSerial, buybilldate, buybillsupplierid, buybilltotaldeptbefor, buybilltotaldeptafter, buybilldiscount, buybilldiscountrype, buybilltotalbill, buybillaftertotalbill, buybilltotalpayed, buybillfinalbill, buybillstoreid, buybillsysdate,comment, userid, conditions, buybillclientname, buybillsaveid, buybilldirectpayment, billnameid,deletedbuyid,controlname,payedtax,costcenterid,dailyentryid,currencyId,taxOfDiscountPer,taxOfDiscountVal,client) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?,?,?,?,?,?,?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($buyandruternbill->buybillSerial);
$sqlQuery->set($buyandruternbill->buybilldate);
$sqlQuery->setNumber($buyandruternbill->buybillsupplierid);
$sqlQuery->set($buyandruternbill->buybilltotaldeptbefor);
$sqlQuery->set($buyandruternbill->buybilltotaldeptafter);
$sqlQuery->set($buyandruternbill->buybilldiscount);
$sqlQuery->setNumber($buyandruternbill->buybilldiscountrype);
$sqlQuery->set($buyandruternbill->buybilltotalbill);
$sqlQuery->set($buyandruternbill->buybillaftertotalbill);
$sqlQuery->set($buyandruternbill->buybilltotalpayed);
$sqlQuery->set($buyandruternbill->buybillfinalbill);
$sqlQuery->setNumber($buyandruternbill->buybillstoreid);
$sqlQuery->set($buyandruternbill->buybillsysdate);
$sqlQuery->set($buyandruternbill->comment);
$sqlQuery->setNumber($buyandruternbill->userid);
$sqlQuery->setNumber($buyandruternbill->conditions);
$sqlQuery->set($buyandruternbill->buybillclientname);
$sqlQuery->setNumber($buyandruternbill->buybillsaveid);
$sqlQuery->setNumber($buyandruternbill->buybilldirectpayment);
$sqlQuery->setNumber($buyandruternbill->billnameid);
$sqlQuery->setNumber($buyandruternbill->deletedbuyid);
$sqlQuery->set($buyandruternbill->controlname);
$sqlQuery->set($buyandruternbill->payedtax);
$sqlQuery->set($buyandruternbill->costcenterid);
$sqlQuery->set($buyandruternbill->dailyentryid);
$sqlQuery->setNumber((int) $buyandruternbill->currencyId);
$sqlQuery->setNumber((float) $buyandruternbill->taxOfDiscountPer);
$sqlQuery->setNumber((float) $buyandruternbill->taxOfDiscountVal);
$sqlQuery->setNumber((int) $buyandruternbill->client);
$id = $this->executeInsert($sqlQuery);
$buyandruternbill->buybillid = $id;
return $id;
}
public function queryAllSerials() {
$sql = 'SELECT buyandruternbill.buybillSerial, buybillid
FROM buyandruternbill
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllSerialsNotDeleted() {
$sql = 'SELECT buyandruternbill.buybillSerial, buybillid
FROM buyandruternbill
WHERE conditions = 0
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByConditionExt($value) {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
WHERE buyandruternbill.conditions = ?
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByConditionExtLimited($value, $start, $end) {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
WHERE buyandruternbill.conditions = ?
ORDER BY buybillid DESC
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByDateExt($from, $to, $queryString = '') {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
LEFT JOIN user as delbyuser
ON delbyuser.userid = buyandruternbill.delbyuserid
WHERE buyandruternbill.buybilldate >= "' . $from . '" AND buyandruternbill.buybilldate <= "' . $to . '" ' . $queryString . '
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBySupplierId($value, $queryString = '') {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
LEFT JOIN user as delbyuser
ON delbyuser.userid = buyandruternbill.delbyuserid
WHERE buybillsupplierid = ? ' . $queryString . '
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryBySupplierIdLimited($value, $start, $end) {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
WHERE buybillsupplierid = ?
ORDER BY buybillid DESC
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryBySerial($value, $queryString = '') {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
LEFT JOIN user as delbyuser
ON delbyuser.userid = buyandruternbill.delbyuserid
WHERE buybillSerial = "' . $value . '" ' . $queryString . '
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryBySerialLimited($value, $start, $end) {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid
WHERE buybillSerial = ?
ORDER BY buybillid DESC
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function updateConditions($buyandruternbill) {
$sql = 'UPDATE buyandruternbill SET buybillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? WHERE buybillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($buyandruternbill->buybillsysdate);
$sqlQuery->setNumber($buyandruternbill->userid);
$sqlQuery->setNumber($buyandruternbill->delbyuserid);
$sqlQuery->setNumber($buyandruternbill->conditions);
$sqlQuery->setNumber($buyandruternbill->buybillid);
return $this->executeUpdate($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
WHERE buybillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function loadExt2($id, $queryString = '') {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName,delbyuser.username as delbyusername
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user as delbyuser
ON delbyuser.userid = buyandruternbill.delbyuserid
WHERE buybillid = ? ' . $queryString . '
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function queryBuyBillsOnlyWithSupplierIdAndDate($supplierId, $from, $to) {
$sql = 'SELECT DISTINCT(buyandruternbill.buybillid), buyandruternbill.buybillid, buybillSerial, buybilldate, buybillaftertotalbill
,buybilldiscountrype,buybilldiscount
FROM buyandruternbill
JOIN buyandruternbilldetail
ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
WHERE buyandruternbill.buybilldate >= "' . $from . '" AND buyandruternbill.buybilldate <= "' . $to . '"
AND buyandruternbill.buybillsupplierid = ' . $supplierId . '
AND buyandruternbilldetail.billtype = 0
AND buyandruternbill.conditions = 0
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBuyBillsOnlyWithSupplierIdAndDateNew($queryString) {
$sql = 'SELECT DISTINCT(buyandruternbill.buybillid), buybilldate, buybillSerial, buybillaftertotalbill, buyandruternbill.buybillid
,buybilldiscountrype,buybilldiscount
FROM buyandruternbill
JOIN buyandruternbilldetail
ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid
WHERE 1 ' . $queryString . '
AND buyandruternbilldetail.billtype = 0
AND buyandruternbill.conditions = 0
ORDER BY buybillid DESC';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryReturnBuyBillsOnlyWithSupplierIdAndDate($supplierId, $from, $to) {
$sql = 'SELECT DISTINCT(buyandruternbill.buybillid), buyandruternbill.buybillid, buybillSerial, buybilldate, buybillaftertotalbill
FROM buyandruternbill
JOIN buyandruternbilldetail
ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
WHERE buyandruternbill.buybilldate >= "' . $from . '" AND buyandruternbill.buybilldate <= "' . $to . '"
AND buyandruternbill.buybillsupplierid = ' . $supplierId . '
AND buyandruternbilldetail.billtype = 1
AND buyandruternbill.conditions = 0
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryReturnBuyBillsOnlyWithSupplierIdAndDateNew($queryString) {
$sql = 'SELECT DISTINCT(buyandruternbill.buybillid), buyandruternbill.buybillid, buybillSerial, buybilldate, buybillaftertotalbill,buybilltotalpayed
FROM buyandruternbill
JOIN buyandruternbilldetail
ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
WHERE 1 ' . $queryString . '
AND buyandruternbilldetail.billtype = 1
AND buyandruternbill.conditions = 0
ORDER BY buybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBystring($queryString) {
$sql = 'SELECT buyandruternbill.*, supplier.suppliername, store.storeName, user.username
FROM buyandruternbill LEFT JOIN supplier
ON buyandruternbill.buybillsupplierid = supplier.supplierid JOIN store
ON buyandruternbill.buybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = buyandruternbill.userid ' . $queryString . '
AND buyandruternbill.conditions=0
';
$sqlQuery = new SqlQuery($sql);
//print_r($sql.'<br>');
return $this->getList($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT buyandruternbill.* , s.suppliername as suppliername,u.employeename as username
FROM buyandruternbill
JOIN supplier s ON buyandruternbill.buybillsupplierid = s.supplierid
join user u ON u.userid = buyandruternbill.userid
' . $queryString . ' AND buyandruternbill.conditions = 0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderSuppliersWithBuyAndReturnBuybillsBuys($queryString) {
$sql = 'SELECT buyandruternbill.buybillsupplierid , SUM(buyandruternbill.buybilltotalbill) as allGenTotalBuyAndReturn , SUM(buyandruternbill.buybillaftertotalbill) as buybillaftertotalbill ,
COUNT(buyandruternbill.buybillsupplierid ) , client.clientname as clientname FROM `buyandruternbill`
join client on client.clientid = buyandruternbill.buybillsupplierid
' . $queryString . ' GROUP BY buyandruternbill.buybillsupplierid
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}
?>