<?php

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

    public function loadlastpriceforclient($sellbillid) {
        $sql = 'SELECT * FROM sellandruternbilldetail WHERE sellbillid = ' . $sellbillid . '  order by sellbilldetailid desc limit 1 ';
        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithSellBillIdAndSellType($sellbillid, $selltype) {
        $sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName
                ,size.name as sizeName,color.name as colorName,productserailid,productserial.don,productunit.productnumber
		 FROM sellandruternbilldetail JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 left JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid

		 left JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		  left join store
		  on store.storeid=sellandruternbilldetail.storeid
                left join sizecolor as size on size.id = sellandruternbilldetail.sizeid
                left join sizecolor as color on color.id = sellandruternbilldetail.colorid

                left join productserial on (productserial.serialnumber=sellandruternbilldetail.parcode and productserial.productid=sellandruternbilldetail.sellbilldetailproductid and sellandruternbilldetail.sizeid=productserial.sizeid and sellandruternbilldetail.colorid=productserial.colorid)

		 WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
		 and sellandruternbilldetail.selltype =' . $selltype . '
                 order by sellandruternbilldetail.sellbilldetailid asc ';



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

    public function queryWithSellBillIdAndSellTypeSimple($sellbillid, $selltype) {
        $sql = 'SELECT *
		FROM sellandruternbilldetail
		WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
		and sellandruternbilldetail.selltype =' . $selltype . '';



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

    public function queryWithSellBillIdwithoutSellType($sellbillid) {
        $sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName
		 FROM sellandruternbilldetail JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 left JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid

		 left JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		  left join store
		  on store.storeid=sellandruternbilldetail.storeid


		 WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
		';


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

    public function queryWithSellBillIdAndSellTypeforproft($sellbillid) {
        $sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName,productcat.discounttype,productcat.buydiscount,productunit.productnumber
		 FROM sellandruternbilldetail JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 left JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid

		 left JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		  left join store
		  on store.storeid=sellandruternbilldetail.storeid


		 WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
		';


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

    public function queryWithSellBillIdAndSellTypeandstor($sellbillid, $selltype, $storid) {
        $sql = 'SELECT sellandruternbilldetail.*, product.productName , store.storeName, unit.unitName ,productcat.productCatName
		 FROM sellandruternbilldetail JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		  left join store
		  on store.storeid=sellandruternbilldetail.storeid


		 WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
		 and sellandruternbilldetail.storeid = ' . $storid . '
		 and sellandruternbilldetail.selltype =' . $selltype . '';


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

    public function queryWithDateAndConditionsType($startDate, $endDate, $selltype) {
        $sql = 'SELECT sellandruternbilldetail.*
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsTypeExt($startDate, $endDate, $selltype) {
        $sql = 'SELECT sellandruternbilldetail.sellbilldetailproductid,
		sellandruternbilldetail.sellbilldetailquantity,
		sellandruternbilldetail.productunitid,
		sellandruternbilldetail.buyprice,
		sellandruternbilldetail.buydiscount,
		sellandruternbilldetail.buydiscounttype,
                sellandruternbilldetail.lastbuyprice,
                sellandruternbilldetail.meanbuyprice,
                sellandruternbilldetail.lastbuyprice_withDiscount,
                sellandruternbilldetail.meanbuyprice_withDiscount,
                sellandruternbilldetail.lastbuyprice_withTax,sellandruternbilldetail.meanbuyprice_withTax,
                sellandruternbilldetail.lastbuyprice_withDiscountAndTax,
                product.overAllAveragePrice as generalPrice
		 FROM sellandruternbilldetail
                 JOIN sellbillandrutern	 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsTypeAndProductId($startDate, $endDate, $selltype, $ProductId) {
        $sql = 'SELECT sellandruternbilldetail.*
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailproductid =' . $ProductId . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsTypeAndProductIdExt($startDate, $endDate, $selltype, $ProductId) {
        $sql = 'SELECT
		 sellandruternbilldetail.sellbilldetailquantity,
		 sellandruternbilldetail.sellbilldetailprice,
		 sellandruternbilldetail.sellbilldetailtotalprice,
		 sellandruternbilldetail.discountvalue,
		 sellandruternbilldetail.productunitid,
		 sellandruternbilldetail.buyprice,
                 sellandruternbilldetail.lastbuyprice,
                 sellandruternbilldetail.meanbuyprice,
                 sellandruternbilldetail.lastbuyprice_withDiscount,
                 sellandruternbilldetail.meanbuyprice_withDiscount,
                 sellandruternbilldetail.lastbuyprice_withTax,
                 sellandruternbilldetail.meanbuyprice_withTax,
                 sellandruternbilldetail.lastbuyprice_withDiscountAndTax,
		 sellandruternbilldetail.buydiscount,
		 sellandruternbilldetail.buydiscounttype,

		 sellbillandrutern.sellbillprice,
		 sellbillandrutern.sellbilldiscount,
		 sellbillandrutern.sellbilldiscounttype,
		 sellbillandrutern.returnsellbillprice,
		 sellbillandrutern.sellbilldate as note


		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailproductid in(' . $ProductId . ')';


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

    public function queryWithDateAndConditionsTypeAndProductIdExtNew($selltype, $queryString) {
        $sql = 'SELECT
		 sellandruternbilldetail.sellbilldetailquantity,
		 sellandruternbilldetail.sellbilldetailprice,
		 sellandruternbilldetail.sellbilldetailtotalprice,
		 sellandruternbilldetail.discountvalue,
		 sellandruternbilldetail.productunitid,
		 sellandruternbilldetail.buyprice,
		 sellandruternbilldetail.buydiscount,
		 sellandruternbilldetail.buydiscounttype,

		 sellbillandrutern.sellbillprice,
		 sellbillandrutern.sellbilldiscount,
		 sellbillandrutern.sellbilldiscounttype,
		 sellbillandrutern.returnsellbillprice


		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
                 ' . $queryString . '
		 ';


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

    public function queryWithDateAndConditionsTypeAndProductIdExtandstor($startDate, $endDate, $selltype, $ProductId, $storid) {
        $sql = 'SELECT
		 sellandruternbilldetail.sellbilldetailquantity,
		 sellandruternbilldetail.sellbilldetailprice,
		 sellandruternbilldetail.sellbilldetailtotalprice,
		 sellandruternbilldetail.discountvalue,
		 sellandruternbilldetail.productunitid,
		 sellandruternbilldetail.buyprice,
		 sellandruternbilldetail.buydiscount,
		 sellandruternbilldetail.buydiscounttype,

		 sellbillandrutern.sellbillprice,
		 sellbillandrutern.sellbilldiscount,
		 sellbillandrutern.sellbilldiscounttype

		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailproductid =' . $ProductId . '
		 and (sellandruternbilldetail.storeid=' . $storid . ' or sellbillandrutern.sellbillstoreid)
		 ';


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

    public function queryWithDateAndConditionsTypeAndProductIdExtandstorNew($selltype, $queryString) {
        $sql = 'SELECT
		 sellandruternbilldetail.sellbilldetailquantity,
		 sellandruternbilldetail.sellbilldetailprice,
		 sellandruternbilldetail.sellbilldetailtotalprice,
		 sellandruternbilldetail.discountvalue,
		 sellandruternbilldetail.productunitid,
		 sellandruternbilldetail.buyprice,
		 sellandruternbilldetail.buydiscount,
		 sellandruternbilldetail.buydiscounttype,

		 sellbillandrutern.sellbillprice,
		 sellbillandrutern.sellbilldiscount,
		 sellbillandrutern.sellbilldiscounttype

		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 ' . $queryString . '
		 ';


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

    public function queryQuantityWithProductAndSellType($productId, $selltype) {
        $sql = 'SELECT Sum(sellandruternbilldetail.sellbilldetailquantity) as sumQuantity
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellandruternbilldetail.sellbilldetailproductid =' . $productId . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryQuantityWithProductAndSellTypeAndDate($productId, $selltype, $startDate, $endDate) {
        $sql = 'SELECT Sum(sellandruternbilldetail.sellbilldetailquantity) as sumQuantity
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =' . $selltype . '
		 and sellandruternbilldetail.sellbilldetailproductid =' . $productId . '
		 and sellbillandrutern.sellbilldate >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid, $selltype) {
        $sql = 'SELECT sellandruternbilldetail.*, product.productName,
		 unit.unitName ,productcat.productCatName, sellbilldate
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		 WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
		 AND sellandruternbilldetail.productunitid = ' . $productunitid . '
		 AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
		 AND sellbillandrutern.conditions = 0
		 AND sellandruternbilldetail.selltype =' . $selltype . '
		 order by sellbilldetailid desc limit 1';

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

    public function queryPriceWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid, $selltype) {
        $QS = '';
        if ($productunitid > 0) {
            $QS = 'AND sellandruternbilldetail.productunitid = ' . $productunitid . '';
        }
        $sql = 'SELECT sellandruternbilldetail.sellbilldetailprice, sellbilldate,discountvalue,discounttype
		 FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		 WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
                     ' . $QS . '
		 AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
		 AND sellbillandrutern.conditions = 0
		 AND sellandruternbilldetail.selltype =' . $selltype . '
		 order by sellbilldetailid desc limit 1';

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

    public function getNoOfBillsWithProductIdAndClientId($ProductId, $ClientId, $selltype) {
        $sql = 'SELECT count( distinct sellbillandrutern.sellbillid)
		 FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		 WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
		 AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
		 AND sellbillandrutern.conditions = 0
		 AND sellandruternbilldetail.selltype =' . $selltype . ' limit 1';

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

    public function queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $productunitid, $selltype, $startDate, $endDate) {
        $sql = 'SELECT sellandruternbilldetail.sellbilldetailprice, sellandruternbilldetail.pricetype, sellandruternbilldetail.sellbilldetaildate, product.productName, unit.unitName ,productcat.productCatName, sellbilldate, sellbillandrutern.sellbillid
		,1 as billtype FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid
		 JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		 WHERE sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '
		 AND sellandruternbilldetail.productunitid = ' . $productunitid . '
		 AND sellbillandrutern.sellbillclientid = ' . $ClientId . '
		 AND sellbillandrutern.conditions = 0
		 AND sellandruternbilldetail.selltype =' . $selltype . '
		 AND sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 order by sellbilldetailid desc';

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

    public function countProductInSellBill($sellBillId) {
        $sql = 'SELECT SUM(sellbilldetailquantity) as totalQTY
		 FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		 WHERE sellandruternbilldetail.sellbillid = ' . $sellBillId . '
		 AND sellandruternbilldetail.selltype = 0
		 AND sellbillandrutern.conditions  = 0';

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

    //emaan
    public function countProductInSellBillExt($sellBillId) {
        $sql = 'SELECT SUM(sellbilldetailquantity) as totalQTY
		 FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		 WHERE sellandruternbilldetail.sellbillid = ' . $sellBillId . '
		 AND sellandruternbilldetail.selltype = 0';

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

    public function countProductInReturnSellBill($sellBillId) {
        $sql = 'SELECT SUM(sellbilldetailquantity) as totalQTY
		 FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		 WHERE sellandruternbilldetail.sellbillid = ' . $sellBillId . '
		 AND sellandruternbilldetail.selltype = 1';

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

    public function queryAllsellstor($sellbillid) {
        $sql = 'SELECT distinct(sellandruternbilldetail.storeid) ,store.storeName
 FROM sellandruternbilldetail

		join store
			on store.storeid=sellandruternbilldetail.storeid
		WHERE sellandruternbilldetail.sellbillid = ' . $sellbillid . '
		';

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

    public function queryAllnothaveuintid() {
        $sql = 'SELECT * FROM sellandruternbilldetail where sellandruternbilldetail.productunitid not in(

		SELECT productunitid FROM `productunit`  )';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function updateproductunit($productunitid, $sellbilldetailid) {
        $sql = 'UPDATE sellandruternbilldetail SET  productunitid = ' . $productunitid . '   WHERE sellbilldetailid = ' . $sellbilldetailid . '';

        //print_r('<br>'.$sql);

        $sqlQuery = new SqlQuery($sql);



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

    public function queryAllnothavestor($start, $end) {
        $sql = 'SELECT * FROM sellandruternbilldetail where storeid =0 LIMIT ' . $start . ' , ' . $end . ' ';

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

    public function updatestoreid($storeid, $sellbilldetailid) {
        $sql = 'UPDATE sellandruternbilldetail SET  storeid = ' . $storeid . ' WHERE sellbilldetailid = ' . $sellbilldetailid . '';

        //print_r('<br>'.$sql);

        $sqlQuery = new SqlQuery($sql);



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

    public function getsumdiscount($sellbillid, $selltype) {
        $sql = 'SELECT sum(discountvalue) FROM sellandruternbilldetail where sellbillid=' . $sellbillid . ' and selltype = ' . $selltype . '';

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

    public function queryAllGeneral($queryString) {
        if (strpos($queryString, "where") === false) { // no where use it before and that is coming without where
            $queryString = " where 1 " . $queryString;
        }
        $sql = 'SELECT  sellandruternbilldetail.*,sellbillandrutern.sellbillclientid as clientid,client.clientname,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note,productcat.productCatName,product.productCatId,sellbillandrutern.userid
		 FROM sellandruternbilldetail
                 left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 left join client on client.clientid = sellbillandrutern.sellbillclientid
                 left join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
                 left join productcat on productcat.productCatId = product.productCatId
		 ' . $queryString . ' AND sellbillandrutern.conditions = 0
                  order by sellandruternbilldetail.sellbilldetailproductid'; //AND sellbillandrutern.deletedsellid=0
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function sellbillandreturnsellbillWithProducts($queryString) {
        $sql = 'SELECT  sellandruternbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note
                FROM sellandruternbilldetail JOIN sellbillandrutern
		 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 1 ' . $queryString . '  AND sellbillandrutern.conditions = 0  ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneralPriceType($queryString) {
        $sql = 'SELECT sellandruternbilldetail.*,product.productName as productName,unit.unitId as unitid,unit.unitName as unitName
                FROM sellandruternbilldetail
                join productunit on productunit.productunitid = sellandruternbilldetail.productunitid
                join unit on unit.unitId = productunit.unitid
                join sellbillandrutern on sellbillandrutern.sellbillid = sellandruternbilldetail.sellbillid
                join product on product.productId = sellandruternbilldetail.sellbilldetailproductid ' . $queryString . '
                AND sellbillandrutern.conditions = 0 order by sellandruternbilldetail.sellbilldetailproductid';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneral2($queryString, $clientid) {
        $sql = 'SELECT client.clientname as clientname ,  sellandruternbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
                 join client on client.clientid = sellbillandrutern.sellbillclientid
		 ' . $queryString . ' AND sellbillandrutern.conditions = 0
                 AND client.clientid  = ' . $clientid . '
                 AND sellbillandrutern.deletedsellid=0 order by sellandruternbilldetail.sellbilldetailproductid';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneral3($queryString, $productid) {
        $sql = 'SELECT   client.clientname as clientname , client.clientid as clientid,  sellandruternbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
                 join client on client.clientid = sellbillandrutern.sellbillclientid
		 ' . $queryString . ' AND sellbillandrutern.conditions = 0
                 AND product.productId = ' . $productid . '
                 AND sellbillandrutern.deletedsellid=0 order by sellandruternbilldetail.sellbilldetailproductid';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneralSimple($queryString) {
        $sql = 'SELECT sellandruternbilldetail.sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbillandrutern.sellbillid,sellbillandrutern.sellbilldate,clientname as discountvalue,storeName as note
		 FROM sellandruternbilldetail
                 JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 join client on sellbillandrutern.sellbillclientid = client.clientid
                 join store on sellandruternbilldetail.storeid = store.storeId
		 ' . $queryString . ' AND sellbillandrutern.conditions = 0
                 AND sellbillandrutern.deletedsellid=0 order by sellandruternbilldetail.sellbilldetailproductid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBillNoOfPecies($billid) {
        $sql = 'SELECT sum(sellandruternbilldetail.sellbilldetailquantity) as note
        FROM sellandruternbilldetail WHERE sellbillid = ' . $billid;
        $sqlQuery = new SqlQuery($sql);
        //echo $sql . "<br/>";
        return $this->getRow($sqlQuery);
    }

    //# m7md 21/11/2017
    public function queryWithProductIdAndClientIdAndUnitExtNew($where) {
        $sql = 'SELECT sellandruternbilldetail.sellbilldetailprice, sellandruternbilldetail.pricetype, sellandruternbilldetail.sellbilldetaildate, product.productName, unit.unitName ,productcat.productCatName, sellbilldate, sellbillandrutern.sellbillid
		,1 as billtype , client.clientname  , sellbillandrutern.sellQuantity  as quantity
                FROM sellandruternbilldetail
		 JOIN sellbillandrutern
		 ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 JOIN productunit
		 ON sellandruternbilldetail.productunitid = productunit.productunitid
		 JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId
                 join client on client.clientid = sellbillandrutern.sellbillclientid
		 WHERE ' . $where . '
		  sellandruternbilldetail.selltype = 0
		 order by sellbilldetailid desc';

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

    public function queryAllNotDel($queryString) {
        $sql = 'SELECT sellandruternbilldetail.*,sellbillandrutern.sellbilldate
		 FROM sellandruternbilldetail
                 JOIN sellbillandrutern ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBySellbillidEX($sellbillid, $queryString = '') {
        $sql = 'SELECT * FROM sellandruternbilldetail WHERE sellbillid = ' . $sellbillid . ' ' . $queryString . ' ';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryreturnandSellBill($productId) {
        $sql = 'SELECT  sellandruternbilldetail.*,sellbillandrutern.sellbillclientid as clientid,client.clientname,product.productName as productName,product.productBuyPrice as storeid,sellbillandrutern.sellbilltotalbill as parcodeTotal,sellbillandrutern.sellbillaftertotalbill as note,productcat.productCatName,product.productCatId,sellbillandrutern.userid
		 FROM sellandruternbilldetail
                 left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 left join client on client.clientid = sellbillandrutern.sellbillclientid
                 left join product on product.productId = sellandruternbilldetail.sellbilldetailproductid
                 left join productcat on productcat.productCatId = product.productCatId
		 where sellandruternbilldetail.sellbilldetailproductid =  ' . $productId . ' AND sellbillandrutern.conditions = 0
                  order by sellandruternbilldetail.sellbilldetailproductid'; //AND sellbillandrutern.deletedsellid=0
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function SellAndReturnDETAILSCats($sellandreturndate = '') {
        $sql = 'SELECT productcat.productCatId,productcat.productCatName,sellbillandrutern.sellbilltotalbill as parcodeTotal,sellbillandrutern.sellbillaftertotalbill as note,sellandruternbilldetail.* FROM productcat
                left join sellandruternbilldetail on productcat.productCatId = sellandruternbilldetail.sellbilldetailcatid
                left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid where sellbillandrutern.conditions = 0 ' . $sellandreturndate . '';
        $sqlQuery = new SqlQuery($sql);
//        print($sql);
        return $this->getList($sqlQuery);
    }

    public function SellAndReturnDETAILSClients($sellandreturndate = '') {
        $sql = 'SELECT client.clientid,client.clientname as clientname,sellbillandrutern.sellbillclientid,sellandruternbilldetail.*,sellbillandrutern.sellbilltotalbill as parcodeTotal,sellbillandrutern.sellbillaftertotalbill as note
		 FROM client
                 left JOIN sellbillandrutern ON sellbillandrutern.sellbillclientid = client.clientid
		        left JOIN sellandruternbilldetail ON sellandruternbilldetail.sellbillid	= sellbillandrutern.sellbillid	WHERE sellandruternbilldetail.sellbillid IS NOT NULL ' . $sellandreturndate . ' ';
        $sqlQuery = new SqlQuery($sql);
//        print($sql);
        return $this->getList($sqlQuery);
    }

}

?>