public static List <BO.LignesConsultations> GetAllRappel()
{
try
{
var query = @"select *
from LignesConsultations l, Animaux a, Consultations c, Baremes b, Vaccins v, Clients cl
where c.CodeAnimal = a.CodeAnimal
and c.CodeConsultation = l.CodeConsultation
and b.CodeGroupement = l.CodeGroupement and b.DateVigueur = l.DateVigueur
and v.CodeVaccin = b.CodeVaccin
and l.RappelEnvoye = 0
and cl.Archive = 0
and DATEADD(MONTH, v.PeriodeValidite,c.DateConsultation) < DATEADD(DAY, -15, GETDATE())";
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.LignesConsultations> results = cnx.Query <BO.LignesConsultations, BO.Animaux, BO.Consultations, BO.Baremes, BO.Vaccins, BO.Clients, BO.LignesConsultations>(query,
(ligneconsultation, animal, consultation, bareme, vaccin, client) =>
{ animal.Client = client; consultation.Animal = animal; bareme.Vaccin = vaccin; ligneconsultation.Barem = bareme; ligneconsultation.Consultation = consultation; return(ligneconsultation); },
splitOn: "CodeAnimal,CodeConsultation,CodeGroupement,DateVigueur,CodeVaccin").ToList <BO.LignesConsultations>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
public static bool Verif(BO.Agenda agenda)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
String query = @"SELECT count(*) FROM Agendas WHERE DateRdv=@date AND CodeVeto=@code";
int results1 = cnx.Query <int>(query, new { date = agenda.DateRdv, code = agenda.Veterinaires.CodeVeto }).ToList <int>()[0];
query = @"SELECT count(*) FROM Agendas WHERE DateRdv=@date AND CodeAnimal=@code";
int results2 = cnx.Query <int>(query, new { date = agenda.DateRdv, code = agenda.Animal.CodeAnimal }).ToList <int>()[0];
SqlConnexion.CloseConnexion(cnx);
if (results1 > 0 || results2 > 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Creer l'animal passé en params
/// Et le retourne avec son GUID
/// </summary>
/// <param name="animal"></param>
public static BO.Animaux Create(BO.Animaux animal)
{
try
{
BO.Clients cli = DAL.Clients.Get(animal.CodeClient);
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
Guid temp = cnx.ExecuteScalar <Guid>("EXEC ajout_animal @nomclient, @prenomclient, @nomani, @sexe, @couleur, @espece, @race, @archive",
new
{
nomclient = cli.NomClient,
prenomclient = cli.PrenomClient,
nomani = animal.NomAnimal,
sexe = animal.Sexe,
couleur = animal.Couleur,
espece = animal.Espece,
race = animal.Race,
archive = false
});
animal.CodeAnimal = temp;
SqlConnexion.CloseConnexion(cnx);
return(animal);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Retournes tout les barèmes avec le type passé en param
/// </summary>
/// <param name="Type"></param>
/// <returns></returns>
public static List <BO.Baremes> GetBaremes(String Type)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.Baremes> results = null;
if (String.IsNullOrEmpty(Type))
{
String query = @"SELECT * FROM Baremes b
LEFT JOIN Vaccins v ON b.CodeVaccin = v.CodeVaccin
WHERE b.archive = 0
ORDER BY b.CodeGroupement";
results = cnx.Query <BO.Baremes, BO.Vaccins, BO.Baremes>(query, (bareme, vaccin) => { bareme.Vaccin = vaccin; return(bareme); }, splitOn: "CodeVaccin").ToList <BO.Baremes>();
}
else
{
String query = @"SELECT * FROM Baremes b
LEFT JOIN Vaccins v ON b.CodeVaccin = v.CodeVaccin
WHERE b.archive = 0
AND TypeActe LIKE ('%' + @type + '%')
ORDER BY b.CodeGroupement";
results = cnx.Query <BO.Baremes, BO.Vaccins, BO.Baremes>(query, (bareme, vaccin) => { bareme.Vaccin = vaccin; return(bareme); }, new { type = Type }, splitOn: "CodeVaccin").ToList <BO.Baremes>();
}
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Creation d'un client
/// </summary>
/// <param name="client"></param>
/// <returns></returns>
public static BO.Clients Create(BO.Clients client)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
Guid temp = cnx.ExecuteScalar <Guid>("EXEC ajout_client @nom, @prenom, @add, @add2, @cp, @ville, @tel, @ass, @mail, @arch",
new
{
nom = client.NomClient,
prenom = client.PrenomClient,
add = client.Adresse1,
add2 = client.Adresse2,
cp = client.CodePostal,
ville = client.Ville,
tel = client.NumTel,
ass = client.Assurance,
mail = client.Email,
arch = 0
});
client.CodeClient = temp;
SqlConnexion.CloseConnexion(cnx);
return(client);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Retourne tout les animaux avec le tatouage comme value
/// </summary>
/// <param name="value"></param>
/// <param name="archived"></param>
/// <returns></returns>
public static List <BO.Animaux> GetAllByTatooArchive(String value, bool archived)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
String query = @"SELECT * FROM Animaux a
LEFT JOIN Clients c ON a.CodeClient = c.CodeClient
WHERE a.Archive = @archive
AND a.Tatouage LIKE (@value)
ORDER BY a.CodeAnimal";
List <BO.Animaux> results = cnx.Query <BO.Animaux, BO.Clients, BO.Animaux>(query,
(animaux, client) => { animaux.Client = client; return(animaux); },
param: new { value = '%' + value + '%', archive = archived },
splitOn: "CodeClient")
.ToList <BO.Animaux>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Création du barème
/// </summary>
/// <param name="bareme"></param>
/// <returns></returns>
public static bool CreateBareme(BO.Baremes bareme)
{
try
{
if (Exist(bareme.CodeGroupement, bareme.DateVigueur))
{
return(DAL.Baremes.Update(bareme));
}
else
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
int output = cnx.Execute("ajout_bareme", new
{
codegroupement = bareme.CodeGroupement,
datevigueur = bareme.DateVigueur,
typeacte = bareme.TypeActe,
libelle = bareme.Libelle,
tfixe = bareme.TarifFixe,
tmini = bareme.TarifMini,
tmaxi = bareme.TarifMaxi,
nomvaccin = (bareme.Vaccin == null) ? "" : bareme.Vaccin.NomVaccin
},
commandType: CommandType.StoredProcedure);
SqlConnexion.CloseConnexion(cnx);
return(output > 0);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Récupére tout les animaux par clients, archivé ou non
/// </summary>
/// <param name="?"></param>
/// <param name="archived"></param>
/// <returns></returns>
public static List <BO.Animaux> GetAllByClient(BO.Clients cli, bool archived)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
String query = @"SELECT * FROM Animaux a
LEFT JOIN Clients c ON a.CodeClient = c.CodeClient
WHERE a.CodeClient = @codeClient
AND a.Archive = @archive
ORDER BY a.CodeAnimal";
List <BO.Animaux> results = cnx.Query <BO.Animaux, BO.Clients, BO.Animaux>(query,
(animaux, client) => { animaux.Client = client; return(animaux); },
param: new { archive = archived, codeClient = cli.CodeClient },
splitOn: "CodeClient")
.ToList <BO.Animaux>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Retourne tout les clients dont le nom est %name% archivé ou non
/// </summary>
/// <param name="archived"></param>
/// <param name="name"></param>
/// <returns></returns>
public static List <BO.Clients> GetAll(bool archived, string name = "")
{
var query = String.Format(@"SELECT * FROM Clients c WHERE c.Archive = @archive and NomClient LIKE (@name) ORDER BY c.CodeClient");
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.Clients> results = cnx.Query <BO.Clients>(query, new { archive = archived, name = '%' + name + '%' }).ToList <BO.Clients>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
/// <summary>
/// Supprime la ligne consultation passé en params
/// </summary>
/// <param name="ligneConsultation"></param>
/// <returns></returns>
public static bool Delete(BO.LignesConsultations ligneConsultation)
{
var query = @"DELETE FROM LignesConsultations
WHERE CodeConsultation = @codeConsultation
AND NumLigne = @numLigne";
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
int deletedLine = cnx.Execute(query, new { codeConsultation = ligneConsultation.Consultation.CodeConsultation, ligneConsultation.NumLigne });
SqlConnexion.CloseConnexion(cnx);
return(deletedLine > 0);
}
/// <summary>
/// Retourne l'ensemble des comptes
/// </summary>
/// <returns>List de BO.Account</returns>
public static List <BO.Account> GetAll()
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.Account> results = cnx.Query <BO.Account>(@"SELECT * FROM Account").ToList <BO.Account>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Modifie le compte passé en paramètre
/// </summary>
/// <returns></returns>
public static bool Update(BO.Account account)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Account SET username=@user, password=@pwd WHERE id=@id";
int rowNb = cnx.Execute(query, new { id = account.Id, user = account.Username, pwd = account.PasswordSHA1 });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Supprime le compte passé en paramètre
/// </summary>
/// <param name="account"></param>
/// <returns>vrai si reussite, faux sinon</returns>
public static bool Delete(BO.Account account)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"DELETE FROM Account WHERE id = @id";
int rowNb = cnx.Execute(query, new { id = account.Id });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Retourne toutes les races
/// </summary>
/// <returns></returns>
public static List <String> GetAllEspeces()
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <String> results = cnx.Query <String>(@"SELECT DISTINCT Espece FROM Races").ToList <String>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Archive l'animal séléctionner
/// </summary>
/// <param name="animal"></param>
/// <returns></returns>
public static bool Archive(BO.Animaux animal)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Animaux SET Archive=1 WHERE CodeAnimal = @codeAnimal";
int rowNb = cnx.Execute(query, new { codeAnimal = animal.CodeAnimal });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Selectionne les données en base correspondant au compte passé en paramètre.
/// </summary>
/// <param name="account"></param>
/// <returns>Vrai si reussite, faux sinon</returns>
public static bool Logout(BO.Account account)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Account SET is_onlie=0 WHERE id = @id";
int rowNb = cnx.Execute(query, new { id = account.Id });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Vérifi l'existence du barem passé en param
/// </summary>
/// <param name="codeParam"></param>
/// <param name="dateParam"></param>
/// <returns></returns>
public static Boolean Exist(string codeParam, string dateParam)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
String query = String.Format(@"SELECT count(*) FROM Baremes b WHERE b.CodeGroupement = {0} AND b.DateVigueur LIKE ('%{1}%')", codeParam, dateParam);
int result = cnx.Query <int>(query).Single <int>();
SqlConnexion.CloseConnexion(cnx);
return(result > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Retourne tout les types d'acte possible
/// </summary>
/// <returns></returns>
public static List <String> GetTypeActe()
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
String query = @"Select distinct TypeActe FROM Baremes";
List <String> results = cnx.Query <String>(query).ToList <String>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Retourne le barème correspond a la PK
/// </summary>
/// <param name="codeParam"></param>
/// <param name="dateParam"></param>
/// <returns></returns>
public static BO.Baremes GetBareme(string codeParam, string dateParam)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
String query = String.Format(@"SELECT * FROM Baremes b LEFT JOIN Vaccins v ON b.CodeVaccin = v.CodeVaccin WHERE b.CodeGroupement = {0} AND b.DateVigueur LIKE ('%{1}%')", codeParam, dateParam);
List <BO.Baremes> results = cnx.Query <BO.Baremes, BO.Vaccins, BO.Baremes>(query, (bareme, vaccin) => { bareme.Vaccin = vaccin; return(bareme); }, splitOn: "CodeVaccin").ToList <BO.Baremes>();
SqlConnexion.CloseConnexion(cnx);
return(results.First());
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Archive tous les barèmes
/// </summary>
/// <returns></returns>
public static bool Flush()
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Baremes SET Archive=1";
int rowNb = cnx.Execute(query);
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Archive ou desarchive un bareme
/// </summary>
/// <param name="bareme"></param>
/// <param name="archiveParam"></param>
/// <returns></returns>
public static bool Archive(BO.Baremes bareme, bool archiveParam)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Baremes SET Archive=@archive WHERE CodeGroupement = @code AND DateVigueur like('%' + @date + '%')";
int rowNb = cnx.Execute(query, new { archive = (archiveParam) ? 1 : 0, code = bareme.CodeGroupement, date = bareme.DateVigueur });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Creer en base le compte passer en parametre
/// </summary>
/// <param name="account"></param>
/// <returns>Vrai si l'insertion a reussi, faux sinon</returns>
public static bool Create(BO.Account account)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
int output = cnx.Execute("ajout_account", new { username = account.Username, password = account.PasswordSHA1 },
commandType: CommandType.StoredProcedure);
SqlConnexion.CloseConnexion(cnx);
return(output > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Déactive un vétérinaire
/// </summary>
/// <param name="veto"></param>
/// <returns></returns>
public static bool Archive(BO.Veterinaires vetoParams, bool archived)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Veterinaires SET Archive=@archive WHERE CodeVeto = @codeVeto";
int rowNb = cnx.Execute(query, new { codeVeto = vetoParams.CodeVeto, archive = (archived) ? 1 : 0 });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
public static bool Update(BO.Vaccins vac)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Vaccins SET QuantiteStock=@qte WHERE CodeVaccin=@code";
int rowNb = cnx.Execute(query, new { code = vac.CodeVaccin, qte = vac.QuantiteStock });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
public static bool Delete(BO.Agenda agenda)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"DELETE FROM Agendas WHERE CodeVeto = @codeVeto AND DateRdv = @date AND CodeAnimal = @codeAnimal";
int rowNb = cnx.Execute(query, new { codeVeto = agenda.Veterinaires.CodeVeto, codeAnimal = agenda.Animal.CodeAnimal, date = agenda.DateRdv });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Déactive/Active un client
/// </summary>
/// <param name="client"></param>
/// <returns></returns>
public static bool Archive(BO.Clients client, bool archived)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE Clients SET Archive=@archive WHERE CodeClient = @codeClient";
int rowNb = cnx.Execute(query, new { codeClient = client.CodeClient, archive = (archived) ? 1 : 0 });
SqlConnexion.CloseConnexion(cnx);
return(rowNb > 0);
}
catch (Exception e)
{
throw e;
}
}
public static int Relance(BO.LignesConsultations ligne)
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
var query = @"UPDATE LignesConsultations SET RappelEnvoye = 1 WHERE CodeConsultation = @codeConsultation AND NumLigne = @numLigne";
int rowNb = cnx.Execute(query, new { codeConsultation = ligne.Consultation.CodeConsultation, numLigne = ligne.NumLigne });
SqlConnexion.CloseConnexion(cnx);
return(rowNb);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Surcharge de la méthode getAll pour obtenir les archivés ou non
/// </summary>
/// <param name="archived"></param>
/// <returns></returns>
public static List <BO.Clients> GetAll(bool archived)
{
try
{
var query = @"SELECT * FROM Clients c WHERE c.Archive=@archive ORDER BY c.CodeClient";
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.Clients> results = cnx.Query <BO.Clients>(query, new { archive = archived }).ToList <BO.Clients>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
public static List <BO.Vaccins> GetAll()
{
try
{
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.Vaccins> results = cnx.Query <BO.Vaccins>(@"SELECT * FROM Vaccins ORDER BY NomVaccin")
.ToList <BO.Vaccins>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Récupère tout les clients
/// </summary>
/// <returns></returns>
public static List <BO.Clients> GetAll()
{
try
{
var query = @"SELECT * FROM Clients c ORDER BY c.CodeClient";
SqlConnection cnx = DAL.SqlConnexion.OpenConnexion();
List <BO.Clients> results = cnx.Query <BO.Clients>(query).ToList <BO.Clients>();
SqlConnexion.CloseConnexion(cnx);
return(results);
}
catch (Exception e)
{
throw e;
}
}