/// <summary>
/// Сохранение
/// </summary>
public void Save(object parameter = null)
{
if (CanSave())
{
cti.ThreadProgress.ShowWait();
try
{
if (IsRegistered)
{
using (SqlWork sqlWork = new SqlWork(true))
{
sqlWork.sql =
String.Format(@"UPDATE sys_scheme.table_info
SET name_map = @name_map, photo = {1}, lablefiled = '{2}', pk_fileld = '{3}', geom_field = '{4}'
WHERE id = {5};",
Text,
HasFiles,
(LabelField != null) ? LabelField.Name : "",
(PkField != null) ? PkField.Name : "",
(GeomField != null) ? GeomField.Name : "",
Id);
sqlWork.ExecuteNonQuery(new List <Params>()
{
new Params("@name_map", Text, NpgsqlDbType.Varchar)
});
}
}
else
{
using (SqlWork sqlWork = new SqlWork(true))
{
sqlWork.BeginTransaction();
sqlWork.sql = "SELECT nextval('sys_scheme.table_info_id_seq')::integer as val";
int idTable = sqlWork.ExecuteScalar <Int32>();
sqlWork.CloseReader();
sqlWork.sql =
String.Format(@"INSERT INTO sys_scheme.table_info
(id, name_db, name_map, geom_type, type, map_style, scheme_name, photo, pk_fileld, lablefiled, geom_field)
VALUES ({0}, '{1}',@name_map, {3}, {4}, {5}, '{6}', {7}, '{8}', '{9}', '{10}')",
idTable,
Name,
Text,
Convert.ToInt32(GeomType),
Convert.ToInt32(TableType),
IsMapStyle,
ParentScheme.SchemeName,
HasFiles,
PkField.Name,
(LabelField != null) ? LabelField.Name : "",
(GeomField != null) ? GeomField.Name : "");
sqlWork.ExecuteNonQuery(new List <Params>()
{
new Params("@name_map", Text, NpgsqlDbType.Varchar)
});
if (IsMapStyle && (TableType == ERTableType.Interval || TableType == ERTableType.Catalog))
{
bool exists_style = false;
sqlWork.sql = String.Format("SELECT * FROM {0}.{1} LIMIT 1", ParentScheme.SchemeName, Name);
sqlWork.ExecuteReader();
if (sqlWork.CanRead())
{
for (int i = 0; sqlWork.GetFiealdCount() > i; i++)
{
if (sqlWork.GetFieldName(i) == "exists_style")
{
exists_style = true;
break;
}
}
}
sqlWork.CloseReader();
if (!exists_style)
{
MessageBoxResult dr = MessageBox.Show(
Rekod.Properties.Resources.PgReg_NeedAdditionalFields,
Rekod.Properties.Resources.PgReg_StyleCreation, MessageBoxButton.YesNo);
if (dr == MessageBoxResult.Yes)
{
sqlWork.sql =
String.Format("ALTER TABLE {0}.{1} " +
"ADD exists_style boolean DEFAULT true," +
"ADD fontname character varying DEFAULT 'Map Symbols'," +
"ADD fontcolor integer DEFAULT 16711680," +
"ADD fontframecolor integer DEFAULT 16711680," +
"ADD fontsize integer DEFAULT 12," +
"ADD symbol integer DEFAULT 35," +
"ADD pencolor integer DEFAULT 16711680," +
"ADD pentype integer DEFAULT 2," +
"ADD penwidth integer DEFAULT 1," +
"ADD brushbgcolor bigint DEFAULT 16711680," +
"ADD brushfgcolor integer DEFAULT 16711680," +
"ADD brushstyle integer DEFAULT 0," +
"ADD brushhatch integer DEFAULT 1;",
ParentScheme.SchemeName, Name);
sqlWork.ExecuteNonQuery();
}
}
}
// Регистрация полей
foreach (var f in Fields)
{
if (f.ToBeRegistered)
{
if (f.DataType == 0)
{
f.DataType = ERDataType.Text;
}
sqlWork.CloseReader();
sqlWork.sql =
String.Format(
@"INSERT INTO sys_scheme.table_field_info(id_table, name_db, name_map, type_field, visible, name_lable, is_reference, is_interval)
VALUES ({0}, '{1}', '{2}', {3}, {4}, @name_lable, {6}, {7});",
idTable, f.Name, f.Name, Convert.ToInt32(f.DataType), true, f.Description, false, false);
sqlWork.ExecuteNonQuery(new List <Params>()
{
new Params("@name_lable", f.Description, NpgsqlDbType.Varchar)
});
}
}
sqlWork.CloseReader();
if (HasFiles)
{
bool exist = false;
sqlWork.sql =
String.Format("SELECT EXISTS(SELECT * FROM pg_tables WHERE schemaname='{0}' AND tablename='photo_{1}');",
ParentScheme.SchemeName,
Name);
exist = sqlWork.ExecuteScalar <Boolean>();
if (exist)
{
bool est_v_tabl = false;
sqlWork.sql = String.Format("SELECT EXISTS(SELECT id FROM sys_scheme.table_photo_info WHERE id_table = {0});", idTable);
est_v_tabl = sqlWork.ExecuteScalar <Boolean>();
if (!est_v_tabl)
{
sqlWork.sql =
String.Format("INSERT INTO sys_scheme.table_photo_info(id_table, photo_table, photo_field, photo_file, id_field_tble)" +
" VALUES ({0}, 'photo_{1}', 'id_obj', 'file', '{2}');",
idTable, Name, PkField.Name);
sqlWork.ExecuteNonQuery();
}
}
else
{
sqlWork.sql = String.Format("SELECT sys_scheme.set_photo_table({0}, true)", idTable);
sqlWork.ExecuteNonQuery();
}
}
else
{
sqlWork.sql = String.Format("DELETE FROM sys_scheme.table_photo_info WHERE id_table = {0};", Id);
sqlWork.ExecuteNonQuery();
}
sqlWork.sql = String.Format("SELECT sys_scheme.set_table_rights_for_admins({0});", idTable.ToString());
sqlWork.ExecuteNonQuery();
sqlWork.EndTransaction();
IsRegistered = true;
foreach (var f in Fields)
{
if (f.ToBeRegistered)
{
f.IsRegistered = true;
}
}
Id = idTable;
}
}
if (IsSscActive)
{
if (SscGroup != null)
{
Interfaces.tablesInfo tableInfo =
new Interfaces.tablesInfo()
{
idTable = Id,
nameDB = Name,
nameMap = Text,
pkField = PkField.Name,
geomFieldName = (GeomField == null) ? null : GeomField.Name,
lableFieldName = (LabelField == null) ? null : LabelField.Name,
TypeGeom = (Interfaces.TypeGeometry)GeomType
};
tableInfo.ListField = new List <Interfaces.fieldInfo>();
foreach (var f in Fields.Where(p => p.IsRegistered))
{
tableInfo.ListField.Add(new Interfaces.fieldInfo()
{
nameDB = Name, nameMap = Text
});
}
MapAdmin.RegisterTable(tableInfo, SscGroup);
}
}
cti.ThreadProgress.Close();
MessageBox.Show(Rekod.Properties.Resources.PgReg_ChangesSaved);
}
catch (Exception ex)
{
cti.ThreadProgress.Close();
MessageBox.Show(ex.Message);
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text != "" && textBox2.Text != "")
{
if (Program.sscUser != null && this.sscGroupCmb.SelectedItem == null)
{
MessageBox.Show("Не указана группа!", Rekod.Properties.Resources.error, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string name_scheme = classesOfMetods.getTableInfo(idT).nameSheme;
string name_table = FindAcceptableTableName(textBox1.Text, name_scheme);
if (String.IsNullOrEmpty(name_table))
{
return;
}
string nameField = "";
int id_filed = 0, id_table = 0;
string type_field = "", field_list = "", insert_sql = "", type_geom = "";
SqlWork sqlCmd = new SqlWork();
sqlCmd.sql = "SELECT nextval('sys_scheme.table_info_id_seq')::INTEGER";
sqlCmd.Execute(false);
if (sqlCmd.CanRead())
{
id_table = sqlCmd.GetInt32(0);
}
sqlCmd.Close();
sqlCmd = new SqlWork();
sqlCmd.sql = "SELECT type FROM geometry_columns WHERE f_table_schema like '" + classesOfMetods.getTableInfo(idT).nameSheme
+ "' AND f_table_name like '" + classesOfMetods.getTableInfo(idT).nameDB +
"' AND f_geometry_column like '" + classesOfMetods.getTableInfo(idT).geomFieldName + "';";
sqlCmd.Execute(false);
if (sqlCmd.CanRead())
{
type_geom = sqlCmd.GetValue <string>(0);
}
sqlCmd.Close();
string sql = "CREATE TABLE " + name_scheme +
"." + name_table + " ( " +
classesOfMetods.getTableInfo(idT).pkField + " serial NOT NULL, ";
for (int i = 0; checkedListBox1.CheckedItems.Count > i; i++)
{
id_filed = ((itemObj)checkedListBox1.CheckedItems[i]).Id_o;
field_list = field_list + id_filed + ",";
nameField = classesOfMetods.getFieldInfo(id_filed).nameDB;
switch (classesOfMetods.getFieldInfo(id_filed).type)
{
case 1:
type_field = " integer DEFAULT 0, ";
break;
case 2:
type_field = " character varying, ";
break;
case 3:
type_field = " date, ";
break;
case 4:
type_field = " timestamp with time zone, ";
break;
case 5:
type_field = " geometry, ";
break;
case 6:
type_field = " numeric DEFAULT 0, ";
break;
}
sql = sql + nameField + type_field;
insert_sql = insert_sql + nameField + ",";
}
List <fieldInfo> fInfo = classesOfMetods.getFieldInfoTable(idT);
for (int i = 0; fInfo.Count > i; i++)
{
itemObj item = new itemObj(fInfo[i].idField, fInfo[i].nameMap, fInfo[i].nameDB);
if (pk_field == fInfo[i].nameDB || geom_field == fInfo[i].nameDB)
{
field_list = field_list + fInfo[i].idField + ",";
insert_sql = insert_sql + fInfo[i].nameDB + ",";
}
}
field_list = field_list.Substring(0, field_list.Length - 1);
insert_sql = insert_sql.Substring(0, insert_sql.Length - 1);
sql = sql + "CONSTRAINT " + name_table + "_pkey PRIMARY KEY (" + pk_field + ") );" +
"ALTER TABLE " + classesOfMetods.getTableInfo(idT).nameSheme + "." + name_table +
" OWNER TO admin_" + Program.connString["DataBase"] + ";";
if (classesOfMetods.getTableInfo(idT).type == 1)
{
sql = sql + " SELECT AddGeometryColumn('" + classesOfMetods.getTableInfo(idT).nameSheme +
"', '" + name_table + "','" + geom_field + "'," +
classesOfMetods.getTableInfo(idT).srid.ToString() + ",'" + type_geom + "',2); ";
}
sql = sql + "INSERT INTO sys_scheme.table_info( " +
"id, scheme_name, name_db, name_map, lablefiled, map_style, geom_field, " +
"style_field, geom_type, type, default_style, fontname, fontcolor, " +
"fontframecolor, fontsize, symbol, pencolor, pentype, penwidth, " +
"brushbgcolor, brushfgcolor, brushstyle, brushhatch, read_only, " +
"id_style, pk_fileld, is_style, source_layer, image_column, " +
"angle_column, use_bounds, min_scale, max_scale, default_visibl, " +
"range_colors, range_column, " +
"precision_point, type_color, min_color, min_val, max_color, max_val, " +
"use_min_val, null_color, use_null_color, hidden, use_max_val) " +
"SELECT " + id_table.ToString() + ", scheme_name, '" + name_table + "', '" +
textBox2.Text + "', lablefiled, map_style, geom_field, " +
"style_field, geom_type, type, default_style, fontname, fontcolor, " +
"fontframecolor, fontsize, symbol, pencolor, pentype, penwidth, " +
"brushbgcolor, brushfgcolor, brushstyle, brushhatch, read_only, " +
"id_style, pk_fileld, is_style, source_layer, image_column, " +
"angle_column, use_bounds, min_scale, max_scale, default_visibl, " +
"range_colors, range_column, " +
"precision_point, type_color, min_color, min_val, max_color, max_val, " +
"use_min_val, null_color, use_null_color, hidden, use_max_val " +
"FROM sys_scheme.table_info " +
"WHERE id = " + idT.ToString() + ";";
sql = sql + "INSERT INTO sys_scheme.table_field_info( " +
"id_table, name_db, name_map, type_field, visible, name_lable, " +
"is_reference, is_interval, is_style, ref_table, ref_field, ref_field_end, " +
"ref_field_name, read_only) " +
"SELECT " + id_table.ToString() + ", name_db, name_map, type_field, visible, name_lable, " +
"is_reference, is_interval, is_style, ref_table, ref_field, ref_field_end, " +
"ref_field_name, read_only " +
"FROM sys_scheme.table_field_info " +
"WHERE id in (" + field_list + ") ORDER BY num_order;";
sql = sql + " INSERT INTO " + classesOfMetods.getTableInfo(idT).nameSheme +
"." + name_table + " (" + insert_sql + ") SELECT " + insert_sql + " FROM " +
classesOfMetods.getTableInfo(idT).nameSheme + "." + classesOfMetods.getTableInfo(idT).nameDB + ";";
sql += string.Format(
"INSERT INTO {0}.table_right (id_table, id_user, read_data, write_data) SELECT {1}, id, true, true FROM {0}.user_db WHERE admin=true;",
Program.scheme,
id_table);
if (!Program.user_info.admin)
{
sql += string.Format(
"INSERT INTO {0}.table_right (id_table, id_user, read_data, write_data) VALUES({1},{2},true,true);",
Program.scheme,
id_table,
Program.user_info.id_user);
}
sqlCmd = new SqlWork();
sqlCmd.sql = sql;
if (sqlCmd.Execute(true))
{
//Установить значение сиквенса
string sequenceSetError = null;
try
{
sqlCmd.sql =
String.Format("SELECT \"{0}\" FROM \"{1}\".\"{2}\" ORDER BY \"{0}\" DESC LIMIT 1",
pk_field,
name_scheme,
name_table);
int?lastGid = sqlCmd.ExecuteScalar <int?>();
sqlCmd.CloseReader();
if (lastGid != null)
{
sqlCmd.sql =
String.Format("SELECT pg_get_serial_sequence('{0}.{1}', '{2}')",
name_scheme,
name_table,
pk_field);
sqlCmd.Connection.Open();
String sequenceName = sqlCmd.ExecuteScalar <String>();
sqlCmd.CloseReader();
sqlCmd.sql = String.Format("SELECT setval('{0}', {1})", sequenceName, lastGid);
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
sequenceSetError = ex.Message;
}
String messageBoxText = Rekod.Properties.Resources.DGBH_OperSuccess;
if (sequenceSetError != null)
{
messageBoxText = messageBoxText + "\nError while setting sequence: " + sequenceSetError;
}
MessageBox.Show(messageBoxText, Properties.Resources.AET_CreatingTable, MessageBoxButtons.OK, MessageBoxIcon.Information);
classesOfMetods cls = new classesOfMetods();
cls.reloadInfo();
sqlCmd.Close();
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
if (Program.sscUser != null && classesOfMetods.getTableInfo(id_table).type == 1)
{
SyncController.RegisterTable(id_table, ((RESTLib.Model.REST.Group) this.sscGroupCmb.SelectedItem).name);
}
}
}