<?php

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

    public function getAllClientDeptChangeAfterId($id, $query)
    {
        $sql = 'SELECT clientdebtchange.*,client.clientname,client.clientdebt,client.clientcode,client.clientmobile,client.clientaddress
		FROM clientdebtchange
                JOIN client ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchangeid > ' . $id . ' ' . $query . ' order by clientdebtchangeid asc';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getAllClientDeptChange($limit, $query)
    {
        $sql = 'SELECT clientdebtchange.*,client.clientname,client.clientdebt,client.clientcode,client.clientmobile,client.clientaddress
		FROM clientdebtchange
                JOIN client ON client.clientid  = clientdebtchange.clientid
		WHERE 1 ' . $query . ' order by clientdebtchangeid asc limit ' . $limit;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryPayedDeptOnly($startDate, $endDate)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "clientPayedDeptController.php"
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND  DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"
		order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateDebt($clientdebtchange)
    {
        $sql = 'UPDATE clientdebtchange SET del=? WHERE clientdebtchangeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($clientdebtchange->del);

        $sqlQuery->setNumber($clientdebtchange->clientdebtchangemodelid);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryPayedDeptOnlyLimited($startDate, $endDate, $startpage, $perpage)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "clientPayedDeptController.php"
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"
		order by clientdebtchangeid desc
		limit ' . $startpage . ', ' . $perpage . ' ';

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

    public function queryWithTableName($startDate, $endDate, $tablename)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "' . $tablename . '"
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"
		order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithTableNameLimited($startDate, $endDate, $tablename, $startpage, $perpage)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "' . $tablename . '"
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND  DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"

		order by clientdebtchangeid desc
		limit ' . $startpage . ', ' . $perpage . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByClientIdAndDate($queryString, $orderBy = ' order by  clientdebtchangedate desc ,clientdebtchangeid desc ')
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname , CONCAT(`tablename`,`clientdebtchangemodelid`,clientdebtchangeamount,`clientdebtchangetype`) AS finalstring
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		 ' . $queryString . '
		' . $orderBy . ''; //order by  clientdebtchangedate desc ,clientdebtchangeid desc
        // print("<br>" . $sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function clientShowforsellonly($queryString)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname,client.taxnumber as clienttaxnumber , user.username, CONCAT(`tablename`,`clientdebtchangemodelid`,clientdebtchangeamount,`clientdebtchangetype`) AS finalstring
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
                JOIN user
                ON user.userid = clientdebtchange.userid

		where  ( tablename="sellbillController.php" || tablename="returnsellbillController.php" ||  tablename="sellbillandruternController.php" )
		 ' . $queryString . '


		order by clientdebtchangeid desc';
        //    print("<br>" . $sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function clientShowAll($queryString)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname,client.taxnumber as clienttaxnumber , user.username, CONCAT(`tablename`,`clientdebtchangemodelid`,clientdebtchangeamount,`clientdebtchangetype`) AS finalstring
		FROM clientdebtchange
		left JOIN client
		ON client.clientid  = clientdebtchange.clientid
                left JOIN user
                ON user.userid = clientdebtchange.userid

		where  1 ' . $queryString . '

		order by clientdebtchangeid desc';
        //print("<br>" . $sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function queryByClientIdAndDatenew($queryString)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname , CONCAT(`tablename`,`clientdebtchangemodelid`,clientdebtchangeamount,`clientdebtchangetype`) AS finalstring
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		 ' . $queryString . '
		order by clientdebtchangeid asc';
        //print("<br>".$sql);
        $sqlQuery = new SqlQuery($sql);

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

    //# فى حالة اظهار المدفوعات فى الفواتير حذف سطر
    //# and (tablename != "sellbillController.php" and  tablename != "returnsellbillController.php" and tablename != "sellbillandruternController.php")
    //# بالكامل
    public function queryByClientIdAndDate2($clientid, $startDate, $endDate)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.clientid = ' . $clientid . '
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"
		and (tablename != "sellbillController.php" and  tablename != "returnsellbillController.php" and tablename != "sellbillandruternController.php")
		order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        //print('<br>'.$sql.'<br>');
        return $this->getList($sqlQuery);
    }

    public function queryByClientIdAndDateLimited($clientid, $startDate, $endDate, $startpage, $perpage)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.clientid = ' . $clientid . '
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND  DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"

		order by clientdebtchangeid desc
		limit ' . $startpage . ', ' . $perpage . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientName($clientdebtchangeid)
    {
        $sql = 'SELECT client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.clientdebtchangeid = ' . $clientdebtchangeid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadvouchernumber($id)
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientdebtchangemodelid` LIKE "' . $id . '%" order by clientdebtchangeid desc';

        $sqlQuery = new SqlQuery($sql);

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

    public function queryreceipt($queryString = '', $limitQS = '')
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "clientPayedDeptController.php"
		and clientdebtchangemodelid > 0 ' . $queryString . '
		order by clientdebtchangeid desc ' . $limitQS;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryreceiptSimple($queryString = '', $limitQS = '')
    {
        $sql = 'SELECT clientdebtchange.*
		FROM clientdebtchange
		WHERE clientdebtchange.tablename = "clientPayedDeptController.php"
		' . $queryString . ' ' . $limitQS;
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function showbymodelid($modelid)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "clientPayedDeptController.php"
		AND clientdebtchangemodelid=' . $modelid . '
		order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function search($queryString, $tablename, $branchId)
    {
        $sql = 'SELECT Distinct(clientdebtchange.clientdebtchangeid) as clientdebtchangeid,
		clientdebtchange.clientdebtchangebefore
		,clientdebtchange.clientdebtchangetype
		,clientdebtchange.clientdebtchangeamount
		,clientdebtchange.processname
		,clientdebtchange.clientdebtchangeafter
		,clientdebtchange.clientdebtchangedate
		,clientdebtchange.totalOperationCost
		,clientdebtchange.comment
		,clientdebtchange.clientid
		, client.clientname
		,m_finishingpayed.finishingPayedId
		,clientdebtchange.discount
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		left join m_finishingpayed
		ON clientdebtchange.clientdebtchangeid  = m_finishingpayed.clientDebtChangeId
		' . $queryString . '
		AND clientdebtchange.tablename = "' . $tablename . '"
		AND client.branchId = ' . $branchId . '
		order by clientdebtchange.clientdebtchangeid desc';

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

    public function loadclient($id)
    {
        $sql = 'SELECT Distinct(clientdebtchange.clientdebtchangeid) as clientdebtchangeid,
		clientdebtchange.clientdebtchangebefore
		,clientdebtchange.clientdebtchangetype
		,clientdebtchange.clientdebtchangeamount
		,clientdebtchange.processname
		,clientdebtchange.clientdebtchangeafter
		,clientdebtchange.clientdebtchangedate
		,clientdebtchange.totalOperationCost
		,clientdebtchange.comment
		,clientdebtchange.clientid
		,clientdebtchange.discount
		,clientdebtchange.reciptid
		,m_finishingpayed.finishingPayedId
		, client.clientname
		, client.clientdebt
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		left join m_finishingpayed
		ON clientdebtchange.clientdebtchangeid  = m_finishingpayed.clientDebtChangeId
		 WHERE clientdebtchange.clientdebtchangeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function searchforDeptOnly($querystring)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname ,user.employeename
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
                JOIN user
		ON user.userid  = clientdebtchange.userid
		' . $querystring . '
		AND clientdebtchange.tablename = "clientPayedDeptController.php"

		order by clientdebtchangeid desc';
        //print_r("<br>".$sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function searchforDeptAndSave($querystring)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname ,user.employeename,save.savename
		FROM clientdebtchange
		JOIN client ON client.clientid  = clientdebtchange.clientid
                JOIN user ON user.userid  = clientdebtchange.userid
                JOIN savedaily on savedaily.savedailymodelid = clientdebtchange.clientdebtchangeid
                join save on savedaily.saveid =  save.saveid
		' . $querystring . '
		AND clientdebtchange.tablename = "clientPayedDeptController.php"
                AND savedaily.tablename = "clientPayedDeptController.php"
		order by clientdebtchangeid desc';
        //print_r("<br>" . $sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function searchforDeptOnly2($querystring)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		' . $querystring . '
		AND clientdebtchange.tablename = "clientPayedDeptController.php"
                and clientdebtchange.del = 0
		order by clientdebtchangeid desc';
        //print_r("<br>".$sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function searchforDeptOnlySimple($querystring)
    {
        $sql = 'SELECT clientdebtchange.*
		FROM clientdebtchange
		' . $querystring . '
		AND clientdebtchange.tablename = "clientPayedDeptController.php"
		order by clientdebtchangeid desc';
        //print_r("<br>".$sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithQueryString($queryString, $tablename)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "' . $tablename . '"
                 ' . $queryString . ' order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getlastByModelId($id)
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientdebtchangemodelid` = ' . $id . ' order by clientdebtchangeid desc';

        $sqlQuery = new SqlQuery($sql);

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

    public function selectByClientId($id)
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientid` = ' . $id . ' order by clientdebtchangeid desc limit 1';

        $sqlQuery = new SqlQuery($sql);

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

    public function selectByClientIdOrderByDateDESC($id, $queryString = '')
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientid` = ' . $id . ' ' . $queryString . ' order by clientdebtchangedate DESC limit 1';
        $sqlQuery = new SqlQuery($sql);

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

    public function selectListClientId($id)
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientid` = ' . $id . ' order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function selectClientIdByClientController($id, $tablename)
    {

        $sql = "SELECT * FROM clientdebtchange 	WHERE `clientid` = '  $id  ' and `tablename` = '$tablename' order by clientdebtchangeid desc";
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function queryWithClientId2($clientid)
    {
        $sql = 'SELECT SUM( clientdebtchange.clientdebtchangebefore ) AS cliTotalDept,
		       SUM( clientdebtchange.clientdebtchangeamount ) AS cliAmountDept
		       FROM clientdebtchange  JOIN client
                       ON clientdebtchange.clientid = client.clientid
                       where  clientdebtchange.clientid =' . $clientid . '
                       group by clientdebtchange.clientid limit 1';
        $sqlQuery = new SqlQuery($sql);


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

    public function selectListClientIdext($id, $querystring)
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientid` = ' . $id . ' ' . $querystring . ' order by clientdebtchangeid desc';

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

    public function selectClientIdByClientControllerext($id, $tablename, $querystring)
    {

        $sql = "SELECT * FROM clientdebtchange 	WHERE `clientid` = '  $id  ' and `tablename` = '$tablename'  '.$querystring.' order by clientdebtchangeid desc";
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

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

    public function selectLastClientDept($id)
    {
        $sql = 'SELECT * FROM clientdebtchange 	WHERE `clientid` = ' . $id . '   order by clientdebtchangeid desc limit 1';

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

    public function sumByBillId($billid)
    {
        $sql = 'SELECT billid , sum(clientdebtchangeamount) as clientdebtchangeamount FROM clientdebtchange WHERE `billid` = ' . $billid . '';

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

    public function sumByBillIdAndControl($billid, $tablename)
    {
        $sql = 'SELECT billid , sum(clientdebtchangeamount) as clientdebtchangeamount FROM clientdebtchange WHERE `billid` = ' . $billid . ' and tablename = "' . $tablename . '" ';

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

    public function lastByBillId($billid)
    {
        $sql = 'SELECT * FROM clientdebtchange WHERE `billid` = ' . $billid . ' order by clientdebtchangeid DESC limit 1 ';

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

    public function lastByBillIdAndControl($billid, $tablename)
    {
        $sql = 'SELECT * FROM clientdebtchange WHERE `billid` = ' . $billid . ' and tablename = "' . $tablename . '" order by clientdebtchangeid DESC limit 1 ';

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

    public function selectAllPayedBills($where)
    {
        $sql = "SELECT distinct `billid` FROM `clientdebtchange` where `billid` != '' " . $where . " order by `billid`";
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function selectAllPayedBillsAndControl($where, $tablename)
    {
        $sql = "SELECT distinct `billid` FROM `clientdebtchange` where `billid` != '' " . $where . " and tablename = '" . $tablename . "' order by `billid`";
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function selectByBillId($billid)
    {
        $sql = 'SELECT clientdebtchangeamount , clientdebtchangedate , billid , paytype  FROM clientdebtchange WHERE `billid` = ' . $billid . '';

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

    public function selectByBillIdAndControl($billid, $tablename)
    {
        $sql = 'SELECT clientdebtchangeid,clientdebtchangeamount , clientdebtchangedate , billid , paytype , comment,user.employeename,dailyentryid
                FROM clientdebtchange
                JOIN user ON clientdebtchange.userid  = user.userid
                WHERE `billid` = ' . $billid . ' and tablename = "' . $tablename . '" ';

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

    public function queryByClientIdAndDate3($clientid, $date)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.clientid = ' . $clientid . '
		AND ' . $date . '
		 (tablename != "sellbillController.php" and  tablename != "returnsellbillController.php" and tablename != "sellbillandruternController.php")
		order by clientdebtchangeid desc';
        //        print('<br>' . $sql . '<br>');

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

    public function getlastInGeneral()
    {
        $sql = 'SELECT * FROM clientdebtchange 	order by clientdebtchangeid desc limit 1';

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

    //azmy
    public function queryByClientIdAndDate2az($clientid, $queryString1)
    {
        $sql = 'SELECT clientdebtchange.*
		FROM clientdebtchange
		WHERE clientdebtchange.clientid = ' . $clientid . ' ' . $queryString1 . '
		and (tablename != "sellbillController.php" and  tablename != "returnsellbillController.php" and tablename != "sellbillandruternController.php"
                and  tablename != "clientDeficitController.php")
		and del = 0 order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        //        print('<br>'.$sql.'<br>');
        return $this->getList($sqlQuery);
    }

    public function clientdeptchangeaz($clientId)
    {
        $sql = 'SELECT * FROM clientdebtchange JOIN client ON client.clientid  = clientdebtchange.clientid where
        clientdebtchange.clientid =
        ' . $clientId . ' ORDER BY clientdebtchangeid DESC LIMIT 1 ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function clientdeptchangeandCommentClient($clientId)
    {
        $sql = '(SELECT clientdebtchange.clientdebtchangeid ,clientdebtchange.processname,clientdebtchange.clientdebtchangedate,clientdebtchange.totalOperationCost,
                clientdebtchange.clientdebtchangeamount,clientdebtchange.clientdebtchangebefore,clientdebtchange.clientdebtchangetype,clientdebtchange.clientdebtchangeafter,
                clientdebtchange.comment,clientdebtchange.userid,clientdebtchange.clientid,client.clientname,user.username
                FROM clientdebtchange
                JOIN client ON client.clientid  = clientdebtchange.clientid
                JOIN user ON client.userid  = user.userid
                where clientdebtchange.clientid =' . $clientId . ')
         UNION
                (' .
            'SELECT commentclient.id as clientdebtchangeid ,("اضافة تعليق") AS processname,commentclient.commentdate as clientdebtchangedate,0 AS totalOperationCost,0 AS clientdebtchangeamount,
                0 AS clientdebtchangebefore,0 AS clientdebtchangetype,0 AS clientdebtchangeafter,commentclient.comments as comment,commentclient.userid,commentclient.clientid,
                client.clientname,user.username
                 FROM  commentclient
                 JOIN client ON client.clientid  = commentclient.clientid
                JOIN user ON client.userid  = user.userid
                WHERE  commentclient.clientid = ' . $clientId . ') ORDER BY clientdebtchangeid DESC';
        //        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getMandobCollectData($queryString)
    {
        $sql = 'SELECT clientdebtchange.*,client.clientname,client.mandobCollectRatio
                FROM clientdebtchange
                JOIN client ON client.clientid  = clientdebtchange.clientid
                where 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function updateDailyEntryId($dailyentryid, $clientdebtchangeid)
    {
        $sql = 'UPDATE clientdebtchange SET dailyentryid=' . $dailyentryid . ' WHERE clientdebtchangeid = ' . $clientdebtchangeid;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

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

    public function queryPayedDeptOnlyByDate($startDate, $endDate)
    {
        $sql = 'SELECT clientdebtchange.*, client.clientname
		FROM clientdebtchange
		JOIN client
		ON client.clientid  = clientdebtchange.clientid
		WHERE clientdebtchange.tablename = "clientPayedDeptController.php" and clientdebtchange.del = 0
		AND DATE(clientdebtchange.clientdebtchangedate)
		>=  "' . $startDate . '"  AND  DATE(clientdebtchange.clientdebtchangedate) <= "' . $endDate . '"
		order by clientdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateComment($comment, $clientdebtchangeId)
    {
        $sql = 'UPDATE clientdebtchange SET comment=? WHERE clientdebtchangeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($comment);

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