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();
}
