<?php

/**
 * Class that operate on table 'billsreturn'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2016-08-06 13:48
 */
class BillsreturnMySqlExtDAO extends BillsreturnMySqlDAO {

    public function searchBills($q1, $q2, $q3, $q4) {
        $sql = 'select r.*  , b.clientid , b.billno
                from billsreturn  r
                join   bills   b        on b.id = r.billid
                where r.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 returnedpriceSumExcept($billsReturnId, $billId) {
        $sql = 'SELECT sum(returnedprice) as returnedprice  FROM billsreturn  where id != ' . $billsReturnId . ' and billid = ' . $billId . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function returnedpriceSum($billId) {
        $sql = 'SELECT sum(returnedprice) as returnedprice  FROM billsreturn  where  billid = ' . $billId . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllGeneral($queryString) {
        $sql = 'SELECT billsreturn.* , c.clientname as clientname,u.employeename as username
                        FROM billsreturn
                        join bills     on bills.id = billsreturn.billid
                        join client c    on bills.clientid = c.clientid
                        join user u      on billsreturn.userid = u.userid
                        ' . $queryString . ' AND billsreturn.deleted=0 order by billsreturn.id';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsExt($startDate, $endDate) {
        $sql = 'SELECT SUM(billsreturn.returnedprice)
		FROM billsreturn
			 where  billsreturn.`date` >= "' . $startDate . '" and  billsreturn.`date` <= "' . $endDate . '"
			 And  billsreturn.deleted = 0';

        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryAllforrateall($startDate, $endDate) {
        $sql = 'SELECT *
		FROM billsreturn
			 where  billsreturn.`date` >= "' . $startDate . '" and  billsreturn.`date` <= "' . $endDate . '"
			 And  billsreturn.deleted = 0';

        //print_r($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithBillsReturnSells($queryString) {
        $sql = 'SELECT bills.clientid , SUM(billsreturn.returnedprice) as clientreceivevalue , COUNT(bills.clientid ) , client.clientname as clientname FROM `billsreturn`
                join bills on bills.id = billsreturn.billid
                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 orderClientsWithBillsReturnSells2($clientid, $billsReturnQuery) {
        $sql = 'SELECT bills.clientid , SUM(billsreturn.returnedprice) as clientreceivevalue , COUNT(bills.clientid ) , client.clientname as clientname FROM `billsreturn`
                join bills on bills.id = billsreturn.billid
                join client on client.clientid = bills.clientid
                where bills.clientid = ' . $clientid . '
                ' . $billsReturnQuery . ' GROUP BY bills.clientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function billReturnWithProducts($queryStringBillReturn) {
        $sql = 'SELECT sum(billsreturn.returnedprice) as sumBillReturnQuantity , billsproducts.productprice as productprice , bills.clientid as clientid ,product.productid as productid , product.productName as productName
                FROM `billsreturn`
                join billsproducts on billsproducts.billid = billsreturn.billid
                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  ' . $queryStringBillReturn . '  GROUP BY billsproducts.productid order by  client.clientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientPayed($queryString1, $queryString2) {
        $sql = 'SELECT bills.clientid,billsreturn.clientreceivevalue
                FROM `billsreturn`
                join bills on bills.id = billsreturn.billid
                ' . $queryString1 . ' ' . $queryString2 . '';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

}

?>