Пример #1
0
        public IEnumerable <int> GetSonID(int projectID, int p_id)
        {
            using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
            {
                var query = from c in dbcontext.SubProjectTree
                            where c.ProjectID == projectID &&
                            c.ParentID == p_id
                            select c.ChildID;

                return(query.ToList().Concat(query.ToList().SelectMany(t => GetSonID(projectID, t))));
            }
        }
Пример #2
0
        /// <summary>
        /// 根据ProjectID,SpaceID获取立项编号
        /// </summary>
        /// <param name="projectBinder"></param>
        /// <returns></returns>
        public int?GetProjectRequestTask(ProjectBinder projectBinder)
        {
            using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
            {
                LogHelper.WriteLog("ready to get linked specid");
                int?linkedSpecID = (from b in dbcontext.Bug where b.ProjectID == projectBinder.ProjectID && b.SubProjectID == -(1500000001 + projectBinder.SpaceID) select b).SingleOrDefault <Bug>().LinkedSpecID;

                projectBinder.ProjectRequestID = -linkedSpecID;
                LogHelper.WriteLog("linked specid" + projectBinder.ProjectRequestID);
                return(projectBinder.ProjectRequestID);
            }
        }
Пример #3
0
        /// <summary>
        /// 获取所有wbs元素对应的子目录
        /// </summary>
        /// <param name="wbsInfo"></param>
        /// <returns></returns>
        public Dictionary <string, List <int> > GetAllChildFolderIds(WBSInfo wbsInfo)
        {
            Dictionary <string, List <int> > ReleaseFolders = new Dictionary <string, List <int> >();

            using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
            {
                foreach (var item in wbsInfo.WbsFolders)
                {
                    List <int> folders = GetSonID(wbsInfo.DevProjectID, item.WBSFolderID).ToList();
                    folders.Add(item.WBSFolderID);
                    if (ReleaseFolders.ContainsKey(item.WBSUniqueNo))
                    {
                        ReleaseFolders[item.WBSUniqueNo] = ReleaseFolders[item.WBSUniqueNo].Union(folders).ToList <int>();
                    }
                    else
                    {
                        ReleaseFolders.Add(item.WBSUniqueNo, folders);
                    }
                }
            }
            return(ReleaseFolders);
        }
Пример #4
0
        private void GetBuMenTaskCostByOwner(WBSInfo wbsInfo)
        {
            using (ABZG_DSEntities dbcontent = new ABZG_DSEntities())
            {
                // 定位时间段
                //取上月第一天
                DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00.000")).AddMonths(-1);
                //上月最后一天是当月第一天-1天
                DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 23:59:59.000")).AddDays(-1);

                var month = DateTime.Parse(checkMonthFrom.ToString("yyyy-MM"));

                //2. 获取所有审过的表单
                var confirmSheets = from t in dbcontent.TimeSheet
                                    join status in dbcontent.ProgressStatusTypes
                                    on t.StatusID equals status.ProgressStatusID
                                    where t.TSProjectID == 191 && status.ProjectID == 191 && status.ProgressStatusName == "已审核"
                                    select t;

                //3. 获取审核过表单内涉及到的人员
                var users = confirmSheets.Select(p => p.TimeSheetUserID).Distinct().ToList();

                List <int> folders = new List <int>();//保存部门项目下的所有目录
                foreach (WBSFolder item in wbsInfo.WbsFolders)
                {
                    folders.AddRange(ReleaseFolders[item.WBSUniqueNo]);
                }


                //4. 获取对应的子目录Id

                List <MonthCostByOwner> monthCostsByOwner = new List <MonthCostByOwner>();
                foreach (var user in users)    //根据用户遍历
                {
                    int currentUser = Convert.ToInt32(user);
                    //5. 获取每个用户对应的已审核的sheets
                    var mySheets = from mysheet in confirmSheets
                                   where mysheet.TimeSheetUserID == currentUser
                                   select mysheet;

                    //6. 从这些sheets中过滤出添加的任务的所属的timeentryid
                    var mySheetEntrys = from s in dbcontent.TimeSheetEntry
                                        join sheet in mySheets
                                        on s.TimeSheetID equals sheet.TimeSheetID
                                        join f in folders
                                        on s.SubProjectID equals f
                                        where s.IssueID > 0
                                        select s;

                    //7. 从timesheethour中获取对应的工时记录
                    var mysheetHours = from s in dbcontent.TimeSheetHour
                                       join entry in mySheetEntrys
                                       on s.TimeEntryID equals entry.TimeEntryID
                                       where s.TSProjectID == 191 && s.TimeEntryDate > checkMonthFrom &&
                                       s.TimeEntryDate < checkMonthTo
                                       select s;

                    //8. 获取当前用户的统计工时
                    var TotalTime = mysheetHours.Sum(s => s.TimeEntryHours);
                    if (TotalTime > 0)
                    {
                        //遍历。。添加工时
                        monthCostsByOwner.Add(new MonthCostByOwner()
                        {
                            PersonID  = currentUser,
                            LoginName = (from s in dbcontent.LogIn where s.PersonID == currentUser select s.Login1).SingleOrDefault <string>(),
                            TotalTime = TotalTime,
                            Month     = month,
                            Yeart     = month.Year,
                            Montht    = month.Month
                        });
                    }
                }

                wbsInfo.WbsFolders = null;

                List <WBSFolder> wbsFolders = new List <WBSFolder>()
                {
                    new WBSFolder()
                    {
                        WBSFolderID = 01, WBSName = "部门虚拟wbs节点", WBSUniqueNo = "01", TimeCosts = monthCostsByOwner
                    }
                };

                wbsInfo.WbsFolders = wbsFolders;
            }
        }
Пример #5
0
        private WBSInfo GetDevTimeCostByOwner(WBSInfo wbsInfo)
        {
            //定义存储时间的泛型列表,统计每个人当月在DevTime中花费的总工时
            List <MonthCostByOwner> monthCostByOwners = new List <MonthCostByOwner>();

            //取上月第一天
            DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00.000")).AddMonths(-1);
            //上月最后一天是当月第一天-1天
            DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 23:59:59.000")).AddDays(-1);

            var month = DateTime.Parse(checkMonthFrom.ToString("yyyy-MM"));

            //直接获取该项目中指定人员在上月中在已审批的时间表单中花费的工时
            using (ABZG_DSEntities dbcontent = new ABZG_DSEntities())
            {
                //获取参与部门项目开发空间的人员编号
                var subProjectOwners = from owners in dbcontent.SubProjectOwners
                                       where owners.ProjectID == wbsInfo.DevProjectID && owners.SubProjectID == wbsInfo.DevSpaceID
                                       select owners;

                if (subProjectOwners.Count() > 0)//代表当前开发空间有参与人员,需要统计devtime中工时
                {
                    foreach (var item in subProjectOwners)
                    {
                        //获取当前遍历用户所属的已审批过的时间表单
                        var timeSheets = from timesheet in dbcontent.TimeSheet
                                         join status in dbcontent.ProgressStatusTypes
                                         on timesheet.StatusID equals status.ProgressStatusID
                                         where status.ProgressStatusName == "已审核" && status.ProjectID == 191 &&//devtime编号
                                         timesheet.TimeSheetUserID == item.TeamMemberID && timesheet.TSProjectID == 191
                                         select timesheet;

                        if (timeSheets.Count() > 0)//如果有表单数据
                        {
                            //找到所属这些时间表单中的属于devtime公共部分的数据
                            var timeSheetEntry = from entry in dbcontent.TimeSheetEntry
                                                 join sheets in timeSheets
                                                 on new { entry.TSProjectID, entry.TimeSheetID } equals new { sheets.TSProjectID, sheets.TimeSheetID }
                            where entry.TSProjectID == 191 && entry.TimeEntryType == 3 && entry.StandTimeEntryID > 3
                            select entry;

                            if (timeSheetEntry.Count() > 0)//如果有需要检查的表单中的行数据
                            {
                                var timesheetHours = from hours in dbcontent.TimeSheetHour
                                                     join entrys in timeSheetEntry
                                                     on new { hours.TSProjectID, hours.TimeSheetID, hours.TimeEntryID } equals new { entrys.TSProjectID, entrys.TimeSheetID, entrys.TimeEntryID }
                                where hours.TimeEntryDate > checkMonthFrom && hours.TimeEntryDate < checkMonthTo
                                select hours;

                                monthCostByOwners.Add(new MonthCostByOwner()
                                {
                                    PersonID  = item.TeamMemberID,
                                    LoginName = (from l in dbcontent.LogIn
                                                 where l.PersonID == item.TeamMemberID
                                                 select l.Login1).SingleOrDefault(),
                                    Month     = month,
                                    Yeart     = month.Year,
                                    Montht    = month.Month,
                                    TotalTime = (from c in timesheetHours
                                                 select c.TimeEntryHours).Sum()
                                });
                            }
                        }
                    }
                }

                //判断部门的虚拟的节点的时间
                var tr1times = (from currentr1 in wbsInfo.WbsFolders
                                where currentr1.WBSUniqueNo == "01"
                                select currentr1).SingleOrDefault().TimeCosts;

                tr1times.AddRange(monthCostByOwners);
                // List<MonthCostByOwner> newmonthCostByOwners = new List<MonthCostByOwner>();
                var newmonthCostByOwners = from costs in tr1times
                                           group costs
                                           by new { costs.PersonID, costs.LoginName } into g
                    select new MonthCostByOwner
                {
                    PersonID  = g.Key.PersonID,
                    LoginName = g.Key.LoginName,
                    Month     = month,
                    Yeart     = month.Year,
                    Montht    = month.Month,
                    TotalTime = g.Sum(x => x.TotalTime),
                };
                List <MonthCostByOwner> monthCostByOwners2 = new List <MonthCostByOwner>();
                monthCostByOwners2 = newmonthCostByOwners.ToList();
                foreach (var item in wbsInfo.WbsFolders)
                {
                    if (item.WBSUniqueNo == "01")
                    {
                        item.TimeCosts = null;
                        item.TimeCosts = new List <MonthCostByOwner>();
                        item.TimeCosts.AddRange(monthCostByOwners2);
                    }
                }
            }

            return(wbsInfo);
        }
Пример #6
0
        /// <summary>
        /// 获取每个wbs节点的工时统计并按人员分组
        /// </summary>
        /// <param name="wbsInfo">参数为wbs信息</param>
        /// <returns>返回带有工时统计的wbs信息</returns>
        private WBSInfo GetCostByOwner(WBSInfo wbsInfo)
        {
            using (ABZG_DSEntities dbcontent = new ABZG_DSEntities())
            {
                //                对应的sql语句
                //                -- - 获取对应目录中的所有任务
                //select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924)

                //  --获取对应任务的所有工时
                //select* from IssueTimeTracking where ProjectID = 563 and issueid in (
                // select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924))

                //--找出满足时间的所有工时

                //select* from IssueTimeTracking where ProjectID = 563 and issueid in (
                // select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924)) and DateAdded > '2019-08-01 00:00:00.000' and DateAdded< '2019-08-31 11:59:59.000'

                //     --分组

                //     select TimeItemOwnerID,l.Login,SUM(TimeHours) as 'total time','2019-08' as 'Month'  from IssueTimeTracking time join LogIn l on time.TimeItemOwnerID = l.PersonID where ProjectID = 563 and issueid in (
                //        select BugID from Bug where projectid = 563 and subprojectid in (19929, 19930, 19931, 19950, 19924)) and DateAdded > '2019-08-01 00:00:00.000' and DateAdded< '2019-08-31 11:59:59.000'
                //group by TimeItemOwnerID,l.Login

                foreach (WBSFolder item in wbsInfo.WbsFolders)
                {
                    //1. 获取对应的子目录Id
                    List <int> folders = ReleaseFolders[item.WBSUniqueNo];

                    //2. 根据所有的子目录,获取该wbs节点中所有的任务
                    var allTasks = from t in dbcontent.Bug
                                   join id in folders
                                   on t.SubProjectID equals id
                                   where t.ProjectID == wbsInfo.DevProjectID
                                   select t;


                    //3. 根据任务获取指定时间段内所有的工时记录
                    //取上月第一天
                    DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00.000")).AddMonths(-1);
                    //上月最后一天是当月第一天-1天
                    DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 23:59:59.000")).AddDays(-1);

                    ////测试数据,取当月
                    //DateTime checkMonthFrom = DateTime.Parse(DateTime.Now.ToString("2019-08-01 00:00:00.000"));
                    ////测试数据,取当月
                    //DateTime checkMonthTo = DateTime.Parse(DateTime.Now.ToString("2019-08-31 23:59:59.000"));

                    var alltimetracking = from track in dbcontent.IssueTimeTracking
                                          join tasks in allTasks
                                          on track.IssueID equals tasks.BugID
                                          where track.ProjectID == wbsInfo.DevProjectID
                                          // && track.DateAdded > checkMonthFrom && track.DateAdded < checkMonthTo
                                          && track.TimeItemFromDate > checkMonthFrom && track.TimeItemFromDate < checkMonthTo
                                          select track;

                    // 4.工时统计,并按人员分组,显示每位员工花费的总工时,月份以及统计的月份
                    var month = DateTime.Parse(checkMonthFrom.ToString("yyyy-MM"));


                    var groupbytimetracking = (from track in alltimetracking
                                               join lg in dbcontent.LogIn
                                               on track.TimeItemOwnerID equals lg.PersonID
                                               where track.ProjectID == wbsInfo.DevProjectID
                                               group new { track.TimeItemOwnerID, lg.Login1, track.TimeHours, Month = month }
                                               by new { track.TimeItemOwnerID, lg.Login1 } into g
                                               select new
                    {
                        PersonID = g.Key.TimeItemOwnerID,
                        LoginName = g.Key.Login1,
                        TotalTime = g.Sum(c => c.TimeHours),
                        CheckMonth = month
                    });


                    //遍历。。添加工时
                    List <MonthCostByOwner> monthCostsByOwner = new List <MonthCostByOwner>();
                    foreach (var item3 in groupbytimetracking)
                    {
                        monthCostsByOwner.Add(new MonthCostByOwner()
                        {
                            PersonID  = item3.PersonID,
                            LoginName = item3.LoginName,
                            TotalTime = item3.TotalTime,
                            Month     = item3.CheckMonth,
                            Yeart     = item3.CheckMonth.Year,
                            Montht    = item3.CheckMonth.Month
                        });
                    }


                    item.TimeCosts = monthCostsByOwner;
                }
            }

            return(wbsInfo);//待实现
        }
Пример #7
0
        //构造函数实现按照指定的项目SpaceID获取具体的项目信息
        public Project(ProjectBinder projectBinder)
        {
            using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
            {
                //立项任务编号
                //int? BugID = new ProjectHelper().GetProjectRequestTask(projectBinder);
                int?BugID = projectBinder.ProjectRequestID;


                //指定立项任务存储在表CustomerFieldTrackExt中的信息集合
                var customerFieldTrackExt = (from c in dbcontext.CustomerFieldTrackExt where c.ProjectID == projectBinder.ProjectID && c.BugID == BugID select c).SingleOrDefault <CustomerFieldTrackExt>();
                //指定SpaceID所在的表Subproject的单条数据
                var subproject = (from p in dbcontext.SubProject where p.ProjectID == projectBinder.ProjectID && p.SubProjectID == projectBinder.SpaceID select p).SingleOrDefault <SubProject>();

                //项目类型和编码的字典集合
                code4ProjectType = (from code in dbcontext.CustomerFieldListValue where code.ProjectID == projectBinder.ProjectID && code.CustomFieldID == 505 select code).ToDictionary(k => k.ChoiceName.Split(' ')[0], v => v.ChoiceName.Split(' ')[1]);
                //部门类型和编码的字典集合
                code4Depart = (from code in dbcontext.CustomerFieldListValue where code.ProjectID == projectBinder.ProjectID && code.CustomFieldID == 503 select code).ToDictionary(k => k.ChoiceName.Split(' ')[0], v => v.ChoiceName.Split(' ')[1]);

                //用户表集合
                var login = (from l in dbcontext.LogIn select l);

                //获取项目经理对应的personID
                var bugSelection = (from b in dbcontext.BugSelectionInfo select b);
                int loginID      = (from c in bugSelection where c.ProjectID == projectBinder.ProjectID && c.BugID == BugID && c.FieldID == 7 select c.FieldSelectionID).SingleOrDefault();

                //-------------以下是给项目属性赋值---------------------------
                //PPM基础项目编号
                BaseProjectID  = projectBinder.ProjectID;
                ProjectSpaceID = projectBinder.SpaceID;
                //项目编码
                ProjectUniqueID = customerFieldTrackExt.Desc_Custom_1;
                //项目名称
                ProjectName = subproject.Title;
                //项目类型
                ProjectType = customerFieldTrackExt.Desc_Custom_3 is null?"": customerFieldTrackExt.Desc_Custom_3;
                //项目类型对应的编码
                if (code4ProjectType.ContainsKey(ProjectType))
                {
                    ProjectTypeID = code4ProjectType[ProjectType];
                }
                ;

                //项目经理 need improve it..
                ProjectManager = (from c in login where c.PersonID == loginID select c.Login1).SingleOrDefault().ToString();
                //ProjectManager = 00000002;
                //项目代号
                ProjectCodeName = subproject.Title;

                //委托部门名称
                var code4DepartName = customerFieldTrackExt.Custom_4 == null ? "" : customerFieldTrackExt.Custom_4;

                //委托部门
                if (!(code4DepartName == ""))
                {
                    if (code4Depart.ContainsKey(customerFieldTrackExt.Custom_4))
                    {
                        DelegateDepartment = code4Depart[customerFieldTrackExt.Custom_4];
                    }
                    ;
                }
                else
                {
                    DelegateDepartment = "";
                }

                //承担部门
                if (code4Depart.ContainsKey(customerFieldTrackExt.Custom_6))
                {
                    UnderTaskDepartment = code4Depart[customerFieldTrackExt.Custom_6];
                }
                ;
                //项目复杂度等级
                ProjectComplexLevel = customerFieldTrackExt.Custom_10;
                //项目优先级
                ProjectPriority = customerFieldTrackExt.Custom_11;
                //项目状态
                switch (projectBinder.MyFlag)
                {
                case Flag.insert:
                    ProjectStatus = "下达";
                    break;

                case Flag.delay:
                    ProjectStatus = "暂停";
                    break;

                case Flag.close:
                    ProjectStatus = "关闭";
                    break;

                default:
                    ProjectStatus = "";
                    break;
                }
            }
        }
Пример #8
0
        public WBSInfo(ProjectBinder projectBinder)
        {
            //wbs编码赋值,初始化wbs编码字典,后续这里通过获取表的值,分析得到
            //code4Wbs = new Dictionary<string, string>()
            //    {
            //        { "TR1/TR2","01"},
            //        { "TR3","02"},
            //        { "TR4A","03"},
            //        { "TR5","04"},
            //        { "TR6","05"},
            //        { "概念与计划阶段","06"},
            //        { "开发阶段","07"},
            //        { "验证阶段","08"},
            //        { "结项阶段","09"},
            //        { "立项准备","10"}
            //    };


            using (ABZG_DSEntities dbcontext = new ABZG_DSEntities())
            {
                //  code4Wbs = (from code in dbcontext.CustomerFieldListValue where code.ProjectID == projectBinder.ProjectID && code.CustomFieldID == 508 select code).ToDictionary(k => k.ChoiceName.Split(' ')[0], v => v.ChoiceName.Split(' ')[1]);
                //立项任务编号
                // int? BugID = new ProjectHelper().GetProjectRequestTask(projectBinder);
                int?BugID = projectBinder.ProjectRequestID;
                //指定立项任务存储在表CustomerFieldTrackExt中的信息集合
                var customerFieldTrackExt = (from c in dbcontext.CustomerFieldTrackExt where c.ProjectID == projectBinder.ProjectID && c.BugID == BugID select c).SingleOrDefault <CustomerFieldTrackExt>();

                var spaceLink = (from link in dbcontext.SpaceLink where link.ProjectID == projectBinder.ProjectID && link.ProjectTypeID == 41 && link.SpaceID == projectBinder.SpaceID select link);

                //项目类型------
                ProjectType = customerFieldTrackExt.Desc_Custom_3 is null ? "" : customerFieldTrackExt.Desc_Custom_3;

                //项目编码
                ProjectUniqueID = customerFieldTrackExt.Desc_Custom_1;

                if (spaceLink.Where(s => s.ToProjectTypeID == 1).Count() > 0)
                {
                    DevProjectID = spaceLink.Where(s => s.ToProjectTypeID == 1).SingleOrDefault().ToProjectID;
                    DevSpaceID   = spaceLink.Where(s => s.ToProjectTypeID == 1).SingleOrDefault().ToSpaceID;
                    //把sql的in查询用linq的嵌套查询来实现
                    //第一步:获取项目下的child folder  id列表
                    var releaseIDList = from f in dbcontext.SubProjectTree where f.ProjectID == DevProjectID && f.ParentID == DevSpaceID select f.ChildID;

                    //第二步:获取所有的sub folders
                    // var allreleaseFolders = from sub in dbcontext.SubProject where sub.ProjectID == DevProjectID && sub.SubProjectType == 99 select sub;
                    var allreleaseFolders = from sub in dbcontext.SubProject where sub.ProjectID == DevProjectID && sub.SubProjectType != 2002
                                            select sub;

                    //第三步:获取对应项目下的满足的folders
                    var releaseFolders = from s in allreleaseFolders where releaseIDList.Contains(s.SubProjectID) select new { s.Title, s.ProjectID, s.SubProjectID };

                    WbsFolders = new List <WBSFolder>();
                    if (releaseFolders.Count() > 0)
                    {
                        foreach (var item in releaseFolders)
                        {
                            WbsFolders.Add(new WBSFolder()
                            {
                                WBSFolderID = item.SubProjectID,
                                WBSName     = item.Title,
                                // WBSUniqueNo = code4Wbs.ContainsKey(item.Title) ? code4Wbs[item.Title] : "NoneMatchCode"
                                WBSUniqueNo = item.SubProjectID.ToString()
                            });
                        }
                    }
                    else
                    {
                    }
                }
                ;
            }
        }