<?php
/**
 * Class that operate on table 'premium'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2020-04-04 10:39
 */
class PremiumMySqlExtDAO extends PremiumMySqlDAO{

    public function getpremium($queryString) {

        $sql = 'SELECT premiumclient.*,premium.*,premium.conditions as premConditions,client.*,clientarea.name as areaName,typeclient.typeName FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join client on premiumclient.client_id = client.clientid 
                left join clientarea on clientarea.id = client.clientareaid 
                left join typeclient on typeclient.typeId = client.typeclientid
                  ' . $queryString . ' and premium.payed = 0';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getList($sqlQuery);
    }

    public function getpremiumdatabyid($id) {

        $sql = 'SELECT premium.*,premiumclient.client_id as clientid ,client.clientdebt FROM `premium` 
                join premiumclient on premiumclient.id = premium.premiumclient_id
                  join client on premiumclient.client_id = client.clientid WHERE premium.id = '.$id;
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getRow($sqlQuery);
    }


    public function getpremiumbyclientid($queryString) {

        $sql = 'SELECT premiumclient.*,premium.*,premium.conditions as premConditions,client.*,clientarea.name as areaName,typeclient.typeName FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join client on premiumclient.client_id = client.clientid 
                left join clientarea on clientarea.id = client.clientareaid 
                left join typeclient on typeclient.typeId = client.typeclientid
                  ' . $queryString . ' and premium.conditions = 1';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getList($sqlQuery);
    }


    public function getAllpremium($queryString) {

        $sql = 'SELECT premiumclient.*,premium.*,premium.conditions as premConditions,client.*,clientarea.name as areaName,typeclient.typeName FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join client on premiumclient.client_id = client.clientid 
                left join clientarea on clientarea.id = client.clientareaid 
                left join typeclient on typeclient.typeId = client.typeclientid
                  ' . $queryString . ' ';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getList($sqlQuery);
    }

    public function selectallbyid($premiumclientId) {

        $sql = 'SELECT count(premium.id) as countPremium from premium where premiumclient_id = '.$premiumclientId;
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getRow($sqlQuery);
    }

    public function queryByPremiumclientIdOrderBy($value){
        $sql = 'SELECT * FROM premium WHERE premiumclient_id = ? order by date ASC ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByPremiumclientConditionPayed($value){
        $sql = 'SELECT * FROM premium WHERE premiumclient_id = ? and payed = 0 order by date ASC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function getpremiumNew($queryString) {
            $date = date('Y-m-d');
        $sql = 'SELECT premiumclient.*,client.clientname,sum(premium.value) as totalPremium ,sum(premium.rest) as restPremium , premiumForCOuntTable.totalnotpayed, premiumForCOuntTable.totalValueNotPayed,premiumForCOuntTable.totalRestNotPayed, premiumForToday.totalLate, premiumForToday.totalValueLate  FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join client on premiumclient.client_id = client.clientid 
                left join( select premium.premiumclient_id ,count(premium.id) as totalnotpayed,sum(premium.value) as totalValueNotPayed,sum(premium.rest) as totalRestNotPayed from premium where premium.payed = 0  group By premium.premiumclient_id) as premiumForCOuntTable 
                on premiumForCOuntTable.premiumclient_id = premium.premiumclient_id 
                left join( select premium.premiumclient_id ,premium.date ,count(premium.id) as totalLate ,sum(premium.value) as totalValueLate from premium where premium.payed = 0 and premium.date < "'.$date.'"  group By premium.premiumclient_id) as premiumForToday 
                on premiumForToday.premiumclient_id = premium.premiumclient_id   
                  ' . $queryString . ' group By premium.premiumclient_id';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getList($sqlQuery);
    }

    public function getbyuseridtonotic($user_id,$today) {

        $sql = 'SELECT premiumclient.*,client.clientname , premiumForCOuntTable.totalnotpayed, premiumForToday.totalLate, premiumForTodayRest.totalRest  FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join client on premiumclient.client_id = client.clientid 
                left join( select premium.premiumclient_id ,premium.date,sum(premium.value) as totalnotpayed from premium where premium.payed = 0 and premium.date < "'.$today.'"  group By premium.premiumclient_id) as premiumForCOuntTable 
                on premiumForCOuntTable.premiumclient_id = premium.premiumclient_id 
                left join( select premium.premiumclient_id ,premium.date ,count(premium.id) as totalLate from premium where premium.payed = 0 and premium.date < "'.$today.'"  group By premium.premiumclient_id) as premiumForToday 
                on premiumForToday.premiumclient_id = premium.premiumclient_id 
                left join( select premium.premiumclient_id ,premium.date ,sum(premium.rest) as totalRest from premium where premium.payed = 0 and premium.date < "'.$today.'"  group By premium.premiumclient_id) as premiumForTodayRest 
                on premiumForTodayRest.premiumclient_id = premium.premiumclient_id 
                group By client.clientid';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getList($sqlQuery);
    }


    public function checkPremiumClientData($clientID) {

        $sql = 'SELECT premiumclient.*,premium.*,premium.conditions as premConditions,client.* FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join client on premiumclient.client_id = client.clientid 
                  where premiumclient.client_id = '.$clientID.' and premium.payed = 0';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getList($sqlQuery);
    }


    public function getpremiumtotal($premiumclient_id) {
        $sql = 'SELECT premiumclient.*,premiumForCOuntTable.totalnotpayed, premiumForCOuntTable.totalValueNotPayed,premiumForCOuntTable.totalRestNotPayed FROM `premiumclient` 
                join premium on premiumclient.id = premium.premiumclient_id 
                left join( select premium.premiumclient_id ,count(premium.id) as totalnotpayed,sum(premium.value) as totalValueNotPayed,sum(premium.rest) as totalRestNotPayed from premium where premium.payed = 0  group By premium.premiumclient_id) as premiumForCOuntTable 
                on premiumForCOuntTable.premiumclient_id = premium.premiumclient_id 
                where premiumclient.id = '.$premiumclient_id.' group By premium.premiumclient_id';
        $sqlQuery = new SqlQuery($sql);
//        print_r($sql.'<br>');
        return $this->getRow($sqlQuery);
    }

}
?>