Example #1
0
 /// <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;
 }
Example #3
0
        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;
 }
Example #5
0
        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);
        }
Example #6
0
        //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);
        }
Example #7
0
        //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;
         }
Example #8
0
         /// <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;
             }

         }