/**
*
* @param string $proveedor_id
* @param string $texto_1er_elemento
* @param string $color_1er_elemento
* @return string
*/
function getCombo($proveedor_id, $texto_1er_elemento = "<Seleccione>", $color_1er_elemento = "#FFFFAA", $buscar_proveedor_id = null)
{
$ProveedorDAO = new ProveedorDAO();
$ProveedorDAO->setEntityManager($this->getEntityManager());
$result = $ProveedorDAO->consultarTodos($buscar_proveedor_id);
$opciones = \Application\Classes\Combo::getComboDataResultset($result, 'id', 'nombre', $proveedor_id, $texto_1er_elemento, $color_1er_elemento);
return $opciones;
}
public function generarExcelInternoCajas($condiciones)
{
set_time_limit(0);
ini_set('memory_limit', '-1');
$GrupoDispoCabDAO = new GrupoDispoCabDAO();
$ProveedorDAO = new ProveedorDAO();
$GrupoDispoCabDAO->setEntityManager($this->getEntityManager());
$ProveedorDAO->setEntityManager($this->getEntityManager());
//----------------Se configura las Etiquetas de Seleccion-----------------
$texto_grupo_dispo_cab_id = 'TODOS';
$texto_color_ventas_id = 'TODOS';
$texto_calidad_variedad_id = 'TODOS';
$inventario_id = $condiciones['inventario_id'];
if (!empty($condiciones['grupo_dispo_cab_id'])) {
$texto_grupo_dispo_cab_id = $condiciones['grupo_dispo_cab_id'];
}
//end if
if (!empty($condiciones['color_ventas_id'])) {
$texto_color_ventas_id = $condiciones['color_ventas_id'];
}
//end if
if (!empty($condiciones['calidad_variedad_id'])) {
$texto_calidad_variedad_id = $condiciones['calidad_variedad_id'];
}
//end if
//----------------Se inicia la configuracion del PHPExcel-----------------
$PHPExcelApp = new PHPExcelApp();
$objPHPExcel = new \PHPExcel();
// Set document properties
$PHPExcelApp->setUserName('');
$PHPExcelApp->setMetaDataDocument($objPHPExcel);
$objPHPExcel->setActiveSheetIndex(0);
//Configura el tamaño del Papel
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//Se establece la escala de la pagina
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
//Se establece los margenes de la pagina
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
//Consulta la lista de registros
$condiciones['opcion_dispo'] = 'BUNCH_TODOS';
$result_dispo = $this->listadoDisponibilidadPorProveedor($condiciones, true);
//Convertir Dispo en Cajas
$result_HB = $this->transformarDispoEnCajas($inventario_id, 'HB', $result_dispo);
$result_QB = $this->transformarDispoEnCajas($inventario_id, 'QB', $result_dispo);
//Consulta las fincas
$result_fincas = $ProveedorDAO->consultarTodos();
$estilo_titulo = $PHPExcelApp::STYLE_ARRAY_TITULO01;
$estilo_columna = $PHPExcelApp::STYLE_ARRAY_COLUMNA01;
$indice_hoja = -1;
$arr_tipo_caja[] = array('tipo_caja_id' => 'HB');
$arr_tipo_caja[] = array('tipo_caja_id' => 'QB');
foreach ($result_fincas as $reg_finca) {
foreach ($arr_tipo_caja as $reg_tipo_caja) {
switch ($reg_tipo_caja['tipo_caja_id']) {
case 'HB':
$result_procesar = $result_HB;
break;
case 'QB':
$result_procesar = $result_QB;
break;
}
//end switch
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
$indice_hoja++;
if ($indice_hoja > 0) {
$objPHPExcel->createSheet($indice_hoja);
$estilo_titulo = $PHPExcelApp::STYLE_ARRAY_TITULO02;
$estilo_columna = $PHPExcelApp::STYLE_ARRAY_COLUMNA02;
}
//end if
$objPHPExcel->setActiveSheetIndex($indice_hoja);
//------------------------------Registra la cabecera--------------------------------
$row = 1;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Disponibilidad Por Grupo");
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//------------------------------Registra criterios linea 1--------------------------
$row++;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
//$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objRichText = new \PHPExcel_RichText();
$objRichText->createText('');
$objInventario = $objRichText->createTextRun(' Grupo: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_grupo_dispo_cab_id);
$objInventario = $objRichText->createTextRun(' Color: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_color_ventas_id);
$objInventario = $objRichText->createTextRun(' Calidad: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_calidad_variedad_id);
$objPHPExcel->getActiveSheet()->getCell($col_ini . $row)->setValue($objRichText);
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
//------------------------------ Registro de Fecha de Generacion --------------------------------
$row++;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
//$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor());
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//---------------------------IMPRIME TITULO DE COLUMNA-----------------------------
$row++;
$row_detalle_ini = $row;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Variedad");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Color");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "40");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "50");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "60");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "70");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "80");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "90");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "100");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, "110");
//----------------------AUTO DIMENSIONAR CELDAS DE ACUERDO AL CONTENIDO---------------
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(6)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(8)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(9)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(10)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(11)->setWidth(6);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($estilo_titulo));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
// $objPHPExcel->getActiveSheet()->getStyle($col_ini.$row.':'.$col_fin.$row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
//----------------------CONSULTA LOS REGISTROS A EXPORTAR---------------
//$result = $this->listado($condiciones);
$cont_linea = 0;
$row_detalle_info_ini = $row + 1;
foreach ($result_procesar as $reg) {
if (!empty($reg['variedad_id'])) {
$reg['variedad_id'] = trim($reg['variedad_id']);
}
//end if
if (!empty($reg['color_ventas_nombre'])) {
$reg['color_ventas_nombre'] = trim($reg['color_ventas_nombre']);
}
//end if
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['variedad_id']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
if ($reg['tallos_x_bunch'] == 25) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
} else {
$objRichText = new \PHPExcel_RichText();
$objRichText->createText($reg['variedad']);
$objInventario = $objRichText->createTextRun(' (' . $reg['tallos_x_bunch'] . ')');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setItalic(true);
$col_variedad = $PHPExcelApp->getNameFromNumber(2);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\Application\Classes\PHPExcelApp::COLOR_ORANGE));
$objPHPExcel->getActiveSheet()->getCell($col_variedad . $row)->setValue($objRichText);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad'] );
}
//end if
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['color_ventas_nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['40']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['50']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['60']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['70']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['80']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['90']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['100']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $reg['100']['cajas']['fincas'][$reg_finca['id']]['total']);
}
// end foreach
//Formato de Numeros
$col_ini = $PHPExcelApp->getNameFromNumber(4);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$row_detalle_info_ini = $row_detalle_ini + 1;
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ':' . $col_fin . $row)->getNumberFormat()->setFormatCode("#,###");
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($reg_finca['id'] . ' - ' . $reg_tipo_caja['tipo_caja_id']);
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
}
//end foreach
}
//end foreach($result_fincas as $reg_finca)
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Dispo Grupo.xlsx");
}
public function consultarPorInventarioPorCalidadPorVariedadPorGrado($inventario_id, $calidad_id, $variedad_id, $grado_id)
{
$ProveedorDAO = new ProveedorDAO();
$DispoDAO = new DispoDAO();
$CalidadDAO = new CalidadDAO();
$DispoDAO->setEntityManager($this->getEntityManager());
$CalidadDAO->setEntityManager($this->getEntityManager());
$ProveedorDAO->setEntityManager($this->getEntityManager());
$CalidadData = $CalidadDAO->consultar($calidad_id);
$clasifica_fox = null;
if ($CalidadData) {
$clasifica_fox = $CalidadData->getClasificaFox();
}
//end if
$row = null;
$result_proveedor = $ProveedorDAO->consultarTodos();
foreach ($result_proveedor as $reg) {
$row[$reg['id']]['tot_bunch_disponible'] = 0;
}
//end foreach
$result = $DispoDAO->consultarPorInventarioPorCalidadPorVariedadPorGrado($inventario_id, $clasifica_fox, $variedad_id, $grado_id);
foreach ($result as $reg) {
$row[$reg['proveedor_id']]['tot_bunch_disponible'] = $reg['tot_bunch_disponible'];
}
//end if
return $row;
}
