<?php

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

    public function getbuybilldetail($buybillid) {
        $sql = 'SELECT * FROM productserial
        where billid = ' . $buybillid . ' ';
        $sqlQuery = new SqlQuery($sql);
        //$sqlQuery->setNumber($value);
        //print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function queryAllByQueryString($queryString = '') {
        $sql = 'SELECT *
                FROM productserial
                where 1 ' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryLastWithProductId($productid) {
        $sql = 'SELECT * FROM productserial where productid =' . $productid . '
		order by productserailid desc limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryLastWithProductIdAndDon($productid, $don) {
        $sql = 'SELECT * FROM productserial where productid =' . $productid . '
		and don = ' . $don . ' and del = 0
		order by productserailid desc limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryLastWithProductIdAndDonExt($productid) {
        $sql = 'SELECT * FROM productserial where productid =' . $productid . '
		and don > 0 and del = 0
		limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function getProductAvailableParcodes($productid, $sizeid = 0, $colorId = 0, $storeId = 0) {
        $sql = 'SELECT * FROM productserial where productid =' . $productid . ' and sizeId = ' . $sizeid . ' and colorId=' . $colorId . ' and storeid=' . $storeId . '
		and don > 0 and del = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryLastWithProductIdAndSerial($productid, $parcode, $queryString = '') {
        $sql = 'SELECT * FROM productserial
		where productid =' . $productid . '
		AND serialnumber = "' . $parcode . '" ' . $queryString . '
		and del = 0 order by productserailid asc'; //and don>0
        //print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryWithProductIdandbillid($productid, $billid, $type, $buybilldetailid) {
//        $sql = 'SELECT productserial.*,soldserialproduct.quantity FROM productserial
//		left join soldserialproduct
//		on soldserialproduct.productserailid=productserial.productserailid
//
//		 where productid =' . $productid . '
//		and billid=' . $billid . ' and type=' . $type . ' and buybilldetailid=' . $buybilldetailid . '
//
//		order by productserial.productserailid  '; //group by soldserialproduct.productserailid
        $sql = 'SELECT productserial.*,soldserialproduct.quantity
                FROM productserial
		left join (
                    select productserailid,sum(soldserialproduct.quantity) as quantity
                    from soldserialproduct
                    where del=0
                    group by soldserialproduct.productserailid
                ) as soldserialproduct on soldserialproduct.productserailid=productserial.productserailid
		where productid =' . $productid . '
		and billid=' . $billid . ' and type=' . $type . ' and buybilldetailid=' . $buybilldetailid . '
		order by productserial.productserailid  '; //
        //print_r($sql . "<br>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function deleteBybillid($billid, $type, $queryString = '') {
        $sql = 'DELETE FROM productserial WHERE billid = ' . $billid . '
		and type =' . $type . ' ' . $queryString . '
		';
        $sqlQuery = new SqlQuery($sql);

        //print_r($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByQuerySting($queryString = '') {
        $sql = 'DELETE FROM productserial WHERE 1 ' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function updateDelBybillid($billid, $type, $del, $queryString = '') {
        $sql = 'update productserial set del= ' . $del . ' WHERE billid = ' . $billid . '
		and type =' . $type . ' ' . $queryString . '
		';
        $sqlQuery = new SqlQuery($sql);

        //print_r($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryWithbillid($billid, $type) {
        $sql = 'SELECT productserial.*,product.productName,product.productSellUnitPrice FROM productserial

		join product
		on product.productId=productserial.productid
		 where billid=' . $billid . ' and productserial.type=' . $type . '
		order by productserial.productserailid  ';

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

    public function queryallEx() {
        $sql = 'SELECT productserial.*,product.productName,product.productSellUnitPrice FROM productserial

			join product
			on product.productId=productserial.productid

			order by productserial.productserailid  ';

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

    public function queryallExbyid($productserailid) {
        $sql = 'SELECT productserial.*,product.productName, product.productSellUnitPrice
			, (soldserialproduct.sellbillid) as soldsellbillid, soldserialproduct.sellbilltype ,soldserialproduct.quantity, product.productCatId
			 FROM productserial

			join product
			on product.productId=productserial.productid
			left join soldserialproduct
			on soldserialproduct.productserailid=productserial.productserailid
			where productserial.productserailid=' . $productserailid . '
			order by productserial.productserailid  ';

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

    public function queryallExbyidExt($productserailid) {
        $sql = 'SELECT productserial.*,product.productName,product.productSellUnitPrice            FROM productserial
			JOIN product
			ON product.productId = productserial.productid
			WHERE productserial.productserailid = ' . $productserailid . '
			ORDER BY productserial.productserailid  ';

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

    public function updatedel($productserialid, $del) {
        $sql = 'UPDATE productserial SET del = ' . $del . '
		 WHERE productserailid = ' . $productserialid . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function delByBillid($billid) {
        $sql = 'UPDATE productserial SET del = 1
		 WHERE billid = ' . $billid . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function queryallExbydiff($condation, $num) {
        $sql = 'SELECT productserial.*,(DATEDIFF(
productserial.`enddate` ,  productserial.`startdate`)
) AS different,product.productName,product.productSellUnitPrice
,(soldserialproduct.sellbillid) as soldsellbillid,soldserialproduct.sellbilltype,soldserialproduct.quantity
FROM productserial
			left join soldserialproduct
			on soldserialproduct.productserailid=productserial.productserailid
			join product
			on product.productId=productserial.productid
			where (DATEDIFF(
productserial.`enddate` ,  productserial.`startdate`
) ) ' . $condation . '   ' . $num . '
			order by productserial.productserailid  ';

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

    public function queryallExbydifflimted($start, $end, $condation, $num) {
        $sql = 'SELECT productserial.*,DATEDIFF(
productserial.`enddate`, productserial.`startdate`
) AS different,product.productName,product.productSellUnitPrice FROM productserial

			join product
			on product.productId=productserial.productid
			where DATEDIFF(
productserial.`enddate` , productserial.`startdate`
) ' . $condation . '   ' . $num . '
			order by productserial.productserailid  desc
			 limit ' . $start . ', ' . $end . '';

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

    public function queryallExbydiffExt($condation, $num) {
        $sql = 'SELECT productserial.*, DATEDIFF(productserial.`enddate`, productserial.`startdate`) AS different, product.productName, product.productSellUnitPrice, product.productCatId FROM productserial

			join product
			on product.productId = productserial.productid
			where DATEDIFF(productserial.`enddate`, productserial.`startdate`) ' . $condation . '   ' . $num . '
			order by productserial.productserailid  desc';

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

    public function queryallExbydiffExt2($condation, $num, $queryString) {
        $sql = 'SELECT productserial.*, DATEDIFF(productserial.`enddate`,NOW()) AS different, product.productName, product.productSellUnitPrice, product.productCatId FROM productserial

			join product
			on product.productId = productserial.productid
			where DATEDIFF(productserial.`enddate`,NOW()) ' . $condation . '   ' . $num . '
                            ' . $queryString . '
			order by productserial.productserailid  desc';

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

    public function updateDon($productserial) {
        $sql = 'UPDATE productserial SET don = ? WHERE productserailid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($productserial->don);

        $sqlQuery->setNumber($productserial->productserailid);
        return $this->executeUpdate($sqlQuery);
    }

    public function updateDonExt($productserial) {
        $sql = 'UPDATE productserial SET don = ?, sellbillid = ? WHERE productserailid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($productserial->don);
        $sqlQuery->setNumber($productserial->sellbillid);

        $sqlQuery->setNumber($productserial->productserailid);
        return $this->executeUpdate($sqlQuery);
    }

    public function getProductQuantity($productserailId) {
        $sql = 'SELECT productserial.don
			FROM productserial
			WHERE productserailid = ' . $productserailId . '';
        $sqlQuery = new SqlQuery($sql);

        //print_r('<br>'.$sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllbyserial($serialnumber) {
        $sql = 'SELECT * FROM productserial where serialnumber=' . $serialnumber . '

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

    public function queryallisdSerial($productid, $parcode, $queryString = '') {
        $sql = 'SELECT * FROM productserial
		where productid =' . $productid . '
		AND serialnumber = "' . $parcode . '" ' . $queryString . '
		AND del = 0';

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

    public function queryAllbybillandtype($billid, $type) {
        $sql = 'SELECT * FROM `productserial` WHERE
				`productserailid` in
				(SELECT  `productserailid` FROM  `soldserialproduct` )
				and `billid`= ' . $billid . ' and `type`=' . $type . '  ';


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

    public function queryallbuyandsellbill($productid, $serialnumber) {
        $sql = 'SELECT productserial. * , sellbill.sellbillid, sellbill.sellbilldate,
		sellbillandrutern.sellbillid, sellbillandrutern.sellbilldate,
		buyandruternbill.buybilldate, buybill.buybilldate, buyandruternbill.buybillid, buybill.buybillid
		,product.productName,product.expireDate
		FROM productserial
		join product
		on productserial.productid=product.productId
		LEFT JOIN sellbill ON sellbill.sellbillid = productserial.`sellbillid`
		LEFT JOIN sellbillandrutern ON sellbillandrutern.sellbillid = productserial.`sellbillid`
		LEFT JOIN buybill ON buybill.buybillid = productserial.`billid`
		LEFT JOIN buyandruternbill ON buyandruternbill.buybillid = productserial.`billid`
		WHERE productserial.productid =' . $productid . '
		and productserial.serialnumber =' . $serialnumber . '
		and productserial.del =0
		ORDER BY productserial.productserailid  ';

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

    public function queryBydel($value) {
        $sql = 'SELECT * FROM productserial WHERE del = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function loadserial($id) {
        $sql = 'SELECT * FROM productserial WHERE productserailid = ?
		and del =0';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($id);
        return $this->getRow($sqlQuery);
    }

    public function getProductIdWithItsSerial($value) {
        $sql = 'SELECT productid FROM productserial WHERE serialnumber = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->querySingleResult($sqlQuery);
    }

    public function updateStoreByQueryString($storeid, $moreUpdateColums = '', $queryString = '') {
        $sql = 'UPDATE productserial SET storeid = ' . $storeid . ' ' . $moreUpdateColums . '
		 WHERE 1 ' . $queryString . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function queryByQueryString($queryString = '') {
        $sql = 'SELECT productserial.* ,storeName,productCatName,productName,product.productDescription,size.name as sizeName,color.name as colorName,product.productSellAllPrice,product.productSellUnitPrice ,product.productSellHalfPrice
                FROM productserial
                join product on productserial.productid=product.productId
                join productcat on product.productCatId=productcat.productCatId
                join store on productserial.storeid=store.storeId

                left join sizecolor as size on size.id = productserial.sizeid
                left join sizecolor as color on color.id = productserial.colorid
                where don > 0 and del = 0 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByQueryStringSimple($queryString = '') {
        $sql = 'SELECT productserial.* ,storeName
                FROM productserial
                join store on productserial.storeid=store.storeId
                where don > 0 and del = 0 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByserialnumber_Store_ProductAndExpireDateNotDel($serialnumber, $productid, $sizeid, $colorid, $storeid, $enddate) {
        $sql = 'SELECT * FROM productserial WHERE serialnumber = "' . $serialnumber . '" and productid =' . $productid . '  and sizeid =' . $sizeid . ' and colorid =' . $colorid . ' and storeid=' . $storeid . '  and enddate ="' . $enddate . '" and del= 0';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}

?>