<?php

/**
 * Class that operate on table 'billsproducts'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2016-06-21 11:00
 */
class BillsproductsMySqlExtDAO extends BillsproductsMySqlDAO {

    public function queryByBillIdAndDeleted($billId, $deleted) {
        $sql = 'SELECT * FROM billsproducts 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 billsproducts.*,product.productName as deleted ,productCatId as service
                FROM billsproducts
                join product on product.productId = billsproducts.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 billsproducts WHERE id = ' . $id;
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

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

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

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

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

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

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

                    billsproducts.billid,
                    billsproducts.productid,
                    billsproducts.productno

                    FROM billsproducts
                     JOIN storereport
                     ON storereport.storereportmodelid = billsproducts.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 billsproducts.productprice as sellbilldetailprice,bills.billdate as sellbilldetaildate,2 as billtype ,
                 product.productName,
                   productcat.productCatName, bills.id
		 FROM billsproducts
		 JOIN bills
		 ON billsproducts.billid = bills.id
		 JOIN product
		 ON billsproducts.productid = product.productId
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
		 WHERE billsproducts.productid = ' . $ProductId . '
		 AND bills.clientid = ' . $ClientId . '
		 AND bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"
		 AND billsproducts.deleted = 0
		 order by billsproducts.id desc';

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

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

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

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

    public function updatePrices_f_optic($lastbuyprice, $meanbuyprice, $detailId) {
        $sql = 'UPDATE billsproducts 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 billsproducts.productprice as productprice,billsproducts.productno as productno , billsproducts.lastbuyprice as lastbuyprice ,
                       billsproducts.billid,bills.netbillvalue as netbillvalue,bills.discountvalue as discountvalue  , product.productBuyPrice as productBuyPrice
		FROM billsproducts
                JOIN bills ON billsproducts.billid = bills.id
                JOIN product ON product.productId = billsproducts.productid

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

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

    public function get_total_sellcost($startDate, $endDate) {
        $sql = 'SELECT billsproducts.productid as productid , billsproducts.productno as productno , product.productBuyPrice as productBuyPrice,
                       billsproducts.lastbuyprice as lastbuyprice , billsproducts.meanbuyprice as meanbuyprice

		FROM billsproducts
                JOIN bills ON billsproducts.billid = bills.id
                JOIN product ON product.productId = billsproducts.productid

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

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

    public function deleteIDS($ids) {
        $sql = 'DELETE FROM billsproducts WHERE id in(' . $ids . ')';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    //# m7md 21/11/2017

    public function queryWithProductIdAndClientIdAndUnitExtNew($where) {
        $sql = 'SELECT billsproducts.productprice as sellbilldetailprice,bills.billdate as sellbilldetaildate,2 as billtype ,
                 product.productName,
                   productcat.productCatName, bills.id , client.clientname
		 FROM billsproducts
		 JOIN bills
		 ON billsproducts.billid = bills.id
		 JOIN product
		 ON billsproducts.productid = product.productId
		 JOIN productcat
		 ON productcat.productCatId=product.productCatId
                 join client on client.clientid = bills.clientid
		 WHERE ' . $where . '
		  billsproducts.deleted = 0
		 order by billsproducts.id desc';

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

}

?>