private void btnGen_Click(object sender, EventArgs e) { bool result; var createdList = new List <string>(); var startViewCount = TabloidConfig.Config.Views.Count; //clean response list foreach (DataRow dr in _tbContainer.Rows) { if (dr.RowState != DataRowState.Deleted && dr["Réponses"].ToString() != "") { var r = dr["Réponses"].ToString().Trim(); r = r.Replace('-', ';'); dr["Réponses"] = r; } } _tbContainer.AcceptChanges(); //search duplicate response list for combo box _tbContainer.Columns.Add("Liste"); var list = 0; foreach (DataRow dr in _tbContainer.Rows) { if (dr.RowState != DataRowState.Deleted && dr["Réponses"].ToString() != "" && dr["Liste"] == DBNull.Value) { var currentListName = "lr" + list.ToString(); dr["Liste"] = currentListName; var dv = new DataView(_tbContainer, string.Format("Réponses like '{0}'", DataTools.ExtendedStringToSql(dr["Réponses"].ToString())), "", DataViewRowState.Unchanged); foreach (DataRowView drv in dv) { drv["Liste"] = currentListName; } list++; } } //add new View var param = new string[] { Program.AppSet.Schema, txtVue.Text, "id_" + txtVue.Text }; var t = new TabloidConfigView { Schema = param[0], Nom = txtVue.Text, Titre = txtVue.Text, NomTable = txtVue.Text, DbKey = param[2], Detail = true, DisplayOnTowRow = true }; result = WizardSQLHelper.ExecuteFromFile("table.sql", param, Program.AppSet.ConnectionString, this); if (!result) { return; } var i = 1; param = new string[] { txtVue.Text, "", "", Program.AppSet.Schema }; foreach (DataRow dr in _tbContainer.Rows) { if (dr.RowState != DataRowState.Deleted) { param[1] = "R" + i; var info = dr["Info"].ToString(); var Tc = new TabloidConfigColonne { Nom = "C" + i, Champ = "r" + i, Groupe = "!!!" + dr["Groupe"], Titre = dr["Questions"].ToString(), VisibleListe = false, Information = info }; if (dr["Réponses"].ToString() == "") { //TextBox Tc.Editeur = Tabloid.Classes.Controls.TemplateType.Defaut; Tc.Type = DbType.String; param[2] = dataHelper.DbTypeConverter.ConvertFromGenericDbType(DbType.String, Classes.WizardTools.Tools.ConvertProviderType(Program.AppSet.ProviderType)); result = WizardSQLHelper.ExecuteFromFile("addField.sql", param, Program.AppSet.ConnectionString, this); t.Colonnes.Add(Tc); } else {//ComboBox var tableCreationNeeded = !createdList.Contains(dr["Liste"].ToString()); result = WizardSQLHelper.SetDataBaseForList(this, false, Program.AppSet.Schema, dr["Liste"].ToString(), dr["Liste"].ToString(), "id_" + dr["Liste"].ToString(), false, t, Tc.Champ, Program.AppSet.ConnectionString, Program.AppSet.ProviderType, true, false, tableCreationNeeded ? "" : "alr" + i, !tableCreationNeeded); var c = t.Colonnes[t.Colonnes.Count - 1]; //last added column c.Groupe = "!!!" + dr["Groupe"]; c.Editeur = Tabloid.Classes.Controls.TemplateType.ComboBox; //replace comboboxplus by combobox c.Titre = dr["Questions"].ToString(); c.VisibleListe = false; c.Obligatoire = true; c.Information = info; createdList.Add(dr["Liste"].ToString()); } i++; } } t.Index = TabloidConfig.Config.Views.Count - startViewCount; TabloidConfig.Config.Views.Add(t); // fill possible response table (lrx) DataView view = new DataView(_tbContainer); DataTable distinctValues = view.ToTable(true, "Liste", "Réponses"); foreach (DataRow dr in distinctValues.Rows) { if (dr.RowState != DataRowState.Deleted) { var sols = dr["Réponses"].ToString().Split(';'); var values = ""; foreach (string sol in sols) { values += $"('{DataTools.StringToSql(sol.Trim())}'),"; } values = values.TrimEnd(','); var sql = $"INSERT INTO {Program.AppSet.Schema}.{ dr["Liste"]} (nom_{ dr["Liste"]}) VALUES {values}"; string error; DataTools.Command(sql, null, Program.AppSet.ConnectionString, out error); } } //add default url field in user table var sqlType = dataHelper.DbTypeConverter.ConvertFromGenericDbType(DbType.String, Classes.WizardTools.Tools.ConvertProviderType(Program.AppSet.ProviderType)); var requestParam = new string[] { "roles", "default_url", sqlType + $"(300)", Program.AppSet.Schema }; WizardSQLHelper.ExecuteFromFile("addField.sql", requestParam, Program.AppSet.ConnectionString, this); //add role "sonde" var detailURL = AppSetting.GetDefaultPageURL(t.Nom, TabloidPages.Type.Detail) + "&mode=questionnaire"; var profileRight = (ulong)Math.Pow(2, t.Index); //var profileRight2 = ~profileRight; var sql2 = $"insert into roles (titre_role,datemaj_roles,droits_lecture,droits_ecriture,droits_limite,droits_limiteecr,droits_suppression,default_url) values ('Sondé',now(),{profileRight},{profileRight},{profileRight},{profileRight},0,'{detailURL}')"; sql2 += string.Format( SqlConverter.GetSql(SqlConverter.SqlType.InsertCommandKeyOut), "id_role"); string error2; var roleId = DataTools.ScalarCommand(sql2, null, Program.AppSet.ConnectionString, out error2); //set config Program.AppSet.TabloidType = TabloidTypes.Questionnaire; AppSetting.setDefaultPage(t.Nom); Program.AppSet.champPageDefaut = "default_url"; Program.AppSet.AutoenrollmentRole = roleId.ToString(); DialogResult = DialogResult.OK; Close(); }