/// <summary>
/// 日期格式化
/// </summary>
/// <param name="json">json</param>
/// <param name="fromat">格式</param>
/// <returns></returns>
public string DateTimeFormat(pagingJson json, string fromat)
{
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = fromat;
return(JsonConvert.SerializeObject(json, Formatting.Indented, timeFormat));
}
// 获取用户列表
private string getUserList()
{
pagingJson paging = new pagingJson();
try
{
//获取Datatables发送的参数 必要
int draw = Int32.Parse(Funcs.Get("draw"));//这个值作者会直接返回给前台
//排序
string orderColumn = Funcs.Get("order[0][column]"); //那一列排序，从0开始
string orderDir = Funcs.Get("order[0][dir]"); //ase desc 升序或者降序
//搜索
string sStatus = Funcs.Get("sStatus"); //用户状态
string sUserType = Funcs.Get("sUserType"); //用户类型
string sName = GlobalObject.unescape(Funcs.Get("sName")); //搜索框值
//分页
int start = Int32.Parse(Funcs.Get("start")); //第一条数据的起始位置
int length = Int32.Parse(Funcs.Get("length")); //每页显示条数
// where条件,排序条件
string strWhere = "tt.userStatus!=2", strOrderBy = "";
if (sStatus != null && sStatus != "")
{
strWhere += " and userStatus=" + sStatus;
}
if (sUserType != null && sUserType != "")
{
strWhere += " and userType=" + sUserType;
}
if (sName != null && sName != "")
{
strWhere += " and (userName like '%" + Funcs.ToSqlString(sName) + "%' or trueName like '%" + Funcs.ToSqlString(sName) + "%')";
}
if (orderColumn != "" && orderDir != "")
{
strOrderBy = Funcs.Get("columns[" + orderColumn + "][data]") + " " + orderDir;
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@param_table", SqlDbType.Text)
{
Value = "(select su.*,sp.paramsName from system_users su left join system_params sp on su.userType=sp.id)tt"
},
new SqlParameter("@param_field", SqlDbType.VarChar)
{
Value = "*"
},
new SqlParameter("@param_where", SqlDbType.Text)
{
Value = strWhere
},
new SqlParameter("@param_groupBy", SqlDbType.VarChar)
{
Value = ""
},
new SqlParameter("@param_orderBy", SqlDbType.VarChar)
{
Value = strOrderBy
},
new SqlParameter("@param_pageNumber", SqlDbType.VarChar)
{
Value = start / length + 1
},
new SqlParameter("@param_pageSize", SqlDbType.Int)
{
Value = length
},
new SqlParameter("@param_isCount", SqlDbType.Int)
{
Value = 1
}
};
DataSet ds = Utility.SqlHelper.ExecProcFillDataSet("sp_GetPagingList", param);
paging.draw = draw;
paging.recordsTotal = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
paging.data = ds.Tables[0];
paging.recordsFiltered = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
return(DateTimeFormat(paging, "yyyy-MM-dd HH:mm "));
}
catch (Exception ex)
{
paging.error = "获取用户列表失败:" + ex.Message;
paging.data = null;
return(JsonConvert.SerializeObject(paging));
}
}
// 获取用户类型列表
private string getTypeList()
{
pagingJson paging = new pagingJson();
try
{
//获取Datatables发送的参数 必要
int draw = Int32.Parse(Funcs.Get("draw"));//请求次数 这个值作者会直接返回给前台
//排序
string orderColumn = Funcs.Get("order[0][column]"); //那一列排序，从0开始
string orderDir = Funcs.Get("order[0][dir]"); //ase desc 升序或者降序
//搜索
string userType = GlobalObject.unescape(Funcs.Get("userType"));//搜索框值
//分页
int start = Int32.Parse(Funcs.Get("start")); //第一条数据的起始位置
int length = Int32.Parse(Funcs.Get("length")); //每页显示条数
// where条件,排序条件
string strWhere = "parentId=1", strOrderBy = "";
if (userType != null && userType != "")
{
strWhere += " and paramsName like '%" + Funcs.ToSqlString(userType) + "%'";
}
if (orderColumn != "" && orderDir != "")
{
strOrderBy = Funcs.Get("columns[" + orderColumn + "][data]") + " " + orderDir;
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@param_table", SqlDbType.Text)
{
Value = "system_params"
},
new SqlParameter("@param_field", SqlDbType.VarChar)
{
Value = "*"
},
new SqlParameter("@param_where", SqlDbType.Text)
{
Value = strWhere
},
new SqlParameter("@param_groupBy", SqlDbType.VarChar)
{
Value = ""
},
new SqlParameter("@param_orderBy", SqlDbType.VarChar)
{
Value = strOrderBy
},
new SqlParameter("@param_pageNumber", SqlDbType.VarChar)
{
Value = start / length + 1
},
new SqlParameter("@param_pageSize", SqlDbType.Int)
{
Value = length
},
new SqlParameter("@param_isCount", SqlDbType.Int)
{
Value = 1
}
};
DataSet ds = Utility.SqlHelper.ExecProcFillDataSet("sp_GetPagingList", param);
paging.draw = draw;
paging.recordsTotal = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
paging.data = ds.Tables[0];
paging.recordsFiltered = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
return(JsonConvert.SerializeObject(paging));
}
catch (Exception ex)
{
paging.error = "获取用户类型列表失败:" + ex.Message;
paging.data = null;
return(JsonConvert.SerializeObject(paging));
}
}
// 获取文章列表
private string getArticleList()
{
pagingJson paging = new pagingJson();
try
{
//获取Datatables发送的参数 必要
int draw = Int32.Parse(Funcs.Get("draw"));//请求次数 这个值作者会直接返回给前台
//排序
string orderColumn = Funcs.Get("order[0][column]"); //那一列排序，从0开始
string orderDir = Funcs.Get("order[0][dir]"); //ase desc 升序或者降序
//搜索
string sCategory = Funcs.Get("sCategory"); //文章类型
string sTitle = GlobalObject.unescape(Funcs.Get("sTitle")); //标题
//分页
int start = Int32.Parse(Funcs.Get("start")); //第一条数据的起始位置
int length = Int32.Parse(Funcs.Get("length")); //每页显示条数
// where条件,排序条件
string strWhere = "status!=2", strOrderBy = "";
if (sCategory != null && sCategory != "")
{
strWhere += " and categoryId =" + sCategory;
}
if (sTitle != null && sTitle != "")
{
strWhere += " and title like '%" + Funcs.ToSqlString(sTitle) + "%'";
}
if (orderColumn != "" && orderDir != "")
{
strOrderBy = Funcs.Get("columns[" + orderColumn + "][data]") + " " + orderDir;
}
string strTB = "(select a.*,c.categoryName from tb_article a left join tb_category c on a.categoryId=c.categoryId)tt";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@param_table", SqlDbType.Text)
{
Value = strTB
},
new SqlParameter("@param_field", SqlDbType.VarChar)
{
Value = "*"
},
new SqlParameter("@param_where", SqlDbType.Text)
{
Value = strWhere
},
new SqlParameter("@param_groupBy", SqlDbType.VarChar)
{
Value = ""
},
new SqlParameter("@param_orderBy", SqlDbType.VarChar)
{
Value = strOrderBy
},
new SqlParameter("@param_pageNumber", SqlDbType.VarChar)
{
Value = start / length + 1
},
new SqlParameter("@param_pageSize", SqlDbType.Int)
{
Value = length
},
new SqlParameter("@param_isCount", SqlDbType.Int)
{
Value = 1
}
};
DataSet ds = Utility.SqlHelper.ExecProcFillDataSet("sp_GetPagingList", param);
paging.draw = draw;
paging.recordsTotal = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
paging.data = ds.Tables[0];
paging.recordsFiltered = Int32.Parse(ds.Tables[1].Rows[0][0].ToString());
return(DateTimeFormat(paging, "yyyy-MM-dd hh:mm"));
}
catch (Exception ex)
{
paging.error = "获取文章列表失败:" + ex.Message;
paging.data = null;
return(JsonConvert.SerializeObject(paging));
}
}
