<?php
include_once('../../../php/sessionStart.php');
include_once('../../../php/db/dbExec.php');
include_once('../../../php/functions.php');
include_once('../../../php/timezone.php');
include_once('../../../php/privileges.php');

$bitForm = "nom1010";
$bitMod = "nom";
$empCod = $_SESSION['empCod'];
$usuCod = utf8_decode($_SESSION['usuCod']);
$usuNom = utf8_decode($_SESSION['usuNom']);

switch ($_POST['action']) {
	case 'consultarProypla':
		echo consultarProypla();
		break;

	case 'generar':
		echo generar();
		break;

	case 'borrar':
		echo borrar();
		break;
}

function consultarProypla() {
	global $empCod;

	$func = new functions();

	$dtDel=$_POST['dtDel'];
	$dtAl=$_POST['dtAl'];
	$selGrupo=$_POST['selGrupo'];


	# QUERYS
	if ($selGrupo==99999) {
		$sMaestro = "SELECT *, mae.mae_cod, TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape,
				TRIM(pue.tpu_nom) AS tpu_nom
				FROM maestro mae LEFT JOIN tpuesto pue ON mae.mae_pue=pue.tpu_cod 
				WHERE mae_emp='$empCod' AND (mae_stat='A' or mae_stat='J') or (mae_emp='$empCod' AND mae_stat='B' and mae_fsal>='$dtDel') ORDER BY mae_emp,mae_gru,mae_dep,CAST(mae_cod AS UNSIGNED)";
	} else 	{
		$sMaestro = "SELECT *, mae.mae_cod, TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape,
				TRIM(pue.tpu_nom) AS tpu_nom
				FROM maestro mae LEFT JOIN tpuesto pue ON mae.mae_pue=pue.tpu_cod 
				WHERE mae_emp='$empCod' AND mae_gru='$selGrupo' AND (mae_stat='A' or mae_stat='J') or (mae_emp='$empCod' AND mae_gru='$selGrupo' AND mae_stat='B' and mae_fsal>='$dtDel') ORDER BY mae_emp,mae_gru,mae_dep,CAST(mae_cod AS UNSIGNED)";
	}		
	# EJECUCION DE QUERYS
	$db = new dbExec();
	$resp["maestro"] = $db->sFetchAll($sMaestro);
	$db->close();
	$func = new functions();
	return $func->jsonEncodeArray($resp, "maestro");
}

function generar() {
	global $empCod, $usuNom, $bitMod, $bitForm, $dtDel, $dtAl, $ano, $mes;

	$priv = new privileges();
	if (!$priv->modify($_SESSION['usuPriv'])) {
		return json_encode($priv->rErrorModify());
	}

	$func = new functions();

	$dtDel=$_POST['dtDel'];
	$dtAl=$_POST['dtAl'];
	$crAnti=$_POST['crAnti'];
	$numDiasb=$_POST['numDiasb'];
    $ano=date("Y", strtotime($dtAl));
    $mes=date("n", strtotime($dtAl));
    $diaal=date("d", strtotime($dtAl));
    $diadel=date("d", strtotime($dtDel));
	$jsonListaEmp=json_decode($_POST['tblListaEmp']);

	# QUERYS PARA UN REGISTRO
	$sCorrPla = "SELECT * FROM corrpla WHERE cp_emp='$empCod'";

	function iCorrPla($nuevocorr) { 
		global $empCod, $usuNom;
		return "INSERT INTO corrpla(cp_emp, cp_num) VALUES ('$empCod', $nuevocorr)";
	}
	function uCorrPla($nuevocorr) { 
		global $empCod, $usuNom;
		return "UPDATE corrpla SET cp_num=$nuevocorr WHERE cp_emp='$empCod'";
	}

	function sRegvacd($cod) { 
		global $empCod;
		return "SELECT * FROM regvacd WHERE rvd_emp='$empCod' AND rvd_cod='$cod'";
	}

	function sSuspen($cod) { 
		global $empCod;
		return "SELECT * FROM suspen WHERE sus_emp='$empCod' AND sus_cod='$cod'";
	}

	function sPrestamos($cod) { 
		global $empCod;
		return "SELECT * FROM pres_mae WHERE pm_emp='$empCod' AND pm_cod='$cod'";
	}

	function sDescuentos($cod) { 
		global $empCod;
		return "SELECT * FROM descuentos WHERE d_emp='$empCod' AND d_cod='$cod'";
	}

	function sMaestro($cod) { 
		global $empCod;
		return "SELECT *, mae.mae_cod, TRIM(mae.mae_nom) AS mae_nom, TRIM(mae.mae_ape) AS mae_ape,
			TRIM(pue.tpu_nom) AS tpu_nom
			FROM maestro mae LEFT JOIN tpuesto pue ON mae.mae_pue=pue.tpu_cod
			WHERE mae_emp='$empCod' AND mae_cod='$cod' ";
	}

	function iCtrpla($numpla) { 
		global $empCod, $usuNom, $dtDel, $dtAl;
		return "INSERT INTO ctrpla(ctrp_emp, ctrp_num, ctrp_fini, ctrp_ffin, ctrp_stat) VALUES ('$empCod', '$numpla', '$dtDel', '$dtAl', 'P')";
	}
	function iNomper($numpla,$cod,$grupo,$dep,$dtra,$dsusi,$dvaca,$snom,$sord,$boni,$bonii,$bonoe,$depre,$viati,$totdev,$igss,$isr,$pree,$preb,$anti,$unif,$lent,$desj,$otrd,$totdes,$liqrec,$boniinom,$bonoenom,$igsssn,$obs,$sueldodia, $bonidia) { 
		global $empCod, $usuNom,  $dtDel, $dtAl, $ano, $mes;
		return "INSERT INTO nomper(np_emp, np_numpla, np_fini, np_ffin, np_cod, np_gru, np_dep, np_dtra, np_dsusi, np_dvaca, np_snom, np_sord,np_boni, np_bonii, np_bonoe, np_depre, np_viati, np_totdev, np_igss, np_isr, np_pree, np_preb, np_anti, np_unif, np_lent, np_desj, np_otrd, np_totdes, np_liqrec, np_boniinom, np_bonoenom, np_mes, np_ano, np_igsssn, np_genero,np_ulcam, np_obs, np_snomdia, np_bonidia) VALUES ('$empCod', '$numpla', '$dtDel','$dtAl','$cod','$grupo','$dep','$dtra', $dsusi,'$dvaca','$snom','$sord','$boni','$bonii','$bonoe','$depre','$viati','$totdev','$igss','$isr','$pree','$preb','$anti','$unif','$lent','$desj','$otrd','$totdes','$liqrec','$boniinom','$bonoenom','$mes','$ano','$igsssn','$usuNom','$usuNom','$obs', '$sueldodia', '$bonidia')";
	}
	function iPres_mov($cod,$numpla,$monto,$predel,$preal,$tipopre,$nopres,$prestat) { 
		global $empCod, $usuNom, $dtDel, $dtAl, $ano, $mes;
		return "INSERT INTO pres_mov(pmo_emp,pmo_cod, pmo_npla, pmo_monto, pmo_plafini, pmo_plaffin, pmo_tipopre, pmo_noprest, pmo_desde, pmo_hasta, pmo_stat) VALUES ('$empCod','$cod','$numpla','$monto','$predel','$preal','$tipopre','$nopres','$dtDel','$dtAl','$prestat')";
	}
	function iAnti($cod,$numpla,$dtra,$dsusi,$dvaca,$vanti) { 
		global $empCod, $usuNom, $dtDel, $dtAl, $ano, $mes;
		return "INSERT INTO antipla(ant_emp, ant_cod, ant_numpla, ant_mes, ant_ano, ant_dtra, ant_dsusi, ant_dvaca, ant_sord, ant_liqrec) VALUES ('$empCod','$cod','$numpla','$mes','$ano','$dtra','$dsusi','$dvaca','$vanti','$vanti')";
	}

	# EJECUCION DE QUERYS 
	$db = new dbExec();
	$select["corrpla"] = $db->query($sCorrPla);
	$fetchAssoc["corrpla"] = $db->fetchAssoc($select["corrpla"]);
	if ($db->numRows($select["corrpla"]) == 0) {
	  $numpla=1;
	  $nuevocorr=$numpla+1;
	  $resp = $db->iEx(iCorrPla($nuevocorr));
	} else {
      $numpla=$fetchAssoc["corrpla"]["cp_num"];
	  $nuevocorr=$numpla+1;
	  $resp = $db->uEx(uCorrPla($nuevocorr));
	}
	$dias=$numDiasb;
	$diasant=15;
	$finiant1=$ano."-".$mes."-01";
	$ffinant1=$ano."-".$mes."-15";
	//$finiant = new DateTime($finiant1);
	//$ffinant = new DateTime($ffinant1);
	$finiant = date("Y-m-d", strtotime($finiant1));
	$ffinant = date("Y-m-d", strtotime($ffinant1));
	$paso="No paso";
	$paso2="No paso anti";
	foreach ($jsonListaEmp as $obj) {
		$cod = $obj->cod;
		$paso="Si paso";

		$select["maestro"] = $db->query(sMaestro($cod));
		$fetchAssoc["maestro"] = $db->fetchAssoc($select["maestro"]);
        $snom=$fetchAssoc["maestro"]["mae_sueldo"];
        $boni=$fetchAssoc["maestro"]["mae_bonil"];
        $boninom=$fetchAssoc["maestro"]["mae_bonil"];
        $boniinom=$fetchAssoc["maestro"]["mae_bonii"];
        $bonoenom=$fetchAssoc["maestro"]["mae_bonoe"];
        $deprenom=$fetchAssoc["maestro"]["mae_depre"];
        $viatinom=$fetchAssoc["maestro"]["mae_viati"];
//        $visr=$fetchAssoc["maestro"]["mae_visr"];
        $poranti=$fetchAssoc["maestro"]["mae_anti"];
        $igsssn=$fetchAssoc["maestro"]["mae_igsssn"];
        $fing=$fetchAssoc["maestro"]["mae_fing"];
        $fsal=$fetchAssoc["maestro"]["mae_fsal"];
        $stat=$fetchAssoc["maestro"]["mae_stat"];
        $grupo=$fetchAssoc["maestro"]["mae_gru"];
        $dep=$fetchAssoc["maestro"]["mae_dep"];
        $sueldodia=round(($snom/$numDiasb),6);
        $bonidia=round(($boni/$numDiasb),6);
        $boniidia=round(($boniinom/$numDiasb),6);
        $bonoedia=round(($bonoenom/$numDiasb),6);
        $depredia=round(($deprenom/$numDiasb),6);
        $viatidia=round(($viatinom/$numDiasb),6);
/*  Se bloqueo porque los dias vienen de lo que se determino en dias base para la planilla        
		if (($diadel==1 && $diaal==15) || $diadel>15) {
			$dias=15;
		}
		if (($diadel==1 && $diaal>27)) {
			$dias=30;
		}
*/
       	$horas=0;

	    if ($fing>$dtDel && $fing<=$dtAl) {
		    $dias = $func->dateDiff($fing,$dtAl);
			$dias = $dias->format('%a');    
		    $dias = $dias+1;
	    }
	    if ($stat=='B') {
	      if ($fsal>=$dtDel && $fsal<=$dtAl) {
		    $dias = $func->dateDiff($dtDel,$fsal);
			$dias = $dias->format('%a');    
		    $dias = $dias+1;
	      }
	    }
	    /// Dias Anticipo
	    if ($fing>$finiant && $fing<=$ffinant) {
		    $diasant = $func->dateDiff($fing,$ffinant);
			$diasant = $diasant->format('%a');    
		    $diasant = $diasant+1;
	    }
	    if ($stat=='B') {
	      if ($fsal>=$finiant && $fsal<=$ffinant) {
		    $diasant = $func->dateDiff($finiant,$fsal);
			$diasant = $diasant->format('%a');    
		    $diasant = $diasant+1;
	      }
	    }


	    // Para revisar Suspensiones
	    $dsusi=0;
	    $dsusiant=0;
	    $obssusi="";
		$select["suspen"] = $db->query(sSuspen($cod));
		while ($recsus = $db->fetchAssoc($select['suspen'])) {
		    $susdel = $recsus["sus_del"];
		    $susal = $recsus["sus_al"];
		    $dsus1=0;
		    $dsusant1=0;
		    if ($susdel<$dtDel && $susal<=$dtAl && $susal>=$dtDel) {
			    $dsus1 = $func->dateDiff($dtDel,$susal);
				$dsus1 = $dsus1->format('%a');    
			    $dsus1 = $dsus1+1;
		    }
		    if ($susal>$dtAl && $susdel<=$dtAl) {
			    $dsus1 = $func->dateDiff($dtDel,$dtAl);
				$dsus1 = $dsus1->format('%a');    
			    $dsus1 = $dsus1+1;
		    }
		    if ($susdel>=$dtDel && $susal<=$dtAl) {
			    $dsus1 = $func->dateDiff($susdel,$susal);
				$dsus1 = $dsus1->format('%a');    
			    $dsus1 = $dsus1+1;
		    }
		    if ($susdel<=$dtDel && $susal>=$dtAl) {
			    $dsus1 = $func->dateDiff($dtDel,$dtAl);
				$dsus1 = $dsus1->format('%a');    
			    $dsus1 = $dsus1+1;
		    }
		    $dsusi=$dsusi+$dsus1;

		    if ($crAnti=='S') {
				// Dias suspendido afecta anticipo
			    if ($susdel<$finiant && $susal<=$ffinant && $susal>=$finiant) {
				    $dsusant1 = $func->dateDiff($finiant,$susal);
					$dsusant1 = $dsusant1->format('%a');    
				    $dsusant1 = $dsusant1+1;
			    }
			    if ($susal>$ffinant && $susdel<=$ffinant) {
				    $dsusant1 = $func->dateDiff($finiant,$ffinant);
					$dsusant1 = $dsusant1->format('%a');    
				    $dsusant1 = $dsusant1+1;
			    }
			    if ($susdel>=$finiant && $susal<=$ffinant) {
				    $dsusant1 = $func->dateDiff($susdel,$susal);
					$dsusant1 = $dsusant1->format('%a');    
				    $dsusant1 = $dsusant1+1;
			    }
			    if ($susdel<=$finiant && $susal>=$ffinant) {
				    $dsusant1 = $func->dateDiff($finiant,$ffinant);
					$dsusant1 = $dsusant1->format('%a');    
				    $dsusant1 = $dsusant1+1;
			    }
			    $dsusiant=$dsusiant+$dsusant1;
			}
		}
		if ($dsusi>=$dias) {
			$dias=0;
		} else {
			$dias=$dias-$dsusi;
		}
		if ($dsusi>0) {
			$obssusi="Suspendido del ".$susdel." al ".$susal;
		}
		// Actualiza dias de anticipo
		if ($dsusiant>=$diasant) {
			$diasant=0;
		} else {
			$diasant=$diasant-$dsusiant;
		}

		// Para revisar Vacaciones
	    $dvaca=0;
	    $dvacaant=0;
	    $obsvaca="";
		$select["regvacd"] = $db->query(sRegvacd($cod));
		while ($recvac = $db->fetchAssoc($select['regvacd'])) {
		    $vacdel = $recvac["rvd_fdesi"];
		    $vacal = $recvac["rvd_fdesf"];
		    $dvac1=0;
		    $dvacant1=0;
		    if ($vacdel<$dtDel && $vacal<=$dtAl && $vacal>=$dtDel) {
			    $dvac1 = $func->dateDiff($dtDel,$vacal);
				$dvac1 = $dvac1->format('%a');    
			    $dvac1 = $dvac1+1;
		    }
		    if ($vacal>$dtAl && $vacdel<=$dtAl) {
			    $dvac1 = $func->dateDiff($dtDel,$dtAl);
				$dvac1 = $dvac1->format('%a');    
			    $dvac1 = $dvac1+1;
		    }
		    if ($vacdel>=$dtDel && $vacal<=$dtAl) {
			    $dvac1 = $func->dateDiff($vacdel,$vacal);
				$dvac1 = $dvac1->format('%a');    
			    $dvac1 = $dvac1+1;
		    }
		    if ($vacdel<=$dtDel && $vacal>=$dtAl) {
			    $dvac1 = $func->dateDiff($dtDel,$dtAl);
				$dvac1 = $dvac1->format('%a');    
			    $dvac1 = $dvac1+1;
		    }
		    $dvaca=$dvaca+$dvac1;

		    if ($crAnti=='S') {
			    if ($vacdel<$finiant && $vacal<=$ffinant && $vacal>=$finiant) {
				    $dvacant1 = $func->dateDiff($finiant,$vacal);
					$dvacant1 = $dvacant1->format('%a');    
				    $dvacant1 = $dvacant1+1;
			    }
			    if ($vacal>$ffinant && $vacdel<=$ffinant) {
				    $dvacant1 = $func->dateDiff($finiant,$ffinant);
					$dvacant1 = $dvacant1->format('%a');    
				    $dvacant1 = $dvacant1+1;
			    }
			    if ($vacdel>=$finiant && $vacal<=$ffinant) {
				    $dvacant1 = $func->dateDiff($vacdel,$vacal);
					$dvacant1 = $dvacant1->format('%a');    
				    $dvacant1 = $dvacant1+1;
			    }
			    if ($vacdel<=$finiant && $vacal>=$ffinant) {
				    $dvacant1 = $func->dateDiff($finiant,$ffinant);
					$dvacant1 = $dvacant1->format('%a');    
				    $dvacant1 = $dvacant1+1;
			    }
			    $dvacaant=$dvacaant+$dvacant1;
			}

		}
		if ($dvaca>=$dias) {
			$dias=0;
		} else {
			$dias=$dias-$dvaca;
		}
		if ($dvaca>0) {
			$obsvaca="Vacaciones del ".$vacdel." al ".$vacal;
		}

		// Actualiza dias de anticipos segun fecha de vacaciones
		if ($dvacaant>=$diasant) {
			$diasant=0;
		} else {
			$diasant=$diasant-$dvacaant;
		}

		// Para revisar prestamos
	    $pree=0;
	    $preb=0;
	    $prestat='P';
		$select["pres_mae"] = $db->query(sPrestamos($cod));
		while ($recpre = $db->fetchAssoc($select['pres_mae'])) {
		    $predel = $recpre["pm_desde"];
		    $preal = $recpre["pm_hasta"];
		    $tipopre = $recpre["pm_tipopre"];
		    $cuotam = $recpre["pm_cuotam"];
		    $saldo = $recpre["pm_saldo"];
		    $noprest = $recpre["pm_noprest"];
		    if ($saldo<0) {
		    	continue;
		    }	
		    if (($dtDel<$predel || $dtDel>$preal) && ($dtAl<$predel || $dtAl>$preal)) {
		    	continue;
		    }	
		    if ($tipopre == 'B') {
			    $pree = $pree+$cuotam;
		    } else {
			    $preb = $preb+$cuotam;

		    }
		    $db->iEx(iPres_mov($cod,$numpla,$cuotam,$predel,$preal,$tipopre,$noprest,$prestat));
		}
		// Para revisar prestamos
	    $isr=0;
	    $unif=0;
	    $lent=0;
	    $desj=0;
	    $descv=0;
		$select["descuentos"] = $db->query(sDescuentos($cod));
		while ($recdesc = $db->fetchAssoc($select['descuentos'])) {
		    $descdel = $recdesc["d_feci"];
		    $descal = $recdesc["d_fecf"];
		    $codd = $recdesc["d_codd"];
		    $monto = $recdesc["d_monto"];
		    if ($monto<=0) {
		    	continue;
		    }	
		    if (($dtDel<$descdel || $dtDel>$descal) && ($dtAl<$descdel || $dtAl>$descal)) {
		    	continue;
		    }	
		    if ($codd == 2) {
			    $isr = $isr+$monto;
		    }
		    if ($codd == 3) {
			    $desj = $desj+$monto;
		    }
		    if ($codd == 10) {
			    $unif = $unif+$monto;
		    }
		    if ($codd == 19) {
			    $lent = $lent+$monto;
		    }
		    if ($codd!=2 && $codd!=3 && $codd!=10 && $codd!=19) {
			    $descv = $descv+$monto;
		    }
		}

		if ($dias==$numDiasb) {
			$sord=$snom;
			$boni=$boni;
			$bonii=$boniinom;
			$bonoe=$bonoenom;
			$depre=$deprenom;
			$viati=$viatinom;
		} else {
			if ($dias==15) {
				$sord=round($snom/2,2);
				$boni=round($boni/2,2);
				$bonii=round($boniinom/2,2);
				$bonoe=round($bonoenom/2,2);
				$depre=round($deprenom/2,2);
				$viati=round($viatinom/2,2);
			} else {
				$sord=round($dias*$sueldodia,2);
				$boni=round($dias*$bonidia,2);
				$bonii=round($dias*$boniidia,2);
				$bonoe=round($dias*$bonoedia,2);
				$depre=round($dias*$depredia,2);
				$viati=round($dias*$viatidia,2);
			}
		}

		$anti=0;
		if ($crAnti=='S') {
//			if ($diasant==15) {
//				$anti=round(($snom+$boninom)/2,2);
//			} else {
				$santi=round(($snom*$poranti/100),2);
				$boniant=0;
				$anti=$santi+$boniant;
//			}
			$paso2="Si paso anti";
			$db->iEx(iAnti($cod,$numpla,$diasant,$dsusiant,$dvacaant,$anti));
		}

		$totdev=($sord+$boni+$bonii+$bonoe+$depre+$viati);
		if ($igsssn=='S') {
			$igss=round($sord*$_SESSION['pIgsse']/100,2);
			if ($stat=="J") {
  			  $igss=round($sord*3/100,2);
			}
		} else {
			$igss=0;	
		}
		$totdes=($igss+$isr+$anti+$preb+$pree+$unif+$lent+$desj+$descv);
		$liqrec=$totdev-$totdes;
		$obs=$obssusi.$obsvaca;

		$resp = $db->iEx(iNomper($numpla,$cod,$grupo,$dep,$dias,$dsusi,$dvaca,$snom,$sord,$boni,$bonii,$bonoe,$depre,$viati,$totdev,$igss,$isr,$pree,$preb,$anti,$unif,$lent,$desj,$descv,$totdes,$liqrec,$boniinom,$bonoenom,$igsssn,$obs,$sueldodia,$bonidia));

		$db->iEx(iCtrpla($numpla));
	}

	$db->iEx($func->iBitacora(utf8_decode("Se creó Planilla:  $numpla Del:  $dtDel   al  $dtAl Usuario: ").$usuNom, $bitMod, $bitForm));

	$resp["numpla"] = $numpla;
	$resp["paso"] = $paso;
	$resp["paso2"] = $paso2;
	$db->close();

	return json_encode($resp);
}

?>