365 lines
13 KiB
C#
365 lines
13 KiB
C#
using System.Data;
|
||
using CliWrap;
|
||
using Microsoft.AspNetCore.Http;
|
||
using Microsoft.AspNetCore.Mvc;
|
||
using Microsoft.AspNetCore.OutputCaching;
|
||
using Microsoft.Data.SqlClient;
|
||
using NLog;
|
||
using RestSharp;
|
||
using static System.Runtime.InteropServices.JavaScript.JSType;
|
||
|
||
namespace UseSQLQueryData.Controllers
|
||
{
|
||
[Route("api/[controller]/[action]")]
|
||
[ApiController]
|
||
public class ValuesController : ControllerBase
|
||
{
|
||
public static string SqlConnectionString = "server=WIN-061EVIHKD86\\BLW;database=CRICS;uid=sa;pwd=pass@123$%^;TrustServerCertificate=True;";
|
||
//public static string SqlConnectionString = "Data Source=DESKTOP-DUNS5K7;Initial Catalog=CRICS;User ID=sa;Password=123456;Trust Server Certificate=True";
|
||
public static Logger logger = LogManager.GetCurrentClassLogger();
|
||
[HttpGet()]
|
||
[OutputCache()]
|
||
public async Task<ReturnInfo> GetRoomCount()
|
||
{
|
||
ReturnInfo returnInfo = new ReturnInfo();
|
||
//return returnInfo;
|
||
try
|
||
{
|
||
using SqlConnection con = new SqlConnection(SqlConnectionString);
|
||
con.Open();
|
||
using SqlCommand sqlCommand = con.CreateCommand();
|
||
|
||
string sql = @"select A.HotelID,B.Code,Count(A.HotelID) as TotalCount from tb_Hosts A
|
||
left join tb_Sys_Hotels B
|
||
on A.HotelID=B.ID
|
||
where A.IsDeleted=0 and B.IsDeleted=0
|
||
group by HotelID,B.Code
|
||
ORDER BY HotelID;";
|
||
sqlCommand.CommandText = sql;
|
||
//SqlParameter ps1 = new SqlParameter("@HotelID", hotelid);
|
||
//sqlCommand.Parameters.Add(ps1);
|
||
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
|
||
DataSet dataSet = new DataSet();
|
||
adapter.Fill(dataSet);
|
||
|
||
DataTable dt = dataSet.Tables[0];
|
||
List<ChaXun> chaList = new List<ChaXun>();
|
||
if (dt != null && dt.Rows.Count > 0)
|
||
{
|
||
foreach (DataRow item in dt.Rows)
|
||
{
|
||
var id = item["HotelID"].ToString();
|
||
var totalcount = item["TotalCount"].ToString();
|
||
var code = item["Code"].ToString();
|
||
ChaXun cha = new ChaXun();
|
||
cha.HotelID = id;
|
||
cha.HotelCode = code;
|
||
cha.TotalCount = totalcount;
|
||
chaList.Add(cha);
|
||
}
|
||
}
|
||
returnInfo.isok = true;
|
||
returnInfo.response = chaList;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
returnInfo.isok = false;
|
||
returnInfo.message = ex.Message;
|
||
}
|
||
return returnInfo;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 生成主机标识号
|
||
/// </summary>
|
||
/// <param name="HotelID"></param>
|
||
/// <returns></returns>
|
||
[HttpPost()]
|
||
public async Task<ReturnInfo> GenericHOSTNUMBER([FromForm] string HotelID, [FromForm] string HotelCode)
|
||
{
|
||
//2147 --099008
|
||
ReturnInfo r = new ReturnInfo();
|
||
try
|
||
{
|
||
logger.Error("HotelID:" + HotelID + " HotelCode:" + HotelCode);
|
||
ushort a = ushort.Parse(HotelCode);
|
||
byte[] bbf = BitConverter.GetBytes(a);
|
||
byte b1 = bbf[0];
|
||
byte b2 = bbf[1];
|
||
string nn1 = b1.ToString("000");
|
||
string nn2 = b2.ToString("000");
|
||
|
||
string nn3 = nn1 + nn2;
|
||
|
||
using SqlConnection con = new SqlConnection("server=WIN-061EVIHKD86\\BLW;database=CRICS;uid=sa;pwd=pass@123$%^;TrustServerCertificate=True;");
|
||
con.Open();
|
||
SqlTransaction tran = con.BeginTransaction();
|
||
using SqlCommand sqlCommand = con.CreateCommand();
|
||
sqlCommand.Transaction = tran;
|
||
|
||
string sql = string.Format("select ID,MAC from tb_Hosts where HotelID={0} and IsDeleted=0 and MAC is not NULL and MAC!='';", HotelID);
|
||
sqlCommand.CommandText = sql;
|
||
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
|
||
DataSet dataSet = new DataSet();
|
||
adapter.Fill(dataSet);
|
||
|
||
DataTable dt = dataSet.Tables[0];
|
||
if (dt != null && dt.Rows.Count > 0)
|
||
{
|
||
foreach (DataRow item in dt.Rows)
|
||
{
|
||
var id = item["ID"].ToString();
|
||
var mymac = item["MAC"].ToString();
|
||
if (!string.IsNullOrEmpty(mymac))
|
||
{
|
||
|
||
string[] NNN = mymac.Split('-');
|
||
string n1 = NNN[4];
|
||
string n2 = NNN[5];
|
||
byte bbn1 = Convert.ToByte(n1, 16);
|
||
byte bbn2 = Convert.ToByte(n2, 16);
|
||
short MAC1 = Convert.ToInt16(bbn1);
|
||
short MAC2 = Convert.ToInt16(bbn2);
|
||
|
||
string HOSTNUMBER = nn3 + MAC1.ToString("000") + MAC2.ToString("000");
|
||
|
||
string sql111 = string.Format(@"update tb_Hosts set HostNumber='{0}' where ID={1} and (HostNumber is NULL or HostNumber='');", HOSTNUMBER, id);
|
||
sqlCommand.CommandText = sql111;
|
||
await sqlCommand.ExecuteNonQueryAsync();
|
||
}
|
||
|
||
}
|
||
}
|
||
tran.Commit();
|
||
r.isok = true;
|
||
r.response = "success";
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
logger.Error("出错了" + ex.Message);
|
||
r.isok = false;
|
||
r.message = ex.Message;
|
||
}
|
||
return r;
|
||
}
|
||
|
||
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="HotelID"></param>
|
||
/// <returns></returns>
|
||
[HttpPost()]
|
||
public async Task<ReturnInfo> SendGetRCUInfoData(string HotelID)
|
||
{
|
||
ReturnInfo r = new ReturnInfo();
|
||
try
|
||
{
|
||
|
||
}
|
||
catch (Exception)
|
||
{
|
||
|
||
throw;
|
||
}
|
||
return r;
|
||
}
|
||
|
||
[HttpPost()]
|
||
public async Task<string> HttpRecv()
|
||
{
|
||
|
||
using var reader = new StreamReader(Request.Body);
|
||
var body = await reader.ReadToEndAsync();
|
||
return body;
|
||
}
|
||
|
||
|
||
#region TFTP
|
||
|
||
public static string BaseUrl = "https://www.boonlive-rcu.com/";
|
||
|
||
[HttpPost()]
|
||
public async Task<ReturnInfo> GetTFTPInfo()
|
||
{
|
||
ReturnInfo r = new ReturnInfo();
|
||
try
|
||
{
|
||
|
||
using SqlConnection con = new SqlConnection(SqlConnectionString);
|
||
await con.OpenAsync();
|
||
using SqlCommand sqlCommand = con.CreateCommand();
|
||
|
||
string sql = @"select A.*,B.HostNumber,B.MAC from TFTP_set A left join tb_Hosts B on A.HostID=B.ID;";
|
||
sqlCommand.CommandText = sql;
|
||
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
|
||
DataSet dataSet = new DataSet();
|
||
adapter.Fill(dataSet);
|
||
|
||
DataTable dt = dataSet.Tables[0];
|
||
List<TFTPSet> chaList = new List<TFTPSet>();
|
||
if (dt != null && dt.Rows.Count > 0)
|
||
{
|
||
foreach (DataRow item in dt.Rows)
|
||
{
|
||
var id = item["ID"].ToString();
|
||
var HotelCode = item["HotelCode"].ToString();
|
||
var HotelID = item["HotelID"].ToString();
|
||
var RoomNumber = item["RoomNumber"].ToString();
|
||
var HostID = item["HostID"].ToString();
|
||
var HostNumber = item["HostNumber"].ToString();
|
||
var MAC = item["MAC"].ToString();
|
||
var TargetPort = item["TargetPort"].ToString();
|
||
var TargetDomain = item["TargetDomain"].ToString();
|
||
var LT = item["LastTime"].ToString();
|
||
var IT = item["IsTrigger"].ToString();
|
||
var CT = item["CreateTime"].ToString();
|
||
TFTPSet cha = new TFTPSet();
|
||
cha.hotelcode = HotelCode;
|
||
cha.hotelid = HotelID;
|
||
cha.hostnumber = HostNumber;
|
||
cha.roomnumber = RoomNumber;
|
||
cha.mac = MAC;
|
||
cha.hostid = HostID;
|
||
cha.domain = TargetDomain;
|
||
cha.port = TargetPort;
|
||
cha.lasttime = LT;
|
||
cha.isenable = IT;
|
||
cha.createtime = CT;
|
||
chaList.Add(cha);
|
||
}
|
||
}
|
||
|
||
var U = chaList.GroupBy(A => new { A.hotelid, A.hotelcode });
|
||
List<TFTP> tl = new List<TFTP>();
|
||
foreach (var item in U)
|
||
{
|
||
TFTP t = new TFTP();
|
||
t.hotelcode = item.Key.hotelcode;
|
||
t.hotelid = item.Key.hotelid;
|
||
t.TFTPRoomList = item.ToList();
|
||
tl.Add(t);
|
||
}
|
||
|
||
r.isok = true;
|
||
r.response = tl;
|
||
}
|
||
catch (Exception)
|
||
{
|
||
|
||
throw;
|
||
}
|
||
return r;
|
||
}
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="listdata">[{"hotelcode":"","hostnumber":"","mac":""}]</param>
|
||
/// <returns></returns>
|
||
[HttpPost()]
|
||
public async Task<ReturnInfo> TFTPReadSet([FromBody] List<Dictionary<string, string>> listdata)
|
||
{
|
||
|
||
ReturnInfo r = new ReturnInfo();
|
||
try
|
||
{
|
||
var options = new RestClientOptions(BaseUrl);
|
||
var client = new RestClient(options);
|
||
|
||
var request = new RestRequest("api/TFTPReadSet");
|
||
request.AddJsonBody(listdata);
|
||
RestResponse response = await client.PostAsync(request);
|
||
string? str1 = response.Content;
|
||
r.isok = true;
|
||
r.response = str1;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
r.isok = false;
|
||
r.response = ex.Message;
|
||
}
|
||
return r;
|
||
}
|
||
|
||
|
||
[HttpPost()]
|
||
public async Task<ReturnInfo> TFTPSet_Execute([FromBody] FTP_JSON body)
|
||
{
|
||
ReturnInfo r = new ReturnInfo();
|
||
try
|
||
{
|
||
var options = new RestClientOptions(BaseUrl);
|
||
var client = new RestClient(options);
|
||
var request = new RestRequest("api/TFTPSet_Execute");
|
||
request.AddJsonBody(body);
|
||
RestResponse response = await client.PostAsync(request);
|
||
string? str1 = response.Content;
|
||
r.isok = true;
|
||
r.response = str1;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
r.isok = false;
|
||
r.response = ex.Message;
|
||
}
|
||
return r;
|
||
}
|
||
#endregion
|
||
|
||
|
||
}
|
||
public class TFTP
|
||
{
|
||
public string hotelcode { get; set; }
|
||
public string hotelid { get; set; }
|
||
public List<TFTPSet> TFTPRoomList { get; set; }
|
||
|
||
}
|
||
public class TFTPSet
|
||
{
|
||
public string hotelcode { get; set; }
|
||
public string hotelid { get; set; }
|
||
public string hostid { get; set; }
|
||
public string roomnumber { get; set; }
|
||
public string hostnumber { get; set; }
|
||
public string mac { get; set; }
|
||
public string port { get; set; }
|
||
public string domain { get; set; }
|
||
/// <summary>
|
||
/// 上传 几个小时的日志
|
||
/// </summary>
|
||
public string lasttime { get; set; }
|
||
public string isenable { get; set; }
|
||
public string createtime { get; set; }
|
||
}
|
||
|
||
public class host_data
|
||
{
|
||
public string hostnumber { get; set; }
|
||
public string mac { get; set; }
|
||
}
|
||
public class FTP_JSON
|
||
{
|
||
public List<host_data> host_data { get; set; }
|
||
public bool isenable { get; set; }
|
||
public string domain { get; set; }
|
||
public UInt16 port { get; set; }
|
||
public UInt16 lasttime { get; set; }
|
||
}
|
||
public class ChaXun
|
||
{
|
||
public string HotelID { get; set; }
|
||
public string HotelCode { get; set; }
|
||
public string TotalCount { get; set; }
|
||
}
|
||
public class ReturnInfo
|
||
{
|
||
public bool isok { set; get; } // 是否成功，true成功
|
||
public string message { set; get; } // 传递接口信息，or报错信息
|
||
public int status { set; get; } // 服务器报错信息，正确为200，错误404/500等
|
||
public object response { set; get; } // 获取到的信息本体，若报错则为null
|
||
}
|
||
}
|