/// <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>
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>
/// 从提供的网点id中，筛选出指定范围内的网点
/// </summary>
/// <param name="id">网点的id数组</param>
/// <param name="leftTop">地理范围的左上角经纬度</param>
/// <param name="rightBottom">地理范围的右下角经纬度</param>
/// <param name="points">网点集</param>
/// <returns></returns>
public static void GetPoints(int[] id, LatLngCoordinate leftTop, LatLngCoordinate rightBottom, out List <GeoPoint> points)
{
points = new List <GeoPoint>();
var sql =
new StringBuilder(
"SELECT GeoID,Name,FormattedAddress,Latitude,Longitude,Phone FROM ipvt_geoinfotable WHERE Latitude>?latStart AND Latitude<?latEnd AND Longitude>?lngStart AND Longitude<?lngEnd");
if (id.Length > 0)
{
sql.Append(" AND GeoID IN (");
foreach (int i in id)
{
sql.Append(i);
sql.Append(",");
}
sql.Remove(sql.Length - 1, 1);
sql.Append(")");
}
var parameters = new MySqlParameter[4];
parameters[0] = new MySqlParameter("?latStart", rightBottom.Latitude);
parameters[0] = new MySqlParameter("?latEnd", leftTop.Latitude);
parameters[0] = new MySqlParameter("?lngStart", leftTop.Longitude);
parameters[0] = new MySqlParameter("?lngEnd", rightBottom.Longitude);
try
{
DataSet set = CustomMySqlHelper.ExecuteDataSet(sql.ToString(), parameters);
if (set.Tables.Count > 0)
{
DataRowCollection rows = set.Tables[0].Rows;
if (rows != null)
{
foreach (DataRow row in rows)
{
GeoPoint pnt = new GeoPoint();
pnt.Id = EvaluationHelper.ObjectToInt(row["GeoID"]);
pnt.Name = Convert.ToString(row["Name"]);
pnt.Address = Convert.ToString(row["FormattedAddress"]);
//pnt.Ip = Convert.ToString(row["ip"]);
pnt.Phone = Convert.ToString(row["phone"]);
pnt.Latitude = EvaluationHelper.ObjectToDouble(row["Latitude"]);
pnt.Longitude = EvaluationHelper.ObjectToDouble(row["Longitude"]);
points.Add(pnt);
}
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("GeoPointManager.GetPoints error!" + ex);
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int InsertSdkSetupModel(SdkSetupModel model)
{
if (model == null)
{
return(-1);
}
string sql = "insert into ipvt_sdkinfotable(SdkIP,SdkPort) values(?ip,?port);SELECT @@IDENTITY;";
return(EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sql,
new[] { new MySqlParameter("?ip", model.Ip), new MySqlParameter("?port", model.Port) })));
}
/// <summary>
/// 序列化用户对象
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private static User SerializeToUser(MySqlDataReader reader)
{
#region 用户基本信息
var user = new User();
user.Id = EvaluationHelper.ObjectToInt(reader["UserID"]);
user.Name = reader["UserName"].ToString();
user.Password = reader["Password"].ToString();
user.DisplayName = reader["DisplayName"].ToString();
user.RegTime = EvaluationHelper.ObjectToDateTime(reader["RegTime"]);
user.UserType = EvaluationHelper.ObjectToInt(reader["UserType"]);
user.IsLogin = EvaluationHelper.ObjectToInt2(reader["IsLogin"]);
user.LoginTime = EvaluationHelper.ObjectToDateTime(reader["LoginTime"]);
user.Avater = null;
if (reader["Avatar"] != DBNull.Value)
{
try
{
BitmapImage img = ImageHelper.Bytearraytobitmapimage(reader["Avatar"] as byte[]);
img.Freeze(); //冻结bitmap，使其可以跨线程访问
user.Avater = img;
}
catch (Exception ex)
{
LogHelper.MainLog(
string.Format("Convert To BitmapImage produce error!UserName={0},ex={1}",
user.Name, ex));
}
}
//角色
user.UserRole.RoleId = EvaluationHelper.ObjectToInt(reader["RoleID"]);
user.UserRole.RoleName = reader["GroupName"].ToString();
#region 用户对应的员工信息
var us = new Staff();
us.Department = reader["Department"].ToString();
us.EmployeeNumber = reader["StaffNO"].ToString();
us.Id = EvaluationHelper.ObjectToInt(string.IsNullOrEmpty(reader["StaffID"].ToString()) ? "0" : reader["StaffID"]);
us.Name = reader["StaffName"].ToString();
us.Phone = reader["Telephone"].ToString();
us.Sex = EvaluationHelper.ObjectToInt2(string.IsNullOrEmpty(reader["Sex"].ToString()) ? "false" : reader["Sex"]);
//职位
us.StaffPosition.PositionId = EvaluationHelper.ObjectToInt(string.IsNullOrEmpty(reader["PositionID"].ToString()) ? "0" : reader["PositionID"]);
us.StaffPosition.PositionName = reader["PositionName"].ToString();
#endregion
user.UserStaff = us; //用户对应的员工信息对象赋值
#endregion
return(user);
}
/// <summary>
/// 判断是否已存在分机号
/// </summary>
/// <returns></returns>
public static bool IsHaveExtenNo()
{
string sql = "select count(*) from ipvt_extensionmessagetable";
int result = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sql));
if (result > 0)
{
return(true);
}
return(false);
}
/// <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>
/// <param name="id">网点id数组</param>
/// <returns></returns>
public static List <GeoPoint> GetPoints(int[] id)
{
var points = new List <GeoPoint>();
var sqlBuilder =
new StringBuilder("SELECT GeoID,Name,FormattedAddress,Latitude,Longitude,Phone FROM ipvt_geoinfotable ");
if (id.Length > 0)
{
sqlBuilder.Append(" WHERE GeoID IN (");
foreach (int i in id)
{
sqlBuilder.Append(i);
sqlBuilder.Append(",");
}
sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
sqlBuilder.Append(")");
}
sqlBuilder.Append(" ORDER BY Latitude DESC");
try
{
DataSet set = CustomMySqlHelper.ExecuteDataSet(sqlBuilder.ToString());
if (set.Tables.Count > 0)
{
DataRowCollection rows = set.Tables[0].Rows;
if (rows != null)
{
foreach (DataRow row in rows)
{
GeoPoint pnt = new GeoPoint();
pnt.Id = EvaluationHelper.ObjectToInt(row["GeoID"]);
pnt.Name = Convert.ToString(row["Name"]);
pnt.Address = Convert.ToString(row["FormattedAddress"]);
pnt.Phone = Convert.ToString(row["phone"]);
pnt.Latitude = EvaluationHelper.ObjectToDouble(row["Latitude"]);
pnt.Longitude = EvaluationHelper.ObjectToDouble(row["Longitude"]);
points.Add(pnt);
}
}
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
return(points);
}
/// <summary>
/// 插入新职位
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private static int InsertPosition(Position p)
{
if (p == null)
{
return(0);
}
string sql = "insert into ipvt_positiontable(PositionName) "
+ "values(?name);"
+ "SELECT @@IDENTITY";
var parameters = new MySqlParameter[1];
parameters[0] = new MySqlParameter("?name", p.PositionName);;
return(EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sql, parameters)));
}
/// <summary>
/// 判断用户名是否已存在
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static bool JudgeUserNameIsExist(string name)
{
string sql = "select COUNT(*) from ipvt_userregmessagetable where UserName=?name";
var ps = new MySqlParameter[1];
ps[0] = new MySqlParameter("?name", name);
int num = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sql, ps));//查询结果
if (num > 0)
{
return(true);
}
return(false);
}
/// <summary>
/// 执行插入语句，获取自增id
/// </summary>
/// <param name="sqls"></param>
/// <param name="parms"></param>
public static int ExecuteSqlToGetId(string[] sqls, params MySqlParameter[] parms)
{
int result = 0;
if (sqls.Length != 2)
{
return(result);
}
using (var con = new MySqlConnection(ConnectionString))
{
try
{
con.Open();
var command = con.CreateCommand();
for (int i = 0; i < 2; i++)
{
command.CommandText = sqls[i];
if (i == 0)
{
//预处理MySqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
foreach (MySqlParameter parameter in parms)
{
if ((parameter.Direction == ParameterDirection.InputOutput ||
parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
}
command.Parameters.AddRange(parms);
command.ExecuteNonQuery();
}
if (i == 1)
{
result = EvaluationHelper.ObjectToInt(command.ExecuteScalar());
}
command.Parameters.Clear();
}
}
catch (Exception ex)
{
LogHelper.MainLog("error in ExecuteSqlToGetId!" + ex);
}
}
return(result);
}
/// <summary>
/// 按关键字搜索网点
/// </summary>
/// <param name="points"></param>
/// <param name="query">查询关键字</param>
/// <returns></returns>
public static void Search(out List <GeoPoint> points, params string[] query)
{
points = new List <GeoPoint>();
DataSet set;
if (query.Length > 0)
{
int len = query.Length;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("CREATE TEMPORARY TABLE ptemp(ID varchar(100), tempStr varchar(350));");
sqlBuilder.Append("INSERT INTO ptemp SELECT GeoID,CONCAT(Name,FormattedAddress,Phone,Note) FROM ipvt_geoinfotable;");
sqlBuilder.Append("SELECT GeoID,Name,FormattedAddress,Latitude,Longitude,Phone,Note FROM ipvt_geoinfotable WHERE GeoID IN");
sqlBuilder.Append("(");
sqlBuilder.Append("SELECT ID FROM ptemp WHERE");
for (int i = 0; i < len;)
{
sqlBuilder.Append(string.Format(" ptemp.tempStr LIKE '%{0}%'", query[i]));
if (++i < len)
{
sqlBuilder.Append(" AND ");
}
}
sqlBuilder.Append(")");
try
{
set = CustomMySqlHelper.ExecuteDataSet(sqlBuilder.ToString());
DataRowCollection rows = set.Tables[0].Rows;
foreach (DataRow row in rows)
{
GeoPoint 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"]);
points.Add(pnt);
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
}
}
/// <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>
/// 更新语音文件表对应的分机id
/// </summary>
/// <param name="oldnum"></param>
/// <param name="newnum"></param>
public static void UpdatePhoneNuber(string oldnum, string newnum)
{
try
{
string s1 = "select ExtensionID from ipvt_extensionmessagetable where ExtensionNo=?oldnum";
string s2 = "select ExtensionID from ipvt_extensionmessagetable where ExtensionNo=?newnum";
int oldid = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(s1, new MySqlParameter("?oldnum", string.IsNullOrEmpty(oldnum) ? 0 : Convert.ToInt32(oldnum))));
int newid = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(s2, new MySqlParameter("?newnum", string.IsNullOrEmpty(newnum) ? 0 : Convert.ToInt32(newnum))));
string sql = "update ipvt_voicefiletable set PhoneNumber=?newid where PhoneNumber=?oldid";
CustomMySqlHelper.ExecuteNonQuery(sql, new[] { new MySqlParameter("?oldid", oldid), new MySqlParameter("?newid", newid) });
}
catch (Exception ex)
{
LogHelper.MainLog("Error! " + ex.Message);
}
}
/// <summary>
/// 服务器登录，判断用户登陆账号密码是否正确
/// </summary>
/// <param name="id">账号</param>
/// <param name="pwd">密码</param>
/// <returns>true：存在 false：不存在</returns>
public static bool IsUserExist(string id, string pwd)
{
//查询匹配的用户名、密码
string sqlStr = "select COUNT(*) from ipvt_userregmessagetable where IsEnable=1 AND (UserType=1 OR UserType=2) " +
"AND UserName=?name AND Password=?pwd";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?name", id);
ps[1] = new MySqlParameter("?pwd", pwd);
int num = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sqlStr, ps));//查询结果
if (num > 0)
{
return(true);
}
return(false);
}
/// <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>
/// 获取寻呼话筒简要信息
/// </summary>
/// <returns></returns>
public static void GetPhones(out List <Device> devices)
{
devices = new List <Device>();
string sql;
sql = "select A.DeviceID,A.DeviceName,A.DeviceType,A.DeviceIP,A.ExtensionID,B.ExtensionNo" +
" from ipvt_deviceinfotable AS A,ipvt_extensionmessagetable AS B" +
" WHERE A.DeviceType=0 and A.ExtensionID=B.ExtensionID";
try
{
DataSet set = CustomMySqlHelper.ExecuteDataSet(sql);
if (set.Tables.Count > 0)
{
DataRowCollection rows = set.Tables[0].Rows;
foreach (DataRow row in rows)
{
var device = new Device();
device.Name = row["DeviceName"].ToString();
device.Id = EvaluationHelper.ObjectToInt(row["DeviceID"]);
device.Type = EvaluationHelper.ObjectToInt(row["DeviceType"]);
device.Ip = EvaluationHelper.ObjectToString(row["DeviceIP"]);
device.Extension = new Extension
{
Number = EvaluationHelper.ObjectToString(row["ExtensionNo"]),
Id = EvaluationHelper.ObjectToInt(row["ExtensionID"]),
};
if (string.IsNullOrEmpty(device.Name)) //如果名称为空给设备添加默认名称=分机号
{
device.Name = EvaluationHelper.ObjectToString(row["ExtensionNo"]);
}
devices.Add(device);
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("error int GetPanels(int extensionId)!" + ex);
}
}
public static void Get(LatLngCoordinate leftTop, LatLngCoordinate rightBottom, out List <GeoPoint> points)
{
points = new List <GeoPoint>();
string sql =
"SELECT GeoID,Name,FormattedAddress,Latitude,Longitude,Phone,Note FROM ipvt_geoinfotable WHERE Latitude>?latStart and Latitude<?latEnd and Longitude>?lngStart and Longitude<?lngEnd";
var parameters = new MySqlParameter[4];
parameters[0] = new MySqlParameter("?latStart", rightBottom.Latitude);
parameters[1] = new MySqlParameter("?latEnd", leftTop.Latitude);
parameters[2] = new MySqlParameter("?lngStart", leftTop.Longitude);
parameters[3] = new MySqlParameter("?lngEnd", rightBottom.Longitude);
try
{
DataSet set = CustomMySqlHelper.ExecuteDataSet(sql, parameters);
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"]);
points.Add(pnt);
}
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("GeoPointManager.Get error!" + ex);
}
}
/// <summary>
/// 获取设备
/// </summary>
/// <param name="decies"></param>
public static void GetDevices(out List <Device> decies)
{
decies = new List <Device>();
if (HaveDb())
{
SQLiteDataReader reader = null;
try
{
string sql = "select * from device";
reader = SqLiteHelper.ExcuteReader(SqLiteHelper.Open(_dbPath), sql);
while (reader.Read())
{
Device device = new Device();
device.Id = EvaluationHelper.ObjectToInt(reader["did"]);
device.Ip = EvaluationHelper.ObjectToString(reader["ip"]);
device.Name = EvaluationHelper.ObjectToString(reader["name"]);
device.Type = EvaluationHelper.ObjectToInt(reader["type"]);
device.IsHaveVideo = EvaluationHelper.ObjectToInt(reader["video"]);
device.Generation = EvaluationHelper.ObjectToInt(reader["generation"]);
device.Extension.Number = EvaluationHelper.ObjectToString(reader["extno"]);
device.PanelNum = EvaluationHelper.ObjectToInt(reader["panelnum"]);
//device.StateString = "离线";
//device.StateString = "通话中";
device.StateString = "空闲";
//device.StateString = "呼叫中";
decies.Add(device);
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
/// <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>
/// 通过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>
/// <param name="group">组对象</param>
/// <returns>自增的id，失败则返回0</returns>
public static int InsertGroup(DeviceGroup group)
{
int result = 0;
if (group != null)
{
string sql = "insert into ipvt_groupinfotable(ParentID,GroupName,GroupLevel) values(?pid,?name,?level);" +
"SELECT @@IDENTITY";
var ps = new[]
{
new MySqlParameter("?pid", group.ParentId),
new MySqlParameter("?name", group.Name),
new MySqlParameter("?level", group.Level)
};
result = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sql, ps));
}
return(result);
}
/// <summary>
/// 保存员工
/// </summary>
/// <param name="sta">员工对象</param>
/// <returns>受影响的行数</returns>
public static int SaveStaff(Staff sta)
{
if (sta.Id != 0)
{
string insertStatffSqlStr = "update ipvt_staffmessagetable " +
"set StaffName=?name,StaffNO=?staffNo,Sex=?sex," +
"Department=?department,Telephone=?phone,PositionID=?positionId" +
" where StaffID=?sid";
var param = new MySqlParameter[7];
param[0] = new MySqlParameter("?name", sta.Name);
param[1] = new MySqlParameter("?staffNo", sta.EmployeeNumber);
param[2] = new MySqlParameter("?sex", sta.Sex);
param[3] = new MySqlParameter("?department", sta.Department);
param[4] = new MySqlParameter("?phone", sta.Phone);
param[5] = new MySqlParameter("?positionId", sta.StaffPosition.PositionId);
param[6] = new MySqlParameter("?sid", sta.Id);
return(CustomMySqlHelper.ExecuteNonQuery(insertStatffSqlStr, param));
}
else if (!string.IsNullOrEmpty(sta.Name))
{
string sqlStr = "INSERT INTO ipvt_staffmessagetable(StaffName,StaffNO,Sex,Department,Telephone,PositionID) " +
"VALUES(?name,?staffNo,?sex,?department,?phone,?positionId);" +
"SELECT @@IDENTITY";
// string sqlStr = "INSERT INTO ipvt_staffmessagetable(StaffName,StaffNO,Sex,Department,Telephone,PositionID) " +
//"VALUES('" + sta.Name + "','" + sta.EmployeeNumber + "'," + sta.Sex + "," + sta.Department + "," + sta.Phone + "," + sta.StaffPosition.PositionId + ")";
var param = new MySqlParameter[6];
param[0] = new MySqlParameter("?name", sta.Name);
param[1] = new MySqlParameter("?staffNo", sta.EmployeeNumber);
param[2] = new MySqlParameter("?sex", sta.Sex);
param[3] = new MySqlParameter("?department", sta.Department);
param[4] = new MySqlParameter("?phone", sta.Phone);
param[5] = new MySqlParameter("?positionId", sta.StaffPosition.PositionId);
//return CustomMySqlHelper.ExecuteSql(sqlStr);
return(EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sqlStr, param)));
}
return(0);
}
/// <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>
/// <returns></returns>
public static void GetDeviceGroups(out List <DeviceGroup> groups)
{
groups = new List <DeviceGroup>();
DataSet set;
string sql = "SELECT GroupID,GroupName,ParentID,GroupLevel FROM ipvt_groupinfotable";
try
{
set = CustomMySqlHelper.ExecuteDataSet(sql);
if (set.Tables.Count > 0)
{
DataRowCollection rows = set.Tables[0].Rows;
var grps = new DeviceGroup[rows.Count];
int i = 0;
foreach (DataRow row in rows)
{
var grp = new DeviceGroup();
grp.Id = EvaluationHelper.ObjectToInt(row["GroupID"]);
grp.Name = Convert.ToString(row["GroupName"]);
grp.ParentId = EvaluationHelper.ObjectToInt(row["ParentID"]);
grp.Level = EvaluationHelper.ObjectToInt(row["GroupLevel"]);
grps[i++] = grp;
}
var roots = from r in grps where r.Id == -1 select r;
foreach (var r in roots)
{
FillGroup(r, grps);
groups.Add(r);
}
}
}
catch (Exception ex)
{
LogHelper.MainLog("error int GetDeviceGroups()!" + ex);
}
}
/// <summary>
/// 插入新用户
/// </summary>
/// <param name="user">用户对象</param>
/// <returns>受影响的行数</returns>
private static int InsertUser(User user)
{
if (user == null)
{
return(0);
}
string sql = "insert into ipvt_userregmessagetable(UserName,Avatar,Password,UserType,RoleID,RegTime,StaffID,DisplayName) "
+ "values(?name,?avatar,?pwd,?type,?roleId,?time,?staffId,?disName);"
+ "SELECT @@IDENTITY";
var parameters = new MySqlParameter[8];
parameters[0] = new MySqlParameter("?name", user.Name);
parameters[1] = new MySqlParameter("?avatar", ImageHelper.Bitmapimagetobytearray(user.Avater));
parameters[2] = new MySqlParameter("?pwd", user.Password);
parameters[3] = new MySqlParameter("?type", user.UserType);
parameters[4] = new MySqlParameter("?roleId", user.UserRole.RoleId);
parameters[5] = new MySqlParameter("?time", DateTime.Now);
parameters[6] = new MySqlParameter("?disName", user.DisplayName);
parameters[7] = new MySqlParameter("?staffId", user.UserStaff.Id);
return(EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(sql, parameters)));
}
/// <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>
/// <param name="group"></param>
/// <returns></returns>
public static bool InsertGroup(JurisdictionGroup group)
{
var models = new List <SqlTextModel>();
if (group != null && !string.IsNullOrEmpty(group.GroupName))
{
//插入组信息
string gsql =
"insert into ipvt_jurgrouptable(GroupName,Description) values(?name,?des);SELECT @@IDENTITY";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?name", group.GroupName);
ps[1] = new MySqlParameter("?des", group.Description);
int newid = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(gsql, ps));
if (newid > 0)
{
//添加权限组-权限表指定组的新对应数据
foreach (Jurisdiction jur in group.Jurisdictions)
{
string insertsql =
"insert into ipvt_jurisdiction_grouptable(GroupID,JurisdictionID) values(?gid,?jid)";
var inps = new MySqlParameter[2];
inps[0] = new MySqlParameter("?gid", newid);
inps[1] = new MySqlParameter("?jid", jur.Id);
models.Add(new SqlTextModel {
SqlString = insertsql, MySqlParams = inps
});
}
return(CustomMySqlHelper.ExecuteSqlList(models));//事务批量执行
}
}
return(false);
}