public function exportDecisions($export_key)
{
if (!Yii::app()->user->checkAccess("Export Decisions")) {
throw new CHttpException(403, 'You are not authorized to access this feature');
}
if (isset($export_key)) {
$export_key = $export_key;
} else {
throw new CHttpException(404, 'Invalid request');
}
//get decision source
$decisionSourceModel = EacLookup::model()->find('`key` = :key', [':key' => $export_key]);
$models = EacDecision::model()->findAll('decision_source_id = :id', [':id' => $decisionSourceModel->id]);
spl_autoload_unregister(array('YiiBase', 'autoload'));
$phpExcelPath = Yii::getPathOfAlias('application.vendor.phpoffice');
include $phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("EAMS Country TZ")->setLastModifiedBy("EAMs")->setTitle("EAMS TZ Decisions Export")->setSubject("EAMS TZ Decisions Export")->setDescription("EAMS TZ Decisions Export")->setKeywords("office 2007 openxml php")->setCategory("decisions");
$objPHPExcel->getActiveSheet()->setTitle('decisions');
// Add heading row
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Decision Reference')->setCellValue('B1', 'Description')->setCellValue('C1', 'Responsibility Center')->setCellValue('D1', 'Time Frame')->setCellValue('E1', 'Performance Indicators')->setCellValue('F1', 'Implementation Status')->setCellValue('G1', 'Status / Comments')->setCellValue('H1', 'ImplementationStatusID')->setCellValue('I1', 'id');
$rowCount = 2;
foreach ($models as $model) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$rowCount}", $model->decision_reference)->setCellValue("B{$rowCount}", $model->description)->setCellValue("C{$rowCount}", $model->responsibility_center)->setCellValue("D{$rowCount}", $model->time_frame)->setCellValue("E{$rowCount}", $model->performance_indicators)->setCellValue("F{$rowCount}", $model->implementation_status_id)->setCellValue("G{$rowCount}", $model->sectoral_council_id . 'Status / Comments')->setCellValue("H{$rowCount}", $model->implementation_status_id)->setCellValue("I{$rowCount}", $model->eams_central_id);
$rowCount++;
}
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="template.xlsx"');
// header('Cache-Control: max-age=0');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//$objWriter->save('php://output');
$targetDir = Yii::getPathOfAlias('webroot') . '/uploads/exports/';
$filename = 'desicion_export_' . md5(time() . uniqid('export', true)) . '.xlsx';
$objWriter->save($targetDir . $filename);
spl_autoload_register(array('YiiBase', 'autoload'));
$fileExportModel = new EamsFilesExport();
$fileExportModel->name = $filename;
$fileExportModel->export_key = $export_key;
$fileExportModel->mime_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
$fileExportModel->file_extension = 'xlsx';
$fileExportModel->file_size = filesize($targetDir . $filename);
$fileExportModel->date_created = date('Y-m-d H:i:s');
$fileExportModel->save();
$this->sendFileToEamsCentral($fileExportModel);
}
public function getStatusColor($data)
{
if (!isset($data->implementation_status_id)) {
return TbHtml::LABEL_COLOR_IMPORTANT;
}
$model = EacLookup::model()->find('type=:type AND id=:status_id', array(':type' => EacLookup::IMPLEMENTATION_STATUS, ':status_id' => $data->implementation_status_id));
if ($model) {
if ($model->color == '#468847') {
#return TbHtml::LABEL_COLOR_SUCCESS;
return 'green';
}
if ($model->color == '#D24842') {
#return TbHtml::LABEL_COLOR_IMPORTANT;
return 'red';
}
if ($model->color == '#F89406') {
#return TbHtml::LABEL_COLOR_WARNING;
return 'yellow';
}
}
return '';
}
/**
* Returns the data model based on the primary key given in the GET variable.
* If the data model is not found, an HTTP exception will be raised.
* @param integer $id the ID of the model to be loaded
* @return EacLookup the loaded model
* @throws CHttpException
*/
public function loadModel($id)
{
$model = EacLookup::model()->findByPk($id);
if ($model === null) {
throw new CHttpException(404, 'The requested page does not exist.');
}
return $model;
}
public function loadDecisionDataFromFile($model)
{
//get decision source
$decisionSourceModel = EacLookup::model()->find('`key` = :key', [':key' => $model->import_key]);
//extract data fro file
spl_autoload_unregister(array('YiiBase', 'autoload'));
$phpExcelPath = Yii::getPathOfAlias('application.vendor.phpoffice');
include $phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php';
spl_autoload_register(array('YiiBase', 'autoload'));
$filename = Yii::getPathOfAlias('webroot') . '/uploads/imports/' . $model->name;
$objPHPExcel = PHPExcel_IOFactory::load($filename);
$worksheet = $objPHPExcel->getSheet(0);
$objPHPExcel->getActiveSheet()->getStyle('C2')->getNumberFormat()->setFormatCode('mm-dd-yyyy');
$headingColsCount = 0;
foreach ($worksheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
$cellContent = $cell->getValue();
$data[$cell->getRow()][$cell->getColumn()] = $cellContent;
if ($headingColsCount < 15) {
if (preg_match('/^id$/i', $cellContent)) {
$headingRow = $cell->getRow();
$idCol = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^Decision Reference$/i', $cellContent)) {
$decisionReferenceCol = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^Decision Date$/i', $cellContent)) {
$decisionDateCol = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^Description$/i', $cellContent)) {
$descriptionCol = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^BudgetaryImplications$/i', $cellContent)) {
$budgetaryImplicationCol = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^TimeFrame$/i', $cellContent)) {
$timeFrameCol = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^Performance Indicators$/i', $cellContent)) {
$performanceIndicatorsCols = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^Responsibility Center$/i', $cellContent)) {
$responsibilityCenterCols = $cell->getColumn();
$headingColsCount++;
}
if (preg_match('/^MeetingNo$/i', $cellContent)) {
$meetingNoCol = $cell->getColumn();
$headingColsCount++;
}
if ($model->import_key === 'sc') {
//sectoral council decisions
if (preg_match('/^sectoral_council_id$/i', $cellContent)) {
$sectoralCouncilIdCol = $cell->getColumn();
$headingColsCount++;
}
}
}
}
}
unset($data[$headingRow]);
$readData = [];
foreach ($data as $dataItem) {
$decision = [];
$decision['eams_central_id'] = $dataItem[$idCol];
$decision['decision_reference'] = $dataItem[$decisionReferenceCol];
$decision['decision_source_id'] = $decisionSourceModel->id;
$decision['sectoral_council_id'] = @$sectoralCouncilIdCol;
//save null if not present
$formattedDate = $this->excelDateToPhp($dataItem[$decisionDateCol]);
$decision['decision_date'] = $formattedDate;
$decision['description'] = $dataItem[$descriptionCol];
$decision['budgetary_implications'] = $dataItem[$budgetaryImplicationCol];
$decision['time_frame'] = $dataItem[$timeFrameCol];
$decision['performance_indicators'] = $dataItem[$performanceIndicatorsCols];
$decision['responsibility_center'] = $dataItem[$responsibilityCenterCols];
$decision['meeting_no'] = $dataItem[$meetingNoCol];
$decision['date_created'] = date('Y-m-d H:i:s');
$decision['create_user_id'] = Yii::app()->user->id;
$readData[] = $decision;
}
return $readData;
}
<div class="form">
<?php $form=$this->beginWidget('bootstrap.widgets.TbActiveForm', array(
'id'=>'eac-lookup-form',
// Please note: When you enable ajax validation, make sure the corresponding
// controller action is handling ajax validation correctly.
// There is a call to performAjaxValidation() commented in generated controller code.
// See class documentation of CActiveForm for details on this.
'enableAjaxValidation'=>false,
)); ?>
<p class="help-block">Fields with <span class="required">*</span> are required.</p>
<?php echo $form->errorSummary($model); ?>
<?php echo $form->dropDownListControlGroup($model,'type', EacLookup::getLookupTypes(),array('span'=>5)); ?>
<?php echo $form->textFieldControlGroup($model,'description',array('span'=>5,'maxlength'=>500)); ?>
<?php echo $form->textFieldControlGroup($model,'eams_central_id',array('span'=>5)); ?>
<div class="form-actions">
<?php echo TbHtml::submitButton($model->isNewRecord ? 'Create' : 'Save',array(
'color'=>TbHtml::BUTTON_COLOR_PRIMARY,
'size'=>TbHtml::BUTTON_SIZE_DEFAULT,
)); ?>
</div>
<?php $this->endWidget(); ?>
</div><!-- form -->
<?php $form = $this->beginWidget('bootstrap.widgets.TbActiveForm',array(
'id'=>'upload_decisions-form',
// Please note: When you enable ajax validation, make sure the corresponding
// controller action is handling ajax validation correctly.
// There is a call to performAjaxValidation() commented in generated controller code.
// See class documentation of CActiveForm for details on this.
'enableAjaxValidation'=>false,
'htmlOptions'=>array(
'enctype'=>'multipart/form-data'
)
));?>
<fieldset>
<legend>Upload Decisions</legend>
<?php if(Yii::app()->user->hasFlash('success')):?>
<?php echo TbHtml::alert(TbHtml::ALERT_COLOR_SUCCESS, Yii::app()->user->getFlash('success'))?>
<?php endif;?>
<?php echo $form->errorSummary($model); ?>
<?php echo $form->dropDownListControlGroup($model,'decision_source', TbHtml::listData(EacLookup::model()->findAll('type=:type',array(':type'=> EacLookup::DECISION_SOURCE)),"id","description"),
array('prompt'=>'--select--')); ?>
<?php echo $form->fileFieldControlGroup($model,'upload_file'); ?>
<div class="form-actions">
<?php echo TbHtml::submitButton('Import Decisions',array(
'color'=>TbHtml::BUTTON_COLOR_SUCCESS,
'size'=>TbHtml::BUTTON_SIZE_DEFAULT,
)); ?>
</div>
</fieldset>
<?php $this->endWidget();?>
</div>
<div class="form">
<?php $form=$this->beginWidget('bootstrap.widgets.TbActiveForm', array(
'id'=>'eac-status-log-form',
// Please note: When you enable ajax validation, make sure the corresponding
// controller action is handling ajax validation correctly.
// There is a call to performAjaxValidation() commented in generated controller code.
// See class documentation of CActiveForm for details on this.
'enableAjaxValidation'=>false,
)); ?>
<?php echo $form->errorSummary($model); ?>
<?php echo $form->dropDownListControlGroup($model,'status_id',TbHtml::listData(EacLookup::model()->findAll('type=:type',array(':type'=> EacLookup::IMPLEMENTATION_STATUS)),"id","description"),
array(
'empty'=>'--select--'
)); ?>
<?php echo $form->textAreaControlGroup($model,'status_narrative',array('rows'=>6,'span'=>8)); ?>
<?php echo $form->textAreaControlGroup($model,'remarks',array('rows'=>6,'span'=>8)); ?>
<div class="form-actions">
<?php echo TbHtml::submitButton($model->isNewRecord ? 'Submit' : 'Save',array(
'color'=>TbHtml::BUTTON_COLOR_SUCCESS,
'size'=>TbHtml::BUTTON_SIZE_DEFAULT,
)); ?>
</div>
<?php $this->endWidget(); ?>
public function actionSaveDecisions()
{
if (isset($_POST['data'])) {
$data = json_decode($_POST['data']);
$internalScId = NULL;
$eamsCentralScId = $data[0]->sectoral_council_id;
if (!empty($eamsCentralScId)) {
$scModel = EacLookup::model()->find('eams_central_id=:id', [':id' => $eamsCentralScId]);
$internalScId = $scModel->id;
}
foreach ($data as $item) {
$decision = new EacDecision();
$decision->eams_central_id = $item->eams_central_id;
$decision->decision_reference = $item->decision_reference;
$decision->decision_source_id = $item->decision_source_id;
$decision->sectoral_council_id = $internalScId;
$decision->decision_date = $item->decision_date;
$decision->description = $item->description;
$decision->budgetary_implications = $item->budgetary_implications;
$decision->time_frame = $item->time_frame;
$decision->performance_indicators = $item->performance_indicators;
$decision->responsibility_center = $item->responsibility_center;
$decision->meeting_no = $item->meeting_no;
$decision->date_created = date('Y-m-d H:i:s');
$decision->create_user_id = Yii::app()->user->id;
if (!$decision->decisionExists()) {
$decision->save();
}
//if
}
//foreach
}
}