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/OutputService.cs | 476 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 476 insertions(+), 0 deletions(-)
diff --git a/HotelPms.GrpcService/Services/OutputService.cs b/HotelPms.GrpcService/Services/OutputService.cs
new file mode 100644
index 0000000..62f0054
--- /dev/null
+++ b/HotelPms.GrpcService/Services/OutputService.cs
@@ -0,0 +1,476 @@
+using HotelPms.Data;
+using HotelPms.Data.Common.Pagination;
+using HotelPms.Data.Master;
+using HotelPms.GrpcService.Util;
+using HotelPms.Share.Data;
+using HotelPms.Share.IO;
+using HotelPms.Share.Util;
+using Google.Protobuf;
+using Grpc.Core;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.Logging;
+using System.Data;
+using System.Data.SqlClient;
+using System.Text;
+using System.Text.Json;
+using System.Threading.Tasks;
+
+namespace HotelPms.GrpcService
+{
+ /// <summary>
+ /// ■攻略
+ /// 1.Output.proto(message：Output、OutputTable;Service：OutputCore) ⇒ projectファイルのInclude
+ /// 2.Output.cs(両方)
+ /// 3.OutputTable.cs(両方)
+ /// 4.OutputService.cs(サーバー側) ⇒ StartupでMapGrpcServiceの処理が必要
+ /// 5.OutputAccess.cs(クライアント側)
+ /// </summary>
+ public class OutputService : OutputCore.OutputCoreBase
+ {
+ #region ★★★★★ Declartions ★★★★★
+
+ public enum EActionType : int
+ {
+ Master = 0,
+ Page = 1,
+ Xlsx = 2,
+ Xls = 3,
+ Pdf = 4,
+ Csv = 5,
+ Json = 6,
+ Xml = 7,
+ DefOutput = 8,
+ }
+
+ private static string TableName = "M_Output";
+ private readonly ILogger<OutputService> _logger;
+ private IConfiguration m_Configuration;
+
+ #endregion
+
+ #region ★★★★★ Property ★★★★★
+ #endregion
+
+ #region ★★★★★ Class Event ★★★★★
+
+ public OutputService(ILogger<OutputService> logger, IConfiguration configuration)
+ {
+ _logger = logger;
+ m_Configuration = configuration;
+ }
+
+ #endregion
+
+ #region ★★★★★ Private Function ★★★★★
+
+ private string GetSql(DataRequest request)
+ {
+ StringBuilder sql = new StringBuilder();
+ if (request.ActionType == (int)EActionType.Master)
+ {
+ sql.Append($"SELECT * FROM {TableName} {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")}");
+ }
+ else if (request.ActionType >= (int)EActionType.Page && request.ActionType <= (int)EActionType.Xml)
+ {
+ PagingRequest pagingRequest = JsonSerializer.Deserialize<PagingRequest>(request.Data);
+ sql.Append($"EXEC Pagination {pagingRequest.PageNumber},{pagingRequest.PageSize},'{pagingRequest.Table}','{pagingRequest.Field}','{pagingRequest.OrderBy}','{CConvert.FromBase64(pagingRequest.Filter)}'");
+ }
+ else if (request.ActionType == (int)EActionType.DefOutput)
+ {
+ sql.Append($"EXEC GetDefaultOuput {request.Data}");
+ }
+ return sql.ToString();
+ }
+
+ private DataTable GetDataTable(DataRequest request)
+ {
+ //ActionType、Dataの組み合わせでデータを取得する
+ DataTable table = null;
+ OperationLog.Instance.WriteLog($"データ請求：{request.ToString()}");
+
+ using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
+ {
+ string sql = GetSql(request);
+ // 全部データの出力(頁変更なし)
+ if (request.ActionType >= (int)EActionType.Xlsx && request.ActionType <= (int)EActionType.Xml)
+ {
+ using (DataSet set = msSqlNet.GetDataSet(sql))
+ {
+ if (set == null || set.Tables.Count == 0)
+ {
+ OperationLog.Instance.WriteLog("null");
+ }
+ else
+ {
+ if (set.Tables.Count == 2)
+ {
+ table = set.Tables[0];
+ }
+ }
+ }
+ }
+ }
+ OperationLog.Instance.WriteLog("データ読込完了");
+ return table;
+ }
+
+ /// <summary>
+ /// マスタデータのセット
+ /// </summary>
+ /// <param name="msSqlNet"></param>
+ /// <param name="sql"></param>
+ /// <param name="table"></param>
+ private void SetMaster(MsSqlNet msSqlNet, string sql, OutputTable table)
+ {
+ using (SqlConnection dbConnect = msSqlNet.Open())
+ {
+ 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 (reader.Read())
+ {
+ Output item = new Output();
+ item.ConvertReader(reader);
+ table.Rows.Add(item);
+ }
+ }
+ reader.Close();
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 頁毎データのセット
+ /// </summary>
+ /// <param name="msSqlNet"></param>
+ /// <param name="sql"></param>
+ /// <param name="table"></param>
+ private void SetPage(MsSqlNet msSqlNet, string sql, OutputTable table)
+ {
+ using (DataSet set = msSqlNet.GetDataSet(sql))
+ {
+ if (set == null || set.Tables.Count == 0)
+ {
+ OperationLog.Instance.WriteLog("null");
+ table.ErrNo = msSqlNet.ErrNo;
+ table.ErrData = msSqlNet.ErrInfo;
+ }
+ else
+ {
+ if (set.Tables.Count == 2)
+ {
+ foreach (DataRow row in set.Tables[0].Rows)
+ {
+ Output item = new Output();
+ item.ConvertDataRow(row);
+ table.Rows.Add(item);
+ }
+
+ table.Tag = JsonSerializer.Serialize(new PagingRespone()
+ {
+ MaxPage = CConvert.ToInt(set.Tables[1].Rows[0]["MaxPage"]),
+ TotalRow = CConvert.ToInt(set.Tables[1].Rows[0]["TotalRow"])
+ });
+ }
+ else
+ {
+ table.ErrNo = -1;
+ table.ErrData = set.Tables[0].Rows[0]["Descript"].ToString();
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// ディフォルト列設定をセット
+ /// </summary>
+ /// <param name="msSqlNet"></param>
+ /// <param name="sql"></param>
+ /// <param name="table"></param>
+ private void SetDefOutput(MsSqlNet msSqlNet, string sql, OutputTable table)
+ {
+ using (DataSet set = msSqlNet.GetDataSet(sql))
+ {
+ if (set == null || set.Tables.Count == 0)
+ {
+ OperationLog.Instance.WriteLog("null");
+ table.ErrNo = msSqlNet.ErrNo;
+ table.ErrData = msSqlNet.ErrInfo;
+ }
+ else
+ {
+ Output output = new Output();
+ output.ConvertDataRow(set.Tables[0].Rows[0]);
+ table.Rows.Add(output);
+
+ foreach (DataRow row in set.Tables[1].Rows)
+ {
+ OutputItem item = new OutputItem();
+ item.ConvertDataRow(row);
+ output.Items.Add(item);
+ }
+
+ // 表示名称 ⇒ フィールド名のマッピング
+ // ※存在しなかったら、表示名称 = フィールド名ということ
+ foreach (DataRow row in set.Tables[2].Rows)
+ {
+ output.FieldMap.Add(row["Name"].ToString(), row["Field"].ToString());
+ }
+
+ foreach (DataRow row in set.Tables[3].Rows)
+ {
+ output.SelectList.Add(CConvert.ToInt(row["ID"]), row["Name"].ToString());
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// ActionTypeによるデータ取得
+ /// </summary>
+ /// <param name="request"></param>
+ /// <returns></returns>
+ private OutputTable Get(DataRequest request)
+ {
+ //ActionType、Dataの組み合わせでデータを取得する
+
+ OutputTable table = new OutputTable() { ErrNo = 0, ErrData = string.Empty, Name = nameof(OutputTable) };
+ OperationLog.Instance.WriteLog($"データ請求：{request.ToString()}");
+
+ using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
+ {
+ string sql = GetSql(request);
+ if (request.ActionType == (int)EActionType.Master) { SetMaster(msSqlNet, sql, table); }
+ else if (request.ActionType == (int)EActionType.Page) { SetPage(msSqlNet, sql, table); }
+ else if (request.ActionType == (int)EActionType.DefOutput) { SetDefOutput(msSqlNet, sql, table); }
+ }
+ OperationLog.Instance.WriteLog("データ読込完了");
+ return table;
+ }
+
+ private DataResult Set(OutputTable table)
+ {
+ DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
+ OperationLog.Instance.WriteLog($"データ請求：{table.Rows.Count}");
+
+ StringBuilder sql = new StringBuilder();
+ if (table.BeforeUpdate.Length > 0) { sql.AppendLine(table.BeforeUpdate.ToStringUtf8()); }
+
+ foreach (Output item in table.Rows)
+ {
+ sql.AppendLine(table.UpdateType == 2 ? item.UpdateSql() : item.AddSql());
+ }
+
+ if (table.AfterUpdate.Length > 0) { sql.AppendLine(table.AfterUpdate.ToStringUtf8()); }
+
+ string sqlCmd = sql.ToString();
+ OperationLog.Instance.WriteLog($"Sql生成：{sqlCmd}");
+
+ using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
+ {
+ if (!msSqlNet.ExecuteNonQueryWithTran(sqlCmd))
+ {
+ result.ErrNo = msSqlNet.ErrNo;
+ result.ErrData = msSqlNet.ErrInfo;
+ }
+ }
+ OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
+ return result;
+ }
+
+ private DataResult AddOrUpdate(bool add, Output item)
+ {
+ DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
+ OperationLog.Instance.WriteLog($"データ請求：{item.ToString()}");
+
+ using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
+ {
+ StringBuilder sql = new StringBuilder();
+ if (add)
+ {
+ int id = CConvert.ToInt(msSqlNet.ExecuteScalar(item.MaxIDSql())) + 1;
+ item.ID = id;
+ }
+ else
+ {
+ sql.AppendLine(item.ClearDetailSql());
+ }
+ sql.AppendLine(add ? item.AddSql() : item.UpdateSql());
+ int i = 1;
+ foreach (OutputItem detail in item.Items)
+ {
+ detail.MachineName = item.MachineName;
+ detail.UserName = item.UserName;
+ detail.ReportID = item.ReportID;
+ detail.OutputID = item.ID;
+ detail.ID = i;
+ sql.AppendLine(detail.AddSql());
+ i++;
+ }
+
+ OperationLog.Instance.WriteLog($"Sql生成：{sql}");
+
+ if (!msSqlNet.ExecuteNonQueryWithTran(sql.ToString()))
+ {
+ result.ErrNo = msSqlNet.ErrNo;
+ result.ErrData = msSqlNet.ErrInfo;
+ }
+ }
+ OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
+ return result;
+ }
+
+ private async Task AddOrUpdate(bool add, IAsyncStreamReader<Output> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
+ {
+ Output item = null;
+ await foreach (var message in requestStream.ReadAllAsync())
+ {
+ item = message;
+ break;
+ }
+
+ DataResult result = AddOrUpdate(add, item);
+ await responseStream.WriteAsync(result); //データ送信
+ }
+
+ private DataResult Delete(SqlWhere item)
+ {
+ DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
+ OperationLog.Instance.WriteLog($"データ請求：{item.ToString()}");
+
+ string sql = $@"DELETE FROM M_OutputItem WHERE OutputID IN (SELECT ID FROM {TableName} WHERE {item.Data.ToStringUtf8()});
+DELETE FROM {TableName} WHERE {item.Data.ToStringUtf8()};";
+ OperationLog.Instance.WriteLog($"Sql生成：{sql}");
+
+ using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
+ {
+ if (!msSqlNet.ExecuteNonQueryWithTran(sql))
+ {
+ result.ErrNo = msSqlNet.ErrNo;
+ result.ErrData = msSqlNet.ErrInfo;
+ }
+ }
+ OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
+ return result;
+ }
+
+ #endregion
+
+
+ #region ★★★★★ Public Function ★★★★★
+
+ public async override Task OutputStream(DataRequest request, IServerStreamWriter<FileGrpcData> responseStream, ServerCallContext context)
+ {
+ try
+ {
+ using (DataTable data = GetDataTable(request))
+ {
+ byte[] bytes = Excel.Export(data);
+ FileGrpcData fileGrpcData = new FileGrpcData
+ {
+ Block = 1,
+ FileName = "Data",
+ Content = ByteString.CopyFrom(bytes)
+ };
+ await responseStream.WriteAsync(fileGrpcData);
+ }
+ }
+ catch { }
+ }
+
+ /// <summary>
+ /// 同期実行
+ /// </summary>
+ /// <param name="request"></param>
+ /// <param name="context"></param>
+ /// <returns></returns>
+ public override Task<OutputTable> GetData(DataRequest request, ServerCallContext context)
+ {
+ return Task.FromResult(Get(request));
+ }
+
+ public async override Task GetDataStream(IAsyncStreamReader<DataRequest> requestStream, IServerStreamWriter<OutputTable> responseStream, ServerCallContext context)
+ {
+ DataRequest request = null;
+ await foreach (var message in requestStream.ReadAllAsync())
+ {
+ request = message;
+ break;
+ }
+ OutputTable table = Get(request);
+ await responseStream.WriteAsync(table); //データ送信
+ }
+
+ public override Task<DataResult> SetData(OutputTable request, ServerCallContext context)
+ {
+ return Task.FromResult(Set(request));
+ }
+
+ public async override Task SetDataStream(IAsyncStreamReader<OutputTable> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
+ {
+ OutputTable table = null;
+ await foreach (var message in requestStream.ReadAllAsync())
+ {
+ table = message;
+ break;
+ }
+
+ DataResult result = Set(table);
+ //データ送信
+ await responseStream.WriteAsync(result);
+ }
+
+ public override Task<DataResult> Add(Output request, ServerCallContext context)
+ {
+ return Task.FromResult(AddOrUpdate(true, request));
+ }
+
+ public async override Task AddStream(IAsyncStreamReader<Output> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
+ {
+ await AddOrUpdate(true, requestStream, responseStream, context);
+ }
+
+ public override Task<DataResult> Update(Output request, ServerCallContext context)
+ {
+ return Task.FromResult(AddOrUpdate(false, request));
+ }
+
+ public async override Task UpdateStream(IAsyncStreamReader<Output> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
+ {
+ await AddOrUpdate(false, requestStream, responseStream, context);
+ }
+
+ public override Task<DataResult> Remove(SqlWhere request, ServerCallContext context)
+ {
+ return Task.FromResult(Delete(request));
+ }
+
+ public async override Task RemoveStream(IAsyncStreamReader<SqlWhere> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
+ {
+ SqlWhere item = null;
+ await foreach (var message in requestStream.ReadAllAsync())
+ {
+ item = message;
+ break;
+ }
+
+ DataResult result = Delete(item);
+ await responseStream.WriteAsync(result); //データ送信
+ }
+
+ #endregion
+ }
+}
--
Gitblit v1.10.0