/// <summary>
/// Сохранение активного фильтра
/// </summary>
public void Save(object parameter = null)
{
FilterSaveFrm frm = new FilterSaveFrm();
frm.Text = "Фильтр";
if (frm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
int idSource = 0;
int idTable = 0;
Npgsql.NpgsqlConnectionStringBuilder connect = (_source.Table.Source as PgDataRepositoryVM).Connect;
String sourceName = String.Format("{0}@{1}", connect.Database, connect.Host);
String sourceType = _source.Table.Source.Type.ToString();
Pg_M.PgTableBaseM pgTable = _source.Table as Pg_M.PgTableBaseM;
String tableName = String.Format("\"{0}\".\"{1}\"", pgTable.SchemeName, pgTable.Name);
bool sourceExists = (from Source src
in Program.SettingsDB.Sources
where src.SourceName == sourceName && src.SourceType == sourceType
select src).Count() > 0;
if (!sourceExists)
{
Program.SettingsDB.Sources.AddObject(new Source()
{
SourceName = sourceName, SourceType = sourceType
});
}
idSource = Convert.ToInt32((from Source src
in Program.SettingsDB.Sources
where src.SourceName == sourceName && src.SourceType == sourceType
select src.Id).FirstOrDefault());
bool tableExists = (from Table tbl
in Program.SettingsDB.Tables
where tbl.SourceId == idSource && tbl.TableName == tableName
select tbl).Count() > 0;
if (!tableExists)
{
Program.SettingsDB.Tables.AddObject(new Table()
{
TableName = tableName, SourceId = idSource
});
}
idTable = Convert.ToInt32((from Table tbl
in Program.SettingsDB.Tables
where tbl.TableName == tableName && tbl.SourceId == idSource
select tbl.Id).FirstOrDefault());
FilterRelationModel filterRM = GetFilterModel(_dynamicFilter);
Program.SettingsDB.Filters.AddObject(new Filter()
{
FilterName = frm.textBox1.Text,
WhereText = SqlJsonParser.ToJson(filterRM),
IdTable = idTable
});
Program.SettingsDB.SaveChanges();
ReloadFiltersFromSqLite();
}
}
public CosmeticTableViewFilterVM(CosmeticTableViewVM source, FilterRelationModel fixedFilterPattern)
{
_source = source;
GetFilterField();
GetFilterOperation();
GetFilterRelation();
_dynamicFilter = new CosTableViewFiltersM(this);
_filters = new ObservableCollection <ITableViewFiltersM>(new[] { _dynamicFilter });
}
/// <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");
}
/// <summary>
/// Загрузка активного фильтра из SQLite
/// </summary>
public void LoadFilter(object parameter = null)
{
long filterId = Convert.ToInt64(parameter);
foreach (var filter in (from Filter f
in Program.SettingsDB.Filters
where f.Id == filterId
select f))
{
FilterRelationModel pattern = SqlJsonParser.FromJson <FilterRelationModel>(filter.WhereText);
SetFilterFromPattern(_dynamicFilter, pattern);
_dynamicFilter.OnPropertyChanged("QueryString");
}
}
/// <summary>
/// Конструктор менеджера фильтров.
/// </summary>
/// <param name="source">Источник, в котором хранится экземпляр класса</param>
/// <param name="fixedFilterPattern">Паттерн фиксированного фильтра</param>
public PgTableViewFilterVM(PgTableViewVM source, FilterRelationModel fixedFilterPattern)
{
this._source = source;
GetFilterField();
GetFilterRelation();
GetFilterOperation();
_dynamicFilter = new PgTVM.PgTableViewFiltersM(this);
if (fixedFilterPattern != null)
{
_fixedFilter = new PgTVM.PgTableViewFilterM(this);
SetFilterFromPattern(_fixedFilter, fixedFilterPattern);
}
_filters = new ObservableCollection <PgTVM.PgTableViewFiltersM>(new[] { _dynamicFilter });
ReloadFiltersFromSqLite();
}
/// <summary>
/// ViewModel для окна списка объектов
/// </summary>
/// <param name="table">Таблица с объектами</param>
/// <param name="filter">Фильтр который нужно применить для данного окна</param>
/// <param name="id">Перейти к нужному объекту</param>
/// <param name="toSelected"> Открыть окно для выбора объекта</param>
/// <param name="isDebug"> Debug режим </param>
public PgTableViewVM(AbsM.ITableBaseM table, FilterRelationModel filter = null, object id = null, bool toSelected = false, bool isDebug = false)
{
var pgTable = table as PgM.PgTableBaseM;
if (pgTable == null)
{
throw new ArgumentNullException("table");
}
_table = pgTable;
_isReadOnly = _table.IsReadOnly;
_source = pgTable.Source as PgVM.PgDataRepositoryVM;
_connect = Source.Connect;
_sysScheme = "sys_scheme";
_isDebug = isDebug;
Title = String.Format("Таблица: \"{0}\"; Источник: \"{2}@{3}:{4} ({5})\"; Тип: \"{1}\"",
pgTable.Text,
_source.Type,
_source.Connect.Database,
_source.Connect.Host,
_source.Connect.Port,
_source.CurrentUser.NameFull);
_columns = new ObservableCollection <HeaderValue>();
sqlSelect = GetSQL();
_filterVM = new PgTableView.PgTableViewFilterVM(this, filter);
if (id != null)
{
int?page = GetPageInTable(id);
if (page != null)
{
PageCurrent = page;
}
}
GetColumns();
Reload();
if (id != null)
{
CurrentRowView = GetRow(id);
}
_toSelected = toSelected;
_source.EventUpdateAttribute += _source_EventUpdateAttribute;
this.PropertyChanged += PgTableViewVM_PropertyChanged;
}
/// <summary>
/// Получает FilterRelationModel из контейнера
/// </summary>
/// <param name="cont"></param>
/// <returns></returns>
private FilterRelationModel GetFilterModelContainer(PgTVM.PgTableViewFiltersM cont)
{
var RelModel = new FilterRelationModel();
RelModel.Type = cont.TRelation;
RelModel.Arguments = new ObservableCollection <FilterRelationModel>();
foreach (var item in cont.Container)
{
var filt = GetFilterModel(item);
if (filt != null)
{
RelModel.Arguments.Add(filt);
}
}
if (RelModel.Arguments.Count == 0)
{
return(null);
}
return(RelModel);
}
/// <summary>
/// Устанавливает активный фильтр из FilterRelationModel
/// </summary>
/// <param name="iFilter"></param>
/// <param name="pattern"></param>
public void SetFilterFromPattern(PgTVM.IPgTableViewFilterM iFilter, FilterRelationModel pattern)
{
switch (pattern.Type)
{
case TypeRelation.AND:
case TypeRelation.OR:
{
PgTVM.PgTableViewFiltersM container = iFilter as PgTVM.PgTableViewFiltersM;
container.Container.Clear();
container.TRelation = pattern.Type;
foreach (FilterRelationModel relM in pattern.Arguments)
{
if (relM.Type == TypeRelation.ELEMENT)
{
PgTVM.PgTableViewFilterM innerelement = new PgTVM.PgTableViewFilterM(container, false);
container.Container.Add(innerelement);
SetFilterFromPattern(innerelement, relM);
}
else
{
PgTVM.PgTableViewFiltersM innercontainer = new PgTVM.PgTableViewFiltersM(container, false);
container.Container.Add(innercontainer);
SetFilterFromPattern(innercontainer, relM);
}
}
break;
}
case TypeRelation.ELEMENT:
{
PgTVM.PgTableViewFilterM element = (PgTVM.PgTableViewFilterM)iFilter;
element.Field = element.Source.FindField(pattern.Element.Column);
element.TOperation = pattern.Element.Type;
element.Value = pattern.Element.Value;
break;
}
}
}
/// <summary>
/// Обработчик событий обновления атрибута
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void _source_EventUpdateAttribute(object sender, PgM.PgAttributes.PgAttributeEventArgs e)
{
if (e.Id == null)
{
return;
}
if (e.Table == Table)
{
var rowView = GetRow(e.Id);
if (e.TypeChange == PgM.PgAttributes.attributeTypeChange.Delete)
{
rowView.Delete();
if (rowView != null)
{
TableRows--;
}
PageShowRows--;
return;
}
if (rowView != null || e.TypeChange == PgM.PgAttributes.attributeTypeChange.Create)
{
if (Data == null)
{
Reload();
return;
}
else
{
//Загрузка таблицы
var filter = new FilterRelationModel()
{
Type = TypeRelation.ELEMENT,
Element = new FilterElementModel()
{
Column = _table.PrimaryKey,
Type = TypeOperation.Equal,
Value = e.Id.ToString()
}
};
int tmpTableRows, tmpTablePages, tmpTableShowPages;
DataView tableChange = GetData(null, PageCurrent, out tmpTableRows, out tmpTablePages, out tmpTableShowPages, filter);
if (tableChange == null || tableChange.Table.Rows.Count != 1)
{
return;
}
DataRow row;
if (e.TypeChange == PgM.PgAttributes.attributeTypeChange.Create)
{
row = Data.Table.NewRow();
Data.Table.Rows.Add(row);
TableRows++;
PageShowRows++;
}
else
{
row = rowView.Row;
}
var rowChange = tableChange.Table.Rows;
for (int i = 0; i < Data.Table.Columns.Count; i++)
{
string columnName = Data.Table.Columns[i].ColumnName;
if (tableChange.Table.Columns.Contains(columnName))
{
row[columnName] = rowChange[0][columnName];
}
}
}
}
}
}
/// <summary>
/// Загружает данные с БД
/// </summary>
/// <param name="page">Указывает страницу для загрузки или загружать все</param>
/// <param name="tableRows"></param>
/// <param name="tablePages"></param>
/// <param name="pageShowRows"></param>
/// <returns></returns>
private DataView GetData(DataView data, int?page, out int tableRows, out int tablePages, out int pageShowRows, FilterRelationModel filterPattern = null)
{
tableRows = GetCountRows();
pageShowRows = 0;
if (page != null)
{
tablePages = tableRows / __pageMaxRows;
if (tableRows % __pageMaxRows > 0)
{
tablePages++;
}
}
else
{
tablePages = 1;
}
if (tableRows != 0)
{
using (SqlWork sqlCmd = new SqlWork(_connect, _isDebug))
{
List <IParams> listParams = new List <IParams>();
string whereDynamic = PgTableViewFilterVM.GetWhere(FilterVM.DynamicFilter, listParams);
string whereFixed = PgTableViewFilterVM.GetWhere(FilterVM.FixedFilter, listParams);
string whereUpdated = "1 = 1";
if (filterPattern != null)
{
PgTVM.PgTableViewFilterM filterUpdated = new PgTableViewFilterM(FilterVM);
FilterVM.SetFilterFromPattern(filterUpdated, filterPattern);
whereUpdated = PgTableViewFilterVM.GetWhere(filterUpdated, listParams);
}
sqlCmd.sql =
string.Format(
@"
SELECT *
FROM (
SELECT *
FROM (
{0}
) as gg
WHERE {1} AND {2}
ORDER BY {3}
{4}
) s
WHERE {5}",
sqlSelect,
whereFixed,
whereDynamic,
GetSQLOrderBy(data),
(page == null) ? "" : string.Format("LIMIT {0} OFFSET {1}", __pageMaxRows, ((int)page - 1) * __pageMaxRows),
whereUpdated);
data = sqlCmd.ExecuteGetTable(data, listParams);
}
}
if (data != null)
{
pageShowRows = data.Table.Rows.Count;
if (data.Sort == "")
{
data.Sort = _table.PrimaryKey;
}
}
return(data);
}
