<?php

/**
 * Class that operate on table 'income'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2014-07-19 10:51
 */
class IncomeMySqlExtDAO extends IncomeMySqlDAO {

    public function queryAllWithDateAndType($from, $to, $incomeTypeId) {
        $sql = 'SELECT incometype.incomeTypeName, income.*, savename, clientname as clientid
				FROM income
				left join incometype
				on incometype.incomeTypeId = income.incomeTypeId
                left join save
                on save.saveid = income.saveid
                left join client
                on client.clientid = income.clientid
				where incometype.conditions = 0
				AND income.incomeTypeId = ' . $incomeTypeId . '
				AND income.incomeDate >= "' . $from . '" AND income.incomeDate <= "' . $to . '"
				order by incomeId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

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

    public function queryAllWithOneDate($condition, $date, $incomeTypeId, $queryString = '') {
        $sql = 'SELECT incometype.incomeTypeName, income.*, savename
				FROM income
				left join incometype
				on incometype.incomeTypeId = income.incomeTypeId
                left join save
                on save.saveid = income.saveid
				where incometype.conditions = 0
				AND income.incomeTypeId = ' . $incomeTypeId . '
				AND income.incomeDate ' . $condition . '"' . $date . '" ' . $queryString . '
				order by incomeId desc';
        $sqlQuery = new SqlQuery($sql);
        //  print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithOneDateOnly($condition, $date) {
        $sql = 'SELECT incometype.incomeTypeName, income.*
				FROM income
				left join incometype
				on incometype.incomeTypeId = income.incomeTypeId
				where incometype.conditions = 0
				AND income.incomeDate ' . $condition . '"' . $date . '"
				order by incomeId desc';
        $sqlQuery = new SqlQuery($sql);
        //  print_r($sqlQuery);
        return $this->getList($sqlQuery);
    }

    public function queryByincometypeidForShow($incomeTypeId) {
        $sql = 'SELECT incometype.incomeTypeName, income.*
				FROM income
				left join incometype
				on incometype.incomeTypeId = income.incomeTypeId
				WHERE income.incomeTypeId = ' . $incomeTypeId . '
				and  incometype.conditions = 0
				order by incomeId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllWithDate($from, $to, $queryString = '') {
        $sql = 'SELECT incometype.incomeTypeName, income.*, savename, clientname as clientid
				FROM income
				left join incometype
				on incometype.incomeTypeId = income.incomeTypeId
                left join save
                on save.saveid = income.saveid
                left join client
                on client.clientid = income.clientid
				where income.conditions = 0
				AND income.incomeDate >= "' . $from . '" AND income.incomeDate <= "' . $to . '" ' . $queryString . '
				order by incomeId desc';
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    
    public function insertWithId($income) {
        $sql = 'INSERT INTO income (incomeId,incomeTypeId, incomeName, incomeDetails, incomeValue, incomeDate, userid, conditions,costcenterid) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($income->incomeId);
        $sqlQuery->setNumber($income->incomeTypeId);
        $sqlQuery->set($income->incomeName);
        $sqlQuery->setString2($income->incomeDetails);
        $sqlQuery->set($income->incomeValue);
        $sqlQuery->set($income->incomeDate);
        $sqlQuery->setNumber($income->userid);
        $sqlQuery->setNumber($income->conditions);
        $sqlQuery->setNumber($income->costcenterid);


        $id = $this->executeInsert($sqlQuery);
        $income->incomeId = $id;
        return $id;
    }


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



}

?>