/// <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;
}
/// <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;
}
/// <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;
}