File: /home/mostafedeg/public_html/erp/models/mysql/ext/SellbillMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'sellbill'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-06 10:57
*/
class SellbillMySqlExtDAO extends SellbillMySqlDAO {
public function getlastbill($clintid) {
$sql = 'SELECT * FROM `sellbill`
WHERE sellbillclientid = ' . $clintid . ' order by sellbill.sellbillid desc ';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function fixDiscountRestaurantFatma($billdate) {
$sql = 'update sellbill set sellbillaftertotalbill = sellbilltotalbill where date(sellbilldate) = "' . $billdate . '"';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getSellBillsUsingQueryString($queryString) {
$sql = 'SELECT sellbill.*
FROM sellbill
' . $queryString . '
AND sellbill.deletedsellid=0 ';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getSellBillCount() {
$sql = 'SELECT count(sellbillid) FROM sellbill';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllSellBill($sellId) {
$sql = 'SELECT sellbill.*
FROM sellbill
where sellbillid = "' . $sellId . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
/*
public function insertupdate($sellbill){
$sql = 'INSERT INTO sellbill (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, tempclientName, sellQuantity,deletedsellid,controlname,tax) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($sellbill->sellbilldate);
$sqlQuery->set($sellbill->sellbillserial);
$sqlQuery->setNumber($sellbill->sellbillclientid);
$sqlQuery->set($sellbill->sellbilltotaldeptbefor);
$sqlQuery->set($sellbill->sellbilltotaldeptafter);
$sqlQuery->set($sellbill->sellbilldiscount);
$sqlQuery->setNumber($sellbill->sellbilldiscounttype);
$sqlQuery->set($sellbill->sellbilltotalbill);
$sqlQuery->set($sellbill->sellbillaftertotalbill);
$sqlQuery->set($sellbill->sellbilltotalpayed);
$sqlQuery->set($sellbill->sellbillfinalbill);
$sqlQuery->setNumber($sellbill->sellbillstoreid);
$sqlQuery->set($sellbill->sellbillsysdate);
$sqlQuery->setNumber($sellbill->userid);
$sqlQuery->setNumber($sellbill->conditions);
$sqlQuery->set($sellbill->sellbillclientname);
$sqlQuery->setNumber($sellbill->sellbillsaveid);
$sqlQuery->setNumber($sellbill->sellbildirectpayment);
$sqlQuery->setNumber($sellbill->billnameid);
$sqlQuery->setNumber($sellbill->sellerid);
$sqlQuery->set($sellbill->tempclientName);
$sqlQuery->setNumber($sellbill->sellQuantity);
$sqlQuery->setNumber($sellbill->deletedsellid);
$sqlQuery->set($sellbill->controlname);
$sqlQuery->set($sellbill->tax);
$id = $this->executeInsert($sqlQuery);
$sellbill->sellbillid = $id;
return $id;
}
*/
public function insertupdate($sellbill) {
$sql = 'INSERT INTO sellbill (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate,comment, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, tempclientName, sellQuantity,deletedsellid,controlname,tax, carnumber, cartype, carchase, carmotor,costcenterid,webApiId,visaAccountId,cashPayed,visaPayed,isBankAccountTransfer,obygyVisitId,extraDiscountPer,extraDiscountVal,taxOfDiscountPer,taxOfDiscountVal,taxBillNumber,collectionType,collectionDate,obgyBillType,billoperationid,datestarting,pricetype,currencyId,knownwayId,travelDriverId,travelBusId,travelPathId,travelTripId,travelTripTypeId,travelTripDateId,totalprodiscount,manage_ex,med_hallmark,pay_rec_no,pay_rec_val,decisionNo,decisionValue) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($sellbill->sellbilldate);
$sqlQuery->set($sellbill->sellbillserial);
$sqlQuery->setNumber($sellbill->sellbillclientid);
$sqlQuery->set($sellbill->sellbilltotaldeptbefor);
$sqlQuery->set($sellbill->sellbilltotaldeptafter);
$sqlQuery->set($sellbill->sellbilldiscount);
$sqlQuery->setNumber($sellbill->sellbilldiscounttype);
$sqlQuery->set($sellbill->sellbilltotalbill);
$sqlQuery->set($sellbill->sellbillaftertotalbill);
$sqlQuery->set($sellbill->sellbilltotalpayed);
$sqlQuery->set($sellbill->sellbillfinalbill);
$sqlQuery->setNumber($sellbill->sellbillstoreid);
$sqlQuery->set($sellbill->sellbillsysdate);
$sqlQuery->set($sellbill->comment);
$sqlQuery->setNumber($sellbill->userid);
$sqlQuery->setNumber($sellbill->conditions);
$sqlQuery->set($sellbill->sellbillclientname);
$sqlQuery->setNumber($sellbill->sellbillsaveid);
$sqlQuery->setNumber($sellbill->sellbildirectpayment);
$sqlQuery->setNumber($sellbill->billnameid);
$sqlQuery->setNumber($sellbill->sellerid);
$sqlQuery->set($sellbill->tempclientName);
$sqlQuery->setNumber($sellbill->sellQuantity);
$sqlQuery->setNumber($sellbill->deletedsellid);
$sqlQuery->set($sellbill->controlname);
$sqlQuery->set($sellbill->tax);
$sqlQuery->set($sellbill->carnumber);
$sqlQuery->set($sellbill->cartype);
$sqlQuery->set($sellbill->carchase);
$sqlQuery->set($sellbill->carmotor);
$sqlQuery->setNumber($sellbill->costcenterid);
$sqlQuery->setNumber((int) $sellbill->webApiId);
$sqlQuery->setNumber((int) $sellbill->visaAccountId);
$sqlQuery->setNumber((float) $sellbill->cashPayed);
$sqlQuery->setNumber((float) $sellbill->visaPayed);
$sqlQuery->setNumber((int) $sellbill->isBankAccountTransfer);
$sqlQuery->setNumber((int) $sellbill->obygyVisitId);
$sqlQuery->setNumber((float) $sellbill->extraDiscountPer);
$sqlQuery->setNumber((float) $sellbill->extraDiscountVal);
$sqlQuery->setNumber((float) $sellbill->taxOfDiscountPer);
$sqlQuery->setNumber((float) $sellbill->taxOfDiscountVal);
$sqlQuery->setNumber((int) $sellbill->taxBillNumber);
$sqlQuery->setNumber((int) $sellbill->collectionType);
$sqlQuery->set($sellbill->collectionDate);
$sqlQuery->setNumber((int) $sellbill->obgyBillType);
$sqlQuery->setNumber((int) $sellbill->billoperationid);
$sqlQuery->set($sellbill->datestarting);
$sqlQuery->setNumber((int) $sellbill->pricetype);
$sqlQuery->setNumber((int) $sellbill->currencyId);
$sqlQuery->setNumber((int) $sellbill->knownwayId);
$sqlQuery->setNumber((int) $sellbill->travelDriverId);
$sqlQuery->setNumber((int) $sellbill->travelBusId);
$sqlQuery->setNumber((int) $sellbill->travelPathId);
$sqlQuery->setNumber((int) $sellbill->travelTripId);
$sqlQuery->setNumber((int) $sellbill->travelTripTypeId);
$sqlQuery->setNumber((int) $sellbill->travelTripDateId);
$sqlQuery->set($sellbill->totalprodiscount);
$sqlQuery->set($sellbill->manage_ex);
$sqlQuery->set($sellbill->med_hallmark);
$sqlQuery->set($sellbill->pay_rec_no);
$sqlQuery->set($sellbill->pay_rec_val);
$sqlQuery->set($sellbill->decisionNo);
$sqlQuery->set($sellbill->decisionValue);
$id = $this->executeInsert($sqlQuery);
$sellbill->sellbillid = $id;
return $id;
}
public function loadExt($sellbillid) {
$sql = 'SELECT sellbill.sellbilltotalbill, sellbilldiscount, sellbilldiscounttype
FROM sellbill
WHERE sellbill.sellbillid = ' . $sellbillid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function loadExtWithClient($sellbillid) {
$sql = 'SELECT sellbill.sellbilltotalbill, sellbilldiscount, sellbilldiscounttype, client.clientname
FROM sellbill
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
WHERE sellbill.sellbillid = ' . $sellbillid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function loadSellBillById($sellbillid) {
$sql = 'SELECT sellbill.*, client.clientname, client.taxnumber, store.storeName,store.storeDescription, user.employeename , user2.employeename as useremployeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
LEFT JOIN user as user2
ON sellbill.userid = user2.userid
WHERE sellbill.sellbillid = ' . $sellbillid . ' ';
// print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function loadSellBillByIdMany($sellbillid) {
$sql = 'SELECT sellbill.*, client.clientname, store.storeName, user.employeename , user2.employeename as useremployeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
LEFT JOIN user as user2
ON sellbill.userid = user2.userid
WHERE sellbill.sellbillid in ( ' . $sellbillid . ' ) '; //using in for Bulk print of sell bill
// print_r('<br>'.$sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadlastSellBillByclient($clientid) {
$sql = 'SELECT sellbillclientid, `sellbillid` , `sellbilldate`
FROM `sellbill`
WHERE sellbillclientid = ' . $clientid . ' ORDER BY `sellbilldate` desc ,sellbillid desc LIMIT 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithOrder() {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename , user2.employeename as useremployeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
LEFT JOIN user as user2
ON sellbill.userid = user2.userid
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallWithOrder($queryString) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename , user2.employeename as useremployeename,storeName,savename,editedBill.sellbillid as newSellbillidAfterEdit,delbyuser.username as delbyusername
FROM sellbill
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
JOIN store ON sellbill.sellbillstoreid = store.storeId
JOIN save ON sellbill.sellbillsaveid = save.saveid
LEFT JOIN user ON sellbill.sellerid = user.userid
LEFT JOIN user as user2 ON sellbill.userid = user2.userid
LEFT JOIN user as delbyuser ON delbyuser.userid = sellbill.delbyuserid
LEFT JOIN sellbill as editedBill ON sellbill.sellbillid = editedBill.deletedsellid
' . $queryString . '
ORDER BY `sellbilldate` desc ,sellbillid desc ';
// print_r($sql . '<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithOrderLimited($start, $end) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename , user2.employeename as useremployeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
LEFT JOIN user as user2
ON sellbill.userid = user2.userid
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientId($clientid) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbillclientid =' . $clientid . '
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientId2($clientid) {
$sql = 'SELECT
SUM( sellbill.sellbillaftertotalbill ) AS allGenTotal
FROM sellbill JOIN client
ON sellbill.sellbillclientid = client.clientid
where sellbill.sellbillclientid =' . $clientid . '
group by sellbill.sellbillclientid
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
public function queryWithClientIdLimited($start, $end, $clientid) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbillclientid =' . $clientid . '
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSerail($serial) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbillserial ="' . $serial . '"
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSellbillId($sellbillid) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbillid =' . $sellbillid . '
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSerailLimited($start, $end, $serial) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbillserial ="' . $serial . '"
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientIdAndDate($clientId, $startDate, $endDate) {
$sql = 'SELECT sellbill.sellbillid, sellbill.sellbilldate, sellbill.sellbillserial, sellbill.sellbillaftertotalbill, sellbill.sellQuantity, sellbill.sellbilltotalpayed
FROM sellbill
JOIN client
ON sellbill.sellbillclientid = client.clientid
where sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
AND sellbill.sellbillclientid = ' . $clientId . '
AND sellbill.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryWithDate($startDate, $endDate) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querybyqueryString($queryString) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename,u.employeename as username
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
LEFT JOIN user u
ON u.userid = sellbill.userid
' . $queryString . '
AND sellbill.conditions=0
order by sellbilldate desc, sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateLimited($start, $end, $startDate, $endDate) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditions($startDate, $endDate) {
$sql = 'SELECT sellbill.*, client.clientname, user.employeename
FROM sellbill LEFT JOIN client
ON sellbill.sellbillclientid = client.clientid JOIN store
ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbill.sellerid = user.userid
where sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
and sellbill.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsExt($startDate, $endDate) {
$sql = 'SELECT SUM(sellbill.sellbillaftertotalbill)
FROM sellbill
where sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
and sellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function updateConditions($sellbill, $query1 = '') {
$sql = 'UPDATE sellbill SET sellbillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? ' . $query1 . ' WHERE sellbillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($sellbill->sellbillsysdate);
$sqlQuery->setNumber($sellbill->userid);
$sqlQuery->setNumber($sellbill->delbyuserid);
$sqlQuery->setNumber($sellbill->conditions);
$sqlQuery->setNumber($sellbill->sellbillid);
return $this->executeUpdate($sqlQuery);
}
public function queryAllforrate($sellerid, $startDate, $endDate) {
$sql = 'SELECT * FROM `sellbill` WHERE `sellerid` =' . $sellerid . ' and
sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" and conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateall($startDate, $endDate) {
$sql = 'SELECT * FROM `sellbill` WHERE
sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" and conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function GetSellBillUsingQueryString($queryString) {
$sql = 'SELECT * FROM sellbill
' . $queryString . '
AND sellbill.conditions=0
';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadlast($queryString = '') {
$sql = 'SELECT * FROM `sellbill`
LEFT JOIN user user2 ON sellbill.userid = user2.userid
where 1
' . $queryString . '
ORDER BY `sellbillid` DESC LIMIT 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function checkIfThereIsABill() {
$sql = 'SELECT * FROM sellbill
WHERE sellbill.conditions=0 limit 1
';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getsellbilldetail($sellId) {
$sql = 'SELECT parcode
FROM sellbilldetail
where sellbillid = "' . $sellId . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getSalesVal() {
$sql = 'SELECT sellbilldetail.*, product.productBuyPrice
FROM sellbill LEFT JOIN sellbilldetail
ON sellbill.sellbillid = sellbilldetail.sellbillid JOIN product
ON product.productId = sellbilldetail.sellbilldetailproductid
where sellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforreceipt($queryString) {
$sql = 'SELECT sellbillid,sellbillserial,sellbilldate,sellbilltotalpayed,sellbillfinalbill,sellbillclientid FROM sellbill
' . $queryString . '
AND sellbill.conditions=0
AND sellbill.deletedsellid=0
order by sellbilldate asc
';
//print_r("<br/>".$sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updatePayedAndRemain($sellbill) {
$sql = 'UPDATE sellbill SET sellbilltotalpayed = ' . $sellbill->sellbilltotalpayed . ' , sellbillfinalbill = ' . $sellbill->sellbillfinalbill . ' WHERE sellbillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($sellbill->sellbillid);
return $this->executeUpdate($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT sellbill.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
FROM sellbill
join client c on sellbill.sellbillclientid = c.clientid
left join user u on sellbill.userid = u.userid
left join user s on sellbill.sellerid = s.userid
' . $queryString . ' AND sellbill.conditions = 0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
// public function queryAllGeneral($queryString) {
// $sql = 'SELECT sellbill.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
// FROM sellbill
// join client c on sellbill.sellbillclientid = c.clientid
// join user u on sellbill.userid = u.userid
// join user s on sellbill.sellerid = s.userid
// ' . $queryString . ' AND sellbill.conditions = 0';
// //echo $sql . "<br/>";
// $sqlQuery = new SqlQuery($sql);
// return $this->getList($sqlQuery);
// }
public function queryAllGeneral_2($sellbill) {
$sql = 'SELECT *
FROM sellbill
' . $sellbill;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllcount() {
$sql = 'SELECT count(*) FROM sellbill';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithSellbillsSells($queryString) {
$sql = 'SELECT sellbill.sellbillclientid , SUM(sellbill.sellbilltotalbill) as allGenTotal , SUM(sellbill.sellbillaftertotalbill) as sellbillaftertotalbill ,
COUNT(sellbill.sellbillclientid ) , client.clientname as clientname , client.clientdebt as clientdebt FROM `sellbill`
join client on client.clientid = sellbill.sellbillclientid
' . $queryString . ' GROUP BY sellbill.sellbillclientid
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithSellbillsSells2($clientid, $sellBillsQuery) {
$sql = 'SELECT sellbill.sellbillclientid , SUM(sellbill.sellbilltotalbill) as allGenTotal , SUM(sellbill.sellbilldiscount) as totaldiscount ,
COUNT(sellbill.sellbillclientid ) , client.clientname as clientname ,
SUM(sellbill.sellbilltotalpayed) as sellbilltotalAllPayed
FROM `sellbill`
join client on client.clientid = sellbill.sellbillclientid
Where sellbill.sellbillclientid = ' . $clientid . '
' . $sellBillsQuery . ' GROUP BY sellbill.sellbillclientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function sellbillWithProducts($queryString) {
$sql = 'SELECT sum(sellbill.sellQuantity) as sumQuantity , sum(sellbill.sellbilltotalbill) as sumTotalbill , sum(sellbill.sellbilldiscount) as sumDiscount ,
sellbill.sellbillclientid as sellbillclientid ,product.productid as productid , product.productName as productName
FROM `sellbill`
join sellbilldetail on sellbilldetail.sellbillid = sellbill.sellbillid
join client on client.clientid = sellbill.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 = sellbilldetail.sellbilldetailproductid
WHERE ' . $queryString . ' GROUP BY sellbilldetail.sellbilldetailproductid
order by client.clientid';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientPayed($queryString1, $queryString2) {
$sql = 'SELECT sellbill.sellbillclientid,sellbill.sellbilltotalpayed
FROM `sellbill`
' . $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 = "sellbill" ';
$sqlQuery = new SqlQuery($sql); //
return $this->querySingleResult($sqlQuery);
}
public function isSerialUnique($value, $oldid) {
$sql = 'SELECT sellbillid FROM sellbill WHERE sellbillserial = "' . $value . '" and sellbillid != ' . $oldid . ' and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($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 sellbill.* , c.clientname as clientname,u.employeename as employeename ' . $sellerSql . '
FROM sellbill
join client c on sellbill.sellbillclientid = c.clientid
join user u on sellbill.userid = u.userid
' . $sellerJoin . '
' . $queryString . ' AND sellbill.conditions = 0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientIdAndDate2($clientId, $date) {
$sql = 'SELECT sellbill.sellbillid, sellbill.sellbilldate, sellbill.sellbillserial, sellbill.sellbillaftertotalbill, sellbill.sellQuantity, sellbill.sellbilltotalpayed
FROM sellbill
JOIN client
ON sellbill.sellbillclientid = client.clientid
where ' . $date . '
sellbill.sellbillclientid = ' . $clientId . '
AND sellbill.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
//emaaaaaaaaaaaaan
public function queryWithClientIdAndDateExt($clientId, $date) {
$sql = 'SELECT sellbill.sellbillid, sellbill.sellbilldate, sellbill.sellbillserial, sellbill.sellbillaftertotalbill, sellbill.sellQuantity, sellbill.sellbilltotalpayed,sellbill.conditions
FROM sellbill
JOIN client
ON sellbill.sellbillclientid = client.clientid
where ' . $date . '
sellbill.sellbillclientid = ' . $clientId . '
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
// print($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateWithSellerName($sellerid, $startDate, $endDate, $priceType) {
$sql = 'SELECT sellbill.*, user.username, client.clientname, sellbilldetail.pricetype FROM `sellbill`
LEFT JOIN user ON sellbill.sellerid = user.userid
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
JOIN sellbilldetail ON sellbilldetail.sellbillid = sellbill.sellbillid
WHERE `sellerid` = ' . $sellerid . ' and
sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" and sellbilldetail.pricetype in( ' . $priceType . ') and sellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//emaaaaan
public function queryAllforrateWithSellerNameDistinct($sellerid, $startDate, $endDate, $priceType) {
$sql = 'SELECT DISTINCT sellbill.*, user.username, client.clientname, sellbilldetail.pricetype FROM `sellbill`
LEFT JOIN user ON sellbill.sellerid = user.userid
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
JOIN sellbilldetail ON sellbilldetail.sellbillid = sellbill.sellbillid
WHERE `sellerid` = ' . $sellerid . ' and
sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" and sellbilldetail.pricetype in( ' . $priceType . ') and sellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateallWithSellerName($startDate, $endDate, $priceType) {
$sql = 'SELECT DISTINCT sellbill.*, user.username, client.clientname, sellbilldetail.pricetype FROM `sellbill`
LEFT JOIN user ON sellbill.sellerid = user.userid
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
JOIN sellbilldetail ON sellbilldetail.sellbillid = sellbill.sellbillid
WHERE sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '" and sellbilldetail.pricetype in( ' . $priceType . ') and sellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//emaaaaaaan
public function queryAllforrateallWithoutSeller() {
$sql = 'SELECT DISTINCT sellbill.*, user.username, client.clientname, sellbilldetail.pricetype FROM `sellbill`
LEFT JOIN user ON sellbill.sellerid = user.userid
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
JOIN sellbilldetail ON sellbilldetail.sellbillid = sellbill.sellbillid
WHERE MONTH(sellbill.sellbilldate) = Month(CURRENT_DATE()) and sellbill.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getBillsCountNotDel($queryString = '') {
$sql = 'SELECT count(sellbillid) as sellbillid
FROM sellbill where conditions = 0 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllOrderedLimitedSimple($start, $end) {
$sql = 'SELECT *
FROM sellbill
where conditions = 0
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllOrderedAscLimitedSimple($start, $end, $queryString) {
$sql = 'SELECT *
FROM sellbill
where conditions = 0
' . $queryString . '
order by sellbillid 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 executeSQLQuery($sql) {
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function executeInsertSQLQuery($sql) {
$sqlQuery = new SqlQuery($sql);
return $this->executeInsert($sqlQuery);
}
public function uerrazmy($clientID, $queryStringsellbill) {
$sql = 'SELECT sellbill.*
FROM sellbill where sellbillclientid =' . $clientID . ' ' . $queryStringsellbill;
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateLastReview($lastReviewType, $lastReviewUser, $lastReviewDateTime, $billId) {
$sql = 'UPDATE sellbill SET lastReviewType = ' . $lastReviewType . ',lastReviewUser = ' . $lastReviewUser . ',lastReviewDateTime= "' . $lastReviewDateTime . '" WHERE sellbillid = ' . $billId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateLastReviewAutoHandle($lastReviewUser, $lastReviewDateTime, $billId) {
$sql = 'UPDATE sellbill 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 sellbillid = ' . $billId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function ClientsWithSellbillsSells2($queryStringTy, $queryString1) {
$sql = 'SELECT client.*,government.governmentname as governmentname ,clientarea.name as areaName,sellbill.sellbillclientid , SUM(sellbill.sellbilltotalbill) as allGenTotal , SUM(sellbill.sellbillaftertotalbill) as sellbillaftertotalbill ,
COUNT(sellbill.sellbillclientid ) , client.clientname as clientname , client.clientdebt as clientdebt 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 sellbill on client.clientid = sellbill.sellbillclientid
' . $queryStringTy . ' ' . $queryString1 . ' ';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientSellStatistics($clientId, $queryString = '') {
$sql = 'SELECT sellbilldetailproductid,sizeid,colorid,count(sellbill.sellbillid) as sellbillid
FROM sellbill
JOIN sellbilldetail ON (sellbill.sellbillid = sellbilldetail.sellbillid and sellbill.conditions = 0 and sellbillclientid = ' . $clientId . ')
where 1
' . $queryString . '
group by sellbilldetailproductid,sizeid,colorid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateshippingDate($shippingDate, $billId) {
$sql = 'UPDATE sellbill SET shippingDate = "' . $shippingDate . '" WHERE sellbillid = ' . $billId;
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getNextTaxBillNumber($DBName) {
$sql = 'SELECT taxBillNumber
FROM sellbill
WHERE taxBillNumber > 0
order by sellbillid desc limit 1';
$sqlQuery = new SqlQuery($sql); //
return $this->querySingleResult($sqlQuery);
}
public function getBillsNumbersList($billNo) {
$sql = 'select sellbillid FROM sellbill WHERE conditions = 0 and sellbillid like "%' . $billNo . '%"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function loadByObygyVisitId($id) {
$sql = 'SELECT * FROM sellbill WHERE obygyVisitId = ? and conditions = 0 ';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function queryAllByKnownWay($wayid) {
$sql = 'SELECT sellbill.*
FROM sellbill
where knownwayId = "' . $wayid . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryClientCountByKnownWay($wayid) {
$sql = 'SELECT count(DISTINCT sellbillclientid)
FROM sellbill
where knownwayId = "' . $wayid . '"
and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryClientByKnownWay($wayid) {
$sql = 'SELECT DISTINCT sellbillclientid
FROM sellbill
where knownwayId = "' . $wayid . '"
and conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}
?>