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);
|
}
|
}
|
}
|
