/// <summary>
/// Метод для сохранения стиля
/// </summary>
public void SaveStyle(object parameter = null)
{
using (SqlWork sqlWork = new SqlWork(_connect))
{
sqlWork.BeginTransaction();
sqlWork.sql =
String.Format(@"SELECT EXISTS (SELECT true FROM sys_scheme.style_object_info WHERE id_table={0} AND id_obj={1})",
_table.Id,
_pkAttribute.Value);
bool exists = sqlWork.ExecuteScalar <Boolean>();
if (!exists)
{
sqlWork.sql = String.Format("INSERT INTO sys_scheme.style_object_info (id_table, id_obj) VALUES ({0}, {1})",
_table.Id,
_pkAttribute.Value);
sqlWork.ExecuteNonQuery();
}
sqlWork.sql =
String.Format(@"UPDATE sys_scheme.style_object_info SET
fontname = '{0}',
fontcolor = {1},
fontframecolor = {2},
fontsize = {3},
brushbgcolor = {4},
brushfgcolor = {5},
brushstyle = {6},
brushhatch = {7},
pencolor = {8},
pentype = {9},
penwidth = {10},
symbol = {11}
WHERE id_table = {12} AND id_obj = {13}",
Style.FontName,
Style.FontColor,
Style.FontFrameColor,
Style.FontSize,
Style.BrushBgColor,
Style.BrushFgColor,
Style.BrushStyle,
Style.BrushHatch,
Style.PenColor,
Style.PenType,
Style.PenWidth,
Style.Symbol,
_table.Id,
_pkAttribute.Value);
sqlWork.ExecuteNonQuery();
sqlWork.EndTransaction();
}
}
public void SetGroupsOrder(List <itemObjOrdered> newGroupsOrder)
{
try
{
SqlWork sqlCmd = new SqlWork();
sqlCmd.BeginTransaction();
for (int i = 0; i < newGroupsOrder.Count; i++)
{
itemObjOrdered itemObjOrd = newGroupsOrder[i];
int objId = itemObjOrd.Id_o;
int orderNum = i + 1;
sqlCmd.sql = String.Format("UPDATE {0}.{1} SET order_num={2} WHERE id={3}", Program.scheme, "table_groups", orderNum, objId);
sqlCmd.ExecuteNonQuery();
}
sqlCmd.EndTransaction();
}
catch (Exception ex)
{
Rekod.Classes.workLogFile.writeLogFile(Rekod.Properties.Resources.GF_OrderGroup + " \n" + ex.Message, false, true);
}
}
/// <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 load(List <FieldMatch> fields)
{
if (fields.Count == 0)
{
return;
}
var parameters = new List <Interfaces.Params>();
var insertSql = "insert into {0}.{1} (";
var selectSql = "select ";
string valuesStr = ") values (";
DataTable datas = new DataTable();
foreach (var f in fields)
{
insertSql += "\"" + f.Dest.Name + "\",";
valuesStr += ":" + f.Dest.Name + ",";
selectSql += f.Src + ",";
var p = new Interfaces.Params()
{
paramName = f.Dest.Name
};
ImporterHelper.SetParamType(f.Dest, p);
parameters.Add(p);
datas.Columns.Add(f.Src);
}
selectSql = selectSql.TrimEnd(',');
insertSql = insertSql.TrimEnd(',');
valuesStr = valuesStr.TrimEnd(',');
insertSql += valuesStr + ")";
selectSql += string.Format(" FROM [{0}#dbf]", Name);
//инициируем считывание
var reader = (new OleDbCommand(selectSql, Connection)).ExecuteReader();
using (SqlWork sw = new SqlWork(_pgConnect, true))
{
sw.BeginTransaction();
sw.sql = string.Format(insertSql, _pgTable.SchemeName, _pgTable.Name);
for (int i = 0, report = 0; reader.Read(); report++)
{
//добавляем в таблицу
List <object> colData = new List <object>();
for (int j = 0; j < datas.Columns.Count; j++)
{
colData.Add(reader[j]);
}
datas.Rows.Add(colData.ToArray());
i++;
if (i >= _rowsInSect)
{
//кладем в базу
for (int j = 0; j < datas.Rows.Count; j++)
{
for (int k = 0; k < datas.Columns.Count; k++)
{
ImporterHelper.SetParamValue(parameters[k], datas.Rows[j][k]);
}
sw.ExecuteNonQuery(parameters.ToArray());
}
i = 0;
datas.Clear();
}
ReportProgress(0, report);
}
reader.Close();
sw.EndTransaction();
}
}
private void load(List <FieldMatch> fields)
{
if (fields.Count == 0 && _loadGeom == false)
{
return;
}
// размер блока для загрузки в БД
int rowsInSect = Math.Min(100, Math.Max(1, RowsCount / 10));
// список параметров для загрузки в БД
List <Interfaces.Params>[] paramList = new List <Interfaces.Params> [rowsInSect];
// названия колонок таблицы
List <String> columns = new List <string>();
string wktTo = GetSRText(_pgTable.Srid);
string wktFrom = GetSRText(_srid);
//инициируем считывание
_shpWork.StartRead();
List <string> errors = new List <string>();
using (var sw = new SqlWork(_connect, true))
{
sw.BeginTransaction();
sw.sql = FormQuerySQL(fields, ref paramList, ref columns);
for (int i = 0, report = 0; _shpWork.Read() && report < RowsCount; report++)
{
//добавляем в таблицу
for (int j = 0; j < columns.Count; j++)
{
try
{
if (_loadGeom == true && j == columns.Count - 1)
{
ImporterHelper.SetParamValue(
paramList[i][j],
_shpWork.TransformedCurrentGeometry(wktTo, wktFrom));
}
else
{
ImporterHelper.SetParamValue(
paramList[i][j],
_shpWork[columns[j]]);
}
}
catch (Exception e)
{
if (!errors.Contains(e.Message))
{
errors.Add(e.Message);
}
}
}
if (++i >= rowsInSect || report == RowsCount - 1)
{
//кладем в базу
for (int k = 0; k < i; k++)
{
try
{
sw.ExecuteNonQuery(paramList[k].ToArray());
}
catch (Exception e)
{
if (!errors.Contains(e.Message))
{
errors.Add(e.Message);
}
}
}
i = 0;
ReportProgress(0, report);
}
}
_shpWork.EndRead();
sw.EndTransaction();
if (errors.Count > 0)
{
throw new Exception(String.Join(Environment.NewLine, errors));
}
}
}
private void load(List <FieldMatch> fields, string rngString)
{
if (fields.Count == 0)
{
return;
}
var parameters = new List <Interfaces.Params>();
var insertSql = "insert into {0}.{1} (";
string valuesStr = ") values (";
foreach (var f in fields)
{
insertSql += "\"" + f.Dest.Name + "\",";
valuesStr += ":" + f.Dest.Name + ",";
var p = new Interfaces.Params()
{
paramName = f.Dest.Name
};
ImporterHelper.SetParamType(f.Dest, p);
parameters.Add(p);
}
insertSql = insertSql.TrimEnd(',');
valuesStr = valuesStr.TrimEnd(',');
insertSql += valuesStr + ")";
//читаем excel
String sheetString = rngString.Split('$')[0];
String rangeString = rngString.Split('$')[1];
var excelAppl = new Excel.Application()
{
Visible = false
};
var workbook = excelAppl.Workbooks.Open(_inputFile.FullName);
Excel.Worksheet workSheet = (Excel.Worksheet)(workbook.Sheets.Item[sheetString]);
Excel.Range workRange = workSheet.get_Range(rangeString);
// Где на странице Excel находятся данные
int lastRow = workRange.Row + workRange.Rows.Count - 1;
int firstRow = workRange.Row;
int lastColumn = workRange.Column + workRange.Columns.Count - 1;
int firstColumn = workRange.Column;
// Получаются данные из excel в виде массива объектов
var excValues = (object[, ])(workRange.Value);
workbook.Close();
// Сколько строк и сколько столбцов
_rowsCount = excValues.GetLength(0) - 1; //первая строка заголовки
var colsCount = excValues.GetLength(1);
excelAppl.Quit();
DataTable datas = new DataTable();
for (int j = 1; j <= colsCount; j++)
{
datas.Columns.Add(excValues[1, j].ToString());
}
using (SqlWork sw = new SqlWork(_pgConnect, true))
{
sw.BeginTransaction();
sw.sql = string.Format(insertSql, _pgTable.SchemeName, _pgTable.Name);
for (int i = 2, report = 0; i <= _rowsCount + 1; i++)
{
//добавляем в таблицу
datas.Rows.Add(getRow(excValues, i, colsCount));
report++;
if (report >= _rowsInSect)
{
//убираем ненужные колонки
List <object> cols2delete = new List <object>();
for (int j = 0; j < datas.Columns.Count; j++)
{
if (fields.Find(w => w.Src == datas.Columns[j].Caption) == null)
{
cols2delete.Add(datas.Columns[j]);
}
}
foreach (var c in cols2delete)
{
datas.Columns.Remove((DataColumn)c);
}
//кладем в базу
for (int j = 0; j < datas.Rows.Count; j++)
{
for (int k = 0; k < datas.Columns.Count; k++)
{
ImporterHelper.SetParamValue(parameters[k], datas.Rows[j][k]);
}
sw.ExecuteNonQuery(parameters.ToArray());
}
report = 0;
datas = new DataTable();
for (int j = 1; j <= colsCount; j++)
{
datas.Columns.Add(excValues[1, j].ToString());
}
}
ReportProgress(0, i - 2);
}
sw.EndTransaction();
}
}
/// <summary>
/// Создание новой схемы
/// </summary>
public void CreateNewScheme(object parameter = null)
{
if (CanCreateNewScheme(parameter))
{
try
{
String newSchemeName = parameter.ToString().Trim().ToLower();
if (newSchemeName.Length > 0)
{
if (newSchemeName[0] >= 'a' && newSchemeName[0] <= 'z')
{
bool valid = true;
for (int i = 1; i < newSchemeName.Length; i++)
{
if ((newSchemeName[i] >= 'a' && newSchemeName[i] <= 'z') || (newSchemeName[i] >= '0' && newSchemeName[i] <= '9') || newSchemeName[i] == '_')
{
}
else
{
valid = false;
}
}
if (!valid)
{
MessageBox.Show("В названии схемы могут присутствовать только латинские символы, цифры и знак подчеркивания");
}
else
{
using (SqlWork sqlWork = new SqlWork(true))
{
sqlWork.BeginTransaction();
sqlWork.sql = String.Format(@"CREATE SCHEMA {0} " +
"AUTHORIZATION postgres; " +
"GRANT ALL ON SCHEMA public TO postgres; " +
"GRANT ALL ON SCHEMA public TO public; " +
"COMMENT ON SCHEMA public IS 'scheme created by the system';", newSchemeName);
sqlWork.ExecuteNonQuery();
sqlWork.sql = String.Format("INSERT INTO sys_scheme.table_schems(\"name\") VALUES ('{0}');", newSchemeName);
sqlWork.ExecuteNonQuery();
sqlWork.EndTransaction();
}
ReloadSchems();
MessageBox.Show("Новая схема создана", "Создание новое схемы", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
else
{
MessageBox.Show("Первый символ в названии схемы должен быть латинской буквой");
}
}
else
{
MessageBox.Show("Вы должны ввести хотя бы один символ в название схемы");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}