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);
}
