/// <summary>
/// 根据ip获取对应的分机号
/// </summary>
/// <param name="ip"></param>
public static string GetExtensionNoByIp(string ip)
{
string result = "";
if (string.IsNullOrEmpty(ip))
{
return(result);
}
string sql = "SELECT ExtensionNo FROM ipvt_extensionmessagetable WHERE DeviceIP=?ip";
var ps = new MySqlParameter[1];
ps[0] = new MySqlParameter("?ip", ip);
try
{
result = CustomMySqlHelper.ExecuteScalar(sql, ps).ToString();
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
return(result);
}
/// <summary>
/// 添加网点信息
/// </summary>
/// <param name="point">网点信息</param>
public static void Add(ref GeoPoint point)
{
if (point == null)
{
return;
}
var sqlBuilder =
new StringBuilder("INSERT INTO ipvt_geoinfotable(Name,FormattedAddress,Latitude,Longitude,Phone,Note) ");
sqlBuilder.Append("VALUES(?name,?address,?lat,?lng,?phone,?note);");
sqlBuilder.Append("SELECT @@IDENTITY");
//string sql = " ";
var parameters = new MySqlParameter[6];
parameters[0] = new MySqlParameter("?name", point.Name);
parameters[1] = new MySqlParameter("?address", point.Address);
parameters[2] = new MySqlParameter("?lat", point.Latitude);
parameters[3] = new MySqlParameter("?lng", point.Longitude);
parameters[4] = new MySqlParameter("?phone", point.Phone);
parameters[5] = new MySqlParameter("?note", point.Note);
object obj = CustomMySqlHelper.ExecuteScalar(sqlBuilder.ToString(), parameters);
if (obj != null)
{
point.Id = EvaluationHelper.ObjectToInt(obj);
}
}
/// <summary>
/// 通过id获取职位名称
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetPositionNameById(int id)
{
string sqlStr = "select PositionName from ipvt_positiontable where PositionID=?id";
var ps = new MySqlParameter[1];
ps[0] = new MySqlParameter("?id", id);
return(CustomMySqlHelper.ExecuteScalar(sqlStr, ps) != null?CustomMySqlHelper.ExecuteScalar(sqlStr, ps).ToString() : string.Empty);
}
/// <summary>
/// 获取报警信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static string GetAlarmType(int id)
{
if (id > 0)
{
string sql = "select AlarmType from ipvt_alarmtypetable where AlarmTypeID=?id";
var result = CustomMySqlHelper.ExecuteScalar(sql, new MySqlParameter("?id", id));
return(EvaluationHelper.ObjectToString(result));
}
return(null);
}
/// <summary>
/// 判断指定id的用户指定的列的值
/// </summary>
/// <param name="uid"></param>
/// <param name="cname"></param>
/// <returns></returns>
public static object GetValue(int uid, string cname)
{
//查询匹配的用户名、密码
string sqlStr = string.Format("select {0} from ipvt_userregmessagetable where UserID=?id and IsEnable=1", cname);
var ps = new MySqlParameter[1];
ps[0] = new MySqlParameter("?id", uid);
return(CustomMySqlHelper.ExecuteScalar(sqlStr, ps));//查询结果
}
/// <summary>
/// 通过单个分机号获取Direction
/// </summary>
/// <param name="exten">分机号</param>
/// <returns></returns>
public static string GetApplicationByNumber(string exten)
{
string sql = "SELECT application from ipvt_channelsinfotable where ExtensionNO=?extenNo";
var ps = new MySqlParameter[1];
ps[0] = new MySqlParameter("?extenNo", exten);
var result = CustomMySqlHelper.ExecuteScalar(sql, ps);
return(result != null?result.ToString() : string.Empty);
}
/// <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>
/// <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>
/// <param name="id"></param>
/// <returns></returns>
public static string GetVoiceNameById(int id)
{
if (id < 0)
{
return(string.Empty);
}
string sql = "SELECT VoiceFileName from ipvt_voicefiletable where ID=?id";
var result = CustomMySqlHelper.ExecuteScalar(sql, new MySqlParameter("?id", id));
return(EvaluationHelper.ObjectToString(result));
}
/// <summary>
/// 获取主叫uuid
/// </summary>
/// <param name="caller">主叫分机号</param>
/// <param name="callee">被叫分机号</param>
/// <returns>uuid/string.Empty</returns>
public static string GetUuid(string caller, string callee)
{
string sql = "SELECT call_uuid FROM ipvt_talkinginfotable where caller=?caller AND callee=?callee";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?caller", caller);
ps[1] = new MySqlParameter("?callee", callee);
var result = CustomMySqlHelper.ExecuteScalar(sql, ps);
return(result != null?result.ToString() : string.Empty);
}
/// <summary>
/// 获取指定分机id的面板名称
/// </summary>
/// <param name="num">面板号</param>
/// <param name="id">分机id</param>
public static string GetPanelName(string id, string num)
{
string sql = @"select a.PanelName from ipvt_panelinfotable as a inner join ipvt_extensionmessagetable as b
on a.ExtensionID=b.ExtensionID where a.PanelNum=?num and b.ExtensionNO=?id ";
MySqlParameter[] pars =
{
new MySqlParameter("?num", num),
new MySqlParameter("?id", EvaluationHelper.StringToInt(id))
};
var result = CustomMySqlHelper.ExecuteScalar(sql, pars);
return(EvaluationHelper.ObjectToString(result));
}
/// <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="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>
/// 插入分组
/// </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="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="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);
}