File: /home/mostafedeg/public_html/erp/models/mysql/ext/ReturnbuybillMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'returnbuybill'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class ReturnbuybillMySqlExtDAO extends ReturnbuybillMySqlDAO {
public function getSellBillsUsingQueryString($queryString1) {
$sql = 'SELECT returnbuybill.*
FROM returnbuybill
' . $queryString1 . '
AND returnbuybill.deletedbuyid=0 ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function insertEX($returnbuybill) {
$sql = 'INSERT INTO returnbuybill (returnbuybillSerial, returnbuybilldate, returnbuybillsupplierid, returnbuybilltotaldeptbefor, returnbuybilltotaldeptafter, returnbuybilldiscount, returnbuybilldiscountrype, returnbuybilltotalbill, returnbuybillaftertotalbill, returnbuybilltotalpayed, returnbuybillfinalbill, returnbuybillstoreid, returnbuybillsysdate,comment, userid, conditions, returnbuybillclientname, returnbuybillsaveid, billnameid, returnbuybilldirectpayment,deletedbuyid,controlname,payedtax,costcenterid,currencyId,taxOfDiscountPer,taxOfDiscountVal,client) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? ,?,?,?,?,?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($returnbuybill->returnbuybillSerial);
$sqlQuery->set($returnbuybill->returnbuybilldate);
$sqlQuery->setNumber($returnbuybill->returnbuybillsupplierid);
$sqlQuery->set($returnbuybill->returnbuybilltotaldeptbefor);
$sqlQuery->set($returnbuybill->returnbuybilltotaldeptafter);
$sqlQuery->set($returnbuybill->returnbuybilldiscount);
$sqlQuery->setNumber($returnbuybill->returnbuybilldiscountrype);
$sqlQuery->set($returnbuybill->returnbuybilltotalbill);
$sqlQuery->set($returnbuybill->returnbuybillaftertotalbill);
$sqlQuery->set($returnbuybill->returnbuybilltotalpayed);
$sqlQuery->set($returnbuybill->returnbuybillfinalbill);
$sqlQuery->setNumber($returnbuybill->returnbuybillstoreid);
$sqlQuery->set($returnbuybill->returnbuybillsysdate);
$sqlQuery->set($returnbuybill->comment);
$sqlQuery->setNumber($returnbuybill->userid);
$sqlQuery->setNumber($returnbuybill->conditions);
$sqlQuery->set($returnbuybill->returnbuybillclientname);
$sqlQuery->setNumber($returnbuybill->returnbuybillsaveid);
$sqlQuery->setNumber($returnbuybill->billnameid);
$sqlQuery->setNumber($returnbuybill->returnbuybilldirectpayment);
$sqlQuery->setNumber($returnbuybill->deletedbuyid);
$sqlQuery->set($returnbuybill->controlname);
$sqlQuery->set($returnbuybill->payedtax);
$sqlQuery->set($returnbuybill->costcenterid);
$sqlQuery->set((int) $returnbuybill->currencyId);
$sqlQuery->setNumber((float) $returnbuybill->taxOfDiscountPer);
$sqlQuery->setNumber((float) $returnbuybill->taxOfDiscountVal);
$sqlQuery->setNumber((int) $returnbuybill->client);
$id = $this->executeInsert($sqlQuery);
$returnbuybill->returnbuybillid = $id;
return $id;
}
public function queryAllSerials() {
$sql = 'SELECT returnbuybill.returnbuybillSerial, returnbuybillid
FROM returnbuybill
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllSerialsNotDeleted() {
$sql = 'SELECT returnbuybill.returnbuybillSerial, returnbuybillid
FROM returnbuybill
WHERE conditions = 0
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
WHERE returnbuybillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function loadExt2($id, $queryString = '') {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName,delbyuser.username as delbyusername
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user as delbyuser
ON delbyuser.userid = returnbuybill.delbyuserid
WHERE returnbuybillid = ? ' . $queryString . '
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function queryByConditionExt($value) {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
WHERE returnbuybill.conditions = ?
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByConditionExtLimited($value, $start, $end) {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
WHERE returnbuybill.conditions = ?
ORDER BY returnbuybillid DESC
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryByDateExt($from, $to, $queryString = '') {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
LEFT JOIN user as delbyuser
ON delbyuser.userid = returnbuybill.delbyuserid
WHERE returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '" ' . $queryString . '
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryBySupplierId($value, $queryString = '') {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
LEFT JOIN user as delbyuser
ON delbyuser.userid = returnbuybill.delbyuserid
WHERE returnbuybillsupplierid = ? ' . $queryString . '
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryBySupplierIdLimited($value, $start, $end) {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
WHERE returnbuybillsupplierid = ?
ORDER BY returnbuybillid DESC
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($value);
return $this->getList($sqlQuery);
}
public function queryBySerial($value, $queryString = '') {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
LEFT JOIN user as delbyuser
ON delbyuser.userid = returnbuybill.delbyuserid
WHERE returnbuybillSerial = ? ' . $queryString . '
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function queryBySerialLimited($value, $start, $end) {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
WHERE returnbuybillSerial = ?
ORDER BY returnbuybillid DESC
limit ' . $start . ', ' . $end . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function updateConditions($returnbuybill) {
$sql = 'UPDATE returnbuybill SET returnbuybillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? WHERE returnbuybillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($returnbuybill->returnbuybillsysdate);
$sqlQuery->setNumber($returnbuybill->userid);
$sqlQuery->setNumber($returnbuybill->delbyuserid);
$sqlQuery->setNumber($returnbuybill->conditions);
$sqlQuery->setNumber($returnbuybill->returnbuybillid);
return $this->executeUpdate($sqlQuery);
}
public function queryWithSupplierIdAndDate($supplierid, $from, $to) {
$sql = 'SELECT returnbuybill.returnbuybillid, returnbuybillSerial, returnbuybilldate, returnbuybillaftertotalbill
FROM returnbuybill
LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid
WHERE returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"
AND returnbuybill.returnbuybillsupplierid = ' . $supplierid . '
AND returnbuybill.conditions = 0
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryWithSupplierIdAndDateNew($queryString) {
$sql = 'SELECT returnbuybill.returnbuybillid, returnbuybill.comment, returnbuybillSerial, returnbuybilldate, returnbuybillaftertotalbill,returnbuybilltotalpayed
FROM returnbuybill
LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid
WHERE 1 ' . $queryString . '
AND returnbuybill.conditions = 0
ORDER BY returnbuybillid DESC';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryBystring($queryString) {
$sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
FROM returnbuybill LEFT JOIN supplier
ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
ON returnbuybill.returnbuybillstoreid = store.storeId
LEFT JOIN user
ON user.userid = returnbuybill.userid
' . $queryString . '
AND returnbuybill.conditions=0
ORDER BY returnbuybillid DESC';
// print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT returnbuybill.* , s.suppliername as suppliername,u.employeename as username
FROM returnbuybill
join supplier s on returnbuybill.returnbuybillsupplierid = s.supplierid
join user u ON u.userid = returnbuybill.userid
' . $queryString . ' AND returnbuybill.conditions = 0
AND (returnbuybill.deletedbuyid=0 or returnbuybill.deletedbuyid is null)';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderSuppliersWithReturnBuybillsBuys($queryString) {
$sql = 'SELECT returnbuybill.returnbuybillsupplierid , SUM(returnbuybill.returnbuybilltotalbill) as allGenTotalReturn , SUM(returnbuybill.returnbuybillaftertotalbill) as returnbuybillaftertotalbill ,
COUNT(returnbuybill.returnbuybillsupplierid ) , supplier.suppliername as suppliername FROM `returnbuybill`
join supplier on supplier.supplierid = returnbuybill.returnbuybillsupplierid
' . $queryString . ' GROUP BY returnbuybill.returnbuybillsupplierid
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}