<?PHP
/****************************************************************************************
SMI (Services Maintenance Interventions) est une gestion libre de maintenance et de SAV

Version 0.9.6
Copyright (C) 2006  Sylvain FATOME

This program is free software; you can redistribute it and/or modify it under the terms
of the GNU General Public License as published by the Free Software Foundation; 
either version 2 of the License, or (at your option) any later version.

This program 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 General Public License for more details.

You should have received a copy of the GNU General Public License along with this program
(in the directory docs); 
if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 
Boston, MA  02110-1301, USA.

Contact : galawa@free.fr
****************************************************************************************/

/*****************************************/
/* Diverses fonctions utilisées dans SMI */
/*****************************************/

/********************************************************/
/* Stockage et récupérations diverses dans les fichiers */
/********************************************************/

// Affichage de l'entête de société sur les écrans d'affichage

function Affsiege($urll,$urli,$code_session,$niv_session,$code_age)
{
	$cge_cree=TRUE;

	// Récupération des informations centre de gestion

	$conn = mysql_connect(DBHOST, DBUSER, DBPASS);
	mysql_select_db(DBNAME);
	$rech = "SELECT * FROM " .TBCG;
	$rs = mysql_query($rech)or die(mysql_error());
	if (mysql_num_rows($rs) == 1) // Uniquement UN SEUL enregistrement !
	{
		 while ($row = mysql_fetch_array($rs)) 
		{
			extract ($row);
		}
	}
	else
	{
		$cge_cree=FALSE;
	}
	mysql_free_result($rs);
	mysql_close($conn);

	$urlcache = $urli."/cache.gif"; 

	echo "<table class=\"Ttable\" width=\"100%\" height=\"152\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
	echo "<tr>";
	echo "<td valign=\"top\">";
		$urllog = $urll."/logcge.gif"; 
		echo "<a href=\"index.php\"><img src=\"".$urllog."\"  title=\"RETOUR A L'INDEX\" alt=\"Retour à l'index\" width=\"196\" height=\"36\"border=\"0\" align=\"absmiddle\"></a><br>";
		if ($cge_cree==TRUE)
		{
			echo $cge_adr1."<br>";
			$pasadr2 = FALSE;
			if ($cge_adr2 <>"")
				echo $cge_adr2."<br>";
			else
				$pasadr2 = TRUE;
			echo Retournecp($cge_ville)." ".Retourneville($cge_ville)."<br>";
			echo "<br>";
			if ($pasadr2 == TRUE)
				echo "<br>";
			$urlico = $urli."/telc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo " ".$cge_telf;
			echo "<span class=\"annot_petit_it\">";
			echo " ".$cge_txttelf."<br>";
			echo "</span>";
			$urlico = $urli."/faxc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo " ".$cge_fax;
			echo "<span class=\"annot_petit_it\">";
			echo " ".$cge_txtfax."<br>";
			echo "</span>";
			echo "<span class=\"annot_petit\">";
			$urlico = $urli."/webc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo " ".$cge_web;
			echo "<img src=\"".$urlcache."\" align=\"absmiddle\">";
			$urlico = $urli."/atc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo " ".$cge_email."<br>";
			echo "</span>";
			echo "<span class=\"annot_petit_it\">";
			echo $cge_rcs."<br>";
			echo "</span>";
		}
		else
		{
			echo "<br>";
			echo "<br>";
			echo "<br>";
			echo "<br>";
			echo "<br>";
			$urlico = $urli."/telc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo "<br>";
			$urlico = $urli."/faxc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo "<br>";
			$urlico = $urli."/webc.png"; 
			echo "<img src=\"".$urlico."\" align=\"absmiddle\">";
			echo "<br>";
			echo "<br>";
		}
	echo "</td>";
	echo "<td align=\"right\" valign=\"top\">";
		echo "<span class=\"annot_petit_it\">".date ("d-m-Y")."</span><br>";

		echo "<br>";
        echo "<span class=\"annot_petit\">";
		echo "Code : ".$code_session."<br>";

		$nivutil = "";
	
		switch ($niv_session)
		{
			case 1 : $nivutil = "Client";
					   break;
			case 2 : $nivutil = "Opérateur";
					   break;
			case 3 : $nivutil = "Technicien";
					   break;
			case 4 : $nivutil = "Agence";
					   break;
			case 5 : $nivutil = "Centre de Gestion";
					   break;
			case 9 : $nivutil = "Administrateur";
					   break;
		}
	
		echo "Session : ".$nivutil."<br>";

		if (($niv_session == 3) OR ($niv_session == 4))
			echo "Agence : ".$code_age."<br>";

		echo "</span>";
		echo "<br>";
		echo "<br>";
		echo "<br>";
		echo "<br>";
		$urlico = $urli."/quit.gif"; 
		echo "<a href=\"quit.php\"><img src=\"".$urlico."\" title=\"QUITTER\" alt=\"Quitter\" border=\"0\" align=\"absmiddle\"></a>";
		echo "<br>";
	echo "</td>";
	echo "</tr>";
	echo "</table>";
}

// Fonction qui renvoie la civilité en fonction du code civilité

function Retournecivilite($code)
{
	$civilite = "";
	
	$conn2 = mysql_connect(DBHOST, DBUSER, DBPASS);
	mysql_select_db(DBNAME);
	$rciv = "SELECT * FROM " .TBCIV. " WHERE civ_code='".$code."'";
	if ($rowciv = mysql_fetch_array($rsciv)) 
	{
		extract ($rowciv);
	}
	mysql_free_result($rsciv);
	mysql_close($conn2);  

	$civilite = str_majuscules($civ_desc);

	return $civilite;
}

// Fonction qui renvoie la ville en fonction du code insee

function Retourneville($insee)
{
	$villes_nom = "";
	
	$conn2 = mysql_connect(DBHOST, DBUSER, DBPASS);
	mysql_select_db(DBNAME);
	$rnom = "SELECT villes_insee,villes_nom FROM " .TBVILLES. " WHERE villes_insee='".$insee."'";;
	$rsnom = mysql_query($rnom) or die(mysql_error());
	if (mysql_num_rows($rsnom) == 1) // Uniquement UN SEUL enregistrement !
	{
		if ($rowville = mysql_fetch_array($rsnom)) 
		{
			extract ($rowville);
		}
	}

	mysql_free_result($rsnom);
	mysql_close($conn2);  

	$ville = str_majuscules($villes_nom);
	
	// Les villes de LYON, MARSEILLE, PARIS doivent être traitées à part (arrondissements)
	
	if (stristr($villes_nom,"LYON"))
		$ville="LYON";
	if (stristr($villes_nom,"MARSEILLE"))
		$ville="MARSEILLE";
	if (stristr($villes_nom,"PARIS"))
		$ville="PARIS";
			
	return $ville;
}

// Fonction qui renvoie le code postal en fonction du code insee

function Retournecp($insee)
{
	$villes_cp = "";
	
	$conn2 = mysql_connect(DBHOST, DBUSER, DBPASS);
	mysql_select_db(DBNAME);
	$rcp = "SELECT villes_insee,villes_cp FROM " .TBVILLES. " WHERE villes_insee='".$insee."'";;
	$rscp = mysql_query($rcp) or die(mysql_error());
	if (mysql_num_rows($rscp) == 1) // Uniquement UN SEUL enregistrement !
	{
		if ($rowcp = mysql_fetch_array($rscp)) 
		{
			extract ($rowcp);
		}
	}

	mysql_free_result($rscp);
	// mysql_close($conn2);  

	return $villes_cp;
}

// Fonction qui renvoie un nom de créateur

function Retournecreateur($code,$code_admin)
{
	$createur = "";
	$cdg = FALSE;
	
	if ($code <> $code_admin)
	{
		$racine = substr($code,0,1);
		switch ($racine)
		{
			case "A" : $rech2 = "SELECT * FROM " .TBAGE. " WHERE age_code='".$code."'";
					   break;
			case "C" : $rech2 = "SELECT * FROM " .TBCLI. " WHERE cli_code='".$code."'";
					   break;
			case "T" : $rech2 = "SELECT * FROM " .TBTEC. " WHERE tec_code='".$code."'";
					   break;
			case "O" : $rech2 = "SELECT * FROM " .TBOPE. " WHERE ope_code='".$code."'";
					   break;
					   
			default  : $cdg = TRUE;
					   break;
		}

		if ($cdg == FALSE)
		{
			$conn2 = mysql_connect(DBHOST, DBUSER, DBPASS);
			mysql_select_db(DBNAME);
			
			$rs2 = mysql_query($rech2) or die(mysql_error());
			if (mysql_num_rows($rs2) == 1) // Il ne peut y avoir qu'un seul résultat
			{
				if ($row2 = mysql_fetch_array($rs2)) 
					extract ($row2);
				
				switch ($racine)
				{
					case "A" : $createur = $age_nom." (Agence)";
							   break;
					case "C" : $createur = $cli_civilite." ".$cli_prenom." ".$cli_nom." (Client)";
							   break;
					case "T" : $createur = $tec_civilite." ".$tec_prenom." ".$tec_nom." (Technicien)";
							   break;
					case "O" : $createur = $ope_civilite." ".$ope_prenom." ".$ope_nom." (Opérateur)";
							   break;
				}
			}
			else // S'il n'y a pas de résultat, c'est le centre de gestion
				 // (Code centre commence par A/C/T/O)
			{
				$createur = "le centre de gestion";
			}
			mysql_free_result($rs2);
			mysql_close($conn2);  
		}
		else
		{
			$createur = "le centre de gestion";
		}
	}
	else
	{
		$createur = "l'administrateur";
	}

	return $createur;
}

/***************************************/
/* Gestion des codes et mots de passes */
/***************************************/

// Création d'un code pour les Clients, Interventions, Techniciens, Opérateurs, Contrats, Demandes, Relevés lors de l'enregistrement
// de la fiche 

function crecode($racine)
	{
		$conn2 = mysql_connect(DBHOST, DBUSER, DBPASS);
		mysql_select_db(DBNAME);
		$rcons = "SELECT * FROM " .TBCONS;
		$rscons = mysql_query($rcons) or die(mysql_error());
		if (mysql_num_rows($rscons) == 1) // Uniquement UN SEUL enregistrement !
		{
			 while ($rowcons = mysql_fetch_array($rscons)) 
			{
				extract ($rowcons);

				// Code Client

				if ($racine == "C")
				{
					$rcli = strip_tags(trim($rowcons["cons_rcli"]));
					$ncli = $rowcons["cons_ncli"];
					$ncli++;
					settype ($ncli, "string");
					$format = "%'07s";
					$nclif = sprintf ($format,$ncli);
					$codecree = $rcli.$nclif;

					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_ncli`='".$ncli."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}
		
				// Code Technicien

				if ($racine == "T")
				{
					$rtec = strip_tags(trim($rowcons["cons_rtec"]));
					$ntec = $rowcons["cons_ntec"];
					$ntec++;
					settype ($ntec, "string");
					$format = "%'07s";
					$ntecf = sprintf ($format,$ntec);
					$codecree = $rtec.$ntecf;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_ntec`='".$ntec."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}
		
				// Code Intervention

				if ($racine == "I")
				{
					$rint = strip_tags(trim($rowcons["cons_rint"]));
					$nint = $rowcons["cons_nint"];
					$nint++;
					settype ($nint, "string");
					$format = "%'07s";
					$nintf = sprintf ($format,$nint);
					$codecree = $rint.$nintf;

					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_nint`='".$nint."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}

				// Code Opérateur

				if ($racine == "O")
				{
					$rope = strip_tags(trim($rowcons["cons_rope"]));
					$nope = $rowcons["cons_nope"];
					$nope++;
					settype ($nope, "string");
					$format = "%'07s";
					$nopef = sprintf ($format,$nope);
					$codecree = $rope.$nopef;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_nope`='".$nope."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}
		
				// Code Contrat

				if ($racine == "CO")
				{
					$rcon = strip_tags(trim($rowcons["cons_rcon"]));
					$ncon = $rowcons["cons_ncon"];
					$ncon++;
					settype ($ncon, "string");
					$format = "%'07s";
					$nconf = sprintf ($format,$ncon);
					$codecree = $rcon.$nconf;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_ncon`='".$ncon."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}
		
				// Code Certificat de destruction

				if ($racine == "CD")
				{
					$rcer = strip_tags(trim($rowcons["cons_rcer"]));
					$ncer = $rowcons["cons_ncer"];
					$ncer++;
					settype ($ncer, "string");
					$format = "%'07s";
					$ncerf = sprintf ($format,$ncer);
					$codecree = $rcer.$ncerf;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_ncer`='".$ncer."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}
		
				// Code Demande

				if ($racine == "D")
				{
					$rdde = strip_tags(trim($rowcons["cons_rdde"]));
					$ndde = $rowcons["cons_ndde"];
					$ndde++;
					settype ($ndde, "string");
					$format = "%'07s";
					$nddef = sprintf ($format,$ndde);
					$codecree = $rdde.$nddef;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_ndde`='".$ndde."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}
		
				// Code Relevé

				if ($racine == "R")
				{
					$rrel = strip_tags(trim($rowcons["cons_rrel"]));
					$nrel = $rowcons["cons_nrel"];
					$nrel++;
					settype ($nrel, "string");
					$format = "%'07s";
					$nrelf = sprintf ($format,$nrel);
					$codecree = $rrel.$nrelf;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_nrel`='".$nrel."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}


				// Code Relevé de Licences

				if ($racine == "RL")
				{
					$rrlic = strip_tags(trim($rowcons["cons_rrlic"]));
					$nrlic = $rowcons["cons_nrlic"];
					$nrlic++;
					settype ($nrlic, "string");
					$format = "%'07s";
					$nrlicf = sprintf ($format,$nrlic);
					$codecree = $rrlic.$nrlicf;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_nrlic`='".$nrlic."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}

				// Code Relevé de Matériels

				if ($racine == "RM")
				{
					$rrpdt = strip_tags(trim($rowcons["cons_rrpdt"]));
					$nrpdt = $rowcons["cons_nrpdt"];
					$nrpdt++;
					settype ($nrpdt, "string");
					$format = "%'07s";
					$nrpdtf = sprintf ($format,$nrpdt);
					$codecree = $rrpdt.$nrpdtf;
					
					$rcmaj = "UPDATE `" .TBCONS. "` SET `cons_nrpdt`='".$nrpdt."'  WHERE `cons_id` = '".$rowcons["cons_id"]."'";
					$rscmaj = mysql_query($rcmaj) or die(mysql_error());
				}

			}
		}

		mysql_free_result($rscons);
		mysql_close($conn2);  

		return $codecree;
	}

// Création d'un code session pour création de fiche Client ("C"), Technicien ("T"), Intervention ("I"), etc...
// Fonction utilisée pendant la saisie d'un formulaire

function crecodesession($racine)
	{
		$codecree = $racine.time();
		return $codecree;
	}

// Création du mot de passe

function cremdp($aveccryptage)
{
    mt_srand();
    $longueur = mt_rand(8,10);

    $mdpcree = "";
    for($i=0; $i<$longueur; $i++)
	{
        $quoi= mt_rand(1,3);
        switch($quoi){
            case 1: $mdpcree .= mt_rand(0,9); break;
            case 2: $mdpcree .= chr(mt_rand(65,90)); break;
            case 3: $mdpcree .= chr(mt_rand(97,122)); break;
        }
    }

	if ($aveccryptage == TRUE)	
		$mdpcree = md5($mdpcree); // Cryptage du MDP

	return $mdpcree;
}

// Affichage des erreurs MYSQL

function erreur_bdd($argTexte,$argSql = '')
{
 echo "<table width=\"100%\"  border=\"0\">";
 echo "<tr>";
 echo "<td width=\"30%\"><div align=\"right\"><font size=\"10\" color=\"red\"><b>Erreur MYSQL</b></font></div></td>";
 echo "<td width=\"70%\">&nbsp;</td>";
 echo "</tr>";
 echo "<tr>";
 echo "<td width=\"30%\"><div align=\"right\"><b>Type : </b></div></td>";
 echo "<td width=\"70%\"><b>$argTexte </b>.</td>";
 echo "</tr>";
 echo "<tr>";
 echo "<td width=\"30%\"><div align=\"right\"><b>Numéro : </b></div></td>";
 $strCorps = @mysql_errno()." --> ".@mysql_error();
 echo "<td width=\"70%\"><b>$strCorps</b>. </td>";
 echo "</tr>";
 echo "</table>";

 // envoi email à admin
 // $strCorps = "[$argTexte]\n";
 // $strCorps .= "(".@mysql_errno()." : ".@mysql_error().")\n";
 // if(!empty($argSql)) $strCorps .= "\nSQL : ".$argSql;
 // @mail(
 // "email@administrateur",
 // "Erreur Bdd",
 // $strCorps
 // );
}

/***************************/
/* Gestion des impressions */
/***************************/

//	Entête des impressions des contrats

function entimpcon($titre)
{
	echo "<p align=\"center\"><span class=\"titre_impression\">$titre</span></p>";
}

//	Entête des impressions des fiches Agences, Clients, Techniciens

function entimpfic($titre)
{
	echo "<p align=\"center\"><span class=\"titre_impression\">$titre</span></p>";
	echo "<span class=\"annot_petit_it\">";
	echo "Imprimée le : ";
	echo "</span>";
	echo "<span class=\"annot_gras_it\">";
	echo date("d-m-Y H:i:s")."<br><br>";
	echo "</span>";
}

//	Entête des impressions des fiches Interventions

function entimpint($titre,$inter,$dateinter,$daterdv,$url,$agence)
{
	echo "<table width=\"740\" border=\"0\">";
    echo "<tr>";

    echo "<td rowspan=\"2\">";
	$urlimg = "";
	if (agence <> "")
	{
		$urlimg = $url."/log".strtolower($agence).".png"; 
	}
	echo "<div align=\"left\">";
	echo "<img src=\"".$urlimg."\"><br>";

	$rowage = lit_enr_age($agence);
	extract ($rowage);

	echo "<span class=\"annot_petit_it\">";
	echo $age_rcs."<br>";
	echo "</span>";
	echo $age_adr1."<br>";
	if ($age_adr2 <> "")
		echo $age_adr2."<br>";
	echo Retournecp($age_ville)." ".Retourneville($age_ville)."<br>";
	echo "Tel : ";
	echo $age_telf;
	echo "<span class=\"annot_petit_it\">";
	echo " ".$age_txttelf."<br>";
	echo "</span>";
	echo "Fax : ";
	echo $age_fax;
	echo "<span class=\"annot_petit_it\">";
	echo " ".$age_txtfax."<br>";
	echo "</span>";
	echo "<span class=\"annot_petit_it\">";
	if ($age_web <> "")
	{
		echo " Site Web : ";
		echo $age_web." / ";
	}
	echo "Email : ";
	echo $age_email."<br>";
	echo "Horaires : ";
	echo $age_heures."<br>";
	echo formatetxt($age_txtent);
	echo "</span>";
	echo "</div>";
	echo "</td>";

    echo "<td valign=\"top\">";
	echo "<div align=\"center\">";
	echo "<span class=\"titre_impression\">$titre</span>";
	echo "<br>";
	echo "Numéro : ";
	echo "<b>";
	echo $inter."<br>";
	echo "</b>";
	echo "Date création : ";
	echo "<b>";
	echo $dateinter."<br>";
	echo "</b>";
	if ($daterdv <> "")
	{
		echo "<span class=\"annot_petit_it\">";
		echo "Rendez-vous le : ";
		echo "</span>";
		echo "<span class=\"annot_gras_it\">";
		echo $daterdv."<br>";
		echo "</span>";
	}
	echo "</div>";
	echo "</td>";

    echo "</tr>";

    echo "<tr>";
	echo "<td>";
	echo "<div valign =\"bottom\" align=\"right\">";
	echo "<span class=\"annot_petit_it\">";
	echo "Imprimée le : ";
	echo "</span>";
	echo "<span class=\"annot_gras_it\">";
	echo date("d-m-Y H:i:s")."<br>";
	echo "</span>";
	echo "</div>";
	echo "</td>";
    echo "</tr>";

	echo "</table>";
}

//	Entête des impressions complémentaires (contacts, articles, etc...) des fiches

function compimpfic($titre)
{
	echo "<p align=\"center\"><span class=\"titre_gras_soul\">$titre</span></p>";
}

/***************************/
/*   Gestion des langues   */
/***************************/

// Retoune le pays du connecté

function pays_connect($no_ip)
{
	global
		$root;
		
	$chem_geoip=$root.REP."/inc/GeoIP.dat";
	$pays= "";
	$code_pays = "";

	// read GeoIP database
	$handle = geoip_open($chem_geoip, GEOIP_STANDARD);

	// map IP to country
	$code_pays = geoip_country_code_by_addr($handle, $no_ip);

	// close database handler
	geoip_close($handle);
	
	if ($code_pays <> "")
	{
		$conn2 = mysql_connect(DBHOST, DBUSER, DBPASS);
		mysql_select_db(DBNAME);
		$rnom = "SELECT * FROM " .TBPAYS. " WHERE pays_code2='".$code_pays."'";;
		$rsnom = mysql_query($rnom) or die(mysql_error());
		if (mysql_num_rows($rsnom) == 1) // Uniquement UN SEUL enregistrement !
		{
			if ($rowpays = mysql_fetch_array($rsnom)) 
			{
				extract ($rowpays);
				$pays =  $pays_nom;
			}
		}
	
		mysql_free_result($rsnom);
		mysql_close($conn2);
	}
	
	return $pays;
}
?> 
