//报警数据导出 public ActionResult ExportAlarmData(int pid = 0, string startdate = "", string enddate = "", string typename = "") { string PDRList = HomeController.GetPID(CurrentUser.UNITList); string query = " where 1=1 "; if (pid > 0) { query = query + " and pid=" + pid; } else { //query = query + " and pid in (1,3)"; query = query + " and pid in (" + PDRList + ")"; } if (!typename.Equals("") && !typename.Equals("==全部==")) { query = query + " and AlarmCate='" + typename + "'"; } if (!startdate.Equals("")) { startdate = startdate.Replace('-', '/'); query = query + " and AlarmDateTime>='" + startdate + "'"; } if (!enddate.Equals("")) { enddate = enddate.Replace('-', '/'); query = query + " and AlarmDateTime<='" + enddate + "'"; } string strsql = "select ALarmType 报警级别,AlarmDate 日期,AlarmTime 时间,AlarmCate 报警类型,AlarmValue 数值,AlarmAddress 监测位置,AlarmArea 监测站点,Company 所属单位,AlarmConfirm 确认,UserName 确认人,ConfirmDate 确认时间 from t_AlarmTable_en " + query + " order by AlarmID desc"; DataSet ds = SQLtoDataSet.GetReportSummary(strsql); ExportExcel.doExport2003(ds, "~/DownLoad/alarmdata.xls"); return(Content("/DownLoad/alarmdata.xls")); }
//通讯状态数据导出 public ActionResult ExportAlarmSysTableData(int pid = 0, int alarmstate = -1, string startdate = "", string enddate = "") { string query = " where 1=1 "; string PDRList = HomeController.GetPID(CurrentUser.UNITList); if (pid > 0) { query = query + " and pid=" + pid; } else { query = query + " and pid in (" + PDRList + ")"; } if (alarmstate > -1) { query = query + " and alarmstate= " + alarmstate; } if (!startdate.Equals("")) { //startdate = startdate.Replace('-', '/'); query = query + " and AlarmDateTime>='" + startdate + "'"; } if (!enddate.Equals("")) { //enddate = enddate.Replace('-', '/'); query = query + " and AlarmDateTime<='" + enddate + "'"; } string strsql = "select ALarmType 报警类型,AlarmDateTime 日期,PDRName 站室名称,CASE AlarmState WHEN 0 THEN '通讯正常' ELSE '通讯断开' END 通讯状态 from t_AlarmSysTable_en " + query + " order by AlarmID desc"; DataSet ds = SQLtoDataSet.GetReportSummary(strsql); ExportExcel.doExport2003(ds, "~/DownLoad/alarmsysdata.xls"); return(Content("/DownLoad/alarmsysdata.xls")); }
public ActionResult ExportPointsInfoData(int pid, string tagname, string chinesedesc) { //string pdrlist = CurrentUser.PDRList; string pdrlist = HomeController.GetPID(CurrentUser.UNITList); string query = " where 1=1"; if (pid > 0) { query = query + " and pid=" + pid; } if (!tagname.Equals("")) { query = query + " and tagname like '%" + tagname + "%'"; } if (!chinesedesc.Equals("")) { query = query + " and 中文描述 like '%" + chinesedesc + "%'"; } string strsql = "select 数据类型,中文描述,TagName as 测点名称,站内点号,TagID as 点编号,实时库索引,PID as 站编 ,通信地址,例外报告死区,工程下限,工程上限,码值下限,码值上限,远动数据类型,报警下限1,报警上限1,报警定义,置0说明,置1说明,单位,分组,初始值,最大间隔时间,小信号切除值,报警下限2,报警上限2,报警下限3,报警上限3,报警死区,报警级别,报警方式,速率报警限制,DID as 所属设备号,DataTypeID as 数据类型ID,Position as 测点位置,MPID as 监测位置,UseState as 使用状态,PIOID as 进出线ID,ABCID as ABC相ID,Remarks as 备注,设备点名,传感器SN编码 from t_CM_PointsInfo " + query + " order by TagID desc"; DataSet ds = SQLtoDataSet.GetReportSummary(strsql); ExportExcel.doExport2003(ds, "~/DownLoad/pointsinfodata.xls"); return(Content("/DownLoad/pointsinfodata.xls")); }
/// <summary> /// 获取点表的信息 /// </summary> /// <param name="did">设备编号</param> /// <param name="CPositin">监测位置</param> /// <returns></returns> private DataSet GetPointInfoByDid(string did, string CPositin) { DataSet ds = new DataSet(); try { string sqlStr = string.Empty; if (string.IsNullOrEmpty(did)) { //返回为空 } else { if (string.IsNullOrEmpty(CPositin)) { sqlStr = string.Format("select p.TagID, p.传感器SN编码, p.Position, d.DeviceName, d.PName, pdr.Position as pAddr from t_cm_PointsInfo p left join t_DM_DeviceInfo d on p.DID = d.DID left join t_CM_PDRInfo pdr on d.PID = pdr.PID where p.DiD in ( {0} );", did); ds = SQLtoDataSet.GetReportSummary(sqlStr); } else { sqlStr = string.Format("select p.TagID, p.传感器SN编码, p.Position, d.DeviceName, d.PName, pdr.Position as pAddr from t_cm_PointsInfo p left join t_DM_DeviceInfo d on p.DID = d.DID left join t_CM_PDRInfo pdr on d.PID = pdr.PID where p.DiD in ( {0} ) and p.Position like '%{1}%';", did, CPositin); ds = SQLtoDataSet.GetReportSummary(sqlStr); } } } catch (Exception ex) { throw ex; } return(ds); }
//public t_CM_UserInfo CurrentUser //{ // get { return loginbll.CurrentUser; } //} #region "传感器信息模块" /// <summary> /// 获取传感器信息 /// </summary> /// <param name="SN">传感器编码</param> /// <param name="SType">传感器类型</param> /// <returns></returns> private DataSet GetSensorInfoBySN(string SNS, string SType) { DataSet ds = new DataSet(); try { string sqlStr = string.Empty; if (string.IsNullOrEmpty(SNS)) { //返回为空 } else { if (string.IsNullOrEmpty(SType)) { sqlStr = string.Format("select sv.SN, sv.DevName, sv.DevModel, st.Enable, st.InstallTime from v_BaseProductOrder sv left join t_InstallInfo st on sv.ProductID = st.ProductID where sv.SN in ( {0} );", SNS); ds = SQLtoDataSet.GetSensorListSummary(sqlStr); } else { sqlStr = string.Format("select sv.SN, sv.DevName, sv.DevModel, st.Enable, st.InstallTime from v_BaseProductOrder sv left join t_InstallInfo st on sv.ProductID = st.ProductID where sv.SN in ( {0} ) and sv.DevModel like '%{1}%';", SNS, SType); ds = SQLtoDataSet.GetSensorListSummary(sqlStr); } } } catch (Exception ex) { throw ex; } return(ds); }
//获取光纤实时数据--生成暂态曲线 public ActionResult Getrtmdatatemp(int did, DateTime rectime) { try { string tabname = "t_dts_sm_hisdata_tempdata_" + did.ToString("0000"); string strsql = " select channelsinfoid id,pv text from " + tabname + " where rectime<='" + rectime + "' order by rectime desc LIMIT 0,1"; List <DDLValue> list = SQLtoDataSet.GetMySqlList(strsql); string strTemp = "", strJson = ""; if (list.Count > 0) { strTemp = list[0].text; //strTemp = GZipCompressHelper.Decompress(strTemp).TrimEnd(','); } if (!strTemp.Equals("")) { //点数 strJson = strTemp.Substring(0, strTemp.IndexOf(',')); //点数+通道+时间 strTemp = strTemp.Substring(27); } return(Content(strJson + "|" + strTemp)); } catch (Exception ex) { return(Content("error")); } }
//获取光纤设备 public ActionResult GetDevicesinfo() { //string pdrlist = CurrentUser.PDRList; string pdrlist = HomeController.GetPID(CurrentUser.UNITList); string query = "1=1";// devicesinfoid in (" + pdrlist + ")"; string strsql = " select devicesinfoid id,dname text from t_dts_bi_devicesinfo where " + query; List <DDLValue> list = SQLtoDataSet.GetMySqlList(strsql); string strJson = Common.ComboboxToJson(list); return(Content(strJson)); }
//获取光纤设备名称 public ActionResult GetDeviceName(int pid) { string strsql = " select devicesinfoid id,dname text from t_dts_bi_devicesinfo where devicesinfoid=" + pid; List <DDLValue> list = SQLtoDataSet.GetMySqlList(strsql); string dname = ""; if (list.Count > 0) { dname = list[0].text; } return(Content(dname)); }
//获取光纤通道分区 public ActionResult Getchannelzone(int pid = 1, int did = 1, int showall = 0) { string strsql = " select zoneno id,zonename text from t_dts_bi_channelzone where channelsinfoid=" + did + " and devicesinfoid=" + pid; List <DDLValue> list = SQLtoDataSet.GetMySqlList(strsql); string strJson = Common.ComboboxToJson(list); if (showall > 0) { strJson = AddShowAll(list.Count, strJson, "id", "text"); } return(Content(strJson)); }
//保存测点对照表 public ActionResult SavePointmapdts(t_cm_pointmapdts model) { string result = "OK", strsql = ""; //新增 try { List <t_cm_pointmapdts> list2 = bll.t_cm_pointmapdts.Where(p => p.devicesinfoid == model.devicesinfoid && p.channelsinfoid == model.channelsinfoid && p.zoneno == model.zoneno && p.parentid == 0).ToList(); if (list2.Count == 0 || list2[0].tagid == model.tagid)//判断纤分区已分配是否已经分配 { List <t_cm_pointmapdts> list = bll.t_cm_pointmapdts.Where(p => p.tagid == model.tagid).ToList(); if (list.Count > 0)//修改 { strsql = " select beginIndex id,endIndex text from t_dts_bi_channelzone where zoneno=" + model.zoneno + " and channelsinfoid=" + model.channelsinfoid + " and devicesinfoid=" + model.devicesinfoid; List <DDLValue> listzone = SQLtoDataSet.GetMySqlList(strsql); int startPt = 0, endPt = 0; if (listzone.Count > 0) { startPt = listzone[0].id; endPt = Convert.ToInt32(listzone[0].text); } strsql = "update t_cm_pointmapdts set startPt=" + startPt + ",endPt=" + endPt + ",devicesinfoid=" + model.devicesinfoid + ", dname='" + model.dname + "',channelsinfoid=" + model.channelsinfoid + ",cname='" + model.cname + "',zonename='" + model.zonename + "',zoneno=" + model.zoneno + " where tagid=" + model.tagid; bll.ExecuteStoreCommand(strsql, null); Common.InsertLog("光纤分区对照管理", CurrentUser.UserName, "修改光纤分区对照[测点ID:" + model.tagid + "]"); } else { strsql = "insert into t_cm_pointmapdts values(" + model.pid + "," + model.did + "," + model.tagid + "," + model.devicesinfoid + ",'" + model.dname + "'," + model.channelsinfoid + ",'" + model.cname + "'," + model.zoneno + ",'" + model.zonename + "')"; bll.ExecuteStoreCommand(strsql, null); Common.InsertLog("光纤分区对照管理", CurrentUser.UserName, "新增光纤分区对照[定位点ID:" + model.tagid + "]"); } } else { result = "该光纤分区已分配,请从新分配!"; } } catch (Exception ex) { result = strsql; } return(Content(result)); }
/// <summary> /// 获取SN编码序列 /// </summary> /// <param name="did">设备编号</param> /// <returns></returns> private string GetSNsByDid(string did) { string SNs = string.Empty; try { if (string.IsNullOrEmpty(did)) { SNs = string.Empty; } else { string sqlStr = string.Format("select p.传感器SN编码 from t_cm_PointsInfo p where p.DiD in ( {0} );", did); DataSet ds = new DataSet(); ds = SQLtoDataSet.GetReportSummary(sqlStr); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["传感器SN编码"].ToString())) { //当前传感器SN编码为空 } else { SNs += ds.Tables[0].Rows[i]["传感器SN编码"].ToString() + ","; } } } else { SNs = string.Empty; } } } catch (Exception ex) { throw ex; } return(SNs); }