public int SavePenaltyWorkCollection(PenaltyWorkCollectionModel penaltyWcModel) { int rowsAffected = 0; var sqlText = @"insert into t_pnt_penalty_work_collection_mapping values (@penalty_code, @work_collection_id, @created_date, @created_by, @last_modified, @modified_by)"; using (SqlConnection connection = new SqlConnection(_conString)) { using (SqlCommand command = new SqlCommand(sqlText, connection)) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@penalty_code", penaltyWcModel.penalty_code); command.Parameters.AddWithValue("@work_collection_id", penaltyWcModel.work_collection_id); command.Parameters.AddWithValue("@created_date", penaltyWcModel.created_date); command.Parameters.AddWithValue("@created_by", penaltyWcModel.created_by); command.Parameters.AddWithValue("@last_modified", SqlDbType.DateTime).SqlValue = DBNull.Value; //penaltymodel.last_modified; command.Parameters.AddWithValue("@modified_by", DBNull.Value); if (connection.State == ConnectionState.Closed) { connection.Open(); } rowsAffected = command.ExecuteNonQuery(); } } return(rowsAffected); }
public int Save(t_pnt_penalty_definition penaltymodel, PenaltyWorkCollectionModel penaltyWCModel = null) { using (var dbContext = new IRSAdhocContext()) { using (var tranx = dbContext.Database.BeginTransaction()) { var query = dbContext.t_pnt_penalty_definition.Find("", ""); if (query != null) { dbContext.Entry(query).State = System.Data.Entity.EntityState.Modified; } else { dbContext.t_pnt_penalty_definition.Add(penaltymodel); } tranx.Commit(); } } return(1); }
public int SavePenalty(PenaltiesModel penaltymodel, PenaltyWorkCollectionModel penaltyWCModel = null) { //penalty_limit, ,@penalty_limit int rowsAffected = 0; var sqlText2 = ""; var sqlText = @"Insert into t_pnt_penalty_definition(penalty_code,penalty_desc,ri_type_id,penalty_type,penalty_freq,penalty_freq_unit,delivery_deadline_day,delivery_deadline_hr, delivery_deadline_min,penalty_value,start_validity_date,end_validity_date,created_date,created_by) values(@penalty_code,@penalty_desc,@ri_type_id,@penalty_type,@penalty_freq,@penalty_freq_unit,@delivery_deadline_day, @delivery_deadline_hr,@delivery_deadline_min,@penalty_value,@start_validity_date,@end_validity_date,@created_date,@created_by)"; sqlText = @"INSERT INTO dbo.t_pnt_penalty_definition (penalty_code,penalty_desc,ri_type_id,frequency,penalty_type,penalty_freq,penalty_freq_unit,delivery_deadline_day ,delivery_deadline_hr,delivery_deadline_min,min_limit_accepted,max_limit_accepted,penalty_value,penalty_per_unit,failed_penalty_value,start_validity_date,end_validity_date ,created_date,created_by,last_modified,modified_by) VALUES (@penalty_code,@penalty_desc,@ri_type_id,@frequency,@penalty_type,@penalty_freq,@penalty_freq_unit ,@delivery_deadline_day,@delivery_deadline_hr,@delivery_deadline_min,@min_limit_accepted,@max_limit_accepted,@penalty_value,@penalty_per_unit,@failed_penalty_value ,@start_validity_date,@end_validity_date,@created_date,@created_by,@last_modified,@modified_by)"; using (TransactionScope transScope = new TransactionScope(TransactionScopeOption.Required)) { using (SqlCommand command = new SqlCommand(sqlText, DatabaseOps.OpenSqlConnection())) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@penalty_code", penaltymodel.penalty_code); command.Parameters.AddWithValue("@penalty_desc", penaltymodel.penalty_desc); command.Parameters.AddWithValue("@ri_type_id", penaltymodel.ri_type_id); command.Parameters.AddWithValue("@frequency", penaltymodel.frequncy); command.Parameters.AddWithValue("@penalty_type", penaltymodel.penalty_type); command.Parameters.AddWithValue("@penalty_freq", penaltymodel.penalty_freq); command.Parameters.AddWithValue("@penalty_freq_unit", penaltymodel.penalty_freq_unit); //min_limit_accepted,max_limit_accepted,failed_penalty_value, #region conditional check if (penaltymodel.delivery_deadline_day == null) { command.Parameters.AddWithValue("@delivery_deadline_day", DBNull.Value); } else { command.Parameters.AddWithValue("@delivery_deadline_day", penaltymodel.delivery_deadline_day); } if (penaltymodel.delivery_deadline_hr == null) { command.Parameters.AddWithValue("@delivery_deadline_hr", DBNull.Value); } else { command.Parameters.AddWithValue("@delivery_deadline_hr", penaltymodel.delivery_deadline_hr); } if (penaltymodel.delivery_deadline_min == null) { command.Parameters.Add("@delivery_deadline_min", DBNull.Value); } else { command.Parameters.Add("@delivery_deadline_min", penaltymodel.delivery_deadline_min); } #endregion if (String.IsNullOrWhiteSpace(penaltymodel.min_limit_accepted)) { command.Parameters.AddWithValue("@min_limit_accepted", penaltymodel.min_limit_accepted); } else { command.Parameters.Add("@min_limit_accepted", penaltymodel.min_limit_accepted); } if (String.IsNullOrWhiteSpace(penaltymodel.max_limit_accepted)) { command.Parameters.AddWithValue("@max_limit_accepted", penaltymodel.max_limit_accepted); } else { command.Parameters.Add("@max_limit_accepted", penaltymodel.max_limit_accepted); } command.Parameters.AddWithValue("@penalty_value", penaltymodel.penalty_value); command.Parameters.AddWithValue("@penalty_per_unit", penaltymodel.penalty_per_unit.Equals("0") ? false : true); if (String.IsNullOrWhiteSpace(penaltymodel.failed_penalty_value)) { command.Parameters.Add("@failed_penalty_value", DBNull.Value); } else { command.Parameters.AddWithValue("@failed_penalty_value", penaltymodel.failed_penalty_value); } command.Parameters.Add("@start_validity_date", SqlDbType.DateTime).SqlValue = penaltymodel.start_validity_date.ToShortDateString(); if (penaltymodel.end_validity_date == null || penaltymodel.end_validity_date == DateTime.MinValue) { command.Parameters.Add("@end_validity_date", SqlDbType.DateTime).SqlValue = DBNull.Value; } else { command.Parameters.Add("@end_validity_date", SqlDbType.DateTime).SqlValue = penaltymodel.end_validity_date; } command.Parameters.AddWithValue("@created_date", SqlDbType.DateTime).SqlValue = penaltymodel.created_date; command.Parameters.AddWithValue("@created_by", penaltymodel.created_by); command.Parameters.Add("@last_modified", SqlDbType.DateTime).SqlValue = DBNull.Value; command.Parameters.AddWithValue("@modified_by", SqlDbType.NVarChar).SqlValue = DBNull.Value; if (command.Connection.State == ConnectionState.Closed) { command.Connection.Open(); } rowsAffected = command.ExecuteNonQuery(); if (penaltyWCModel != null) { //rowsAffected += new PenaltyWorkCollection().SavePenaltyWorkCollection(penaltyWCModel); if (penaltyWCModel.work_collection_id.Count > 0) //More than one work collection was added to only one penalty { foreach (var key in penaltyWCModel.work_collection_id.Keys) { sqlText2 = @"insert into t_pnt_penalty_work_collection_mapping(penalty_code,work_collection_id,created_date,created_by) values (@penalty_code, @work_collection_id, @created_date, @created_by)"; using (SqlCommand command2 = new SqlCommand(sqlText2, DatabaseOps.OpenSqlConnection())) { command2.CommandType = CommandType.Text; command2.Parameters.AddWithValue("@penalty_code", penaltyWCModel.penalty_code); command2.Parameters.AddWithValue("@work_collection_id", key); command2.Parameters.AddWithValue("@created_date", penaltyWCModel.created_date); command2.Parameters.AddWithValue("@created_by", penaltyWCModel.created_by); //command2.Parameters.AddWithValue("@last_modified", SqlDbType.DateTime).SqlValue = DBNull.Value; //penaltymodel.last_modified; //command2.Parameters.AddWithValue("@modified_by", DBNull.Value); if (command2.Connection.State == ConnectionState.Closed) { command2.Connection.Open(); } rowsAffected += command2.ExecuteNonQuery(); } } } } } transScope.Complete(); return(rowsAffected); } }