<?php

/**
 * Class that operate on table 'sellbillandrutern'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2013-04-29 13:54
 */
class SellbillandruternMySqlExtDAO extends SellbillandruternMySqlDAO {

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


        $sqlQuery = new SqlQuery($sql);

        //	print_r($sqlQuery);

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

    public function loadlastSellBillByclient($clientid) {
        $sql = 'SELECT sellbillclientid,  `sellbillid` ,  `sellbilldate`
				FROM  `sellbillandrutern`
				WHERE sellbillclientid = ' . $clientid . ' ORDER BY  `sellbilldate`  desc ,sellbillid desc  LIMIT 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithClientId2($clientid) {
        $sql = 'SELECT
                        SUM( sellbillandrutern.sellbillaftertotalbill ) AS allGenTotalSellAndReturn
                        FROM sellbillandrutern  JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid
			 where  sellbillandrutern.sellbillclientid =' . $clientid . '
                         group by sellbillandrutern.sellbillclientid
                         order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sqlQuery);
        return $this->getRow($sqlQuery);
    }

    /*
      public function insertupdate($sellbillandrutern){
      $sql = 'INSERT INTO sellbillandrutern (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, sellbillprice, returnsellbillprice, tempclientName,sellQuantity, returnsellQuantity,deletedsellid,controlname ,tax) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ,?)';
      $sqlQuery = new SqlQuery($sql);



      $sqlQuery->set($sellbillandrutern->sellbilldate);
      $sqlQuery->set($sellbillandrutern->sellbillserial);
      $sqlQuery->setNumber($sellbillandrutern->sellbillclientid);
      $sqlQuery->set($sellbillandrutern->sellbilltotaldeptbefor);
      $sqlQuery->set($sellbillandrutern->sellbilltotaldeptafter);
      $sqlQuery->set($sellbillandrutern->sellbilldiscount);
      $sqlQuery->setNumber($sellbillandrutern->sellbilldiscounttype);
      $sqlQuery->set($sellbillandrutern->sellbilltotalbill);
      $sqlQuery->set($sellbillandrutern->sellbillaftertotalbill);
      $sqlQuery->set($sellbillandrutern->sellbilltotalpayed);
      $sqlQuery->set($sellbillandrutern->sellbillfinalbill);
      $sqlQuery->setNumber($sellbillandrutern->sellbillstoreid);
      $sqlQuery->set($sellbillandrutern->sellbillsysdate);
      $sqlQuery->setNumber($sellbillandrutern->userid);
      $sqlQuery->setNumber($sellbillandrutern->conditions);
      $sqlQuery->set($sellbillandrutern->sellbillclientname);
      $sqlQuery->setNumber($sellbillandrutern->sellbillsaveid);
      $sqlQuery->setNumber($sellbillandrutern->sellbildirectpayment);
      $sqlQuery->setNumber($sellbillandrutern->billnameid);
      $sqlQuery->setNumber($sellbillandrutern->sellerid);
      $sqlQuery->set($sellbillandrutern->sellbillprice);
      $sqlQuery->set($sellbillandrutern->returnsellbillprice);
      $sqlQuery->set($sellbillandrutern->tempclientName);
      $sqlQuery->setNumber($sellbillandrutern->sellQuantity);
      $sqlQuery->setNumber($sellbillandrutern->returnsellQuantity);

      $sqlQuery->setNumber($sellbillandrutern->deletedsellid);
      $sqlQuery->set($sellbillandrutern->controlname);
      $sqlQuery->set($sellbillandrutern->tax);





      $id = $this->executeInsert($sqlQuery);
      $sellbillandrutern->sellbillid = $id;
      return $id;
      }
     */

    public function insertupdate($sellbillandrutern) {
        $sql = 'INSERT INTO sellbillandrutern (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate,comment, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, sellbillprice, returnsellbillprice, tempclientName,sellQuantity, returnsellQuantity,deletedsellid,controlname ,tax, carnumber, cartype, carchase, carmotor,costcenterid,dailyentryid,visaAccountId,cashPayed,visaPayed,isBankAccountTransfer,extraDiscountPer,extraDiscountVal,taxOfDiscountPer,taxOfDiscountVal,obgyBillType,billoperationid,pricetype,currencyId,sellbillno) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);



        $sqlQuery->set($sellbillandrutern->sellbilldate);
        $sqlQuery->set($sellbillandrutern->sellbillserial);
        $sqlQuery->setNumber($sellbillandrutern->sellbillclientid);
        $sqlQuery->set($sellbillandrutern->sellbilltotaldeptbefor);
        $sqlQuery->set($sellbillandrutern->sellbilltotaldeptafter);
        $sqlQuery->set($sellbillandrutern->sellbilldiscount);
        $sqlQuery->setNumber($sellbillandrutern->sellbilldiscounttype);
        $sqlQuery->set($sellbillandrutern->sellbilltotalbill);
        $sqlQuery->set($sellbillandrutern->sellbillaftertotalbill);
        $sqlQuery->set($sellbillandrutern->sellbilltotalpayed);
        $sqlQuery->set($sellbillandrutern->sellbillfinalbill);
        $sqlQuery->setNumber($sellbillandrutern->sellbillstoreid);
        $sqlQuery->set($sellbillandrutern->sellbillsysdate);
        $sqlQuery->set($sellbillandrutern->comment);

        $sqlQuery->setNumber($sellbillandrutern->userid);
        $sqlQuery->setNumber($sellbillandrutern->conditions);
        $sqlQuery->set($sellbillandrutern->sellbillclientname);
        $sqlQuery->setNumber($sellbillandrutern->sellbillsaveid);
        $sqlQuery->setNumber($sellbillandrutern->sellbildirectpayment);
        $sqlQuery->setNumber($sellbillandrutern->billnameid);
        $sqlQuery->setNumber($sellbillandrutern->sellerid);
        $sqlQuery->set($sellbillandrutern->sellbillprice);
        $sqlQuery->set($sellbillandrutern->returnsellbillprice);
        $sqlQuery->set($sellbillandrutern->tempclientName);
        $sqlQuery->setNumber($sellbillandrutern->sellQuantity);
        $sqlQuery->setNumber($sellbillandrutern->returnsellQuantity);

        $sqlQuery->setNumber($sellbillandrutern->deletedsellid);
        $sqlQuery->set($sellbillandrutern->controlname);
        $sqlQuery->set($sellbillandrutern->tax);
        $sqlQuery->set($sellbillandrutern->carnumber);
        $sqlQuery->set($sellbillandrutern->cartype);
        $sqlQuery->set($sellbillandrutern->carchase);
        $sqlQuery->set($sellbillandrutern->carmotor);

        $sqlQuery->setNumber($sellbillandrutern->costcenterid);
        $sqlQuery->set($sellbillandrutern->dailyentryid);
        $sqlQuery->setNumber((int) $sellbillandrutern->visaAccountId);
        $sqlQuery->setNumber((float) $sellbillandrutern->cashPayed);
        $sqlQuery->setNumber((float) $sellbillandrutern->visaPayed);
        $sqlQuery->setNumber((int) $sellbillandrutern->isBankAccountTransfer);
        $sqlQuery->setNumber((float) $sellbillandrutern->extraDiscountPer);
        $sqlQuery->setNumber((float) $sellbillandrutern->extraDiscountVal);
        $sqlQuery->setNumber((float) $sellbillandrutern->taxOfDiscountPer);
        $sqlQuery->setNumber((float) $sellbillandrutern->taxOfDiscountVal);
        $sqlQuery->setNumber((int) $sellbillandrutern->obgyBillType);
        $sqlQuery->setNumber((int) $sellbillandrutern->billoperationid);
        $sqlQuery->setNumber((int) $sellbillandrutern->pricetype);
        $sqlQuery->setNumber((int) $sellbillandrutern->currencyId);
        $sqlQuery->set($sellbillandrutern->sellbillno);


        $id = $this->executeInsert($sqlQuery);
        $sellbillandrutern->sellbillid = $id;
        return $id;
    }

    public function loadSellbillandruternById($sellbillid) {
        $sql = 'SELECT sellbillandrutern.*, client.clientname, store.storeName, user.employeename , user2.employeename  as  useremployeename

		 FROM sellbillandrutern LEFT JOIN client
		 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
		 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
		 ON sellbillandrutern.sellerid = user.userid
		  LEFT JOIN user as user2
		  ON sellbillandrutern.userid = user2.userid
		 WHERE sellbillandrutern.sellbillid = ' . $sellbillid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithOrder() {
        $sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function querySellBillsOnlyWithClientId($clientid) {
        $sql = 'SELECT  DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid
			 JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId
			 LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid

			 where  sellbillandrutern.sellbillclientid =' . $clientid . '
			 AND  sellandruternbilldetail.selltype = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryReturnSellBillsOnlyWithClientId($clientid) {
        $sql = 'SELECT  DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid
			 JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId
			 LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid

			 where  sellbillandrutern.sellbillclientid =' . $clientid . '
			 AND  sellandruternbilldetail.selltype = 1
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

    public function querySellBillsOnlyWithSerail($serial) {
        $sql = 'SELECT  DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern

			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbillserial ="' . $serial . '"
			 AND  sellandruternbilldetail.selltype = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryReturnSellBillsOnlyWithSerail($serial) {
        $sql = 'SELECT  DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern

			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbillserial ="' . $serial . '"
			 AND  sellandruternbilldetail.selltype = 1
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

    public function querySellBillsOnlyWithSellbillid($sellbillid) {
        $sql = 'SELECT  DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbillid =' . $sellbillid . '
			 AND  sellandruternbilldetail.selltype = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryReturnSellBillsOnlyWithSellbillid($sellbillid) {
        $sql = 'SELECT  DISTINCT (sellbillandrutern.`sellbillid`), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbillid =' . $sellbillid . '
			 AND  sellandruternbilldetail.selltype = 1
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function querySellBillsOnlyWithClientIdAndDate($clientId, $startDate, $endDate) {
        $sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`),  sellbilldate, sellbillserial, sellQuantity, sellbillaftertotalbill,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid
			 where  sellbillandrutern.sellbilldate  >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 AND  sellandruternbilldetail.selltype = 0
			 AND sellbillandrutern.sellbillclientid = ' . $clientId . '
			 AND sellbillandrutern.conditions = 0
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function querySellBillsOnlyWithDate($startDate, $endDate) {
        $sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`),  sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbilldate  >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 AND  sellandruternbilldetail.selltype = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

//start/////////////////////////////////
/////////////////////////////////
////////////hazem updaate//////////////////
/////////////////////////////
////////////////////////////--------
    public function queryReturnSellBillsOnlyWithDate($startDate, $endDate) {
        $sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`),  sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbilldate  >=  "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 AND  sellandruternbilldetail.selltype = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

//end/////////////////////////////////
/////////////////////////////////
////////////hazem updaate//////////////////
/////////////////////////////
////////////////////////////--------
    public function queryReturnSellBillsOnlyWithClientIdAndDate($clientid, $startDate, $endDate) {
        $sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename ,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbilldate  >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 AND sellbillandrutern.sellbillclientid = ' . $clientid . '
			 AND  sellandruternbilldetail.selltype = 1
			 AND sellbillandrutern.conditions = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDate($startDate, $endDate) {
        $sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbilldate  >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryallWithqueryString($queryString) {
        $sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename,editedBill.sellbillid as newSellbillidAfterEdit,delbyuser.username as delbyusername
			 FROM sellbillandrutern
			 LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
			 JOIN store ON sellbillandrutern.sellbillstoreid = store.storeId
                         JOIN save ON sellbillandrutern.sellbillsaveid = save.saveid
                         LEFT JOIN user ON sellbillandrutern.sellerid = user.userid
			 LEFT JOIN user as user2  ON sellbillandrutern.userid = user2.userid
                         LEFT JOIN user as delbyuser ON delbyuser.userid = sellbillandrutern.delbyuserid
                         LEFT JOIN sellbillandrutern as editedBill ON sellbillandrutern.sellbillid = editedBill.deletedsellid
			 ' . $queryString . '
			  order by sellbillid desc';

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

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

    public function queryWithDateAndConditions($startDate, $endDate) {
        $sql = 'SELECT sellbillandrutern.*, client.clientname, user.employeename
			 FROM sellbillandrutern LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  sellbillandrutern.sellbilldate
			 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 And  sellbillandrutern.conditions = 0
			  order by sellbillid desc';

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

    public function queryWithDateAndConditionsForSellBillOnly($startDate, $endDate) {
        $sql = 'SELECT SUM(sellbillandrutern.sellbillaftertotalbill)
		FROM sellbillandrutern
			 where  sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 AND sellbilltotalbill >= 0
			 And  sellbillandrutern.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryWithDateAndConditionsForReturnSellBillOnly($startDate, $endDate) {
        $sql = 'SELECT SUM(sellbillandrutern.sellbillaftertotalbill)
		FROM sellbillandrutern
			 where  sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
			 AND sellbilltotalbill < 0
			 And  sellbillandrutern.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print($sql.'<br>');
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryWithDateAndConditionsForReturnSellBillOnly2($startDate, $endDate) {
        $sql = 'SELECT sum(sellbillandrutern.sellbillprice- (sellbilltotalbill-sellbillaftertotalbill))
		FROM sellbillandrutern
			 where  sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"

			 And  sellbillandrutern.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print('<br>'.$sql.'<br>');
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryWithDateAndConditionsForReturnSellBillOnly22($startDate, $endDate) {
        $sql = 'SELECT *
		FROM sellbillandrutern
			 where  sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"

			 And  sellbillandrutern.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        //print('<br>'.$sql.'<br>');
        return $this->getList($sqlQuery);
    }

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


        $sqlQuery->set($sellbillandrutern->sellbillsysdate);
        $sqlQuery->setNumber($sellbillandrutern->userid);
        $sqlQuery->setNumber($sellbillandrutern->delbyuserid);
        $sqlQuery->setNumber($sellbillandrutern->conditions);

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

    public function loadExt($id) {
        $sql = 'SELECT sellbillprice, sellbilldiscount, sellbilldiscounttype
		FROM sellbillandrutern
		WHERE sellbillid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

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

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

    public function GetSellBillAndReturnUsingQueryString($queryString) {
        $sql = 'SELECT sellbillandrutern.* , user.employeename , client.clientname,u.employeename as username FROM sellbillandrutern
		 LEFT JOIN user
		 ON sellbillandrutern.sellerid = user.userid
                  LEFT JOIN client
		 ON sellbillandrutern.sellbillclientid = client.clientid
                 LEFT JOIN user u
			 ON u.userid = sellbillandrutern.userid
		' . $queryString . '
		AND sellbillandrutern.conditions=0
		';
        $sqlQuery = new SqlQuery($sql);

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

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


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

    public function getSalesVal() {
        $sql = 'SELECT  sellandruternbilldetail.*, product.productBuyPrice
			 FROM sellbillandrutern LEFT JOIN  sellandruternbilldetail
			 ON sellbillandrutern.sellbillid =  sellandruternbilldetail.sellbillid JOIN product
			 ON product.productId = sellandruternbilldetail.sellbilldetailproductid
			 where  sellbillandrutern.conditions = 0';

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

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

    public function orderClientsWithSellAndReturnSellbillsSells($queryString) {
        $sql = 'SELECT sellbillandrutern.sellbillclientid , SUM(sellbillandrutern.sellbilltotalbill) as allGenTotalSellAndReturn ,  SUM(sellbillandrutern.sellbillaftertotalbill) as sellbillaftertotalbill ,
                COUNT(sellbillandrutern.sellbillclientid ) , client.clientname as clientname FROM `sellbillandrutern`
                join client on client.clientid = sellbillandrutern.sellbillclientid

                ' . $queryString . ' GROUP BY sellbillandrutern.sellbillclientid
                ';
//        echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithSellAndReturnSellbillsSells2($clientid, $sellBillsQuery) {
        $sql = 'SELECT sellbillandrutern.sellbillclientid , SUM(sellbillandrutern.sellbilltotalbill) as allGenTotalSellAndReturn ,  SUM(sellbillandrutern.sellbilldiscount) as totaldiscount ,
                COUNT(sellbillandrutern.sellbillclientid ) , client.clientname as clientname ,
                SUM(sellbillandrutern.sellbilltotalpayed) as sellAndReturnTotalPayed
                FROM `sellbillandrutern`
                join client on client.clientid = sellbillandrutern.sellbillclientid
                where sellbillandrutern.sellbillclientid = ' . $clientid . '
                ' . $sellBillsQuery . ' GROUP BY sellbillandrutern.sellbillclientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function sellbillandreturnsellbillWithProducts($queryString) {
        $sql = 'SELECT 	sum(sellbillandrutern.sellQuantity) as sumQuantity , sum(sellbillandrutern.sellbillprice) as sumTotalbill ,
		sum(sellbillandrutern.returnsellQuantity) as sumQuantityReturend , sum(sellandruternbilldetail.sellbilldetailtotalprice) as sumTotalbillReturend ,
                sum(sellbillandrutern.sellbilldiscount) as sumDiscount ,
                sellbillandrutern.sellbillclientid as sellbillclientid ,product.productid as productid , product.productName as productName
                FROM `sellbillandrutern`
                join sellandruternbilldetail on sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                join client on client.clientid = sellbillandrutern.sellbillclientid
                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 = sellandruternbilldetail.sellbilldetailproductid
		WHERE  ' . $queryString . '  GROUP BY sellandruternbilldetail.sellbilldetailproductid order by  client.clientid
                ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientPayed($queryString1, $queryString2) {
        $sql = 'SELECT sellbillandrutern.sellbillclientid,sellbillandrutern.sellbilltotalpayed,sellbillandrutern.sellbillaftertotalbill
                FROM `sellbillandrutern`
                ' . $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   = "sellbillandrutern" ';

        $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  sellbillandrutern.* , c.clientname as clientname,u.employeename as employeename ' . $sellerSql . '
		FROM sellbillandrutern
                        join client c    on sellbillandrutern.sellbillclientid = c.clientid
                        join user u      on sellbillandrutern.userid = u.userid
                        ' . $sellerJoin . '
                        ' . $queryString . ' AND sellbillandrutern.conditions = 0';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGenerales($sellbillandrutern) {
        $sql = 'SELECT * FROM sellbillandrutern' . $sellbillandrutern;
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function querySellBillsOnlyWithClientIdAndDate2($clientId, $date) {
        $sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`),  sellbilldate, sellbillserial, sellQuantity, sellbillaftertotalbill,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid
			 where  ' . $date . '
			   sellandruternbilldetail.selltype = 0
			 AND sellbillandrutern.sellbillclientid = ' . $clientId . '
			 AND sellbillandrutern.conditions = 0
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    //emaaaaaaaaaaan
    public function querySellBillsOnlyWithClientIdAndDateExt($clientId, $date) {
        $sql = 'SELECT DISTINCT (sellbillandrutern.`sellbillid`),  sellbilldate, sellbillserial, sellQuantity, sellbillaftertotalbill,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid
			 where  ' . $date . '
			   sellandruternbilldetail.selltype = 0
			 AND sellbillandrutern.sellbillclientid = ' . $clientId . '
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryReturnSellBillsOnlyWithClientIdAndDate2($clientid, $date) {
        $sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename ,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  ' . $date . '
			  sellbillandrutern.sellbillclientid = ' . $clientid . '
			 AND  sellandruternbilldetail.selltype = 1
			 AND sellbillandrutern.conditions = 0
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

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

    public function queryReturnSellBillsOnlyWithClientIdAndDateExt($clientid, $date) {
        $sql = 'SELECT DISTINCT(sellbillandrutern.sellbillid), sellbillandrutern.*, client.clientname, user.employeename ,sellbillprice,returnsellbillprice,sellbilldiscounttype,sellbilldiscount,sellbillandrutern.conditions
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail
			 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client
			 ON sellbillandrutern.sellbillclientid = client.clientid JOIN store
			 ON sellbillandrutern.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbillandrutern.sellerid = user.userid
			 where  ' . $date . '
			  sellbillandrutern.sellbillclientid = ' . $clientid . '
			 AND  sellandruternbilldetail.selltype = 1
			  order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

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

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

    //emaaaaaaaaaaan
    public function queryAllforrateallWithoutSeller() {
        $sql = 'SELECT DISTINCT sellbillandrutern.*, user.username, client.clientname, sellandruternbilldetail.pricetype ,user.awardrate
            FROM `sellbillandrutern`
            LEFT JOIN user ON sellbillandrutern.`sellerid` = user.userid
            LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
            JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
            WHERE MONTH(sellbillandrutern.sellbilldate) = Month(CURRENT_DATE()) and sellbillandrutern.conditions =0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

    public function queryAllOrderedAscLimitedSimple($start, $end, $queryString) {
        $sql = 'SELECT *
		FROM sellbillandrutern
                where conditions = 0
                ' . $queryString . '
                order by sellbillid 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 uerrazmy($clientID, $queryStringsellbillandrutern) {
        $sql = 'SELECT *
		FROM sellbillandrutern where sellbillclientid =' . $clientID . '' . $queryStringsellbillandrutern;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function ClientsWithSellAndReturnSellbillsSells($queryStringTy, $queryString1SR) {
        $sql = 'SELECT client.*,clientarea.name as areaName,government.governmentname as governmentname , sellbillandrutern.sellbillclientid , SUM(sellbillandrutern.sellbilltotalbill) as allGenTotalSellAndReturn ,  SUM(sellbillandrutern.sellbillaftertotalbill) as sellbillaftertotalbill ,
                COUNT(sellbillandrutern.sellbillclientid ) , 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 sellbillandrutern on client.clientid = sellbillandrutern.sellbillclientid
                ' . $queryStringTy . ' ' . $queryString1SR . ' ';
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getClientSellStatistics($clientId, $queryString = '') {
        $sql = 'SELECT sellbilldetailproductid,sizeid,colorid,count(sellandruternbilldetail.sellbillid) as sellbillid
			 FROM sellbillandrutern
			 JOIN sellandruternbilldetail ON (sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid and sellbillandrutern.conditions = 0 AND sellandruternbilldetail.selltype = 0 and sellbillandrutern.sellbillclientid =' . $clientId . ')
			 where 1
			 ' . $queryString . '
			 group by sellbilldetailproductid,sizeid,colorid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>