From 1a1c8e71fcd14858f595029f089b2d4a00202b32 Mon Sep 17 00:00:00 2001
From: ogi <Administrator@S-OGI-PC>
Date: Fri, 05 Dec 2025 09:24:16 +0900
Subject: [PATCH] プロジェクトファイルを追加。
---
HotelPms.GrpcService/Services/GrpcTableService.cs | 310 +++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 310 insertions(+), 0 deletions(-)
diff --git a/HotelPms.GrpcService/Services/GrpcTableService.cs b/HotelPms.GrpcService/Services/GrpcTableService.cs
new file mode 100644
index 0000000..1da2e50
--- /dev/null
+++ b/HotelPms.GrpcService/Services/GrpcTableService.cs
@@ -0,0 +1,310 @@
+using System;
+using System.Collections.Generic;
+using System.Collections.ObjectModel;
+using System.Data;
+using System.Data.Common;
+using System.Data.SqlClient;
+using System.IO;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+using Google.Protobuf;
+using Grpc.Core;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.Logging;
+using HotelPms.Data;
+using HotelPms.GrpcService.Util;
+using HotelPms.Share.Data;
+using HotelPms.Share.IO;
+using HotelPms.Share.Util;
+using HotelPms.Data.Common;
+using Org.BouncyCastle.Asn1.Ocsp;
+
+namespace HotelPms.GrpcService
+{
+ public class GrpcTableService : GrpcTableCore.GrpcTableCoreBase
+ {
+ private readonly ILogger<GrpcTableService> _logger;
+ private IConfiguration m_Configuration;
+
+ public GrpcTableService(ILogger<GrpcTableService> logger, IConfiguration configuration)
+ {
+ _logger = logger;
+ m_Configuration = configuration;
+ }
+
+ private static byte[] GetFieldValue(SqlDataReader reader, int index, Type type, bool isNull)
+ {
+ if (type == typeof(System.Int32)) { return CConvert.GetBytes((int)(isNull ? 0 : reader.GetInt32(index))); }
+ else if (type == typeof(bool)) { return CConvert.GetBytes((bool)(isNull ? false : reader.GetBoolean(index))); }
+ else if (type == typeof(DateTime)) { return CConvert.GetBytes((DateTime)(isNull ? DateTime.MinValue : reader.GetDateTime(index))); }
+ else if (type == typeof(float)) { return CConvert.GetBytes((float)(isNull ? 0F : reader.GetFloat(index))); }
+ else if (type == typeof(double)) { return CConvert.GetBytes((double)(isNull ? 0D : reader.GetDouble(index))); }
+ else if (type == typeof(decimal)) { return CConvert.GetBytes((decimal)(isNull ? 0M : reader.GetDecimal(index))); }
+ else if (type == typeof(System.Byte)) { return CConvert.GetBytes((byte)(isNull ? 0x00 : reader.GetByte(index))); }
+ else if (type == typeof(System.Int16)) { return CConvert.GetBytes((short)(isNull ? 0 : reader.GetInt16(index))); }
+ else
+ {
+ byte[] data = Encoding.UTF8.GetBytes(isNull ? string.Empty : reader.GetString(index));
+ byte[] endFlg = new byte[data.Length + 1];
+ Array.Copy(data, 0, endFlg, 0, data.Length);
+ return endFlg;
+ }
+
+ //object value = reader.GetValue(index);
+ //return GetValue(type, value);
+ }
+
+ private string GetSql(DataRequest request)
+ {
+ string[] fields;
+ StringBuilder sql = new StringBuilder();
+ if (request.ActionType == (int)ETableActionType.CustomSql)
+ {
+ sql.Append(request.Data);
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomTypeMasterGrid)
+ {
+ sql.Append($"SELECT A.*,dbo.FN_GetRoomKind(A.Kind) KindName FROM M_RoomType A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
+ }
+ else if (request.ActionType == (int)ETableActionType.BuildingMasterGrid)
+ {
+ sql.Append($"SELECT A.* FROM M_Building A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomStatusMasterGrid)
+ {
+ sql.Append($"SELECT A.* FROM M_RoomStatus A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.SortID");
+ }
+ else if (request.ActionType == (int)ETableActionType.HotelMasterGrid)
+ {
+ sql.Append($"SELECT A.* FROM S_Hotel A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
+ }
+ else if (request.ActionType == (int)ETableActionType.UpdateRoomStatus)
+ {
+ fields = request.Data.Split(new char[] { ',' });
+ sql.Append($"EXECUTE SetRoomStatus '{fields[0]}',{fields[1]},{fields[2]}");
+ }
+ else if (request.ActionType == (int)ETableActionType.DemoMasterGrid)
+ {
+ sql.Append($"SELECT A.* FROM M_Demo A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomViewTabMasterGrid)
+ {
+ sql.Append($"SELECT A.* FROM M_RoomViewTab A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomViewLayoutGrid)
+ {
+ sql.Append($"SELECT A.* FROM M_RoomViewLayout A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.TabID,A.Row,A.Col");
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomViewState)
+ {
+ fields = request.Data.Split(new char[] { ',' });
+ sql.Append($"EXECUTE GetRoomState '{fields[0]}',{fields[1]}");
+ }
+ else if (request.ActionType == (int)ETableActionType.ReportMaster)
+ {
+ sql.Append($"SELECT Name FROM S_Report WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY ID");
+ }
+ else if (request.ActionType == (int)ETableActionType.PostNoSearch)
+ {
+ sql.Append($"SELECT ZipCode,Address FROM M_Address WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE ZipCode LIKE '{request.Data}%'")} ORDER BY ZipCode");
+ }
+ else if (request.ActionType == (int)ETableActionType.AddressSearch)
+ {
+ sql.Append($"SELECT ZipCode,Address FROM M_Address WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE Address LIKE '%{request.Data}%'")} ORDER BY ZipCode");
+ }
+ else if (request.ActionType == (int)ETableActionType.HotelDate)
+ {
+ sql.Append($"SELECT TOP 1 HotelDate FROM S_Hotel WITH(NOLOCK)");
+ }
+ else if (request.ActionType == (int)ETableActionType.Sequence)
+ {
+ sql.Append($"SELECT NEXT VALUE FOR dbo.{GetSequence(CConvert.ToInt(request.Data))}");
+ }
+ else if (request.ActionType == (int)ETableActionType.TaxRate)
+ {
+ fields = request.Data.Split(new char[] { ',' });
+ sql.Append($"SELECT dbo.GetTax('{fields[0]}',{fields[1]})");
+ }
+ else if (request.ActionType == (int)ETableActionType.MasterName)
+ {
+ fields = request.Data.Split(new char[] { ',' });
+ sql.Append($"SELECT Name FROM M_{fields[0]} WITH(NOLOCK) WHERE ID = {fields[1]}");
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomTypeBase)
+ {
+ sql.Append($"SELECT Name,Kind FROM M_RoomType WITH(NOLOCK) WHERE ID = {CConvert.ToInt(request.Data)}");
+ }
+ else if (request.ActionType == (int)ETableActionType.RoomTypeList)
+ {
+ sql.Append($"SELECT ID コード,Name 名称,ShortName 略称 FROM M_RoomType WITH(NOLOCK)");
+ }
+ else if (request.ActionType == (int)ETableActionType.SalesLoginList)
+ {
+ sql.Append($"SELECT ID コード,Name 名称 FROM M_Login WITH(NOLOCK) WHERE DataType = 1");
+ }
+ else if (request.ActionType == (int)ETableActionType.SalesLoginName)
+ {
+ sql.Append($"SELECT Name FROM M_Login WITH(NOLOCK) WHERE ID = {request.Data} AND DataType = 1");
+ }
+ return sql.ToString();
+ }
+
+ private string GetSequence(int type)
+ {
+ return $"Seq{((ESeqType)type).ToString()}";
+ }
+
+ private string GetDB(DataRequest request)
+{
+ if (request.ActionType == (int)ETableActionType.PostNoSearch
+ || request.ActionType == (int)ETableActionType.AddressSearch)
+ {
+ return Setting.SystemCenterDB;
+ }
+ else if (request.ActionType == (int)ETableActionType.ReportMaster)
+ {
+ return Setting.HotelCommonDB;
+ }
+ else
+ {
+ return Setting.HotelPmsDB;
+ }
+ }
+
+ public static void SetTable(GrpcTable table, SqlDataReader reader)
+ {
+ OperationLog.Instance.WriteLog($"SetTable Begin：{DateTime.Now.ToString("HH:mm:ss fff")}");
+ List<Type> fieldTypes = new List<Type>();
+ //List<string> sqlTypes = new List<string>();
+
+ #region Col
+
+ OperationLog.Instance.WriteLog("Col Begin");
+ for (int colIdx = 0; colIdx < reader.FieldCount; colIdx++)
+ {
+ //sqlTypes.Add(reader.GetDataTypeName(colIdx));
+ fieldTypes.Add(reader.GetFieldType(colIdx));
+ int fieldTypeInt = CConvert.ToTypeInt(fieldTypes[colIdx]);
+ string name = reader.GetName(colIdx);
+ //_logger.LogInformation($"{name}：{fieldTypes[colIdx].Name} = {fieldTypeInt}");
+ table.Cols.Add(new GrpcCol() { Name = name, FieldType = fieldTypeInt, PrimaryKey = false });
+ }
+
+ #endregion
+
+ #region Row
+
+ OperationLog.Instance.WriteLog("Row Begin");
+ while (reader.Read())
+ {
+ int fieldCount = reader.FieldCount;
+ bool[] isNullCache = new bool[fieldCount];
+ for (int i = 0; i < fieldCount; i++)
+ {
+ isNullCache[i] = reader.IsDBNull(i);
+ }
+
+ using (MemoryStream stream = new MemoryStream())
+ {
+ for (int i = 0; i < fieldCount; i++)
+ {
+ stream.Write(GetFieldValue(reader, i, fieldTypes[i], isNullCache[i]));
+ }
+
+ stream.Seek(0, SeekOrigin.Begin);
+ table.Rows.Add(new GrpcRow() { Data = ByteString.FromStream(stream) });
+ }
+ }
+ OperationLog.Instance.WriteLog($"Row End⇒{table.Rows.Count}件");
+
+ #endregion
+
+ OperationLog.Instance.WriteLog($"SetTable End：{DateTime.Now.ToString("HH:mm:ss fff")}");
+ }
+
+
+ private GrpcTable Get(DataRequest request)
+ {
+ GrpcTable table = new GrpcTable() { ErrNo = 0, ErrData = string.Empty };
+ OperationLog.Instance.WriteLog(request.ToString());
+
+ using (MsSqlNet msSqlNet = new MsSqlNet(GetDB(request)))
+ {
+ using (SqlConnection dbConnect = msSqlNet.Open())
+ {
+ string sql = GetSql(request);
+ using (SqlCommand command = new SqlCommand(sql, dbConnect))
+ {
+ OperationLog.Instance.WriteLog(sql);
+ command.CommandTimeout = 60000 * 100;
+ using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
+ {
+ //エラーまだ対応していない
+ if (reader == null)
+ {
+ OperationLog.Instance.WriteLog("null");
+ table.ErrNo = msSqlNet.ErrNo;
+ table.ErrData = msSqlNet.ErrInfo;
+ }
+ else
+ {
+ while (true)
+ {
+ SetTable(table, reader);
+ if (!reader.NextResult()) { break; }
+ }
+ }
+ reader.Close();
+ }
+ }
+ }
+ }
+
+ if(table.ErrNo == 0)
+ {
+ table.ErrData = ""; //総行数を返す
+ }
+ return table;
+ }
+
+ public override Task<GrpcTable> GetData(DataRequest request, ServerCallContext context)
+ {
+ OperationLog.Instance.WriteLog("GetData Start");
+ return Task.FromResult(Get(request));
+ }
+
+ /// <summary>
+ /// データ取得
+ /// </summary>
+ /// <param name="requestStream"></param>
+ /// <param name="responseStream"></param>
+ /// <param name="context"></param>
+ /// <returns></returns>
+ public async override Task GetDataStream(IAsyncStreamReader<DataRequest> requestStream, IServerStreamWriter<GrpcTable> responseStream, ServerCallContext context)
+ {
+ DataRequest request = null;
+ await foreach (var message in requestStream.ReadAllAsync())
+ {
+ request = message;
+ break;
+ }
+
+ //データ送信
+ GrpcTable table = Get(request);
+ await responseStream.WriteAsync(table);
+ }
+
+ /// <summary>
+ ///
+ /// </summary>
+ /// <param name="requestStream"></param>
+ /// <param name="responseStream"></param>
+ /// <param name="context"></param>
+ /// <returns></returns>
+ public override Task SetData(IAsyncStreamReader<GrpcTable> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
+ {
+ return base.SetData(requestStream, responseStream, context);
+ }
+ }
+}
--
Gitblit v1.10.0