public function getExcel($lista_id)
{
$nomina = VistaNomina::select('empleado as Empleado', 'salario_diario as SD', 'salario_semanal as Sueldo', 'dias_pago as DT', 'nomina_ss as SS', 'h_extra as H.E.', 'p_dominical as PD', 'otras_percepciones as OP', 'bono_mtto as BM', DB::raw('if(infonavit > 0, infonavit * -1, 0 ) as INF'), DB::raw('if(abono_prestamo > 0, abono_prestamo * -1, 0 ) as PRE'), 'nomina as Total', DB::raw('if(ss > 0, " ", 0 ) as firma'))->where('lista_id', '=', $lista_id)->get();
$lista = Lista::find($lista_id);
$fecha = 'Nomina ' . date("d/M/Y", strtotime($lista->fecha_inicio)) . ' al ' . date("d/M/Y", strtotime($lista->fecha_fin));
$empleados_no = VistaNomina::where('lista_id', '=', $lista_id)->count();
$incidencias = VistaNomina::select('empleado as Empleado', 'departamento as Depto.', DB::raw('sa+do+lu+ma+mi+ju+vi as Días'), DB::raw('if(sa > 0, "Ok", "" ) as Sab'), DB::raw('if(do > 0, "Ok", "" ) as Dom'), DB::raw('if(lu > 0, "Ok", "" ) as Lun'), DB::raw('if(ma > 0, "Ok", "" ) as Mar'), DB::raw('if(mi > 0, "Ok", "" ) as Mie'), DB::raw('if(ju > 0, "Ok", "" ) as Jue'), DB::raw('if(vi > 0, "Ok", "" ) as Vie'), 'prima_dominical as PD', 'hora_extra as HE', 'observaciones as Observaciones')->where('lista_id', '=', $lista_id)->get();
ob_end_clean();
ob_start();
//At the very top of your program (first line)
Excel::create($fecha, function ($excel) use($nomina, $incidencias, $empleados_no, $fecha) {
// Set the title
$excel->setTitle('Relacion de incidencias semanal');
// Chain the setters
$excel->setCreator('By MDL')->setCompany('Parque Funeral Guadalule');
// Call them separately
$excel->setDescription('A demonstration to change the file properties');
$excel->sheet('Nomina', function ($sheet) use($nomina, $empleados_no, $fecha) {
// Manipulate first row
//DATOS GENERALES DE LA NOMINA
$sheet->row(1, array('INVERSIONES PFG, S.A. DE C.V. ' . $fecha));
//ESPACIO PARA ENCABEZADO
$sheet->mergeCells('A1:M1');
//FORMATOS DE LAS COL
$sheet->setColumnFormat(array('B' => '0.00', 'C' => '0.00', 'E' => '0.00', 'F' => '0.00', 'G' => '0.00', 'H' => '0.00', 'I' => '0.00', 'J' => '0.00', 'K' => '0.00', 'L' => '0.00'));
//formato de celdas TITULOS en negritas
$sheet->cells('A1:O1', function ($cells) {
//primer renglon en negritas
$cells->setFontWeight('bold');
// Set font size
$cells->setFontSize(14);
// Set alignment to center
$cells->setAlignment('center');
// Set vertical alignment to middle
$cells->setValignment('middle');
});
//formato de celdas encabezado en negritas, CONGELA LA PRIMER COLUMNA Y LOS RENGLONES HASTA EL 3
$sheet->freezePaneByColumnAndRow(1, 3)->cells('A2:M2', function ($cells) {
//primer renglon en negritas
$cells->setFontWeight('bold');
// Set font size
$cells->setFontSize(12);
// Set alignment to center
$cells->setAlignment('center');
// Set vertical alignment to middle
$cells->setValignment('middle');
// Set black background
$cells->setBackground('#000000');
// Set with font color
$cells->setFontColor('#ffffff');
});
//formato y alineacion de el contenido
$sheet->cells('C1:L' . ($empleados_no + 2), function ($cells) {
// Set alignment to center
$cells->setAlignment('center');
// Set vertical alignment to middle
$cells->setValignment('middle');
});
//primer renglon con borde
$sheet->setBorder('A2:M' . ($empleados_no + 2), 'thin');
for ($i = 2; $i < $empleados_no + 3; $i++) {
$sheet->setHeight($i, 25);
}
// Set width for multiple cells
$sheet->setWidth(array('A' => 35, 'C' => 10, 'D' => 5, 'E' => 10, 'L' => 10, 'M' => 25));
$sheet->setOrientation('landscape');
$sheet->fromArray($nomina, null, 'A2', false);
});
//segunda hoja de movimientos
$excel->sheet('Movimientos', function ($sheet) use($incidencias, $fecha, $empleados_no) {
// Manipulate first row
//DATOS GENERALES DE LA NOMINA
$sheet->row(1, array('INVERSIONES PFG, S.A. DE C.V. ' . " OBSERVACIONES DE " . $fecha));
//ESPACIO PARA ENCABEZADO
$sheet->mergeCells('A1:M1');
//formato de celdas TITULOS en negritas
$sheet->cells('A1:O1', function ($cells) {
//primer renglon en negritas
$cells->setFontWeight('bold');
// Set font size
$cells->setFontSize(14);
// Set alignment to center
$cells->setAlignment('center');
// Set vertical alignment to middle
$cells->setValignment('middle');
});
//formato de celdas encabezado en negritas, CONGELA LA PRIMER COLUMNA Y LOS RENGLONES HASTA EL 3
$sheet->freezePaneByColumnAndRow(1, 3)->cells('A2:M2', function ($cells) {
//primer renglon en negritas
$cells->setFontWeight('bold');
// Set font size
$cells->setFontSize(12);
// Set alignment to center
$cells->setAlignment('center');
// Set vertical alignment to middle
$cells->setValignment('middle');
// Set black background
$cells->setBackground('#000000');
// Set with font color
$cells->setFontColor('#ffffff');
});
$sheet->setOrientation('landscape');
$sheet->fromArray($incidencias, null, 'A2', true);
//COLOR DE LOS DOMINGOS
$sheet->cells('E3:E' . ($empleados_no + 2), function ($cells) {
$cells->setBackground('#F7FE2E');
$cells->setFontWeight('bold');
});
});
})->export('xlsx');
ob_flush();
}
