public static tableData1 GetPageDate <T, TKey>(int pageIndex, int pageSize, Expression <Func <T, dynamic> > select, Expression <Func <T, bool> > where, Expression <Func <T, TKey> > order) where T : class
{
using (var db = new LayAdminEntities())
{
tableData1 data = new tableData1();
data.count = db.Set <T>().Where(where).Count();
data.data = db.Set <T>().Where(where).OrderByDescending(order).Select(select).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return(data);
}
}
public string Save(int pageID, int type, string key)
{
string modifyDataSource = string.Empty;
using (var db = new LayAdminEntities())
{
coreDevPage devPage = db.coreDevPage.Find(pageID);
modifyDataSource = string.IsNullOrEmpty(devPage.pageModifySource) ? devPage.pageDataSource : devPage.pageModifySource;
}
return(GridManager.Save(modifyDataSource, type, key));
}
public ActionResult RoleMenu()
{
using (var db = new LayAdminEntities())
{
List <coreRole> roleList = db.coreRole.Where <coreRole>(r => 1 == 1).ToList();
string option = "";
foreach (coreRole item in roleList)
{
option += string.Format("<option value = \"{0}\" > {1} </option >", item.RoleID, item.RoleDesc);
}
ViewBag.Roles = option;
return(View());
}
}
// GET: Home
public ActionResult Index()
{
string sessionID = (string)Session["CurrentSessionID"];
string roleID = Session["CurrentRole"].ToString();
ViewBag.Title = "LayAdmin1.0";
using (var db = new LayAdminEntities())
{
List <string> RoleMenus = db.coreRoleMenu.Where <coreRoleMenu>(r => r.RoleID == roleID).Select <coreRoleMenu, string>(r => r.MenuID).ToList();
List <coreMenu> menuList = db.coreMenu.Where <coreMenu>(r => RoleMenus.Contains(r.MenuID)).ToList();
ViewBag.MenuStr = GetMenu(menuList, null);
return(View());
}
}
public string Delete(int pageID, string key)
{
if (key.IndexOf("[") != 0)
{
key = "[" + key + "]";
}
string modifyDataSource = string.Empty;
using (var db = new LayAdminEntities())
{
coreDevPage devPage = db.coreDevPage.Find(pageID);
modifyDataSource = string.IsNullOrEmpty(devPage.pageModifySource) ? devPage.pageDataSource : devPage.pageModifySource;
}
return(GridManager.Delete(modifyDataSource, key));
}
/// <summary>
/// 根据角色ID获取菜单
/// </summary>
/// <param name="RoleIDs"></param>
public string GetMenuJson(List <string> RoleIDs)
{
using (var db = new LayAdminEntities())
{
JsonData data = new JsonData();
data.code = 0;
try
{
List <string> RoleMenus = db.coreRoleMenu.Where <coreRoleMenu>(r => RoleIDs.Contains(r.RoleID)).Select <coreRoleMenu, string>(r => r.MenuID).ToList();
List <coreMenu> menuList = db.coreMenu.Where <coreMenu>(r => 1 == 1).ToList();
data.data = GetMenu(menuList, RoleMenus, null);
}
catch (Exception ex)
{
data.msg = ex.Message;
}
return(JsonConvert.SerializeObject(data));
}
}
public string UserLogin(string userid, string password)
{
JsonData data = new JsonData();
using (var db = new LayAdminEntities())
{
List <coreUser> user = db.coreUser.Where <coreUser>(r => r.USER_ID == userid && r.USER_PASSWORD == password).ToList();
if (user.Count <= 0)
{
data.code = 1;
data.msg = "用户名或密码错误";
}
else
{
Session["CurrentUserID"] = userid.ToUpper();
Session["CurrentRole"] = user[0].USER_ROLE;
Session["CurrentSessionID"] = Session.SessionID;
//Redirect("/Home/Index");
}
return(JsonConvert.SerializeObject(data));
}
}
/// <summary>
/// 获取table数据
/// </summary>
/// <param name="PageID">页面编号</param>
/// <param name="pageIndex">页面索引</param>
/// <param name="limit">每页记录数</param>
/// <param name="key">查询条件(Json)</param>
/// <returns></returns>
public static string GetPageData(int PageID, int pageIndex, int limit, string key)
{
tableData data = new tableData();
string whereStr = "1=1";
string fields = "*";
string orderBy = "";
try
{
List <SqlParameter> sqlParams = new List <SqlParameter>();
using (var db = new LayAdminEntities())
{
coreDevPage devPage = db.coreDevPage.Find(PageID);
if (devPage == null)
{
throw new Exception("页面编号不正确");
}
//取表或试图对应的字段
DataTable table = SqlHelper.ExecuteDataset(DBConfig.DBConnString, CommandType.Text, "select lower(col.name) as FieldName, tp.name FieldType from sys.columns col left join dbo.systypes tp on col.user_type_id = tp.xusertype where object_id in(object_id('" + devPage.pageDataSource + "'))").Tables[0];
if (table == null || table.Rows.Count <= 0)
{
throw new Exception("表或试图不存在字段");
}
//拼接where条件
if (string.IsNullOrEmpty(devPage.pageWhereExpress) == false)
{
whereStr += " and " + devPage.pageWhereExpress;
}
if (key != null)
{
JObject jObj = JsonConvert.DeserializeObject(key) as JObject;
foreach (var item in jObj)
{
if (string.IsNullOrEmpty(item.Value.ToString()) == false)
{
var d = from r in table.AsEnumerable()
where r.Field <string>("FieldName") == item.Key.ToString().ToLower()
select r.Field <string>("FieldType");
if (d.FirstOrDefault().ToString() == "datetime")
{
whereStr += string.Format(" and {0}>=@{1}", item.Key.ToString(), "BEGIN" + item.Key.ToString());
whereStr += string.Format(" and {0}<@{1}", item.Key.ToString(), "END" + item.Key.ToString());
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + "BEGIN" + item.Key.ToString();
param.SqlDbType = GetSqlDbType(d.FirstOrDefault().ToString());
param.Value = item.Value.ToString();
sqlParams.Add(param);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@" + "END" + item.Key.ToString();
param1.SqlDbType = GetSqlDbType(d.FirstOrDefault().ToString());
param1.Value = DateTime.Parse(item.Value.ToString()).AddDays(1);
sqlParams.Add(param1);
}
else
{
whereStr += string.Format(" and {0}=@{1}", item.Key.ToString(), item.Key.ToString());
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + item.Key.ToString();
param.SqlDbType = GetSqlDbType(d.FirstOrDefault().ToString());
param.Value = item.Value.ToString();
sqlParams.Add(param);
}
}
}
}
//获取查询字段
if (string.IsNullOrEmpty(devPage.pageQueryFields) == false)
{
fields = devPage.pageQueryFields;
}
//获取排序字段，若无排序，则取表的第一个字段倒序
if (string.IsNullOrEmpty(devPage.pageOrderBy) == false)
{
orderBy = devPage.pageOrderBy;
}
else
{
orderBy = table.Rows[0][0].ToString() + " desc";
}
data.count = (int)SqlHelper.ExecuteScalar(DBConfig.DBConnString, CommandType.Text, "select count(1) from " + devPage.pageDataSource + " where " + whereStr + "", sqlParams.ToArray());
DataSet ds = SqlHelper.ExecuteDataset(DBConfig.DBConnString, CommandType.Text, string.Format("SELECT " + fields + " FROM " + devPage.pageDataSource + " where " + whereStr + " ORDER BY " + orderBy + " OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY ", (pageIndex - 1) * limit, limit == 0? data.count : limit), sqlParams.ToArray());
data.data = ds.Tables[0];
}
}
catch (Exception ex)
{
data.code = 1;
data.msg = ex.Message;
}
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
return(JsonConvert.SerializeObject(data, timeFormat));
}
