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 Guid AddNewDictionaryEntity(Guid dictionaryTreeID, Guid entityID) { DictionaryTree tree = DictionaryTrees.SingleOrDefault(d => d.ID == dictionaryTreeID); Guid id = Guid.NewGuid(); if (tree != null) { string sql = string.Format("insert into {0}({1})values('{2}')", tree.Dictionary.TableName, tree.PK, id); using (CommonDataProvider domain = new CommonDataProvider()) { domain.ExecuteNonQuery(sql); return id; } } return Guid.Empty; }
public Guid AddNewProduct(Guid parentProduct, bool withDictNomen, Guid dictNomenID, Guid UserID) { Product parent = Products.SingleOrDefault(p => p.ID == parentProduct); List<ClassificationTreeProduct> trees = parent.ClassificationTreeProducts.ToList(); Guid id = Guid.NewGuid(); if (parent != null) { //insert product entity string sql; if (withDictNomen) { sql = "INSERT INTO Product(ID,Name,CreatedDate,_dictNomenID)VALUES('{0}','{1}',CONVERT(datetime, '{2}', 120),'{3}')"; sql = string.Format(sql, id, parent.PublicName.Trim(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), Guid.Empty.Equals(dictNomenID) ? parent._dictNomenID : dictNomenID); } else { sql = "INSERT INTO Product(ID,Name,CreatedDate)VALUES('{0}','{1}',CONVERT(datetime, '{2}', 120))"; sql = string.Format(sql, id, parent.PublicName.Trim(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); } using (CommonDataProvider provider = new CommonDataProvider()) { provider.ExecuteNonQuery(sql); } //add to global classification tree List<ClassificationTreeProduct> classif = new List<ClassificationTreeProduct>(); foreach (ClassificationTreeProduct item in trees) { ClassificationTreeProduct entity = new ClassificationTreeProduct() { ClassificationTreeID = item.ClassificationTreeID, ID = Guid.NewGuid(), ProductID = id }; classif.Add(entity); } this.ClassificationTreeProducts.InsertAllOnSubmit(classif); this.SubmitChanges(); return id; } return Guid.Empty; }
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)); } }
/* 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); } }
/// <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 void SetDictionaryItemValue(Guid dictionaryTreeID, Guid entityID, string column, string value, Guid userID) { DictionaryTree tree = DictionaryTrees.SingleOrDefault(d => d.ID == dictionaryTreeID); if (tree != null) { using (CommonDataProvider domain = new CommonDataProvider()) { ColumnTypeEnum type = this.GetColumnType(domain, tree.Dictionary.TableName, column); int length = this.GetColumnLength(domain, tree.Dictionary.TableName, column); string sql; switch (type) { case ColumnTypeEnum.Integer: case ColumnTypeEnum.Numeric: case ColumnTypeEnum.Decimal: value = value.Replace(",", "."); if (string.IsNullOrEmpty(value)) value = "NULL"; sql = string.Format("UPDATE {1} SET [{0}] = {2} WHERE [{3}] = '{4}'", column, tree.Dictionary.TableName, value, tree.PK, entityID); break; case ColumnTypeEnum.Datetime: sql = string.Format("UPDATE {1} SET [{0}] = '{2}' WHERE [{3}] = '{4}'", column, tree.Dictionary.TableName, Convert.ToDateTime(value).ToString(CultureInfo.InvariantCulture), tree.PK, entityID); break; case ColumnTypeEnum.Varchar: if (value.Length > length) value = value.Substring(0, length); sql = string.Format("UPDATE {1} SET [{0}] = '{2}' WHERE [{3}] = '{4}'", column, tree.Dictionary.TableName, value, tree.PK, entityID); break; case ColumnTypeEnum.Uniqueidentifier: case ColumnTypeEnum.Unknown: default: sql = string.Format("UPDATE {1} SET [{0}] = '{2}' WHERE [{3}] = '{4}'", column, tree.Dictionary.TableName, value, tree.PK, entityID); break; } domain.ExecuteNonQuery(sql); // setting UserID sql = string.Format("UPDATE {0} SET [UserID] = '{1}' WHERE [{2}] = '{3}'", tree.Dictionary.TableName, userID, tree.PK, entityID); domain.ExecuteNonQuery(sql); } } }
public object GetProductDictionaryText(Guid entityID, DictionaryTree entity) { string sql = "select [{0}] from {1} where [{2}] = '{3}'"; sql = string.Format(sql, entity.Dictionary.IdentifierField, entity.Dictionary.TableName, entity.PK, entityID); using (CommonDataProvider provider = new CommonDataProvider()) { return provider.ExecuteScalar(sql); } }
public object GetProductDicitonaryValue(Guid productID, string property) { string sql = "select [{0}] from Product where ID = '{1}'"; sql = string.Format(sql, property, productID); using (CommonDataProvider provider = new CommonDataProvider()) { return provider.ExecuteScalar(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 object GetDictionaryItemValue(Guid dictionaryTreeID, Guid entityID, string column) { DictionaryTree tree = DictionaryTrees.SingleOrDefault(d => d.ID == dictionaryTreeID); if (tree != null) { string sql = string.Format("select [{0}] from [{1}] where [{2}] = '{3}'", column, tree.Dictionary.TableName, tree.PK, entityID); using (CommonDataProvider domain = new CommonDataProvider()) { return domain.ExecuteScalar(sql); } } return null; }
public void DeleteProductDictionaryValue(Guid productID, string property, Guid userID) { object oldValue = this.GetProductDicitonaryValue(productID, property); if (oldValue != DBNull.Value) { Aspect.Utility.TraceHelper.Log(userID, "Продукт: {0}. Свойство справочника удалено: {1}. Старое значение {2}. Новое значение NULL", productID, property, oldValue == DBNull.Value ? "NULL" : oldValue.ToString()); } string sql = "update Product set [{0}] = NULL where ID = '{1}'"; sql = string.Format(sql, property, productID); using (CommonDataProvider provider = new CommonDataProvider()) { provider.ExecuteNonQuery(sql); } }
public void DeleteDictionaryItemValue(Guid dictionaryTreeID, Guid entityID, string column) { DictionaryTree tree = DictionaryTrees.SingleOrDefault(d => d.ID == dictionaryTreeID); if (tree != null) { string sql = string.Format("UPDATE {1} SET [{0}] = NULL WHERE [{2}] = '{3}'", column, tree.Dictionary.TableName, tree.PK, entityID); using (CommonDataProvider domain = new CommonDataProvider()) { domain.ExecuteNonQuery(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 void SetProductDictioanryValue(Guid productID, string property, string value, Guid userID) { object oldValue = this.GetProductDicitonaryValue(productID, property); if (oldValue == DBNull.Value || value != oldValue.ToString()) { Aspect.Utility.TraceHelper.Log(userID, "Продукт: {0}. Свойство справочника изменино: {1}. Старое значение {2}. Новое значение {3}", productID, property, oldValue == DBNull.Value ? "NULL" : oldValue.ToString(), value); } string sql = string.Format("update Product set [{0}] = '{1}' where ID = '{2}'", property, value, productID); string sql2 = string.Format("update Product set [UserID] = '{0}' where ID = '{1}'", userID, productID); using (CommonDataProvider provider = new CommonDataProvider()) { provider.ExecuteNonQuery(sql); provider.ExecuteNonQuery(sql2); } }
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)); } }
private int GetColumnLength(CommonDataProvider provider, string tableName, string columnName) { string sql = string.Format("select character_maximum_length from information_schema.columns where table_name = '{0}' AND column_name = '{1}'", tableName, columnName); object val = provider.ExecuteScalar(sql); if (val == DBNull.Value) return 0; return Convert.ToInt32(val); }
private ColumnTypeEnum GetColumnType(CommonDataProvider provider, string tableName, string columnName) { string sql = string.Format("select data_type from information_schema.columns where table_name = '{0}' AND column_name = '{1}'", tableName, columnName); string val = provider.ExecuteScalar(sql).ToString(); if (val == "char" || val == "nchar" || val == "nvarchar" || val == "varchar") { return ColumnTypeEnum.Varchar; } else if (val == "int") { return ColumnTypeEnum.Integer; } else if (val == "numeric") { return ColumnTypeEnum.Numeric; } else if (val == "decimal") { return ColumnTypeEnum.Decimal; } else if (val == "uniqueidentifier") { return ColumnTypeEnum.Uniqueidentifier; } else { return ColumnTypeEnum.Unknown; } }
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; }