public ActionResult GetCompanyList(RequestData data) { FieldModel where = null; List <dynamic> filterRuleslist = Newtonsoft.Json.JsonConvert.DeserializeObject <List <dynamic> >(data.Get("filterRules")); if (filterRuleslist != null) { foreach (var item in filterRuleslist) { this.SetWhere <T_BASE_COMPANY>(item, new T_BASE_COMPANY(), ref where); } } SqlModel subSql = SqlModel.Select("t".Field("ID"), "t".Field("TYPE").WmConcat().As("POLLUTION_ITEM_TEXT")).From( SqlModel.Select(T_BASE_COMPANY.ID, T_BASE_COMPANY_PK.TYPE).From(DB.T_BASE_COMPANY) .LeftJoin(DB.T_BASE_COMPANY_PK).On(T_BASE_COMPANY.ID == T_BASE_COMPANY_PK.COMPANYID) .GroupBy(T_BASE_COMPANY.ID, T_BASE_COMPANY_PK.TYPE) .As("t") ) .GroupBy("t".Field("ID")); //where &= T_BASE_COMPANY.IS_OVERALL_INFO == "1"; SqlModel sql = SqlModel.SelectAll( T_SYS_AREA.AREA_TEXT.As("AREA_TEXT") , "d1".Field("TITLE").As("INDUSTRY_TYPE_TEXT") , "d2".Field("TITLE").As("POLLUTION_TYPE_TEXT") , "sub1".Field("POLLUTION_ITEM_TEXT") //, "d3".Field(T_BAS_AIR_MONITOR.MONITOR_ITEM_NAME).As("POLLUTION_ITEM_TEXT") ) .From(DB.T_BASE_COMPANY) .LeftJoin(DB.T_SYS_AREA).On(T_BASE_COMPANY.AREA == T_SYS_AREA.AREA_CODE) .LeftJoin(DB.BASDIC.As("d1")).On(T_BASE_COMPANY.BASTYPE == "d1".Field("CODE") & "d1".Field("TYPECODE") == ConstStrings.IndustryType) .LeftJoin(DB.BASDIC.As("d2")).On(T_BASE_COMPANY.COMPANYTYPE == "d2".Field("CODE") & "d2".Field("TYPECODE") == ConstStrings.PollutionType) .LeftJoin(subSql.As("sub1")).On("sub1".Field("ID") == T_BASE_COMPANY.ID) //.LeftJoin(DB.T_BAS_AIR_MONITOR.As("d3")).On(T_COMPANY.POLLUTION_ITEM == "d3".Field(T_BAS_AIR_MONITOR.MONITOR_ITEM_CODE)) .Where(where) .OrderByDesc(T_BASE_COMPANY.ID) ; ; return(base.PagedJson(sql, data)); }
// // GET: /Bas/SmsTemplate/ protected override SqlModel GetSqlModel(RequestData data) { FieldModel where = null; string SMSTEMPTYPE = data.Get("TEMPTYPE").Trim(); if (SMSTEMPTYPE != "") { where &= SMSTEMPLATE.SMSTEMPTYPE == SMSTEMPTYPE; } DateTime dtNow = DateTime.Now; DateTime tmStart = new DateTime(dtNow.Year, dtNow.Month, 1); DateTime tmEnd = dtNow.Date; if (!string.IsNullOrEmpty(data.Get("opBegDate"))) { DateTime.TryParse(data.Get("opBegDate"), out tmStart); } if (!string.IsNullOrEmpty(data.Get("opEndDate"))) { DateTime.TryParse(data.Get("opEndDate"), out tmEnd); } tmEnd = tmEnd.AddHours(23).AddMinutes(59).AddSeconds(59); where &= SMSLOG.SENDTIME.BetweenAnd(tmStart, tmEnd); SqlModel sqlmode = SqlModel.SelectAll( BASDIC.TITLE.As("SMSTEMPTYPE_TEXT"), "u".Field(BASUSER.TRUENAME).ListAgg("u".Field(BASUSER.TRUENAME).ToChar(), ',').As("USERNAME_TEXT") ) .From(DB.SMSLOG) .LeftJoin(DB.BASDIC).On(SMSLOG.SMSTEMPTYPE == BASDIC.CODE & BASDIC.TYPECODE == "SMSTEMPTYPE") .LeftJoin(DB.BASUSER.As("u")).On(SMSLOG.RECEIVEUSER.Instr("u".Field(BASUSER.MOBILE).ToChar(), ',')) .Where(where).OrderByDesc(SMSLOG.SENDTIME) .GroupBy( SMSLOG.ID, SMSLOG.CONTENT, SMSLOG.STATUS, SMSLOG.SENDTIME, SMSLOG.RECEIVEUSER, SMSLOG.SMSTEMPTYPE, SMSLOG.SMSBASTYPE, BASDIC.TITLE, "u".Field(BASUSER.TRUENAME) ); if (!string.IsNullOrEmpty(data.Get("USERNAME_TEXT"))) { FieldModel field = new FieldModel(); field.Name = "SEND_PERSON_TEXT"; field.TableName = "t"; sqlmode = SqlModel.Select(new FieldModel[] { new FieldModel() { Name = SMSLOG.ID.Name, TableName = "t" }, new FieldModel() { Name = SMSLOG.CONTENT.Name, TableName = "t" }, new FieldModel() { Name = SMSLOG.STATUS.Name, TableName = "t" }, new FieldModel() { Name = SMSLOG.SENDTIME.Name, TableName = "t" }, new FieldModel() { Name = SMSLOG.RECEIVEUSER.Name, TableName = "t" }, new FieldModel() { Name = SMSLOG.SMSTEMPTYPE.Name, TableName = "t" }, new FieldModel() { Name = "USERNAME_TEXT", TableName = "t" } }).From(sqlmode.As("t")).Where(field.Like(data.Get("USERNAME_TEXT"))); } return(sqlmode); }