File: /home/mostafedeg/public_html/erp/models/mysql/ext/ReturnsellbillMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'returnsellbill'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class ReturnsellbillMySqlExtDAO extends ReturnsellbillMySqlDAO {
public function getSellBillsUsingQueryString($queryString1) {
$sql = 'SELECT returnsellbill.*
FROM returnsellbill
' . $queryString1 . '
AND returnsellbill.deletedsellid=0 ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGenerale($returnsellbill) {
$sql = 'SELECT *
FROM returnsellbill
' . $returnsellbill;
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function insertupdate($returnsellbill) {
$sql = 'INSERT INTO returnsellbill (returnsellbilldate, returnsellbillserial, returnsellbillclientid, returnsellbilltotaldeptbefor, returnsellbilltotaldeptafter, returnsellbilldiscount, returnsellbilldiscounttype, returnsellbilltotalbill, returnsellbillaftertotalbill, returnsellbilltotalpayed, returnsellbillfinalbill, returnsellbillstoreid, returnsellbillsysdate,comment, userid, conditions, returnsellbillclientname, returnsellbillsaveid, returnsellbildirectpayment, billnameid, sellerid, tempclientName, returnsellQuantity,deletedsellid,controlname,tax, carnumber, cartype, carchase, carmotor,costcenterid,visaAccountId,cashPayed,visaPayed,isBankAccountTransfer,extraDiscountPer,extraDiscountVal,taxOfDiscountPer,taxOfDiscountVal,obgyBillType,billoperationid,pricetype,currencyId,sellbillno) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($returnsellbill->returnsellbilldate);
$sqlQuery->set($returnsellbill->returnsellbillserial);
$sqlQuery->setNumber($returnsellbill->returnsellbillclientid);
$sqlQuery->set($returnsellbill->returnsellbilltotaldeptbefor);
$sqlQuery->set($returnsellbill->returnsellbilltotaldeptafter);
$sqlQuery->set($returnsellbill->returnsellbilldiscount);
$sqlQuery->setNumber($returnsellbill->returnsellbilldiscounttype);
$sqlQuery->set($returnsellbill->returnsellbilltotalbill);
$sqlQuery->set($returnsellbill->returnsellbillaftertotalbill);
$sqlQuery->set($returnsellbill->returnsellbilltotalpayed);
$sqlQuery->set($returnsellbill->returnsellbillfinalbill);
$sqlQuery->setNumber($returnsellbill->returnsellbillstoreid);
$sqlQuery->set($returnsellbill->returnsellbillsysdate);
$sqlQuery->set($returnsellbill->comment);
$sqlQuery->setNumber($returnsellbill->userid);
$sqlQuery->setNumber($returnsellbill->conditions);
$sqlQuery->set($returnsellbill->returnsellbillclientname);
$sqlQuery->setNumber($returnsellbill->returnsellbillsaveid);
$sqlQuery->setNumber($returnsellbill->returnsellbildirectpayment);
$sqlQuery->setNumber($returnsellbill->billnameid);
$sqlQuery->setNumber($returnsellbill->sellerid);
$sqlQuery->set($returnsellbill->tempclientName);
$sqlQuery->setNumber($returnsellbill->returnsellQuantity);
$sqlQuery->setNumber($returnsellbill->deletedsellid);
$sqlQuery->set($returnsellbill->controlname);
$sqlQuery->set($returnsellbill->tax);
$sqlQuery->set($returnsellbill->carnumber);
$sqlQuery->set($returnsellbill->cartype);
$sqlQuery->set($returnsellbill->carchase);
$sqlQuery->set($returnsellbill->carmotor);
$sqlQuery->setNumber($returnsellbill->costcenterid);
$sqlQuery->setNumber((int) $returnsellbill->visaAccountId);
$sqlQuery->setNumber((float) $returnsellbill->cashPayed);
$sqlQuery->setNumber((float) $returnsellbill->visaPayed);
$sqlQuery->setNumber((int) $returnsellbill->isBankAccountTransfer);
$sqlQuery->setNumber((float) $returnsellbill->extraDiscountPer);
$sqlQuery->setNumber((float) $returnsellbill->extraDiscountVal);
$sqlQuery->setNumber((float) $returnsellbill->taxOfDiscountPer);
$sqlQuery->setNumber((float) $returnsellbill->taxOfDiscountVal);
$sqlQuery->setNumber((int) $returnsellbill->obgyBillType);
$sqlQuery->setNumber((int) $returnsellbill->billoperationid);
$sqlQuery->setNumber((int) $returnsellbill->pricetype);
$sqlQuery->setNumber((int) $returnsellbill->currencyId);
$sqlQuery->set($returnsellbill->sellbillno);
$id = $this->executeInsert($sqlQuery);
$returnsellbill->returnsellbillid = $id;
return $id;
}
public function loadReturnsellbillById($returnsellbillid) {
$sql = 'SELECT returnsellbill.*, client.clientname,
store.storeName, user.employeename , user2.employeename as useremployeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid left JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
LEFT JOIN user as user2
ON returnsellbill.userid = user2.userid
WHERE returnsellbill.returnsellbillid = ' . $returnsellbillid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function updateConditions($returnsellbill, $query1 = '') {
$sql = 'UPDATE returnsellbill SET returnsellbillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? ' . $query1 . ' WHERE returnsellbillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($returnsellbill->returnsellbillsysdate);
$sqlQuery->setNumber($returnsellbill->userid);
$sqlQuery->setNumber($returnsellbill->delbyuserid);
$sqlQuery->setNumber($returnsellbill->conditions);
$sqlQuery->setNumber($returnsellbill->returnsellbillid);
return $this->executeUpdate($sqlQuery);
}
public function queryWithOrder($queryString = '') {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where 1 ' . $queryString . '
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithOrderLimited($start, $end) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
order by returnsellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientId($clientid) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbillclientid =' . $clientid . '
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientId2($clientid) {
$sql = 'SELECT
SUM( returnsellbill.returnsellbillaftertotalbill ) AS allGenTotalReturn
FROM returnsellbill JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid
where returnsellbill.returnsellbillclientid =' . $clientid . '
group by returnsellbill.returnsellbillclientid
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
// print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function queryWithClientIdLimited($start, $end, $clientid) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbillclientid =' . $clientid . '
order by returnsellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithReturnsellbillid($returnsellbillid) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbillid =' . $returnsellbillid . '
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSerail($serial) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbillserial ="' . $serial . '"
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSerailLimited($start, $end, $serial) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbillserial ="' . $serial . '"
order by returnsellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClinetIdAndDate($clientid, $startDate, $endDate) {
$sql = 'SELECT returnsellbill.returnsellbillid, returnsellbilldate, returnsellbillserial, returnsellbillaftertotalbill, returnsellQuantity
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid
where returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
AND returnsellbill.returnsellbillclientid = ' . $clientid . '
AND returnsellbill.conditions = 0
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDate($startDate, $endDate) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename , user2.employeename as useremployeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
LEFT JOIN user as user2
ON returnsellbill.userid = user2.userid
where returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallWithqueryString($queryString) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename , user2.employeename as useremployeename,storeName,savename,editedBill.returnsellbillid as newSellbillidAfterEdit,delbyuser.username as delbyusername
FROM returnsellbill
LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
JOIN store ON returnsellbill.returnsellbillstoreid = store.storeId
JOIN save ON returnsellbill.returnsellbillsaveid = save.saveid
LEFT JOIN user ON returnsellbill.sellerid = user.userid
LEFT JOIN user as user2 ON returnsellbill.userid = user2.userid
LEFT JOIN user as delbyuser ON delbyuser.userid = returnsellbill.delbyuserid
LEFT JOIN returnsellbill as editedBill ON returnsellbill.returnsellbillid = editedBill.deletedsellid
' . $queryString . '
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallWithqueryString2($queryString) {
$sql = 'SELECT returnsellbill.* , user.employeename , client.clientname,u.username FROM returnsellbill
LEFT JOIN user
ON returnsellbill.sellerid = user.userid
LEFT JOIN user u
ON returnsellbill.userid = u.userid
LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid
' . $queryString . '
AND returnsellbill.conditions=0
';
$sqlQuery = new SqlQuery($sql);
//print_r($sql.'<br>');
return $this->getList($sqlQuery);
}
public function queryWithDateLimited($start, $end, $startDate, $endDate) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
order by returnsellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditions($startDate, $endDate) {
$sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
ON returnsellbill.sellerid = user.userid
where returnsellbill.returnsellbilldate
>= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
And returnsellbill.conditions = 0
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsExt($startDate, $endDate) {
$sql = 'SELECT SUM(returnsellbill.returnsellbillaftertotalbill)
FROM returnsellbill
where returnsellbill.returnsellbilldate
>= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
And returnsellbill.conditions = 0';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->QuerySingleResult($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT returnsellbilltotalbill, returnsellbilldiscount, returnsellbilldiscounttype
FROM returnsellbill
WHERE returnsellbillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function queryAllforrate($sellerid, $startDate, $endDate) {
$sql = 'SELECT * FROM `returnsellbill` WHERE `sellerid` =' . $sellerid . ' and
returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateall($startDate, $endDate) {
$sql = 'SELECT * FROM `returnsellbill` WHERE
returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function GetReturnSellBillUsingQueryString($queryString) {
$sql = 'SELECT returnsellbill.* , user.employeename FROM returnsellbill
LEFT JOIN user
ON returnsellbill.sellerid = user.userid
LEFT JOIN user user2
ON returnsellbill.userid = user2.userid
' . $queryString . '
AND returnsellbill.conditions=0
';
//print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function checkIfThereIsABill() {
$sql = 'SELECT returnsellbill.*
FROM returnsellbill
WHERE returnsellbill.conditions=0 limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getSalesVal() {
$sql = 'SELECT returnsellbilldetail.*, product.productBuyPrice
FROM returnsellbill LEFT JOIN returnsellbilldetail
ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid JOIN product
ON product.productId = returnsellbilldetail.returnsellbilldetailproductid
where returnsellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT returnsellbill.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
FROM returnsellbill
join client c on returnsellbill.returnsellbillclientid = c.clientid
left join user u on returnsellbill.userid = u.userid
left join user s on returnsellbill.sellerid = s.userid
' . $queryString . ' AND returnsellbill.conditions = 0
AND returnsellbill.deletedsellid=0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral_f($queryString) {
$sql = 'SELECT returnsellbill.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
FROM returnsellbill
join client c on returnsellbill.returnsellbillclientid = c.clientid
left join user u on returnsellbill.userid = u.userid
left join user s on returnsellbill.sellerid = s.userid
' . $queryString . ' AND returnsellbill.conditions = 0
'; //AND returnsellbill.deletedsellid=0
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithReturnSellbillsSells($queryString) {
$sql = 'SELECT returnsellbill.returnsellbillclientid , SUM(returnsellbill.returnsellbilltotalbill) as allGenTotalReturn , SUM(returnsellbill.returnsellbillaftertotalbill) as returnsellbillaftertotalbill ,
COUNT(returnsellbill.returnsellbillclientid ) , client.clientname as clientname FROM `returnsellbill`
join client on client.clientid = returnsellbill.returnsellbillclientid
' . $queryString . ' GROUP BY returnsellbill.returnsellbillclientid
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithReturnSellbillsSells2($clientid, $returnSellBillsQuery) {
$sql = 'SELECT returnsellbill.returnsellbillclientid , SUM(returnsellbill.returnsellbilltotalbill) as allGenTotalReturn , SUM(returnsellbill.returnsellbilldiscount) as totaldiscount ,
COUNT(returnsellbill.returnsellbillclientid ) , client.clientname as clientname ,
SUM(returnsellbill.returnsellbilltotalpayed) as returnsellbilltotalpayed
FROM `returnsellbill`
join client on client.clientid = returnsellbill.returnsellbillclientid
where returnsellbill.returnsellbillclientid = ' . $clientid . '
' . $returnSellBillsQuery . ' GROUP BY returnsellbill.returnsellbillclientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function returnsellbillWithProducts($queryString) {
$sql = 'SELECT sum(returnsellbill.returnsellQuantity) as sumQuantity , sum(returnsellbill.returnsellbilltotalbill) as sumTotalbill ,
sum(returnsellbill.returnsellbilldiscount) as sumDiscount ,returnsellbill.returnsellbillclientid as returnsellbillclientid ,
product.productid as productid , product.productName as productName
FROM `returnsellbill`
join returnsellbilldetail on returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
join client on client.clientid = returnsellbill.returnsellbillclientid
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 = returnsellbilldetail.returnsellbilldetailproductid
WHERE ' . $queryString . ' GROUP BY returnsellbilldetail.returnsellbilldetailproductid order by client.clientid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientPayed($queryString1, $queryString2) {
$sql = 'SELECT returnsellbill.returnsellbillclientid,returnsellbill.returnsellbilltotalpayed
FROM `returnsellbill`
' . $queryString1 . ' ' . $queryString2 . '';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getAutoIncrementValue($DBName) {
$sql = 'SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "' . $DBName . '"
AND TABLE_NAME = "returnsellbill" ';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllGeneral_1($queryString, $sellerid) {
$sellerSql = "";
$sellerJoin = "";
if ($sellerid != 0 && $sellerid != -1) {
$sellerSql = " ,s.employeename as useremployeename ";
$sellerJoin = " join user s on sellbill.sellerid = s.userid ";
}
$sql = 'SELECT returnsellbill.* , c.clientname as clientname,u.employeename as employeename ' . $sellerSql . '
FROM returnsellbill
join client c on returnsellbill.returnsellbillclientid = c.clientid
join user u on returnsellbill.userid = u.userid
' . $sellerJoin . '
' . $queryString . ' AND returnsellbill.conditions = 0
AND returnsellbill.deletedsellid=0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClinetIdAndDate2($clientid, $date) {
$sql = 'SELECT returnsellbill.returnsellbillid, returnsellbilldate, returnsellbillserial, returnsellbillaftertotalbill, returnsellQuantity
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid
where ' . $date . '
returnsellbill.returnsellbillclientid = ' . $clientid . '
AND returnsellbill.conditions = 0
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
/////eman/////////////
public function queryWithClinetIdAndDateExt($clientid, $date) {
$sql = 'SELECT returnsellbill.returnsellbillid, returnsellbilldate, returnsellbillserial, returnsellbillaftertotalbill, returnsellQuantity, returnsellbill.conditions
FROM returnsellbill LEFT JOIN client
ON returnsellbill.returnsellbillclientid = client.clientid
where ' . $date . '
returnsellbill.returnsellbillclientid = ' . $clientid . '
order by returnsellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateWithSellerName($sellerid, $startDate, $endDate, $priceType) {
$sql = 'SELECT DISTINCT returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype FROM `returnsellbill`
LEFT JOIN user ON returnsellbill.sellerid = user.userid
LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
WHERE returnsellbill.`sellerid` =' . $sellerid . ' and returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and returnsellbilldetail.pricetype in( ' . $priceType . ') and returnsellbill.conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateallWithSellerName($startDate, $endDate, $priceType) {
$sql = 'SELECT DISTINCT returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype FROM `returnsellbill`
LEFT JOIN user ON returnsellbill.sellerid = user.userid
LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
WHERE returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and returnsellbilldetail.pricetype in( ' . $priceType . ') and returnsellbill.conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//emaaaaaaaaaan
public function queryAllforrateallWithoutSeller() {
$sql = 'SELECT DISTINCT returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype ,user.awardrate
FROM `returnsellbill`
LEFT JOIN user ON returnsellbill.sellerid = user.userid
LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
WHERE MONTH(returnsellbill.returnsellbilldate) = Month(CURRENT_DATE()) and returnsellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getBillsCountNotDel($queryString = '') {
$sql = 'SELECT count(returnsellbillid) as returnsellbillid
FROM returnsellbill where conditions = 0 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllOrderedLimitedSimple($start, $end) {
$sql = 'SELECT *
FROM returnsellbill
where conditions = 0
order by returnsellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedAscLimitedSimple($start, $end, $queryString) {
$sql = 'SELECT *
FROM returnsellbill
where conditions = 0
' . $queryString . '
order by returnsellbillid asc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function runSelectQuery($sql) {
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateLastReview($lastReviewType, $lastReviewUser, $lastReviewDateTime, $billId) {
$sql = 'UPDATE returnsellbill SET lastReviewType = ' . $lastReviewType . ',lastReviewUser = ' . $lastReviewUser . ',lastReviewDateTime= "' . $lastReviewDateTime . '" WHERE returnsellbillid = ' . $billId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateLastReviewAutoHandle($lastReviewUser, $lastReviewDateTime, $billId) {
$sql = 'UPDATE returnsellbill SET lastReviewType= CASE WHEN lastReviewType=0 THEN 1 WHEN lastReviewType=1 THEN 3 WHEN lastReviewType=3 THEN 4 WHEN lastReviewType=4 THEN 3 ELSE 1 END,lastReviewUser = ' . $lastReviewUser . ',lastReviewDateTime= "' . $lastReviewDateTime . '" WHERE returnsellbillid = ' . $billId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function ClientsWithReturnSellbillsSells($queryStringTy, $queryString1R) {
$sql = 'SELECT client.*,clientarea.name as areaName,government.governmentname as governmentname , returnsellbill.returnsellbillclientid , SUM(returnsellbill.returnsellbilltotalbill) as allGenTotalReturn , SUM(returnsellbill.returnsellbillaftertotalbill) as returnsellbillaftertotalbill ,
COUNT(returnsellbill.returnsellbillclientid ) , client.clientname as clientname FROM `client`
LEFT JOIN clientarea on client.clientareaid = clientarea.id
LEFT JOIN goverarea on clientarea.id = goverarea.clientareaid
LEFT JOIN government on goverarea.governmentid = government.governmetid
join returnsellbill on client.clientid = returnsellbill.returnsellbillclientid
' . $queryStringTy . ' ' . $queryString1R . ' ';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getBillsNumbersList($billNo) {
$sql = 'select returnsellbillid FROM returnsellbill WHERE conditions = 0 and returnsellbillid like "%' . $billNo . '%"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}
?>