<?php
if (PHP_SAPI == 'cli')
  die('Se visualiza solamente desde un navegador.');
require_once dirname(__FILE__) . '/../../libreriasphp/phpexcel/Classes/PHPExcel.php';
date_default_timezone_set('America/Lima');
session_start();
require_once ("../../config/Cado.php");
require_once ("../compra/cCompra.php");
$oCompra = new cCompra();
require_once ("../venta/cVenta.php");
$oVenta = new cVenta();
require_once ("../notacredito/cNotacredito.php");
$oNotacredito = new cNotacredito();
require_once("../tipocambio/cTipocambio.php");
$oTipocambio = new ctipocambio();
$fec = date('Y-m-d');
require_once ("../formatos/formato.php");
require_once ("../formatos/fechas.php");
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("https://infosisperu.com")
->setLastModifiedBy("https://infosisperu.com")
->setTitle("REPORTE CONSOLIDADO IGV")
->setSubject("Reporte")
->setDescription("Reporte generado por https://infosisperu.com")
->setKeywords("")
->setCategory("reporte excel");
$objPHPExcel->setActiveSheetIndex(0);
$c=1;
$estiloTituloColumnas = array(
  'font' => array(
    'name'  => 'Arial',
    'bold'  => true,
    'size'  =>8,
    'color' => array(
      'rgb' => '000000'
      )
    ),
  'fill' => array(
    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array(
      'rgb' => 'FAFAFA')
    ),
  'borders' => array(
    'outline' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN ,
      'color' => array(
        'rgb' => '143860'
        )
      )
    ),
  'alignment' =>  array(
    'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    'wrap'      => TRUE
    )
  );
  
  $estiloTitulos = array(
  'font' => array(
    'name'  => 'Arial',
    'bold'  => true,
    'size'  =>8,
    'color' => array(
      'rgb' => 'FFFFFF'
      )
    ),
  'fill' => array(
    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array(
      'rgb' => 'FF8800')
    ),
  'borders' => array(
    'outline' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN ,
      'color' => array(
        'rgb' => '143860'
        )
      )
    ),
  'alignment' =>  array(
    'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    'wrap'      => TRUE
    )
  );
  
  $estiloTotales = array(
  'font' => array(
    'name'  => 'Arial',
    'bold'  => true,
    'size'  =>8,
    'color' => array(
      'rgb' => 'FFFFFF'
      )
    ),
  'fill' => array(
    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array(
      'rgb' => '0d47a1')
    ),
  'borders' => array(
    'outline' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN ,
      'color' => array(
        'rgb' => '143860'
        )
      )
    ),
  'alignment' =>  array(
    'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    'wrap'      => TRUE
    )
  );
  
  $titulosColumnascompras = array(
  'FECHA',
  'DOCUMENTO',
  'RUC',
  'PROVEEDOR', 
  'EM', 
  'VALOR', 
  'IGV',
  'TOTAL'
  );
  $titulosColumnasventas = array(
  'FECHA',
  'DOCUMENTO',
  'RUC',
  'CLIENTE', 
  'EM', 
  'VALOR', 
  'IGV',
  'TOTAL'
  );
  
  $titulosColumnasnotas = array(
  'FECHA',
  'DOCUMENTO',
  'RUC',
  'CLIENTE', 
  'EM', 
  'FECHA DOC. RELACIONADO', 
  'DOC. RELACIONADO', 
  'VALOR', 
  'IGV',
  'TOTAL'
  );
  $letras = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
  
	$objPHPExcel->getActiveSheet()->setCellValue("A$c", 'COMPRAS');
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTitulos);
	
	$c = $c+1;
  
  $letracolumn = 0;
  
  //// COMPRAS
  for ($i=0; $i < count($titulosColumnascompras) ; $i++) { 
    $letracolumn = $i;
    $objPHPExcel->getActiveSheet()->setCellValue($letras[$i].$c, $titulosColumnascompras[$i]);
  }
  $objPHPExcel->getActiveSheet()->getStyle("A$c:".$letras[$letracolumn]."$c")->applyFromArray($estiloTituloColumnas);
		$c = $c+1;
 
		$dtc1 = $oCompra->mostrar_detalle_agru($_GET['mes'],$_GET['anio'],$_GET['emp']);
		while($dtc = mysqli_fetch_array($dtc1)){
		
		$dtc1d = $oCompra->mostrar_detalle_des($_GET['mes'],$_GET['anio'],$dtc['empid']);
		while($dt = mysqli_fetch_array($dtc1d)){
														
		if($dt['tb_moneda_id']== 2){
				$rs = $oTipocambio->consultar(fecha_mysql($dt['tb_compra_fec']));  
				$r = mysqli_fetch_array($rs);	
				$tipcam = formato_money3($r['tb_tipocambio_dolsun']);
		}
		else{
				$tipcam = '1.00';	
		}
		
		$valvenc = 0;
		$igvc = 0;
		$totc = 0;		
		$com_igvsub = $dt['tb_compra_igvsub'];
		$com_igvigv = $dt['tb_compra_igvigv'];
		
		$totc = $dt['tb_compra_tot'] * $tipcam;
		$valvenc = $totc / $com_igvsub;
		$igvc = $valvenc* $com_igvigv;		
		$totalc  += $totc; 
		$igvtotalc  += $igvc; 
		$valventotalc  += $valvenc; 
		
	$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, mostrarFecha($dt['tb_compra_fec']))
    ->setCellValue('B'.$c, $dt['tb_documento_abr'].' - '.$dt['tb_compra_numdoc'])    
    ->setCellValue('C'.$c, $dt['tb_proveedor_doc'])
    ->setCellValue('D'.$c, $dt['tb_proveedor_nom'])
    ->setCellValue('E'.$c, $dt['tb_empresa_nomcom'])   
    ->setCellValue('F'.$c, formato_money($valvenc))
    ->setCellValue('G'.$c, formato_money($igvc))
    ->setCellValue('H'.$c, formato_money($totc));
	
    $objPHPExcel->getActiveSheet(0)->getStyle("F$c:H$c")->getNumberFormat()->setFormatCode('0.00');
    $c++;
		
		
		}
		}
		
		mysqli_free_result($dtc1);
	$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, '')
    ->setCellValue('B'.$c, '')    
    ->setCellValue('C'.$c, '')
    ->setCellValue('D'.$c, '') 
    ->setCellValue('E'.$c, '')
    ->setCellValue('F'.$c, formato_money($valventotalc))
    ->setCellValue('G'.$c, formato_money($igvtotalc))
    ->setCellValue('H'.$c, formato_money($totalc));
	
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTotales);
	$objPHPExcel->getActiveSheet(0)->getStyle("F$c:H$c")->getNumberFormat()->setFormatCode('0.00');
	$c = $c +1;
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTituloColumnas);
	
	$c = $c+1;
	$objPHPExcel->getActiveSheet()->setCellValue("A$c", 'VENTAS');
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTitulos);
	
	$c = $c+1;
  	
	
	$letracolumn = 0;
  ///// VENTAAAAAS
  for ($i=0; $i < count($titulosColumnasventas) ; $i++) { 
    $letracolumn = $i;
    $objPHPExcel->getActiveSheet()->setCellValue($letras[$i].$c, $titulosColumnasventas[$i]);
  }
  $objPHPExcel->getActiveSheet()->getStyle("A$c:".$letras[$letracolumn]."$c")->applyFromArray($estiloTituloColumnas);
	$c = $c+1;
	
	
	
	
	
		$dtc1 = $oVenta->mostrar_detalle_agru($_GET['mes'],$_GET['anio'],$_GET['emp']);
		while($dtc = mysqli_fetch_array($dtc1)){
		
		$dtc1d = $oVenta->mostrar_detalle_des($_GET['mes'],$_GET['anio'],$dtc['empid']);
		while($dt = mysqli_fetch_array($dtc1d)){
		
		if($dt['tb_venta_estsun']==2){
			$valvenv = 0;
			$igvv = 0;
			$totv = 0;
		}else{
		if($dt['tb_moneda_id']==2){
				$rs = $oTipocambio->consultar(fecha_mysql($dt['tb_venta_fec']));  
				$r = mysqli_fetch_array($rs);	
				$tipcam = formato_money3($r['tb_tipocambio_dolsun']);
		}
		else{
				$tipcam = '1.00';	
		}
		
		$valvenv = 0;
		$igvv = 0;
		$totv = 0;
		$ven_igvsub = $dt['tb_venta_igvsub'];
		$ven_igvigv = $dt['tb_venta_igvigv'];
		$totv = $dt['tb_venta_tot'] * $tipcam;
		
		}
		
		
		$valvenv = $totv/$ven_igvsub;
		$igvv = $valvenv*$ven_igvigv;
		
		
		$totalv  += $totv; 
		$igvtotalv  += $igvv; 
		$valventotalv  += $valvenv; 
		
		
		$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, mostrarFecha($dt['tb_venta_fec']))
    ->setCellValue('B'.$c, $dt['tb_documento_abr'].' - '.$dt['tb_venta_numdoc'])    
    ->setCellValue('C'.$c, $dt['tb_cliente_doc'])
    ->setCellValue('D'.$c, $dt['tb_cliente_nom'])
    ->setCellValue('E'.$c, $dt['tb_empresa_nomcom'])   
    ->setCellValue('F'.$c, formato_money($valvenv))
    ->setCellValue('G'.$c, formato_money($igvv))
    ->setCellValue('H'.$c, formato_money($totv));
	
    $objPHPExcel->getActiveSheet(0)->getStyle("F$c:H$c")->getNumberFormat()->setFormatCode('0.00');
    $c++;
	
		
		}
		}
		mysqli_free_result($dtc1);
	
	
	
	$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, '')
    ->setCellValue('B'.$c, '')    
    ->setCellValue('C'.$c, '')
    ->setCellValue('D'.$c, '') 
    ->setCellValue('E'.$c, '')
    ->setCellValue('F'.$c, formato_money($valventotalv))
    ->setCellValue('G'.$c, formato_money($igvtotalv))
    ->setCellValue('H'.$c, formato_money($totalv));
	
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTotales);
	$objPHPExcel->getActiveSheet(0)->getStyle("F$c:H$c")->getNumberFormat()->setFormatCode('0.00');
	$c = $c +1;
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTituloColumnas);
	$c = $c+1;
	
	$totalv = 0; 
	$igvtotalv = 0; 
	$valventotalv  = 0; 
	
	$objPHPExcel->getActiveSheet()->setCellValue("A$c", 'NOTA DE CREDITO');
	$objPHPExcel->getActiveSheet()->getStyle("A$c:H$c")->applyFromArray($estiloTitulos);
	
	$c = $c+1;
  	
	$letracolumn = 0;
  ///// NOTA DE CREDITO
  for ($i=0; $i < count($titulosColumnasnotas) ; $i++) { 
    $letracolumn = $i;
    $objPHPExcel->getActiveSheet()->setCellValue($letras[$i].$c, $titulosColumnasnotas[$i]);
	}
	$objPHPExcel->getActiveSheet()->getStyle("A$c:".$letras[$letracolumn]."$c")->applyFromArray($estiloTituloColumnas);
	$c = $c+1;
	
		$dtc1 = $oNotacredito->mostrar_detalle_agru($_GET['mes'],$_GET['anio'],$_GET['emp']);
		while($dtc = mysqli_fetch_array($dtc1)){
				
		$dtc1d = $oNotacredito->mostrar_detalle_des($_GET['mes'],$_GET['anio'],$dtc['tb_empresa_id']);
		while($dt = mysqli_fetch_array($dtc1d)){
		
		if($dt['tb_notacredito_estsun']==2){
			$valvenv = 0;
		$igvv = 0;
		$totv = 0;
	}else{	
		if($dt['tb_moneda_id']==2){
				$rs = $oTipocambio->consultar(fecha_mysql($dt['tb_notacredito_fec']));  
				$r = mysqli_fetch_array($rs);	
				$tipcam = formato_money3($r['tb_tipocambio_dolsun']);
		}
		else{
				$tipcam = '1.00';	
		}
	
	$valvenv = 0;
		$igvv = 0;
		$totv = 0;
		$notcre_igvsub = $dt['tb_notacredito_igvsub'];
		$notcre_igvigv = $dt['tb_notacredito_igvigv'];
		$totv = $dt['tb_notacredito_tot'] * $tipcam;
	
	}
		
		
		$valvenv = $totv/$notcre_igvsub;
		$igvv = $valvenv * $notcre_igvigv;
		
		
		$totalv  += $totv; 
		$igvtotalv  += $igvv; 
		$valventotalv  += $valvenv; 
		
		$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, mostrarFecha($dt['tb_notacredito_fec']))
    ->setCellValue('B'.$c, $dt['tb_documento_abr'].' - '.$dt['tb_notacredito_numdoc'])    
    ->setCellValue('C'.$c, $dt['tb_cliente_doc'])
    ->setCellValue('D'.$c, $dt['tb_cliente_nom'])
    ->setCellValue('E'.$c, $dt['tb_empresa_nomcom'])  
    ->setCellValue('F'.$c, mostrarFecha($dt['tb_venta_fec']))   
    ->setCellValue('G'.$c, $dt['tb_venta_numdoc'])   	
    ->setCellValue('H'.$c, formato_money($valvenv))
    ->setCellValue('I'.$c, formato_money($igvv))
    ->setCellValue('J'.$c, formato_money($totv));
	
    $objPHPExcel->getActiveSheet(0)->getStyle("H$c:J$c")->getNumberFormat()->setFormatCode('0.00');
    $c++;	
		
			}
		}
		mysqli_free_result($dtc1);
	
	
	
	$objPHPExcel->getActiveSheet(0)
    ->setCellValue('A'.$c, '')
    ->setCellValue('B'.$c, '')    
    ->setCellValue('C'.$c, '')
    ->setCellValue('D'.$c, '') 
    ->setCellValue('E'.$c, '')
    ->setCellValue('F'.$c, '')
    ->setCellValue('G'.$c, '')
    ->setCellValue('H'.$c, formato_money($valventotalv))
    ->setCellValue('I'.$c, formato_money($igvtotalv))
    ->setCellValue('J'.$c, formato_money($totalv));
	
	$objPHPExcel->getActiveSheet()->getStyle("A$c:J$c")->applyFromArray($estiloTotales);
	$objPHPExcel->getActiveSheet(0)->getStyle("H$c:J$c")->getNumberFormat()->setFormatCode('0.00');
	$c = $c +1;
	$objPHPExcel->getActiveSheet()->getStyle("A$c:J$c")->applyFromArray($estiloTituloColumnas);
	$c = $c+1;
	
	
for($z = 'A'; $z <= 'L'; $z++){
  $objPHPExcel->getActiveSheet()->getColumnDimension($z)->setAutoSize(TRUE);
}
	
	$objPHPExcel->getActiveSheet()->setTitle('CONSOLIDADO IGV ');
	
	$nombre_archivo="CONSOLIDADO IGV ".date('d-m-Y H-i:s');
// Redirect output to a client’s web browser (Excel5)
//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment;filename="'.$nombre_archivo.'.xls"');
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$nombre_archivo.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.date('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;
?>