private void tsbDown_Click(object sender, EventArgs e)
{
DataRow curr_row = GreenLight.Tools.FindCurrentRow(dgTableConfig);
int curr_id = (int)curr_row["TableConfigID"];
//Получим следующий номер
object next_id_obj = DBFunctions.ReadScalarFromDB("SELECT TableConfigID From tableconfig WHERE TableConfigID > " + Convert.ToString(curr_row["TableConfigID"]) +
" AND TableDBName = '" + curr_row["TableDBName"] + "' ORDER BY TableConfigID LIMIT 1");
int next_id;
if (next_id_obj != null)
{
next_id = (int)next_id_obj;
}
else
{
return;
}
DBFunctions.ExecuteScript("CALL SwapLinesInTableConfig(" + Convert.ToString(curr_id) + "," + Convert.ToString(next_id) + ")");
int rw = dgTableConfig.CurrentCell.RowIndex;
int col = dgTableConfig.CurrentCell.ColumnIndex;
FillDataGrid();
dgTableConfig.CurrentCell = dgTableConfig[col, rw + 1];
}
private void tsbEdit_Click(object sender, EventArgs e)
{
DataRow current_row = GreenLight.Tools.FindCurrentRow(dgTableData);
if (current_row == null)
{
return;
}
var tre = new Forms.TableRecordEditorNew(cbTables.Text, current_row, HaveWriteRights());
if (tre.ShowDialog() == DialogResult.OK)
{
bool row_added = false;
if (current_row.RowState == DataRowState.Added)
{
row_added = true;
}
DBFunctions.WriteToDB(dt_table_data, ts);
if (row_added)
{
current_row["ID"] = Convert.ToInt32(DBFunctions.ReadScalarFromDB("SELECT LAST_INSERT_ID()"));
}
}
else
{
dt_table_data.RejectChanges();
}
}
public UniversalQuestionaryOfferSelect(int c_id, DataTable dt)
{
InitializeComponent();
client_id = c_id;
dt_offers = new DataTable();
dt_offers.Columns.Add("Selected", Type.GetType("System.Boolean"));
dt_offers.Columns.Add("ID", Type.GetType("System.Int32"));
dt_offers.Columns.Add("Bank_LK", Type.GetType("System.String"));
dt_offers.Columns.Add("Programma", Type.GetType("System.String"));
foreach (DataRow row in dt.Rows)
{
DataRow newrow = dt_offers.Rows.Add();
newrow["Selected"] = false;
newrow["ID"] = row["ID"];
//2014_11_08
//newrow["Bank_LK"] = row["Банк,ЛК(ф)(ю)"];
//newrow["Programma"] = row["Программа(ф)(ю)"];
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("id", row["ID"]);
newrow["Bank_LK"] = DBFunctions.ReadScalarFromDB("SELECT bank_lk FROM table_credprogr WHERE id=@id", parameters);
newrow["Programma"] = DBFunctions.ReadScalarFromDB("SELECT programma FROM table_credprogr WHERE id=@id", parameters);
//2014_11_08
}
}
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 update_activity_timer_Tick(object sender, EventArgs e)
{
//Проверим не пора ли нам завершить работу
bool shut_down_needed = Convert.ToBoolean(DBFunctions.ReadScalarFromDB("SELECT shutdown FROM force_shutdown"));
if (shut_down_needed)
{
Close();
}
UpdateSessionInfo();
}
private void UpdateSessionInfo()
{
DBFunctions.ExecuteScript("DELETE FROM active_sessions WHERE SessionID = " + Convert.ToString(Session_ID));
DateTime curr_datetime = (DateTime)DBFunctions.ReadScalarFromDB("SELECT current_timestamp()");
DBFunctions.ExecuteScript("INSERT INTO active_sessions SET SessionID = " + Convert.ToString(Session_ID) + ","
+ "Computer_Name='" + Environment.MachineName + "',"
+ "DomainUser='******',"
+ "LastActivity='" + curr_datetime.ToString("s") + "',"
+ "SessionStart='" + session_start.ToString("s") + "'");
}
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);
}
}
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);
}
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);
}
}
private void btnNext_Click(object sender, EventArgs e)
{
bool is_variation = (bool)row_question["question_variation"];
object next_question = DBNull.Value;
//Если анкета с фильтром, то щелкаем вопросы, пока не найдем подходящий
if (!questionary_use_filter)
{
if (is_variation && tbAnswer.Tag != null)
{
next_question = DBFunctions.ReadScalarFromDB("SELECT answer_next_question_id FROM questionary_answers WHERE question_id = " + current_question_id +
" AND answer = " + tbAnswer.Tag);
}
;
if (next_question == DBNull.Value)
{
next_question = row_question["question_next"];
}
}
else
{
object answerTag = tbAnswer.Tag;
if (is_variation && tbAnswer.Tag != null)
{
next_question = DBFunctions.ReadScalarFromDB("SELECT answer_next_question_id FROM questionary_answers WHERE question_id = " + current_question_id +
" AND answer = " + tbAnswer.Tag);
}
;
if (next_question == DBNull.Value || next_question == null)
{
next_question = row_question["question_next"];
}
Dictionary <string, object> parameters;
while (next_question != DBNull.Value)
{
if (dt_filter_allowed_questions.Select("question_id = " + Convert.ToString(next_question)).Length > 0)
{
break;
}
parameters = new Dictionary <string, object>();
parameters.Add("questionary_id", questionary_id);
parameters.Add("q_id", next_question);
next_question = DBFunctions.ReadScalarFromDB("SELECT question_next FROM questionary_questions WHERE questionary_id=@questionary_id AND question_id = @q_id", parameters);
}
}
QuestionInfo qi = new QuestionInfo();
qi.question_id = current_question_id;
qi.answer_text = tbAnswer.Text;
qi.question_text = lbQuestionText.Text;
try
{
qi.answer_id = (int)tbAnswer.Tag;
}
catch (Exception) { };
if ((string)row_question["question_type"] == "Справочник")
{
row_table_record[(string)row_question["question_dest_column_db_name"]] = qi.answer_id;
}
else
{
row_table_record[(string)row_question["question_dest_column_db_name"]] = qi.answer_text;
}
question_history.Add(qi);
UpdateQuestionHistory();
if (next_question == DBNull.Value)
{
//Завершаем анкетирование, пишем в базу
DBFunctions.WriteToDB(dt_table_data, ts);
Close();
return;
}
current_question_id = (int)next_question;
LoadQuestion();
}
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);
}
private void MainForm_Load(object sender, EventArgs e)
{
//Загрузим параметры, проверим валидность
LocalParameters.LoadParameters();
string parameters_error_text = "Необходимо заполнить следующие параметры: " + Environment.NewLine;
bool parameters_error = false;
if (LocalParameters.MySQLServer == "")
{
parameters_error = true;
parameters_error_text += " - Сервер MySQL" + Environment.NewLine;
}
if (LocalParameters.MySQLDatabase == "")
{
parameters_error = true;
parameters_error_text += " - База данных MySQL" + Environment.NewLine;
}
if (LocalParameters.MySQLUser == "")
{
parameters_error = true;
parameters_error_text += " - Логин MySQL" + Environment.NewLine;
}
if (LocalParameters.MySQLPassword == "")
{
parameters_error = true;
parameters_error_text += " - Пароль MySQL" + Environment.NewLine;
}
if (parameters_error)
{
MessageBox.Show(parameters_error_text, "Необходимо заполнить параметры.");
LocalParameters.EditParameters();
}
if (LocalParameters.MySQLServer == "" ||
LocalParameters.MySQLDatabase == "" ||
LocalParameters.MySQLUser == "" ||
LocalParameters.MySQLPassword == "")
{
MessageBox.Show("Не все обязательные параметры были заполнены. Работа программы будет завершена.");
Close();
}
DBFunctions.m_frm = this;
update_activity_timer = new Timer();
DBFunctions.login_from_parameters = true;
DBFunctions.Init();
Auth.AuthForm af = new Auth.AuthForm();
if (af.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
Close();
return;
}
UpdateVisibility();
//Если включен режим завершения работы, то не дадим пользователю запустить систему
bool shut_down_needed = Convert.ToBoolean(DBFunctions.ReadScalarFromDB("SELECT shutdown FROM force_shutdown"));
if (shut_down_needed)
{
ShowAutoClosingMessageBox("На сервере установлен запрет на запуск программы. Программа будет закрыта", "Запрет на запуск");//, MessageBoxButtons.OK, MessageBoxIcon.Error);
Close();
}
UpdateVisibility();
session_start = (DateTime)DBFunctions.ReadScalarFromDB("SELECT current_timestamp()");
Session_ID = (int)DBFunctions.ReadScalarFromDB("SELECT new_session_id()");
UpdateSessionInfo();
update_activity_timer.Tick += new EventHandler(update_activity_timer_Tick);
update_activity_timer.Interval = 20000;
update_activity_timer.Start();
}
public static string GetRefName(string ref_db_name, int id)
{
return((string)DBFunctions.ReadScalarFromDB("SELECT RefName FROM ref_data_" + ref_db_name + " WHERE ID = " + Convert.ToString(id)));
}
private void StringReplaseSettings_Load(object sender, EventArgs e)
{
//Получим наименование ключевого поля
try
{
key_field_db_name = DBFunctions.GetGlobalParameter("StringReplaceKeyField");
}
catch (Exception err)
{
System.Windows.Forms.MessageBox.Show(err.Message, "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
Close();
}
//Теперь пользовательское наименование для размещения в label на тулстрипе
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("coldbname", key_field_db_name);
tslKeyFieldName.Text = (string)DBFunctions.ReadScalarFromDB("SELECT ColumnName FROM tableconfig WHERE ColumnDBName = @coldbname", parameters);
//Получим все возможные значения ключевого поля
cbKeyFieldValues.Items.Clear();
key_field_values = new ArrayList();
DataTable dt_key_field_values = DBFunctions.ReadFromDB("SELECT DISTINCT " + key_field_db_name + " AS key_field FROM table_credprogr ORDER BY " + key_field_db_name);
foreach (DataRow kf_row in dt_key_field_values.Rows)
{
cbKeyFieldValues.Items.Add(kf_row["key_field"]);
key_field_values.Add(kf_row["key_field"]);
}
//Заполним справочники
reference_db_names = new ArrayList();
DataTable dt_references = DBFunctions.ReadFromDB("SELECT referencesconfig.ReferenceDBName,referencesconfig.ReferenceName FROM referencesconfig ORDER BY referencename");
foreach (DataRow ref_row in dt_references.Rows)
{
cbReferenceNames.Items.Add(ref_row["ReferenceName"]);
reference_db_names.Add(ref_row["ReferenceDBName"]);
}
dgReplaceStrings.AutoGenerateColumns = false;
dgReplaceStrings.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataGridViewTextBoxColumn ref_id_column = new DataGridViewTextBoxColumn();
ref_id_column.Name = "ref_id";
ref_id_column.HeaderText = "ID элемента справочника";
ref_id_column.DataPropertyName = "table_reference_id";
ref_id_column.Visible = false;
dgReplaceStrings.Columns.Add(ref_id_column);
DataGridViewComboBoxColumn ref_name_column = new DataGridViewComboBoxColumn();
ref_name_column.Name = "ref_name";
ref_name_column.HeaderText = "Элемент справочника";
ref_name_column.DataPropertyName = "table_reference_value";
dgReplaceStrings.Columns.Add(ref_name_column);
DataGridViewTextBoxColumn replace_string_column = new DataGridViewTextBoxColumn();
replace_string_column.Name = "replace_string";
replace_string_column.HeaderText = "Строка для замены";
replace_string_column.DataPropertyName = "replace_string";
dgReplaceStrings.Columns.Add(replace_string_column);
dt_replaces = new DataTable();
dgReplaceStrings.DataSource = dt_replaces;
cbReferenceNames.SelectedIndex = 0;
cbKeyFieldValues.SelectedIndex = 0;
TestRights();
}
