Template1

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Dapper;
using ChatworkBulkSender.Dtos;
using ChatworkBulkSender.Utils;

namespace ChatworkBulkSender.Daos
{
    public class SendHistoryDetailDao
    {
        private readonly DBAccess _db = new DBAccess();
        /// <summary>
        /// 指定[送信履歴ID]のレコード全てを取得する。
        /// </summary>
        /// <param name="sendHistoryId"></param>
        /// <returns></returns>
        public List<SendHistoryDetailDto> GetSendHistoryDetails(int sendHistoryId)
        {
            const string sql = @"
                SELECT *
                FROM dbo.送信履歴_詳細
                WHERE 送信履歴ID = @SendHistoryId
                ORDER BY 並び順";

            var param = new Dictionary<string, object> {
                { "@SendHistoryId", sendHistoryId },
            };

            DataTable dt = _db.ExecQuery(sql, param);
            var list = new List<SendHistoryDetailDto>();

            foreach (DataRow row in dt.Rows)
            {
                var dto = new SendHistoryDetailDto
                {
                    SendHistoryId = Convert.ToInt32(row["送信履歴ID"]),
                    ManagementNumber = Convert.ToInt32(row["管理番号"]),
                    CustomerName = row["顧客名"] as string,
                    RoomId = row["C_ルームID"] as string,
                    AccountId = row["C_宛先アカウントID"] as string,
                    Order = Convert.ToInt32(row["並び順"]),
                    SendActualContents = row["実際の送信内容"] as string,
                    FilePath = row["ファイルパス"] as string,
                    SentAt = row["送信日時"] != DBNull.Value
                                     ? (DateTime?)Convert.ToDateTime(row["送信日時"])
                                     : null,
                    Success = (Constants.SEND_RESULT)Convert.ToInt32(row["送信結果"]),
                    SystemErrorMessage = row["システムエラー情報"] as string,
                    DisplayErrorMessage = row["表示用エラー情報"] as string,
                    CreatedAt = row["登録日時"] != DBNull.Value
                                     ? (DateTime?)Convert.ToDateTime(row["登録日時"])
                                     : null,
                    CreatedBy = row["登録ユーザ"] as string,
                    SendAttempted = (Constants.SEND_ATTEMPTED)Convert.ToInt32(row["送信試行フラグ"])
                };
                list.Add(dto);
            }

            return list;
        }

        /// <summary>
        /// 詳細レコードを1件登録する。
        /// </summary>
        /// <param name="headerId"></param>
        /// <param name="destination"></param>
        /// /// <param name="conn"></param>
        /// <returns></returns>
        public async Task InsertSendHistoryDetailAsync(int headerId, CustomerMasterDto destination, SqlConnection conn)
        {
            const string sql = @"
                INSERT INTO dbo.送信履歴_詳細
                  (送信履歴ID, 管理番号, 顧客名, C_ルームID, C_宛先アカウントID,
                   並び順, 実際の送信内容, ファイルパス, 送信日時, 送信結果,
                   システムエラー情報, 表示用エラー情報, 登録日時, 登録ユーザ, 送信試行フラグ)
                VALUES
                  (@SendHistoryId, @ManagementNumber, @CustomerName, @RoomId, @AccountId,
                   @Order, @ActualMessage, @FilePath, @SentAt, @IsSuccess,
                   @SystemError, @DisplayError, @CreatedAt, @CreatedBy, @SendAttempted)";

            var param = new
            {
                SendHistoryId = headerId,
                ManagementNumber = destination.ManagementNumber,
                CustomerName = destination.CustomerName,
                RoomId = destination.RoomId,
                AccountId = destination.DestinationAccountId,
                Order = destination.SortOrder,
                ActualMessage = destination.SendActualContents,
                FilePath = destination.FilePath,
                SentAt = destination.Result.Success == Constants.SEND_RESULT.SUCCESS ? destination.Result.SendCompletedDt: (DateTime?)null,
                IsSuccess = destination.Result.Success,
                SystemError = destination.Result.SystemErrorMessage,
                DisplayError = destination.Result.DisplayErrorMessage,
                CreatedAt = DateTime.Now,
                CreatedBy = Environment.MachineName,
                SendAttempted = destination.Result.SendAttempted
            };

            await conn.ExecuteAsync(sql, param);
        }

        /// <summary>
        /// 管理番号で検索して該当する送信履歴IDのリストを取得
        /// </summary>
        /// <param name="managementNumber">管理番号（前方一致）</param>
        /// <returns>送信履歴IDのリスト</returns>
        public List<int> GetSendHistoryIdsByManagementNumber(string managementNumber)
        {
            if (string.IsNullOrWhiteSpace(managementNumber))
                return new List<int>();

            // 先頭のゼロを削除して数値に変換
            if (int.TryParse(managementNumber, out int mgmtNumber))
            {
                managementNumber = mgmtNumber.ToString();
            }

            const string sql = @"
                SELECT DISTINCT 送信履歴ID
                FROM dbo.送信履歴_詳細
                WHERE CAST(管理番号 AS VARCHAR) LIKE @ManagementNumber + '%'
                ORDER BY 送信履歴ID DESC";

            var param = new Dictionary<string, object>
            {
                { "@ManagementNumber", managementNumber }
            };

            DataTable dt = _db.ExecQuery(sql, param);
            var list = new List<int>();

            foreach (DataRow row in dt.Rows)
            {
                list.Add(Convert.ToInt32(row["送信履歴ID"]));
            }

            return list;
        }

        /// <summary>
        /// 顧客名で検索して該当する送信履歴IDのリストを取得
        /// </summary>
        /// <param name="customerName">顧客名（部分一致）</param>
        /// <returns>送信履歴IDのリスト</returns>
        public List<int> GetSendHistoryIdsByCustomerName(string customerName)
        {
            if (string.IsNullOrWhiteSpace(customerName))
                return new List<int>();

            const string sql = @"
                SELECT DISTINCT 送信履歴ID
                FROM dbo.送信履歴_詳細
                WHERE 顧客名 LIKE '%' + @CustomerName + '%'
                ORDER BY 送信履歴ID DESC";

            var param = new Dictionary<string, object>
            {
                { "@CustomerName", customerName }
            };

            DataTable dt = _db.ExecQuery(sql, param);
            var list = new List<int>();

            foreach (DataRow row in dt.Rows)
            {
                list.Add(Convert.ToInt32(row["送信履歴ID"]));
            }

            return list;
        }

    }
}