<?php

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

    public function getSellBillsUsingQueryString($queryString1) {
        $sql = 'SELECT returnbuybill.*
		        FROM returnbuybill
				' . $queryString1 . '
				AND returnbuybill.deletedbuyid=0 ';


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

    public function insertEX($returnbuybill) {
        $sql = 'INSERT INTO returnbuybill (returnbuybillSerial, returnbuybilldate, returnbuybillsupplierid, returnbuybilltotaldeptbefor, returnbuybilltotaldeptafter, returnbuybilldiscount, returnbuybilldiscountrype, returnbuybilltotalbill, returnbuybillaftertotalbill, returnbuybilltotalpayed, returnbuybillfinalbill, returnbuybillstoreid, returnbuybillsysdate,comment, userid, conditions, returnbuybillclientname, returnbuybillsaveid, billnameid, returnbuybilldirectpayment,deletedbuyid,controlname,payedtax,costcenterid,currencyId,taxOfDiscountPer,taxOfDiscountVal,client) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? ,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($returnbuybill->returnbuybillSerial);
        $sqlQuery->set($returnbuybill->returnbuybilldate);
        $sqlQuery->setNumber($returnbuybill->returnbuybillsupplierid);
        $sqlQuery->set($returnbuybill->returnbuybilltotaldeptbefor);
        $sqlQuery->set($returnbuybill->returnbuybilltotaldeptafter);
        $sqlQuery->set($returnbuybill->returnbuybilldiscount);
        $sqlQuery->setNumber($returnbuybill->returnbuybilldiscountrype);
        $sqlQuery->set($returnbuybill->returnbuybilltotalbill);
        $sqlQuery->set($returnbuybill->returnbuybillaftertotalbill);
        $sqlQuery->set($returnbuybill->returnbuybilltotalpayed);
        $sqlQuery->set($returnbuybill->returnbuybillfinalbill);
        $sqlQuery->setNumber($returnbuybill->returnbuybillstoreid);
        $sqlQuery->set($returnbuybill->returnbuybillsysdate);
        $sqlQuery->set($returnbuybill->comment);
        $sqlQuery->setNumber($returnbuybill->userid);
        $sqlQuery->setNumber($returnbuybill->conditions);
        $sqlQuery->set($returnbuybill->returnbuybillclientname);
        $sqlQuery->setNumber($returnbuybill->returnbuybillsaveid);
        $sqlQuery->setNumber($returnbuybill->billnameid);
        $sqlQuery->setNumber($returnbuybill->returnbuybilldirectpayment);

        $sqlQuery->setNumber($returnbuybill->deletedbuyid);
        $sqlQuery->set($returnbuybill->controlname);
        $sqlQuery->set($returnbuybill->payedtax);
        $sqlQuery->set($returnbuybill->costcenterid);
        $sqlQuery->set((int) $returnbuybill->currencyId);
        $sqlQuery->setNumber((float) $returnbuybill->taxOfDiscountPer);
        $sqlQuery->setNumber((float) $returnbuybill->taxOfDiscountVal);
        $sqlQuery->setNumber((int) $returnbuybill->client);



        $id = $this->executeInsert($sqlQuery);
        $returnbuybill->returnbuybillid = $id;
        return $id;
    }

    public function queryAllSerials() {
        $sql = 'SELECT returnbuybill.returnbuybillSerial, returnbuybillid
		 FROM returnbuybill
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllSerialsNotDeleted() {
        $sql = 'SELECT returnbuybill.returnbuybillSerial, returnbuybillid
		 FROM returnbuybill
		 WHERE conditions = 0
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadExt($id) {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 WHERE returnbuybillid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    public function loadExt2($id, $queryString = '') {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName,delbyuser.username as delbyusername
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
                 LEFT JOIN  user as delbyuser
		 ON delbyuser.userid = returnbuybill.delbyuserid

		 WHERE returnbuybillid = ?   ' . $queryString . '
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getList($sqlQuery);
    }

    public function queryByConditionExt($value) {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
		 WHERE returnbuybill.conditions = ?
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByConditionExtLimited($value, $start, $end) {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
		 FROM returnbuybill LEFT JOIN supplier
		ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
		 WHERE returnbuybill.conditions = ?
		 ORDER BY returnbuybillid DESC
		 limit ' . $start . ', ' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByDateExt($from, $to, $queryString = '') {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
                 LEFT JOIN  user as delbyuser
		 ON delbyuser.userid = returnbuybill.delbyuserid

		 WHERE returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"  ' . $queryString . '
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBySupplierId($value, $queryString = '') {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
                   LEFT JOIN  user as delbyuser
		 ON delbyuser.userid = returnbuybill.delbyuserid

		 WHERE returnbuybillsupplierid = ?  ' . $queryString . '
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryBySupplierIdLimited($value, $start, $end) {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
		 WHERE returnbuybillsupplierid = ?
		 ORDER BY returnbuybillid DESC
		 limit ' . $start . ', ' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryBySerial($value, $queryString = '') {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
                 LEFT JOIN  user as delbyuser
		 ON delbyuser.userid = returnbuybill.delbyuserid

		 WHERE returnbuybillSerial = ?  ' . $queryString . '
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryBySerialLimited($value, $start, $end) {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
		 WHERE returnbuybillSerial = ?
		 ORDER BY returnbuybillid DESC
		 limit ' . $start . ', ' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function updateConditions($returnbuybill) {
        $sql = 'UPDATE returnbuybill SET  returnbuybillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? WHERE returnbuybillid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($returnbuybill->returnbuybillsysdate);
        $sqlQuery->setNumber($returnbuybill->userid);
        $sqlQuery->setNumber($returnbuybill->delbyuserid);
        $sqlQuery->setNumber($returnbuybill->conditions);

        $sqlQuery->setNumber($returnbuybill->returnbuybillid);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryWithSupplierIdAndDate($supplierid, $from, $to) {
        $sql = 'SELECT returnbuybill.returnbuybillid, returnbuybillSerial, returnbuybilldate, returnbuybillaftertotalbill
		 FROM returnbuybill
		 LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid
		 WHERE returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"
		 AND returnbuybill.returnbuybillsupplierid = ' . $supplierid . '
		 AND returnbuybill.conditions = 0
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithSupplierIdAndDateNew($queryString) {
        $sql = 'SELECT returnbuybill.returnbuybillid, returnbuybill.comment, returnbuybillSerial, returnbuybilldate, returnbuybillaftertotalbill,returnbuybilltotalpayed
		 FROM returnbuybill
		 LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid
		 WHERE 1 ' . $queryString . '
		 AND returnbuybill.conditions = 0
		 ORDER BY returnbuybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBystring($queryString) {
        $sql = 'SELECT returnbuybill.*, supplier.suppliername, store.storeName, user.username
		 FROM returnbuybill LEFT JOIN supplier
		 ON returnbuybill.returnbuybillsupplierid = supplier.supplierid JOIN store
		 ON returnbuybill.returnbuybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = returnbuybill.userid
	' . $queryString . '
            AND returnbuybill.conditions=0
		 ORDER BY returnbuybillid DESC';


        // print_r($sql.'<br>');
        $sqlQuery = new SqlQuery($sql);

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

    public function queryAllGeneral($queryString) {
        $sql = 'SELECT  returnbuybill.* , s.suppliername as suppliername,u.employeename as username
		FROM returnbuybill
                        join supplier s    on returnbuybill.returnbuybillsupplierid = s.supplierid
                        join user u        ON u.userid = returnbuybill.userid
                        ' . $queryString . ' AND returnbuybill.conditions = 0
                 AND (returnbuybill.deletedbuyid=0 or returnbuybill.deletedbuyid is null)';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderSuppliersWithReturnBuybillsBuys($queryString) {
        $sql = 'SELECT returnbuybill.returnbuybillsupplierid , SUM(returnbuybill.returnbuybilltotalbill) as allGenTotalReturn , SUM(returnbuybill.returnbuybillaftertotalbill) as returnbuybillaftertotalbill ,
                COUNT(returnbuybill.returnbuybillsupplierid ) , supplier.suppliername as suppliername FROM `returnbuybill`
                join supplier on supplier.supplierid = returnbuybill.returnbuybillsupplierid

                ' . $queryString . ' GROUP BY returnbuybill.returnbuybillsupplierid
                ';
//        echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

}
