/// <summary>
/// 返回FromSection
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public FromSection <T> GetFromSection(WhereClip where, OrderByClip order)
{
if (_trans != null)
{
return(_trans.From <T>().Where(where).OrderBy(order));
}
return(DBSession.CurrentSession.From <T>().Where(where).OrderBy(order));
}
/// <summary>
/// 分页获取获取员工信息表datatable
/// </summary>
/// <param name="pageindex">当前页数</param>
/// <param name="pagesize">每页显示条数</param>
/// <param name="orderby">排序方式</param>
/// <param name="pageCount">总页数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public DataTable GetDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
return(Dal.From <PersonInfo>().Join <DepartAndPerson>(PersonInfo._.ID == DepartAndPerson._.UserID, JoinType.leftJoin)
.Join <AdministrativeRegions>(DepartAndPerson._.DepartID == AdministrativeRegions._.ID && DepartAndPerson._.IsDefault == true, JoinType.leftJoin)
.Select(PersonInfo._.ID.All, AdministrativeRegions._.Name.Alias("DepartName"), new ExpressionClip("case MarryStatus when 0 then '外部' else '内部' end NWName"))
.Where(where).OrderBy(orderby).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount));
}
public static List <T> GetAll(OrderByClip orderBy = null)
{
var fs = Db.Context.From <T>();
if (orderBy != null)
{
fs.OrderBy(orderBy);
}
return(fs.ToList());
}
/// <summary>
/// 分页获取获取工作信息表datatable
/// </summary>
/// <param name="pageindex">当前页数</param>
/// <param name="pagesize">每页显示条数</param>
/// <param name="orderby">排序方式</param>
/// <param name="pageCount">总页数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public DataTable GetDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
WhereClip appendwhere = new WhereClip();
appendwhere.Append(" (HandSequence is null or HandSequence= (select max(HandSequence) from WorkHandLog where [WorkID]=[WorkInfo].[ID]))");
return(Dal.From <WorkInfo>().Join <ShebeiInfo>(ShebeiInfo._.ID == WorkInfo._.SbID)
.Join <WorkHandLog>(WorkInfo._.ID == WorkHandLog._.WorkID && appendwhere, JoinType.leftJoin)
.Select(WorkInfo._.ID.All, WorkHandLog._.HandResult, WorkHandLog._.HandDate, ShebeiInfo._.Code, ShebeiInfo._.Name, ShebeiInfo._.GuiGe, WorkHandLog._.ChuliYj)
.Where(where).OrderBy(orderby).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount));
}
public virtual string LambdaSelect(LambdaQueryHelper selectHelper, ref Dictionary <string, Parameter> parameters, int?pageIndex, int?pageSize, bool loadOrderby = true)
{
{
string topSql = this.Configuration.Dialect.GetTopString(Convert.ToInt32(pageIndex), Convert.ToInt32(pageSize));
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
if (!string.IsNullOrEmpty(selectHelper.DistinctString))
{
sql.Append(selectHelper.DistinctString);
sql.Append(" ");
}
sql.Append(topSql);
sql.Append(BuildSelectColumns(selectHelper.ClassMap, selectHelper.Fields, selectHelper.Joins));
sql.Append(" FROM ");
sql.Append(BuildFrom(selectHelper.ClassMap, selectHelper.Joins, selectHelper.EnabledNoLock));
sql.Append(" ");
if (!WhereClip.IsNullOrEmpty(selectHelper.WhereClip))
{
sql.Append(selectHelper.WhereClip.WhereString);
}
if (!GroupByClip.IsNullOrEmpty(selectHelper.GroupByClip))
{
sql.Append(selectHelper.GroupByClip.GroupByString);
if (!WhereClip.IsNullOrEmpty(selectHelper.HavingClip))
{
sql.Append(" HAVING ");
sql.Append(selectHelper.HavingClip.ToString());
}
}
if (loadOrderby && !OrderByClip.IsNullOrEmpty(selectHelper.OrderByClip))
{
sql.Append(selectHelper.OrderByClip.OrderByString);
sql.Append(" ");
}
if (pageIndex != null && pageSize != null && string.IsNullOrEmpty(topSql))
{
Dictionary <string, object> pageParameters = new Dictionary <string, object>();
string pageSql = this.Configuration.Dialect.GetPagingSql(sql.ToString(), Convert.ToInt32(pageIndex), Convert.ToInt32(pageSize), pageParameters);
foreach (var item in pageParameters)
{
if (!parameters.ContainsKey(item.Key))
{
parameters.Add(item.Key, new Parameter(item.Key, item.Value));
}
}
return(pageSql);
}
return(sql.ToString());
}
}
public void TestEntityQueryBasicUsage()
{
WhereClip where = (!!(UserGroup._.ID > 0)) & (!(UserGroup._.Name == "teddy"));
OrderByClip orderBy = UserGroup._.ID.Desc & UserGroup._.Name.Asc;
Console.WriteLine(where.ToString());
Console.WriteLine(orderBy.ToString());
Console.WriteLine(PropertyItem.ParseExpressionByMetaData(where.ToString(), new PropertyToColumnMapHandler(SimplePropertyToColumn), "[", "]", "@"));
Console.WriteLine(PropertyItem.ParseExpressionByMetaData(orderBy.ToString(), new PropertyToColumnMapHandler(SimplePropertyToColumn), "[", "]", "@"));
Console.WriteLine(UserGroup._.ID.In(1, 2, 3));
Console.WriteLine(UserGroup._.ID.Between(2, 6));
}
/// <summary>
/// 通用查询
/// </summary>
public static List <T> Query(Where <T> where, OrderByClip orderBy = null, string ascOrDesc = "asc", int?pageSize = null, int?pageIndex = null)
{
var fs = Db.Context.From <T>().Where(where);
if (pageIndex != null && pageSize != null)
{
fs.Page(pageSize.Value, pageIndex.Value);
}
if (orderBy != null)
{
fs.OrderBy(orderBy);
}
return(fs.ToList());
}
public EntityType[] Filter(Rock.Orm.Common.WhereClip where, OrderByClip orderBy, int topCount, int skipCount)
{
if (filter == null)
{
lock (this)
{
if (filter == null)
{
filter = new EntityArrayQuery <EntityType>(this.ToArray());
}
}
}
return(filter.FindArray(where, orderBy, topCount, skipCount));
}
/// <summary>
/// 得到实体类
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public T GetModel(WhereClip where, OrderByClip order)
{
T t = default(T);
if (_trans != null)
{
t = _trans.From <T>().Where(where).OrderBy(order).ToFirst();
}
else
{
t = DBSession.CurrentSession.From <T>().Where(where).OrderBy(order).ToFirst();
}
GetRelations(ref t);
return(t);
}
/// <summary>
/// 根据条件获取待办任务
/// </summary>
/// <param name="p"></param>
/// <param name="pageSize"></param>
/// <param name="where"></param>
/// <param name="orderByClip"></param>
/// <param name="count"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetDaiBanDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
WhereClip appendwhere = new WhereClip();
appendwhere.Append(" ( HandSequence= (select max(HandSequence) from WorkHandLog where [WorkID]=[WorkInfo].[ID]))");
return Dal.From<WorkHandLog>().Join<WorkInfo>(WorkInfo._.ID == WorkHandLog._.WorkID && appendwhere, JoinType.leftJoin)
.Join<ShebeiInfo>(ShebeiInfo._.ID == WorkInfo._.SbID, JoinType.leftJoin)
.Where(where && ShebeiInfo._.State != "正常").OrderBy(orderby)
.Select(WorkHandLog._.ID.All,
WorkInfo._.SbID, WorkInfo._.Address, WorkInfo._.ChuLiYiJian
, WorkInfo._.CreaterName, WorkInfo._.CurrentUser, WorkInfo._.Guzhang, WorkInfo._.GuZhangXx
, WorkInfo._.Note, WorkInfo._.PlanTime, WorkInfo._.RealTime, WorkInfo._.Status
, WorkInfo._.Tel, WorkInfo._.City, WorkInfo._.Xian, WorkInfo._.Zhen
, ShebeiInfo._.Code, ShebeiInfo._.Name, ShebeiInfo._.GuiGe, ShebeiInfo._.Note.Alias("SheBeiNote"))
.ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount);
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
if (startIndex == 1)
{
return(base.CreatePageFromSection(fromSection, startIndex, endIndex));
}
if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip))
{
foreach (Field f in fromSection.Fields)
{
if (!f.PropertyName.Equals("*"))
{
fromSection.OrderBy(f.Asc);
break;
}
}
}
Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!");
if (fromSection.Fields.Count == 0)
{
fromSection.Select(Field.All);
}
fromSection.AddSelect(new Field(string.Concat("row_number() over(", fromSection.OrderByString, ") AS tmp_rowid")));
//OrderByClip tempOrderBy = fromSection.OrderByClip;
fromSection.OrderBy(OrderByClip.None);
fromSection.TableName = string.Concat("(", fromSection.SqlString, ") AS tmp_table");
fromSection.Parameters = fromSection.Parameters;
fromSection.DistinctString = string.Empty;
fromSection.PrefixString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
//fromSection.OrderBy(tempOrderBy);
fromSection.Where(new WhereClip(string.Concat("tmp_rowid BETWEEN ", startIndex.ToString(), " AND ", endIndex.ToString())));
return(fromSection);
}
public PageList <WF_AuditRecordResult> GetAuditRecList(WF_AuditRecordParam param)
{
this.CheckSession();
PageList <WF_AuditRecordResult> ret = new PageList <WF_AuditRecordResult>();
WhereClip whereClip = WF_AuditRecord._.IsDeleted == false && WF_AuditRecord._.GCompanyID == this.SessionInfo.CompanyID;
OrderByClip orderBy = WF_AuditRecord._.SourceBillGuid.Asc && WF_AuditRecord._.CreatedTime.Asc && WF_AuditRecord._.SeqNo.Asc;
if (param.SourceTableEngName.ToStringHasNull().Trim() != "")
{
whereClip = whereClip && WF_AuditRecord._.SourceTableEngName == param.SourceTableEngName;
}
if (param.SourceBillGuid != null)
{
whereClip = whereClip && WF_AuditRecord._.SourceBillGuid == param.SourceBillGuid;
}
ret = this.SelectList <WF_AuditRecordResult>(param.PageIndex.ToInt32(), param.PageSize.ToInt32(), whereClip, orderBy);
return(ret);
}
protected void Page_Load(object sender, EventArgs e)
{
string desc_tmpl = "报工设备：{0} 报工时间：{1} <br/><br/>处理意见：{2}";
if (!IsPostBack)
{
WorkInfoManager wkMgr = new WorkInfoManager();
string id = Request.QueryString.Get("id");
WhereClip where = WorkInfo._.ID == new Guid(id);
OrderByClip order = WorkInfo._.CreateDate.Desc;
int count = 0;
DataTable dtwk = wkMgr.GetDataTable(1, 1, where, order, ref count, ref count);
news_title.InnerHtml = "报修设备：" + dtwk.Rows[0]["Name"].ToString();
//news_desc.InnerHtml = string.Format(desc_tmpl, dtwk.Rows[0]["Name"], dtwk.Rows[0]["GuZhangXx"], dtwk.Rows[0]["ChuLiYiJian"]);
news_content.InnerHtml = string.Format(desc_tmpl, dtwk.Rows[0]["Name"], dtwk.Rows[0]["GuZhangXx"], dtwk.Rows[0]["ChuLiYiJian"]);;
}
}
public Product[] ProductCount(int orgid, int?colid, int count, bool?isDel, bool?isUse, string type)
{
WhereClip wc = new WhereClip();
if (orgid > 0)
{
wc.And(Product._.Org_ID == orgid);
}
if (colid != null && colid > 0)
{
wc.And(Product._.Col_Id == colid);
}
if (isDel != null)
{
wc.And(Product._.Pd_IsDel == isDel);
}
if (isUse != null)
{
wc.And(Product._.Pd_IsUse == isUse);
}
type = type.ToLower();
//排序方式
OrderByClip order = Product._.Pd_Tax.Asc;
if (type == "new")
{
order = Product._.Pd_IsNew.Asc;
wc.And(Product._.Pd_IsNew == true);
}
if (type == "hot")
{
order = Product._.Pd_Number.Desc;
}
if (type == "rec")
{
order = Product._.Pd_IsRec.Asc;
}
if (type == "flux")
{
order = Product._.Pd_Number.Desc;
}
//取产品数量
return(Gateway.Default.From <Product>().Where(wc).OrderBy(order && Product._.Pd_PushTime.Desc && Product._.Pd_Tax.Asc).ToArray <Product>(count));
}
public async Task <JsonResult> GetPages(PageParm parm)
{
var where = new Where <PayMch>();
where.And(d => 1 == 1);
string mch_id = Request.Query["mch_id"].FirstOrDefault();
string mch_name = Request.Query["mch_name"].FirstOrDefault();
string plat_id = Request.Query["plat_id"].FirstOrDefault();
string field = Request.Query["field"].FirstOrDefault();
string order = Request.Query["order"].FirstOrDefault();
if (!string.IsNullOrEmpty(mch_id))
{
where.And(d => d.Mch_id == mch_id);
}
if (!string.IsNullOrEmpty(mch_name))
{
where.And(d => d.Mch_name.Like(mch_name.SqlFilters()));
}
if (!string.IsNullOrEmpty(plat_id) && plat_id != "0")
{
where.And(d => d.Plat_id == Convert.ToInt32(plat_id));
}
parm.whereClip = where;
OrderByClip orderClip = new OrderByClip("id", OrderByOperater.DESC);
if (!string.IsNullOrEmpty(field))
{
if (order.ToLower() == "asc")
{
orderClip = new OrderByClip(field.SqlFilters(), OrderByOperater.ASC);
}
else
{
orderClip = new OrderByClip(field.SqlFilters(), OrderByOperater.DESC);
}
}
parm.orderByClip = orderClip;
var res = await PayMchBll._.GetPagesAsync(parm);
return(Json(new { code = 0, msg = "success", count = res.data.TotalItems, data = res.data.Items }));
}
protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments)
{
NBearDataSourceSelectingEventArgs selectingArgs = new NBearDataSourceSelectingEventArgs(arguments);
owner.OnSelecting(selectingArgs);
arguments = selectingArgs.SelectArguments == null ? DataSourceSelectArguments.Empty : selectingArgs.SelectArguments;
arguments.RaiseUnsupportedCapabilitiesError(this);
Gateway gateway = owner.Gateway;
Type type = Util.GetType(this.owner.TypeName);
string sort = owner.DefaultOrderByExpression;
if (!string.IsNullOrEmpty(arguments.SortExpression))
{
string[] parts = arguments.SortExpression.Split(new char[] { ' ' }, 2);
string field = "{" + parts[0] + "}";
sort = field + (parts.Length >= 2 ? " " + parts[1] : " ASC");
}
int page = (arguments.MaximumRows > 0 ? (arguments.StartRowIndex / arguments.MaximumRows) + 1 : 1);
WhereClip where = string.IsNullOrEmpty(owner.FilterExpression) ? WhereClip.All : new WhereClip(owner.FilterExpression);
OrderByClip orderBy = string.IsNullOrEmpty(sort) ? OrderByClip.Default : new OrderByClip(sort);
Array list;
if (arguments.MaximumRows > 0)
{
object pageSelector = typeof(Gateway).GetMethod("GetPageSelector").MakeGenericMethod(type).Invoke(gateway, new object[] { where, orderBy, arguments.MaximumRows });
list = (Array)typeof(PageSelector <>).MakeGenericType(type).GetMethod("FindPage").Invoke(pageSelector, new object[] { page });
}
else
{
list = (Array)GetGatewayMethodInfo("EntityType[] FindArray[EntityType](NBear.Common.WhereClip, NBear.Common.OrderByClip)").MakeGenericMethod(type).Invoke(gateway, new object[] { where, orderBy });
}
if (arguments.RetrieveTotalRowCount)
{
arguments.TotalRowCount = (int)GetGatewayMethodInfo("Int32 Count[EntityType](NBear.Common.WhereClip)").MakeGenericMethod(type).Invoke(gateway, new object[] { where });
}
NBearDataSourceSelectedEventArgs selectedArgs = new NBearDataSourceSelectedEventArgs(arguments, list);
owner.OnSelected(selectedArgs);
return(selectedArgs.ResultEntities);
}
/// <summary>
/// 获取用户不分页数据列表
/// </summary>
/// <param name="code">编码</param>
/// <param name="type">1：角色 2：岗位</param>
public static List <TbUser> GetUserGridList(UserListRequset param)
{
var where = new Where <TbUser>();
var userCode = new List <string>();
if (param.type == 1)
{
userCode = TbUserRoleRepository.Query(p => p.RoleCode == param.code).Select(p => p.UserCode).Distinct().ToList();
}
else
{
userCode = Repository <TbPositionUser> .Query(p => p.PositionCode == param.code).Select(p => p.UserCode).Distinct().ToList();
}
if (userCode.Count > 0)
{
where.And(d => d.UserCode.NotIn(userCode));
}
if (!string.IsNullOrEmpty(param.keyword))
{
where.And(d => d.UserCode.Like(param.keyword));
where.Or(d => d.UserName.Like(param.keyword));
}
param.records = Repository <TbUser> .Count(where);
try
{
var orderBy = OrderByOperater.ASC;
if (param.sord.Equals("desc"))
{
orderBy = OrderByOperater.DESC;
}
var orderByClip = new OrderByClip(param.sidx, orderBy);//排序字段
return(Repository <TbUser> .Query(where, orderByClip, param.sord, param.rows, param.page).ToList());
}
catch (Exception ex)
{
throw;
}
}
public SelectSqlSection OrderBy(params OrderByClip[] orderBys)
{
if (orderBys != null && orderBys.Length > 0)
{
if (orderBys.Length == 1)
{
whereClip.SetOrderBy(orderBys[0].OrderBys.ToArray());
}
else
{
OrderByClip combinedOrderBy = new OrderByClip();
for (int i = 0; i < orderBys.Length; ++i)
{
combinedOrderBy = combinedOrderBy & orderBys[i];
}
whereClip.SetOrderBy(combinedOrderBy.OrderBys.ToArray());
}
}
return(this);
}
public void TestSerializable()
{
PropertyItem pi = new PropertyItem("Teddy");
Assert.AreEqual(pi.PropertyName, ((PropertyItem)SerializationManager.Deserialize(typeof(PropertyItem), SerializationManager.Serialize(pi))).PropertyName);
OrderByClip orderBy = new OrderByClip("{ID} DESC");
Assert.AreEqual(orderBy.OrderBy, ((OrderByClip)SerializationManager.Deserialize(typeof(OrderByClip), SerializationManager.Serialize(orderBy))).OrderBy);
StoredProcedureParamItem spi = new StoredProcedureParamItem("Test");
Assert.AreEqual(spi.Name, ((StoredProcedureParamItem)SerializationManager.Deserialize(typeof(StoredProcedureParamItem), SerializationManager.Serialize(spi))).Name);
WhereClip where1 = WhereClip.All;
Assert.AreEqual(where1.ToString(), ((WhereClip)SerializationManager.Deserialize(typeof(WhereClip), SerializationManager.Serialize(where1))).ToString());
WhereClip where2 = new WhereClip("{ID} > @id and {Name} = @Name", 1, "test");
WhereClip s_where2 = (WhereClip)SerializationManager.Deserialize(typeof(WhereClip), SerializationManager.Serialize(where2));
Assert.AreEqual(where2.ToString(), s_where2.ToString());
Assert.AreEqual(where2.ParamValues[0], s_where2.ParamValues[0]);
Assert.AreEqual(where2.ParamValues[1], s_where2.ParamValues[1]);
}
public Article[] ArticleCount(int orgid, int colid, int topNum, string order)
{
WhereClip wc = Article._.Art_IsDel == false && Article._.Art_IsShow == true;
if (orgid > 0)
{
wc.And(Article._.Org_ID == orgid);
}
//if (colid > 0) wc.And(Article._.Col_Id == colid);
if (colid > 0)
{
WhereClip wcColid = new WhereClip();
List <int> list = Business.Do <IColumns>().TreeID((int)colid);
foreach (int l in list)
{
wcColid.Or(Article._.Col_Id == l);
}
wc.And(wcColid);
}
OrderByClip wcOrder = new OrderByClip();
if (order == "hot")
{
wcOrder = Article._.Art_IsHot.Desc;
}
if (order == "img")
{
wcOrder = Article._.Art_IsImg.Desc;
}
if (order == "rec")
{
wcOrder = Article._.Art_IsRec.Desc;
}
if (order == "flux")
{
wcOrder = Article._.Art_Number.Desc;
}
Song.Entities.Article[] arts = Gateway.Default.From <Article>().Where(wc).OrderBy(wcOrder && Article._.Art_PushTime.Desc && Article._.Art_CrtTime.Desc).ToArray <Article>(topNum);
return(arts);
}
/// <summary>
/// 获取数据列表(分页)
/// </summary>
public List <TbSysMenuTable> GetDataListForPage(TbSysMenuTableRequset param, string keyword)
{
//组装查询语句
#region 模糊搜索条件
var where = new Where <TbSysMenuTable>();
if (!string.IsNullOrWhiteSpace(keyword))
{
where.And(d => d.MenuCode.Like(keyword));
where.Or(d => d.TableName.Like(keyword));
}
//if (!string.IsNullOrWhiteSpace(param.TableName))
//{
// where.And(d => d.TableName.Like(param.TableName));
//}
#endregion
var orderBy = OrderByOperater.ASC;
if (param.sord.Equals("desc"))
{
orderBy = OrderByOperater.DESC;
}
try
{
//取总数，以计算共多少页。
var dateCount = Repository <TbSysMenuTable> .Count(where);
var orderByClip = new OrderByClip(new Field(param.sidx), orderBy);//排序字段
var list = Repository <TbSysMenuTable> .Query(where, orderByClip, param.sord, param.rows, param.page).ToList();
param.records = dateCount;
return(list);
}
catch (Exception)
{
throw;
}
}
public Subject[] SubjectCount(int orgid, string sear, bool?isUse, int pid, string order, int index, int count)
{
WhereClip wc = new WhereClip();
if (orgid >= 0)
{
wc.And(Subject._.Org_ID == orgid);
}
if (isUse != null)
{
wc.And(Subject._.Sbj_IsUse == (bool)isUse);
}
if (!string.IsNullOrWhiteSpace(sear))
{
wc.And(Subject._.Sbj_Name.Like("%" + sear + "%"));
}
if (pid >= 0)
{
wc.And(Subject._.Sbj_PID == pid);
}
OrderByClip wcOrder = new OrderByClip();
if (order == "def")
{
wcOrder = Subject._.Sbj_IsRec.Desc & Subject._.Sbj_Tax.Asc;
}
if (order == "tax")
{
wcOrder = Subject._.Sbj_Tax.Asc;
}
if (order == "rec")
{
//wc &= Subject._.Sbj_IsRec == true;
wcOrder = Subject._.Sbj_IsRec.Desc && Subject._.Sbj_Tax.Asc;
}
return(Gateway.Default.From <Subject>().Where(wc).OrderBy(wcOrder).ToArray <Subject>(count, index));
}
public Message[] GetCount(int couid, int olid, string order, int count)
{
WhereClip wc = new WhereClip();
if (couid > 0)
{
wc &= Message._.Cou_ID == couid;
}
if (olid > 0)
{
wc &= Message._.Ol_ID == olid;
}
OrderByClip ord = Message._.Msg_CrtTime.Asc;
if ("desc".Equals(order, StringComparison.CurrentCultureIgnoreCase))
{
ord = Message._.Msg_CrtTime.Desc;
}
if ("asc".Equals(order, StringComparison.CurrentCultureIgnoreCase))
{
ord = Message._.Msg_CrtTime.Asc;
}
return(Gateway.Default.From <Message>().Where(wc).OrderBy(ord).ToArray <Message>(count));
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
HttpRequest rp = context.Request;
WorkInfoManager manager = new WorkInfoManager();
int currentPage = int.Parse(rp["pagenum"]);
int pageSize = int.Parse(rp["pagesize"]);
int count = 0, recordCount = 0;
string workstatus = rp["Workstatus"];
WhereClip where = null;
where = WorkInfo._.CreateDate.Like("%%");
if (!string.IsNullOrEmpty(workstatus))
{
where.And(WorkInfo._.Status == workstatus);
}
if (!string.IsNullOrEmpty(context.Request["RQ"]))
{
string[] datestr = context.Request["RQ"].ToString().Split('@');
string begin = datestr[0];
if (!string.IsNullOrEmpty(begin))
{
where.And(WorkInfo._.CreateDate >= begin);
}
string end = datestr[1];
if (!string.IsNullOrEmpty(end))
{
where.And(WorkInfo._.CreateDate <= end);
}
}
if (!string.IsNullOrEmpty(context.Request["USERNAME"]))
{
where.And(WorkInfo._.CreaterName.Like("%" + context.Request["USERNAME"].ToString() + "%"));
}
if (!string.IsNullOrEmpty(context.Request["SBNAME"]))
{
where.And(ShebeiInfo._.Name.Like("%" + context.Request["SBNAME"].ToString() + "%"));
}
if (!string.IsNullOrEmpty(context.Request["SBZT"]))
{
where.And(WorkInfo._.Status.Like("%" + context.Request["SBZT"].ToString() + "%"));
}
if (!string.IsNullOrEmpty(context.Request["GZXX"]))
{
where.And(WorkInfo._.GuZhangXx.Like("%" + context.Request["GZXX"].ToString() + "%"));
}
if (context.Session["AllDepart"] != null)
{
List<AdministrativeRegions> list = context.Session["AllDepart"] as List<AdministrativeRegions>;
if (list != null && list.Count > 0)
{
string[] dparr = new string[list.Count];
for (int i = 0; i < list.Count; i++)
{
dparr[i] = list[i].ID.ToString();
}
if (WhereClip.IsNullOrEmpty(where))
{
where = ShebeiInfo._.SocrceDepart.In(dparr);
}
else
{
where = where && ShebeiInfo._.SocrceDepart.In(dparr);
}
}
}
OrderByClip or = WorkInfo._.CreateDate.Desc;
if (!string.IsNullOrEmpty(context.Request["sortdatafield"]))
{
if (!string.IsNullOrEmpty(context.Request["sortorder"]) && context.Request["sortorder"] == "desc")
{
or = new OrderByClip(context.Request["sortdatafield"] + " desc");
}
else
{
or = new OrderByClip(context.Request["sortdatafield"]);
}
}
if (!string.IsNullOrEmpty(context.Request["index"]))
{
pageSize = 4;
}
DataTable dt = manager.GetDataTable(currentPage + 1, pageSize, where, or, ref count, ref recordCount);
string result = JsonConvert.Convert2Json(dt);
context.Response.Write("{ \"totalRecords\":\"" + recordCount + "\",\"rows\":" + result + "}");
context.Response.End();
}
/// <summary>
/// 返回实体列表分页数据
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public List <T> GetList(WhereClip where, OrderByClip order, int pageIndex, int pageSize, ref int recordCount)
{
recordCount = Count(where);
return(GetFromSection(where, order).Page(pageSize, pageIndex).ToList());
}
/// <summary>
/// 返回实体列表
/// </summary>
/// <param name="where"></param>
/// <param name="?"></param>
/// <returns></returns>
public List <T> GetList(WhereClip where, OrderByClip order)
{
return(GetFromSection(where, order).ToList());
}
/// <summary>
/// 返回实体数据集分页数据
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetDataTable(FromSection fromSection, WhereClip where, OrderByClip order, int pageIndex, int pageSize, ref int recordCount)
{
recordCount = fromSection.Where(where).Count();
return(fromSection.Where(where).OrderBy(order).Page(pageSize, pageIndex).ToDataTable());
}
/// <summary>
/// 返回实体数据集分页数据
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetDataTable(WhereClip where, OrderByClip order, int pageIndex, int pageSize, ref int recordCount)
{
recordCount = Count(where);
return(GetFromSection(where, order).Page(pageSize, pageIndex).ToDataTable());
}
/// <summary>
/// 返回实体数据集
/// </summary>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetDataTable(WhereClip where, OrderByClip order)
{
return(GetFromSection(where, order).ToDataTable());
}
/// <summary>
/// 分页获取获取工作信息表datatable
/// </summary>
/// <param name="pageindex">当前页数</param>
/// <param name="pagesize">每页显示条数</param>
/// <param name="orderby">排序方式</param>
/// <param name="pageCount">总页数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public DataTable GetDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
WhereClip appendwhere = new WhereClip();
appendwhere.Append(" (HandSequence is null or HandSequence= (select max(HandSequence) from WorkHandLog where [WorkID]=[WorkInfo].[ID]))");
return Dal.From<WorkInfo>().Join<ShebeiInfo>(ShebeiInfo._.ID == WorkInfo._.SbID)
.Join<WorkHandLog>(WorkInfo._.ID == WorkHandLog._.WorkID && appendwhere, JoinType.leftJoin)
.Select(WorkInfo._.ID.All, WorkHandLog._.HandResult,WorkHandLog._.HandDate, ShebeiInfo._.Code, ShebeiInfo._.Name, ShebeiInfo._.GuiGe, WorkHandLog._.ChuliYj)
.Where(where).OrderBy(orderby).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount);
}
/// <summary>
/// 根据条件获取报修设备
/// </summary>
/// <param name="p"></param>
/// <param name="pageSize"></param>
/// <param name="where"></param>
/// <param name="orderByClip"></param>
/// <param name="count"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetGzDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
return Dal.From<WorkInfo>()
.Join<ShebeiInfo>(ShebeiInfo._.ID == WorkInfo._.SbID)
.Where(where).OrderBy(orderby)
.Select(WorkInfo._.ID.All
, ShebeiInfo._.Code, ShebeiInfo._.Name, ShebeiInfo._.GuiGe, ShebeiInfo._.Note.Alias("SheBeiNote"))
.ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
HttpRequest rp = context.Request;
WorkInfoManager manager = new WorkInfoManager();
int currentPage = int.Parse(rp["pagenum"]);
int pageSize = int.Parse(rp["pagesize"]);
int count = 0, recordCount = 0;
string workstatus = rp["Workstatus"];
WhereClip where = null;
where = WorkInfo._.CreateDate.Like("%%");
if (!string.IsNullOrEmpty(workstatus))
{
where.And(WorkInfo._.Status == workstatus);
}
if (!string.IsNullOrEmpty(context.Request["RQ"]))
{
string[] datestr = context.Request["RQ"].ToString().Split('@');
string begin = datestr[0];
if (!string.IsNullOrEmpty(begin))
{
where.And(WorkInfo._.CreateDate >= begin);
}
string end = datestr[1];
if (!string.IsNullOrEmpty(end))
{
where.And(WorkInfo._.CreateDate <= end);
}
}
if (!string.IsNullOrEmpty(context.Request["USERNAME"]))
{
where.And(WorkInfo._.CreaterName.Like("%" + context.Request["USERNAME"].ToString() + "%"));
}
if (!string.IsNullOrEmpty(context.Request["SBNAME"]))
{
where.And(ShebeiInfo._.Name.Like("%" + context.Request["SBNAME"].ToString() + "%"));
}
if (!string.IsNullOrEmpty(context.Request["SBZT"]))
{
where.And(WorkInfo._.Status.Like("%" + context.Request["SBZT"].ToString() + "%"));
}
if (!string.IsNullOrEmpty(context.Request["GZXX"]))
{
where.And(WorkInfo._.GuZhangXx.Like("%" + context.Request["GZXX"].ToString() + "%"));
}
if (context.Session["AllDepart"] != null)
{
List <AdministrativeRegions> list = context.Session["AllDepart"] as List <AdministrativeRegions>;
if (list != null && list.Count > 0)
{
string[] dparr = new string[list.Count];
for (int i = 0; i < list.Count; i++)
{
dparr[i] = list[i].ID.ToString();
}
if (WhereClip.IsNullOrEmpty(where))
{
where = ShebeiInfo._.SocrceDepart.In(dparr);
}
else
{
where = where && ShebeiInfo._.SocrceDepart.In(dparr);
}
}
}
OrderByClip or = WorkInfo._.CreateDate.Desc;
if (!string.IsNullOrEmpty(context.Request["sortdatafield"]))
{
if (!string.IsNullOrEmpty(context.Request["sortorder"]) && context.Request["sortorder"] == "desc")
{
or = new OrderByClip(context.Request["sortdatafield"] + " desc");
}
else
{
or = new OrderByClip(context.Request["sortdatafield"]);
}
}
if (!string.IsNullOrEmpty(context.Request["index"]))
{
pageSize = 4;
}
DataTable dt = manager.GetDataTable(currentPage + 1, pageSize, where, or, ref count, ref recordCount);
string result = JsonConvert.Convert2Json(dt);
context.Response.Write("{ \"totalRecords\":\"" + recordCount + "\",\"rows\":" + result + "}");
context.Response.End();
}
/// <summary>
/// 使用linq查询（分页）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <typeparam name="OutEnity"></typeparam>
/// <param name="linq"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="lambdaOrderBy"></param>
/// <returns></returns>
public ActionResult getListByPaging <TEntity, OutEnity>(FromSection <TEntity> linq, Pagination pagination, OrderByClip lambdaOrderBy = null) where TEntity : Entity
{
int pageIndex = pagination.page;
int pageSize = pagination.rows <= 0 ? 20 : pagination.rows;
List <OutEnity> sqlList = new List <OutEnity>();
if (lambdaOrderBy != null)
{
sqlList = linq.OrderBy(lambdaOrderBy).Page(pageSize, pageIndex).ToList <OutEnity>();
}
else
{
sqlList = linq.Page(pageSize, pageIndex).ToList <OutEnity>();
}
int totalCount = linq.Count();
return(Content(new
{
rows = sqlList,
total = (totalCount / pageSize) + 1,
page = pagination.page,
records = totalCount
}.ToJson()));
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
HttpRequest rp = context.Request;
WorkInfoManager manager = new WorkInfoManager();
int currentPage = int.Parse(rp["pagenum"]);
int pageSize = int.Parse(rp["pagesize"]) ;
int count = 0, recordCount = 0;
string workstatus = rp["Workstatus"];
WhereClip where = new WhereClip();
if (context.Session["AllDepart"] != null)
{
List<AdministrativeRegions> list = context.Session["AllDepart"] as List<AdministrativeRegions>;
if (list != null && list.Count > 0)
{
string[] dparr = new string[list.Count];
for (int i = 0; i < list.Count; i++)
{
dparr[i] = list[i].ID.ToString();
}
if (WhereClip.IsNullOrEmpty(where))
{
where = ShebeiInfo._.SocrceDepart.In(dparr);
}
else
{
where = where && ShebeiInfo._.SocrceDepart.In(dparr);
}
}
}
if (!string.IsNullOrEmpty(workstatus))
{
where.And(WorkInfo._.Status == workstatus);
}
if (!string.IsNullOrEmpty(context.Request["begindate"]))
{
string begin = context.Request["begindate"];
if (!string.IsNullOrEmpty(begin))
{
where = where && WorkInfo._.CreateDate >= begin;
}
}
if (!string.IsNullOrEmpty(context.Request["enddate"]))
{
string enddate = context.Request["enddate"];
if (!string.IsNullOrEmpty(enddate))
{
where = where && WorkInfo._.CreateDate <= enddate;
}
}
if (!string.IsNullOrEmpty(context.Request["username"]))
{
where = where && WorkInfo._.CurrentUser.Contains(context.Request["username"]);
}
if (!string.IsNullOrEmpty(context.Request["sbcode"]))
{
where = where && ShebeiInfo._.Code.Contains(context.Request["sbcode"]);
}
if (!string.IsNullOrEmpty(context.Request["sbname"]))
{
where = where && ShebeiInfo._.Name.Contains(context.Request["sbname"]);
}
OrderByClip or = WorkInfo._.CreateDate.Desc;
if (!string.IsNullOrEmpty(context.Request["sortdatafield"]))
{
if (!string.IsNullOrEmpty(context.Request["sortorder"]) && context.Request["sortorder"] == "desc")
{
or = new OrderByClip(context.Request["sortdatafield"] + " desc");
}
else
{
or = new OrderByClip(context.Request["sortdatafield"]);
}
}
DataTable dt = manager.GetDataTable(currentPage + 1, pageSize, where, or, ref count, ref recordCount);
string result = JsonConvert.Convert2Json(dt);
context.Response.Write("{ \"totalRecords\":\"" + recordCount + "\",\"rows\":" + result + "}");
context.Response.End();
}
/// <summary>
/// 分页获取获取员工信息表datatable
/// </summary>
/// <param name="pageindex">当前页数</param>
/// <param name="pagesize">每页显示条数</param>
/// <param name="orderby">排序方式</param>
/// <param name="pageCount">总页数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public DataTable GetDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
return Dal.From<PersonInfo>().Join<DepartAndPerson>(PersonInfo._.ID == DepartAndPerson._.UserID, JoinType.leftJoin)
.Join<AdministrativeRegions>(DepartAndPerson._.DepartID == AdministrativeRegions._.ID && DepartAndPerson._.IsDefault == true, JoinType.leftJoin)
.Select(PersonInfo._.ID.All, AdministrativeRegions._.Name.Alias("DepartName"), new ExpressionClip("case MarryStatus when 0 then '外部' else '内部' end NWName"))
.Where(where).OrderBy(orderby).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount);
}
/// <summary>
/// 分页获取获取日志信息表datatable
/// </summary>
/// <param name="pageindex">当前页数</param>
/// <param name="pagesize">每页显示条数</param>
/// <param name="orderby">排序方式</param>
/// <param name="pageCount">总页数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public DataTable GetDataTable(int pageindex, int pagesize, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
return Dal.From<DayLog>().Where(where).OrderBy(orderby).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount);
}
/// <summary>
/// 分页获取获取工作信息表datatable
/// </summary>
/// <param name="pageindex">当前页数</param>
/// <param name="pagesize">每页显示条数</param>
/// <param name="orderby">排序方式</param>
/// <param name="pageCount">总页数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public DataTable GetDataTable(int pageindex, int pagesize, string UserId, WhereClip where, OrderByClip orderby, ref int pageCount, ref int recordCount)
{
WhereClip wherenew = new WhereClip();
if (!string.IsNullOrEmpty(UserId))
{
string[] s = UserId.Split(';');
if (s[1] == "2")
{
wherenew = wherenew && WorkHandLog._.Uper == new Guid(s[0]);
}
else
{
wherenew = wherenew && WorkHandLog._.DownEr == new Guid(s[0]);
}
}
if (WhereClip.IsNullOrEmpty(where))
{
where = wherenew;
}
else
{
where = where && wherenew;
}
if (WhereClip.IsNullOrEmpty(where))
{
where.Append(@" (handsequence is null or handsequence = (select max(handsequence) from [WorkHandLog] b where [WorkInfo].[ID] = b.[WorkID])) ");
}
else
{
where.Append(@" and (handsequence is null or handsequence = (select max(handsequence) from [WorkHandLog] b where [WorkInfo].[ID] = b.[WorkID]) )");
}
return Dal.From<WorkInfo>().Join<ShebeiInfo>(ShebeiInfo._.ID == WorkInfo._.SbID)
.Join<WorkHandLog>(WorkInfo._.ID == WorkHandLog._.WorkID, JoinType.leftJoin)
.Select(WorkInfo._.ID.All, ShebeiInfo._.Code, ShebeiInfo._.Name, ShebeiInfo._.GuiGe)
.Where(where).OrderBy(orderby).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
HttpRequest rp = context.Request;
WorkInfoManager manager = new WorkInfoManager();
int currentPage = int.Parse(rp["pagenum"]);
int pageSize = int.Parse(rp["pagesize"]);
int count = 0, recordCount = 0;
string workstatus = rp["Workstatus"];
WhereClip where = new WhereClip();
if (context.Session["AllDepart"] != null)
{
List <AdministrativeRegions> list = context.Session["AllDepart"] as List <AdministrativeRegions>;
if (list != null && list.Count > 0)
{
string[] dparr = new string[list.Count];
for (int i = 0; i < list.Count; i++)
{
dparr[i] = list[i].ID.ToString();
}
if (WhereClip.IsNullOrEmpty(where))
{
where = ShebeiInfo._.SocrceDepart.In(dparr);
}
else
{
where = where && ShebeiInfo._.SocrceDepart.In(dparr);
}
}
}
if (!string.IsNullOrEmpty(workstatus))
{
where.And(WorkInfo._.Status == workstatus);
}
if (!string.IsNullOrEmpty(context.Request["begindate"]))
{
string begin = context.Request["begindate"];
if (!string.IsNullOrEmpty(begin))
{
where = where && WorkInfo._.CreateDate >= begin;
}
}
if (!string.IsNullOrEmpty(context.Request["enddate"]))
{
string enddate = context.Request["enddate"];
if (!string.IsNullOrEmpty(enddate))
{
where = where && WorkInfo._.CreateDate <= enddate;
}
}
if (!string.IsNullOrEmpty(context.Request["username"]))
{
where = where && WorkInfo._.CurrentUser.Contains(context.Request["username"]);
}
if (!string.IsNullOrEmpty(context.Request["sbcode"]))
{
where = where && ShebeiInfo._.Code.Contains(context.Request["sbcode"]);
}
if (!string.IsNullOrEmpty(context.Request["sbname"]))
{
where = where && ShebeiInfo._.Name.Contains(context.Request["sbname"]);
}
OrderByClip or = WorkInfo._.CreateDate.Desc;
if (!string.IsNullOrEmpty(context.Request["sortdatafield"]))
{
if (!string.IsNullOrEmpty(context.Request["sortorder"]) && context.Request["sortorder"] == "desc")
{
or = new OrderByClip(context.Request["sortdatafield"] + " desc");
}
else
{
or = new OrderByClip(context.Request["sortdatafield"]);
}
}
DataTable dt = manager.GetDataTable(currentPage + 1, pageSize, where, or, ref count, ref recordCount);
string result = JsonConvert.Convert2Json(dt);
context.Response.Write("{ \"totalRecords\":\"" + recordCount + "\",\"rows\":" + result + "}");
context.Response.End();
}
public DataTable GetDaiBanDataTable(WhereClip where, OrderByClip orderby)
{
return Dal.From<WorkHandLog>()
.Where(where).OrderBy(orderby).ToDataTable();
}