public static void SetParamType(PgM.PgFieldM field, Interfaces.IParams param)
{
switch (field.Type)
{
case Rekod.DataAccess.AbstractSource.Model.EFieldType.Integer:
param.type = DbType.Int32;
param.typeData = NpgsqlTypes.NpgsqlDbType.Integer;
break;
case Rekod.DataAccess.AbstractSource.Model.EFieldType.Date:
param.type = DbType.Date;
param.typeData = NpgsqlTypes.NpgsqlDbType.Date;
break;
case Rekod.DataAccess.AbstractSource.Model.EFieldType.DateTime:
param.type = DbType.DateTime;
param.typeData = NpgsqlTypes.NpgsqlDbType.TimestampTZ;
break;
case Rekod.DataAccess.AbstractSource.Model.EFieldType.Real:
param.type = DbType.Double;
param.typeData = NpgsqlTypes.NpgsqlDbType.Numeric;
break;
default:
case Rekod.DataAccess.AbstractSource.Model.EFieldType.Geometry:
case Rekod.DataAccess.AbstractSource.Model.EFieldType.Text:
param.type = DbType.String;
param.typeData = NpgsqlTypes.NpgsqlDbType.Text;
break;
}
}
/// <summary>
/// Загрузка связанных таблиц
/// </summary>
private void LoadRelatedTables(object idObject)
{
if (idObject != null && (_table.Type == AbsM.ETableType.Data || _table.Type == AbsM.ETableType.MapLayer))
{
RelatedTables.Clear();
using (SqlWork sqlWork = new SqlWork(Connect))
{
// Запрос для получения идентификаторов связанных таблиц
sqlWork.sql = String.Format(@"SELECT tfi.id_table, tfi.name_db, tfi.ref_field
FROM sys_scheme.table_field_info tfi
INNER JOIN sys_scheme.table_info ti ON tfi.id_table = ti.id
INNER JOIN sys_scheme.table_type_table ttt ON ttt.id = ti.type
WHERE tfi.ref_table = {0} AND (ttt.id = 1 OR ttt.id = 4)", _table.Id);
sqlWork.ExecuteReader();
while (sqlWork.CanRead())
{
int idTable = sqlWork.GetInt32("id_table");
string colName = sqlWork.GetString("name_db");
int refFieldId = sqlWork.GetInt32("ref_field");
PgM.PgFieldM field = _table.Fields.FirstOrDefault(f => f.Id == refFieldId) as PgM.PgFieldM;
if (field != null && field.Table != null && field.Table.Id is int &&
classesOfMetods.getRefTableRight(_table.Id, (int)field.Table.Id))
{
PgAtM.PgAttributeM attr = _attributesListVM.Attributes.FirstOrDefault(a => a.Field == field);
String filterValue = null;
if (attr.Value != null)
{
filterValue = attr.Value.ToString();
}
PgM.PgTableBaseM pgTable = _table.Source.Tables.FirstOrDefault(p => (int)p.Id == idTable) as PgM.PgTableBaseM;
if (pgTable != null)
{
FilterRelationModel filterRM = new FilterRelationModel()
{
Type = TypeRelation.ELEMENT,
Element = new FilterElementModel()
{
Column = colName,
Type = TypeOperation.Equal,
Value = filterValue
}
};
RelatedTables.Add(new PgTV.PgTableViewVM(pgTable, filterRM));
}
}
}
sqlWork.Close();
}
}
OnPropertyChanged("HasRelatedTables");
}
public PgAttributeM(PgAtM.IPgAttributesVM source, PgM.PgFieldM field, object value = null)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
if (field == null)
{
throw new ArgumentNullException("field");
}
_source = source;
_field = field;
_value = value;
if ((Field as PgM.PgFieldM).RefTable != null)
{
_variants = new ObservableCollection <PgAttributeVariantM>();
}
}
/// <summary>
/// Функция генерации sql запроса
/// </summary>
/// <param name="field">Поле с ссылкой на таблицу</param>
/// <param name="whereId">Where запрос</param>
/// <param name="isStyle">Таблица со стилем</param>
/// <returns>Сгенерированая строка SQL запроса</returns>
private string GetSQLQueryRefTable(PgM.PgFieldM field, string whereId, string sysScheme)
{
var listColumns = new List <string>();
if (field.RefTable == null)
{
return(null);
}
if (field.RefField != null)
{
listColumns.Add(string.Format("t1.\"{0}\"", field.RefField.Name));
}
if (field.RefFieldEnd != null)
{
listColumns.Add(string.Format("t1.\"{0}\"", field.RefFieldEnd.Name));
}
if (field.RefFieldName != null)
{
listColumns.Add(string.Format("t1.\"{0}\"", field.RefFieldName.Name));
}
// Добавляем идентификатор в коллекции
var pkField = field.RefTable.PrimaryKeyField;
if (field.RefField != pkField &&
field.RefFieldEnd != pkField &&
field.RefFieldName != pkField)
{
listColumns.Add(string.Format("t1.\"{0}\"", pkField.Name));
}
if (field.RefTable.IsMapStyle && LoadStyle)
{
listColumns.AddRange(new[] {
"soi.fontname AS \"!fontname\"",
"soi.fontcolor AS \"!fontcolor\"",
"soi.fontframecolor AS \"!fontframecolor\"",
"soi.fontsize AS \"!fontsize\"",
"soi.symbol AS \"!symbol\"",
"soi.pencolor AS \"!pencolor\"",
"soi.pentype AS \"!pentype\"",
"soi.penwidth AS \"!penwidth\"",
"soi.brushbgcolor AS \"!brushbgcolor\"",
"soi.brushfgcolor AS \"!brushfgcolor\"",
"soi.brushstyle AS \"!brushstyle\"",
"soi.brushhatch AS \"!brushhatch\""
});
}
String sql = string.Format(@"
SELECT {0}
FROM
""{1}"".""{2}"" t1 " +
(LoadStyle ? @"LEFT
JOIN
""{3}"".style_object_info soi
ON
(t1.""{4}"" = soi.id_obj AND soi.id_table = {5}) " : " ") +
@"WHERE
{6}",
"\n\t" + string.Join(", \n\t", listColumns.ToArray()),
field.RefTable.SchemeName,
field.RefTable.Name,
sysScheme,
field.RefTable.PrimaryKey,
field.RefTable.Id,
whereId);
return(sql);
}
/// <summary>
/// Загружает из БД данные о наличии индекса, используемых полях и подписи
/// </summary>
private void LoadIndexInfo()
{
if (Table == null)
{
return;
}
_indexedFields.Clear();
_notIndexedFields.Clear();
foreach (var item in _table.Fields)
{
var field = new PgM.PgFieldM(_table, item.Id)
{
Name = item.Name,
Text = item.Text
};
if (field.Name == _table.GeomField || field.Name == _table.PrimaryKey)
{
continue;
}
_notIndexedFields.Add((AbsM.IFieldM)field);
}
using (var sqlCmd = new SqlWork(Source.Connect))
{
sqlCmd.sql = "SELECT display_text, enabled FROM " + Program.scheme +
".fts_tables WHERE id_table = " + Table.Id;
bool success = sqlCmd.ExecuteReader();
if (sqlCmd.CanRead())
{
IsNew = false;
Enabled = sqlCmd.GetBoolean("enabled");
DisplayText = ParseResultBackDB(sqlCmd.GetString("display_text"));
using (var sqlMore = new SqlWork(Source.Connect))
{
sqlMore.sql = "SELECT id_field FROM " + Program.scheme +
".fts_fields WHERE id_table = " + Table.Id + " ORDER BY order_num";
sqlMore.ExecuteReader();
while (sqlMore.CanRead())
{
int id_field = sqlMore.GetInt32("id_field");
var indexedField = _notIndexedFields.FirstOrDefault(w => w.Id == id_field);
if (indexedField != null)
{
_notIndexedFields.Remove(indexedField);
_indexedFields.Add(indexedField);
}
}
}
}
else
{
IsNew = true;
Enabled = false;
DisplayText = String.Empty;
}
}
}