public function createExcelFile($file_name)
{
$this->init();
//Add some headers
$this->objPHPExcel->getProperties()->setCreator("Yupe! UnnamedTeam")->setLastModifiedBy("Yupe! UnnamedTeam")->setTitle("БКТ. Прайс-лист товаров от " . Yii::app()->dateFormatter->format('dd.mm.yyyy', time()))->setSubject("Office 2007 XLSX Test Document")->setKeywords("БКТ прайс-лист")->setCategory("БКТ");
//Rename worksheet
$this->activeList->setTitle('Прайс-лист');
//Add some header data
$this->activeList->setCellValue('A1', 'Прайс-лист')->setCellValue('A3', 'ООО "БумКанцТорг"')->setCellValue('A4', 'Адрес: 460009, Оренбургская обл, Оренбург, Пролетарская, дом № 135, тел.: (3532) 56-17-62')->setCellValue('A5', 'В валютах цен.')->setCellValue('A6', 'Цены указаны на ' . Yii::app()->dateFormatter->format('dd.mm.yyyy', time()));
//Create table header
$this->activeList->getColumnDimension('A')->setWidth(75);
$this->activeList->getColumnDimension('B')->setWidth(20);
$this->activeList->getColumnDimension('C')->setWidth(15);
$this->activeList->mergeCells('A9:A10')->setCellValue('A9', 'Ценовая группа/ Номенклатура/ Характеристика')->mergeCells('B9:B10')->setCellValue('B9', 'Номенклатура.Артикул')->mergeCells('C9:D9')->setCellValue('C9', 'Розничные')->setCellValue('C10', 'Цены')->setCellValue('D10', 'Ед.');
//Меняем активную клетку на 11, все остальное было шапкой
$this->activeRow = 11;
//Create categories and products
//получаем массив всех категорий в виде дерева
$data = StoreCategory::model()->getMenuList(10);
if (!$this->createCatalogTree($data)) {
throw new CHttpException(400, Yii::t('Exchange1cModule.main', "Произошла ошибка во время заполнения листа продуктами"));
}
if (!$this->stylingSheet()) {
throw new CHttpException(400, Yii::t('Exchange1cModule.main', "Произошла ошибка во время выполнения функции декорирования листа"));
}
$this->objPHPExcel->setActiveSheetIndex(0);
//Write in file
$objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
$objWriter->save(Yii::getPathOfAlias('public') . '/uploads/files/store/' . $file_name);
return true;
}
public function actionExcel()
{
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
//$objReader = PHPExcel_IOFactory::createReader('Excel5');
//$objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR."templates".DIRECTORY_SEPARATOR."PO_Template.xls");
$data = $_SESSION['reportdata-excel'];
$data->setPagination(false);
//$criteria = $_SESSION['reportcriteria-excel'];
$objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)->setLastModifiedBy(Yii::app()->user->name)->setTitle("Report");
//->setSubject("Office 2007 XLSX Test Document")
//->setDescription("Sales Order#")
//->setKeywords("office 2007 openxml php")
//->setCategory("Test result file");
$row = 3;
$i = 1;
// Write the sale items now
$items = $data->getData();
foreach ($items as $item) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B" . $row, $i)->setCellValue("C" . $row, $item["compName"])->setCellValue("D" . $row, $item["shippedSum"]);
$i++;
$row++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Redirect output to a client’s web browser
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Report.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
public function actionExcel($id)
{
$po = $this->loadModel($id);
$poItemCriteria = new CDbCriteria();
$poItemCriteria->with = array('material');
$poItemCriteria->compare('po_number', $id);
$poItemDataProvider = new CActiveDataProvider('PoItems', array('criteria' => $poItemCriteria, 'pagination' => false));
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR . "templates" . DIRECTORY_SEPARATOR . "PO_Template.xls");
$objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)->setLastModifiedBy(Yii::app()->user->name)->setTitle("PO Order-" . $id);
//->setSubject("Office 2007 XLSX Test Document")
//->setDescription("Sales Order#")
//->setKeywords("office 2007 openxml php")
//->setCategory("Test result file");
// Add the data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', $po->po_number)->setCellValue('C3', Yii::app()->dateFormatter->formatDateTime($po->maturity_date, "short", null))->setCellValue('C4', Yii::app()->dateFormatter->formatDateTime($po->created, "short", null))->setCellValue('C5', Yii::app()->dateFormatter->formatDateTime($po->updated, "short", null))->setCellValue('E2', $po->comp->name)->setCellValue('E3', $po->contact)->setCellValue('E4', $po->contact_telephone)->setCellValue('C6', $po->paymentTerm->description)->setCellValue('C7', $po->is_open ? "Open" : "Closed")->setCellValue('A8', "Comments:\r" . $po->comments);
$row = 13;
//$i = 1;
// Write the sale items now
$items = $poItemDataProvider->getData();
foreach ($items as $item) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B" . $row, $item->material->cat->description . "-" . $item->material->description)->setCellValue("D" . $row, $item->qty)->setCellValue("E" . $row, $item->qty_units)->setCellValue("F" . $row, $item->unit_price)->setCellValue("G" . $row, $item->price_units)->setCellValue("H" . $row, $item->qty_recieved)->setCellValue("I" . $row, $item->qty_diff);
//$i++;
$row++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Redirect output to a client’s web browser
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . '"PurchaseOrder-' . $id . '.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
/**
* Register autoloader.
*/
public static function init()
{
if (!self::$_isInitialized) {
spl_autoload_unregister(array('YiiBase', 'autoload'));
require dirname(__FILE__) . DIRECTORY_SEPARATOR . 'vendor' . DIRECTORY_SEPARATOR . '/PHPExcel.php';
spl_autoload_register(array('YiiBase', 'autoload'));
self::$_isInitialized = true;
}
}
public function actionExportExcel()
{
$id = Yii::app()->request->getParam('id');
$model = $this->loadModel($id);
$usuario = IdentificacionUsuario::model()->findByAttributes(array('checklist_id' => $id));
$informacionEquipo = InformacionEquipo::model()->findByAttributes(array('checklist_id' => $id));
$impresora = Impresora::model()->findByAttributes(array('checklist_id' => $id));
$foto = Foto::model()->findByAttributes(array('checklist_id' => $id));
$migracion = Migracion::model()->findByAttributes(array('checklist_id' => $id));
$configuracionRed = ConfiguracionRed::model()->findByAttributes(array('checklist_id' => $id));
$tareas = ChecklistHasTarea::model()->findAllByAttributes(array('checklist_id' => $id));
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$sheet = $objPHPExcel->getActiveSheet(0);
$sheet->setCellValueByColumnAndRow(1, 2, "Checklist Usuario Final");
$sheet->mergeCells('B2:E2');
//INFORMACION USUARIO
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B3:E3')->setCellValue('B3', 'Informacion Usuario')->setCellValue('B4', 'Nombre')->setCellValue('C4', $usuario->nombre)->setCellValue('D4', 'Cliente')->setCellValue('E4', isset($model->cliente->nombre) ? $model->cliente->nombre : '')->setCellValue('B5', 'Departamento')->setCellValue('C5', isset($model->cliente->departamento->nombre) ? $model->cliente->departamento->nombre : '')->setCellValue('D5', 'Email')->setCellValue('E5', $usuario->email)->setCellValue('B6', 'Telefono')->setCellValue('C6', $usuario->telefono)->setCellValue('D6', 'Region')->setCellValue('E6', isset($model->cliente->region->nombre) ? $model->cliente->region->nombre : '')->setCellValue('B7', 'Comuna')->setCellValue('C7', isset($model->cliente->comuna->nombre) ? $model->cliente->comuna->nombre : '')->setCellValue('D7', 'Direccion')->setCellValue('E7', isset($model->cliente->direccion) ? $model->cliente->direccion : '');
$bold = array('font' => array('color' => array('rgb' => '000000'), 'bold' => true));
$background = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'ffffff')));
$background2 = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '2644a0')), 'font' => array('color' => array('rgb' => 'ffffff')));
$objPHPExcel->getActiveSheet(0)->getStyle('B4:E32')->applyFromArray($background);
$objPHPExcel->getActiveSheet(0)->getStyle('B4:B32')->applyFromArray($bold);
$objPHPExcel->getActiveSheet(0)->getStyle('D4:D32')->applyFromArray($bold);
$objPHPExcel->getActiveSheet(0)->getStyle('B3:E3')->applyFromArray($background2);
$objPHPExcel->getActiveSheet(0)->getStyle('B9:E9')->applyFromArray($background2);
$objPHPExcel->getActiveSheet(0)->getStyle('B13:E13')->applyFromArray($background2);
$objPHPExcel->getActiveSheet(0)->getStyle('B17:E17')->applyFromArray($background2);
$objPHPExcel->getActiveSheet(0)->getStyle('B20:E20')->applyFromArray($background2);
$objPHPExcel->getActiveSheet(0)->getStyle('B26:E26')->applyFromArray($background2);
//INFORMACION EQUIPO
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B9', 'Informacion Equipo')->setCellValue('B10', 'Ram')->setCellValue('C10', $informacionEquipo->ram)->setCellValue('D10', 'Disco')->setCellValue('E10', $informacionEquipo->disco)->setCellValue('B11', 'Cpu')->setCellValue('C11', isset($informacionEquipo->cpu->modelo) ? $informacionEquipo->cpu->modelo : '')->setCellValue('D11', 'Monitor')->setCellValue('E11', isset($informacionEquipo->monitor->modelo) ? $informacionEquipo->monitor->modelo : '');
//IMPRESORA
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B13', 'Impresora')->setCellValue('B14', 'Puerto')->setCellValue('C14', $impresora->puerto)->setCellValue('D14', 'Pdfcmon')->setCellValue('E14', $impresora->pdfcmon)->setCellValue('B15', 'Usb001')->setCellValue('C15', $impresora->usb001)->setCellValue('D15', 'Ip')->setCellValue('E15', $impresora->ip);
//SISTEMA OPERATIVO
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B17', 'Sistema Operativo')->setCellValue('B18', 'Sistema Operativo')->setCellValue('C18', isset($model->sistemaOperativo->version) ? $model->sistemaOperativo->version : '');
//CONFIGURACION RED
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B20', 'Configuracion Red')->setCellValue('B21', 'Usuario')->setCellValue('C21', $configuracionRed->usuario)->setCellValue('D21', 'Dominio')->setCellValue('E21', $configuracionRed->dominio)->setCellValue('B22', 'Nombre Maquina')->setCellValue('C22', $configuracionRed->nombre_maquina)->setCellValue('D22', 'Red')->setCellValue('E22', $configuracionRed->red)->setCellValue('B23', 'Mascara')->setCellValue('C23', $configuracionRed->mascara)->setCellValue('D23', 'Puerto Enlace')->setCellValue('E23', $configuracionRed->puerto_enlace)->setCellValue('B24', 'Dns Preferencial')->setCellValue('C24', $configuracionRed->dns_preferencial)->setCellValue('D24', 'Dns Alternativo')->setCellValue('E24', $configuracionRed->dns_alternativo);
//MIGRACION
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B26', 'Migracion')->setCellValue('B27', 'Antes')->setCellValue('B28', 'Ram Antes')->setCellValue('C28', $migracion->ram_antes)->setCellValue('D28', 'Disco Antes')->setCellValue('E28', $migracion->disco_antes)->setCellValue('B29', 'Cpu Antes')->setCellValue('C29', isset($migracion->cpuIdAntes->modelo) ? $migracion->cpuIdAntes->modelo : '')->setCellValue('D29', 'Monitor Antes')->setCellValue('E29', isset($migracion->monitorIdAntes->modelo) ? $migracion->monitorIdAntes->modelo : '')->setCellValue('B30', 'Despues')->setCellValue('B31', 'Ram Despues')->setCellValue('C31', $migracion->ram_despues)->setCellValue('D31', 'Disco Despues')->setCellValue('E31', $migracion->disco_despues)->setCellValue('B32', 'Cpu Despues')->setCellValue('C32', isset($migracion->cpuIdDespues->modelo) ? $migracion->cpuIdDespues->modelo : '')->setCellValue('D32', 'Monitor Despues')->setCellValue('E32', isset($migracion->monitorIdDespues->modelo) ? $migracion->monitorIdDespues->modelo : '');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Checklist');
$objPHPExcel->setActiveSheetIndex(0);
foreach (range('A', 'I') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
ob_end_clean();
ob_start();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
Yii::app()->end();
}
public static function exportExcel($data, $file)
{
## add header
if (count($data) > 0) {
array_unshift($data, $data[0]);
foreach ($data[0] as $k => $i) {
$data[0][$k] = $k;
}
}
## generate excel
Yii::import('ext.phpexcel.XPHPExcel');
$phpExcelObject = XPHPExcel::createPHPExcel();
$phpExcelObject->getActiveSheet()->fromArray($data, null, 'A1');
foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}
Helper::generateExcel($phpExcelObject, $file);
}
public function actionimportExcelTable($id, $issueId)
{
//step id
if (isset($_FILES['Excel'])) {
$post = Result::model()->importExcelTable($_FILES['Excel']);
//var_dump($post);
Yii::import('ext.phpexcel.IOFactory');
Yii::import('ext.phpexcel.*');
Yii::import('ext.phpexcel.XPHPExcel');
Yii::import('ext.phpexcel.shared.string');
$newsheet = XPHPExcel::createPHPExcel();
$inputFileName = $post;
try {
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch (PHPExcel_Reader_Exception $e) {
die('Error loadinng file: ' . $post);
//$e->getMessage());
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumnIndex = $sheet->getHighestColumn();
$index = array('A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8);
$highestColumn = $index[$highestColumnIndex];
$index = array_slice($index, 0, $highestColumn);
$element = new Element();
$element->stepId = $id;
$element->typeId = 13;
$element->save();
$elementId = $element->id;
$step = $this->loadModel($id);
for ($row = 1; $row <= $highestRow; $row++) {
$rows[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow('A', $row)->getValue();
}
$columns = array();
foreach ($index as $key => $position) {
$columns[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, 1)->getValue();
}
//$salida=$step->addGrid($element, $rows, $columns);
$issue = new Issue();
for ($row = 1; $row <= $highestRow; $row++) {
foreach ($index as $key => $position) {
if ($row >= 1 && $position > 0) {
$result = new Result();
$result->elementId = $elementId;
$result->issueId = $issueId;
$result->value = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, $row)->getValue();
$result->colonne = $position - 1;
$result->ligne = $row - 2;
throw new CHttpException(403, 'row: ' . $result->ligne . '. Columna: ' . $result->colonne . '.Value: ' . $result->value);
$result->save();
}
}
}
//$this->redirect(array('view','id'=>$id));
} else {
throw new CHttpException(403, "Failure loading file");
}
}
public function actionexcelFileOutput14($id, $issueId)
{
Yii::import('ext.phpexcel.XPHPExcel');
$columnCont = 1;
$rowCont = 1;
$newsheet = XPHPExcel::createPHPExcel();
$element = Element::model()->findByPk($id);
$i = 4;
$r = 0;
$resCol = Value::model()->findAll("elementId={$id} and colonne=1");
foreach ($element->columns as $colonne) {
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($columnCont, 1, $colonne->value);
$i++;
$columnCont++;
}
foreach ($element->rows as $row) {
$rowCont++;
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $rowCont, $row->value);
for ($index = 0; $index < $columnCont; $index++) {
$res = $element->getResultTableForExcel($issueId, $id, $index, $r);
switch ($res) {
case "off":
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "No");
break;
case "checked":
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "Yes");
break;
default:
}
}
$r++;
}
$newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007');
$objWriter->save('php://output');
}
public function actionExcel()
{
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("")->setCategory("");
if (Usuario::model()->findByPk(Yii::app()->user->id)->usu_rol == "admins") {
$var = Evaluacion::model()->findAll();
} else {
$var = Evaluacion::model()->findAllByAttributes(array('emp_rut' => Usuario::model()->findByPk(Yii::app()->user->id)->emp_rut));
}
foreach ($var as $key => $value) {
if (Telefono::model()->findByAttributes(array('tel_numero' => $value->tel_numero, 'emp_rut' => $value->emp_rut)) != null) {
$fono = Telefono::model()->findByAttributes(array('tel_numero' => $value->tel_numero, 'emp_rut' => $value->emp_rut))->tel_mac;
} else {
$fono = null;
}
$var2 = EvaluacionPregunta::model()->findAllByAttributes(array('eva_id' => $value->eva_id));
// datos de las respuestas a la evaluacion a imprimir
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($key + 2), $key + 1)->setCellValue('B' . ($key + 2), $value->eva_fecha)->setCellValue('C' . ($key + 2), $value->usu_rut)->setCellValue('D' . ($key + 2), $value->emp_rut)->setCellValue('E' . ($key + 2), $value->emp_nombre)->setCellValue('F' . ($key + 2), $value->tel_numero)->setCellValue('G' . ($key + 2), $fono);
$c = 7;
$k = 0;
$j = null;
foreach ($var2 as $key2 => $value2) {
if (Pregunta::model()->findByPk($value2->pre_id) != null) {
$descripcion = Pregunta::model()->findByPk($value2->pre_id)->pre_descripcion;
if ($value2->pre_respuesta) {
$respuesta = "SI";
} else {
$respuesta = "NO";
}
} else {
$descripcion = "Sin resultados";
}
if ($c + $key2 > 25) {
$j = intval(($c + $key2) / 26);
$k = ($c + $key2) % 26;
}
if ($j == null) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($key2 + 65 + $c) . ($key + 2), $descripcion);
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . ($key + 2), $descripcion);
}
$c++;
if ($c + $key2 > 25) {
$j = intval(($c + $key2) / 26);
$k = ($c + $key2) % 26;
}
if ($j == null) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($key2 + 65 + $c) . ($key + 2), $respuesta);
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . ($key + 2), $respuesta);
}
}
}
$j = null;
$k = 0;
$c = 1;
for ($i = 7; $i < 107; $i++) {
if ($i > 25) {
$j = intval($i / 26);
$k = $i - intval($i / 26) * 26;
}
if ($i % 2 != 0) {
if ($j == null) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . '1', "Pregunta" . " " . $c);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($i + 65))->setWidth(50);
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . '1', "Pregunta" . " " . $c);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($j + 64) . chr($k + 65))->setWidth(50);
}
} else {
if ($j == null) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . '1', "Respuesta" . " " . $c);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($i + 65))->setWidth(15);
$c++;
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . '1', "Respuesta" . " " . $c);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($j + 64) . chr($k + 65))->setWidth(15);
$c++;
}
}
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'N°')->setCellValue('B1', 'fecha')->setCellValue('C1', 'Usuario')->setCellValue('D1', 'Rut Empresa')->setCellValue('E1', 'Empresa')->setCellValue('F1', 'N° Telefono')->setCellValue('G1', 'Mac Telefono');
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(25);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="informe.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
public function actionGenerateExcelTemplate()
{
$cols = json_decode($_GET['columns'], true);
Yii::import('ext.phpexcel.XPHPExcel');
$phpExcelObject = XPHPExcel::createPHPExcel();
$phpExcelObject->getActiveSheet()->fromArray($cols, null, 'A1');
foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) {
$phpExcelObject->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}
$this->generateExcel($phpExcelObject, 'contoh-template');
}
public function actionAdminAbonos()
{
$model = new Contrato('search');
$model->unsetAttributes();
// clear any default values
if (isset($_GET['Contrato'])) {
$model->attributes = $_GET['Contrato'];
}
$meses = array();
for ($i = 1; $i <= 12; $i++) {
$meses[] = array('id' => str_pad($i, 2, "0", STR_PAD_LEFT), 'nombre' => Tools::fixMes($i));
}
$agnos = array();
$agnoInicio = 2000;
$agnoFin = (int) date('Y') + 10;
for ($i = $agnoInicio; $i < $agnoFin; $i++) {
$agnos[] = array('id' => $i, 'nombre' => $i);
}
$filtroModel = new EstadoCuentaForm();
$filtroModel->agnoH = date('Y');
$filtroModel->mesH = date('m');
if ($filtroModel->mesH == '01') {
$filtroModel->mesD = '12';
$filtroModel->agnoD = (int) $filtroModel->agnoH - 1;
} else {
$mes = (int) $filtroModel->mesH;
$filtroModel->mesD = str_pad($mes - 1, 2, "0", STR_PAD_LEFT);
$filtroModel->agnoD = $filtroModel->agnoH;
}
if (isset($_POST['EstadoCuentaForm'])) {
$filtroModel->attributes = $_POST['EstadoCuentaForm'];
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$contrato = Contrato::model()->findByPk($filtroModel->contratoId);
if ($contrato == null) {
die;
}
if (!$contrato->estaAsociadoAPropietario(Yii::app()->user->id)) {
die;
}
$sheet->setCellValue('A1', 'Movimientos de Cliente');
$sheet->mergeCells("A1:L1");
$sheet->getStyle("A1")->getFont()->setSize(15);
$sheet->setCellValue('A3', 'Nombre: ');
$sheet->setCellValue('B3', $contrato->cliente->usuario->nombre . " " . $contrato->cliente->usuario->apellido);
$sheet->setCellValue('F3', 'Fecha Consulta: ' . date('d/m/Y'));
$sheet->setCellValue('A4', 'Propiedad: ');
$sheet->setCellValue('B4', $contrato->departamento->propiedad->nombre);
$sheet->setCellValue('C4', "Departamento: ");
$sheet->setCellValue('D4', $contrato->departamento->numero);
$sheet->getStyle("A3")->getFont()->setSize(13);
$sheet->getStyle("B3")->getFont()->setSize(13);
$sheet->getStyle("F3")->getFont()->setSize(13);
$sheet->getStyle("A4")->getFont()->setSize(13);
$sheet->getStyle("B4")->getFont()->setSize(13);
$sheet->getStyle("C4")->getFont()->setSize(13);
$sheet->getStyle("D4")->getFont()->setSize(13);
$sheet->setCellValue('A5', "Rango de fechas consultado");
$sheet->getStyle("A5")->getFont()->setSize(13);
$sheet->setCellValue('A6', "Desde:");
$sheet->getStyle("A6")->getFont()->setSize(13);
$sheet->setCellValue('B6', Tools::fixMes($filtroModel->mesD) . " " . $filtroModel->agnoD);
$sheet->getStyle("B6")->getFont()->setSize(13);
$fechaDesde = $filtroModel->agnoD . "-" . $filtroModel->mesD . "-" . "01";
if ($filtroModel->desdeInicio == '1') {
$sheet->setCellValue('A6', "Desde inicio del Contrato:");
$sheet->setCellValue('B6', Tools::backFecha($contrato->fecha_inicio));
$fechaDesde = $contrato->fecha_inicio;
}
if ($filtroModel->desdeSaldo0 == '1') {
$movimiento = Movimiento::model()->findByPk($contrato->cuentaCorriente->idMovUltimoSaldo0());
if ($movimiento != null) {
$sheet->setCellValue('A6', "Desde último saldo 0:");
$sheet->setCellValue('B6', Tools::backFecha($movimiento->fecha));
$fechaDesde = $movimiento->fecha;
} else {
$sheet->setCellValue('A6', "Desde último saldo 0:");
$sheet->setCellValue('B6', "No hay saldo 0 ");
$fechaDesde = $contrato->fecha_inicio;
}
}
$fechaArr = explode("-", $fechaDesde);
$filtroModel->mesD = $fechaArr[1];
$filtroModel->agnoD = $fechaArr[0];
$sheet->setCellValue('A7', "Hasta:");
$sheet->getStyle("A7")->getFont()->setSize(13);
$sheet->setCellValue('B7', Tools::fixMes($filtroModel->mesH) . " " . $filtroModel->agnoH);
$sheet->getStyle("B7")->getFont()->setSize(13);
$sheet->setCellValue('A9', "Saldo Anterior:");
$sheet->getStyle("A9")->getFont()->setSize(13);
$sheet->setCellValue('B9', $contrato->cuentaCorriente->saldoAFecha($fechaDesde));
$sheet->getStyle("B9")->getFont()->setSize(13);
if ($filtroModel->conDetalle == "1") {
$sheet->setCellValue('A11', "Mes/Año");
$sheet->setCellValue('B11', "Concepto");
$sheet->setCellValue('C11', "Cargos");
$sheet->mergeCells('C11:D11');
$sheet->setCellValue('E1', "Abonos");
$sheet->mergeCells('E11:F11');
$sheet->setCellValue('C12', "Fecha");
$sheet->setCellValue('D12', "Monto");
$sheet->setCellValue('E12', "Fecha");
$sheet->setCellValue('F12', "Monto");
$sheet->getStyle("A11:F12")->getFont()->setBold(true);
$j = 13;
} else {
$sheet->setCellValue('A11', "Mes/Año");
$sheet->setCellValue('B11', "Cargos");
$sheet->setCellValue('C11', "Abonos");
$sheet->getStyle("A11:C11")->getFont()->setBold(true);
$j = 12;
}
$meses = Tools::arregloMeses($filtroModel->mesD, $filtroModel->agnoD, $filtroModel->mesH, $filtroModel->agnoH);
$abonos = 0;
$cargos = 0;
foreach ($meses as $mesArr) {
$mes = $mesArr['mes'];
$agno = $mesArr['agno'];
$mesNombre = $mesArr['mesNombre'];
if ($filtroModel->conDetalle == "1") {
$sheet->setCellValue('A' . $j, $mesNombre . " " . $agno);
$j++;
$movimientosMes = $contrato->cuentaCorriente->movimientosDeMes($mes, $agno);
foreach ($movimientosMes as $movimiento) {
$sheet->setCellValue('B' . $j, $movimiento->detalle);
if ($movimiento->tipo == Tools::MOVIMIENTO_TIPO_CARGO) {
$sheet->setCellValue('C' . $j, Tools::backFecha($movimiento->fecha));
$sheet->setCellValue('D' . $j, $movimiento->monto);
$cargos += $movimiento->monto;
} else {
$sheet->setCellValue('E' . $j, Tools::backFecha($movimiento->fecha));
$sheet->setCellValue('F' . $j, $movimiento->monto);
$abonos += $movimiento->monto;
}
$j++;
}
} else {
$saldoMes = $contrato->cuentaCorriente->saldoMes($mes, $agno);
$sheet->setCellValue('A' . $j, $mesNombre . " " . $agno);
$sheet->setCellValue('B' . $j, $saldoMes['cargos']);
$sheet->setCellValue('C' . $j, $saldoMes['abonos']);
$j++;
}
}
if ($filtroModel->conDetalle == "1") {
$sheet->setCellValue('A' . $j, "SUB TOTAL");
$sheet->setCellValue('D' . $j, $cargos);
$sheet->setCellValue('F' . $j, $abonos);
$j++;
}
$saldo = $contrato->cuentaCorriente->saldoAFecha(date('Y-m-d'));
$sheet->setCellValue('A' . ($j + 1), "Saldo fecha consulta: ");
$sheet->getStyle('A' . ($j + 1))->getFont()->setSize(13);
$sheet->setCellValue('B' . ($j + 1), $saldo);
$sheet->getStyle('B' . ($j + 1))->getFont()->setSize(13);
$color = '00FF00';
if ($saldo < 0) {
$color = 'FF0000';
}
$sheet->getStyle("B" . ($j + 1))->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $color))));
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Movimientos Cliente ' . $contrato->cliente->usuario->nombre . ' ' . $contrato->cliente->usuario->apellido . '.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
}
Yii::app()->user->returnUrl = array('//contrato/adminAbonos');
$this->render('adminAbonos', array('model' => $model, 'filtroModel' => $filtroModel, 'meses' => $meses, 'agnos' => $agnos));
}
private function exportEXCEL($excelname, $col, $title, $colAry)
{
// PHP EXCEL 初始化
XPHPExcel::init();
$fileTitle = "JIT Excel File";
$objPHPExcel = XPHPExcel::createPHPExcel();
$objPHPExcel->getProperties()->setCreator("JIT")->setLastModifiedBy("JIT")->setTitle($fileTitle)->setSubject("")->setDescription($fileTitle)->setKeywords("office 2007 openxml php")->setCategory("Excel File");
// 第一列 填入標題
$column = 0;
// 第幾欄. 由第0欄開始
for ($i = 0; $i < count($col); $i++) {
if (isset($title[$col[$i]])) {
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, 1, isset($title[$col[$i]]) ? $title[$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING);
$column++;
}
}
// 後續 填入內容
// 第幾列, 由第2列開始
$row = 2;
for ($j = 0; $j < count($colAry); $j++) {
if (isset($colAry[$j][$col[0]])) {
$column = 0;
// 第幾欄. 由第0欄開始
for ($i = 0; $i < count($col); $i++) {
// 若符合篩選欄位. 才進行
if (isset($title[$col[$i]])) {
/*$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, (isset($colAry[$j][$col[$i]]))?
$colAry[$j][$col[$i]]:'');*/
//避免(007002變7002) 0被吃掉，所以關鍵 setValueExplicit所有都轉字串
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit(isset($colAry[$j][$col[$i]]) ? $colAry[$j][$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING);
$column++;
}
}
$row++;
}
}
//CVarDumper::dump($colAry,10,true);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($excelname . '-正航匯入表');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a web browser (Excel5)
$webroot = Yii::getPathOfAlias('webroot');
//$fileName =$excelname.'-'.time().'.xls';
$fileName = 'PerformOut01' . '.xls';
$filePath = $webroot . '/' . "protected" . '/' . "tmp" . '/';
$fileUrl = $filePath . $fileName;
// If you're serving to IE over SSL, then the following may be needed
// header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
// header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
// header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
// header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// $fileName = Yii::app()->request->baseUrl."/protected/tmp/".$fileName;
// CVarDumper::dump($fileName);
// $objWriter->save($fileName);
$objWriter->save($fileUrl);
//
// Yii::app()->end();
// $objWriter->save(str_replace('.php', '.xls', __FILE__));
// $objWriter->save(str_replace(__FILE__,'/protected/tmp/'.$fileName,__FILE__));
// $objWriter->save('/protected/tmp/'.$fileName);
// @readfile('/protected/tmp/'.$fileName);
// spl_autoload_register(array('YiiBase','autoload'),true,true);
return $fileName;
}
echo "</div>";
break;
case 13:
echo "<table class='table'><tr><td>";
$i = 0;
$id = $element->id;
//echo "<a id=traceability style='color:black; background-color:rgb(200,200,200); border-radius:4px; ' onclick=toggle_visibility('info')> Click for Info </a></td>";
foreach ($element->columns as $column) {
echo '<th>' . $column->value . '</th>';
$i++;
}
echo "</tr>";
$r = 0;
echo "<h1>PHPExcel Reader</h1>";
Yii::import('ext.phpexcel.XPHPExcel');
XPHPExcel::init();
//$objPHPExcel = new PHPExcel();
//set_include_path(get_include_path() . PATH_SEPARATOR . 'c:\inetpub\wwwroot\vector2014\protected\vendors\\');
//include 'PHPExcel\IOFactory.php';
$inputFileName = 'protected/extensions/examples/reader/sampledata/example1.xls';
echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using IOFactory to identify the format<br />';
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
echo '<br />';
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);
foreach ($element->rows as $row) {
echo '<tr><th>' . $row->value . '</th>';
for ($index = 0; $index < $i; $index++) {
$res = $element->getResultTable($issueId, $index, $r);
if ($res != null) {
echo "<td title='" . $element->getUserDateForTables2($issueId, $r, $index) . "'>\n\t\t\t\t\t\t\t\t<input style='margin:2px;' name='Table[{$id}][{$r}][{$index}]' type='text' value ='{$res}'><br>\n\t\t\t\t\t\t\t\t<div class='info' style='display: none; font-size:small; padding:3px; background-color:rgb(220,220,220); border-radius:4px'>" . $element->getUserDateForTables($issueId, $r, $index) . "</div></td>";
public function actionListado()
{
$model = new ListadoPrestacionesForm();
$propiedades = Propiedad::model()->getDeUsuario(Yii::app()->user->id);
$meses = array();
for ($i = 1; $i <= 12; $i++) {
$meses[] = array('id' => str_pad($i, 2, "0", STR_PAD_LEFT), 'nombre' => Tools::fixMes($i));
}
$agnos = array();
$agnoInicio = 2000;
$agnoFin = (int) date('Y') + 10;
for ($i = $agnoInicio; $i < $agnoFin; $i++) {
$agnos[] = array('id' => $i, 'nombre' => $i);
}
$model->agnoH = date('Y');
$model->mesH = date('m');
if ($model->mesH == '01') {
$model->mesD = '12';
$model->agnoD = (int) $model->agnoH - 1;
} else {
$mes = (int) $model->mesH;
$model->mesD = str_pad($mes - 1, 2, "0", STR_PAD_LEFT);
$model->agnoD = $model->agnoH;
}
if (isset($_POST['ListadoPrestacionesForm'])) {
$model->attributes = $_POST['ListadoPrestacionesForm'];
$propiedad = Propiedad::model()->findByPk($model->propiedad_id);
$departamento = Departamento::model()->findByPk($model->departamento_id);
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('A1', 'Listado de Prestaciones por Propiedad');
$sheet->mergeCells("A1:K1");
$sheet->getStyle("A1")->getFont()->setSize(20);
$sheet->setCellValue('A2', 'Rango de Fechas: Desde ' . Tools::fixMes($model->mesD) . " de " . $model->agnoD . " hasta " . Tools::fixMes($model->mesH) . " de " . $model->agnoH);
$sheet->mergeCells("A2:K2");
$sheet->getStyle("A2")->getFont()->setSize(15);
$i = 3;
if ($propiedad != null) {
$sheet->setCellValueByColumnAndRow(0, $i, 'Propiedad: ' . $propiedad->nombre);
$sheet->mergeCellsByColumnAndRow(0, $i, 5, $i);
$sheet->getStyleByColumnAndRow(0, $i)->getFont()->setSize(15);
$i++;
}
if ($departamento != null && $propiedad != null) {
$sheet->setCellValueByColumnAndRow(0, $i, 'Departamento: ' . $departamento->numero);
$sheet->mergeCellsByColumnAndRow(0, $i, 5, $i);
$sheet->getStyleByColumnAndRow(0, $i)->getFont()->setSize(15);
$i++;
}
$i++;
$sheet->setCellValueByColumnAndRow(0, $i, 'Fecha');
$sheet->setCellValueByColumnAndRow(1, $i, 'Propiedad');
$sheet->setCellValueByColumnAndRow(2, $i, 'Departamento');
$sheet->setCellValueByColumnAndRow(3, $i, 'General Prop');
$sheet->setCellValueByColumnAndRow(4, $i, 'Nro Cheque');
$sheet->setCellValueByColumnAndRow(5, $i, 'Monto');
$sheet->setCellValueByColumnAndRow(6, $i, 'C/S Cargo');
$sheet->setCellValueByColumnAndRow(7, $i, 'Concepto');
$sheet->setCellValueByColumnAndRow(8, $i, 'Maestro');
$sheet->setCellValueByColumnAndRow(9, $i, 'Tipo Prestación');
$sheet->getStyleByColumnAndRow(0, $i, 10, $i)->getFont()->setSize(15);
$sheet->getStyleByColumnAndRow(0, $i, 10, $i)->getFont()->setBold(true);
$i++;
$prestaciones = Prestacionesadepartamentos::model()->getDePropiedadYDepartamento($propiedad, $departamento);
foreach ($prestaciones as $prestacion) {
$sheet->setCellValueByColumnAndRow(0, $i, Tools::backFecha($prestacion->fecha));
$propiedad = Propiedad::model()->findByPk($prestacion->propiedad_id);
$departamento = Departamento::model()->findByPk($prestacion->departamento_id);
if ($propiedad == null) {
$propiedad = $departamento->propiedad;
}
$sheet->setCellValueByColumnAndRow(1, $i, $propiedad->nombre);
$sheet->setCellValueByColumnAndRow(2, $i, $departamento != null ? $departamento->numero : "SIN DEPARTAMENTO");
$sheet->setCellValueByColumnAndRow(3, $i, $prestacion->general_prop == 1 ? "SÍ" : "NO");
$sheet->setCellValueByColumnAndRow(4, $i, $prestacion->documento);
$sheet->setCellValueByColumnAndRow(5, $i, $prestacion->monto);
$sheet->setCellValueByColumnAndRow(6, $i, $prestacion->genera_cargos == 1 ? "SÍ" : "NO");
$sheet->setCellValueByColumnAndRow(7, $i, $prestacion->descripcion);
$sheet->setCellValueByColumnAndRow(8, $i, $prestacion->maestro);
$sheet->setCellValueByColumnAndRow(9, $i, $prestacion->tipo);
$i++;
}
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Prestaciones a Propiedades.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
}
$this->render('listado', array('model' => $model, 'meses' => $meses, 'agnos' => $agnos, 'propiedades' => $propiedades));
}
public function actionexcelComp($id, $elementId)
{
//issueId and elementId
$element = Element::model()->findByPk($elementId);
Yii::import('ext.phpexcel.XPHPExcel');
$title = Element::model()->findByPk($elementId);
$newsheet = XPHPExcel::createPHPExcel();
switch ($element->typeId) {
case 0:
case 1:
case 3:
case 4:
$criteria = "elementId={$elementId}";
$results = Result::model()->findAll($criteria);
//$results=Result::model()->findAll($title->label);
$newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Issue ID");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "User");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "Fill date");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, 1, $title->label);
$newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$cont = 2;
foreach ($results as $result) {
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $result->issueId);
$issue = $this->loadIssue($result->issueId);
$user = Element::model()->getUserForExcel($result->issueId, $elementId);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user);
//
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, $cont, Yii::app()->dateFormatter->format("HH:mm d/M/y", $result->createTime));
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, $cont, $result->value);
$cont++;
}
$traveler = Traveler::model()->findByPk($issue->travelerId);
if (isset($issue)) {
if (strlen($traveler->name) > 28) {
$traveler->name = substr($traveler->name, 0, 28);
}
$newsheet->getActiveSheet()->setTitle($traveler->name);
}
//else throw new CHttpException(403, 'There is a problem with the data');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx');
header('Cache-Control: max-age=0');
//metada:
$newsheet->getProperties()->setCreator(Yii::app()->user->username);
$objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007');
$objWriter->save('php://output');
break;
case 5:
//devuelve el value de result, que es el id de value!!
//center 1st column
$newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//name first column
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Issue ID");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "User");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "Fill date");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, 1, $title->label);
//center data cells
$newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//color
$newsheet->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
//size
$newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$criteria = "elementId={$elementId}";
$preresults = Result::model()->findAll($criteria);
$cont = 2;
foreach ($preresults as $preresult) {
$issue = $this->loadIssue($preresult->issueId);
$user = Element::model()->getUserForExcel($preresult->issueId, $elementId);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $preresult->issueId);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, $cont, Yii::app()->dateFormatter->format("HH:mm d/M/y", $preresult->createTime));
$value = Value::model()->findByPk($preresult->value);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, $cont, $value->value);
$cont++;
}
if (isset($issue)) {
$traveler = Traveler::model()->findByPk($issue->travelerId);
$newsheet->getActiveSheet()->setTitle($traveler->name);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx');
header('Cache-Control: max-age=0');
//metada:
$newsheet->getProperties()->setCreator(Yii::app()->user->username);
$objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007');
$objWriter->save('php://output');
break;
case 6:
case 7:
//center 1st column
$newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//name first column
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Issue ID");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "User");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "Fill date");
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, 1, $title->label);
//center data cells
$newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//color
$newsheet->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
$newsheet->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF');
//size
$newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
//saber numero de issues
$criteria = new CDbCriteria();
$criteria->distinct = true;
$criteria->condition = "elementId=" . $elementId;
$criteria->select = "issueId";
$issues = Result::model()->findAll($criteria);
$contCol = 0;
$cont = array();
$cont = 1;
//looping through results by issues!!!
foreach ($issues as $issue) {
$user = null;
$cont++;
$results = Result::model()->findAll("elementId={$elementId} and issueId={$issue->issueId}");
$contCol = 3;
$newest = Date('1970-01-21 00:00:00.0');
foreach ($results as $result) {
//$user=Element::model()->getUserForExcel7($result->issueId, $elementId, $result->value);
//$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1+$userColumn,$cont+1,$user);
$date = $result->createTime;
if ($date > $newest) {
$newest = $date;
$user = $result->userId;
$user = Element::model()->getUserForExcel($result->issueId, $elementId);
}
if (!is_numeric($result->value)) {
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($contCol, $cont, "NULL");
$contCol++;
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $result->issueId);
} else {
$criteria = "elementId={$elementId} and id={$result->value}";
$values = Value::model()->find($criteria);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($contCol, $cont, $values->value);
$contCol++;
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $result->issueId);
}
//else throw new CHttpException(404, 'There is a problem with the data.');
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user);
}
$newest = Yii::app()->dateFormatter->format("HH:mm d/M/y", $newest);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user);
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, $cont, $newest);
/*
//esto es para cargar el nombre del traveler en el nombre de la hoja de calculo, no funciona porque no está bien cargado el issue
if(isset($issue)){
$traveler=Traveler::model()->findByPk($issue->travelerId);
$newsheet->getActiveSheet()->setTitle($traveler->name);
}*/
}
if ($contCol <= 26 && $contCol > 0) {
$columnLookup = array(1 => 'a', 2 => 'b', 3 => 'c', 4 => 'd', 5 => 'e', 6 => 'f', 7 => 'g', 8 => 'h', 9 => 'i', 10 => 'j', 11 => 'k', 12 => 'l', 13 => 'm', 14 => 'n', 15 => 'o', 16 => 'p', 17 => 'q', 18 => 'r', 19 => 's', 20 => 't', 21 => 'u', 22 => 'v', 23 => 'w', 24 => 'x', 25 => 'y', 26 => 'z');
$mergeCells = "d1:" . $columnLookup[$contCol] . "1";
$newsheet->setActiveSheetIndex(0)->mergeCells($mergeCells);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx');
header('Cache-Control: max-age=0');
//metada:
$newsheet->getProperties()->setCreator(Yii::app()->user->username);
$objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007');
$objWriter->save('php://output');
break;
case 13:
case 14:
$criteria = new CDbCriteria();
$criteria->distinct = true;
$criteria->condition = "elementId=" . $elementId;
$criteria->select = "issueId";
$issues = Result::model()->findAll($criteria);
$sheetNumber = 0;
foreach ($issues as $issue) {
$myWorkSheet = new PHPExcel_Worksheet($newsheet, $issue->issueId);
$newsheet->addSheet($myWorkSheet);
$newsheet->setActiveSheetIndex($sheetNumber + 1);
$sheetNumber++;
$criteria = "elementId={$elementId}";
$results = Result::model()->findAll($criteria);
$cont = 1;
$contCol = 0;
$columnCont = 1;
$rowCont = 1;
$element = Element::model()->findByPk($elementId);
$i = 4;
$r = 0;
$resCol = Value::model()->findAll("elementId={$elementId} and colonne=1");
foreach ($element->columns as $colonne) {
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($columnCont, 1, $colonne->value);
$i++;
$columnCont++;
}
foreach ($element->rows as $row) {
$rowCont++;
$newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $rowCont, $row->value);
for ($index = 0; $index < $columnCont; $index++) {
$res = $element->getResultTableForExcel($issue->issueId, $elementId, $index, $r);
switch ($res) {
case "off":
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "No");
break;
case "checked":
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "Yes");
break;
default:
$newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, $res);
}
}
$r++;
}
$newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$newsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml0sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx');
header('Cache-Control: max-age=0');
$newsheet->removeSheetByIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007');
$objWriter->save('php://output');
break;
}
}
public function actionInforme()
{
$form = new InformeForm();
if (isset($_POST['InformeForm'])) {
$form->attributes = $_POST['InformeForm'];
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$styleCenter = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$sheet->setCellValue('A1', 'Informe de Movimientos de Propietario');
$sheet->getStyle("A1")->getFont()->setSize(20);
$sheet->setCellValue('A2', 'Desde: ' . $form->fechaDesde . ' Hasta: ' . $form->fechaHasta);
$sheet->getStyle("A2")->getFont()->setSize(16);
$fechaDesdeArr = explode('-', $form->fechaDesde);
if (count($fechaDesdeArr) == 3) {
$mesInicio = $fechaDesdeArr[1];
$agnoInicio = $fechaDesdeArr[0];
} else {
$mesInicio = date('m');
$agnoInicio = date('Y');
$form->fechaDesde = date('Y-m-d');
}
$fechaHastaArr = explode('-', $form->fechaHasta);
if (count($fechaHastaArr) == 3) {
$mesFin = $fechaHastaArr[1];
$agnoFin = $fechaHastaArr[0];
} else {
$mesFin = date('m');
$agnoFin = date('Y');
$form->fechaHasta = date('Y-m-d');
}
$meses = Tools::arregloMeses($mesInicio, $agnoInicio, $mesFin, $agnoFin);
$propietario_id = Propietario::model()->getId(Yii::app()->user->id);
$propietario = Propietario::model()->findByPk($propietario_id);
$propiedades = Propiedad::model()->findAllByAttributes(array('propietario_id' => $propietario_id));
$row = 4;
foreach ($propiedades as $propiedad) {
$sheet->setCellValueByColumnAndRow(0, $row, "PROPIEDAD: " . $propiedad->nombre);
$row++;
foreach ($propiedad->departamentos as $departamento) {
if ($departamento->contrato != null) {
$rmax = 0;
$sheet->setCellValueByColumnAndRow(0, $row, "Departamento: " . $departamento->numero . ", Contrato: " . $departamento->contrato->folio . ", Arrendatario: " . $departamento->contrato->cliente->rut . " " . $departamento->contrato->cliente->usuario->nombre . " " . $departamento->contrato->cliente->usuario->apellido);
$row++;
$sheet->setCellValueByColumnAndRow(0, $row, "Movimientos de la cuenta");
$col = 0;
$cuentaCorriente = $departamento->contrato->cuentaCorriente;
$saldo = 0;
foreach ($meses as $mesArreglo) {
$nDias = cal_days_in_month(CAL_GREGORIAN, $mesArreglo['mes'], $mesArreglo['agno']);
$sheet->mergeCellsByColumnAndRow($col, $row, $col + 1, $row);
$sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($styleCenter);
$sheet->setCellValueByColumnAndRow($col, $row, $mesArreglo['mesNombre'] . " " . $mesArreglo['agno']);
$sheet->setCellValueByColumnAndRow($col, $row + 1, Tools::MOVIMIENTO_TIPO_ABONO);
$sheet->setCellValueByColumnAndRow($col + 1, $row + 1, Tools::MOVIMIENTO_TIPO_CARGO);
$movimientos = Movimiento::model()->findAll(array('condition' => 'fecha >= :fIni and fecha <= :fFin and cuenta_corriente_id = :cta', 'params' => array(':fIni' => $mesArreglo['agno'] . "-" . $mesArreglo['mes'] . "-01", ':fFin' => $mesArreglo['agno'] . "-" . $mesArreglo['mes'] . "-" . $nDias, ':cta' => $cuentaCorriente->id)));
if (count($movimientos) > $rmax) {
$rmax = count($movimientos);
}
$iMov = 0;
while ($iMov < count($movimientos)) {
$movimiento = $movimientos[$iMov];
if ($movimiento->tipo == Tools::MOVIMIENTO_TIPO_ABONO) {
if ($movimiento->validado == 1) {
$sheet->setCellValueByColumnAndRow($col, $row + 2 + $iMov, $movimiento->monto);
$saldo += $movimiento->monto;
}
}
if ($movimiento->tipo == Tools::MOVIMIENTO_TIPO_CARGO) {
$sheet->setCellValueByColumnAndRow($col + 1, $row + 2 + $iMov, $movimiento->monto);
$saldo -= $movimiento->monto;
}
$iMov++;
}
$sheet->mergeCellsByColumnAndRow($col, $row + 2 + $iMov, $col + 1, $row + 2 + $iMov);
$sheet->setCellValueByColumnAndRow($col, $row + 2 + $iMov, $saldo);
$sheet->getStyleByColumnAndRow($col, $row + 2 + $iMov)->applyFromArray($styleCenter);
if ($saldo < 0) {
$sheet->getStyleByColumnAndRow($col, $row + 2 + $iMov)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0000'))));
} else {
$sheet->getStyleByColumnAndRow($col, $row + 2 + $iMov)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '0000FF'))));
}
$col += 2;
}
$row += $rmax + 1;
}
}
$row += 3;
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Movimientos Propietario ' . $propietario->usuario->nombre . ' ' . $propietario->usuario->apellido . '.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
} else {
$form->fechaHasta = date('Y-m-d');
}
$this->render('informe', array('model' => $form));
}
public function actionExportarXLS()
{
// generate a resultset
$morosos = Yii::app()->user->getState('morososFiltrados');
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('A1', 'Clientes Morosos');
$sheet->mergeCells("A1:K1");
$sheet->getStyle("A1")->getFont()->setSize(20);
$filtros = "";
if ($morosos->propiedad != "") {
$filtros .= " Propiedad: " . $morosos->propiedad . ".";
}
if ($morosos->departamento != "") {
$filtros .= " Departamento: " . $morosos->departamento . ".";
}
if ($morosos->nombre_ap != "") {
$filtros .= " Nombre Cliente: " . $morosos->nombre_ap . ".";
}
if ($morosos->fecha != "") {
$filtros .= " Fecha: " . $morosos->fecha . ".";
}
if ($morosos->dias != "") {
$filtros .= " Días de mora: " . $morosos->dias . ".";
}
$i = 3;
if ($filtros != "") {
$sheet->setCellValue('A2', 'Criterios de búsqueda aplicados: ' . $filtros);
$sheet->mergeCells("A2:K2");
$i++;
$criteria = new CDbCriteria();
$criteria->compare('id', $morosos->id);
if ($morosos->nombre_ap != "") {
$arreglo = explode(" ", $morosos->nombre_ap);
$nombreApellido = array();
foreach ($arreglo as $palabra) {
if (trim($palabra) != '') {
$nombreApellido[] = $palabra;
}
}
$criteriaNombre = new CDbCriteria();
$palabras = count($nombreApellido);
if ($palabras == 1) {
$busqueda = $nombreApellido[0];
if (trim($busqueda) != '') {
$criteriaNombre->compare('nombre', $busqueda, true);
$criteriaNombre->compare('apellido', $busqueda, true, 'OR');
}
}
if ($palabras == 2) {
$nombre = $nombreApellido[0];
$apellido = $nombreApellido[1];
$criteriaNombre->compare('nombre', $nombre, true);
$criteriaNombre->compare('apellido', $apellido, true);
}
$criteria->mergeWith($criteriaNombre, 'AND');
}
if ($morosos->propiedad != "") {
$criteria->compare('propiedad', $morosos->propiedad, true);
}
if ($morosos->departamento != "") {
$criteria->compare('departamento', $morosos->departamento, true);
}
if ($morosos->monto != "") {
$criteria->compare('monto', $morosos->monto);
}
if ($morosos->fecha != "") {
$criteria->compare('fecha', Tools::fixFecha($morosos->fecha));
}
if ($morosos->dias != "") {
$criteria->compare('dias', $morosos->dias);
}
$tempMorosos = TempMorosos::model()->findAll($criteria);
} else {
$tempMorosos = TempMorosos::model()->findAll();
}
$sheet->setCellValue('A' . $i, 'Propiedad');
$sheet->setCellValue('B' . $i, 'Departamento');
$sheet->setCellValue('C' . $i, 'Nombre');
$sheet->setCellValue('D' . $i, 'Monto');
$sheet->setCellValue('E' . $i, 'Fecha');
$sheet->setCellValue('F' . $i, 'Dias');
$sheet->getStyle("A{$i}:F{$i}")->getFont()->setBold(true);
$i++;
foreach ($tempMorosos as $moroso) {
$sheet->setCellValue('A' . $i, $moroso->propiedad);
$sheet->setCellValue('B' . $i, $moroso->departamento);
$sheet->setCellValue('C' . $i, $moroso->nombre . " " . $moroso->apellido);
$sheet->setCellValue('D' . $i, $moroso->monto);
$sheet->setCellValue('E' . $i, Tools::backFecha($moroso->fecha));
$sheet->setCellValue('F' . $i, $moroso->dias);
$i++;
}
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Movimientos Propietario.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
}
public function actionExcelEPC()
{
date_default_timezone_set('America/Tegucigalpa');
if (isset($_GET['Seccion'])) {
$IdCurso = $_GET['Seccion'];
$Curso = Curso::model()->findByPk($IdCurso);
$AlumnosPorCurso = AlumnosPorCurso::model()->findAllByAttributes(array('Curso_IdCurso' => $IdCurso), array('order' => 'Activo DESC'));
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
$objPHPExcel->getProperties()->setCreator("Henry Arévalo")->setLastModifiedBy("Henry Arévalo")->setTitle("Sistema de Gestión de Prácticas Profesionales")->setSubject("UNITEC")->setDescription("SGPP")->setKeywords("HAF")->setCategory("SGPP");
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Lista de Estudiantes');
// FORMATO DE CELDAS (MERGE)
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B1:M1');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B2:M2');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C3:D3');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('F3:G3');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('I3:J3');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('L3:M3');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B4:M4');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B5:H5');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('I5:M5');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C6:D6');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E6:F6');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G6:H6');
// BORDES
$BordesEncabezado = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$objPHPExcel->getActiveSheet()->getStyle('B1:M3')->applyFromArray($BordesEncabezado);
$objPHPExcel->getActiveSheet()->getStyle('B5:M6')->applyFromArray($BordesEncabezado);
$objPHPExcel->getActiveSheet()->getStyle('B1:M2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8');
$objPHPExcel->getActiveSheet()->getStyle('E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8');
$objPHPExcel->getActiveSheet()->getStyle('H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8');
$objPHPExcel->getActiveSheet()->getStyle('K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8');
$objPHPExcel->getActiveSheet()->getStyle('B5:H6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('e89b9e');
$objPHPExcel->getActiveSheet()->getStyle('I5:M6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('b1dee0');
// FORMATOS DE TEXTO ( SIZE, FONT , BOLD)
$styleArray = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
// Negrita tamaño 14
$NormalTamanioDoce = array('font' => array('size' => 13), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
// Normal Tamaño 12
$NegritaTamanioDoce = array('font' => array('bold' => true, 'size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
// Negrita Tamaño 12
// ESCRITURA DE TEXTO
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', $Curso->Nombre);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "Listado de Estudiantes");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B5', "\tINFORMACIÓN ESTUDIANTE");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I5', "INFORMCIÓN EMPRESA");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B6', "Nº Cuenta");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C6', "Nombre Estudiante");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E6', "Correo Electrónico");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G6', "Carrera");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I6', "Empresa");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J6', "Jefe");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K6', "Puesto");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L6', "Correo");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M6', "Celular");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C3', $Curso->Seccion);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F3', $Curso->Codigo);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I3', $Curso->periodoAcademico->PeriodoConcatenado);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L3', date('Y-m-d H:i:s'));
// APLICANDO FORMATOS
$objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($NormalTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('H3')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('K3')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('B5:I5')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('B6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('C6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('E6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('G6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('I6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('J6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('K6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('L6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('M6')->applyFromArray($NegritaTamanioDoce);
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B3', 'Sección:')->setCellValue('E3', 'Código:')->setCellValue('H3', 'Periodo:')->setCellValue('K3', 'Emitido:');
// RECORRIENDO MODELO CON LOS ESTUDIANTES
$contador = 7;
foreach ($AlumnosPorCurso as $Alumno) {
$PracticasPorAlumno = PracticasPorAlumno::model()->findAllByAttributes(array('UsuarioEstudiante_IdUsuarioEstudiante' => $Alumno->userEstudiante->IdUsuarioEstudiante, 'Curso_IdCurso' => $IdCurso));
if ($Alumno->userEstudiante->Activo == 1) {
foreach ($PracticasPorAlumno as $practica) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $contador, $practica->practica->asesor->usuarioEmpresa->NombreEmpresa)->setCellValue('J' . $contador, $practica->practica->asesor->NombreCompleto)->setCellValue('K' . $contador, $practica->practica->asesor->PuestoEmpresa)->setCellValue('L' . $contador, $practica->practica->asesor->CorreoElectronico)->setCellValue('M' . $contador, $practica->practica->asesor->TelefonoCelular);
}
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C' . $contador . ':D' . $contador);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E' . $contador . ':F' . $contador);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G' . $contador . ':H' . $contador);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $contador, $Alumno->userEstudiante->NumeroDeCuenta)->setCellValue('C' . $contador, $Alumno->userEstudiante->NombreCompleto)->setCellValue('E' . $contador, $Alumno->userEstudiante->Email)->setCellValue('G' . $contador, $Alumno->userEstudiante->carrera->NombreCarrera);
$contador += 1;
}
}
$contador -= 1;
$objPHPExcel->getActiveSheet()->getStyle('B7:M' . $contador)->applyFromArray($BordesEncabezado);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(27);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(23);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename= Lista de Estudiantes - ' . $Curso->Nombre . '.xls');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
} else {
$this->redirect(Yii::app()->request->urlReferrer);
}
}
public function actionFullupload()
{
/*
$price_update = Prices::model()->findByAttributes(['trade_date'=>'0000-00-00', 'instrument_id' =>43],
[
'condition'=>'price!=:price',
'params'=>array('price'=>17.420),
]
);
var_dump($price_update);
exit;
*/
$model = new Uploads();
//$path = Yii::app()->basePath.'../../uploads/';
$path = Yii::getPathOfAlias('webroot') . '/uploads/';
if (isset($_POST['Uploads'])) {
Yii::import('ext.phpexcel.XPHPExcel');
XPHPExcel::init();
ini_set('max_execution_time', 150000);
ini_set("memory_limit", "128M");
require_once Yii::app()->basePath . '/extensions/XLSXReader/XLSXReader.php';
//OKarray(2) { ["Uploads"]=> array(2) { ["instrument_id"]=> string(2) "12" ["upload_description"]=> string(5) "sfggs" } ["yt0"]=> string(6) "Upload" }
$model->attributes = $_POST['Uploads'];
if ($upload_file = self::uploadMultifile($model, 'upload_file', $path)) {
$model->upload_file = implode(",", $upload_file);
}
$model->user_id = Yii::app()->user->id;
//$instrument_id = $model->instrument_id;
//////////////////////////////////////////
if ($model->validate()) {
//Upload File //
if ($model->save()) {
$upload_file_id = Yii::app()->db->getLastInsertID();
$csvFile = CUploadedFile::getInstance($model, 'upload_file', '../../uploads/');
$tempLoc = Yii::getPathOfAlias('webroot') . '/uploads/' . $model->upload_file;
$xlsx = new XLSXReader($tempLoc);
$data = $xlsx->getSheetData('Sheet1');
$instruments = Instruments::model()->findAll(array('select' => 'id, instrument'));
$instruments_for_returns_update = [];
foreach ($data as $dat) {
$trade_date = gmdate('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dat['0']));
$instrument_name = trim($dat['1']);
$price = $dat['2'];
$currency = $dat['3'];
$instrument = Instruments::model()->findByAttributes(['instrument' => $instrument_name, 'is_current' => 1]);
if ($instrument) {
$instrument_id = $instrument->id;
$instruments_for_returns_update[] = $instrument_id;
} else {
$new_instrument = new Instruments();
$new_instrument->instrument = $instrument_name;
$new_instrument->price_uploaded = 1;
$new_instrument->currency = $currency;
$new_instrument->save();
$instrument_id = $new_instrument->id;
$instruments_for_returns_update[] = $instrument_id;
}
$existing_record = Prices::model()->findByAttributes(['trade_date' => $trade_date, 'instrument_id' => $instrument_id]);
if ($existing_record) {
if ($existing_record->price !== $price) {
$existing_record->price = $price;
$existing_record->upload_file_id = $upload_file_id;
$existing_record->save();
}
} else {
$new_price = new Prices();
$new_price->instrument_id = $instrument_id;
$new_price->trade_date = $trade_date;
$new_price->price = $price;
$new_price->upload_file_id = $upload_file_id;
//$new_price->name = $instrument_name;
$new_price->save();
}
}
$unique_instruments_for_returns_update = array_unique($instruments_for_returns_update);
Returns::model()->instrumnetReturnsUpdate($unique_instruments_for_returns_update);
Yii::app()->user->setFlash('success', "Prices Uploaded!");
@chmod($tempLoc, 0777);
@unlink($tempLoc);
//unlink(Yii::getPathOfAlias('webroot').'/uploads/'.$model->upload_file);
//$this->redirect(array('view','id'=>$model->id));
$user_data = Users::model()->findByPk(Yii::app()->user->id);
$step_completed = $user_data->step_completed;
if ($user_data->user_role == 2 && $step_completed < 2) {
$user_data->step_completed = 1;
$user_data->save();
$this->redirect(Yii::app()->baseUrl . '/site/admin');
}
//else{
// $this->redirect(Yii::app()->baseUrl.'/site/admin');
// $this->render('overview', ['user_data' => $user_data]); }
}
}
///////////////////////////////////////////
}
$this->render('upload_form', array('model' => $model));
}
<?php
Yii::import('ext.phpexcel.XPHPExcel');
$objPHPExcel = XPHPExcel::createPHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("")->setCategory("");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!');
// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="archivo.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
public function actionimportExcelTable($id, $issueId)
{
//step id
if (isset($_FILES['Excel'])) {
$post = Result::model()->importExcelTable($_FILES['Excel']);
//throw new CHttpException(403,$_FILES['File']);
Yii::import('ext.phpexcel.IOFactory');
Yii::import('ext.phpexcel.*');
Yii::import('ext.phpexcel.XPHPExcel');
Yii::import('ext.phpexcel.shared.string');
$newsheet = XPHPExcel::createPHPExcel();
//$inputFileName='c:\inetpub\wwwroot\vector2014\protected\extensions\examples\reader\sampledata\book1.xlsx';
$inputFileName = $post["tmp_name"];
Result::model()->alsjkd($inputFileName);
//var_dump($post);
try {
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch (PHPExcel_Reader_Exception $e) {
die('Error loading file: ' . $e->getMessage());
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumnIndex = $sheet->getHighestColumn();
$index = array('A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8);
$highestColumn = $index[$highestColumnIndex];
$index = array_slice($index, 0, $highestColumn);
$element = new Element();
$element->stepId = $id;
$element->typeId = 13;
$element->save();
$elementId = $element->id;
$step = $this->loadModel($id);
for ($row = 1; $row <= $highestRow; $row++) {
$rows[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow('A', $row)->getValue();
}
$columns = array();
//for ($column =1 ; $column<=$highestColumn; $column++){
foreach ($index as $key => $position) {
$columns[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, 1)->getValue();
}
$salida = $step->addGrid($element, $rows, $columns);
$issue = new Issue();
//$travelerId= $step->travelerId;
for ($row = 1; $row <= $highestRow; $row++) {
foreach ($index as $key => $position) {
if ($row >= 1 && $position > 0) {
$result = new Result();
$result->elementId = $elementId;
$result->issueId = $issueId;
$result->value = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, $row)->getValue();
//throw new CHttpException(403, 'row: '.$row.'. Columna: '.$position.'.Value: '.$result->value);
$result->colonne = $position - 1;
$result->ligne = $row - 2;
$result->save();
}
//throw new CHttpException (403,"exception: ". $iasdasdeId);
//$objPHPExcel->getActiveSheet()->getCellByColumnAndRow('A',$row)->getValue();
}
}
$alsdjk = $LKSJAD;
} else {
$alsdjk = $LKSJAD;
throw new CHttpException(403, "Error in the file");
}
}
public function actionExportResponseStatistics($id, $location)
{
$survey = $this->loadModel($id, $location);
$allowedForUsers = array_map(function ($x) {
return $x->userId;
}, $survey->surveyUsers);
if (!Yii::app()->user->checkAccess('survey.view') && !Yii::app()->user->checkAccess('survey.viewAndDownloadAllowed', array('user_ids' => $allowedForUsers))) {
throw new Http403Exception();
}
if ($survey->surveyInfo && $survey->surveyInfo->vicidialCampaign) {
$statistics = $survey->surveyInfo->vicidialCampaign->getCallsStatistics();
Yii::import('ext.PHPExcel.XPHPExcel');
XPHPExcel::init();
$phpExcel = XPHPExcel::createPHPExcel();
$phpExcel->getProperties()->setCreator("BCGroup");
$phpExcel->getProperties()->setLastModifiedBy("BCGroup");
$phpExcel->getProperties()->setTitle("Статистика отвечаемости по проекту {$survey->title}");
$phpExcel->getProperties()->setSubject("Статистика отвечаемости");
$phpExcel->setActiveSheetIndex(0);
$phpExcel->getActiveSheet()->SetCellValue('A1', 'Код статуса');
$phpExcel->getActiveSheet()->SetCellValue('B1', 'Название статуса');
$phpExcel->getActiveSheet()->SetCellValue('C1', 'Количество');
foreach ($statistics as $idx => $statisticsRow) {
$phpExcel->getActiveSheet()->SetCellValue('A' . ($idx + 2), $statisticsRow['status']);
$phpExcel->getActiveSheet()->SetCellValue('B' . ($idx + 2), $statisticsRow['status_name']);
$phpExcel->getActiveSheet()->SetCellValue('C' . ($idx + 2), $statisticsRow['count']);
}
$phpExcel->getActiveSheet()->setTitle('Статистика отвечаемости');
$phpExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$phpExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$phpExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true);
$objWriter = new PHPExcel_Writer_Excel2007($phpExcel);
$filePath = $survey->getResponseStatisticsSrc();
$objWriter->save($filePath);
Yii::app()->request->sendFile(basename($filePath), file_get_contents($filePath));
} else {
throw new CHttpException(404, 'The requested page does not exist.');
}
Yii::app()->end();
}
