/// <summary> /// 分页获取获取待选的人员 /// </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 GetPersonByDepartID(int pageindex, int pagesize, WhereClip wherefilrer, string departID, bool has, ref int pageCount, ref int recordCount) { if (string.IsNullOrEmpty(departID)) { departID = Guid.NewGuid().ToString(); } WhereClip where = null; if (has) { where = DepartAndPerson._.DepartID == departID; return(Dal.From <PersonInfo>().Join <DepartAndPerson>(DepartAndPerson._.UserID == PersonInfo._.ID, JoinType.leftJoin) .Where(where) .Select(PersonInfo._.ID, PersonInfo._.UserName, PersonInfo._.RealName, DepartAndPerson._.ID.Alias("DepartPersonID"), DepartAndPerson._.DepartID).OrderBy(PersonInfo._.RealName).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount)); } else { //过滤人员 where = DepartAndPerson._.DepartID == departID; if (!WhereClip.IsNullOrEmpty(wherefilrer)) { where = where && wherefilrer; } return(Dal.From <PersonInfo>().Join <DepartAndPerson>(DepartAndPerson._.UserID == PersonInfo._.ID && DepartAndPerson._.DepartID == departID, JoinType.leftJoin) .Where(DepartAndPerson._.DepartID == null) .Select(PersonInfo._.ID, PersonInfo._.UserName, PersonInfo._.RealName, DepartAndPerson._.ID.Alias("DepartPersonID"), DepartAndPerson._.DepartID).OrderBy(PersonInfo._.RealName).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; if (!string.IsNullOrEmpty(workstatus)) { where = WorkInfo._.Status == workstatus; } string UserId = string.Empty; if (!string.IsNullOrEmpty(rp["IsDaiBan"])) { UserId = context.Session["UserID"].ToString() + ";" + rp["IsDaiBan"]; } else { 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); } } } } DataTable dt = manager.GetDataTable(currentPage + 1, pageSize, UserId, where, WorkInfo._.CreateDate.Desc, ref count, ref recordCount); string result = JsonConvert.Convert2Json(dt); context.Response.Write("{ \"totalRecords\":\"" + recordCount + "\",\"rows\":" + result + "}"); context.Response.End(); }
//public virtual string LambdaInsert(LambdaInsertHelper insertHelper, ref Dictionary<string, Parameter> parameters) //{ // if (insertHelper.Fields.Count == 0) // { // throw new // } // StringBuilder sql = new StringBuilder(); // sql.Append("INSERT INTO "); // sql.Append(GetTableName(insertHelper.ClassMap.TableName)); // foreach (Field filed in insertHelper.Fields) // { // columns.Append(","); // columns.Append(Configuration.Dialect.GetColumnName(GetTableName(filed.TableName), filed.ColumnName, filed.AliasName)); // } // sql.Append(BuildSelectColumns(insertHelper.ClassMap, insertHelper.Fields, insertHelper.Joins)); // sql.Append(BuildFrom(insertHelper.ClassMap, insertHelper.Joins, insertHelper.EnabledNoLock)); // sql.Append(" "); // if (!WhereClip.IsNullOrEmpty(insertHelper.WhereClip)) // { // sql.Append(insertHelper.WhereClip.WhereString); // } // if (!GroupByClip.IsNullOrEmpty(insertHelper.GroupByClip)) // { // sql.Append(insertHelper.GroupByClip.GroupByString); // if (!WhereClip.IsNullOrEmpty(insertHelper.HavingClip)) // { // sql.Append(" HAVING "); // sql.Append(insertHelper.HavingClip.ToString()); // } // } // if (!OrderByClip.IsNullOrEmpty(insertHelper.OrderByClip)) // { // sql.Append(insertHelper.OrderByClip.OrderByString); // sql.Append(" "); // } // if (insertHelper.PageIndex > 0 && insertHelper.PageSize > 0 && string.IsNullOrEmpty(topSql)) // { // Dictionary<string, object> pageParameters = new Dictionary<string, object>(); // string pageSql = this.Configuration.Dialect.GetPagingSql(sql.ToString(), Convert.ToInt32(insertHelper.PageIndex), Convert.ToInt32(insertHelper.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 virtual string LambdaDelete(LambdaDeleteHelper deleteHelper, ref Dictionary <string, Parameter> parameters) { StringBuilder sql = new StringBuilder(); sql.Append("DELETE FROM "); sql.Append(GetTableName(deleteHelper.ClassMap.TableName)); if (!WhereClip.IsNullOrEmpty(deleteHelper.WhereClip)) { sql.Append(deleteHelper.WhereClip.WhereString); } return(sql.ToString()); }
/// <summary> /// Where语句,默认为Add /// </summary> /// <param name="where"></param> /// <returns>WhereClip</returns> public ModalAdapter <T> Where(WhereClip where) { if (WhereClip.IsNullOrEmpty(this._Where)) { this._Where = where; } else { this._Where.And(where); } return(this); }
/// <summary> /// Where语句,使用Or连接 /// </summary> /// <param name="where"></param> /// <returns>WhereClip</returns> public SearchHelper OrWhere(WhereClip where) { if (WhereClip.IsNullOrEmpty(this._Where)) { this._Where = where; } else { this._Where.Or(where); } return(this); }
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()); } }
/// <summary> /// 分页获取获取待选的人员 /// </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 GetPersonByRoleID(int pageindex, int pagesize, WhereClip wherefilrer, string roleID, bool has, ref int pageCount, ref int recordCount) { if (string.IsNullOrEmpty(roleID)) { roleID = Guid.NewGuid().ToString(); } WhereClip where = null; if (has) { where = RolePerson._.RoleID == roleID; return(Dal.From <PersonInfo>().Join <RolePerson>(RolePerson._.PersonID == PersonInfo._.ID, JoinType.leftJoin) .Where(where) .Select(PersonInfo._.ID, PersonInfo._.UserName, PersonInfo._.RealName, RolePerson._.ID.Alias("RolePersonID"), RolePerson._.RoleID).OrderBy(PersonInfo._.RealName).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount)); } else { //过滤人员 where = RolePerson._.RoleID == roleID; if (!WhereClip.IsNullOrEmpty(wherefilrer)) { where = where && wherefilrer; } return(Dal.From <PersonInfo>().Join <RolePerson>(RolePerson._.PersonID == PersonInfo._.ID && RolePerson._.RoleID == roleID, JoinType.leftJoin) .Where(RolePerson._.RoleID == null) .Select(PersonInfo._.ID, PersonInfo._.UserName, PersonInfo._.RealName, RolePerson._.ID.Alias("RolePersonID"), RolePerson._.RoleID).OrderBy(PersonInfo._.RealName).ToDataTable(pagesize, pageindex, ref pageCount, ref recordCount)); } // // return Dal.FromCustomSql(@"select * from personinfo //where ID NOT IN ( SELECT PersonID //FROM RolePerson INNER JOIN // RoleInfo ON RoleID = RoleInfo.ID // WHERE RoleId=@RoleId ) order by RealName").AddInputParameter("RoleId", new Guid(roleID)).ToDataTable(); }
public virtual string LambdaUpdate(LambdaUpdateHelper updateHelper, ref Dictionary <string, Parameter> parameters) { StringBuilder sql = new StringBuilder(); sql.Append("UPDATE "); sql.Append(GetTableName(updateHelper.ClassMap.TableName)); if (!WhereClip.IsNullOrEmpty(updateHelper.SetClip)) { sql.Append(updateHelper.SetClip.SetString); } else { throw new Exception("Update 语句中未找到 Set 的字段信息"); } if (!WhereClip.IsNullOrEmpty(updateHelper.WhereClip)) { sql.Append(updateHelper.WhereClip.WhereString); } return(sql.ToString()); }
public SelectSqlSection Having(WhereClip where) { if (!WhereClip.IsNullOrEmpty(where) && where.Sql.Length > 0) { string tempSql = where.Sql.ToString(); Dictionary <string, KeyValuePair <DbType, object> > .Enumerator en = where.Parameters.GetEnumerator(); while (en.MoveNext()) { object value = en.Current.Value.Value; if (value != null && value != DBNull.Value && value is ICloneable) { value = ((ICloneable)value).Clone(); } string newParamName = ExpressionBase.MakeUniqueParamNameWithoutPrefixToken(); tempSql = tempSql.Replace('@' + en.Current.Key.TrimStart(SqlQueryUtils.PrefixCharArray), '@' + newParamName); whereClip.Parameters.Add('@' + newParamName, new KeyValuePair <DbType, object>(en.Current.Value.Key, value)); } if (whereClip.Having.Length > 0) { whereClip.Having.Append(" AND "); } if (where.IsNot && tempSql.Length > 0) { whereClip.Having.Append("NOT "); } if (tempSql.Length > 0) { whereClip.Having.Append('('); whereClip.Having.Append(tempSql); whereClip.Having.Append(')'); } } return(this); }
/// <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 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(); }
protected void Page_Load(object sender, EventArgs e) { if (Session["UserName"] != null) { hid.Value = Session["UserName"].ToString(); } if (!IsPostBack) { NoticeInfoManager ntMgr = new NoticeInfoManager(); down.InnerText = "主办单位:长清公安分局"; string titlepix = "首页"; switch (Request.FilePath.ToLower()) { case "/indexxjsb.aspx": titlepix = "巡检设备"; break; case "/indexkaoqin.aspx": titlepix = "考勤统计"; break; case "/indexpaigong.aspx": titlepix = "派工统计"; break; case "/indexXjsb.aspx": titlepix = "巡检设备"; break; } ttt.Text = titlepix + "-长清公安分局运维综合管理系统"; //NoticeInfo notice = ntMgr.GetTopText(true); //string s = "<a style='color:#FFFFFF; ' href='newsinfo.aspx?id={0}'>{1}</a>"; //if (notice != null) //{ // hidZuiTopTongZhi.Value = string.Format(s, notice.ID, notice.TITLE); //} //NoticeInfo notice = ntMgr.GetTopText(false); //string s = "<a style='font-family:Microsoft YaHei;color:#FFFF00;font-size:24px;' href='newsinfo.aspx?id={0}'>{1}</a>"; //if (notice != null) //{ // hidTopTongZhid.Value = string.Format(s, notice.ID, notice.TITLE); //} WorkInfoManager wkMgr = new WorkInfoManager(); WhereClip where = WorkInfo._.Status == "制单"; OrderByClip order = WorkInfo._.CreateDate.Desc; int count = 0; if (Session["AllDepart"] != null) { List <AdministrativeRegions> list = 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); } } } DataTable dtwk = wkMgr.GetGzDataTable(1, 4, where, order, ref count, ref count); string s = "<a style='font-family:Microsoft YaHei;color:#FFFF00;font-size:24px;' >{0}</a>"; string msg = string.Empty; if (dtwk == null || dtwk.Rows.Count == 0) { msg = "没有最新设备报修信息,所有报修设备信息都已派工"; } else { foreach (DataRow item in dtwk.Rows) { msg += item["Name"] + "(" + item["Code"] + "):" + item["Guzhang"]; msg += " | "; } } hidTopTongZhid.Value = string.Format(s, msg);; } }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; ShebeiInfoManager manager = new ShebeiInfoManager(); int pageNum = int.Parse(context.Request.QueryString.Get("pagenum")); int pagesize = int.Parse(context.Request.QueryString.Get("pagesize")); int recordCount = 0; WhereClip where = new WhereClip(); if (!string.IsNullOrEmpty(context.Request.QueryString["nb"])) { if (context.Request.QueryString["nb"] == "1") { where = where && ShebeiInfo._.IsNeiWai == true; } else if (context.Request.QueryString["nb"] == "0") { where = where && ShebeiInfo._.IsNeiWai == false; } } if (!string.IsNullOrEmpty(context.Request["sbzt"])) { where = where && ShebeiInfo._.State == context.Request["sbzt"]; } 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"]); } 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); } } } string or = " state, CODE "; if (!string.IsNullOrEmpty(context.Request["sortdatafield"])) { if (!string.IsNullOrEmpty(context.Request["sortorder"]) && context.Request["sortorder"] == "desc") { or = context.Request["sortdatafield"] + " desc"; } else { or = context.Request["sortdatafield"]; } } DataTable dt = manager.GetDataTableWithImg(pageNum + 1, pagesize, where, or, ref pagesize, ref recordCount); //manager.GetDataTable(); string result = JsonConvert.Convert2Json(dt); context.Response.Write("{\"totalRecords\":\"" + recordCount.ToString() + "\",\"rows\":" + result + "}"); context.Response.End(); }