/// <summary> /// 根据提供的分机号获取设备信息 /// </summary> /// <param name="extenNo"></param> /// <returns></returns> public static Device GetDevice(string extenNo, string geoid = null) { Device device = null; #region 获取设备信息 var sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT * FROM (SELECT A.DeviceID,A.DeviceName,A.DeviceType,"); sqlBuilder.Append("A.DeviceRegCode,A.FactoryNum,A.PhoneLevel,A.DeviceTypeInfo,"); sqlBuilder.Append("A.DeviceIP,A.DevicePort,A.SoftVersion,A.HardVersion,A.Manufacturer,A.GeoId,A.GroupID,"); sqlBuilder.Append("B.ExtensionID AS ExtenID,B.ExtensionNo AS ExtenNO,B.StateID AS StateID,B.PhoneState "); sqlBuilder.Append("FROM ipvt_deviceinfotable AS A,ipvt_extensionmessagetable AS B "); sqlBuilder.Append("WHERE B.ExtensionNo=?extenNo AND A.ExtensionID=B.ExtensionID "); if (!string.IsNullOrEmpty(geoid)) { sqlBuilder.Append(" AND A.GroupID= " + geoid); } sqlBuilder.Append(") AS C "); //sqlBuilder.Append(" LEFT JOIN ipvt_geoinfotable AS D ON C.GeoId=D.GeoID "); sqlBuilder.Append(@" LEFT JOIN ipvt_geoinfotable AS D ON C.GeoId=D.GeoID LEFT JOIN ipvt_panelinfotable as E on C.ExtenID=E.ExtensionID ORDER BY ExtenID,E.PanelNum"); var param = new MySqlParameter[1]; param[0] = new MySqlParameter("?extenNo", Convert.ToInt32(extenNo)); MySqlDataReader reader = null; try { reader = CustomMySqlHelper.ExecuteDataReader(sqlBuilder.ToString(), param); if (reader != null) { while (reader.Read()) { if (device == null) { device = new Device(); device.Name = EvaluationHelper.ObjectToString(reader["DeviceName"]); device.Id = EvaluationHelper.ObjectToInt(reader["DeviceID"]); device.Type = EvaluationHelper.ObjectToInt(reader["DeviceType"]); device.Ip = EvaluationHelper.ObjectToString(reader["DeviceIP"]); device.PhoneLevel = EvaluationHelper.ObjectToInt(reader["PhoneLevel"]); device.RegistCode = EvaluationHelper.ObjectToString(reader["DeviceRegCode"]); device.SoftVersion = EvaluationHelper.ObjectToString(reader["SoftVersion"]); device.HardVersion = EvaluationHelper.ObjectToString(reader["HardVersion"]); device.DeviceTypeInfo = EvaluationHelper.ObjectToString(reader["DeviceTypeInfo"]); if (string.IsNullOrEmpty(device.DeviceTypeInfo) || device.DeviceTypeInfo == "IP-Center" || device.DeviceTypeInfo == "IP-Phone") { device.Generation = 1; } else { device.Generation = 2; } device.ColorString = GetColor(device.DeviceTypeInfo); if (reader["GroupID"] != DBNull.Value) { device.GroupId = EvaluationHelper.ObjectToInt(reader["GroupID"]); } device.Port = EvaluationHelper.ObjectToInt(reader["DevicePort"]); device.Extension = new Extension { Number = EvaluationHelper.ObjectToString(reader["ExtenNO"]), Id = EvaluationHelper.ObjectToInt(reader["ExtenID"]), State = StateManager.GetState(EvaluationHelper.ObjectToInt(reader["StateID"])), PhoneState = EvaluationHelper.ObjectToInt(reader["PhoneState"]) == 0 ? State.STAT_INVALID : State.STAT_DEVICE_TALKING }; if (string.IsNullOrEmpty(device.Name)) //如果名称为空给设备添加默认名称=分机号 { device.Name = EvaluationHelper.ObjectToString(reader["ExtenNO"]); } if (reader["GeoId"] != DBNull.Value) { var pnt = new GeoPoint(); pnt.Id = EvaluationHelper.ObjectToInt(reader["GeoID"]); pnt.Name = EvaluationHelper.ObjectToString(reader["Name"]); pnt.Address = EvaluationHelper.ObjectToString(reader["FormattedAddress"]); pnt.Phone = EvaluationHelper.ObjectToString(reader["phone"]); pnt.Latitude = EvaluationHelper.ObjectToDouble(reader["Latitude"]); pnt.Longitude = EvaluationHelper.ObjectToDouble(reader["Longitude"]); pnt.Note = EvaluationHelper.ObjectToString(reader["Note"]); device.GeoPoint = pnt; } else { device.GeoPoint = new GeoPoint(); } device.Manufacturer = EvaluationHelper.ObjectToString(reader["Manufacturer"]); if (!string.IsNullOrEmpty(EvaluationHelper.ObjectToString(reader["PanelID"]))) { var panel = new PanelDevice(); //面板对象 panel.Id = EvaluationHelper.ObjectToInt(reader["PanelID"]); //面板号 panel.Number = EvaluationHelper.ObjectToInt(reader["PanelNum"]); //面板号 int stateNum = EvaluationHelper.ObjectToInt(reader["PanelState"]); //面板状态号 switch (stateNum) { case 0: panel.LineState = State.STAT_DEVICE_ONLINE; //在线 break; case 1: panel.LineState = State.STAT_DEVICE_OFFLINE; //离线 break; } int alarmState = EvaluationHelper.ObjectToInt(reader["TamperAlarm"]); if (alarmState == 2) { panel.State = State.STAT_ANTI_DISMANTLE_ALARM; //防拆警报 } else { panel.State = panel.LineState; } panel.Name = EvaluationHelper.ObjectToString(reader["PanelName"]); //面板名 if (string.IsNullOrEmpty(panel.Name)) { panel.Name = string.Format("面板{0}", panel.Number); } if (device.Extension.State == State.STAT_DEVICE_OFFLINE) { panel.State = State.STAT_DEVICE_OFFLINE; } else if (panel.State == State.STAT_ANTI_DISMANTLE_ALARM) { device.Extension.AlarmState = State.STAT_ANTI_DISMANTLE_ALARM; } device.Panels.Add(panel); } } else { if (!string.IsNullOrEmpty(EvaluationHelper.ObjectToString(reader["PanelID"]))) { var panel = new PanelDevice(); //面板对象 panel.Id = EvaluationHelper.ObjectToInt(reader["PanelID"]); //面板号 panel.Number = EvaluationHelper.ObjectToInt(reader["PanelNum"]); //面板号 int stateNum = EvaluationHelper.ObjectToInt(reader["PanelState"]); //面板状态号 switch (stateNum) { case 0: panel.LineState = State.STAT_DEVICE_ONLINE; //在线 break; case 1: panel.LineState = State.STAT_DEVICE_OFFLINE; //离线 break; } int alarmState = EvaluationHelper.ObjectToInt(reader["TamperAlarm"]); if (alarmState == 2) { panel.State = State.STAT_ANTI_DISMANTLE_ALARM; //防拆警报 } else { panel.State = panel.LineState; } panel.Name = EvaluationHelper.ObjectToString(reader["PanelName"]); //面板名 if (string.IsNullOrEmpty(panel.Name)) { panel.Name = string.Format("面板{0}", panel.Number); } if (device.Extension.State == State.STAT_DEVICE_OFFLINE) { panel.State = State.STAT_DEVICE_OFFLINE; } else if (panel.State == State.STAT_ANTI_DISMANTLE_ALARM) { device.Extension.AlarmState = State.STAT_ANTI_DISMANTLE_ALARM; } device.Panels.Add(panel); } } //break; } } } catch (Exception ex) { LogHelper.MainLog("error int GetDevice(int extenNo)!" + ex.StackTrace); } finally { if (reader != null) { reader.Close(); } } #endregion //#region 获取设备的面板信息 //if (device != null) //{ // List<PanelDevice> list; // GetPanels(device.Extension.Id, out list); // //LogHelper.MainLog(string.Format("in GetDevice(string extenNo),ExtensionNumber:{1} --- PanelNum:{0}", list.Count, device.Extension.Number)); // foreach (PanelDevice panel in list) // { // if (panel.State == State.STAT_ANTI_DISMANTLE_ALARM) // { // device.Extension.AlarmState = State.STAT_ANTI_DISMANTLE_ALARM; // } // device.Panels.Add(panel); // } //} //#endregion return(device); }
/// <summary> /// 根据提供的组id获取设备信息,id为-1表示获取所有 /// </summary> /// <param name="devices">out 设备集合</param> /// <param name="groupid">组id</param> public static void GetDevices(out List <Device> devices, int groupid = -1, string geoid = null, string deviceid = null) { LogHelper.MainLog("GetDevices1 time:" + DateTime.Now.TimeOfDay); devices = new List <Device>(); var points = new List <GeoPoint>(); DataSet set; #region sql语句 StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT * FROM (SELECT A.DeviceID,A.DeviceName,A.DeviceType,"); sqlBuilder.Append("A.DeviceRegCode,A.FactoryNum,A.PhoneLevel,A.DeviceTypeInfo,"); sqlBuilder.Append("A.DeviceIP,A.DevicePort,A.SoftVersion,A.HardVersion,A.Manufacturer,A.GeoId,A.GroupID,"); sqlBuilder.Append("B.ExtensionID AS ExtenID,B.ExtensionNo AS ExtenNO,B.StateID AS StateID,B.PhoneState,B.PanelNum "); sqlBuilder.Append("FROM ipvt_deviceinfotable AS A left join ipvt_extensionmessagetable AS B "); sqlBuilder.Append("on A.ExtensionID=B.ExtensionID where 1=1"); if (groupid != -1) { sqlBuilder.Append(" AND A.GroupID=" + groupid); } if (!string.IsNullOrEmpty(geoid)) { sqlBuilder.Append(" AND (A.GroupID=" + geoid + " or A.DeviceIP='" + deviceid + "' )"); } sqlBuilder.Append(") AS C "); sqlBuilder.Append(@" LEFT JOIN ipvt_geoinfotable AS D ON C.GeoId=D.GeoID LEFT JOIN ipvt_panelinfotable as E on C.ExtenID=E.ExtensionID ORDER BY ExtenID,E.PanelNum"); #endregion #region 获取设备集 try { set = CustomMySqlHelper.ExecuteDataSet(sqlBuilder.ToString()); if (set.Tables.Count > 0) { DataRowCollection rows = set.Tables[0].Rows; foreach (DataRow row in rows) { int id = EvaluationHelper.ObjectToInt(row["DeviceID"]); if (!devices.Exists(m => m.Id == id)) { var device = new Device(); device.Name = EvaluationHelper.ObjectToString(row["DeviceName"]); device.Id = EvaluationHelper.ObjectToInt(row["DeviceID"]); device.Type = EvaluationHelper.ObjectToInt(row["DeviceType"]); device.Ip = EvaluationHelper.ObjectToString(row["DeviceIP"]); device.PhoneLevel = EvaluationHelper.ObjectToInt(row["PhoneLevel"]); device.RegistCode = EvaluationHelper.ObjectToString(row["DeviceRegCode"]); device.SoftVersion = EvaluationHelper.ObjectToString(row["SoftVersion"]); device.HardVersion = EvaluationHelper.ObjectToString(row["HardVersion"]); device.DeviceTypeInfo = EvaluationHelper.ObjectToString(row["DeviceTypeInfo"]); if (string.IsNullOrEmpty(device.DeviceTypeInfo) || device.DeviceTypeInfo == "IP-Center" || device.DeviceTypeInfo == "IP-Phone") { device.Generation = 1; } else { device.Generation = 2; } device.ColorString = GetColor(device.DeviceTypeInfo); if (row["GroupID"] != DBNull.Value) { device.GroupId = EvaluationHelper.ObjectToInt(row["GroupID"]); } device.Port = EvaluationHelper.ObjectToInt(row["DevicePort"]); device.Extension = new Extension { Number = EvaluationHelper.ObjectToString(row["ExtenNO"]), Id = EvaluationHelper.ObjectToInt(row["ExtenID"]), State = StateManager.GetState(EvaluationHelper.ObjectToInt(row["StateID"])), PhoneState = EvaluationHelper.ObjectToInt(row["PhoneState"]) == 0 ? State.STAT_INVALID : State.STAT_DEVICE_TALKING, PanelNum = EvaluationHelper.ObjectToString(row["PanelNum"]) }; if (string.IsNullOrEmpty(device.Name)) //如果名称为空给设备添加默认名称=分机号 { device.Name = EvaluationHelper.ObjectToString(row["ExtenNO"]); } GeoPoint pnt = new GeoPoint(); if (row["GeoId"] != DBNull.Value) { pnt.Id = EvaluationHelper.ObjectToInt(row["GeoId"]); bool flag = false; foreach (var p in points) { if (pnt.Equals(p)) { pnt = p; flag = true; break; } } if (!flag) { pnt.Id = EvaluationHelper.ObjectToInt(row["GeoID"]); pnt.Name = EvaluationHelper.ObjectToString(row["Name"]); pnt.Address = EvaluationHelper.ObjectToString(row["FormattedAddress"]); pnt.Phone = EvaluationHelper.ObjectToString(row["phone"]); pnt.Latitude = EvaluationHelper.ObjectToDouble(row["Latitude"]); pnt.Longitude = EvaluationHelper.ObjectToDouble(row["Longitude"]); pnt.Note = EvaluationHelper.ObjectToString(row["Note"]); } } device.GeoPoint = pnt; device.Manufacturer = EvaluationHelper.ObjectToString(row["Manufacturer"]); if (!string.IsNullOrEmpty(EvaluationHelper.ObjectToString(row["PanelID"]))) { var panel = new PanelDevice(); //面板对象 panel.Id = EvaluationHelper.ObjectToInt(row["PanelID"]); //面板号 panel.Number = EvaluationHelper.ObjectToInt(row["PanelNum1"]); //面板号 int stateNum = EvaluationHelper.ObjectToInt(row["PanelState"]); //面板状态号 switch (stateNum) { case 0: panel.LineState = State.STAT_DEVICE_ONLINE; //在线 break; case 1: panel.LineState = State.STAT_DEVICE_OFFLINE; //离线 break; } int alarmState = EvaluationHelper.ObjectToInt(row["TamperAlarm"]); if (alarmState == 2) { panel.State = State.STAT_ANTI_DISMANTLE_ALARM; //防拆警报 } else { panel.State = panel.LineState; } panel.Name = EvaluationHelper.ObjectToString(row["PanelName"]); //面板名 if (string.IsNullOrEmpty(panel.Name)) { panel.Name = string.Format("面板{0}", panel.Number); } if (device.Extension.State == State.STAT_DEVICE_OFFLINE) { panel.State = State.STAT_DEVICE_OFFLINE; } else if (panel.State == State.STAT_ANTI_DISMANTLE_ALARM) { device.Extension.AlarmState = State.STAT_ANTI_DISMANTLE_ALARM; } device.Panels.Add(panel); } devices.Add(device); } else { if (!string.IsNullOrEmpty(EvaluationHelper.ObjectToString(row["PanelID"]))) { Device device = devices.Find(m => m.Id == id); var panel = new PanelDevice(); //面板对象 panel.Id = EvaluationHelper.ObjectToInt(row["PanelID"]); //面板号 panel.Number = EvaluationHelper.ObjectToInt(row["PanelNum1"]); //面板号 int stateNum = EvaluationHelper.ObjectToInt(row["PanelState"]); //面板状态号 switch (stateNum) { case 0: panel.LineState = State.STAT_DEVICE_ONLINE; //在线 break; case 1: panel.LineState = State.STAT_DEVICE_OFFLINE; //离线 break; } int alarmState = EvaluationHelper.ObjectToInt(row["TamperAlarm"]); if (alarmState == 2) { panel.State = State.STAT_ANTI_DISMANTLE_ALARM; //防拆警报 } else { panel.State = panel.LineState; } panel.Name = EvaluationHelper.ObjectToString(row["PanelName"]); //面板名 if (string.IsNullOrEmpty(panel.Name)) { panel.Name = string.Format("面板{0}", panel.Number); } if (device.Extension.State == State.STAT_DEVICE_OFFLINE) { panel.State = State.STAT_DEVICE_OFFLINE; } else if (panel.State == State.STAT_ANTI_DISMANTLE_ALARM) { device.Extension.AlarmState = State.STAT_ANTI_DISMANTLE_ALARM; } device.Panels.Add(panel); } } } } } catch (Exception ex) { LogHelper.MainLog("error int GetDevices!" + ex); } #endregion LogHelper.MainLog("GetDevices2 time:" + DateTime.Now.TimeOfDay); //#region 获取设备的面板信息 //foreach (Device dev in devices) //{ // List<PanelDevice> list; // GetPanels(dev.Extension.Id, out list); // //LogHelper.MainLog(string.Format("GetDevices(int groupid = -1),ExtensionNumber:{1} --- PanelNum:{0}", list.Count, dev.Extension.Number)); // foreach (PanelDevice panel in list) // { // if (dev.Extension.State == State.STAT_DEVICE_OFFLINE) // { // panel.State = State.STAT_DEVICE_OFFLINE; // } // else if (panel.State == State.STAT_ANTI_DISMANTLE_ALARM) // { // dev.Extension.AlarmState = State.STAT_ANTI_DISMANTLE_ALARM; // } // dev.Panels.Add(panel); // } //} //#endregion //LogHelper.MainLog("GetDevices3 time:" + DateTime.Now.TimeOfDay); //#region 获取转移信息 //foreach (Device dev in devices) //{ // if (dev.Type == 0) //刷选寻呼话筒 // { // List<Transfer> trans; // TransferTableManager.GetTransferTableByExtension(out trans, dev.Extension); //转移信息集合 // foreach (Transfer tran in trans) // { // dev.TransferTable.Add(tran); // } // } //} //#endregion //LogHelper.MainLog("GetDevices4 time:" + DateTime.Now.TimeOfDay); points.Clear(); }