/// <summary>
/// 根据当前登录人取管辖分组所有项目的分配，提交以及接通，成功等统计量
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public DataTable GetB_ProjectReport(Entities.BusinessReport.QueryProjectReport query, string order, int currentPage, int pageSize, out int totalCount, int userid)
{
string where = string.Empty;
string where1 = string.Empty;
if (query.ProjectID != Constant.INT_INVALID_VALUE)
{
where += " and b.projectid=" + query.ProjectID;
where1 += " and b.projectid=" + query.ProjectID;
}
if (!string.IsNullOrEmpty(query.AgentNum))
{
where += " and c.AgentNum= '" + StringHelper.SqlFilter(query.AgentNum) + "'";
where1 += " and c.AgentNum= '" + StringHelper.SqlFilter(query.AgentNum) + "'";
}
if (query.UserID != Constant.INT_INVALID_VALUE)
{
where += " and c.userid= " + query.UserID;
where1 += " and c.userid= " + query.UserID;
}
if (query.BeginTime != Constant.DATE_INVALID_VALUE && query.EndTime != Constant.DATE_INVALID_VALUE)
{
if (query.BusinessType == 6)
{
where1 += " and a.LastUpdateTime>='" + query.BeginTime + "' and a.LastUpdateTime<Dateadd(day,1,'" + query.EndTime + "')";
}
if (query.BusinessType == 4)
{
where1 += " and a.lastopttime>='" + query.BeginTime + "' and a.lastopttime<Dateadd(day,1,'" + query.EndTime + "')";
}
}
order = " tjcount desc";
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 4000),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4),
new SqlParameter("@BussinessType", SqlDbType.Int, 4),
new SqlParameter("@userid", SqlDbType.Int, 4),
new SqlParameter("@where1", SqlDbType.NVarChar, 4000),
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
parameters[5].Value = query.BusinessType;
parameters[6].Value = userid;
parameters[7].Value = where1;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_ProjectBussiness_Select", parameters);
totalCount = (int)(parameters[4].Value);
return(ds.Tables[0]);
}
/// <summary>
/// 根据当前登录人取管辖分组所有项目的分配，提交以及接通，成功等统计量总计
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public DataTable GetB_ProjectReportSum_Excel(Entities.BusinessReport.QueryProjectReport query, int userid)
{
string where = string.Empty;
string where1 = string.Empty;
if (query.ProjectID != Constant.INT_INVALID_VALUE)
{
where += " and b.projectid=" + query.ProjectID;
where1 += " and b.projectid=" + query.ProjectID;
}
if (!string.IsNullOrEmpty(query.AgentNum))
{
where += " and c.AgentNum= '" + StringHelper.SqlFilter(query.AgentNum) + "'";
where1 += " and c.AgentNum= '" + StringHelper.SqlFilter(query.AgentNum) + "'";
}
if (query.UserID != Constant.INT_INVALID_VALUE)
{
where += " and c.userid= " + query.UserID;
where1 += " and c.userid= " + query.UserID;
}
if (query.BeginTime != Constant.DATE_INVALID_VALUE && query.EndTime != Constant.DATE_INVALID_VALUE)
{
if (query.BusinessType == 6)
{
where1 += " and a.LastUpdateTime>='" + query.BeginTime + "' and a.LastUpdateTime<=Dateadd(day,1,'" + query.EndTime + "')";
}
if (query.BusinessType == 4)
{
where1 += " and a.lastopttime>='" + query.BeginTime + "' and a.lastopttime<=Dateadd(day,1,'" + query.EndTime + "')";
}
}
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where",
SqlDbType.NVarChar, 4000),
new SqlParameter("@BussinessType", SqlDbType.Int, 4),
new SqlParameter("@userid", SqlDbType.Int, 4),
new SqlParameter("@where1", SqlDbType.NVarChar, 4000),
new SqlParameter("@projectid", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = query.BusinessType;;
parameters[2].Value = userid;
parameters[3].Value = where1;
parameters[4].Value = query.ProjectID;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_ProjectBussiness_Sum_Export", parameters);
return(ds.Tables[0]);
}
/// <summary>
/// 根据当前登录人取管辖分组所有项目的分配，提交以及接通，成功等统计量总计
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public DataTable GetB_ProjectReportSum_Excel(Entities.BusinessReport.QueryProjectReport query, int userid)
{
return(Dal.ProjectInfo.Instance.GetB_ProjectReportSum_Excel(query, userid));
}
/// <summary>
/// 根据当前登录人取管辖分组所有项目的分配，提交以及接通，成功等统计量
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public DataTable GetB_ProjectReport(Entities.BusinessReport.QueryProjectReport query, string order, int currentPage, int pageSize, out int totalCount, int userid)
{
return(Dal.ProjectInfo.Instance.GetB_ProjectReport(query, order, currentPage, pageSize, out totalCount, userid));
}