public Case GetCustomersCase(int id, string mail)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@id", id, DbType.Int32);
dal.AddParameter("@mail", mail, DbType.String);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case] WHERE [id] = @id AND [customer_id] = (SELECT [id] FROM [vwCustomer] WHERE [mail] = @mail)");
dal.ClearParameters();
if (dt.Rows.Count != 1)
{
throw new DoesNotExistException(typeof(Case));
}
else
{
return(MapCase(dt.Rows[0]));
}
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Category> GetCategories(bool fullAssociation = false)
{
try
{
DataTable dt = new DataAccessLayer().ExecuteDataTable("SELECT * FROM category");
return(dt.AsEnumerable().Select(r => MapCategory(r, fullAssociation)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Case> GetCases()
{
try
{
DataAccessLayer dal = new DataAccessLayer();
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case]");
return(dt.AsEnumerable().Select(r => MapCase(r)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Supporter> GetSupporters()
{
try
{
DataAccessLayer dal = new DataAccessLayer();
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [vwSupporter]");
return(dt.AsEnumerable().Select(r => MapSupporter(r)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public void CaseChangeStatus(int caseId, int statusId, int supporterId)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@caseId", caseId, DbType.Int32);
dal.AddParameter("@statusId", statusId, DbType.Int32);
dal.AddParameter("@editingSupporterId", supporterId, DbType.Int32);
dal.ExecuteStoredProcedure("CaseChangeStatus");
dal.ClearParameters();
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public void CreateComment(int caseId, int personId, string text)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@caseId", caseId, DbType.Int32);
dal.AddParameter("@personId", personId, DbType.Int32);
dal.AddParameter("@text", text, DbType.String);
dal.ExecuteStoredProcedure("CommentCreate");
dal.ClearParameters();
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Comment> GetComments(int caseId)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@caseId", caseId, DbType.Int32);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [comment] WHERE [case_id] = @caseId");
dal.ClearParameters();
return(dt.AsEnumerable().Select(r => MapComment(r)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Subcategory> GetSubcategories(int categoryId)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@category_id", categoryId, DbType.Int32);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM subcategory WHERE [category_id] = @category_id");
dal.ClearParameters();
return(dt.AsEnumerable().Select(r => MapSubcategory(r)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public Category GetCategoryForSubcategory(int subcategoryId, bool fullAssociation = false)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@subcategoryId", subcategoryId, DbType.Int32);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [category] WHERE [id] = (SELECT [category_id] FROM [subcategory] WHERE [subcategory].id = @subcategoryId)");
dal.ClearParameters();
return(dt.Rows.Count == 1 ? MapCategory(dt.Rows[0], fullAssociation) : null);
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public void CaseEdit(Case c, int editingSupporterId)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@caseId", c.Id, DbType.Int32);
dal.AddParameter("@description", c.Description, DbType.String);
dal.AddParameter("@priority", c.Priority, DbType.Int32);
dal.AddParameter("@subcategoryId", c.Subcategory.Id, DbType.Int32);
dal.AddParameter("@operatingSystem", c.OperatingSystem, DbType.String);
//The editing supporter. Not the supporter assigned to the case.
dal.AddParameter("@editingSupporterId", editingSupporterId, DbType.Int32);
dal.ExecuteStoredProcedure("CaseEdit");
dal.ClearParameters();
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Case> GetCasesForSupporter(int supporterId)
{
//No reason to query DB if we know that no records exist
if (supporterId <= 0)
{
return(new Case[0]);
}
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@supporterId", supporterId, DbType.Int32);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case] WHERE [supporter_id] = @supporterId");
dal.ClearParameters();
return(dt.AsEnumerable().Select(r => MapCase(r)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public IEnumerable <Case> GetCasesForCustomer(string mail)
{
//No reason to query DB if we know that no records exist
if (String.IsNullOrEmpty(mail))
{
return(new Case[0]);
}
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@mail", mail, DbType.String);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [case] WHERE [customer_id] = (SELECT [Id] FROM [vwCustomer] WHERE [mail] = @mail)");
dal.ClearParameters();
return(dt.AsEnumerable().Select(r => MapCase(r)));
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public int CaseCreate(Case c)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@description", c.Description, DbType.String);
dal.AddParameter("@priority", c.Priority, DbType.String);
dal.AddParameter("@subcategoryId", c.Subcategory.Id, DbType.Int32);
dal.AddParameter("@operatingSystem", c.OperatingSystem, DbType.String);
dal.AddParameter("@customerId", c.Customer.Id, DbType.Int32);
dal.AddParameter("@newId", null, DbType.Int32, ParameterDirection.Output);
dal.ExecuteStoredProcedure("CaseCreate");
int newId = Int32.Parse(dal.GetParameter("@newId").Value.ToString());
dal.ClearParameters();
return(newId);
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public Person GetPerson(int id)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@id", id, DbType.Int32);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [vwPerson] WHERE [id] = @id");
dal.ClearParameters();
if (dt.Rows.Count == 1)
{
return(MapPerson(dt.Rows[0]));
}
else
{
throw new DoesNotExistException(typeof(Person));
}
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
public Customer GetCustomer(string mail)
{
try
{
DataAccessLayer dal = new DataAccessLayer();
dal.AddParameter("@mail", mail, DbType.String);
DataTable dt = dal.ExecuteDataTable("SELECT * FROM [vwCustomer] WHERE [mail] = @mail");
dal.ClearParameters();
if (dt.Rows.Count == 1)
{
return(MapCustomer(dt.Rows[0]));
}
else
{
throw new DoesNotExistException(typeof(Customer));
}
}
catch (Exception ex)
{
throw DataExceptionCheck.CheckException(ex);
}
}
