<?php

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

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


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

    public function querylastid($tabelname) {
        $sql = 'SELECT  `buybillid` FROM   ' . $tabelname . ' ORDER BY  `buybillid` DESC  LIMIT 1';

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

    public function insertEX($buybill) {
        $sql = 'INSERT INTO buybill (buybillSerial, buybilldate, buybillsupplierid, buybilltotaldeptbefor, buybilltotaldeptafter, buybilldiscount, buybilldiscountrype, buybilltotalbill, buybillaftertotalbill, buybilltotalpayed, buybillfinalbill, buybillstoreid, buybillsysdate,comment, userid, conditions, buybillclientname, buybillsaveid, buybilldirectpayment, billnameid,deletedbuyid,controlname,payedtax,costcenterid,currencyId,taxOfDiscountPer,taxOfDiscountVal,files,client,totalBuyDiscountVal,webApiId,buybilladdition,buybilladditiontrype,tagids) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($buybill->buybillSerial);
        $sqlQuery->set($buybill->buybilldate);
        $sqlQuery->setNumber($buybill->buybillsupplierid);
        $sqlQuery->set($buybill->buybilltotaldeptbefor);
        $sqlQuery->set($buybill->buybilltotaldeptafter);
        $sqlQuery->set($buybill->buybilldiscount);
        $sqlQuery->setNumber($buybill->buybilldiscountrype);
        $sqlQuery->set($buybill->buybilltotalbill);
        $sqlQuery->set($buybill->buybillaftertotalbill);
        $sqlQuery->set($buybill->buybilltotalpayed);
        $sqlQuery->set($buybill->buybillfinalbill);
        $sqlQuery->setNumber($buybill->buybillstoreid);
        $sqlQuery->set($buybill->buybillsysdate);
        $sqlQuery->set($buybill->comment);
        $sqlQuery->setNumber($buybill->userid);
        $sqlQuery->setNumber($buybill->conditions);
        $sqlQuery->set($buybill->buybillclientname);
        $sqlQuery->setNumber($buybill->buybillsaveid);
        $sqlQuery->setNumber($buybill->buybilldirectpayment);
        $sqlQuery->setNumber($buybill->billnameid);

        $sqlQuery->setNumber($buybill->deletedbuyid);
        $sqlQuery->set($buybill->controlname);
        $sqlQuery->set($buybill->payedtax);
        $sqlQuery->set($buybill->costcenterid);
        $sqlQuery->set((int) $buybill->currencyId);
        $sqlQuery->setNumber((float) $buybill->taxOfDiscountPer);
        $sqlQuery->setNumber((float) $buybill->taxOfDiscountVal);
        $sqlQuery->set($buybill->files);
        $sqlQuery->setNumber((float) $buybill->client);
        $sqlQuery->setNumber((float) $buybill->totalBuyDiscountVal);
        $sqlQuery->setNumber((int) $buybill->webApiId);
        $sqlQuery->setNumber((float) $buybill->buybilladdition);
        $sqlQuery->setNumber((int) $buybill->buybilladditiontrype);
        $sqlQuery->set($buybill->tagids);




        $id = $this->executeInsert($sqlQuery);
        $buybill->buybillid = $id;
        return $id;
    }

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

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

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

    public function queryAllSerials() {
        $sql = 'SELECT buybill.buybillSerial, buybill.buybillid
		 FROM buybill
		 ORDER BY buybillid ASC';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

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

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

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

    public function queryWithSupplierIdAndDate($supplierId, $from, $to) {
        $sql = 'SELECT buybill.buybillid, buybillSerial, buybilldate, buybillaftertotalbill
		 FROM buybill
		 LEFT JOIN supplier
		 ON buybill.buybillsupplierid = supplier.supplierid
		 WHERE buybill.buybilldate >= "' . $from . '" AND buybill.buybilldate <= "' . $to . '"
		 AND buybill.buybillsupplierid = ' . $supplierId . '
		 AND buybill.conditions = 0
		 ORDER BY buybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithSupplierIdAndDateNew($queryString) {
        $sql = 'SELECT buybill.buybillid, buybill.comment, buybillSerial, buybilldate, buybillaftertotalbill,buybilltotalpayed
		 FROM buybill
		 LEFT JOIN supplier
		 ON buybill.buybillsupplierid = supplier.supplierid
		 WHERE 1 ' . $queryString . '
		 AND buybill.conditions = 0
		 ORDER BY buybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

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

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

    public function queryBySupplierId($value, $queryString = '') {
        $sql = 'SELECT buybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
		 FROM buybill LEFT JOIN supplier
		 ON buybill.buybillsupplierid = supplier.supplierid JOIN store
		 ON buybill.buybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = buybill.userid
                 LEFT JOIN  user as delbyuser
		 ON delbyuser.userid = buybill.delbyuserid
		 WHERE buybillsupplierid = ? ' . $queryString . '
		 ORDER BY buybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByStoreId($value, $queryString = '') {
        $sql = 'SELECT buybill.*, supplier.suppliername, store.storeName, user.username,delbyuser.username as delbyusername
		 FROM buybill LEFT JOIN supplier
		 ON buybill.buybillsupplierid = supplier.supplierid JOIN store
		 ON buybill.buybillstoreid = store.storeId
		 LEFT JOIN  user
		 ON user.userid = buybill.userid
                 LEFT JOIN  user as delbyuser
		 ON delbyuser.userid = buybill.delbyuserid
		 WHERE buybillstoreid = ? ' . $queryString . '
		 ORDER BY buybillid DESC';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

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

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

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

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

    public function updateConditions($buybill) {
        $sql = 'UPDATE buybill SET  buybillsysdate = ?, userid = ?,delbyuserid = ?, conditions = ? WHERE buybillid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($buybill->buybillsysdate);
        $sqlQuery->setNumber($buybill->userid);
        $sqlQuery->setNumber($buybill->delbyuserid);
        $sqlQuery->setNumber($buybill->conditions);

        $sqlQuery->setNumber($buybill->buybillid);
        return $this->executeUpdate($sqlQuery);
    }

    public function executeSQLQuery($sql) {
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

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


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

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

    public function getAutoIncrementValue($DBName) {
        $sql = 'SELECT `AUTO_INCREMENT`
                FROM  INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = "' . $DBName . '"
                AND   TABLE_NAME   = "buybill" ';

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

    public function queryAllGeneral_1($queryString, $sellerid) {

        $sql = 'SELECT  buybill.* , s.suppliername as suppliername,u.employeename as username
		FROM buybill
                        JOIN supplier s  ON buybill.buybillsupplierid = s.supplierid
                        JOIN  user u	 ON u.userid = buybill.userid
                        ' . $queryString . ' AND buybill.conditions = 0';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function orderSuppliersWithBuybillsBuys($queryString) {
        $sql = 'SELECT buybill.buybillsupplierid , SUM(buybill.buybilltotalbill) as allGenTotal ,  SUM(buybill.buybillaftertotalbill) as buybillaftertotalbill ,
                COUNT(buybill.buybillsupplierid ) , supplier.suppliername as suppliername , supplier.suppliercurrentDebt as supplierdebt FROM `buybill`
                join supplier on supplier.supplierid = buybill.buybillsupplierid

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

}

?>