<?php

/**
 * Class that operate on table 'billsbuy'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2017-02-20 09:04
 */
class BillsbuyMySqlExtDAO extends BillsbuyMySqlDAO {

    public function searchInBillNo($billNo) {
        $sql = "select * from billsbuy 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 billsbuy where 1 " . $queryString . " order by billno asc";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getAllBillNoDistinctSearch($billNo) {
        $sql = "select distinct billno from billsbuy where billno like  '$billNo%' order by billno asc";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByBillNoAndNotId($billNo, $billId) {
        $sql = "select *  from billsbuy 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 billsbuy 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 billsbuy SET billstatus = ' . $billstatus . ' WHERE id = ' . $id . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateNotifSeen($notifSeen, $id) {
        $sql = 'UPDATE billsbuy SET notifSeen = ' . $notifSeen . ' WHERE id = ' . $id . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getNewBills() {
        $sql = "select *  from billsbuy where billstatus = 0 and deleted = 0";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByBillStatus($billstatus) {
        $sql = "select *  from billsbuy where billstatus = " . $billstatus . " and deleted = 0";
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getBillsByClientCompanyIdAndInsuranceCompanyId($insuranceCompanyId, $branchQuery, $dateFromQuery, $clientIdQuery, $joinQuery, $joinQuery1) {
        $sql = 'SELECT b.* , s.suppliername as clientname  ' . $joinQuery1 . '
                        FROM billsbuy b
                        join supplier s        on b.clientid = s.supplierid
                        ' . $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) {
//        $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 . '
//                        and b.insurancecompanyid =' . $insuranceCompanyId . '   ';
//        //print_r($sql);
//        $sqlQuery = new SqlQuery($sql);
//        return $this->getList($sqlQuery);
//    }

    public function getBillsByInsuranceCompanyId($insuranceCompanyId, $branchQuery, $dateFromQuery) {
        $sql = 'SELECT b.* , a.name as clientname
                        FROM billsbuy b
                        WHERE b.insurance = 1 ' . $branchQuery . '  ' . $dateFromQuery . '
                        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 billsbuy 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 billsbuy
		' . $queryString . '
		AND billsbuy.deleted=0
                order by billsbuy.billdate asc
		';

        //print_r("<br/>".$sql);
        $sqlQuery = new SqlQuery($sql);

        return $this->getList($sqlQuery);
    }

    public function updateClientPayReceiptVal($clientPayReceiptVal, $id) {
        $sql = 'UPDATE billsbuy SET clientPayReceiptVal = ' . $clientPayReceiptVal . ' WHERE id = ' . $id . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByBilldateAndNetwork($date, $networkId) {
        $sql = 'SELECT * FROM billsbuy 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 billsbuy.*, supplier.suppliername as clientname, paymentnetworks.name as networkname
                FROM billsbuy, supplier, paymentnetworks WHERE supplier.supplierid = billsbuy.clientid and paymentnetworks.id = billsbuy.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 billsbuy.*, supplier.suppliername as clientname, paymentnetworks.name as networkname
        FROM billsbuy, supplier, paymentnetworks WHERE supplier.supplierid = billsbuy.clientid and paymentnetworks.id = billsbuy.paymentnetworkid and card = 1 ' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryTotalNetworkReportButMada($queryString) {
        $sql = 'SELECT sum(billsbuy.cardvalue) as totalNet, sum((billsbuy.cardvalue * billsbuy.netdiscountpercent)/100) as totalCarry, billdate, supplier.suppliername as clientname, paymentnetworks.name as networkname
        FROM billsbuy, supplier, paymentnetworks WHERE supplier.supplierid = billsbuy.clientid and paymentnetworks.id = billsbuy.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(billsbuy.cardvalue) as totalCarry, supplier.suppliername as clientname, paymentnetworks.name as networkname
        FROM billsbuy, supplier, paymentnetworks WHERE supplier.supplierid = billsbuy.clientid and paymentnetworks.id = billsbuy.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(billsbuy.cardvalue) as totalCarry
        FROM billsbuy 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 billsbuy.* ,s.suppliername as clientname,u.employeename as username
                        FROM billsbuy
                        join supplier s    on billsbuy.clientid = s.supplierid
                        join user u      on billsbuy.userid = u.userid
                        ' . $queryString . ' AND billsbuy.deleted=0 order by billsbuy.id';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function selectListClientId($id) {
        $sql = 'SELECT *  from billsbuy 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 billsbuy 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 billsbuy WHERE rondomtxt = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function sumOfMoneyPaidByInsCompany($qDate) {
        $sql = 'select sum( companycarry )as networkname  from billsbuy
                    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 billsbuy
                    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 billsbuy
                    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 billsbuy
                    where   deleted = 0
                    ' . $qDate . '    ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadExt($id) {
        $sql = 'SELECT billsbuy.*,supplier.suppliername as clientname
		 FROM billsbuy LEFT JOIN supplier
		 ON billsbuy.clientid = supplier.supplierid
		 WHERE billsbuy.id  = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function queryWithDateAndConditionsExt($startDate, $endDate) {
        $sql = 'SELECT SUM(billsbuy.netbillvalue)
			 FROM billsbuy
			 where date(billsbuy.billdate) >= "' . $startDate . '" and date(billsbuy.billdate) <= "' . $endDate . '"
			 and billsbuy.deleted = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllforrateall($startDate, $endDate) {
        $sql = 'SELECT billsbuy.*
			 FROM billsbuy
			 where date(billsbuy.billdate) >= "' . $startDate . '" and date(billsbuy.billdate) <= "' . $endDate . '"
			 and billsbuy.deleted = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithBillsSells($queryString) {
        $sql = 'SELECT billsbuy.clientid , SUM(billsbuy.productstotalprice) as totalNet , SUM(billsbuy.discountvalue) as totaldiscount ,
                COUNT(billsbuy.clientid ) , supplier.suppliername as clientname FROM `billsbuy`
                join supplier on supplier.supplierid = billsbuy.clientid


                ' . $queryString . ' GROUP BY billsbuy.clientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientPayed($queryString1, $queryString2) {
        $sql = 'SELECT billsbuy.clientid,billsbuy.cashvalue,billsbuy.cardvalue
                FROM `billsbuy`
                ' . $queryString1 . ' ' . $queryString2 . '';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithBillsSells2($clientid, $BillsQuery) {
        $sql = 'SELECT billsbuy.clientid , SUM(billsbuy.productstotalprice) as totalNet , SUM(billsbuy.discountvalue) as totaldiscount ,
                COUNT(billsbuy.clientid ) , supplier.suppliername as clientname FROM `billsbuy`
                join supplier on supplier.supplierid = billsbuy.clientid
                where billsbuy.clientid = ' . $clientid . '

                ' . $BillsQuery . ' GROUP BY billsbuy.clientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function billWithProducts($queryStringBill) {
        $sql = 'SELECT sum(billsproductsbuy.productno) as sumBillQuantity , sum(billsproductsbuy.producttotalprice) as sumBilltotalbill ,
                sum(billsbuy.discountvalue) as sumBillDiscount,
                billsbuy.clientid as clientid ,product.productid as productid , product.productName as productName
                FROM `billsproductsbuy`
                join billsbuy on billsbuy.id = billsproductsbuy.billid
                join product on product.productId = billsproductsbuy.productid
		WHERE ' . $queryStringBill . ' GROUP BY billsproductsbuy.productid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllNotDelWithQuerySrting($queryString) {
        $sql = "select * from billsbuy where 1 " . $queryString . " and deleted = 0";
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>