<?php

/**
 * Class that operate on table 'client'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2013-04-06 10:57
 */
class ClientMySqlExtDAO extends ClientMySqlDAO {

    public function countAddressOccurance($value) {
        $sql = 'SELECT count(*) FROM client WHERE clientaddress = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->querySingleResult($sqlQuery);
    }

    public function insertFirstClient($client) {
        $sql = 'INSERT INTO client (clientid, clientname, clientaddress, clientphone, clientmobile, clientdebt, clientdetails, conditions, clientdate, userid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($client->clientid);
        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientaddress);
        $sqlQuery->set($client->clientphone);
        $sqlQuery->set($client->clientmobile);
        $sqlQuery->set($client->clientdebt);
        $sqlQuery->setString2($client->clientdetails);
        $sqlQuery->setNumber($client->conditions);
        $sqlQuery->set($client->clientdate);
        $sqlQuery->setNumber($client->userid);

        $this->executeInsert($sqlQuery);
    }

    public function getClientByRFID($rfid) {
        $sql = 'SELECT *
		FROM client
		WHERE clientRFID = "' . $rfid . '" and conditions = 0';
        //print_r($sql . '<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function querybyarea($clientareaid, $clientDate) {
        $sql = 'SELECT client.* , clientarea.name as clientareaName

		FROM client
                join clientarea on clientarea.id = client.clientareaid
		WHERE ' . $clientareaid . '
                ' . $clientDate . '
		order by clientid	 desc';


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

    public function querybyarea2($clientareaid) {
        $sql = 'SELECT client.* , clientarea.name as clientareaName

		FROM client
                join clientarea on clientarea.id = client.clientareaid
		WHERE client.clientareaid = ' . $clientareaid . '

		order by clientid	 desc';


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

    public function updatedept($client) {
        $sql = 'UPDATE client SET  clientdebt = ? , clientdate = ?, userid = ? WHERE clientid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($client->clientdebt);
        $sqlQuery->set($client->clientdate);
        $sqlQuery->setNumber($client->userid);

        $sqlQuery->setNumber($client->clientid);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatedeptlimit($debtLimit) {
        $sql = 'UPDATE client SET  debtLimit = ' . $debtLimit;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatedept1($clientid, $clientdebt) {
        $sql = 'UPDATE client SET  clientdebt = "' . $clientdebt . '"
		        WHERE clientid = "' . $clientid . '"';

        $sqlQuery = new SqlQuery($sql);

        //print_r($sqlQuery);

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

    public function updateClientDelegateid($clientid, $delegateid) {
        $sql = 'UPDATE client SET  delegateid = ' . $delegateid . ' where clientid = ' . $clientid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function loadExt($id) {
        $sql = 'SELECT * FROM client WHERE clientid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getList($sqlQuery);
    }

    //pagination
    public function queryAllWithLimit($startpoint, $perpage) {
        $sql = 'SELECT * FROM client
				ORDER BY clientid ASC
				limit ' . $startpoint . ',' . $perpage . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllForShow() {
        $sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName,deligate.employeename as deligateName
			FROM client
                        left join user on user.userid = client.userid
                        left join user as lasteditone on lasteditone.userid = client.lastEditUser
                        left join user as deligate on deligate.userid = client.delegateid
                        WHERE  client.conditions = 0
				ORDER BY clientid ASC ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllsup() {
        $sql = 'SELECT * FROM client WHERE conditions=0 ORDER BY clientdate DESC ,clientid DESC  ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /*
     * Update record in table to make conditions field = 1 to mark deleted status.
     */

    public function deletetemp($clientid) {

        $sql = 'UPDATE client SET conditions = 1
		        WHERE clientid =' . $clientid . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function returndelete($clientid) {

        $sql = 'UPDATE client SET conditions = 0
		        WHERE clientid =' . $clientid . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByClientNameExt($input) {
        $sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
			FROM client
                        left join user on user.userid = client.userid
                        left join user as lasteditone on lasteditone.userid = client.lastEditUser
			WHERE clientname LIKE "' . $input . '%"
			ORDER BY clientdate DESC ,clientid DESC  ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByClientNameExtLimited($input, $start, $end) {
        $sql = 'SELECT *
			FROM client
			WHERE clientname LIKE "' . $input . '%"
			ORDER BY clientdate DESC ,clientid DESC
			limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallExtLimited($input) {
        $sql = 'SELECT clientname,clientid,clientmobile,clientdebt
			FROM client
			WHERE (clientname LIKE "%' . $input . '%"
                            or  clientphone	 LIKE "' . $input . '%"
                            or  clientmobile	 LIKE "' . $input . '%"
                            or  clientcode LIKE "' . $input . '%") and conditions = 0
			ORDER BY clientdate DESC ,clientid DESC
			';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateClientdebt($client) {
        $sql = 'UPDATE client SET  clientdebt = ?, userid = ? WHERE clientid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($client->clientdebt);
        $sqlQuery->setNumber($client->userid);

        $sqlQuery->setNumber($client->clientid);
        return $this->executeUpdate($sqlQuery);
    }

    public function updatewebStoreType($webStoreType, $clientid) {
        $sql = 'UPDATE client SET  webStoreType = ' . $webStoreType . ' WHERE clientid = ' . $clientid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getClientsTotalDebt() {
        $sql = 'SELECT SUM(clientdebt)
			FROM client
			WHERE conditions=0';
        $sqlQuery = new SqlQuery($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryAllClientsEX() {
        $sql = 'SELECT client.clientid,client.clientname
				FROM client WHERE conditions=0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryClientsEX($branchId) {
        $sql = 'SELECT client.clientid,client.clientname
				FROM client WHERE conditions=0 AND branchId = ' . $branchId . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClients1($name) {
        $sql = 'SELECT client.clientid AS value ,client.clientname  AS label
				FROM client
				WHERE clientname LIKE "%' . $name . '%"
				AND conditions=0
				AND clientname != ""';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function getClients2($name) {
        $sql = 'SELECT client.clientid AS value ,client.clientphone  AS label
				FROM client
				WHERE clientphone LIKE "%' . $name . '%"
				AND conditions=0
				AND clientphone != ""';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllbyarea($clientareaid) {
        $sql = 'SELECT * FROM client where clientareaid = ' . $clientareaid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientsTotalDebt2() {
        $sql = 'SELECT SUM(clientdebt)
			FROM client
			WHERE conditions=0
			';
        $sqlQuery = new SqlQuery($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    public function getClientsforoffer($name) {
        $sql = 'SELECT client.clientid AS value ,client.clientname  AS label
				FROM client
				WHERE clientname LIKE "%' . $name . '%"
				AND conditions=0
				AND clientname != ""';

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

    public function insertclient($client) {
        $sql = 'INSERT INTO client (clientname,clientdebt,clientdetails,treeId) VALUES (?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientdebt);
        $sqlQuery->set($client->clientdetails);
        $sqlQuery->set($client->treeId);
        $id = $this->executeInsert($sqlQuery);
        $client->clientid = $id;
        return $id;
    }

    public function insertclientstudents($client) {
        $sql = 'INSERT INTO client (clientname,clientphone,clientaddress,branchId,clientdate,userid)
         VALUES (?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientphone);
        $sqlQuery->set($client->clientaddress);
        $sqlQuery->set($client->branchid);
        $sqlQuery->set($client->clientdate);
        $sqlQuery->set($client->userid);
        $id = $this->executeInsert($sqlQuery);
        $client->clientid = $id;
        return $id;
    }

    public function updateclientstudents($client) {
        $sql = 'UPDATE client SET  clientname = ?, clientphone = ?, clientaddress = ?, branchId = ? WHERE clientid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientphone);
        $sqlQuery->set($client->clientaddress);
        $sqlQuery->set($client->branchid);
        $sqlQuery->setNumber($client->clientid);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateClientname($client) {
        $sql = 'UPDATE client SET  clientname = ?, clientdebt = ?, clientdetails = ? WHERE clientid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientdebt);
        $sqlQuery->set($client->clientdetails);
        $sqlQuery->setNumber($client->clientid);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByRondomtxt($value) {
        $sql = 'SELECT * FROM client WHERE rondomtxt = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getRow($sqlQuery);
    }

    public function clientsWithoutBills($queryString1, $queryString, $queryStringR, $queryString1R, $queryString1SR) {
        $sql = 'SELECT * from client where  client.conditions = 0 and   client.clientid
                NOT IN
                    (
                        SELECT sellbill.sellbillclientid   FROM `sellbill`
                        join client on client.clientid = sellbill.sellbillclientid
                        ' . $queryString1 . '
                     )
                and  client.clientid
                NOT IN
                    (
                        SELECT client.clientid  FROM `bills`
                        join client on client.clientid = bills.clientid
                        ' . $queryString . '
                     )
                and    client.clientid
                NOT IN
                    (
                        SELECT client.clientid FROM `billsreturn`
                        join bills on bills.id = billsreturn.billid
                        join client on client.clientid = bills.clientid
                        ' . $queryStringR . '
                     )
                and  client.clientid
                NOT IN
                    (
                        SELECT client.clientid FROM `returnsellbill`
                        join client on client.clientid = returnsellbill.returnsellbillclientid
                        ' . $queryString1R . '
                     )
                and   client.clientid
                NOT IN
                    (
                        SELECT client.clientid FROM `sellbillandrutern`
                        join client on client.clientid = sellbillandrutern.sellbillclientid
                        ' . $queryString1SR . '
                     )
                ';
        // echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientsAndGovernmentAndArea($queryString) {
        $sql = 'SELECT client.clientid as clientid ,  client.clientdebt as clientdebt , client.clientareaid as clientareaid ,client.typeclientid as typeclientid, government.governmetid as governmetid,
                client.clientname as clientname , clientarea.name as clientareaName , government.governmentname as governmentname
                FROM `client`
                join goverarea on goverarea.clientareaid = client.clientareaid
                join clientarea on clientarea.id = client.clientareaid
                join government on government.governmetid = goverarea.governmentid
                   ' . $queryString . ' order by  client.clientid';
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //azmy
    public function getClientsAndGovernmentAndAreas($queryString, $queryStringTy) {
        $sql = 'SELECT client.clientid as clientid ,  client.clientdebt as clientdebt , client.clientareaid as clientareaid ,client.typeclientid as typeclientid, government.governmetid as governmetid,
                client.clientname as clientname , clientarea.name as clientareaName , government.governmentname as governmentname
                FROM `client`
                join goverarea on goverarea.clientareaid = client.clientareaid
                join clientarea on clientarea.id = client.clientareaid
                join government on government.governmetid = goverarea.governmentid
                   ' . $queryString . ' ' . $queryStringTy . ' order by  client.clientid ';
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientsAndGovernmentAndAreafromdeptchange($clientID) {
        $sql = 'SELECT * from clientdebtchange where clientid = ' . $clientID;
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByAreaidSimple($value) {
        $sql = 'SELECT clientid FROM client WHERE clientareaid = ?';

        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);

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

    public function queryByGovididSimple($value) {
        $sql = 'SELECT client.clientid
                FROM client
                join goverarea on goverarea.clientareaid = client.clientareaid
                WHERE goverarea.governmentid = ?';

        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);

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

    public function getAutoIncrementValue($DBName) {
        $sql = 'SELECT `AUTO_INCREMENT`
                FROM  INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = "' . $DBName . '"
                AND   TABLE_NAME   = "client" ';

        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryByTypeclientid($typeclientid) {
        $sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
            FROM client
                        left join user on user.userid = client.userid
                        left join user as lasteditone on lasteditone.userid = client.lastEditUser
                        WHERE client.conditions = 0 and (typeclientid like "%,' . $typeclientid . ',%" or typeclientid like "%,-20,%") ';
        // old was "%' . $typeclientid . '%" with out the two commas ,
        //or like "%,-20,%" to show client with type -20 in all other types tab
        $sqlQuery = new SqlQuery($sql);
        //        $sqlQuery->set($typeclientid);
        return $this->getList($sqlQuery);
    }

    public function queryByTypeclientidEqual($typeclientid) {
        $sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
            FROM client
                        left join user on user.userid = client.userid
                        left join user as lasteditone on lasteditone.userid = client.lastEditUser
                        WHERE client.conditions = 0 and typeclientid = "' . $typeclientid . '" ';
        $sqlQuery = new SqlQuery($sql);
        //        $sqlQuery->set($typeclientid);
        return $this->getList($sqlQuery);
    }

    public function queryByTypeclientconditions() {
        $sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
            FROM client
                        left join user on user.userid = client.userid
                        left join user as lasteditone on lasteditone.userid = client.lastEditUser
                        WHERE client.conditions = 1 ';
        $sqlQuery = new SqlQuery($sql);
        //        $sqlQuery->set($typeclientid);
        return $this->getList($sqlQuery);
    }

    public function getClientsAndAreaSelect2($name) {
        $sql = 'SELECT client.clientid,CONCAT(client.clientname, " - ",  clientarea.name) as clientname
		FROM client
                join clientarea on clientarea.id = client.clientareaid
		WHERE  client.conditions = 0
                and (client.clientname like "%' . $name . '%" or clientarea.name like "%' . $name . '%")';


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

    public function runSelectQuery($sql) {
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //it gets the count of products that is not optic
    public function getClientsCountForCURLNormal($sqlquery = '') {
        $sql = 'SELECT count(clientid) as clientid
                FROM client
                where conditions=0 ' . $sqlquery;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllOrderedLimitedSimpleForCurlNormal($start, $end, $sqlquery = '') {
        $sql = 'SELECT *
		FROM client
                where conditions=0 ' . $sqlquery . ' and clientid > ' . $start . '
                order by clientid asc
		limit ' . $end . '';
        //var_dump($sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function queryAllOrderedLimitedSimpleForCurlNormal2($limit, $sqlquery = '') {
        $sql = 'SELECT *
		FROM client
                where conditions=0 ' . $sqlquery . '
                order by clientid asc
		limit ' . $limit . '';
        //var_dump($sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function getalldata($clientID) {
        $sql = 'SELECT client.*,clientarea.name as areaName,typeclient.typeName FROM client
                left join clientarea on clientarea.id = client.clientareaid
                left join typeclient on typeclient.typeId = client.typeclientid
		WHERE  client.conditions = 0
        and client.clientid = ' . $clientID;

        //        print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadGetalldata2($clientid) {
        $sql = 'SELECT client.*, government.governmetid as governmetid,client.clientname as clientname,clientarea.name as clientareaName,government.governmentname as governmentname
                FROM `client`
                left join goverarea on goverarea.clientareaid = client.clientareaid
                left join clientarea on clientarea.id = client.clientareaid
                left join government on government.governmetid = goverarea.governmentid
                WHERE client.clientid = ' . $clientid;
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function getClientsDebtAfterRevision() {
        $sql = 'SELECT client.clientid,client.clientname,client.clientdebt,t.clientdebtcalc
		FROM client
                join clienttemp t on t.clientid = client.clientid
		WHERE client.clientdebt!=t.clientdebtcalc';
        //print_r($sql . '<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function markClientAsNOTInUse($clientid = 0) {
        $sql = 'UPDATE client SET  inUse = 0 where clientid = ' . $clientid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function createClientInUseSP() {

        $_SESSION['stop_checking_result'] = 1;
        $_SESSION['noWriteToBackup'] = 1;

        $sql = 'DROP PROCEDURE IF EXISTS clientInUse;';
        $sqlQuery = new SqlQuery($sql);
        $this->executeUpdate($sqlQuery);

        //create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
        $sql = '
CREATE PROCEDURE clientInUse(IN id INT,OUT debtis VARCHAR(256),OUT nameis VARCHAR(256),OUT treeIdis VARCHAR(256))
BEGIN
DECLARE in_use_flag INT DEFAULT 1;
SELECT inUse FROM client WHERE clientid = id INTO in_use_flag;

    IF in_use_flag = 0 THEN
        update client set inUse=1 where clientid = id;
        SELECT clientdebt,clientname,treeId INTO debtis,nameis,treeIdis FROM client WHERE clientid = id ;
    ELSE
        SELECT "in_use","in_use","in_use" INTO debtis,nameis,treeIdis;
    END IF;

END

';
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery);
        unset($_SESSION['stop_checking_result']);
        unset($_SESSION['noWriteToBackup']);
        return $affected_rows;
    }

    public function callClientInUseSP($clientId) {
        if ($clientId > 1) {
            $mysqli = new mysqli(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
            if ($mysqli->connect_errno) {
                echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
            }
            ##checks
            $callSql = "CALL clientInUse($clientId,@clientdebt,@clientname,@treeIdis)";

            $myfile = fopen("backup/" . date("Y-m-d") . ".txt", "a+") or die("Unable to open file!");
            $txt = $callSql . ";\r\n";
            fwrite($myfile, $txt);

            fclose($myfile);


            if (!$mysqli->query("SET @clientdebt='in_use',@clientname='in_use',@treeIdis='in_use'") || !$mysqli->query($callSql)) {
                echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }

            if (!($res = $mysqli->query("SELECT @clientdebt as clientdebt,@clientname as clientname,@treeIdis as treeId"))) {
                echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
            }
            $row = $res->fetch_assoc();
            $rowAsObj = new stdClass();
            foreach ($row as $key => $value) {
                $rowAsObj->$key = $value;
            }
            return $rowAsObj;
        }
    }

    //    public function clientdeptbytypeclient($queryStringTy) {
    //        $sql = 'SELECT client.*,clientarea.name as areaName,government.governmentname , FROM client
    //                LEFT JOIN clientarea on client.clientareaid  = clientarea.id
    //                LEFT JOIN goverarea on client.clientareaid = goverarea.clientareaid
    //                LEFT JOIN government on goverarea.governmentid = government.governmetid where
    //        ' . $queryStringTy.' ORDER BY clientid DESC';
    ////        echo $sql;
    //        $sqlQuery = new SqlQuery($sql);
    //        return $this->getList($sqlQuery);
    //    }

    public function getCountByColumnEqualNotDel($column, $value, $queryString = '') {
        $sql = 'SELECT count(*) FROM client WHERE conditions = 0 and ' . $column . '= "' . $value . '" ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllForShowaz() {
        $sql = 'SELECT client.*,user.employeename,lasteditone.employeename as lastEditUserName
			FROM client
                        left join user on user.userid = client.userid
                        left join user as lasteditone on lasteditone.userid = client.lastEditUser WHERE  client.conditions = 1
				ORDER BY clientid ASC ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllTamweenDa3mStatus($queryStringJoin = '', $queryString = '') {
        $sql = 'SELECT client.clientid,client.clientcode,client.clientname,da3mStatus,tamweenda3mstatus.id as tamweenda3mstatusid,user.employeename,tamweenda3mstatus.sysdate,cardNum,noOfPersonsDa3m,cardPassword
                FROM client
                join tamweenclientdetail on tamweenclientdetail.clientid = client.clientid
                left join tamweenda3mstatus on (client.clientid = tamweenda3mstatus.clientid ' . $queryStringJoin . ')
                left join user on user.userid = tamweenda3mstatus.userid
                where 1 ' . $queryString;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllTamweenDa3mStatus2($queryStringJoin = '', $queryString = '') {
        $sql = 'SELECT client.clientid,client.clientcode,client.clientname,tamweenStatus,tamweenda3mstatus.id as tamweenda3mstatusid,user.employeename,tamweenda3mstatus.sysdateTamween as sysdate,cardNum,noOfPersonsTamween,cardPassword
                FROM client
                join tamweenclientdetail on tamweenclientdetail.clientid = client.clientid
                left join tamweenda3mstatus on (client.clientid = tamweenda3mstatus.clientid ' . $queryStringJoin . ')
                left join user on user.userid = tamweenda3mstatus.useridTamween
                where 1 ' . $queryString;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function sumDebtByQueryString($queryString) {
        $sql = 'SELECT sum(clientdebt) as clientdebt
                FROM client ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function updateButNotDept($client) {
        $sql = 'UPDATE client SET clientname = ?, clientaddress = ?, clientphone = ?, clientmobile = ?, clientdetails = ?, conditions = ?, clientdate = ?, userid = ?  , clientareaid = ?  ,clientcode = ?,dailyentryid = ? , rondomtxt = ?,clientStoreIds = ?, obygyPatientId = ? , debtLimit=? , typeclientid=?,priceTypeId=?,lastEditUser = ?,inUse = ?,card_number = ?,file_faida = ?,specialDiscount = ?,specialDiscountVal = ?,file = ?,mandobCollectRatio=?,clientRFID = ?,postponeDays=?,taxnumber=?, password=?,clientTypeForTree=?,treeId=?,clientaddress2=?,country=?,delegateid=?,webStoreType=?,clientaddress3=?,taxfile=?,fileimg=?,tagids=?,txtemail=? WHERE clientid = ?'; //, clientdebt = ?
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientaddress);
        $sqlQuery->set($client->clientphone);
        $sqlQuery->set($client->clientmobile);
        //$sqlQuery->set($client->clientdebt);
        $sqlQuery->setString2($client->clientdetails);
        $sqlQuery->setNumber($client->conditions);
        $sqlQuery->set($client->clientdate);
        $sqlQuery->setNumber($client->userid);
        $sqlQuery->setNumber($client->clientareaid);
        $sqlQuery->set($client->clientcode);
        $sqlQuery->setNumber($client->dailyentryid);
        $sqlQuery->set($client->rondomtxt);
        $sqlQuery->set($client->clientStoreIds);
        $sqlQuery->set((int) $client->obygyPatientId);
        $sqlQuery->setNumber((float) $client->debtLimit);
        $sqlQuery->set($client->typeclientid);
        $sqlQuery->setNumber((int) $client->priceTypeId);
        $sqlQuery->setNumber((int) $client->lastEditUser);
        $sqlQuery->setNumber((int) $client->inUse);
        $sqlQuery->setNumber((int) $client->card_number);
        $sqlQuery->set($client->file_faida);
        $sqlQuery->setNumber((int) $client->specialDiscount);
        $sqlQuery->setNumber((float) $client->specialDiscountVal);
        $sqlQuery->set($client->file);
        $sqlQuery->setNumber((float) $client->mandobCollectRatio);
        $sqlQuery->set($client->clientRFID);
        $sqlQuery->set((int) $client->postponeDays);
        $sqlQuery->set($client->taxnumber);
        $sqlQuery->set($client->password);
        $sqlQuery->setNumber((int) $client->clientTypeForTree);
        $sqlQuery->setNumber((int) $client->treeId);
        $sqlQuery->set($client->clientaddress2);
        $sqlQuery->set($client->country);
        $sqlQuery->setNumber((int) $client->delegateid);
        $sqlQuery->setNumber((int) $client->webStoreType);
        $sqlQuery->set($client->clientaddress3);
        $sqlQuery->set($client->taxfile);
        $sqlQuery->set($client->fileimg);
        $sqlQuery->set($client->tagids);
        $sqlQuery->set($client->txtemail);


        $sqlQuery->setNumber($client->clientid);
        return $this->executeUpdate($sqlQuery);
    }

    public function removeAnyClientLinkForASupplier($linkedSupplierId) {
        $sql = 'UPDATE client SET linkedSupplierId = 0 WHERE linkedSupplierId = ' . $linkedSupplierId;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryAllWithQueryString($queryString) {
        $sql = 'SELECT SQL_CACHE *
                FROM client where 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getclientPremium() {
        $sql = 'SELECT premiumclient.client_id ,client.clientname,client.clientid
                FROM premiumclient join client on premiumclient.client_id = client.clientid group BY premiumclient.client_id';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByCondition($queryString) {
        $sql = 'SELECT SQL_CACHE *
                FROM client where 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByHusbandNameObgy($value) {
        $sql = 'SELECT * FROM client WHERE husbandNameObgy = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function freeClient($clientid) {
        $sql = 'UPDATE client SET inUse=0 WHERE clientid = ' . $clientid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function getAllClientsExceptId($clientid) {
        $sql = 'SELECT * FROM client where clientid != ' . $clientid;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function clientsWithoutBillsWithArea($areaQS = '', $queryString1, $queryString, $queryStringR, $queryString1R, $queryString1SR) {
        $sql = 'SELECT * from client where  client.conditions = 0
        ' . $areaQS . ' and   client.clientid
                NOT IN
                    (
                        SELECT sellbill.sellbillclientid   FROM `sellbill`
                        join client on client.clientid = sellbill.sellbillclientid
                        ' . $queryString1 . '
                     )
                and  client.clientid
                NOT IN
                    (
                        SELECT client.clientid  FROM `bills`
                        join client on client.clientid = bills.clientid
                        ' . $queryString . '
                     )
                and    client.clientid
                NOT IN
                    (
                        SELECT client.clientid FROM `billsreturn`
                        join bills on bills.id = billsreturn.billid
                        join client on client.clientid = bills.clientid
                        ' . $queryStringR . '
                     )
                and  client.clientid
                NOT IN
                    (
                        SELECT client.clientid FROM `returnsellbill`
                        join client on client.clientid = returnsellbill.returnsellbillclientid
                        ' . $queryString1R . '
                     )
                and   client.clientid
                NOT IN
                    (
                        SELECT client.clientid FROM `sellbillandrutern`
                        join client on client.clientid = sellbillandrutern.sellbillclientid
                        ' . $queryString1SR . '
                     )
                ';
        // echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateClientnameWithoutDebt($client) {
        $sql = 'UPDATE client SET  clientname = ?, clientdetails = ? WHERE clientid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($client->clientname);
        $sqlQuery->set($client->clientdetails);
        $sqlQuery->setNumber($client->clientid);
        return $this->executeUpdate($sqlQuery);
    }

}
