<?php

//error_reporting(E_ALL);
//ini_set('display_errors', 1);

session_cache_limiter('private');
session_start();
$com = "I";
include("DMMYSQLG.php");
include("DSA3000.php");
include('DF80000.php');
require_once 'excel/PHPExcel.php';

date_default_timezone_set('America/Mexico_City');

// Crea un nuevo objeto PHPExcel
$objPHPExcel = new PHPExcel();

// Establecer propiedades
$objPHPExcel->getProperties()
->setCreator("Jorge")
->setLastModifiedBy("Jorge")
->setTitle("REPORTE DE ENTRADAS GENERALES POR PROVEEDORES")
->setSubject("Reporte")
->setDescription("REPORTE DE ENTRADAS GENERALES POR PROVEEDORES")
->setKeywords("")
->setCategory("");

// Propiedades Celda
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);  
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(0)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);;
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(2)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(3)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(4)->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension('1')->setRowHeight(20);  

$style = array(
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

$objPHPExcel->getDefaultStyle()->applyFromArray($style);






$meses = array("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre");


$f=5;

$ano=$_POST['fec'];
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(0, 1, "REPORTE DE ENTRADAS GENERALES POR PROVEEDORES")
->setCellValueByColumnAndRow(0, 2, "REPORTE DEL AÑO ".$ano)
->mergeCells('A1:E1');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(1, 1, "año del reporte".$ano);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(0,$f, "")
->setCellValueByColumnAndRow(1,$f, "ARTICULO");

for ($i=0; $i<sizeof($meses); $i++){
	
  $objPHPExcel->setActiveSheetIndex(0)
  ->setCellValueByColumnAndRow($i+2,$f, $meses[$i]);
}
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal('left'); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B5:N5')->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()
->setRGB('F28A8C');
$letra=1;
$mes=1;
$let='C';
$b=0;
$arreglo=array();
for ($i=0; $i<sizeof($meses); $i++){
    $a=0;

    $letra++;

    $sql = mysqli_query($con ,"SELECT * FROM fproveedores where t1='ACTIVO' order by np_desc1 ");

    $numeros=mysqli_num_rows($sql);

    $f=5;  
                                    while($reg=mysqli_fetch_array($sql)){//deptos	
                                        $f++;	
                                            //echo intval($reg['nparte1'])."<br>";
                                        if($i==0){
                                            $objPHPExcel->setActiveSheetIndex(0)
                                            ->setCellValueByColumnAndRow(0,$f, "")
                                            ->setCellValueByColumnAndRow(1,$f,utf8_decode($reg['np_desc1'] ) );
                                            $objPHPExcel->setActiveSheetIndex(0)->getStyle('B'.$f)->getFill()
                                            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                                            ->getStartColor()
                                            ->setRGB('F28A8C');
                                        }
                                        $prov=$reg['nparte1'];
                                        $articulos = mysqli_query($con ,"SELECT SUM(dgreqded.subtotal) AS canti FROM dgreqdev
                                            INNER JOIN dgreqded ON dgreqdev.fol_ven = dgreqded.cons_ven
                                            INNER JOIN farticulos on dgreqded.nparte1=farticulos.nparte1
                                            where dgreqdev.tip_ven='$prov'
                                            and   YEAR(dgreqdev.fec_ven)='$ano'
                                            and   MONTH(dgreqdev.fec_ven)='$mes'
                                            and dgreqdev.cancelado = 0 ");
                                        $objPHPExcel->getActiveSheet()->getStyle($let.$f)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                                        if ($f==6) {
                                            $objPHPExcel->setActiveSheetIndex(0)->getStyle($let.$f)->getNumberFormat()->setFormatCode('$#,##0.00');
                                        } else {
                                            $objPHPExcel->setActiveSheetIndex(0)->getStyle($let.$f)->getNumberFormat()->setFormatCode('#,##0.00');
                                        }

                                        while($cant=mysqli_fetch_array($articulos)){ 

                                            if ($cant['canti']!=null) {
                                                $objPHPExcel->setActiveSheetIndex(0)
                                                ->setCellValueByColumnAndRow($letra,$f,$cant['canti']);
                                                $arreglo[$a][$b]=$cant['canti'];
                                            } else {
                                             $objPHPExcel->setActiveSheetIndex(0)
                                             ->setCellValueByColumnAndRow($letra,$f,0);
                                             $arreglo[$a][$b]=0;
                                         }
                                     }
                                     $a++;

                                 }
                                 $let++;                           
                                 $mes++;

                                 $b++;
                             }
                             for($fila=0;$fila<$numeros;$fila++)
                             {
                                $suma=0;
                                for($columna=0;$columna<12;$columna++)
                                {
                                    $suma+=$arreglo[$fila][$columna];
                                }
                                $vertical[$fila]=$suma;
                            }
                            $cont=6;
                            for($j=0;$j<$numeros;$j++)
                            {
                                $objPHPExcel->getActiveSheet()->getStyle("O".$cont)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                                $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(14,$cont,$vertical[$j]);
                                $cont++;
                            }



                            $objPHPExcel->setActiveSheetIndex(0)->setTitle('Articulos x Dpto');

                            $objPHPExcel->setActiveSheetIndex(0);

// Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel.
                            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                            header('Content-Disposition: attachment;filename="REPORTE DE ENTRADAS GENERALES POR PROVEEDORES.xlsx"');
                            header('Cache-Control: max-age=0');
                            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                            $objWriter->save('php://output');




// Establecer la hoja activa, para que cuando se abra el documento se muestre primero.

                            ?> 
