<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2015 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    ##VERSION##, ##DATE##
 */

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
//date_default_timezone_set('Bogota/Lima/Quito');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
Include ("../php_conexion.php");
Include ("../Class/funciones.php");

$fechaini=$_GET['fechaini'];
$fechafin=$_GET['fechafin'];

$querypacientes=mysqli_query($conexion,"SELECT codpaciente,idpaciente,apepaterno,apematerno,nombres,fecha,muestra,precioexamen,importe FROM caja c JOIN pacientes p ON c.idpaciente=p.id AND c.fecha>='$fechaini' AND c.fecha<='$fechafin' ORDER BY c.id DESC");

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$sheet = $objPHPExcel->getActiveSheet();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Jose Huiñapi Peralta")
							 ->setLastModifiedBy("Jose Huiñapi Peralta")
							 ->setTitle("REPORTE CAJA")
							 ->setSubject("CAJA $fechaini AL $fechafin")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");

//COMBINANDO CELDAS
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:G1');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:G2');

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('c')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);


/*$objPHPExcel->getActiveSheet()
                ->getStyle('A1')
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)
                ->getColor()
                ->setRGB('DDDDDD');



$objPHPExcel->getActiveSheet()->getStyle('A1:M1')->applyFromArray(
    array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => 'DDDDDD')
            )
        )
    )
);
*/

$estilo = array( 
  'borders' => array(
    'outline' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN
    )
  )
);

//$objPHPExcel->getActiveSheet()->getStyle('A1:M2')->applyFromArray($estilo);


//$sheet->setCellValue('A2', 'ID');
//$sheet->setCellValue('B2', 'Name');
//$sheet->setCellValue('C2', 'Date');
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'SISTEMA DE GESTION DE CONSULTORIO');
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A2', 'REGISTRO EMITIDOS DEL '.$fechaini.' AL '.$fechafin);


// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', 'COD. PACIENTE')
            ->setCellValue('B3', 'NOMBRES')
            ->setCellValue('C3', 'FECHA')
            ->setCellValue('D3', 'MUESTRA')
            ->setCellValue('E3', 'PRECIO EXAMEN')
            ->setCellValue('F3', 'IMPORTE');


// Fuente de la primera fila en negrita
$boldArray = array('font' => array('bold' => true,),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
$objPHPExcel->getActiveSheet()->getStyle('A1:M3')->applyFromArray($boldArray);

$fila=4;
$total=0;
// Miscellaneous glyphs, UTF-8
while ($row=mysqli_fetch_array($querypacientes)) {
	$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$fila, $row['codpaciente'])
            ->setCellValue('B'.$fila, $row['nombres']." ".$row['apepaterno']." ".$row['apematerno'])
            ->setCellValue('C'.$fila, $row['fecha'])
            ->setCellValue('D'.$fila, $row['muestra'])
            ->setCellValue('E'.$fila, $row['precioexamen'])
            ->setCellValue('F'.$fila, $row['importe']);

            $total=$total+$row['importe'];

$fila++;
}

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('E'.($fila+1), 'TOTAL CAJA S/.')
            ->setCellValue('F'.($fila+1), $total);

//FORMATO DE CELDAS
$rango="A3:F".($fila-1);
$styleArray = array('font' => array( 'name' => 'Arial','size' => 10),
'borders'=>array('allborders'=>array('style'=> PHPExcel_Style_Border::BORDER_THIN,'color'=>array('argb' => '000')))
);
$objPHPExcel->getActiveSheet()->getStyle($rango)->applyFromArray($styleArray);

// Cambiar el nombre de hoja de cálculo
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Reporte de Caja');

// Establecer índice de hoja activa a la primera hoja , por lo que Excel abre esto como la primera hoja
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirigir la salida al navegador web de un cliente ( Excel5 )
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Reporte caja del '.$fechaini.' al '.$fechafin.'.xlsx"');
header('Cache-Control: max-age=0');
// Si usted está sirviendo a IE 9 , a continuación, puede ser necesaria la siguiente
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// Si usted está sirviendo a IE a través de SSL , a continuación, puede ser necesaria la siguiente
// 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: '.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, 'Excel2007');
$objWriter->save('php://output');
exit;
?>