<?php

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

    public function updateDebt($supplierdebtchange)
    {
        $sql = 'UPDATE supplierdebtchange SET del = ? WHERE supplierdebtchangeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($supplierdebtchange->del);

        $sqlQuery->setNumber($supplierdebtchange->supplierdebtchangeid);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryPayedDeptOnly($startDate, $endDate, $queryString = '')
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		WHERE supplierdebtchange.tablename = "supplierPayedDeptController.php"
			AND date( supplierdebtchange.supplierdebtchangedate ) >= "' . $startDate . '" AND date( supplierdebtchange.supplierdebtchangedate ) <= "' . $endDate . '"
                            ' . $queryString . '
		order by supplierdebtchangeid desc ';

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

    public function queryPayedDeptAndPayedDeptInBills($queryString = '')
    {
        $sql = 'SELECT SUM(ABS(supplierdebtchange.supplierdebtchangeamount))
		FROM supplierdebtchange
		WHERE supplierdebtchange.del = 0 and (supplierdebtchange.tablename = "supplierPayedDeptController.php" or  (supplierdebtchange.tablename in("buyBillController.php","buyAndReturnBillController.php","returnBuyBillController.php") and supplierdebtchangeamount < 0) )
                            ' . $queryString . '
		order by supplierdebtchangeid desc ';

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

    public function queryPayedDeptOnlyLimited($startDate, $endDate, $startpage, $perpage)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		WHERE supplierdebtchange.tablename = "supplierPayedDeptController.php"
			AND date( supplierdebtchange.supplierdebtchangedate ) >= "' . $startDate . '" AND date( supplierdebtchange.supplierdebtchangedate ) <= "' . $endDate . '"
		order by supplierdebtchangeid desc
		limit ' . $startpage . ', ' . $perpage . '';
        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBySupplierIdAndDate($supplierid, $startDate, $endDate)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		WHERE supplierdebtchange.supplierid = ' . $supplierid . '
			AND date( supplierdebtchange.supplierdebtchangedate ) >= "' . $startDate . '" AND date( supplierdebtchange.supplierdebtchangedate ) <= "' . $endDate . '"
				and (tablename != "buyBillController.php" and  tablename != "returnBuyBillController.php" and tablename != "buyAndReturnBillController.php")
		order by supplierdebtchangeid desc ';

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

    public function queryBySupplierIdAndDateNew_f($queryString)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		WHERE 1 ' . $queryString . '
				and (tablename != "buyBillController.php" and  tablename != "returnBuyBillController.php" and tablename != "buyAndReturnBillController.php")
		order by supplierdebtchangeid desc ';

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

    public function queryBySupplierIdAndDate2($queryString, $orderBy = ' order by supplierdebtchangedate desc ,supplierdebtchangeid desc ')
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername , CONCAT(`tablename` ,  `supplierdebtchangemodelid` , supplierdebtchangeamount,`supplierdebtchangetype`) AS finalstring
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		' . $queryString . '

		' . $orderBy . ' '; //order by supplierdebtchangedate desc ,supplierdebtchangeid desc
        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBySupplierIdAndDatenew($queryString)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername , CONCAT(`tablename` ,  `supplierdebtchangemodelid` , supplierdebtchangeamount,`supplierdebtchangetype`) AS finalstring
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		' . $queryString . '

		order by  supplierdebtchangeid asc '; //supplierdebtchangedate asc ,
        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryBySupplierIdAndDateLimited($supplierid, $startDate, $endDate, $startpage, $perpage)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		WHERE supplierdebtchange.supplierid = ' . $supplierid . '
		AND date( supplierdebtchange.supplierdebtchangedate ) >= "' . $startDate . '" AND date( supplierdebtchange.supplierdebtchangedate ) <= "' . $endDate . '"
		order by supplierdebtchangeid desc
		limit ' . $startpage . ', ' . $perpage . '';
        //print_r($sql."<br>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithSupplierIdAndTableName($supplierid, $tablename)
    {

        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername,employeename

		 FROM supplierdebtchange JOIN supplier

		 ON supplierdebtchange.supplierid = supplier.supplierid
         join user on supplierdebtchange.userid = user.userid

		 where supplierdebtchange.supplierid =' . $supplierid . '

		 and  supplierdebtchange.tablename ="' . $tablename . '"

		 and  supplier.conditions=0

		 order by supplierdebtchangeid desc ';

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

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

    public function queryWithSupplierIdAndTableNameLimited($start, $end, $supplierid, $tablename)
    {

        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername

		 FROM supplierdebtchange JOIN supplier

		 ON supplierdebtchange.supplierid = supplier.supplierid

		 where supplierdebtchange.supplierid =' . $supplierid . '

		  and  supplierdebtchange.tablename ="' . $tablename . '"

		 and  supplier.conditions=0

		 order by supplierdebtchangeid desc limit ' . $start . ',' . $end . '';

        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithTableName($tablename)
    {

        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername

		 FROM supplierdebtchange JOIN supplier

		 ON supplierdebtchange.supplierid = supplier.supplierid

		 where supplier.conditions=0

		 and  supplierdebtchange.tablename ="' . $tablename . '"

		 order by supplierdebtchangeid desc ';

        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithTableNameLimited($start, $end, $tablename)
    {

        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername

		 FROM supplierdebtchange JOIN supplier

		 ON supplierdebtchange.supplierid = supplier.supplierid

		 where supplier.conditions=0

		 and  supplierdebtchange.tablename ="' . $tablename . '"

		 order by supplierdebtchangeid desc limit ' . $start . ',' . $end . '';

        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithDateAndTableName($startDate, $endDate, $tablename)
    {

        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername, employeename

		 FROM supplierdebtchange JOIN supplier

		 ON supplierdebtchange.supplierid = supplier.supplierid
            join user on supplierdebtchange.userid = user.userid

		 where supplierdebtchange.supplierdebtchangedate >= "' . $startDate . '"  and supplierdebtchange.supplierdebtchangedate <= "' . $endDate . '"

		 and  supplierdebtchange.tablename ="' . $tablename . '"

		 and  supplier.conditions=0

		 order by supplierdebtchangeid desc ';

        $sqlQuery = new SqlQuery($sql);

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

    public function queryWithDateAndTableNameLimited($start, $end, $startDate, $endDate, $tablename)
    {

        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername

		 FROM supplierdebtchange JOIN supplier

		 ON supplierdebtchange.supplierid = supplier.supplierid

		 where supplierdebtchange.supplierdebtchangedate >= "' . $startDate . '"  and supplierdebtchange.supplierdebtchangedate <= "' . $endDate . '"

		  and  supplierdebtchange.tablename ="' . $tablename . '"

		 and  supplier.conditions=0

		 order by supplierdebtchangeid desc limit ' . $start . ',' . $end . '';

        $sqlQuery = new SqlQuery($sql);

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

    public function searchPayedDeptOnly($querystring)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		 ' . $querystring . '
		and supplierdebtchange.tablename = "supplierPayedDeptController.php"

		order by supplierdebtchangeid desc ';

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

    public function getAllPayedDept()
    {
        $sql = 'SELECT supplierdebtchange.*
		FROM supplierdebtchange
		where supplierdebtchange.tablename = "supplierPayedDeptController.php"
		order by supplierdebtchangeid desc ';

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

    public function showbymodelid($modelid)
    {
        $sql = 'SELECT supplierdebtchange.*, client.clientname
		FROM supplierdebtchange
		JOIN client
		ON client.clientid  = supplierdebtchange.supplierid
		WHERE supplierdebtchange.tablename = "supplierPayedDeptController.php"
		AND supplierdebtchangemodelid =' . $modelid . '
		order by supplierdebtchangeid desc';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    ////////////////eman//////////////
    public function queryPayedDeptOnlyNew($startDate, $endDate, $queryString = '')
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername, user.username, save.savename
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
                JOIN user
                ON supplierdebtchange.userid = user.userid
                JOIN save
                ON supplierdebtchange.saveid = save.saveid
		WHERE supplierdebtchange.tablename = "supplierPayedDeptController.php"
			AND date( supplierdebtchange.supplierdebtchangedate ) >= "' . $startDate . '" AND date( supplierdebtchange.supplierdebtchangedate ) <= "' . $endDate . '"
                            ' . $queryString . '
		order by supplierdebtchangeid desc ';

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

    public function supplierShowAll($queryString = '')
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername, user.username
		FROM supplierdebtchange
		left JOIN supplier ON supplier.supplierid  = supplierdebtchange.supplierid
                left JOIN user ON supplierdebtchange.userid = user.userid
                where 1
		' . $queryString . '
		order by supplierdebtchangeid desc ';

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

    public function searchPayedDeptOnlyNew($querystring)
    {
        $sql = 'SELECT supplierdebtchange.*, supplier.suppliername, user.username, save.savename
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
                JOIN user
                ON supplierdebtchange.userid = user.userid
                JOIN save
                ON supplierdebtchange.saveid = save.saveid
		 ' . $querystring . '
		and supplierdebtchange.tablename = "supplierPayedDeptController.php"

		order by supplierdebtchangeid desc ';

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

    public function queryByComment($id)
    {
        $sql = " select supplierdebtchange.* from supplierdebtchange where comment = 'delRow$id'";
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByCostCenter($costcenterid)
    {
        $sql = 'SELECT * FROM `supplier`
       left join supplierdebtchange on supplier.supplierid = supplierdebtchange.supplierid
        WHERE supplierdebtchange.del = 0 and supplierdebtchange.costcenterid =
		' . $costcenterid . ' ';

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

    public function getlastInGeneral()
    {
        $sql = 'SELECT * FROM supplierdebtchange 	order by supplierdebtchangeid desc limit 1';

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

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

    public function queryPayedDeptAndPayedDeptInBillsWithoutABS($queryString = '')
    {
        $sql = 'SELECT SUM(supplierdebtchange.supplierdebtchangeamount)
		FROM supplierdebtchange
		WHERE supplierdebtchange.del = 0 and (supplierdebtchange.tablename = "supplierPayedDeptController.php" or  (supplierdebtchange.tablename in("buyBillController.php","buyAndReturnBillController.php","returnBuyBillController.php") and supplierdebtchangeamount < 0) )
                            ' . $queryString . '
		order by supplierdebtchangeid desc ';

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

    public function queryBySupplierIdNotDeleted($id, $queryString = '')
    {
        $sql = 'SELECT * FROM supplierdebtchange WHERE supplierid = ? and del != 1 ' . $queryString;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        // print($sql);
        return $this->getList($sqlQuery);
    }

    public function updateComment($comment, $id)
    {
        $sql = 'UPDATE supplierdebtchange SET comment = ? WHERE supplierdebtchangeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($comment);
        $sqlQuery->setNumber($id);
        return $this->executeUpdate($sqlQuery);
    }

    public function getSupplierName($supplierdebtchangeid)
    {
        $sql = 'SELECT supplier.suppliername
		FROM supplierdebtchange
		JOIN supplier
		ON supplier.supplierid  = supplierdebtchange.supplierid
		WHERE supplierdebtchange.supplierdebtchangeid = ' . $supplierdebtchangeid . '';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }
}
