public override System.Data.DataSet GetList(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression, PagingInfo pagingInfo) { Query.Query query = new Query.CustomClassificationQuery(this); //query.ClassificationTreeID = treeNodeID; query.Columns = this.GetUserProperties(userID, treeNodeID, FieldPlaceHolder.Grid); query.UserFields = this.GetUserFields(userID, treeNodeID, FieldPlaceHolder.Grid); query.OrderExpression = order; query.SearchExpression = this.ValidateSearchExpression(query.Columns, query.UserFields, searchExpression); string sql = query.BuildListQuery(treeNodeID); using (CommonDataProvider provider = new CommonDataProvider()) { if (pagingInfo.Enabled) { StringBuilder cursor = new StringBuilder(); cursor.AppendLine("DECLARE @handle int, @rows int;"); cursor.AppendLine("EXEC sp_cursoropen @handle OUT,"); cursor.AppendFormat("'{0}',", sql.Replace("'", "''")); cursor.AppendLine("1,1,@rows OUT SELECT @rows;"); cursor.AppendFormat("EXEC sp_cursorfetch @handle, 16, {0}, {1}\n", pagingInfo.Start + 1, pagingInfo.Limit); cursor.AppendLine("EXEC sp_cursorclose @handle;"); return provider.ExecuteCommand(cursor.ToString()); } else { return provider.ExecuteCommand(sql); } } }
public override string getQuery(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression) { Query.Query query = new Query.CustomClassificationQuery(this); //query.ClassificationTreeID = treeNodeID; query.Columns = this.GetUserProperties(userID, treeNodeID, FieldPlaceHolder.Grid); query.UserFields = this.GetUserFields(userID, treeNodeID, FieldPlaceHolder.Grid); query.OrderExpression = order; query.SearchExpression = this.ValidateSearchExpression(query.Columns, query.UserFields, searchExpression); string sql = query.BuildListQuery(treeNodeID); return sql; }
public override System.Data.DataSet GetList(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression, PagingInfo pagingInfo) { Query.Query query = new Query.Query(this); //query.ClassificationTreeID = treeNodeID; query.Columns = this.GetUserProperties(userID, treeNodeID, FieldPlaceHolder.Grid); foreach (SearchExpression expr in searchExpression) { // основная версия if (expr.FieldID == new Guid("BBE170B0-28E4-4738-B365-1038B03F4552") && !query.Columns.Exists(p => p.ID == new Guid("BBE170B0-28E4-4738-B365-1038B03F4552"))) { query.Columns.Add(this.GetColumnPropertyByID(new Guid("BBE170B0-28E4-4738-B365-1038B03F4552"))); } // IsBeing if (expr.FieldCond == Condition.Beable && !query.Columns.Exists(p => p.ID == new Guid(expr.FieldValue))) { query.Columns.Add(this.GetColumnPropertyByID(new Guid(expr.FieldValue))); } // InList if (expr.FieldCond == Condition.Inset && expr.FieldName == "ID") { expr.FieldName = "Product.ID"; } } query.UserFields = this.GetUserFields(userID, treeNodeID, FieldPlaceHolder.Grid); // default order by nomen super pole if (order.OrderClause == string.Empty) { //IUserField uf = query.UserFields.SingleOrDefault(u => u.DictionaryProperty.ID == new Guid("CAE5AFF1-1103-45CF-8135-7834DC9FAD35")); IUserField uf = query.UserFields.FirstOrDefault(u => u.DictionaryProperty.ID == new Guid("CAE5AFF1-1103-45CF-8135-7834DC9FAD35") && u.DictionaryTreeID == new Guid("316C6BC7-D883-44C8-AAE0-602F49C73595")); if (uf != null) { order = new OrderExpression() { Expression = string.Format("{0}.{1}", uf.DictionaryTree.Alias, uf.DictionaryProperty.ColumnName), SortDirection = SortDirection.asc }; } } // query.OrderExpression = order; query.SearchExpression = this.ValidateSearchExpression(query.Columns, query.UserFields, searchExpression); string sql = query.BuildListQuery(treeNodeID); using (CommonDataProvider provider = new CommonDataProvider()) { if (pagingInfo.Enabled) { StringBuilder cursor = new StringBuilder(); cursor.AppendLine("DECLARE @handle int, @rows int;"); cursor.AppendLine("EXEC sp_cursoropen @handle OUT,"); cursor.AppendFormat("'{0}',", sql.Replace("'", "''")); cursor.AppendLine("1,1,@rows OUT SELECT @rows;"); cursor.AppendFormat("EXEC sp_cursorfetch @handle, 16, {0}, {1}\n", pagingInfo.Start + 1, pagingInfo.Limit); cursor.AppendLine("EXEC sp_cursorclose @handle;"); return provider.ExecuteCommand(cursor.ToString()); } else { return provider.ExecuteCommand(sql); } } }
public override System.Data.DataSet GetList(Guid formGridViewID, Guid productID, Guid userID, /*OrderExpression order , */List<SearchExpression> searchExpression) { // //List<SearchExpression> searchExpression = new List<SearchExpression>(); OrderExpression order = new OrderExpression(); // Query.TreeQuery query = new Query.TreeQuery(this); //query.ClassificationTreeID = treeNodeID; query.Columns = this.GetUserProperties(userID, formGridViewID, FieldPlaceHolder.Grid); query.UserFields = this.GetUserFields(userID, formGridViewID, FieldPlaceHolder.Grid); query.OrderExpression = order; query.SearchExpression = this.ValidateSearchExpression(query.Columns, query.UserFields, searchExpression); string sql = query.BuildListQuery(productID); using (CommonDataProvider provider = new CommonDataProvider()) { return provider.ExecuteCommand(sql); } }
public override System.Data.DataSet GetList(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression, PagingInfo pagingInfo) { Query.Query query = new Query.Query(this); query.UserFields = this.GetUserFields(userID, treeNodeID, FieldPlaceHolder.Grid); query.OrderExpression = order; foreach (SearchExpression expr in searchExpression) { // InList if (expr.FieldID == new Guid("11111111-0000-1111-0000-6097b75f5d3d") && expr.FieldName == "ID") { Aspect.Domain.DictionaryTree entity = this.DictionaryTrees.Single(d => d.ID == treeNodeID); expr.FieldName = string.Format("[{0}].[{1}]", entity.Alias, entity.PK); } } query.SearchExpression = this.ValidateSearchExpression(query.UserFields, searchExpression); string sql = query.BuildListQuery(treeNodeID); using (CommonDataProvider provider = new CommonDataProvider()) { if (pagingInfo.Enabled) { StringBuilder cursor = new StringBuilder(); cursor.AppendLine("DECLARE @handle int, @rows int;"); cursor.AppendLine("EXEC sp_cursoropen @handle OUT,"); cursor.AppendFormat("'{0}',", sql.Replace("'", "''")); cursor.AppendLine("1,1,@rows OUT SELECT @rows;"); cursor.AppendFormat("EXEC sp_cursorfetch @handle, 16, {0}, {1}\n", pagingInfo.Start + 1, pagingInfo.Limit); cursor.AppendLine("EXEC sp_cursorclose @handle;"); return provider.ExecuteCommand(cursor.ToString()); } else { return provider.ExecuteCommand(sql); } } }
public override string getQuery(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression) { Query.Query query = new Query.Query(this); query.UserFields = this.GetUserFields(userID, treeNodeID, FieldPlaceHolder.Grid); query.OrderExpression = order; query.SearchExpression = this.ValidateSearchExpression(query.UserFields, searchExpression); string sql = query.BuildListQuery(treeNodeID); return sql; }
public virtual string getQuery(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression) { return string.Empty; }
/* * Метод перелистывания страниц в гриде */ protected void Store1_RefreshData(object sender, Ext.Net.StoreRefreshDataEventArgs e) { DataSet data = null; List<GridColumn> columns = new List<GridColumn>(); using (ContentDomain provider = Common.GetContentDomain(ClassifiacationTypeView)) { Domain.OrderExpression OrderExpression = new OrderExpression(); if (e.Sort.Length > 0) { string expr = e.Sort.Split('|')[0]; string tp = e.Sort.Split('|')[1]; OrderExpression.Expression = expr; OrderExpression.ColumnType = (TypeEnum)Enum.Parse(typeof(TypeEnum), tp); } if (e.Dir == Ext.Net.SortDirection.DESC) OrderExpression.SortDirection = Aspect.Domain.SortDirection.desc; List<SearchExpression> listSearch = this.SearchConditions.ToList(); // показывать только выбранные if (this.ShowSelected && !String.IsNullOrEmpty(hiddenSelectedProducts.Value.ToString())) { SearchExpression selected = new SearchExpression(); selected.FieldCond = Condition.Inset; selected.FieldValue = hiddenSelectedProducts.Value.ToString(); selected.FieldName = "ID"; listSearch.Add(selected); } // показывать основные версии if (FilterView.Value.ToString().Contains("mainVers")) { SearchExpression mainVers = new SearchExpression(); mainVers.FieldID = new Guid("BBE170B0-28E4-4738-B365-1038B03F4552"); //основная версия mainVers.FieldValue = "1"; listSearch.Add(mainVers); } // показывать приказные версии if (FilterView.Value.ToString().Contains("prikazVers")) { SearchExpression mainVers = new SearchExpression(); mainVers.FieldCond = Condition.Beable; mainVers.FieldValue = "9A38E338-DD60-4636-BFE3-6A98BAF8AE87"; // номер приказала listSearch.Add(mainVers); } // получить страницу из списка продуктов, попадающих под все заданные условия data = provider.GetList(RequestClassificationTreeID, this.User.ID, OrderExpression, listSearch, new PagingInfo(e.Start, e.Limit)); columns = provider.GetGridColumns(this.User.ID, RequestClassificationTreeID, FieldPlaceHolder.Grid); } if (data != null && data.Tables.Count > 0) { e.Total = data.Tables[1].Rows[0].Field<int>(0); List<object> dataSource = new List<object>(); foreach (DataRow row in data.Tables[2].Rows) { List<object> dataRow = new List<object>(); foreach (GridColumn column in columns) { if (column.GridColumnType == TypeEnum.Boolean) { dataRow.Add(row[column.DataItem].ToString() == "1"); } else if (column.GridColumnType == TypeEnum.Datetime) { try { dataRow.Add(row.Field<DateTime>(column.DataItem).ToString("yyyy-MM-dd HH:mm:ss.fff")); } catch { dataRow.Add(row[column.DataItem].ToString()); } } else { dataRow.Add(row[column.DataItem].ToString()); } } // adding another field dataRow.Add(row["ID"].ToString()); dataRow.Add(ClassifiacationTypeView == ClassifiacationTypeView.Dictionary ? this.RequestClassificationTreeID : Guid.Empty); dataSource.Add(dataRow.ToArray()); } this.Store1.DataSource = dataSource; this.Store1.DataBind(); } }
public abstract System.Data.DataSet GetList(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression, PagingInfo pagingInfo);
public override string getQuery(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression) { return base.getQuery(treeNodeID, userID, order, searchExpression); }
public string getQuery(Guid formGridViewID, Guid productID, Guid userID/*, OrderExpression order , List<SearchExpression> searchExpression*/) { // List<SearchExpression> searchExpression = new List<SearchExpression>(); OrderExpression order = new OrderExpression(); // Query.Query query = new Query.Query(this); //query.ClassificationTreeID = treeNodeID; query.Columns = this.GetUserProperties(userID, formGridViewID, FieldPlaceHolder.Grid); query.UserFields = this.GetUserFields(userID, formGridViewID, FieldPlaceHolder.Grid); query.OrderExpression = order; query.SearchExpression = this.ValidateSearchExpression(query.Columns, query.UserFields, searchExpression); string sql = query.BuildListQuery(productID); return sql; }
public override System.Data.DataSet GetList(Guid treeNodeID, Guid userID, OrderExpression order, List<SearchExpression> searchExpression, PagingInfo pagingInfo) { throw new NotImplementedException(); }
public virtual string BuildListQuery(Guid treeNodeID) { StringBuilder sql = new StringBuilder(); WhereClause classificationWhere = new ClassificationWhereClauseBuilder(treeNodeID, Provider); //-- List<WhereClause> whereList = SearchSource.Convert(this.SearchExpression); whereList.Insert(0, classificationWhere); string where = WhereClause.BuildExpression(/*classificationWhere, this.SearchExpression*/ whereList); //-collect from and join values- Aspect.Model.Query.FieldsSQLBuilder propertyQuery = new PropertyFieldsSQLBuilder(Columns); Aspect.Model.Query.FieldsSQLBuilder dictionaryQuery = new DictionaryFieldsSQLBuilder(UserFields, Provider); StringBuilder join = new StringBuilder(); join.Append(propertyQuery.Join); join.Append(dictionaryQuery.Join); StringBuilder from = new StringBuilder(); from.Append(propertyQuery.From); from.Append(dictionaryQuery.From); //prevent order by not selected record if (!from.ToString().Contains(OrderExpression.Expression)) OrderExpression = new OrderExpression(); sql.AppendFormat(SqlTemplate, from.ToString(), join.ToString(), where, OrderExpression.OrderClause); return sql.ToString(); }