Beispiel #1
0
        public DeliveryResutl Statistic(string customer, DateTime sStart, DateTime sEnd, string specifications, string goodname)
        {
            DeliveryResutl result = new DeliveryResutl();

            string strSql1 = "SELECT SUM(b.lenght*b.discnum), SUM(b.discnum),SUM(b.totalprice),SUM(b.weight) FROM ct_deliverynote a,ct_deliveryitem b where a.noteid=b.noteid and a.deliverdate>='" + sStart.ToString() + "' and a.deliverdate<='" + sEnd.ToString() + "'";

            if (customer.Length > 0)
            {
                strSql1 += " and a.customer = '" + customer + "'";
            }
            if (goodname.Length > 0)
            {
                strSql1 += " and a.goodname = '" + goodname + "'";
            }
            if (specifications.Length > 0)
            {
                strSql1 += " and b.specifications = '" + specifications + "'";
            }

            DataTable dtSum = DMContext.TSqlCommand(strSql1).ToDataTable();

            if (dtSum != null)
            {
                if (dtSum.Rows.Count > 0)
                {
                    result.TotalLength = ControlHelper.Object2Int(dtSum.Rows[0][0]);
                    result.TotalDisc   = ControlHelper.Object2Int(dtSum.Rows[0][1]);
                    result.TotalPrice  = ControlHelper.Object2Double(dtSum.Rows[0][2]);
                    result.TotalWeight = ControlHelper.Object2Double(dtSum.Rows[0][3]);
                }
            }

            return(result);
        }
        private void createOrAlterTable(string tableName, string Name, List <FieldInfo> fields, Dictionary <int, string> dataTypeToSqlMap, bool hasProject = false, bool hasCategory = false)
        {
            var context = new DMContext();

            var exists = context.Database.SqlQueryForDataTatable(string.Format(@"SELECT TOP 1 1 FROM SYSOBJECTS WHERE xtype='u' AND name = '{0}'", tableName));

            if (exists.Rows.Count == 1)
            {
                // 如果表没有数据直接删除表重建
                var tableSource = context.Database.SqlQueryForDataTatable(string.Format(@"SELECT TOP 1 1 FROM dbo.[{0}]", tableName));

                if (tableSource.Rows.Count == 0)
                {
                    dropTable(context, tableName);

                    createTable(context, Name, tableName, fields, dataTypeToSqlMap, hasProject, hasCategory);
                }
                else
                {
                    alterTable(context, tableName, fields, dataTypeToSqlMap);
                }
            }
            else
            {
                createTable(context, Name, tableName, fields, dataTypeToSqlMap, hasProject, hasCategory);
            }
        }
Beispiel #3
0
        public void UpdateFieldData(int ID, string FondsNumber, string FileNumber, int UserID, List <FieldInfo> Fields)
        {
            var paramList   = new List <SqlParameter>();
            var sbSqlFields = new StringBuilder();

            foreach (var field in Fields)
            {
                sbSqlFields.AppendFormat("[_f{0}] = @_f{0},", field.ID);
                paramList.Add(new SqlParameter(string.Format("@_f{0}", field.ID), field.Value));
            }

            sbSqlFields.Append("[ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate");

            paramList.Add(new SqlParameter("@ModifyUser", UserID));
            paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));
            paramList.Add(new SqlParameter("@ID", ID));

            var sql = string.Format(@"
                UPDATE [dbo].[FileDoc_{0}_{1}] 
                SET {2}
                WHERE ID = @ID", FondsNumber, FileNumber, sbSqlFields.ToString());

            var context = new DMContext();

            context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
        }
Beispiel #4
0
        public List <QualityTrackingEntity> Query(DateTime sStart, DateTime sEnd, string batch, string target, string type, string decision)
        {
            string strSql = "select * from ct_qualitytracking where qtdate>='" + sStart.ToString() + "' and qtdate<='" + sEnd.ToString() + "'";

            if (batch.Length > 0)
            {
                strSql = strSql + " and batch = '" + batch + "'";
            }
            if (target.Length > 0)
            {
                strSql = strSql + " and target ='" + target + "'";
            }
            if (type.Length > 0)
            {
                strSql = strSql + " and type ='" + type + "'";
            }
            if (decision.Length > 0)
            {
                strSql = strSql + " and decision ='" + decision + "'";
            }

            strSql = strSql + " order by qtid";


            //CSpec<QualityTrackingEntity> order = new CSpec<QualityTrackingEntity>();
            //order.And(p => p.qtdate.Asc());

            //Spec<QualityTrackingEntity> where = new Spec<QualityTrackingEntity>();
            //where.And(p => p.qtdate.Between(sStart, sEnd));
            //return base.DMSelectList(1000, where.Exp, order.Exp);

            return(DMContext.TSqlCommand(strSql).ToList <QualityTrackingEntity>());
        }
Beispiel #5
0
        public int AddFieldData(string FondsNumber, string FileNumber, int UserID, FileDataInfo Info, string NodeID = "", string dept = "")
        {
            var paramList = new List <SqlParameter>();

            paramList.Add(new SqlParameter("@FondsNumber", FondsNumber));
            paramList.Add(new SqlParameter("@NodeID", NodeID));

            var sbSql       = new StringBuilder("@FondsNumber,@NodeID,");
            var sbSqlFields = new StringBuilder("[FondsNumber],[NodeID],");

            foreach (var field in Info.Fields)
            {
                sbSql.AppendFormat("@_f{0},", field.ID);
                sbSqlFields.AppendFormat("[_f{0}],", field.ID);

                if (field.NotNull && field.Value == null)
                {
                    // 默认值
                    paramList.Add(new SqlParameter(string.Format("@_f{0}", field.ID), field.GetDefaultValue()));
                }
                else
                {
                    paramList.Add(new SqlParameter(string.Format("@_f{0}", field.ID), field.Value));
                }
            }
            sbSql.Append("@Dept,@CreateUser");
            sbSqlFields.Append("[Dept],[CreateUser]");

            paramList.Add(new SqlParameter("@Dept", dept));
            paramList.Add(new SqlParameter("@CreateUser", UserID));
            paramList.Add(new SqlParameter("@ID", 0)
            {
                Direction = System.Data.ParameterDirection.Output
            });

            var sql = string.Format(@"
                INSERT INTO [dbo].[FileDoc_{0}_{1}]({2}) VALUES({3})
                SET @ID = @@IDENTITY ", FondsNumber, FileNumber, sbSqlFields.ToString(), sbSql.ToString());

            var context = new DMContext();

            context.Database.ExecuteSqlCommand(sql, paramList.ToArray());

            var id = Convert.ToInt32(paramList.Last().Value);

            foreach (var attachID in Info.AttachIDs)
            {
                _IObjectAttachService.Add(new ObjectAttachEntity()
                {
                    ObjectKey = string.Format("FileDoc_{0}_{1}", FondsNumber, FileNumber),
                    ObjectID  = id,
                    AttachID  = attachID
                });
            }

            return(id);
        }
Beispiel #6
0
 static void CleanupDB()
 {
     using (var context = new DMContext())
     {
         context.Database.ExecuteSqlCommand("delete from Factors");
         context.Database.ExecuteSqlCommand("delete from Categories");
         context.SaveChanges();
     }
 }
 public DocumentController(
     IHostingEnvironment appEnvironment,
     DMContext context)
 {
     _appEnvironment = appEnvironment;
     _doc            = new DocumentService(context);
     _categories     = new CategoryService(context);
     _userService    = new UserService(context);
     _emailService   = new EmailService(context, _appEnvironment.WebRootPath);
 }
Beispiel #8
0
        public String Delete(int noteid, bool isdecrease)
        {
            String         bErr     = "";
            CustomerEntity customer = null;

            if (isdecrease)
            {
                using (var repo = DMRepository.Get())
                {
                    DeliveryNote note = repo.Get <DeliveryNote>(p => p.noteid == noteid);
                    if (note == null)
                    {
                        return("当前送货单不存在");
                    }

                    Spec <CustomerEntity> where = new Spec <CustomerEntity>();
                    where.And(p => p.customer == note.customer);
                    if (note.sdate.Year <= 1)
                    {
                        where.And(p => p.cyear == DateTime.Now.Year);
                    }
                    else
                    {
                        where.And(p => p.cyear == note.sdate.Year);
                    }
                    customer = repo.Get <CustomerEntity>(where.Exp);
                    if (customer == null)
                    {
                        return("系统中没有此客户信息");
                    }

                    if (customer.sequence != note.deliverid)
                    {
                        return("当前送货单已归档,不能删除");
                    }
                    customer.sequence = customer.sequence - 1;
                }
            }

            using (var dmt = DMContext.GetTransaction())
            {
                var trans = dmt.BeginTransaction();
                if (customer != null & isdecrease)
                {
                    DMContext.Update <CustomerEntity>(customer, p => p.cid == customer.cid, null, trans);
                }

                DMContext.Delete <DeliveryItem>(p => p.noteid == noteid, trans);
                DMContext.Delete <DeliveryNote>(p => p.noteid == noteid, trans);
                trans.Commit();
            }

            return(bErr);
        }
Beispiel #9
0
        public void SetFileArchived(string FondsNumber, string FileNumber, string Ids)
        {
            var sql = string.Format(@"
                UPDATE [dbo].[FileDoc_{0}_{1}]
                SET [Status] = 2
                WHERE ID IN ({2})", FondsNumber, FileNumber, Ids.Trim(','));

            var context = new DMContext();

            context.Database.ExecuteSqlCommand(sql);
        }
        public bool FillTaskGroupRecord(DMContext dMContext, TaskGroupRecord taskGroupRecord, CreateTaskGroupRecordViewModel createTaskGroupRecordViewModel)
        {
            var presentGroupMembers = createTaskGroupRecordViewModel.PresentGroupMembersIds.Select(groupMemberId =>
                                                                                                   dMContext.GroupMembers.SingleOrDefault(gm => gm.Id == groupMemberId)
                                                                                                   );

            var presentGroupMembersList = presentGroupMembers.OrderBy(gm => gm.Score).ToList();

            if (presentGroupMembers.Contains(null))
            {
                return(false);
            }

            taskGroupRecord.PresentGroupMembers = presentGroupMembers.Select(gm => {
                var pgm             = new PresentGroupMember();
                pgm.GroupMember     = gm;
                pgm.TaskGroupRecord = taskGroupRecord;
                return(pgm);
            }).ToHashSet();

            var links = new HashSet <TaskGroupMemberLink>();

            var tasksList = taskGroupRecord.TaskGroup.Tasks.OrderByDescending(t => t.Bounty).ToList();

            while (tasksList.Count() > 0)
            {
                var heaviestTask = tasksList[0];
                tasksList.RemoveAt(0);

                var lowestScoringMember = presentGroupMembersList.FirstOrDefault();
                if (lowestScoringMember == null)
                {
                    presentGroupMembersList = presentGroupMembers.OrderBy(gm => gm.Score + CalculateGainedScore(gm, links)).ToList();
                    lowestScoringMember     = presentGroupMembersList[0];
                }

                presentGroupMembersList.RemoveAt(0);

                var link = new TaskGroupMemberLink();
                link.Task            = heaviestTask;
                link.GroupMember     = lowestScoringMember;
                link.TaskGroupRecord = taskGroupRecord;
                links.Add(link);
            }

            taskGroupRecord.TaskGroupMemberLinks = links;

            return(true);
        }
Beispiel #11
0
        public void DeleteFieldData(string IDs, string FondsNumber, string FileNumber, int UserID)
        {
            var paramList = new List <SqlParameter>();

            paramList.Add(new SqlParameter("@ModifyUser", UserID));
            paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));
            var sql = string.Format(@"
                UPDATE [dbo].[FileDoc_{0}_{1}] 
                SET [IsDelete] = 1,[ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate
                WHERE ID IN ({2})", FondsNumber, FileNumber, IDs.Trim(','));

            var context = new DMContext();

            context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
        }
Beispiel #12
0
        public void Generate(string Fonds, string FileNumer)
        {
            var k      = string.Format("BusinessSystem.{0}.Structure.Fonds.{1}.FileLibrary.{2}.Field.", ConstValue.BusinessKey, Fonds, FileNumer);
            var fields = _IFieldService.GetFields(k, "");

            var dataTypes = _IEnum.GetEnumInfo(ConstValue.BusinessKey, "FieldType");

            var dataTypeToSqlMap = new Dictionary <int, string>();

            foreach (var item in dataTypes.Items)
            {
                dataTypeToSqlMap.Add(int.Parse(item.Key), item.Tags["sqldatatype"]);
            }

            var sbSql = new StringBuilder();

            foreach (var field in fields)
            {
                sbSql.AppendFormat("_f{0} {1}{2} {3},",
                                   field.ID, dataTypeToSqlMap[field.DataType],
                                   field.Length > 0 ? string.Format("({0})", field.Length) : "",
                                   field.NotNull ? "NOT NULL" : "");
            }

            var sql = string.Format(@"
                CREATE TABLE [dbo].[FileDoc_{0}_{1}](
	                [ID] [int] IDENTITY(1,1) NOT NULL,
                    [FondsNumber] [nvarchar](20) NOT NULL,
                    [NodeID] [nvarchar](50) NULL,
	                {2}
                    [Status] [int] NOT NULL DEFAULT(1),
                    [Dept] [nvarchar](50) NULL,
                    [CreateUser] [int] NOT NULL DEFAULT(0),
                    [CreateDate] [datetime] NOT NULL DEFAULT(GETDATE()),
                    [ModifyUser] [int] NULL,
                    [ModifyDate] [datetime] NULL,
                    [IsDelete] [bit] NOT NULL DEFAULT(0),

                    CONSTRAINT [PK_FileDoc_{0}_{1}] PRIMARY KEY CLUSTERED 
                    (
	                    [ID] ASC
                    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                ) ON [PRIMARY]", Fonds, FileNumer, sbSql.ToString());

            var context = new DMContext();

            context.Database.ExecuteSqlCommand(sql);
        }
Beispiel #13
0
        public void Update(DeliveryNote note)
        {
            using (var dmt = DMContext.GetTransaction())
            {
                int noteid = note.noteid;
                var trans  = dmt.BeginTransaction();
                DMContext.Delete <DeliveryItem>(p => p.noteid == noteid, trans);

                DMContext.Update <DeliveryNote>(note, p => p.noteid == noteid, null, trans);
                if (note.items != null)
                {
                    foreach (DeliveryItem item in note.items)
                    {
                        item.noteid = note.noteid;
                        DMContext.Insert(item, null, trans);
                    }
                }

                trans.Commit();
            }
        }
Beispiel #14
0
        static void WriteToDB(int numberOfFactors)
        {
            var stopWatch = new Stopwatch();
            var category  = CreateCategory();
            var factors   = new List <Factor>(numberOfFactors);

            for (int iterator = 1; iterator < numberOfFactors; iterator++)
            {
                factors.Add(CreateFactor(category, iterator));
            }

            using (var context = new DMContext())
            {
                stopWatch.Start();
                context.Categories.Add(category);
                context.Factors.AddRange(factors);
                context.SaveChanges();
                stopWatch.Stop();
            }

            Console.WriteLine("Writing of {0} factors to DB took {1} ms", numberOfFactors, stopWatch.ElapsedMilliseconds);
        }
Beispiel #15
0
        static void ReadFromDB()
        {
            var                      stopWatch = new Stopwatch();
            List <Factor>            factors;
            ICollection <FieldValue> fields;

            using (var context = new DMContext())
            {
                stopWatch.Start();
                factors = context.Factors.Include("Category").ToList();
                foreach (var factor in factors)
                {
                    fields = factor.Fields;
                }
                stopWatch.Stop();
            }

            var numberOfFactors = factors.Count;

            Console.WriteLine("Reading of {0} factors from DB took {1} ms", numberOfFactors, stopWatch.ElapsedMilliseconds);
            //factors.Take(10).ToList().ForEach(PrintFactor);
        }
Beispiel #16
0
        static void QueryDB()
        {
            var           stopWatch = new Stopwatch();
            List <Factor> factors;

            using (var context = new DMContext())
            {
                stopWatch.Start();
                var fieldId = context.Factors.First().Category.Fields.First(f => f.Name == "Val1").Id;
                factors = context.Factors
                          .Include("Category")
                          .ToList() // Because of xml deserialization we cant do Linq to entity
                          .Where(f => f.Fields.Any(field => field.Id == fieldId && (field as FieldValue <int>).Value < 10))
                          .ToList();
                stopWatch.Stop();
            }

            var numberOfFactors = factors.Count;

            Console.WriteLine("Linq query of {0} factors took {1} ms", numberOfFactors, stopWatch.ElapsedMilliseconds);
            //factors.ForEach(PrintFactor);
        }
Beispiel #17
0
 private void dropTable(DMContext context, string tableName)
 {
     context.Database.ExecuteSqlCommand(string.Format(@"DROP TABLE dbo.[{0}]", tableName));
 }
 public TaskGroupController(DMContext dMContext) : base(dMContext)
 {
 }
 public UserService(DMContext context)
 {
     _context = context;
 }
Beispiel #20
0
        public TableSource GetFileData(string FondsNumber, string FileNumber, PageQueryParam Param, List <Condition> Conditions)
        {
            var sbWhereSql = new StringBuilder("WHERE [IsDelete] = 0");
            var sqlParams  = new List <SqlParameter>();

            var orderStr = "[ID]";

            if (!string.IsNullOrEmpty(Param.OrderFiled))
            {
                orderStr = string.Format("[{0}] {1}", Param.OrderFiled, Param.IsDesc ? "DESC" : "");
            }

            foreach (DictionaryEntry filter in Param.FilterCondtion)
            {
                var val = filter.Value.ToString();

                if (string.IsNullOrEmpty(val))
                {
                    continue;
                }

                switch (filter.Key.ToString())
                {
                case "IDs":
                {
                    if (val != "0")
                    {
                        sbWhereSql.AppendFormat(" AND [ID] IN ({0})", val);
                    }

                    break;
                }

                case "NodeID":
                {
                    sbWhereSql.Append(" AND [NodeID] = @NodeID");
                    sqlParams.Add(new SqlParameter("@NodeID", val));
                    break;
                }

                case "Status":
                {
                    var intVal = int.Parse(val);
                    if (intVal > 0)
                    {
                        sbWhereSql.Append(" AND [Status] = @Status");
                        sqlParams.Add(new SqlParameter("@Status", intVal));
                    }
                    break;
                }

                case "Dept":
                {
                    if (val != "0")
                    {
                        sbWhereSql.Append(" AND [Dept] = @Dept");
                        sqlParams.Add(new SqlParameter("@Dept", val));
                    }

                    break;
                }

                default:
                    break;
                }
            }

            var wheresql = Conditions == null ? string.Empty : Conditions.ToQuerySql(sqlParams);

            if (!string.IsNullOrEmpty(wheresql))
            {
                sbWhereSql.Append(string.Format(" AND {0}", wheresql));
            }

            if (Param.PageSize > 0)
            {
                var sql = string.Format(@"

                SELECT @AllCount = COUNT(1) FROM [dbo].[FileDoc_{0}_{1}] {4}

                SELECT TOP {2} *
                FROM 
                (

                 SELECT ROW_NUMBER() OVER(ORDER BY {5}) AS ROWNUMBER,* 
                 FROM [dbo].[FileDoc_{0}_{1}] WITH(NOLOCK)
                 {4}
 
                ) A
                WHERE ROWNUMBER > {3}
                ORDER BY {5}", FondsNumber, FileNumber, Param.PageSize, Param.PageSize * (Param.PageIndex - 1), sbWhereSql.ToString(), orderStr);

                var paramAllCount = new SqlParameter("@AllCount", 0)
                {
                    Direction = ParameterDirection.Output
                };
                sqlParams.Add(paramAllCount);

                var context = new DMContext();
                var table   = context.Database.SqlQueryForDataTatable(sql, sqlParams.ToArray());
                return(new TableSource(table, Convert.ToInt32(paramAllCount.Value), Param.PageSize));
            }
            else
            {
                var sql = string.Format(@"
                SELECT  *
                FROM [dbo].[FileDoc_{0}_{1}] WITH(NOLOCK)
                {2}
                ORDER BY {3}", FondsNumber, FileNumber, sbWhereSql.ToString(), orderStr);

                var context = new DMContext();
                var table   = context.Database.SqlQueryForDataTatable(sql, sqlParams.ToArray());
                return(new TableSource(table, 0, 0));
            }
        }
Beispiel #21
0
 public AuthController(SignInManager <Person> signInManager, UserManager <Person> userManager, IConfiguration configuration, DMContext dMContext) : base(dMContext)
 {
     this.signInManager = signInManager;
     this.userManager   = userManager;
     this.configuration = configuration;
 }
 public InvitationController(DMContext dMContext) : base(dMContext)
 {
 }
Beispiel #23
0
        public void SetFileArchived(string FondsNumber, string FileNumber, PageQueryParam Param, List <Condition> Conditions)
        {
            var sbWhereSql = new StringBuilder("WHERE [IsDelete] = 0");
            var sqlParams  = new List <SqlParameter>();

            foreach (DictionaryEntry filter in Param.FilterCondtion)
            {
                var val = filter.Value.ToString();

                if (string.IsNullOrEmpty(val))
                {
                    continue;
                }

                switch (filter.Key.ToString())
                {
                case "IDs":
                {
                    if (val != "0")
                    {
                        sbWhereSql.AppendFormat(" AND [ID] IN ({0})", val);
                    }

                    break;
                }

                case "NodeID":
                {
                    sbWhereSql.Append(" AND [NodeID] = @NodeID");
                    sqlParams.Add(new SqlParameter("@NodeID", val));
                    break;
                }

                case "Status":
                {
                    var intVal = int.Parse(val);
                    if (intVal > 0)
                    {
                        sbWhereSql.Append(" AND [Status] = @Status");
                        sqlParams.Add(new SqlParameter("@Status", intVal));
                    }
                    break;
                }

                case "Dept":
                {
                    if (val != "0")
                    {
                        sbWhereSql.Append(" AND [Dept] = @Dept");
                        sqlParams.Add(new SqlParameter("@Dept", val));
                    }

                    break;
                }

                default:
                    break;
                }
            }

            var wheresql = Conditions == null ? string.Empty : Conditions.ToQuerySql(sqlParams);

            if (!string.IsNullOrEmpty(wheresql))
            {
                sbWhereSql.Append(string.Format(" AND {0}", wheresql));
            }

            var sql = string.Format(@"
                UPDATE [dbo].[FileDoc_{0}_{1}]
                SET [Status] = 2
                {2}", FondsNumber, FileNumber, sbWhereSql.ToString());

            var context = new DMContext();

            context.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
        }
 public BookService()
 {
     _DMContext  = new DMContext();
     _DBBook     = new BaseRepository <BookEntity>(_DMContext);
     _DBBookItem = new BaseRepository <BookItemEntity>(_DMContext);
 }
Beispiel #25
0
        private void createTable(DMContext context, string Name, string tableName, List <FieldInfo> fields, Dictionary <int, string> dataTypeToSqlMap, bool hasProject = false, bool hasCategory = false)
        {
            var sbSql = new StringBuilder();

            if (Name == "File")
            {
                sbSql.Append(@"[NodeID] [nvarchar](50) NULL,
                    [RefID] [INT] NULL,
                    [FileNumber] [INT] NULL,
                    [FileID] [INT] NULL,");
            }
            else if (Name == "Project")
            {
            }
            else
            {
                sbSql.Append(@"[NodeID] [nvarchar](50) NULL,
                    [FileNumber] [INT] NULL,
                    [AccessLevel] [INT] NOT NULL DEFAULT(1),
                    [Copies] [INT] NOT NULL DEFAULT(1),");

                if (hasProject)
                {
                    sbSql.Append(@"[ProjectID] [INT] NOT NULL DEFAULT(0),");
                }

                if (hasCategory)
                {
                    sbSql.Append(@"[Category] [NVARCHAR](50) NULL,");
                }
            }

            foreach (var field in fields)
            {
                var defaultStr = "";

                if (field.DataType == 1 || field.DataType == 5)
                {
                    defaultStr = string.Format("DEFAULT({0}) ", field.GetDefaultValue());
                }
                else
                {
                    defaultStr = string.Format("DEFAULT('{0}') ", field.GetDefaultValue());
                }

                sbSql.AppendFormat("_f{0} {1}{2} {3},",
                                   field.ID, dataTypeToSqlMap[field.DataType],
                                   field.Length > 0 ? string.Format("({0})", field.Length) : "",
                                   field.NotNull ? string.Format("NOT NULL {0} ", defaultStr) : "");
            }

            var sql = string.Format(@"
                CREATE TABLE [dbo].[{0}](
	                [ID] [int] IDENTITY(1,1) NOT NULL,
                    [FondsNumber] [nvarchar](20) NOT NULL,
	                {1}
                    [Status] [int] NOT NULL DEFAULT(1),
                    [CreateUser] [int] NOT NULL DEFAULT(0),
                    [CreateDate] [datetime] NOT NULL DEFAULT(GETDATE()),
                    [ModifyUser] [int] NULL,
                    [ModifyDate] [datetime] NULL,
                    [IsDelete] [bit] NOT NULL DEFAULT(0),

                    CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
                    (
	                    [ID] ASC
                    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                ) ON [PRIMARY]", tableName, sbSql.ToString());

            context.Database.ExecuteSqlCommand(sql);
        }
 public DocumentService(DMContext db, IHostingEnvironment appEnvironment)
 {
     _context        = db;
     _appEnvironment = appEnvironment;
 }
Beispiel #27
0
        private void alterTable(DMContext context, string tableName, List <FieldInfo> fields, Dictionary <int, string> dataTypeToSqlMap)
        {
            var result = new StringBuilder();

            var columnSql = string.Format(@"SELECT CONVERT(INT,SUBSTRING(name,3,2),0) AS name FROM SYSCOLUMNS C
                WHERE C.name like '_f%' AND C.name <> 'FondsNumber'
                AND EXISTS(SELECT TOP 1 1 FROM SYSOBJECTS T WHERE C.id = T.id AND T.xtype='u' AND T.name = '{0}' )", tableName);

            var columns = context.Database.SqlQueryForDataTatable(columnSql);

            var fieldIDs = string.Join(",", fields.Select(f => f.ID));

            // 需要被删除的字段
            var dropCols = columns.Select(string.Format("name NOT IN ({0})", fieldIDs));

            // 删除列
            if (dropCols.Length > 0)
            {
                var cols = string.Join(",", dropCols.Select(c => "_f" + c[0].ToString()));

                var sqlDropConSql = string.Format(@"SELECT B.name FROM SYSOBJECTS B JOIN SYSCOLUMNS A ON B.id = A.cdefault 
                    WHERE A.id = OBJECT_ID('{0}') 
                    AND A.name IN ('{1}')", tableName, string.Join("','", dropCols.Select(c => "_f" + c[0].ToString())));

                var cons = context.Database.SqlQueryForDataTatable(sqlDropConSql);
                if (cons.Rows.Count > 0)
                {
                    var conStrs = cons.Select().Select(c => c[0].ToString());
                    result.AppendFormat(@"ALTER TABLE {0} DROP CONSTRAINT {1}", tableName, string.Join(",", conStrs));
                }

                result.AppendFormat(@"
                    ALTER TABLE {0} DROP COLUMN {1}", tableName, string.Join(",", dropCols.Select(c => "_f" + c[0].ToString())));
            }

            var existsCols = columns.Select(string.Format("name IN ({0})", fieldIDs)).Select(r => r[0].ToString());

            // 需要新增的字段
            var newCols = fields.Where(f => !existsCols.Contains(f.ID));

            if (newCols.Count() > 0)
            {
                result.AppendFormat(@"
                ALTER TABLE {0} ADD ", tableName);

                foreach (var field in newCols)
                {
                    var defaultStr = "";

                    if (field.DataType == 1 || field.DataType == 5)
                    {
                        defaultStr = string.Format("DEFAULT({0}) ", field.GetDefaultValue());
                    }
                    else
                    {
                        defaultStr = string.Format("DEFAULT('{0}') ", field.GetDefaultValue());
                    }

                    result.AppendFormat("_f{0} {1}{2} {3},",
                                        field.ID, dataTypeToSqlMap[field.DataType],
                                        field.Length > 0 ? string.Format("({0})", field.Length) : "",
                                        field.NotNull ? string.Format("NOT NULL {0}", defaultStr) : "");
                }
            }

            if (dropCols.Length > 0 || newCols.Count() > 0)
            {
                context.Database.ExecuteSqlCommand(result.ToString().TrimEnd(','));
            }
        }
Beispiel #28
0
 public BorrowService()
 {
     _DMContext     = new DMContext();
     _DBArchive     = new BaseRepository <ArchiveBorrowEntity>(_DMContext);
     _DBArchiveItem = new BaseRepository <ArchiveBorrowItemEntity>(_DMContext);
 }
Beispiel #29
0
 public TaskGroupRecordController(DMContext dMContext, ITaskGroupRecordLogic taskGroupRecordLogic) : base(dMContext)
 {
     this.taskGroupRecordLogic = taskGroupRecordLogic;
 }
Beispiel #30
0
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="FondsNumber"></param>
        /// <param name="FileNumber"></param>
        /// <param name="Info"></param>
        public void BatchUpdate(string FondsNumber, string FileNumber, BatchUpdateInfo Info)
        {
            if (Info.Field == null || string.IsNullOrEmpty(Info.Field.ID))
            {
                throw new Exception("更新字段不能为空");
            }

            var paramList = new List <SqlParameter>();

            if (Info.Field.DataType == (int)FieldDataTypeEnum.字符)
            {
                var valueSB = new StringBuilder();

                var index       = 1;
                var expressions = new StringBuilder();
                var identityExp = new StringBuilder();

                foreach (var v in Info.Expressions)
                {
                    if (v.Value != null)
                    {
                        // 字符串
                        paramList.Add(new SqlParameter("@Value" + index, v.Value));
                        expressions.AppendFormat("@Value{0} + ", index);
                    }
                    else if (v.Field != null && !string.IsNullOrEmpty(v.Field.ID))
                    {
                        // 字段
                        if (v.Field.DataType == (int)FieldDataTypeEnum.字符)
                        {
                            expressions.AppendFormat("[_f{0}] + ", v.Field.ID);
                        }
                        else if (v.Field.DataType == (int)FieldDataTypeEnum.日期)
                        {
                            expressions.AppendFormat("CONVERT(NVARCHAR(100),[_f{0}],111) + ", v.Field.ID);
                        }
                        else
                        {
                            expressions.AppendFormat("CONVERT(NVARCHAR(100),[_f{0}],0) + ", v.Field.ID);
                        }
                    }
                    else if (v.IdentityLength > 0)
                    {
                        // 自增加编号

                        identityExp.AppendFormat("@Identity{0} = @Identity{0} + 1 , ", index);

                        expressions.AppendFormat("CONVERT(NVARCHAR({1}), dbo.PadLeft(@Identity{0}, '{2}', {1}),0) + ", index, v.IdentityLength, v.IdentityFill);

                        paramList.Add(new SqlParameter("@Identity" + index, v.IdentityStart));
                    }

                    index++;
                }

                var expressionsStr = expressions.ToString().Trim().TrimEnd('+');

                // 节点条件
                var sbWhereSql = new StringBuilder("WHERE NodeID = @NodeID");
                paramList.Add(new SqlParameter("@NodeID", Info.NodeID));

                // 部门条件
                if (!string.IsNullOrEmpty(Info.Dept) && Info.Dept != "0")
                {
                    sbWhereSql.Append(" AND Dept = @Dept ");
                    paramList.Add(new SqlParameter("@Dept", Info.Dept));
                }

                if (!string.IsNullOrEmpty(Info.UpdateIDs))
                {
                    sbWhereSql.AppendFormat(" AND [ID] IN ({0})", Info.UpdateIDs.Trim(','));
                }
                else if (Info.Conditions != null)
                {
                    var wheresql = Info.Conditions.ToQuerySql(paramList);
                    if (!string.IsNullOrEmpty(wheresql))
                    {
                        sbWhereSql.AppendFormat(" ADN {0}", wheresql);
                    }
                }

                var sql = "";
                if (!string.IsNullOrEmpty(Info.ReplaceValue))
                {
                    // 字段的查找替换
                    sql = string.Format(@"
                    UPDATE [dbo].[FileDoc_{0}_{1}] 
                    SET {4} [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate, [_f{2}] = REPLACE([_f{2}],@ReplaceValue,{3})
                    {5}", FondsNumber, FileNumber, Info.Field.ID, expressionsStr, identityExp, sbWhereSql);

                    paramList.Add(new SqlParameter("@ReplaceValue", Info.ReplaceValue));
                }
                else if (Info.PartLength > 0)
                {
                    // 字段的区域更新
                    sql = string.Format(@"
                    UPDATE [dbo].[FileDoc_{0}_{1}] 
                    SET {4} [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate, [_f{2}] = dbo.ReplacePart([_f{2}],@PartStart,@PartLength,{3})
                    {5}", FondsNumber, FileNumber, Info.Field.ID, expressionsStr, identityExp, sbWhereSql);

                    paramList.Add(new SqlParameter("@PartStart", Info.PartStart));
                    paramList.Add(new SqlParameter("@PartLength", Info.PartLength));
                }
                else
                {
                    sql = string.Format(@"
                    UPDATE [dbo].[FileDoc_{0}_{1}] 
                    SET {4} [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate, [_f{2}] = {3}
                    {5}", FondsNumber, FileNumber, Info.Field.ID, expressionsStr, identityExp, sbWhereSql);
                }

                paramList.Add(new SqlParameter("@ModifyUser", Info.UpdateUser));
                paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));

                var context = new DMContext();
                context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
            }
            else
            {
                paramList.Add(new SqlParameter("@Value", Info.Expressions.First().Value));

                // 节点条件
                var sbWhereSql = new StringBuilder("WHERE NodeID = @NodeID");
                paramList.Add(new SqlParameter("@NodeID", Info.NodeID));

                // 部门条件
                if (!string.IsNullOrEmpty(Info.Dept) && Info.Dept != "0")
                {
                    sbWhereSql.Append(" AND Dept = @Dept ");
                    paramList.Add(new SqlParameter("@Dept", Info.Dept));
                }

                if (!string.IsNullOrEmpty(Info.UpdateIDs))
                {
                    sbWhereSql.AppendFormat(" AND [ID] IN ({0})", Info.UpdateIDs.Trim(','));
                }
                else if (Info.Conditions != null)
                {
                    var wheresql = Info.Conditions.ToQuerySql(paramList);
                    if (!string.IsNullOrEmpty(wheresql))
                    {
                        sbWhereSql.AppendFormat(" ADN {0}", wheresql);
                    }
                }

                var sql = string.Format(@"
                UPDATE [dbo].[FileDoc_{0}_{1}] 
                SET [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate,[_f{2}] = @Value
                {3}", FondsNumber, FileNumber, Info.Field.ID, sbWhereSql);

                paramList.Add(new SqlParameter("@ModifyUser", Info.UpdateUser));
                paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));

                var context = new DMContext();
                context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
            }
        }