<?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);
    }
    
    
}

?>
