public List <WarningRecordDetails> QueryWarningDataView(out string msg, DataTable dt, string starttime, string endtime)
{
msg = string.Empty;
string _and = " and ";
List <WarningRecordDetails> list = new List <WarningRecordDetails>();
try
{
string sqlstr = string.Empty;
if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
{
sqlstr += "warningtime > '" + starttime + "' " + _and + " warningtime < '" + endtime + "' ";
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("执行sql简化语句：{0}", sqlstr));
DataRow[] drs = dt.Select(sqlstr);
foreach (DataRow dw in drs)
{
WarningRecordDetails ob = new WarningRecordDetails
{
_warningdatetime = DateTime.Parse(dw[0].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
_warningType = dw[1].ToString(),
_warningStatus = dw[2].ToString()
};
list.Add(ob);
}
msg = "Success";
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("筛选数据完成"));
return(list);
}
catch (Exception ex)
{
msg = "执行查询数据表异常：" + ex.Message;
return(list);
}
}
/// <summary>
/// 解析授权文件，返回存在的加密数据
/// </summary>
/// <returns></returns>
public List <string> ReturnCheckData()
{
string file = LiceneseDir;
/// <summary>
/// 日志文件数据流
/// </summary>
FileStream fs = null;
List <string> outlist = new List <string>();
try
{
fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read);
//fs.Seek(0, System.IO.SeekOrigin.End);
StreamReader streamReader = new StreamReader(fs);
string line = string.Empty;
while ((line = streamReader.ReadLine()) != null)
{
outlist.Add(line);
}
return(outlist);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("读取授权文件异常:{0}", ex.Message));
return(outlist);
}
finally
{
if (fs != null)
{
fs.Close();
fs = null;
}
}
}
/// <summary>
/// 根据条件写事件日志函数
/// </summary>
/// <param name="address"></param>
/// <param name="Event"></param>
/// <param name="param"></param>
/// <param name="value"></param>
public void TraceEvent(long address, string Event, string param, string value = "")
{
try
{
string s = string.Empty;
if (address != 0L)
{
s = address.ToString("X12");
}
switch (value)
{
case "Failed":
RecordLog.GetInstance().WriteLog(Level.Error, "Address:" + s + ",TX:" + Event + ",param:" + param);
break;
case "Success":
RecordLog.GetInstance().WriteLog(Level.Info, "Address:" + s + ",TX:" + Event + ",param:" + param);
break;
default:
RecordLog.GetInstance().WriteLog(Level.Info, "Address:" + s + ",TX:" + Event + ",param:" + param + ",value:" + value);
break;
}
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, "Exception: " + ex.Message);
}
}
/// <summary>
/// 记录对应门锁信息
/// </summary>
/// <param name="lockInfo"></param>
public void AddLockMap(string name, string mac, string timedate)
{
string LocalPath = baseDir + @"\" + infopath;
try
{
string param = name + "," + mac + "," + timedate;
fs = new FileStream(LocalPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
fs.Seek(0, System.IO.SeekOrigin.End);
sw = new StreamWriter(fs, System.Text.Encoding.UTF8);
sw.WriteLine(param);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, string.Format("Exception:{0}", ex.Message));
}
finally
{
if (sw != null)
{
sw.Close();
sw = null;
}
if (fs != null)
{
fs.Close();
fs = null;
}
}
}
/// <summary>
/// 修改已经存在的配对文件
/// </summary>
/// <param name="name"></param>
/// <param name="mac"></param>
/// <param name="timedate"></param>
public void ModifyLockTime(string name, string mac, string timedate)
{
try
{
string LocalPath = baseDir + @"\" + infopath;
//先删除指定行
List <string> lines = new List <string>(File.ReadAllLines(LocalPath));//先读取到内存变量
if (lines.Count != 0)
{
string param = string.Empty;
for (int i = 0; i < lines.Count; i++)
{
if (mac.Equals(lines[i].Split(',')[1].ToString()))
{
lines.RemoveAt(i);//指定删除的行
param = name + "," + mac + "," + timedate;
lines.Insert(i, param);
}
}
File.WriteAllLines(LocalPath, lines.ToArray());//在写回硬盤
}
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, string.Format("Exception:{0}", ex.Message));
}
}
public static string Value()
{
if (string.IsNullOrEmpty(fingerPrint))
{
RecordLog.GetInstance().WriteLog(Level.Info, "生成设备指纹信息");
fingerPrint = GetHash("CPU >> " + cpuId() /*+ "\nBIOS >> " + biosId() + "\nBASE >> " + baseId() + videoId() + "\nMAC >> " + macId()*/);
}
return(fingerPrint);
}
/// <summary>
/// 获取GATT服务
/// </summary>
public void GetService()
{
try
{
this._fServices = null;
int resServices = this._client.ReadServices(wclGattOperationFlag.goNone, out this._fServices);
if (resServices != 0)
{
RecordLog.GetInstance().WriteLog(Level.Warning, "Error: 0x" + resServices.ToString("X8"));
//MessageBox.Show("Error: 0x" + resServices.ToString("X8"), "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
this.TraceEvent(this._client.Address, "Services", "FF00", "Failed");
this._client.Disconnect();
return;
}
this.TraceEvent(this._client.Address, "Services", "FF00", "Success");
if (this._fServices == null)
{
return;
}
foreach (wclGattService service in this._fServices)
{
if (service.Uuid.IsShortUuid && this._uuid.ShortUuid == service.Uuid.ShortUuid)
{
if (this._client.ReadCharacteristics(service, wclGattOperationFlag.goNone, out this._fCharacteristics) != 0)
{
return;
}
foreach (wclGattCharacteristic character in this._fCharacteristics)
{
if (character.Uuid.ShortUuid == this._rxUuid)
{
if (this._client.Subscribe(character) != 0)
{
this.TraceEvent(this._client.Address, "Subs", "RX", "Failed");
return;
}
this.TraceEvent(this._client.Address, "Subs", "RX", "Success");
if (this._client.ReadDescriptors(character, wclGattOperationFlag.goNone, out this._fDescriptors) != 0)
{
this.TraceEvent(this._client.Address, "Subs", "RX", "Failed to get description");
return;
}
}
else if (character.Uuid.ShortUuid == this._txUuid)
{
this._txCharacteristic = character;
}
}
}
}
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, "Exception: " + ex.Message);
}
}
/// <summary>
/// 首先判断是否存在已知门锁对应信息
/// </summary>
/// <param name="status">返回的内容信息</param>
/// <returns>0为返回，其他为失败</returns>
public int judelkconnect(out int status)
{
status = 0;
string LocalPath = baseDir + @"\" + infopath;
try
{
fs = new FileStream(LocalPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
if (fs.Length > 8)
{
StreamReader read = new StreamReader(fs);
//循环读取每一行
string strReadline = string.Empty;
while ((strReadline = read.ReadLine()) != null)
{
//name
string temp = strReadline.Split(',')[0].ToString();
temp += ",";
//mac
temp += strReadline.Split(',')[1].ToString();
temp += ",";
//lastConnecttime
temp += strReadline.Split(',')[2].ToString();
//
EventClass.GetInstance().knowsLock.Add(temp);
}
RecordLog.GetInstance().WriteLog(Level.Info, "读取对应门锁信息文件成功");
status = 1;
}
return(0);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, string.Format("Exception:{0}", ex.Message));
return(-1);
}
finally
{
if (sw != null)
{
sw.Close();
sw = null;
}
if (fs != null)
{
fs.Close();
fs = null;
}
}
}
/// <summary>
/// 删除device.info文件
/// </summary>
public void DeletDeviceInfoFile()
{
string LocalPath = baseDir + @"\" + infopath;
try
{
if (File.Exists(LocalPath))
{
File.Delete(LocalPath);
RecordLog.GetInstance().WriteLog(Level.Info, "删除蓝牙设备信息文件");
}
}
catch (Exception ex)
{
MessageBox.Show("Exception" + ex.Message);
return;
}
}
/// <summary>
/// 判断listeners文件是否存在
/// </summary>
/// <returns></returns>
public bool IsHaveListenersFile()
{
try
{
LiceneseDir = baseDir + @"\" + licName;
if (!File.Exists(LiceneseDir))
{
RecordLog.GetInstance().WriteLog(Level.Error, "授权文件不存在");
return(false);
}
RecordLog.GetInstance().WriteLog(Level.Info, "检测到授权文件");
return(true);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, string.Format("检查授权文件异常：{0}", ex.Message));
return(false);
}
}
/// <summary>
/// 判断密钥是否正确
/// </summary>
/// <param name="sourdata"></param>
/// <param name="destdata"></param>
/// <returns></returns>
public bool CheckLockDecValue(string sourdata, List <string> destdata)
{
try
{
foreach (var dest in destdata)
{
if (sourdata.Equals(dest))
{
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("数据授权成功"));
return(true);
}
}
return(false);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, string.Format("判断授权密钥异常:{0}", ex.Message));
return(false);
}
}
/// <summary>
/// 本机蓝牙信息
/// </summary>
/// <returns></returns>
public wclBluetoothRadio GetRadio()
{
try
{
for (int i = 0; i < this._manager.Count; i++)
{
if (this._manager[i].Available)
{
return(this._manager[i]);
}
}
RecordLog.GetInstance().WriteLog(Level.Warning, "No one Bluetooth Radio found.");
//MessageBox.Show("No one Bluetooth Radio found.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
return(null);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, "No one Bluetooth Radio found. Exception: " + ex.Message);
return(null);
}
}
/// <summary>
/// 发送报文
/// </summary>
/// <param name="package"></param>
public void WriteCommand(byte[] package)
{
try
{
string str = string.Empty;
for (int i = 0; i < package.Length; i++)
{
str += package[i].ToString("X2");
}
if (this._client.WriteCharacteristicValue(_txCharacteristic, package, wclGattProtectionLevel.plNone) != 0)
{
this.TraceEvent(_client.Address, "TX", "Status", "Failed");
return;
}
this.TraceEvent(_client.Address, "TX", "Data", str);
this.TraceEvent(_client.Address, "TX", "Status", "Success");
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, "Exception: " + ex.Message);
}
}
/// <summary>
/// 获取记录操作函数
/// </summary>
/// <param name="data"></param>
private static void OperationRecordHandler(byte[] data)
{
if (data[3] == 2)//0x02
{
switch (data[5])
{
case 2: //0x02
//RecordLog.GetInstance().WriteLog(Level.Info, "操作错误.");
break;
case 3: //0x03
RecordLog.GetInstance().WriteLog(Level.Info, "所有开门事件记录已经上报.");
return;
case 4: //0x04
RecordLog.GetInstance().WriteLog(Level.Info, "所有操作记录等非开门事件已经上报.");
return;
default:
return;
}
}
else
{
BleProtocol.Record = new OperationRecord();
int year = BleProtocol.Bcd2Int(data[10]);
int month = BleProtocol.Bcd2Int(data[9]);
int day = BleProtocol.Bcd2Int(data[8]);
int hour = BleProtocol.Bcd2Int(data[7]);
int minute = BleProtocol.Bcd2Int(data[6]);
int second = BleProtocol.Bcd2Int(data[5]);
BleProtocol.Record.Date = new DateTime(2000 + year, month, day, hour, minute, second); // 日期
//BleProtocol.Record.UserId = (int)data[11] + ((int)data[12] << 8); //id
BleProtocol.Record.UserId = (int)data[11]; //id
BleProtocol.Record.OperationType = (int)data[12] + (int)data[13]; //机械操作方式
BleProtocol.Record.UserType = (int)data[14] + (int)data[15]; //开锁方式
EventClass.GetInstance()._battery = (int)data[16];
}
}
/// <summary>
/// 将已经获取到的日志放到DataTable环境中保存
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
public DataTable CreatNewDataTable(string tablename = "")
{
DataTable dt = new DataTable(tablename);
try
{
if (tablename == "gloabledataTable")
{
dt.Columns.Add("openlocktime", typeof(DateTime));
dt.Columns.Add("lockmacid", typeof(System.String));
dt.Columns.Add("lockid", typeof(System.String));
dt.Columns.Add("lockmodifyname", typeof(System.String));
dt.Columns.Add("openlocktype", typeof(System.String));
dt.Columns.Add("userid", typeof(System.String));
dt.Columns.Add("username", typeof(System.String));
dt.Columns.Add("userjob", typeof(System.String));
dt.Columns.Add("userlevel", typeof(System.String));
dt.Columns.Add("opentime", typeof(System.String));
dt.Columns.Add("opentotalTime", typeof(System.String));
dt.Columns.Add("openlimitTimes", typeof(System.String));
dt.Columns.Add("openexception", typeof(System.String));
dt.Columns.Add("userstatus", typeof(System.String));
}
if (tablename == "warningdataTable")
{
dt.Columns.Add("warningtime", typeof(DateTime));
dt.Columns.Add("warningtype", typeof(System.String));
dt.Columns.Add("warningstatus", typeof(System.String));
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("创建指定数据表{0}完成", tablename));
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("创建[" + tablename + "]数据表异常：" + ex.Message);
return(dt);
}
}
/// <summary>
/// 数据导入
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public System.Data.DataTable ImportExcelToDataTable(string tablename)
{
try
{
string path = string.Empty;
OpenFileDialog pOpenFileDialog = new OpenFileDialog();
pOpenFileDialog.Filter = "Execl 表格文件 (*.xls)| *.xls";//若打开指定类型的文件只需修改Filter，如打开txt文件，改为*.txt即可
pOpenFileDialog.Multiselect = false;
pOpenFileDialog.Title = "打开Execl 表格文件";
if (pOpenFileDialog.ShowDialog() == DialogResult.OK)
{
path = pOpenFileDialog.FileName;
}
string conStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data source={0}; Extended Properties=Excel 12.0;", path);
using (OleDbConnection conn = new OleDbConnection(conStr))
{
conn.Open();
//获取所有Sheet的相关信息
System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//获取第一个 Sheet的名称
string sheetName = dtSheet.Rows[0]["Table_Name"].ToString();
string sql = string.Format("select * from [{0}]", sheetName);
using (OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn))
{
System.Data.DataTable dt = new System.Data.DataTable(tablename);
Task ta = Task.Factory.StartNew(delegate { oda.Fill(dt); });
Task.WaitAny(ta);
return(dt);
}
}
}
catch (Exception ex)
{
MessageBox.Show("选择导入文件异常", "系统提示");
RecordLog.GetInstance().WriteLog(Level.Error, "导入错误：" + ex.Message);
return(null);
}
}
/// <summary>
/// 拆包函数
/// </summary>
/// <param name="data"></param>
public static void PackageAnalyze(byte[] data)
{
try
{
byte[] dest = new byte[data.Length - 3];
for (int i = 2; i < data.Length - 1; i++)
{
dest[i - 2] = data[i];
}
if (data[0] == 0xAA && data[1] == 0x55)
{
byte b = data[data.Length - 1];
byte crcCalced = CRC.Crc8(dest);
if (b == crcCalced)
{
BleProtocol.CommandHandler(data[4] & 127, data);
}
}
}
catch
{
RecordLog.GetInstance().WriteLog(Level.Error, "PackageAnalyze Error.");
}
}
/// <summary>
/// 读取报警记录函数
/// </summary>
/// <param name="data"></param>
private static void WarningRecordHandler(byte[] data)
{
if (data[3] == 2)//0x02
{
switch (data[5])
{
case 2: //0x02
//RecordLog.GetInstance().WriteLog(Level.Info, "操作错误.");
break;
case 3: //0x03
RecordLog.GetInstance().WriteLog(Level.Info, "开门报警事件全部上报.");
return;
case 4: //0x04
RecordLog.GetInstance().WriteLog(Level.Info, "配置等其他报警事件已上报.");
return;
default:
return;
}
}
else
{
BleProtocol.warningRecord = new WarningRecord();
int year = BleProtocol.Bcd2Int(data[10]);
int month = BleProtocol.Bcd2Int(data[9]);
int day = BleProtocol.Bcd2Int(data[8]);
int hour = BleProtocol.Bcd2Int(data[7]);
int minute = BleProtocol.Bcd2Int(data[6]);
int second = BleProtocol.Bcd2Int(data[5]);
BleProtocol.warningRecord.Date = new DateTime(2000 + year, month, day, hour, minute, second);
BleProtocol.warningRecord.WarningType = (int)data[11];
BleProtocol.warningRecord.WarningStatus = (int)data[12];
}
}
/// <summary>
/// 3，对外接口：连接BLE设备
/// </summary>
/// <param name="MacAddress"></param>
/// <returns></returns>
public string ConnectDevice(string MacAddress)
{
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("开始配对蓝牙地址,Mac:{0}", MacAddress));
LicenseHelper licenseHelper = new LicenseHelper();
if (!licenseHelper.IsHaveListenersFile())
{
MessageBox.Show("授权文件不存在，请联系系统管理员", "系统提示");
return("Error");
}
List <string> CheckData = licenseHelper.ReturnCheckData();
if (CheckData.Count == 0)
{
MessageBox.Show("授权文件信息不存在，请联系系统管理员", "系统提示");
return("Error");
}
string name = string.Empty;
string mac = string.Empty;
string outCheckValue = string.Empty;
//连接地址的时候判断授权文件
foreach (var s in EventClass.GetInstance()._dicdeviceAddress)
{
if (s.Value.ToString().Equals(MacAddress))
{
bool res = licenseHelper.EncrptyData(s.Key.ToString(), s.Value.ToString(), out outCheckValue);
if (res == true)
{
bool res2 = licenseHelper.CheckLockDecValue(outCheckValue, CheckData);
if (res2 == true)
{
RecordLog.GetInstance().WriteLog(Level.Info, "开始连接设备: " + MacAddress.ToString());
EventClass.GetInstance()._ConnectBleAddress = MacAddress;
//EventClass.GetInstance()._eventDelegate.OnHandlerConnect();
//return "Success";
string msg = string.Empty;
List <lockDetail> objlist = RedvelopRecord.GetInstance().GetlockSetting(out msg);
if (objlist == null)
{
MessageBox.Show("系统尚未配置门锁文件", "系统提示");
return("Error");
}
foreach (lockDetail lockDetail in objlist)
{
if (lockDetail._id.Equals(EventClass.GetInstance()._ConnectBleAddress))
{
OpenAppDo.GetInstance().ModifyLockTime(lockDetail._modifyName, lockDetail._id, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//OpenAppDo.GetInstance().AddLockMap(lockDetail._modifyName, lockDetail._id, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
EventClass.GetInstance()._client.Address = Convert.ToInt64(MacAddress, 16);
wclBluetoothRadio radio = EventClass.GetInstance()._devices[MacAddress];
if (EventClass.GetInstance()._client.Connect(radio) != 0)
{
RecordLog.GetInstance().WriteLog(Level.Info, "设备连接失败");
MessageBox.Show("设备连接失败", "提示");
return("Error");
}
RecordLog.GetInstance().WriteLog(Level.Info, "设备连接成功");
EventClass.GetInstance()._dicdeviceAddress.Clear();
return("Success");
}
else
{
MessageBox.Show(string.Format("{0}门锁未授权，请联系系统管理员", s.Key.ToString()), "系统提示");
return("Error");
}
}
else
{
MessageBox.Show(string.Format("处理授权文件失败，请联系系统管理员"), "系统提示");
return("Error");
}
}
}
MessageBox.Show("未找到适配的蓝牙设备", "系统提示");
return("Error");
}
/// <summary>
/// 2，对外接口：查看已经搜索到的所有BLE设备
/// </summary>
/// <returns></returns>
public string ViewDevice()
{
List <outParam> outdata = new List <outParam>();
//string outParam = string.Empty;
if (EventClass.GetInstance()._processFlags == 1)
{
RecordLog.GetInstance().WriteLog(Level.Info, "查找结束");
}
if (EventClass.GetInstance()._deviceAddress.Count <= 0)
{
return("");
}
RecordDeviceInfo.GetInstance().DeletDeviceInfoFile();
string lastmac = string.Empty;
string lastTemp = string.Empty;
int status = 0;
int ret = OpenAppDo.GetInstance().judelkconnect(out status);
if (ret == 0)
{
if (status != 0)
{
RecordLog.GetInstance().WriteLog(Level.Info, "发现已经存在蓝牙信息");
List <string> outStrList = EventClass.GetInstance().knowsLock;
foreach (string splitparam in EventClass.GetInstance()._deviceAddress)
{
foreach (string lockInfo in outStrList)
{
if (lockInfo.Split(',')[1].ToString().Equals(splitparam.Split('|')[1].ToString()))
{
//mac相同才会显示
outParam temp = new outParam()
{
Name = lockInfo.Split(',')[0].ToString(),
Mac = lockInfo.Split(',')[1].ToString(),
SetTime = lockInfo.Split(',')[2].ToString(),
};
outdata.Add(temp);
if (lastmac == lockInfo.Split(',')[1].ToString())
{
continue;
}
lastmac = temp.Mac;
//判断上次地址和当前地址是否一致
EventClass.GetInstance()._dicdeviceAddress.Add(splitparam.Split('|')[0].ToString(), splitparam.Split('|')[1].ToString());
RecordDeviceInfo.GetInstance().WriteLocalInfo(splitparam.Split('|')[0].ToString(), splitparam.Split('|')[1].ToString(), FingerPrint.Value().ToString());
}
}
}
EventClass.GetInstance().knowsLock.Clear();
}
else
{
foreach (string splitparam in EventClass.GetInstance()._deviceAddress)
{
//记录本次地址 start
string msg = string.Empty;
if (lastTemp != splitparam.Split('|')[1].ToString())
{
RedvelopRecord.GetInstance().RegeditDoorSetting(out msg, splitparam.Split('|')[1].ToString());
lastTemp = splitparam.Split('|')[1].ToString();
}
//记录本次地址 end
EventClass.GetInstance()._dicdeviceAddress.Add(splitparam.Split('|')[0].ToString(), splitparam.Split('|')[1].ToString());
RecordDeviceInfo.GetInstance().WriteLocalInfo(splitparam.Split('|')[0].ToString(), splitparam.Split('|')[1].ToString(), FingerPrint.Value().ToString());
outParam temp = new outParam()
{
Name = "",
Mac = splitparam.Split('|')[1].ToString(),
SetTime = "",
};
outdata.Add(temp);
}
}
}
//[{"Name":"前门","Mac":"0CAE7DAB5F72","SetTime":"2018-10-16 03:00:45"}]
string json = JsonConvert.SerializeObject(outdata);
return(json);
}
/// <summary>
/// 导入数据分页展示
/// </summary>
/// <param name="page"></param>
/// <param name="counts"></param>
/// <returns></returns>
public string GetRecordImportEcxel(string page, string counts)
{
RecordGolable goableData = new RecordGolable();
List <RecordLogsDataDetails> tempList = new List <RecordLogsDataDetails>();
Task ta = Task.Factory.StartNew(delegate { goableData._recordlogsdatadetailsList = RedvelopRecord.GetInstance().ReadDataTableToMemory(EventClass.GetInstance().tb); });
Task.WaitAny(ta);
int iPage = Int32.Parse(page);
int iCounts = Int32.Parse(counts);
try
{
int totalcounts = goableData._recordlogsdatadetailsList.Count;
int totalpages = 0;
if (totalcounts % Int32.Parse(counts) > 0)
{
totalpages = totalcounts / Int32.Parse(counts) + 1;
}
else
{
totalpages = totalcounts / Int32.Parse(counts);
}
if (Int32.Parse(page) > totalpages)
{
//MessageBox.Show("当前输入页数大于总页数", "系统提示");
return("[]");
}
for (int i = 0; i < iCounts; i++)
{
if (((iPage - 1) * iCounts + i) == totalcounts)
{
break;
}
if (goableData._recordlogsdatadetailsList[(iPage - 1) * iCounts + i].openlocktime != "")
{
tempList.Add(goableData._recordlogsdatadetailsList[(iPage - 1) * iCounts + i]);
}
else
{
break;
}
//RecordLog.GetInstance().WriteLog(Level.Info, string.Format("查询分页返回数据完成,page:[{0}],count:[{1}]", iPage, (iPage - 1) * iCounts + i + 1));
}
object obj = new
{
totalcounts = totalcounts,
pagedata = tempList
};
string json = JsonConvert.SerializeObject(obj);
//string json = JsonConvert.SerializeObject(goableData._recordlogsdatadetailsList);
return(json);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Info, "分页时异常：" + ex.Message);
return("");
}
}
/// <summary>
/// 全盘分析
/// </summary>
/// <param name="msg"></param>
/// <param name="dt"></param>
/// <param name="lockname"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <returns></returns>
public RecordGolable totalAnalysis(out string msg, DataTable dt, string lockname, string starttime, string endtime)
{
RecordGolable recordGolable = new RecordGolable();
List <userinfoAnalysis> userinfolist = new List <userinfoAnalysis>();
msg = string.Empty;
string _and = " and ";
int _yeat = DateTime.Parse(endtime).Year;
int _startMonth = DateTime.Parse(starttime).Month;
int _endMonth = DateTime.Parse(endtime).Month;
try
{
//所有用户正常次数总和
int normal = 0;
//所有用户异常次数总和
int exception = 0;
//所有用户次数总和
int total = 0;
//读取用户信息
List <userDetails> userDetails = RedvelopRecord.GetInstance().getuserDetails(out msg);
if (msg != "")
{
return(recordGolable);
}
foreach (userDetails userinfo in userDetails)
{
List <analysisdata> analysisdataslist = new List <analysisdata>();
if (userinfo._useName == "")
{
continue;
}
string username = userinfo._useName;
//该用户所有正常次数
int allnormaltimes = 0;
//该用户所有异常次数
int allexceptiontimes = 0;
//该用户所有次数
int alltotaltimes = 0;
//该用户异常次数情况占比（与所有总异常次数比）
string abnormaloccupancyrate = string.Empty;
//时间节点
for (int m = 1; m <= (_endMonth - _startMonth) + 1; m++)
{
AnalysisData analysisData = new AnalysisData();
//门锁名称
string sqlstr = string.Empty;
if (string.IsNullOrEmpty(lockname))
{
MessageBox.Show("未选择门锁", "提示");
msg = "未选择门锁";
return(null);
}
if (string.IsNullOrEmpty(starttime) || string.IsNullOrEmpty(endtime))
{
MessageBox.Show("时间段选择不正确", "提示");
msg = "时间段选择不正确";
return(null);
}
if (!string.IsNullOrEmpty(lockname))
{
sqlstr += "lockmodifyname like '%" + lockname + "%' ";
}
sqlstr += _and;
if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
{
sqlstr += "openlocktime > '" + DateTime.Parse(_yeat + "-" + (_startMonth + m - 1).ToString()) + "' " + _and +
" openlocktime < '" + DateTime.Parse(_yeat + "-" + (_startMonth + m).ToString()) + "' ";
if (!string.IsNullOrEmpty(username))
{
sqlstr += _and;
}
}
if (!string.IsNullOrEmpty(username))
{
sqlstr += "username = '******' ";
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("执行sql简化语句：{0}", sqlstr));
DataRow[] drs = dt.Select(sqlstr);
analysisdata analysisdata = new analysisdata();
if (drs.Count() != 0)
{
//开门总次数
int totaltimes = drs.Count();
//异常总次数
int exceptiontimes = 0;
foreach (DataRow dw in drs)
{
if ("异常" == dw[13].ToString())
{
exceptiontimes++;
}
}
//正常总次数
int normaltimes = totaltimes - exceptiontimes;
//异常率
double ayt = (double)0;
if ((double)exception == 0)
{
ayt = (double)0;
}
else
{
ayt = (double)exceptiontimes / (double)totaltimes;
}
decimal outtemp = Math.Round(Convert.ToDecimal(ayt), 2, MidpointRounding.AwayFromZero);
string abnormalityrate = ((int)(outtemp * 100)).ToString() + "%";
analysisdata = new analysisdata {
normaltimes = normaltimes,
exceptiontimes = exceptiontimes,
totaltimes = totaltimes,
abnormalityrate = abnormalityrate,
};
allnormaltimes += normaltimes;
allexceptiontimes += exceptiontimes;
alltotaltimes += totaltimes;
}
analysisdata.month = _startMonth + m - 1;
analysisdataslist.Add(analysisdata);
}
normal += allnormaltimes;
exception += allexceptiontimes;
total += alltotaltimes;
userinfolist.Add(new userinfoAnalysis
{
name = username,
analysisdata = analysisdataslist,
allnormaltimes = allnormaltimes,
allexceptiontimes = allexceptiontimes,
alltotaltimes = alltotaltimes
});
}
recordGolable._analysisData.normal = normal;
recordGolable._analysisData.exception = exception;
recordGolable._analysisData.total = total;
string abnormaloccupancyrateTmp = string.Empty;
foreach (userDetails userinfo in userDetails)
{
foreach (var user in userinfolist)
{
if (user.name == userinfo._useName)
{
double ayt = (double)0;
if ((double)exception == 0)
{
ayt = (double)0;
}
else
{
ayt = (double)user.allexceptiontimes / (double)exception;
}
decimal outtemp = Math.Round(Convert.ToDecimal(ayt), 2, MidpointRounding.AwayFromZero);
user.abnormaloccupancyrate = ((int)(outtemp * 100)).ToString() + "%";
recordGolable._analysisData.userinfo.Add(user);
}
}
}
msg = "Success";
return(recordGolable);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, "Ecxeption：" + ex.Message);
return(null);
}
}
/// <summary>
/// 向表中添加数据
/// </summary>
/// <param name="dt"></param>
/// <param name="recordLogsDataDetailsList"></param>
/// <returns></returns>
public DataTable AddTableData(DataTable dt, List <RecordLogsDataDetails> recordLogsDataDetailsList,
List <WarningRecordDetails> WarningDetailsList, string ReadType)
{
try
{
if (ReadType == "record")
{
dt.Rows.Clear();
foreach (RecordLogsDataDetails _rlds in recordLogsDataDetailsList)
{
if (_rlds != null)
{
dt.Rows.Add(
DateTime.Parse(_rlds.openlocktime),
_rlds.lockmacid,
_rlds.lockid,
_rlds.lockmodifyname,
_rlds.openlocktype,
_rlds.userid,
_rlds.username,
_rlds.userjob,
_rlds.userlevel,
_rlds.opentime,
_rlds.openlimitTimes,
_rlds.opentotalTime,
_rlds.openexception,
_rlds.userstatus
);
}
else
{
continue;
}
}
}
if (ReadType == "warning")
{
dt.Rows.Clear();
foreach (WarningRecordDetails _rlds in WarningDetailsList)
{
if (_rlds != null)
{
dt.Rows.Add(
DateTime.Parse(_rlds._warningdatetime),
_rlds._warningType,
_rlds._warningStatus
);
}
else
{
continue;
}
}
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("向[{0}]表中添加数据完成", ReadType));
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("添加到数据表异常：" + ex.Message);
return(dt);
}
}
/// <summary>
/// 保存为excel表格，并对表格进行同步
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool DataGridtoExcelModify(System.Data.DataTable dt)
{
#region
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl 表格文件 (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = false;
saveFileDialog.Title = "导出Excel文件";
//设置默认文件名称
saveFileDialog.FileName = "报表-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xls";
if (saveFileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return(false);
}
if (saveFileDialog.FileName == "")
{
return(false);
}
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
object str = string.Empty;
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i > 0)
{
str += "\t";
}
str += dt.Columns[i].ColumnName;
}
sw.WriteLine(str);
for (int j = 0; j < dt.Rows.Count; j++)
{
object tempStr = string.Empty;
for (int k = 0; k < dt.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dt.Rows[j][k].ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
return(true);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
RecordLog.GetInstance().WriteLog(Level.Error, "DataGridtoExcel:" + ex.Message);
return(true);
}
finally
{
sw.Close();
myStream.Close();
}
#endregion
}
/// <summary>
/// 统计分析
/// </summary>
/// <param name="msg"></param>
/// <param name="dt"></param>
/// <param name="lockname"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <param name="username"></param>
public RecordGolable StatisticalAnalysis(out string msg, DataTable dt, string lockname, string starttime, string endtime, string username)
{
RecordGolable recordGolable = new RecordGolable();
msg = string.Empty;
string _and = " and ";
int _yeat = DateTime.Parse(endtime).Year;
int _startMonth = DateTime.Parse(starttime).Month;
int _endMonth = DateTime.Parse(endtime).Month;
try
{
//时间节点
for (int m = 1; m <= (_endMonth - _startMonth) + 1; m++)
{
Monthlydisplay monthlydisplay = new Monthlydisplay();
StatisticalAnalysis statisticalAnalysis = new StatisticalAnalysis();
//门锁名称
string sqlstr = string.Empty;
if (string.IsNullOrEmpty(lockname))
{
MessageBox.Show("未选择门锁", "提示");
msg = "未选择门锁";
return(null);
}
if (string.IsNullOrEmpty(username))
{
MessageBox.Show("用户不能为空", "提示");
msg = "用户不能为空";
return(null);
}
if (string.IsNullOrEmpty(starttime) || string.IsNullOrEmpty(endtime))
{
MessageBox.Show("时间段选择不正确", "提示");
msg = "时间段选择不正确";
return(null);
}
if (!string.IsNullOrEmpty(lockname))
{
sqlstr += "lockmodifyname like '%" + lockname + "%' ";
}
sqlstr += _and;
if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
{
sqlstr += "openlocktime > '" + DateTime.Parse(_yeat + "-" + (_startMonth + m - 1).ToString()) + "' " + _and +
" openlocktime < '" + DateTime.Parse(_yeat + "-" + (_startMonth + m).ToString()) + "' ";
if (!string.IsNullOrEmpty(username))
{
sqlstr += _and;
}
}
if (!string.IsNullOrEmpty(username))
{
sqlstr += "username = '******' ";
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("执行sql简化语句：{0}", sqlstr));
DataRow[] drs = dt.Select(sqlstr);
if (drs.Count() != 0)
{
//此人此月开门总次数
int totaltimes = drs.Count();
//此人此月开门异常总次数
int exception = 0;
string userjob = string.Empty;
if (drs[0][6].ToString() == username)
{
userjob = drs[0][7].ToString();
}
foreach (DataRow dw in drs)
{
if ("异常" == dw[13].ToString())
{
exception++;
}
}
statisticalAnalysis = new StatisticalAnalysis
{
UserName = username,
UserJob = userjob,
ExcetptionTotalTimes = exception,
SuccessTotalTimes = totaltimes - exception,
TotalTimes = totaltimes
};
}
monthlydisplay.dicmonth.Add("Month", _startMonth + m - 1);
monthlydisplay.dicstatus.Add("Data", statisticalAnalysis);
recordGolable._monthlydisplaysList.Add(monthlydisplay);
}
msg = "Success";
return(recordGolable);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Error, "Ecxeption：" + ex.Message);
return(null);
}
}
/// <summary>
/// 清除内存变量
/// </summary>
/// <returns></returns>
private string ClearDevice()
{
EventClass.GetInstance().Cleanup();
RecordLog.GetInstance().WriteLog(Level.Info, "初始化参数");
return("初始化参数");
}
/// <summary>
/// 搜索内存数据库
/// </summary>
/// <param name="msg"></param>
/// <param name="dt"></param>
/// <param name="lockname"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <param name="username"></param>
/// <param name="status"></param>
/// <returns></returns>
public List <RecordLogsDataDetails> QueryDataRecordView(
out string msg,
DataTable dt,
string lockname,
string starttime, string endtime,
string userjob, string status,
string opentype)
{
msg = string.Empty;
string _OR = " or ";
string _and = " and ";
List <RecordLogsDataDetails> list = new List <RecordLogsDataDetails>();
try
{
string sqlstr = string.Empty;
if (!string.IsNullOrEmpty(lockname))
{
sqlstr += "lockmodifyname like '%" + lockname + "%' ";
if (!string.IsNullOrEmpty(starttime) | !string.IsNullOrEmpty(endtime) |
!string.IsNullOrEmpty(userjob) | !string.IsNullOrEmpty(status) | !string.IsNullOrEmpty(opentype))
{
sqlstr += _and;
}
}
if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
{
sqlstr += "openlocktime > '" + starttime + "' " + _and + " openlocktime < '" + endtime + "' ";
if (!string.IsNullOrEmpty(userjob) | !string.IsNullOrEmpty(status) | !string.IsNullOrEmpty(opentype))
{
sqlstr += _and;
}
}
if (!string.IsNullOrEmpty(userjob))
{
//sqlstr += "userjob = '" + userjob + "' ";
sqlstr += "username = '******' "; //搜索条件将角色改为人名
if (!string.IsNullOrEmpty(status) | !string.IsNullOrEmpty(opentype))
{
sqlstr += _and;
}
}
if (!string.IsNullOrEmpty(status))
{
sqlstr += "userstatus = '" + status + "' ";
if (!string.IsNullOrEmpty(opentype))
{
sqlstr += _and;
}
}
if (!string.IsNullOrEmpty(opentype))
{
sqlstr += "openlocktype = '" + opentype + "' ";
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("执行sql简化语句：{0}", sqlstr));
DataRow[] drs = dt.Select(sqlstr);
foreach (DataRow dw in drs)
{
RecordLogsDataDetails ob = new RecordLogsDataDetails
{
openlocktime = DateTime.Parse(dw[0].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
lockmacid = dw[1].ToString(),
lockid = dw[2].ToString(),
lockmodifyname = dw[3].ToString(),
openlocktype = dw[4].ToString(),
userid = dw[5].ToString(),
username = dw[6].ToString(),
userjob = dw[7].ToString(),
userlevel = Int32.Parse(dw[8].ToString() == ""?"0": dw[8].ToString()),
opentime = dw[9].ToString(),
openlimitTimes = Int32.Parse(dw[10].ToString() == ""?"0": dw[10].ToString()),
opentotalTime = dw[11].ToString(),
openexception = dw[12].ToString(),
userstatus = dw[13].ToString()
};
list.Add(ob);
}
msg = "Success";
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("筛选数据完成"));
return(list);
}
catch (Exception ex)
{
msg = "执行查询数据表异常：" + ex.Message;
//MessageBox.Show("执行查询数据表异常：" + ex.Message);
return(list);
}
}
public string SearchWarningResult(string starttime, string endtime, string page, string counts)
{
string msg = string.Empty;
List <WarningRecordDetails> QueryDataRecordList = DataAnalysis.GetInstance().QueryWarningDataView(
out msg, EventClass.GetInstance()._warningdataTable, starttime, endtime);
if (msg != "Success")
{
return(msg);
}
List <WarningRecordDetails> tempList = new List <WarningRecordDetails>();
int iPage = Int32.Parse(page);
int iCounts = Int32.Parse(counts);
try
{
int totalcounts = QueryDataRecordList.Count;
int totalpages = 0;
if (totalcounts % Int32.Parse(counts) > 0)
{
totalpages = totalcounts / Int32.Parse(counts) + 1;
}
else
{
totalpages = totalcounts / Int32.Parse(counts);
}
if (totalcounts == 0)
{
//MessageBox.Show("未搜索到数据", "系统提示");
return("[]");
}
if (Int32.Parse(page) > totalpages)
{
//MessageBox.Show("当前输入页数大于总页数", "系统提示");
return("[]");
}
for (int i = 0; i < iCounts; i++)
{
if (((iPage - 1) * iCounts + i) == totalcounts)
{
break;
}
if (QueryDataRecordList[(iPage - 1) * iCounts + i]._warningdatetime != "")
{
tempList.Add(QueryDataRecordList[(iPage - 1) * iCounts + i]);
}
else
{
break;
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("搜索报警分页返回数据完成,page:[{0}],count:[{1}]", iPage, (iPage - 1) * iCounts + i + 1));
}
object obj = new
{
totalcounts = totalcounts,
pagedata = tempList
};
string json = JsonConvert.SerializeObject(obj);
//string json = JsonConvert.SerializeObject(goableData._recordlogsdatadetailsList);
return(json);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Info, "搜索报警分页时异常：" + ex.Message);
return("");
}
}
/// <summary>
/// 报警对象数据
/// </summary>
/// <param name="page"></param>
/// <param name="counts"></param>
/// <returns></returns>
public string GetWarningObject(string page, string counts)
{
RecordGolable goableData = new RecordGolable();
List <WarningRecordDetails> tempList = new List <WarningRecordDetails>();
goableData = RedvelopRecord.GetInstance().ReadDataToMemory("warning");
int iPage = Int32.Parse(page);
int iCounts = Int32.Parse(counts);
try
{
int totalcounts = goableData._warningRecordList.Count;
int totalpages = 0;
if (totalcounts % Int32.Parse(counts) > 0)
{
totalpages = totalcounts / Int32.Parse(counts) + 1;
}
else
{
totalpages = totalcounts / Int32.Parse(counts);
}
if (Int32.Parse(page) > totalpages)
{
//MessageBox.Show("当前输入页数大于总页数", "系统提示");
return("[]");
}
for (int i = 0; i < iCounts; i++)
{
if (((iPage - 1) * iCounts + i) == totalcounts)
{
break;
}
if (goableData._warningRecordList[(iPage - 1) * iCounts + i]._warningdatetime != "")
{
tempList.Add(goableData._warningRecordList[(iPage - 1) * iCounts + i]);
}
else
{
break;
}
RecordLog.GetInstance().WriteLog(Level.Info, string.Format("报警分页返回数据完成,page:[{0}],count:[{1}]", iPage, (iPage - 1) * iCounts + i + 1));
}
object obj = new
{
totalcounts = totalcounts,
pagedata = tempList
};
string json = JsonConvert.SerializeObject(obj);
//string json = JsonConvert.SerializeObject(goableData._recordlogsdatadetailsList);
return(json);
}
catch (Exception ex)
{
RecordLog.GetInstance().WriteLog(Level.Info, "分页时异常：" + ex.Message);
return("");
}
}
/// <summary>
/// 设置系统时间
/// </summary>
public static void SetCurrentTime()
{
byte[] package = BleProtocol.GetUpdateTimePackage();
EventClass.GetInstance().WriteCommand(package);
RecordLog.GetInstance().WriteLog(Level.Info, "设置时间行为结束");
}