Template1

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ChatworkBulkSender.Dtos;
using ChatworkBulkSender.Utils;

namespace ChatworkBulkSender.Daos
{
    public class CustomerMasterDao
    {
        private readonly DBAccess _db = new DBAccess();

        public int Insert(CustomerMasterDto dto)
        {
            var sql = @"
                INSERT
                INTO dbo.顧客マスタ(
                                    管理番号,
                                    顧客名,
                                    C_ルームID,
                                    C_宛先アカウントID,
                                    並び順,
                                    未使用フラグ,
                                    登録日時,
                                    登録ユーザ
                )
                VALUES(
                    @Id,
                    @CustomerName,
                    @RoomId,
                    @DestAccId,
                    @SortOrder,
                    @IsUnused,
                    @CreatedAt,
                    @CreatedBy
                )";

            var param = new Dictionary<string, object>
            {
                {"@Id",dto.ManagementNumber },
                { "@customerName",dto.CustomerName},
                {"@RoomId",dto.RoomId},
                {"@DestAccId",dto.DestinationAccountId },
                {"@SortOrder",dto.SortOrder },
                {"@IsUnused",dto.IsUnused},
                {"@CreatedAt",dto.CreatedAt},
                {"@CreatedBy",dto.CreatedBy}

                ,
            };

            return _db.ExecNonQuery(sql,param);
        }

        /// <summary>
        /// 全顧客一覧を取得（並び順順、未使用フラグ=ODではないレコード）
        /// </summary>
        public List<CustomerMasterDto> GetAll(bool includeUnused = false)
        {
            var sql = new StringBuilder();
            sql.AppendLine("SELECT");
            sql.AppendLine("  管理番号,");
            sql.AppendLine("  顧客名,");
            sql.AppendLine("  C_ルームID,");
            sql.AppendLine("  C_宛先アカウントID,");
            sql.AppendLine("  並び順,");
            sql.AppendLine("  未使用フラグ,");
            sql.AppendLine("  登録日時,");
            sql.AppendLine("  登録ユーザ,");
            sql.AppendLine("  更新日時,");
            sql.AppendLine("  更新ユーザ");
            sql.AppendLine("FROM dbo.顧客マスタ");
            if (!includeUnused)
                sql.AppendLine("WHERE 未使用フラグ = 0");
            sql.AppendLine("ORDER BY 並び順");

            DataTable dt = _db.ExecQuery(sql.ToString());
            var list = new List<CustomerMasterDto>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new CustomerMasterDto
                {
                    ManagementNumber = row.Field<int>("管理番号"),
                    CustomerName = row.Field<string>("顧客名"),
                    RoomId = row.Field<string>("C_ルームID"),
                    DestinationAccountId = row.Field<string>("C_宛先アカウントID"),
                    SortOrder = row.Field<int>("並び順"),
                    IsUnused = row.Field<bool>("未使用フラグ"),
                    CreatedAt = row.Field<DateTime?>("登録日時"),
                    CreatedBy = row.Field<string>("登録ユーザ"),
                    UpdatedAt = row.Field<DateTime?>("更新日時"),
                    UpdatedBy = row.Field<string>("更新ユーザ")
                });
            }

            return list;
        }

        /// <summary>
        /// 指定IDの顧客情報を取得
        /// </summary>
        public CustomerMasterDto GetById(int id)
        {
            var sql = @"
                SELECT
                  管理番号, 顧客名, C_ルームID, C_宛先アカウントID, 並び順, 未使用フラグ, 登録日時, 登録ユーザ, 更新日時, 更新ユーザ
                FROM dbo.顧客マスタ
                WHERE 管理番号 = @Id";
            var param = new Dictionary<string, object> { { "@Id", id } };
            DataTable dt = _db.ExecQuery(sql, param);
            if (dt.Rows.Count == 0) return null;

            var row = dt.Rows[0];
            return new CustomerMasterDto
            {
                ManagementNumber = row.Field<int>("管理番号"),
                CustomerName = row.Field<string>("顧客名"),
                RoomId = row.Field<string>("C_ルームID"),
                DestinationAccountId = row.Field<string>("C_宛先アカウントID"),
                SortOrder = row.Field<int>("並び順"),
                IsUnused = row.Field<bool>("未使用フラグ"),
                CreatedAt = row.Field<DateTime?>("登録日時"),
                CreatedBy = row.Field<string>("登録ユーザ"),
                UpdatedAt = row.Field<DateTime?>("更新日時"),
                UpdatedBy = row.Field<string>("更新ユーザ")
            };
        }

        /// <summary>
        /// 指定IDの情報を更新
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Update(CustomerMasterDto dto, DateTime currentUpdatedAt)
        {
            // DateTime精度問題対策：1秒以内の差は同じとみなす
            string sql = @"
            UPDATE dbo.顧客マスタ
            SET 顧客名 = @CustomerName, 
                C_ルームID = @RoomId,
                C_宛先アカウントID = @DestAccId,
                並び順 = @SortOrder,
                未使用フラグ = @IsUnused,
                更新日時 = @UpdatedAt,
                更新ユーザ = @UpdatedBy
            WHERE 管理番号 = @Id 
            AND ABS(DATEDIFF(SECOND, 更新日時, @CurrentUpdatedAt)) <= 1";

            var param = new Dictionary<string, object>
            {
                { "@customerName",dto.CustomerName},
                {"@RoomId",dto.RoomId},
                {"@DestAccId",dto.DestinationAccountId },
                {"@SortOrder",dto.SortOrder },
                {"@IsUnused",dto.IsUnused },
                {"@UpdatedAt",dto.UpdatedAt },
                {"@UpdatedBy",dto.UpdatedBy },
                {"@Id",dto.ManagementNumber },
                {"@CurrentUpdatedAt",currentUpdatedAt}
                ,
            };

          return _db.ExecNonQuery(sql, param);
        }

        public bool HasUpdatesAfter(DateTime? lastCheckTIme)
        {
            var sql = @"
            SELECT
                CASE 
                    WHEN EXISTS(
                        SELECT 1
                        FROM dbo.顧客マスタ
                        WHERE 更新日時 > @LastCheckTime
                    ) 
                    THEN 1 
                    ELSE 0 
                END AS HasUpdates";

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

            var result = _db.ExecuteScalar(sql,param);

            return Convert.ToBoolean(result);
        }
        
        /// <summary>
        /// 管理番号から顧客情報を取得
        /// </summary>
        public CustomerMasterDto GetByManagementNumber(int managementNumber)
        {
            var sql = @"
                SELECT
                    管理番号,
                    顧客名,
                    C_ルームID,
                    C_宛先アカウントID,
                    並び順,
                    未使用フラグ,
                    登録日時,
                    登録ユーザ,
                    更新日時,
                    更新ユーザ
                FROM dbo.顧客マスタ
                WHERE 管理番号 = @ManagementNumber";

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

            DataTable dt = _db.ExecQuery(sql, param);
            
            if (dt.Rows.Count == 0) return null;
            
            var row = dt.Rows[0];
            return new CustomerMasterDto
            {
                ManagementNumber = row.Field<int>("管理番号"),
                CustomerName = row.Field<string>("顧客名"),
                RoomId = row.Field<string>("C_ルームID"),
                DestinationAccountId = row.Field<string>("C_宛先アカウントID"),
                SortOrder = row.Field<int>("並び順"),
                IsUnused = row.Field<bool>("未使用フラグ"),
                CreatedAt = row.Field<DateTime?>("登録日時"),
                CreatedBy = row.Field<string>("登録ユーザ"),
                UpdatedAt = row.Field<DateTime?>("更新日時"),
                UpdatedBy = row.Field<string>("更新ユーザ")
            };
        }
    }
}