protected virtual SqlParameter CreateParamter_Orderby(ReportSearchEntity sp)
{
StringBuilder sb = new StringBuilder();
sb.Append(" r.CreateTime DESC ");
return(new SqlParameter("@OrderField", sb.ToString()));
}
/// <summary>
///
/// </summary>
/// <param name="sp"></param>
/// <returns></returns>
protected virtual SqlParameter CreateParameter_Fileds(ReportSearchEntity sp)
{
StringBuilder sbfileds = new StringBuilder();
sbfileds.Append(@"r.*
,drt.ReportTypeDesc
,drit.ReportInfoTypeDesc
,u.NickName AS FormUserName
,tu.NickName AS ToUserName
,ISNULL(ec.CommentContent,'')+ ISNULL(er.ReplyContent,'') AS ReportInfo
,CONVERT(NVARCHAR(20),r.CreateTime,20) AS FormatCreateTime");
return(new SqlParameter("@Fields", sbfileds.ToString()));
}
/// <summary>
///
/// </summary>
/// <param name="sp"></param>
/// <returns></returns>
protected virtual SqlParameter CreateParameter_Table(ReportSearchEntity sp)
{
StringBuilder sbtable = new StringBuilder();
sbtable.Append(@"[dbo].[M_Report] AS r
INNER JOIN dbo.M_Dict_ReportType AS drt ON drt.ReportTypeID = r.ReportTypeID
INNER JOIN dbo.M_Dict_ReportInfoType AS drit ON drit.ReportInfoTypeID = r.ReportInfoTypeID
LEFT JOIN dbo.M_User AS u ON r.FromUserID = u.UserID
LEFT JOIN dbo.M_EventItem_Comments AS ec ON ec.CommentID = r.ReportInfoID AND r.ReportInfoTypeID = 1
LEFT JOIN dbo.M_EventItem_Replies AS er ON er.ReplyID = r.ReportInfoID AND r.ReportInfoTypeID= 2
LEFT JOIN dbo.M_User AS tu ON tu.UserID = ec.UserID OR tu.UserID=er.UserID");
return(new SqlParameter("@TableName", sbtable.ToString()));
}
/// <summary>
/// 核心方法。查询条件转换成sql参数
/// </summary>
/// <param name="sp"></param>
/// <returns></returns>
protected virtual List <SqlParameter> ParseToSqlParameters(ReportSearchEntity sp)
{
List <SqlParameter> _parameters = new List <SqlParameter>();
_parameters.Add(CreateParameter_Table(sp));
_parameters.Add(CreateParameter_Fileds(sp));
_parameters.Add(CreateParamter_Orderby(sp));
_parameters.Add(CreateParameter_Where(sp));
_parameters.Add(new SqlParameter("@pageSize", sp.PageSize));
_parameters.Add(new SqlParameter("@pageIndex", sp.PageIndex));
_parameters.Add(new SqlParameter()
{
ParameterName = "@Records", Value = 0, Direction = System.Data.ParameterDirection.Output
});
return(_parameters);
}
/// <summary>
///
/// </summary>
/// <param name="info"></param>
/// <param name="totalcnt"></param>
/// <returns></returns>
public DataTable QueryData(ReportSearchEntity info, out int totalcnt)
{
try
{
SqlParameter[] prms = ParseToSqlParameters(info).ToArray();
DataTable dt = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager05", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
return(dt);
}
catch (Exception ex)
{
totalcnt = -1;
LogUtil.WriteLog(ex);
return(null);
}
}
//,CONVERT(varchar(30), a.ActivityStartTime, 20) as ActivityStartTime
//,CONVERT(varchar(30), a.ActivityEndTime, 20) as ActivityEndTime
/// <summary>
///
/// </summary>
/// <param name="sp"></param>
/// <returns></returns>
protected virtual SqlParameter CreateParameter_Where(ReportSearchEntity sp)
{
StringBuilder sbwhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(sp.StartTime) && !string.IsNullOrEmpty(sp.EndTime))
{
sbwhere.AppendFormat(" AND r.CreateTime BETWEEN '{0} 00:00:00' AND '{1} 23:59:59' "
, sp.StartTime, sp.EndTime);
}
else if (string.IsNullOrEmpty(sp.StartTime) && !string.IsNullOrEmpty(sp.EndTime))
{
sbwhere.AppendFormat(" AND r.CreateTime < '{0} 23:59:59' "
, sp.EndTime);
}
else if (!string.IsNullOrEmpty(sp.StartTime) && string.IsNullOrEmpty(sp.EndTime))
{
sbwhere.AppendFormat(" AND r.CreateTime > '{0} 00:00:00' "
, sp.StartTime);
}
return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
}
/// <summary>
/// 使用缓存查询信息
/// </summary>
/// <param name="info">查询主体</param>
/// <param name="totalcnt">返回条目数</param>
/// <returns></returns>
public DataTable QueryData(ReportSearchEntity info, out int totalcnt)
{
return(dao.QueryData(info, out totalcnt));
}
