<?php
require_once 'Framework/Modele.php';

class Societeuser extends Modele {

    public function getSocieteuser($codeSociete)
	{
		$sql = 'select A.codeSociete, A.nomSociete, A.adresseGeoSociete, A.adressePostSociete, 
		A.ville, A.telephoneSociete, A.emailSociete, A.faxSociete, A.codePays, 
		medecinConseil, emailMedecinConseil, telephoneMedecinConseil, 
		emailAdministrateurSysteme, telephoneAdministrateurSysteme, sigleSociete, A.registreCommerce
		from societeuser A
		left join pays B on (B.codeSociete=A.codeSociete) and (B.codePays=A.codePays)
		where (A.codeSociete=?)';

        $societe = $this->executerRequete($sql, array($codeSociete));
        return $societe->fetch(PDO::FETCH_ASSOC);
    }
	
    public function getVwSocieteuser($codeSociete)
	{
		$sql = 'SELECT * FROM vw_societeuser where (codeSociete=?);';

        $societe = $this->executerRequete($sql, array($codeSociete));
        return $societe->fetch(PDO::FETCH_ASSOC);
    }

    public function enregistrermodif($idSocieteuser, $nomCentreGestion, $adresseGeoSociete, $adressePostSociete, 
		$codePays, $ville, $telephoneSociete, $faxSociete, $portable, $nomResponsable, $sigleSociete, $emailSociete, 
		$emailMedecinConseil, $emailGestionBon, $emailCcomptabilite, $emailDerogation, $emailAccordPrealable, 
		$emailFraudeFinger, $email_standard, $email_reply, $smsMedecinConseil, $codeLangueSociete, $codePrefixePolice, 
		$smsGestionBon, $smsDerogation, $smsAccordPrealable, $fraisCarteAN, $spMax, $indicatifTelephone, $codeTypeSociete,
		$emailAlertLimiteConsommation, $activerLogVisitePages, $alertLimiteConsommation, $emailRd, $smsRd)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_modifier_parametresgeneraux(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
													?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
													?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
													?, ?, ?, ?, ?, ?);';
		

		$this->executerRequete($sql, array($idSocieteuser, $nomCentreGestion, $adresseGeoSociete, $adressePostSociete, 
		$codePays, $ville, $telephoneSociete, $faxSociete, $portable, $nomResponsable, $sigleSociete, $emailSociete, 
		$emailMedecinConseil, $emailGestionBon, $emailCcomptabilite, $emailDerogation, $emailAccordPrealable, 
		$emailFraudeFinger, $email_standard, $email_reply, $smsMedecinConseil, $codeLangueSociete, $codePrefixePolice, 
		$smsGestionBon, $smsDerogation, $smsAccordPrealable, $fraisCarteAN, $spMax, $indicatifTelephone, $codeTypeSociete,
		$user, $emailAlertLimiteConsommation, $activerLogVisitePages, $alertLimiteConsommation, $emailRd, $smsRd));
    }
	
     public function enregistrermodifprod($idSocieteuser, $tauxHonoraires, $spMax, $fraisCarteAN, $fraisCarteREN, 
		$fraisCarteImp, $spAlertAdherent, $tauxRistourne, $seuilRistourne, $archivageAutomatiquePrime, 
		$ecartEncaissementTolerable, $notificationSmsNumeroAssure, $notificationGroupeNumeroAssure, $lettrageAutoGarant,
		$parametresFacturationGarantProduit, $assureAjoutPhoto)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_modifier_parametres_prod(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		
		
		
		$this->executerRequete($sql, array($idSocieteuser, $tauxHonoraires, $spMax, $fraisCarteAN, $fraisCarteREN, 
		$fraisCarteImp, $spAlertAdherent, $tauxRistourne, $seuilRistourne, $user, $archivageAutomatiquePrime, 
		$ecartEncaissementTolerable, $notificationSmsNumeroAssure, $notificationGroupeNumeroAssure, 
		$lettrageAutoGarant, $parametresFacturationGarantProduit, $assureAjoutPhoto));
    }

     public function enregistrermodifsin($idSocieteuser, $accesAjoutPh, $ajoutPrescription, $ajoutPrescriptionExam, 
		$envoismsactif, $smsAssure, $smsmParFacture, $emailParFacture, $copieSmsPrestataireAssure, $filtreMedecin,
		$nbMedicamentMax, $seuilAlerteActif,$appliquerMargePrixMedicament, $appliquerMargePrixSubstitutMedicament,
		$margePrixMedicament, $margePrixSubstitutMedicament, $montantTotalExamen, $montantTotalMedicament,
		$nbConsultationSpecialisteJour, $ageMaxiPediatrie, $nombreFeuilleGratuitJour, $codeModeBiometrie,
		$permettrePrescriptionSiCritereBareme, $interdirVentePartielleMedicament, $accorderMedecinSouscripteur,
		$delaisReponseMedecinSouscripteur, $tousActesSoumisOuinon, $nombreActeEntentePrealable, $typeMargePrixMedicament, 
		$codeActeConsultationGratuite, $quantiteMedicamentPermise, $prixMedicamentModifiable, $nbTentativeBiometrie,
		$envoismswhatsappactif, $appliquerTarifBas, $autoriserUnBonPrincipal, $autoriserBonHospitAvantDate,
		$encaissementExige)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_modifier_parametres_sin(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idSocieteuser, $accesAjoutPh, $ajoutPrescription, $ajoutPrescriptionExam, 
		$envoismsactif, $smsAssure, $smsmParFacture, $emailParFacture, $copieSmsPrestataireAssure, $filtreMedecin,
		$nbMedicamentMax, $seuilAlerteActif, $appliquerMargePrixMedicament, $appliquerMargePrixSubstitutMedicament,
		$margePrixMedicament, $margePrixSubstitutMedicament, $montantTotalExamen, $montantTotalMedicament, $user, 
		$nbConsultationSpecialisteJour, $ageMaxiPediatrie, $nombreFeuilleGratuitJour, $codeModeBiometrie,
		$permettrePrescriptionSiCritereBareme, $interdirVentePartielleMedicament, $accorderMedecinSouscripteur,
		$delaisReponseMedecinSouscripteur, $tousActesSoumisOuinon, $nombreActeEntentePrealable, $typeMargePrixMedicament, 
		$codeActeConsultationGratuite, $quantiteMedicamentPermise, $prixMedicamentModifiable, $nbTentativeBiometrie,
		$envoismswhatsappactif, $appliquerTarifBas, $autoriserUnBonPrincipal, $autoriserBonHospitAvantDate, $encaissementExige));
    }
		
    public function verifiermotpass($codeSociete, $mdp)
    {
		$sql = "call sp_get_mot_pass_reinit(?);";
		
		$resultat = $this->executerRequete($sql, array($codeSociete));
		
		if ($resultat->rowCount() == 1)
		{
			$user = $resultat->fetch(PDO::FETCH_ASSOC);
			$hash = $user['motPassReinit'];
			return (password_verify($mdp, $hash));
		}
		else 
		{
			return false;
		}
    }
		
    public function changerpass($codeSociete, $ancmdp, $nvmdp)
    {
		$user = $_SESSION['login'];
		
		$hash = password_hash($nvmdp, PASSWORD_DEFAULT);
		
		$sql = "CALL sp_r_changementmotpassreinit(?, ?, ?, ?);";

		$this->executerRequete($sql, array($codeSociete, $hash, $user, $nvmdp));
    }
		
    public function getSocieteSimple($codeSociete)
	{
		$sql = 'select * from societeuser where (codeSociete=?);';

        $societe = $this->executerRequete($sql, array($codeSociete));
		
        return $societe->fetch(PDO::FETCH_ASSOC);
    }

    public function getcompsantepassword()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT expression AS `code`, description AS libelle FROM compsantepassword ORDER BY ordre;';
		}
		else
		{
			$sql = 'SELECT expression AS `code`, description AS libelle FROM compsantepassword ORDER BY ordre;';
		}

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);	
    }

    public function getcompsantepasswordactif($actif)
	{
		if (est_anglophone())
		{
			$sql = 'SELECT id AS `code`, descriptionEng AS libelle FROM compsantepassword where (actif=?) ORDER BY ordre;';
		}
		else
		{
			$sql = 'SELECT id AS `code`, description AS libelle FROM compsantepassword where (actif=?) ORDER BY ordre;';
		}

        $resultat = $this->executerRequete($sql, array($actif));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);	
    }

    public function majcomplexitepassword($idExpression , $actif) 
	{
		$codeSociete 	= $_SESSION['codeSociete'];
		$user 			= $_SESSION['login'];
			
		$sql = "call sp_maj_complexite_password(?, ?, ?, ?);";
		
        $this->executerRequete($sql, array($codeSociete, $idExpression , $actif, $user));
    }

    public function get_nbTentativeConnexion()
	{
		$sql = 'SELECT nbTentativeConnexion FROM societeuser LIMIT 1;';
		
        $resultat = $this->executerRequete($sql);

		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		$nbTentativeConnexionMax = $ligne['nbTentativeConnexion'];
		
		$_SESSION['nbTentativeConnexionMax'] = $nbTentativeConnexionMax;
		
		return $nbTentativeConnexionMax;		
    }	

    public function getparamsgenerationpass()
	{
		$codeSociete = $_SESSION['codeSociete'];
		
		$sql = 'SELECT A.codeModeGenerationPass, B.libelle AS modegenerationpass, A.codeModeEnvoiPass FROM societeuser A
		LEFT JOIN modegenerationpass B ON (B.codeModeGenerationPass=A.codeModeGenerationPass) WHERE (A.codeSociete=?) LIMIT 1;';
		
        $resultat = $this->executerRequete($sql, array($codeSociete));;

		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		/*
		$codeModeGenerationPass = $ligne['codeModeGenerationPass'];
		$_SESSION['codeModeGenerationPass'] = $codeModeGenerationPass;
		*/
		
		return $ligne;		
    }	

    public function getmodeenvoipass()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeModeEnvoiPass AS `code`, libelleEng AS libelle FROM modeenvoipass ORDER BY ordre;';
		}
		else
		{
			$sql = 'SELECT codeModeEnvoiPass AS `code`, libelle FROM modeenvoipass ORDER BY ordre;';
		}

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);	
    }

    public function getmodegenerationpass()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeModeGenerationPass AS `code`, libelleEng AS libelle FROM modegenerationpass ORDER BY ordre;';
		}
		else
		{
			$sql = 'SELECT codeModeGenerationPass AS `code`, libelle FROM modegenerationpass ORDER BY ordre;';
		}

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);	
    }	


	public function get_reclamation_prestataire_obligatoire()
	{
		$codeSociete 		= $_SESSION['codeSociete'];

		$sql = 'select fn_reclamation_prestataire_obligatoire(?) as reclamationPrestataireObligatoire;';
		
        $resultat = $this->executerRequete($sql, array($codeSociete));
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		return $ligne['reclamationPrestataireObligatoire'];		
	}

	public function get_demandedecompte_prestataire()
	{
		$codeSociete 		= $_SESSION['codeSociete'];

		$sql = 'select fn_demande_decompte_prestataire(?) as demandedecomptePrestataire;';
		
        $resultat = $this->executerRequete($sql, array($codeSociete));
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		return $ligne['demandedecomptePrestataire'];		
	}
	
	public function getModeBiometrie()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeModeBiometrie AS `code`, libelleEng AS libelle FROM modebiometrie where actif="1";';
		}
		else
		{
			$sql = 'SELECT codeModeBiometrie AS `code`, libelle FROM modebiometrie where actif="1";';
		}

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function getMotifsDepassement()
	{
		
		$sql = 'SELECT * FROM motifdepassement ORDER BY id;';
		
        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function enregistrerlibelle($idMessage, $libelle)
	{
		if (est_anglophone())
		{
			$sql = 'UPDATE motifdepassement SET 
						libelleEng=?
					WHERE id=?;';
		}
		else
		{
			$sql = 'UPDATE motifdepassement SET 
						libelle=?
					WHERE id=?;';
		}
		
		$this->executerRequete($sql, array($libelle, $idMessage));
	}
	

	public function getPays()
	{
		
		$sql = 'SELECT codePays AS `code`, libelle FROM pays
		ORDER BY libelle;';
		
        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function getVilles($codePays)
	{
		$sql = 'SELECT codeLocalite AS `code`, libelle 
				FROM localite WHERE codePays=? ORDER BY libelle;';
		
        $resultat = $this->executerRequete($sql, array($codePays));

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function existeligne($codeSociete) 
	{
        $sql = 'select id FROM societeuser WHERE (codeSociete=?)';
        $resultat = $this->executerRequete($sql, array($codeSociete));
		
		return ($resultat->rowCount() > 0);
    }
	
	public function creersocieteuser($codeSociete, $nomCentreGestion, $adresseGeoSociete, $adressePostSociete, $codePays, 
	$ville, $telephoneSociete, $faxSociete, $portable, $nomResponsable, $sigleSociete, $emailSociete, $emailMedecinConseil, 
	$emailGestionBon, $emailCcomptabilite, $emailDerogation, $emailAccordPrealable, $emailFraudeFinger, $email_standard, 
	$email_reply, $smsMedecinConseil, $codeLangueSociete, $codePrefixePolice, $smsGestionBon, $smsDerogation, $smsAccordPrealable, 
	$fraisCarteAN, $spMax, $indicatifTelephone, $codeTypeSociete)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_creer_societeuser(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
		       
		$this->executerRequete($sql, array($codeSociete, $nomCentreGestion, $adresseGeoSociete, $adressePostSociete, $codePays, $ville, 
		$telephoneSociete, $faxSociete, $portable, $nomResponsable, $sigleSociete, $emailSociete, $emailMedecinConseil, $emailGestionBon, 
		$emailCcomptabilite, $emailDerogation, $emailAccordPrealable, $emailFraudeFinger, $email_standard, $email_reply, $smsMedecinConseil, 
		$codeLangueSociete, $codePrefixePolice, $smsGestionBon, $smsDerogation, $smsAccordPrealable, $fraisCarteAN, $spMax, $indicatifTelephone,
		$codeTypeSociete));
    }
	
	public function gettypeSociete()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT codeType AS `code`, libelleTypeEng AS libelle
					FROM typesociete ORDER BY libelleTypeEng;';
		}
		else
		{
			$sql = 'SELECT codeType AS `code`, libelleType AS libelle
					FROM typesociete ORDER BY libelleType;';
		}

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
	}
	
	public function getlisterListecentregestion(){
		
		$sql = "SELECT B.libelle AS pays, C.libelle AS villeSociete, A.*
				FROM societeuser A 
				JOIN pays B ON (A.codePays = B.codePays)
				JOIN localite C  ON (A.ville = C.codeLocalite)
				ORDER BY 1,2, 5;";

		
		$resultat = $this->executerRequete($sql);
        return $resultat->fetchAll(PDO::FETCH_ASSOC);
	}

	public function getListecentregestion($id){
		$sql = "SELECT * FROM societeuser ORDER BY codeSociete ASC";
		
		$resultat = $this->executerRequete($sql, array($id, $_SESSION['codeSociete']));
        return $resultat->fetch(PDO::FETCH_ASSOC);
	}
	
	public function modifierListecentregestion($codeGcAssureur,$codeEnteteTrancheAge,$libelle,$libelleEng,$id){
		
		$codeSociete = $_SESSION['codeSociete'];
		
		$sql = "SELECT * FROM societeuser ORDER BY codeSociete ASC";
		
		$this->executerRequete($sql, array($codeSociete,$libelle,$libelleEng,$id,$codeSociete));
	}
	
	public function getVwSocieteuserId($idCentreGestion)
	{
		$sql = 'select * from vw_societeuser where (id=?);';

        $societe = $this->executerRequete($sql, array($idCentreGestion));
        return $societe->fetch(PDO::FETCH_ASSOC);
    }
	
	public function existeligneiD($id) 
	{
        $sql = 'select id FROM societeuser WHERE (id=?)';
        $resultat = $this->executerRequete($sql, array($id));
		
		return ($resultat->rowCount() > 0);
    }
	
	public function getTexteFacturationGarant()
	{
		
		$sql = 'SELECT * FROM typefacturationgarant ORDER BY libelle;';
		
        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function majtextefacturation($idTexte, $texte)
	{
		if (est_anglophone())
		{
			$sql = 'UPDATE typefacturationgarant SET
						texteEng = ?
					WHERE id = ?;';
		}
		else
		{
			$sql = 'UPDATE typefacturationgarant SET
						texte = ?
					WHERE id = ?;';
		}

        $this->executerRequete($sql, array($texte, $idTexte));
		
	}
	
	public function majecheancefacturation($idTexte, $echeance)
	{
		$sql = 'UPDATE typefacturationgarant SET
					echeance = ?
				WHERE id = ?;';
		
		$this->executerRequete($sql, array($echeance, $idTexte));
	}
	
	public function get_ville_societe()
	{
		$codeSociete 		= $_SESSION['codeSociete'];

		$sql = 'select fn_ville_societe(?) as villeSociete;';
		
        $resultat = $this->executerRequete($sql, array($codeSociete));
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		return $ligne['villeSociete'];		
	}
	
	public function getTypeMarge()
	{
		if (est_anglophone())
		{
			$sql = 'SELECT libelle AS `code`, libelleEng AS libelle 
					FROM choixforfaittaux;';
		}
		else
		{
			$sql = 'SELECT libelle AS `code`, libelle
					FROM choixforfaittaux;';
		}

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	
	public function getActeConsultationGratuite()
	{
		
			$sql = 'SELECT A.codeActe AS `code`, B.libelle  
					FROM p_acteconsultation A
					JOIN p_acte B ON (A.codeActe = B.codeActe);';
		

        $resultat = $this->executerRequete($sql);

		return $resultat->fetchAll(PDO::FETCH_ASSOC);
		
	}
	
	public function get_mode_biometrie_societe()
	{
		$codeSociete 		= $_SESSION['codeSociete'];

		$sql = 'select fn_mode_biometrie_societe(?) as codeModeBiometrie;';
		
        $resultat = $this->executerRequete($sql, array($codeSociete));
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
		
		return $ligne['codeModeBiometrie'];		
	}
	
	public function majAuthentificationOTP($codeSociete, $activerOtp)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_maj_authentification_otp(?, ?, ?);';
		
		$this->executerRequete($sql, array($codeSociete, $activerOtp, $user));
	}
	
	public function majfiltreip($idSocieteuser, $filtrerIp, $filtrerIpPrestataire)
	{
		$user = $_SESSION['login'];
		
		$sql = 'call sp_maj_majfiltreip(?, ?, ?, ?);';
		
		$this->executerRequete($sql, array($idSocieteuser, $filtrerIp, $filtrerIpPrestataire, $user));
	}
}