/// <summary>
/// 获取查询数据的JSON结果（多表查询，WEB用）
/// </summary>
/// <param name="sqlCmdName">执行的SQL</param>
///<param name="filter">查询参数</param>
///<param name="where">过滤语句</param>
///<param name="orderBy">结果排序</param>
/// <param name="hsDDL">列表中的下拉框</param>
/// <returns>JSON结果</returns>
public string GetCmdQueryData(string sqlCmdName, Hashtable filter, string where, string orderBy, Hashtable hsDDL)
{
#region 查询结果集
/*
* command.config文件
SELECT T_.*, ROW_NUMBER() OVER(ORDER BY 行号标志字段) RN FROM ( SQL语句) T_ WHERE 1=1
*/
int pageindex = 0, pagesize = 0;
int draw = 0;
if (HttpContext.Current.Request["draw"] != null)
int.TryParse(HttpContext.Current.Request["draw"], out draw);
if (HttpContext.Current.Request["start"] != null)
int.TryParse(HttpContext.Current.Request["start"], out pageindex);
pageindex = pageindex == 0 ? 1 : pageindex;
if (HttpContext.Current.Request["length"] != null)
int.TryParse(HttpContext.Current.Request["length"], out pagesize);
if (pagesize == 0)
pageindex = 1;
else
pageindex = pageindex / pagesize + 1;
if (pagesize == 0)
pagesize = 10;//默认10条
if (!string.IsNullOrEmpty(orderBy))
{
orderBy = " order by " + orderBy;
}
var cmd = ds.GetCommand(sqlCmdName);
DacHelper.PrepareCommand(cmd, filter);
if (!string.IsNullOrEmpty(where))
{
if (!where.Trim().ToLower().StartsWith("and"))
{
cmd.DbCommand.CommandText += " AND " + where;
}
else
{
cmd.DbCommand.CommandText += where;
}
}
var oldCmdText = cmd.DbCommand.CommandText;
cmd.DbCommand.CommandText = string.Format("select count(1) from ({0}) c_", oldCmdText);
object count = cmd.ExecuteScalar();
cmd.DbCommand.CommandText = string.Format("select * from ({0}) list_ where list_.RN>{1} and list_.RN<={2} {3}", oldCmdText, pagesize * (pageindex - 1), pagesize * pageindex, orderBy);
var dt = cmd.ExecuteDataTable();
if (hsDDL != null && hsDDL.Count > 0)
{
cod.ConvertTabDdl(dt, hsDDL);
}
#endregion 查询结果集
#region 新增"审批类型"，“审批岗位描述”两列
//添加"审批类型"，“审批岗位描述”两列
dt.Columns.Add("APPROVE_TYPE", typeof(string)); //数据类型为 文本
dt.Columns.Add("POST_NOTE", typeof(string)); //数据类型为 文本
//添加"撤销审批类型"，“撤销审批岗位描述”两列
dt.Columns.Add("REVOKE_APPROVE_TYPE", typeof(string)); //数据类型为 文本
dt.Columns.Add("REVOKE_POST_NOTE", typeof(string)); //数据类型为 文本
#endregion 新增"审批类型"，“审批岗位描述”两列
#region 给"审批类型"，“审批岗位描述”两列赋值
foreach (DataRow row in dt.Rows)
{
//审批类型
row["APPROVE_TYPE"] = GetApproveType(row["DOC_TYPE"].ToString(), WKF_VLAUES.DECLARE_TYPE_D);
//审批岗位描述
row["POST_NOTE"] = GetPostNote(row["DOC_TYPE"].ToString());
//撤销审批类型
row["REVOKE_APPROVE_TYPE"] = GetApproveType(row["DOC_TYPE"].ToString(), WKF_VLAUES.DECLARE_TYPE_R);
//撤销审批岗位描述
row["REVOKE_POST_NOTE"] = GetPostNote_Revoke(row["DOC_TYPE"].ToString());
}
#endregion 给"审批类型"，“审批岗位描述”两列赋值
//return string.Format("{{\"total\":{0},\"rows\":[{1}]}}", count == null ? 0 : Convert.ToInt32(count), Json.DatatableToJson(dt));
return string.Format("{{\"draw\":{0},\"recordsTotal\":{1},\"recordsFiltered\":{2},\"data\":[{3}]}}", draw, count == null ? 0 : Convert.ToInt32(count), dt.Rows.Count, Json.DatatableToJson(dt));
}
