<?php

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

    public function queryQuantityWithProductAndSellType($productId, $selltype) {
        $sql = 'SELECT Sum(buyandruternbilldetail.buybilldetailquantity) as sumQuantity
				 FROM buyandruternbilldetail JOIN buyandruternbill
				 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
				 where buyandruternbill.conditions = 0
				 and buyandruternbilldetail.billtype =' . $selltype . '
				 and buyandruternbilldetail.buybilldetailproductid =' . $productId . '';

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

    public function queryQuantityWithProductAndSellTypeAndDate($productId, $selltype, $startDate, $endDate) {
        $sql = 'SELECT Sum(buyandruternbilldetail.buybilldetailquantity) as sumQuantity
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions = 0
		 and buyandruternbilldetail.billtype =' . $selltype . '
		 and buyandruternbilldetail.buybilldetailproductid =' . $productId . '
		 and buyandruternbill.buybilldate >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryQuantityWithProductAndSellTypeAndDate2($productId, $selltype, $startDate, $endDate, $queryString = '') {
        $sql = 'SELECT buyandruternbilldetail.buybilldetailquantity as buybilldetailquantity ,
                        buyandruternbilldetail.productunitid as productunitid
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions = 0
		 and buyandruternbilldetail.billtype =' . $selltype . '
		 and buyandruternbilldetail.buybilldetailproductid =' . $productId . '
		 and buyandruternbill.buybilldate >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '" ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByBuybillidExt($value) {
        $sql = 'SELECT buyandruternbilldetail.*, product.productName, unit.unitName ,unit.unitid ,productunit.productnumber , productcat.productCatName
                ,size.name as sizeName,color.name as colorName
		 FROM buyandruternbilldetail JOIN product
		 ON buyandruternbilldetail.buybilldetailproductid = product.productId JOIN productunit
		 ON buyandruternbilldetail.productunitid = productunit.productunitid JOIN unit
		 ON unit.unitId = productunit.unitid
		 JOIN productcat
		 ON productcat.productCatId = product.productCatId

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

		   WHERE buybillid = ?';

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

    public function queryByBuybillidANDType($value, $type) {
        $sql = 'SELECT buyandruternbilldetail.*, product.productName, product.parcode, unit.unitName ,unit.unitid ,productunit.productnumber , productcat.productCatName,size.name as sizeName,color.name as colorName
		 FROM buyandruternbilldetail JOIN product
		 ON buyandruternbilldetail.buybilldetailproductid = product.productId
		 left JOIN productunit
		 ON buyandruternbilldetail.productunitid = productunit.productunitid
		 left JOIN unit
		 ON unit.unitId = productunit.unitid
		 JOIN productcat
		 ON productcat.productCatId = product.productCatId

                 left join sizecolor as size on size.id = buyandruternbilldetail.sizeid
                 left join sizecolor as color on color.id = buyandruternbilldetail.colorid
		   WHERE buybillid = ?
		   AND buyandruternbilldetail.billtype = ' . $type . '';

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

    public function queryWithDateAndConditions($startDate, $endDate) {
        $sql = 'SELECT buyandruternbilldetail.*
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions  = 0
		 AND  buyandruternbill.buybilldate >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '"';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsAndType($startDate, $endDate, $billtype) {
        $sql = 'SELECT buyandruternbilldetail.*
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions  = 0
		 AND  buyandruternbill.buybilldate
		 >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '"
		 and buyandruternbilldetail.billtype = ' . $billtype . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsAndTypeAndProductIdandstore($startDate, $endDate, $billtype, $ProductId, $storid) {
        $sql = 'SELECT buyandruternbilldetail.*
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions  = 0
		 and buyandruternbill.buybillstoreid=' . $storid . '
                 AND  buyandruternbill.buybilldate
		 >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '"
		 and buyandruternbilldetail.billtype = ' . $billtype . '
		 and buybilldetailproductid =' . $ProductId . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsAndTypeAndProductIdandstoreNew($billtype, $queryString) {
        $sql = 'SELECT buyandruternbilldetail.*
		 FROM buyandruternbilldetail JOIN buyandruternbill
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions  = 0
                 and buyandruternbilldetail.billtype = ' . $billtype . '
		' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function countProductInBuyBillsOnly($buyBillId) {
        $sql = 'SELECT SUM(buybilldetailquantity) as totalQty
		 FROM buyandruternbill
		 JOIN buyandruternbilldetail
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 WHERE buyandruternbill.buybillid = ' . $buyBillId . '
		 AND buyandruternbilldetail.billtype = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function buytotalpriceBillsOnly($buyBillId, $type) {
        $sql = 'SELECT SUM(buybilldetailtotalprice-discountvalue) as buytotalprice
		 FROM buyandruternbill
		 JOIN buyandruternbilldetail
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 WHERE buyandruternbill.buybillid = ' . $buyBillId . '
		 AND buyandruternbilldetail.billtype = ' . $type . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function countProductInReturnBuyBillsOnly($buyBillId) {
        $sql = 'SELECT SUM(buybilldetailquantity) as totalQty
		 FROM buyandruternbill
		 JOIN buyandruternbilldetail
		 ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 WHERE buyandruternbill.buybillid = ' . $buyBillId . '
		 AND buyandruternbilldetail.billtype = 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

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

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

    public function updatequantityBefore($quantitybefore, $buybilldetailid) {
        $sql = 'UPDATE buyandruternbilldetail SET  quantitybefore = ' . $quantitybefore . ' WHERE buybilldetailid = ' . $buybilldetailid . ' ';
        $sqlQuery = new SqlQuery($sql);

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

    public function getProductOrderInBillDetails($buybillid, $buybilldetailid, $productid) {
        $sql = "SELECT count(buybilldetailid)
                FROM buyandruternbilldetail
                WHERE buybilldetailid < $buybilldetailid
                and buybillid = $buybillid
                and buybilldetailproductid = $productid
                order by buybilldetailid asc"; //-- = and buybill.store //store is fixed in each bill

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

    public function updateproductunit($buyandruternbilldetail) {
        $sql = 'UPDATE buyandruternbilldetail SET  productunitid = ? WHERE buybilldetailid = ?';
        $sqlQuery = new SqlQuery($sql);


        $sqlQuery->setNumber($buyandruternbilldetail->productunitid);
        $sqlQuery->setNumber($buyandruternbilldetail->buybilldetailid);

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

    public function queryWithProductIdAndSupplierIdAndUnitExt($ProductId, $supplierId, $productunitid, $startDate, $endDate) {
        $sql = 'SELECT buyandruternbilldetail.buybilldetailprice, buyandruternbilldetail.buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, buybilldate, buyandruternbill.buybillid
		 FROM buyandruternbilldetail
		 JOIN buyandruternbill
		 ON   buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 JOIN product
		 ON buyandruternbilldetail.buybilldetailproductid = product.productId
		 JOIN productunit
		 ON buyandruternbilldetail.productunitid = productunit.productunitid
		 JOIN unit
		 ON unit.unitId = productunit.unitid
		  join productcat
		  on productcat.productCatId=product.productCatId

		 WHERE buyandruternbilldetail.buybilldetailproductid = ' . $ProductId . '
		 AND buyandruternbilldetail.productunitid = ' . $productunitid . '
		 AND buyandruternbill.buybillsupplierid = ' . $supplierId . '
		 AND buyandruternbill.conditions = 0
		 AND buyandruternbill.buybilldate >= "' . $startDate . '" AND buyandruternbill.buybilldate <= "' . $endDate . '"
		 order by buybilldetailid desc';

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

    public function getsumdiscount($buybillid, $billtype) {
        $sql = 'SELECT sum(discountvalue) FROM buyandruternbilldetail where buybillid=' . $buybillid . ' and billtype = ' . $billtype . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

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

    public function queryAllGeneral($queryString) {
        $sql = 'SELECT buyandruternbilldetail.*,product.productName as productName,unit.unitId as unitid,unit.unitName as unitName,productcat.productCatName,product.productCatId,product.logo,buyandruternbill.buybilltotalbill as Parcode,buyandruternbill.buybillaftertotalbill as note,buyandruternbill.userid
                FROM buyandruternbilldetail
                join productunit on productunit.productunitid = buyandruternbilldetail.productunitid
                join unit on unit.unitId = productunit.unitid
                join buyandruternbill on buyandruternbill.buybillid = buyandruternbilldetail.buybillid
                join product on product.productId = buyandruternbilldetail.buybilldetailproductid
                join productcat on productcat.productCatId = product.productCatId
                ' . $queryString . '
                AND buyandruternbill.conditions = 0 order by buyandruternbilldetail.buybilldetailproductid';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneralSimple($queryString) {
        $sql = 'SELECT buyandruternbilldetail.buybilldetailquantity,buyandruternbilldetail.productunitid,buyandruternbill.buybillid,buyandruternbill.buybilldate,suppliername as discountvalue,storeName as note
		 FROM buyandruternbilldetail
                 join buyandruternbill on buyandruternbill.buybillid = buyandruternbilldetail.buybillid
                 join supplier on buyandruternbill.buybillsupplierid = supplier.supplierid
                 join store on buyandruternbill.buybillstoreid = store.storeId
		 ' . $queryString . ' AND buyandruternbill.conditions = 0
                 AND (buyandruternbill.deletedbuyid=0 ||buyandruternbill.deletedbuyid is null) order by buyandruternbilldetail.buybilldetailproductid';
        //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   = "buyandruternbill" ';

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

    public function queryPriceWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid, $selltype) {
        $sql = 'SELECT buyandruternbilldetail.buybilldetailprice, buybilldate
		 FROM buyandruternbilldetail
		 join buyandruternbill on buyandruternbill.buybillid = buyandruternbilldetail.buybillid

		 WHERE buyandruternbilldetail.buybilldetailproductid = ' . $ProductId . '
		 AND buyandruternbilldetail.productunitid = ' . $productunitid . '
		 AND buyandruternbill.buybillsupplierid = ' . $ClientId . '
		 AND buyandruternbill.conditions = 0
		 AND buyandruternbilldetail.billtype =' . $selltype . '
		 order by buybilldetailid desc limit 1';
        //echo "<br/>" . $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

}

?>