<?php

/**
 * Class that operate on table 'tamweenbill'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2020-12-14 16:00
 */
class TamweenbillMySqlExtDAO extends TamweenbillMySqlDAO {

    public function getClientTakenAmountFromTo($clientid, $from, $to) {
        $sql = 'SELECT sum(quantity)
                FROM tamweenbill WHERE clientid = ' . $clientid . ' and date(billdate) >= "' . $from . '" and date(billdate) <= "' . $to . '"
                and del = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllEX($queryString = '') {
        $sql = 'SELECT tamweenbill.*,client.clientname
                FROM tamweenbill
                join client on client.clientid = tamweenbill.clientid where 1 ' . $queryString;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadEX($id) {
        $sql = 'SELECT tamweenbill.*,client.clientname,client.clientcode
                FROM tamweenbill
                join client on client.clientid = tamweenbill.clientid
                WHERE id = ' . $id;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function getTakenDa3mReport($queryStringTamweenBillAllMonth, $queryStringTamweenBill = '') {
        $sql = 'SELECT sum(quantity) as quantity,client.clientname,noOfPersonsDa3m,cardNum,takenInAMonthQuantity,client.addDate,client.clientcode
                FROM (
                select * from tamweenbill
                where tamweenbill.del = 0
                ' . $queryStringTamweenBill . ') as bill
                right join client on client.clientid = bill.clientid
                left join ( select sum(quantity) as takenInAMonthQuantity,clientid
                from tamweenbill where clientid !=1 ' . $queryStringTamweenBillAllMonth . '
                    group by clientid ) as monthBill on monthBill.clientid = client.clientid
                left join tamweenclientdetail on client.clientid = tamweenclientdetail.clientid
                where client.clientid !=1
                group by client.clientid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getTakenDa3mReport3($queryStringTamweenBill = '', $joinRemainStatusQueryString = '') {//remain report
        $sql = 'SELECT sum(quantity) as quantity,tamweenremainstatus.id as tamweenremainstatusid,remainstatus,tamweenremainstatus.sysdate as sysdate,user.employeename,client.clientname,noOfPersonsDa3m,client.clientid as clientid,client.addDate,client.clientcode
                FROM (
                select * from tamweenbill
                where tamweenbill.del = 0
                ' . $queryStringTamweenBill . ') as bill

                right join client on client.clientid = bill.clientid

                left join tamweenclientdetail on client.clientid = tamweenclientdetail.clientid
                left join tamweenremainstatus on (client.clientid = tamweenremainstatus.clientid ' . $joinRemainStatusQueryString . ')
                left join user on user.userid = tamweenremainstatus.userid

                where client.clientid !=1
                group by client.clientid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getTakenDa3mReport2($queryStringTamweenBill = '') {
        $sql = 'SELECT sum(quantity) as quantity,client.clientname,noOfPersonsDa3m,date(bill.billdate) as billdate
                FROM (
                select * from tamweenbill
                where tamweenbill.del = 0
                ' . $queryStringTamweenBill . ') as bill
                join client on client.clientid = bill.clientid
                left join tamweenclientdetail on client.clientid = tamweenclientdetail.clientid
                group by date(bill.billdate)';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getTakenDa3mReport22($queryStringTamweenBill = '') {
        $sql = 'SELECT quantity,billdate,client.clientname,noOfPersonsDa3m,client.clientid as clientid,client.addDate,client.clientcode
                FROM
                tamweenbill
                join client on client.clientid = tamweenbill.clientid
                left join tamweenclientdetail on client.clientid = tamweenclientdetail.clientid
                where tamweenbill.del = 0
                ' . $queryStringTamweenBill . '
                order by billdate desc';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>