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; }