예제 #1
0
 public IList<WorkloadEntity> GetWorkloadList(WorkloadQuery query)
 {
     try
     {
         IAuthorInfoService authorService = ServiceContainer.Instance.Container.Resolve<IAuthorInfoService>();
         IList<WorkloadEntity> listAuthorStat = authorService.GetWorkloadList(query);
         return listAuthorStat;
     }
     catch (Exception ex)
     {
         LogProvider.Instance.Error("编辑、专家工作量统计出现异常:" + ex.Message);
         throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.Message));
     }
 }
예제 #2
0
        /// <summary>
        /// 编辑工作量统计(不再统计已删除与已撤稿件) zhanglc 2014-09-27
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IList<WorkloadEntity> GetWorkloadList(WorkloadQuery query)
        {
            IList<WorkloadEntity> authorStatList = new List<WorkloadEntity>();
            string strSql = "";
            if (query.GroupID == 1)
            {
                strSql = @"
                            SELECT a.AuthorID,a.LoginName,a.RealName,ad.AuthorName,ad.Address,ad.WorkUnit,ad.InvoiceUnit,ad.ZipCode,ad.Mobile,ad.Tel,COUNT(DISTINCT f.CID) AS AlreadyCount,COUNT(DISTINCT f2.CID) AS WaitCount FROM
                            dbo.AuthorInfo a WITH(NOLOCK)
                            left join dbo.AuthorDetail ad WITH(NOLOCK) on a.JournalID=ad.JournalID and a.AuthorID=ad.AuthorID
                            left join dbo.FlowLogInfo f WITH(NOLOCK) on a.JournalID=f.JournalID and a.AuthorID=f.RecUserID and f.Status=1 and f.CID in (select CID from ContributionInfo where Status!=-999 and Status!=-1) and f.DealDate>=@StartDate AND f.DealDate<@EndDate
                            left join dbo.FlowLogInfo f2 WITH(NOLOCK) on a.JournalID=f2.JournalID and a.AuthorID=f2.RecUserID and f2.Status=0 and f2.CID in (select CID from ContributionInfo where Status!=-999 and Status!=-1) and f2.AddDate>=@StartDate AND f2.AddDate<@EndDate
                            where a.JournalID=@JournalID and a.RealName like '%" + query.RealName + @"%' and a.GroupID=@GroupID
                            GROUP BY a.AuthorID,a.LoginName,a.RealName,ad.Address,ad.WorkUnit,ad.InvoiceUnit,ad.ZipCode,ad.Mobile,ad.Tel,ad.ReserveField1,ad.ReserveField2,ad.ReserveField3";
            }
            else
            {
                strSql = @"
                            SELECT a.AuthorID,a.LoginName,a.RealName,ad.Address,ad.WorkUnit,ad.InvoiceUnit,ad.ZipCode,ad.Mobile,ad.Tel,ad.ReserveField1,ad.ReserveField2,ad.ReserveField3,COUNT(DISTINCT f.CID) AS AlreadyCount,COUNT(DISTINCT f2.CID) AS WaitCount FROM
                            dbo.AuthorInfo a WITH(NOLOCK)
                            left join dbo.AuthorDetail ad WITH(NOLOCK) on a.JournalID=ad.JournalID and a.AuthorID=ad.AuthorID
                            left join dbo.FlowLogInfo f WITH(NOLOCK) on a.JournalID=f.JournalID and a.AuthorID=f.RecUserID and f.Status=1 and f.CID in (select CID from ContributionInfo where Status!=-999 and Status!=-1) and f.DealDate>=@StartDate AND f.DealDate<@EndDate
                            left join dbo.FlowLogInfo f2 WITH(NOLOCK) on a.JournalID=f2.JournalID and a.AuthorID=f2.RecUserID and f2.Status=0 and f2.CID in (select CID from ContributionInfo where Status!=-999 and Status!=-1) and f2.AddDate>=@StartDate AND f2.AddDate<@EndDate
                            where a.JournalID=@JournalID and a.RealName like '%" + query.RealName + @"%' and a.GroupID in (3,4)
                            GROUP BY a.AuthorID,a.LoginName,a.RealName,ad.Address,ad.WorkUnit,ad.InvoiceUnit,ad.ZipCode,ad.Mobile,ad.Tel,ad.ReserveField1,ad.ReserveField2,ad.ReserveField3";
            }

            if (!string.IsNullOrEmpty(query.OrderBy))
            {
                strSql += " ORDER BY " + WKT.Common.Security.SecurityUtils.SafeSqlString(query.OrderBy.TextFilter());
            }
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            db.AddInParameter(cmd, "@JournalID", DbType.Int64, query.JournalID);
            db.AddInParameter(cmd, "@StartDate", DbType.DateTime, query.StartDate == null ? Convert.ToDateTime("2000-01-01") : query.StartDate);
            db.AddInParameter(cmd, "@EndDate", DbType.DateTime, query.EndDate == null ? DateTime.Now.AddDays(1) : query.EndDate.Value.AddDays(1));
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    WorkloadEntity item = new WorkloadEntity();
                    item.AuthorID = TypeParse.ToLong(dr["AuthorID"]);
                    item.LoginName = dr["LoginName"].ToString();
                    item.RealName = dr["RealName"].ToString();
                    item.Address = dr["Address"].ToString();
                    item.WorkUnit = dr["WorkUnit"].ToString();
                    item.InvoiceUnit = dr["InvoiceUnit"].ToString();
                    item.ZipCode = dr["ZipCode"].ToString();
                    item.Mobile = dr["Mobile"].ToString();
                    item.Tel = dr["Tel"].ToString();
                    if (query.GroupID == 3)
                    {
                        item.ReserveField1 = dr["ReserveField1"].ToString();
                        item.ReserveField2 = dr["ReserveField2"].ToString();
                        item.ReserveField3 = dr["ReserveField3"].ToString();
                    }
                    item.AlreadyCount = TypeParse.ToInt(dr["AlreadyCount"]);
                    item.WaitCount = TypeParse.ToInt(dr["WaitCount"]);
                    authorStatList.Add(item);
                }
                dr.Close();
            }
            return authorStatList;
        }
예제 #3
0
        /// <summary>
        /// 个人收稿量 文海峰 2014-1-7
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IList<WorkloadEntity> GetPersonCirCountList(WorkloadQuery query)
        {
            IList<WorkloadEntity> authorStatList = new List<WorkloadEntity>();
            string strSql = @"select ar.AuthorID,RealName,count(CID) as CirCount from  [FlowLogInfo]  inner join (select AuthorID,RealName from  AuthorInfo where GroupID=1) as ar on ar.AuthorID=FlowLogInfo.RecUserID and  FlowLogID in (select FlowLogID from  [FlowLogInfo] where   ";
            if (!string.IsNullOrEmpty(query.StatusList))
            {
                string[] paras = query.StatusList.Split(',');
                for (int i = 0; i < paras.Length; i++)
                {
                    if (i == paras.Length-1)
                    {
                         strSql += "  TargetStatusID="+paras[i];
                    }
                    else
                    {
                        strSql += "  TargetStatusID="+ paras[i]+ " OR ";
                    }
                }

            }
            if (query.StartDate != null)
            {
                strSql += "  AddDate>=@StartDate AND";
            }
            else if (query.EndDate != null)
            {
                strSql += " AddDate<=@EndDate AND";
            }
            strSql += "  JournalID=@JournalID ) group by AuthorID,RealName ";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            db.AddInParameter(cmd, "@JournalID", DbType.Int64, query.JournalID);
            db.AddInParameter(cmd, "@Statuslist", DbType.Int64, query.StatusList);
            db.AddInParameter(cmd, "@StartDate", DbType.DateTime, query.StartDate == null ? Convert.ToDateTime("2000-01-01") : query.StartDate);
            db.AddInParameter(cmd, "@EndDate", DbType.DateTime, query.EndDate == null ? DateTime.Now.AddDays(1) : query.EndDate.Value.AddDays(1));

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    WorkloadEntity item = new WorkloadEntity();
                    item.RealName = Convert.IsDBNull(dr["RealName"])?"" : Convert.ToString(dr["RealName"]);
                    item.AlreadyCount = TypeParse.ToInt(dr["CirCount"]);
                    authorStatList.Add(item);
                }
                dr.Close();
            }
            return authorStatList;
        }
예제 #4
0
        /// <summary>
        /// 编辑工作量统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        //        public IList<WorkloadEntity> GetEditorWorkloadList(WorkloadQuery query)
        //        {
        //            IList<WorkloadEntity> authorStatList = new List<WorkloadEntity>();
        //            string strSql = @"
        //                            SELECT ai.AuthorID,ai.LoginName,ai.RealName
        //                            FROM dbo.AuthorInfo ai WITH(NOLOCK)
        //                            WHERE ai.JournalID=@JournalID AND ai.GroupID=1
        //                            ";
        //            if (!string.IsNullOrEmpty(query.RealName))
        //            {
        //                //strSql = strSql + " AND a.RealName LIKE '%" + WKT.Common.Security.SecurityUtils.SafeSqlString(query.RealName) + "%'";
        //                strSql = strSql + " AND ai.RealName LIKE '%" + query.RealName + "%'";
        //            }
        //            DbCommand cmd = db.GetSqlStringCommand(strSql);
        //            db.AddInParameter(cmd, "@JournalID", DbType.Int64, query.JournalID);
        //            // 获取统计数量
        //            List<WorkloadEntity> listWorkStat = GetEditorStatWorkStat(query);
        //            // 获取统计的数据字典
        //            IDictionary<int, string> dictKey = null;
        //            if (query.IsNewContributes)
        //            {
        //                dictKey = DictValueDataAccess.Instance.GetDictValueDcit(query.JournalID, "NewContribute");
        //            }
        //            else
        //            {
        //                dictKey = DictValueDataAccess.Instance.GetDictValueDcit(query.JournalID, "EditorWorkStat");
        //            }
        //            using (IDataReader dr = db.ExecuteReader(cmd))
        //            {
        //                while (dr.Read())
        //                {
        //                    WorkloadEntity item = new WorkloadEntity();
        //                    item.AuthorID = TypeParse.ToLong(dr["AuthorID"]);
        //                    item.LoginName = dr["LoginName"].ToString();
        //                    item.RealName = dr["RealName"].ToString();
        //                    foreach (var item1 in dictKey.Keys)
        //                    {
        //                        item.DictEditorStatItems.Add(dictKey[item1] + "-" + item1, 0);
        //                    }
        //                    List<WorkloadEntity> statItemList = listWorkStat.Where(p => p.AuthorID == item.AuthorID).ToList<WorkloadEntity>();
        //                    if (statItemList != null && statItemList.Count > 0)
        //                    {
        //                        foreach (WorkloadEntity wleItem in statItemList)
        //                        {
        //                            string key = dictKey.ContainsKey(wleItem.StatusID) ? dictKey[wleItem.StatusID] + "-" + wleItem.StatusID.ToString() : "";
        //                            int value = wleItem.WorkCount;
        //                            if (item.DictEditorStatItems.Keys.Contains(key))
        //                            {
        //                                item.DictEditorStatItems[key] = value;
        //                            }
        //                            else
        //                            {
        //                                item.DictEditorStatItems.Add(key, value);
        //                            }
        //                        }
        //                    }
        //                    authorStatList.Add(item);
        //                }
        //                dr.Close();
        //            }
        //            return authorStatList;
        //        }
        /// <summary>
        /// 获取人员数据字典
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private List<WorkloadEntity> GetEditorStatWorkStat(WorkloadQuery query)
        {
            List<WorkloadEntity> listWork = new List<WorkloadEntity>();
            string strSql = string.Empty;
            if (query.IsNewContributes)
            {
                strSql = @"
                            SELECT ai.AuthorID,fi.StatusID, ai.RealName,COUNT(ai.AuthorID) as WorkCount
                            FROM dbo.AuthorInfo ai WITH(NOLOCK)
                                INNER JOIN dbo.FlowLogInfo fi WITH(NOLOCK) ON ai.AuthorID=fi.RecUserID and ai.GroupID=1
                                INNER JOIN dbo.ContributionInfo ci WITH(NOLOCK) ON fi.CID=ci.CID AND ci.Status!=-999
                            WHERE ai.JournalID=@JournalID AND ai.JournalID=fi.JournalID AND fi.JournalID=ci.JournalID AND fi.StatusID=fi.TargetStatusID and fi.TargetStatusID IN (
                            SELECT dv.ValueID FROM DictValue dv WITH(NOLOCK) WHERE dv.DictKey='NewContribute'
                            ) AND fi.AddDate>=@StartDate AND fi.AddDate<@EndDate
                            ";
            }
            else
            {
                strSql = @"
                            SELECT ai.AuthorID,fi.TargetStatusID,COUNT(DISTINCT fi.CID) as WorkCount
                            FROM dbo.AuthorInfo ai WITH(NOLOCK)
                                INNER JOIN dbo.FlowLogInfo fi WITH(NOLOCK) ON ai.AuthorID=fi.SendUserID and ai.GroupID=1
                                INNER JOIN dbo.ContributionInfo ci WITH(NOLOCK) ON fi.CID=ci.CID AND ci.Status!=-999
                            WHERE ai.JournalID=@JournalID AND ai.JournalID=fi.JournalID AND fi.JournalID=ci.JournalID AND fi.TargetStatusID IN (
                            SELECT dv.ValueID FROM DictValue dv WITH(NOLOCK) WHERE dv.DictKey='EditorWorkStat'
                            ) AND fi.AddDate>=@StartDate AND fi.AddDate<@EndDate
                            ";
            }
            if (!string.IsNullOrEmpty(query.RealName))
            {
                strSql = strSql + " AND ai.RealName LIKE '%" + WKT.Common.Security.SecurityUtils.SafeSqlString(query.RealName) + "%'";
            }
            strSql += query.IsNewContributes ? " GROUP BY ai.RealName,ai.AuthorID,fi.StatusID" : " GROUP BY ai.AuthorID,fi.TargetStatusID";
            DbCommand cmd = db.GetSqlStringCommand(strSql);

            db.AddInParameter(cmd, "@JournalID", DbType.Int64, query.JournalID);
            db.AddInParameter(cmd, "@StartDate", DbType.DateTime, query.StartDate == null ? Convert.ToDateTime("2000-01-01") : query.StartDate);
            db.AddInParameter(cmd, "@EndDate", DbType.DateTime, query.EndDate == null ? DateTime.Now.AddDays(1) : query.EndDate.Value.AddDays(1));

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                WorkloadEntity item = null;
                while (dr.Read())
                {
                    item = new WorkloadEntity();
                    if (DBConfigUtility.IsContainsColumn(dr, "TargetStatusID"))
                    {
                        item.StatusID = TypeParse.ToInt(dr["TargetStatusID"]);
                    }
                    if (DBConfigUtility.IsContainsColumn(dr, "RealName"))
                    {
                        item.RealName = TypeParse.ToString(dr["RealName"]);
                    }

                    if (DBConfigUtility.IsContainsColumn(dr, "StatusID"))
                    {
                        item.StatusID = TypeParse.ToInt(dr["StatusID"]);
                    }
                    item.AuthorID = TypeParse.ToLong(dr["AuthorID"]);
                    item.WorkCount = TypeParse.ToInt(dr["WorkCount"]);
                    listWork.Add(item);
                }
                dr.Close();
            }
            return listWork;
        }
예제 #5
0
 /// <summary>
 /// 编辑、专家工作量统计
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public IList<WorkloadEntity> GetWorkloadList(WorkloadQuery query)
 {
     return AuthorInfoBusProvider.GetWorkloadList(query);
 }
예제 #6
0
 /// <summary>
 /// 编辑、专家工作量统计
 /// </summary>
 /// <param name="query"></param>
 /// <returns></returns>
 public IList<WorkloadEntity> GetWorkloadList(WorkloadQuery query)
 {
     return AuthorInfoDataAccess.Instance.GetWorkloadList(query);
 }