/// <summary>
/// 生成拼接sql参数列表
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List <SqlParameter> ParseToSqlParameters(FunctionSearchEntity entity)
{
List <SqlParameter> paraList = new List <SqlParameter>();
//table
paraList.Add(CPTable(entity));
//fields
paraList.Add(CPFields(entity));
//filter_SqlWhere
paraList.Add(CPWhere(entity));
//order
paraList.Add(CPOrder(entity));
//pagesize
paraList.Add(new SqlParameter("@pageSize", entity.PageSize));
//pageindex
paraList.Add(new SqlParameter("@pageIndex", entity.PageIndex));
paraList.Add(new SqlParameter()
{
ParameterName = "@Records", Value = 0, Direction = ParameterDirection.Output
});
return(paraList);
}
public DataTable QueryFunctionTable(FunctionSearchEntity entity, out int totalcnt)
{
totalcnt = 0;
DataTable table = null;
try
{
SqlParameter[] prms = ParseToSqlParameters(entity).ToArray();
if (entity.UseDBPagination)
{
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
}
else
{
throw new NotImplementedException();
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(null);
}
return(table);
}
/// <summary>
/// 设置表关联
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPTable(FunctionSearchEntity entity)
{
StringBuilder sbtable = new StringBuilder();
//基本表
sbtable.Append(" M_System_Function ");
return(new SqlParameter("@TableName", sbtable.ToString()));
}
/// <summary>
/// 获取分组后的功能菜单
/// </summary>
/// <returns></returns>
public List <FunctionEntity> QueryFunctionListByGroup(FunctionSearchEntity entity)
{
List <FunctionEntity> list = this.QueryFunctionList();
List <FunctionEntity> resultList = null;
if (list != null && list.Count > 0)
{
if (!string.IsNullOrEmpty(entity.Function_Name))
{
resultList = list.Where(item => item.Function_Level == 1 && item.Function_ParentID == 0 && item.Function_Name.Contains(entity.Function_Name)).ToList();
}
else
{
resultList = list.Where(item => item.Function_Level == 1 && item.Function_ParentID == 0).ToList();
}
foreach (FunctionEntity item in resultList)
{
List <FunctionEntity> childList = list.Where(child => child.Function_Level == 2 && child.Function_ParentID == item.Function_ID).ToList();
item.ChildFunctionList = childList;
}
}
#region 作废
//if (table != null && table.Rows.Count > 0)
//{
// list = table.AsEnumerable().Where(row => row.Field<int>("Function_Level") == 1 && row.Field<int>("Function_ParentID") == 0)
// .Select(row => new FunctionEntity
// {
// Function_Level = row.Field<int>("Function_Level"),
// Function_Name = row.Field<string>("Function_Name"),
// Function_ID = row.Field<int>("Function_ID"),
// Function_URL_New = row.Field<string>("Function_URL_New"),
// CreateDate = row.Field<DateTime>("CreateDate")
// }).ToList();
// foreach (FunctionEntity funentity in list)
// {
// List<FunctionEntity> childFunList = table.AsEnumerable().Where(row => row.Field<int>("Function_Level") == 2 && row.Field<int>("Function_ParentID") == funentity.Function_ID)
// .OrderBy(row => row.Field<int>("Function_Order"))
// .Select(row => new FunctionEntity
// {
// Function_Level = row.Field<int>("Function_Level"),
// Function_Name = row.Field<string>("Function_Name"),
// Function_ID = row.Field<int>("Function_ID"),
// Function_URL_New = row.Field<string>("Function_URL_New"),
// CreateDate = row.Field<DateTime>("CreateDate")
// }).ToList();
// funentity.ChildFunctionList = childFunList;
// }
//}
#endregion
return(resultList);
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPWhere(FunctionSearchEntity entity)
{
StringBuilder sbwhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(entity.Function_Name))
{
sbwhere.Append(" AND Function_Name like '" + entity.Function_Name + "%'");
}
return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
}
private List <FunctionEntity> GetQueryData()
{
BCtrl_Function bll = new BCtrl_Function();
FunctionSearchEntity entity = new FunctionSearchEntity();
entity.Function_Name = _StrFunctionName;
entity.UseDBPagination = false;
return(bll.QueryFunctionListByGroup(entity));
}
/// <summary>
/// 设置显示字段
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPFields(FunctionSearchEntity entity)
{
StringBuilder sbfileds = new StringBuilder();
if (entity.UseDBPagination)
{
sbfileds.Append(@" Function_ID, Function_Name, Function_URL_New, Function_ParentID, Function_Order,Function_isValid,Function_Level,Function_IsNew,CONVERT(NVARCHAR(19),CreateDate,121) AS CreateDate ");
}
else
{
throw new NotImplementedException();
}
return(new SqlParameter("@Fields", sbfileds.ToString()));
}
/// <summary>
/// 设置排序
/// </summary>
/// <param name="enity"></param>
/// <returns></returns>
private SqlParameter CPOrder(FunctionSearchEntity enity)
{
StringBuilder sborder = new StringBuilder();
if (enity.OrderfieldType == OrderFieldType.Desc)
{
sborder.Append(" Function_ID DESC ");
}
else
{
sborder.Append(" Function_ID ASC ");
}
return(new SqlParameter("@OrderField", sborder.ToString()));
}
private DataTable GetQueryData(bool isDownload)
{
BCtrl_Function bll = new BCtrl_Function();
int totalcnt = 0;
FunctionSearchEntity entity = new FunctionSearchEntity();
entity.Function_Name = _StrFunctionName;
entity.PageSize = base.PageSize;
entity.PageIndex = base.PageIndex;
entity.UseDBPagination = !isDownload;
DataTable table = bll.QueryFunctionTable(entity, out totalcnt);
base.TotalRecords = totalcnt;
return(table);
}
public DataTable QueryFunctionTable(FunctionSearchEntity entity, out int totalcnt)
{
return(functiondao.QueryFunctionTable(entity, out totalcnt));
}
