File: /home/mostafedeg/public_html/erp/models/mysql/ext/SellbillandruternMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'sellbillandrutern'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2013-04-29 13:54
*/
class SellbillandruternMySqlExtDAO extends SellbillandruternMySqlDAO {
public function getSellBillsUsingQueryString($queryString1) {
$sql = 'SELECT sellbillandrutern.*
FROM sellbillandrutern
' . $queryString1 . '
AND sellbillandrutern.deletedsellid=0 ';
$sqlQuery = new SqlQuery($sql);
// print_r($sqlQuery);
return $this->getList($sqlQuery);
}
public function loadlastSellBillByclient($clientid) {
$sql = 'SELECT sellbillclientid, `sellbillid` , `sellbilldate`
FROM `sellbillandrutern`
WHERE sellbillclientid = ' . $clientid . ' ORDER BY `sellbilldate` desc ,sellbillid desc LIMIT 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithClientId2($clientid) {
$sql = 'SELECT
SUM( sellbillandrutern.sellbillaftertotalbill ) AS allGenTotalSellAndReturn
FROM sellbillandrutern JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
where sellbillandrutern.sellbillclientid =' . $clientid . '
group by sellbillandrutern.sellbillclientid
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print_r($sqlQuery);
return $this->getRow($sqlQuery);
}
/*
public function insertupdate($sellbillandrutern){
$sql = 'INSERT INTO sellbillandrutern (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, sellbillprice, returnsellbillprice, tempclientName,sellQuantity, returnsellQuantity,deletedsellid,controlname ,tax) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($sellbillandrutern->sellbilldate);
$sqlQuery->set($sellbillandrutern->sellbillserial);
$sqlQuery->setNumber($sellbillandrutern->sellbillclientid);
$sqlQuery->set($sellbillandrutern->sellbilltotaldeptbefor);
$sqlQuery->set($sellbillandrutern->sellbilltotaldeptafter);
$sqlQuery->set($sellbillandrutern->sellbilldiscount);
$sqlQuery->setNumber($sellbillandrutern->sellbilldiscounttype);
$sqlQuery->set($sellbillandrutern->sellbilltotalbill);
$sqlQuery->set($sellbillandrutern->sellbillaftertotalbill);
$sqlQuery->set($sellbillandrutern->sellbilltotalpayed);
$sqlQuery->set($sellbillandrutern->sellbillfinalbill);
$sqlQuery->setNumber($sellbillandrutern->sellbillstoreid);
$sqlQuery->set($sellbillandrutern->sellbillsysdate);
$sqlQuery->setNumber($sellbillandrutern->userid);
$sqlQuery->setNumber($sellbillandrutern->conditions);
$sqlQuery->set($sellbillandrutern->sellbillclientname);
$sqlQuery->setNumber($sellbillandrutern->sellbillsaveid);
$sqlQuery->setNumber($sellbillandrutern->sellbildirectpayment);
$sqlQuery->setNumber($sellbillandrutern->billnameid);
$sqlQuery->setNumber($sellbillandrutern->sellerid);
$sqlQuery->set($sellbillandrutern->sellbillprice);
$sqlQuery->set($sellbillandrutern->returnsellbillprice);
$sqlQuery->set($sellbillandrutern->tempclientName);
$sqlQuery->setNumber($sellbillandrutern->sellQuantity);
$sqlQuery->setNumber($sellbillandrutern->returnsellQuantity);
$sqlQuery->setNumber($sellbillandrutern->deletedsellid);
$sqlQuery->set($sellbillandrutern->controlname);
$sqlQuery->set($sellbillandrutern->tax);
$id = $this->executeInsert($sqlQuery);
$sellbillandrutern->sellbillid = $id;
return $id;
}
*/
public function insertupdate($sellbillandrutern) {
$sql = 'INSERT INTO sellbillandrutern (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate,comment, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, sellbillprice, returnsellbillprice, tempclientName,sellQuantity, returnsellQuantity,deletedsellid,controlname ,tax, carnumber, cartype, carchase, carmotor,costcenterid,dailyentryid,visaAccountId,cashPayed,visaPayed,isBankAccountTransfer,extraDiscountPer,extraDiscountVal,taxOfDiscountPer,taxOfDiscountVal,obgyBillType,billoperationid,pricetype,currencyId,sellbillno) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($sellbillandrutern->sellbilldate);
$sqlQuery->set($sellbillandrutern->sellbillserial);
$sqlQuery->setNumber($sellbillandrutern->sellbillclientid);
$sqlQuery->set($sellbillandrutern->sellbilltotaldeptbefor);
$sqlQuery->set($sellbillandrutern->sellbilltotaldeptafter);
$sqlQuery->set($sellbillandrutern->sellbilldiscount);
$sqlQuery->setNumber($sellbillandrutern->sellbilldiscounttype);
$sqlQuery->set($sellbillandrutern->sellbilltotalbill);
$sqlQuery->set($sellbillandrutern->sellbillaftertotalbill);
$sqlQuery->set($sellbillandrutern->sellbilltotalpayed);
$sqlQuery->set($sellbillandrutern->sellbillfinalbill);
$sqlQuery->setNumber($sellbillandrutern->sellbillstoreid);
$sqlQuery->set($sellbillandrutern->sellbillsysdate);
$sqlQuery->set($sellbillandrutern->comment);
$sqlQuery->setNumber($sellbillandrutern->userid);
$sqlQuery->setNumber($sellbillandrutern->conditions);
$sqlQuery->set($sellbillandrutern->sellbillclientname);
$sqlQuery->setNumber($sellbillandrutern->sellbillsaveid);
$sqlQuery->setNumber($sellbillandrutern->sellbildirectpayment);
$sqlQuery->setNumber($sellbillandrutern->billnameid);
$sqlQuery->setNumber($sellbillandrutern->sellerid);
$sqlQuery->set($sellbillandrutern->sellbillprice);
$sqlQuery->set($sellbillandrutern->returnsellbillprice);
$sqlQuery->set($sellbillandrutern->tempclientName);
$sqlQuery->setNumber($sellbillandrutern->sellQuantity);
$sqlQuery->setNumber($sellbillandrutern->returnsellQuantity);
$sqlQuery->setNumber($sellbillandrutern->deletedsellid);
$sqlQuery->set($sellbillandrutern->controlname);
$sqlQuery->set($sellbillandrutern->tax);
$sqlQuery->set($sellbillandrutern->carnumber);
$sqlQuery->set($sellbillandrutern->cartype);
$sqlQuery->set($sellbillandrutern->carchase);
$sqlQuery->set($sellbillandrutern->carmotor);
$sqlQuery->setNumber($sellbillandrutern->costcenterid);
$sqlQuery->set($sellbillandrutern->dailyentryid);
$sqlQuery->setNumber((int) $sellbillandrutern->visaAccountId);
$sqlQuery->setNumber((float) $sellbillandrutern->cashPayed);
$sqlQuery->setNumber((float) $sellbillandrutern->visaPayed);
$sqlQuery->setNumber((int) $sellbillandrutern->isBankAccountTransfer);
$sqlQuery->setNumber((float) $sellbillandrutern->extraDiscountPer);
$sqlQuery->setNumber((float) $sellbillandrutern->extraDiscountVal);
$sqlQuery->setNumber((float) $sellbillandrutern->taxOfDiscountPer);
$sqlQuery->setNumber((float) $sellbillandrutern->taxOfDiscountVal);
$sqlQuery->setNumber((int) $sellbillandrutern->obgyBillType);
$sqlQuery->setNumber((int) $sellbillandrutern->billoperationid);
$sqlQuery->setNumber((int) $sellbillandrutern->pricetype);
$sqlQuery->setNumber((int) $sellbillandrutern->currencyId);
$sqlQuery->set($sellbillandrutern->sellbillno);
$id = $this->executeInsert($sqlQuery);
$sellbillandrutern->sellbillid = $id;
return $id;
}
public function loadSellbillandruternById($sellbillid) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, store.storeName, user.employeename , user2.employeename as useremployeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
LEFT JOIN user as user2
ON sellbillandrutern.userid = user2.userid
WHERE sellbillandrutern.sellbillid = ' . $sellbillid . '';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryWithOrder() {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithOrderLimited($start, $end) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySellBillsOnlyWithClientId($clientid) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId
LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillclientid =' . $clientid . '
AND sellandruternbilldetail.selltype = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryReturnSellBillsOnlyWithClientId($clientid) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId
LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillclientid =' . $clientid . '
AND sellandruternbilldetail.selltype = 1
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientId($clientid) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillclientid =' . $clientid . '
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithClientIdLimited($start, $end, $clientid) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillclientid =' . $clientid . '
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySellBillsOnlyWithSerail($serial) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillserial ="' . $serial . '"
AND sellandruternbilldetail.selltype = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryReturnSellBillsOnlyWithSerail($serial) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillserial ="' . $serial . '"
AND sellandruternbilldetail.selltype = 1
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSerail($serial) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillserial ="' . $serial . '"
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSerailLimited($start, $end, $serial) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillserial ="' . $serial . '"
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySellBillsOnlyWithSellbillid($sellbillid) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillid =' . $sellbillid . '
AND sellandruternbilldetail.selltype = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryReturnSellBillsOnlyWithSellbillid($sellbillid) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillid =' . $sellbillid . '
AND sellandruternbilldetail.selltype = 1
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithSellbillid($sellbillid) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbillid =' . $sellbillid . '
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySellBillsOnlyWithClientIdAndDate($clientId, $startDate, $endDate) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbilldate, sellbillserial, sellQuantity, sellbillaftertotalbill,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
AND sellandruternbilldetail.selltype = 0
AND sellbillandrutern.sellbillclientid = ' . $clientId . '
AND sellbillandrutern.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function querySellBillsOnlyWithDate($startDate, $endDate) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
AND sellandruternbilldetail.selltype = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
//start/////////////////////////////////
/////////////////////////////////
////////////hazem updaate//////////////////
/////////////////////////////
////////////////////////////--------
public function queryReturnSellBillsOnlyWithDate($startDate, $endDate) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
AND sellandruternbilldetail.selltype = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
//end/////////////////////////////////
/////////////////////////////////
////////////hazem updaate//////////////////
/////////////////////////////
////////////////////////////--------
public function queryReturnSellBillsOnlyWithClientIdAndDate($clientid, $startDate, $endDate) {
$sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename ,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
AND sellbillandrutern.sellbillclientid = ' . $clientid . '
AND sellandruternbilldetail.selltype = 1
AND sellbillandrutern.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryWithDate($startDate, $endDate) {
$sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryallWithqueryString($queryString) {
$sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename , user2.employeename as useremployeename,storeName,savename,editedBill.sellbillid as newSellbillidAfterEdit,delbyuser.username as delbyusername
FROM sellbillandrutern
LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
JOIN store ON sellbillandrutern.sellbillstoreid = store.storeId
JOIN save ON sellbillandrutern.sellbillsaveid = save.saveid
LEFT JOIN user ON sellbillandrutern.sellerid = user.userid
LEFT JOIN user as user2 ON sellbillandrutern.userid = user2.userid
LEFT JOIN user as delbyuser ON delbyuser.userid = sellbillandrutern.delbyuserid
LEFT JOIN sellbillandrutern as editedBill ON sellbillandrutern.sellbillid = editedBill.deletedsellid
' . $queryString . '
order by sellbillid desc';
// print_r($sql.'<br>');
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateLimited($start, $end, $startDate, $endDate) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
order by sellbillid desc
limit ' . $start . ',' . $end . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditions($startDate, $endDate) {
$sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
FROM sellbillandrutern LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where sellbillandrutern.sellbilldate
>= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
And sellbillandrutern.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryWithDateAndConditionsForSellBillOnly($startDate, $endDate) {
$sql = 'SELECT SUM(sellbillandrutern.sellbillaftertotalbill)
FROM sellbillandrutern
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
AND sellbilltotalbill >= 0
And sellbillandrutern.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->QuerySingleResult($sqlQuery);
}
public function queryWithDateAndConditionsForReturnSellBillOnly($startDate, $endDate) {
$sql = 'SELECT SUM(sellbillandrutern.sellbillaftertotalbill)
FROM sellbillandrutern
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
AND sellbilltotalbill < 0
And sellbillandrutern.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print($sql.'<br>');
return $this->QuerySingleResult($sqlQuery);
}
public function queryWithDateAndConditionsForReturnSellBillOnly2($startDate, $endDate) {
$sql = 'SELECT sum(sellbillandrutern.sellbillprice- (sellbilltotalbill-sellbillaftertotalbill))
FROM sellbillandrutern
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
And sellbillandrutern.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print('<br>'.$sql.'<br>');
return $this->QuerySingleResult($sqlQuery);
}
public function queryWithDateAndConditionsForReturnSellBillOnly22($startDate, $endDate) {
$sql = 'SELECT *
FROM sellbillandrutern
where sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
And sellbillandrutern.conditions = 0';
$sqlQuery = new SqlQuery($sql);
//print('<br>'.$sql.'<br>');
return $this->getList($sqlQuery);
}
public function updateConditions($sellbillandrutern, $query1 = '') {
$sql = 'UPDATE sellbillandrutern SET sellbillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? ' . $query1 . ' WHERE sellbillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($sellbillandrutern->sellbillsysdate);
$sqlQuery->setNumber($sellbillandrutern->userid);
$sqlQuery->setNumber($sellbillandrutern->delbyuserid);
$sqlQuery->setNumber($sellbillandrutern->conditions);
$sqlQuery->setNumber($sellbillandrutern->sellbillid);
return $this->executeUpdate($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT sellbillprice, sellbilldiscount, sellbilldiscounttype
FROM sellbillandrutern
WHERE sellbillid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function queryAllforrate($sellerid, $startDate, $endDate) {
$sql = 'SELECT * FROM `sellbillandrutern` WHERE `sellerid` =' . $sellerid . ' and
sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '" and conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateall($startDate, $endDate) {
$sql = 'SELECT * FROM `sellbillandrutern` WHERE
sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '" and conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function GetSellBillAndReturnUsingQueryString($queryString) {
$sql = 'SELECT sellbillandrutern.* , user.employeename , client.clientname,u.employeename as username FROM sellbillandrutern
LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
LEFT JOIN user u
ON u.userid = sellbillandrutern.userid
' . $queryString . '
AND sellbillandrutern.conditions=0
';
$sqlQuery = new SqlQuery($sql);
//print_r($sql.'<br>');
return $this->getList($sqlQuery);
}
public function checkIfThereIsABill() {
$sql = 'SELECT sellbillandrutern.*
FROM sellbillandrutern
WHERE sellbillandrutern.conditions=0 limit 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getSalesVal() {
$sql = 'SELECT sellandruternbilldetail.*, product.productBuyPrice
FROM sellbillandrutern LEFT JOIN sellandruternbilldetail
ON sellbillandrutern.sellbillid = sellandruternbilldetail.sellbillid JOIN product
ON product.productId = sellandruternbilldetail.sellbilldetailproductid
where sellbillandrutern.conditions = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT sellbillandrutern.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
FROM sellbillandrutern
join client c on sellbillandrutern.sellbillclientid = c.clientid
left join user u on sellbillandrutern.userid = u.userid
left join user s on sellbillandrutern.sellerid = s.userid
' . $queryString . ' AND sellbillandrutern.conditions = 0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithSellAndReturnSellbillsSells($queryString) {
$sql = 'SELECT sellbillandrutern.sellbillclientid , SUM(sellbillandrutern.sellbilltotalbill) as allGenTotalSellAndReturn , SUM(sellbillandrutern.sellbillaftertotalbill) as sellbillaftertotalbill ,
COUNT(sellbillandrutern.sellbillclientid ) , client.clientname as clientname FROM `sellbillandrutern`
join client on client.clientid = sellbillandrutern.sellbillclientid
' . $queryString . ' GROUP BY sellbillandrutern.sellbillclientid
';
// echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithSellAndReturnSellbillsSells2($clientid, $sellBillsQuery) {
$sql = 'SELECT sellbillandrutern.sellbillclientid , SUM(sellbillandrutern.sellbilltotalbill) as allGenTotalSellAndReturn , SUM(sellbillandrutern.sellbilldiscount) as totaldiscount ,
COUNT(sellbillandrutern.sellbillclientid ) , client.clientname as clientname ,
SUM(sellbillandrutern.sellbilltotalpayed) as sellAndReturnTotalPayed
FROM `sellbillandrutern`
join client on client.clientid = sellbillandrutern.sellbillclientid
where sellbillandrutern.sellbillclientid = ' . $clientid . '
' . $sellBillsQuery . ' GROUP BY sellbillandrutern.sellbillclientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function sellbillandreturnsellbillWithProducts($queryString) {
$sql = 'SELECT sum(sellbillandrutern.sellQuantity) as sumQuantity , sum(sellbillandrutern.sellbillprice) as sumTotalbill ,
sum(sellbillandrutern.returnsellQuantity) as sumQuantityReturend , sum(sellandruternbilldetail.sellbilldetailtotalprice) as sumTotalbillReturend ,
sum(sellbillandrutern.sellbilldiscount) as sumDiscount ,
sellbillandrutern.sellbillclientid as sellbillclientid ,product.productid as productid , product.productName as productName
FROM `sellbillandrutern`
join sellandruternbilldetail on sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
join client on client.clientid = sellbillandrutern.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 = sellandruternbilldetail.sellbilldetailproductid
WHERE ' . $queryString . ' GROUP BY sellandruternbilldetail.sellbilldetailproductid order by client.clientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientPayed($queryString1, $queryString2) {
$sql = 'SELECT sellbillandrutern.sellbillclientid,sellbillandrutern.sellbilltotalpayed,sellbillandrutern.sellbillaftertotalbill
FROM `sellbillandrutern`
' . $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 = "sellbillandrutern" ';
$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 sellbillandrutern.* , c.clientname as clientname,u.employeename as employeename ' . $sellerSql . '
FROM sellbillandrutern
join client c on sellbillandrutern.sellbillclientid = c.clientid
join user u on sellbillandrutern.userid = u.userid
' . $sellerJoin . '
' . $queryString . ' AND sellbillandrutern.conditions = 0';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllGenerales($sellbillandrutern) {
$sql = 'SELECT * FROM sellbillandrutern' . $sellbillandrutern;
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function querySellBillsOnlyWithClientIdAndDate2($clientId, $date) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbilldate, sellbillserial, sellQuantity, sellbillaftertotalbill,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
where ' . $date . '
sellandruternbilldetail.selltype = 0
AND sellbillandrutern.sellbillclientid = ' . $clientId . '
AND sellbillandrutern.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
//emaaaaaaaaaaan
public function querySellBillsOnlyWithClientIdAndDateExt($clientId, $date) {
$sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`), sellbilldate, sellbillserial, sellQuantity, sellbillaftertotalbill,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid
where ' . $date . '
sellandruternbilldetail.selltype = 0
AND sellbillandrutern.sellbillclientid = ' . $clientId . '
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryReturnSellBillsOnlyWithClientIdAndDate2($clientid, $date) {
$sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename ,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where ' . $date . '
sellbillandrutern.sellbillclientid = ' . $clientid . '
AND sellandruternbilldetail.selltype = 1
AND sellbillandrutern.conditions = 0
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
////////////eman//////////////
public function queryReturnSellBillsOnlyWithClientIdAndDateExt($clientid, $date) {
$sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename ,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
FROM sellbillandrutern
JOIN sellandruternbilldetail
ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
LEFT JOIN client
ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
ON sellbillandrutern.sellerid = user.userid
where ' . $date . '
sellbillandrutern.sellbillclientid = ' . $clientid . '
AND sellandruternbilldetail.selltype = 1
order by sellbillid desc';
$sqlQuery = new SqlQuery($sql);
//print($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateWithSellerName($sellerid, $startDate, $endDate, $priceType) {
$sql = 'SELECT DISTINCT sellbillandrutern.*, user.username, client.clientname, sellandruternbilldetail.pricetype FROM `sellbillandrutern`
LEFT JOIN user ON sellbillandrutern.`sellerid` = user.userid
LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellbillandrutern.`sellerid` =' . $sellerid . ' and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '" and sellandruternbilldetail.pricetype in( ' . $priceType . ') and sellbillandrutern.conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforrateallWithSellerName($startDate, $endDate, $priceType) {
$sql = 'SELECT DISTINCT sellbillandrutern.*, user.username, client.clientname, sellandruternbilldetail.pricetype FROM `sellbillandrutern`
LEFT JOIN user ON sellbillandrutern.`sellerid` = user.userid
LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '" and sellandruternbilldetail.pricetype in( ' . $priceType . ') and sellbillandrutern.conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
//emaaaaaaaaaaan
public function queryAllforrateallWithoutSeller() {
$sql = 'SELECT DISTINCT sellbillandrutern.*, user.username, client.clientname, sellandruternbilldetail.pricetype ,user.awardrate
FROM `sellbillandrutern`
LEFT JOIN user ON sellbillandrutern.`sellerid` = user.userid
LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
WHERE MONTH(sellbillandrutern.sellbilldate) = Month(CURRENT_DATE()) and sellbillandrutern.conditions =0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getBillsCountNotDel($queryString = '') {
$sql = 'SELECT count(sellbillid) as sellbillid
FROM sellbillandrutern where conditions = 0 ' . $queryString;
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryAllOrderedLimitedSimple($start, $end) {
$sql = 'SELECT *
FROM sellbillandrutern
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 sellbillandrutern
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 uerrazmy($clientID, $queryStringsellbillandrutern) {
$sql = 'SELECT *
FROM sellbillandrutern where sellbillclientid =' . $clientID . '' . $queryStringsellbillandrutern;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function ClientsWithSellAndReturnSellbillsSells($queryStringTy, $queryString1SR) {
$sql = 'SELECT client.*,clientarea.name as areaName,government.governmentname as governmentname , sellbillandrutern.sellbillclientid , SUM(sellbillandrutern.sellbilltotalbill) as allGenTotalSellAndReturn , SUM(sellbillandrutern.sellbillaftertotalbill) as sellbillaftertotalbill ,
COUNT(sellbillandrutern.sellbillclientid ) , 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 sellbillandrutern on client.clientid = sellbillandrutern.sellbillclientid
' . $queryStringTy . ' ' . $queryString1SR . ' ';
// echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientSellStatistics($clientId, $queryString = '') {
$sql = 'SELECT sellbilldetailproductid,sizeid,colorid,count(sellandruternbilldetail.sellbillid) as sellbillid
FROM sellbillandrutern
JOIN sellandruternbilldetail ON (sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid and sellbillandrutern.conditions = 0 AND sellandruternbilldetail.selltype = 0 and sellbillandrutern.sellbillclientid =' . $clientId . ')
where 1
' . $queryString . '
group by sellbilldetailproductid,sizeid,colorid';
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
}
?>