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.Demo.proto(message：Demo、DemoTable;Service：DemoCore) ⇒ projectファイルのInclude
|
/// 2.Demo.cs(両方)
|
/// 3.DemoTable.cs(両方)
|
/// 4.DemoService.cs(サーバー側) ⇒ StartupでMapGrpcServiceの処理が必要
|
/// 5.DemoAccess.cs(クライアント側)
|
/// </summary>
|
public class DemoService : DemoCore.DemoCoreBase
|
{
|
#region ★★★★★ Declartions ★★★★★
|
|
private static string TableName = "M_Demo";
|
private readonly ILogger<DemoService> _logger;
|
private IConfiguration m_Configuration;
|
|
#endregion
|
|
#region ★★★★★ Property ★★★★★
|
#endregion
|
|
#region ★★★★★ Class Event ★★★★★
|
|
public DemoService(ILogger<DemoService> logger, IConfiguration configuration)
|
{
|
_logger = logger;
|
m_Configuration = configuration;
|
}
|
|
#endregion
|
|
#region ★★★★★ Private Function ★★★★★
|
|
private string GetSql(DataRequest request)
|
{
|
StringBuilder sql = new StringBuilder();
|
if (request.ActionType == 0)
|
{
|
sql.Append($"SELECT * FROM {TableName} {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")}");
|
}
|
else if (request.ActionType == 1 || request.ActionType == 2)
|
{
|
PagingRequest pagingRequest = JsonSerializer.Deserialize<PagingRequest>(request.Data);
|
sql.Append($"EXECUTE Pagination {pagingRequest.PageNumber},{pagingRequest.PageSize},'{pagingRequest.Table}','{pagingRequest.Field}','{pagingRequest.OrderBy}','{CConvert.FromBase64(pagingRequest.Filter)}'");
|
}
|
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 == 2)
|
{
|
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;
|
}
|
|
|
private DemoTable Get(DataRequest request)
|
{
|
//ActionType、Dataの組み合わせでデータを取得する
|
|
DemoTable table = new DemoTable() { ErrNo = 0, ErrData = string.Empty, Name = nameof(DemoTable) };
|
OperationLog.Instance.WriteLog($"データ請求：{request.ToString()}");
|
|
using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
|
{
|
string sql = GetSql(request);
|
if (request.ActionType == 0)
|
{
|
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())
|
{
|
Demo item = new Demo();
|
item.ConvertReader(reader);
|
table.Rows.Add(item);
|
}
|
}
|
reader.Close();
|
}
|
}
|
}
|
}
|
else if (request.ActionType == 1)
|
{
|
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)
|
{
|
Demo item = new Demo();
|
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();
|
}
|
}
|
}
|
}
|
}
|
OperationLog.Instance.WriteLog("データ読込完了");
|
return table;
|
}
|
|
private DataResult Set(DemoTable 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 (Demo 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, Demo item)
|
{
|
DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
|
OperationLog.Instance.WriteLog($"データ請求：{item.ToString()}");
|
|
string sql = add ? item.AddSql() : item.UpdateSql();
|
OperationLog.Instance.WriteLog($"Sql生成：{sql}");
|
|
using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
|
{
|
if (msSqlNet.ExecuteNonQuery(sql) == -1)
|
{
|
result.ErrNo = msSqlNet.ErrNo;
|
result.ErrData = msSqlNet.ErrInfo;
|
}
|
}
|
OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
|
return result;
|
}
|
|
private async Task AddOrUpdate(bool add, IAsyncStreamReader<Demo> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
|
{
|
Demo 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 {TableName} WHERE {item.Data.ToStringUtf8()}";
|
OperationLog.Instance.WriteLog($"Sql生成：{sql}");
|
|
using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
|
{
|
if (msSqlNet.ExecuteNonQuery(sql) == -1)
|
{
|
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<DemoTable> GetData(DataRequest request, ServerCallContext context)
|
{
|
return Task.FromResult(Get(request));
|
}
|
|
public async override Task GetDataStream(IAsyncStreamReader<DataRequest> requestStream, IServerStreamWriter<DemoTable> responseStream, ServerCallContext context)
|
{
|
DataRequest request = null;
|
await foreach (var message in requestStream.ReadAllAsync())
|
{
|
request = message;
|
break;
|
}
|
DemoTable table = Get(request);
|
await responseStream.WriteAsync(table); //データ送信
|
}
|
|
public override Task<DataResult> SetData(DemoTable request, ServerCallContext context)
|
{
|
return Task.FromResult(Set(request));
|
}
|
|
public async override Task SetDataStream(IAsyncStreamReader<DemoTable> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
|
{
|
DemoTable 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(Demo request, ServerCallContext context)
|
{
|
return Task.FromResult(AddOrUpdate(true, request));
|
}
|
|
public async override Task AddStream(IAsyncStreamReader<Demo> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
|
{
|
await AddOrUpdate(true, requestStream, responseStream, context);
|
}
|
|
public override Task<DataResult> Update(Demo request, ServerCallContext context)
|
{
|
return Task.FromResult(AddOrUpdate(false, request));
|
}
|
|
public async override Task UpdateStream(IAsyncStreamReader<Demo> 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
|
}
|
}
|
