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