public int InserCalendarStage(CalendarStage calendarStage)
{
var sql = $@"INSERT INTO [pq].[CalendarStage] ([SortOrder],[Name],[StageType], [IsServiceDefault],[CalendarStageConfigId]) output INSERTED.ID
VALUES (@SortOrder,@Name, @StageType, @IsServiceDefault, @CalendarStageConfigId)";
int result = 0;
using (SqlConnection connection = new SqlConnection(_settings.QFlowConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
{
sqlCommand.Parameters.Add("@SortOrder", SqlDbType.Int);
sqlCommand.Parameters["@SortOrder"].Value = calendarStage.SortOrder;
sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar);
sqlCommand.Parameters["@Name"].Value = calendarStage.Name;
sqlCommand.Parameters.Add("@StageType", SqlDbType.Int);
sqlCommand.Parameters["@StageType"].Value = (int)calendarStage.StageType;
sqlCommand.Parameters.Add("@IsServiceDefault", SqlDbType.Int);
sqlCommand.Parameters["@IsServiceDefault"].Value = calendarStage.IsServiceDefault ? 1 :0;
sqlCommand.Parameters.Add("@CalendarStageConfigId", SqlDbType.Int);
sqlCommand.Parameters["@CalendarStageConfigId"].Value = calendarStage.CalendarStageConfigId;
sqlCommand.CommandType = CommandType.Text;
connection.Open();
result = (int)sqlCommand.ExecuteScalar();
}
}
return(result);
}
public void UpdateCalendarStage(CalendarStage calendarStage)
{
var sql = $@"UPDATE [pq].[CalendarStage] SET [SortOrder] = @SortOrder, [Name] = @Name, [IsServiceDefault] = @IsServiceDefault, CalendarStageConfigId = @CalendarStageConfigId WHERE ID = @Id";
using (SqlConnection connection = new SqlConnection(_settings.QFlowConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
{
sqlCommand.Parameters.Add("@SortOrder", SqlDbType.Int);
sqlCommand.Parameters["@SortOrder"].Value = calendarStage.SortOrder;
sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar);
sqlCommand.Parameters["@Name"].Value = calendarStage.Name;
sqlCommand.Parameters.Add("@Id", SqlDbType.Int);
sqlCommand.Parameters["@Id"].Value = calendarStage.Id;
sqlCommand.Parameters.Add("@IsServiceDefault", SqlDbType.Int);
sqlCommand.Parameters["@IsServiceDefault"].Value = calendarStage.IsServiceDefault ? 1 : 0;
sqlCommand.Parameters.Add("@CalendarStageConfigId", SqlDbType.Int);
sqlCommand.Parameters["@CalendarStageConfigId"].Value = calendarStage.CalendarStageConfigId;
sqlCommand.CommandType = CommandType.Text;
connection.Open();
sqlCommand.ExecuteNonQuery();
}
}
}
private List <CalendarStage> GetClendarStages(int configId, int?id, CalendarStageType?stageType)
{
string whereCalusesql = $" WHERE CalendarStageConfigId = {configId} ";
var clauses = new List <string>();
if (id.HasValue)
{
clauses.Add($"(ID = {id.Value})");
}
if (stageType.HasValue)
{
clauses.Add($"(StageType = {(int)stageType.Value})");
}
if (clauses.Count > 0)
{
whereCalusesql += $" AND {string.Join(" AND ", clauses)}";
}
var sql = $"select Id, Name, SortOrder, StageType, IsServiceDefault, CalendarStageConfigId from pq.CalendarStage {whereCalusesql} order by SortOrder";
var result = new List <CalendarStage>();
using (SqlConnection connection = new SqlConnection(_settings.QFlowConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
{
sqlCommand.CommandType = CommandType.Text;
connection.Open();
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
var item = new CalendarStage();
item.Id = Converter.ToInt32(sqlDataReader["Id"]);
item.Name = Converter.ToString(sqlDataReader["Name"]);
item.SortOrder = Converter.ToInt32(sqlDataReader["SortOrder"]);
item.StageType = (CalendarStageType)Converter.ToInt32(sqlDataReader["StageType"]);
item.IsServiceDefault = Converter.ToBoolean(sqlDataReader["IsServiceDefault"]);
item.CalendarStageConfigId = Converter.ToInt32(sqlDataReader["CalendarStageConfigId"]);
result.Add(item);
}
}
}
}
return(result);
}
internal void UpdateOrInsertStage(int configId, CustomizeCalendarStage stagedata)
{
CalendarStage stage = null;
if (stagedata.Id > 0)
{
stage = GetClendarStage(configId, stagedata.Id);
}
if (stage == null)
{
stage = new CalendarStage()
{
Name = stagedata.Name,
SortOrder = stagedata.SortOrder,
IsServiceDefault = stagedata.IsServiceDefault,
StageType = (CalendarStageType)stagedata.StageType,
CalendarStageConfigId = configId
};
stage.Id = InserCalendarStage(stage);
}
else
{
stage.Name = stagedata.Name;
stage.SortOrder = stagedata.SortOrder;
stage.IsServiceDefault = stagedata.IsServiceDefault;
stage.CalendarStageConfigId = configId;
UpdateCalendarStage(stage);
}
stagedata.Id = stage.Id;
if (stagedata.Services?.Count > 0)
{
InsertCalendarStageServices(stage.Id, stagedata.Services);
}
}
