<?php

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

    public function getlastbill($clintid) {
        $sql = 'SELECT * FROM `sellbill`
                WHERE sellbillclientid = ' . $clintid . ' order by sellbill.sellbillid desc ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function fixDiscountRestaurantFatma($billdate) {
        $sql = 'update sellbill set sellbillaftertotalbill = sellbilltotalbill where date(sellbilldate) = "' . $billdate . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

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


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

    public function getSellBillCount() {
        $sql = 'SELECT count(sellbillid) FROM sellbill';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllSellBill($sellId) {
        $sql = 'SELECT sellbill.*
			    FROM sellbill
				where sellbillid = "' . $sellId . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /*

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

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

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


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

     */

    public function insertupdate($sellbill) {
        $sql = 'INSERT INTO sellbill (sellbilldate, sellbillserial, sellbillclientid, sellbilltotaldeptbefor, sellbilltotaldeptafter, sellbilldiscount, sellbilldiscounttype, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, sellbillfinalbill, sellbillstoreid, sellbillsysdate,comment, userid, conditions, sellbillclientname, sellbillsaveid, sellbildirectpayment, billnameid, sellerid, tempclientName, sellQuantity,deletedsellid,controlname,tax, carnumber, cartype, carchase, carmotor,costcenterid,webApiId,visaAccountId,cashPayed,visaPayed,isBankAccountTransfer,obygyVisitId,extraDiscountPer,extraDiscountVal,taxOfDiscountPer,taxOfDiscountVal,taxBillNumber,collectionType,collectionDate,obgyBillType,billoperationid,datestarting,pricetype,currencyId,knownwayId,travelDriverId,travelBusId,travelTripId,travelTripDayId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);

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

        $sqlQuery->setNumber($sellbill->deletedsellid);
        $sqlQuery->set($sellbill->controlname);
        $sqlQuery->set($sellbill->tax);
        $sqlQuery->set($sellbill->carnumber);
        $sqlQuery->set($sellbill->cartype);
        $sqlQuery->set($sellbill->carchase);
        $sqlQuery->set($sellbill->carmotor);
        $sqlQuery->setNumber($sellbill->costcenterid);
        $sqlQuery->setNumber((int) $sellbill->webApiId);
        $sqlQuery->setNumber((int) $sellbill->visaAccountId);
        $sqlQuery->setNumber((float) $sellbill->cashPayed);
        $sqlQuery->setNumber((float) $sellbill->visaPayed);
        $sqlQuery->setNumber((int) $sellbill->isBankAccountTransfer);
        $sqlQuery->setNumber((int) $sellbill->obygyVisitId);
        $sqlQuery->setNumber((float) $sellbill->extraDiscountPer);
        $sqlQuery->setNumber((float) $sellbill->extraDiscountVal);
        $sqlQuery->setNumber((float) $sellbill->taxOfDiscountPer);
        $sqlQuery->setNumber((float) $sellbill->taxOfDiscountVal);
        $sqlQuery->setNumber((int) $sellbill->taxBillNumber);
        $sqlQuery->setNumber((int) $sellbill->collectionType);
        $sqlQuery->set($sellbill->collectionDate);
        $sqlQuery->setNumber((int) $sellbill->obgyBillType);
        $sqlQuery->setNumber((int) $sellbill->billoperationid);
        $sqlQuery->set($sellbill->datestarting);
        $sqlQuery->setNumber((int) $sellbill->pricetype);
        $sqlQuery->setNumber((int) $sellbill->currencyId);
        $sqlQuery->setNumber((int) $sellbill->knownwayId);
        $sqlQuery->setNumber((int) $sellbill->travelDriverId);
        $sqlQuery->setNumber((int) $sellbill->travelBusId);
        $sqlQuery->setNumber((int) $sellbill->travelTripId);
        $sqlQuery->setNumber((int) $sellbill->travelTripDayId);




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

    public function loadExt($sellbillid) {
        $sql = 'SELECT sellbill.sellbilltotalbill, sellbilldiscount, sellbilldiscounttype
		 FROM sellbill
		 WHERE sellbill.sellbillid = ' . $sellbillid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadExtWithClient($sellbillid) {
        $sql = 'SELECT sellbill.sellbilltotalbill, sellbilldiscount, sellbilldiscounttype, client.clientname
		 FROM sellbill
                 LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
		 WHERE sellbill.sellbillid = ' . $sellbillid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadSellBillById($sellbillid) {
        $sql = 'SELECT sellbill.*, client.clientname, client.taxnumber, store.storeName, user.employeename , user2.employeename  as  useremployeename
		 FROM sellbill LEFT JOIN client
		 ON sellbill.sellbillclientid = client.clientid JOIN store
		 ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
		 ON sellbill.sellerid = user.userid
		 LEFT JOIN user as user2
		  ON sellbill.userid = user2.userid
		 WHERE sellbill.sellbillid = ' . $sellbillid . ' ';
        // print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function loadSellBillByIdMany($sellbillid) {
        $sql = 'SELECT sellbill.*, client.clientname, store.storeName, user.employeename , user2.employeename  as  useremployeename
		 FROM sellbill LEFT JOIN client
		 ON sellbill.sellbillclientid = client.clientid JOIN store
		 ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
		 ON sellbill.sellerid = user.userid
		 LEFT JOIN user as user2
		  ON sellbill.userid = user2.userid
		 WHERE sellbill.sellbillid in ( ' . $sellbillid . ' ) '; //using in for Bulk print of sell bill
        // print_r('<br>'.$sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

    public function queryWithOrder() {
        $sql = 'SELECT sellbill.*, client.clientname, user.employeename , user2.employeename  as  useremployeename
			 FROM sellbill LEFT JOIN client
			 ON sellbill.sellbillclientid = client.clientid JOIN store
			 ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbill.sellerid = user.userid
			 LEFT JOIN user as user2
		     ON sellbill.userid = user2.userid
			 order by sellbillid desc';

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

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

                         LEFT JOIN sellbill as editedBill ON sellbill.sellbillid = editedBill.deletedsellid

			 ' . $queryString . '
			 ORDER BY  `sellbilldate`  desc ,sellbillid desc  ';


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

    public function queryWithOrderLimited($start, $end) {
        $sql = 'SELECT sellbill.*, client.clientname, user.employeename , user2.employeename  as  useremployeename
			 FROM sellbill LEFT JOIN client
			 ON sellbill.sellbillclientid = client.clientid JOIN store
			 ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbill.sellerid = user.userid
			 LEFT JOIN user as user2
		     ON sellbill.userid = user2.userid

			 order by sellbillid desc
			 limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

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

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

    public function queryWithSellbillId($sellbillid) {
        $sql = 'SELECT sellbill.*, client.clientname, user.employeename
			 FROM sellbill LEFT JOIN client
			 ON sellbill.sellbillclientid = client.clientid JOIN store
			 ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbill.sellerid = user.userid
			 where  sellbill.sellbillid =' . $sellbillid . '
			  order by sellbillid desc';

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

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

    public function queryWithClientIdAndDate($clientId, $startDate, $endDate) {
        $sql = 'SELECT sellbill.sellbillid, sellbill.sellbilldate, sellbill.sellbillserial, sellbill.sellbillaftertotalbill, sellbill.sellQuantity, sellbill.sellbilltotalpayed
			 FROM sellbill
			 JOIN client
			 ON sellbill.sellbillclientid = client.clientid
			 where  sellbill.sellbilldate  >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
			 AND  sellbill.sellbillclientid = ' . $clientId . '
			 AND  sellbill.conditions = 0
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

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

    public function querybyqueryString($queryString) {
        $sql = 'SELECT sellbill.*, client.clientname, user.employeename,u.employeename as username
			 FROM sellbill LEFT JOIN client
			 ON sellbill.sellbillclientid = client.clientid JOIN store
			 ON sellbill.sellbillstoreid = store.storeId LEFT JOIN user
			 ON sellbill.sellerid = user.userid
                         LEFT JOIN user u
			 ON u.userid = sellbill.userid
			 ' . $queryString . '
                         AND sellbill.conditions=0
			  order by  sellbilldate desc,  sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

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

    public function queryWithDateAndConditionsExt($startDate, $endDate) {
        $sql = 'SELECT SUM(sellbill.sellbillaftertotalbill)
			 FROM sellbill
			 where sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
			 and sellbill.conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

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

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

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

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

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

    public function GetSellBillUsingQueryString($queryString) {
        $sql = 'SELECT * FROM sellbill
		' . $queryString . '
		AND sellbill.conditions=0
		';
        $sqlQuery = new SqlQuery($sql);

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

    public function loadlast($queryString = '') {
        $sql = 'SELECT * FROM  `sellbill`
                LEFT JOIN user user2 ON sellbill.userid = user2.userid
                where 1
                ' . $queryString . '
                ORDER BY  `sellbillid` DESC LIMIT 1';
        $sqlQuery = new SqlQuery($sql);

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

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

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

    public function getsellbilldetail($sellId) {
        $sql = 'SELECT parcode
             FROM sellbilldetail
             where sellbillid = "' . $sellId . '"';

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

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

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

    public function queryAllforreceipt($queryString) {
        $sql = 'SELECT sellbillid,sellbillserial,sellbilldate,sellbilltotalpayed,sellbillfinalbill,sellbillclientid FROM sellbill
		' . $queryString . '
		AND sellbill.conditions=0
                AND sellbill.deletedsellid=0
                order by sellbilldate asc
		';

        //print_r("<br/>".$sql);
        $sqlQuery = new SqlQuery($sql);

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

    public function updatePayedAndRemain($sellbill) {
        $sql = 'UPDATE sellbill SET  sellbilltotalpayed = ' . $sellbill->sellbilltotalpayed . ' , sellbillfinalbill = ' . $sellbill->sellbillfinalbill . ' WHERE sellbillid = ?';
        $sqlQuery = new SqlQuery($sql);

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

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

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


    public function queryAllGeneral_2($sellbill) {


        $sql = 'SELECT *
    FROM sellbill

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

    public function queryAllcount() {
        $sql = 'SELECT count(*) FROM sellbill';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderClientsWithSellbillsSells($queryString) {
        $sql = 'SELECT sellbill.sellbillclientid , SUM(sellbill.sellbilltotalbill) as allGenTotal ,  SUM(sellbill.sellbillaftertotalbill) as sellbillaftertotalbill ,
                COUNT(sellbill.sellbillclientid ) , client.clientname as clientname , client.clientdebt as clientdebt FROM `sellbill`
                join client on client.clientid = sellbill.sellbillclientid

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

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

    public function sellbillWithProducts($queryString) {
        $sql = 'SELECT sum(sellbill.sellQuantity) as sumQuantity , sum(sellbill.sellbilltotalbill) as sumTotalbill , sum(sellbill.sellbilldiscount) as sumDiscount ,
                sellbill.sellbillclientid as sellbillclientid ,product.productid as productid , product.productName as productName
                FROM `sellbill`
                join sellbilldetail on sellbilldetail.sellbillid = sellbill.sellbillid
                join client on client.clientid = sellbill.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 = sellbilldetail.sellbilldetailproductid
		WHERE  ' . $queryString . ' GROUP BY sellbilldetail.sellbilldetailproductid
                order by  client.clientid';


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

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

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

    public function isSerialUnique($value, $oldid) {
        $sql = 'SELECT sellbillid FROM sellbill WHERE sellbillserial = "' . $value . '" and sellbillid != ' . $oldid . ' and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($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  sellbill.* , c.clientname as clientname,u.employeename as employeename ' . $sellerSql . '
		FROM sellbill
                        join client c    on sellbill.sellbillclientid = c.clientid
                        join user u      on sellbill.userid = u.userid
                        ' . $sellerJoin . '
                        ' . $queryString . ' AND sellbill.conditions = 0';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithClientIdAndDate2($clientId, $date) {
        $sql = 'SELECT sellbill.sellbillid, sellbill.sellbilldate, sellbill.sellbillserial, sellbill.sellbillaftertotalbill, sellbill.sellQuantity, sellbill.sellbilltotalpayed
			 FROM sellbill
			 JOIN client
			 ON sellbill.sellbillclientid = client.clientid
			 where  ' . $date . '
			   sellbill.sellbillclientid = ' . $clientId . '
			 AND  sellbill.conditions = 0
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    //emaaaaaaaaaaaaan
    public function queryWithClientIdAndDateExt($clientId, $date) {
        $sql = 'SELECT sellbill.sellbillid, sellbill.sellbilldate, sellbill.sellbillserial, sellbill.sellbillaftertotalbill, sellbill.sellQuantity, sellbill.sellbilltotalpayed,sellbill.conditions
			 FROM sellbill
			 JOIN client
			 ON sellbill.sellbillclientid = client.clientid
			 where  ' . $date . '
			   sellbill.sellbillclientid = ' . $clientId . '
			 order by sellbillid desc';
        $sqlQuery = new SqlQuery($sql);
//        print($sql);
        return $this->getList($sqlQuery);
    }

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

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

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

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

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

    public function queryAllOrderedLimitedSimple($start, $end) {
        $sql = 'SELECT *
		FROM sellbill
                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 sellbill
                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 executeSQLQuery($sql) {
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

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

    public function uerrazmy($clientID, $queryStringsellbill) {
        $sql = 'SELECT sellbill.*
		FROM sellbill where sellbillclientid =' . $clientID . ' ' . $queryStringsellbill;
//        echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

    public function updateLastReviewAutoHandle($lastReviewUser, $lastReviewDateTime, $billId) {
        $sql = 'UPDATE sellbill 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 sellbillid = ' . $billId;
        $sqlQuery = new SqlQuery($sql);

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

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

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

    public function updateshippingDate($shippingDate, $billId) {
        $sql = 'UPDATE sellbill SET  shippingDate = "' . $shippingDate . '" WHERE sellbillid = ' . $billId;
        $sqlQuery = new SqlQuery($sql);

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

    public function getNextTaxBillNumber($DBName) {
        $sql = 'SELECT taxBillNumber
                FROM  sellbill
                WHERE taxBillNumber > 0
                order by sellbillid desc limit 1';

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

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

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

    public function loadByObygyVisitId($id) {
        $sql = 'SELECT * FROM sellbill WHERE obygyVisitId = ? and conditions = 0 ';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function queryAllByKnownWay($wayid) {
        $sql = 'SELECT sellbill.*
			    FROM sellbill
				where knownwayId = "' . $wayid . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryClientCountByKnownWay($wayid) {
        $sql = 'SELECT count(DISTINCT sellbillclientid)
			    FROM sellbill
				where knownwayId = "' . $wayid . '"
                and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryClientByKnownWay($wayid) {
        $sql = 'SELECT DISTINCT sellbillclientid
			    FROM sellbill
				where knownwayId = "' . $wayid . '"
                and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>