private void FillDataGrid()
{
dt_ref_config = DBFunctions.ReadFromDB("SELECT * FROM referencesconfig WHERE ReferenceDBName = '" + reference_list.Rows[cbCurrentReference.SelectedIndex]["ReferenceDBName"] + "'");
dt_ref_config.TableNewRow += new DataTableNewRowEventHandler(dt_TableNewRow);
dgRefConfig.Columns.Clear();
dgRefConfig.DataSource = dt_ref_config;
dgRefConfig.Columns["ReferenceConfigID"].Visible = false;
dgRefConfig.Columns["ReferenceName"].Visible = false;
dgRefConfig.Columns["ReferenceDBName"].Visible = false;
dgRefConfig.Columns["Hierarchycal"].Visible = false;
dgRefConfig.Columns["ColumnDBName_Old"].Visible = false;
dgRefConfig.Columns["ColumnName"].HeaderText = "Имя колонки";
dgRefConfig.Columns["ColumnDBName"].HeaderText = "Имя колонки в БД";
DataGridViewComboBoxColumn column_type = new DataGridViewComboBoxColumn();
column_type.Items.Add("Строка50");
column_type.Items.Add("Строка300");
column_type.Items.Add("Число с плавающей точкой");
column_type.Items.Add("Целое число");
column_type.Width = 200;
column_type.FlatStyle = FlatStyle.Flat;
column_type.Name = "Тип колонки";
column_type.DataPropertyName = "ColumnType";
dgRefConfig.Columns.Remove("ColumnType");
dgRefConfig.Columns.Add(column_type);
}
void AddElement()
{
dt_elem = DBFunctions.ReadFromDB("SELECT * FROM `ref_data_" + reference_db_name + "` WHERE ID = null");
DataRow new_elem = dt_elem.Rows.Add();
new_elem["ParentID"] = tvReference.SelectedNode != null ? tvReference.SelectedNode.Tag : 0;
new_elem["RefName"] = "Новый элемент";
foreach (Control cntrl in edit_controls.Values)
{
if (cntrl is TextBox)
{
((TextBox)cntrl).Text = "";
}
}
edit_controls["RefName"].Text = "Новый элемент";
((TextBox)edit_controls["RefName"]).SelectionStart = 0;
((TextBox)edit_controls["RefName"]).SelectionLength = ((TextBox)edit_controls["RefName"]).TextLength;
edit_controls["RefName"].Select();
new_node = new TreeNode("Новый элемент");
if (tvReference.SelectedNode != null)
{
tvReference.SelectedNode.Nodes.Add(new_node);
}
else
{
tvReference.Nodes.Add(new_node);
}
tvReference.SelectedNode = new_node;
}
private void HierarchicalRefEdit_Load(object sender, EventArgs e)
{
//Получим список справочников
if (!select_mode)
{
reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig ORDER BY ReferenceName");
tsbSelect.Visible = false;
}
else
{
reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig WHERE ReferenceDBName = '" + reference_db_name + "'");
cbCurrentReference.Visible = false;
tsbAdd.Visible = tsbDelete.Visible = tsbSave.Visible = false;
if (select_mode_multiselect)
{
tvReference.CheckBoxes = true;
}
}
foreach (DataRow row in reference_list.Rows)
{
cbCurrentReference.Items.Add((string)row["ReferenceName"]);
}
cbCurrentReference.SelectedIndex = 0;
TestRights();
}
private void FillDataGrid()
{
dt_active_sessions = DBFunctions.ReadFromDB("SELECT SessionID AS 'Номер сессии', Computer_Name AS 'Имя компьютера', DomainUser AS 'Доменный пользователь',LastActivity AS 'Последняя активность',SessionStart AS 'Начало сессии' " +
"FROM active_sessions");
dgActiveSessions.DataSource = dt_active_sessions;
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
DataRow curr_row = GreenLight.Tools.FindCurrentRow(dgTableData);
int curr_id = (int)curr_row["ID"];
DataTable dt_columns = DBFunctions.ReadFromDB("SELECT ColumnDBName FROM tableconfig WHERE TableDBName = '" + table_db_names[cbTables.SelectedIndex] + "'");
string fields = "";
foreach (DataRow row in dt_columns.Rows)
{
fields += row["ColumnDBName"];
if (dt_columns.Rows.IndexOf(row) + 1 < dt_columns.Rows.Count)
{
fields += ",";
}
}
DBFunctions.ExecuteCommand("INSERT INTO table_" + table_db_names[cbTables.SelectedIndex] + " (" + fields + ") SELECT " + fields + " FROM table_" + table_db_names[cbTables.SelectedIndex] + " WHERE ID = " + Convert.ToString(curr_id));
int col = dgTableData.CurrentCell.ColumnIndex;
FillDataGrid();
dgTableData.CurrentCell = dgTableData[col, dgTableData.Rows.Count - 1];
}
private void FillDataGrid()
{
dt_replaces.Clear();
if (cbKeyFieldValues.SelectedIndex == -1 || cbReferenceNames.SelectedIndex == -1)
{
return;
}
dt_ref_values = DBFunctions.ReadFromDB("SELECT id, RefName FROM ref_data_" + reference_db_names[cbReferenceNames.SelectedIndex] + " ORDER BY RefName");
DataGridViewComboBoxColumn ref_name_column = (DataGridViewComboBoxColumn)dgReplaceStrings.Columns["ref_name"];
ref_name_column.Items.Clear();
foreach (DataRow ref_values_row in dt_ref_values.Rows)
{
ref_name_column.Items.Add(ref_values_row["RefName"]);
}
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("tkfv", key_field_values[cbKeyFieldValues.SelectedIndex]);
parameters.Add("trdn", reference_db_names[cbReferenceNames.SelectedIndex]);
dt_replaces.Merge(DBFunctions.ReadFromDB("SELECT table_reference_id,table_reference_value,replace_string FROM replace_strings WHERE table_key_field_value = @tkfv AND table_reference_db_name = @trdn", parameters));
}
private void LoadAnswers(int question_id)
{
dt_answers = DBFunctions.ReadFromDB("SELECT * FROM questionary_answers WHERE question_id = " + Convert.ToString(question_id));
dt_answers.TableNewRow += new DataTableNewRowEventHandler(dt_TableAnswersNewRow);
dgAnswers.DataSource = dt_answers;
}
private void LoadQuestion()
{
DataTable dt_question = DBFunctions.ReadFromDB("SELECT * FROM questionary_questions WHERE question_id = " +
Convert.ToString(current_question_id));
if (dt_question.Rows.Count != 1)
{
System.Windows.Forms.MessageBox.Show("Ошибка загрузки вопроса.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
else
{
row_question = dt_question.Rows[0];
}
lbQuestionText.Text = (string)row_question["question_text"];
tbAnswer.Text = "";
if ((string)row_question["question_type"] == "Справочник")
{
btnSelect.Visible = true;
tbAnswer.ReadOnly = true;
//Самойлов. Подгружаем данные из таблицы в случае их наличия
if (row_table_record[(string)row_question["question_dest_column_db_name"]] != System.DBNull.Value)
{
tbAnswer.Tag = (int)row_table_record[(string)row_question["question_dest_column_db_name"]];
tbAnswer.Text = Tables.GetRefName((string)row_question["question_reference"], (int)tbAnswer.Tag);
}
}
else
{
btnSelect.Visible = false;
tbAnswer.ReadOnly = false;
//Самойлов. Подгружаем данные из таблицы в случае их наличия
tbAnswer.Text = Convert.ToString(row_table_record[(string)row_question["question_dest_column_db_name"]]);
}
if (question_history.Count == 0)
{
btnPrev.Enabled = false;
}
else
{
btnPrev.Enabled = true;
}
if (row_question["question_next"] == DBNull.Value && !(bool)row_question["question_variation"])
{
btnNext.Text = "Завершить";
}
else
{
btnNext.Text = "Следующий >";
}
}
public static DataTable GetTable(string table_name, ref DataGridView dgTableData, ref TableStruct ts, int record_id = 0)
{
DataTable dt_table_data;
DataTable dt_table_structure = DBFunctions.ReadFromDB("SELECT ColumnName,ColumnDBName,ColumnType,ColumnReference,ReferenceMultiSelect,ShowInEditor FROM tableconfig WHERE TableDBName = '" + table_name + "'");
string query_text = "SELECT table_" + table_name + ".ID";
int rows_count = 0;
foreach (DataRow row in dt_table_structure.Rows)
{
query_text += ",";
rows_count++;
query_text += row["ColumnDBName"];
}
query_text += " FROM table_" + table_name;
if (record_id != 0)
{
query_text += " WHERE table_" + table_name + ".ID=" + Convert.ToString(record_id);
}
dt_table_data = DBFunctions.ReadFromDB(query_text);
if (dgTableData != null)
{
dgTableData.DataSource = dt_table_data;
}
ts = new TableStruct();
ts.TableName = "table_" + table_name;
string[] p_keys = { "ID" };
ts.p_keys = p_keys;
ts.columns = new string[rows_count];
int curr_ts_row = 0;
for (int i = 0; i < dt_table_structure.Rows.Count; i++)
{
if ((bool)dt_table_structure.Rows[i]["ShowInEditor"] == false)
{
dgTableData.Columns[i + 1].Visible = false;
}
else
{
dgTableData.Columns[i + 1].HeaderText = (string)dt_table_structure.Rows[i]["ColumnName"];
}
/*if ( (string)dt_table_structure.Rows[i]["ColumnType"] == "Справочник")
* {
* dgTableData.Columns[curr_dg_column - 1].Visible = false;
* dgTableData.Columns[curr_dg_column++].HeaderText = (string)dt_table_structure.Rows[i]["ColumnName"];
* }*/
//if ((bool)dt_table_structure.Rows[i]["ReferenceMultiSelect"] == false)
ts.columns[curr_ts_row++] = (string)dt_table_structure.Rows[i]["ColumnDBName"];
}
return(dt_table_data);
}
void SaveElement()
{
if (tvReference.SelectedNode == null)
{
return;
}
foreach (DataColumn dc in dt_elem.Columns)
{
try
{
if (dc.DataType.Name == "String")
{
dt_elem.Rows[0][dc] = edit_controls[dc.ColumnName].Text;
}
else if (dc.DataType.Name == "Int32")
{
dt_elem.Rows[0][dc] = Convert.ToInt32(edit_controls[dc.ColumnName].Text);
}
else if (dc.DataType.Name == "Single")
{
dt_elem.Rows[0][dc] = Convert.ToSingle(edit_controls[dc.ColumnName].Text);
}
}
catch (Exception) { };
}
DBFunctions.WriteToDB(dt_elem, ref_table_struct);
if (tvReference.SelectedNode.Tag == null)
{
tvReference.SelectedNode.Tag = Convert.ToInt32(DBFunctions.ReadScalarFromDB("SELECT LAST_INSERT_ID()"));
//Добавим этот элемент во все мультирефы, в которых выбран его родитель
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("ref_db_name", reference_db_name);
DataTable dtMultirefFields = DBFunctions.ReadFromDB("SELECT tabledbname,columndbname FROM tableconfig WHERE referencemultiselect=1 and columnreference=@ref_db_name", parameters);
foreach (DataRow multirefFieldRow in dtMultirefFields.Rows)
{
parameters.Clear();
parameters.Add("id_string", "{" + Convert.ToString(tvReference.SelectedNode.Tag) + "}");
parameters.Add("id", dt_elem.Rows[0]["ParentID"]);
string query = @"UPDATE table_" + ((string)multirefFieldRow["tabledbname"]) + @" SET " + ((string)multirefFieldRow["columndbname"]) +
@" = CONCAT(" + ((string)multirefFieldRow["columndbname"]) + @",@id_string)
WHERE POSITION(CONCAT('{',(SELECT parentid FROM ref_data_" + reference_db_name + @" WHERE id=@id),'}') IN " + ((string)multirefFieldRow["columndbname"]) + ") > 0";
DBFunctions.ExecuteCommand(query, parameters);
}
}
tvReference.SelectedNode.Text = (string)dt_elem.Rows[0]["RefName"];
new_node = null;
}
private void ReferenceStructureEdit_Load(object sender, EventArgs e)
{
reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig");
foreach (DataRow row in reference_list.Rows)
{
cbCurrentReference.Items.Add((string)row["ReferenceName"]);
}
cbCurrentReference.SelectedIndex = 0;
TestRights();
}
private void FillClients()
{
ts = new TableStruct();
dt_clients = DBFunctions.ReadFromDB("SELECT id,CONCAT(familia_zaem,' ',IFNULL(Imya_zaem,''),' ',IFNULL(Otchestvo_zaem,'')) AS fio_zaem FROM table_clients");
dgClients.DataSource = dt_clients;
dgClients.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dgClients.Columns[0].HeaderText = "ID";
dgClients.Columns[1].HeaderText = "ФИО";
}
private void LoadOtborFields()
{
DataTable dt_otbor = DBFunctions.ReadFromDB("SELECT ColumnName FROM tableconfig WHERE tabledbname = 'credprogr' ORDER BY ColumnName");
tscbFields.Items.Clear();
tscbFields.Items.Add(" ");
foreach (DataRow otbor_row in dt_otbor.Rows)
{
tscbFields.Items.Add(otbor_row["ColumnName"]);
}
}
public static void RecreateRefStatements()
{
DataTable References = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceDBName FROM referencesconfig");
foreach (DataRow row in References.Rows)
{
//Создадим хранимые процедуры и триггеры
string ref_create_script = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'Reference_Create'");
ref_create_script = ref_create_script.Replace("[RefDBName]", (string)row["ReferenceDBName"]);
DBFunctions.ExecuteScript(ref_create_script);
}
}
void FillEditForm()
{
dt_elem = DBFunctions.ReadFromDB("SELECT * FROM `ref_data_" + reference_db_name + "` WHERE ID = " + Convert.ToString(current_elem_id));
if (dt_elem.Rows.Count != 1)
{
return;
}
foreach (DataColumn dc in dt_elem.Columns)
{
try
{
edit_controls[dc.ColumnName].Text = Convert.ToString(dt_elem.Rows[0][dc]);
}
catch (Exception) { };
}
}
private void ClauseEditor_Load(object sender, EventArgs e)
{
dt = DBFunctions.ReadFromDB("SELECT * FROM where_clauses");
if (dt.Rows.Count > 0)
{
tbWhereClause.Text = (string)dt.Rows[0]["Clause"];
}
//Загрузим подсказку с именами столбцов
dt_column_names = DBFunctions.ReadFromDB("SELECT TableConfigID, TableName AS 'Имя таблицы', ColumnName AS 'Имя колонки', ColumnDBName AS 'Наименование колонки в БД', ColumnType AS 'Тип', ColumnReference, ReferenceMultiSelect, UseInWhereClause FROM tableconfig ORDER BY TableName");
dgColumnNames.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dgColumnNames.DataSource = dt_column_names;
TestRights();
}
public static void UpdateDBStructure()
{
//Получим список главных таблиц
DataTable MainTables = DBFunctions.ReadFromDB("SELECT DISTINCT TableDBName FROM tableconfig");
foreach (DataRow row in MainTables.Rows)
{
UpdateMainTableStructure((string)row["TableDBName"]);
}
//Списки справочников
DataTable References = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceDBName FROM referencesconfig");
foreach (DataRow row in References.Rows)
{
UpdateReferenceStructure((string)row["ReferenceDBName"]);
}
}
private void tbAddReference_Click(object sender, EventArgs e)
{
string ref_name = Microsoft.VisualBasic.Interaction.InputBox("Введите наименование справочника", "Ввод наименования");
if (ref_name == "")
{
return;
}
string ref_db_name = Microsoft.VisualBasic.Interaction.InputBox("Введите название справочника для базы данных", "Ввод наименования");
if (ref_db_name == "")
{
return;
}
ref_db_name = ref_db_name.ToLower();
SaveData();
DBFunctions.ExecuteCommand("INSERT INTO referencesconfig SET ReferenceName = '" + ref_name + "',ReferenceDBName = '" +
ref_db_name + "',Hierarchycal = 1,ColumnName = 'Наименование',ColumnDBName = 'RefName',ColumnType = 'Строка50'");
DBStructure.UpdateDBStructure();
reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig");
cbCurrentReference.Items.Clear();
foreach (DataRow row in reference_list.Rows)
{
cbCurrentReference.Items.Add((string)row["ReferenceName"]);
}
foreach (string Item in cbCurrentReference.Items)
{
if (Item == ref_name)
{
cbCurrentReference.SelectedIndex = cbCurrentReference.Items.IndexOf(Item);
break;
}
}
}
public DataTable SelectOffers()
{
string query_text = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'OfferSelect'");
query_text = query_text.Replace("[ClientID]", Convert.ToString(ClientID));
DataTable dt;
try
{
dt = DBFunctions.ReadFromDB(query_text);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("Ошибка в условии. " + e.Message, "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return(null);
}
return(dt);
}
public static void ReorganizeMultiref()
{
DataTable dt_multirefcolumns = DBFunctions.ReadFromDB("SELECT TableDBName,ColumnDBName FROM tableconfig WHERE ReferenceMultiSelect = 1");
DBFunctions.ExecuteCommand("SET group_concat_max_len = 32000");
foreach (DataRow row_multirefcolumn in dt_multirefcolumns.Rows)
{
DataTable dt_values = DBFunctions.ReadFromDB(
@"SELECT TableID, GROUP_CONCAT(CONCAT('{',CONVERT(RefID, CHAR),'}') SEPARATOR '') AS RefID
FROM multiref_" + row_multirefcolumn["TableDBName"] + "_" + row_multirefcolumn["ColumnDBName"] + @"
GROUP BY TableID");
foreach (DataRow row_value in dt_values.Rows)
{
DBFunctions.ExecuteCommand(@"UPDATE table_" + row_multirefcolumn["TableDBName"] + @"
SET " + row_multirefcolumn["ColumnDBName"] + " = '" + row_value["RefID"] + @"'
WHERE id = " + Convert.ToString(row_value["TableID"]));
}
}
}
public static DataTable GetTableWODataGrid(string table_name, ref TableStruct ts, int record_id = 0)
{
DataTable dt_table_data;
DataTable dt_table_structure = DBFunctions.ReadFromDB("SELECT ColumnName,ColumnDBName,ColumnType,ColumnReference,ReferenceMultiSelect FROM tableconfig WHERE TableDBName = '" + table_name + "'");
string query_text = "SELECT table_" + table_name + ".ID";
int rows_count = 0;
foreach (DataRow row in dt_table_structure.Rows)
{
query_text += ",";
rows_count++;
query_text += row["ColumnDBName"];
}
query_text += " FROM table_" + table_name;
//query_text += join_text;
if (record_id != 0)
{
query_text += " WHERE table_" + table_name + ".ID=" + Convert.ToString(record_id);
}
dt_table_data = DBFunctions.ReadFromDB(query_text);
ts = new TableStruct();
ts.TableName = "table_" + table_name;
string[] p_keys = { "ID" };
ts.p_keys = p_keys;
ts.columns = new string[rows_count];
int curr_ts_row = 0;
for (int i = 0; i < dt_table_structure.Rows.Count; i++)
{
ts.columns[curr_ts_row++] = (string)dt_table_structure.Rows[i]["ColumnDBName"];
}
return(dt_table_data);
}
public static void GenerateMacrosValuesQuery(int clientID)
{
string query_text = "SELECT ";
//Заменим макросы в условиях
DataTable dtMacros = DBFunctions.ReadFromDB("SELECT * FROM macros");
bool first = true;
foreach (DataRow macro in dtMacros.Rows)
{
if (!first)
{
query_text = query_text + "," + Environment.NewLine;
}
first = false;
query_text = query_text + "[" + macro["name"] + "] AS '" + macro["name"] + "'";
}
query_text = query_text + Environment.NewLine + "FROM table_clients LEFT JOIN table_credprogr WHERE id=" + Convert.ToString(clientID);
bool macros_found = true;
while (macros_found)
{
macros_found = false;
foreach (DataRow macro in dtMacros.Rows)
{
if (query_text.IndexOf("[" + (string)macro["name"] + "]") != -1)
{
macros_found = true;
query_text = query_text.Replace("[" + (string)macro["name"] + "]", (string)macro["macro"]);
}
}
}
query_text = query_text;
}
private void tbDeleteReference_Click(object sender, EventArgs e)
{
if (System.Windows.Forms.MessageBox.Show("Данные справочника будут безвозвратно удалены. Продолжить?", "Вопрос", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
string ref_db_name = (string)reference_list.Rows[cbCurrentReference.SelectedIndex]["ReferenceDBName"];
DBFunctions.ExecuteCommand("DELETE FROM referencesconfig WHERE ReferenceDBName='" +
ref_db_name + "';" +
"DROP TABLE `ref_data_" + ref_db_name + "`;" +
"DROP TABLE `ref_hierarchy_" + ref_db_name + "`;");
cbCurrentReference.Items.Clear();
reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig");
foreach (DataRow row in reference_list.Rows)
{
cbCurrentReference.Items.Add((string)row["ReferenceName"]);
}
cbCurrentReference.SelectedIndex = 0;
}
public void SetDisplayName()
{
if (Value == System.DBNull.Value)
{
DisplayName = "";
return;
}
if (!isReference)
{
DisplayName = Convert.ToString(Value);
}
else
{
if (isMultiRef)
{
DisplayName = "";
DataTable dt_selected_ids = DBFunctions.ReadFromDB(@"SELECT RefName
FROM ref_data_" + RefDBName + @"
WHERE LOCATE(concat('{',CAST(ID AS CHAR),'}'),'" + (string)Value + "') > 0 ORDER BY RefName");
foreach (DataRow ref_id_row in dt_selected_ids.Rows)
{
DisplayName += (string)ref_id_row["RefName"] + ", ";
}
char[] trimchars = { ',', ' ' };
DisplayName = DisplayName.TrimEnd(trimchars);
}
else
{
DisplayName = Tables.GetRefName(RefDBName, (int)Value);
}
}
}
private void FillDataGrid()
{
dgFilter.AutoGenerateColumns = false;
dgFilter.Columns.Clear();
dt_filter = new DataTable();
dt_filter.Columns.Add("ID вопроса", Type.GetType("System.Int32"));
DataGridViewTextBoxColumn dgc = new DataGridViewTextBoxColumn();
dgc.Name = "ID вопроса";
dgc.DataPropertyName = "ID вопроса";
dgc.Frozen = true;
dgFilter.Columns.Add(dgc);
dt_filter.Columns.Add("Текст вопроса", Type.GetType("System.String"));
dgc = new DataGridViewTextBoxColumn();
dgc.Name = "Текст вопроса";
dgc.DataPropertyName = "Текст вопроса";
dgc.Frozen = true;
dgc.Width = dgc.Width * 4;
dgFilter.Columns.Add(dgc);
string ColumnDBName = (string)DBFunctions.ReadScalarFromDB("SELECT filter_name_field FROM questionary WHERE id = " + Convert.ToString(questionary_id));
string query_text = "SELECT id, " + ColumnDBName + " AS name FROM table_credprogr";
if (tscbFields.SelectedIndex != -1 && (string)(tscbFields.Items[tscbFields.SelectedIndex]) != " ")
{
query_text += " WHERE " +
(string)DBFunctions.ReadScalarFromDB("SELECT ColumnDBName FROM tableconfig WHERE ColumnName = '" + tscbFields.Items[tscbFields.SelectedIndex] + "'") +
" LIKE '%" + tstbOtbor.Text + "%'";
}
DataTable dt_programs = DBFunctions.ReadFromDB(query_text);
foreach (DataRow program_row in dt_programs.Rows)
{
DataColumn clm = dt_filter.Columns.Add(Convert.ToString(program_row["id"]), Type.GetType("System.Boolean"));
DataGridViewCheckBoxColumn dg_cb_clmn = new DataGridViewCheckBoxColumn();
dg_cb_clmn.Name = (string)program_row["name"] + " (" + Convert.ToString(program_row["id"]) + ")";
dg_cb_clmn.DataPropertyName = Convert.ToString(program_row["id"]);
dg_cb_clmn.TrueValue = true;
dg_cb_clmn.FalseValue = false;
dgFilter.Columns.Add(dg_cb_clmn);
}
dgFilter.DataSource = dt_filter;
DataTable dt_questions = DBFunctions.ReadFromDB("SELECT question_id,question_text FROM questionary_questions WHERE questionary_id = " + Convert.ToString(questionary_id));
DataTable dt_filter_pairs = DBFunctions.ReadFromDB("SELECT CONCAT(question_id,'-',filter_id) FROM questionary_filter WHERE questionary_id = " + Convert.ToString(questionary_id));
DataColumn[] keys = { dt_filter_pairs.Columns[0] };
dt_filter_pairs.PrimaryKey = keys;
foreach (DataRow q_row in dt_questions.Rows)
{
DataRow new_row = dt_filter.Rows.Add(new object[] { q_row["question_id"], q_row["question_text"] });
foreach (DataColumn q_column in dt_filter.Columns)
{
if (dt_filter.Columns.IndexOf(q_column) < 2)
{
continue;
}
if (dt_filter_pairs.Rows.Find(Convert.ToString(q_row["question_id"]) + "-" + q_column.ColumnName) != null)
{
new_row[q_column] = true;
}
else
{
new_row[q_column] = false;
}
}
}
dt_filter.AcceptChanges();
}
private void LoadData()
{
questionary_id = (int)DBFunctions.ReadScalarFromDB("SELECT id FROM questionary WHERE Name = '" + questionary_names[cbQuestionaries.SelectedIndex] + "'");
questionary_table_db_name = (string)DBFunctions.ReadScalarFromDB("SELECT TableDBName FROM questionary WHERE Name = '" + questionary_names[cbQuestionaries.SelectedIndex] + "'");
questionary_use_filter = (bool)DBFunctions.ReadScalarFromDB("SELECT use_filter FROM questionary WHERE Name = '" + questionary_names[cbQuestionaries.SelectedIndex] + "'");
tsbEditFilter.Enabled = questionary_use_filter;
dt_questions = DBFunctions.ReadFromDB("SELECT * FROM questionary_questions WHERE questionary_id = " + Convert.ToString(questionary_id));
dt_questions.TableNewRow += new DataTableNewRowEventHandler(dt_TableNewRow);
dgQuestions.Columns.Clear();
dgQuestions.DataSource = dt_questions;
dgQuestions.Columns["question_id"].HeaderText = "ID";
dgQuestions.Columns["questionary_id"].Visible = false;
dgQuestions.Columns["question_text"].HeaderText = "Текст вопроса";
dgQuestions.Columns["question_variation"].HeaderText = "Разветвление";
dgQuestions.Columns["question_type"].HeaderText = "Тип";
dgQuestions.Columns["question_next"].HeaderText = "ID след. вопроса";
dgQuestions.Columns["question_dest_column_db_name"].HeaderText = "Поле таблицы";
dgQuestions.Columns["question_hint"].HeaderText = "Подсказка";
dgQuestions.Columns["question_input_type"].HeaderText = "Тип элемента ввода(веб)";
dgQuestions.Columns["question_web_position"].HeaderText = "Положение на странице(веб)";
DataGridViewComboBoxColumn column_type = new DataGridViewComboBoxColumn();
column_type.Items.Add("Строка50");
column_type.Items.Add("Строка300");
column_type.Items.Add("Число с плавающей точкой");
column_type.Items.Add("Справочник");
column_type.Items.Add("Целое число");
column_type.Items.Add("Формула");
column_type.Width = 200;
column_type.FlatStyle = FlatStyle.Flat;
column_type.Name = "Тип";
column_type.DataPropertyName = "question_type";
dgQuestions.Columns.Remove("question_type");
dgQuestions.Columns.Add(column_type);
DataGridViewComboBoxColumn column_ref = new DataGridViewComboBoxColumn();
DataTable reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceDBName FROM referencesconfig");
foreach (DataRow ref_row in reference_list.Rows)
{
column_ref.Items.Add(ref_row["ReferenceDBName"]);
}
column_ref.Width = 200;
column_ref.FlatStyle = FlatStyle.Flat;
column_ref.Name = "Имя справочника";
column_ref.DataPropertyName = "question_reference";
dgQuestions.Columns.Remove("question_reference");
dgQuestions.Columns.Add(column_ref);
DataGridViewComboBoxColumn column_table_column = new DataGridViewComboBoxColumn();
DataTable columns_list = DBFunctions.ReadFromDB("SELECT DISTINCT ColumnDBName FROM tableconfig WHERE ReferenceMultiSelect = 0 AND TableDBName = '" +
questionary_table_db_name + "' ORDER BY ColumnDBName");
foreach (DataRow col_row in columns_list.Rows)
{
column_table_column.Items.Add(col_row["ColumnDBName"]);
}
column_table_column.Width = 200;
column_table_column.FlatStyle = FlatStyle.Flat;
column_table_column.Name = "Поле таблицы";
column_table_column.DataPropertyName = "question_dest_column_db_name";
dgQuestions.Columns.Remove("question_dest_column_db_name");
dgQuestions.Columns.Add(column_table_column);
}
public Questionary(int t_r_id = -1, string q_name = "Анкета клиента", int [] filt_ids = null)
{
InitializeComponent();
questionary_name = q_name;
questionary_id = (int)DBFunctions.ReadScalarFromDB("SELECT id FROM questionary WHERE Name = '" + questionary_name + "'");
questionary_table_db_name = (string)DBFunctions.ReadScalarFromDB("SELECT TableDBName FROM questionary WHERE Name = '" + questionary_name + "'");
current_question_id = (int)DBFunctions.ReadScalarFromDB("SELECT first_question_id FROM questionary WHERE Name = '" + questionary_name + "'");
questionary_use_filter = (bool)DBFunctions.ReadScalarFromDB("SELECT use_filter FROM questionary WHERE Name = '" + questionary_name + "'");
if (questionary_use_filter)
{
filter_ids = (int[])filt_ids.Clone();
string query_text = "SELECT DISTINCT question_id FROM questionary_filter WHERE questionary_id=@q_id AND filter_id IN (";
foreach (int filter_id in filter_ids)
{
query_text += filter_id.ToString() + ",";
}
query_text = query_text.TrimEnd(',') + ")";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("q_id", questionary_id);
dt_filter_allowed_questions = DBFunctions.ReadFromDB(query_text, parameters);
//Дойдем до первого разрешенного вопроса
while (current_question_id != -1)
{
if (dt_filter_allowed_questions.Select("question_id = " + Convert.ToString(current_question_id)).Length > 0)
{
break;
}
parameters = new Dictionary <string, object>();
parameters.Add("questionary_id", questionary_id);
parameters.Add("q_id", current_question_id);
object new_question_id = DBFunctions.ReadScalarFromDB("SELECT question_next FROM questionary_questions WHERE questionary_id=@questionary_id AND question_id = @q_id", parameters);
if (new_question_id == DBNull.Value)
{
current_question_id = -1;
}
else
{
current_question_id = (int)new_question_id;
}
}
}
question_history = new List <QuestionInfo>();
table_record_id = t_r_id;
//Подгрузим инфо о записи таблицы или пустую строку
ts = new TableStruct();
dt_table_data = Tables.GetTableWODataGrid(questionary_table_db_name, ref ts, table_record_id);
if (dt_table_data.Rows.Count > 0)
{
row_table_record = dt_table_data.Rows[0];
}
else
{
row_table_record = dt_table_data.NewRow();
dt_table_data.Rows.Add(row_table_record);
}
}
static void UpdateMainTableStructure(string TableDBName)
{
//Получим требуемую структуру
DataTable neededStructure = DBFunctions.ReadFromDB("SELECT TableConfigID,ColumnDBName,ColumnDBName_Old,ColumnType,ReferenceMultiSelect FROM tableconfig WHERE TableDBName = '" + TableDBName + "'");
//Получим текущую структуру
DataTable CurrentStrurture = new DataTable();
bool new_table = false;
try
{
CurrentStrurture = DBFunctions.ReadFromDB("SHOW COLUMNS FROM table_" + TableDBName);
}
catch (Exception)
{
//Таблицы не существует, будем создавать
new_table = true;
}
if (new_table)
{
string CommandText = "CREATE TABLE `table_" + TableDBName + "` (";
//ПК
CommandText += "`ID` int(11) NOT NULL AUTO_INCREMENT";
foreach (DataRow row in neededStructure.Rows)
{
if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true)
{
CommandText += ",`" + row["ColumnDBName"] + "` MEDIUMTEXT DEFAULT NULL";
//CreateMultiRefTable(TableDBName, (string)row["ColumnDBName"]);
}
{
string field_type = ConvertTypeToSQL((string)row["ColumnType"]);
if (field_type != null)
{
CommandText += ",`" + row["ColumnDBName"] + "` " + field_type + " DEFAULT NULL";
}
}
}
CommandText += ", PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8";
DBFunctions.ExecuteCommand(CommandText);
}
else
{
//Создаем и меняем строки
foreach (DataRow row in neededStructure.Rows)
{
string col_type;
if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true)
{
//CreateMultiRefTable(TableDBName, (string)row["ColumnDBName"]);
col_type = "MEDIUMTEXT";
}
else
{
col_type = ConvertTypeToSQL((string)row["ColumnType"]);
}
{
//Ищем колонку
DataRow[] foundRows = CurrentStrurture.Select("Field = '" + row["ColumnDBName_Old"] + "'");
if (foundRows.Length == 0)
{
//Добавляем колонку
DBFunctions.ExecuteCommand("ALTER TABLE table_" + TableDBName + " ADD `" + row["ColumnDBName"] + "` " + col_type);
}
else
{
//Проверяем соответствие имени
if ((string)foundRows[0]["Field"] != (string)row["ColumnDBName"])
{
//Переименовываем колонку
try
{
DBFunctions.ExecuteCommand("ALTER TABLE `table_" + TableDBName + "` CHANGE `" + row["ColumnDBName_Old"] + "` `" + row["ColumnDBName"] + "` " + col_type);
row["ColumnDBName_Old"] = row["ColumnDBName"];
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
else if ((string)foundRows[0]["Type"] != col_type)
{
//Меняем тип
try
{
DBFunctions.ExecuteCommand("ALTER TABLE `table_" + TableDBName + "` MODIFY `" + row["ColumnDBName"] + "` " + col_type);
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
}
}
}
//Внесем изменения конфигурационную таблицу
if (neededStructure.GetChanges() != null)
{
TableStruct ts = new TableStruct();
ts.TableName = "tableconfig";
string[] p_keys = { "TableConfigID" };
ts.p_keys = p_keys;
string[] columns = { "ColumnDBName_Old" };
ts.columns = columns;
DBFunctions.WriteToDB(neededStructure, ts);
}
//Удалим лишние колонки
foreach (DataRow row in CurrentStrurture.Rows)
{
if ((string)row["Field"] == "ID")
{
continue;
}
DataRow[] foundRows = neededStructure.Select("ColumnDBName = '" + row["Field"] + "'");
if (foundRows.Length == 0)
{
//Удаляем колонку
DBFunctions.ExecuteCommand("ALTER TABLE table_" + TableDBName + " DROP `" + row["Field"] + "`");
}
}
//Удалим лишние таблицы с множественным выбором
//DataTable multi_ref_tables = DBFunctions.ReadFromDB("SHOW TABLES WHERE tables_in_" + DBFunctions.db_name + " LIKE 'MultiRef_" + TableDBName + "_%'");
//foreach(DataRow row in multi_ref_tables.Rows)
//{
// DataRow[] foundRows = neededStructure.Select("'MultiRef_" + TableDBName + "_' + ColumnDBName = '" + row["tables_in_" + DBFunctions.db_name] + "' AND ReferenceMultiSelect = 1");
/* if (foundRows.Length == 0)
* {
* //Удаляем таблицу
* DBFunctions.ExecuteCommand("DROP TABLE `" + row["tables_in_" + DBFunctions.db_name] + "`");
*
* }
* }*/
}
}
public static void UpdateSelectorScript()
{
//Получим таблицу с формулами
//Список формульных полей
DataTable dt_formula_fields = DBFunctions.ReadFromDB("SELECT concat('table_',TableDBName,'.',ColumnDBName) AS field FROM tableconfig WHERE ColumnType = 'Формула'");
string formula_query_text = "SELECT ID";
foreach (DataRow row in dt_formula_fields.Rows)
{
formula_query_text += "," + Convert.ToString(row["field"]) + " AS '" + Convert.ToString(row["field"]) + "'";
}
formula_query_text += " FROM table_credprogr";
DataTable dt_formulas = DBFunctions.ReadFromDB(formula_query_text);
//Получим текст условия
string clause_text;
DataTable dt_clause = DBFunctions.ReadFromDB("SELECT * FROM where_clauses");
if (dt_clause.Rows.Count > 0)
{
clause_text = (string)dt_clause.Rows[0]["Clause"];
}
else
{
return;
}
//Заменим макросы в условиях
DataTable dtMacros = DBFunctions.ReadFromDB("SELECT * FROM macros");
bool macros_found = true;
while (macros_found)
{
macros_found = false;
foreach (DataRow macro in dtMacros.Rows)
{
if (clause_text.IndexOf("[" + (string)macro["name"] + "]") != -1)
{
macros_found = true;
clause_text = clause_text.Replace("[" + (string)macro["name"] + "]", (string)macro["macro"]);
}
}
}
//Соберем текст запроса
string query_text = "SELECT table_credprogr.ID";
DataTable all_fields = DBFunctions.ReadFromDB("SELECT concat('table_',TableDBName,'.',ColumnDBName) AS field_name,ColumnName,ColumnType,ReferenceMultiSelect,ShowInOffer,TableDBName,ColumnDBName FROM tableconfig WHERE ShowInOffer = 1 OR UseInWhereClause = 1 OR ShowInOfferShort = 1");
if (all_fields.Rows.Count == 0)
{
System.Windows.Forms.MessageBox.Show("Не выбраны поля для отображения", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
foreach (DataRow row in all_fields.Rows)
{
query_text += ",";
if ((string)row["ColumnType"] == "Формула")
{
query_text += "CASE table_credprogr.ID ";
foreach (DataRow formula_row in dt_formulas.Rows)
{
query_text += " WHEN " + formula_row["ID"] + " THEN " + formula_row[(string)row["field_name"]];
}
query_text += " END AS '" + row["ColumnDBName"] + "'";
}
else
{
query_text += Convert.ToString(row["field_name"]) + " AS '" + row["ColumnDBName"] + "'";
}
}
query_text += " FROM table_credprogr LEFT JOIN table_clients ON table_clients.id=" + "[ClientID] ";
//Здесь формируем запрос по полям с ShowInOffer = 1
DataTable fields_to_show = DBFunctions.ReadFromDB("SELECT ColumnDBName,ColumnName,ColumnType,ColumnReference,ReferenceMultiSelect,ShowFullName FROM tableconfig WHERE ShowInOffer = 1 ORDER BY WebOrder");
string itog_query = "SELECT DISTINCT inner_select.ID";
string join_text = "";
foreach (DataRow row in fields_to_show.Rows)
{
itog_query += ",";
if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == false)
{
itog_query += "ref_name_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + "," + Convert.ToString(row["ShowFullName"]) + ") AS '" + row["ColumnName"] + "'";
}
else if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true)
{
itog_query += "multiref_names_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + ")" + " AS '" + row["ColumnName"] + "'";
}
else
{
itog_query += row["ColumnDBName"] + " AS '" + row["ColumnName"] + "'";
}
}
itog_query += " FROM (" + query_text + ") AS inner_select " + join_text + " WHERE ";
itog_query += clause_text.Replace("@", "");;
//Занесем результат в базу
//Удалим текущий скрипт
DBFunctions.ExecuteCommand("DELETE FROM scripts WHERE script_name = 'OfferSelect'");
DBFunctions.ExecuteCommand("INSERT INTO scripts VALUES('OfferSelect','" + itog_query.Replace("'", "\\'") + "')");
//Здесь формируем запрос по полям с ShowInOfferShort = 1
fields_to_show = DBFunctions.ReadFromDB("SELECT ColumnDBName,ColumnName,ColumnType,ColumnReference,ReferenceMultiSelect,ShowFullName FROM tableconfig WHERE ShowInOfferShort = 1 ORDER BY WebOrder");
itog_query = "SELECT DISTINCT inner_select.ID";
foreach (DataRow row in fields_to_show.Rows)
{
itog_query += ",";
if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == false)
{
itog_query += "ref_name_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + "," + Convert.ToString(row["ShowFullName"]) + ") AS '" + row["ColumnName"] + "'";
}
else if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true)
{
itog_query += "multiref_names_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + ")" + " AS '" + row["ColumnName"] + "'";
}
else
{
itog_query += row["ColumnDBName"] + " AS '" + row["ColumnName"] + "'";
}
}
itog_query += " FROM (" + query_text + ") AS inner_select WHERE ";
itog_query += clause_text.Replace("@", "");
//Занесем результат в базу
//Удалим текущий скрипт
DBFunctions.ExecuteCommand("DELETE FROM scripts WHERE script_name = 'OfferSelectShort'");
DBFunctions.ExecuteCommand("INSERT INTO scripts VALUES('OfferSelectShort','" + itog_query.Replace("'", "\\'") + "')");
}
static void UpdateReferenceStructure(string ref_db_name)
{
//Получим требуемую структуру
DataTable neededStructure = DBFunctions.ReadFromDB("SELECT * FROM referencesconfig WHERE ReferenceDBName = '" + ref_db_name + "'");
if (neededStructure.Rows.Count == 0)
{
System.Windows.Forms.MessageBox.Show("Неверное наименование справочника.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
bool hierarchical = Convert.ToBoolean(neededStructure.Rows[0]["Hierarchycal"]);
//Проверим есть ли такая таблица в БД
bool ref_data_exists = Convert.ToBoolean(DBFunctions.ReadFromDB("Show tables like 'ref_data_" + ref_db_name + "'").Rows.Count);
bool ref_hierarchy_exists = Convert.ToBoolean(DBFunctions.ReadFromDB("Show tables like 'ref_hierarchy_" + ref_db_name + "'").Rows.Count);
if ((hierarchical && ref_data_exists && ref_hierarchy_exists) || //Иерархический, есть обе таблицы
(!hierarchical && ref_data_exists && !ref_hierarchy_exists) //Не иерархический, есть одна таблица
)
{
//Всё ок, проверим соответствие состава колонок
DataTable CurrentStrurture = DBFunctions.ReadFromDB("SHOW COLUMNS FROM ref_data_" + ref_db_name);
//Создаем и меняем строки
foreach (DataRow row in neededStructure.Rows)
{
//Ищем колонку
DataRow[] foundRows = CurrentStrurture.Select("Field = '" + row["ColumnDBName_Old"] + "'");
if (foundRows.Length == 0)
{
//Добавляем колонку
DBFunctions.ExecuteCommand("ALTER TABLE ref_data_" + ref_db_name + " ADD `" + row["ColumnDBName"] + "` " + ConvertTypeToSQL((string)row["ColumnType"]));
}
else
{
//Проверяем соответствие имени
if ((string)foundRows[0]["Field"] != (string)row["ColumnDBName"])
{
//Переименовываем колонку
try
{
DBFunctions.ExecuteCommand("ALTER TABLE `ref_data_" + ref_db_name + "` CHANGE `" + row["ColumnDBName_Old"] + "` `" + row["ColumnDBName"] + "` " + ConvertTypeToSQL((string)row["ColumnType"]));
row["ColumnDBName_Old"] = row["ColumnDBName"];
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
else if ((string)foundRows[0]["Type"] != ConvertTypeToSQL((string)row["ColumnType"]))
{
//Меняем тип
try
{
DBFunctions.ExecuteCommand("ALTER TABLE `ref_data_" + ref_db_name + "` MODIFY `" + row["ColumnDBName"] + "` " + ConvertTypeToSQL((string)row["ColumnType"]));
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
}
}
//Внесем изменения конфигурационную таблицу
if (neededStructure.GetChanges() != null)
{
TableStruct ts = new TableStruct();
ts.TableName = "referencesconfig";
string[] p_keys = { "ReferenceConfigID" };
ts.p_keys = p_keys;
string[] columns = { "ColumnDBName_Old" };
ts.columns = columns;
DBFunctions.WriteToDB(neededStructure, ts);
}
//Удалим лишние колонки
foreach (DataRow row in CurrentStrurture.Rows)
{
if ((string)row["Field"] == "id" || (string)row["Field"] == "ID")
{
continue;
}
if ((string)row["Field"] == "parentid" || (string)row["Field"] == "ParentID")
{
continue;
}
DataRow[] foundRows = neededStructure.Select("ColumnDBName = '" + row["Field"] + "'");
if (foundRows.Length == 0)
{
//Удаляем колонку
DBFunctions.ExecuteCommand("ALTER TABLE ref_data_" + ref_db_name + " DROP `" + row["Field"] + "`");
}
}
//Создадим хранимые процедуры и триггеры
string ref_create_script = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'Reference_Create'");
ref_create_script = ref_create_script.Replace("[RefDBName]", ref_db_name);
DBFunctions.ExecuteScript(ref_create_script);
return;
}
if (ref_data_exists || ref_hierarchy_exists)
{
if (System.Windows.Forms.MessageBox.Show("Сменился тип справочника " + ref_db_name + " данные справочника будут удалены. Продолжить?", "Вопрос", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
//Удалим таблицы
if (ref_data_exists)
{
DBFunctions.ExecuteCommand("DROP TABLE `ref_data_" + ref_db_name + "`");
}
if (ref_hierarchy_exists)
{
DBFunctions.ExecuteCommand("DROP TABLE `ref_hierarchy_" + ref_db_name + "`");
}
//Создадим новые таблицы
//Таблица с данными
string CommandText = "CREATE TABLE `ref_data_" + ref_db_name + "` (";
CommandText += "`ID` int(11) NOT NULL AUTO_INCREMENT";
CommandText += ",`ParentID` int(11) DEFAULT 0";
foreach (DataRow row in neededStructure.Rows)
{
if (row["ColumnType"] == System.DBNull.Value)
{
continue;
}
string field_type = ConvertTypeToSQL((string)row["ColumnType"]);
if (field_type != null)
{
CommandText += ",`" + row["ColumnDBName"] + "` " + field_type + " DEFAULT NULL";
}
}
CommandText += ", PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8";
DBFunctions.ExecuteCommand(CommandText);
//Таблица с иерархией
if (hierarchical)
{
CommandText = "CREATE TABLE `ref_hierarchy_" + ref_db_name + "` (";
CommandText += "`ElemID` int(11) NOT NULL";
CommandText += ",`ParentID` int(11) NOT NULL";
CommandText += ",`Level` int(11) NOT NULL";
CommandText += ", PRIMARY KEY (`ElemID`,`ParentID`,`Level`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8";
DBFunctions.ExecuteCommand(CommandText);
}
//Создадим хранимые процедуры и триггеры
string ref_create_script_inner = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'Reference_Create'");
ref_create_script_inner = ref_create_script_inner.Replace("[RefDBName]", ref_db_name);
DBFunctions.ExecuteScript(ref_create_script_inner);
}
