/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns>成功返回true</returns>
public static bool DeleteCascading(Cascading model)
{
if (model != null)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString = "delete from ipvt_cascadingtable where CascadingID=?id";
var param = new MySqlParameter("?id", model.Id);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, param);
string sqliteSql = string.Format("delete from db_data where RowID={0}", model.RowId);
SqLiteHelper.ExecuteNonquery(con, sqliteSql);
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}
/// <summary>
/// 删除用户(绝对删除)
/// </summary>
/// <param name="uid">用户id</param>
/// <returns>受影响的行数</returns>
public static int DeleteUser(int uid)
{
string sql = "delete from ipvt_userregmessagetable where UserID=?id";
var param = new MySqlParameter[1];
param[0] = new MySqlParameter("?id", uid);
return(CustomMySqlHelper.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 删除用户(软删除)
/// </summary>
/// <param name="uid">用户id</param>
/// <returns>受影响的行数</returns>
public static int RemoveUser(int uid)
{
string sql = "update ipvt_userregmessagetable set IsEnable=0 where UserID=?id";
var param = new MySqlParameter[1];
param[0] = new MySqlParameter("?id", uid);
return(CustomMySqlHelper.ExecuteNonQuery(sql, param));
}
/// <summary>
/// 删除设备网点信息
/// </summary>
public static void ClearDeviceGeo(int did)
{
string sql = @" UPDATE ipvt_deviceinfotable SET GeoId=NULL where DeviceId=?id";
var param = new MySqlParameter[1];
param[0] = new MySqlParameter("?id", did);
CustomMySqlHelper.ExecuteNonQuery(sql, param);
}
//删除指定语音文件
private static void DeleteVoice(int id)
{
if (id > 0)
{
string sql =
"delete from ipvt_voicefiletable where ID=?id";
CustomMySqlHelper.ExecuteNonQuery(sql, new MySqlParameter("?id", id));
}
}
/// <summary>
/// 删除网点组信息
/// </summary>
public static void ClearGeo(int gid)
{
string sql = string.Format(@"DELETE FROM ipvt_geoinfotable where GeoID={0};
UPDATE ipvt_deviceinfotable SET GeoId=NULL where GeoId={0}", gid);
//var param = new MySqlParameter[2];
//param[0] = new MySqlParameter("?gid", gid);
//param[1] = new MySqlParameter("?gid2", gid);
int i = CustomMySqlHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int DeleteSdkSetupModel(SdkSetupModel model)
{
if (model != null && model.Id > 0)
{
string sql = "delete from ipvt_sdkinfotable where SdkID=?id";
return(CustomMySqlHelper.ExecuteNonQuery(sql,
new[] { new MySqlParameter("?id", model.Id) }));
}
return(-1);
}
/// <summary>
/// 更新寻呼话筒行政级别
/// </summary>
/// <param name="did"></param>
/// <param name="level"></param>
/// <returns></returns>
public static int UpdatePhoneLevel(int did, int level)
{
string sql = "update ipvt_deviceinfotable set PhoneLevel=?level where DeviceID=?id";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?level", level);
ps[1] = new MySqlParameter("?id", did);
return(CustomMySqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 改变指定id的用户的登录标识
/// </summary>
/// <param name="id">|用户id</param>
/// <param name="type">0：未登录 1：已登录</param>
/// <returns></returns>
public static int UpdateSetIsLogin(int id, int type)
{
string sql = "update ipvt_userregmessagetable set IsLogin=?type,LoginTime=?time where UserID=?id";
var ps = new MySqlParameter[3];
ps[0] = new MySqlParameter("?id", id);
ps[1] = new MySqlParameter("?type", type);
ps[2] = new MySqlParameter("?time", DateTime.Now);
return(CustomMySqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 更新设备所属组
/// </summary>
/// <param name="did"></param>
/// <param name="gid"></param>
public static void UpdateDeviceGroup(int did, int gid)
{
if (did > 0)
{
string sql = "UPDATE ipvt_deviceinfotable SET GroupID=?gid where DeviceId=?id";
var param = new MySqlParameter[2];
param[0] = new MySqlParameter("?gid", gid);
param[1] = new MySqlParameter("?id", did);
CustomMySqlHelper.ExecuteNonQuery(sql, param);
}
}
/// <summary>
/// 更新设备信息
/// </summary>
/// <param name="extension"></param>
public static void Update(Extension extension)
{
var sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE ipvt_extensionmessagetable SET PanelNum=?panelNum WHERE ExtensionID=?id ");
var param = new MySqlParameter[2];
param[0] = new MySqlParameter("?panelNum", extension.PanelNum);
param[1] = new MySqlParameter("?id", extension.Id);
CustomMySqlHelper.ExecuteNonQuery(sqlBuilder.ToString(), param);
}
/// <summary>
/// 替换转移分机号
/// </summary>
/// <param name="oldNo"></param>
/// <param name="newNo"></param>
public static void ReplaceTransferNo(string oldNo, string newNo)
{
string sql = "Update ipvt_transferinfotable set " +
"TransferExtensionNo=?newNo " +
"where TransferExtensionNo=?oldNo";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?newNo", newNo);
ps[1] = new MySqlParameter("?oldNo", oldNo);
CustomMySqlHelper.ExecuteNonQuery(sql, ps);
}
/// <summary>
/// 更新网点信息
/// </summary>
/// <param name="point">需要更新的网点信息</param>
public static void Update(GeoPoint point)
{
string sql =
"UPDATE ipvt_geoinfotable SET Name=?name,FormattedAddress=?address,Phone=?phone,Note=?note WHERE GeoID=?id";
var parameters = new MySqlParameter[5];
parameters[0] = new MySqlParameter("?name", point.Name);
parameters[1] = new MySqlParameter("?address", point.Address);
parameters[2] = new MySqlParameter("?id", point.Id);
parameters[3] = new MySqlParameter("?phone", point.Phone);
parameters[4] = new MySqlParameter("?note", point.Note);
CustomMySqlHelper.ExecuteNonQuery(sql, parameters);
}
/// <summary>
/// 更新职位
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private static int UpdatePosion(Position p)
{
if (p != null)
{
string sql = "update ipvt_positiontable set PositionName=?name where 1=1 and " +
"PositionID=?positionId";
var parameters = new MySqlParameter[2];
parameters[0] = new MySqlParameter("?name", p.PositionName);
parameters[1] = new MySqlParameter("?positionId", p.PositionId);
return(CustomMySqlHelper.ExecuteNonQuery(sql, parameters));
}
return(0);
}
/// <summary>
/// 删除分组
/// </summary>
/// <param name="group">组对象</param>
/// <returns>自增的id，失败则返回0</returns>
public static int DeleteGroup(DeviceGroup group)
{
string sql = "delete from ipvt_groupinfotable where GroupID=?id";
if (group != null)
{
var ps = new[]
{
new MySqlParameter("?pid", group.ParentId),
new MySqlParameter("?id", group.Id)
};
return(CustomMySqlHelper.ExecuteNonQuery(sql, ps));
}
return(0);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int UpdateSdkSetupModel(SdkSetupModel model)
{
if (model != null && model.Id > 0)
{
string sql = "update ipvt_sdkinfotable set SdkIP=?ip,SdkPort=?port where SdkID=?id";
return(CustomMySqlHelper.ExecuteNonQuery(sql,
new[]
{
new MySqlParameter("?ip", model.Ip),
new MySqlParameter("?port", model.Port),
new MySqlParameter("?id", model.Id)
}));
}
return(-1);
}
/// <summary>
/// 执行.sql文件
/// </summary>
/// <param name="path">.sql文件</param>
public static bool ExcuteSqlFile(string path)
{
if (File.Exists(path))
{
FileInfo file = new FileInfo(path);
string sql = file.OpenText().ReadToEnd();
int result = CustomMySqlHelper.ExecuteNonQuery(sql);
if (result >= 0)
{
return(true);
}
}
return(false);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool SaveVideoLinkageModel(VideoLinkageModel model)
{
if (model != null /*&& model.DeviceId > 0*/ && model.PanelNumber >= 0)//一代的设备面板号有0的
{
string sql;
if (model.Id > 0)
{
sql = "update ipvt_videolinkagetable set ChannelNumber=?cnum,NetConnectMethods=?thods," +
"ImgFormat=?img,TransmitJur=?jur,ReceivePort=?rport,ForwardingPort=?fport," +
"ServerUserId=?suid,ServerIp=?sip,TranServerIp=?tsip,UserName=?uname,UserPwd=?pwd," +
"DeviceType=?type,UserVerify=?verify" +
" where DeviceId=?did AND PanelNumber=?pnum";
}
else
{
sql = "insert into ipvt_videolinkagetable(DeviceId,PanelNumber,ChannelNumber,NetConnectMethods,"
+ "ImgFormat,TransmitJur,ReceivePort,ForwardingPort,ServerUserId,ServerIp,TranServerIp,"
+ "UserName,UserPwd,DeviceType,UserVerify) "
+ "values(?did,?pnum,?cnum,?thods,?img,?jur,?rport,?fport,?suid,?sip,?tsip,?uname,?pwd,?type,?verify)";
}
MySqlParameter[] ps = new MySqlParameter[15];
ps[0] = new MySqlParameter("?did", model.DeviceId);
ps[1] = new MySqlParameter("?pnum", model.PanelNumber);
ps[2] = new MySqlParameter("?cnum", model.ChannelNumber);
ps[3] = new MySqlParameter("?thods", model.NetConnectMethods);
ps[4] = new MySqlParameter("?img", model.ImgFormat);
ps[5] = new MySqlParameter("?jur", model.TransmitJur);
ps[6] = new MySqlParameter("?rport", model.ReceivePort);
ps[7] = new MySqlParameter("?fport", model.ForwardingPort);
ps[8] = new MySqlParameter("?suid", model.ServerUserId);
ps[9] = new MySqlParameter("?sip", model.ServerIp);
ps[10] = new MySqlParameter("?tsip", model.TranServerIp);
ps[11] = new MySqlParameter("?uname", model.UserName);
ps[12] = new MySqlParameter("?pwd", model.UserPwd);
ps[13] = new MySqlParameter("?type", model.DeviceType);
ps[14] = new MySqlParameter("?verify", model.UserVerify);
int result = CustomMySqlHelper.ExecuteNonQuery(sql, ps);
if (result > 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="model"></param>
/// <returns></returns>
public static bool DeleteVideoLinkageModel(VideoLinkageModel model)
{
if (model != null && model.DeviceId > 0 /*&& model.PanelNumber > 0*/) //允许话筒删除联动配置 yk 2017-12-21
{
string sql = "delete from ipvt_videolinkagetable where DeviceId=?did AND PanelNumber=?pnum;";
MySqlParameter[] ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?did", model.DeviceId);
ps[1] = new MySqlParameter("?pnum", model.PanelNumber);
int result = CustomMySqlHelper.ExecuteNonQuery(sql, ps);
if (result > 0)
{
return(true);
}
}
return(false);
}
/// <summary>
/// 更新设备信息
/// </summary>
/// <param name="device"></param>
public static void Update(Device device)
{
var sqlBuilder = new StringBuilder();
sqlBuilder.Append(
"UPDATE ipvt_deviceinfotable SET DeviceName=?dname,DeviceRegCode=?dregcode,FactoryNum=?dfnum,");
sqlBuilder.Append(
"DeviceIP=?dip,DevicePort=?dport,SoftVersion=?dsver,HardVersion=?dhver,Manufacturer=?dmft,GroupID=?dgrpid,GeoId=?geoid ");
sqlBuilder.Append("WHERE DeviceId=?id");
var param = new MySqlParameter[11];
param[0] = new MySqlParameter("?dname", device.Name);
param[1] = new MySqlParameter("?dregcode", string.Empty);
param[2] = new MySqlParameter("?dfnum", string.Empty);
param[3] = new MySqlParameter("?dip", device.Ip);
param[4] = new MySqlParameter("?dport", device.Port);
param[5] = new MySqlParameter("?dsver", device.SoftVersion);
param[6] = new MySqlParameter("?dhver", device.HardVersion);
param[7] = new MySqlParameter("?dmft", device.Manufacturer);
if (device.GroupId == 0)
{
param[8] = new MySqlParameter("?dgrpid", null);
}
else
{
param[8] = new MySqlParameter("?dgrpid", device.GroupId);
}
if (device.GeoPoint.Id != 0)
{
param[9] = new MySqlParameter("?geoid", device.GeoPoint.Id);
}
else
{
param[9] = new MySqlParameter("?geoid", null);
}
param[10] = new MySqlParameter("?id", device.Id);
CustomMySqlHelper.ExecuteNonQuery(sqlBuilder.ToString(), param);
}
/// <summary>
/// 自动写转移是更新转移设置表
/// </summary>
/// <param name="tran"></param>
/// <param name="id"></param>
public static void SetupTranser(Transfer tran, string id)
{
if (tran != null && !string.IsNullOrEmpty(id))
{
string sql = "UPDATE ipvt_transetuptable set StairTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?num),IsEnable=1 where ID=?id";
if (tran.Level == 2)
{
sql = "UPDATE ipvt_transetuptable set SecondTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?num),IsEnable=1 where ID=?id";
}
if (tran.Level == 3)
{
sql = "UPDATE ipvt_transetuptable set ThreeTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?num),IsEnable=1 where ID=?id";
}
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?id", id);
ps[1] = new MySqlParameter("?num", string.IsNullOrEmpty(tran.TransferExtensionNo) ? 0 : Convert.ToInt32(tran.TransferExtensionNo));
CustomMySqlHelper.ExecuteNonQuery(sql, ps);
}
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="user">用户对象</param>
/// <returns>受影响的行数</returns>
private static int UpdateUser(User user)
{
if (user != null)
{
string sql = "update ipvt_userregmessagetable set UserName=?name,Avatar=?avatar,Password=?pwd," +
"UserType=?type,RoleID=?roleId,StaffID=?sid,DisplayName=?disName where UserID=?userId";
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("?sid", user.UserStaff.Id);
parameters[6] = new MySqlParameter("?disName", user.DisplayName);
parameters[7] = new MySqlParameter("?userId", user.Id);
return(CustomMySqlHelper.ExecuteNonQuery(sql, parameters));
}
return(0);
}
/// <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="model"></param>
/// <returns>成功返回true</returns>
public static bool UpdataCascading(Cascading model)
{
if (model != null && model.Id != 0)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString =
"update ipvt_cascadingtable set Data_key=?key,Data=?data,Remark=?remark where CascadingID=?id";
var ps = new MySqlParameter[4];
ps[0] = new MySqlParameter("?key", model.Key);
ps[1] = new MySqlParameter("?data", model.Ip);
ps[2] = new MySqlParameter("?remark", model.Remark);
ps[3] = new MySqlParameter("?id", model.Id);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, ps); //更新
string sqliteString =
string.Format("update db_data set data_key='{0}',data='{1}' where RowID={2}",
model.Key, model.Ip, model.RowId);
SqLiteHelper.ExecuteNonquery(con, sqliteString); //SQLite更新
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}
/// <summary>
/// 保存语音文件名到数据库
/// </summary>
/// <param name="ss"></param>
public static bool SavaVoiceFile(string ss)
{
if (!string.IsNullOrEmpty(ss))
{
List <string> list;
GetVoiceList(out list);
if (!list.Contains(ss))
{
string sql = "insert into ipvt_voicefiletable(VoiceFileName) values(?name)";
var ps = new MySqlParameter("?name", ss);
var num = CustomMySqlHelper.ExecuteNonQuery(sql, ps);
if (num > 0)
{
return(true);
}
}
else
{
return(true);
}
}
return(false);
}
private static void SaveTranSetup(ObservableCollection <Transfer> transfers, string id)
{
if (!string.IsNullOrEmpty(id))
{
string one = string.Empty;
string two = string.Empty;
string three = string.Empty;
foreach (Transfer transfer in transfers)
{
if (transfer.Level == 1)
{
one = transfer.TransferExtensionNo;
}
if (transfer.Level == 2)
{
two = transfer.TransferExtensionNo;
}
if (transfer.Level == 3)
{
three = transfer.TransferExtensionNo;
}
}
//string sql = "UPDATE ipvt_transetuptable set StairTran=?one,SecondTran=?two,ThreeTran=?three,IsEnable=1 where ID=?id";
string sql =
"UPDATE ipvt_transetuptable set StairTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?one)," +
"SecondTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?two)," +
"ThreeTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?three),IsEnable=1 where ID=?id";
var ps = new MySqlParameter[4];
ps[0] = new MySqlParameter("?one", string.IsNullOrEmpty(one) ? 0 : Convert.ToInt32(one));
ps[1] = new MySqlParameter("?two", string.IsNullOrEmpty(two) ? 0 : Convert.ToInt32(two));
ps[2] = new MySqlParameter("?three", string.IsNullOrEmpty(three) ? 0 : Convert.ToInt32(three));
ps[3] = new MySqlParameter("?id", id);
CustomMySqlHelper.ExecuteNonQuery(sql, ps);
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns>成功返回true</returns>
public static bool InsertCascading(Cascading model)
{
if (model != null)
{
var con = SqLiteHelper.Open(_dbPath);
try
{
string mysqlString =
"insert into ipvt_cascadingtable(Data_key,Data,Remark) values(?key,?data,?remark)";
var ps = new MySqlParameter[3];
ps[0] = new MySqlParameter("?key", model.Key);
ps[1] = new MySqlParameter("?data", model.Ip);
ps[2] = new MySqlParameter("?remark", model.Remark);
CustomMySqlHelper.ExecuteNonQuery(mysqlString, ps);//插入
string sqliteString =
string.Format("insert into db_data(realm,data_key,data) values('hy_extern_domain','{0}','{1}')",
model.Key, model.Ip);
SqLiteHelper.ExecuteNonquery(con, sqliteString);//SQLite插入
return(true);
}
catch (Exception ex)
{
LogHelper.MainLog(ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
return(false);
}