//获取用户列表 public DataTable GetPageList(ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select UserId,Account,Mobile, Email,CreateTime,Enabled from BaseUser where IsDelete=0 "); return(Repository().FindTablePageBySql(strSql.ToString(), ref jqgridparam)); }
/// <summary> /// 加班申请列表 /// </summary> public DataTable GetPageList(string keywords, ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT * FROM ( SELECT a.OvertimeId, (select FullName from Base_DataDictionaryDetail where Code=a.Type and DataDictionaryId= (select DataDictionaryId from Base_DataDictionary where Code='Overtime')) AS Type, a.Date,a.StartTime,a.EndTime, (select FullName from Base_DataDictionaryDetail where Code=a.Time and DataDictionaryId= (select DataDictionaryId from Base_DataDictionary where Code='OvertimeLong')) AS Time, b.FullName as DepartmentName,c.RealName as UserName, d.FullName as CreatDepartmentName,e.RealName as CreatUserName,a.CreateTime from OvertimeRequest a inner join Base_Department b on a.DepartmentId=b.DepartmentId inner join Base_User c on a.UserId=c.UserId left join Base_Department d on a.CreateDepartmentId=d.DepartmentId left join Base_User e on a.CreateUserId=e.UserId ) T WHERE 1=1"); if (!string.IsNullOrEmpty(keywords)) { strSql.Append(@" AND (UserName LIKE @keyword)"); parameter.Add(DbFactory.CreateDbParameter("@keyword", '%' + keywords + '%')); } //if (!ManageProvider.Provider.Current().IsSystem) //{ // strSql.Append(" AND ( UserId IN ( SELECT ResourceId FROM Base_DataScopePermission WHERE"); // strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') "); // strSql.Append(" ) )"); //} return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
// <summary> /// 获取列表 /// </summary> /// <param name="jqgridparam">分页条件</param> /// <returns></returns> public IList <Ho_SetSubscribe> GetPageList(ref JqGridParam jqgridparam, string KeyValue, string Keyword) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT * FROM Ho_SetSubscribe where 1 = 1"); //订单编号 if (!string.IsNullOrEmpty(KeyValue)) { strSql.Append(" AND MS_Number = @MS_Number"); parameter.Add(DbFactory.CreateDbParameter("@MS_Number", KeyValue)); } //关键字 if (!string.IsNullOrEmpty(Keyword)) { strSql.Append(@" AND (s_CarOrBus LIKE @keyword OR s_Address LIKE @keyword OR s_Reception LIKE @keyword OR s_CarType LIKE @keyword OR s_CarNumer LIKE @keyword OR s_CarColor LIKE @keyword)"); parameter.Add(DbFactory.CreateDbParameter("@keyword", '%' + Keyword + '%')); } return(Repository().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
public DataTable GetPageList(ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@" select * from ( select m.meetingid as meetingid , m.Meeting_id as Meeting_id , --主键 m.unit_id as unit_id, --组会单位ID c.unit as unit_name, --组会单位名称 m.adduser_id as adduser_id, --组会人员ID u.RealName as adduser_name, --组会人员名称 m.adddate as adddate, --组会日期 m.name as name , --会议名称 m.type as type, --会议类型 m.Room_id as room_id , --房间ID m.startdate as startdate, --开始时间 m.enddate as enddate, --结束时间 m.userid as userid, --会场参会人员的Id m.username as username, --会场参会人员 m.QJid as QJid, --会场全景ID m.LDid as LDid, --领导ID m.ZhCid as ZhCid, --主持人ID m.screenmodel as screenmodel , --窗口模式 m.state --状态 FROM Base_Meeting m LEFT JOIN Base_Unit c ON c.Base_Unit_id = m.Unit_id LEFT JOIN Base_User u ON u.UserId = m.adduser_id ) a where 1=1 and state=0 and type=2 "); strSql.Append(" AND adduser_id = @user_id"); parameter.Add(DbFactory.CreateDbParameter("@user_id", ManageProvider.Provider.Current().UserId)); return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 【系统日志】返回系统日志列表JSON /// </summary> /// <param name="ModuleId">模块ID</param> /// <param name="ParameterJson">搜索条件</param> /// <param name="jqgridparam">表格参数</param> /// <returns></returns> public ActionResult GridPageListJson(string ModuleId, string ParameterJson, JqGridParam jqgridparam) { try { Stopwatch watch = CommonHelper.TimerStart(); //if (!string.IsNullOrEmpty(ParameterJson) && ParameterJson.Length > 2) //{ // List<DbParameter> parameter = new List<DbParameter>(); // IList conditions = ParameterJson.JonsToList<Condition>(); // string WhereSql = ConditionBuilder.GetWhereSql(conditions, out parameter); //} List <Base_SysLog> ListData = base_syslogbll.GetPageList(ModuleId, ParameterJson, ref jqgridparam); var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return(Content(JsonData.ToJson())); } catch (Exception ex) { Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message); return(null); } }
public ActionResult GridPageListJson(string keywords, JqGridParam jqgridparam, string ParameterJson) { try { Stopwatch watch = CommonHelper.TimerStart(); StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@" select * from product where 1=1 "); if (!CommonHelper.IsEmpty(keywords)) { strSql.AppendFormat(@" and (partname like '%{0}%' or productno like '%{0}%') ", keywords); } if (ManageProvider.Provider.Current().IsSystem == false) { strSql.AppendFormat(@" and CompanyID='{0}' ", ManageProvider.Provider.Current().CompanyId); } DataTable ListData = productre.FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam); var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return(Content(JsonData.ToJson())); } catch (Exception ex) { //Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message); return(null); } }
public virtual JsonResult GridPageJson(string parameterJson, JqGridParam jqgridparam) { try { Stopwatch watch = CommonHelper.TimerStart(); List <TEntity> ListData = new List <TEntity>(); if (!string.IsNullOrEmpty(parameterJson)) { List <DbParameter> parameter = new List <DbParameter>(); IList conditions = parameterJson.JonsToList <Condition>(); string WhereSql = ConditionBuilder.GetWhereSql(conditions, out parameter); ListData = Repositoryfactory.Repository().FindListPage(WhereSql, parameter.ToArray(), ref jqgridparam); } else { ListData = Repositoryfactory.Repository().FindListPage(ref jqgridparam); } var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return(Json(JsonData, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { BaseSysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message + "\r\n条件:" + parameterJson); return(null); } }
public ActionResult GridPageListJson(string keywords, string CompanyId, string DepartmentId, JqGridParam jqgridparam, string ParameterJson) { try { Stopwatch watch = CommonHelper.TimerStart(); StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@" select * from WanWuYunDevice where 1=1 "); DataTable ListData = re.FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam); var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return(Content(JsonConvert.SerializeObject(JsonData))); } catch (Exception ex) { return(null); } }
/// <summary> /// 获取系统日志列表 /// </summary> /// <param name="ModuleId">模块ID</param> /// <param name="ParameterJson">搜索条件</param> /// <param name="jqgridparam">分页条件</param> /// <returns></returns> public List <Base_SysLog> GetPageList(string ModuleId, string ParameterJson, ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT * FROM ( SELECT l.SysLogId , l.ObjectId , l.LogType , l.IPAddress , l.IPAddressName , c.FullName AS CompanyId , D.FullName AS DepartmentId , l.CreateDate , l.CreateUserId , l.CreateUserName , m.FullName AS ModuleId , l.Remark , l.Status FROM Base_SysLog l LEFT JOIN Base_Department d ON d.DepartmentId = l.DepartmentId LEFT JOIN Base_Company c ON c.CompanyId = l.CompanyId LEFT JOIN Base_Module m ON m.ModuleId = l.ModuleId ) A WHERE 1 = 1"); //strSql.Append(WhereSql); if (!string.IsNullOrEmpty(ModuleId)) { strSql.Append(" AND ModuleId = @ModuleId"); parameter.Add(DbFactory.CreateDbParameter("@ModuleId", ModuleId)); } return(Repository().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 列表加载 /// </summary> /// <param name="ParameterJson"></param> /// <param name="unit_id"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public string GetQuery(string ParameterJson, JqGridParam jqgridparam) { string user_id = ManageProvider.Provider.Current().UserId; try { int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; Stopwatch watch = CommonHelper.TimerStart(); string sql = "select orm.OperationRoomMain_id,orm.room_id,orm.adduser,CONVERT(varchar(20), orm.adddate, 120) adddate,"; sql = sql + " case orm.state when 0 then '待销售' when 1 then '待发放' when 2 then '已发放' end state,r.Name as roomName,u.RealName from Operation_RoomMain orm"; sql = sql + " left join base_room r on orm.room_id=r.room_id"; sql = sql + " left join Base_User u on u.UserId=orm.adduser"; sql = sql + " and orm.room_id in (select room_id from base_room where user_id='" + user_id + "')"; sql = sql + " order by adddate"; DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql); var JsonData = new { total = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数 page = jqgridparam.page, //当前页码 records = dt.Rows.Count, //总记录数 costtime = CommonHelper.TimerEnd(watch), //查询消耗的毫秒数 rows = dt }; return(JsonData.ToJson()); } catch (Exception) { return(null); } }
public Dictionary <string, object> CauseList(JqGridParam jqgridparam) { var type = HttpContext.Current.Request.Form["type"].ToString(); var key = HttpContext.Current.Request.Form["key"].ToString(); IDatabase database = DataFactory.Database(); IRepository <Module> re = new Repository <Module>(); DataTable data = new DataTable(); string strwhere = " 1=1 "; if (!string.IsNullOrEmpty(type)) { strwhere += " and ParentId=" + type; } if (!string.IsNullOrEmpty(key)) { strwhere += " and (CauseContent like '%" + key + "%' or SuggestionContent like '%" + key + "%')"; } data = re.FindTablePageBySql("select CauseId,ParentId,Code,[CauseContent],SuggestionContent,CreateTime,[CreateUserId],CreateUserName ,Sort,RelatedContent from Sys_CauseSuggestion ", ref jqgridparam); return(new Dictionary <string, object> { { "code", 1 }, { "total", jqgridparam.total }, { "page", jqgridparam.page }, { "records", jqgridparam.records }, { "rows", data }, }); }
public Dictionary <string, object> YearList(JqGridParam jqgridparam) { var datetime = HttpContext.Current.Request.Form["datetime"].ToString(); var factory = HttpContext.Current.Request.Form["factory"].ToString(); var key = HttpContext.Current.Request.Form["key"].ToString(); IDatabase database = DataFactory.Database(); IRepository <Module> re = new Repository <Module>(); DataTable data = new DataTable(); string strwhere = " 1=1 "; if (!string.IsNullOrEmpty(factory)) { strwhere += " and FactorySation='" + factory + "'"; } if (!string.IsNullOrEmpty(datetime)) { strwhere += " and datediff(Year, CreateTime,'" + datetime + "-01-01" + "') = 0"; } if (!string.IsNullOrEmpty(key)) { strwhere += " and Signal like'%" + key + "%'"; } data = re.FindTablePageBySql("SELECT sum(happentimes) as total,[FactorySation],year(CreateTime) as CreateTime,Signal FROM[ElectricPower].[dbo].[M_HitchInfo] where " + strwhere + " group by year(CreateTime),[FactorySation], Signal", ref jqgridparam); return(new Dictionary <string, object> { { "code", 1 }, { "total", jqgridparam.total }, { "page", jqgridparam.page }, { "records", jqgridparam.records }, { "rows", data }, }); }
/// <summary> /// 列表加载 /// </summary> /// <param name="ParameterJson"></param> /// <param name="unit_id"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public string GetSale(string ParameterJson, JqGridParam jqgridparam) { string unit_id = ManageProvider.Provider.Current().CompanyId; string user_id = ManageProvider.Provider.Current().UserId; try { int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; Stopwatch watch = CommonHelper.TimerStart(); string sql = "select om.operationmain_id,r.name as roomName,p.designation,p.name as peopleName,CONVERT(varchar(20), om.adddate, 120) adddate,om.saletype,om.cash,u.RealName,u.RealName from Operation_Main om"; sql = sql + " left join base_room r on om.room_id=r.room_id"; sql = sql + " left join people p on om.People_id=p.People_id"; sql = sql + " left join Base_User u on u.UserId=om.actuser"; sql = sql + " where om.room_id in (select room_id from base_room where user_id='" + user_id + "')"; sql = sql + " and om.room_id <> p.room_id and om.state=0 and p.state = 1 ";//换监室 and 商品未发放 and 在押 sql = sql + " order by operationmain_id"; DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql); var JsonData = new { total = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数 page = jqgridparam.page, //当前页码 records = dt.Rows.Count, //总记录数 costtime = CommonHelper.TimerEnd(watch), //查询消耗的毫秒数 rows = dt }; return(JsonData.ToJson()); } catch (Exception) { return(null); } }
/// <summary> /// 列表加载明细 /// </summary> /// <param name="ParameterJson"></param> /// <param name="unit_id"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public string GetSaleDetail(string ParameterJson, JqGridParam jqgridparam, string KeyValue) { string unit_id = ManageProvider.Provider.Current().CompanyId; string user_id = ManageProvider.Provider.Current().UserId; try { int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; Stopwatch watch = CommonHelper.TimerStart(); string sql = "select g.shortcode ,g.name,g.name ,g.price,case ISNULL(od.goodsnum,'') when '' then od.goodschoosenum else od.goodsnum end as goodsnum from Operation_Detail od"; sql = sql + " left join base_goods g on od.goods_id=g.goods_id"; sql = sql + " where od.operationmain_id='" + KeyValue + "'"; DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql); var JsonData = new { total = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数 page = jqgridparam.page, //当前页码 records = dt.Rows.Count, //总记录数 costtime = CommonHelper.TimerEnd(watch), //查询消耗的毫秒数 rows = dt }; return(JsonData.ToJson()); } catch (Exception) { return(null); } }
/// <summary> /// 获取用户信息列表 /// </summary> /// <param name="parameterJson"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public JsonResult GetUserListByQuery(string parameterJson, JqGridParam jqgridparam) { var model = new QueryUserModel(); if (!string.IsNullOrWhiteSpace(parameterJson)) { model = JsonConvert.DeserializeObject <QueryUserModel>(parameterJson); } model.PageIndex = jqgridparam.page; model.PageSize = jqgridparam.rows; model.UserType = (int)EnumUserType.User; var send = JsonConvert.SerializeObject(model); var result = ServiceHelper.CallService <PagedListData <List <SysUser> > >( ServiceConst.SysUserApi.GetUserListByQuery, send, this.CurrentUser.Token); var json = new { page = result.Data.PagingInfo.PageIndex, total = result.Data.PagingInfo.PageCount, records = result.Data.PagingInfo.TotalCount, rows = result.Data.PagingData.ToArray() }; return(Json(json, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 获取公告内容 /// </summary> /// <param name="keyvalue">关键字条件</param> /// <param name="jqgridparam">JqGrid参数</param> /// <returns>数据集</returns> public DataTable GetAnnouncementPageList(string keyvalue, ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT d.ID, --Id,主键 d.Title, --标题 d.Content, --内容 d.ContentState, --状态 d.CreateTime, --创建时间 d.UpdateTime, --更新时间 d.Remark FROM "); string sqlTableName = "DStudent_Announcement d"; string sqlConditon = " WHERE 1=1"; strSql.Append(sqlTableName); strSql.Append(sqlConditon); if (!string.IsNullOrEmpty(keyvalue)) { strSql.Append(@" AND (d.Title LIKE @keyvalue or d.Content LIKE @keyvalue)"); parameter.Add(DbFactory.CreateDbParameter("@keyvalue", '%' + keyvalue + '%')); } return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 根据公司id获取角色 列表 /// </summary> /// <param name="companyId">公司ID</param> /// <param name="jqgridparam">分页条件</param> /// <returns></returns> public DataTable GetPageList(string companyId, ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT * FROM ( SELECT r.RoleId, r.CompanyId, c.FullName AS CompanyName, r.Code, r.FullName, ISNULL(U.Qty, 0) AS MemberCount, r.Category, r.Enabled, r.SortCode, r.Remark FROM Base_Roles r LEFT JOIN Base_Company c ON c.CompanyId = r.CompanyId LEFT JOIN ( SELECT COUNT(1) AS Qty, ObjectId FROM Base_ObjectUserRelation WHERE Category = '2' GROUP BY ObjectId ) U ON U.ObjectId = r.RoleId ) T WHERE 1 = 1 "); if (!string.IsNullOrEmpty(companyId)) { strSql.Append(" AND CompanyId = @CompanyId"); parameter.Add(DbFactory.CreateDbParameter("@CompanyId", companyId)); } if (!ManageProvider.Provider.Current().IsSystem) { strSql.Append(" AND ( RoleId IN ( SELECT ResourceId FROM Base_DataScopePermission WHERE"); strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') "); strSql.Append(" ) )"); } return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 列表加载 /// </summary> /// <param name="ParameterJson"></param> /// <param name="unit_id"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public string GetPeople(string ParameterJson, JqGridParam jqgridparam) { string unit_id = ManageProvider.Provider.Current().CompanyId; string user_id = ManageProvider.Provider.Current().UserId; try { int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; Stopwatch watch = CommonHelper.TimerStart(); string sql = "select p.People_id,p.designation,r.name as room_id,p.name,p.sex,p.selftype,p.selfmoney,"; sql = sql + " CONVERT(varchar(20), p.startdate, 120) startdate,CONVERT(varchar(20), p.enddate, 120) enddate,p.limit,p.punish,p.note,p.adduser,p.adddate from people p"; sql = sql + " left join base_room r on r.room_id=p.room_id"; sql = sql + " where p.room_id in (select room_id from base_room where user_id='" + user_id + "')"; sql = sql + " order by p.People_id"; DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql); var JsonData = new { total = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数 page = jqgridparam.page, //当前页码 records = dt.Rows.Count, //总记录数 costtime = CommonHelper.TimerEnd(watch), //查询消耗的毫秒数 rows = dt }; return(JsonData.ToJson()); } catch (Exception) { return(null); } }
/// <summary> /// 返回产品栏目列表JSON /// </summary> /// <param name="jqgridparam">表格参数</param> /// <returns></returns> public ActionResult GridPageListJson(JqGridParam jqgridparam, string StartTime, string EndTime, string Keyword, string Stuts, [DefaultValue(0)] int IsAll) { try { Stopwatch watch = CommonHelper.TimerStart(); //DataTable ListData = bll.GetPageList(ref jqgridparam); var ListData = bll.GetPageList1(ref jqgridparam, StartTime, EndTime, Keyword, Stuts, IsAll); var newlist = new List <Fx_Orders>(); foreach (var item in ListData) { item.Arddress = item.Province + item.City + item.County + item.Arddress; newlist.Add(item); } var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = newlist }; return(Content(JsonData.ToJson())); } catch (Exception ex) { Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message); return(null); } }
public Dictionary <string, object> YujingList(JqGridParam jqgridparam) { var timetype = HttpContext.Current.Request.Form["timetype"].ToString(); var datetime = HttpContext.Current.Request.Form["datetime"].ToString(); var times = HttpContext.Current.Request.Form["times"].ToString(); var factory = HttpContext.Current.Request.Form["factory"].ToString(); IDatabase database = DataFactory.Database(); IRepository <Module> re = new Repository <Module>(); DataTable data = new DataTable(); string strwhere = ""; if (!string.IsNullOrEmpty(factory)) { strwhere = " and FactorySation='" + factory + "'"; } //按照月份删选 if (timetype == "0") { data = re.FindTablePageBySql("select [FactorySation],[Signal],sum([HappenTimes]) as total from (SELECT [FactorySation],[Signal] ,[HappenTimes] ,[SignalType], [CreateTime] FROM[ElectricPower].[dbo].[M_HitchInfo] where datediff(month, CreateTime, '" + datetime + "') = 0 " + strwhere + ") a group by[FactorySation],[Signal] having sum([HappenTimes])>" + times + "", ref jqgridparam); } else { data = re.FindTablePageBySql("select [FactorySation],[Signal],sum([HappenTimes]) as total from (SELECT [FactorySation],[Signal] ,[HappenTimes] ,[SignalType], [CreateTime] FROM[ElectricPower].[dbo].[M_HitchInfo] where datediff(day, CreateTime, '" + datetime + "') = 0 " + strwhere + ") a group by[FactorySation],[Signal] having sum([HappenTimes])>" + times + "", ref jqgridparam); } return(new Dictionary <string, object> { { "code", 1 }, { "total", jqgridparam.total }, { "page", jqgridparam.page }, { "records", jqgridparam.records }, { "rows", data }, }); }
/// <summary> /// 返回产品栏目列表JSON /// </summary> /// <param name="jqgridparam">表格参数</param> /// <returns></returns> public ActionResult GridPageListJson(JqGridParam jqgridparam, string Keyword) { try { Stopwatch watch = CommonHelper.TimerStart(); //DataTable ListData = bll.GetPageList(ref jqgridparam); var ListData = bll.GetPageList1(ref jqgridparam, Keyword); var newlist = new List <Fx_ProductClass>(); foreach (var item in ListData) { if (item.IsShow == 1 && ManageProvider.Provider.Current().InnerUser > 2) { continue; //非管理员看不到此项,2为管理员 } item.ClassUrl = "http://" + item.ClassPath + "/" + item.ClassUrl + "?p=" + item.Number + "&u=" + ManageProvider.Provider.Current().UserId; newlist.Add(item); } var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = newlist }; return(Content(JsonData.ToJson())); } catch (Exception ex) { Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message); return(null); } }
/// <summary> /// 获取列表-导出 /// </summary> /// <param name="jqgridparam">分页条件</param> /// <returns></returns> public IList <Am_MoneyDetail> GetPageList(ref JqGridParam jqgridparam, string keywords, string BeginTime, string EndTime) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT * FROM Am_MoneyDetail where 1=1 "); //关键字 if (!string.IsNullOrEmpty(keywords)) { strSql.Append(@" AND (UserName LIKE @keyword OR U_Name LIKE @keyword OR OperateTypeStr LIKE @keyword OR CreateUserName LIKE @keyword)"); parameter.Add(DbFactory.CreateDbParameter("@keyword", '%' + keywords + '%')); } //开始时间 if (!string.IsNullOrEmpty(BeginTime)) { strSql.Append(" AND CreateTime > @StartTime"); parameter.Add(DbFactory.CreateDbParameter("@StartTime", Convert.ToDateTime(BeginTime).ToString("yyyy-MM-dd") + " 00:00:00")); } //结束时间 if (!string.IsNullOrEmpty(EndTime)) { strSql.Append(" AND CreateTime < @EndTime"); parameter.Add(DbFactory.CreateDbParameter("@EndTime", Convert.ToDateTime(EndTime).AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00")); } return(Repository().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
public ActionResult GridProductCalListJson(string keywords, JqGridParam jqgridparam, string ParameterJson) { try { Stopwatch watch = CommonHelper.TimerStart(); StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.AppendFormat(@" select a.PartName,a.ProductNO,a.Feature,a.Customer,a.PictureType,b.* from product a left join CalMethod b on a.ProductID=b.ProductID where 1=1 "); if (!CommonHelper.IsEmpty(keywords)) { strSql.AppendFormat(@" and (partname like '%{0}%' or productno like '%{0}%') ", keywords); } strSql.AppendFormat(@" and a.CompanyID='{0}' ", ManageProvider.Provider.Current().CompanyId); DataTable ListData = Productre.FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam); var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return(Content(JsonData.ToJson())); } catch (Exception ex) { return(null); } }
/// <summary> /// 获取商品列表列表 /// </summary> /// <param name="keyword">查询关键词</param> /// <param name="categoryId">商品分类ID</param> /// <param name="jqgridparam">分页条件</param> /// <returns></returns> public DataTable GetPageList(string keyword, string categoryId, ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append(@"SELECT p.ProductId, p.CategoryId, p.Code, p.ProductName, p.BriefName, p.Weight, p.Volume, p.Specification, p.Brand, p.BarCode, p.Price, p.BaseUnit, p.IsLotControl, p.IsForcedScan, p.Remark, p.Enabled, p.SortCode, p.CreateDate, p.ModifyDate, c.CategoryName FROM Product p LEFT JOIN dbo.Product_Category c ON c.CategoryId = p.CategoryId WHERE 1 = 1"); if (!string.IsNullOrEmpty(keyword)) { strSql.AppendFormat(@" AND (p.Code LIKE '{0}' OR p.ProductName LIKE '{0}' OR p.BriefName LIKE '{0}' OR p.BarCode LIKE '{0}')", '%' + keyword + '%'); } if (!string.IsNullOrEmpty(categoryId) && categoryId != "0") { strSql.Append(" AND p.CategoryId = @CategoryId"); parameter.Add(DbFactory.CreateDbParameter("@CategoryId", categoryId)); } //if (!ManageProvider.Provider.Current().IsSystem) //{ // strSql.Append(" AND ( MerchantId IN ( SELECT ResourceId FROM Base_DataScopePermission WHERE"); // strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') "); // strSql.Append(" ) )"); //} return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 新增监室销售初始(jqgrid方式) /// </summary> /// <param name="ParameterJson"></param> /// <param name="unit_id"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public string AddRoomSale(string ParameterJson, JqGridParam jqgridparam) { string user_id = ManageProvider.Provider.Current().UserId; try { int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; Stopwatch watch = CommonHelper.TimerStart(); string sql = "select p.People_id,p.code,p.name,p.room_id,r.Name as roomName,account,"; sql = sql + " p.limit-(select isnull(sum(cash),0) from Operation_Main where People_id=p.People_id and CONVERT(varchar(7), adddate, 120)=CONVERT(varchar(7), getdate(), 120) group by People_id ) ";//,p.People_id,p.code,p.room_id, sql = sql + " as canuse from People p"; sql = sql + " left join base_room r on p.room_id=r.room_id"; sql = sql + " and p.room_id in (select room_id from base_room where user_id='" + user_id + "')"; sql = sql + " where 1=2 "; sql = sql + " order by People_id"; DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql); var JsonData = new { total = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数 page = jqgridparam.page, //当前页码 records = dt.Rows.Count, //总记录数 costtime = CommonHelper.TimerEnd(watch), //查询消耗的毫秒数 rows = dt }; return(JsonData.ToJson()); } catch (Exception) { return(null); } }
public Dictionary <string, object> PredicSettingList(JqGridParam jqgridparam) { var factory = HttpContext.Current.Request.Form["factory"].ToString(); var key = HttpContext.Current.Request.Form["key"].ToString(); IDatabase database = DataFactory.Database(); IRepository <Module> re = new Repository <Module>(); DataTable data = new DataTable(); string strwhere = " 1=1 "; if (!string.IsNullOrEmpty(factory)) { strwhere += " and FactorySation='" + factory + "'"; } if (!string.IsNullOrEmpty(key)) { strwhere += " and Signal like'%" + key + "%'"; } data = re.FindTablePageBySql("select * from PredicSetting where " + strwhere, ref jqgridparam); return(new Dictionary <string, object> { { "code", 1 }, { "total", jqgridparam.total }, { "page", jqgridparam.page }, { "records", jqgridparam.records }, { "rows", data }, }); }
/// <summary> /// 绑定表格 /// </summary> /// <param name="ParameterJson">查询条件</param> /// <param name="Gridpage">分页条件</param> /// <returns></returns> public virtual JsonResult GridPageJson(string ParameterJson, JqGridParam jqgridparam) { try { Stopwatch watch = CommonHelper.TimerStart(); List <TEntity> ListData = new List <TEntity>(); if (!string.IsNullOrEmpty(ParameterJson)) { List <DbParameter> parameter = new List <DbParameter>(); IList conditions = ParameterJson.JonsToList <Condition>(); string WhereSql = ConditionBuilder.GetWhereSql(conditions, out parameter); ListData = repositoryfactory.Repository().FindListPage(WhereSql, parameter.ToArray(), ref jqgridparam); } else { ListData = repositoryfactory.Repository().FindListPage(ref jqgridparam); } var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return(Json(JsonData, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(null); } }
/// <summary> /// 列表加载 /// </summary> /// <param name="ParameterJson"></param> /// <param name="unit_id"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public string GridPageJsonMyQuery(string ParameterJson, JqGridParam jqgridparam) { //string unit_id = ManageProvider.Provider.Current().CompanyId; try { int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; Stopwatch watch = CommonHelper.TimerStart(); string sql = "SELECT Base_Goods.goods_id ,Base_Goods.code,Base_Goods.shortcode,Base_Goods.name,Base_Goods.shortname, " + "Base_Goods.standand,Base_Goods.unit,Base_GoodsType.name As goodstypename,Base_Goods.price,Base_Goods.islimit,Base_Goods.imgurl,Base_Goods.filetype,Base_Goods.limitnum,Base_Goods.state" + " FROM Base_Goods" + " LEFT JOIN Base_GoodsType" + " ON Base_Goods.goodstype_id=Base_GoodsType.goodstype_id"; DataTable dt = DbHelper.GetDataSet(CommandType.Text, sql).Tables[0];//Repository().FindTableBySql(sql); var JsonData = new { total = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / jqgridparam.rows)), //总页数 page = jqgridparam.page, //当前页码 records = dt.Rows.Count, //总记录数 costtime = CommonHelper.TimerEnd(watch), //查询消耗的毫秒数 rows = dt }; return(JsonData.ToJson()); } catch (Exception) { return(null); } }
/// <summary> /// 订单列表 /// </summary> /// <param name="pickNo">拣货单号</param> /// <param name="startTime">订单开始时间</param> /// <param name="endTime">订单结束时间</param> /// <param name="jqgridparam">分页参数</param> /// <param name="warehouseId"></param> /// <returns></returns> public List <PickMasterViewModel> GetPickMasterList(string warehouseId, string pickNo, string startTime, string endTime, JqGridParam jqgridparam) { var strSql = new StringBuilder(); var parameter = new List <DbParameter>(); strSql.Append(@"SELECT PM.*, W.WarehouseName FROM dbo.Pick_Master PM LEFT JOIN dbo.Warehouse W ON PM.WarehouseId = W.WarehouseId WHERE 1 = 1"); if (!string.IsNullOrEmpty(warehouseId)) { strSql.Append(" AND PM.WarehouseId = @WarehouseId "); parameter.Add(DbFactory.CreateDbParameter("@WarehouseId", warehouseId)); } if (!string.IsNullOrEmpty(pickNo)) { strSql.Append(" AND PM.PickNo = @PickNo "); parameter.Add(DbFactory.CreateDbParameter("@PickNo", pickNo)); } if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime)) { strSql.Append(" AND PM.CreateDate Between @StartTime AND @EndTime "); parameter.Add(DbFactory.CreateDbParameter("@StartTime", CommonHelper.GetDateTime(startTime + " 00:00"))); parameter.Add(DbFactory.CreateDbParameter("@EndTime", CommonHelper.GetDateTime(endTime + " 23:59"))); } return(new Repository <PickMasterViewModel>().FindListPageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 根据公司id获取部门 列表 /// </summary> /// <param name="CompanyId">公司ID</param> /// <returns></returns> public DataTable GetList(ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT * FROM ( SELECT d.DepartmentId , --主键 c.FullName AS CompanyName , --所属公司 d.CompanyId , --所属公司Id d.Code , --编码 d.FullName , --部门名称 d.ShortName , --部门简称 d.Nature , --部门性质 d.Manager , --负责人 d.Phone , --电话 d.Fax , --传真 d.Enabled , --有效 d.SortCode, --排序码 d.Remark --说明 FROM Base_Department d LEFT JOIN Base_Company c ON c.CompanyId = d.CompanyId ) T WHERE 1=1 "); List <DbParameter> parameter = new List <DbParameter>(); return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); //return Repository().FindTableBySql(strSql.ToString(), parameter.ToArray()); }
/// <summary> /// 查询数据列表、返回 DataTable /// </summary> /// <param name="strSql">Sql语句</param> /// <param name="jqgridparam">分页参数</param> /// <returns></returns> public DataTable FindTablePageBySql(string strSql, ref JqGridParam jqgridparam) { string orderField = jqgridparam.sidx; string orderType = jqgridparam.sord; int pageIndex = jqgridparam.page; int pageSize = jqgridparam.rows; int totalRow = jqgridparam.records; DataTable dt = DataFactory.Database().FindTablePageBySql(strSql, orderField, orderType, pageIndex, pageSize, ref totalRow); jqgridparam.records = totalRow; return dt; }
public ActionResult TestTableMegaListJson(JqGridParam jqgridparam) { Stopwatch watch = CommonHelper.TimerStart(); string UserId = ManageProvider.Provider.Current().UserId; DataTable ListData = this.FindTablePageBySql("SELECT TestId, Code, FullName, CreateDate, CreateUserName, Remark FROM TestTable", ref jqgridparam); var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; return Content(JsonData.ToJson()); }
/// <summary> /// 获取数据库表数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="ParameterJson">查询条件</param> /// <param name="jqgridparam">分页参数</param> /// <returns></returns> public DataTable GetDataTableList(string tableName, string ParameterJson, ref JqGridParam jqgridparam) { StringBuilder strSql = new StringBuilder(); List<DbParameter> parameter = new List<DbParameter>(); strSql.Append("SELECT * FROM " + tableName + " WHERE 1=1"); if (!string.IsNullOrEmpty(ParameterJson) && ParameterJson.Length > 2) { strSql.Append(ConditionBuilder.GetWhereSql(ParameterJson.JonsToList<Condition>(), out parameter)); } int totalRow = jqgridparam.records; DataTable dt = DataFactory.Database().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), jqgridparam.sidx, jqgridparam.sord, jqgridparam.page, jqgridparam.rows, ref totalRow); jqgridparam.records = totalRow; return dt; }
/// <summary> /// 查询数据库表数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="ParameterJson">查询条件</param> /// <param name="jqgridparam">分页参数</param> /// <returns></returns> public ActionResult GridDataTableListJson(string tableName, string ParameterJson, JqGridParam jqgridparam) { try { Stopwatch watch = CommonHelper.TimerStart(); DataTable ListData = base_databasebll.GetDataTableList(tableName, ParameterJson, ref jqgridparam); var JsonData = new { total = jqgridparam.total, page = jqgridparam.page, records = jqgridparam.records, costtime = CommonHelper.TimerEnd(watch), rows = ListData, }; string str = JsonData.ToJson(); return Content(JsonData.ToJson()); } catch (Exception ex) { Base_SysLogBll.Instance.WriteLog("", OperationType.Query, "-1", "异常错误:" + ex.Message); return null; } }