public function save() { $post = $this->_post(NULL, TRUE); try { if (is_array($post)) { $producto = NomProductoTable::getInstance()->find($post['producto_id']); if (!$producto) { $msg = "Producto no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } $actividad = NomActividadTable::getInstance()->find($post['actividad_id']); if (!$actividad) { $msg = "Actividad no encontrada."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } if ($post['id']) { $actividad_producto = $this->_getTable()->find($post['id']); if (!$actividad_producto) { $msg = "BoletaProducto no encontrado."; return $this->_jsonResponse(array("msg" => $msg), 404, $msg); } } else { $actividad_producto = new ActividadProducto(); } $actividad_producto->fromArray($post, false); $actividad_producto->set('Producto', $producto); $actividad_producto->set('Actividad', $actividad); $actividad_producto->save(); //Creating security log $this->load->library('appunto-auth/appunto_auth'); $this->appunto_auth->create_security_log(1, "Asignando/Actualizando tabla " . $this->_model . "[id:" . $actividad_producto->id . "]"); //End security log $this->_jsonResponse($actividad_producto->toArray()); } } catch (Exception $exc) { log_message('error', $exc->getMessage()); log_message('error', $exc->getTraceAsString()); //Creating security log $this->load->library('appunto-auth/appunto_auth'); $this->appunto_auth->create_security_log(0, "Error creando/actualizando tabla " . $this->_model); //End security log $this->_jsonResponse(array("msg" => "Ha ocurrido un error mientras se intentaba guardar una actividad."), 500); } }
private function generar_reporte_balance($year) { $time = $year . '%'; $actividades = NomActividadTable::getInstance()->findAll(Doctrine::HYDRATE_ARRAY); $productos = ActividadProductoTable::getInstance()->productos($time); // Create new PHPExcel object $this->load->library('phpexcel'); // Set document properties $this->phpexcel->getProperties()->setCreator("PHPExcel")->setLastModifiedBy("PHPExcel")->setTitle("Reporte generado por ALIPLAN")->setSubject("Reporte generado por ALIPLAN")->setDescription("Report document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Report result file"); // Add some data $this->phpexcel->getActiveSheet()->setCellValue('A2', 'PRODUCTO/ACTIVIDAD'); $this->phpexcel->getActiveSheet()->getStyle('A2')->applyFromArray($this->styles('borderStyle')); //$this->phpexcel->getActiveSheet()->getStyle('A1')->getAlignment()->setIndent(1); // Set fonts $this->phpexcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true); $this->phpexcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $num_columna = 1; $month = date("m"); if (date("Y") != $year) { $month = "00"; } $months = $this->de_enero_a_estemes($month); //columnas foreach ($months as $mes_key => $value) { $fila = 1; $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $fila, "Mes de " . $value, true); $c1 = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 2, $fila); $c2 = $b->getCoordinate(); $this->phpexcel->getActiveSheet()->mergeCells($c1 . ':' . $c2); $this->phpexcel->getActiveSheet()->getStyle($c1 . ':' . $c2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($c1 . ':' . $c2)->applyFromArray($this->styles('blackStyle')); $fila = 2; //columna plan $col_plan = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $fila, "PLAN", true); $coor_plan = $col_plan->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coor_plan)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($coor_plan)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //columna real $col_real = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 1, $fila, "REAL", true); $coor_real = $col_real->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coor_real)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($coor_real)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //columna porciento $col_porc = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 2, $fila, "%", true); $coor_porc = $col_porc->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coor_porc)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($coor_porc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $num_columna = $num_columna + 3; if ($mes_key != '01') { $fila = 1; $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $fila, "ACUMULADO/Hasta " . $value, true); $c1 = $a->getCoordinate(); $b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 2, $fila); $c2 = $b->getCoordinate(); $this->phpexcel->getActiveSheet()->mergeCells($c1 . ':' . $c2); $this->phpexcel->getActiveSheet()->getStyle($c1 . ':' . $c2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($c1 . ':' . $c2)->applyFromArray($this->styles('blackStyle')); $fila = 2; //columna plan $col_plan = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $fila, "PLAN", true); $coor_plan = $col_plan->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coor_plan)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($coor_plan)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //columna real $col_real = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 1, $fila, "REAL", true); $coor_real = $col_real->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coor_real)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($coor_real)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //columna porciento $col_porc = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 2, $fila, "%", true); $coor_porc = $col_porc->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coor_porc)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($coor_porc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $num_columna = $num_columna + 3; } } $iterator_colA = 3; $num_columna = 0; for ($i = 0; $i < count($productos); $i++) { $obj = $productos[$i]; $product = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $iterator_colA, $obj['producto_nombre'] . ' (' . $obj['unidad_medida'] . ')', true); $this->phpexcel->getActiveSheet()->getStyle($product->getCoordinate())->applyFromArray($this->styles('blackStyle')); $iterator_colA = $iterator_colA + 1; $y = 0; for (; $y < count($actividades); $y++) { $a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $iterator_colA + $y, $actividades[$y]['nombre'], true); $coord_a = $a->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($coord_a)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($coord_a)->getAlignment()->setIndent(1); } $iterator_colA = $iterator_colA + $y; $num_columna = 0; } $num_columna = 1; $num_activities = count($actividades); foreach ($months as $mes_key => $value) { $act_prod = ActividadProductoTable::getInstance()->productos_por_meses($mes_key, $year); $matrix_act_prod = ActividadProductoTable::getInstance()->matrix($actividades, $productos, $act_prod); $begin_row = 3; for ($i = 0; $i < count($productos); $i++) { $producto = $productos[$i]; $upper = $matrix_act_prod[$producto['producto_nombre']]; $x1 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna, $begin_row + 1); $c1 = $x1->getCoordinate(); $x2 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna, $begin_row + $num_activities); $c2 = $x2->getCoordinate(); $sum_plan = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $begin_row, '=SUM(' . $c1 . ':' . $c2 . ')', true); $c3 = $sum_plan->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($c3)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($c3)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $x1 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 1, $begin_row + 1); $c1 = $x1->getCoordinate(); $x2 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 1, $begin_row + $num_activities); $c2 = $x2->getCoordinate(); $sum_real = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 1, $begin_row, '=SUM(' . $c1 . ':' . $c2 . ')', true); $zz = $sum_real->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($zz)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($zz)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $percent_formula = '=' . $zz . '*100/' . $c3; $per = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 2, $begin_row, $percent_formula, true); $zzz = $per->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($zzz)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($zzz)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($zzz)->getNumberFormat()->setFormatCode('#,##0.0'); $begin_row = $begin_row + 1; for ($y = 0; $y < $num_activities; $y++) { $actividad = $actividades[$y]; $middle = $upper[$actividad['nombre']]; //Asignar datos a las celdas (plan,real y porciento) $pl = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $begin_row, $middle['plan'], true); $pl_coor = $pl->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($pl_coor)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($pl_coor)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $real = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 1, $begin_row, $middle['actual'], true); $real_coor = $real->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($real_coor)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($real_coor)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $percent_formula = '=' . $real_coor . '*100/' . $pl_coor; if ($middle['plan'] == 0) { $percent_formula = 0; } $pc = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 2, $begin_row, $percent_formula, true); $porc_coor = $pc->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($porc_coor)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($porc_coor)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($porc_coor)->getNumberFormat()->setFormatCode('#,##0.0'); $begin_row += 1; } } $num_columna = $num_columna + 3; if ($mes_key != '01') { $begin_row = 3; for ($i = 0; $i < count($productos); $i++) { $x1 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna, $begin_row + 1); $c1 = $x1->getCoordinate(); $x2 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna, $begin_row + $num_activities); $c2 = $x2->getCoordinate(); $sum_plan = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $begin_row, '=SUM(' . $c1 . ':' . $c2 . ')', true); $c3 = $sum_plan->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($c3)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($c3)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $x1 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 1, $begin_row + 1); $c1 = $x1->getCoordinate(); $x2 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 1, $begin_row + $num_activities); $c2 = $x2->getCoordinate(); $sum_real = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 1, $begin_row, '=SUM(' . $c1 . ':' . $c2 . ')', true); $zz = $sum_real->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($zz)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($zz)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $percent_formula = '=' . $zz . '*100/' . $c3; $per = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 2, $begin_row, $percent_formula, true); $zzz = $per->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($zzz)->applyFromArray($this->styles('blackStyle')); $this->phpexcel->getActiveSheet()->getStyle($zzz)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($zzz)->getNumberFormat()->setFormatCode('#,##0.0'); $begin_row = $begin_row + 1; for ($y = 0; $y < $num_activities; $y++) { $actividad = $actividades[$y]; $middle = $upper[$actividad['nombre']]; //Asignar Acumulados //------------PLAN---------- $first = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna - 6, $begin_row); $first_coor = $first->getCoordinate(); $second = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna - 3, $begin_row); $second_coor = $second->getCoordinate(); $pl = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna, $begin_row, '=' . $first_coor . '+' . $second_coor, true); $pl_coor = $pl->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($pl_coor)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($pl_coor)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //------------REAL----------- $first1 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 1 - 6, $begin_row); $first1_coor = $first1->getCoordinate(); $second1 = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($num_columna + 1 - 3, $begin_row); $second1_coor = $second1->getCoordinate(); $real = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 1, $begin_row, '=' . $first1_coor . '+' . $second1_coor, true); $real_coor = $real->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($real_coor)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($real_coor)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $percent_formula = '=' . $real_coor . '*100/' . $pl_coor; $pc = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($num_columna + 2, $begin_row, $percent_formula, true); $porc_coor = $pc->getCoordinate(); $this->phpexcel->getActiveSheet()->getStyle($porc_coor)->applyFromArray($this->styles('borderStyle')); $this->phpexcel->getActiveSheet()->getStyle($porc_coor)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($porc_coor)->getNumberFormat()->setFormatCode('#,##0.0'); $begin_row += 1; } } $num_columna = $num_columna + 3; } } // Set header and footer. When no different headers for odd/even are used, odd header is assumed. $this->phpexcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&RFecha &D'); $this->phpexcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPágina &P de &N'); // Set page orientation and size $this->phpexcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $this->phpexcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER); // Rename first worksheet $this->phpexcel->getActiveSheet()->setTitle('Hoja1'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $this->phpexcel->setActiveSheetIndex(0); return $this->phpexcel; }