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.SourceFactory/Table/SqlFactory.cs | 207 +++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 207 insertions(+), 0 deletions(-)
diff --git a/HotelPms.SourceFactory/Table/SqlFactory.cs b/HotelPms.SourceFactory/Table/SqlFactory.cs
new file mode 100644
index 0000000..0d0ae6d
--- /dev/null
+++ b/HotelPms.SourceFactory/Table/SqlFactory.cs
@@ -0,0 +1,207 @@
+using HotelPms.Share.Data;
+using HotelPms.Share.Data.Script;
+using HotelPms.Share.IO;
+using HotelPms.Share.Windows.Util;
+using System.Reflection;
+using System.Text;
+
+namespace HotelPms.SourceFactory.Table
+{
+ /// <summary>
+ /// Excelのテーブル定義書より、
+ /// SqlScriptを作成する
+ /// </summary>
+ public class SqlFactory : IDisposable
+ {
+ public DBConnectItem? DBSetting { get; set; }
+ public string ExcelFile { get; set; } = string.Empty;
+
+ public void Dispose()
+ {
+
+ }
+
+ public bool CreateByExcel(string file, DBConnectItem dbSetting)
+ {
+ try
+ {
+ ExcelFile = file;
+ DBSetting = dbSetting;
+ using (NPOIExcel excel = new NPOIExcel(file))
+ {
+ for (int i = 0; i < excel.Workbook.NumberOfSheets; i++)
+ {
+ excel.SetCurrentSheet(i);
+ if (!IsTableSheet(excel)) { continue; }
+ CreateBySheet(excel);
+ }
+ }
+ return true;
+ }
+ catch (Exception ex)
+ {
+ OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{ex.Message}");
+ return false;
+ }
+ }
+
+ /// <summary>
+ /// 対象シートはテーブル定義のシートかどうか
+ /// B4 = "テーブル名" ⇒ TABLE = G4
+ /// </summary>
+ /// <param name="sheet"></param>
+ /// <returns></returns>
+ private bool IsTableSheet(NPOIExcel excel)
+ {
+ bool result = false;
+ try
+ {
+ if("テンプレート" == excel.Sheet.SheetName
+ || excel.GetCellForceString("B4") != "テーブル名"
+ || excel.GetCellForceString("B6") != "No"
+ || excel.GetCellForceString("B7") != "1"
+ || excel.GetCellForceString("D6") != "項目名"
+ || excel.GetCellForceString("K6") != "説明"
+ || excel.GetCellForceString("S6") != "型"
+ || excel.GetCellForceString("X6") != "PK")
+ {
+ result = false;
+ return result;
+ }
+
+ result = true;
+ return result;
+ }
+ catch (Exception ex)
+ {
+ OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{ex.Message}");
+ result = false;
+ return result;
+ }
+ finally
+ {
+ OperationLog.Instance.WriteLog($"{excel.Sheet.SheetName} ⇒ テーブル定義シート：{ (result ? "Yes" : "No") }");
+ }
+ }
+
+ /// <summary>
+ /// シート情報よりテーブル作成する
+ /// </summary>
+ /// <param name="sheet"></param>
+ public bool CreateBySheet(NPOIExcel excel)
+ {
+ try
+ {
+ OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{excel.Sheet.SheetName}");
+ string[] tables = excel.GetCellString("G4").Split(new char[] { ',' });
+
+ using (TableCreator creator = new TableCreator())
+ using (DataAccess dataAccess = new MsSqlNet(DBSetting))
+ {
+
+ //DB中のデータ取得
+ var dbFields = dataAccess.GetDataReader<Dictionary<string, Field>>($"EXECUTE [dbo].[GetTableDefine] '{tables[0]}'", (reader, data) =>
+ {
+ while (reader.Read())
+ {
+ var item = new Field(reader);
+ data.Add(item.Name, item);
+ }
+ });
+
+ bool isNew = dbFields == null || dbFields.Count == 0;
+ StringBuilder sqlEdit = new StringBuilder();
+
+ //Excelを読込む
+ List<Field> curFields = new List<Field>();
+ int r = 0;
+ for (int i = 6; i < excel.Sheet.LastRowNum; i++)
+ {
+ int no = excel.GetCellInt($"B{i + 1}");
+ if (no != (r + 1)) { continue; } //必ず連続
+ r++;
+ string colName = excel.GetCellString($"D{i + 1}");
+ if (colName.Length == 0) { break; }
+
+ string pk = excel.GetCellString($"X{i + 1}").ToUpper();
+ var curItem = new Field
+ {
+ Name = colName,
+ Description = excel.GetCellString($"K{i + 1}"),
+ DataType = excel.GetCellString($"S{i + 1}"),
+ IsPrimaryKey = pk.Length > 0,
+ IsIdentity = pk == "AUTO"
+ };
+
+ creator.Fields.Add(curItem); //無条件追加、データ型変更した場合、テーブル再作成になる
+
+ Field dbItem;
+ if (dbFields != null && dbFields.TryGetValue(colName, out dbItem))
+ {
+ //突合(追加・変更分のみ)
+ int ret = dbItem.CompareTo(curItem);
+ if (ret == 1 || ret == 12)
+ {
+ //削除
+ sqlEdit.AppendLine($@"IF EXISTS(SELECT * FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('*TableName*') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '{curItem.Name}' AND [object_id] = OBJECT_ID('*TableName*')))
+EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'*TableName*', @level2type=N'COLUMN',@level2name=N'{curItem.Name}';");
+
+
+ if (curItem.Description.Length > 0)
+ {
+ sqlEdit.AppendLine($"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{curItem.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'*TableName*', @level2type=N'COLUMN',@level2name=N'{curItem.Name}';");
+ }
+ }
+
+ if (ret == 2 || ret == 12)
+ {
+ //タイプ変更があったら、削除してから、再作成のほうが増し
+ isNew = true;
+ //例：DateTime⇒intではダメ
+ //sqlEdit.AppendLine($"ALTER TABLE [*TableName*] ALTER COLUMN {curItem.Name} {curItem.DataType} NULL;");
+ //sqlEdit.AppendLine($"ALTER TABLE [*TableName*] DROP COLUMN {curItem.Name};");
+ //sqlEdit.AppendLine($"ALTER TABLE [*TableName*] ADD {curItem.Name} {curItem.DataType} NULL;");
+ }
+ }
+ else
+ {
+ //新規追加
+ sqlEdit.AppendLine($"ALTER TABLE [*TableName*] ADD {curItem.Name} {curItem.DataType} NULL;");
+ }
+ }
+
+ if(!isNew && sqlEdit.Length == 0) { return true; }
+
+ //作成
+ string sql = String.Empty;
+ foreach (string tableName in tables)
+ {
+ if (isNew)
+ {
+ creator.Name = tableName;
+ creator.Description = excel.GetCellString("G5");
+ creator.PartitionSchema = excel.GetCellString("X4");
+ creator.PartitionKey = excel.GetCellString("AH4");
+ sql = creator.ToString();
+ }
+ else
+ {
+ sql = sqlEdit.ToString().Replace("*TableName*", tableName);
+ }
+
+ int ret = dataAccess.ExecuteNonQuery(sql);
+ OperationLog.Instance.WriteLog(sql);
+ if (ret == -1) { return false; }
+ }
+ }
+ return true;
+ }
+ catch (Exception ex)
+ {
+ OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{ex.Message}");
+ return false;
+ }
+ }
+
+ }
+}
--
Gitblit v1.10.0