{/// <summary>
/// Récupère un lecteur d'après son Id.
/// </summary>
/// <param name="affiliateId"></param>
/// <param name="AffToFill"></param>
public static void GetAffiliateById(int affiliateId, ref Affiliate AffToFill)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.GetAffiliateByCardNum", connection))
{
Affiliate affTemp = new Affiliate();
SqlParameter param1 = new SqlParameter("@number", affiliateId);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
sLog.Append("Open");
connection.Open();
SqlDataReader dtReader = command.ExecuteReader();
sLog.Append("Read");
while (dtReader.Read())
{
affTemp.CardNum = dtReader.GetInt32(0);
if (!dtReader.IsDBNull(1))
{
affTemp.CardValidity = dtReader.GetDateTime(1);
}
// else affTemp.CardValidity = default(DateTime);
affTemp.MainLibraryId = dtReader.GetInt32(2);
affTemp.FirstName = dtReader.GetString(3);
affTemp.LastName = dtReader.GetString(4);
if (!dtReader.IsDBNull(5))
{
affTemp.BirthDate = dtReader.GetDateTime(5);
}
}
AffToFill = affTemp;
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// mise à jour d'un client.
/// </summary>
/// <param name="id"></param>
/// <param name="name"></param>
/// <param name="surName"></param>
/// <param name="birthDay"></param>
/// <param name="email"></param>
/// <param name="phone"></param>
public static void UpdteCstmr(int id, string name, string surName, DateTime birthDay, string email, string phone) //, DateTime LastModified
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder _sLog = new StringBuilder();
SqlParameter param0 = new SqlParameter("@id", id);
SqlParameter param1 = new SqlParameter("@name", name);
SqlParameter param2 = new SqlParameter("@surName", surName);
SqlParameter param3 = new SqlParameter("@birthDate", birthDay);
SqlParameter param4 = new SqlParameter("@email", email);
SqlParameter param5 = new SqlParameter("@phone", phone);
SqlParameter[] parameters = { param0, param1, param2, param3, param4, param5 };
try
{
using (SqlCommand command = new SqlCommand("SchCommon.AddCustomer", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
_sLog.Append("Open");
connection.Open();
_sLog.Append("ExecuteNonQuery");
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new CstmEx(ExType.dtaUpdate); // La modification n'a pas pu être effectuée ! \n Veuillez réessayer.
}
if (rowsAffected > 1)
{
throw new CstmEx(ExType.sqlLineCount); // nbre de lignes affectées erroné
}
}
}
#region Catch
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", _sLog);
switch (sqlEx.Number)
{
case 4060:
throw new CstmEx(ExType.badDB, sqlEx); //"Mauvaise base de données"
case 18456:
throw new CstmEx(ExType.badPWD, sqlEx); //"Mauvais mot de passe"
default:
throw new CstmEx(ExType.notHandledSql, sqlEx); //"Erreur SQL non traitée !" L'exception sera rError_Layerancée.
}
}
catch (Exception ex)
{
ex.Data.Add("Log", _sLog);
throw new CstmEx(ExType.dtaUpdate, ex); //"La modification n'a pas pu être effectuée !"
}
#endregion Catch
}
}
/// <summary>
/// Insert un volume (sans les auteurs ! Doit être inclus dans une transaction).
/// </summary>
/// <param name="volumeToIns"></param>
public static void InsertVolume(Volume volumeToIns)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
SqlParameter param1 = new SqlParameter("@Isbn", volumeToIns.Isbn);
SqlParameter param2 = new SqlParameter("@Title", volumeToIns.Title);
SqlParameter param3 = new SqlParameter("@Cover", volumeToIns.Cover);
SqlParameter[] parameters = { param1, param2, param3 };
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.InsertVolume", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
sLog.Append("Open");
connection.Open();
sLog.Append("ExecuteNonQuery");
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 1)
{
throw new EL.CstmError(3); // nbre de lignes affectées erroné
}
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 2601:
goto case 2627;
case 2627:
throw new EL.CstmError(5, sqlEx); //Cet enregistrement existe déjà ! violation d'unicité d'index.
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 12; //" Un problème est survenu à l'ajout du volume ou de son / ses auteur(s) !".
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// Clôture un emprunt.
/// </summary>
/// <param name="EmpruntToCloseID"></param>
/// <param name="LastModified"></param>
public static void CloseEmprunt(int EmpruntToCloseID, DateTime LastModified)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
SqlParameter param1 = new SqlParameter("@Id", EmpruntToCloseID);
SqlParameter param2 = new SqlParameter("@LastModified", LastModified);
SqlParameter[] parameters = { param1, param2 };
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.CloseEmprunt", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
sLog.Append("Open");
connection.Open();
sLog.Append("ExecuteNonQuery");
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new EL.CstmError(13); // La modification n'a pas pu être effectuée ! \n Veuillez réessayer.
}
if (rowsAffected > 1)
{
throw new EL.CstmError(3); // nbre de lignes affectées erroné
}
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 10; //" Un problème est survenu à la modification des données !".
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// Récupération des promos liées à un type véhicule.
/// pour clients et employés.
/// </summary>
/// <param name="vehicleTypeId"></param>
/// <returns>ALL = PromotionModel_Id, VehicleType_Id,[Name],Office_Name,[StartDate],[EndDate],[PercentReduc],[FixedReduc</returns>
public static DataTable GetPromoByVehicle(int vehicleTypeId, string officeName)
{
DataTable _dataToReturn = null;
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder _sLog = new StringBuilder();
SqlParameter param1 = new SqlParameter("@vehicleTypeId", vehicleTypeId);
SqlParameter param2 = new SqlParameter("@officeName", officeName);
try
{
using (SqlCommand command = new SqlCommand("SchCommon.GetPromoByVehicle", connection))
{
DataTable dataTemp = new DataTable();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
command.Parameters.Add(param2);
SqlDataAdapter datadapt = new SqlDataAdapter(command);
_sLog.Append("Open");
connection.Open();
_sLog.Append("Fill");
datadapt.Fill(dataTemp);
_dataToReturn = dataTemp;
}
}
#region Catch
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", _sLog);
switch (sqlEx.Number)
{
case 4060:
throw new CstmEx(ExType.badDB, sqlEx); //"Mauvaise base de données"
case 18456:
throw new CstmEx(ExType.badPWD, sqlEx); //"Mauvais mot de passe"
default:
throw new CstmEx(ExType.notHandledSql, sqlEx); //"Erreur SQL non traitée !" L'exception sera rError_Layerancée.
}
}
catch (Exception ex)
{
ex.Data.Add("Log", _sLog);
throw new CstmEx(ExType.dtaRead, ex); //"Problème à la récupération des données par la DAL !"
}
#endregion Catch
}
return(_dataToReturn);
}
/// <summary>
/// Retourne un volume par son ID.
/// </summary>
/// <param name="VolumeId"></param>
/// <param name="VolToFill"></param>
public static void GetVolumeById(int VolumeId, ref Volume VolToFill)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.GetVolumeById", connection))
{
Volume VolTemp = new Volume();
SqlParameter param1 = new SqlParameter("@Id", VolumeId);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
sLog.Append("Open");
connection.Open();
SqlDataReader dtReader = command.ExecuteReader();
sLog.Append("Read");
while (dtReader.Read())
{
VolTemp.Id = dtReader.GetInt32(0);
VolTemp.Isbn = dtReader.GetString(1);
VolTemp.Title = dtReader.GetString(2);
VolTemp.Cover = dtReader.SafeGetString(3);
VolToFill = VolTemp;
}
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données par la DAL !"
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// Récupère les auteurs correspondants au volume.
/// </summary>
/// <param name="VolumeIsbn"></param>
/// <returns></returns>
public static DataTable GetVolumeAuthors(string VolumeIsbn)
{
DataTable dataToReturn = null;
SqlDataAdapter datadapt = new SqlDataAdapter();
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.GetAuthorByVolumeIsbn", connection))
{
DataTable dataTemp = new DataTable();
SqlParameter param1 = new SqlParameter("@VolumeIsbn", VolumeIsbn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
sLog.Append("Open");
connection.Open();
datadapt.SelectCommand = command;
sLog.Append("Fill");
datadapt.Fill(dataTemp);
dataToReturn = dataTemp;
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données par la DAL !"
throw new EL.CstmError(DefaultError, ex);
}
}
return(dataToReturn);
}
/// <summary>
/// retourne les emprunts du lecteur.
/// Sous forme de datatable.
/// </summary>
/// <param name="cardNum"></param>
/// <param name="emprunts"></param>
public static void GetEmpruntsByCardNum(int cardNum, bool SelectClosed, ref DataTable emprunts)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("[SchAdmin].[GetEmpruntById]", connection))
{
DataTable dataTemp = new DataTable();
SqlParameter param1 = new SqlParameter("@Id", cardNum);
SqlParameter param2 = new SqlParameter("@SelectClosed", SelectClosed);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
command.Parameters.Add(param2);
SqlDataAdapter datadapt = new SqlDataAdapter(command);
sLog.Append("Open");
connection.Open();
sLog.Append("Fill");
datadapt.Fill(dataTemp);
emprunts = dataTemp;
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
}
public static void GetAllAuthorsNames(ref DataTable tableToFill)
{
SqlDataAdapter datadapt = new SqlDataAdapter();
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("[SchAdmin].[GetAllAuthors]", connection))
{
DataTable dataTemp = new DataTable();
command.CommandType = CommandType.StoredProcedure;
sLog.Append("Open");
connection.Open();
datadapt.SelectCommand = command;
sLog.Append("Fill");
datadapt.Fill(dataTemp);
tableToFill = dataTemp;
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// Crée un nouveau client.
/// </summary>
/// <param name="name"></param>
/// <param name="surName"></param>
/// <param name="birthDay"></param>
/// <param name="email"></param>
/// <param name="phone"></param>
/// <returns></returns>
public static int CreateCstmr(string name, string surName, DateTime birthDay, string email, string phone)
{
int _newId = 0;
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder _sLog = new StringBuilder();
SqlParameter param0 = new SqlParameter("@new_ID", SqlDbType.Int, 0); // 0 en output par défaut.
param0.Direction = System.Data.ParameterDirection.Output;
SqlParameter param1 = new SqlParameter("@name", name);
SqlParameter param2 = new SqlParameter("@surName", surName);
SqlParameter param3 = new SqlParameter("@birthDate", birthDay);
SqlParameter param4 = new SqlParameter("@email", email);
SqlParameter param5 = new SqlParameter("@phone", phone);
SqlParameter[] parameters = { param1, param2, param3, param4, param5 };
try
{
using (SqlCommand command = new SqlCommand("SchCommon.AddCustomer", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
_sLog.Append("Open");
connection.Open();
_sLog.Append("ExecuteNonQuery");
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new CstmEx(ExType.dtaWrite); // La création a échoué.
}
if (rowsAffected > 1)
{
throw new CstmEx(ExType.sqlLineCount); // nbre de lignes affectées erroné.
}
_newId = (int)command.Parameters["@new_ID"].Value;
}
}
#region Catch
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", _sLog);
switch (sqlEx.Number)
{
case 4060:
throw new CstmEx(ExType.badDB, sqlEx);
case 18456:
throw new CstmEx(ExType.badPWD, sqlEx);
default:
throw new CstmEx(ExType.notHandledSql, sqlEx);
}
}
catch (Exception ex)
{
ex.Data.Add("Log", _sLog);
throw new CstmEx(ExType.dtaWrite, ex);
}
#endregion Catch
}
return(_newId);
}
/// <summary>
/// Retourne un volume par son ISBN.
/// </summary>
/// <param name="VolumeIsbn"></param>
/// <param name="VolToFill"></param>
public static void GetVolumeByIsbn(string VolumeIsbn, ref Volume VolToFill)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.GetVolumeByIsbn", connection))
{
Volume VolTemp = new Volume();
SqlParameter param1 = new SqlParameter("@isbn", VolumeIsbn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
sLog.Append("Open");
connection.Open();
SqlDataReader dtReader = command.ExecuteReader();
sLog.Append("Read");
while (dtReader.Read())
{
VolTemp.Id = dtReader.GetInt32(0);
VolTemp.Isbn = dtReader.GetString(1);
VolTemp.Title = dtReader.GetString(2);
if (!dtReader.IsDBNull(3))
{
VolTemp.Cover = dtReader.GetString(3);
}
else
{
VolTemp.Cover = string.Empty;
}
}
if (VolTemp.Id != null)
{
DataTable dtAuthors = GetVolumeAuthors(VolTemp.Isbn);
if (dtAuthors != null)
{
List <Author> listauthors = new List <Author>();
foreach (DataRowView row in dtAuthors.DefaultView)
{
Author author = new Author();
author.PersId = (Int32)row["Pers_Id"];
author.FirstName = row["FirstName"].ToString();
author.LastName = row["LastName"].ToString();
listauthors.Add(author);
}
VolTemp.Authors = listauthors;
}
else
{
VolTemp.Authors = null;
}
}
VolToFill = VolTemp;
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// retourne les emprunts du lecteur.
/// Sous forme de liste d'objets Emprunt.
/// </summary>
/// <param name="cardNum"></param>
/// <param name="listToReturn"></param>
public static void GetEmpruntsByCardNum(int cardNum, bool SelectClosed, ref List <Emprunt> listToReturn)
{
DataTable dataTemp = new DataTable();
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("[SchAdmin].[GetEmpruntByAffiliate]", connection))
{
SqlParameter param1 = new SqlParameter("@Id", cardNum);
SqlParameter param2 = new SqlParameter("@SelectClosed", SelectClosed);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
command.Parameters.Add(param2);
SqlDataAdapter datadapt = new SqlDataAdapter(command);
sLog.Append("Open");
connection.Open();
sLog.Append("Fill");
datadapt.Fill(dataTemp);
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
try
{
if (dataTemp.Rows.Count != 0)
{
List <Emprunt> listTemp = new List <Emprunt>();
foreach (DataRowView row in dataTemp.DefaultView)
{
Emprunt emprunt = new Emprunt();
emprunt.Id = (int)row["IdEmprunt"];
emprunt.CardNum = (int)row["CardNum"];
emprunt.ItemId = (int)row["Item_Id"];
emprunt.ItemCode = row["Code"].ToString();
emprunt.LibraryId = (int)row["Item_Id"];
emprunt.LibraryName = row["NameLibrary"].ToString();
emprunt.TarifName = row["NameTarif"].ToString();
emprunt.VolumeTitle = row["Title"].ToString();
emprunt.StartDate = (DateTime)row["StartDate"];
emprunt.Duration = int.Parse(row["Duration"].ToString());
if (row["ReturnDate"] != DBNull.Value)
{
emprunt.ReturnDte = (DateTime)row["ReturnDate"];
}
emprunt.Fee = (decimal)row["Fee"];
emprunt.DailyPenalty = (decimal)row["DailyPenalty"];
emprunt.LastModified = (DateTime)row["LastModified"];
listTemp.Add(emprunt);
}
listToReturn = listTemp;
}
//else throw new EL.CstmError(11); // " Aucun résultat ne correspond à cette recherche !"
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
/// <summary>
/// Retourne un emprunt par le code de l'exemplaire emprunté.
/// </summary>
/// <param name="code"></param>
/// <param name="EmpruntToReturn"></param>
public static void GetEmpruntByCode(string code, bool SelectClosed, ref Emprunt EmpruntToReturn)
{
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder sLog = new StringBuilder();
try
{
using (SqlCommand command = new SqlCommand("SchAdmin.GetEmpruntByCode", connection))
{
Emprunt emprunt = new Emprunt();
SqlParameter param1 = new SqlParameter("@Code", code);
SqlParameter param2 = new SqlParameter("@SelectClosed", SelectClosed);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(param1);
command.Parameters.Add(param2);
sLog.Append("Open");
connection.Open();
SqlDataReader dtReader = command.ExecuteReader();
sLog.Append("Read");
while (dtReader.Read())
{
emprunt.Id = dtReader.GetInt32(0);
emprunt.CardNum = (int)dtReader["CardNum"];
emprunt.ItemId = (int)dtReader["Item_Id"];
emprunt.ItemCode = dtReader["Code"].ToString();
emprunt.LibraryId = (int)dtReader["Library_Id"];
emprunt.LibraryName = dtReader["NameLibrary"].ToString();
emprunt.TarifName = dtReader["NameTarif"].ToString();
emprunt.VolumeTitle = dtReader["Title"].ToString();
emprunt.StartDate = (DateTime)dtReader["StartDate"];
emprunt.Duration = int.Parse(dtReader["Duration"].ToString());
if (dtReader["ReturnDate"] != DBNull.Value)
{
emprunt.ReturnDte = (DateTime)dtReader["ReturnDate"];
}
emprunt.Fee = (decimal)dtReader["Fee"];
emprunt.DailyPenalty = (decimal)dtReader["DailyPenalty"];
emprunt.LastModified = (DateTime)dtReader["LastModified"];
}
EmpruntToReturn = emprunt;
}
}
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", sLog);
int DefaultSqlError = 6; //"Erreur SQL non traitée !" L'exception sera relancée.
switch (sqlEx.Number)
{
case 4060:
throw new EL.CstmError(1, sqlEx); //"Mauvaise base de données"
case 18456:
throw new EL.CstmError(2, sqlEx); //"Mauvais mot de passe"
default:
throw new EL.CstmError(DefaultSqlError, sqlEx); //"Erreur SQL non traitée !" L'exception sera relancée.
}
}
catch (Exception ex)
{
int DefaultError = 7; //"Problème à la récupération des données !"
throw new EL.CstmError(DefaultError, ex);
}
}
}
/// <summary>
/// Met à jour / Clôture une réservation.
/// Seul un employé peut le faire
/// </summary>
/// <param name="id"></param>
/// <param name="vehicle_Id"></param>
/// <param name="customer_Id"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="toPay"></param>
/// <param name="isClosed"></param>
/// <param name="paid"></param>
public static void UpdateRent(int id, int vehicle_Id, int customer_Id, DateTime startDate, DateTime endDate, decimal toPay, bool isClosed, decimal paid)
{
int _newId = 0;
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder _sLog = new StringBuilder();
SqlParameter param0 = new SqlParameter("@id", id);
SqlParameter param1 = new SqlParameter("@Vehicle_Id", vehicle_Id);
SqlParameter param2 = new SqlParameter("@Customer_Id", customer_Id);
SqlParameter param3 = new SqlParameter("@StartDate", startDate);
SqlParameter param4 = new SqlParameter("@EndDate", endDate);
SqlParameter param5 = new SqlParameter("@ToPay", toPay);
SqlParameter param6 = new SqlParameter("@isClosed", isClosed);
SqlParameter param7 = new SqlParameter("@Paid", paid);
SqlParameter[] parameters = { param0, param1, param2, param3, param4, param5, param6, param7 };
try
{
using (SqlCommand command = new SqlCommand(cmdText: "SchEmployee.UpdateReservation", connection: connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
_sLog.Append("Open");
connection.Open();
_sLog.Append("ExecuteNonQuery");
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new CstmEx(ExType.dtaUpdate);
}
if (rowsAffected > 1)
{
throw new CstmEx(ExType.sqlLineCount);
}
_newId = (int)command.Parameters["@new_ID"].Value;
}
}
#region Catch
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", _sLog);
switch (sqlEx.Number)
{
case 4060:
throw new CstmEx(ExType.badDB, sqlEx);
case 18456:
throw new CstmEx(ExType.badPWD, sqlEx);
default:
throw new CstmEx(ExType.notHandledSql, sqlEx);
}
}
catch (Exception ex)
{
ex.Data.Add("Log", _sLog);
throw new CstmEx(ExType.dtaUpdate, ex);
}
#endregion Catch
}
}
/// <summary>
/// Crée une réservation (Rent).
/// différentie le schéma utilisé si un n° d'employé à été renseigné ou non.
/// Un client ne peut pas non plus changer le montant de 0 pour "paid".
/// </summary>
/// <param name="vehicle_Id"></param>
/// <param name="customer_Id"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="toPay"></param>
/// <param name="paid"></param>
/// <param name="employeeID"></param>
/// <returns></returns>
public static int CreateRent(int vehicle_Id, int customer_Id, DateTime startDate, DateTime endDate, decimal toPay, decimal paid = 0, int employeeID = 0)
{
int _newId = 0;
using (SqlConnection connection = UtilsDAL.GetConnection())
{
StringBuilder _sLog = new StringBuilder();
SqlParameter param0 = new SqlParameter("@new_ID", SqlDbType.Int, 0); // 0 en output par défaut.
param0.Direction = System.Data.ParameterDirection.Output;
SqlParameter param1 = new SqlParameter("@Vehicle_Id", vehicle_Id);
SqlParameter param2 = new SqlParameter("@Customer_Id", customer_Id);
SqlParameter param3 = new SqlParameter("@StartDate", startDate);
SqlParameter param4 = new SqlParameter("@EndDate", endDate);
SqlParameter param5 = new SqlParameter("@ToPay", toPay);
SqlParameter param6 = new SqlParameter("@Paid", paid);
SqlParameter param7 = new SqlParameter("@Employee_Id", paid);
SqlParameter[] parameters = { param0, param1, param2, param3, param4, param5 };
string cmdText = "\"SchCustomer.AddReservation\"";
if (employeeID != 0)
{
parameters = new SqlParameter[] { param1, param2, param3, param4, param5, param6, param7 };
cmdText = "\"SchEmployee.AddReservation\"";
}
try
{
using (SqlCommand command = new SqlCommand(cmdText, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
_sLog.Append("Open");
connection.Open();
_sLog.Append("ExecuteNonQuery");
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new CstmEx(ExType.dtaWrite);
}
if (rowsAffected > 1)
{
throw new CstmEx(ExType.sqlLineCount);
}
_newId = (int)command.Parameters["@new_ID"].Value;
}
}
#region Catch
catch (SqlException sqlEx)
{
sqlEx.Data.Add("Log", _sLog);
switch (sqlEx.Number)
{
case 4060:
throw new CstmEx(ExType.badDB, sqlEx);
case 18456:
throw new CstmEx(ExType.badPWD, sqlEx);
default:
throw new CstmEx(ExType.notHandledSql, sqlEx);
}
}
catch (Exception ex)
{
ex.Data.Add("Log", _sLog);
throw new CstmEx(ExType.dtaWrite, ex);
}
#endregion Catch
}
return(_newId);
}