294 lines
11 KiB
C#
294 lines
11 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Web;
|
|||
|
|
using System.Xml;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using TestWebSocket;
|
|||
|
|
using MySql.Data.MySqlClient;
|
|||
|
|
using System.Text;
|
|||
|
|
//using Newtonsoft.Json;
|
|||
|
|
//using Newtonsoft.Json.Linq;
|
|||
|
|
|
|||
|
|
namespace WebSocketToolsConsole
|
|||
|
|
{
|
|||
|
|
public static class BLL
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取所有人脸机
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
//public static DataSet GetDevices()
|
|||
|
|
//{
|
|||
|
|
// return MysqlHelpers.ExecuteDataset(MysqlHelpers.connectionString, CommandType.Text, "select a.*from tb_Devices a");
|
|||
|
|
//}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 保存人脸机信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="serialNo"></param>
|
|||
|
|
/// <param name="devName"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int SaveDevice(string serialNo, string factory,string ip,string location)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Format("select Facelid from DeviceManage where SerialNo='{0}'", serialNo);
|
|||
|
|
object id = MysqlHelpers.ExecuteScalar(sql);
|
|||
|
|
if (id != null && Convert.ToInt64(id) > 0)
|
|||
|
|
{
|
|||
|
|
sql = string.Format("update DeviceManage set SerialNo='{0}',Factory='{1}',Status={2},maintainStatus={3} where Facelid={4}", serialNo, factory, 1, 0, id);
|
|||
|
|
//LogHelper.WriteLine(sql);
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
int status = 0;
|
|||
|
|
sql = "insert into DeviceManage (SerialNo,CreatedDate,Factory,Status,bindingStatus,faceIp,faceAddress,maintainStatus) values (@SerialNo,@CreatedDate,@Factory,@Status,@bindingStatus,@faceIp,@faceAddress,@maintainStatus)";
|
|||
|
|
MySqlParameter[] sqlParams = new MySqlParameter[8] {
|
|||
|
|
new MySqlParameter("@SerialNo", serialNo),
|
|||
|
|
new MySqlParameter("@CreatedDate", DateTime.Now),
|
|||
|
|
new MySqlParameter("@Factory", factory),
|
|||
|
|
new MySqlParameter("@Status",1),
|
|||
|
|
new MySqlParameter("@bindingStatus",status),
|
|||
|
|
new MySqlParameter("@faceIp",ip),
|
|||
|
|
new MySqlParameter("@faceAddress",location),
|
|||
|
|
new MySqlParameter("@maintainStatus",0)};
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(sql,sqlParams);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine("SaveDevice()函数异常：" + ex.Message);
|
|||
|
|
|
|||
|
|
throw;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 更新设备状态
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="serialNo"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int Online(string serialNo)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Format("update DeviceManage set Status=0 where SerialNo='{0}'", serialNo);
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine(ex.Message);
|
|||
|
|
throw;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
public static int consoleface(FaceIssue fi)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = "insert into FaceIssue (faceSn,creationtime,pmsid,picture,issuestate) values (@faceSn,@creationtime,@pmsid,@picture,@issuestate)";
|
|||
|
|
MySqlParameter[] sqlParams = new MySqlParameter[5] {
|
|||
|
|
new MySqlParameter("@faceSn", fi.faceSn),
|
|||
|
|
new MySqlParameter("@creationtime",fi.creationtime),
|
|||
|
|
new MySqlParameter("@pmsid", fi.pmsid),
|
|||
|
|
new MySqlParameter("@picture",fi.picture),
|
|||
|
|
new MySqlParameter("@issuestate",fi.issuestate)};
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(sql,sqlParams);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine(ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 添加退房时间
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="date"></param>
|
|||
|
|
/// <param name="hotel"></param>
|
|||
|
|
/// <param name="room"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int reviseDate(string hotel, string room)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
sql += string.Format("UPDATE CheckInInfo SET checkOutTime = SYSDATE() WHERE HotelCode={0} and Roomid={1} and checkOutTime='2000-01-01'", hotel, room);
|
|||
|
|
int sd = MysqlHelpers.ExecuteNonQuery(sql);
|
|||
|
|
return sd;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine("添加退房时间报错"+sql+""+ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 添加退房时间
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="date"></param>
|
|||
|
|
/// <param name="hotel"></param>
|
|||
|
|
/// <param name="room"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int getmessage(int pmsid)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
sql += $"select * from pmsInterface where pmsId={pmsid}";
|
|||
|
|
LogHelper.WriteLine(sql);
|
|||
|
|
DataTable ds = MysqlHelpers.ExecuteDataTable(sql);
|
|||
|
|
string hote = ds.Rows[0]["hotelid"].ToString();
|
|||
|
|
string room = ds.Rows[0]["room"].ToString();
|
|||
|
|
LogHelper.WriteLine("记录酒店id和房间id"+ hote+"::::"+ room);
|
|||
|
|
return reviseDate(hote, room);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine("退房时间报错"+ sql +"信息"+ ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 添加pms日志
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="log"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int addPmsLog(pmsLog log)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = "insert into pmsLog (pmsid,step,app,Creationtime,message,Data) values (@pmsid,@step,@app,@Creationtime,@message,@Data)";
|
|||
|
|
MySqlParameter[] sqlParams = new MySqlParameter[6] {
|
|||
|
|
new MySqlParameter("@pmsid", log.pmsid),
|
|||
|
|
new MySqlParameter("@step",log.step),
|
|||
|
|
new MySqlParameter("@app", log.app),
|
|||
|
|
new MySqlParameter("@Creationtime",DateTime.Now),
|
|||
|
|
new MySqlParameter("@message",log.message),
|
|||
|
|
new MySqlParameter("@Data",log.Data)};
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine(ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 添加ResendLog日志
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="log"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public static int addResendLog(ResendLog log)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = "insert into ResendLog (pmsid,SerialNo,message,State) values (@pmsid,@SerialNo,@message,@State)";
|
|||
|
|
MySqlParameter[] sqlParams = new MySqlParameter[4] {
|
|||
|
|
new MySqlParameter("@pmsid", log.pmsid),
|
|||
|
|
new MySqlParameter("@SerialNo",log.SerialNo),
|
|||
|
|
new MySqlParameter("@message", log.message),
|
|||
|
|
new MySqlParameter("@State",log.State)};
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine(ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// 查询未下发人脸机的pms数据
|
|||
|
|
/// </summary>
|
|||
|
|
public static List<ResendLog> GetResendLog()
|
|||
|
|
{
|
|||
|
|
List<ResendLog> Resend = new List<ResendLog>();
|
|||
|
|
string sql = string.Format("select* from ResendLog where State=1");
|
|||
|
|
MySqlDataReader dr = MysqlHelpers.ExecuteReader(sql);
|
|||
|
|
while (dr.Read())
|
|||
|
|
{
|
|||
|
|
ResendLog log = new ResendLog();
|
|||
|
|
log.message= dr["message"].ToString();
|
|||
|
|
log.ID= int.Parse(dr["ID"].ToString());
|
|||
|
|
log.message= dr["message"].ToString();
|
|||
|
|
log.pmsid= int.Parse(dr["pmsid"].ToString());
|
|||
|
|
log.SerialNo = dr["SerialNo"].ToString();
|
|||
|
|
log.State = int.Parse(dr["State"].ToString());
|
|||
|
|
Resend.Add(log);
|
|||
|
|
}
|
|||
|
|
return Resend;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 添加下发数据状态
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="pmsid"></param>
|
|||
|
|
/// <param name="state"></param>
|
|||
|
|
public static int amendResendLog(int pmsid,int state)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Format("UPDATE ResendLog SET State ={0} WHERE pmsid = {1}", state, pmsid);
|
|||
|
|
LogHelper.WriteLine(sql);
|
|||
|
|
int sd = MysqlHelpers.ExecuteNonQuery(sql);
|
|||
|
|
return sd;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine(ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int InsertFacedevicerxtxinfoLogBatch(List<facedevicerxtxinfo> logs)
|
|||
|
|
{
|
|||
|
|
List<String> allstatements = new List<String>();
|
|||
|
|
|
|||
|
|
string strInsertSql = "insert into facedevicerxtxinfo (pmsid,sn,msgid,cmd,data,datatime,direction,trresult,ipaddr,iplocation) values ";
|
|||
|
|
string strValSql = "";
|
|||
|
|
|
|||
|
|
StringBuilder finalSqlBuiler = new StringBuilder();
|
|||
|
|
foreach (var it in logs)
|
|||
|
|
{
|
|||
|
|
finalSqlBuiler.Append("(");
|
|||
|
|
finalSqlBuiler.Append(it.pmsid + ", ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.sn + "', ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.msgid + "', ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.cmd + "', ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.data + "', ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.datatime + "', ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.direction + "', ");
|
|||
|
|
finalSqlBuiler.Append(it.trresult + ", ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.ipaddr + "', ");
|
|||
|
|
finalSqlBuiler.Append("'" + it.iplocation + "'");
|
|||
|
|
finalSqlBuiler.Append("), ");
|
|||
|
|
}
|
|||
|
|
strValSql = finalSqlBuiler.ToString();
|
|||
|
|
strValSql = strValSql.Substring(0, strValSql.Length - 2);
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
return MysqlHelpers.ExecuteNonQuery(strInsertSql + strValSql);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
LogHelper.WriteLine("批量添加数据到facedevicerxtxinfo异常：" + ex.Message);
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|