655 lines
26 KiB
C#
655 lines
26 KiB
C#
using Face.Domain.Application;
|
|
using Face.Domain.Entities;
|
|
using Face.Services.Cache;
|
|
using Face.Services.Enums;
|
|
using Face.Services.Manager;
|
|
using MySql.Data.MySqlClient;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using WebSocketToolsConsole;
|
|
|
|
namespace Face.Services.DBUtility.Common
|
|
{
|
|
public static class SqlOperationsData
|
|
{
|
|
static SqlDependency dependency;
|
|
|
|
/// <summary>
|
|
/// 更新人脸机状态
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static DataSet facestate()
|
|
{
|
|
string sql = "select Facestate,snnumber from DeviceManage";
|
|
SqlDataAdapter sqldata = new SqlDataAdapter(sql, MysqlHelpers.connectionString);
|
|
DataSet data = new DataSet();
|
|
sqldata.Fill(data);
|
|
return data;
|
|
}
|
|
/// <summary>
|
|
/// 数据库更新发生变化
|
|
/// </summary>
|
|
public static void Monitor()
|
|
{
|
|
//Start和Stop方法
|
|
SqlDependency.Start(MysqlHelpers.connectionString);
|
|
Update(MysqlHelpers.connectionString);
|
|
}
|
|
private static void Update(string conn)
|
|
{
|
|
using (
|
|
SqlConnection connection =
|
|
new SqlConnection(conn))
|
|
{
|
|
//此处 要注意 不能使用* 表名要加[dbo] 否则会出现一直调用执行 OnChange
|
|
string sql = "select Status from [dbo].[DeviceManage]";
|
|
|
|
using (SqlCommand command = new SqlCommand(sql, connection))
|
|
{
|
|
connection.Open();
|
|
command.CommandType = CommandType.Text;
|
|
dependency = new SqlDependency(command);
|
|
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
|
|
//必须要执行一下command
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
//update insert delete都会进入
|
|
private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
|
|
{
|
|
CacheHelp.ClearFaceList();
|
|
//using (var db=new )
|
|
//{
|
|
|
|
//}
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 添加顾客信息
|
|
/// </summary>
|
|
public static int addClient(Lodger ci)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("select LodgerId from Lodger where IDNumber='" + ci.IDNumber + "'");
|
|
object id = MysqlHelpers.ExecuteScalar(sql);
|
|
if (id != null && Convert.ToInt64(id) > 0)
|
|
{
|
|
sql = string.Format("update Lodger set phonenumber='{0}' where LodgerId='{1}'", ci.phonenumber, id);
|
|
int ss = MysqlHelpers.ExecuteNonQuery(sql);
|
|
return ss;
|
|
}
|
|
else
|
|
{
|
|
sql = "insert into Lodger(LodgerNmae, IDNumber, Sex, CheckInDate, Sourcedian,phonenumber,remark)VALUES(@LodgerNmae,@IDNumber,@Sex,@CheckInDate,@Sourcedian,@phonenumber,@remark)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[7] {
|
|
new MySqlParameter("@LodgerNmae",ci.LodgerNmae),
|
|
new MySqlParameter("@IDNumber", ci.IDNumber),
|
|
new MySqlParameter("@Sex",ci.Sex),
|
|
new MySqlParameter("@CheckInDate",DateTime.Now),
|
|
new MySqlParameter("@Sourcedian",ci.Sourcedian),
|
|
new MySqlParameter("@remark", ci.remark),
|
|
new MySqlParameter("@phonenumber", ci.phonenumber)};
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
throw;
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 添加入住信息
|
|
/// </summary>
|
|
public static int addcheckininfo(CheckInInfo co)
|
|
{
|
|
string sql = "insert into CheckInInfo(Name, IdNumber, CreationTime, InfoSource, HotelCode, Roomid,CheckTime,checkOutTime,picture,identitys)VALUES(@Name,@IdNumber,@CreationTime,@InfoSource,@HotelCode,@Roomid,@CheckTime,@checkOutTime,@picture,@identitys)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[10] {
|
|
new MySqlParameter("@Name",co.Name),
|
|
new MySqlParameter("@IdNumber", co.IdNumber),
|
|
new MySqlParameter("@CreationTime",co.CreationTime),
|
|
new MySqlParameter("@InfoSource",co.InfoSource),
|
|
new MySqlParameter("@HotelCode",co.HotelCode),
|
|
new MySqlParameter("@Roomid", co.Roomid),
|
|
new MySqlParameter("@CheckTime", co.CheckTime),
|
|
new MySqlParameter("@checkOutTime", co.checkOutTime),
|
|
new MySqlParameter("@picture", co.picture),
|
|
new MySqlParameter("@identitys", co.identitys) };
|
|
return MysqlHelpers.ExecuteNonQuery(sql,sqlParams);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 接口，查看是否有此人脸机
|
|
/// </summary>
|
|
/// <param name="hotelname"></param>
|
|
/// <param name="roomname"></param>
|
|
/// <returns></returns>
|
|
public static string selectFace(string hotelname, string roomname)
|
|
{
|
|
string consequence = "";
|
|
try
|
|
{
|
|
string sql = "select SerialNo from DeviceManage where HotelCode='" + hotelname + "' and RoomId='" + roomname + "'";
|
|
object id = MysqlHelpers.ExecuteScalar(sql);
|
|
if (id != null)
|
|
{
|
|
consequence = id.ToString();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
}
|
|
return consequence;
|
|
}
|
|
/// <summary>
|
|
/// 为房间绑定人脸机
|
|
/// </summary>
|
|
public static int reviseRommFace(string hotel, int romm, string faceNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("UPDATE DeviceManage SET HotelCode='{0}',RoomId='{1}',bindingStatus=1,bindingDate=CURRENT_DATE() where SerialNo='{2}' ", hotel, romm, faceNo);
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 为酒店绑定人脸机
|
|
/// </summary>
|
|
/// <param name="hotel"></param>
|
|
/// <param name="romm"></param>
|
|
/// <param name="faceNo"></param>
|
|
/// <returns></returns>
|
|
public static int reviseHotelFace(string hotel, string faceNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("UPDATE DeviceManage SET HotelCode='{0}',bindingStatus=1,bindingDate=CURRENT_DATE() where SerialNo='{1}' ", hotel, faceNo);
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 记录绑定/解绑操作
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static int bindingState(FaceBinding fb)
|
|
{
|
|
try
|
|
{
|
|
string sql = "insert into FaceBinding(SerialNo, Operator, OperatorType, bindingDate, HotelCode,RoomId)VALUES(@SerialNo,@Operator,@OperatorType,@bindingDate,@HotelCode,@RoomId)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[6] {
|
|
new MySqlParameter("@SerialNo", fb.SerialNo),
|
|
new MySqlParameter("@Operator",fb.Operator),
|
|
new MySqlParameter("@OperatorType",fb.OperatorType),
|
|
new MySqlParameter("@bindingDate",fb.bindingDate),
|
|
new MySqlParameter("@HotelCode", fb.HotelCode),
|
|
new MySqlParameter("@RoomId", fb.RoomId)};
|
|
return MysqlHelpers.ExecuteNonQuery(sql,sqlParams);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 登录
|
|
/// </summary>
|
|
/// <param name="accountentity"></param>
|
|
//public static Accountentity LoginAccount(string uid)
|
|
//{
|
|
// List<Accountentity> listay = new List<Accountentity>();
|
|
// string sql = "select * from AuthorityDB.dbo.UserInfo where Uid='" + uid + "'";
|
|
// SqlDataReader dr = DbHelperMySQL.ExecuteReader(sql);
|
|
// Accountentity ay = new Accountentity();
|
|
// while (dr.Read())
|
|
// {
|
|
// ay.Uid = dr["Uid"].ToString();
|
|
// ay.CreateTime = dr["CreateTime"].ToDateTime();
|
|
// }
|
|
// return ay;
|
|
//}
|
|
|
|
/// <summary>
|
|
/// 查询酒店权限
|
|
/// </summary>
|
|
/// <param name="name"></param>
|
|
/// <returns></returns>
|
|
public static List<string> permission(string name)
|
|
{
|
|
List<string> list = new List<string>();
|
|
string sql = "select distinct HotelId from AuthorityDB.dbo.UserAuthoes where UserId in(select Id from AuthorityDB.dbo.UserInfo where Uid='" + name + "')";
|
|
DbHelperMySQL.ExecuteReader(sql, dr => {
|
|
while (dr.Read())
|
|
{
|
|
list.Add(dr["HotelId"].ToString());
|
|
}
|
|
});
|
|
return list;
|
|
}
|
|
/// <summary>
|
|
/// 登录验证
|
|
/// </summary>
|
|
/// <param name="accountentity"></param>
|
|
//public static Accountentity LoginAccount(string uid, string pwd)
|
|
//{
|
|
// List<Accountentity> listay = new List<Accountentity>();
|
|
// string sql = "select * from AuthorityDB.dbo.UserInfo where Uid='" + uid + "'and Pwd='" + pwd + "'";
|
|
// SqlDataReader dr = DbHelperMySQL.ExecuteReader(sql);
|
|
// Accountentity ay = new Accountentity();
|
|
// while (dr.Read())
|
|
// {
|
|
// ay.Uid = dr["Uid"].ToString();
|
|
// ay.CreateTime = dr["CreateTime"].ToDateTime();
|
|
// }
|
|
// return ay;
|
|
//}
|
|
|
|
/// <summary>
|
|
/// 解绑房间
|
|
/// </summary>
|
|
public static int unbundleoperateRoom(string sn)
|
|
{
|
|
try
|
|
{
|
|
string sql = "UPDATE DeviceManage SET RoomId=null,bindingStatus=0 WHERE SerialNo='" + sn + "'";
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 解绑酒店
|
|
/// </summary>
|
|
public static int unbundleoperatehotel(string sn)
|
|
{
|
|
try
|
|
{
|
|
string sql = "UPDATE DeviceManage SET RoomId=null,bindingStatus=0,HotelCode=null WHERE SerialNo='" + sn + "'";
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
}
|
|
}
|
|
///// <summary>
|
|
///// 通过酒店id查询房间列表
|
|
///// </summary>
|
|
//public static Hosts Roominfo(string hotelname)
|
|
//{
|
|
// string sql = "select * from Hosts where HotelID=" + hotelname + "";
|
|
// SqlDataReader dr = DbHelperMySQL.ExecuteReader(sql);
|
|
// Hosts ay = new Hosts();
|
|
// while (dr.Read())
|
|
// {
|
|
// ay.HotelID = int.Parse(dr["HotelID"].ToString());
|
|
// ay.RoomNumber = dr["RoomNumber"].ToString();
|
|
// ay.Status = int.Parse(dr["Status"].ToString());
|
|
// }
|
|
// return ay;
|
|
//}
|
|
/// <summary>
|
|
/// 添加入住信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static int additionCheck(CheckInInfo cf)
|
|
{
|
|
try
|
|
{
|
|
string date = cf.CreationTime.Value.ToString("yyyy-MM-dd");
|
|
string sql = string.Format("select * from CheckInInfo where HotelCode=" + cf.HotelCode + " and IdNumber='" + cf.IdNumber + "'and Roomid="+cf.Roomid+"");
|
|
object id = MysqlHelpers.ExecuteScalar(sql);
|
|
if (id != null && Convert.ToInt64(id) > 0)
|
|
{
|
|
string dateout = cf.checkOutTime.Value.ToString("yyyy-MM-dd");
|
|
sql = string.Format("UPDATE CheckInInfo SET HotelCode = '{0}', RoomId = '{1}',checkOutTime='{2}',picture='{3}' where id={4}", cf.HotelCode, cf.Roomid,dateout,cf.picture, id);
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|
}
|
|
else
|
|
{
|
|
string sql2 = "insert into CheckInInfo (Name,CreationTime,IdNumber,InfoSource,HotelCode,Roomid,CheckTime,checkOutTime,picture) values(@Name,@CreationTime,@IdNumber,@InfoSource,@HotelCode,@Roomid,@CheckTime,@checkOutTime,@picture)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[9] {
|
|
new MySqlParameter("@Name",cf.Name),
|
|
new MySqlParameter("@CreationTime",cf.CreationTime),
|
|
new MySqlParameter("@IdNumber",cf.IdNumber),
|
|
new MySqlParameter("@InfoSource",cf.InfoSource),
|
|
new MySqlParameter("@HotelCode",cf.HotelCode),
|
|
new MySqlParameter("@Roomid",cf.Roomid),
|
|
new MySqlParameter("@CheckTime",cf.CheckTime),
|
|
new MySqlParameter("@checkOutTime",cf.checkOutTime.Value.ToString("yyyy/MM/dd")),
|
|
new MySqlParameter("@picture",cf.picture) };
|
|
return MysqlHelpers.ExecuteNonQuery(sql2, sqlParams);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 绑定/解绑操作记录
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static int bindingoperation(FaceBinding fb)
|
|
{
|
|
string sql = "insert into FaceBinding (SerialNo,Operator,OperatorType,bindingDate,HotelCode,RoomId) values(@SerialNo,@Operator,@OperatorType,@bindingDate,@HotelCode,@RoomId)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[6] {
|
|
new MySqlParameter("@SerialNo",fb.SerialNo),
|
|
new MySqlParameter("@Operator",fb.Operator),
|
|
new MySqlParameter("@OperatorType",fb.OperatorType),
|
|
new MySqlParameter("@bindingDate",fb.bindingDate),
|
|
new MySqlParameter("@HotelCode",fb.HotelCode),
|
|
new MySqlParameter("@RoomId",fb.RoomId) };
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|
}
|
|
/// <summary>
|
|
///// 获取人脸机地理位置
|
|
///// </summary>
|
|
///// <returns></returns>
|
|
public static List<string> facelocation()
|
|
{
|
|
string sql = "SELECT faceAddress from DeviceManage WHERE HotelCode='' GROUP BY faceAddress ";
|
|
List<string> location = new List<string>();
|
|
|
|
DbHelperMySQL.ExecuteReader(sql, dr => {
|
|
|
|
while (dr.Read())
|
|
{
|
|
location.Add(dr["faceAddress"].ToString());
|
|
}
|
|
});
|
|
return location;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 保存pms数据
|
|
/// </summary>
|
|
public static int preserve(string info, string hotelid, string room,string facesn,int issueresult,int pmstype)
|
|
{
|
|
string sql = "insert into pmsInterface values(CURRENT_DATE(),@pmsContent,@hotelid,@room,@faceSN,@issueresult,@pmstype)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[6] {
|
|
new MySqlParameter("@pmsContent",info),
|
|
new MySqlParameter("@hotelid",hotelid),
|
|
new MySqlParameter("@room",room),
|
|
new MySqlParameter("@faceSN",facesn),
|
|
new MySqlParameter("@issueresult",issueresult),
|
|
new MySqlParameter("@pmstype",pmstype)};
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|
}
|
|
/// <summary>
|
|
/// 操作日志
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static int writeLog(ULog ul)
|
|
{
|
|
string sql = "insert into ulog values(@Uname,@Creationtime,@type,@faceSN,@hotelCode,@roomid)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[6] {
|
|
new MySqlParameter("@Uname",ul.Uname),
|
|
new MySqlParameter("@Creationtime",DateTime.Now),
|
|
new MySqlParameter("@type",ul.operatetype),
|
|
new MySqlParameter("@faceSN",ul.faceSN),
|
|
new MySqlParameter("@hotelCode",ul.hotelcode),
|
|
new MySqlParameter("@roomid",ul.roomid)};
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 日常日志
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static int writeDailyLog(Dailyoperation dp)
|
|
{
|
|
string sql = "insert into Dailyoperation values(@Uname,CURRENT_DATE(),@type,@operatedata,@hotelCode)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[4] {
|
|
new MySqlParameter("@Uname",dp.Uname),
|
|
new MySqlParameter("@type",dp.operatetype),
|
|
new MySqlParameter("@operatedata",dp.operatedata),
|
|
new MySqlParameter("@hotelCode",dp.hotelCode)};
|
|
return MysqlHelpers.ExecuteNonQuery(sql, sqlParams);
|
|
}
|
|
/// <summary>
|
|
/// 根据酒店code获取酒店id
|
|
/// </summary>
|
|
/// <param name="hotelid"></param>
|
|
/// <returns></returns>
|
|
public static string geihotelid(string hotelid)
|
|
{
|
|
string sql = string.Format("select Id from [AuthorityDB].[dbo].Hotels where Code={0}", hotelid);
|
|
object roomid = MysqlHelpers.ExecuteScalar(sql);
|
|
if (roomid == null)
|
|
{
|
|
roomid = "";
|
|
}
|
|
return roomid.ToString();
|
|
|
|
|
|
}
|
|
/// <summary>
|
|
/// 根据房间号获取房间id
|
|
/// </summary>
|
|
/// <param name="hotelid"></param>
|
|
/// <param name="roomname"></param>
|
|
/// <returns></returns>
|
|
public static string geiroomid(string hotelid, string roomname)
|
|
{
|
|
string sql = string.Format("select Id ,RoomStatusID,HotelID,[RoomNumber],[Status] = convert(int,[Status]),[Desc] =[remark],[CreateTime]=[registerdate] from BLW.CRICS.[dbo].tb_Hosts where HotelID={0} and RoomNumber='{1}'", hotelid, roomname);
|
|
object roomid = MysqlHelpers.ExecuteScalar(sql);
|
|
if (roomid == null)
|
|
{
|
|
roomid = "";
|
|
}
|
|
return roomid.ToString();
|
|
}
|
|
/// <summary>
|
|
/// 通过酒店id和房间id获取入住人的信息
|
|
/// </summary>
|
|
public static List<string> getCheck(string code,string room)
|
|
{
|
|
List<string> idnumber = new List<string>();
|
|
string sql = string.Format("select IdNumber from CheckInInfo where checkOutTime = '2000-01-01' and HotelCode={0} and Roomid={1}", code,room);
|
|
DbHelperMySQL.ExecuteReader(sql,dr=> {
|
|
|
|
while (dr.Read())
|
|
{
|
|
idnumber.Add(dr["IdNumber"].ToString());
|
|
}
|
|
});
|
|
|
|
return idnumber;
|
|
}
|
|
/// <summary>
|
|
/// 添加退房时间
|
|
/// </summary>
|
|
/// <param name="date"></param>
|
|
/// <param name="hotel"></param>
|
|
/// <param name="room"></param>
|
|
/// <returns></returns>
|
|
public static int reviseDate(string date,string hotel,string room)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("UPDATE CheckInInfo SET checkOutTime ='{0}' WHERE HotelCode={1} and Roomid={2} and checkOutTime='2000-01-01'", date, hotel, room);
|
|
int sd= MysqlHelpers.ExecuteNonQuery(sql);
|
|
return sd;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
throw;
|
|
}
|
|
|
|
}
|
|
/// <summary>
|
|
/// 冻结测试人员
|
|
/// </summary>
|
|
/// <param name="tiaojian"></param>
|
|
/// <returns></returns>
|
|
public static int amendtestUser(string tiaojian)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("UPDATE TestUser SET state =0 WHERE id={0}",tiaojian);
|
|
int sd = MysqlHelpers.ExecuteNonQuery(sql);
|
|
return sd;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 解冻测试人员
|
|
/// </summary>
|
|
/// <param name="tiaojian"></param>
|
|
/// <returns></returns>
|
|
public static int amendtestUser1(string tiaojian)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("UPDATE TestUser SET state =1 WHERE id={0}", tiaojian);
|
|
int sd = MysqlHelpers.ExecuteNonQuery(sql);
|
|
return sd;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 人脸机维修状态
|
|
/// </summary>
|
|
public static int maintainStaet(transferFace tf)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("select id from transferFace where faceSN='"+tf.faceSN+"'");
|
|
object id = MysqlHelpers.ExecuteScalar(sql);
|
|
if (id != null && Convert.ToInt64(id) > 0)
|
|
{
|
|
sql = string.Format("UPDATE transferFace SET infoid = '{0}', faultState = '{1}',creationTime='{2}' where faceSN={3}", tf.infoid, tf.faultState, tf.creationTime, id);
|
|
return MysqlHelpers.ExecuteNonQuery(sql);
|
|
}
|
|
else
|
|
{
|
|
string sql2 = "insert into transferFace (faceSN,infoid,faultState,creationTime) values(@faceSN,@infoid,@faultState,@creationTime)";
|
|
MySqlParameter[] sqlParams = new MySqlParameter[4] {
|
|
new MySqlParameter("@faceSN",tf.faceSN),
|
|
new MySqlParameter("@infoid",tf.infoid),
|
|
new MySqlParameter("@faultState",tf.faultState),
|
|
new MySqlParameter("@creationTime",tf.creationTime) };
|
|
return MysqlHelpers.ExecuteNonQuery(sql2, sqlParams);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 添加pms日志
|
|
/// </summary>
|
|
/// <param name="log"></param>
|
|
/// <returns></returns>
|
|
public static int revise(pmsLog log)
|
|
{
|
|
try
|
|
{
|
|
string sql2 = "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",log.Creationtime),
|
|
new MySqlParameter("@message",log.message),
|
|
new MySqlParameter("@Data",log.Data) };
|
|
return MysqlHelpers.ExecuteNonQuery(sql2, sqlParams);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 添加pms酒店房间信息
|
|
/// </summary>
|
|
/// <param name="pms"></param>
|
|
/// <returns></returns>
|
|
public static int revisepms(pmsInterface pms)
|
|
{
|
|
try
|
|
{
|
|
string sql = string.Format("UPDATE pmsInterface SET faceSN ='{0}',hotelid={1},room={2},messageid='{3}' WHERE pmsId={4}",pms.faceSN,pms.hotelid,pms.room,pms.messageid,pms.pmsId);
|
|
int sd = MysqlHelpers.ExecuteNonQuery(sql);
|
|
return sd;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelp.WriteExceptionLog(ex);
|
|
return 0;
|
|
throw;
|
|
}
|
|
}
|
|
///// <summary>
|
|
///// 获取所有的房间
|
|
///// </summary>
|
|
//public static List<TBL_ROOM_BASIC_INFO> Roomdata()
|
|
//{
|
|
// List<TBL_ROOM_BASIC_INFO> Room = new List<TBL_ROOM_BASIC_INFO>();
|
|
|
|
// string sql = "select * from tbl_room_basic_info";
|
|
// MySqlDataReader dr = MysqlHelper.ExecuteReader(sql);
|
|
// while (dr.Read())
|
|
// {
|
|
// TBL_ROOM_BASIC_INFO log = new TBL_ROOM_BASIC_INFO();
|
|
// log.ROOM_NUMBER = dr["ROOM_NUMBER"].ToString();
|
|
// log.RoomStatusID = int.Parse(dr["RoomStatusID"].ToString());
|
|
// log.HOTEL_OLD_ID = int.Parse(dr["HOTEL_OLD_ID"].ToString());
|
|
// log.ROOM_OLD_ID = int.Parse(dr["ROOM_OLD_ID"].ToString());
|
|
// Room.Add(log);
|
|
// }
|
|
// return Room;
|
|
//}
|
|
}
|
|
}
|