public static void GetGeo(ref List <GeoPoint> listGeo)
{
string sql = "select * from ipvt_geoinfotable";
MySqlDataReader rd = CustomMySqlHelper.ExecuteDataReader(sql);
try
{
DataSet set = CustomMySqlHelper.ExecuteDataSet(sql);
if (set.Tables.Count > 0)
{
DataRowCollection rows = set.Tables[0].Rows;
if (rows != null)
{
foreach (DataRow row in rows)
{
var pnt = new GeoPoint();
pnt.Id = EvaluationHelper.ObjectToInt(row["GeoID"]);
pnt.Name = Convert.ToString(row["Name"]);
pnt.Address = Convert.ToString(row["FormattedAddress"]);
pnt.Note = Convert.ToString(row["Note"]);
pnt.Phone = Convert.ToString(row["Phone"]);
pnt.Latitude = EvaluationHelper.ObjectToDouble(row["Latitude"]);
pnt.Longitude = EvaluationHelper.ObjectToDouble(row["Longitude"]);
listGeo.Add(pnt);
}
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("GeoPointManager.Get error!" + ex);
}
}
public static int GetCount()
{
int count = 0;
string sqlcount = " select count(*) as count from ipvt_geoinfotable";
MySqlDataReader readercount = CustomMySqlHelper.ExecuteDataReader(sqlcount);
try
{
while (readercount.Read())
{
count = EvaluationHelper.ObjectToInt(readercount["count"]);
}
}
catch (Exception ex)
{
LogHelper.MainLog("Error in GetCallLogCount!" + ex.Message);
}
finally
{
if (readercount != null)
{
readercount.Close(); //读取完关闭reader对象
}
}
return(count);
}
/// <summary>
/// 通过单个分机号获取正在等待的分机信息
/// </summary>
/// <returns></returns>
public static void GetChannelsinfo(out List <Channelsinfo> infoes)
{
infoes = new List <Channelsinfo>();
string sql = "SELECT uuid,ExtensionNO,application,application_data from ipvt_channelsinfotable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
Channelsinfo model = new Channelsinfo();
model.Uuid = EvaluationHelper.ObjectToString(reader["uuid"]);
model.ExtNo = EvaluationHelper.ObjectToString(reader["ExtensionNO"]);
model.ApplicationInfo = EvaluationHelper.ObjectToString(reader["application"]);
model.ApplicationDate = EvaluationHelper.ObjectToString(reader["application_data"]);
infoes.Add(model);
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 获取所有权限
/// </summary>
public static void GetJurisdictions(out List <Jurisdiction> list)
{
list = new List <Jurisdiction>();
string sql = "select JurisdictionID,JurisdictionName,Description from ipvt_jurisdictiontable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
var jur = new Jurisdiction();
jur.Id = EvaluationHelper.ObjectToInt(reader["JurisdictionID"]);
jur.JurName = reader["JurisdictionName"].ToString();
jur.Description = reader["Description"].ToString();
list.Add(jur);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in GetJurList(int gid)!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <summary>
/// 获取正在通话的集合
/// </summary>
/// <returns></returns>
public static void GeTalkInfos(ref List <TalkInfo> list)
{
//list = new List<TalkInfo>();
string sql = "SELECT * FROM ipvt_talkinginfotable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
if (reader != null)
{
while (reader.Read())
{
var info = new TalkInfo();
info.From = reader["caller"].ToString();
info.To = reader["callee"].ToString();
list.Add(info);
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in GeTalkInfos()!ex:" + ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 获取未使用的分机号集合
/// </summary>
/// <returns></returns>
public static void GetExtensionNoList(out List <string> list)
{
list = new List <string>();
string sql = "SELECT ExtensionNO from ipvt_extensionmessagetable WHERE CurrentState=0";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
if (reader["ExtensionNO"] != null)
{
list.Add(reader["ExtensionNO"].ToString());
}
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <summary>
/// 获取设备分组信息
/// </summary>
/// <returns></returns>
public static void GetGroups(ref List <DeviceGroup> groups)
{
try
{
string sql = "SELECT * FROM ipvt_groupinfotable";
MySqlDataReader reader = null;
reader = CustomMySqlHelper.ExecuteDataReader(sql);
if (reader != null)
{
while (reader.Read())
{
var grp = new DeviceGroup();
grp.Id = EvaluationHelper.ObjectToInt(reader["GroupID"]);
grp.Name = Convert.ToString(reader["GroupName"]);
grp.ParentId = EvaluationHelper.ObjectToInt(reader["ParentID"]);
grp.Level = EvaluationHelper.ObjectToInt(reader["GroupLevel"]);
groups.Add(grp);
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("error int GetGroups()!" + ex.StackTrace);
}
}
/// <summary>
/// 根据组id获取组信息
/// </summary>
/// <param name="gid"></param>
/// <returns></returns>
public static DeviceGroup GetGroup(int gid)
{
DeviceGroup group = null;
string sql = "SELECT GroupID,GroupName,ParentID,GroupLevel FROM ipvt_groupinfotable where GroupID=?gid";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql, new MySqlParameter("?gid", gid));
while (reader.Read())
{
group = new DeviceGroup();
group.Id = EvaluationHelper.ObjectToInt(reader["GroupID"]);
group.Name = Convert.ToString(reader["GroupName"]);
group.ParentId = EvaluationHelper.ObjectToInt(reader["ParentID"]);
group.Level = EvaluationHelper.ObjectToInt(reader["GroupLevel"]);
break;
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(group);
}
/// <summary>
/// 获取职位集
/// </summary>
public static void GetPositions(out List <Position> list)
{
list = new List <Position>();
string sqlStr = "select PositionID,PositionName from ipvt_positiontable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sqlStr);
while (reader.Read())
{
var p = new Position();
p.PositionId = EvaluationHelper.ObjectToInt(reader["PositionID"]);
p.PositionName = reader["PositionName"].ToString();
list.Add(p);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in UserManager.GetPositions()!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 获取角色集
/// </summary>
public static void GetRoles(out List <Role> list)
{
list = new List <Role>();
string sqlStr = "select GroupID,GroupName from ipvt_jurgrouptable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sqlStr);
while (reader.Read())
{
var r = new Role();
r.RoleId = EvaluationHelper.ObjectToInt(reader["GroupID"]);
r.RoleName = reader["GroupName"].ToString();
list.Add(r);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in UserManager.GetRoles()!" + ex);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
public static bool JudgeVoiceExist(string name)
{
string sql =
string.Format(@"select count(*) as count from ipvt_voicefiletable where VoiceFileName={0} and VoiceType=0",
name);
MySqlDataReader readercount = CustomMySqlHelper.ExecuteDataReader(sql);
try
{
int SumCount = 0;
while (readercount.Read())
{
SumCount = EvaluationHelper.ObjectToInt(readercount["count"]);
}
if (SumCount > 0)
{
return(true);
}
}
catch (Exception ex)
{
LogHelper.MainLog("Error in GetCallLogCount!" + ex.Message);
}
return(false);
}
/// <summary>
/// 通过单个分机号获取正在等待的分机信息
/// </summary>
/// <param name="exten">分机号</param>
/// <returns></returns>
public static Channelsinfo GetChannelsinfo(string exten)
{
string sql = "SELECT uuid,ExtensionNO,application,application_data from ipvt_channelsinfotable where ExtensionNO=?extenNo";
var ps = new MySqlParameter[1];
ps[0] = new MySqlParameter("?extenNo", exten);
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql, ps);
while (reader.Read())
{
Channelsinfo model = new Channelsinfo();
model.Uuid = EvaluationHelper.ObjectToString(reader["uuid"]);
model.ExtNo = EvaluationHelper.ObjectToString(reader["ExtensionNO"]);
model.ApplicationInfo = EvaluationHelper.ObjectToString(reader["application"]);
model.ApplicationDate = EvaluationHelper.ObjectToString(reader["application_data"]);
return(model);
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(null);
}
/// <summary>
/// 获取语音文件名称集合
/// </summary>
public static void GetVoiceList(out List <PathAndNameModel> list)
{
list = new List <PathAndNameModel>();
string sql = "SELECT * from ipvt_voicefiletable where VoiceType=0";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
list.Add(new PathAndNameModel()
{
Name = reader["VoiceFileName"].ToString(), FileTime = reader["Description"].ToString()
});
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
// 获取语音文件
private static Dictionary <int, string> GetVoices(int number)
{
var list = new Dictionary <int, string>();
if (number > 0)
{
string sql = "SELECT ID,VoiceFileName from ipvt_voicefiletable where PhoneNumber=?num and VoiceType=1";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql, new MySqlParameter("?num", number));
while (reader.Read())
{
int id = EvaluationHelper.ObjectToInt(reader["ID"]);
if (!list.ContainsKey(id))
{
list.Add(id, reader["VoiceFileName"].ToString());
}
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
return(list);
}
/// <summary>
/// 获取SDK配置信息
/// </summary>
/// <param name="models"></param>
public static void GetSdkModels(out List <SdkSetupModel> models)
{
models = new List <SdkSetupModel>();
string sql = "select SdkID,SdkIP,SdkPort from ipvt_sdkinfotable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
if (reader != null)
{
while (reader.Read())
{
models.Add(new SdkSetupModel()
{
Id = EvaluationHelper.ObjectToInt(reader["SdkID"]),
Ip = EvaluationHelper.ObjectToString(reader["SdkIP"]),
Port = EvaluationHelper.ObjectToString(reader["SdkPort"])
});
}
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 获取输入/输出端口报警设置原型
/// </summary>
/// <param name="models"></param>
public static void GetPortAlarmModels(out List <CommonModel> models)
{
models = new List <CommonModel>();
string sql = "select AlarmTypeID,AlarmType from ipvt_alarmtypetable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
CommonModel model = new CommonModel();
model.Id = EvaluationHelper.ObjectToInt(reader["AlarmTypeID"]);
model.Content = EvaluationHelper.ObjectToString(reader["AlarmType"]);
models.Add(model);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in GetPortAlarmModels()" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <summary>
/// 获取
/// </summary>
/// <returns></returns>
public static VideoLinkageModel GetVideoLinkageModel(int did, int pnum)
{
VideoLinkageModel model = null;
if (did > 0 && pnum >= 0)//一代设备的面板号有0的
{
string sql = "SELECT * FROM ipvt_videolinkagetable WHERE DeviceId=?did AND PanelNumber=?pnum;";
MySqlParameter[] ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?did", did);
ps[1] = new MySqlParameter("?pnum", pnum);
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql, ps);
while (reader.Read())
{
model = new VideoLinkageModel();
model.Id = EvaluationHelper.ObjectToInt(reader["ID"]);
model.DeviceId = EvaluationHelper.ObjectToInt(reader["DeviceId"]);
model.PanelNumber = EvaluationHelper.ObjectToInt(reader["PanelNumber"]);
model.ChannelNumber = EvaluationHelper.ObjectToByte(reader["ChannelNumber"]);
model.NetConnectMethods = EvaluationHelper.ObjectToByte(reader["NetConnectMethods"]);
model.ImgFormat = EvaluationHelper.ObjectToByte(reader["ImgFormat"]);
model.TransmitJur = EvaluationHelper.ObjectToByte(reader["TransmitJur"]);
model.ReceivePort = EvaluationHelper.ObjectToUshort(reader["ReceivePort"]);
model.ForwardingPort = EvaluationHelper.ObjectToUshort(reader["ForwardingPort"]);
model.ServerUserId = EvaluationHelper.ObjectToString(reader["ServerUserId"]);
model.ServerIp = EvaluationHelper.ObjectToString(reader["ServerIp"]);
model.TranServerIp = EvaluationHelper.ObjectToString(reader["TranServerIp"]);
model.UserName = EvaluationHelper.ObjectToString(reader["UserName"]);
model.UserPwd = EvaluationHelper.ObjectToString(reader["UserPwd"]);
model.DeviceType = EvaluationHelper.ObjectToByte(reader["DeviceType"]);
model.UserVerify = EvaluationHelper.ObjectToString(reader["UserVerify"]);
break;
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in GetVideoLinkageModel!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
return(model);
}
/// <summary>
/// 获取权限组
/// </summary>
/// <returns></returns>
public static void GetJurGroups(out List <JurisdictionGroup> list)
{
list = new List <JurisdictionGroup>();
string sql = "select GroupID,GroupName,Description,DefaultGroup from ipvt_jurgrouptable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
var group = new JurisdictionGroup();
group.GroupId = EvaluationHelper.ObjectToInt(reader["GroupID"]);
group.Default = EvaluationHelper.ObjectToInt(reader["DefaultGroup"]);
group.GroupName = reader["GroupName"].ToString();
group.Description = reader["Description"].ToString();
list.Add(group);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in JurisdictionManager.GetJurGroups()!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
foreach (JurisdictionGroup group in list)
{
List <Jurisdiction> js;
GetJurisdictions(group.GroupId, out js);
if (js != null)
{
foreach (Jurisdiction jur in js)
{
group.Jurisdictions.Add(jur);
}
}
}
}
/// <summary>
/// 指定账户密码获取用户(服务器)
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public static User GetUserByNameAndPwd(string name, string pwd)
{
#region
var builder = new StringBuilder();
builder.Append("SELECT a.UserID,a.UserName,a.Avatar,a.Password,a.RegTime,a.StaffID," +
"a.UserType,a.RoleID,a.DisplayName,a.IsLogin,a.LoginTime,");
builder.Append("b.Department,b.Sex,b.StaffName,b.StaffNO,b.Telephone,b.PositionID,");
builder.Append("d.GroupName,e.PositionName ");
builder.Append("from ipvt_userregmessagetable as a LEFT JOIN ipvt_staffmessagetable as b on a.StaffID=b.StaffID ");
builder.Append("LEFT JOIN ipvt_jurgrouptable as d on a.RoleID=d.GroupID ");
builder.Append("LEFT JOIN ipvt_positiontable as e on b.PositionID=e.PositionID ");
builder.Append("where a.IsEnable=1 AND a.UserName=?name AND a.Password=?pwd and (a.UserType=1 OR a.UserType=2)");
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?name", name);
ps[1] = new MySqlParameter("?pwd", pwd);
#endregion
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(builder.ToString(), ps);
if (reader != null)
{
while (reader.Read())
{
return(SerializeToUser(reader));
}
}
}
catch (Exception ex)
{
LogHelper.MainLog(string.Format("Serialize to user in UserManager.GetUserByNameAndPwd error!{0}", ex));
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
return(null);
}
/// <summary>
/// 通过id获取员工
/// </summary>
/// <returns></returns>
public static Staff GetStaveById(int id)
{
string sqlStr = "Select * from ipvt_staffmessagetable where StaffID=?id";
var param = new MySqlParameter[1];
param[0] = new MySqlParameter("?id", id);
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sqlStr, param); //执行SQL
while (reader.Read())
{
var staff = new Staff();
staff.Id = EvaluationHelper.ObjectToInt(reader["StaffID"]);
staff.Name = reader["StaffName"].ToString();
staff.Sex = EvaluationHelper.ObjectToInt2(reader["Sex"]);
staff.EmployeeNumber = reader["StaffNO"].ToString();
staff.Phone = reader["Telephone"].ToString();
staff.Department = reader["Department"].ToString();
//
staff.StaffPosition.PositionId = EvaluationHelper.ObjectToInt(reader["PositionID"]);
if (staff.StaffPosition.PositionId != 0) //如果职位id存在，尝试获取职位名称
{
staff.StaffPosition.PositionName = GetPositionNameById(staff.StaffPosition.PositionId);
}
return(staff);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in UserManager.GetStaveById(int id)!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
return(new Staff());
}
/// <summary>
/// 通过ID获取Position实体
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Position GetPositionById(int id)
{
var builder = new StringBuilder();
builder.Append("SELECT * FROM ipvt_positiontable WHERE 1=1 AND ");
builder.Append("PositionID=?id");
var prams = new MySqlParameter[1];
prams[0] = new MySqlParameter("?id", id);
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(builder.ToString(), prams);
while (reader.Read())
{
reader = CustomMySqlHelper.ExecuteDataReader(builder.ToString(), prams);
while (reader.Read())
{
var _Position = new Position();
_Position.PositionId = EvaluationHelper.ObjectToInt(reader["PositionID"]);
_Position.PositionName = reader["PositionName"].ToString();
return(_Position);
}
}
}
catch (Exception ex)
{
LogHelper.MainLog(string.Format("Serialize to position in UserManager.GetPositionById error!{0}", ex));
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
return(new Position());
}
/// <summary>
/// 获取级联集合
/// </summary>
/// <returns></returns>
public static void GetCascadings(out List <Cascading> list)
{
list = new List <Cascading>();
string mysql = "select CascadingID,Data_key,Data,Remark from ipvt_cascadingtable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(mysql);
while (reader != null && reader.Read())
{
var model = new Cascading();
model.Id = EvaluationHelper.ObjectToInt(reader["CascadingID"]);
model.Key = reader["Data_key"].ToString();
model.Ip = reader["Data"].ToString();
model.Remark = reader["Remark"].ToString();
var con = SqLiteHelper.Open(_dbPath);
string sql =
string.Format(
"select RowID from db_data where realm='hy_extern_domain' and data_key='{0}' and data='{1}'",
model.Key, model.Ip);
model.RowId = EvaluationHelper.ObjectToInt(SqLiteHelper.ExecuteScalar(con, sql));
list.Add(model);
con.Close();
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 获取转移设置信息
/// </summary>
/// <param name="list"></param>
public static void GetTransInfo(out List <TranSetupInfo> list)
{
list = new List <TranSetupInfo>();
string sql = "select ID,TranType,StartTime,EndTime," +
"(SELECT ExtensionNO FROM ipvt_extensionmessagetable where ExtensionID=StairTran) as Stair," +
"(SELECT ExtensionNO FROM ipvt_extensionmessagetable where ExtensionID=SecondTran) as Second," +
"(SELECT ExtensionNO FROM ipvt_extensionmessagetable where ExtensionID=ThreeTran) as Three," +
"IsEnable,Description from ipvt_transetuptable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
var model = new TranSetupInfo();
model.Id = EvaluationHelper.ObjectToInt(reader["ID"]);
model.TranType = EvaluationHelper.ObjectToInt(reader["TranType"]);
model.StartTime = EvaluationHelper.ObjectToInt(reader["StartTime"]);
model.EndTime = EvaluationHelper.ObjectToInt(reader["EndTime"]);
model.IsEnable = EvaluationHelper.ObjectToInt(reader["IsEnable"]);
model.StairTran = EvaluationHelper.ObjectToString(reader["Stair"]);
model.SecondTran = EvaluationHelper.ObjectToString(reader["Second"]);
model.ThreeTran = EvaluationHelper.ObjectToString(reader["Three"]);
model.Description = EvaluationHelper.ObjectToString(reader["Description"]);
list.Add(model);
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <summary>
/// 获取员工
/// </summary>
/// <returns></returns>
public static void GetStaves(out List <Staff> list)
{
list = new List <Staff>();
string sqlStr = "Select * from ipvt_staffmessagetable";
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sqlStr); //执行SQL
while (reader.Read())
{
var staff = new Staff();
staff.Id = EvaluationHelper.ObjectToInt(reader["StaffID"]);
staff.Name = reader["StaffName"].ToString();
staff.Sex = EvaluationHelper.ObjectToInt2(reader["Sex"]);
staff.EmployeeNumber = reader["StaffNO"].ToString();
staff.Phone = reader["Telephone"].ToString();
staff.Department = reader["Department"].ToString();
//
staff.StaffPosition.PositionId = EvaluationHelper.ObjectToInt(reader["PositionID"]);
if (staff.StaffPosition.PositionId != 0) //如果职位id存在，尝试获取职位名称
{
staff.StaffPosition.PositionName = GetPositionNameById(staff.StaffPosition.PositionId);
}
list.Add(staff);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in UserManager.GetStaves()!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <summary>
/// 获取权限集合
/// </summary>
/// <param name="gid"></param>
/// <param name="list">out</param>
/// <returns></returns>
public static void GetJurisdictions(int gid, out List <Jurisdiction> list)
{
list = new List <Jurisdiction>();
if (gid > 0)
{
var builder = new StringBuilder();
builder.Append("select a.JurisdictionID,b.JurisdictionName,b.Description ");
builder.Append("from ipvt_jurisdiction_grouptable as a ");
builder.Append("LEFT JOIN ipvt_jurisdictiontable as b ");
builder.Append("on a.JurisdictionID=b.JurisdictionID ");
builder.Append("where a.GroupID=?gid");
var ps = new MySqlParameter("?gid", gid);
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(builder.ToString(), ps);
while (reader.Read())
{
var jur = new Jurisdiction();
jur.Id = EvaluationHelper.ObjectToInt(reader["JurisdictionID"]);
jur.JurName = reader["JurisdictionName"].ToString();
jur.Description = reader["Description"].ToString();
list.Add(jur);
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in GetJurList(int gid)!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
}
/// <summary>
/// 获取权限集合
/// </summary>
/// <param name="gid"></param>
/// <param name="list">out</param>
/// <returns></returns>
public static void GetJurList(int gid, out List <JurisdictionEnum> list)
{
if (gid <= 0)
{
gid = 1;
}
list = new List <JurisdictionEnum>();
if (gid > 0)
{
string sql = "SELECT JurisdictionID from ipvt_jurisdiction_grouptable where GroupID=?gid";
var ps = new MySqlParameter("?gid", gid);
var reader = CustomMySqlHelper.ExecuteDataReader(sql, ps);
try
{
while (reader.Read())
{
var jid = EvaluationHelper.ObjectToInt(reader[0]);
if (jid > 0)
{
list.Add(GetJurisdictionEnum(jid));
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in GetJurList(int gid)!" + ex.Message);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
}
/// <summary>
/// 获取所有用户信息
/// </summary>
/// <returns></returns>
public static void GetUsers(out List <User> users, int type = 0)
{
users = new List <User>();
#region
var builder = new StringBuilder();
builder.Append("SELECT a.UserID,a.UserName,a.Avatar,a.Password,a.RegTime,a.StaffID," +
"a.UserType,a.RoleID,a.DisplayName,a.IsLogin,a.LoginTime,");
builder.Append("b.Department,b.Sex,b.StaffName,b.StaffNO,b.Telephone,b.PositionID,");
builder.Append("d.GroupName,e.PositionName ");
builder.Append("from ipvt_userregmessagetable as a LEFT JOIN ipvt_staffmessagetable as b on a.StaffID=b.StaffID ");
builder.Append("LEFT JOIN ipvt_jurgrouptable as d on a.RoleID=d.GroupID ");
builder.Append("LEFT JOIN ipvt_positiontable as e on b.PositionID=e.PositionID");
builder.Append(" where a.IsEnable=1");
if (type != 0)
{
builder.Append(" and (a.UserType=0 or a.UserType=2)");
}
#endregion
MySqlDataReader reader = null;
try
{
int count = 0;
while (string.IsNullOrEmpty(CustomMySqlHelper.ConnectionString))//自动循环重复
{
if (count > 2)
{
break;
}
count++;
Thread.Sleep(count * 1000);
}
//string time = DateTime.Now.TimeOfDay.ToString();
//string time3="";
reader = CustomMySqlHelper.ExecuteDataReader(builder.ToString());
if (reader != null)
{
while (reader.Read())
{
users.Add(SerializeToUser(reader));
//time3 = DateTime.Now.TimeOfDay.ToString();
}
}
//string time2 = DateTime.Now.TimeOfDay.ToString();
//MessageBox.Show(time + "\n" + time3 + "\n" + time2);
;
}
catch (Exception ex)
{
LogHelper.MainLog(string.Format("Serialize to user in UserManager.GetUsers error!{0}", ex.StackTrace));
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <summary>
/// 获取指定分机id的面板信息
/// </summary>
/// <param name="extensionId">分机id</param>
/// <param name="list">out 面板信息集合</param>
public static void GetPanels(int extensionId, out List <PanelDevice> list)
{
list = new List <PanelDevice>();
//查询面板信息的sql语句
string sqlStr = "select * from ipvt_panelinfotable where ExtensionID=?id order by PanelNum";
var param = new MySqlParameter[1]; //参数对象
param[0] = new MySqlParameter("?id", extensionId);
//获取reader对象
MySqlDataReader reader = null;
try
{
reader = CustomMySqlHelper.ExecuteDataReader(sqlStr, param);
if (reader != null)
{
while (reader.Read())
{
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);
}
list.Add(panel); //将面板信息对象加入设备面板集合
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("error int GetPanels(int extensionId)!" + ex);
}
finally
{
if (reader != null)
{
reader.Close(); //读取完关闭reader对象
}
}
}
/// <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);
}