public static ErrorMessageAndEvelator GetIdError(int id)
{
ErrorMessageAndEvelator error = new ErrorMessageAndEvelator();
using (SqlConnection connection = new SqlConnection(ConnectString))
{
connection.Open();
string sqlQuery = "SELECT Number, Postcode, Street, Locality FROM Evelator WHERE EvelatorID = @EvelatorID";
using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, connection))
{
sqlCommand.Parameters.AddWithValue("@EvelatorID", id);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
error.Number = (dataReader["Number"] == DBNull.Value ? (int?)null : (int)dataReader["Number"]);
error.Postcode = (dataReader["Postcode"] == DBNull.Value
? (int?)null
: (int)dataReader["Postcode"]);
error.Street = (dataReader["Street"] == DBNull.Value ? null : (string)dataReader["Street"]);
error.Locality = (dataReader["Locality"] == DBNull.Value
? null
: (string)dataReader["Locality"]);
}
}
}
return(error);
}
public HttpResponseMessage GetIdInfo([FromBody] int id)
{
HttpResponseMessage response = new HttpResponseMessage();
ErrorMessageAndEvelator error = Database.GetIdError(id);
if (error != null)
{
response.StatusCode = HttpStatusCode.OK;
var jsonContent = JsonConvert.SerializeObject(error);
response.Content = new StringContent(jsonContent);
}
else
{
response.StatusCode = HttpStatusCode.Forbidden;
response.Content = new StringContent("Error when loading from DB");
}
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);
}
public static ErrorMessageAndEvelator GetIdError(int id)
{
ErrorMessageAndEvelator error = new ErrorMessageAndEvelator();
using (SqlConnection connection = new SqlConnection(ConnectString))
{
connection.Open();
string sqlQuery = "SELECT Number, Postcode, Street, Locality FROM Evelator WHERE EvelatorID = @EvelatorID";
using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, connection))
{
sqlCommand.Parameters.AddWithValue("@EvelatorID", id);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
error.Number = (dataReader["Number"] == DBNull.Value ? (int?)null : (int)dataReader["Number"]);
error.Postcode = (dataReader["Postcode"] == DBNull.Value
? (int?)null
: (int)dataReader["Postcode"]);
error.Street = (dataReader["Street"] == DBNull.Value ? null : (string)dataReader["Street"]);
error.Locality = (dataReader["Locality"] == DBNull.Value
? null
: (string)dataReader["Locality"]);
}
}
}
return error;
}
//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;
}
