public List <Fichier> listerLesfichiersParDate(String date)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Fichier>($"Select * From Fichier Where datediff(day, DateAjout, '{date}')=0 OR datediff(day, DateModification,'{date}')=0 OR datediff(day, DateSuppression, '{date}')=0 ORDER BY IdFichier DESC").ToList());
}
}
public void supprimerUtilisateur(int IdUtilisateur)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"DELETE FROM Utilisateur WHERE IdUtilisateur='{IdUtilisateur}';");
}
}
public List <Utilisateur> rechercheUtilisateurParNom(string Nom)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
String nomGenere = "%" + Nom + "%";
return(connection.Query <Model.Utilisateur>($"Select Nom * From Utilisateur Where Nom like {nomGenere};")
.ToList());
}
}
public void supprimerHistorique(Historique h)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"DELETE FROM Historique WHERE IdHistorique='{h.IdHistorique}';");
}
}
public List <Utilisateur> listerTousUtilisateur()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Model.Utilisateur>($"Select * From Utilisateur ").ToList());
}
}
public List <Model.Notification> listerTousNotification()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Model.Notification>($"Select * From Notification ORDER BY IdNotification DESC ").ToList());
}
}
public void ajouterHistorique(Historique h)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"INSERT INTO Historique(textHistorique,date,idFichier) values('{h.textHistorique}','{h.date}','{h.IdFichier}')");
}
}
public void supprimerType(Model.Type type)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"DELETE FROM Type WHERE idType ='{type.idType}';");
}
}
/* public void supprimerType(int idType)
* {
* using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
* {
* connection.Execute($"DELETE FROM GestionType WHERE idType ='{idType}';");
* }
* }*/
public Model.Type getTypeById(int idType)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
Model.Type type = connection.QuerySingle <Model.Type>($"Select * From Type Where idType='{idType}'");
return(type);
}
}
public List <Model.Type> listerTypes()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Model.Type>($"Select * From Type ORDER BY idType DESC;").ToList());
}
}
public void modifierType(Model.Type type)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"UPDATE Type SET nomType='{type.nomType}', duree='{type.duree}',action= '{type.action}',DUAselon='{type.DUAselon}',Description='{type.Description}' WHERE idType ='{type.idType}'");
}
}
public int ajouterType(Model.Type t)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
String query = $"INSERT INTO Type(nomType,duree,action,DUAselon,Description) values ('{ t.nomType}','{ t.duree}','{t.action}','{t.DUAselon}','{t.Description}');" +
"SELECT CAST(SCOPE_IDENTITY() as int)";
int id = connection.Query <int>(query).Single();
return(id);
}
}
public List <Fichier> rechercheFichierParType(int idType)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Fichier>($"Select Nom * From Fichier Where idType='{idType}';")
.ToList());
}
}
public List <Fichier> listerTousLesfichiers()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Fichier>($"Select * From Fichier ORDER BY IdFichier DESC")
.ToList());
}
}
public List <Model.Notification> listerNotificationFichier(Model.Fichier fichier)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Model.Notification>($"Select * From Notification Where IdFichier='{fichier.idFichier}' ORDER BY IdNotification DESC").ToList());
}
}
public Model.Type getTypeByName(string name)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
Model.Type type = (Model.Type)connection.Query <Model.Type>($"Select * From Type Where nomType='{name}'");
return(type);
}
}
public List <Model.Notification> listerNotificationUtilisateur(Model.Utilisateur utilisateur)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Model.Notification>($"Select n.* " +
$"From Notification n, Utilisateur u, GestionNotification g " +
$"Where n.IdNotification=g.IdNotification and u.IdUtilisateur=g.IdUtilisateur and g.IdUtilisateur='{utilisateur.idUtilisateur}' ORDER BY IdNotification DESC;").ToList());
}
}
public List <Model.Type> rechercheTypeParNom(string type)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
String nomGenere = "%" + type + "%";
return(connection.Query <Model.Type>($"Select * From Type Where nomType like {nomGenere};")
.ToList());
}
}
public void supprimerNotDuFichier(Fichier f)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
try
{
connection.Execute($"DELETE FROM Notification WHERE IdFichier='{f.idFichier}';");
}
catch (Exception)
{
throw;
}
}
}
public void ajouterNotifAvecUser(Utilisateur u, Notification n)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
String query = $"INSERT INTO GestionNotification(IdNotification,IdUtilisateur) values('{n.idNotification}','{u.idUtilisateur}')";
connection.Execute(query);
}
}
public List <Historique> listerTousHistoriques()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.Query <Historique>($"Select * From Historique ORDER BY IdHistorique DESC")
.ToList());
}
}
public Notification getNotificationByID(int idNotif)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
return(connection.QuerySingle <Model.Notification>($"Select * From Notification WHERE IdNotification = '{idNotif}'"));
}
}
public void ajouterUtilisateur(string Nom, string Prenom, string Email, string MotDePasse, string Privillege)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"INSERT INTO Utilisateur(Nom,Prenom,Email,MotDePasse,Privillege) " +
$"values ('{ Nom}','{ Prenom}', '{Email}', '{MotDePasse}', '{ Privillege}')");
}
}
public int ajouterNotification(Model.Notification notification)
{
if (FichierDAOSQLServer.getInstance().getFichierById(notification.idFichier) != null)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
String query = $"INSERT INTO Notification(textNotification,DateNotification,IdFichier) values('{notification.textNotification}','{notification.dateNotification}','{notification.idFichier}');" +
$"SELECT CAST(SCOPE_IDENTITY() as int)";
int id = connection.Query <int>(query).Single();
return(id);
}
}
else
{
return(-1);
}
}
public void modifierUtilisateur(int IdUtilisateur, string Nom, String Prenom, string Email, string MotDePasse, string Privillege)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"UPDATE Utilisateur SET Nom='{Nom}', Prenom='{ Prenom}', Email='{Email}'," +
$" MotDePasse='{ MotDePasse}', Privillege='{Privillege}' WHERE IdUtilisateur = '{IdUtilisateur}';");
}
}
public void modifierNotification(Model.Notification notification)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"UPDATE Notification SET textNotification='{notification.textNotification}', DateNotification='{notification.dateNotification}',Vu = '{notification.Vu}', IdFichier='{notification.idFichier}' WHERE IdNotification='{notification.idNotification}'");
}
}
public Utilisateur rechercheUtilisateurParId(int IdUtilisateur)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
Model.Utilisateur utilisateur = connection.QuerySingle <Model.Utilisateur>($"Select * From Utilisateur Where IdUtilisateur='{IdUtilisateur}'");
return(utilisateur);
}
}
public void supprimerNotification(Model.Notification notification)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"DELETE FROM Notification WHERE IdNotification='{notification.idNotification}';");
}
}
public Utilisateur Authentifier(string email, string motDePasse)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
try
{
Model.Utilisateur utilisateur = connection.QuerySingle <Model.Utilisateur>($"Select * From Utilisateur Where Email='{email}' AND MotDePasse='{motDePasse}'");
return(utilisateur);
}
catch (Exception e)
{
return(null);
}
}
}
public void modifierFichier(Fichier f)
{
if (f.type.idType == 0)
{
f.type.idType = f.idType;
}
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionHelper.conVal("CalendrierDatabase")))
{
connection.Execute($"UPDATE Fichier SET Nom='{f.Nom}',idType={f.type.idType},DateModification='{f.dateModification}'," +
$"DateDernierAcces='{f.dateDernierAcces}',DateSuppression='{f.dateSuppression}',[index]='{f.index}'," +
$"emplacementPC='{f.emplacementPC}',sortFinalComm='{f.sortFinalComm}',commArch='{f.commArch}',Chemain='{f.chemain}',Description='{f.Description}'," +
$"HangFireID='{f.HangFireID}',HangFireNotificationID='{f.HangFireNotificationID}',HangFireRecJobNotID='{f.HangFireRecJobNotID}'" +
$" WHERE IdFichier = {f.idFichier}");
}
}
