<?php

/**
 * Class that operate on table 'billsproductsbuy'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2017-02-20 09:04
 */
class BillsproductsbuyMySqlExtDAO extends BillsproductsbuyMySqlDAO {

    public function queryByBillIdAndDeleted($billId, $deleted) {
        $sql = 'SELECT * FROM billsproductsbuy WHERE billid = ? and deleted = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($billId);
        $sqlQuery->setNumber($deleted);
        return $this->getList($sqlQuery);
    }

    public function getproducts($billId, $deleted) {
        $sql = 'SELECT billsproductsbuy.*,product.productName as deleted ,productCatId as service
                FROM billsproductsbuy
                join product on product.productId = billsproductsbuy.productid
                WHERE billid = ? and deleted = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($billId);
        $sqlQuery->setNumber($deleted);
        return $this->getList($sqlQuery);
    }

    public function loadProduct($id) {
        $sql = 'SELECT * FROM billsproductsbuy WHERE id = ' . $id;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllGeneral($queryString) {
        $sql = 'SELECT billsproductsbuy.*,product.productName as productName,product.productBuyPrice as deleted,productcat.productCatName,product.productCatId,product.logo
                FROM billsproductsbuy
                join billsbuy on billsbuy.id = billsproductsbuy.billid
                join product on product.productId = billsproductsbuy.productid
                join productcat on productcat.productCatId = product.productCatId
                ' . $queryString . '
                AND billsbuy.deleted=0 order by billsproductsbuy.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

    public function queryAllGeneralSimple($queryString) {
        $sql = 'SELECT billsproductsbuy.productno,billsbuy.id,billsbuy.billdate as note,suppliername as deleted
                FROM billsproductsbuy
                join billsbuy on billsbuy.id = billsproductsbuy.billid
                join supplier on billsbuy.clientid = supplier.supplierid
                ' . $queryString . '
                 AND billsbuy.deleted=0 order by billsproductsbuy.productid';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBillNoOfPecies($billid) {
        $sql = 'SELECT sum(billsproductsbuy.productno) as deleted
        FROM billsproductsbuy WHERE billid = ' . $billid;
        $sqlQuery = new SqlQuery($sql);
        //echo $sql;
        return $this->getRow($sqlQuery);
    }

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

                    billsproductsbuy.billid,
                    billsproductsbuy.productid,
                    billsproductsbuy.productno

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

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

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

    public function queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $productid) {
        $sql = 'SELECT
		billsproductsbuy.productno,
		billsproductsbuy.productprice,
		billsproductsbuy.producttotalprice,
                billsbuy.netbillvalue,
		billsbuy.discountvalue,
		billsbuy.discounttype,
                product.productBuyPrice,
                billsproductsbuy.lastbuyprice,
                billsproductsbuy.meanbuyprice

		FROM billsproductsbuy
		JOIN billsbuy
		ON billsproductsbuy.billid = billsbuy.id
                JOIN product
		ON billsproductsbuy.productid = product.productId
		where billsbuy.deleted = 0
		and date(billsbuy.billdate) >= "' . $startDate . '" and date(billsbuy.billdate) <= "' . $endDate . '"
		and billsproductsbuy.productid in(' . $productid . ')';
        $sqlQuery = new SqlQuery($sql);
//print('<br>'.$sql.'<br>');
        return $this->getList($sqlQuery);
    }

    public function queryWithSellBillIdforproft($id) {
        $sql = 'SELECT billsproductsbuy.*, product.productName, product.productBuyPrice,productcat.productCatName,productcat.buydiscount,productcat.discounttype
		 FROM billsproductsbuy
                 JOIN product ON billsproductsbuy.productid = product.productId
		 join productcat on productcat.productCatId=product.productCatId
		 WHERE billsproductsbuy.billid = ' . $id . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

}

?>