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);
         }
     }
 }
示例#2
0
 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;
 }
示例#3
0
        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;
        }
示例#4
0
 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")));
            }
        }
示例#9
0
        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);
                }
            }
        }
示例#10
0
 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);
     }
 }
示例#11
0
 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);
     }
 }
示例#12
0
        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);
                }

            }
        }
示例#13
0
 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;
 }
示例#14
0
 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);
     }
 }
示例#15
0
 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);
         }
     }
 }
示例#16
0
        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);
     }
 }
示例#18
0
 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));
     }
 }
示例#20
0
 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);
 }
示例#21
0
 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;
        }