/// <summary>
/// Method to Update existing evelator according to evelatorID
/// </summary>
/// <remarks> Control if evelatorID exists. If it is not, throw exception. If newEvelatorID parameter is not null, then control if newEvelatorID in database is null
/// or dont exists NewEvelatorID record here. In other case throw exeption.
/// All parameter except evelatorID should be null</remarks>
/// <param name="evelatorID"> New evelator id record. Shouldnt be null</param>
/// <param name="postcode">New postcode record</param>
/// <param name="street">New street record</param>
/// <param name="number">New number record</param>
/// <param name="locality">New locality record</param>
/// <param name="newEvelatorID">New newEvelator record</param>
public static void UpdateEvelator(filterModel filter)
//Finish update SerialID. If exits error with this SerialID, I have to update serialID of ther.
{
using (SqlConnection connection = new SqlConnection(ConnectString))
{
try
{
connection.Open();
///Control existing record (evelatorID) existuje zaznam, ktery chceme aktualizovat? Existuje ID?
if (TestRecordIdExists(filter.EvelatorID, connection) == 0)
{
throw new UpdateRecordDontExistException();
}
if (filter.NewEvelatorID.HasValue)
{
if (filter.EvelatorID == filter.NewEvelatorID.Value)
{
throw new UpdateIdIsSame();
}
if (TestRecordIdExists(filter.NewEvelatorID.Value, connection) == 0) //existuje new evelator id?
{
throw new UpdateRecordDontExistException();
}
///Control if Evelator according to EvelatorID have NewEvelatorID record and where points. (jeslti uz tam neni zaznam)
if (TestRecordIdPoints(filter.EvelatorID, connection) != null)
{
throw new UpdateRecordPoint();
}
///Control duplicate newEvelatorID
if (TestDuplicateNewRecordId(filter.NewEvelatorID.Value, connection) != null)
{
throw new UpdateDuplicateKeyException();
}
}
///Update record
string sqlQuery = "UPDATE Evelator SET Postcode = @PostCode, Street = @Street, Number = @Number, Locality = @Locality, NewEvelatorID = @NewEvelatorID WHERE EvelatorID = @EvelatorID";
using (
SqlCommand sqlCommand =
new SqlCommand(sqlQuery, connection))
{
sqlCommand.Parameters.AddWithValue("@EvelatorID", filter.EvelatorID);
sqlCommand.Parameters.AddWithValue("@PostCode",
filter.PostCode.HasValue ? (object)filter.PostCode.Value : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@Street", filter.Street != null ? (object)filter.Street : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@Number",
filter.Number.HasValue ? (object)filter.Number.Value : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@Locality",
filter.Locality != null ? (object)filter.Locality : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@NewEvelatorID",
filter.NewEvelatorID.HasValue ? (object)filter.NewEvelatorID.Value : DBNull.Value);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
return;
}
}
public HttpResponseMessage GetFilterErrors(filterModel filter)
{
HttpResponseMessage response = new HttpResponseMessage();
List<ErrorMessageAndEvelator> errors = Database.GetAllFilterErrors(filter);
if (errors != null)
{
response.StatusCode = HttpStatusCode.OK;
var jsonContent = JsonConvert.SerializeObject(errors);
response.Content = new StringContent(jsonContent);
}
else
{
response.StatusCode = HttpStatusCode.BadRequest;
response.Content = new StringContent("Error when loading from DB");
}
return response;
}
public HttpResponseMessage GetFilterErrors(filterModel filter)
{
HttpResponseMessage response = new HttpResponseMessage();
List <ErrorMessageAndEvelator> errors = Database.GetAllFilterErrors(filter);
if (errors != null)
{
response.StatusCode = HttpStatusCode.OK;
var jsonContent = JsonConvert.SerializeObject(errors);
response.Content = new StringContent(jsonContent);
}
else
{
response.StatusCode = HttpStatusCode.BadRequest;
response.Content = new StringContent("Error when loading from DB");
}
return(response);
}
public HttpResponseMessage UpdateEvelator(filterModel filter)
{
HttpResponseMessage response = new HttpResponseMessage();
try
{
Database.UpdateEvelator(filter);
response.StatusCode = HttpStatusCode.OK;
}
catch (UpdateException e) //rozdelit podle vyjimek a vyzkouset.
{
response.StatusCode = HttpStatusCode.BadRequest;
response.Content = new StringContent(e.message);
}
catch (Exception e)
{
response.StatusCode = HttpStatusCode.BadRequest;
response.Content = new StringContent(e.Message);
}
return response;
}
public HttpResponseMessage UpdateEvelator(filterModel filter)
{
HttpResponseMessage response = new HttpResponseMessage();
try
{
Database.UpdateEvelator(filter);
response.StatusCode = HttpStatusCode.OK;
}
catch (UpdateException e) //rozdelit podle vyjimek a vyzkouset.
{
response.StatusCode = HttpStatusCode.BadRequest;
response.Content = new StringContent(e.message);
}
catch (Exception e)
{
response.StatusCode = HttpStatusCode.BadRequest;
response.Content = new StringContent(e.Message);
}
return(response);
}
//todo predelat dotaz
//+nacitani erroru a statu tak jako u getAllErrors
public static List <ErrorMessageAndEvelator> GetAllFilterErrors(filterModel filter)
{
List <ErrorMessageAndEvelator> errors = new List <ErrorMessageAndEvelator>();
using (SqlConnection connection = new SqlConnection(ConnectString))
{
connection.Open();
string sqlQuery =
@"select f.OrderID, f.EvelatorID, f.Floor, f.TimeStamp, Evelator.Postcode, Evelator.Street, Evelator.Number, Evelator.Locality, Evelator.NewEvelatorID
from
(select f.OrderID, f.EvelatorID, f.Floor, f.TimeStamp
from
(select EvelatorID, max(TimeStamp) as maxtime
from
EvelatorMessage
group by EvelatorID
) as x inner join EvelatorMessage as f on f.EvelatorID = x.EvelatorID and f.TimeStamp = x.maxtime
)as f, Evelator
where Evelator.EvelatorID = f.EvelatorID";
if (filter.Number.HasValue)
{
sqlQuery += " AND Evelator.Number = " + filter.Number.ToString();
}
if (filter.PostCode.HasValue)
{
sqlQuery += " AND Evelator.Postcode = " + filter.PostCode.ToString();
}
if (!String.IsNullOrEmpty(filter.Locality))
{
sqlQuery += " AND Evelator.Locality = '" + filter.Locality + "'";
}
if (!String.IsNullOrEmpty(filter.Street))
{
sqlQuery += " AND Evelator.Street = '" + filter.Street + "'";
}
sqlQuery += ";";
using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, connection))
{
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
ErrorMessageAndEvelator error = new ErrorMessageAndEvelator();
error.EvelatorID = (int)dataReader["EvelatorID"];
error.Floor = (int)dataReader["Floor"];
error.TimeStamp = (System.DateTime)dataReader["TimeStamp"];
error.Number = (dataReader["Number"] == DBNull.Value ? (int?)null : (int)dataReader["Number"]);
error.Postcode = (dataReader["Postcode"] == DBNull.Value
? (int?)null
: (int)dataReader["Postcode"]);
error.NewEvelatorID = (dataReader["NewEvelatorID"] == DBNull.Value
? (int?)null
: (int)dataReader["NewEvelatorID"]);
error.Street = (dataReader["Street"] == DBNull.Value ? null : (string)dataReader["Street"]);
error.Locality = (dataReader["Locality"] == DBNull.Value
? null
: (string)dataReader["Locality"]);
error.IsNull = (dataReader["Number"] == DBNull.Value || dataReader["Postcode"] == DBNull.Value ||
dataReader["Street"] == DBNull.Value || dataReader["Locality"] == DBNull.Value
? 1
: 0);
error.OrderId = (int)dataReader["OrderID"];
errors.Add(error);
}
dataReader.Close();
foreach (var error in errors)
{
ErrorMessageAndEvelator referenceMessage = errors.Find(x => x.OrderId == error.OrderId);
using (SqlCommand sqlCommandErr = new SqlCommand("SELECT ErrorCode FROM Error WHERE MessageID = " + error.OrderId, connection))
{
SqlDataReader dataReaderError = sqlCommandErr.ExecuteReader();
referenceMessage.Errors = new List <int>();
while (dataReaderError.Read())
{
referenceMessage.Errors.Add((int)dataReaderError["ErrorCode"]);
}
dataReaderError.Close();
}
using (SqlCommand sqlCommandState = new SqlCommand("SELECT StateCode FROM State WHERE MessageID = " + error.OrderId, connection))
{
SqlDataReader dataReaderState = sqlCommandState.ExecuteReader();
referenceMessage.States = new List <int>();
while (dataReaderState.Read())
{
referenceMessage.States.Add((int)dataReaderState["StateCode"]);
}
dataReaderState.Close();
}
}
}
}
return(errors);
}
//todo predelat dotaz
//+nacitani erroru a statu tak jako u getAllErrors
public static List<ErrorMessageAndEvelator> GetAllFilterErrors(filterModel filter)
{
List<ErrorMessageAndEvelator> errors = new List<ErrorMessageAndEvelator>();
using (SqlConnection connection = new SqlConnection(ConnectString))
{
connection.Open();
string sqlQuery =
@"select f.OrderID, f.EvelatorID, f.Floor, f.TimeStamp, Evelator.Postcode, Evelator.Street, Evelator.Number, Evelator.Locality, Evelator.NewEvelatorID
from
(select f.OrderID, f.EvelatorID, f.Floor, f.TimeStamp
from
(select EvelatorID, max(TimeStamp) as maxtime
from
EvelatorMessage
group by EvelatorID
) as x inner join EvelatorMessage as f on f.EvelatorID = x.EvelatorID and f.TimeStamp = x.maxtime
)as f, Evelator
where Evelator.EvelatorID = f.EvelatorID";
if (filter.Number.HasValue)
{
sqlQuery += " AND Evelator.Number = " + filter.Number.ToString() ;
}
if (filter.PostCode.HasValue)
{
sqlQuery += " AND Evelator.Postcode = " + filter.PostCode.ToString();
}
if (!String.IsNullOrEmpty(filter.Locality))
{
sqlQuery += " AND Evelator.Locality = '" + filter.Locality + "'";
}
if (!String.IsNullOrEmpty(filter.Street))
{
sqlQuery += " AND Evelator.Street = '" + filter.Street + "'";
}
sqlQuery += ";";
using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, connection))
{
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
ErrorMessageAndEvelator error = new ErrorMessageAndEvelator();
error.EvelatorID = (int) dataReader["EvelatorID"];
error.Floor = (int) dataReader["Floor"];
error.TimeStamp = (System.DateTime) dataReader["TimeStamp"];
error.Number = (dataReader["Number"] == DBNull.Value ? (int?) null : (int) dataReader["Number"]);
error.Postcode = (dataReader["Postcode"] == DBNull.Value
? (int?) null
: (int) dataReader["Postcode"]);
error.NewEvelatorID = (dataReader["NewEvelatorID"] == DBNull.Value
? (int?) null
: (int) dataReader["NewEvelatorID"]);
error.Street = (dataReader["Street"] == DBNull.Value ? null : (string) dataReader["Street"]);
error.Locality = (dataReader["Locality"] == DBNull.Value
? null
: (string) dataReader["Locality"]);
error.IsNull = (dataReader["Number"] == DBNull.Value || dataReader["Postcode"] == DBNull.Value ||
dataReader["Street"] == DBNull.Value || dataReader["Locality"] == DBNull.Value
? 1
: 0);
error.OrderId = (int)dataReader["OrderID"];
errors.Add(error);
}
dataReader.Close();
foreach (var error in errors)
{
ErrorMessageAndEvelator referenceMessage = errors.Find(x => x.OrderId == error.OrderId);
using (SqlCommand sqlCommandErr = new SqlCommand("SELECT ErrorCode FROM Error WHERE MessageID = " + error.OrderId, connection))
{
SqlDataReader dataReaderError = sqlCommandErr.ExecuteReader();
referenceMessage.Errors = new List<int>();
while (dataReaderError.Read())
{
referenceMessage.Errors.Add((int)dataReaderError["ErrorCode"]);
}
dataReaderError.Close();
}
using (SqlCommand sqlCommandState = new SqlCommand("SELECT StateCode FROM State WHERE MessageID = " + error.OrderId, connection))
{
SqlDataReader dataReaderState = sqlCommandState.ExecuteReader();
referenceMessage.States = new List<int>();
while (dataReaderState.Read())
{
referenceMessage.States.Add((int)dataReaderState["StateCode"]);
}
dataReaderState.Close();
}
}
}
}
return errors;
}
/// <summary>
/// Method to Update existing evelator according to evelatorID
/// </summary>
/// <remarks> Control if evelatorID exists. If it is not, throw exception. If newEvelatorID parameter is not null, then control if newEvelatorID in database is null
/// or dont exists NewEvelatorID record here. In other case throw exeption.
/// All parameter except evelatorID should be null</remarks>
/// <param name="evelatorID"> New evelator id record. Shouldnt be null</param>
/// <param name="postcode">New postcode record</param>
/// <param name="street">New street record</param>
/// <param name="number">New number record</param>
/// <param name="locality">New locality record</param>
/// <param name="newEvelatorID">New newEvelator record</param>
public static void UpdateEvelator(filterModel filter)
//Finish update SerialID. If exits error with this SerialID, I have to update serialID of ther.
{
using (SqlConnection connection = new SqlConnection(ConnectString))
{
try
{
connection.Open();
///Control existing record (evelatorID) existuje zaznam, ktery chceme aktualizovat? Existuje ID?
if (TestRecordIdExists(filter.EvelatorID, connection) == 0)
{
throw new UpdateRecordDontExistException();
}
if (filter.NewEvelatorID.HasValue)
{
if (filter.EvelatorID == filter.NewEvelatorID.Value)
{
throw new UpdateIdIsSame();
}
if (TestRecordIdExists(filter.NewEvelatorID.Value, connection) == 0)//existuje new evelator id?
{
throw new UpdateRecordDontExistException();
}
///Control if Evelator according to EvelatorID have NewEvelatorID record and where points. (jeslti uz tam neni zaznam)
if (TestRecordIdPoints(filter.EvelatorID, connection) != null)
{
throw new UpdateRecordPoint();
}
///Control duplicate newEvelatorID
if (TestDuplicateNewRecordId(filter.NewEvelatorID.Value, connection) != null)
{
throw new UpdateDuplicateKeyException();
}
}
///Update record
string sqlQuery = "UPDATE Evelator SET Postcode = @PostCode, Street = @Street, Number = @Number, Locality = @Locality, NewEvelatorID = @NewEvelatorID WHERE EvelatorID = @EvelatorID";
using (
SqlCommand sqlCommand =
new SqlCommand(sqlQuery, connection))
{
sqlCommand.Parameters.AddWithValue("@EvelatorID", filter.EvelatorID);
sqlCommand.Parameters.AddWithValue("@PostCode",
filter.PostCode.HasValue ? (object) filter.PostCode.Value : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@Street",filter.Street != null ? (object) filter.Street : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@Number",
filter.Number.HasValue ? (object) filter.Number.Value : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@Locality",
filter.Locality != null ? (object) filter.Locality : DBNull.Value);
sqlCommand.Parameters.AddWithValue("@NewEvelatorID",
filter.NewEvelatorID.HasValue ? (object) filter.NewEvelatorID.Value : DBNull.Value);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception)
{
throw ;
}
return;
}
}