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];
}
static void CreateMultiRefTable(string TableDBName, string ColumnDBName)
{
try//Вдруг такая таблица уже есть?
{
DBFunctions.ExecuteCommand("CREATE TABLE `MultiRef_" + TableDBName + "_" + ColumnDBName + "` (`TableID` int(11) NOT NULL,`RefID` int(11) NOT NULL, PRIMARY KEY (`TableID`,`RefID`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8");
} catch (Exception) {}
}
private void SaveData()
{
if (!Auth.AuthModule.rights.string_replace.write)
{
return;
}
dgReplaceStrings.EndEdit();
Validate();
dt_replaces.AcceptChanges();
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("tkfv", key_field_values[current_key_field_index]);
parameters.Add("trdn", reference_db_names[current_ref_db_names_index]);
DBFunctions.ExecuteCommand("DELETE FROM replace_strings WHERE table_key_field_value = @tkfv AND table_reference_db_name = @trdn", parameters);
foreach (DataRow replace_row in dt_replaces.Rows)
{
parameters = new Dictionary <string, object>();
parameters.Add("tkfv", key_field_values[current_key_field_index]);
parameters.Add("trdn", reference_db_names[current_ref_db_names_index]);
parameters.Add("refid", replace_row["table_reference_id"]);
parameters.Add("refvalue", replace_row["table_reference_value"]);
parameters.Add("replace_string", replace_row["replace_string"]);
DBFunctions.ExecuteCommand("INSERT INTO replace_strings VALUES(@tkfv,@trdn,@refid,@refvalue,@replace_string)", parameters);
}
}
static void DropMultiRefTable(string TableDBName, string ColumnDBName)
{
try
{
DBFunctions.ExecuteCommand("DROP TABLE `MultiRef_" + TableDBName + "_" + ColumnDBName + "`");
}
catch (Exception) { }
}
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 tsbInvertUseInWhereClause_Click(object sender, EventArgs e)
{
DataRow curr_row = GreenLight.Tools.FindCurrentRow(dgColumnNames);
if (curr_row != null)
{
curr_row["UseInWhereClause"] = !((bool)curr_row["UseInWhereClause"]);
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("id", (int)curr_row["TableConfigID"]);
parameters.Add("use_in_clause", (bool)curr_row["UseInWhereClause"]);
DBFunctions.ExecuteCommand("UPDATE tableconfig SET UseInWhereClause = @use_in_clause WHERE TableConfigID = @id", parameters);
}
}
private void tvReference_DragDrop(object sender, DragEventArgs e)
{
if (e.Effect == DragDropEffects.Move)
{
TreeNode hoveringNode = GetHoveringNode(e.X, e.Y);
if (hoveringNode != null)
{
TreeNode draggingNode = e.Data.GetData(typeof(TreeNode)) as TreeNode;
if (draggingNode != null)
{
/*if (draggingNode.Level == hoveringNode.Level)
* {
* if (draggingNode.Level != 0)
* {
* draggingNode.Remove();
* hoveringNode.Nodes.Insert(hoveringNode.Index + 1, draggingNode);
* }
* if (draggingNode.Level == 0)
* {
*
* }
*
*
* }*/
if (!ContainsNode(draggingNode, hoveringNode))
{
draggingNode.Remove();
hoveringNode.Nodes.Insert(0, draggingNode);
DBFunctions.ExecuteCommand("UPDATE ref_data_" + reference_db_name + " SET ParentID = " + Convert.ToString(hoveringNode.Tag) + " WHERE ID = " + Convert.ToString(draggingNode.Tag) + ";");
}
}
}
else
{
TreeNode draggingNode = e.Data.GetData(typeof(TreeNode)) as TreeNode;
if (draggingNode != null)
{
draggingNode.Remove();
tvReference.Nodes.Add(draggingNode);
DBFunctions.ExecuteCommand("UPDATE ref_data_" + reference_db_name + " SET ParentID ='0' WHERE ID = " + Convert.ToString(draggingNode.Tag) + ";");
}
}
}
}
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 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"]));
}
}
}
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;
}
private void SaveData()
{
if (!Auth.AuthModule.rights.questionary_editor.write)
{
return;
}
foreach (DataRow filter_row in dt_filter.Rows)
{
for (int col_num = 2; col_num < dt_filter.Columns.Count; col_num++)
{
//Очистим текущие данные
DBFunctions.ExecuteCommand("DELETE FROM questionary_filter WHERE questionary_id = " + Convert.ToString(questionary_id) + " AND question_id = " + filter_row["ID вопроса"] +
" AND filter_id = " + dt_filter.Columns[col_num].ColumnName);
//Занесем новые при необходимости
if ((bool)filter_row[col_num] == true)
{
DBFunctions.ExecuteCommand("INSERT INTO questionary_filter VALUES(" + Convert.ToString(questionary_id) + "," + dt_filter.Columns[col_num].ColumnName + "," +
filter_row["ID вопроса"] + ")");
}
}
}
dt_filter.AcceptChanges();
}
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);
}
private void tsbCopy_Click(object sender, EventArgs e)
{
dgColumns.EndEdit();
dgTableData.EndEdit();
string query_text = "UPDATE ";
query_text += "table_" + table_db_names[cbTables.SelectedIndex] + " AS dest, ";
query_text += "table_" + table_db_names[cbTables.SelectedIndex] + " AS src SET ";
bool first = true;
foreach (DataRow column_row in dt_columns.Rows)
{
if (Convert.ToBoolean(column_row["Checked"]))
{
if (first)
{
first = false;
}
else
{
query_text += " , ";
}
query_text += "dest.`" + column_row["ColumnDBName"] + "` = src.`" + column_row["ColumnDBName"] + "`";
}
}
int source_id = -1;
foreach (DataRow row in dt_table.Rows)
{
if (Convert.ToBoolean(row["Source"]))
{
if (source_id == -1)
{
source_id = (int)row["ID"];
}
else
{
System.Windows.Forms.MessageBox.Show("Выбрано больше одного источника", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
}
}
if (source_id == -1)
{
System.Windows.Forms.MessageBox.Show("Не выбрано ни одного источника", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
query_text += " WHERE dest.id = [d_id] AND src.id = " + Convert.ToString(source_id);
foreach (DataRow row in dt_table.Rows)
{
if (Convert.ToBoolean(row["Destination"]))
{
DBFunctions.ExecuteCommand(query_text.Replace("[d_id]", Convert.ToString(row["ID"])));
}
}
System.Windows.Forms.MessageBox.Show("Копирование завершено.", "Успешное завершение", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
}
