public JsonResult List(FlexigridParam p)
{
var json = new JsonResult();
var filtered =
from r in _person.All
select r;
json.Data = new
{
page = p.page,
total = filtered.Count(),
rows =
filtered
.OrderBy(x => x.Username)
.Skip((p.page - 1) * p.rp).Take(p.rp)
.ToList()
.Select(x =>
new
{
id = x.PersonId.ToString(),
cell = new string[] { x.Username, x.Firstname, x.Lastname, x.FavoriteNumber.ToString(), x.Country.CountryName, Convert.ToBase64String(x.RowVersion) }
})
};
return(json);
}//List
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <YiDuiDuoZhongDeDuo> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
IQueryable <YiDuiDuoZhongDeDuo> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
if (param.Cols.Contains("MyCaiGouXuQiouJiHuaIdOld") && item.MyCaiGouXuQiouJiHuaId != null && item.YiDuiDuoZhongDeYi != null)
{
item.MyCaiGouXuQiouJiHuaIdOld = item.YiDuiDuoZhongDeYi.MingCheng; //可以做成超链接
}
if (param.Cols.Contains("JiLiangDanWei") && !string.IsNullOrWhiteSpace(item.JiLiangDanWei))
{
item.JiLiangDanWei = db.SysField.Where(s => s.Id == item.JiLiangDanWei).Select(s => s.MyTexts).FirstOrDefault();
}
}
}
return(queryData);
}
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <YiDuiDuoZhongDeYi> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
IQueryable <YiDuiDuoZhongDeYi> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
if (param.Cols.Contains("RenWuShuXing") && !string.IsNullOrWhiteSpace(item.RenWuShuXing))
{
item.RenWuShuXing = db.SysField.Where(s => s.Id == item.RenWuShuXing).Select(s => s.MyTexts).FirstOrDefault();
}
if (param.Cols.Contains("CaiGouLeiXing") && !string.IsNullOrWhiteSpace(item.CaiGouLeiXing))
{
item.CaiGouLeiXing = db.SysField.Where(s => s.Id == item.CaiGouLeiXing).Select(s => s.MyTexts).FirstOrDefault();
}
}
}
return(queryData);
}
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <food_list> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
IQueryable <food_list> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
}
}
return(queryData);
}
public JsonResult FlexigridList(string id, FlexigridParam param)
{
addUserFilter(param);
return(Json(m_BLL.FlexigridList(id, param)));
}
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <SysDepartment> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
IQueryable <SysDepartment> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
if (param.Cols.Contains("MyParentId") && !string.IsNullOrWhiteSpace(item.MyParentId) && item.SysDepartment2 != null)
{
item.MyParentId = item.SysDepartment2.Name; //可以做成超链接
}
}
}
return(queryData);
}
public ActionResult GetDaoChu(string id, FlexigridParam param)
{
var query = m_BLL.DaoChu(id, param).ToArray();
var fileName = WriteExcle(param, query, 1);
return(Content(fileName));
}
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <tasktype> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
IQueryable <tasktype> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
if (param.Cols.Contains("useridOld") && item.userid != null && item.SysPerson != null)
{
item.useridOld = item.SysPerson.Name; //可以做成超链接
}
}
}
return(queryData);
}
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <SysPerson> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
IQueryable <SysPerson> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
if (param.Cols.Contains("SysDepartmentIdOld") && item.SysDepartmentId != null && item.SysDepartment != null)
{
item.SysDepartmentIdOld = item.SysDepartment.Name; //可以做成超链接
}
if (param.Cols.Contains("Province") && !string.IsNullOrWhiteSpace(item.Province))
{
item.Province = db.SysField.Where(s => s.Id == item.Province).Select(s => s.MyTexts).FirstOrDefault();
}
if (param.Cols.Contains("City") && !string.IsNullOrWhiteSpace(item.City))
{
item.City = db.SysField.Where(s => s.Id == item.City).Select(s => s.MyTexts).FirstOrDefault();
}
if (param.Cols.Contains("Village") && !string.IsNullOrWhiteSpace(item.Village))
{
item.Village = db.SysField.Where(s => s.Id == item.Village).Select(s => s.MyTexts).FirstOrDefault();
}
if (param.Cols.Contains("State") && !string.IsNullOrWhiteSpace(item.State))
{
item.State = db.SysField.Where(s => s.Id == item.State).Select(s => s.MyTexts).FirstOrDefault();
}
if (param.Cols.Contains("SysRoleId") && item.SysRole != null)
{
item.SysRoleId = string.Empty;
foreach (var it in item.SysRole)
{
item.SysRoleId += it.Name + ' ';
}
}
}
}
return(queryData);
}
public string WriteExcle(FlexigridParam param, dynamic[] query, int from = 1)
{
var names = param.QType.GetString().Split(',');
var cols = param.Cols.GetString().Split(',');
C1XLBook _book = new C1XLBook();
string path = @"~/Files/a.xls";
string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path);
_book.Load(xlsPath);
string guid = Guid.NewGuid().ToString();
string saveFileName = xlsPath.Path(guid);
XLSheet sheet = _book.Sheets[0];
XLCell cell = sheet[0, 0];
Dictionary <string, string> propertyName;
PropertyInfo[] properties;
for (int i = 0; i < names.Length; i++)
{
cell = sheet[0, i];
if (!string.IsNullOrWhiteSpace(names[i]))
{
cell.Value = names[i]; //列值
}
}
for (int i = 0; i < query.Length; i++)
{
propertyName = new Dictionary <string, string>();
if (query[i] == null)
{
continue;
}
Type type = query[i].GetType();
properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo property in properties)
{
object o = property.GetValue(query[i], null);
if (!string.IsNullOrEmpty(property.Name) && o != null)
{
propertyName.Add(property.Name, o.ToString());
}
}
int j = 0;
cols.All(a =>
{
cell = sheet[i + from, j];
if (propertyName.ContainsKey(a)) //列名
{
cell.Value = propertyName[a]; //列值
}
j++;
return(true);
});
}
_book.Save(saveFileName);
LogClassModels.WriteServiceLog(LogType.Operation, string.Format("../../../Files/{0}.xls", guid) + "，", "导出数据",
Result.Succeed); //写入日志
return(string.Format("../../../Files/{0}.xls", guid)); //path.Replace("b",guid);
}
/// <summary>
/// Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
/// <param name="id">额外的参数</param>
/// <returns></returns>
public FlexigridObject FlexigridList(string id, FlexigridParam param)
{
int count = 0;
IQueryable <SysDepartment> queryData = GetByParam(id, param, ref count);
FlexigridObject flexigridObject = new FlexigridObject()
{
FlexigridPara = param,
total = (count > 0) ? count : 0,
Query = queryData
};
return(flexigridObject);
}
public void addUserFilter(FlexigridParam param)
{
#region 添加用户id过滤
if (GetCurrentPerson().ToLower() != "admin")
{
if (param.Query == null || param.Query == "")
{
param.Query = "userid&" + GetCurrentPerson();
}
else
{
param.Query = param.Query + "^userid&" + GetCurrentPerson();
}
}
#endregion
}
/// <summary>
/// 查询的数据
/// </summary>
/// <param name="id">额外的参数</param>
/// <param name="param">客户端传进的数据</param>
/// <param name="count">结果集的总数</param>
/// <returns></returns>
public IQueryable <SysRole> GetByParam(string id, FlexigridParam param, ref int count)
{
int page = param.Page.GetInt();
int rp = param.RP.GetInt();
if (!string.IsNullOrWhiteSpace(id))
{
param.Query += "SysMenu&" + id + "^";
}
IQueryable <SysRole> queryData = repository.DaoChuData(db, param);
count = queryData.Count();
if (count > 0)
{
if (page <= 1)
{
queryData = queryData.Take(rp);
}
else
{
queryData = queryData.Skip((page - 1) * rp).Take(rp);
}
foreach (var item in queryData)
{
if (param.Cols.Contains("SysPersonId") && item.SysPerson != null)
{
item.SysPersonId = string.Empty;
foreach (var it in item.SysPerson)
{
item.SysPersonId += it.Name + ' ';
}
}
if (param.Cols.Contains("SysMenuId") && item.SysMenu != null)
{
item.SysMenuId = string.Empty;
foreach (var it in item.SysMenu)
{
item.SysMenuId += it.Name + ' ';
}
}
}
}
return(queryData);
}
/// <summary>
/// 在查询中输入字符串，自动提示的功能
/// </summary>
/// <param name="id">需要查询的数据库字段的名称</param>
/// <param name="term">输入的字符串</param>
/// <returns></returns>
public string SearchAutoComplete(string id, string term)
{
FlexigridParam param = new FlexigridParam();
param.Query = id + "&" + term + "^";
param.SortName = id;
List <string> listString = new List <string>();
using (SysEntities db = new SysEntities())
{
var queryData = repository.DaoChuData(db, param);
if (queryData != null)
{
queryData = queryData.Take(5).Distinct();
foreach (var item in queryData)
{
listString.Add(FlexigridRow.GetPropertyList(item, new string[] { id }).FirstOrDefault());
}
}
}
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("[");
int i = 0;
foreach (var item in listString)
{
if (string.IsNullOrEmpty(item))
{
continue;
}
if (i == 0)
{
i++;
stringBuilder.Append(@"{@label@:@" + item + "@}");
}
else
{
stringBuilder.Append(",");
stringBuilder.Append(@"{@label@:@" + item + "@}");
}
}
stringBuilder.Append("]");
return(stringBuilder.ToString().Replace('@', '"'));
}
/// <summary>
/// 导出Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
/// <param name="id">额外的参数</param>
/// <returns></returns>
public SysException[] DaoChu(string id, FlexigridParam param)
{
return(repository.DaoChuData(db, param).ToArray());
}
/// <summary>
/// 导出Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
// <param name="listQuery">额外的参数</param>
/// <returns></returns>
public IQueryable <SysMenu> DaoChuData(SysEntities db, FlexigridParam param, params object[] listQuery)
{
string where = string.Empty;
int flagWhere = 0;
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(param.Query.GetString());
if (queryDic != null && queryDic.Count > 0)
{
foreach (var item in queryDic)
{
if (flagWhere != 0)
{
where += " and ";
}
flagWhere++;
//if (false)//个性化的东西在循环内部排添加
//{//必须有where条件
// if (queryDic.ContainsKey("Name") && queryDic.ContainsValue("Clark")) { }//需要查询的列名
// where += "it." + item.Key + " like '%" + item.Value + "%'";
// continue;
//}
if (queryDic.ContainsKey("ParentId") && !string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Value == "noway" && item.Key == "ParentId")
{//查询一对多关系的列名
where += "it.ParentId is null";
continue;
}
if (queryDic.ContainsKey("SysRole") && !string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key == "SysRole")
{//查询多对多关系的列名
where += "EXISTS(select p from it.SysRole as p where p.Id='" + item.Value + "')";
continue;
}
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Contains(Start_Time)) //需要查询的列名
{
where += "it. " + item.Key.Remove(item.Key.IndexOf(Start_Time)) + " >= CAST('" + item.Value + "' as System.DateTime)";
continue;
}
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Contains(End_Time)) //需要查询的列名
{
where += "it." + item.Key.Remove(item.Key.IndexOf(End_Time)) + " < CAST('" + Convert.ToDateTime(item.Value).AddDays(1) + "' as System.DateTime)";
continue;
}
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Contains(Start_Int)) //需要查询的列名
{
where += "it." + item.Key.Remove(item.Key.IndexOf(Start_Int)) + " >= " + item.Value.GetInt();
continue;
}
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Contains(End_Int)) //需要查询的列名
{
where += "it." + item.Key.Remove(item.Key.IndexOf(End_Int)) + " <= " + item.Value.GetInt();
continue;
}
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Contains(End_String)) //需要查询的列名
{
where += "it." + item.Key.Remove(item.Key.IndexOf(End_String)) + " = '" + item.Value + "'";
continue;
}
if (!string.IsNullOrWhiteSpace(item.Key) && !string.IsNullOrWhiteSpace(item.Value) && item.Key.Contains(DDL_String)) //需要查询的列名
{
where += "it." + item.Key.Remove(item.Key.IndexOf(DDL_String)) + " = '" + item.Value + "'";
continue;
}
where += "it." + item.Key + " like '%" + item.Value + "%'";
}
}
return(db.SysMenu
.Where(string.IsNullOrEmpty(where) ? "true" : where)
.OrderBy("it." + param.SortName.GetString() + " " + param.SortOrder.GetString())
.AsQueryable());
}
public JsonResult FlexigridList(string id, FlexigridParam param)
{
return(Json(m_BLL.FlexigridList(id, param)));
}
/// <summary>
/// 导出Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
/// <param name="id">额外的参数</param>
/// <returns></returns>
public YiDuiDuoZhongDeYi[] DaoChu(string id, FlexigridParam param)
{
return(repository.DaoChuData(db, param).ToArray());
}
/// <summary>
/// 导出Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
/// <param name="id">额外的参数</param>
/// <returns></returns>
public SysDepartment[] DaoChu(string id, FlexigridParam param)
{
return(repository.DaoChuData(db, param).ToArray());
}
/// <summary>
/// 导出Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
/// <param name="id">额外的参数</param>
/// <returns></returns>
public food_list[] DaoChu(string id, FlexigridParam param)
{
return(repository.DaoChuData(db, param).ToArray());
}
/// <summary>
/// 导出Flexigrid中查询的数据
/// </summary>
/// <param name="param">客户端传进的数据</param>
/// <param name="id">额外的参数</param>
/// <returns></returns>
public tasktype[] DaoChu(string id, FlexigridParam param)
{
return(repository.DaoChuData(db, param).ToArray());
}
