/**
*
* @return Zend_Db_Table_Rowset
*/
public function feFormationReport()
{
$mapperFeContract = new Fefop_Model_Mapper_FEContract();
$select = $mapperFeContract->getSelect();
$dbBudgetCategory = App_Model_DbTable_Factory::get('BudgetCategory');
$dbFEFOPTransaction = App_Model_DbTable_Factory::get('FEFOPTransaction');
$select->join(array('bc' => $dbBudgetCategory), 'bc.id_budget_category = bcc.fk_id_budget_category', array('expense' => 'description'))->joinLeft(array('t' => $dbFEFOPTransaction), 't.fk_id_fefop_contract = fec.fk_id_fefop_contract AND t.fk_id_budget_category = bcc.fk_id_budget_category', array('total_spent' => new Zend_Db_Expr("IFNULL(ABS(SUM(t.amount * IF(t.operation = 'D', -1, 1))), 0)")))->where('UPPER(bc.description) LIKE ?', '%Crédito%')->group(array('id_fe_contract'))->order(array('date_formation DESC'));
if (!empty($this->_data['fk_id_dec'])) {
$select->where('u.fk_id_dec IN(?)', $this->_data['fk_id_dec']);
}
if (!empty($this->_data['id_adddistrict'])) {
$select->where('d.id_adddistrict IN(?)', $this->_data['id_adddistrict']);
}
if (!empty($this->_data['id_fefop_status'])) {
$select->where('cs.id_fefop_status IN(?)', $this->_data['id_fefop_status']);
}
//Período
$select->where("DATE(c.date_inserted) >= STR_TO_DATE(?, '%d/%m/%Y')", $this->_data['date_start']);
$select->where("DATE(c.date_inserted) <= STR_TO_DATE(?, '%d/%m/%Y')", $this->_data['date_finish']);
return array('rows' => $dbBudgetCategory->fetchAll($select));
}
