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