private bool ChangeWarnState(string warnClass, string warnID, string state)
{
FieldModel where = null;
string sql = "";
//switch (warnClass)
//{
// // yecha、peak、voc、dache
// case "voc":
// where = T_DATA_WARNING.ID == warnID;
// sql = string.Format("update t_data_warning t set t.state = '{0}' where t.id = {1}", state, warnID);
// break;
// case "peak":
// where = T_DATA_WARNING_PEAK.ID == warnID;
// sql = string.Format("update t_data_warning_peak t set t.state = '{0}' where t.id = {1}", state, warnID);
// break;
// case "yecha":
// where = T_WARNING.ID == warnID;
// sql = string.Format("update t_Warning t set t.state = '{0}' where t.id = {1}", state, warnID);
// break;
// case "dache":
// where = T_TRAFFIC_WARNING.ID == warnID;
// sql = string.Format("update t_traffic_warning t set t.state = '{0}' where t.id = {1}", state, warnID);
// break;
//}
int result = SqlModel.Select(sql).Native().ExecuteNonQuery();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
protected override SqlModel GetSqlModel(RequestData data)
{
DateTime stime = data.GetDateTime("stime");
DateTime etime = data.GetDateTime("etime");
etime = etime.AddDays(1).AddSeconds(-1);
string pkIds = data.Get("PKId");
FieldModel where = T_MID_CHECKRECORD.CHECKTIME.BetweenAnd(stime, etime);
//if (!string.IsNullOrEmpty(pkIds))
{
where &= T_BASE_COMPANY_PK.ID.In("'" + string.Join("','", pkIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) + "'");
}
return(SqlModel.Select(T_BASE_COMPANY.NAME.As("COMPANYNAME"), T_BASE_COMPANY_PK.NAME.As("PKNAME"), ("D1").Field("TITLE").As("ITEMTEXT"), ("D2").Field("TITLE").As("SUBITEMTEXT")
, T_MID_CHECKRECORD.RECTIME, T_MID_CHECKRECORD.VALUE, T_MID_CHECKRECORD.CHECKVALUE, T_MID_CHECKRECORD.CHECKTIME, ("D3").Field("TITLE").As("CHECKTYPETEXT")
, BASUSER.TRUENAME.As("CHECKUSERNAME"), T_MID_CHECKRECORD.FJ, T_MID_CHECKRECORD.REMARK)
.From(DB.T_MID_CHECKRECORD)
.LeftJoin(DB.T_BASE_COMPANY_PK_TX).On(T_BASE_COMPANY_PK_TX.MN == T_MID_CHECKRECORD.MN)
.LeftJoin(DB.T_BASE_COMPANY_PK).On(T_BASE_COMPANY_PK.ID == T_BASE_COMPANY_PK_TX.PKID)
.LeftJoin(DB.T_BASE_COMPANY).On(T_BASE_COMPANY.ID == T_BASE_COMPANY_PK.COMPANYID)
.LeftJoin(DB.BASDIC.As("D1")).On(("D1").Field("CODE") == T_MID_CHECKRECORD.ITEMCODE)
.LeftJoin(DB.BASDIC.As("D2")).On(("D2").Field("CODE") == T_MID_CHECKRECORD.SUBITEMCODE & ("D2").Field("TYPECODE") == T_MID_CHECKRECORD.ITEMCODE)
.LeftJoin(DB.BASDIC.As("D3")).On(("D3").Field("CODE") == T_MID_CHECKRECORD.CHECKTYPE & ("D3").Field("TYPECODE") == "CHECKTYPE")
.LeftJoin(DB.BASUSER).On(BASUSER.USERNAME == T_MID_CHECKRECORD.CHECKUSER)
.Where(where));
}
private List <dynamic> GetDataTypeData(RequestData requestData)
{
SqlModel sqlmodel = null;
string sql = @"
select *, a.rowid from {0} a
left join T_BASE_COMANY_PK_TX b on a.devicecode = b.mn
left join T_BASE_COMANY_PK c on b.pkid = c.id
where RECTIME>= to_date('{0}','yyyy/MM/dd hh24:mi:ss')
and RECTIME<=to_date('{2}','yyyy/MM/dd hh24:mi:ss') ";
switch (requestData.Get("DataType"))
{
case "M":
sql = string.Format(sql, "T_MID_MINUTE", requestData.Get("BeginTime"), requestData.Get("EndTime"));
break;
case "H":
sql = string.Format(sql, "T_MID_HOUR", requestData.Get("BeginTime"), requestData.Get("EndTime"));
break;
case "D":
sql = string.Format(sql, "T_MID_DAY", requestData.Get("BeginTime"), requestData.Get("EndTime"));
break;
}
return(SqlModel.Select(sql).ExecToDynamicList());
}
/// <summary>
/// 获取限制比例
/// </summary>
/// <param name="type"></param>
/// <param name="id"></param>
/// <returns></returns>
private decimal LimitValuePercent(string type, string id)
{
string conpanyID = id;
if ("P" == type)
{
conpanyID = SqlModel.Select(T_BASE_COMPANY_PK.COMPANYID).From(DB.T_BASE_COMPANY_PK).Where(T_BASE_COMPANY_PK.ID == id).ExecuteScalar().ToString();
}
ThePeakInfo info = new ThePeakInfo();
T_THEPEAK_ENT_SUB_LISTModel company = info.GetThePeakEnterprise().Find(item => StringHelper.DynamicToString(item.COMPANY_ID) == conpanyID);
if (null == company)
{
return(100);
}
decimal limitValue = 100;
switch (StringHelper.DynamicToString(company.ENT_PEAK_TYPE))
{
case "1": limitValue = 0; break;
case "2": limitValue = company.LIMIT_RATIO.Value; break;
case "3": limitValue = company.LIMIT_RATIO.Value; break;
}
return(limitValue);
}
private static string GetPageButtonHtml(int navId, int userId)
{
object ret = SqlModel.Select(BASUSER.ISADMIN).From(DB.BASUSER).Where(BASUSER.ID == userId).ExecuteScalar();
FieldModel where = BASBUTTON.BUTTONTAG != "browser";
if (ret == null)
{
return("");
}
else if (ret.ToBoolean() == true)
{
where &= BASNAVBTN.NAVID == navId;
}
else
{
DataTable rTab = GetRightButtonTable(navId, userId);
string btnIdStr = rTab.Join((r, i) => (i > 0 ? "," : "") + r[BASUSERNAVBTN.BTNID.Name]);
where &= BASNAVBTN.NAVID == navId& BASBUTTON.ID.In(btnIdStr);
}
DataTable btnTab = SqlModel.Select(BASBUTTON.BUTTONHTML, BASBUTTON.BUTTONHTMLEN)
.From(DB.BASNAVBTN)
.InnerJoin(DB.BASBUTTON).On(BASBUTTON.ID == BASNAVBTN.BTNID).OrderByAsc(BASNAVBTN.SORTNO)
.Where(where).ExecToDataTable();
string split = @"<div class='datagrid-btn-separator'></div>";
List <string> btnList = new List <string>();
btnTab.Each(r => btnList.Add(LangHelper.Name == LangEnum.ZHCN.ToString().ToLower() ?
r[BASBUTTON.BUTTONHTML.Name].ToString() : r[BASBUTTON.BUTTONHTMLEN.Name].ToString()));
return(string.Join(split, btnList.ToArray()));
}
/// <summary>
/// 获取企业（或排口）的名字和编号
/// </summary>
/// <param name="type"></param>
/// <param name="id"></param>
/// <returns></returns>
public static dynamic GetCompanyOrPK(string type, string id)
{
if ("P" == type)
{
SqlModel sqlmodel = SqlModel.Select(T_BASE_COMPANY_PK.CODE, T_BASE_COMPANY_PK.NAME).From(DB.T_BASE_COMPANY_PK).Where(T_BASE_COMPANY_PK.ID == id);
var list = sqlmodel.ExecToDynamicList();
if (list.Count > 0)
{
return(new {
Code = StringHelper.DynamicToString(list[0]["CODE"]),
Name = StringHelper.DynamicToString(list[0]["NAME"]),
});
}
}
else
{
SqlModel sqlmodel = SqlModel.Select(T_BASE_COMPANY.ID, T_BASE_COMPANY.NAME).From(DB.T_BASE_COMPANY).Where(T_BASE_COMPANY.ID == id);
var list = sqlmodel.ExecToDynamicList();
if (list.Count > 0)
{
return(new
{
Code = StringHelper.DynamicToString(list[0]["ID"]),
Name = StringHelper.DynamicToString(list[0]["NAME"]),
});
}
}
return(new
{
Code = "",
Name = ""
});
}
public ActionResult GetBtnColumns(RequestData data)
{
DataTableModel dt = SqlModel.Select(BASBUTTON.BUTTONTEXT, BASBUTTON.BUTTONTAG)
.From(DB.BASBUTTON).OrderByAsc(BASBUTTON.SORTNO).ExecToTableModel();
List <dynamic> list = new List <dynamic>();
if (dt != null && dt.Count > 0)
{
foreach (DataRowModel r in dt)
{
list.Add(new
{
title = r[BASBUTTON.BUTTONTEXT].ToString(),
field = r[BASBUTTON.BUTTONTAG].ToString(),
width = 60,
align = "center",
editor = new
{
type = "checkbox",
options = new
{
on = "√",
off = "x"
}
},
});
}
}
string jsonStr = string.Format("var btns = {0}", Newtonsoft.Json.JsonConvert.SerializeObject(list));
return(Content(jsonStr));
}
public ActionResult GetTreeDicType(RequestData data)
{
List <dynamic> list = SqlModel.Select(BASDICTYPE.ID.As("\"id\""), BASDICTYPE.TITLE.As("\"text\""))
.From(DB.BASDICTYPE).OrderByAsc(BASDICTYPE.SORTNO).ExecToDynamicList();
return(Json(list));
}
public string GetCompanyXYData(RequestData data)
{
DateTime beginTime = DateTime.Now.AddHours(-DateTime.Now.Hour - 1);
DateTime endTime = DateTime.Now;
var where = T_BASE_COMPANY.ISDEL == "0" & T_BASE_COMPANY.ISVOCS == "1" & T_MID_HOUR_C.RECTIME >= beginTime & T_MID_HOUR_C.RECTIME <= endTime& T_MID_HOUR_C.ITEMCODE.In("'a25003','a25005','a24088','a25002'");
var dtXYDatas = SqlModel.Select(T_BASE_COMPANY_PK.NAME.As("NAME"), T_MID_HOUR_C.VALUE.SumAs("VALUE")
, T_MID_CHECKRECORD.RECTIME.MaxAs("maxTime").Decode("null", "未修约", "有修约").As("WARNTYPE"), T_MID_ALERT.ID.MaxAs("maxID").Decode("null", "正常", "有超标").As("STATUS"))
.From(DB.T_MID_HOUR_C)
.InnerJoin(DB.T_BASE_COMPANY_PK_TX)
.On(T_BASE_COMPANY_PK_TX.MN == T_MID_HOUR_C.DEVICECODE)
.InnerJoin(DB.T_BASE_COMPANY_PK)
.On(T_BASE_COMPANY_PK.ID == T_BASE_COMPANY_PK_TX.PKID)
.InnerJoin(DB.T_BASE_COMPANY)
.On(T_BASE_COMPANY.ID == T_BASE_COMPANY_PK_TX.COMPANYID)
.LeftJoin(DB.T_MID_CHECKRECORD)
.On(T_MID_CHECKRECORD.MN == T_MID_HOUR_C.DEVICECODE)
.LeftJoin(DB.T_MID_ALERT)
.On(T_MID_ALERT.ITEMCODE == T_MID_HOUR_C.ITEMCODE & T_MID_ALERT.TXID == T_BASE_COMPANY_PK_TX.ID)
.Where(where)
.GroupBy(T_BASE_COMPANY_PK.NAME)
.ExecToDataTable();
return(dtXYDatas.ToJson());
}
public ActionResult GetManageDeptData(RequestData data)
{
FieldModel where = null;
TableModel table = null;
FieldModel[] m = null;
string powerTypeId = data.Get("powerTypeId");
string areaCode = data.Get("areaCode");
//if (powerTypeId == "1" || powerTypeId == "2")
//{
// m = new FieldModel[2] { T_MANAGE_DEPT_INFO.ID.As("ORGNO"), T_MANAGE_DEPT_INFO.ORGNAME.As("ORGNAME") };
// table = DB.T_MANAGE_DEPT_INFO;
// where = T_MANAGE_DEPT_INFO.POWERTYPE == powerTypeId;
// if (!string.IsNullOrEmpty(areaCode))
// where &= T_MANAGE_DEPT_INFO.AREACODE == areaCode;
//}
//else
//{
// m = new FieldModel[2] { T_COMPANY_INFO.TSNO.As("ORGNO"), T_COMPANY_INFO.TESTSTATION.As("ORGNAME") };
// table = DB.T_COMPANY_INFO;
// if (!string.IsNullOrEmpty(areaCode))
// where = T_COMPANY_INFO.CITY == areaCode;
//}
DataTable dt = SqlModel.Select(m).From(table)
.Where(where).ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
//角色授权
private bool RoleAuthorize(string data)
{
JObject jobj = JObject.Parse(data);
DataTableModel dtm = SqlModel.Select(BASBUTTON.ID, BASBUTTON.BUTTONTEXT, BASBUTTON.BUTTONTAG)
.From(DB.BASBUTTON).ExecToTableModel();
var roleId = jobj["roleId"];
var menus = jobj["menus"];
var navs = menus.Where(m => m["buttons"].Count() > 0);
BASROLENAVBTNModel delModel = new BASROLENAVBTNModel();
delModel.Delete(BASROLENAVBTN.ROLEID == roleId.ToString());
using (TranModel tran = new DBTranModel())
{
//BASROLENAVBTNModel delModel = new BASROLENAVBTNModel();
//delModel.Deleting(tran, BASROLENAVBTN.ROLEID == roleId.ToString());
BASROLENAVBTNModel insModel = null;
DataRowModel rm = null;
foreach (var nav in navs)
{
foreach (var btn in nav["buttons"])
{
rm = dtm.Find(m => m[BASBUTTON.BUTTONTAG].ToString() == btn.ToString());
insModel = new BASROLENAVBTNModel();
insModel.ROLEID = roleId.ToString().ToInt32();
insModel.NAVID = nav["navid"].ToString().ToInt32();
insModel.BTNID = rm[BASBUTTON.ID].ToInt32();
insModel.Inserting(tran);
}
}
return(tran.Execute());
}
}
public ActionResult GetCreateTreeData()
{
SqlModel model = SqlModel.Select(@"select a.area_code,a.area_text,b.area_text parenttext from T_SYS_AREA a left join T_SYS_AREA b on a.parent_code = b.area_code WHERE a.RELATION_TYPE < 4 AND a.Remark = 1 and a.parent_code='410100'").Native();
DataTable dt = model.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
//得到所有用户
public ActionResult GetUsers()
{
DataTable dt = SqlModel.Select(BASUSER.USERNAME, BASUSER.TRUENAME)
.From(DB.BASUSER).OrderByAsc(BASUSER.ID).Where(BASUSER.ISDEL == "0")
.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetCompanyTypeData(RequestData data)
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE, BASDIC.SORTNO)
.From(DB.BASDIC)
.Where(BASDIC.TYPECODE == ConstStrings.CompanyType)
.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetUserTypeData()
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE)
.From(DB.BASDIC)
.Where(BASDIC.TYPECODE == ConstStrings.PowerType).OrderByAsc(BASDIC.SORTNO)
.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetLogTypeData(RequestData data)
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE)
.From(DB.BASDIC)
.LeftJoin(DB.BASDICTYPE).On(BASDICTYPE.ID == BASDIC.TYPEID)
.Where(BASDICTYPE.CODE == ConstStrings.LogType).ExecToDataTable();
return(Json(dt.AddNoneSelectItem(" ", "全部").ToDynamicList()));
}
/// <summary>
/// 获取系统信息
/// </summary>
/// <returns></returns>
public ActionResult GetSysData()
{
var dic = new Dictionary <string, object>();
dic.Add("time", SqlModel.Select().Native().DataBaseTime);
dic.Add("username", CurrentUser.UserName);
dic.Add("usertruename", CurrentUser.UserTrueName);
return(Json(dic));
}
public ActionResult GetDicCodeData(string typeCode)
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE)
.From(DB.BASDIC)
.Where(BASDIC.TYPECODE == typeCode).OrderByAsc(BASDIC.SORTNO)
.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetAreaDataByParent(RequestData data)
{
string parentCode = data.Get("parentCode");
DataTable dt = SqlModel.Select(T_SYS_AREA.AREA_CODE.As("CODE"), T_SYS_AREA.AREA_TEXT.As("TITLE")).From(DB.T_SYS_AREA)
.Where(T_SYS_AREA.PARENT_CODE == parentCode).OrderByAsc(T_SYS_AREA.ID).ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetFuelSpecData()
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE)
.From(DB.BASDIC)
.Where(BASDIC.TYPECODE == ConstStrings.FuelSpec)
.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetIllegalRealStatusData()
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE)
.From(DB.BASDIC)
.Where(BASDIC.TYPECODE == ConstStrings.IllegalDealType)
.ExecToDataTable();
return(Json(dt.AddNoneSelectItem(" ", "全部").ToDynamicList()));
}
private static string GetSmsTemplate(eTemplateType tempType)
{
object content = SqlModel.Select(SMSTEMPLATE.TEMPCONTENT).From(DB.SMSTEMPLATE)
.Where(SMSTEMPLATE.SMSTEMPTYPE == (int)tempType
& SMSTEMPLATE.ISUSE == "1")
.ExecuteScalar();
return(content == null ? "" : content.ToString());
}
public ActionResult GetPKTreeByKeyword(string keyWord, string companyOnly = "0")
{
string strSQL = string.Format("select 'P'||t.id id,t.name text,t.type,'C'||t.companyid parentid from t_base_company_pk t where t.name is not null {0}", string.IsNullOrEmpty(keyWord) ? "" : " and t.name like '%" + keyWord + "%' ");
DataTable dtPK = SqlModel.Select(strSQL).Native().ExecToDataTable();
strSQL = string.Format("select 'C'||t.id id,t.name text,t.gzcd type from t_base_company t where t.name is not null {0}", string.IsNullOrEmpty(keyWord) ? "" : " and t.name like '%" + keyWord + "%'");
List <dynamic> listCompany = SqlModel.Select(strSQL).Native().ExecToDynamicList();
DataTable dtResult = new DataTable();
dtResult.Columns.Add("ID");
dtResult.Columns.Add("TEXT");
dtResult.Columns.Add("checked");
dtResult.Columns.Add("PARENTID");
dtResult.Columns.Add("TYPE");
foreach (dynamic company in listCompany)
{
DataRow[] rows = dtPK.Select(" parentid='" + company["ID"] + "'");
if (rows == null || rows.Length == 0)
{
continue;
}
DataRow newRow = dtResult.NewRow();
newRow["ID"] = company["ID"];
newRow["TEXT"] = company["TEXT"];
newRow["checked"] = false;
newRow["TYPE"] = company["TYPE"];
newRow["PARENTID"] = "-1";
dtResult.Rows.Add(newRow);
if (companyOnly == "1")
{
continue;
}
foreach (DataRow row in rows)
{
newRow = dtResult.NewRow();
newRow["ID"] = row["ID"];
newRow["TEXT"] = row["TEXT"];
newRow["checked"] = false;
newRow["PARENTID"] = row["PARENTID"];
newRow["TYPE"] = row["TYPE"];
dtResult.Rows.Add(newRow);
}
}
DataRow r = dtResult.NewRow();
r["id"] = -1;
r["parentId"] = 0;
r["type"] = "";
r["text"] = dtResult.Rows.Count > 0 ? (companyOnly == "1" ? "请选择企业" : "请选择排口") : "无结果";
dtResult.Rows.InsertAt(r, 0);
return(Json(ToDynamicComboTree(dtResult, "0", "TYPE")));
}
/// <summary>
/// 获取区域信息
/// </summary>
/// <param name="areaCode"></param>
/// <returns></returns>
private static List <dynamic> GetAreaInfo(string areaCode)
{
SqlModel sql = SqlModel.Select(
T_SYS_AREA.AREA_CODE
, T_SYS_AREA.AREA_TEXT
)
.From(DB.T_SYS_AREA).Where(T_SYS_AREA.AREA_CODE.In(StringHelper.SqlInCondition(areaCode)));
return(sql.ExecToDynamicList());
}
private List <decimal> GetCompanyIDs(FieldModel where)
{
var dtCompany = SqlModel.Select(T_BASE_COMPANY.ID)
.From(DB.T_BASE_COMPANY)
.Where(where)
.ExecToDataTable();
var lstID = dtCompany.AsEnumerable().Select(c => c.Field <decimal>("ID")).ToList();
return(lstID);
}
/// <summary>
/// 获取行业信息
/// </summary>
/// <param name="areaCode"></param>
/// <returns></returns>
private static List <dynamic> GetAreaInfo(string industryCode)
{
SqlModel sql = SqlModel.Select(
BASDIC.CODE
, BASDIC.TITLE
)
.From(DB.BASDIC).Where(BASDIC.CODE.In(StringHelper.SqlInCondition(industryCode)));
return(sql.ExecToDynamicList());
}
public ActionResult GetMenu(int id)
{
DataTable urTab = SqlModel.Select(BASUSERROLE.ROLEID).From(DB.BASUSERROLE)
.Where(BASUSERROLE.USERID == id).ExecToDataTable();
string roleIdStr = urTab.Join((r, i) => (i > 0 ? "," : "") + r[BASUSERROLE.ROLEID.Name]);
var json = this.GetNavBtnJson(roleIdStr, id);
string jsonStr = Newtonsoft.Json.JsonConvert.SerializeObject(json);
return(Content(jsonStr));
}
public ActionResult GetDriveModeData()
{
DataTable dt = SqlModel.Select(BASDIC.CODE, BASDIC.TITLE)
.From(DB.BASDIC)
//.LeftJoin(DB.BASDICTYPE).On(BASDICTYPE.ID == BASDIC.TYPEID)
.Where(BASDIC.TYPECODE == ConstStrings.DriveMode)
.ExecToDataTable();
return(Json(dt.ToDynamicList()));
}
public ActionResult GetPeakLevelData(RequestData data)
{
FieldModel where = null;
// string relationType = data.Get("userTypeId");
var sqlDynamicList = SqlModel.Select(T_THEPEAK_LEVEL_LIST_INFO.PEAK_LEVEL.As("CODE"), "d1".Field("TITLE")).From(DB.T_THEPEAK_LEVEL_LIST_INFO)
.LeftJoin(DB.BASDIC.As("d1")).On(T_THEPEAK_LEVEL_LIST_INFO.PEAK_LEVEL == "d1".Field("CODE") & "d1".Field("TYPECODE") == ConstStrings.LevelType)
.Where(where).ExecToDynamicList();
return(Json(sqlDynamicList));
}
public ActionResult GetUserRole(int userId)
{
List <dynamic> list = SqlModel.Select(BASROLE.ID, BASROLE.ROLENAME, BASROLE.REMARK)
.From(DB.BASUSERROLE)
.LeftJoin(DB.BASROLE).On(BASROLE.ID == BASUSERROLE.ROLEID)
.Where(BASUSERROLE.USERID == userId)
.ExecToDynamicList();
return(Json(list));
}