//通讯状态数据导出
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 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 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);
}
/// <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);
}