/**
*
*/
public function indexAction()
{
$module = $this->_getParam('module');
if (empty($module)) {
$this->_helper->redirector->goToSimple('index', 'index', 'external');
}
$client = $this->view->session->client->id_perdata;
$businessPlan = $this->_mapper->fetchBusinessPlanByClient($client, $module);
if ($businessPlan) {
$this->view->businessPlan = $businessPlan;
$this->view->date_ini = $this->view->date($businessPlan->date_inserted);
$this->view->hasBudgetCategory = $this->_mapper->hasBudgetCategory($businessPlan->id_businessplan);
if (!$businessPlan->submitted) {
$this->view->revision = $this->_mapper->getLastRevision($businessPlan->id_businessplan);
}
if (!empty($businessPlan->business_group)) {
$this->view->is_group = true;
}
if (!empty($businessPlan->fk_id_fefop_contract)) {
$mapperContract = new Fefop_Model_Mapper_Contract();
$this->view->contract = $mapperContract->detail($businessPlan->fk_id_fefop_contract);
}
} else {
$this->view->date_ini = Zend_Date::now()->toString('dd/MM/yyyy');
}
$this->view->can_create = $this->_mapper->canCreateBusinessPlan($client, $module);
$this->view->module = $module;
}
/**
* @access protected
* @param Zend_Db_Select $select
* @return void
*/
protected function _joinDefault(Zend_Db_Select $select)
{
$dbFEFOPPrograms = App_Model_DbTable_Factory::get('FEFOPPrograms');
$dbFEFOPModules = App_Model_DbTable_Factory::get('FEFOPModules');
$dbAddDistrict = App_Model_DbTable_Factory::get('AddDistrict');
$dbSysUser = App_Model_DbTable_Factory::get('SysUser');
$mapper = new Fefop_Model_Mapper_Contract();
$select->join($dbFEFOPPrograms->__toString(), 'FEFOP_Programs.id_fefop_programs = FEFOP_Contract.fk_id_fefop_programs', array())->join($dbFEFOPModules->__toString(), 'FEFOP_Modules.id_fefop_modules = FEFOP_Contract.fk_id_fefop_modules', array())->join($dbAddDistrict->__toString(), 'AddDistrict.acronym = FEFOP_Contract.num_district', array())->join($dbSysUser->__toString(), 'SysUser.id_sysuser = FEFOP_Contract.fk_id_sysuser', array())->join(array('s' => new Zend_Db_Expr('(' . $this->_selectFEFOPStatus() . ')')), 's.fk_id_fefop_contract = FEFOP_Contract.id_fefop_contract', array())->join(array('b' => new Zend_Db_Expr('(' . $mapper->getSelectBeneficiary() . ')')), 'b.fk_id_fefop_contract = FEFOP_Contract.id_fefop_contract AND b.target = 1', array());
}
/**
*
* @param mixed $contract
*/
public function setContract($contract)
{
if (!$contract instanceof Zend_Db_Table_Row) {
$mapperContract = new Fefop_Model_Mapper_Contract();
$contract = $mapperContract->detail($contract);
}
$this->_contract = $contract;
return $this;
}
/**
*
*/
public function contractAction()
{
if ($this->getRequest()->isXMLHTTPRequest()) {
$this->_helper->layout()->disableLayout();
}
$id = $this->_getParam('id');
if (!empty($id)) {
$mapperContract = new Fefop_Model_Mapper_Contract();
$this->view->contract = $mapperContract->detail($id);
}
}
/**
* @access public
* @return void
*/
public function init()
{
parent::init();
$this->getElement('path')->setValue(self::PATH);
$this->getElement('title')->setValue(self::TITLE);
$elements = array();
$adapter = App_Model_DbTable_Abstract::getDefaultAdapter();
$mapper = new Fefop_Model_Mapper_Contract();
$rows = $adapter->fetchAll($mapper->getSelectBeneficiary());
$optUsers = array();
array_unshift($optUsers, '');
foreach ($rows as $row) {
$optUsers[$row['id']] = $row['name'];
}
$elements[] = $this->createElement('select', 'id_beneficiary')->setDecorators($this->getDefaultElementDecorators())->setAttrib('class', 'm-wrap span12 chosen')->setLabel('Benefisiariu')->addMultiOptions($optUsers);
$this->addElements($elements);
}
/**
*
*/
public function exportAction()
{
$id = $this->_getParam('id');
$contract = $this->_mapper->detail($id);
$data = $contract->toArray();
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($contract->fk_id_fefop_contract);
$data['evidence'] = Client_Model_Mapper_Client::buildNumById($contract->fk_id_perdata);
$data['date_start'] = $this->view->date($data['date_start']);
$data['date_finish'] = $this->view->date($data['date_finish']);
$data['date_formation'] = $this->view->date($data['date_formation']);
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$expenses = $this->_mapper->listExpenses($id);
$data['expenses'] = $expenses->toArray();
$excelPath = APPLICATION_PATH . '/../library/PHPExcel/';
require_once $excelPath . 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(APPLICATION_PATH . '/../public/forms/FEFOP/Contrato_FE_tet.xlsx');
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setCellValue('R10', $data['contract']);
$activeSheet->setCellValue('V8', $data['date_inserted']);
$activeSheet->setCellValue('F15', $data['scholarity_area']);
$activeSheet->setCellValue('F16', $data['ocupation_name_timor']);
$activeSheet->setCellValue('G21', $data['date_start']);
$activeSheet->setCellValue('G22', $data['date_finish']);
$activeSheet->setCellValue('K21', (int) $data['duration_month']);
$activeSheet->setCellValue('Q21', $data['district']);
$activeSheet->setCellValue('Q22', $data['sub_district']);
$activeSheet->setCellValue('H28', $data['entity']);
$activeSheet->setCellValue('H29', $data['beneficiary']);
$activeSheet->setCellValue('E117', $data['beneficiary']);
$activeSheet->setCellValue('S29', $data['evidence']);
//$activeSheet->setCellValue( 'U28', $data['date_formation'] );
// Expenses
$startRow = 34;
foreach ($data['expenses'] as $expense) {
$activeSheet->setCellValue('D' . $startRow, $expense['description']);
$activeSheet->setCellValue('U' . $startRow, $expense['amount']);
$startRow++;
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$file = sprintf('Contract_%s.xlsx', $data['contract']);
header(sprintf('Content-Disposition: attachment;filename="%s"', $file));
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
*
*/
public function getFilters()
{
$filters = $this->_data;
if (!empty($this->_data['fk_id_dec'])) {
$dbDec = App_Model_DbTable_Factory::get('Dec');
$select = $dbDec->select()->from($dbDec, array('name_dec'))->where('id_dec IN(?)', $this->_data['fk_id_dec']);
$rows = $dbDec->fetchAll($select);
$filters['ceop'] = '';
foreach ($rows as $key => $row) {
$filters['ceop'] .= $row->name_dec;
if ($rows->count() != ++$key) {
$filters['ceop'] .= ', ';
}
}
}
if (!empty($this->_data['fk_id_counselor'])) {
$dbSysUser = App_Model_DbTable_Factory::get('SysUser');
$select = $dbSysUser->select()->from($dbSysUser, array('name'))->where('id_sysuser IN(?)', $this->_data['fk_id_counselor']);
$rows = $dbSysUser->fetchAll($select);
$filters['fk_id_counselor'] = '';
foreach ($rows as $key => $row) {
$filters['fk_id_counselor'] .= $row->name;
if ($rows->count() != ++$key) {
$filters['fk_id_counselor'] .= ', ';
}
}
}
if (!empty($this->_data['fk_id_addcountry'])) {
$mapperCountry = new Register_Model_Mapper_AddCountry();
$contry = $mapperCountry->fetchRow($this->_data['fk_id_addcountry']);
$filters['country'] = $contry->country;
}
if (!empty($this->_data['fk_id_adddistrict'])) {
$mapperDistrict = new Register_Model_Mapper_AddDistrict();
$district = $mapperDistrict->fetchRow($this->_data['fk_id_adddistrict']);
$filters['district'] = $district->District;
}
if (!empty($this->_data['fk_id_fefpenterprise'])) {
$mapperEnterprise = new Register_Model_Mapper_Enterprise();
$enterprise = $mapperEnterprise->fetchRow($this->_data['fk_id_fefpenterprise']);
$filters['enterprise'] = $enterprise->enterprise_name;
}
if (!empty($this->_data['fk_id_fefpeduinstitution'])) {
$mapperEducationInstititue = new Register_Model_Mapper_EducationInstitute();
$educationInstititute = $mapperEducationInstititue->fetchRow($this->_data['fk_id_fefpeduinstitution']);
$filters['institute'] = $educationInstititute->institution;
}
if (!empty($this->_data['fk_id_profocupation'])) {
$mapperOccupation = new Register_Model_Mapper_ProfOcupationTimor();
$occupation = $mapperOccupation->fetchRow($this->_data['fk_id_profocupation']);
$filters['occupation'] = $occupation['acronym'] . ' ' . $occupation['ocupation_name_timor'];
}
if (!empty($this->_data['fk_id_scholarity_area'])) {
$mapperScholarityArea = new Register_Model_Mapper_ScholarityArea();
$area = $mapperScholarityArea->fetchRow($this->_data['fk_id_scholarity_area']);
$filters['area'] = $area['scholarity_area'];
}
if (!empty($this->_data['fk_id_pertypescholarity'])) {
$mapperTypeScholarity = new Register_Model_Mapper_PerTypeScholarity();
$typeScholarity = $mapperTypeScholarity->fetchRow($this->_data['fk_id_pertypescholarity']);
$filters['type_scholarity'] = $typeScholarity['type_scholarity'];
}
if (!empty($this->_data['fk_typeinstitution'])) {
$mapperTypeInstitution = App_Model_DbTable_Factory::get('TypeInstitution');
$typeInstitution = $mapperTypeInstitution->fetchRow(array('id_typeinstitution = ?' => $this->_data['fk_typeinstitution']));
$filters['type_institution'] = $typeInstitution['type_institution'];
}
if (!empty($this->_data['fk_id_sectorindustry'])) {
$mapperIsicClass = App_Model_DbTable_Factory::get('ISICClassTimor');
$sectorIndustry = $mapperIsicClass->fetchRow(array('id_isicclasstimor = ?' => $this->_data['fk_id_sectorindustry']));
$filters['sector_industry'] = $sectorIndustry['name_classtimor'];
}
if (!empty($this->_data['fk_fefptypeenterprite'])) {
$mapperTypeEnterprise = App_Model_DbTable_Factory::get('FEFPTypeEnterprise');
$typeEnterprise = $mapperTypeEnterprise->fetchRow(array('id_fefptypeenterprise = ?' => $this->_data['fk_fefptypeenterprite']));
$filters['type_enterprise'] = $typeEnterprise['type_enterprise'];
}
if (!empty($this->_data['fk_id_perlevelscholarity'])) {
$dbLevelScholarity = App_Model_DbTable_Factory::get('PerLevelScholarity');
$levelScholarity = $dbLevelScholarity->fetchRow(array('id_perlevelscholarity = ?' => $this->_data['fk_id_perlevelscholarity']));
$filters['level_scholarity'] = $levelScholarity['level_scholarity'];
}
if (!empty($this->_data['fk_id_perscholarity'])) {
$mapperScholarity = new Register_Model_Mapper_PerScholarity();
$course = $mapperScholarity->fetchRow($this->_data['fk_id_perscholarity']);
$filters['course'] = (empty($course['external_code']) ? '' : $course['external_code'] . ' - ') . $course['scholarity'];
}
if (!empty($this->_data['fk_id_sysuser'])) {
$mapperSysUser = new Admin_Model_Mapper_SysUser();
$user = $mapperSysUser->fetchRow($this->_data['fk_id_sysuser']);
$filters['user'] = $user['name'] . ' (' . $user['login'] . ')';
}
if (!empty($this->_data['fk_id_department'])) {
$mapperDepartment = new Admin_Model_Mapper_Department();
$department = $mapperDepartment->fetchRow($this->_data['fk_id_department']);
$filters['department'] = $department['name'];
}
if (!empty($this->_data['fk_id_campaign_type'])) {
$mapperCampaignType = new Sms_Model_Mapper_CampaignType();
$campaignType = $mapperCampaignType->fetchRow($this->_data['fk_id_campaign_type']);
$filters['campaign_type'] = $campaignType['campaign_type'];
}
if (!empty($this->_data['status_campaign'])) {
$view = Zend_Layout::getMvcInstance()->getView();
$optStatuses = $view->campaign()->getStatuses();
$filters['status_campaign'] = $optStatuses[$this->_data['status_campaign']];
}
if (!empty($this->_data['fk_id_campaign'])) {
$mapperCampaign = new Sms_Model_Mapper_Campaign();
$campaign = $mapperCampaign->fetchRow($this->_data['fk_id_campaign']);
$filters['campaign_title'] = $campaign['campaign_title'];
}
if (!empty($this->_data['id_fefop_programs']) || !empty($this->_data['fk_id_fefop_programs'])) {
$dbFEFOPPrograms = App_Model_DbTable_Factory::get('FEFOPPrograms');
if (!empty($this->_data['id_fefop_programs'])) {
if (!is_array($this->_data['id_fefop_programs'])) {
$row = $dbFEFOPPrograms->find($this->_data['id_fefop_programs'])->current();
$filters['fefop_program'] = $row->acronym . ' - ' . $row->description;
} else {
$select = $dbFEFOPPrograms->select()->from($dbFEFOPPrograms, array('description', 'acronym'))->where('id_fefop_programs IN(?)', $this->_data['id_fefop_programs']);
$rows = $dbFEFOPPrograms->fetchAll($select);
$filters['fefop_program'] = '';
foreach ($rows as $key => $row) {
$filters['fefop_program'] .= $row->acronym . ' - ' . $row->description;
if ($rows->count() != ++$key) {
$filters['fefop_program'] .= ', ';
}
}
}
} else {
if (!empty($this->_data['fk_id_fefop_programs'])) {
$row = $dbFEFOPPrograms->find($this->_data['fk_id_fefop_programs'])->current();
$filters['fefop_program'] = $row->acronym . ' - ' . $row->description;
}
}
}
if (!empty($this->_data['id_fefop_modules']) || !empty($this->_data['fk_id_fefop_modules'])) {
$dbFEFOPModules = App_Model_DbTable_Factory::get('FEFOPModules');
if (!empty($this->_data['id_fefop_modules'])) {
if (!is_array($this->_data['id_fefop_modules'])) {
$row = $dbFEFOPModules->find($this->_data['id_fefop_modules'])->current();
$filters['fefop_module'] = $row->acronym . ' - ' . $row->description;
} else {
$select = $dbFEFOPModules->select()->from($dbFEFOPModules, array('description', 'acronym'))->where('id_fefop_modules IN(?)', $this->_data['id_fefop_modules']);
$rows = $dbFEFOPModules->fetchAll($select);
$filters['fefop_module'] = '';
foreach ($rows as $key => $row) {
$filters['fefop_module'] .= $row->acronym . ' - ' . $row->description;
if ($rows->count() != ++$key) {
$filters['fefop_module'] .= ', ';
}
}
}
} else {
if (!empty($this->_data['fk_id_fefop_modules'])) {
$row = $dbFEFOPModules->find($this->_data['fk_id_fefop_modules'])->current();
$filters['fefop_module'] = $row->acronym . ' - ' . $row->description;
}
}
}
if (!empty($this->_data['id_adddistrict'])) {
$dbAddDistrict = App_Model_DbTable_Factory::get('AddDistrict');
if (is_array($this->_data['id_adddistrict'])) {
$select = $dbAddDistrict->select()->from($dbAddDistrict, array('District'))->where('id_adddistrict IN(?)', $this->_data['id_adddistrict']);
$rows = $dbAddDistrict->fetchAll($select);
$filters['district'] = '';
foreach ($rows as $key => $row) {
$filters['district'] .= $row->District;
if ($rows->count() != ++$key) {
$filters['district'] .= ', ';
}
}
} else {
$row = $dbAddDistrict->find($this->_data['id_adddistrict'])->current();
$filters['district'] = $row->District;
}
}
if (!empty($this->_data['num_district'])) {
$row = App_Model_DbTable_Factory::get('AddDistrict')->fetchRow(array('acronym = ?' => $this->_data['num_district']));
$filters['district'] = $row->District;
}
if (!empty($this->_data['id_scholarity_area'])) {
$row = App_Model_DbTable_Factory::get('ScholarityArea')->find($this->_data['id_scholarity_area'])->current();
$filters['scholarity_area'] = (empty($row->acronym) ? '' : $row->acronym . ' - ') . $row->scholarity_area;
}
if (!empty($this->_data['id_profocupationtimor'])) {
$row = App_Model_DbTable_Factory::get('PROFOcupationTimor')->find($this->_data['id_profocupationtimor'])->current();
$filters['ocupationtimor'] = $row->acronym . ' - ' . $row->ocupation_name_timor;
}
if (!empty($this->_data['id_fefpeduinstitution'])) {
$row = App_Model_DbTable_Factory::get('FefpEduInstitution')->find($this->_data['id_fefpeduinstitution'])->current();
$filters['institution'] = $row->institution;
}
if (!empty($this->_data['fk_id_user_inserted'])) {
$row = App_Model_DbTable_Factory::get('SysUser')->find($this->_data['fk_id_user_inserted'])->current();
$filters['user_inserted'] = $row->name;
}
if (!empty($this->_data['fk_id_user_removed'])) {
$row = App_Model_DbTable_Factory::get('SysUser')->find($this->_data['fk_id_user_removed'])->current();
$filters['user_removed'] = $row->name;
}
if (array_key_exists('status', $this->_data) && is_numeric($this->_data['status'])) {
$filters['status_description'] = $this->_data['status'] ? 'Loos' : 'Lae';
}
if (!empty($this->_data['date_registration_ini'])) {
$filters['date_registration_ini'] = $this->_data['date_registration_ini'];
}
if (!empty($this->_data['date_registration_fim'])) {
$filters['date_registration_fim'] = $this->_data['date_registration_fim'];
}
if (!empty($this->_data['num_year'])) {
$filters['num_year'] = $this->_data['num_year'];
}
if (!empty($this->_data['num_sequence'])) {
$filters['num_sequence'] = $this->_data['num_sequence'];
}
if (array_key_exists('minimum_amount', $this->_data) && array_key_exists('maximum_amount', $this->_data)) {
$min = new Zend_Currency('en_US');
$min->setValue($this->_data['minimum_amount']);
$max = new Zend_Currency('en_US');
$max->setValue($this->_data['maximum_amount']);
$filters['minmaxamount'] = $min . ' - ' . $max;
}
if (!empty($this->_data['id_fefop_status']) || !empty($this->_data['fk_id_fefop_status'])) {
$dbFEFOPStatus = App_Model_DbTable_Factory::get('FEFOPStatus');
if (!empty($this->_data['id_fefop_status'])) {
if (!is_array($this->_data['id_fefop_status'])) {
$row = $dbFEFOPStatus->find($this->_data['id_fefop_status'])->current();
$filters['fefop_status'] = $row->status_description;
} else {
$select = $dbFEFOPStatus->select()->from($dbFEFOPStatus, array('status_description'))->where('id_fefop_status IN(?)', $this->_data['id_fefop_status']);
$rows = $dbFEFOPStatus->fetchAll($select);
$filters['fefop_status'] = '';
foreach ($rows as $key => $row) {
$filters['fefop_status'] .= $row->status_description;
if ($rows->count() != ++$key) {
$filters['fefop_status'] .= ', ';
}
}
}
} else {
$row = $dbFEFOPStatus->find($this->_data['fk_id_fefop_status'])->current();
$filters['fefop_status'] = $row->status_description;
}
}
if (!empty($this->_data['id_budget_category_type'])) {
$row = App_Model_DbTable_Factory::get('BudgetCategoryType')->find($this->_data['id_budget_category_type'])->current();
$filters['budget_category_type'] = $row->description;
}
if (!empty($this->_data['type_fefopfund'])) {
if ('G' == $this->_data['type_fefopfund']) {
$filters['description_type_fefopfund'] = 'Governo';
} else {
$filters['description_type_fefopfund'] = 'Donor';
}
}
if (!empty($this->_data['id_beneficiary'])) {
$mapper = new Fefop_Model_Mapper_Contract();
$adapter = App_Model_DbTable_Abstract::getDefaultAdapter();
$select = $adapter->select()->from(array('b' => new Zend_Db_Expr('(' . $mapper->getSelectBeneficiary() . ')')), array('name'))->where('b.id = ?', $this->_data['id_beneficiary']);
$row = $adapter->fetchRow($select);
$filters['beneficiary'] = $row['name'];
}
if (!empty($this->_data['type_beneficiary'])) {
switch ($this->_data['type_beneficiary']) {
case 'fk_id_staff':
$type_beneficiary = 'Empreza Staff';
break;
case 'fk_id_fefpenterprise':
$type_beneficiary = 'Empreza';
break;
case 'fk_id_fefpeduinstitution':
$type_beneficiary = 'Inst Ensinu';
break;
case 'fk_id_perdata':
$type_beneficiary = 'Kliente';
break;
default:
$type_beneficiary = 'N/A';
}
$filters['type_beneficiary'] = $type_beneficiary;
}
return $filters;
}
/**
* Replicates all Business Plan to a Contract
* @param int $id
*/
protected function _createContractFromBusinessPlan($id)
{
$businessPlan = $this->fetchBusinessPlan($id);
$mapperBudget = new Fefop_Model_Mapper_Expense();
$itemConfig = $mapperBudget->getModuleToItem($businessPlan['fk_id_fefop_modules']);
$dataValidate = array('fk_id_perdata' => $businessPlan->fk_id_perdata, 'amount' => $this->getTotal($id, 'total_expense'));
$mapperRule = new Fefop_Model_Mapper_Rule();
$mapperRule->validate($this->_message, $dataValidate, $itemConfig);
$dataContract = array('module' => $businessPlan['fk_id_fefop_modules'], 'district' => $businessPlan['fk_id_adddistrict'], 'status' => Fefop_Model_Mapper_Status::PROGRESS);
$mapperFefopContract = new Fefop_Model_Mapper_Contract();
$idContract = $mapperFefopContract->save($dataContract);
// Save relationship between contract and businessplan
$businessPlanRow = $this->fetchRow($id);
$businessPlanRow->fk_id_fefop_contract = $idContract;
$businessPlanRow->save();
$expenses = $this->listExpenses($id, $itemConfig)->toArray();
$dbBudgetCategoryContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
foreach ($expenses as $expense) {
$row = $dbBudgetCategoryContract->createRow();
$row->fk_id_budget_category = $expense['id_budget_category'];
$row->fk_id_fefop_contract = $idContract;
$row->fk_id_sysuser = Zend_Auth::getInstance()->getIdentity()->id_sysuser;
$row->amount = $expense['amount'];
$row->status = 1;
$row->save();
}
}
/**
*
* @return string
* @throws Exception
*/
protected function getFefopDir()
{
if (empty($this->_data['contract'])) {
throw new Exception('Tenki informa kliente');
}
$dir = APPLICATION_PATH . $this->_ds . '..' . $this->_ds . 'public' . $this->_ds . self::DIR . $this->_ds;
if (!is_dir($dir)) {
mkdir($dir);
}
$dir .= 'fefop' . $this->_ds;
if (!is_dir($dir)) {
mkdir($dir);
}
$mapperContract = new Fefop_Model_Mapper_Contract();
$contract = $mapperContract->detail($this->_data['contract']);
$dir .= strtolower($contract->num_program) . $this->_ds;
if (!is_dir($dir)) {
mkdir($dir);
}
$dir .= strtolower($contract->num_module) . $this->_ds;
if (!is_dir($dir)) {
mkdir($dir);
}
$dir .= md5($this->_data['contract']) . $this->_ds;
if (!is_dir($dir)) {
mkdir($dir);
}
return realpath($dir) . $this->_ds;
}
/**
*
*/
public function exportAction()
{
$id = $this->_getParam('id');
$row = $this->_mapper->detail($id);
$contractFiles = array('CEC' => 'Contrato_CEC_I_tet.xlsx', 'CED' => 'Contrato_CED_I_tet.xlsx');
// Fetch Contract
$mapperContract = new Fefop_Model_Mapper_Contract();
$contract = $mapperContract->detail($row->fk_id_fefop_contract);
$data = $row->toArray();
$data += $contract->toArray();
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($row->fk_id_fefop_contract);
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$data['date_start'] = $this->view->date($data['date_start'], 'yyyy-M-dd');
$data['date_finish'] = $this->view->date($data['date_finish'], 'yyyy-M-dd');
$mapperClient = new Client_Model_Mapper_Client();
$client = $mapperClient->detailClient($row->fk_id_perdata);
$data['evidence_card'] = Client_Model_Mapper_Client::buildNumRow($client);
$data['client_name'] = Client_Model_Mapper_Client::buildName($client);
$data['electoral'] = $client->electoral;
$data['gender'] = $client->gender;
$data['client_fone'] = $client->client_fone;
$data['email'] = $client->email;
$expensesRows = $mapperContract->listExpensesContract($row->fk_id_fefop_contract);
$expenses = array();
$total = 0;
foreach ($expensesRows as $expense) {
$expenses[] = array('name' => $expense->description, 'amount' => (double) $expense->amount);
$total += (double) $expense->amount;
}
$excelPath = APPLICATION_PATH . '/../library/PHPExcel/';
require_once $excelPath . 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(APPLICATION_PATH . '/../public/forms/FEFOP/' . $contractFiles[$data['num_module']]);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setCellValue('P12', $data['contract']);
$activeSheet->setCellValue('T8', $data['date_inserted']);
$activeSheet->setCellValue('F16', $data['evidence_card']);
$activeSheet->setCellValue('F17', $data['electoral']);
$activeSheet->setCellValue('E19', $data['client_name']);
$activeSheet->setCellValue('D100', $data['client_name']);
$activeSheet->setCellValue('Q19', $data['client_fone']);
$activeSheet->setCellValue('O20', $data['email']);
$activeSheet->setCellValue('F25', $data['name_disivion']);
$activeSheet->setCellValue('L25', $data['name_classtimor']);
$activeSheet->setCellValue('F27', $data['scholarity']);
$activeSheet->setCellValue('Q27', $data['external_code']);
$activeSheet->setCellValue('F29', $data['class_name']);
//$activeSheet->setCellValue( 'Q23', (float)$data['amount_training'] );
$activeSheet->setCellValue('F33', $data['date_start']);
$activeSheet->setCellValue('F34', $data['date_finish']);
//$activeSheet->setCellValue( 'J27', $data['duration'] . ' days' );
$activeSheet->setCellValue('P33', $data['district_course']);
$activeSheet->setCellValue('P34', $data['sub_district']);
$startExpense = 40;
$count = 'A';
foreach ($expenses as $expense) {
$activeSheet->setCellValue('B' . $startExpense, $count++);
$activeSheet->setCellValue('C' . $startExpense, $expense['name']);
$activeSheet->setCellValue('S' . $startExpense, $expense['amount']);
$startExpense++;
}
//$activeSheet->setCellValue( 'S' . $startExpense, $total );
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$file = sprintf('Contract_%s.xlsx', $data['contract']);
header(sprintf('Content-Disposition: attachment;filename="%s"', $file));
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
*
* @param int $id
* @return array
*/
protected function _contractToExport($id)
{
$contract = $this->_mapper->detail($id);
$data = $contract->toArray();
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($contract->fk_id_fefop_contract);
$data['evidence'] = Client_Model_Mapper_Client::buildNumById($contract->fk_id_perdata);
$data['formation_plan'] = Fefop_Model_Mapper_DRHTrainingPlan::buildNum($contract->fk_id_drh_trainingplan);
$data['date_start'] = $this->view->date($data['date_start']);
$data['date_finish'] = $this->view->date($data['date_finish']);
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$data['modality'] = $this->view->nomenclature()->drhModality($data['modality']);
$expenses = $this->_mapper->listExpenses($id);
$data['expenses'] = $expenses->toArray();
return $data;
}
/**
*
*/
public function exportAction()
{
$id = $this->_getParam('id');
$row = $this->_mapper->fetchBusinessPlan($id);
$contractFiles = array('CEG' => 'Contrato_CEG_tet.xlsx', 'CEC' => 'Contrato_CEC_II_tet.xlsx', 'CED' => 'Contrato_CED_II_tet.xlsx');
// Fetch Contract
$mapperContract = new Fefop_Model_Mapper_Contract();
$contract = $mapperContract->detail($row->fk_id_fefop_contract);
$data = $row->toArray();
$data += $contract->toArray();
$reserve_fund = $this->_mapper->getTotal($id, 'reserve_fund');
$reserve_fund = $reserve_fund < 1 ? 600 : $reserve_fund;
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($row->fk_id_fefop_contract);
$data['business_plan'] = External_Model_Mapper_Pce::buildNumRow($row);
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$dateInsert = new Zend_Date($data['date_inserted']);
$data['date_finish'] = $dateInsert->addYear(2)->toString('dd/MM/yyyy');
$mapperClient = new Client_Model_Mapper_Client();
$client = $mapperClient->detailClient($row->fk_id_perdata);
$data['evidence_card'] = Client_Model_Mapper_Client::buildNumRow($client);
$data['client_name'] = Client_Model_Mapper_Client::buildName($client);
$data['is_handicapped'] = Client_Model_Mapper_Client::isHandicapped($row->fk_id_perdata);
$data['electoral'] = $client->electoral;
$data['gender'] = $client->gender;
$data['client_fone'] = $client->client_fone;
$group = array();
$rowsGroup = $this->_mapper->listClientBusinessPlan($id);
foreach ($rowsGroup as $rowGroup) {
$group[] = array('name' => Client_Model_Mapper_Client::buildName($rowGroup), 'evidence_card' => Client_Model_Mapper_Client::buildNumRow($rowGroup), 'electoral' => $client->electoral, 'gender' => $client->gender, 'is_handicapped' => Client_Model_Mapper_Client::isHandicapped($client->id_perdata));
}
$expensesRows = $mapperContract->listExpensesContract($row->fk_id_fefop_contract);
$expenses = array();
$total = 0;
foreach ($expensesRows as $expense) {
$expenses[] = array('name' => $expense->description, 'amount' => (double) $expense->amount);
$total += (double) $expense->amount;
}
$excelPath = APPLICATION_PATH . '/../library/PHPExcel/';
require_once $excelPath . 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(APPLICATION_PATH . '/../public/forms/FEFOP/' . $contractFiles[$data['num_module']]);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setCellValue('P12', $data['contract']);
$activeSheet->setCellValue('Q40', $data['business_plan']);
$activeSheet->setCellValue('T8', $data['date_inserted']);
$activeSheet->setCellValue('F45', $data['date_inserted']);
$activeSheet->setCellValue('F46', $data['date_finish']);
$activeSheet->setCellValue('F16', $data['evidence_card']);
$activeSheet->setCellValue('F17', $data['electoral']);
$activeSheet->setCellValue('E19', $data['client_name']);
$activeSheet->setCellValue('D117', $data['client_name']);
$activeSheet->setCellValue('Q19', $data['client_fone']);
$activeSheet->setCellValue('O20', $data['email']);
$activeSheet->setCellValue('H25', $data['total_partisipants']);
// Responsible
$activeSheet->setCellValue('E28', $data['client_name']);
$activeSheet->setCellValue('M28', $data['evidence_card']);
$activeSheet->setCellValue('O28', $data['electoral']);
$activeSheet->setCellValue('Q28', $data['gender']);
$activeSheet->setCellValue('R28', $data['is_handicapped'] ? 'Sin' : 'Lae');
$startPerson = 29;
foreach ($group as $person) {
$activeSheet->setCellValue('E' . $startPerson, $person['name']);
$activeSheet->setCellValue('M' . $startPerson, $person['evidence_card']);
$activeSheet->setCellValue('O' . $startPerson, $person['electoral']);
$activeSheet->setCellValue('Q' . $startPerson, $person['gender']);
$activeSheet->setCellValue('R' . $startPerson, $person['is_handicapped'] ? 'Sin' : 'Lae');
$startPerson++;
}
$activeSheet->setCellValue('F38', $data['name_disivion']);
$activeSheet->setCellValue('L38', $data['name_classtimor']);
$activeSheet->setCellValue('F40', $data['project_name']);
$activeSheet->setCellValue('P45', $data['district']);
$activeSheet->setCellValue('P46', $data['sub_district']);
$activeSheet->setCellValue('P47', $data['sucu']);
$startExpense = 52;
$count = 'A';
foreach ($expenses as $expense) {
$activeSheet->setCellValue('B' . $startExpense, $count++);
$activeSheet->setCellValue('C' . $startExpense, $expense['name']);
$activeSheet->setCellValue('S' . $startExpense, $expense['amount']);
$startExpense++;
}
$activeSheet->setCellValue('B' . $startExpense, $count++);
$activeSheet->setCellValue('C' . $startExpense, 'Fundu Maneiu (600 USD + 10% rubrika sira iha leten)');
$activeSheet->setCellValue('S' . $startExpense, $reserve_fund);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$file = sprintf('Contract_%s.xlsx', $data['contract']);
header(sprintf('Content-Disposition: attachment;filename="%s"', $file));
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbDRHContract = App_Model_DbTable_Factory::get('DRHContract');
$dbDRHTrainingPlan = App_Model_DbTable_Factory::get('DRHTrainingPlan');
$dbInstitution = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbScholarityArea = App_Model_DbTable_Factory::get('ScholarityArea');
$dbOcupationTimor = App_Model_DbTable_Factory::get('PROFOcupationTimor');
$dbAddCountry = App_Model_DbTable_Factory::get('AddCountry');
$dbDRHBeneficiary = App_Model_DbTable_Factory::get('DRHBeneficiary');
$dbStaff = App_Model_DbTable_Factory::get('Staff');
$dbPerData = App_Model_DbTable_Factory::get('PerData');
$select->join(array('drhc' => $dbDRHContract), 'drhc.fk_id_fefop_contract = c.id_fefop_contract')->join(array('dhtp' => $dbDRHTrainingPlan), 'dhtp.id_drh_trainingplan = drhc.fk_id_drh_trainingplan', array('modality', 'city'))->join(array('ei' => $dbInstitution), 'ei.id_fefpeduinstitution = dhtp.fk_id_fefpeduinstitution', array('entity' => 'institution'))->join(array('sa' => $dbScholarityArea), 'sa.id_scholarity_area = dhtp.fk_id_scholarity_area', array('scholarity_area'))->join(array('ot' => $dbOcupationTimor), 'ot.id_profocupationtimor = dhtp.fk_id_profocupationtimor', array('ocupation_name_timor'))->join(array('ac' => $dbAddCountry), 'ac.id_addcountry = dhtp.fk_id_addcountry', array('country'))->join(array('db' => $dbDRHBeneficiary), 'db.id_drh_beneficiary = drhc.fk_id_drh_beneficiary', array('handicapped', 'gender'))->join(array('s' => $dbStaff), 's.id_staff = db.fk_id_staff', array('id_staff', 'fk_id_perdata'))->join(array('eis' => $dbInstitution), 's.fk_id_fefpeduinstitution = eis.id_fefpeduinstitution', array('institution', 'responsible' => 'id_fefpeduinstitution'))->join(array('cli' => $dbPerData), 's.fk_id_perdata = cli.id_perdata', array('staff_name' => new Zend_Db_Expr("CONCAT( cli.first_name, ' ', IFNULL(cli.medium_name, ''), ' ', cli.last_name )")));
return $select;
}
/**
*
* @return array
*/
public function calcTotals()
{
// Total preview to the contracts
$mapperContract = new Fefop_Model_Mapper_Contract();
$selectContract = $mapperContract->getSelect();
$statusContract = array(Fefop_Model_Mapper_Status::INITIAL, Fefop_Model_Mapper_Status::PROGRESS, Fefop_Model_Mapper_Status::REVIEWED, Fefop_Model_Mapper_Status::SEMI);
$selectContract->where('cs.id_fefop_status IN(?)', $statusContract);
$selectTotal = $this->_dbTable->select()->setIntegrityCheck(false)->from(array('t' => new Zend_Db_Expr('(' . $selectContract . ')')), array('total' => new Zend_Db_Expr('SUM(t.total)')));
// Current total
$dbTransaction = App_Model_DbTable_Factory::get('FEFOPTransaction');
$dbTransactionStatus = App_Model_DbTable_Factory::get('FEFOPTransactionStatus');
$dbTransactionHistory = App_Model_DbTable_Factory::get('FEFOPTransactionHistory');
$selectStatus = $dbTransactionStatus->select()->from(array('th' => $dbTransactionHistory), array('fk_id_fefop_transaction'))->setIntegrityCheck(false)->join(array('ts' => $dbTransactionStatus), 'ts.id_fefop_transaction_status = th.fk_id_fefop_transaction_status')->where('th.status = ?', 1)->order('id_fefop_transaction_history DESC');
$selectCurrentTotal = $dbTransaction->select()->setIntegrityCheck(false)->from(array('t' => $dbTransaction), array('total' => new Zend_Db_Expr('SUM( t.amount * IF(t.operation = "D", -1, 1) )')))->join(array('ts' => new Zend_Db_Expr('(' . $selectStatus . ')')), 't.id_fefop_transaction = ts.fk_id_fefop_transaction', array('id_fefop_transaction_status'))->where('ts.id_fefop_transaction_status <> ?', self::INACTIVE);
$selectTotalPayment = clone $selectCurrentTotal;
$selectTotalPayment->where('t.fk_id_fefop_type_transaction = ?', self::TYPE_CONTRACT);
$selectTotalReimbursement = clone $selectCurrentTotal;
$selectTotalReimbursement->where('t.fk_id_fefop_type_transaction = ?', self::TYPE_REIMBURSEMENT);
$totalContract = (double) $this->_dbTable->fetchRow($selectTotal)->total;
$totalPayment = (double) $this->_dbTable->fetchRow($selectTotalPayment)->total;
$totalReimbursement = (double) $this->_dbTable->fetchRow($selectTotalReimbursement)->total;
$totals = array('total_contract' => $totalContract, 'total_payments' => abs($totalPayment), 'total_reimbursement' => abs($totalReimbursement), 'total_real' => abs($totalPayment) - $totalReimbursement);
return $totals;
}
/**
*
*/
public function exportAction()
{
$id = $this->_getParam('id');
$contract = $this->_mapper->detail($id);
$data = $contract->toArray();
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($contract->fk_id_fefop_contract);
$data['date_start'] = $this->view->date($data['date_start']);
$data['date_finish'] = $this->view->date($data['date_finish']);
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$mapperInstitute = new Register_Model_Mapper_EducationInstitute();
$contacts = $mapperInstitute->listContacts($contract->fk_id_fefpeduinstitution)->toArray();
$beneficiaries = $this->_mapper->listBeneficiaries($id)->toArray();
foreach ($beneficiaries as $key => $beneficiary) {
$beneficiaries[$key]['complete_nane'] = Client_Model_Mapper_Client::buildName($beneficiary);
$beneficiaries[$key]['evidence'] = Client_Model_Mapper_Client::buildNumRow($beneficiary);
}
$excelPath = APPLICATION_PATH . '/../library/PHPExcel/';
require_once $excelPath . 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(APPLICATION_PATH . '/../public/forms/FEFOP/Contrato_FP_tet.xlsx');
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setCellValue('S14', $data['contract']);
$activeSheet->setCellValue('W12', $data['date_inserted']);
$activeSheet->setCellValue('E17', $data['scholarity']);
$activeSheet->setCellValue('O17', $data['level_scholarity']);
$activeSheet->setCellValue('S17', $data['class_name']);
$activeSheet->setCellValue('E20', $data['district']);
$activeSheet->setCellValue('Q20', $data['date_start']);
$activeSheet->setCellValue('Q21', $data['date_finish']);
#$activeSheet->setCellValue( 'F10', $data['institute'] );
if (!empty($contacts)) {
$activeSheet->setCellValue('G24', $contacts[0]['contact_name']);
$activeSheet->setCellValue('S24', $contacts[0]['cell_fone']);
$activeSheet->setCellValue('S25', $contacts[0]['email']);
}
$activeSheet->setCellValue('R31', $data['unit_cost']);
$activeSheet->setCellValue('J29', count($beneficiaries));
$activeSheet->setCellValue('R29', $data['amount']);
$startRow = 34;
foreach ($beneficiaries as $beneficiary) {
$activeSheet->setCellValue('F' . $startRow, $beneficiary['complete_nane']);
$activeSheet->setCellValue('L' . $startRow, $beneficiary['evidence']);
$activeSheet->setCellValue('N' . $startRow, $beneficiary['electoral']);
$activeSheet->setCellValue('P' . $startRow, $beneficiary['gender']);
$activeSheet->setCellValue('Q' . $startRow, empty($beneficiary['id_handicapped']) ? 'Lae' : 'Sin');
$activeSheet->setCellValue('R' . $startRow, $beneficiary['amount']);
$startRow++;
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$file = sprintf('Contract_%s.xlsx', $data['contract']);
header(sprintf('Content-Disposition: attachment;filename="%s"', $file));
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
*
* @param int $id
* @return array
*/
public function groupContracts($id)
{
$contracts = $this->listContractsStatements($id);
$data = array();
foreach ($contracts as $contract) {
$numContract = Fefop_Model_Mapper_Contract::buildNumById($contract->fk_id_fefop_contract);
if (!array_key_exists($numContract, $data)) {
$data[$numContract] = array('id_fefop_contract' => $contract->fk_id_fefop_contract, 'num_contract' => $numContract, 'total_contract' => 0, 'can-delete' => true, 'expenses' => array());
}
$data[$numContract]['total_contract'] += $contract['amount'];
$data[$numContract]['expenses'][] = $contract;
$data[$numContract]['can-delete'] = $data[$numContract]['can-delete'] && empty($contract['fk_id_fefop_consolidated_id']);
}
return $data;
}
/**
*
*/
public function exportAction()
{
$id = $this->_getParam('id');
$contract = $this->_mapper->detail($id);
$data = $contract->toArray();
$data['contract'] = Fefop_Model_Mapper_Contract::buildNumById($contract->fk_id_fefop_contract);
$data['date_start'] = $this->view->date($data['date_start'], 'MM/dd/yyyy');
$data['date_finish'] = $this->view->date($data['date_finish'], 'MM/dd/yyyy');
$data['date_inserted'] = $this->view->date($data['date_inserted']);
$mapperInstitute = new Register_Model_Mapper_EducationInstitute();
$contacts = $mapperInstitute->listContacts($data['fk_id_fefpeduinstitution'])->toArray();
$expenses = $this->_mapper->listExpenses($id)->toArray();
$itensExpense = $this->_mapper->listItemExpenses($id)->toArray();
$dataItensExpense = array();
foreach ($itensExpense as $item) {
if (!array_key_exists($item['fk_id_budget_category'], $dataItensExpense)) {
$dataItensExpense[$item['fk_id_budget_category']] = array();
}
$dataItensExpense[$item['fk_id_budget_category']][] = $item;
}
$excelPath = APPLICATION_PATH . '/../library/PHPExcel/';
require_once $excelPath . 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(APPLICATION_PATH . '/../public/forms/FEFOP/Contrato_RI_tet.xlsx');
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setCellValue('S10', $data['contract']);
$activeSheet->setCellValue('W8', $data['date_inserted']);
$activeSheet->setCellValue('F12', $data['institute']);
$activeSheet->setCellValue('F14', $data['district']);
$activeSheet->setCellValue('F15', $data['sub_district']);
if (!empty($contacts)) {
$activeSheet->setCellValue('G18', $contacts[0]['contact_name']);
$activeSheet->setCellValue('S18', $contacts[0]['cell_fone']);
$activeSheet->setCellValue('S19', $contacts[0]['email']);
}
$startRow = 24;
$startSubRow = 35;
$count = 'A';
$styleCell = new PHPExcel_Style();
$borders = array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '0000000')));
$styleCell->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFFFF')), 'borders' => $borders));
$ranges = array();
foreach ($expenses as $expense) {
$marker = $count++ . '.';
$expenseName = ucwords(strtolower($expense['description']));
$activeSheet->setCellValue('C' . $startRow, $marker);
$activeSheet->setCellValue('D' . $startRow, $expenseName);
$activeSheet->setCellValueExplicit('U' . $startRow, $expense['amount'], PHPExcel_Cell_DataType::TYPE_NUMERIC);
$activeSheet->mergeCells('U' . $startRow . ':V' . $startRow);
$activeSheet->setCellValue('C' . $startSubRow, $marker);
$activeSheet->setCellValue('D' . $startSubRow, $expenseName);
$subExpenseRow = $startSubRow + 1;
$subRowCount = 0;
if (!empty($dataItensExpense[$expense['id_budget_category']])) {
foreach ($dataItensExpense[$expense['id_budget_category']] as $subExpense) {
$activeSheet->setCellValue('D' . $subExpenseRow, $subExpense['description']);
$activeSheet->setCellValue('O' . $subExpenseRow, $subExpense['quantity']);
$activeSheet->setCellValue('P' . $subExpenseRow, number_format((double) $subExpense['amount_unit'], 2, '.', ','));
$activeSheet->setCellValue('Q' . $subExpenseRow, number_format((double) $subExpense['amount_total'], 2, '.', ','));
$activeSheet->setCellValue('S' . $subExpenseRow, $subExpense['comments']);
$activeSheet->mergeCells('Q' . $subExpenseRow . ':R' . $subExpenseRow);
$activeSheet->mergeCells('S' . $subExpenseRow . ':V' . $subExpenseRow);
$subRowCount++;
$subExpenseRow++;
$activeSheet->insertNewRowBefore($subExpenseRow + 1, 1);
}
$ranges[] = 'O' . ($startSubRow + 2) . ':V' . $subExpenseRow;
}
$activeSheet->insertNewRowBefore($subExpenseRow, 3);
$startSubRow = $subExpenseRow + 2;
$startRow++;
if ($startRow > 28) {
$activeSheet->insertNewRowBefore($startRow + 1, 1);
}
}
$activeSheet->removeRow($startSubRow, 4);
foreach ($ranges as $range) {
$activeSheet->setSharedStyle($styleCell, $range);
}
$dateStartCell = 'G' . ($startSubRow + 2);
$dateFinishCell = 'G' . ($startSubRow + 3);
$formulaDate = $activeSheet->getCell('L' . ($startSubRow + 2))->getValue();
$formulaDate = str_replace('98', $dateStartCell, $formulaDate);
$formulaDate = str_replace('99', $dateFinishCell, $formulaDate);
$activeSheet->setCellValueExplicit('L' . ($startSubRow + 2), $formulaDate, PHPExcel_Cell_DataType::TYPE_FORMULA);
$activeSheet->setCellValue($dateStartCell, $data['date_start']);
$activeSheet->setCellValue($dateFinishCell, $data['date_finish']);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$file = sprintf('Contract_%s.xlsx', $data['contract']);
header(sprintf('Content-Disposition: attachment;filename="%s"', $file));
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbFeContract = App_Model_DbTable_Factory::get('FEContract');
$dbInstitute = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbEnterprise = App_Model_DbTable_Factory::get('FEFPEnterprise');
$dbClient = App_Model_DbTable_Factory::get('PerData');
$dbSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
$dbScholarityArea = App_Model_DbTable_Factory::get('ScholarityArea');
$dbOcupation = App_Model_DbTable_Factory::get('PROFOcupationTimor');
$select->join(array('fec' => $dbFeContract), 'fec.fk_id_fefop_contract = c.id_fefop_contract')->joinLeft(array('ei' => $dbInstitute), 'ei.id_fefpeduinstitution = fec.fk_id_fefpeduinstitution', array())->joinLeft(array('et' => $dbEnterprise), 'et.id_fefpenterprise = fec.fk_id_fefpenterprise', array('entity' => new Zend_Db_Expr('IFNULL(enterprise_name, institution)')))->join(array('sd' => $dbSubDistrict), 'fec.fk_id_addsubdistrict = sd.id_addsubdistrict', array('sub_district'))->join(array('sa' => $dbScholarityArea), 'fec.fk_id_scholarity_area = sa.id_scholarity_area', array('scholarity_area'))->join(array('po' => $dbOcupation), 'fec.fk_id_profocupationtimor = po.id_profocupationtimor', array('ocupation_name_timor'))->join(array('cl' => $dbClient), 'cl.id_perdata = fec.fk_id_perdata', array('beneficiary' => new Zend_Db_Expr('CONVERT(CONCAT( cl.first_name, " ", IFNULL(cl.medium_name, ""), " ", cl.last_name ) USING utf8 ) COLLATE utf8_unicode_ci')))->join(array('bcc' => $dbBudgetContract), 'bcc.fk_id_fefop_contract = c.id_fefop_contract', array('contract_amount' => 'amount'))->group(array('fec.id_fe_contract'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbPERContract = App_Model_DbTable_Factory::get('PerContract');
$dbPERArea = App_Model_DbTable_Factory::get('PerArea');
$dbSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$dbAddSucu = App_Model_DbTable_Factory::get('AddSucu');
$dbEnterprise = App_Model_DbTable_Factory::get('FEFPEnterprise');
$dbFEFOPModules = App_Model_DbTable_Factory::get('FEFOPModules');
$select->join(array('pec' => $dbPERContract), 'pec.fk_id_fefop_contract = c.id_fefop_contract')->join(array('e' => $dbEnterprise), 'e.id_fefpenterprise = pec.fk_id_fefpenterprise', array('enterprise' => 'enterprise_name'))->join(array('sd' => $dbSubDistrict), 'pec.fk_id_addsubdistrict = sd.id_addsubdistrict', array('sub_district'))->join(array('sk' => $dbAddSucu), 'pec.fk_id_addsucu = sk.id_addsucu', array('sucu'))->join(array('pa' => $dbPERArea), 'pec.fk_id_per_area = pa.id_per_area', array('area' => 'description'))->join(array('fm' => $dbFEFOPModules), 'pec.fk_id_fefop_modules = fm.id_fefop_modules', array('module' => 'acronym'))->group(array('pec.id_per_contract'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbFpContract = App_Model_DbTable_Factory::get('FPContract');
$dbInstitute = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbFPAnnualPlanning = App_Model_DbTable_Factory::get('FPAnnualPlanning');
$dbFPPlanningCourse = App_Model_DbTable_Factory::get('FPPlanningCourse');
$dbStudentClass = App_Model_DbTable_Factory::get('FEFPStudentClass');
$dbUnitCost = App_Model_DbTable_Factory::get('UnitCost');
$dbScholarity = App_Model_DbTable_Factory::get('PerScholarity');
$dbLevelScholarity = App_Model_DbTable_Factory::get('PerLevelScholarity');
$select->join(array('fpc' => $dbFpContract), 'fpc.fk_id_fefop_contract = c.id_fefop_contract')->join(array('ap' => $dbFPAnnualPlanning), 'ap.id_annual_planning = fpc.fk_id_annual_planning')->join(array('pc' => $dbFPPlanningCourse), 'pc.id_planning_course = fpc.fk_id_planning_course')->join(array('ei' => $dbInstitute), 'ei.id_fefpeduinstitution = ap.fk_id_fefpeduinstitution', array('institute' => 'institution'))->join(array('sc' => $dbStudentClass), 'fpc.fk_id_fefpstudentclass = sc.id_fefpstudentclass', array('class_name'))->join(array('sl' => $dbScholarity), 'pc.fk_id_perscholarity = sl.id_perscholarity', array('scholarity', 'external_code'))->joinLeft(array('ls' => $dbLevelScholarity), 'ls.id_perlevelscholarity = sl.fk_id_perlevelscholarity', array('level_scholarity'))->join(array('uc' => $dbUnitCost), 'uc.id_unit_cost = fpc.fk_id_unit_cost', array('unit_cost' => 'cost'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbPCEContract = App_Model_DbTable_Factory::get('PCEContract');
$dbStudentClass = App_Model_DbTable_Factory::get('FEFPStudentClass');
$dbIsicDivision = App_Model_DbTable_Factory::get('ISICDivision');
$dbISICClassTimor = App_Model_DbTable_Factory::get('ISICClassTimor');
$dbScholarity = App_Model_DbTable_Factory::get('PerScholarity');
$dbAddAddress = App_Model_DbTable_Factory::get('AddAddress');
$dbAddDistrict = App_Model_DbTable_Factory::get('AddDistrict');
$dbAddSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$select->join(array('pcc' => $dbPCEContract), 'pcc.fk_id_fefop_contract = c.id_fefop_contract')->join(array('sc' => $dbStudentClass), 'pcc.fk_id_fefpstudentclass = sc.id_fefpstudentclass', array('class_name', 'date_start' => new Zend_Db_Expr('sc.start_date'), 'date_finish' => new Zend_Db_Expr('sc.schedule_finish_date'), 'duration' => new Zend_Db_Expr('DATEDIFF(sc.schedule_finish_date, sc.start_date)')))->join(array('scc' => $dbScholarity), 'scc.id_perscholarity = sc.fk_id_perscholarity', array('scholarity', 'external_code'))->joinLeft(array('add' => $dbAddAddress), 'add.fk_id_fefpeduinstitution = sc.fk_id_fefpeduinstitution', array())->joinLeft(array('addd' => $dbAddDistrict), 'addd.id_adddistrict = add.fk_id_adddistrict', array('district_course' => 'District'))->joinLeft(array('adsd' => $dbAddSubDistrict), 'adsd.id_addsubdistrict = add.fk_id_addsubdistrict', array('sub_district'))->join(array('id' => $dbIsicDivision), 'pcc.fk_id_isicdivision = id.id_isicdivision', array('name_disivion'))->join(array('ct' => $dbISICClassTimor), 'pcc.fk_id_isicclasstimor = ct.id_isicclasstimor', array('name_classtimor'))->group(array('id_pce_contract'));
return $select;
}
/**
*
* @param int $contract
* @param int $category
* @return float
*/
public function getAmountExpenseContract($contract, $category)
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$expenses = $mapperContract->listExpensesContract($contract);
$total = 0;
foreach ($expenses as $expense) {
if ($expense->id_budget_category == $category) {
$total = (double) $expense->amount;
}
}
return $total;
}
/**
*
* @param int $feContract
* @return string
*/
public function getFEGraduation($feContract)
{
$view = Zend_Layout::getMvcInstance()->getView();
$link = '<a href="%s" target="_blank">%s</a>';
$feContractNum = Fefop_Model_Mapper_Contract::buildNumRow($feContract);
$feLink = sprintf($link, $view->baseUrl('/fefop/fe-contract/edit/id/' . $feContract->id_fe_contract), $feContractNum);
$clientLink = sprintf($link, $view->baseUrl('/client/client/view/id/' . $feContract->fk_id_perdata), $feContract->beneficiary);
return sprintf(self::FE_GRADUATION, $feLink, $clientLink);
}
/**
*
* @return array
*/
public function listExpensesToConsolidate()
{
// Search items to be consolidated in bank statemetns and financial transactions
$expensesBank = $this->listExpensesToConsolidateBank();
$expensesFinancial = $this->listExpensesToConsolidateFinancial();
$identifier = '%s_%s';
// Group the bank statemtns by CONTRACT_EXPENSE
$groupExpensesBank = array();
foreach ($expensesBank as $expenseBank) {
$currentIdentifier = sprintf($identifier, $expenseBank->fk_id_fefop_contract, $expenseBank->fk_id_budget_category);
$groupExpensesBank[$currentIdentifier] = $expenseBank;
}
// Group the financial transactions by CONTRACT_EXPENSE
$groupExpensesFinancial = array();
foreach ($expensesFinancial as $expenseFinancial) {
$currentIdentifier = sprintf($identifier, $expenseFinancial->fk_id_fefop_contract, $expenseFinancial->fk_id_budget_category);
$groupExpensesFinancial[$currentIdentifier] = $expenseFinancial;
}
$toConsolidate = array();
foreach ($groupExpensesBank as $currentIdentifier => $row) {
// If there is no financial transaction to the current CONTRACT_EXPENSE
if (!array_key_exists($currentIdentifier, $groupExpensesFinancial)) {
$consolidate = false;
$financialTotal = 0;
$financialContract = 0;
} else {
// Get the financial transaction related to the current CONTRACT_EXPENSE
$financialEntry = $groupExpensesFinancial[$currentIdentifier];
$consolidate = (string) $row->total === (string) $financialEntry->total;
$financialTotal = $financialEntry->total;
$financialContract = $financialEntry->financial_contract;
}
$itemToConsolidate = array('contract' => Fefop_Model_Mapper_Contract::buildNumById($row->fk_id_fefop_contract), 'id_contract' => $row->fk_id_fefop_contract, 'bank_amount' => $row->total, 'consolidate' => $consolidate, 'expense' => $row->expense, 'component' => $row->component, 'total_financial' => $financialTotal, 'bank_rows' => explode(',', $row->bank_contract), 'financial_rows' => explode(',', $financialContract));
$toConsolidate[$currentIdentifier] = $itemToConsolidate;
unset($groupExpensesBank[$currentIdentifier]);
unset($groupExpensesFinancial[$currentIdentifier]);
}
foreach ($groupExpensesFinancial as $currentIdentifier => $row) {
$itemToConsolidate = array('contract' => Fefop_Model_Mapper_Contract::buildNumById($row->fk_id_fefop_contract), 'id_contract' => $row->fk_id_fefop_contract, 'bank_amount' => 0, 'consolidate' => false, 'expense' => $row->expense, 'component' => $row->component, 'total_financial' => $row->total, 'bank_rows' => array(), 'financial_rows' => explode(',', $row->financial_contract));
$toConsolidate[$currentIdentifier] = $itemToConsolidate;
}
$this->_session->bank_to_consolidate = $toConsolidate;
return $toConsolidate;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbRiContract = App_Model_DbTable_Factory::get('RIContract');
$dbInstitute = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
$select->join(array('ric' => $dbRiContract), 'ric.fk_id_fefop_contract = c.id_fefop_contract')->join(array('ei' => $dbInstitute), 'ei.id_fefpeduinstitution = ric.fk_id_fefpeduinstitution', array('institute' => 'institution'))->join(array('sd' => $dbSubDistrict), 'ric.fk_id_addsubdistrict = sd.id_addsubdistrict', array('sub_district'))->joinLeft(array('bcc' => $dbBudgetContract), 'bcc.fk_id_fefop_contract = c.id_fefop_contract', array())->group(array('ric.id_ri_contract'));
return $select;
}
/**
*
*/
public function addExpenseAction()
{
$this->_helper->layout()->disableLayout();
$idContract = $this->_getParam('id_contract');
$row = $this->_getParam('row');
if (!empty($row)) {
$this->view->row = $row;
$idContract = $row->fk_id_fefop_contract;
}
$this->view->idContract = $idContract;
$mapperContract = new Fefop_Model_Mapper_Contract();
$expenses = $mapperContract->listExpensesContract($idContract);
$optExpenses = array('' => '');
$cont = 'A';
foreach ($expenses as $expense) {
$optExpenses[$expense->id_budget_category] = $cont++ . ' - ' . $expense->description;
}
$mapperExpense = new Fefop_Model_Mapper_Expense();
$expensesAdditional = $mapperExpense->listAll(Fefop_Model_Mapper_ExpenseType::ADDITIONALS);
foreach ($expensesAdditional as $expense) {
$optExpenses[$expense->id_budget_category] = $expense->description;
}
$this->view->optionsBudgetCategory = $optExpenses;
}
