/**
*
* @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->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
*/
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;
}
/**
*
*/
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;
}
/**
*
* @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;
}
/**
*
*/
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;
}
/**
*
* @param int $id
* @return array
*/
public function groupContractRecept($id)
{
$contracts = $this->listContractsReceipt($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'] && $contract['id_fefop_transaction_status'] == self::ACTIVE;
}
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']);
$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;
}
