<?php

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

    /**
     * Get Domain object by primry key
     *
     * @param String $id primary key
     * @return ExpensesMySql
     */
    public function load($id) {
        $sql = 'SELECT * FROM expenses WHERE expensesid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    /**
     * Get all records from table
     */
    public function queryAll() {
        $sql = 'SELECT * FROM expenses';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Get all records from table ordered by field
     *
     * @param $orderColumn column name
     */
    public function queryAllOrderBy($orderColumn) {
        $sql = 'SELECT * FROM expenses ORDER BY ' . $orderColumn;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Delete record from table
     * @param expense primary key
     */
    public function delete($expensesid) {
        $sql = 'DELETE FROM expenses WHERE expensesid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($expensesid);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Insert record to table
     *
     * @param ExpensesMySql expense
     */
    public function insert($expense) {
        $sql = 'INSERT INTO expenses (expensestypeid, expensesname, expensesdetails, expensesValue, expensesdate, userid, conditions,costcenterid,saveid,dailyentryid,bankid,bankaccountid,checknum,productid,expensenumbrt,expenseprice,expensetotaln,expenseshortfalls,supplier,suppRemainingVal,sellbillid,clientid,realestateid,realestateunitid,file,taxPer,taxVal,travelDriverId,travelBusId,travelPathId,travelTripId,travelTripTypeId,travelTripDateId,travelOfficeId,webApiId) VALUES (?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($expense->expensestypeid);
        $sqlQuery->set($expense->expensesname);
        $sqlQuery->setString2($expense->expensesdetails);
        $sqlQuery->set($expense->expensesValue);
        $sqlQuery->set($expense->expensesdate);
        $sqlQuery->setNumber($expense->userid);
        $sqlQuery->setNumber($expense->conditions);
        $sqlQuery->setNumber($expense->costcenterid);
        $sqlQuery->setNumber((int) $expense->saveid);
        $sqlQuery->setNumber($expense->dailyentryid);
        $sqlQuery->setNumber((int) $expense->bankid);
        $sqlQuery->setNumber((int) $expense->bankaccountid);
        $sqlQuery->set($expense->checknum);
        $sqlQuery->setNumber((int) $expense->productid);
        $sqlQuery->setNumber((int) $expense->expensenumbrt);
        $sqlQuery->setNumber((int) $expense->expenseprice);
        $sqlQuery->setNumber((int) $expense->expensetotaln);
        $sqlQuery->setNumber((int) $expense->expenseshortfalls);
        $sqlQuery->setNumber((int) $expense->supplier);
        $sqlQuery->setNumber((float) $expense->suppRemainingVal);
        $sqlQuery->setNumber((int) $expense->sellbillid);
        $sqlQuery->setNumber((int) $expense->clientid);
        $sqlQuery->setNumber((int) $expense->realestateid);
        $sqlQuery->setNumber((int) $expense->realestateunitid);
        $sqlQuery->set($expense->file);
        $sqlQuery->setNumber((float) $expense->taxPer);
        $sqlQuery->setNumber((float) $expense->taxVal);
        $sqlQuery->setNumber((int) $expense->travelDriverId);
        $sqlQuery->setNumber((int) $expense->travelBusId);
        $sqlQuery->setNumber((int) $expense->travelPathId);
        $sqlQuery->setNumber((int) $expense->travelTripId);
        $sqlQuery->setNumber((int) $expense->travelTripTypeId);
        $sqlQuery->setNumber((int) $expense->travelTripDateId);
        $sqlQuery->setNumber((int) $expense->travelOfficeId);
        $sqlQuery->setNumber((int) $expense->webApiId);



        $id = $this->executeInsert($sqlQuery);
        $expense->expensesid = $id;
        return $id;
    }

    /**
     * Update record in table
     *
     * @param ExpensesMySql expense
     */
    public function update($expense) {
        $sql = 'UPDATE expenses SET expensestypeid = ?, expensesname = ?, expensesdetails = ?, expensesValue = ?, expensesdate = ?, userid = ?, conditions = ?,saveid = ? , dailyentryid = ?, bankid = ?, bankaccountid = ?, checknum = ?, productid = ?, expensenumbrt = ?, expenseprice = ?, expensetotaln = ?, expenseshortfalls = ?,supplier=?,suppRemainingVal=?,sellbillid=?,clientid=?,realestateid=?,realestateunitid=?,file=?,taxPer=?,taxVal=?,travelDriverId=?,travelBusId=?,travelPathId=?,travelTripId=?,travelTripTypeId=?,travelTripDateId=?,travelOfficeId=?,webApiId=? WHERE expensesid = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->setNumber($expense->expensestypeid);
        $sqlQuery->set($expense->expensesname);
        $sqlQuery->setString2($expense->expensesdetails);
        $sqlQuery->set($expense->expensesValue);
        $sqlQuery->set($expense->expensesdate);
        $sqlQuery->setNumber($expense->userid);
        $sqlQuery->setNumber($expense->conditions);
        $sqlQuery->setNumber((int) $expense->saveid);
        $sqlQuery->setNumber($expense->dailyentryid);
        $sqlQuery->setNumber((int) $expense->bankid);
        $sqlQuery->setNumber((int) $expense->bankaccountid);
        $sqlQuery->set($expense->checknum);
        $sqlQuery->setNumber((int) $expense->productid);
        $sqlQuery->setNumber((int) $expense->expensenumbrt);
        $sqlQuery->setNumber((int) $expense->expenseprice);
        $sqlQuery->setNumber((int) $expense->expensetotaln);
        $sqlQuery->setNumber((int) $expense->expenseshortfalls);
        $sqlQuery->setNumber((int) $expense->supplier);
        $sqlQuery->setNumber((float) $expense->suppRemainingVal);
        $sqlQuery->setNumber((int) $expense->sellbillid);
        $sqlQuery->setNumber((int) $expense->clientid);
        $sqlQuery->setNumber((int) $expense->realestateid);
        $sqlQuery->setNumber((int) $expense->realestateunitid);
        $sqlQuery->set($expense->file);
        $sqlQuery->setNumber((float) $expense->taxPer);
        $sqlQuery->setNumber((float) $expense->taxVal);
        $sqlQuery->setNumber((int) $expense->travelDriverId);
        $sqlQuery->setNumber((int) $expense->travelBusId);
        $sqlQuery->setNumber((int) $expense->travelPathId);
        $sqlQuery->setNumber((int) $expense->travelTripId);
        $sqlQuery->setNumber((int) $expense->travelTripTypeId);
        $sqlQuery->setNumber((int) $expense->travelTripDateId);
        $sqlQuery->setNumber((int) $expense->travelOfficeId);
        $sqlQuery->setNumber((int) $expense->webApiId);

        $sqlQuery->setNumber($expense->expensesid);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Delete all rows
     */
    public function clean() {
        $sql = 'DELETE FROM expenses';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByExpensestypeid($value) {
        $sql = 'SELECT * FROM expenses WHERE expensestypeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByExpensesname($value) {
        $sql = 'SELECT * FROM expenses WHERE expensesname = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByExpensesdetails($value) {
        $sql = 'SELECT * FROM expenses WHERE expensesdetails = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByExpensesValue($value) {
        $sql = 'SELECT * FROM expenses WHERE expensesValue = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByExpensesdate($value) {
        $sql = 'SELECT * FROM expenses WHERE expensesdate = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByUserid($value) {
        $sql = 'SELECT * FROM expenses WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCondition($value) {
        $sql = 'SELECT * FROM expenses WHERE conditions = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function deleteByExpensestypeid($value) {
        $sql = 'DELETE FROM expenses WHERE expensestypeid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByExpensesname($value) {
        $sql = 'DELETE FROM expenses WHERE expensesname = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByExpensesdetails($value) {
        $sql = 'DELETE FROM expenses WHERE expensesdetails = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByExpensesValue($value) {
        $sql = 'DELETE FROM expenses WHERE expensesValue = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByExpensesdate($value) {
        $sql = 'DELETE FROM expenses WHERE expensesdate = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByUserid($value) {
        $sql = 'DELETE FROM expenses WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByCondition($value) {
        $sql = 'DELETE FROM expenses WHERE conditions = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Read row
     *
     * @return ExpensesMySql
     */
    protected function readRow($row) {
        $expense = new Expense();

        $expense->expensesid = isset($row['expensesid']) ? $row['expensesid'] : '';
        $expense->expensestypeid = isset($row['expensestypeid']) ? $row['expensestypeid'] : '';
        $expense->expensesname = isset($row['expensesname']) ? $row['expensesname'] : '';
        $expense->expensesdetails = isset($row['expensesdetails']) ? $row['expensesdetails'] : '';
        $expense->expensesValue = isset($row['expensesValue']) ? $row['expensesValue'] : '';
        $expense->expensesdate = isset($row['expensesdate']) ? $row['expensesdate'] : '';
        $expense->userid = isset($row['userid']) ? $row['userid'] : '';
        $expense->conditions = isset($row['conditions']) ? $row['conditions'] : '';
        $expense->costcenterid = isset($row['costcenterid']) ? $row['costcenterid'] : '';
        $expense->saveid = isset($row['saveid']) ? $row['saveid'] : '';
        $expense->dailyentryid = isset($row['dailyentryid']) ? $row['dailyentryid'] : '';
        $expense->bankid = isset($row['bankid']) ? $row['bankid'] : '';
        $expense->bankaccountid = isset($row['bankaccountid']) ? $row['bankaccountid'] : '';
        $expense->checknum = isset($row['checknum']) ? $row['checknum'] : '';
        $expense->productid = isset($row['productid']) ? $row['productid'] : '';
        $expense->addOnlyGroupIds = isset($row['addOnlyGroupIds']) ? $row['addOnlyGroupIds'] : '';
        $expense->productName = isset($row['productName']) ? $row['productName'] : '';
        $expense->name = isset($row['name']) ? $row['name'] : '';
        $expense->expensenumbrt = isset($row['expensenumbrt']) ? $row['expensenumbrt'] : '';
        $expense->expenseprice = isset($row['expenseprice']) ? $row['expenseprice'] : '';
        $expense->expensetotaln = isset($row['expensetotaln']) ? $row['expensetotaln'] : '';
        $expense->expenseshortfalls = isset($row['expenseshortfalls']) ? $row['expenseshortfalls'] : '';
        $expense->supplier = isset($row['supplier']) ? $row['supplier'] : '';
        $expense->suppRemainingVal = isset($row['suppRemainingVal']) ? $row['suppRemainingVal'] : '';



        //required
        $expense->expensestypename = isset($row['expensestypename']) ? $row['expensestypename'] : '';
        $expense->expensestypeConditions = isset($row['expensestypeConditions']) ? $row['expensestypeConditions'] : '';
        $expense->savename = isset($row['savename']) ? $row['savename'] : '';
        $expense->bankname = isset($row['bankname']) ? $row['bankname'] : '';
        $expense->accountname = isset($row['accountname']) ? $row['accountname'] : '';
        $expense->clientid = isset($row['clientid']) ? $row['clientid'] : '';
        $expense->sellbillid = isset($row['sellbillid']) ? $row['sellbillid'] : '';
        $expense->realestateid = isset($row['realestateid']) ? $row['realestateid'] : '';
        $expense->realestateunitid = isset($row['realestateunitid']) ? $row['realestateunitid'] : '';
        $expense->file = isset($row['file']) ? $row['file'] : '';
        $expense->taxPer = isset($row['taxPer']) ? $row['taxPer'] : '';
        $expense->taxVal = isset($row['taxVal']) ? $row['taxVal'] : '';
        $expense->travelDriverId = isset($row['travelDriverId']) ? $row['travelDriverId'] : '';
        $expense->travelBusId = isset($row['travelBusId']) ? $row['travelBusId'] : '';
        $expense->travelPathId = isset($row['travelPathId']) ? $row['travelPathId'] : '';
        $expense->travelTripId = isset($row['travelTripId']) ? $row['travelTripId'] : '';
        $expense->travelTripTypeId = isset($row['travelTripTypeId']) ? $row['travelTripTypeId'] : '';
        $expense->travelTripDateId = isset($row['travelTripDateId']) ? $row['travelTripDateId'] : '';
        $expense->travelOfficeId = isset($row['travelOfficeId']) ? $row['travelOfficeId'] : '';
        $expense->webApiId = isset($row['webApiId']) ? $row['webApiId'] : '';

        return $expense;
    }

    protected function getList($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        $ret = array();
        for ($i = 0; $i < count($tab); $i++) {
            $ret[$i] = $this->readRow($tab[$i]);
        }
        return $ret;
    }

    /**
     * Get row
     *
     * @return ExpensesMySql
     */
    protected function getRow($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        if (count($tab) == 0) {
            return null;
        }
        return $this->readRow($tab[0]);
    }

    /**
     * Execute sql query
     */
    protected function execute($sqlQuery) {
        return QueryExecutor::execute($sqlQuery);
    }

    /**
     * Execute sql query
     */
    protected function executeUpdate($sqlQuery) {
        return QueryExecutor::executeUpdate($sqlQuery);
    }

    /**
     * Query for one row and one column
     */
    protected function querySingleResult($sqlQuery) {
        return QueryExecutor::queryForString($sqlQuery);
    }

    /**
     * Insert row to table
     */
    protected function executeInsert($sqlQuery) {
        return QueryExecutor::executeInsert($sqlQuery);
    }

}

?>