/**
*
* @param string $path
* @param string $name
* @return string
*/
protected function _getNewFileName($path, $name)
{
$fileName = $path . $name;
$info = pathinfo($fileName);
$friendName = ucfirst(App_General_String::friendName($info['filename']));
$name = $friendName;
$fullPath = $path . $friendName . '.' . $info['extension'];
$count = 0;
while (file_exists($fullPath)) {
$friendName = $name . '_' . ++$count;
$fullPath = $path . $friendName . '.' . $info['extension'];
}
return $friendName . '.' . $info['extension'];
}
/**
*
* @return int|bool
*/
public function save()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$dataForm = $this->_data;
$dateIni = new Zend_Date($dataForm['date_start']);
$dateFin = new Zend_Date($dataForm['date_finish']);
// Check if the start date is later than finish date
if ($dateIni->isLater($dateFin)) {
$this->_message->addMessage('Loron inisiu la bele liu loron remata.');
$this->addFieldError('date_start')->addFieldError('date_finish');
return false;
}
// Check to see if either start date or finish date are within the year defined
if (!in_array($dataForm['year_planning'], array($dateIni->toString('yyyy')))) {
$this->_message->addMessage(sprintf('Loron inisiu tenki iha laran Tinan: %s.', $dataForm['year']));
$this->addFieldError('date_start');
return false;
}
$this->_data = $dataForm;
$this->_data['total_cost'] = App_General_String::toFloat($this->_data['total_cost']);
// Save the annual planning
$this->_data['fk_id_annual_planning'] = $this->_saveAnnualPlanning($this->_data);
$this->_data['date_start'] = $dateIni->toString('yyyy-MM-dd');
$this->_data['date_finish'] = $dateFin->toString('yyyy-MM-dd');
$dbPlanningCourse = App_Model_DbTable_Factory::get('FPPlanningCourse');
$id = parent::_simpleSave($dbPlanningCourse);
// Update the totals
$dbAnnualPlanning = App_Model_DbTable_Factory::get('FPAnnualPlanning');
$totals = $this->_getTotalsAnnualPlanning($this->_data['fk_id_annual_planning']);
$row = $dbAnnualPlanning->fetchRow(array('id_annual_planning = ?' => $this->_data['fk_id_annual_planning']));
$row->total_students = $totals->total_students;
$row->total_cost = $totals->total_cost;
$row->save();
$history = sprintf('REJISTU PLANU FORMASAUN: %s BA ANNUAL PLANNING: %s', $id, $this->_data['fk_id_annual_planning']);
$this->_sysAudit($history);
$dbAdapter->commit();
return $id;
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @return int|bool
*/
public function save()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$history = 'INSERE SMS CONFIG';
$this->_data['fk_id_sysuser'] = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$this->_data['sms_unit_cost'] = App_General_String::toFloat($this->_data['sms_unit_cost']);
$id = parent::_simpleSave();
$this->_sysAudit($history);
$this->_reloadSmsConfig();
$dbAdapter->commit();
return $id;
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @return boolean
*/
public function fixPhones()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$tables = array('PerData' => array('client_fone'));
$now = App_Cache::load('current_fix_phone');
if (empty($now)) {
$now = 0;
}
$fixed = 0;
$step = 2000;
echo 'Step: ' . $step . ' - now: ' . $now . "\n\r";
foreach ($tables as $table => $fields) {
$db = App_Model_DbTable_Factory::get($table);
$rows = $db->fetchAll(array(), array(), $step, $now);
foreach ($rows as $row) {
$altered = false;
foreach ($fields as $field) {
$newPhone = App_General_String::cleanFone($row[$field]);
if (!empty($newPhone)) {
$row[$field] = $newPhone;
$altered = true;
}
}
if ($altered) {
$row->save();
$fixed++;
}
}
}
$now += $step;
App_Cache::save($now, 'current_fix_phone');
$dbAdapter->commit();
echo number_format($fixed, 0, '', '.') . ' Phones fixed' . "\n\r";
echo str_repeat('-', 30) . "\n\r";
return true;
} catch (Exception $e) {
$dbAdapter->rollBack();
echo 'Error fixing phones: ' . $e->getMessage() . "\n\r";
return false;
}
}
/**
*
* @return int|bool
*/
public function save()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$this->_data['fk_id_sysuser'] = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$this->_data['value'] = App_General_String::toFloat($this->_data['value']);
$id = parent::_simpleSave();
$history = 'INSERE PULSA, FOLIN HIRA %s - HIRA: %s - DEPARTAMENTU %s';
$history = sprintf($history, $this->_data['value'], $this->_data['amount'], $this->_data['fk_id_department']);
$this->_sysAudit($history);
$dbAdapter->commit();
return $id;
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @return array
*/
public function areaReport()
{
$rows = $this->selectGraduated();
$data = array('MANE' => 0, 'FETO' => 0, 'total' => 0);
$schoolData = array('total' => 0, 'MANE' => 0, 'FETO' => 0, 'porcent' => 0);
$areaScholarity = array();
foreach ($rows as $row) {
$gender = trim($row['gender']);
$area = trim($row['scholarity_area']);
if (!array_key_exists($area, $areaScholarity)) {
$areaScholarity[$area] = $schoolData;
}
$areaScholarity[$area][$gender]++;
$areaScholarity[$area]['total']++;
$data['total']++;
$data[$gender]++;
}
foreach ($areaScholarity as $area => $row) {
$areaScholarity[$area]['porcent'] = round(100 * $row['total'] / $data['total'], 2);
}
$data['rows'] = $areaScholarity;
if (!empty($data['rows'])) {
$pie = array('series' => array(), 'labels' => array());
$column = array('series' => array(), 'labels' => array(), 'names' => array());
$man = array();
$woman = array();
$view = Zend_Layout::getMvcInstance()->getView();
foreach ($data['rows'] as $area => $row) {
$area = App_General_String::addBreakLine($view->truncate(ucfirst(strtolower($area)), 20), 1);
$pie['series'][] = $row['total'];
$pie['labels'][] = $area;
$man[] = $row['MANE'];
$woman[] = $row['FETO'];
$column['labels'][] = $area;
}
$column['series'] = array($man, $woman);
$column['names'] = array('MANE', 'FETO');
$scaleConfig = array('LabelRotation' => 90, 'XMargin' => 20);
$data['graph'][App_General_String::randomHash()] = App_Util_Chart::columnChart($column, 'Graduadu liu husi Area / Seksu', $scaleConfig);
}
return $data;
}
/**
*
* @return int|bool
*/
public function save()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$this->disableLastUnitCost($this->_data);
$this->_data['fk_id_sysuser'] = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$this->_data['status'] = 1;
$this->_data['cost'] = App_General_String::toFloat($this->_data['cost']);
$id = parent::_simpleSave();
$history = sprintf('REJISTU KUSTU UNITARIU: %s', $id);
$this->_sysAudit($history);
$dbAdapter->commit();
return $id;
} catch (Exception $e) {
$dbAdapter->rollBack();
Zend_Debug::dump($e);
exit;
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @return int|bool
*/
public function savePlanning()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$dbFundPlanning = App_Model_DbTable_Factory::get('FundPlanning');
$dbFundPlanningModule = App_Model_DbTable_Factory::get('FundPlanningModule');
$where = array('fk_id_fefopfund = ?' => $this->_data['fk_id_fefopfund'], 'year_planning = ?' => $this->_data['year_planning']);
$this->_data['amount'] = App_General_String::toFloat($this->_data['amount']);
$this->_data['additional_cost'] = App_General_String::toFloat($this->_data['additional_cost']);
$planning = $dbFundPlanning->fetchRow($where);
if (empty($planning)) {
$planning = $dbFundPlanning->createRow();
}
$planning->setFromArray($this->_data);
$idPlanning = $planning->save();
foreach ($this->_data['modules_cost'] as $module => $cost) {
$where = array('fk_id_fund_planning = ?' => $idPlanning, 'fk_id_fefop_modules = ?' => $module);
$planningModule = $dbFundPlanningModule->fetchRow($where);
if (empty($planningModule)) {
$planningModule = $dbFundPlanningModule->createRow();
$planningModule->fk_id_fund_planning = $idPlanning;
$planningModule->fk_id_fefop_modules = $module;
}
$planningModule->amount = App_General_String::toFloat($cost);
$planningModule->save();
}
$history = 'PLANEAMENTU FUNDU: %s - BA TINAN: %s';
$history = sprintf($history, $this->_data['fk_id_fefopfund'], $this->_data['year_planning']);
$this->_sysAudit($history);
$dbAdapter->commit();
return $idPlanning;
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @param array $options
*/
public function __construct($options = null)
{
$name = App_General_String::friendName(get_class($this));
$options['name'] = $name;
parent::__construct($options);
}
/**
*
* @param array $data
* @param int $id
* @return boolean
*/
protected function _saveItemFormationExpense($data, $id)
{
if (empty($data['formation_expense'][$id])) {
return false;
} else {
$items = $this->_arrangeItems($data['formation_expense'][$id]);
}
$dbPERItem = App_Model_DbTable_Factory::get('PerItem');
$dbPERFormation = App_Model_DbTable_Factory::get('PerFormation');
// For each budget category, save its detailed employment items
foreach ($items as $item) {
$item['fk_id_budget_category'] = $id;
$item['fk_id_per_contract'] = $data['fk_id_per_contract'];
$item['amount_unit'] = App_General_String::toFloat($item['amount_unit']);
$item['amount_total'] = App_General_String::toFloat($item['amount_total']);
$rowItem = $dbPERItem->createRow($item);
$idItem = $rowItem->save();
$item['fk_id_per_item'] = $idItem;
$rowBudgetItem = $dbPERFormation->createRow($item);
$rowBudgetItem->save();
}
}
/**
*
* @return boolean
*/
protected function _addGraphs()
{
$xpath = new DOMXPath($this->_dom);
$graphs = $xpath->query('//div[@class="graphs"]', $this->_content);
if (empty($graphs->length)) {
return false;
}
$images = $graphs->item(0)->getElementsByTagName('img');
foreach ($images as $img) {
$src = $img->getAttribute('src');
$src = preg_replace('/^.+image\\/id\\//i', '', $src);
$src = preg_replace('/\\/image.png$/i', '', $src);
$contents = App_Cache::load($src);
$randomName = App_General_String::randomHash();
$fileName = $this->_tempDir . DIRECTORY_SEPARATOR . $randomName . '.png';
file_put_contents($fileName, $contents);
$size = getimagesize($fileName);
$position = PHPExcel_Cell::stringFromColumnIndex($this->_startCol) . ($this->_currentRow += 2);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($fileName)->setResizeProportional(true)->setCoordinates($position)->setWorksheet($this->_mainSheet);
$this->_currentRow += ceil($size[1] / 25) + 4;
}
return true;
}
/**
*
* @return array
*/
public function educationReport()
{
$rows = $this->filterAppliedJob($this->_data);
$mapperClient = new Report_Model_Mapper_Client();
$data = $mapperClient->registerSchoolLevel($rows);
$column = array('series' => array(), 'labels' => array(), 'names' => array());
$man = array();
$woman = array();
foreach ($data['rows'] as $school => $row) {
$man[] = $row['MANE'];
$woman[] = $row['FETO'];
$column['labels'][] = $school;
}
$column['series'] = array($man, $woman);
$column['names'] = array('MANE', 'FETO');
$data['graph'][App_General_String::randomHash()] = App_Util_Chart::columnChart($column, 'Nivel Eskola / Tinan ' . $this->_data['year']);
return $data;
}
/**
*
* @param array $data
*/
protected function _saveBeneficiaries($data)
{
$dbBeneficiary = App_Model_DbTable_Factory::get('FP_Beneficiary');
foreach ($data['cost_client'] as $client => $cost) {
$where = array('fk_id_fp_contract = ?' => $data['id_fp_contract'], 'fk_id_perdata = ?' => $client);
$row = $dbBeneficiary->fetchRow($where);
if (empty($row)) {
$row = $dbBeneficiary->createRow();
$row->fk_id_fp_contract = $data['id_fp_contract'];
$row->fk_id_unit_cost = $data['fk_id_unit_cost'];
$row->fk_id_perdata = $client;
$row->handicapped = $data['client_handicapped'][$client];
$row->amount = App_General_String::toFloat($cost);
$row->save();
}
}
}
/**
*
* @param array $data
*/
protected function _saveExpenses($data)
{
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
if (empty($data['expense'])) {
$message = 'La iha Rubrika Despeza';
$this->_message->addMessage($message, App_Message::ERROR);
throw new Exception($message);
}
// Save each budget category
foreach ($data['expense'] as $id => $costExpense) {
$whereBudget = array('fk_id_fefop_contract = ?' => $data['fk_id_fefop_contract'], 'fk_id_budget_category = ?' => $id);
$row = $dbBudgetContract->fetchRow($whereBudget);
if (empty($row)) {
$row = $dbBudgetContract->createRow();
$row->fk_id_budget_category = $id;
$row->fk_id_fefop_contract = $data['fk_id_fefop_contract'];
$row->fk_id_sysuser = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$row->status = 1;
}
$row->amount = App_General_String::toFloat($costExpense);
$row->save();
}
}
/**
*
* @access protected
* @param string $field
* @param string $value
* @return string
*/
protected function urlAmigavel($field, $value)
{
$url = App_General_String::friendName($value);
$count = 0;
do {
$select = $this->_dbTable->select()->where($field . ' = ?', $url);
$data = $this->_dbTable->fetchRow($select);
if (!empty($data)) {
$url = $url . '-' . ++$count;
} else {
break;
}
} while (true);
return $url;
}
/**
*
*/
public function outputAction()
{
if (!$this->getRequest()->isPost()) {
$this->_helper->redirector->goToSimple('index');
}
$dataReport = $this->_mapper->setData($this->_getAllParams())->report();
$this->_helper->layout()->disableLayout();
$this->_helper->viewRenderer->setRender('templates/output', null, true);
$layoutPath = APPLICATION_PATH . '/modules/report/views/scripts/';
$viewSpec = new Zend_View();
$viewSpec->setScriptPath($layoutPath);
$viewSpec->addHelperPath('App/View/Helpers/', 'App_View_Helper');
$layoutView = new Zend_View();
$layoutView->setScriptPath($layoutPath);
$layoutView->addHelperPath('App/View/Helpers/', 'App_View_Helper');
$viewSpec->data = $dataReport;
$path = $this->_getParam('path');
$title = $this->_getParam('title');
require_once APPLICATION_PATH . '/../library/HTMLPurifier/HTMLPurifier.auto.php';
$config = HTMLPurifier_Config::createDefault();
$purifier = new HTMLPurifier($config);
$layoutView->title = $title;
$layoutView->path = $this->view->baseUrl();
$layoutView->content = $purifier->purify($viewSpec->render($path . '.phtml'));
//$layoutView->content = $viewSpec->render( $path . '.phtml' );
if (preg_match('/fefop/i', $path)) {
$layoutView->department = 'SECRETARIA DE ESTADO PARA A POLÍTICA DE FORMAÇÃO PROFISSIONAL E EMPREGO - FEFOP';
}
$reportOutput = $layoutView->render('templates/report.phtml');
$id = App_General_String::randomHash();
if (empty($this->_session->reportOutput)) {
$this->_session->reportOutput = array();
}
$this->_session->reportOutput[$id] = $reportOutput;
$this->_session->dataReport = $dataReport;
$this->view->content = $reportOutput;
$this->view->id = $id;
}
/**
*
* @param Zend_Db_Select $select
* @param array $filters
* @return Zend_Db_Select
*/
public function applyFiltersSelect($select, $filters)
{
if (!empty($filters['fk_id_fefop_type_transaction'])) {
$select->where('bs.fk_id_fefop_type_transaction = ?', $filters['fk_id_fefop_type_transaction']);
}
if (!empty($filters['fk_id_budget_category'])) {
$select->where('bsc.fk_id_budget_category IN (?)', (array) $filters['fk_id_budget_category']);
}
if (!empty($filters['fk_id_budget_category_type'])) {
$select->where('bc.fk_id_budget_category_type IN (?)', (array) $filters['fk_id_budget_category_type']);
}
if (!empty($filters['fk_id_fefopfund'])) {
$select->where('bs.fk_id_fefopfund = ?', $filters['fk_id_fefopfund']);
}
if (!empty($filters['status'])) {
$select->where('bs.status = ?', $filters['status']);
}
if (!empty($filters['minimum_amount'])) {
$select->where('bs.amount >= ?', App_General_String::toFloat($filters['minimum_amount']));
}
if (!empty($filters['maximum_amount'])) {
$select->where('bs.amount <= ?', App_General_String::toFloat($filters['maximum_amount']));
}
if (!empty($filters['num_contract'])) {
$dbFefopContract = App_Model_DbTable_Factory::get('FEFOPContract');
$select->join(array('fct' => $dbFefopContract), 'fct.id_fefop_contract = bsc.fk_id_fefop_contract', array('num_contract' => new Zend_Db_Expr('CONCAT( fct.num_program, "-",' . ' fct.num_module, "-", ' . 'fct.num_district, "-", ' . 'fct.num_year, "-", fct.num_sequence )')))->having('num_contract LIKE ?', '%' . $filters['num_contract'] . '%');
}
$date = new Zend_Date();
if (!empty($filters['date_start'])) {
$select->where('bs.date_statement >= ?', $date->set($filters['date_start'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['date_finish'])) {
$select->where('bs.date_statement <= ?', $date->set($filters['date_finish'])->toString('yyyy-MM-dd'));
}
return $select;
}
/**
*
* @return array
*/
public function donorContractCostReport()
{
$dbFEFOPFund = App_Model_DbTable_Factory::get('FEFOPFund');
$dbFEFOPContractFund = App_Model_DbTable_Factory::get('FEFOPContractFund');
$dbFEFOPContract = App_Model_DbTable_Factory::get('FEFOPContract');
$dbBudgetCategoryType = App_Model_DbTable_Factory::get('BudgetCategoryType');
$dbFEFOPTransaction = App_Model_DbTable_Factory::get('FEFOPTransaction');
//Donors
$subSelect = $dbFEFOPFund->select()->setIntegrityCheck(false)->distinct()->from($dbFEFOPFund, array('id_fefopfund', 'name_fund', 'type', 'planning' => new Zend_Db_Expr('IFNULL((' . $this->_columnPlanningByFund() . '), 0)'), 'contract' => new Zend_Db_Expr('IFNULL((' . $this->_columnContractByFund() . '), 0)'), 'financial' => new Zend_Db_Expr('IFNULL((' . $this->_columnFinancialByFund() . '), 0)'), 'addcosts' => new Zend_Db_Expr('IFNULL((' . $this->_columnAdditionalCostsByFund() . '), 0)'), 'addcostsplanning' => new Zend_Db_Expr('IFNULL((' . $this->_addCostsPlanningByFund() . '), 0)'), 'bankstmt' => new Zend_Db_Expr('IFNULL((' . $this->_columnBankStatementsByFund() . '), 0)'), 'balance' => new Zend_Db_Expr('IFNULL((' . $this->_columnTotalByFund() . '), 0)')));
// ->joinLeft(
// $dbFEFOPContractFund->__toString(),
// 'FEFOP_Contract_Fund.fk_id_fefopfund = FEFOPFund.id_fefopfund',
// array()
// )
// ->joinLeft(
// $dbFEFOPContract->__toString(),
// 'FEFOP_Contract.id_fefop_contract = FEFOP_Contract_Fund.fk_id_fefop_contract',
// array()
// );
//
//$this->_whereDefault( $subSelect );
//
// if ( !empty( $this->_data['year_start'] ) ) {
// $subSelect->where( '(FEFOP_Contract.id_fefop_contract IS NULL' );
// $subSelect->orWhere( 'YEAR(FEFOP_Contract.date_inserted) >= ?)', $this->_data['year_start'] );
// }
//
// if ( !empty( $this->_data['year_finish'] ) ) {
// $subSelect->where( '(FEFOP_Contract.id_fefop_contract IS NULL' );
// $subSelect->orWhere( 'YEAR(FEFOP_Contract.date_inserted) <= ?)', $this->_data['year_finish'] );
// }
$adapter = App_Model_DbTable_Abstract::getDefaultAdapter();
$rows = $adapter->fetchAll($subSelect);
$donor = array();
$totalDonor = array('planning' => 0, 'contract' => 0, 'financial' => 0, 'addcosts' => 0, 'addcostsplanning' => 0, 'bankstmt' => 0, 'balance' => 0);
foreach ($rows as $row) {
$donor[$row['type']][$row['id_fefopfund']]['name'] = $row['name_fund'];
$donor[$row['type']][$row['id_fefopfund']]['planning'] = $row['planning'];
$donor[$row['type']][$row['id_fefopfund']]['contract'] = $row['contract'];
$donor[$row['type']][$row['id_fefopfund']]['financial'] = $row['financial'];
$donor[$row['type']][$row['id_fefopfund']]['addcosts'] = $row['addcosts'];
$donor[$row['type']][$row['id_fefopfund']]['addcostsplanning'] = $row['addcostsplanning'];
$donor[$row['type']][$row['id_fefopfund']]['bankstmt'] = $row['bankstmt'];
$donor[$row['type']][$row['id_fefopfund']]['balance'] = $row['balance'];
$totalDonor['planning'] += App_General_String::toFloat($row['planning']);
$totalDonor['contract'] += App_General_String::toFloat($row['contract']);
$totalDonor['financial'] += App_General_String::toFloat($row['financial']);
$totalDonor['addcosts'] += App_General_String::toFloat($row['addcosts']);
$totalDonor['addcostsplanning'] += App_General_String::toFloat($row['addcostsplanning']);
$totalDonor['bankstmt'] += App_General_String::toFloat($row['bankstmt']);
$totalDonor['balance'] += App_General_String::toFloat($row['balance']);
}
//Dados do Contrato
$select = $dbFEFOPContract->select()->setIntegrityCheck(false)->distinct()->from($dbFEFOPContract->__toString(), array());
$this->_joinDefault($select);
$this->_whereDefault($select);
//Período
if (!empty($this->_data['year_start'])) {
$select->where('YEAR(FEFOP_Contract.date_inserted) >= ?', $this->_data['year_start']);
}
if (!empty($this->_data['year_finish'])) {
$select->where('YEAR(FEFOP_Contract.date_inserted) <= ?', $this->_data['year_finish']);
}
$select->columns(array('FEFOP_Modules.id_fefop_modules', 'FEFOP_Programs.id_fefop_programs', 'acronym_module' => 'FEFOP_Modules.acronym', 'acronym_program' => 'FEFOP_Programs.acronym', 'planning' => new Zend_Db_Expr('(' . $this->_columnPlanningByProgramModule() . ')'), 'transfer' => new Zend_Db_Expr('(' . $this->_columnTransferByProgramModule() . ')'), 'total' => new Zend_Db_Expr('(' . $this->_columnTotalByProgramModule() . ')')));
$rows = $dbFEFOPContract->fetchAll($select);
$contract = array();
foreach ($rows as $row) {
if (!in_array($row['id_fefop_programs'], array_keys($contract))) {
$contract[$row['id_fefop_programs']]['acronym'] = $row['acronym_program'];
$contract[$row['id_fefop_programs']]['module'] = array();
$contract[$row['id_fefop_programs']]['planning'] = 0;
$contract[$row['id_fefop_programs']]['transfer'] = 0;
$contract[$row['id_fefop_programs']]['total'] = 0;
}
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['acronym'] = $row['acronym_module'];
if (!isset($contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['planning'])) {
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['planning'] = 0;
}
if (!isset($contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['transfer'])) {
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['transfer'] = 0;
}
if (!isset($contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['total'])) {
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['total'] = 0;
}
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['planning'] += App_General_String::toFloat($row['planning']);
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['transfer'] += App_General_String::toFloat($row['transfer']);
$contract[$row['id_fefop_programs']]['module'][$row['id_fefop_modules']]['total'] += App_General_String::toFloat($row['total']);
$contract[$row['id_fefop_programs']]['planning'] += App_General_String::toFloat($row['planning']);
$contract[$row['id_fefop_programs']]['transfer'] += App_General_String::toFloat($row['transfer']);
$contract[$row['id_fefop_programs']]['total'] += App_General_String::toFloat($row['total']);
}
//Dados dos Custos
$select = $dbBudgetCategoryType->select()->setIntegrityCheck(false)->from($dbBudgetCategoryType->__toString(), array('id_budget_category_type', 'description'))->join($dbFEFOPTransaction->__toString(), 'FEFOP_Transaction.fk_id_budget_category_type = BudgetCategoryType.id_budget_category_type', array('amount' => new Zend_Db_Expr('IFNULL(SUM(FEFOP_Transaction.amount), 0)')))->join($dbFEFOPContract->__toString(), 'FEFOP_Contract.id_fefop_contract = FEFOP_Transaction.fk_id_fefop_contract', array());
$this->_whereDefault($select);
//Período
if (!empty($this->_data['year_start'])) {
$select->where('YEAR(FEFOP_Contract.date_inserted) >= ?', $this->_data['year_start']);
}
if (!empty($this->_data['year_finish'])) {
$select->where('YEAR(FEFOP_Contract.date_inserted) <= ?', $this->_data['year_finish']);
}
$select->group(array('BudgetCategoryType.id_budget_category_type'));
$rows = $dbBudgetCategoryType->fetchAll($select);
$cost = array();
foreach ($rows as $row) {
$cost[$row['id_budget_category_type']]['description'] = $row['description'];
$cost[$row['id_budget_category_type']]['amount'] = $row['amount'];
}
return array('item' => array('donor' => $donor, 'total' => $totalDonor, 'contract' => $contract, 'cost' => $cost));
}
/**
*
* @return int|bool
*/
public function updateAmount($data)
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$dbCategoryConfiguration = App_Model_DbTable_Factory::get('BudgetCategoryConfiguration');
$updateValue = array('amount' => App_General_String::toFloat($data['amount']));
$where = array('identifier = ?' => $data['item'], 'fk_id_budget_category = ?' => $data['id']);
$dbCategoryConfiguration->update($updateValue, $where);
$dbAdapter->commit();
return true;
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @param Zend_Db_Table_Row $campaign
*/
protected function _sendSms($campaign)
{
// Get the current SMS Config
$mapperConfig = new Admin_Model_Mapper_SmsConfig();
$smsConfig = $mapperConfig->getConfig();
$dbCampaign = App_Model_DbTable_Factory::get('Campaign');
$dbContactSend = App_Model_DbTable_Factory::get('CampaignSent');
// Fetch All contacts to be sent
$contactsToSend = $this->_mapperCampaign->listContactsToSend($campaign->id_campaign, 300);
// Format Sms Content
$contentCampaign = $this->_formatSmsContent($campaign, $smsConfig);
foreach ($contactsToSend as $contact) {
// Check if the department has credit yet
if (!$this->_checkDepartment($campaign)) {
return false;
}
$number = App_General_String::validateNumber($contact->number);
$data = array('id_campaign_sent' => $contact->id_campaign_sent, 'attempts' => ++$contact->attempts, 'fk_id_sms_config' => $smsConfig->id_sms_config, 'fk_id_campaign' => $campaign->id_campaign, 'fk_id_sms_group_contact' => $contact->id_sms_group_contact, 'target' => $number);
// Check if the number to be sent is valid
if (empty($number)) {
$data['status'] = 'E';
$data['log'] = 'KONTATU IDA NE\'E HO NUMERO SALA';
} else {
// Prepare the parameters to be sent
$toSend = array('to' => $number, 'msg' => $contentCampaign, 'id' => implode('|', array($campaign->id_campaign, $contact->id_sms_group_contact)));
// Try to send the Sms
try {
$result = App_Util_Sms::send($smsConfig->gateway_url, $toSend);
$data['status'] = (bool) $result['status'] ? 'S' : 'E';
$data['log'] = (bool) $result['status'] ? '' : $result['msgerror'];
$data['source'] = $result['source'];
} catch (Exception $e) {
$data['status'] = 'E';
$data['log'] = $e->getMessage();
}
}
$this->_data = $data;
parent::_simpleSave($dbContactSend, false);
}
// Fetch All contacts to be sent
$contactsToSend = $this->_mapperCampaign->listContactsToSend($campaign->id_campaign);
if ($contactsToSend->count() < 1) {
$this->_finishCampaign($campaign->id_campaign);
} else {
// Set the campaign as initied
$data = array('status' => Sms_Model_Mapper_Campaign::STATUS_INITIED);
$where = array('id_campaign = ?' => $campaign->id_campaign);
$dbCampaign->update($data, $where);
}
}
/**
*
* @param array $filters
* @return Zend_Db_Table_Rowset
*/
public function listByFilters($filters = array())
{
$select = $this->getSelect();
if (!empty($filters['fk_id_fefop_type_transaction'])) {
$select->where('bs.fk_id_fefop_type_transaction = ?', $filters['fk_id_fefop_type_transaction']);
}
if (!empty($filters['id_fefop_bank_contract'])) {
$select->where('bc.fk_id_fefop_contract = ?', $filters['id_fefop_bank_contract']);
}
if (!empty($filters['fk_id_budget_category'])) {
$select->where('bc.fk_id_budget_category IN (?)', (array) $filters['fk_id_budget_category']);
}
if (!empty($filters['fk_id_budget_category_type'])) {
$select->where('bgc.fk_id_budget_category_type IN (?)', (array) $filters['fk_id_budget_category_type']);
}
if (!empty($filters['minimum_amount'])) {
$select->having('ABS(total_consolidated) >= ?', App_General_String::toFloat($filters['minimum_amount']));
}
if (!empty($filters['maximum_amount'])) {
$select->having('ABS(total_consolidated) <= ?', App_General_String::toFloat($filters['maximum_amount']));
}
$date = new Zend_Date();
if (!empty($filters['date_start'])) {
$select->where('DATE(co.date_inserted) >= ?', $date->set($filters['date_start'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['date_finish'])) {
$select->where('DATE(co.date_inserted) <= ?', $date->set($filters['date_finish'])->toString('yyyy-MM-dd'));
}
return $this->_dbTable->fetchAll($select);
}
/**
*
* @return boolean
*/
protected function _addGraphs()
{
$xpath = new DOMXPath($this->_dom);
$graphs = $xpath->query('//div[@class="graphs"]', $this->_content);
if (empty($graphs->length)) {
return false;
}
$images = $graphs->item(0)->getElementsByTagName('img');
foreach ($images as $img) {
$src = $img->getAttribute('src');
$src = preg_replace('/^.+image\\/id\\//i', '', $src);
$src = preg_replace('/\\/image.png$/i', '', $src);
$contents = App_Cache::load($src);
$randomName = App_General_String::randomHash();
$fileName = $this->_tempDir . DIRECTORY_SEPARATOR . $randomName . '.png';
file_put_contents($fileName, $contents);
$size = getimagesize($fileName);
$this->_mainSection->addImage($fileName, array('align' => 'center', 'width' => 675, 'height' => $size[1]));
}
return true;
}
/**
*
* @return mixed
*/
public function saveItens()
{
$method = 'save' . App_General_String::toCamelCase($this->_data['action']);
return call_user_func(array($this, $method));
}
/**
*
* @return boolean
*/
public function saveAdditionalContract()
{
$dbAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
$dbAdapter->beginTransaction();
try {
$dataForm = $this->_data;
$dbContractAdditional = App_Model_DbTable_Factory::get('FEFOPContractAdditional');
$totalCost = abs($this->getTotalExpenseContract($dataForm['contract'], $dataForm['expense']));
$totalFunds = 0;
foreach ($dataForm['funds'] as $fund) {
$totalFunds += App_General_String::toFloat($fund);
}
if ((string) $totalCost != (string) $totalFunds) {
$currency = new Zend_Currency();
$message = 'Total husi Fundu: %s keta la hanesan Total Kustu Extra: %s';
$message = sprintf($message, $currency->setValue($totalFunds)->toCurrency(), $currency->setValue($totalCost)->toCurrency());
$this->_message->addMessage($message, App_Message::ERROR);
return false;
}
foreach ($dataForm['funds'] as $idFund => $fund) {
$where = array('fk_id_budget_category = ?' => $dataForm['expense'], 'fk_id_fefop_contract = ?' => $dataForm['contract'], 'fk_id_fefopfund = ?' => $idFund);
$row = $dbContractAdditional->fetchRow($where);
if (empty($row)) {
$row = $dbContractAdditional->createRow();
$row->fk_id_budget_category = $dataForm['expense'];
$row->fk_id_fefop_contract = $dataForm['contract'];
$row->fk_id_fefopfund = $idFund;
}
$row->user_entered = 1;
$row->amount = App_General_String::toFloat($fund);
$row->save();
}
$dbAdapter->commit();
return true;
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_message->addMessage($this->_config->messages->error, App_Message::ERROR);
return false;
}
}
/**
*
* @param array $data
*/
protected function _saveExpenses($data)
{
$dbBusinessPlanExpense = App_Model_DbTable_Factory::get('BusinessPlanExpense');
$dbBusinessPlanBudgetCategory = App_Model_DbTable_Factory::get('BusinessPlanBugdetCategory');
#$dbBudgetContract = App_Model_DbTable_Factory::get( 'BudgetCategoryHasFEFOPContract' );
// Delete all the detailed items
$where = array('fk_id_businessplan = ?' => $data['id_businessplan']);
$dbBusinessPlanBudgetCategory->delete($where);
$dbBusinessPlanExpense->delete($where);
// Fetch Business Plan
$contract = $this->fetchBusinessPlan($data['id_businessplan']);
// Save each budget category
foreach ($data['expense'] as $typeExpense => $expense) {
foreach ($expense as $id => $cost) {
$whereBudget = array('fk_id_businessplan = ?' => $contract['id_businessplan'], 'fk_id_budget_category = ?' => $id);
$row = $dbBusinessPlanExpense->fetchRow($whereBudget);
if (empty($row)) {
$row = $dbBusinessPlanExpense->createRow();
$row->fk_id_budget_category = $id;
$row->fk_id_businessplan = $contract['id_businessplan'];
}
$row->amount = App_General_String::toFloat($cost);
$idBudgetExpense = $row->save();
// If it wasn't defined detailed items
if ($typeExpense != "cost_expense" || empty($data['detailed_expense']['quantity'][$id])) {
continue;
}
$detailedExpense = $data['detailed_expense'];
// For each budget category, save its detailed items
foreach ($detailedExpense['quantity'][$id] as $count => $itemExpense) {
$rowItemExpense = $dbBusinessPlanBudgetCategory->createRow();
$rowItemExpense->fk_id_businessplan = $data['id_businessplan'];
$rowItemExpense->fk_id_business_plan_expense = $idBudgetExpense;
$rowItemExpense->description = $detailedExpense['item_expense'][$id][$count];
$rowItemExpense->quantity = $itemExpense;
$rowItemExpense->amount_unit = App_General_String::toFloat($detailedExpense['amount_unit'][$id][$count]);
$rowItemExpense->amount_total = App_General_String::toFloat($detailedExpense['amount_total'][$id][$count]);
$rowItemExpense->save();
}
}
}
}
/**
*
* @return mixed
* @throws Exception
*/
protected function _getAmount()
{
switch (true) {
case array_key_exists('amount', $this->_dataModel):
return App_General_String::toFloat($this->_dataModel['amount']);
case array_key_exists('total', $this->_dataModel):
return App_General_String::toFloat($this->_dataModel['total']);
case array_key_exists('amount_expenses', $this->_dataModel):
return App_General_String::toFloat($this->_dataModel['amount_expenses']);
default:
$message = 'La hetan folin hira ba halo validasaun';
$this->_messageModel->addMessage($message, App_Message::ERROR);
throw new Exception($message);
}
}
/**
*
* @return array
*/
public function schoolQuarterReport()
{
$rows = $this->getFilteredRows($this->_data);
$gender = array('mane' => 0, 'feto' => 0);
$quarters = array_combine(range(1, 4), array_fill(0, 4, $gender));
$data = array('total' => 0, 'totals' => $quarters, 'rows' => array());
foreach ($rows as $row) {
$school = empty($row['max_level_scholarity']) ? 'LA IHA' : $row['max_level_scholarity'];
if (!array_key_exists($school, $data['rows'])) {
$data['rows'][$school] = array('quarters' => $quarters, 'total' => 0);
}
$quarter = ceil($row['month_registration'] / 3);
$gender = strtolower(trim($row['gender']));
$data['total']++;
$data['totals'][$quarter][$gender]++;
$data['rows'][$school]['total']++;
$data['rows'][$school]['quarters'][$quarter][$gender]++;
}
$column = array('series' => array(), 'labels' => array(), 'names' => array());
$quarters = array(1 => array(), 2 => array(), 3 => array(), 4 => array());
foreach ($data['rows'] as $level => $row) {
$column['labels'][] = $level;
foreach ($row['quarters'] as $q => $value) {
$quarters[$q][] = $value['mane'] + $value['feto'];
}
}
$column['series'] = $quarters;
$column['names'] = array(1 => 'QTR1', 'QTR2', 'QTR3', 'QTR4');
$data['graph'][App_General_String::randomHash()] = App_Util_Chart::columnChart($column, 'Nivel Edukasaun / Tinan ' . $this->_data['year']);
return $data;
}
/**
*
* @param array $data
*/
protected function _saveExpenses($data)
{
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
// Save each budget category
foreach ($data['cost_expense'] as $id => $costExpense) {
$whereBudget = array('fk_id_fefop_contract = ?' => $data['fk_id_fefop_contract'], 'fk_id_budget_category = ?' => $id);
$row = $dbBudgetContract->fetchRow($whereBudget);
if (empty($row)) {
$row = $dbBudgetContract->createRow();
$row->fk_id_budget_category = $id;
$row->fk_id_fefop_contract = $data['fk_id_fefop_contract'];
$row->fk_id_sysuser = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$row->status = 1;
}
$row->amount = App_General_String::toFloat($costExpense);
$row->save();
}
}
/**
*
* @param array $data
*/
protected function _saveExpenses($data)
{
$dbRIBudgetCategory = App_Model_DbTable_Factory::get('RIBudgetCategory');
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
// Delete all the detailed items
$where = array('fk_id_ri_contract = ?' => $data['id_ri_contract']);
$dbRIBudgetCategory->delete($where);
// Save each budget category
foreach ($data['cost_expense'] as $id => $costExpense) {
$whereBudget = array('fk_id_fefop_contract = ?' => $data['fk_id_fefop_contract'], 'fk_id_budget_category = ?' => $id);
$row = $dbBudgetContract->fetchRow($whereBudget);
if (empty($row)) {
$row = $dbBudgetContract->createRow();
$row->fk_id_budget_category = $id;
$row->fk_id_fefop_contract = $data['fk_id_fefop_contract'];
$row->fk_id_sysuser = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$row->status = 1;
}
$row->amount = App_General_String::toFloat($costExpense);
$idBudgetContract = $row->save();
// If it wasn't defined detailed items
if (empty($data['item_expense'][$id])) {
continue;
}
// For each budget category, save its detailed items
foreach ($data['item_expense'][$id] as $count => $itemExpense) {
$rowItemExpense = $dbRIBudgetCategory->createRow();
$rowItemExpense->fk_id_ri_contract = $data['id_ri_contract'];
$rowItemExpense->fk_id_budgetcategory_contract = $idBudgetContract;
$rowItemExpense->description = $itemExpense;
$rowItemExpense->quantity = $data['quantity'][$id][$count];
$rowItemExpense->amount_unit = App_General_String::toFloat($data['amount_unit'][$id][$count]);
$rowItemExpense->amount_total = App_General_String::toFloat($data['amount_total'][$id][$count]);
$rowItemExpense->comments = $data['comments'][$id][$count];
$rowItemExpense->save();
}
}
}
/**
*
* @param array $data
* @return array
*/
public function calcTotals($data)
{
$totals = array('staff' => array(), 'amount' => 0, 'amount_expenses' => 0);
foreach ($data['amount_expense'] as $expense) {
$total = App_General_String::toFloat($expense);
$totals['amount_expenses'] += $total;
}
$unitCost = round($totals['amount_expenses'] / count($data['staff']), 2);
foreach ($data['staff'] as $idStaff) {
$amount = $unitCost;
if ($data['gender'][$idStaff] == 'F') {
$amount *= 1.1;
}
if (!empty($data['handicapped'][$idStaff])) {
$amount *= 1.1;
}
$totals['staff'][] = array('id_staff' => $idStaff, 'unit_cost' => $amount, 'final_cost' => $unitCost, 'training_cost' => $amount - $unitCost);
$totals['amount'] += $amount;
}
return $totals;
}
