File: /home/mostafedeg/public_html/erp/models/mysql/ext/BillsMySqlExtDAO.class.php
<?php
/**
* Class that operate on table 'bills'. Database Mysql.
*
* @author: http://phpdao.com
* @date: 2016-06-21 11:00
*/
class BillsMySqlExtDAO extends BillsMySqlDAO {
public function searchInBillNo($billNo) {
$sql = "select * from bills where billno like '$billNo%' order by billno desc limit 1 ";
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function getAllBillNoDistinct($queryString = "") {
$sql = "select distinct billno from bills where 1 " . $queryString . " order by billno asc";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getAllBillNoDistinctSearch($billNo) {
$sql = "select distinct billno from bills where billno like '$billNo%' order by billno asc";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByBillNoAndNotId($billNo, $billId) {
$sql = "select * from bills where billno = ? and id != ?";
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($billNo);
$sqlQuery->setNumber($billId);
return $this->getList($sqlQuery);
}
public function searchBills($q1, $q2, $q3, $q4) {
$sql = 'select * from bills where deleted in (0 , 1) ' . $q1 . ' ' . $q2 . ' ' . $q3 . ' ' . $q4 . ' ';
/*print_r('<pre>');
print_r($sql);
print_r('</pre>');*/
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateBillStatus($billstatus, $id) {
$sql = 'UPDATE bills SET billstatus = ' . $billstatus . ' WHERE id = ' . $id . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function updateNotifSeen($notifSeen, $id) {
$sql = 'UPDATE bills SET notifSeen = ' . $notifSeen . ' WHERE id = ' . $id . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function getNewBills() {
$sql = "select * from bills where billstatus = 0 and deleted = 0";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryByBillStatus($billstatus) {
$sql = "select * from bills where billstatus = " . $billstatus . " and deleted = 0";
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getBillsByClientCompanyIdAndInsuranceCompanyId($clientAreaId, $insuranceCompanyId, $branchQuery, $dateFromQuery, $clientIdQuery, $joinQuery, $joinQuery1) {
$sql = 'SELECT b.* , c.clientname as clientname ' . $joinQuery1 . '
FROM bills b
join client c on b.clientid = c.clientid
' . $joinQuery . '
WHERE b.insurance = 1 ' . $branchQuery . '
and b.insurancecompanyid =' . $insuranceCompanyId . ' ' . $dateFromQuery . ' ' . $clientIdQuery . '
and c.clientareaid =' . $clientAreaId . '';
///print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getDistinctCompaniesWithBillsByInsuranceCompanyId($insuranceCompanyId, $branchQuery, $dateFromQuery, $clientAreaQuery) {
$sql = 'SELECT distinct c.clientareaid
FROM client c
join bills b on b.clientid = c.clientid
join clientarea a on a.id = c.clientareaid
WHERE b.insurance = 1 ' . $branchQuery . ' ' . $dateFromQuery . ' ' . $clientAreaQuery . '
and b.insurancecompanyid =' . $insuranceCompanyId . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getBillsByInsuranceCompanyId($insuranceCompanyId, $branchQuery, $dateFromQuery, $clientAreaQuery) {
$sql = 'SELECT b.* , a.name as clientname
FROM bills b
join client c on b.clientid = c.clientid
join clientarea a on a.id = c.clientareaid
WHERE b.insurance = 1 ' . $branchQuery . ' ' . $dateFromQuery . ' ' . $clientAreaQuery . '
and b.insurancecompanyid =' . $insuranceCompanyId . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getDistinctInsCompaniesWithBills($insCompanyQuery, $branchQuery, $dateFromQuery) {
$sql = 'SELECT distinct b.insurancecompanyid
FROM bills b
WHERE b.insurance = 1 ' . $insCompanyQuery . ' ' . $branchQuery . ' ' . $dateFromQuery . ' ';
//print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforreceipt($queryString) {
$sql = 'SELECT id,billno,billdate,waitvalue,companycarry,clientid,clientPayReceiptVal FROM bills
' . $queryString . '
AND bills.deleted=0
order by bills.billdate asc
';
//print_r("<br/>".$sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function updateClientPayReceiptVal($clientPayReceiptVal, $id) {
$sql = 'UPDATE bills SET clientPayReceiptVal = ' . $clientPayReceiptVal . ' WHERE id = ' . $id . '';
$sqlQuery = new SqlQuery($sql);
return $this->executeUpdate($sqlQuery);
}
public function queryByBilldateAndNetwork($date, $networkId) {
$sql = 'SELECT * FROM bills WHERE card = 1 and billdate = ? and paymentnetworkid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($date);
$sqlQuery->setNumber($networkId);
return $this->getList($sqlQuery);
}
public function queryNetworkReportToday() {
$sql = 'SELECT bills.*, clientname, paymentnetworks.name as networkname
FROM bills, client, paymentnetworks WHERE client.clientid = bills.clientid and paymentnetworks.id = bills.paymentnetworkid and card = 1 and Date(billdate) = "' . date('Y-m-d') . '"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryNetworkReport($queryString) {
$sql = 'SELECT bills.*, clientname, paymentnetworks.name as networkname
FROM bills, client, paymentnetworks WHERE client.clientid = bills.clientid and paymentnetworks.id = bills.paymentnetworkid and card = 1 ' . $queryString . '';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryTotalNetworkReportButMada($queryString) {
$sql = 'SELECT sum(bills.cardvalue) as totalNet, sum((bills.cardvalue * bills.netdiscountpercent)/100) as totalCarry, billdate, clientname, paymentnetworks.name as networkname
FROM bills, client, paymentnetworks WHERE client.clientid = bills.clientid and paymentnetworks.id = bills.paymentnetworkid and card = 1 ' . $queryString . ' and paymentnetworkid != 4 group by paymentnetworkid , Date(billdate)';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryTotalNetworkReportMada($queryString) {
$sql = 'SELECT sum(bills.cardvalue) as totalCarry, clientname, paymentnetworks.name as networkname
FROM bills, client, paymentnetworks WHERE client.clientid = bills.clientid and paymentnetworks.id = bills.paymentnetworkid and card = 1 ' . $queryString . ' and paymentnetworkid = 4 group by paymentnetworkid , Date(billdate)';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function queryTotalNetworkReportMadaSimple($billdate) {
$sql = 'SELECT sum(bills.cardvalue) as totalCarry
FROM bills WHERE card = 1 and paymentnetworkid = 4 and Date(billdate) = "' . $billdate . '" ';
$sqlQuery = new SqlQuery($sql);
//echo $sql;
return $this->getRow($sqlQuery);
}
public function queryAllGeneral($queryString) {
$sql = 'SELECT bills.* , c.clientname as clientname,u.employeename as username
FROM bills
join client c on bills.clientid = c.clientid
join user u on bills.userid = u.userid
' . $queryString . ' AND bills.deleted=0 order by bills.id';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function selectListClientId($id) {
$sql = 'SELECT * from bills WHERE clientid = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function selectListFromBillsReturn($id) {
$sql = 'SELECT sum( billsreturn.returnedprice )as returnprice,
sum( billsreturn.clientreceivevalue )as returnvalue
from bills
join billsreturn
on bills.id = billsreturn.billid
WHERE clientid = ?
group by bills.id
';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
// print_r($sql);
return $this->getList($sqlQuery);
}
public function selectListClientIdext($id, $querystring) {
$sql = 'SELECT * from bills WHERE clientid = ? ' . $querystring . '';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getList($sqlQuery);
}
public function selectListFromBillsReturnext($id, $querystring) {
$sql = 'SELECT sum( billsreturn.returnedprice )as returnprice,
sum( billsreturn.clientreceivevalue )as returnvalue
from bills
join billsreturn
on bills.id = billsreturn.billid
WHERE clientid = ?
' . $querystring . '
group by bills.id
';
// print_r($sql);
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
// print_r($sql);
return $this->getList($sqlQuery);
}
public function queryByRondomText($value) {
$sql = 'SELECT * FROM bills WHERE rondomtxt = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->set($value);
return $this->getList($sqlQuery);
}
public function sumOfMoneyPaidByInsCompany($qDate) {
$sql = 'select sum( companycarry )as networkname from bills
where deleted = 0
' . $qDate . '
and insurance = 1 ';
// print_r($sql);
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function sumOfMoneyPaidByNetworks($qDate) {
$sql = 'select sum( cardvalue )as networkname from bills
where deleted = 0
' . $qDate . '
and card = 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function sumOfMoneyPaidByCash($qDate) {
$sql = 'select sum( cashvalue )as networkname from bills
where deleted = 0
' . $qDate . '
and cash = 1';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function sumOfMoneyPaidByWait($qDate) {
$sql = 'select sum( waitvalue )as networkname from bills
where deleted = 0
' . $qDate . ' ';
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
public function loadExt($id) {
$sql = 'SELECT bills.*, client.clientname
FROM bills LEFT JOIN client
ON bills.clientid = client.clientid
WHERE bills.id = ?';
$sqlQuery = new SqlQuery($sql);
$sqlQuery->setNumber($id);
return $this->getRow($sqlQuery);
}
public function queryWithDateAndConditionsExt($startDate, $endDate) {
$sql = 'SELECT SUM(bills.netbillvalue)
FROM bills
where date(bills.billdate) >= "' . $startDate . '" and date(bills.billdate) <= "' . $endDate . '"
and bills.deleted = 0';
$sqlQuery = new SqlQuery($sql);
return $this->querySingleResult($sqlQuery);
}
public function queryAllforrateall($startDate, $endDate) {
$sql = 'SELECT bills.*
FROM bills
where date(bills.billdate) >= "' . $startDate . '" and date(bills.billdate) <= "' . $endDate . '"
and bills.deleted = 0';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithBillsSells($queryString) {
$sql = 'SELECT bills.clientid , SUM(bills.productstotalprice) as totalNet , SUM(bills.discountvalue) as totaldiscount ,
COUNT(bills.clientid ) , client.clientname as clientname FROM `bills`
join client on client.clientid = bills.clientid
' . $queryString . ' GROUP BY bills.clientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function getClientPayed($queryString1, $queryString2) {
$sql = 'SELECT bills.clientid,bills.cashvalue,bills.cardvalue
FROM `bills`
' . $queryString1 . ' ' . $queryString2 . '';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function orderClientsWithBillsSells2($clientid, $BillsQuery) {
$sql = 'SELECT bills.clientid , SUM(bills.productstotalprice) as totalNet , SUM(bills.discountvalue) as totaldiscount ,
COUNT(bills.clientid ) , client.clientname as clientname FROM `bills`
join client on client.clientid = bills.clientid
where bills.clientid = ' . $clientid . '
' . $BillsQuery . ' GROUP BY bills.clientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function billWithProducts($queryStringBill) {
$sql = 'SELECT sum(billsproducts.productno) as sumBillQuantity , sum(billsproducts.producttotalprice) as sumBilltotalbill ,
sum(bills.discountvalue) as sumBillDiscount,
bills.clientid as clientid ,product.productid as productid , product.productName as productName
FROM `billsproducts`
join bills on bills.id = billsproducts.billid
join client on client.clientid = bills.clientid
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 = billsproducts.productid
WHERE ' . $queryStringBill . ' GROUP BY billsproducts.productid order by client.clientid
';
//echo $sql . "<br/>";
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllNotDelWithQuerySrting($queryString) {
$sql = "select * from bills where 1 " . $queryString . " and deleted = 0";
//echo $sql;
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function queryAllforcondemnedbills( $where ) {
$sql = 'SELECT id , billno , finalnetbillvalue ,waitvalue , insurance , companycarry , cashvalue , card , cardvalue FROM bills
where '.$where.'
AND deleted=0 and waitvalue > 0
order by billno asc
';
//print_r("<br/>".$sql);
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function deleteByDeletedAndBillNo($billNo) {
$sql = 'select * FROM bills WHERE deleted = 0 and billno like "%'.$billNo.'%"';
$sqlQuery = new SqlQuery($sql);
return $this->getList($sqlQuery);
}
public function selectBillNo($billNo) {
$sql = "select * from bills where billno = ".$billNo." and waitvalue > 0 ";
$sqlQuery = new SqlQuery($sql);
return $this->getRow($sqlQuery);
}
}
?>