/// <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="time">过去时间</param>
public static int DeleteLogs(DateTime time)
{
if (HaveDb())
{
string sql = "delete from log where logtime<@time";
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@time", time)));
}
return(0);
}
/// <summary>
/// 删除设备
/// </summary>
/// <param name="device"></param>
public static int DeleteDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "delete from device where did=@did";
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@did", device.Id)));
}
return(-1);
}
/// <summary>
/// 插入日志
/// </summary>
/// <param name="log"></param>
public static int InsetLog(LogMessage log)
{
if (log != null)
{
HaveDb();
string sql = "insert into log(logtime,logmsg,logtype,username,caller,callpanel) values(@time,@msg,@type,@name,@caller,@pan)";
SQLiteParameter[] ps = new SQLiteParameter[6];
ps[0] = new SQLiteParameter("@time", log.LogTime);
ps[1] = new SQLiteParameter("@msg", log.LogMsg);
ps[2] = new SQLiteParameter("@type", log.LogType);
ps[3] = new SQLiteParameter("@name", log.OperaterId);
ps[4] = new SQLiteParameter("@caller", Convert.ToInt32(log.FromNo));
ps[5] = new SQLiteParameter("@pan", log.PanelNum);
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, ps));
}
return(-1);
}
/// <summary>
/// 更新设备
/// </summary>
/// <param name="device"></param>
public static int UpdateDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "update device set ip=@ip,name=@name,type=@type,generation=@gen,extno=@ext,panelnum=@pan where did=@did";
SQLiteParameter[] ps = new SQLiteParameter[7];
ps[0] = new SQLiteParameter("@did", device.Id);
ps[1] = new SQLiteParameter("@ip", device.Ip);
ps[2] = new SQLiteParameter("@name", device.Name);
ps[3] = new SQLiteParameter("@type", device.Type);
ps[4] = new SQLiteParameter("@gen", device.Generation);
ps[5] = new SQLiteParameter("@ext", device.Extension.Number);
ps[6] = new SQLiteParameter("@pan", device.PanelNum);
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, ps));
}
return(-1);
}
/// <summary>
/// 插入设备
/// </summary>
/// <param name="device"></param>
public static int InsetDevice(Device device)
{
if (device != null)
{
HaveDb();
string sql = "insert into device(did,ip,name,type,generation,extno,panelnum) values(@did,@ip,@name,@type,@gen,@ext,@pan)";
SQLiteParameter[] ps = new SQLiteParameter[7];
ps[0] = new SQLiteParameter("@did", device.Id);
ps[1] = new SQLiteParameter("@ip", device.Ip);
ps[2] = new SQLiteParameter("@name", device.Name);
ps[3] = new SQLiteParameter("@type", device.Type);
ps[4] = new SQLiteParameter("@gen", device.Generation);
ps[5] = new SQLiteParameter("@ext", device.Extension.Number);
ps[6] = new SQLiteParameter("@pan", device.PanelNum);
return(SqLiteHelper.ExecuteNonquery(SqLiteHelper.Open(_dbPath), sql, ps));
}
return(-1);
}
/// <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>
/// 修改
/// </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="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);
}
/// <summary>
/// 获取日志
/// </summary>
/// <param name="type">日志类型</param>
/// <param name="logs"></param>
public static void GetLogs(LogTypeEnum type, out List <LogMessage> logs)
{
logs = new List <LogMessage>();
if (HaveDb())
{
int num = 0;
switch (type)
{
case LogTypeEnum.System: //系统日志10
num = 10;
break;
case LogTypeEnum.Operation: //操作日志11
num = 11;
break;
case LogTypeEnum.Call: //呼叫日志12
num = 12;
break;
case LogTypeEnum.Video: //视频日志13
num = 13;
break;
case LogTypeEnum.Alarm: //报警日志14
num = 14;
break;
case LogTypeEnum.OrdinaryMsg: //普通消息
num = 15;
break;
case LogTypeEnum.MissedCalled: //未接来电16
num = 16;
break;
case LogTypeEnum.PanelMsg: //面板消息17
num = 17;
break;
}
if (num != 0)
{
SQLiteDataReader reader = null;
try
{
string sql = "select * from log where logtype=@type order by RowID DESC";
reader = SqLiteHelper.ExcuteReader(SqLiteHelper.Open(_dbPath), sql, new SQLiteParameter("@type", num));
while (reader.Read())
{
LogMessage log = new LogMessage();
log.LogTime = EvaluationHelper.ObjectToDateTime(reader["logtime"]);
log.LogMsg = EvaluationHelper.ObjectToString(reader["logmsg"]);
log.LogType = EvaluationHelper.ObjectToInt(reader["logtype"]);
log.OperaterId = EvaluationHelper.ObjectToString(reader["username"]);
log.FromNo = EvaluationHelper.ObjectToString(reader["caller"]);
log.PanelNum = EvaluationHelper.ObjectToInt(reader["callpanel"]);
logs.Add(log);
}
}
catch (Exception ex)
{
LogHelper.MainLog(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
}
