using System;
using System.Collections.Generic;
using System.Data;
using ChatworkBulkSender.Dtos;
using ChatworkBulkSender.Utils;
namespace ChatworkBulkSender.Daos
{
public class PatternMasterDao
{
private readonly DBAccess _db = new DBAccess();
private readonly PatternCustomerMasterDao _customerDao = new PatternCustomerMasterDao();
/// <summary>
/// 有効な送信パターン一覧を取得
/// </summary>
public List<PatternMasterDto> GetPatterns(bool includeEmptyPattern = false)
{
string sql = @"
SELECT
*
FROM dbo.送信パターンマスタ
WHERE 削除フラグ = @isDeleted
AND 未使用フラグ = @isUnused
ORDER BY 並び順";
var param = new Dictionary<string, object> {
{ "@isDeleted", Constants.DELETE_FLAG.NON_DELETED },
{ "@isUnused", Constants.UNUSED_FLAG.USED }
};
DataTable dt = _db.ExecQuery(sql, param);
var list = new List<PatternMasterDto>();
foreach (DataRow row in dt.Rows)
{
var dto = new PatternMasterDto
{
PatternId = row.Field<int>("送信パターンID"),
PatternName = row.Field<string>("送信パターン名称"),
TemplateText = row.Field<string>("定型文"),
Target = row.Field<int>("送信対象"),
SortOrder = row.Field<int>("並び順"),
IsUnused = row.Field<bool>("未使用フラグ"),
IsDeleted = row.Field<bool>("削除フラグ"),
CreatedDate = row.Field<DateTime?>("登録日時"),
CreatedBy = row.Field<string>("登録ユーザ"),
Customers = _customerDao.GetCustomersByPatternId(row.Field<int>("送信パターンID"))
};
list.Add(dto);
}
// 先頭への空データ挿入
if (includeEmptyPattern)
{
var emptyDto = new PatternMasterDto();
list.Insert(0, emptyDto);
}
return list;
}
/// <summary>
/// 全ての送信パターンを取得（削除済みを除く）
/// </summary>
/// <param name="includeUnused">未使用データを含めるかどうか（デフォルト: true）</param>
public List<PatternMasterDto> GetAll(bool includeUnused = true)
{
string sql = @"
SELECT
*
FROM dbo.送信パターンマスタ
WHERE 削除フラグ = @isDeleted";
var param = new Dictionary<string, object> {
{ "@isDeleted", Constants.DELETE_FLAG.NON_DELETED }
};
// 未使用データを除外する場合
if (!includeUnused)
{
sql += " AND 未使用フラグ = @isUnused";
param.Add("@isUnused", Constants.UNUSED_FLAG.USED);
}
sql += " ORDER BY 並び順";
DataTable dt = _db.ExecQuery(sql, param);
var list = new List<PatternMasterDto>();
foreach (DataRow row in dt.Rows)
{
var dto = new PatternMasterDto
{
PatternId = row.Field<int>("送信パターンID"),
PatternName = row.Field<string>("送信パターン名称"),
TemplateText = row.Field<string>("定型文"),
Target = row.Field<int>("送信対象"),
SortOrder = row.Field<int>("並び順"),
IsUnused = row.Field<bool>("未使用フラグ"),
IsDeleted = row.Field<bool>("削除フラグ"),
CreatedDate = row.Field<DateTime?>("登録日時"),
CreatedBy = row.Field<string>("登録ユーザ"),
UpdatedDate = row.Field<DateTime?>("更新日時")?.ToString("yyyy-MM-dd HH:mm:ss"),
UpdateBy = row.Field<string>("更新ユーザ"),
Customers = _customerDao.GetCustomersByPatternId(row.Field<int>("送信パターンID"))
};
list.Add(dto);
}
return list;
}
/// <summary>
/// 指定日時以降に更新があるかチェック
/// </summary>
public bool HasUpdatesAfter(DateTime? lastLoadTime)
{
if (lastLoadTime == null) return false;
string sql = @"
SELECT COUNT(*)
FROM dbo.送信パターンマスタ
WHERE 削除フラグ = @isDeleted
AND (登録日時 > @lastLoadTime OR 更新日時 > @lastLoadTime)";
var param = new Dictionary<string, object> {
{ "@isDeleted", Constants.DELETE_FLAG.NON_DELETED },
{ "@lastLoadTime", lastLoadTime.Value }
};
DataTable dt = _db.ExecQuery(sql, param);
return (int)dt.Rows[0][0] > 0;
}
/// <summary>
/// 新規送信パターンを登録
/// </summary>
public int Insert(PatternMasterDto dto)
{
string sql = @"
INSERT INTO dbo.送信パターンマスタ(
送信パターン名称,
定型文,
送信対象,
並び順,
未使用フラグ,
削除フラグ,
登録日時,
登録ユーザ,
更新日時,
更新ユーザ
)
VALUES(
@PatternName,
@TemplateText,
@Target,
@SortOrder,
@IsUnused,
@IsDeleted,
@CreatedDate,
@CreatedBy,
@UpdatedDate,
@UpdatedBy
)";
var param = new Dictionary<string, object>
{
{"@PatternName", dto.PatternName},
{"@TemplateText", dto.TemplateText},
{"@Target", dto.Target},
{"@SortOrder", dto.SortOrder},
{"@IsUnused", dto.IsUnused},
{"@IsDeleted", dto.IsDeleted},
{"@CreatedDate", dto.CreatedDate},
{"@CreatedBy", dto.CreatedBy},
{"@UpdatedDate", dto.UpdatedDate},
{"@UpdatedBy", dto.UpdateBy}
};
return _db.ExecNonQuery(sql, param);
}
public int InsertWithCustomersBatch(PatternMasterDto dto, List<int> selectedCustomerIds)
{
// パターンマスタへの挿入（既存のInsertメソッドを使用）
int result = Insert(dto);
if (result > 0 && selectedCustomerIds != null && selectedCustomerIds.Count > 0)
{
// 最新のパターンIDを取得
string sqlGetId = @"
SELECT TOP 1 送信パターンID
FROM dbo.送信パターンマスタ
WHERE 送信パターン名称 = @PatternName
ORDER BY 送信パターンID DESC";
var param = new Dictionary<string, object> { { "@PatternName", dto.PatternName } };
var dt = _db.ExecQuery(sqlGetId, param);
if (dt.Rows.Count > 0)
{
int patternId = Convert.ToInt32(dt.Rows[0]["送信パターンID"]);
// 顧客データの一括挿入
foreach (var customerId in selectedCustomerIds)
{
string sqlCustomer = @"
INSERT INTO dbo.送信パターンマスタ_顧客(
送信パターンID,
管理番号,
登録日時,
登録ユーザ
)
VALUES(
@PatternId,
@ManagementNumber,
@CreatedDate,
@CreatedBy
)";
var paramCustomer = new Dictionary<string, object>
{
{"@PatternId", patternId},
{"@ManagementNumber", customerId},
{"@CreatedDate", DateTime.Now},
{"@CreatedBy", Environment.UserName}
};
_db.ExecNonQuery(sqlCustomer, paramCustomer);
}
}
}
return result;
}
/// <summary>
/// 送信パターンを更新（既存データの論理削除＋新規データのINSERT）
/// </summary>
/// <returns>新しい送信パターンID。エラーの場合は-1</returns>
public int Update(PatternMasterDto dto, DateTime currentUpdatedDate)
{
// 1. まず更新が可能かチェック
string checkSql = @"
SELECT COUNT(*)
FROM dbo.送信パターンマスタ
WHERE 送信パターンID = @PatternId
AND 削除フラグ = @CurrentIsDeleted
AND 更新日時 = @CurrentUpdatedDate";
var checkParam = new Dictionary<string, object>
{
{"@PatternId", dto.PatternId},
{"@CurrentIsDeleted", Constants.DELETE_FLAG.NON_DELETED},
{"@CurrentUpdatedDate", currentUpdatedDate}
};
var checkResult = _db.ExecuteScalar(checkSql, checkParam);
if (checkResult == null || (int)checkResult != 1)
{
return -1; // 楽観的ロック
}
// 2. 既存レコードを論理削除するSQL
string updateSql = @"
UPDATE dbo.送信パターンマスタ
SET 削除フラグ = @IsDeleted,
更新日時 = @UpdatedDate,
更新ユーザ = @UpdatedBy
WHERE 送信パターンID = @PatternId
AND 削除フラグ = @CurrentIsDeleted
AND 更新日時 = @CurrentUpdatedDate";
var updateParam = new Dictionary<string, object>
{
{"@IsDeleted", Constants.DELETE_FLAG.DELETED},
{"@UpdatedDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")},
{"@UpdatedBy", dto.UpdateBy},
{"@PatternId", dto.PatternId},
{"@CurrentIsDeleted", Constants.DELETE_FLAG.NON_DELETED},
{"@CurrentUpdatedDate", currentUpdatedDate}
};
// 3. 新規レコードをINSERTするSQL
string insertSql = @"
INSERT INTO dbo.送信パターンマスタ(
送信パターン名称,
定型文,
送信対象,
並び順,
未使用フラグ,
削除フラグ,
登録日時,
登録ユーザ,
更新日時,
更新ユーザ
)
VALUES(
@PatternName,
@TemplateText,
@Target,
@SortOrder,
@IsUnused,
@IsDeleted,
@CreatedDate,
@CreatedBy,
@UpdatedDate,
@UpdatedBy
)";
var insertParam = new Dictionary<string, object>
{
{"@PatternName", dto.PatternName},
{"@TemplateText", dto.TemplateText},
{"@Target", dto.Target},
{"@SortOrder", dto.SortOrder},
{"@IsUnused", dto.IsUnused},
{"@IsDeleted", Constants.DELETE_FLAG.NON_DELETED},
{"@CreatedDate", DateTime.Now},
{"@CreatedBy", dto.UpdateBy}, // 更新ユーザーを登録ユーザーとして使用
{"@UpdatedDate", dto.UpdatedDate},
{"@UpdatedBy", dto.UpdateBy}
};
// 4. トランザクションで実行
var commands = new List<(string Sql, Dictionary<string, object> Parameters)>
{
(updateSql, updateParam),
(insertSql, insertParam)
};
int result = _db.ExecNonQuery(commands);
// 5. 新しく挿入されたパターンIDを取得
if (result >= 2) // 1行更新 + 1行挿入
{
var latestPattern = GetLatestPattern();
return latestPattern?.PatternId ?? -1;
}
return -1;
}
/// <summary>
/// 最後に挿入されたパターンを取得
/// </summary>
public PatternMasterDto GetLatestPattern()
{
string sql = @"
SELECT TOP 1 *
FROM dbo.送信パターンマスタ
WHERE 削除フラグ = @IsDeleted
ORDER BY 送信パターンID DESC";
var param = new Dictionary<string, object>
{
{"@IsDeleted", Constants.DELETE_FLAG.NON_DELETED}
};
DataTable dt = _db.ExecQuery(sql, param);
if (dt.Rows.Count == 0) return null;
var row = dt.Rows[0];
return new PatternMasterDto
{
PatternId = row.Field<int>("送信パターンID"),
PatternName = row.Field<string>("送信パターン名称"),
TemplateText = row.Field<string>("定型文"),
Target = row.Field<int>("送信対象"),
SortOrder = row.Field<int>("並び順"),
IsUnused = row.Field<bool>("未使用フラグ"),
IsDeleted = row.Field<bool>("削除フラグ"),
CreatedDate = row.Field<DateTime?>("登録日時"),
CreatedBy = row.Field<string>("登録ユーザ"),
UpdatedDate = row.Field<DateTime?>("更新日時")?.ToString("yyyy-MM-dd HH:mm:ss"),
UpdateBy = row.Field<string>("更新ユーザ")
};
}
/// <summary>
/// 指定IDの送信パターンを取得
/// </summary>
public PatternMasterDto GetById(int patternId)
{
string sql = @"
SELECT *
FROM dbo.送信パターンマスタ
WHERE 送信パターンID = @PatternId
AND 削除フラグ = @IsDeleted";
var param = new Dictionary<string, object>
{
{"@PatternId", patternId},
{"@IsDeleted", Constants.DELETE_FLAG.NON_DELETED}
};
DataTable dt = _db.ExecQuery(sql, param);
if (dt.Rows.Count == 0) return null;
var row = dt.Rows[0];
return new PatternMasterDto
{
PatternId = row.Field<int>("送信パターンID"),
PatternName = row.Field<string>("送信パターン名称"),
TemplateText = row.Field<string>("定型文"),
Target = row.Field<int>("送信対象"),
SortOrder = row.Field<int>("並び順"),
IsUnused = row.Field<bool>("未使用フラグ"),
IsDeleted = row.Field<bool>("削除フラグ"),
CreatedDate = row.Field<DateTime?>("登録日時"),
CreatedBy = row.Field<string>("登録ユーザ"),
UpdatedDate = row.Field<DateTime?>("更新日時")?.ToString("yyyy-MM-dd HH:mm:ss"),
UpdateBy = row.Field<string>("更新ユーザ"),
Customers = _customerDao.GetCustomersByPatternId(patternId)
};
}
}
}