/** * */ 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; }