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);
}
}
}
/// <param name="CurrentDT">Текущая дата</param>
/// <returns></returns>
public System.Data.DataSet GetListOfWares(DateTime CurrentDT)
{
using (CommonDataProvider provider = new CommonDataProvider())
{
return provider.ExecuteCommand(String.Format(sqlListOfWares, CurrentDT.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
/// <param name="ActualDT">Для приказного - дата создания, Для стандартного - текущая дата</param>
/// <param name="CurrentDT">Текущая дата</param>
/// <returns></returns>
public System.Data.DataSet GetListWithKmh(Guid productID, Guid OrderArticleID, DateTime ActualDT, DateTime CurrentDT)
{
using (CommonDataProvider provider = new CommonDataProvider())
{
var actualDate = String.Format("'{0}'", ActualDT.ToString("yyyy-MM-dd HH:mm:ss"));
// для приказных составов, актульной датой стандартной детали является дата, записанная
// в колонке [TechnDates].[gen_date]
if (OrderArticleID != Guid.Empty)
{
actualDate = String.Format("(SELECT gen_date FROM TechnDates WHERE OrderArticleID = '{0}' AND _dictNomenID = spec_stand._Product_ID)",
OrderArticleID);
}
return provider.ExecuteCommand(String.Format(sqlWithKmh, productID, OrderArticleID, actualDate, CurrentDT.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
public System.Data.DataSet GetApplicabilityList(DateTime CurrentDT, int SpecificationTable, Guid MaterialId)
{
using (CommonDataProvider provider = new CommonDataProvider())
{
return provider.ExecuteCommand(String.Format(sqlMaterialApplicability, CurrentDT.ToString("yyyy-MM-dd HH:mm:ss"), SpecificationTable, MaterialId));
}
}
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.DataRow GetEntity(Guid ID, Guid userID, Guid treeNodeID)
{
Query.Query query = new Query.Query(this);
query.Columns = this.GetUserProperties(userID, treeNodeID, FieldPlaceHolder.GridCard);
query.UserFields = this.GetUserFields(userID, treeNodeID, FieldPlaceHolder.GridCard);
string sql = query.BuildEntityQuery(ID);
using (CommonDataProvider provider = new CommonDataProvider())
{
System.Data.DataSet ds = provider.ExecuteCommand(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) return ds.Tables[0].Rows[0];
else return null;
}
}
public DataSet GetApplicability(Guid prod_id)
{
using (CommonDataProvider provider = new CommonDataProvider())
{
return provider.ExecuteCommand(String.Format(QueryGetApplicability2, prod_id));
}
}
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);
}
}
}
/*
private List<GridColumn> GetEditableColumns()
{
List<GridColumn> list = new List<GridColumn>();
list.Add(EditableGridColumn.PositionColumn);
list.Add(EditableGridColumn.QuantityColumn);
EditableGridColumn column = EditableGridColumn._dictUMColumn;
column.DataSource = new EditableGridColumn.Source();
//List<_dictUM> list1 = this._dictUMs.ToList();
//column.DataSource.DataSource = this._dictUMs.ToList();
column.DataSource.DataSource = this._dictUMs.ToList().ConvertAll(
delegate(_dictUM n)
{
return new Pair<Guid, string>(n.ID, n.PublicName);
});
column.DataSource.ValueField = "First";
column.DataSource.TextField = "Second";
//column.DataSource.ValueField = this._dictUMs
list.Add(column);
list.Add(EditableGridColumn.GroupNumberColumn);
list.Add(EditableGridColumn.GroupToChangeColumn);
list.Add(EditableGridColumn.AutoUpdateColumn);
list.Add(EditableGridColumn.QuantityInclusiveColumn);
list.Add(EditableGridColumn.ZoneColumn);
list.Add(EditableGridColumn.CommentColumn);
return list;
}
*/
public System.Data.DataSet GetList(Guid productID)
{
//Query.Query query = new Aspect.Model.ConfigurationDomain.Query.Query();
string sql = BuildConfigurationQuery(productID);
using (CommonDataProvider provider = new CommonDataProvider())
{
return provider.ExecuteCommand(sql);
}
}
public System.Data.DataSet GetAppendList(Dictionary<Guid, Guid> multiBuffer)
{
int countProducts = multiBuffer.Where(item => Guid.Empty.Equals(item.Value)).Count();
int countProductsWithConfiguration = multiBuffer.Where(item => !Guid.Empty.Equals(item.Value)).Count(); ;
System.Data.DataSet dataSet = new System.Data.DataSet();
using (CommonDataProvider provider = new CommonDataProvider())
{
/*
* Надо обязательно исправить, в случае когда в буфере будут и конфигурации
* и просто продукты -- получится не хорошо...
*/
if (countProducts > 0)
{
string sqlProducts = BuildAppendProductsQuery(multiBuffer);
dataSet.Merge(provider.ExecuteCommand(sqlProducts));
}
if (countProductsWithConfiguration > 0)
{
string sqlConfigurations = BuildAppendConfigurationsQuery(multiBuffer);
dataSet.Merge(provider.ExecuteCommand(sqlConfigurations));
}
}
return dataSet;
}