Template2

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 SendHistoryDao
    {

        private readonly DBAccess _db = new DBAccess();

        public List<SendHistoryDto> GetSendTypeList(int type)
        {
            string sql = $@"
            SELECT 
              *,
            CASE 
                WHEN 送信失敗件数 > 0 
                THEN 'エラーあり'
                ELSE '成功'
            END AS '送信結果'
            FROM dbo.送信履歴
            WHERE 送信タイプ = @type   
            ORDER BY 送信日時 DESC";

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

            DataTable dt = _db.ExecQuery(sql,param);

            var list = new List<SendHistoryDto>();

            foreach (DataRow row in dt.Rows)
            {
                var dto = new SendHistoryDto
                {
                    SendHistoryId = row.Field<int>("送信履歴ID"),
                    SendType = row.Field<int>("送信タイプ"),
                    SuccessCount = row.Field<int>("送信成功件数"),
                    FailureCount = row.Field<int>("送信失敗件数"),
                    SenderApiToken = row.Field<string>("C_APIトークン"),
                    PatternId = row.Field<int>("送信パターンID"),
                    PatternName = row.Field<string>("送信パターン名称"),
                    PatternTemplateText = row.Field<string>("送信パターン定型文"),
                    PatternTarget = row.Field<int>("送信パターン送信対象"),
                    PatternSortOrder = row.Field<int>("送信パターン並び順"),
                    SendContents = row.Field<string>("送信内容"),
                    RegularDestinationSelectType = (Constants.DESTINAION_SELECT_TYPE)Convert.ToInt32(row["定期送信_送信対象"]),
                    RegularFolderPath = row.Field<string>("定期送信_添付ファイル管理フォルダパス"),
                    AdhocHasAttachment = row.Field<bool>("不定期送信_添付ファイル有無"),
                    AdhocAttachmentFilePath = row.Field<string>("不定期送信_添付ファイルパス"),
                    SendDt = row.Field<DateTime?>("送信日時"),
                    SendResults = row.Field<string>("送信結果")
                   ,
                };

                list.Add(dto);
            }

            return list;
        }

        public SendHistoryDto GetSendHistory(int sendHistoryId)
        {
            string sql = $@"
                SELECT *
                FROM dbo.送信履歴
                WHERE 送信履歴ID = @SendHistoryId";

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

            DataTable dt = _db.ExecQuery(sql, param);
            foreach (DataRow row in dt.Rows)
            {
                var dto = new SendHistoryDto
                {
                    SendHistoryId = Convert.ToInt32(row["送信履歴ID"]),
                    SendType = Convert.ToInt32(row["送信タイプ"]),
                    SendDt = row["送信日時"] != DBNull.Value
                                ? (DateTime?)Convert.ToDateTime(row["送信日時"])
                                : null,
                    SuccessCount = Convert.ToInt32(row["送信成功件数"]),
                    FailureCount = Convert.ToInt32(row["送信失敗件数"]),
                    SenderApiToken = row["C_APIトークン"] as string,
                    PatternId = row["送信パターンID"] != DBNull.Value
                                   ? (int?)Convert.ToInt32(row["送信パターンID"])
                                   : null,
                    PatternName = row["送信パターン名称"] as string,
                    PatternTemplateText = row["送信パターン定型文"] as string,
                    PatternTarget = row["送信パターン送信対象"] != DBNull.Value
                                   ? (int?)Convert.ToInt32(row["送信パターン送信対象"])
                                   : null,
                    PatternSortOrder = row["送信パターン並び順"] != DBNull.Value
                                   ? (int?)Convert.ToInt32(row["送信パターン並び順"])
                                   : null,
                    SendContents = row["送信内容"] as string,
                    RegularDestinationSelectType = (Constants.DESTINAION_SELECT_TYPE)Convert.ToInt32(row["定期送信_送信対象"]),
                    RegularFolderPath = row["定期送信_添付ファイル管理フォルダパス"] as string,
                    AdhocHasAttachment = Convert.ToBoolean(row["不定期送信_添付ファイル有無"]),
                    AdhocAttachmentFilePath = row["不定期送信_添付ファイルパス"] as string,
                    CreateDate = (DateTime?)Convert.ToDateTime(row["登録日時"]),
                    CreateBy = row["登録ユーザ"] as string,
                    WindowsUserName = row["Windowsログインユーザー名"] as string
                };
                return dto;
            }
            return null;
        }

        /// <summary>
        /// 送信履歴ヘッダーを登録し、生成された履歴IDを返す。
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public async Task<int> InsertSendHistoryHeaderAsync(BulkSendJobDto dto, SqlConnection conn)
        {
            const string sql = @"
                INSERT INTO dbo.送信履歴
                  (送信タイプ, 送信日時, C_APIトークン, 送信パターンID, 送信パターン名称,
                   送信パターン定型文, 送信パターン送信対象, 送信パターン並び順, 送信内容,
                   定期送信_送信対象, 定期送信_添付ファイル管理フォルダパス,
                   不定期送信_添付ファイル有無, 不定期送信_添付ファイルパス,
                   登録日時, 登録ユーザ, Windowsログインユーザー名)
                OUTPUT INSERTED.送信履歴ID
                VALUES
                  (@SendType, @SendAt, @ApiToken, @PatternId, @PatternName,
                   @PatternTemplate, @PatternTarget, @PatternOrder, @Content,
                   @RegularDestinationSelectType, @RegularAttachmentFolderPath,
                   @AdhocHasAttachment, @AdhocAttachmentFilePath,
                   @CreatedAt, @CreatedBy, @WindowsUserName)";

            var param = new
            {
                SendType = dto.SendType,
                SendAt = dto.SendCompletedDt,
                ApiToken = dto.ApiToken,
                PatternId = dto.PatternMaster?.PatternId,
                PatternName = dto.PatternMaster?.PatternName,
                PatternTemplate = dto.PatternMaster?.TemplateText,
                PatternTarget = dto.PatternMaster?.Target,
                PatternOrder = dto.PatternMaster?.SortOrder,
                Content = dto.SendContents,
                RegularDestinationSelectType = dto.RegularDestinationSelectType,
                RegularAttachmentFolderPath = dto.RegularAttachmentFolderPath,
                AdhocHasAttachment = dto.AdhocHasAttachment,
                AdhocAttachmentFilePath = dto.AdhocAttachmentFilePath,
                CreatedAt = DateTime.Now,
                CreatedBy = Environment.MachineName,
                WindowsUserName = Environment.UserName
            };

            return await conn.ExecuteScalarAsync<int>(sql, param);
        }

        /// <summary>
        /// ヘッダーの成功件数／失敗件数を更新する。
        /// </summary>
        /// <param name="headerId"></param>
        /// <param name="successCount"></param>
        /// <param name="failureCount"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public async Task UpdateSendHistoryHeaderCountsAsync(int headerId, int successCount, int failureCount, SqlConnection conn)
        {
            const string sql = @"
                UPDATE dbo.送信履歴
                SET 送信成功件数 = @SuccessCount,
                    送信失敗件数 = @FailureCount,
                    送信日時 = SYSDATETIME()
                WHERE 送信履歴ID = @HistoryId";

            var param = new
            {
                SuccessCount = successCount,
                FailureCount = failureCount,
                HistoryId = headerId
            };

            await conn.ExecuteAsync(sql, param);
        }

    }

}