<?php

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

    public function getSellBillsUsingQueryString($queryString1) {
        $sql = 'SELECT returnsellbill.*
		        FROM returnsellbill
				' . $queryString1 . '
				AND returnsellbill.deletedsellid=0 ';


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

    public function queryAllGenerale($returnsellbill) {


        $sql = 'SELECT *
    FROM returnsellbill

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

    public function insertupdate($returnsellbill) {
        $sql = 'INSERT INTO returnsellbill (returnsellbilldate, returnsellbillserial, returnsellbillclientid, returnsellbilltotaldeptbefor, returnsellbilltotaldeptafter, returnsellbilldiscount, returnsellbilldiscounttype, returnsellbilltotalbill, returnsellbillaftertotalbill, returnsellbilltotalpayed, returnsellbillfinalbill, returnsellbillstoreid, returnsellbillsysdate,comment, userid, conditions, returnsellbillclientname, returnsellbillsaveid, returnsellbildirectpayment, billnameid, sellerid, tempclientName, returnsellQuantity,deletedsellid,controlname,tax, carnumber, cartype, carchase, carmotor,costcenterid,visaAccountId,cashPayed,visaPayed,isBankAccountTransfer,extraDiscountPer,extraDiscountVal,taxOfDiscountPer,taxOfDiscountVal,obgyBillType,billoperationid,pricetype,currencyId,sellbillno) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($returnsellbill->returnsellbilldate);
        $sqlQuery->set($returnsellbill->returnsellbillserial);
        $sqlQuery->setNumber($returnsellbill->returnsellbillclientid);
        $sqlQuery->set($returnsellbill->returnsellbilltotaldeptbefor);
        $sqlQuery->set($returnsellbill->returnsellbilltotaldeptafter);
        $sqlQuery->set($returnsellbill->returnsellbilldiscount);
        $sqlQuery->setNumber($returnsellbill->returnsellbilldiscounttype);
        $sqlQuery->set($returnsellbill->returnsellbilltotalbill);
        $sqlQuery->set($returnsellbill->returnsellbillaftertotalbill);
        $sqlQuery->set($returnsellbill->returnsellbilltotalpayed);
        $sqlQuery->set($returnsellbill->returnsellbillfinalbill);
        $sqlQuery->setNumber($returnsellbill->returnsellbillstoreid);
        $sqlQuery->set($returnsellbill->returnsellbillsysdate);
        $sqlQuery->set($returnsellbill->comment);
        $sqlQuery->setNumber($returnsellbill->userid);
        $sqlQuery->setNumber($returnsellbill->conditions);
        $sqlQuery->set($returnsellbill->returnsellbillclientname);
        $sqlQuery->setNumber($returnsellbill->returnsellbillsaveid);
        $sqlQuery->setNumber($returnsellbill->returnsellbildirectpayment);
        $sqlQuery->setNumber($returnsellbill->billnameid);
        $sqlQuery->setNumber($returnsellbill->sellerid);
        $sqlQuery->set($returnsellbill->tempclientName);
        $sqlQuery->setNumber($returnsellbill->returnsellQuantity);

        $sqlQuery->setNumber($returnsellbill->deletedsellid);
        $sqlQuery->set($returnsellbill->controlname);
        $sqlQuery->set($returnsellbill->tax);
        $sqlQuery->set($returnsellbill->carnumber);
        $sqlQuery->set($returnsellbill->cartype);
        $sqlQuery->set($returnsellbill->carchase);
        $sqlQuery->set($returnsellbill->carmotor);
        $sqlQuery->setNumber($returnsellbill->costcenterid);
        $sqlQuery->setNumber((int) $returnsellbill->visaAccountId);
        $sqlQuery->setNumber((float) $returnsellbill->cashPayed);
        $sqlQuery->setNumber((float) $returnsellbill->visaPayed);
        $sqlQuery->setNumber((int) $returnsellbill->isBankAccountTransfer);
        $sqlQuery->setNumber((float) $returnsellbill->extraDiscountPer);
        $sqlQuery->setNumber((float) $returnsellbill->extraDiscountVal);
        $sqlQuery->setNumber((float) $returnsellbill->taxOfDiscountPer);
        $sqlQuery->setNumber((float) $returnsellbill->taxOfDiscountVal);
        $sqlQuery->setNumber((int) $returnsellbill->obgyBillType);
        $sqlQuery->setNumber((int) $returnsellbill->billoperationid);
        $sqlQuery->setNumber((int) $returnsellbill->pricetype);
        $sqlQuery->setNumber((int) $returnsellbill->currencyId);
        $sqlQuery->set($returnsellbill->sellbillno);

        $id = $this->executeInsert($sqlQuery);
        $returnsellbill->returnsellbillid = $id;
        return $id;
    }

    public function loadReturnsellbillById($returnsellbillid) {
        $sql = 'SELECT returnsellbill.*, client.clientname,
		store.storeName, user.employeename , user2.employeename  as  useremployeename


		 FROM returnsellbill LEFT JOIN client
		 ON returnsellbill.returnsellbillclientid = client.clientid left JOIN store
		 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
		 ON returnsellbill.sellerid = user.userid

		  LEFT JOIN user as user2
		  ON returnsellbill.userid = user2.userid

		 WHERE returnsellbill.returnsellbillid = ' . $returnsellbillid . '';


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

    public function updateConditions($returnsellbill, $query1 = '') {
        $sql = 'UPDATE returnsellbill SET returnsellbillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? ' . $query1 . ' WHERE returnsellbillid = ?';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->set($returnsellbill->returnsellbillsysdate);
        $sqlQuery->setNumber($returnsellbill->userid);
        $sqlQuery->setNumber($returnsellbill->delbyuserid);
        $sqlQuery->setNumber($returnsellbill->conditions);

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

    public function queryWithOrder($queryString = '') {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
                         where 1 ' . $queryString . '
			 order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithOrderLimited($start, $end) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 order by returnsellbillid desc
			 limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithClientId($clientid) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbillclientid =' . $clientid . '
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithClientId2($clientid) {
        $sql = 'SELECT
                        SUM( returnsellbill.returnsellbillaftertotalbill ) AS allGenTotalReturn
        		 FROM returnsellbill  JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid
			 where  returnsellbill.returnsellbillclientid =' . $clientid . '
                         group by returnsellbill.returnsellbillclientid
                         order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        // print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    public function queryWithClientIdLimited($start, $end, $clientid) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbillclientid =' . $clientid . '
			  order by returnsellbillid desc
			 limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithReturnsellbillid($returnsellbillid) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
		 	 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbillid =' . $returnsellbillid . '
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithSerail($serial) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
		 	 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbillserial ="' . $serial . '"
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithSerailLimited($start, $end, $serial) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbillserial ="' . $serial . '"
			  order by returnsellbillid desc
			 limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithClinetIdAndDate($clientid, $startDate, $endDate) {
        $sql = 'SELECT returnsellbill.returnsellbillid, returnsellbilldate, returnsellbillserial, returnsellbillaftertotalbill, returnsellQuantity
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid
			 where  returnsellbill.returnsellbilldate  >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
			 AND returnsellbill.returnsellbillclientid = ' . $clientid . '
			 AND returnsellbill.conditions = 0
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDate($startDate, $endDate) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename , user2.employeename  as  useremployeename


			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			  LEFT JOIN user as user2
		  ON returnsellbill.userid = user2.userid
			 where  returnsellbill.returnsellbilldate  >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallWithqueryString($queryString) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename,editedBill.returnsellbillid as newSellbillidAfterEdit,delbyuser.username as delbyusername
			 FROM returnsellbill
                         LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
                         JOIN store ON returnsellbill.returnsellbillstoreid = store.storeId
                         JOIN save ON returnsellbill.returnsellbillsaveid = save.saveid
                         LEFT JOIN user ON returnsellbill.sellerid = user.userid
			 LEFT JOIN user as user2 ON returnsellbill.userid = user2.userid
                         LEFT JOIN user as delbyuser ON delbyuser.userid = returnsellbill.delbyuserid
                         LEFT JOIN returnsellbill as editedBill ON returnsellbill.returnsellbillid = editedBill.deletedsellid
			 ' . $queryString . '
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallWithqueryString2($queryString) {


        $sql = 'SELECT returnsellbill.* , user.employeename , client.clientname,u.username FROM returnsellbill
		 LEFT JOIN user
		 ON returnsellbill.sellerid = user.userid
                 LEFT JOIN user u
		 ON returnsellbill.userid = u.userid
                  LEFT JOIN client
		 ON returnsellbill.returnsellbillclientid = client.clientid
		' . $queryString . '
		AND returnsellbill.conditions=0
		';
        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithDateLimited($start, $end, $startDate, $endDate) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbilldate  >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
			  order by returnsellbillid desc
			 limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditions($startDate, $endDate) {
        $sql = 'SELECT returnsellbill.*, client.clientname, user.employeename
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid JOIN store
			 ON returnsellbill.returnsellbillstoreid = store.storeId LEFT JOIN user
			 ON returnsellbill.sellerid = user.userid
			 where  returnsellbill.returnsellbilldate
			 >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
			 And  returnsellbill.conditions = 0
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsExt($startDate, $endDate) {
        $sql = 'SELECT SUM(returnsellbill.returnsellbillaftertotalbill)
		FROM returnsellbill
			 where  returnsellbill.returnsellbilldate
			 >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
			 And  returnsellbill.conditions = 0';

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

    public function loadExt($id) {
        $sql = 'SELECT returnsellbilltotalbill, returnsellbilldiscount, returnsellbilldiscounttype
		FROM returnsellbill
		WHERE returnsellbillid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function queryAllforrate($sellerid, $startDate, $endDate) {
        $sql = 'SELECT * FROM  `returnsellbill`  WHERE  `sellerid` =' . $sellerid . '  and
		 returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and conditions =0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllforrateall($startDate, $endDate) {
        $sql = 'SELECT * FROM  `returnsellbill`  WHERE
		 returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and conditions =0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function GetReturnSellBillUsingQueryString($queryString) {
        $sql = 'SELECT returnsellbill.* , user.employeename FROM returnsellbill
		 LEFT JOIN user
		 	 ON returnsellbill.sellerid = user.userid
		 LEFT JOIN user user2
		 	 ON returnsellbill.userid = user2.userid
		' . $queryString . '
		AND returnsellbill.conditions=0
		';
        //print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function checkIfThereIsABill() {
        $sql = 'SELECT returnsellbill.*
		        FROM returnsellbill
			WHERE returnsellbill.conditions=0 limit 1';


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

    public function getSalesVal() {
        $sql = 'SELECT  returnsellbilldetail.*, product.productBuyPrice
			 FROM returnsellbill LEFT JOIN  returnsellbilldetail
			 ON returnsellbill.returnsellbillid =  returnsellbilldetail.returnsellbillid JOIN product
			 ON product.productId = returnsellbilldetail.returnsellbilldetailproductid
			 where  returnsellbill.conditions = 0';

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

    public function queryAllGeneral($queryString) {
        $sql = 'SELECT  returnsellbill.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
		FROM returnsellbill
                        join client c    on returnsellbill.returnsellbillclientid = c.clientid
                        left join user u      on returnsellbill.userid = u.userid
                        left join user s      on returnsellbill.sellerid = s.userid
                        ' . $queryString . ' AND returnsellbill.conditions = 0
                 AND returnsellbill.deletedsellid=0';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneral_f($queryString) {
        $sql = 'SELECT  returnsellbill.* , c.clientname as clientname,u.employeename as employeename,s.employeename as useremployeename
		FROM returnsellbill
                        join client c    on returnsellbill.returnsellbillclientid = c.clientid
                        left join user u      on returnsellbill.userid = u.userid
                        left join user s      on returnsellbill.sellerid = s.userid
                        ' . $queryString . ' AND returnsellbill.conditions = 0
                 '; //AND returnsellbill.deletedsellid=0
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithReturnSellbillsSells($queryString) {
        $sql = 'SELECT returnsellbill.returnsellbillclientid , SUM(returnsellbill.returnsellbilltotalbill) as allGenTotalReturn , SUM(returnsellbill.returnsellbillaftertotalbill) as returnsellbillaftertotalbill ,
                COUNT(returnsellbill.returnsellbillclientid ) , client.clientname, client.clientdebt,client.clientphone,client.clientmobile,typeclientid FROM `returnsellbill`
                join client on client.clientid = returnsellbill.returnsellbillclientid
                ' . $queryString . ' GROUP BY returnsellbill.returnsellbillclientid
                ';
//        echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithReturnSellbillsSells2($clientid, $returnSellBillsQuery) {
        $sql = 'SELECT returnsellbill.returnsellbillclientid , SUM(returnsellbill.returnsellbilltotalbill) as allGenTotalReturn , SUM(returnsellbill.returnsellbilldiscount) as totaldiscount ,
                COUNT(returnsellbill.returnsellbillclientid ) , client.clientname as clientname ,
                SUM(returnsellbill.returnsellbilltotalpayed) as returnsellbilltotalpayed
                FROM `returnsellbill`
                join client on client.clientid = returnsellbill.returnsellbillclientid
                where  returnsellbill.returnsellbillclientid = ' . $clientid . '
                ' . $returnSellBillsQuery . ' GROUP BY returnsellbill.returnsellbillclientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function returnsellbillWithProducts($queryString) {
        $sql = 'SELECT sum(returnsellbill.returnsellQuantity) as sumQuantity , sum(returnsellbill.returnsellbilltotalbill) as sumTotalbill ,
                sum(returnsellbill.returnsellbilldiscount) as sumDiscount ,returnsellbill.returnsellbillclientid as returnsellbillclientid ,
                product.productid as productid , product.productName as productName
                FROM `returnsellbill`
                join returnsellbilldetail on returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
                join client on client.clientid = returnsellbill.returnsellbillclientid
                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 = returnsellbilldetail.returnsellbilldetailproductid
		WHERE  ' . $queryString . '  GROUP BY returnsellbilldetail.returnsellbilldetailproductid order by  client.clientid';


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

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

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

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

    public function queryAllGeneral_1($queryString, $sellerid) {
        $sellerSql = "";
        $sellerJoin = "";
        if ($sellerid != 0 && $sellerid != -1) {
            $sellerSql = " ,s.employeename as useremployeename ";
            $sellerJoin = " join user s    on sellbill.sellerid = s.userid ";
        }
        $sql = 'SELECT  returnsellbill.* , c.clientname as clientname,u.employeename as employeename ' . $sellerSql . '
		FROM returnsellbill
                        join client c    on returnsellbill.returnsellbillclientid = c.clientid
                        join user u      on returnsellbill.userid = u.userid
                        ' . $sellerJoin . '
                        ' . $queryString . ' AND returnsellbill.conditions = 0
                 AND returnsellbill.deletedsellid=0';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithClinetIdAndDate2($clientid, $date) {
        $sql = 'SELECT returnsellbill.returnsellbillid, returnsellbilldate, returnsellbillserial, returnsellbillaftertotalbill, returnsellQuantity,returnsellbilltotalpayed,returnsellbill.conditions
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid
			 where  ' . $date . '
			  returnsellbill.returnsellbillclientid = ' . $clientid . '
			 AND returnsellbill.conditions = 0
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /////eman/////////////

    public function queryWithClinetIdAndDateExt($clientid, $date) {
        $sql = 'SELECT returnsellbill.returnsellbillid, returnsellbilldate, returnsellbillserial, returnsellbillaftertotalbill, returnsellQuantity, returnsellbill.conditions,returnsellbilltotalpayed
			 FROM returnsellbill LEFT JOIN client
			 ON returnsellbill.returnsellbillclientid = client.clientid
			 where  ' . $date . '
			  returnsellbill.returnsellbillclientid = ' . $clientid . '
			  order by returnsellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllforrateWithSellerName($sellerid, $startDate, $endDate, $priceType) {
        $sql = 'SELECT DISTINCT  returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype  FROM `returnsellbill`
            LEFT JOIN user ON returnsellbill.sellerid = user.userid
            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
            JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
            WHERE returnsellbill.`sellerid` =' . $sellerid . '  and returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and returnsellbilldetail.pricetype in( ' . $priceType . ') and returnsellbill.conditions =0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllforrateallWithSellerName($startDate, $endDate, $priceType) {
        $sql = 'SELECT DISTINCT returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype FROM `returnsellbill`
            LEFT JOIN user ON returnsellbill.sellerid = user.userid
            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
            JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
            WHERE returnsellbill.returnsellbilldate >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '" and returnsellbilldetail.pricetype in( ' . $priceType . ') and returnsellbill.conditions =0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    //emaaaaaaaaaan
    public function queryAllforrateallWithoutSeller() {
        $sql = 'SELECT DISTINCT returnsellbill.*, user.username, client.clientname, returnsellbilldetail.pricetype ,user.awardrate
            FROM `returnsellbill`
            LEFT JOIN user ON returnsellbill.sellerid = user.userid
            LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
            JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
            WHERE MONTH(returnsellbill.returnsellbilldate) = Month(CURRENT_DATE()) and returnsellbill.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getBillsCountNotDel($queryString = '') {
        $sql = 'SELECT count(returnsellbillid) as returnsellbillid
		FROM returnsellbill where conditions = 0 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllOrderedLimitedSimple($start, $end) {
        $sql = 'SELECT *
		FROM returnsellbill
                where conditions = 0
                order by returnsellbillid desc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllOrderedAscLimitedSimple($start, $end, $queryString) {
        $sql = 'SELECT *
		FROM returnsellbill
                where conditions = 0
                ' . $queryString . '
                order by returnsellbillid asc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function updateLastReview($lastReviewType, $lastReviewUser, $lastReviewDateTime, $billId) {
        $sql = 'UPDATE returnsellbill SET  lastReviewType = ' . $lastReviewType . ',lastReviewUser = ' . $lastReviewUser . ',lastReviewDateTime= "' . $lastReviewDateTime . '" WHERE returnsellbillid = ' . $billId;
        $sqlQuery = new SqlQuery($sql);

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

    public function updateLastReviewAutoHandle($lastReviewUser, $lastReviewDateTime, $billId) {
        $sql = 'UPDATE returnsellbill SET lastReviewType= CASE WHEN lastReviewType=0 THEN 1 WHEN lastReviewType=1 THEN 3 WHEN lastReviewType=3 THEN 4 WHEN lastReviewType=4 THEN 3 ELSE 1 END,lastReviewUser = ' . $lastReviewUser . ',lastReviewDateTime= "' . $lastReviewDateTime . '" WHERE returnsellbillid = ' . $billId;
        $sqlQuery = new SqlQuery($sql);

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

    public function ClientsWithReturnSellbillsSells($queryStringTy, $queryString1R) {
        $sql = 'SELECT client.*,clientarea.name as areaName,government.governmentname as governmentname , returnsellbill.returnsellbillclientid , SUM(returnsellbill.returnsellbilltotalbill) as allGenTotalReturn , SUM(returnsellbill.returnsellbillaftertotalbill) as returnsellbillaftertotalbill ,
                COUNT(returnsellbill.returnsellbillclientid ) , client.clientname as clientname FROM `client`
                LEFT JOIN clientarea on client.clientareaid  = clientarea.id
                LEFT JOIN goverarea on clientarea.id = goverarea.clientareaid
                LEFT JOIN government on goverarea.governmentid = government.governmetid
                join returnsellbill on client.clientid = returnsellbill.returnsellbillclientid
                ' . $queryStringTy . ' ' . $queryString1R . '  ';
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getBillsNumbersList($billNo) {
        $sql = 'select returnsellbillid FROM returnsellbill WHERE conditions = 0 and returnsellbillid like  "%' . $billNo . '%"';
        $sqlQuery = new SqlQuery($sql);

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

    public function orderClientsWithReturnSellbillsSellsWithCat($queryString) {
        $sql = 'SELECT returnsellbill.returnsellbillclientid , SUM(returnsellbilldetail.returnsellbilldetailprice*returnsellbilldetail.returnsellbilldetailquantity) as allGenTotalReturn , SUM(returnsellbilldetail.returnsellbilldetailtotalprice) as returnsellbillaftertotalbill ,
                SUM(IF(returnsellbill.returnsellbilldiscounttype = 1, returnsellbill.returnsellbilldiscount * returnsellbilldetail.returnsellbilldetailtotalprice / returnsellbill.returnsellbilltotalbill ,(returnsellbill.returnsellbilldiscount * returnsellbilldetail.returnsellbilldetailprice)/ 100)) as returnsellbilldiscount
                ,COUNT(returnsellbill.returnsellbillclientid ) , client.clientname, client.clientdebt,client.clientphone,client.clientmobile,typeclientid FROM `returnsellbill`
                join client on client.clientid = returnsellbill.returnsellbillclientid
                join returnsellbilldetail on returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
                join productcat on returnsellbilldetail.returnsellbilldetailcatid = productcat.productCatId
                ' . $queryString . ' GROUP BY returnsellbill.returnsellbillclientid
                ';
        //    echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>