<?php
define ('FPDF_FONTPATH','/var/www/html/archivo/fpdf153/font/');
require('/var/www/html/archivo/fpdf153/fpdf.php');

class PDF extends FPDF
{
//Cabecera de pagina
function Header()
{
       //$area=$_POST['narea'];
       $secc=$_POST['sseccion'];
       $serie=$_POST['serie'];
       $nano1=$_POST['nano'];
       $nano=$nano1+1;
       $fecha1=$_POST['fecha'];
       $area='1.1';

       //$fecha1=date("d-m");
       $fecha="$fecha1"."-"."$nano";
       pg_connect("dbname='inv_expedientes' port='5432' user='telerin' password='aplicaciones'") or die ("Error en la conexion");


 $querybhomol="select homologa from cat_series1 where homologa=$serie and cve_cat like'%$nano1'";
                  $datohom=pg_query($querybhomol);
       $homologas=pg_fetch_result($datohom,0); 
 
       $query="SELECT nom_area,titular_area,nom_direccion FROM cat_area,cat_direccion WHERE 
       cat_area.cve_direccion=cat_direccion.cve_direccion and cat_area.cve_area='$area' AND STATUS='B'";
       $narea=pg_query($query);
       $nombarea=pg_fetch_result($narea,0);
       $titular=pg_fetch_result($narea,1); 
       $direccion=pg_fetch_result($narea,2);
   

       $querynsecc="SELECT cve_seccion,nom_seccion FROM cat_seccion WHERE cve_seccion='$secc'";
       $nsecc=pg_query($querynsecc);
       $cvesc=Strtoupper(pg_fetch_result($nsecc,0));
       $nsec=Strtoupper(pg_fetch_result($nsecc,1)); 
       $concatnsecc1="$cvesc".": "."$nsec";   

       $search = array("Á", "É", "Í", "Ó", "Ú", "á", "é", "í", "ó", "ú", "Ñ", "ñ");
       $replace = array("Á", "É", "Í", "Ó", "Ú", "Á", "É", "Í", "Ó", "Ú", "Ñ", "Ñ");


       $concatnsecc=str_replace($search, $replace, $concatnsecc1); 
  

       $querybcser="SELECT distinct(cve_serie) AS cserie,TIPO_SERIE,SERIES FROM cat_series1 WHERE cat_series1.homologa=$homologas and cat_series1.cve_serie='2C.5'";
       $datocser=pg_query($querybcser);
       $cveserie=pg_fetch_result($datocser,0);
       $tipo=pg_fetch_result($datocser,1);
       $tiposer=pg_fetch_result($datocser,2);
       

       if($tipo=='SUB'){
          $qbcseri="SELECT nom_serie,cve_serie from cat_series1 WHERE cat_series1.cve_serie ='$tiposer' and  CVE_CAT LIKE'%$nano1' ";
          $dcser=pg_query($qbcseri);
          $nsers=pg_fetch_result($dcser,0);
          $cvesers=pg_fetch_result($dcser,1);
          $concatserser1=Strtoupper("$cvesers"." "."$nsers");

          $search = array("Á", "É", "Í", "Ó", "Ú", "á", "é", "í", "ó", "ú", "Ñ", "ñ");
          $replace = array("Á", "É", "Í", "Ó", "Ú", "Á", "É", "Í", "Ó", "Ú", "Ñ", "Ñ");
          $concatserser=str_replace($search, $replace, $concatserser1);
       }


       $querybser="SELECT distinct(datos_generales.cve_serie) AS cserie,nom_serie FROM cat_series1,datos_generales
       WHERE cat_series1.homologa=$homologas  and cat_series1.cve_serie=datos_generales.cve_serie  and cve_area='1.2' ";
    
       $datoser=pg_query($querybser);
       $filabser=pg_num_rows($datoser);

      // for($bs=0;$bs<$filabser;$bs++){
          //$cveserie=pg_fetch_result($datoser,$bs,0);
          $nomserie=pg_fetch_result($datoser,1); 
          $concatn="$cveserie".""."$concatn";
       // }
        
        $codigo="DGBEH".""."$area"."*"."$concatn"; 
        $concatn11=Strtoupper("$concatn"." "."$nomserie");    

        $search = array("Á", "É", "Í", "Ó", "Ú", "á", "é", "í", "ó", "ú", "Ñ", "ñ");
        $replace = array("Á", "É", "Í", "Ó", "Ú", "Á", "É", "Í", "Ó", "Ú", "Ñ", "Ñ");


       $concatn1=str_replace($search, $replace, $concatn11); 
  
        $this->Image('imagenes/hgo.jpg',23,4,25);
        $this->Image('imagenes/bachillerato.jpg',300,6,55);
	//Arial bold 15
	$this->SetFont('Arial','B',11);
	//Movernos a la derecha
	//Título
        

        $this->SetXY(285,20);
        $this->Cell(285,20,'TIPO DE INVENTARIO: TRÁMITE ',0,1,'L');

        $this->SetXY(285,23);
        $this->Cell(285,23,'FECHA DE EMISIÓN: ',0,1,'');
        $this->SetXY(324,23);

        $this->Cell(324,23,$fecha1,0,1,'');

        $this->SetXY(257,26);
	 $this->Cell(0,26,'HOJA '.$this->PageNo().' DE {nb}',0,0,'C');
        $pagina=$this->PageNo();


        $this->SetXY(285,29);
        $this->Cell(285,29,'CÓDIGO:',0,1,'');

        $this->SetXY(303,42);
        $this->MultiCell(45,3.49,$codigo,0,'','j');
       
        $this->SetFillColor(200,200,200);
        $this->Rect(23,60,55,8.24,'FD');
        $this->SetXY(33,43);
        $this->Cell(33,43,'UBICACIÓN FÍSICA',0,'C');

        $this->SetFillColor(200,200,200);
        $this->Rect(78,60,55,8.24,'FD');
        $this->SetXY(70,43);
        $this->Cell(70,43,'UBICACIÓN TOPOGRÁFICA',0,1,'C');

        $this->SetFillColor(200,200,200);
        $this->Rect(133,60,67,8.24,'FD');
              $this->SetXY(112,41.5);
        $this->Cell(115,41.5,'2C.1-2008 y 2009',0,1,'C');
        $this->SetXY(112,44);
        $this->Cell(115,44,'NO. EXPEDIENTE',0,1,'C');

        $this->SetFillColor(200,200,200);
        $this->Rect(200,60,62,8.24,'FD');
        $this->SetXY(154,43);
        $this->Cell(154,43,'DESCRIPCIÓN DEL EXPEDIENTE',0,1,'C');

        $this->SetFillColor(200,200,200);
        $this->Rect(262,60,44,4,'FD');
        $this->SetXY(190,41.5);
        $this->Cell(190,41.5,'FECHAS EXTREMAS',0,1,'C');
        $this->SetFillColor(200,200,200);
        $this->Rect(262,64,22,4.24,'FD');
        $this->SetXY(183,44);
        $this->Cell(183,44,'INICIO',0,1,'C');

        $this->SetFillColor(200,200,200);
        $this->Rect(284,64,22,4.24,'FD');
        $this->SetXY(196,44);
        $this->Cell(196,44,'FINAL',0,1,'C');
        
        $this->SetFillColor(200,200,200);
        $this->Rect(305,60,49,8.24,'FD');
        $this->SetXY(220,43);
        $this->Cell(220,43,'OBSERVACIONES',0,1,'C');
   
        $this->SetFont('Arial','B',13);
        $this->SetXY(150,15);
	 $this->Cell(18,15,'INVENTARIO DOCUMENTAL',0,1,'B');
        $this->SetFont('Arial','B',11);
        $this->SetXY(23,20);
	 $this->Cell(23,20,'FONDO:',0,1,'');
    

        $this->SetXY(23,23);
	 $this->Cell(23,23,'UNIDAD ADMINISTRATIVA:',0,1,'B');
        $this->SetXY(23,26);
	 $this->Cell(23,26,'ÁREA GENERADORA:',0,1,'B');
        $this->SetXY(23,29);
	 $this->Cell(23,29,'NOMBRE DEL RESPONSABLE:',0,1,'B');
        $this->SetXY(23,32);
	 $this->Cell(23,32,'SECCIÓN: ',0,1,'B');
        

        $this->SetXY(23,35);
	 $this->Cell(23,35,'SERIE:',0,1,'B');
          
        $this->SetXY(23,38);
	 $this->Cell(23,38,'SUBSERIE:',0,1,'B');


        $this->SetFont('Arial','B',11);
        $this->SetXY(39,20);
	 $this->Cell(39,20,'BACHILLERATO DEL ESTADO DE HIDALGO',0,1,'b');
        $this->SetXY(75,23);
	 $this->Cell(75,23,$direccion,0,1,'');
        $this->SetXY(69,26);
	 $this->Cell(69,26,'SUBDIRECCIÓN DE NORMTAIVIDAD',0,1,'B');
        $this->SetXY(82,29);
	 $this->Cell(82,29,'LIC. JUAN ANTONIO TER VEEN BECERRA',0,1,'B');
        $this->SetXY(42.5,32);
	 $this->Cell(42.5,32,$concatnsecc,0,1,'B');
        if($tipo!='SUB'){
           $this->SetXY(37,50.5);
	    $this->MultiCell(220,3.8, $concatn1,0,'1','J');
        

        }else{  
           $this->SetXY(37,50.5);
	    $this->MultiCell(220,3.8,$concatserser,0,'1','J');
           $this->SetXY(44.5,55.1); 
	    $this->MultiCell(220,3.8, $concatn1,0,'1','J');
        

        }


	//Salto de línea
	$this->Ln(0);
}
//Pie de página

function Footer()
{
       //BUSCA DATOS DE QUIEN FIRMA INVENTARIO EN TRÁMITE
       $area=$_POST['narea'];
      
       
       $fecha=date("d-m-Y");
       pg_connect("dbname='inv_expedientes' port='5432' user='telerin' password='aplicaciones'") or die ("Error en la conexion"); 
       $queryres="SELECT titular_area,nom_responsable FROM cat_area WHERE  cve_area='1.2' AND status='B'";
       $datores=pg_query($queryres);
       $titulara=pg_fetch_result($datores,0);
       $responsable=pg_fetch_result($datores,1); 


       $queryenlace="SELECT nom_responsable FROM cat_area WHERE  cve_area='1.2' AND status='B'";
       $datoenlace=pg_query($queryenlace);
       $nenlace=pg_fetch_result($datoenlace,0);
       

        $this->SetFont('Arial','B',11);
        $this->SetXY(5,260);
        $this->Line(15,200,90,200);
        $this->SetXY(15,180);
        $this->Cell(70,10,'ELABORÓ',2,1,'C');
        $this->SetXY(35,197);
        $this->Cell(35,10,$responsable,2,1,'C');
        $this->SetXY(35,201);
        $this->Cell(35,10,'UNIDAD DE ARCHIVO EN TRÁMITE',2,1,'C');
      

        $this->SetXY(207,260);
        $this->Line(230,200,150,200);
        $this->SetXY(150,180);
        $this->Cell(80,10,'AUTORIZÓ',0,0,'C');
        $this->SetXY(127,197);
        
       
        $this->Cell(127,10,'L.I. CECILIA GAYOSSO ESPAÑA',2,1,'C');
        $this->SetXY(127,201);
        $this->Cell(127,10,'ENLACE NORMATIVO',2,1,'C');

        $this->SetXY(200,260);
        $this->Line(270,200,354,200);
        $this->SetXY(280,180);
        $this->Cell(70,10,'VALIDÓ',0,0,'C');
        $this->SetXY(193,197);
        $this->Cell(240,10,'ÓRGANO RECTOR',2,1,'C');
 
      }
}

//Creación del objeto de la clase heredada
$pdf=new PDF('L','mm','Legal');
$pdf->AliasNbPages();
$pdf->AddPage();
$pdf->SetFont('Arial','',11);
$area=$_POST['narea'];
$secc=$_POST['sseccion'];
$serie=$_POST['serie'];
$ano=$_POST['nano'];
$anotr=$ano+1;
$ubica='Circuito Ex-Hacienda la Concepción, Lte. 17, San Juan Tilcuautla, Mpio. de San Agustín Tlaxiaca';


pg_connect("dbname='inv_expedientes' port='5432' user='telerin' password='aplicaciones'") or die ("Error en la conexion"); 

 $querybhomol="select homologa,at from cat_series1 where homologa=6 and cve_cat like'%$ano'";
 $datohom=pg_query($querybhomol);
 $homologas=pg_fetch_result($datohom,0); 

if ( $homologas==6){  //SERIE CORRESPONDIENTE A EXPEDIENTES DE PERSONAL

$c=0;
             
          
         $querybdts="select lugar1||' '||no_caja AS ubicatop,codigo,asunto,fecha_inicio,fecha_final,observacion,substring(fecha_inicio,7,4) as anoi,substring(fecha_final,7,4) as anof,ano,cve_serie,tiempo_guarda,domicilio,descripcion_ubic,lugar from datos_generales,ubicacion
         where cve_area='1.2' and homologa=6 and ((fecha_final like'%2012') or (fecha_final='.') or (fecha_final isnull) or (fecha_final='__/__/____') or (fecha_final='') Or (tiempo_guarda='AT') OR (substring(fecha_final,7,4)>'2017')) and ano<='2012' AND 
         ubicacion.cve_ubicacion=datos_generales.ubicacion order by ano,codigo"; 

//echo"$querybdts";
         $datoat=pg_query($querybdts);
         $filadat=pg_num_rows($datoat);

          if($filadat!=0){  //IF  1
             
            for($i=0;$i< $filadat;$i++){  
              
              // $cuent=$cuent+1;
               //$hresta=$sumtotal-$cuent;  


              $codigo=$codigo1;
              $ubicatp=pg_fetch_result($datoat,$i,0); 
              $codigor=pg_fetch_result($datoat,$i,1);
              $asunto=pg_fetch_result($datoat,$i,2);    
              $fechai=pg_fetch_result($datoat,$i,3);
              $fechaf=pg_fetch_result($datoat,$i,4);
              $observac=pg_fetch_result($datoat,$i,5);
              $anoin=pg_fetch_result($datoat,$i,6);
              $anofi=pg_fetch_result($datoat,$i,7);                 
              $anoexp=pg_fetch_result($datoat,$i,8);
              $cveser=pg_fetch_result($datoat,$i,9);
              $domicilio=pg_fetch_result($datoat,$i,11);
              $descubica=pg_fetch_result($datoat,$i,12);
              $luga=pg_fetch_result($datoat,$i,13);

IF($anoin<=$ano){
//       if($anoexp<=$ano  and ($anofi>=$ano or $anofi==null)){

              $anostramite=$anoac-$anoexp;
      
              $cuent1=$cuent1+1;
              $numeroval=500;              

             $hresta=$numeroval-$cuent1;  
   
              $querytramite="select distinct(at) as archtramite from cat_series1 where homologa=$homologas ";
              $datotramite=pg_query($querytramite);
            
              $anotramite=pg_fetch_result($datotramite,0);
              if($anostramite<=$anoexp){ //IF 2
                $tot=$tot+1;
              }  //FIN DE IF 2
           
              $pdf->SetFont('Arial','',11);
                           $pdf->SetXY(23,($c+3.5)*20);
                           $pdf->MultiCell(55,3.49,$domicilio,0,'J');
                           $pdf->Rect(23,($c+3.4)*20,55,20,'');
            
                           $pdf->SetXY(78,($c+3.5)*20);
                           $pdf->MultiCell(50,3.49,"$descubica"." "."en"." "." "."$luga"." "."$ubicatp",0,'J');
                           $pdf->Rect(78,($c+3.4)*20,55,20,'');
                                              
     
                           $pdf->SetXY(133,($c+3.5)*21.5);
                           $pdf->MultiCell(67,3.49,$codigor,0,'J');
                           $pdf->Rect(133,($c+3.4)*20,67,20,'');                

                           $pdf->SetXY(200,($c+3.5)*20);
                           $pdf->MultiCell(62,3.49,$asunto,0,'J');    
                           $pdf->Rect(200,($c+3.4)*20,62,20,'');

              if($fechai!=''){ //IF 3
                           //$pdf->Rect(200,($c+3.5)*20,62,20,'');
                           $pdf->SetXY(262,($c+3.4)*22);
                           $pdf->MultiCell(22,3.49,$fechai,0,'C'); 
                           $pdf->Rect(262,($c+3.4)*20,22,20,'');
              }else{
                           $pdf->Rect(200,($c+3.5)*20,62,20,'');
                           $pdf->SetXY(262,($c+3.4)*22);
                           $pdf->MultiCell(22,3.49,$ano,0,'C'); 
                           $pdf->Rect(262,($c+3.4)*20,22,20,'');
              } // FIN DE IF 3

              if($fechaf=='.' or $fechaf==null or $fechaf=='__/__/____'){ //IF 4
                           $pdf->SetXY(284,($c+3.4)*22);
                           $pdf->MultiCell(21.5,3.49,$ano,0,'C');
                           $pdf->Rect(284,($c+3.4)*20,21,20,'');
                
                
              }else{
                           $pdf->SetXY(284,($c+3.4)*22);
                           $pdf->MultiCell(21.5,3.49,$fechaf,0,'C');
                           $pdf->Rect(284,($c+3.4)*20,21,20,'');
                
                
              } //FIN DE IF 4

        //   $pdf->SetFont('Arial','',7.5);

              $pdf->SetXY(305,($c+3.5)*20);
              $pdf->MultiCell(50,3.49,$observac,0,'J');
              $pdf->Rect(305,($c+3.4)*20,49,20,'');  
          

               if($bar >=5){ //IF 5
                 $resto = $bar % 5;     
                 $resf=$bar-1;
                 $indice++;
              }   // FIN DE IF 5    
       $c++;

              IF($resto==0){ //IF 6
                            
                if($hresta>=5){ // IF 7
              
                   if ($c>=5){ // IF 8
	               $c=0;
                      $pdf->AddPage();
                   } // FIN DE IF 8
               }
                if($hresta==5){ 
                   if ($c>=5){ // IF 9
	               $c=0;   
                      $pdf->AddPage();
                   } // FIN DE IF 9
                }
                             
       
              }else{
          
               if ($c>=5){// IF 10
	            $c=0;
                   $pdf->AddPage();
                }// FIN DE IF 10 

              }// FIN DE IF 6

           } //FIN DE IF 1 


     }
 
}

}
        $querybtot="select count(codigo) as total from datos_generales where   datos_generales.cve_area='1.2'  AND homologa=21 
              AND  ((fecha_final like'%2011') or (fecha_final='.') or (fecha_final isnull) or (fecha_final='__/__/____') or (fecha_final='') Or (tiempo_guarda='AT') OR (substring(fecha_final,7,4)>2017)) and ano<=2012 "; 
         
         $datotot=pg_query($querybtot);
         $dtotal=pg_fetch_result($datotot,0,0);


          $pdf->SetFont('Arial','B',11);      
          $pdf->SetFillColor(200,200,200);
          $pdf->Rect(78,($c+2.73)*25,67,10,'FD');
          $pdf->SetXY(100,($c+2.7)*26);
          $pdf->MultiCell(40,3.49,'TOTAL',0,'','j');
         
  
          $pdf->SetFillColor(200,200,200);
          $pdf->Rect(133,($c+2.73)*25,67,10,'FD');
          $pdf->SetXY(160,($c+2.7)*26);
          $pdf->MultiCell(60,3.49,$cuent1,'','j');
    
$pdf->Output();
?>