DI_Backup/DI/Agent/Services/UserService.cs

using Agent.Models;
using Dapper;
using System.Data.SqlClient;
using System.Linq;
using System.Configuration;
using System.Diagnostics;
using log4net;
using System;
using System.Windows;

namespace Agent.Services
{
    class UserService
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(UserService));

        private readonly DriveService _driveService = new DriveService();

        private readonly StartupService _startupService = new StartupService();

        /// <summary>
        /// 사용자 조회
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public User GetUser(string userId)
        {
            try
            {
                var sql = @"
                    SELECT top 1 [tid]
                        ,[id]
                        ,[password]
                        ,[name]
                        ,[create_date]
                        ,[is_ad]
                        ,[is_startup]
                        ,[is_retry]
                        ,[is_limit]
                        ,[is_hash]
                        ,[backup_type]                        
                        ,[tag1]
                        ,[tag2]
                        ,[tag3]
                        ,[tag4]
                        ,[tag5]
                        ,[tag6]
                        ,[tag7]
                        ,[tag8]
                        ,[tag9]
                        ,[tag10]
                    FROM [di_user]
                    WHERE id = @UserId
                ";


                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    return con.Query<User>(sql, new { UserId = userId }).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        public User LoginCheck(string userId, string sysplantcd)
        {
            try
            {
                var sql = @"
                   SELECT [user_id] as id
                        ,[emp_cd]  as emp_cd
                        ,[user_passwd] as Password
                        ,[user_nm]  as name
                        ,[di_user_yn] as Di_Auth
                    FROM [dbo].[menu_user]
                    WHERE user_id = @UserId
					  AND sys_plant_cd = @sys_plant_cd
                 ";
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    return con.Query<User>(sql, new { UserId = userId, sys_plant_cd = sysplantcd }).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        /// <summary>
        /// 아이디(시리얼키) 중복체크
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsDuplicate(string id)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = @"
                        SELECT COUNT(*)
                        FROM [di_user]
                        WHERE id = @Id
                    ";

                    return 0 < con.Query<int>(sql, new { Id = id }).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return true;
            }
        }

        /// <summary>
        /// 사용자 가입
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="isStartup"></param>
        /// <returns></returns>
        public User JoinUser(string id, string name, bool isStartup)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = @"
                        INSERT INTO [di_user]
                            ([id]
                            ,[name]
                            ,[create_date]
                            ,[is_ad]
                            ,[is_startup]
                            ,[is_retry]
                            ,[is_limit]
                            ,[is_hash]
                            ,[backup_type]
                            ,[tag1]
                            ,[tag2]
                            ,[tag3]
                            ,[tag4]
                            ,[tag5]
                            ,[tag6]
                            ,[tag7]
                            ,[tag8]
                            ,[tag9]
                            ,[tag10]
                            ,[sys_plant_cd]
                            )
                        OUTPUT
                            INSERTED.tid AS tid
                            ,INSERTED.id AS id
                            ,INSERTED.password AS password
                            ,INSERTED.name AS name
                            ,INSERTED.create_date AS create_date
                            ,INSERTED.is_ad AS is_ad
                            ,INSERTED.is_startup AS is_startup
                            ,INSERTED.is_retry AS is_retry
                            ,INSERTED.is_limit AS is_limit
                            ,INSERTED.is_hash AS is_hash
                            ,INSERTED.backup_type AS backup_type
                            ,INSERTED.[tag1]
                            ,INSERTED.[tag2]
                            ,INSERTED.[tag3]
                            ,INSERTED.[tag4]
                            ,INSERTED.[tag5]
                            ,INSERTED.[tag6]
                            ,INSERTED.[tag7]
                            ,INSERTED.[tag8]
                            ,INSERTED.[tag9]
                            ,INSERTED.[tag10]
                            ,INSERTED.[sys_plant_cd]
                        VALUES
                            (@Id
                            ,@Name
                            ,GETDATE()
                            ,0
                            ,@IsStartup
                            ,1
                            ,1
                            ,1
                            ,@BackupType
                            ,@Tag1
                            ,@Tag2
                            ,@Tag3
                            ,@Tag4
                            ,@Tag5
                            ,@Tag6
                            ,@Tag7
                            ,@Tag8
                            ,@Tag9
                            ,@Tag10
                            ,@sysPlantCd
                            )
                    ";

                    var data = new {
                        Id = id,
                        Name = name,
                        IsStartup = isStartup,
                        BackupType = "REAL_TIME",
                        Tag1 = "TAG1",
                        Tag2 = "TAG2",
                        Tag3 = "TAG3",
                        Tag4 = "TAG4",
                        Tag5 = "TAG5",
                        Tag6 = "TAG6",
                        Tag7 = "TAG7",
                        Tag8 = "TAG8",
                        Tag9 = "TAG9",
                        Tag10 = "TAG10",
                        sysPlantCd = App._sysPlantCd
                    };

                    return con.Query<User>(sql, data).SingleOrDefault();
                }
            }
            catch(Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        /// <summary>
        /// 사용자 조회 및 가입
        /// </summary>
        /// <returns></returns>
        public User GetUserWithJoin()
        {
            var key = _driveService.GetKey();
            var user = GetUser(key);
            var isStartup = _startupService.IsRegist();

            if (null == user)
            {
                return JoinUser(key, Environment.MachineName, isStartup);
            }

            return user;
        }

        /// <summary>
        /// 사용자정보 메모리에 저장
        /// </summary>
        /// <param name="user"></param>
        public void SaveUserProperties(User user)
        {
            Application.Current.Properties["user_tid"] = user.Tid;
            Application.Current.Properties["user_id"] = user.Id;
            Application.Current.Properties["user_name"] = user.Name;
            Application.Current.Properties["user_create_date"] = user.Create_Date;
            Application.Current.Properties["user_is_startup"] = user.Is_Startup;
            Application.Current.Properties["user_is_retry"] = user.Is_Retry;
            Application.Current.Properties["user_is_limit"] = user.Is_Limit;
            Application.Current.Properties["user_is_hash"] = user.Is_Hash;
            Application.Current.Properties["user_backup_type"] = user.Backup_type;
            Application.Current.Properties["user_tag1"] = user.Tag1;
            Application.Current.Properties["user_tag2"] = user.Tag2;
            Application.Current.Properties["user_tag3"] = user.Tag3;
            Application.Current.Properties["user_tag4"] = user.Tag4;
            Application.Current.Properties["user_tag5"] = user.Tag5;
            Application.Current.Properties["user_tag6"] = user.Tag6;
            Application.Current.Properties["user_tag7"] = user.Tag7;
            Application.Current.Properties["user_tag8"] = user.Tag8;
            Application.Current.Properties["user_tag9"] = user.Tag9;
            Application.Current.Properties["user_tag10"] = user.Tag10;
        }

        /// <summary>
        /// 사용자정보 갱신
        /// </summary>
        /// <param name="isStartup"></param>
        /// <param name="isRetry"></param>
        /// <param name="isLimit"></param>
        /// <param name="isHash"></param>
        /// <param name="backupType"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public User ModifyUser(bool isStartup, bool isRetry, bool isLimit, bool isHash, string backupType, string userName,
            string tag1, string tag2, string tag3, string tag4, string tag5,
            string tag6, string tag7, string tag8, string tag9, string tag10)
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = @"
                        UPDATE [di_user ]
                        SET [is_startup] = @IsStartup
                            ,[is_retry] = @IsRetry
                            ,[is_limit] = @IsLimit
                            ,[is_hash] = @IsHash
                            ,[backup_type] = @BackupType
                            ,[name] = @UserName
                            ,[tag1] = @Tag1
                            ,[tag2] = @Tag2
                            ,[tag3] = @Tag3
                            ,[tag4] = @Tag4
                            ,[tag5] = @Tag5
                            ,[tag6] = @Tag6
                            ,[tag7] = @Tag7
                            ,[tag8] = @Tag8
                            ,[tag9] = @Tag9
                            ,[tag10] = @Tag10
                        OUTPUT
                            INSERTED.[tid]
                            ,INSERTED.[id]
                            ,INSERTED.[password]
                            ,INSERTED.[name]
                            ,INSERTED.[create_date]
                            ,INSERTED.[is_ad]
                            ,INSERTED.[is_startup]
                            ,INSERTED.[is_retry]
                            ,INSERTED.[is_limit]
                            ,INSERTED.[is_hash]
                            ,INSERTED.[backup_type]
                            ,INSERTED.[tag1]
                            ,INSERTED.[tag2]
                            ,INSERTED.[tag3]
                            ,INSERTED.[tag4]
                            ,INSERTED.[tag5]
                            ,INSERTED.[tag6]
                            ,INSERTED.[tag7]
                            ,INSERTED.[tag8]
                            ,INSERTED.[tag9]
                            ,INSERTED.[tag10]
                        WHERE tid = @UserTid
                    ";

                    var data = new
                    {
                        IsStartup = isStartup,
                        IsRetry = isRetry,
                        IsLimit = isLimit,
                        IsHash = isHash,
                        BackupType = backupType,
                        UserTid = userTid,
                        UserName = userName,
                        Tag1 = tag1,
                        Tag2 = tag2,
                        Tag3 = tag3,
                        Tag4 = tag4,
                        Tag5 = tag5,
                        Tag6 = tag6,
                        Tag7 = tag7,
                        Tag8 = tag8,
                        Tag9 = tag9,
                        Tag10 = tag10,
                    };

                    return con.Query<User>(sql, data).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        public User Insert_insertAT(string Gubun, string id, string remark, int usertid)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = $@"
                    INSERT  INTO " + App._dbName + "_at.[dbo].[di_policy_at]" +
                        "([audit_Gubun], [audit_emp_cd], [audit_timestamp],  [audit_remark], [tid]," +
                         "[user_tid],    [create_Date],  [is_delete],    [sys_plant_cd],  [path]," +
                         "[type],    [custom_tag1],  [custom_tag2],   [custom_tag3], [custom_tag4]," +
                         "[custom_tag5], [custom_tag6],  [custom_tag7],  [custom_tag8],  [custom_tag9],  [custom_tag10]" +
                       ")" +
                    "SELECT " +
                         "@Type   ,@audit_emp_cd, GETDATE(),  @audit_Remark,  [tid]," +
                         "@usertid,    [create_Date],  [is_delete],    [sys_plant_cd],  [path]," +
                         "[type],    [custom_tag1],  [custom_tag2],   [custom_tag3], [custom_tag4]," +
                         "[custom_tag5], [custom_tag6],  [custom_tag7],  [custom_tag8],  [custom_tag9],  [custom_tag10]" +

                    "FROM [di_policy]" +
                    "WHERE  [user_tid] = @usertid and [path] = @audit_Remark and is_delete = 0";

                    var data = new
                    {
                        Type = Gubun,
                        audit_emp_cd = id,
                        audit_Remark = remark,
                        usertid = usertid
                    };

                    var sql1 = sql.Replace("chang_dbName", App._dbName + "_at.[dbo].[di_policy_at] ");
                    return con.Query<User>(sql, data).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        public User Insert_deleteAT(string Gubun, string id, string remark, int tid, int usertid)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = $@"
                    INSERT  INTO " + App._dbName + "_at.[dbo].[di_policy_at]" +
                        "([audit_Gubun], [audit_emp_cd], [audit_timestamp],  [audit_remark], [tid]," +
                         "[user_tid],    [create_Date],  [is_delete],    [sys_plant_cd],  [path]," +
                         "[type],    [custom_tag1],  [custom_tag2],   [custom_tag3], [custom_tag4]," +
                         "[custom_tag5], [custom_tag6],  [custom_tag7],  [custom_tag8],  [custom_tag9],  [custom_tag10]" +
                       ")" +
                    "SELECT " +
                         "@Type   ,@audit_emp_cd, GETDATE(),  @audit_Remark,  @tid," +
                         "@usertid,    [create_Date],  [is_delete],    [sys_plant_cd],  [path]," +
                         "[type],    [custom_tag1],  [custom_tag2],   [custom_tag3], [custom_tag4]," +
                         "[custom_tag5], [custom_tag6],  [custom_tag7],  [custom_tag8],  [custom_tag9],  [custom_tag10]" +

                    "FROM [di_policy]" +
                    "WHERE  [tid] = @tid";

                    var data = new
                    {
                        Type = Gubun,
                        audit_emp_cd = id,
                        audit_Remark = remark,
                        tid = tid,
                        usertid = usertid
                    };

                    var sql1 = sql.Replace("chang_dbName", App._dbName + "_at.[dbo].[di_policy_at] ");
                    return con.Query<User>(sql, data).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        public User Insert_updateAT(string Gubun, string id, string remark,
            bool isStartup, bool isRetry, bool isLimit, bool isHash, string backupType, string userName,
            string tag1, string tag2, string tag3, string tag4, string tag5,
            string tag6, string tag7, string tag8, string tag9, string tag10)
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = $@"
                    INSERT  INTO " + App._dbName + "_at.[dbo].[di_user_at]" +
                        "([audit_Gubun], [audit_emp_cd], [audit_timestamp], [audit_remark], [tid]," +
                         "[create_Date]," +
                         "[is_startup],  [is_retry], [is_limit], [is_hash], [name]," +
                         "[backup_type], [sys_plant_cd], [id]," +
                         "[tag1], [tag2], [tag3], [tag4], [tag5]," +
                         "[tag6], [tag7], [tag8],  [tag9], [tag10]" +
                       ")" +
                    "SELECT " +
                         "@Type   ,@audit_emp_cd, GETDATE(),  @audit_Remark,  [tid]," +
                         "[create_Date]," +
                         "@IsStartup, @IsRetry, @IsLimit, @IsHash, @UserName," +
                         "@BackupType,[sys_plant_cd], [id]," +
                         "@tag1, @tag2, @tag3, @tag4, @tag5," +
                         "@tag6, @tag7, @tag8, @tag9, @tag10 " +

                    "FROM [di_user] " +
                    "WHERE  [tid] = @UserTid";

                    var data = new
                    {
                        Type = Gubun,
                        audit_emp_cd = id,
                        audit_Remark = remark,
                        IsStartup = isStartup,
                        IsRetry = isRetry,
                        IsLimit = isLimit,
                        IsHash = isHash,
                        BackupType = backupType,
                        UserTid = userTid,
                        UserName = userName,
                        Tag1 = tag1,
                        Tag2 = tag2,
                        Tag3 = tag3,
                        Tag4 = tag4,
                        Tag5 = tag5,
                        Tag6 = tag6,
                        Tag7 = tag7,
                        Tag8 = tag8,
                        Tag9 = tag9,
                        Tag10 = tag10
                    };

                    //var sql1 = sql.Replace("chang_dbName", App._dbName + "_at.[dbo].[di_user_at] ");
                    return con.Query<User>(sql, data).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

    }
}