<?php

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

    public function queryByExpensetypeidExt($expensestypeid) {
        $sql = 'SELECT expenses.*, expensestype.expensestypename,expensestype.conditions as expensestypeConditions
		FROM expenses
		join expensestype
		on expensestype.expensestypeid = expenses.expensestypeid
		WHERE expenses.expensestypeid = ' . $expensestypeid . '

		order by expensesid desc'; //and  expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByQueryString($queryString) {
        $sql = 'SELECT *
		FROM expenses
		WHERE 1 ' . $queryString . '

		order by expensesid desc'; //and  expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function checkForExpenses($expensestypeid) {
        $sql = 'SELECT expenses.*, expensestype.expensestypename
		FROM expenses
		join expensestype
		on expensestype.expensestypeid = expenses.expensestypeid
		WHERE expenses.expensestypeid = ' . $expensestypeid . '
		and  expenses.conditions = 0
		order by expensesid desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByExpensetypeidlimted($start, $end, $expensestypeid) {
        $sql = 'SELECT expenses.*, expensestype.expensestypename
		FROM expenses
		join expensestype
		on expensestype.expensestypeid = expenses.expensestypeid
		WHERE expenses.expensestypeid = ' . $expensestypeid . '
		and  expensestype.conditions = 0
		order by expensesid desc
		limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByExpensetypeidForShow($expensestypeid, $queryString = '') {
        $sql = 'SELECT expenses.*, expensestype.expensestypename,expensestype.addOnlyGroupIds,productName,name,save.savename, client.clientname  as clientid ,supplier.suppliername as  supplier
		FROM expenses
		join expensestype
		on expensestype.expensestypeid = expenses.expensestypeid
        left join product
        on expenses.productid = product.productid
        left join client on expenses.clientid = client.clientid
        left join supplier on expenses.supplier = supplier.supplierid
        left join save
        on expenses.saveid = save.saveid
        left join costcenter
        on expenses.costcenterid = costcenter.id
		WHERE expenses.expensestypeid = ' . $expensestypeid . '
                    ' . $queryString . '
		and  expensestype.conditions = 0
		order by expensesid desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function getsumwithtype($start, $end, $expensestypeid) {
        $sql = 'SELECT  sum(expensesValue) as expensesValue  FROM expenses WHERE expensestypeid =' . $expensestypeid . '  order by expensesid desc
		 limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function getsum($start, $end) {
        $sql = 'SELECT sum(expensesValue) as expensesValue  FROM expenses
		order by expensesid desc limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllexpense() {
        $sql = 'SELECT expensestype.expensestypename, expenses.*,expensestype.conditions as expensestypeConditions,productName,name
				FROM expenses
				join expensestype
				on expensestype.expensestypeid = expenses.expensestypeid
                left join product
                on expenses.productid = product.productid
                left join costcenter
                on expenses.costcenterid = costcenter.id

				order by expensesid desc'; //where expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAlllimted($start, $end) {
        $sql = 'SELECT expensestype.expensestypename, expenses.*
				FROM expenses
				join expensestype
				on expensestype.expensestypeid=expenses.expensestypeid
				where  expensestype.conditions=0
				order by expensesid desc
				limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithDate($from, $to, $queryString = '') {
        $sql = 'SELECT expensestype.expensestypename,expensestype.addOnlyGroupIds, expenses.*,expensestype.conditions as expensestypeConditions,productName,name,save.savename, client.clientname as clientid ,supplier.suppliername as  supplier
				FROM expenses
				join expensestype
				on expensestype.expensestypeid = expenses.expensestypeid
                left join product
                on expenses.productid = product.productid
                left join client on expenses.clientid = client.clientid
                left join supplier on expenses.supplier = supplier.supplierid
                left join save on save.saveid = expenses.saveid
                left join costcenter
                on expenses.costcenterid = costcenter.id
				where  expenses.expensesdate >= "' . $from . '" AND expenses.expensesdate <= "' . $to . '"
                                    ' . $queryString . '
				order by expensesid desc'; //expensestype.conditions = 0

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

    public function queryAllWithDateLimited($from, $to, $start, $end) {
        $sql = 'SELECT expensestype.expensestypename, expenses.*
				FROM expenses
				join expensestype
				on expensestype.expensestypeid=expenses.expensestypeid
				where  expensestype.conditions=0
				AND expenses.expensesdate >= "' . $from . '" AND expenses.expensesdate <= "' . $to . '"
				order by expensesid desc
				limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithDateAndType($from, $to, $expensestypeid, $queryString = '') {
        $sql = 'SELECT expensestype.expensestypename,expensestype.addOnlyGroupIds, expenses.*,expensestype.conditions as expensestypeConditions,productName,name ,client.clientname  as clientid ,supplier.suppliername as  supplier
				FROM expenses
				join expensestype
				on expensestype.expensestypeid = expenses.expensestypeid
                left join product
                on expenses.productid = product.productid
                left join client
                on expenses.clientid = client.clientid
                left join supplier
                on expenses.supplier = supplier.supplierid
                left join costcenter
                on expenses.costcenterid = costcenter.id
				where expenses.expensestypeid = ' . $expensestypeid . '
				AND expenses.expensesdate >= "' . $from . '" AND expenses.expensesdate <= "' . $to . '"
                                    ' . $queryString . '
				order by expensesid desc'; //expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithId($id, $queryString = '') {
        $sql = 'SELECT expensestype.expensestypename,expensestype.addOnlyGroupIds, expenses.*,expensestype.conditions as expensestypeConditions,productName,name ,client.clientname  as clientid ,supplier.suppliername as  supplier
				FROM expenses
				join expensestype
				on expensestype.expensestypeid = expenses.expensestypeid
                left join product
                on expenses.productid = product.productid
                left join client
                on expenses.clientid = client.clientid
                left join supplier
                on expenses.supplier = supplier.supplierid
                left join costcenter
                on expenses.costcenterid = costcenter.id
				where expenses.expensesid = ' . $id . '
                                    ' . $queryString . '
				order by expensesid desc'; //expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithDateAndTypeLimited($from, $to, $expensestypeid, $start, $end) {
        $sql = 'SELECT expensestype.expensestypename, expenses.*
				FROM expenses
				join expensestype
				on expensestype.expensestypeid=expenses.expensestypeid
				where  expensestype.conditions=0
				AND expenses.expensestypeid = ' . $expensestypeid . '
				AND expenses.expensesdate >= "' . $from . '" AND expenses.expensesdate <= "' . $to . '"
				order by expensesid desc
				limit ' . $start . ',' . $end . '';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function deletetemp($expensesid) {
        $sql = 'UPDATE expenses SET conditions =1 WHERE expensesid =' . $expensesid . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function returndelete($expensesid) {
        $sql = 'UPDATE expenses SET conditions =0 WHERE expensesid =' . $expensesid . '';
        $sqlQuery = new SqlQuery($sql);

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

    public function updateExpense($expense) {
        $sql = 'UPDATE expenses SET expensesname = ?, expensesdetails = ?, expensestypeid = ?, conditions = ?, userid = ? dailyentryid = ? WHERE expensesid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($expense->expensesname);
        $sqlQuery->setString2($expense->expensesdetails);
        $sqlQuery->setNumber($expense->expensestypeid);
        $sqlQuery->setNumber($expense->conditions);
        $sqlQuery->setNumber($expense->userid);
        $sqlQuery->setNumber($expense->dailyentryid);

        $sqlQuery->setNumber($expense->expensesid);

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

    public function queryWithDateAndConditions($startDate, $endDate) {
        $sql = 'SELECT * FROM expenses
		where expensesdate >= "' . $startDate . '" and expensesdate <= "' . $endDate . '"
		and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryWithDateAndConditionsExt($startDate, $endDate) {
        $sql = 'SELECT SUM(expensesValue)
		FROM expenses
		where expensesdate >= "' . $startDate . '" and expensesdate <= "' . $endDate . '"
		and conditions = 0';
        $sqlQuery = new SqlQuery($sql);
        return $this->QuerySingleResult($sqlQuery);
    }

    public function queryByCostCenter($costcenterid) {
        $sql = 'SELECT * FROM expenses WHERE conditions = 0 and costcenterid = ' . $costcenterid;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByClient($clientid) {
        $sql = 'SELECT * FROM expenses WHERE conditions = 0 and clientid = ' . $clientid;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByCostCenterForShow($costcenterid, $queryString = '') {
        $sql = 'SELECT expenses.*, expensestype.expensestypename, expensestype.addOnlyGroupIds,expensestype.conditions as expensestypeConditions
		FROM expenses
		join expensestype
		on expensestype.expensestypeid = expenses.expensestypeid
		WHERE costcenterid = ' . $costcenterid . '
		and  expensestype.conditions = 0
                ' . $queryString . '
		order by expensesid desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function getSumByQueryString($queryString = '') {
        $sql = 'SELECT sum(expensesValue)
                FROM expenses
                join expensestype
                on expensestype.expensestypeid = expenses.expensestypeid
                where  expenses.conditions = 0
                    ' . $queryString . ''; //expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function queryAllQueryString($queryString = '') {
        $sql = 'SELECT expensestype.expensestypename,expensestype.addOnlyGroupIds, expenses.*,expensestype.conditions as expensestypeConditions,productName,name
                ,savename,bankname,accountname
                FROM expenses
                join expensestype on expensestype.expensestypeid = expenses.expensestypeid
                left join product on expenses.productid = product.productid
                left join costcenter on expenses.costcenterid = costcenter.id
                left join save on save.saveid = expenses.saveid
                left join bank on bank.bankid = expenses.bankid
                left join bankaccount on bankaccount.accountid = expenses.bankaccountid
                where 1 ' . $queryString . '';
        //expensestype.conditions = 0
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

}

?>