public void Save(ProcessType model, int userId)
{
bool transWasStarted = false;
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
// START TRANS
OleDbCommand command = new OleDbCommand("BEGIN TRANSACTION", connection);
command.ExecuteNonQuery();
transWasStarted = true;
if (model.Id > 0)
command = new OleDbCommand(@"UPDATE ProcessTypes SET TypeName=@TypeName, UpdatedById=@UpdatedById,
UpdatedAt=@UpdatedAt WHERE ID=@id", connection);
else
command = new OleDbCommand(@"INSERT INTO ProcessTypes (TypeName, UpdatedById,
UpdatedAt, CreatedById, CreatedAt) values (@TypeName, @UpdatedById, @UpdatedAt,
@CreatedById, @CreatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@TypeName", model.TypeName));
command.Parameters.Add(new OleDbParameter("@UpdatedById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
if (model.Id > 0)
command.Parameters.Add(new OleDbParameter("@id", model.Id));
else
{
command.Parameters.Add(new OleDbParameter("@CreatedById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@CreatedAt", DateTime.Now));
}
command.ExecuteNonQuery();
if (model.Id <= 0)
{
command = new OleDbCommand(@"SELECT Max(ID) FROM ProcessTypes", connection);
model.Id = (int)command.ExecuteScalar();
// When inserting, assign custom disease to type
command = new OleDbCommand(@"INSERT INTO ProcessTypes_to_Diseases (ProcessTypeId, DiseaseId
) values (@ProcessTypeId, @DiseaseId)", connection);
command.Parameters.Add(new OleDbParameter("@ProcessTypeId", model.Id));
command.Parameters.Add(new OleDbParameter("@DiseaseId", (int)DiseaseType.Custom));
command.ExecuteNonQuery();
// Add year reported
command = new OleDbCommand(@"INSERT INTO ProcessIndicators (ProcessTypeId, DataTypeId, AggTypeId,
DisplayName, IsRequired, IsDisabled, IsEditable, IsDisplayed, SortOrder, UpdatedById, UpdatedAt) VALUES
(@ProcessTypeId, 4, 5, 'DateReported', -1, 0, 0, 0, -1, @UpdatedById,
@UpdatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@ProcessTypeId", model.Id));
command.Parameters.Add(new OleDbParameter("@UpdateById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.ExecuteNonQuery();
// Add notes
command = new OleDbCommand(@"INSERT INTO ProcessIndicators (ProcessTypeId, DataTypeId, AggTypeId,
DisplayName, IsRequired, IsDisabled, IsEditable, IsDisplayed, SortOrder, UpdatedById, UpdatedAt) VALUES
(@ProcessTypeId, 15, 4, 'Notes', 0, 0, 0, -1, 100000, @UpdatedById,
@UpdatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@ProcessTypeId", model.Id));
command.Parameters.Add(new OleDbParameter("@UpdateById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.ExecuteNonQuery();
}
foreach (var indicator in model.Indicators.Values.Where(i => i.Id > 0 && i.IsEdited))
{
command = new OleDbCommand(@"UPDATE ProcessIndicators SET ProcessTypeId=@ProcessTypeId, DataTypeId=@DataTypeId,
DisplayName=@DisplayName, IsRequired=@IsRequired, IsDisabled=@IsDisabled,
IsEditable=@IsEditable, IsDisplayed=@IsDisplayed, UpdatedById=@UpdateById, UpdatedAt=@UpdatedAt
WHERE ID = @id", connection);
command.Parameters.Add(new OleDbParameter("@ProcessTypeId", model.Id));
command.Parameters.Add(new OleDbParameter("@DataTypeId", indicator.DataTypeId));
command.Parameters.Add(new OleDbParameter("@DisplayName", indicator.DisplayName));
command.Parameters.Add(new OleDbParameter("@IsRequired", indicator.IsRequired));
command.Parameters.Add(new OleDbParameter("@IsDisabled", indicator.IsDisabled));
command.Parameters.Add(new OleDbParameter("@IsEditable", true));
command.Parameters.Add(new OleDbParameter("@IsDisplayed", false));
command.Parameters.Add(new OleDbParameter("@UpdateById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.Parameters.Add(new OleDbParameter("@id", indicator.Id));
command.ExecuteNonQuery();
}
foreach (var indicator in model.Indicators.Values.Where(i => i.Id <= 0 && i.IsEdited))
{
command = new OleDbCommand(@"INSERT INTO ProcessIndicators (ProcessTypeId, DataTypeId,
DisplayName, IsRequired, IsDisabled, IsEditable, IsDisplayed, UpdatedById, UpdatedAt) VALUES
(@ProcessTypeId, @DataTypeId, @DisplayName, @IsRequired, @IsDisabled, @IsEditable, @IsDisplayed, @UpdatedById,
@UpdatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@ProcessTypeId", model.Id));
command.Parameters.Add(new OleDbParameter("@DataTypeId", indicator.DataTypeId));
command.Parameters.Add(new OleDbParameter("@DisplayName", indicator.DisplayName));
command.Parameters.Add(new OleDbParameter("@IsRequired", indicator.IsRequired));
command.Parameters.Add(new OleDbParameter("@IsDisabled", indicator.IsDisabled));
command.Parameters.Add(new OleDbParameter("@IsEditable", true));
command.Parameters.Add(new OleDbParameter("@IsDisplayed", false));
command.Parameters.Add(new OleDbParameter("@UpdateById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
command.ExecuteNonQuery();
command = new OleDbCommand(@"SELECT Max(ID) FROM ProcessIndicators", connection);
indicator.Id = (int)command.ExecuteScalar();
}
// COMMIT TRANS
command = new OleDbCommand("COMMIT TRANSACTION", connection);
command.ExecuteNonQuery();
transWasStarted = false;
}
catch (Exception)
{
if (transWasStarted)
{
try
{
OleDbCommand cmd = new OleDbCommand("ROLLBACK TRANSACTION", connection);
cmd.ExecuteNonQuery();
}
catch { }
}
throw;
}
}
}
protected override void SetSpecificType(int id)
{
type = repo.GetProcessType(id);
Indicators = type.Indicators;
DropDownValues = type.IndicatorDropdownValues;
Calculator = new CalcProcess();
Validator = new ProcessCustomValidator();
}
public IView NewProcess(ProcessType type)
{
if (type.Id < 1)
return null;
return new DataEntryEdit(new ProcessBaseVm(adminLevel, type.Id, new CalcProcess()));
}
protected override void ReloadDropdownValues()
{
type = repo.GetProcessType(type.Id);
DropDownValues = type.IndicatorDropdownValues;
}
public ProcessType GetProcessType(int id)
{
ProcessType process = new ProcessType();
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
OleDbCommand command = new OleDbCommand(@"Select ProcessTypes.TypeName, Diseases.DiseaseType, ProcessTypes.UpdatedAt,
aspnet_users.UserName, ProcessTypes.CreatedAt, created.UserName as CreatedBy
FROM ((((ProcessTypes INNER JOIN aspnet_Users on ProcessTypes.UpdatedById = aspnet_Users.UserId)
INNER JOIN aspnet_Users created on ProcessTypes.CreatedById = created.UserId)
INNER JOIN ProcessTypes_to_Diseases itod on ProcessTypes.ID = itod.ProcessTypeId)
INNER JOIN Diseases on itod.DiseaseId = Diseases.Id)
WHERE ProcessTypes.ID=@id
GROUP BY ProcessTypes.TypeName, Diseases.DiseaseType, ProcessTypes.UpdatedAt,
aspnet_users.UserName, ProcessTypes.CreatedAt, created.UserName", connection);
command.Parameters.Add(new OleDbParameter("@id", id));
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
var name = TranslationLookup.GetValue(reader.GetValueOrDefault<string>("TypeName"),
reader.GetValueOrDefault<string>("TypeName"));
if (reader.GetValueOrDefault<string>("DiseaseType") == "Custom")
name = reader.GetValueOrDefault<string>("TypeName");
process = new ProcessType
{
Id = id,
DisplayNameKey = reader.GetValueOrDefault<string>("TypeName"),
TypeName = name,
UpdatedBy = GetAuditInfo(reader)
};
}
reader.Close();
}
List<string> indicatorIds = new List<string>();
command = new OleDbCommand(@"Select
ProcessIndicators.ID,
ProcessIndicators.DataTypeId,
ProcessIndicators.DisplayName,
ProcessIndicators.IsRequired,
ProcessIndicators.IsDisabled,
ProcessIndicators.IsEditable,
ProcessIndicators.IsDisplayed,
ProcessIndicators.CanAddValues,
ProcessIndicators.UpdatedAt,
ProcessIndicators.RedistrictRuleId,
ProcessIndicators.IsCalculated,
ProcessIndicators.SortOrder,
MergeRuleId,
AggTypeId,
aspnet_users.UserName,
IndicatorDataTypes.DataType
FROM ((ProcessIndicators INNER JOIN aspnet_users ON ProcessIndicators.UpdatedById = aspnet_users.UserId)
INNER JOIN IndicatorDataTypes ON ProcessIndicators.DataTypeId = IndicatorDataTypes.ID)
WHERE ProcessTypeId=@ProcessTypeId AND IsDisabled=0
ORDER BY IsEditable DESC, SortOrder, ProcessIndicators.ID", connection);
command.Parameters.Add(new OleDbParameter("@ProcessTypeId", id));
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if(!process.Indicators.ContainsKey(reader.GetValueOrDefault<string>("DisplayName")))
{
process.Indicators.Add(reader.GetValueOrDefault<string>("DisplayName"),
new Indicator
{
Id = reader.GetValueOrDefault<int>("ID"),
DataTypeId = reader.GetValueOrDefault<int>("DataTypeId"),
RedistrictRuleId = reader.GetValueOrDefault<int>("RedistrictRuleId"),
MergeRuleId = reader.GetValueOrDefault<int>("MergeRuleId"),
AggRuleId = reader.GetValueOrDefault<int>("AggTypeId"),
UpdatedBy = reader.GetValueOrDefault<DateTime>("UpdatedAt").ToShortDateString() + " by " +
reader.GetValueOrDefault<string>("UserName"),
DisplayName = reader.GetValueOrDefault<string>("DisplayName"),
IsRequired = reader.GetValueOrDefault<bool>("IsRequired"),
IsDisabled = reader.GetValueOrDefault<bool>("IsDisabled"),
IsEditable = reader.GetValueOrDefault<bool>("IsEditable"),
IsDisplayed = reader.GetValueOrDefault<bool>("IsDisplayed"),
CanAddValues = reader.GetValueOrDefault<bool>("CanAddValues"),
DataType = reader.GetValueOrDefault<string>("DataType"),
IsCalculated = reader.GetValueOrDefault<bool>("IsCalculated"),
SortOrder = reader.GetValueOrDefault<int>("SortOrder")
});
indicatorIds.Add(reader.GetValueOrDefault<int>("ID").ToString());
}
}
reader.Close();
}
process.IndicatorDropdownValues = GetIndicatorDropdownValues(connection, command, IndicatorEntityType.Process, indicatorIds);
}
catch (Exception)
{
throw;
}
}
return process;
}
public ProcessTypeEdit(ProcessType t)
: base()
{
model = t;
InitializeComponent();
}
private void Upload(ProcessType type)
{
List<IHaveDynamicIndicatorValues> forms = new List<IHaveDynamicIndicatorValues>();
forms = options.Processes.Where(d => d.ProcessType.Id == type.Id).Cast<IHaveDynamicIndicatorValues>().ToList();
ProcessImporter importer = new ProcessImporter();
importer.SetType(type.Id);
var payload = new Nada.UI.View.Wizard.SplitDistro.WorkerPayload
{
Importer = importer,
Forms = forms,
};
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = Translations.ExcelFiles + " (*.xlsx)|*.xlsx";
ofd.DefaultExt = ".xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
OnSwitchStep(new WorkingStep(Translations.ImportingFile));
payload.FileName = ofd.FileName;
BackgroundWorker importerWorker = new BackgroundWorker();
importerWorker.DoWork += importerWorker_DoWork;
importerWorker.RunWorkerCompleted += importerWorker_RunWorkerCompleted;
importerWorker.RunWorkerAsync(payload);
}
}