<?php
date_default_timezone_set('America/Bogota');
require_once 'PHPExcel/PHPExcel.php';
if (isset($_SESSION['tipousuario']))
		{if ($_SESSION['tipousuario']=='1')
		header ('location:menu_personal.php');
		}
//PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
		// Se crea el objeto PHPExcel
		$objPHPExcel = new PHPExcel();


		// Se asignan las propiedades del libro
		$objPHPExcel->getProperties()->setCreator("Saireh") //Autor
							 ->setLastModifiedBy("Saireh") //Ultimo usuario que lo modificó
							 ->setTitle("Reporte Prima Servicios")
							 ->setSubject("Reporte Prima Servicios ")
							 ->setDescription("Reporte")
							 ->setKeywords("reporte")
							 ->setCategory("Reporte Asientos");


    $head=array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'a5a5a5')),
		'font' => array('color'=> array('rgb' => '000000')));
	$header=array("dTipoDocumento","dConsecutivo","dTercero","dDescripcion","dFecha","dVencimiento","Concepto","Cuenta","SubCuenta","mDebito","mCredito","mDescripcion","mNit","mBase","mCentroC","mSegmento");
	$dpto=array();
	$cargo=array();

	/*$sql=mysqli_query($enlace,"SELECT * FROM tablas WHERE lower(tabla)='departamento' and id=".trim($_SESSION['id_validar']));
	while($row = mysqli_fetch_array($sql)){
		$dpto[$row['codigo']]=$row['detalle'];
	}*/
    $i=2;
    $cedula="";
	$sql=mysqli_query($enlace,$consulta);
	$num_filas=mysqli_num_rows($sql);

	$sql2=mysqli_query($enlace,$consulta_concepto_total);
	$concepto_total = mysqli_fetch_array($sql2);

	while($row = mysqli_fetch_array($sql)){
		if($cedula<>$row['cedula']){//nuevo row
		    if ($i != 2) {
				$total = ($sum_debito - $sum_credito);
				$objPHPExcel->getActiveSheet()->setCellValue("C".$i,$cedula)
											->setCellValue("D".$i,$row['detalle'])
											->setCellValue("E".$i,"")
												//->setCellValue("F".$i,date("d/m/Y", strtotime(substr(trim($row['desperiodo']), -10))))
												->setCellValue("F".$i,"")
												->setCellValue("G".$i,"")

											->setCellValue("H".$i,$concepto_total["cuenta"])
											->setCellValue("I".$i,$concepto_total["subcuenta"])
											->setCellValue("J".$i,0)
											->setCellValue("K".$i,$total)
											->setCellValue("L".$i,$concepto_total["detalle"])
											->setCellValue("M".$i,$temp_cedula)
											->setCellValue("N".$i,"0")
									        ->setCellValueExplicit("O".$i,str_pad($temp_departamento, 8, "0", STR_PAD_LEFT))
											->setCellValue("P".$i,"",PHPExcel_Cell_DataType::TYPE_STRING);
				$sum_credito = 0;
				$sum_debito = 0;
				$i++;
			}
			//asignar la cedula
		   $cedula=$row['cedula'];
		   $objPHPExcel->getActiveSheet()->setCellValue("A".$i,"NP")
									   ->setCellValue("B".$i,"")
									   ->setCellValue("C".$i,$cedula)
									   ->setCellValue("D".$i,$row['detalle'])
									   ->setCellValue("E".$i,date("d/m/Y", strtotime(substr(trim($row['detalle']), -10))))
									   //->setCellValue("F".$i,date("d/m/Y", strtotime(substr(trim($row['detalle']), -10))))
									   ->setCellValue("F".$i,"")
									   ->setCellValue("G".$i,"");
	        $i++;
		}
		$cuenta=$row['cuenta'];
		$subcuenta=$row['subcuenta'];


		//asignar el excel
		$objPHPExcel->getActiveSheet()->setCellValue("C".$i,$cedula)
									   ->setCellValue("D".$i,$row['detalle'])
									   ->setCellValue("G".$i,$row['concepto'])
									   ->setCellValue("H".$i,$cuenta)
									   ->setCellValue("I".$i,$subcuenta)
									   ->setCellValue("J".$i,$row['tipo']=='D'?0 : ($row['monto']) )
									   ->setCellValue("K".$i,$row['tipo']=='D'? ($row['monto']) : 0)
									   ->setCellValue("L".$i,$row['desconcepto'])
									   ->setCellValue("M".$i,$cedula)
									   ->setCellValue("N".$i,"0")
									   ->setCellValueExplicit("O".$i,str_pad($row['departamento'], 8, "0", STR_PAD_LEFT))
									   ->setCellValue("P".$i,"",PHPExcel_Cell_DataType::TYPE_STRING);

		//$objPHPExcel->getActiveSheet()->getStyle("N".$i)->getNumberFormat()->setFormatCode('0000');
		if ($row["tipo"]=='D') { $sum_credito += ($row['monto']); }
		else { $sum_debito += ($row['monto']); }

		//cuando se haga el calculo de nomina
		$temp_cedula = $row['cedula'];
		$temp_departamento = $row['departamento'];
		$i++;
		//para el ultimo registro del excel
		if ($i >= $num_filas) {
		    $total = ($sum_debito - $sum_credito);
			$objPHPExcel->getActiveSheet()
										->setCellValue("G".$i,"")
										->setCellValue("H".$i,"20C02")
										->setCellValue("I".$i,"220")
										->setCellValue("J".$i,0)
										->setCellValue("K".$i,$total)
										->setCellValue("L".$i,$concepto_total["detalle"])
										->setCellValue("M".$i,$temp_cedula)
										->setCellValue("N".$i,"0")
								        ->setCellValueExplicit("O".$i,str_pad($temp_departamento, 8, "0", STR_PAD_LEFT))
										->setCellValue("P".$i,"",PHPExcel_Cell_DataType::TYPE_STRING);
		}

	}

	 $j=0;
	$letra='A';
	for($j=0;$j<count($header);$j++){
		$objPHPExcel->getActiveSheet()->setCellValue($letra."1",$header[$j]);
		$objPHPExcel->getActiveSheet()->getColumnDimension($letra)->setAutoSize(true);
		$letra++;
	}



	// Se asigna el nombre a la hoja
	$objPHPExcel->getActiveSheet()->setTitle('CONTABILIDAD');
	// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
	$objPHPExcel->setActiveSheetIndex(0);
	// Inmovilizar paneles
	//$objPHPExcel->getActiveSheet(0)->freezePane('A4');
$nombrearch="Asientos_Utilidades";
//header('Content-Type: application/vnd.ms-excel');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$nombrearch.'.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
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 = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
