<?php
require('fpdf/fpdf.php');

include("../bd/sql_variables.php");
$con= mysqli_connect("$sql_host", "$sql_usuario", "$sql_pass") or die(header ("No Disponible Servidor de Base de Datos"));
mysqli_select_db($con,"$sql_db");

$anno= $_GET['anno'];
$mes= $_GET['mes'];
$idj= $_GET['idj'];
$ids= $_GET['ids'];
$idtp= $_GET['idtp'];
//$ugid= $_GET['ugid'];

//DATOS DEL REPORTE
$arraymes = array();
$arraymes[] = array('idmes'=>'1','mes'=>'ENERO');
$arraymes[] = array('idmes'=>'2','mes'=>'FEBRERO');
$arraymes[] = array('idmes'=>'3','mes'=>'MARZO');
$arraymes[] = array('idmes'=>'4','mes'=>'ABRIL');
$arraymes[] = array('idmes'=>'5','mes'=>'MAYO');
$arraymes[] = array('idmes'=>'6','mes'=>'JUNIO');
$arraymes[] = array('idmes'=>'7','mes'=>'JULIO');
$arraymes[] = array('idmes'=>'8','mes'=>'AGOSTO');
$arraymes[] = array('idmes'=>'9','mes'=>'SETIEMBRE');
$arraymes[] = array('idmes'=>'10','mes'=>'OCTUBRE');
$arraymes[] = array('idmes'=>'11','mes'=>'NOVIEMBRE');
$arraymes[] = array('idmes'=>'12','mes'=>'DICIEMBRE');

//nombre de la jefatura
$sqljefatura = "select j_desc as nom from tb_jefatura where j_id='$idj'";
$resultjefatura = mysqli_query($con, $sqljefatura);
while ($rowj= mysqli_fetch_array($resultjefatura)){
$nomjefatura = $rowj['nom'];
}

//nombre de servicio
$sqlserv = "select s_desc as nom from tb_servicio where j_id='$idj' and s_id='$ids'";
$resultserv = mysqli_query($con, $sqlserv);
if(mysqli_num_rows($resultserv)>0){
while ($rows= mysqli_fetch_array($resultserv)){
$nomservicio = $rows['nom'];
}
}else{
$nomservicio = "";
}

//descripcion del tipo del personal
$sqltp = "select tp_desc as descp from tb_tipoperson where tp_id='$idtp'";
$resulttp = mysqli_query($con, $sqltp);
while ($rowtp= mysqli_fetch_array($resulttp)){
$descpersonal = $rowtp['descp'];
}

//////FUNCIONES DEL REPORTE
class PDF extends FPDF
{

function ImprimirTablaSinColorTitulo($data,$x, $y, $h, $w,$alig,$dibuja)
	{
    $this->SetFillColor(224,200,255);
    $this->SetTextColor(0);
    $this->SetDrawColor(128,0,0);
    $this->SetLineWidth(.3);
    $this->SetFont('','B');
		$this->SetXY($x,$y);
    //Datos
	$dib=false;
	        $this->Cell($w,$h,$data,$dibuja,0,$alig,$dib);
	}

function ImprimirTablaHorario($data,$x, $y, $h, $w,$alig)
{
    $this->SetFillColor(224,200,255);
    $this->SetTextColor(0);
    $this->SetDrawColor(128,0,0);
    $this->SetLineWidth(.3);
    $this->SetFont('','B');
	$this->SetXY($x,$y);
    //Datos
	$dib=false;
   		for($j=0;$j<count($w);$j++)
		{
        $this->Cell($w[$j],$h,$data[$j],1,0,$alig,$dib);
		//$dib = !$dib;
		$x = $x + $w[$j];
		$this->SetXY($x,$y);
        }
}

function ImprimirTablaPersonal($data,$x, $y, $h, $w,$alig)
{
    $this->SetFillColor(224,200,255);
    $this->SetTextColor(0);
    $this->SetDrawColor(128,0,0);
    $this->SetLineWidth(.3);
    $this->SetFont('','B');
	$this->SetXY($x,$y);
    //Datos
	$dib=false;
   		for($j=0;$j<count($w);$j++)
		{
        $this->Cell($w[$j],$h,$data[$j],1,0,$alig[$j],$dib);
		//$dib = !$dib;
		$x = $x + $w[$j];
		$this->SetXY($x,$y);
        }

}

function ImprimirTablaFechas($data,$x, $y, $h, $w,$alig)
{
    $this->SetFillColor(224,200,255);
    $this->SetTextColor(0);
    $this->SetDrawColor(128,0,0);
    $this->SetLineWidth(.3);
    $this->SetFont('','B');
		$this->SetXY($x,$y);
    //Datos
	$dib=false;
   		for($j=0;$j<count($w);$j++)
		{
        $this->MultiCell($w[$j],$h,$data[$j],1,$alig[$j],$dib);
		$x = $x + $w[$j];
		$this->SetXY($x,$y);
        }

}

}

$ini = 25;
$sumat = 255;
$alturaX = 25;
$ha = 4.5;
$texto = "REPROGRAMACION DE TURNOS DEL MES DE ".$arraymes[$mes-1]['mes']." DEL ".$anno;


$pdf=new PDF(); // Crea un objeto de la clase fpdf()
$pdf->AddPage(); // Agrega una hoja al documento.
$pdf->SetFont('Helvetica','B',10); //Establece la fuente a utilizar, el formato Negrita y el tama�o

//La siguiente instrucci�n escribe el siguiente mensaje.
$pdf->Image('../img/logoincor.jpg', 10, 10, 45, 16,'jpg');
$pdf->Image('../img/logoessalud.jpg', 160, 12, 40, 11,'jpg');

$pdf->ImprimirTablaSinColorTitulo($nomjefatura,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha;
$pdf->ImprimirTablaSinColorTitulo($nomservicio,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha;
$pdf->ImprimirTablaSinColorTitulo($descpersonal,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha*1.5;

$pdf->ImprimirTablaSinColorTitulo($texto,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha*1.5;
$pdf->SetFont('Arial','B',6.5);
						$subtitulo = array('APELLIDOS Y NOMBRES','FECHA PROGRAMADA','TURNO','FECHA REPROGRAMADA','TURNO','OBSERVACIONES');
						$wh= array($sumat*0.20,$sumat*0.12,$sumat*0.04,$sumat*0.12,$sumat*0.04,$sumat*0.22);
						$pdf->ImprimirTablaHorario($subtitulo,13,$ini,$ha*1.5,$wh,'C');
						$ini = $ini + $ha*1.5;

$sqlaskpr = "SELECT * FROM tb_horareprog".$anno." where tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mes' and hr_idserva='$ids' and j_id='$idj'";
$resultaskp = mysqli_query($con, $sqlaskpr);
if(mysqli_num_rows($resultaskp)!=0){

//pregunta por el personal
$sqlper1="select a.ug_id as cod, b.ut_codprog as codigo, CONCAT(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, ";
$sqlper1= $sqlper1." b.ut_idserva as idsa from tb_usutrab b  inner join tb_usuasist a on a.ug_id= b.ug_id where b.j_id='$idj'  ";
$sqlper1= $sqlper1." and b.tp_id='$idtp' and b.ut_idserva='$ids' and b.uap_anno='$anno' and b.uap_mes ='$mes' order by nombre";
//echo $sqlper1."<br>";
$resultper1 = mysqli_query($con, $sqlper1);

$guardiasarray = array();

while ($row= mysqli_fetch_array($resultper1)){

$codper=$row['cod'];
$codprog=$row['codigo'];
$nombre=$row['nombre'];

  if(strlen($nombre)>=35){
	$nombre = substr($nombre,0,35);
	}
//pregunta por turno prog
$sqlprog = "select a.ut_codprog as codprog, a.ug_id as ugid, a.h_id as idhora, a.a_id as area, a.ga_id as garea, a.rh_dia as dia, concat(a.rh_dia,'/', a.uap_mes,'/', a.uap_anno) as fechap, concat(c.h_horanum,'(',c.h_horaturn,')') as turnop ";
$sqlprog = $sqlprog." from tb_reprohrs a inner join tb_horario c on a.h_id=c.h_id where tp_id='$idtp' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mes' and a.rh_idserva='$ids' and a.j_id='$idj' and a.rh_tipo='1'";

//pregunta por turno reprog
$sqlrprog = "select a.ut_codprog as codprog, a.ug_id as ugid, a.h_id as idhora, a.a_id as area, a.ga_id as garea, a.rh_dia as dia, concat(a.rh_dia,'/', a.uap_mes,'/', a.uap_anno) as fechap, concat(c.h_horanum,'(',c.h_horaturn,')') as turnop ";
$sqlrprog = $sqlrprog." from tb_reprohrs a inner join tb_horario c on a.h_id=c.h_id where tp_id='$idtp' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mes' and a.rh_idserva='$ids' and a.j_id='$idj' and a.rh_tipo='2'";


$resulthp = mysqli_query($con, $sqlprog);
$resulthrp = mysqli_query($con, $sqlrprog);

////pregunta por la cantidad de reprogramaciones
//pregunta por turno prog
$sqlsprog = "select count(*) as cant from tb_reprohrs a inner join tb_horario c on a.h_id=c.h_id where tp_id='$idtp' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mes' and a.rh_idserva='$ids' and a.j_id='$idj' and a.rh_tipo='1'";

//pregunta por turno reprog
$sqlsrprog = "select count(*) as cant from tb_reprohrs a inner join tb_horario c on a.h_id=c.h_id where tp_id='$idtp' and a.ug_id='$codper' and a.uap_anno='$anno' and a.uap_mes='$mes' and a.rh_idserva='$ids' and a.j_id='$idj' and a.rh_tipo='2'";

$resultcant = mysqli_query($con, $sqlsprog);
$resultcantr = mysqli_query($con, $sqlsrprog);
$cantp=0;
$cantpr=0;
while ($rowc= mysqli_fetch_array($resultcant)){
$cantp=$rowc['cant'];
}
while ($rowcr= mysqli_fetch_array($resultcantr)){
$cantpr=$rowcr['cant'];
}
$nmax=0;
if($cantp>$cantpr){
$nmax=$cantp;
}else{
$nmax=$cantpr;
}



if((mysqli_num_rows($resulthp)!=0) || (mysqli_num_rows($resulthrp)!=0)){
// pregunta por servicio de origen

$sqlask ="SELECT ug_id as cod, s_id as ids FROM tb_usutrab where ut_codprog='$codprog' and  ug_id='$codper' and tp_id='$idtp' and uap_anno='$anno' and uap_mes='$mes' and j_id='$idj' and ut_idserva='$ids'";
//echo $sqlask."<br>";
$resultask = mysqli_query($con, $sqlask);
if(mysqli_num_rows($resultask)!=0){
while($rowask = mysqli_fetch_array($resultask)){
$idso = $rowask['ids'];
}}
//PERSONAL QUE NO TIENE REPROG
$sqlsaverepro ="select * from tb_reprohrs where uap_anno='$anno' and ug_id='$codper' and s_id='$idso' and uap_mes='$mes' and rh_idserva='$ids' and j_id='$idj' and tp_id='$idtp'";
$resultsaverepro = mysqli_query($con, $sqlsaverepro);
//echo $sqlsaverepro."<br>";
$cadenaprog="";
$cadenah="";
$cadenareprog="";
$cadenahr="";

if(mysqli_num_rows($resultsaverepro)!=0){
//PERSONAL REGISTRADO CON LAS REPROGRAMACIONES
$aa=0;
$bb=0;
//cuando ya esta registrado la reprogramacion
if(mysqli_num_rows($resulthp)!=0){
while ($rowhp= mysqli_fetch_array($resulthp)){
if($aa==0){
$cadenaprog = $rowhp['fechap'];
$cadenah = $rowhp['turnop'];
$aa = $aa + 1;
}else{
$cadenaprog = $cadenaprog."\n".$rowhp['fechap'];
$cadenah = $cadenah."\n".$rowhp['turnop'];
$aa = $aa + 1;
}
}}else{
$cadenaprog = "";
$cadenah = "";
}
if($aa < $nmax){
for ($i=$aa;$i<=$nmax;$i++){
if($i==$nmax){
$cadenaprog = $cadenaprog;
$cadenah = $cadenah;
}else{
//$cadenaprog = $cadenaprog."\n ";
//$cadenah = $cadenah."\n  ";
}
}
}
//turnos reprogramados

if(mysqli_num_rows($resulthrp)!=0){
while ($rowhrp= mysqli_fetch_array($resulthrp)){
if($bb==0){
$cadenareprog = $rowhrp['fechap'];
$cadenahr = $rowhrp['turnop'];
$bb = $bb +1;
}else{
$cadenareprog = $cadenareprog."\n".$rowhrp['fechap'];
$cadenahr = $cadenahr."\n".$rowhrp['turnop'];
$bb = $bb +1;
}
}}else{
$cadenareprog = "";
$cadenahr = "";
}

if($bb < $nmax){
for ($i=$bb;$i<=$nmax;$i++){
$cadenareprog = $cadenareprog."";
//$cadenareprog = $cadenareprog."\n ";
$cadenahr = $cadenahr."";
//$cadenahr = $cadenahr."\n ";
}
}


//imprime cambios
$sqlask2 ="select or_obs from tb_obsreprog where j_id='$idj' and ug_id='$codper' and s_id='$idso' and uap_anno='$anno' and uap_mes='$mes' and or_idserva='$ids'";
$resultask2 = mysqli_query($con, $sqlask2);
$obsv="";
if(mysqli_num_rows($resultask2)!=0){
while ($rowob= mysqli_fetch_array($resultask2)){
$obsv = $rowob['or_obs'];
}
}

$arraydatos = array($nombre);
$aline1 = array('L');
$wh1= array($sumat*0.20);
$arrayfechas1 = array($cadenaprog,$cadenah);
$aline21 = array('C','C');
$arrayfechas2 = array($cadenareprog,$cadenahr);
$aline22 = array('C','C');

$wh21 = array($sumat*0.12,$sumat*0.04);
$wh22 = array($sumat*0.12,$sumat*0.04);
$i=1;
$inia=0;


if($obsv!="" and strlen($obsv)>36){
//$nc = strlen($obsv)/$nmax;
$nc = 36;
$ncad= strlen($obsv);
while ($inia <= $ncad){
//$obsva =$obsva.(substr($obsv,$inia,$nc));
$inia = $inia + $nc;
$i=$i+1;
}
$i=$i-1;
}

$arrayobsv = array($obsv);
$aline3 = array('C');
$wh3 = array($sumat*0.22);

//$cantp
//$cantpr
/////////////////////////////
//if(($ini + 2*$ha)>240){
if(($ini + 2*$ha)>238){

///NUEVA HOJA
$ini = 25;
$sumat = 255;
$alturaX = 25;
$ha = 4.5;
$texto = "REPROGRAMACION DE TURNOS DEL MES DE ".$arraymes[$mes-1]['mes']." DEL ".$anno;


//$pdf=new PDF(); // Crea un objeto de la clase fpdf()
$pdf->AddPage(); // Agrega una hoja al documento.
$pdf->SetFont('Helvetica','B',10); //Establece la fuente a utilizar, el formato Negrita y el tama�o

//La siguiente instrucci�n escribe el siguiente mensaje.
$pdf->Image('../img/logoincor.jpg', 10, 10, 45, 16,'jpg');
$pdf->Image('../img/logoessalud.jpg', 160, 12, 40, 11,'jpg');

$pdf->ImprimirTablaSinColorTitulo($nomjefatura,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha;
$pdf->ImprimirTablaSinColorTitulo($nomservicio,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha;
$pdf->ImprimirTablaSinColorTitulo($descpersonal,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha*1.5;

$pdf->ImprimirTablaSinColorTitulo($texto,25,$ini,$ha,$sumat*0.65,'C',0);
$ini = $ini + $ha*1.5;
$pdf->SetFont('Arial','B',6.5);
						$subtitulo = array('APELLIDOS Y NOMBRES','FECHA PROGRAMADA','TURNO','FECHA REPROGRAMADA','TURNO','OBSERVACIONES');
						$wh= array($sumat*0.20,$sumat*0.12,$sumat*0.04,$sumat*0.12,$sumat*0.04,$sumat*0.22);
						$pdf->ImprimirTablaHorario($subtitulo,13,$ini,$ha*1.5,$wh,'C');
						$ini = $ini + $ha*1.5;


}

////////////////////////////
$pdf->ImprimirTablaPersonal($arraydatos,13, $ini, $ha*$nmax, $wh1,$aline1);
$movx = 13 + $sumat*0.20;
if($cantp==0){
$ha1 = $ha*$nmax;
}else{
$ha1 = ($ha*$nmax)/$cantp;
}
$pdf->ImprimirTablaFechas($arrayfechas1,$movx, $ini, $ha1, $wh21,$aline21);
$movx = $movx + $sumat*0.12 + $sumat*0.04;
if($cantpr==0){
$ha2 = $ha*$nmax;
}else{
$ha2 = ($ha*$nmax)/$cantpr;
}
$pdf->ImprimirTablaFechas($arrayfechas2,$movx, $ini, $ha2 ,$wh22 ,$aline22);
$movx = $movx + $sumat*0.12 + $sumat*0.04;

//$ha3 = ($ha*$nmax)/($i-1);
if($obsv==""){
$pdf->ImprimirTablaFechas($arrayobsv,$movx, $ini, $ha*$nmax, $wh3,$aline3);
}else{
$ha3 = ($nmax/$i)*$ha;
$pdf->ImprimirTablaFechas($arrayobsv,$movx, $ini, $ha3, $wh3,$aline3);
}
$ini = $ini + $ha*$nmax;

}
}//existencia de hrs reprogramadas

}//termina el listado
}

$pdf->Output(); //Env�a como salida del documento


?>
