public function save()
{
$post = $this->_post(NULL, TRUE);
try {
if (is_array($post)) {
$producto = NomProductoTable::getInstance()->find($post['producto_id']);
if (!$producto) {
//Si el producto no existe devuelvo un error
$msg = "Producto no encontrado.";
return $this->_jsonResponse(array("msg" => $msg), 404, $msg);
}
$fuente = NomMercadoTable::getInstance()->find($post['mercado_id']);
if (!$fuente) {
//Si la fuente no existe devuelvo un error
$msg = "Mercado no encontrado.";
return $this->_jsonResponse(array("msg" => $msg), 404, $msg);
}
if ($post['id']) {
$productomercado = $this->_getTable()->find($post['id']);
if (!$productomercado) {
//Si el producto no existe devuelvo un error
$msg = "Producto no encontrado.";
return $this->_jsonResponse(array("msg" => $msg), 404, $msg);
}
} else {
$productomercado = new ProductoMercado();
}
$productomercado->fromArray($post, false);
$productomercado->set('Producto', $producto);
$productomercado->set('Mercado', $fuente);
$productomercado->save();
$this->_jsonResponse($productomercado->toArray());
}
} catch (Exception $exc) {
log_message('error', $exc->getMessage());
log_message('error', $exc->getTraceAsString());
$this->_jsonResponse(array("msg" => "Ha ocurrido un error mientras se intentaba guardar un producto."), 500);
}
}
private function generar_reporte_portada($year)
{
$time = $year . '%';
$headers = ProductoFuentesTable::getInstance()->productos($time);
$pr1 = ProductoFuentesTable::getInstance()->proveedores_fuentes($time);
$pr2 = ProductoFuentesTable::getInstance()->proveedores_fuentes2($time);
$proveedores = $this->count_distinct($pr1, $pr2, 'nombre');
// Create new PHPExcel object
$this->load->library('phpexcel');
// Set document properties
$this->phpexcel->getProperties()->setCreator($this->session->userdata('username'))->setLastModifiedBy("Aliplan")->setTitle("Confeccionado por: Revisado por:")->setSubject("Office 2007 XLSX Report Document")->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('A1', 'ANALISIS DE LA CIFRA DIRECTIVA PLAN ' . $year);
$this->phpexcel->getActiveSheet()->getStyle('A1')->getAlignment()->setIndent(1);
$this->phpexcel->getActiveSheet()->mergeCells('A1:B1');
$this->phpexcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Set fills
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
$this->phpexcel->getActiveSheet()->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getActiveSheet()->getStyle('A2')->getFill()->getStartColor()->setARGB('FF808080');
// Set fonts
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$this->phpexcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$this->phpexcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$this->phpexcel->getActiveSheet()->setCellValue('A3', 'PRODUCTOS');
$this->phpexcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->phpexcel->getActiveSheet()->getStyle('A3')->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getActiveSheet()->setCellValue('A4', 'FUENTES');
$this->phpexcel->getActiveSheet()->getStyle('A4')->applyFromArray($this->styles('blackStyle'));
$col = 0;
$row = 5;
$fuentes = NomFuentesTable::getInstance()->fuentes($time);
foreach ($fuentes as $value) {
$a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value['nombre'], true);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$col = 1;
foreach ($headers as $v) {
$cantidad = 0;
$arr = ProductoFuentesTable::getInstance()->cantidad($time, $v['producto_id'], $value['fuente_id']);
if ($arr && $arr[0]) {
$obj = $arr[0];
$cantidad = $obj['cantidad'];
}
$a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col++, $row, $cantidad, true);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
$row++;
$col = 0;
}
//Sumatoria de fuentes
$num_fuentes = count($fuentes);
for ($col_number = 1; $col_number <= count($headers); $col_number++) {
$a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 5);
$a_coor = $a->getCoordinate();
$b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 4 + $num_fuentes);
$b_coor = $b->getCoordinate();
$c = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, 4, '=SUM(' . $a_coor . ':' . $b_coor . ')', true);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
//Sumatoria de Destinos
for ($col_number = 1; $col_number <= count($headers); $col_number++) {
$a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 9);
$a_coor = $a->getCoordinate();
$b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 14);
$b_coor = $b->getCoordinate();
$c = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 15);
$c_coor = $c->getCoordinate();
$d = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 17);
$d_coor = $d->getCoordinate();
$e = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 33);
$e_coor = $e->getCoordinate();
$suma = '=' . $a_coor . '+' . $b_coor . '+' . $c_coor . '+' . $d_coor . '+' . $e_coor;
$f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, 5 + $num_fuentes, $suma, true);
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
$col = 0;
$arr_columna_a = $this->columna_a();
$coor_nomin = 0;
$coor_consumoSocial = 0;
$coor_consumoInter = 0;
$reserva_balance = 0;
$canasta_familiar = 0;
foreach ($arr_columna_a as $value) {
$a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row++, $value[0], true);
$coor = $a->getCoordinate();
if ($value[0] === 'Nominalizados') {
$coor_nomin = $row - 1;
} else {
if ($value[0] === 'Consumo Intermedio') {
$coor_consumoInter = $row - 1;
} else {
if ($value[0] === 'Reserva del Balance') {
$reserva_balance = $row - 1;
} else {
if ($value[0] === 'Canasta Familiar') {
$canasta_familiar = $row - 1;
} else {
if ($value[0] === 'Consumo Social') {
$coor_consumoSocial = $row - 1;
}
}
}
}
}
$this->phpexcel->getActiveSheet()->getStyle($coor)->getAlignment()->setIndent($value[1]);
$this->phpexcel->getActiveSheet()->getStyle($coor)->applyFromArray($this->styles($value[2]));
}
//FORMULA DE NOMINALIZADOS
$col = 1;
foreach ($headers as $value) {
$cantidad = 0;
$arr = ProductoFuentesTable::getInstance()->nominalizados($time, $value['producto_id']);
if ($arr && $arr[0]) {
$obj = $arr[0];
$cantidad = $obj['cantidad'];
}
$f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col++, $coor_nomin, $cantidad, true);
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
//FORMULA DE CONSUMO SOCIAL
for ($col_number = 1; $col_number <= count($headers); $col_number++) {
$a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $coor_consumoSocial + 1);
$a_coor = $a->getCoordinate();
$b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $coor_consumoSocial + 9);
$b_coor = $b->getCoordinate();
$bb = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $coor_consumoSocial + 11);
$bb_coor = $bb->getCoordinate();
$c = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, $coor_consumoSocial, '=SUM(' . $a_coor . ':' . $b_coor . ')+' . $bb_coor, true);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
//FORMULA DE CONSUMO INTERMEDIO
$col = 1;
foreach ($headers as $value) {
$cantidad = 0;
$arr = ProductoFuentesTable::getInstance()->consumo_intermedio($time, $value['producto_id']);
if ($arr && $arr[0]) {
$obj = $arr[0];
$cantidad = $obj['cantidad'];
}
$f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col++, $coor_consumoInter, $cantidad, true);
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
//FORMULA DE RESERVA DEL BALANCE
for ($col_number = 1; $col_number <= count($headers); $col_number++) {
$a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 4);
$a_coor = $a->getCoordinate();
$b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 5 + $num_fuentes);
$b_coor = $b->getCoordinate();
$prod = '=PRODUCT(' . $a_coor . '-' . $b_coor . ')';
$f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, $reserva_balance, $prod, true);
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
//SUMATORIA DE Grupo Empresarial de Comercio y la G.
for ($col_number = 1; $col_number <= count($headers); $col_number++) {
$a = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, 9);
$a_coor = $a->getCoordinate();
$b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($col_number, $canasta_familiar);
$b_coor = $b->getCoordinate();
$sum = '=SUM(' . $a_coor . ':' . $b_coor . ')';
$f = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col_number, 7 + $num_fuentes, $sum, true);
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getActiveSheet()->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
$t = 1;
foreach ($proveedores as $value) {
$a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($t, 2, $value[0], true);
$coordA = $a->getCoordinate();
$b = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($t + $value[1] - 1, 2);
$coordB = $b->getCoordinate();
$this->phpexcel->getActiveSheet()->mergeCells($coordA . ':' . $coordB);
$this->phpexcel->getActiveSheet()->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getActiveSheet()->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$t = $t + $value[1];
}
$j = 1;
foreach ($headers as $value) {
$a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($j, 3, $value['nombre_producto'], true);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90);
$this->phpexcel->getActiveSheet()->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle'));
$j = $j + 1;
}
$lastColumn_row1 = count($headers);
$lastCell = $this->phpexcel->getActiveSheet()->getCellByColumnAndRow($lastColumn_row1, 1);
$this->phpexcel->getActiveSheet()->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getActiveSheet()->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080');
// 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('PORTADA');
//MENSUAL
$i = 1;
$newWorkSheet = new PHPExcel_Worksheet($this->phpexcel, 'MENSUAL');
$this->phpexcel->addSheet($newWorkSheet, $i);
$organismos = BalanceAlimTable::getInstance()->organismos($time);
$headers = $this->headers_basedatos($time);
$balanceAlim = $this->balance_bd($time);
$valores = $this->matrix_de_valores_ajuste_productos($time);
$matrix = $this->create_matrix($headers, $balanceAlim, $valores);
$pr1 = $this->proveedores_bd($time);
$pr2 = $this->proveedores_bd2($time);
$proveedores = $this->count_distinct($pr1, $pr2, 'nombre');
// Add some data
$this->phpexcel->getSheet($i)->setCellValue('A1', 'BALANCE DE ALIMENTO DEL MES');
$this->phpexcel->getSheet($i)->getStyle('A1')->getAlignment()->setIndent(1);
$this->phpexcel->getSheet($i)->mergeCells('A1:D1');
$this->phpexcel->getSheet($i)->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Set fills
$this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->getStartColor()->setARGB('FF808080');
// Set fonts
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setName('Candara');
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setSize(19);
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setBold(true);
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$this->phpexcel->getSheet($i)->getColumnDimension('A')->setAutoSize(true);
$this->phpexcel->getSheet($i)->setCellValue('A4', 'TOTAL CAP');
$this->phpexcel->getSheet($i)->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->phpexcel->getSheet($i)->getStyle('A4')->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle'));
//NOMBRE DE LAS COLUMNAS
$this->phpexcel->getSheet($i)->setCellValue('A3', 'ENTIDAD/COMEDOR');
$this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle'));
$indice_organismos = array();
$x = 5;
foreach ($organismos as $org) {
$entityNames = BalanceAlimTable::getInstance()->entidadesPorOrganismo($time, $org['organismo_id']);
$organism = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $x, $org['organismo_nombre'], true);
$this->phpexcel->getSheet($i)->getStyle($organism->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($organism->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$row_org = $x;
$arr_temp = array();
for ($index = 1; $index <= count($headers); $index++) {
$total_coord = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $x);
array_push($arr_temp, $total_coord->getCoordinate());
}
array_push($indice_organismos, $arr_temp);
$x = $x + 1;
$total_1 = array();
foreach ($entityNames as $en) {
$arr = BalanceAlimTable::getInstance()->balance_alimentacion_tabla($en['entidad_id']);
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $x, $en['entidad_nombre'], true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(1);
$a1 = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(1, $x);
$this->phpexcel->getSheet($i)->getStyle($a1->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$total_cap_por_org = array();
for ($index = 1; $index <= count($headers); $index++) {
$aa = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $x);
$this->phpexcel->getSheet($i)->getStyle($aa->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($aa->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$next = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $x + 1);
$coord1 = $next->getCoordinate();
$last = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, count($arr) + $x);
$coord2 = $last->getCoordinate();
$total_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $x, '=IF(SUM(' . $coord1 . ':' . $coord2 . ')>0,0,1)', true);
$total_cell = $total_coord->getCoordinate();
$this->phpexcel->getSheet($i)->getStyle($total_cell)->getNumberFormat()->setFormatCode('#,##0.000');
array_push($total_cap_por_org, $total_cell);
}
array_push($total_1, $total_cap_por_org);
$x = $x + 1;
foreach ($arr as $value) {
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $x, $value['comedor_nombre']);
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $x);
$coord_a = $a->getCoordinate();
$this->phpexcel->getSheet($i)->getStyle($coord_a)->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($coord_a)->getAlignment()->setIndent(2);
$col = 1;
foreach ($matrix[$value['id']] as $producto_id) {
$result_query = BaseDatosTable::getInstance()->valor_producto_mes($value['balance_id'], $producto_id);
$result = 0;
if (count($result_query) > 0) {
$result = $result_query[0]['valor_mes'];
$ajuste = $result_query[0]['ajuste_mes'] * $result / 100;
$result = $result + $ajuste;
}
$a = $this->phpexcel->getActiveSheet()->setCellValueByColumnAndRow($col, $x, $result, true);
$coordin = $a->getCoordinate();
$this->phpexcel->getSheet($i)->getStyle($coordin)->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($coordin)->getNumberFormat()->setFormatCode('#,##0.000');
$col = $col + 1;
}
$x = $x + 1;
}
}
$total = array();
foreach ($total_1[0] as $value) {
array_push($total, $value);
}
//quitar el primer elemento de la lista
array_shift($total_1);
foreach ($total_1 as $arr) {
for ($index = 0; $index < count($arr); $index++) {
$total[$index] = $total[$index] . '+' . $arr[$index];
}
}
for ($index = 1; $index <= count($headers); $index++) {
$sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $row_org, '=' . $total[$index - 1], true);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
}
//TOTAL GENERAL
$total = array();
foreach ($indice_organismos[0] as $value) {
array_push($total, $value);
}
//quitar el primer elemento de la lista
array_shift($indice_organismos);
foreach ($indice_organismos as $arr) {
for ($index = 0; $index < count($arr); $index++) {
$total[$index] = $total[$index] . '+' . $arr[$index];
}
}
for ($index = 1; $index <= count($headers); $index++) {
$sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, 4, '=' . $total[$index - 1], true);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
$coord = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(1, 4);
$this->phpexcel->getSheet($i)->getStyle($coord->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$t = 1;
foreach ($proveedores as $value) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($t, 2, $value[0], true);
$coordA = $a->getCoordinate();
$b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($t + $value[1] - 1, 2);
$coordB = $b->getCoordinate();
$this->phpexcel->getSheet($i)->mergeCells($coordA . ':' . $coordB);
$this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$t = $t + $value[1];
}
$j = 1;
foreach ($headers as $value) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, 3, $value['nombre_producto'], true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle'));
$j = $j + 1;
}
$lastColumn_row1 = count($headers);
$lastCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($lastColumn_row1, 1);
$this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080');
// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$this->phpexcel->getSheet($i)->getHeaderFooter()->setOddHeader('&RFecha &D');
$this->phpexcel->getSheet($i)->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set page orientation and size
$this->phpexcel->getSheet($i)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$this->phpexcel->getSheet($i)->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
//Sumatoria de TOTAL CAP
for ($col_number = 1; $col_number <= count($headers); $col_number++) {
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($col_number, 4);
$a_coor = $a->getCoordinate();
$suma = '=MENSUAL!' . $a_coor;
$f = $this->phpexcel->getSheet(0)->setCellValueByColumnAndRow($col_number, 6 + $num_fuentes, $suma, true);
$this->phpexcel->getSheet(0)->getStyle($f->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet(0)->getStyle($f->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
$i = $i + 1;
//EVENTOS
$newWorkSheet = new PHPExcel_Worksheet($this->phpexcel, 'EVENTOS');
$this->phpexcel->addSheet($newWorkSheet, $i);
$headers = $this->headers_eventos($time);
$items3 = $this->entidades_eventos($time);
$org = $this->organismos_eventos($time);
$org2 = $this->organismos_eventos2($time);
$organismos = $this->count_distinct($org, $org2, 'org_nombre');
$pr1 = $this->proveedores_eventos($time);
$pr2 = $this->proveedores_eventos2($time);
$proveedores = $this->count_distinct($pr1, $pr2, 'nombre');
$items4 = $this->matrix_de_valores_eventos($time);
$arr = $this->create_matrix($headers, $items3, $items4);
$this->phpexcel->getSheet($i)->setCellValue('B1', 'EVENTOS');
$this->phpexcel->getSheet($i)->mergeCells('B1:C1');
$this->phpexcel->getSheet($i)->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Set fonts
$this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setName('Candara');
$this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setSize(20);
$this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setBold(true);
$this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$this->phpexcel->getSheet($i)->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
// $this->phpexcel->getSheet($i)->setCellValue('E1', PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
// $this->phpexcel->getSheet($i)->getStyle('E1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
$this->phpexcel->getSheet($i)->setCellValue('B3', 'ORGANISMO/ENTIDAD');
$this->phpexcel->getSheet($i)->setCellValue('A3', 'REEUP');
$this->phpexcel->getSheet($i)->getColumnDimension('B')->setAutoSize(true);
$this->phpexcel->getSheet($i)->setCellValue('B4', 'RESERVA DE BALANCE');
$this->phpexcel->getSheet($i)->getStyle('A4')->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle('B4')->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getSheet($i)->getStyle('B3')->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getSheet($i)->getStyle('A2:B2')->applyFromArray($this->styles('headerStyle'));
$reserva = array();
$zz = 5;
foreach ($organismos as $value) {
$firstCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($firstCell->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$coor = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, $value[0], true);
$this->phpexcel->getSheet($i)->getStyle($coor->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$this->phpexcel->getSheet($i)->getStyle($coor->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$total_cap_por_org = array();
for ($index = 2; $index <= count($headers) + 1; $index++) {
$sum_coord = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, $zz);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
array_push($total_cap_por_org, $sum_coord->getCoordinate());
}
array_push($reserva, $total_cap_por_org);
$row = $zz;
$zz = $zz + 1;
$entidades = $this->entidades_por_organismo_eventos($time, $value[0]);
$subtotal = array();
foreach ($entidades as $value2) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, $value2['nombre_entidad'], true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(1);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$b = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $zz, $value2['reeup'], true);
$this->phpexcel->getSheet($i)->getStyle($b->getCoordinate())->applyFromArray($this->styles('borderStyle'));
//sumatoria almuerzo+comida+merienda
$subtotal_fila = array();
$x = 2;
foreach ($headers as $val) {
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 1);
$a_coor = $a->getCoordinate();
$b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 3);
$b_coor = $b->getCoordinate();
$sumatoria = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz, '=SUM(' . $a_coor . ':' . $b_coor . ')', true);
$this->phpexcel->getSheet($i)->getStyle($sumatoria->getCoordinate())->applyFromArray($this->styles('borderStyle'));
array_push($subtotal_fila, $sumatoria->getCoordinate());
$x = $x + 1;
}
array_push($subtotal, $subtotal_fila);
$zz = $zz + 1;
//Almuerzo
$first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Almuerzo", true);
$almuerzo = $a->getCoordinate();
$this->phpexcel->getSheet($i)->getStyle($almuerzo)->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($almuerzo)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$this->phpexcel->getSheet($i)->getStyle($almuerzo)->getAlignment()->setIndent(2);
$x = 2;
foreach ($headers as $val) {
$bd = BaseDatosTable::getInstance()->eventos($year . '%', $value2['entidad_id'], $val['producto_id']);
foreach ($bd as $bd_iter) {
$alm = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz, $bd_iter['almuerzo'], true);
//-----Agregar Comentario-------
$alm_coord = $alm->getCoordinate();
$this->phpexcel->getSheet($i)->getComment($alm_coord)->setAuthor('PHPExcel');
$objCommentRichText = $this->phpexcel->getSheet($i)->getComment($alm_coord)->getText()->createTextRun('Fórmula:');
$objCommentRichText->getFont()->setBold(true);
$this->phpexcel->getSheet($i)->getComment($alm_coord)->getText()->createTextRun("\r\n");
$this->phpexcel->getSheet($i)->getComment($alm_coord)->getText()->createTextRun('Se multiplica el ' . 'valor del almuerzo(Nivel de actividad) del evento por el valor del producto(Base de datos).');
//-----------------------------
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz + 1, $bd_iter['comida']);
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz + 2, $bd_iter['merienda']);
}
$celda = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz);
$this->phpexcel->getSheet($i)->getStyle($celda->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$celda = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 1);
$this->phpexcel->getSheet($i)->getStyle($celda->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$celda = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz + 2);
$this->phpexcel->getSheet($i)->getStyle($celda->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$x = $x + 1;
}
$zz = $zz + 1;
//Comida
$first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Comida", true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
$zz = $zz + 1;
//Merienda
$first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Merienda", true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
$zz = $zz + 1;
//Conceptos
$ev = EventosTable::getInstance()->eventos($year . '%', $value2['entidad_id']);
foreach ($ev as $iter) {
$first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, $iter['concepto'], true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
//Valor de los conceptos
$x = 2;
foreach ($headers as $val) {
$event = EventosTable::getInstance()->eventos_por_producto($year . '%', $value2['entidad_id'], $val['producto_id']);
foreach ($event as $event_iter) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $zz, $event_iter['cantidad'], true);
}
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$x = $x + 1;
}
$zz = $zz + 1;
}
//Ajustes
$first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "Ajustes", true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
$x = 2;
foreach ($headers as $val) {
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$x = $x + 1;
}
$zz = $zz + 1;
//%
$first = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $zz);
$this->phpexcel->getSheet($i)->getStyle($first->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(1, $zz, "%", true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
$x = 2;
foreach ($headers as $val) {
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $zz);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$x = $x + 1;
}
$zz = $zz + 1;
}
//Subtotal por organismo
$total = array();
foreach ($subtotal[0] as $value) {
array_push($total, $value);
}
//quitar el primer elemento de la lista
array_shift($subtotal);
foreach ($subtotal as $arr) {
for ($index = 0; $index < count($arr); $index++) {
$total[$index] = $total[$index] . '+' . $arr[$index];
}
}
for ($index = 2; $index <= count($headers) + 1; $index++) {
$sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $row, '=' . $total[$index - 2], true);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle'));
}
}
$t = 2;
foreach ($proveedores as $value) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($t, 2, $value[0], true);
$coordA = $a->getCoordinate();
$b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($t + $value[1] - 1, 2);
$coordB = $b->getCoordinate();
$this->phpexcel->getSheet($i)->mergeCells($coordA . ':' . $coordB);
$this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$t = $t + $value[1];
}
$j = 2;
foreach ($headers as $value) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, 3, $value['nombre_producto'], true);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle'));
$j = $j + 1;
}
//$this->phpexcel->getSheet($i)->fromArray($arr, null, 'B5', false);
$this->phpexcel->getSheet($i)->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//Crear formula para el total general
$row_four = 4;
$total = array();
foreach ($reserva[0] as $value) {
array_push($total, $value);
}
//quitar el primer elemento de la lista
array_shift($reserva);
foreach ($reserva as $arr) {
for ($index = 0; $index < count($arr); $index++) {
$total[$index] = $total[$index] . '+' . $arr[$index];
}
}
for ($index = 2; $index <= count($headers) + 1; $index++) {
$sum_coord = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, $row_four, '=' . $total[$index - 2], true);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$this->phpexcel->getSheet($i)->getStyle($sum_coord->getCoordinate())->applyFromArray($this->styles('blackStyle'));
}
// Set fills
$lastColumn_row1 = count($headers) + 1;
$lastCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($lastColumn_row1, 1);
$this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080');
// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$this->phpexcel->getSheet($i)->getHeaderFooter()->setOddHeader('&RFecha &D');
$this->phpexcel->getSheet($i)->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set page orientation and size
$this->phpexcel->getSheet($i)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$this->phpexcel->getSheet($i)->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
$i = $i + 1;
//ORGANISMOS NOMINALIZADOS
$newWorkSheet = new PHPExcel_Worksheet($this->phpexcel, 'NOMINALIZADOS Y OTROS');
$this->phpexcel->addSheet($newWorkSheet, $i);
$headers = $this->headers_nominalizados($time);
$items3 = $this->organismos_nominalizados($time);
$pr1 = $this->proveedores_nominalizados($time);
$pr2 = $this->proveedores_nominalizados2($time);
$proveedores = $this->count_distinct($pr1, $pr2, 'nombre');
$items4 = $this->data_nominalizados($time);
$arr = $this->create_matrix($headers, $items3, $items4);
// Add some data
$this->phpexcel->getSheet($i)->setCellValue('A1', 'Organismos Nominalizados');
$this->phpexcel->getSheet($i)->mergeCells('A1:B1');
$this->phpexcel->getSheet($i)->getStyle('A1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Set fonts
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setName('Candara');
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setSize(20);
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setBold(true);
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$this->phpexcel->getSheet($i)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$this->phpexcel->getSheet($i)->setCellValue('A3', 'Nombre');
$this->phpexcel->getSheet($i)->getColumnDimension('A')->setAutoSize(true);
$this->phpexcel->getSheet($i)->setCellValue('A4', 'Total');
$this->phpexcel->getSheet($i)->getStyle('A4')->applyFromArray($this->styles('blackStyle'));
for ($index = 1; $index <= count($headers); $index++) {
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, 4);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$next = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, 5);
$coord1 = $next->getCoordinate();
$last = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($index, count($items3) + 4);
$coord2 = $last->getCoordinate();
$formula = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($index, 4, '=SUM(' . $coord1 . ':' . $coord2 . ')', true);
$this->phpexcel->getSheet($i)->getStyle($formula->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
}
$this->phpexcel->getSheet($i)->getStyle('A3')->applyFromArray($this->styles('headerStyle'));
$iii = 5;
foreach ($items3 as $value) {
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $iii, $value['nombre_entidad']);
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $iii);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$iii = $iii + 1;
}
$t = 1;
foreach ($proveedores as $value) {
$a = $this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($t, 2, $value[0], true);
$coordA = $a->getCoordinate();
$b = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($t + $value[1] - 1, 2);
$coordB = $b->getCoordinate();
$this->phpexcel->getSheet($i)->mergeCells($coordA . ':' . $coordB);
$this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->applyFromArray($this->styles('headerStyle'));
$this->phpexcel->getSheet($i)->getStyle($coordA . ':' . $coordB)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$t = $t + $value[1];
}
$j = 1;
foreach ($headers as $value) {
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, 3, $value['nombre_producto']);
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($j, 3);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setTextRotation(90);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('headerStyle'));
$j = $j + 1;
}
$y = 5;
foreach ($arr as $v) {
$x = 1;
foreach ($v as $v2) {
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($x, $y, $v2);
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($x, $y);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('borderStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
$x = $x + 1;
}
$y = $y + 1;
}
//MERCADOS
$mercados = NomMercadoTable::getInstance()->mercados($time);
foreach ($mercados as $value) {
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow(0, $iii, $value['nombre']);
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow(0, $iii);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getAlignment()->setIndent(2);
$j = 1;
foreach ($headers as $v) {
$cantidad = 0;
$arr = ProductoMercadoTable::getInstance()->cantidad($time, $v['producto_id'], $value['mercado_id']);
if ($arr && $arr[0]) {
$obj = $arr[0];
$cantidad = $obj['cantidad'];
}
$this->phpexcel->getSheet($i)->setCellValueByColumnAndRow($j, $iii, $cantidad);
$a = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($j, $iii);
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->applyFromArray($this->styles('blackStyle'));
$this->phpexcel->getSheet($i)->getStyle($a->getCoordinate())->getNumberFormat()->setFormatCode('#,##0.000');
$j = $j + 1;
}
$iii = $iii + 1;
}
//$this->phpexcel->getSheet($i)->fromArray($arr, null, 'B5', false);
$this->phpexcel->getSheet($i)->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
// Set fills
$lastColumn_row1 = count($headers);
$lastCell = $this->phpexcel->getSheet($i)->getCellByColumnAndRow($lastColumn_row1, 1);
$this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getSheet($i)->getStyle('A1:' . $lastCell->getCoordinate())->getFill()->getStartColor()->setARGB('FF808080');
$this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->phpexcel->getSheet($i)->getStyle('A2')->getFill()->getStartColor()->setARGB('FF808080');
// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$this->phpexcel->getSheet($i)->getHeaderFooter()->setOddHeader('&RFecha &D');
$this->phpexcel->getSheet($i)->getHeaderFooter()->setOddFooter('&L&B' . $this->phpexcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set page orientation and size
$this->phpexcel->getSheet($i)->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$this->phpexcel->getSheet($i)->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$this->phpexcel->setActiveSheetIndex(1);
return $this->phpexcel;
}
