<?php

/**
 * Class that operate on table 'billsreturnproducts'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2016-08-06 13:48
 */
class BillsreturnproductsMySqlExtDAO extends BillsreturnproductsMySqlDAO {

    public function queryByBillIdAndProductidAndDeleted($billId, $billProductId, $productId, $deleted) {
        $sql = 'SELECT   p.*
                        FROM billsreturnproducts p
                        join  billsreturn b             on b.id = p.returnbillid

                        WHERE b.billid =' . $billId . '
                        and p.productid =' . $productId . '
                        and p.billproductid =' . $billProductId . '
                        and p.deleted =' . $deleted . '   ';

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

    public function queryByReturnbillidAndDeleted($returnBillId, $deleted) {
        $sql = 'SELECT * FROM billsreturnproducts WHERE returnbillid = ? and deleted = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($returnBillId);
        $sqlQuery->setNumber($deleted);
        return $this->getList($sqlQuery);
    }

    public function queryByBillIdAndProductidAndDeletedExcept($billId, $billProductId, $productId, $deleted, $billsReturnId) {
        $sql = 'SELECT   p.*
                        FROM billsreturnproducts p
                        join  billsreturn b             on b.id = p.returnbillid

                        WHERE b.billid =' . $billId . '
                        and p.productid =' . $productId . '
                        and p.billproductid =' . $billProductId . '
                        and p.returnbillid != ' . $billsReturnId . '
                        and p.deleted =' . $deleted . '   ';

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

    public function queryByBillIdAndProductidAndDeletedAndbillsReturnId($billId, $billProductId, $productId, $deleted, $billsReturnId) {
        $sql = 'SELECT   p.*
                        FROM billsreturnproducts p
                        join  billsreturn b             on b.id = p.returnbillid

                        WHERE b.billid =' . $billId . '
                        and p.productid =' . $productId . '
                        and p.billproductid =' . $billProductId . '
                        and p.returnbillid = ' . $billsReturnId . '
                        and p.deleted =' . $deleted . '   ';

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

    public function queryByProductidAndDeletedAndbillsReturnId($productId, $deleted, $billsReturnId) {
        $sql = 'SELECT *  FROM billsreturnproducts
                        WHERE  productid =' . $productId . ' and returnbillid = ' . $billsReturnId . ' and deleted =' . $deleted . '   ';

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

    public function queryByProductidAndDeletedAndbillsReturnIdAndbillsproductId($productId, $deleted, $billsReturnId, $billsProductId) {
        $sql = 'SELECT *  FROM billsreturnproducts
                        WHERE  productid =' . $productId . ' and returnbillid = ' . $billsReturnId . ' and deleted =' . $deleted . '  and billproductid =' . $billsProductId . '     ';

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

    public function getNoOfReturnedProductsWithbillsReturnId($billId, $deleted, $billsReturnId) {
        $sql = 'SELECT    sum(productno) as  productno  FROM billsreturnproducts
                        WHERE  returnbillid = ' . $billsReturnId . '  and deleted =' . $deleted . '   ';

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

    public function queryAllGeneral($queryString) {
        $sql = 'SELECT billsreturnproducts.*,bills.clientid,client.clientname,product.productName as productName,product.productBuyPrice as deleted,billsreturn.billid as billproductid,productcat.productCatName,product.productCatId
                FROM billsreturnproducts
                left join billsreturn on  billsreturn.id = billsreturnproducts.returnbillid
                left join bills on bills.id = billsreturn.billid
                left join client on client.clientid = bills.clientid
                left join product on product.productId = billsreturnproducts.productid
                left join productcat on productcat.productCatId = product.productCatId
                ' . $queryString . '
                AND billsreturn.deleted=0 order by billsreturnproducts.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function billReturnWithProducts($queryStringBillReturn) {
        $sql = 'SELECT billsreturnproducts.*,product.productName as productName,product.productBuyPrice as deleted,billsreturn.billid as billproductid
                FROM billsreturnproducts
                join  billsreturn on  billsreturn.id = billsreturnproducts.returnbillid
                join  bills on  bills.id = billsreturn.billid
                join client on client.clientid = bills.clientid
                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 = billsreturnproducts.productid
		WHERE 1 ' . $queryStringBillReturn . ' AND billsreturn.deleted = 0 ';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneralPriceType($queryString) {
        $sql = 'SELECT billsreturnproducts.*,product.productName as productName,product.productBuyPrice as deleted,billsreturn.billid as billproductid
                FROM billsreturnproducts
                join  billsreturn on  billsreturn.id = billsreturnproducts.returnbillid
                join  bills on  bills.id = billsreturn.billid
                join product on product.productId = billsreturnproducts.productid
                ' . $queryString . '
                AND billsreturn.deleted=0 order by billsreturnproducts.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneral2($queryString, $clientid) {
        $sql = 'SELECT client.clientname as clientname , billsreturnproducts.*,product.productName as productName,product.productBuyPrice as deleted,billsreturn.billid as billproductid FROM billsreturnproducts
                       join  billsreturn on  billsreturn.id = billsreturnproducts.returnbillid
                       join product on product.productId = billsreturnproducts.productid
                       join bills on bills.id = billsreturn.billid
                       join client on client.clientid = bills.clientid  ' . $queryString . ' AND client.clientid  = ' . $clientid . '
                       AND billsreturn.deleted=0 order by billsreturnproducts.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneral3($queryString, $productid) {
        $sql = 'SELECT client.clientname as clientname , client.clientid as clientid ,  billsreturnproducts.*,product.productName as productName,product.productBuyPrice as deleted,billsreturn.billid as billproductid FROM billsreturnproducts join  billsreturn on  billsreturn.id = billsreturnproducts.returnbillid join product on product.productId = billsreturnproducts.productid  join bills on bills.id = billsreturn.billid join client on client.clientid = bills.clientid ' . $queryString . ' AND product.productId  = ' . $productid . '  AND billsreturn.deleted=0 order by billsreturnproducts.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllGeneralSimple($queryString) {
        $sql = 'SELECT billsreturnproducts.productno,billsreturn.id,billsreturn.`date` as billproductid,clientname as deleted
                FROM billsreturnproducts
                join  billsreturn on  billsreturn.id = billsreturnproducts.returnbillid
                JOIN bills ON billsreturn.billid = bills.id
                join client on bills.clientid = client.clientid
                ' . $queryString . '
                AND billsreturn.deleted=0 order by billsreturnproducts.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryStoreAndBillsReturnProducts($queryString) {
        $sql = 'SELECT

                    billsreturnproducts.returnbillid,
                    billsreturnproducts.productid,
                    billsreturnproducts.productno

                    FROM billsreturnproducts
                     JOIN storereport
                     ON storereport.storereportmodelid = billsreturnproducts.returnbillid
                     where storereport.storereporttype = 0 ' . $queryString . ' ';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $productid) {
        $sql = 'SELECT
		 billsreturnproducts.productno,
		 billsreturnproducts.productprice,
		 billsreturnproducts.producttotalprice,
                 billsreturnproducts.lastbuyprice,
                 billsreturnproducts.meanbuyprice,
		 billsreturn.returnedprice,
                 product.productBuyPrice
		 FROM billsreturnproducts
                 JOIN billsreturn
		 ON billsreturnproducts.returnbillid = billsreturn.id
                 JOIN product
		 ON billsreturnproducts.productid = product.productId
		 where billsreturn.deleted = 0
		 and billsreturn.date >= "' . $startDate . '" and billsreturn.date <= "' . $endDate . '"
		 and billsreturnproducts.productid in(' . $productid . ')';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $startDate, $endDate) {
        $sql = 'SELECT billsreturnproducts.productprice as sellbilldetailprice,bills.billdate as sellbilldetaildate,3 as billtype,
                   product.productName ,
                   productcat.productCatName, billsreturn.id
		   FROM billsreturnproducts
                   JOIN billsreturn
		  ON billsreturnproducts.returnbillid = billsreturn.id
                  JOIN bills
		  ON billsreturn.billid = bills.id
		 JOIN product
		 ON billsreturnproducts.productid = product.productId
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
		 WHERE billsreturnproducts.productid = ' . $ProductId . '
		 AND bills.clientid = ' . $ClientId . '
		 AND bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"
                 AND billsreturn.deleted=0
		 AND billsreturnproducts.deleted = 0
		 order by billsreturnproducts.id desc';

        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithReturnsellbillIdforproft($returnbillid) {
        $sql = 'SELECT billsreturnproducts.*, product.productName, product.productBuyPrice,productcat.productCatName,productcat.discounttype,productcat.buydiscount
		 FROM billsreturnproducts
                 JOIN product ON billsreturnproducts.productid = product.productId
		 join productcat on productcat.productCatId=product.productCatId
		 WHERE billsreturnproducts.returnbillid = ' . $returnbillid . '';

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

    public function updatePrices_f_optic($lastbuyprice, $meanbuyprice, $detailId) {
        $sql = 'UPDATE billsreturnproducts SET  lastbuyprice = ' . $lastbuyprice . ',meanbuyprice = ' . $meanbuyprice . '
                where id = ' . $detailId . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function load_billproducts_and_bills_by_productid($where) {
        $sql = 'SELECT billsreturnproducts.productprice as productprice,billsreturnproducts.productno as productno , billsreturnproducts.lastbuyprice as lastbuyprice ,
                        billsreturn.returnedprice as returnedprice,bills.netbillvalue as netbillvalue,bills.discountvalue as discountvalue
                        , product.productBuyPrice as productBuyPrice
		FROM billsreturnproducts
                JOIN billsreturn ON billsreturnproducts.returnbillid = billsreturn.id
                JOIN bills ON billsreturn.billid = bills.id
                JOIN product ON product.productId = billsreturnproducts.productid

                WHERE ' . $where . '
	        AND billsreturnproducts.deleted = 0
		order by billsreturnproducts.id desc';

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

    public function get_total_return_sellcost($startDate, $endDate) {
        $sql = 'SELECT  billsreturnproducts.productid as productid , billsreturnproducts.productno as productno , product.productBuyPrice as productBuyPrice ,
                       billsreturnproducts.lastbuyprice as lastbuyprice , billsreturnproducts.meanbuyprice as meanbuyprice
		FROM billsreturnproducts
                JOIN billsreturn ON billsreturnproducts.returnbillid = billsreturn.id
                JOIN product ON product.productId = billsreturnproducts.productid

                WHERE date(billsreturn.date) >= "' . $startDate . '" and date(billsreturn.date) <= "' . $endDate . '"
	        AND billsreturnproducts.deleted = 0
		order by billsreturnproducts.id desc';

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

    //# m7md 21/11/2017


    public function queryWithProductIdAndClientIdAndUnitExtNew($where) {
        $sql = 'SELECT billsreturnproducts.productprice as sellbilldetailprice,bills.billdate as sellbilldetaildate,3 as billtype,
                   product.productName ,
                   productcat.productCatName, billsreturn.id , client.clientname , billsreturnproducts.productno as quantity
		   FROM billsreturnproducts
                   JOIN billsreturn
		  ON billsreturnproducts.returnbillid = billsreturn.id
                  JOIN bills
		  ON billsreturn.billid = bills.id
		 JOIN product
		 ON billsreturnproducts.productid = product.productId
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
                 join client on client.clientid = bills.clientid

		 WHERE ' . $where . '
                  billsreturn.deleted=0
		 AND billsreturnproducts.deleted = 0
		 order by billsreturnproducts.id desc';

        $sqlQuery = new SqlQuery($sql);

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

}

?>
