<?php

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

	public function queryWithStoreId($storeId)
	{
		$sql = 'SELECT sellbilldetail. * , product.productName
				FROM sellbilldetail
				JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId

				WHERE sellbilldetail.storeid = "' . $storeId . '"';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

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

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

	public function queryByDateAndProductCat($startDate, $endDate, $catId)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, SUM(sellbilldetailquantity) as totQty

				FROM sellbilldetail , product
				WHERE product.productCatId  =' . $catId . '
				AND sellbilldetail.sellbilldetaildate >= "' . $startDate . '" AND sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
				AND sellbilldetail.sellbilldetailproductid =  product.productId
				GROUP BY sellbilldetail.sellbilldetailproductid';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryByDateAndProductCatLimited($startDate, $endDate, $catId, $start, $end)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, SUM(sellbilldetailquantity) as totQty

				FROM sellbilldetail , product
				WHERE product.productCatId  =' . $catId . '
				AND sellbilldetail.sellbilldetaildate >= "' . $startDate . '" AND sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
				AND sellbilldetail.sellbilldetailproductid =  product.productId
				GROUP BY sellbilldetail.sellbilldetailproductid
				limit ' . $start . ', ' . $end . '';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryProductDetailsByDate($startDate, $endDate, $proId)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName

				FROM sellbilldetail , product
				WHERE sellbilldetailproductid  =' . $proId . '
				AND sellbilldetail.sellbilldetaildate >= "' . $startDate . '" AND sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
				AND sellbilldetail.sellbilldetailproductid =  product.productId
				GROUP BY sellbilldetail.sellbilldetailproductid';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryWithSellBillId($sellbillid)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, store.storeName ,unit.unitName ,productcat.productCatName
                ,size.name as sizeName,color.name as colorName,productserailid,productserial.don,product.parcode as productParcode,sizecolorstoredetail.parcode as productParcodeSC,productunit.productnumber
		 FROM sellbilldetail JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId
		 left JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid
		 left JOIN  unit
		 ON unit.unitId = productunit.unitid

		 join productcat

		  on productcat.productCatId=product.productCatId
		  left join store
		  on store.storeid=sellbilldetail.storeid

                left join sizecolor as size on size.id = sellbilldetail.sizeid
                left join sizecolor as color on color.id = sellbilldetail.colorid

                left join productserial on (productserial.serialnumber=sellbilldetail.parcode and productserial.productid=sellbilldetail.sellbilldetailproductid and sellbilldetail.sizeid=productserial.sizeid and sellbilldetail.colorid=productserial.colorid)
                left join sizecolorstoredetail on (sizecolorstoredetail.productid = sellbilldetail.sellbilldetailproductid and sizecolorstoredetail.sizeId = sellbilldetail.sizeId and sizecolorstoredetail.colorId = sellbilldetail.colorId and sizecolorstoredetail.storeid=sellbilldetail.storeid)

		 WHERE sellbilldetail.sellbillid = ' . $sellbillid . '
                 order by sellbilldetail.sellbilldetailid asc';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryWithSellBillIdforproft($sellbillid)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, store.storeName ,unit.unitName ,productcat.productCatName,productcat.buydiscount,productcat.discounttype,productunit.productnumber
		 FROM sellbilldetail
                 JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId
		 left JOIN productunit ON sellbilldetail.productunitid = productunit.productunitid
		 left JOIN  unit ON unit.unitId = productunit.unitid
		 join productcat on productcat.productCatId=product.productCatId
		 left join store on store.storeid=sellbilldetail.storeid
		 WHERE sellbilldetail.sellbillid = ' . $sellbillid . '';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryWithSellBillIdforpermation($sellbillid, $order = ' order by sellbilldetail.sellbilldetailid asc')
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, store.storeName ,unit.unitName ,productcat.productCatName
                ,size.name as sizeName,color.name as colorName,productserial.enddate
		 FROM sellbilldetail JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid JOIN unit
		 ON unit.unitId = productunit.unitid

		 join productcat

		  on productcat.productCatId=product.productCatId
		  left join store
		  on store.storeid=sellbilldetail.storeid

                  left join sizecolor as size on size.id = sellbilldetail.sizeid
                  left join sizecolor as color on color.id = sellbilldetail.colorid

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

		 WHERE sellbilldetail.sellbillid in( ' . $sellbillid . ' )
                 ' . $order . ' '; //order by storeid  //using in for Bulk print of sell bill
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryWithSellBillIdforpermationWithPlayStationData($sellbillid, $order = ' order by sellbilldetail.sellbilldetailid asc')
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, store.storeName ,unit.unitName ,productcat.productCatName,startsysDate,endsysDate,moveStartSysDate,priceIs
                ,size.name as sizeName,color.name as colorName
		 FROM sellbilldetail JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid JOIN unit
		 ON unit.unitId = productunit.unitid

                 join restaurantplaystationplay on restaurantplaystationplay.id = sellbilldetail.playstationPlayId

		 join productcat

		  on productcat.productCatId=product.productCatId
		  left join store
		  on store.storeid=sellbilldetail.storeid

                  left join sizecolor as size on size.id = sellbilldetail.sizeid
                  left join sizecolor as color on color.id = sellbilldetail.colorid

		 WHERE sellbilldetail.sellbillid in( ' . $sellbillid . ' )
                  ' . $order . '  '; //order by storeid  //using in for Bulk print of sell bill
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryWithSellBillIdforpermationandstor($sellbillid, $storeid)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, store.storeName ,unit.unitName ,productcat.productCatName
		 FROM sellbilldetail JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid JOIN unit
		 ON unit.unitId = productunit.unitid

		 join productcat

		  on productcat.productCatId=product.productCatId
		  left join store
		  on store.storeid=sellbilldetail.storeid
		 WHERE sellbilldetail.sellbillid = ' . $sellbillid . ' and  sellbilldetail.storeid = ' . $storeid . '  ';

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

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

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

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

	public function queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $productid)
	{
		$sql = 'SELECT
		sellbilldetail.sellbilldetailquantity,
		sellbilldetail.sellbilldetailprice,
		sellbilldetail.sellbilldetailtotalprice,
		sellbilldetail.discountvalue,
		sellbilldetail.productunitid,
		sellbilldetail.buyprice,
                sellbilldetail.lastbuyprice,
                sellbilldetail.meanbuyprice,
                sellbilldetail.lastbuyprice_withDiscount,
                sellbilldetail.meanbuyprice_withDiscount,
                sellbilldetail.lastbuyprice_withTax,
                sellbilldetail.lastbuyprice_withDiscountAndTax,
                sellbilldetail.meanbuyprice_withTax,
		sellbilldetail.buydiscount,
		sellbilldetail.buydiscounttype,
		sellbill.sellbilltotalbill,
		sellbill.sellbilldiscount,
		sellbill.sellbilldiscounttype,
                sellbill.sellbilldate as note

		 FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		 where sellbill.conditions = 0
		 and sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
		 and sellbilldetailproductid in(' . $productid . ')';
		$sqlQuery = new SqlQuery($sql);
		//print('<br>'.$sql.'<br>');
		return $this->getList($sqlQuery);
	}

	public function queryWithDateAndConditionsAndProductIdExtNew($queryString)
	{
		$sql = 'SELECT
		sellbilldetail.sellbilldetailquantity,
		sellbilldetail.sellbilldetailprice,
		sellbilldetail.sellbilldetailtotalprice,
		sellbilldetail.discountvalue,
		sellbilldetail.productunitid,
		sellbilldetail.buyprice,
		sellbilldetail.buydiscount,
		sellbilldetail.buydiscounttype,
		sellbill.sellbilltotalbill,
		sellbill.sellbilldiscount,
		sellbill.sellbilldiscounttype

		 FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		 where sellbill.conditions = 0
                 ' . $queryString . '
		';
		$sqlQuery = new SqlQuery($sql);
		//print('<br>'.$sql.'<br>');
		return $this->getList($sqlQuery);
	}

	public function queryWithDateAndConditionsAndProductIdExtandstor($startDate, $endDate, $productid, $storid)
	{
		$sql = 'SELECT
		sellbilldetail.sellbilldetailquantity,
		sellbilldetail.sellbilldetailprice,
		sellbilldetail.sellbilldetailtotalprice,
		sellbilldetail.discountvalue,
		sellbilldetail.productunitid,
		sellbilldetail.buyprice,
		sellbilldetail.buydiscount,
		sellbilldetail.buydiscounttype,
		sellbill.sellbilltotalbill,
		sellbill.sellbilldiscount,
		sellbill.sellbilldiscounttype

		 FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		 where sellbill.conditions = 0

		 and sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
		 and sellbilldetailproductid =' . $productid . '   and (sellbilldetail.storeid=' . $storid . ' or sellbill.sellbillstoreid=' . $storid . ') ';
		$sqlQuery = new SqlQuery($sql);
		//print($sql);
		return $this->getList($sqlQuery);
	}

	public function queryWithDateAndConditionsAndProductIdExtandstorNew($queryString)
	{
		$sql = 'SELECT
		sellbilldetail.sellbilldetailquantity,
		sellbilldetail.sellbilldetailprice,
		sellbilldetail.sellbilldetailtotalprice,
		sellbilldetail.discountvalue,
		sellbilldetail.productunitid,
		sellbilldetail.buyprice,
		sellbilldetail.buydiscount,
		sellbilldetail.buydiscounttype,
		sellbill.sellbilltotalbill,
		sellbill.sellbilldiscount,
		sellbill.sellbilldiscounttype

		 FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		 where sellbill.conditions = 0

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

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

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

	public function queryFromAllTableWithDateAndConditionsAndCatId($startDate, $endDate, $productCatId)
	{
		$sql = 'SELECT profitproductcat.productid FROM
		(
		 SELECT sellbilldetail.sellbilldetailproductid as productid
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		 where sellbill.conditions = 0
		 and sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
		 and sellbilldetail.sellbilldetailcatid in(' . $productCatId . ')

		 union ALL

		 SELECT returnsellbilldetail.returnsellbilldetailproductid as productid
		 FROM returnsellbilldetail JOIN returnsellbill
		 ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
		 where returnsellbill.conditions = 0
		 and returnsellbill.returnsellbilldate
		 >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
		 and returnsellbilldetail.returnsellbilldetailcatid in(' . $productCatId . ')

		 union ALL

		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailcatid in(' . $productCatId . ')

		  union ALL

		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =1
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailcatid in(' . $productCatId . ')

		 union ALL

		 SELECT buybilldetail.buybilldetailproductid as productid
		FROM buybilldetail JOIN buybill
		ON buybilldetail.buybillid = buybill.buybillid
		where buybill.conditions = 0
		and buybill.buybilldate
		>= "' . $startDate . '" and buybill.buybilldate <= "' . $endDate . '"
		and buybilldetailcatid in(' . $productCatId . ')

		 union ALL

		 SELECT buyandruternbilldetail.buybilldetailproductid as productid
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions  = 0
		 AND  buyandruternbill.buybilldate
		 >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '"
		 and buyandruternbilldetail.billtype = 0
		 and buybilldetailcatid in(' . $productCatId . ')

		  union ALL

		  SELECT buyandruternbilldetail.buybilldetailproductid as productid
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions  = 0
		 AND  buyandruternbill.buybilldate
		 >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '"
		 and buyandruternbilldetail.billtype = 1
		 and buybilldetailcatid in(' . $productCatId . ')

		  union ALL

		 SELECT returnbuybilldetail.returnbuybilldetailproductid as productid
		 FROM returnbuybilldetail JOIN returnbuybill
		 ON returnbuybilldetail.returnbuybillid = returnbuybill.returnbuybillid
		 where returnbuybill.conditions = 0
		 and returnbuybill.returnbuybilldate
		 >= "' . $startDate . '" and returnbuybill.returnbuybilldate <= "' . $endDate . '"
		 and returnbuybilldetailcatid in(' . $productCatId . ')

                union ALL


                 SELECT billsproducts.productid as productid
		 FROM billsproducts
                 JOIN bills ON billsproducts.billid = bills.id
                 JOIN product ON product.productId = billsproducts.productid
		 where bills.deleted = 0
		 and date(bills.billdate) >= "' . $startDate . '" and date(bills.billdate) <= "' . $endDate . '"
		 and product.productCatId in(' . $productCatId . ')

                union ALL


                 SELECT billsreturnproducts.productid as productid
		 FROM billsreturnproducts
                 join  billsreturn on billsreturn.id = billsreturnproducts.returnbillid
                 JOIN product ON product.productId = billsreturnproducts.productid
		 where billsreturn.deleted = 0
		 and billsreturn.date >= "' . $startDate . '" and billsreturn.date <= "' . $endDate . '"
		 and product.productCatId in(' . $productCatId . ')


		 )
		 as profitproductcat  group by  profitproductcat.productid

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

	public function queryFromAllTableWithDateAndConditionsAndCatIdNew($query)
	{
		$sql = $query;
		$sqlQuery = new SqlQuery($sql);
		//print($sql);
		return $this->getList($sqlQuery);
	}

	public function getallproductbyclientanddate($startDate, $endDate, $productid, $clientid)
	{
		$sql = '
		SELECT sellbilldetail.sellbilldetailproductid as productid ,sellbilldetail.sellbilldetailquantity  as quantity
		,sellbill.sellbilldate  as selldate ,sellbilldetail.sellbilldetailprice  as price,product.productName
		,productcat.productCatName
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid

		 join product
		 on product.productId =' . $productid . '

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where sellbill.conditions = 0
		 and sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
		 and sellbilldetail.sellbilldetailproductid =' . $productid . '
		  and sellbill.sellbillclientid=' . $clientid . '


		 union ALL

		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 ,sellandruternbilldetail.sellbilldetailquantity  as quantity

		 ,sellbillandrutern.sellbilldate  as selldate ,sellandruternbilldetail.sellbilldetailprice  as price
		,product.productName ,productcat.productCatName
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		  join product
		 on product.productId =' . $productid . '

		  join productcat
		 on productcat.productCatId=product.productCatId

		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailproductid =' . $productid . '
		 and sellbillandrutern.sellbillclientid=' . $clientid . '



		 ';
		/*
          union ALL

          SELECT returnsellbilldetail.returnsellbilldetailproductid as productid   				,returnsellbilldetail.returnsellbilldetailquantity  as quantity
          ,returnsellbill.returnsellbilldate  as selldate ,returnsellbilldetail.returnsellbilldetailprice  as price
          FROM returnsellbilldetail JOIN returnsellbill
          ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
          where returnsellbill.conditions = 0
          and returnsellbill.returnsellbilldate
          >= "'.$startDate.'" and returnsellbill.returnsellbilldate <= "'.$endDate.'"
          and returnsellbilldetail.returnsellbilldetailproductid ='.$productid.'
          and returnsellbill.returnsellbillclientid='.$clientid.'

         */

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

	public function getallproductbyclientanddatenor($startDate, $endDate, $productid, $clientid)
	{
		$sql = '
		SELECT sellbilldetail.sellbilldetailproductid as productid ,sellbilldetail.sellbilldetailquantity  as quantity
		,sellbill.sellbilldate  as selldate ,sellbilldetail.sellbilldetailtotalprice  as price,product.productName
		,productcat.productCatName,"sellbillController.php" as controlnname,sellbill.sellbillid as sellid
		,0 as type
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid

		 join product
		 on product.productId =' . $productid . '

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where sellbill.conditions = 0
		 and sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
		 and sellbilldetail.sellbilldetailproductid =' . $productid . '
		  and sellbill.sellbillclientid=' . $clientid . '


		 union ALL

		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 ,sellandruternbilldetail.sellbilldetailquantity  as quantity

		 ,sellbillandrutern.sellbilldate  as selldate ,sellandruternbilldetail.sellbilldetailtotalprice  as price
		,product.productName ,productcat.productCatName,"sellbillandruternController.php" as controlnname,sellbillandrutern.sellbillid as sellid ,0 as type
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		  join product
		 on product.productId =' . $productid . '

		  join productcat
		 on productcat.productCatId=product.productCatId

		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailproductid =' . $productid . '
		 and sellbillandrutern.sellbillclientid=' . $clientid . '






		 union ALL

		 SELECT returnsellbilldetail.returnsellbilldetailproductid as productid   				,returnsellbilldetail.returnsellbilldetailquantity  as quantity
		 	,returnsellbill.returnsellbilldate  as selldate ,returnsellbilldetail.returnsellbilldetailtotalprice  as price	,product.productName ,productcat.productCatName,"returnsellbillController.php" as  controlnname,returnsellbill.returnsellbillid as sellid ,1 as type
		 FROM returnsellbilldetail JOIN returnsellbill
		 ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid

		   join product
		 on product.productId =' . $productid . '

		  join productcat
		 on productcat.productCatId=product.productCatId
		 where returnsellbill.conditions = 0
		 and returnsellbill.returnsellbilldate
		 >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
		 and returnsellbilldetail.returnsellbilldetailproductid =' . $productid . '
		 and returnsellbill.returnsellbillclientid=' . $clientid . '





		 union ALL
		  SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 ,sellandruternbilldetail.sellbilldetailquantity  as quantity

		 ,sellbillandrutern.sellbilldate  as selldate ,sellandruternbilldetail.sellbilldetailtotalprice  as price
		,product.productName ,productcat.productCatName,"sellbillandruternController.php" as controlnname  ,sellbillandrutern.sellbillid as sellid ,1 as type
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		  join product
		 on product.productId =' . $productid . '

		  join productcat
		 on productcat.productCatId=product.productCatId

		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =1
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and sellandruternbilldetail.sellbilldetailproductid =' . $productid . '
		 and sellbillandrutern.sellbillclientid=' . $clientid . ' ';


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

	public function getallcatsellrate($catid, $startDate, $endDate)
	{
		$sql = '
		SELECT sellbilldetail.sellbilldetailproductid as productid ,sellbilldetail.sellbilldetailquantity  as quantity
		,sellbill.sellbilldate  as selldate ,sellbilldetail.sellbilldetailtotalprice  as price,product.productName
		,productcat.productCatName,"sellbillController.php" as controlnname,sellbill.sellbillid as sellid
		,0 as type
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid

		 join product
		 on product.productId =sellbilldetail.sellbilldetailproductid

		 join productcat
		 on productcat.productCatId=product.productCatId
		 where sellbill.conditions = 0
		 and sellbill.sellbilldate >= "' . $startDate . '" and sellbill.sellbilldate <= "' . $endDate . '"
		 and productcat.productCatId = ' . $catid . '



		 union ALL

		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 ,sellandruternbilldetail.sellbilldetailquantity  as quantity

		 ,sellbillandrutern.sellbilldate  as selldate ,sellandruternbilldetail.sellbilldetailtotalprice  as price
		,product.productName ,productcat.productCatName,"sellbillandruternController.php" as controlnname,sellbillandrutern.sellbillid as sellid ,0 as type
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		  join product
		 on product.productId =sellandruternbilldetail.sellbilldetailproductid

		  join productcat
	      on productcat.productCatId=product.productCatId

		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and productcat.productCatId =' . $catid . '






		 union ALL

		 SELECT returnsellbilldetail.returnsellbilldetailproductid as productid   				,returnsellbilldetail.returnsellbilldetailquantity  as quantity
		 	,returnsellbill.returnsellbilldate  as selldate ,returnsellbilldetail.returnsellbilldetailtotalprice  as price	,product.productName ,productcat.productCatName,"returnsellbillController.php" as  controlnname,returnsellbill.returnsellbillid as sellid ,1 as type
		 FROM returnsellbilldetail JOIN returnsellbill
		 ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid

		   join product
		 on product.productId =returnsellbilldetail.returnsellbilldetailproductid

		  join productcat
		 on productcat.productCatId=product.productCatId

		 where returnsellbill.conditions = 0
		 and returnsellbill.returnsellbilldate
		 >= "' . $startDate . '" and returnsellbill.returnsellbilldate <= "' . $endDate . '"
			 and productcat.productCatId =' . $catid . '





		 union ALL
		  SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 ,sellandruternbilldetail.sellbilldetailquantity  as quantity

		 ,sellbillandrutern.sellbilldate  as selldate ,sellandruternbilldetail.sellbilldetailtotalprice  as price
		,product.productName ,productcat.productCatName,"sellbillandruternController.php" as controlnname  ,sellbillandrutern.sellbillid as sellid ,1 as type
		 FROM sellandruternbilldetail JOIN sellbillandrutern
		 ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid

		  join product
		 on product.productId =sellandruternbilldetail.sellbilldetailproductid


		join productcat
	      on productcat.productCatId=product.productCatId

		 where sellbillandrutern.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellbillandrutern.sellbilldate
		 >= "' . $startDate . '" and sellbillandrutern.sellbilldate <= "' . $endDate . '"
		 and productcat.productCatId =' . $catid . '';




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

	public function queryWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid)
	{
		$sql = 'SELECT sellbilldetail.*, product.productName, unit.unitName ,productcat.productCatName, sellbilldate
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		  JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId
		 JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid
		 JOIN unit
		 ON unit.unitId = productunit.unitid
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
		 WHERE sellbilldetail.sellbilldetailproductid = ' . $ProductId . '
		 AND sellbilldetail.productunitid = ' . $productunitid . '
		 AND sellbill.sellbillclientid = ' . $ClientId . '
		 AND sellbill.conditions = 0
		 order by sellbilldetailid desc limit 1';

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

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

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

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

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

		 WHERE sellbilldetail.sellbilldetailproductid = ' . $ProductId . '
		 AND sellbill.sellbillclientid = ' . $ClientId . '
		 AND sellbill.conditions = 0 limit 1';

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

	public function queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $productunitid, $startDate, $endDate)
	{
		$sql = 'SELECT sellbilldetail.sellbilldetailprice, sellbilldetail.pricetype, sellbilldetail.sellbilldetaildate, product.productName, unit.unitName ,productcat.productCatName, sellbilldate, sellbill.sellbillid
		 ,0 as billtype FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		  JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId
		 JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid
		 JOIN unit
		 ON unit.unitId = productunit.unitid
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
		 WHERE sellbilldetail.sellbilldetailproductid = ' . $ProductId . '
		 AND sellbilldetail.productunitid = ' . $productunitid . '
		 AND sellbill.sellbillclientid = ' . $ClientId . '
		 AND sellbill.sellbilldate >= "' . $startDate . '" AND sellbill.sellbilldate <= "' . $endDate . '"
		 AND sellbill.conditions = 0
		 order by sellbilldetailid desc';

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

	public function countProductInSellBill($sellBillId)
	{
		$sql = 'SELECT SUM(sellbilldetailquantity) as sumQuantity
		 FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		 WHERE sellbilldetail.sellbillid = ' . $sellBillId . '';

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

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

			join store
			on store.storeid=sellbilldetail.storeid
			 where sellbillid=' . $sellid . '

			';

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

	public function queryAllbyproductunitid($productunitid)
	{
		$sql = 'SELECT * FROM sellbilldetail where productunitid=' . $productunitid . '';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

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

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

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

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

		$sqlQuery = new SqlQuery($sql);



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

	public function updateSoldSerialsInDetails($tableName, $idColumnName, $sellbilldetailid, $productSerials)
	{
		$sql = 'UPDATE ' . $tableName . ' SET  soldSerialsInDetails = "' . $productSerials . '" WHERE ' . $idColumnName . ' = ' . $sellbilldetailid . '';
		//print_r('<br>'.$sql);
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}

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

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

		$sqlQuery = new SqlQuery($sql);



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

	//////////////////////////////////////product not sell////////////////////////////////////
	public function queryproductnotsell($startDate, $endDate)
	{
		$sql = 'SELECT Distinct sum(storedetail.productquantity) as productquantity ,
		store.storeName, storedetail.productid,product.productName,sum(product.limitamount) as limitamount
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 and product.productId not in(

		SELECT profitproductcat.productid FROM
		(
		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 FROM sellandruternbilldetail JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 where product.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellandruternbilldetail.sellbilldetaildate
		 >= "' . $startDate . '" and sellandruternbilldetail.sellbilldetaildate <= "' . $endDate . '"

		  union ALL

		 SELECT sellbilldetail.sellbilldetailproductid as productid
		 FROM sellbilldetail JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId
		 where product.conditions = 0
		 and sellbilldetail.sellbilldetaildate
		 >= "' . $startDate . '" and sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
		 ) as profitproductcat  group by  profitproductcat.productid

		 )
		 group by  product.productId
		 ';

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

	public function queryproductnotsellwithstorename($startDate, $endDate, $storename, $queryString = '')
	{
		$sql = 'SELECT Distinct sum(storedetail.productquantity) as productquantity ,
		store.storeName, product.productId as sellbilldetailproductid, product.productId,product.productName,sum(product.limitamount) as limitamount
		 FROM storedetail JOIN product
		 ON  storedetail.productid = product.productId JOIN store
		 ON  storedetail.storeid = store.storeId
		 where product.conditions = 0 and store.conditions = 0
		 and store.storeId="' . $storename . '"
		 ' . $queryString . '
		 and product.productId not in(
		 SELECT profitproductcat.productid FROM
		(

		 SELECT sellandruternbilldetail.sellbilldetailproductid as productid
		 FROM sellandruternbilldetail JOIN product
		 ON sellandruternbilldetail.sellbilldetailproductid = product.productId
		 where product.conditions = 0
		 and sellandruternbilldetail.selltype =0
		 and sellandruternbilldetail.sellbilldetaildate
		 >= "' . $startDate . '" and sellandruternbilldetail.sellbilldetaildate <= "' . $endDate . '"

		  union ALL

		 SELECT sellbilldetail.sellbilldetailproductid as productid
		 FROM sellbilldetail JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId
		 where product.conditions = 0
		 and sellbilldetail.sellbilldetaildate
		 >=  "' . $startDate . '" and sellbilldetail.sellbilldetaildate <= "' . $endDate . '"
		 ) as profitproductcat  group by  profitproductcat.productid
		 )
		  group by product.productId

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

	public function queryAllnothavestor($star, $end)
	{
		$sql = 'SELECT * FROM sellbilldetail where storeid =0  LIMIT ' . $star . ' , ' . $end . '';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function getsumdiscount($sellbillid)
	{
		$sql = 'SELECT sum(discountvalue) FROM sellbilldetail where sellbillid=' . $sellbillid . '';
		$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  sellbilldetail.*,sellbill.sellbillclientid as clientid,client.clientname,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note,productcat.productCatName,product.productCatId,sellbill.userid
		 FROM sellbilldetail
                 left JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid
                 left join client on client.clientid = sellbill.sellbillclientid
                 left join product on product.productId = sellbilldetail.sellbilldetailproductid
                 left join productcat on productcat.productCatId = product.productCatId
		 ' . $queryString . ' AND sellbill.conditions = 0
                  order by sellbilldetail.sellbilldetailproductid'; //AND sellbill.deletedsellid=0
		//echo $sql . "<br/>";
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function sellbillWithProducts($queryString)
	{
		$sql = 'SELECT  sellbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note
                FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
                left join client on client.clientid = sellbill.sellbillclientid
                left join goverarea on goverarea.clientareaid = client.clientareaid
                left join clientarea on clientarea.id = client.clientareaid
                left join government on government.governmetid = goverarea.governmentid
                join product on product.productId = sellbilldetail.sellbilldetailproductid
		WHERE 1 ' . $queryString . ' AND sellbill.conditions = 0 '; //left joins made by fatma to get results for search with date only in sellingReportByArea.php


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

	public function queryAllGeneral2($queryString, $clientid)
	{
		$sql = 'SELECT client.clientname as clientname , sellbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
                 join product on product.productId = sellbilldetail.sellbilldetailproductid
                 join client on client.clientid = sellbill.sellbillclientid

		 ' . $queryString . ' AND sellbill.conditions = 0
                 AND client.clientid  = ' . $clientid . '
                 AND sellbill.deletedsellid=0 order by sellbilldetail.sellbilldetailproductid';
		// echo $sql . "<br/>";
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

	public function queryAllGeneral3($queryString, $productid)
	{
		$sql = 'SELECT client.clientname as clientname , client.clientid as clientid ,  sellbilldetail.*,product.productName as productName,product.productBuyPrice as storeid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
                 join product on product.productId = sellbilldetail.sellbilldetailproductid
                 join client on client.clientid = sellbill.sellbillclientid

		 ' . $queryString . ' AND sellbill.conditions = 0
                 AND product.productId  = ' . $productid . '
                 AND sellbill.deletedsellid=0 order by sellbilldetail.sellbilldetailproductid';
		//echo $sql . "<br/>";
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

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

	public function queryAllGeneralGrouped($queryString)
	{
		$sql = 'SELECT sum(sellbilldetail.sellbilldetailquantity) as totQty, sellbilldetail.*,productcat.productCatName as productCatName,product.productName as productName,product.productBuyPrice as storeid
		 FROM sellbilldetail JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
                 join product on product.productId = sellbilldetail.sellbilldetailproductid
                 join productcat on productcat.productCatId = sellbilldetail.sellbilldetailcatid
		 ' . $queryString . ' AND sellbill.conditions = 0
                 AND sellbill.deletedsellid=0 group by sellbilldetail.sellbilldetailcatid order by sellbilldetail.sellbilldetailproductid';
		//print_r($sql);
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

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

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

	//# m7md 21/11/2017

	public function queryWithProductIdAndClientIdAndUnitExtNew($where)
	{
		$sql = 'SELECT sellbilldetail.sellbilldetailprice, sellbilldetail.pricetype, sellbilldetail.sellbilldetaildate, product.productName, unit.unitName ,productcat.productCatName, sellbilldate, sellbill.sellbillid
		 ,0 as billtype , client.clientname , sellbilldetail.sellbilldetailquantity as quantity
                 FROM sellbilldetail
		 JOIN sellbill
		 ON sellbilldetail.sellbillid = sellbill.sellbillid
		  JOIN product
		 ON sellbilldetail.sellbilldetailproductid = product.productId
		 JOIN productunit
		 ON sellbilldetail.productunitid = productunit.productunitid
		 JOIN unit
		 ON unit.unitId = productunit.unitid
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
                 join client on client.clientid = sellbill.sellbillclientid
		 WHERE ' . $where . '
		  sellbill.conditions = 0
		 order by sellbilldetailid desc';

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

	//emaaaaaaan
	public function queryWithProductid($proid)
	{
		$sql = 'SELECT sellbilldetail.*
		 FROM sellbilldetail WHERE sellbilldetail.sellbilldetailproductid = ' . $proid . '
                 order by sellbilldetail.sellbilldetailid asc';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}

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

	public function updatePrices_f($tableName, $buyprice, $lastbuyprice, $meanbuyprice, $lastbuyprice_withDiscount, $meanbuyprice_withDiscount, $columName, $detailId)
	{


		$sql = 'UPDATE ' . $tableName . ' SET  buyprice = ' . $buyprice . ' ,lastbuyprice = ' . $lastbuyprice . ' , meanbuyprice = ' . $meanbuyprice . ', lastbuyprice_withDiscount = ' . $lastbuyprice_withDiscount . ', meanbuyprice_withDiscount = ' . $meanbuyprice_withDiscount . '
                where ' . $columName . ' = ' . $detailId . '';
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}

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

	public function sellbillDETAILS($sellbilldate = '')
	{
		$sql = 'SELECT product.productId,product.productName as productName,productcat.productCatName,product.productCatId,sellbilldetail.sellbilldetailproductid,sellbilldetail.buyprice,sellbilldetail.productunitid,SUM(sellbilldetail.sellbilldetailquantity) AS sumQuantity
		 FROM product
		 left JOIN sellbilldetail ON sellbilldetail.sellbilldetailproductid = product.productId
                 left JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid
                 left join productcat on productcat.productCatId = product.productCatId where sellbill.conditions = 0 ' . $sellbilldate . '
                  GROUP BY sellbilldetail.sellbilldetailproductid ORDER BY SUM(sellbilldetail.sellbilldetailquantity) DESC ';
		$sqlQuery = new SqlQuery($sql);
		//        print($sql);
		return $this->getList($sqlQuery);
	}

	public function sellbillReturnDETAILS($sellbillreturndate = '')
	{
		$sql = 'SELECT product.productId,product.productName as productName,productcat.productCatName,product.productCatId,returnsellbilldetail.returnsellbilldetailproductid as sellbilldetailproductid,returnsellbilldetail.buyprice,returnsellbilldetail.productunitid,SUM(returnsellbilldetail.returnsellbilldetailquantity) AS sumQuantity
		        FROM product
		        left JOIN returnsellbilldetail ON returnsellbilldetail.returnsellbilldetailproductid = product.productId
                 left JOIN returnsellbill ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
                 left join productcat on productcat.productCatId = product.productCatId
		        where returnsellbill.conditions = 0 ' . $sellbillreturndate . '
                 GROUP BY returnsellbilldetail.returnsellbilldetailproductid  ORDER BY SUM(returnsellbilldetail.returnsellbilldetailquantity) DESC ';
		$sqlQuery = new SqlQuery($sql);
		//        print($sql);
		return $this->getList($sqlQuery);
	}

	public function SellAndReturnDETAILS($sellandreturndate = '')
	{
		$sql = 'SELECT product.productId,product.productName as productName,productcat.productCatName,product.productCatId,sellandruternbilldetail.sellbilldetailproductid	as sellbilldetailproductid,sellandruternbilldetail.buyprice,SUM(sellandruternbilldetail.sellbilldetailquantity) AS sumQuantity ,sellandruternbilldetail.productunitid,sellandruternbilldetail.selltype
		 FROM product
		          left join sellandruternbilldetail on product.productId = sellandruternbilldetail.sellbilldetailproductid
                 left JOIN sellbillandrutern ON sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
                 left join productcat on productcat.productCatId = product.productCatId
		          where sellbillandrutern.conditions = 0 ' . $sellandreturndate . '
                  GROUP BY sellandruternbilldetail.sellbilldetailproductid  ORDER BY SUM(sellandruternbilldetail.sellbilldetailquantity) DESC ';
		$sqlQuery = new SqlQuery($sql);
		//        print($sql);
		return $this->getList($sqlQuery);
	}

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

	public function sellbillDETAILSCats($sellbilldate = '')
	{
		$sql = 'SELECT productcat.productCatId,productcat.productCatName as productCatName,sellbill.sellbilltotalbill as parcodeTotal,sellbill.sellbillaftertotalbill as note,sellbilldetail.* from productcat
                left JOIN sellbilldetail ON sellbilldetail.sellbilldetailcatid = productcat.productCatId
                left JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid where sellbill.conditions = 0 ' . $sellbilldate . ' ';
		$sqlQuery = new SqlQuery($sql);
		//        print($sql);
		return $this->getList($sqlQuery);
	}

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

	public function getLastSellDataOfProduct($productId, $sizeid, $colorid)
	{
		$sql = 'SELECT sellbilldetail.*
		FROM sellbilldetail
                JOIN sellbill ON (sellbill.sellbillid = sellbilldetail.sellbillid and sellbill.conditions = 0)
		WHERE sellbilldetailproductid = ' . $productId . ' and sizeid=' . $sizeid . ' and colorid=' . $colorid . ' order by sellbilldetailid desc limit 1';
		$sqlQuery = new SqlQuery($sql);
		return $this->getRow($sqlQuery);
	}

	public function getproductpricebuy($productId, $storereportmodelid)
	{
		$sql = 'SELECT sellbilldetail.*
		FROM sellbilldetail
		WHERE sellbilldetailproductid = ' . $productId . ' and sellbillid=' . $storereportmodelid;
		$sqlQuery = new SqlQuery($sql);
		return $this->getRow($sqlQuery);
	}
}
