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