DI_Backup/DI/Agent/Services/PolicyService.cs

using Agent.Models;
using log4net;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using Dapper;
using System.Linq;
using System.Windows;
using System.IO;

namespace Agent.Services
{
    class PolicyService
    {
        public static readonly string _jobTimer = @ConfigurationManager.AppSettings["jobTimer"];

        private static readonly ILog log = LogManager.GetLogger(typeof(PolicyService));

        private readonly FileTableService _fileTableService = new FileTableService();

        private readonly ReportService _reportService = new ReportService();

        private readonly HashService _hashService = new HashService();

        private readonly UserService _userService = new UserService();

        private readonly JobKey _jobKey = new JobKey("PolicyJob");

        private readonly TriggerKey _triggerKey = new TriggerKey("PolicyTrigger");
        //시험번호
        public static string G_Testcontrolid = "";
        //시험항목번호
        public static string G_teststandardmasterid = "";
        private System.Windows.Forms.OpenFileDialog openFileDialog1;
        System.IO.FileStream fs;//문서파일을 위함
        System.IO.BinaryReader br;//문서파일을 위함
        private byte[] hwpfile;//문서파일을 위함

        /// <summary>
        /// 정책 조회
        /// </summary>
        /// <returns></returns>
        public List<Policy> GetPolicies()
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

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

                    var sql = $@"
                        SELECT [tid]
                            ,[user_tid]
                            ,[path]
                            ,[type]
                            ,[is_delete]
                            ,[create_date]
                            ,[custom_tag1]
                            ,[custom_tag2]
                            ,[custom_tag3]
                        FROM [di_policy]
                        WHERE 1=1
                        AND user_tid = @UserTid
                        AND is_delete = 0
                    ";

                    return con.Query<Policy>(sql, new { UserTid = userTid }).ToList();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }
        public List<Policy> GetPoliciesRowNum()
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

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

                    var sql = $@"
                        SELECT ROW_NUMBER() OVER (ORDER BY [tid] DESC) AS RowNum 
                            , [tid]
                            ,[user_tid]
                            ,[path]
                            ,[type]
                            ,[is_delete]
                            ,[create_date]
                            ,[custom_tag1]
                            ,[custom_tag2]
                            ,[custom_tag3]
                            ,[custom_tag4]
                            ,[custom_tag5]
                            ,[custom_tag6]
                            ,[custom_tag7]
                            ,[custom_tag8]
                            ,[custom_tag9]
                            ,[custom_tag10]
                        FROM [di_policy]
                        WHERE 1=1
                        AND user_tid = @UserTid
                        AND is_delete = 0
                        ORDER BY [tid] DESC
                    ";

                    return con.Query<Policy>(sql, new { UserTid = userTid }).ToList();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return new List<Policy>();
            }
        }

        /// <summary>
        /// 정책 삭제
        /// </summary>
        /// <param name="policyTid"></param>
        /// <returns></returns>
        public bool DeletePolicy(int policyTid)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = $@"
                        UPDATE [di_policy]
                        SET [is_delete] = 1
                        WHERE tid = @PolicyTid
                    ";

                    return 0 < con.Execute(sql, new { PolicyTid = policyTid });
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }

        /// <summary>
        /// 정책 추가
        /// </summary>
        /// <param name="con"></param>
        /// <param name="type"></param>
        /// <param name="srcPath"></param>
        /// <returns></returns>
        private void AddPolicy(SqlConnection con, string type, string srcPath, string id)
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];
                var sql = @"
                    INSERT INTO [di_policy]
                        ([user_tid]
                        ,[path]
                        ,[type]
                        ,[is_delete]
                        ,[create_date]
                        ,[sys_plant_cd]
                        )
                    VALUES
                        (@UserTid
                        ,@Path
                        ,@Type
                        ,0
                        ,GETDATE()
                        ,@sysPlantCd
                        )
                ";

                con.Execute(sql, new { UserTid = userTid, Type = type, Path = srcPath, sysPlantCd = App._sysPlantCd });
                var sql1 = _userService.Insert_insertAT("추가", id, srcPath, Convert.ToInt32(userTid));
            }

            catch (Exception)
            {
                throw;
            }
        }
        public bool AddFilePolicy(string filePath, string id)
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

                using (var con = new SqlConnection(App._myConnection))
                {
                    if (IsDuplicatedPath(con, filePath))
                    {
                        return false;
                    }

                    AddPolicy(con, "FILE", filePath, id);
                    return true;
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }
        public bool AddDirectoryPolicy(string directoryPath, string id)
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

                using (var con = new SqlConnection(App._myConnection))
                {
                    if (IsDuplicatedPath(con, directoryPath))
                    {
                        return false;
                    }

                    AddPolicy(con, "DIRECTORY", directoryPath, id);
                    return true;
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }
        public bool IsDuplicatedPath(SqlConnection con, string path)
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

                var sql = $@"
                    SELECT COUNT(*)
                    FROM [di_policy]
                    WHERE 1=1
                    AND user_tid = @UserTid
                    AND is_delete = 0
                    AND @Path = path
                ";
                //AND(@Path LIKE path + '%' OR path LIKE @LikePath)

                return 0 < con.Query<int>(sql, new { UserTid = userTid, Path = path, LikePath = $"{path}%" }).SingleOrDefault();
            }
            catch(Exception)
            {
                throw;
            }
        }

        public PolicyIndex GetPolicyIndex(SqlConnection con, SqlTransaction transaction, int policyTid, string path)
        {
            try
            {
                var sql = $@"
                    SELECT [tid]
                        ,[policy_tid]
                        ,[name]
                        ,[path]
                        ,[create_date]
                        ,[last_update]
                    FROM [di_policy_index]
                    WHERE 1=1
                    AND policy_tid = @PolicyTid
                    AND path = @Path
                ";

                PolicyIndex policyIndex = con.Query<PolicyIndex>(sql, new { PolicyTid = policyTid, Path = path }, transaction).SingleOrDefault();

                if (null == policyIndex)
                {
                    policyIndex = CreatePolicyIndex(con, transaction, policyTid, path);
                }

                return policyIndex;
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message, "오류", MessageBoxButton.OK, MessageBoxImage.Error);
                throw;
            }
        }

        private PolicyIndex CreatePolicyIndex(SqlConnection con, SqlTransaction transaction, int policyTid, string path)
        {
            try
            {
                var sql = $@"
                    INSERT INTO [di_policy_index]
                        ([policy_tid]
                        ,[name]
                        ,[path]
                        ,[sys_plant_cd]
                        )
                    OUTPUT
                        INSERTED.tid, INSERTED.policy_tid, INSERTED.name, INSERTED.path, INSERTED.create_date, INSERTED.last_update
                    VALUES
                        (@PolicyTid
                        ,@Name
                        ,@Path
                        ,@sysPlantCd
                        )
                ";

                return con.Query<PolicyIndex>(sql, new { PolicyTid = policyTid, Name = Path.GetFileName(path), Path = path, sysPlantCd = App._sysPlantCd }, transaction).SingleOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 정책 태그 수정
        /// </summary>
        /// <param name="policyTid"></param>
        /// <param name="tag1"></param>
        /// <param name="tag2"></param>
        /// <param name="tag3"></param>
        /// <param name="tag4"></param>
        /// <param name="tag5"></param>
        /// <param name="tag6"></param>
        /// <param name="tag7"></param>
        /// <param name="tag8"></param>
        /// <param name="tag9"></param>
        /// <param name="tag10"></param>
        /// <returns></returns>
        public bool ModifyPolicyTags(int policyTid, string tag1, string tag2, string tag3, string tag4, string tag5, string tag6, string tag7, string tag8, string tag9, string tag10)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    var sql = @"
                        UPDATE [di_policy] 
                        SET custom_tag1 = @Tag1
                            ,custom_tag2 = @Tag2
                            ,custom_tag3 = @Tag3
                            ,custom_tag4 = @Tag4
                            ,custom_tag5 = @Tag5
                            ,custom_tag6 = @Tag6
                            ,custom_tag7 = @Tag7
                            ,custom_tag8 = @Tag8
                            ,custom_tag9 = @Tag9
                            ,custom_tag10 = @Tag10
                        WHERE tid = @PolicyTid
                    ";

                    var data = new
                    {
                        PolicyTid = policyTid,
                        Tag1 = tag1,
                        Tag2 = tag2,
                        Tag3 = tag3,
                        Tag4 = tag4,
                        Tag5 = tag5,
                        Tag6 = tag6,
                        Tag7 = tag7,
                        Tag8 = tag8,
                        Tag9 = tag9,
                        Tag10 = tag10
                    };

                    return 0 < con.Execute(sql, data);
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }

        /// <summary>
        /// 정책 히스토리 조회
        /// </summary>
        /// <param name="policyTid"></param>
        /// <param name="policyIndexTid"></param>
        /// <returns></returns>
        public List<PolicyHistory> GetPolicyHistories(int policyTid, int policyIndexTid)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = @"
                        SELECT [tid]
                            ,[policy_tid]
                            ,[policy_index_tid]
                            ,[create_date]
                            ,[hash]
                            ,[backup_file_id]
                            ,[equip_cd]
                            ,[test_no]
                            ,[work_emp]
                            ,[upload_date]
                        FROM [di_policy_history]
                        WHERE file_ck = '1'
                        AND policy_tid = @PolicyTid
                        AND policy_index_tid = @policyIndexTid
                        ORDER BY upload_date DESC
                    ";

                    return con.Query<PolicyHistory>(sql, new { PolicyTid = policyTid, PolicyIndexTid = policyIndexTid }).ToList();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        /// <summary>
        /// 사용자 디렉터리 조회
        /// </summary>
        /// <returns></returns>
        public string GetUserDirectory()
        {
            try
            {
                var userDirectoryName = $"u{Application.Current.Properties["user_tid"]}";

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

                    var pathId = _fileTableService.GetDirectory(con, userDirectoryName);
                    if(null == pathId)
                    {
                        using (var transaction = con.BeginTransaction())
                        {
                            pathId = _fileTableService.CreateDirectoryToRoot(con, transaction, userDirectoryName);
                            transaction.Commit();
                        }
                    }

                    return pathId;
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        /// <summary>
        /// 정책 디렉터리 조회
        /// </summary>
        /// <param name="policyTid"></param>
        /// <returns></returns>
        public string GetPolicyDirectory(int policyTid)
        {
            try
            {
                var policyDirectoryName = $"p{policyTid}";
                var userDirectoryPathId = GetUserDirectory();

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

                    var pathId = _fileTableService.GetDirectory(con, policyDirectoryName);
                    if (null == pathId)
                    {
                        using (var transaction = con.BeginTransaction())
                        {
                            pathId = _fileTableService.CreateDirectory(con, transaction, policyDirectoryName, userDirectoryPathId);
                            transaction.Commit();
                        }
                    }

                    return pathId;
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        /// <summary>
        /// 파일 정책 히스토리 추가
        /// </summary>
        /// <param name="policy"></param>
        /// <param name="isHashCheck"></param>
        /// <param name="isLimit"></param>
        /// <returns>
        /// -1: 실패
        /// 1: 성공
        /// 2: 중복 업로드 제한
        /// 3: 대용량 업로드 일일 제한
        /// </returns>
        public int AddFilePolicyHistory(Policy policy, bool isHashCheck, bool isLimit)
        {
            try
            {
                if (!File.Exists(policy.Path))
                {
                    log.Error($"[FILE]백업 대상이 경로에 없음 (PolicyTid={policy.Tid})");
                    return -1;
                }

                var policyDirectoryPathId = GetPolicyDirectory(policy.Tid);
                if (null == policyDirectoryPathId)
                {
                    log.Error($"[FILE]정책 디렉터리 생성 실패 (PolicyTid={policy.Tid})");
                    return -1;
                }                

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

                    using (var transaction = con.BeginTransaction())
                    {
                        var policyIndex = GetPolicyIndex(con, transaction, policy.Tid, policy.Path);
                        if (null == policyIndex)
                        {
                            transaction.Rollback();
                            log.Error($"[FILE]인덱스 생성 실패 (PolicyTid={policy.Tid})");
                            return -1;
                        }

                        if (isHashCheck && FileUploadHashChk(con, transaction, policy.Tid, policyIndex.Tid, policyIndex.Path, policyIndex.Name))
                        {
                            transaction.Rollback();
                            log.Info($"[FILE]중복 업로드 제한 (PolicyTid={policy.Tid})");
                            return 2;
                        }

                        if (isLimit && FileUploadLimitChk(con, transaction, policyIndex.Path, policy.Tid, policyIndex.Tid))
                        {
                            transaction.Rollback();
                            log.Info($"[FILE]대용량 업로드 일일 제한 (PolicyTid={policy.Tid})");
                            return 3;
                        }

                        var policyHistoryDirectoryPathId = _fileTableService.CreateDirectory(con, transaction, policyDirectoryPathId);
                        if (null == policyHistoryDirectoryPathId)
                        {
                            transaction.Rollback();
                            log.Error($"[FILE]정책 히스토리 디렉터리 생성 실패 (PolicyTid={policy.Tid}");
                            return -1;
                        }

                        var fileStreamId = _fileTableService.UploadFile(con, transaction, policyIndex.Path, policyHistoryDirectoryPathId);
                        if (Guid.Empty == fileStreamId)
                        {
                            transaction.Rollback();
                            log.Error($"[FILE]백업 대상 업로드에 실패 (PolicyTid={policy.Tid})");
                            return -1;
                        }

                        //var info = new FileInfo(policy.Path);
                        //policy.Create_Date = info.LastWriteTime;
 

                        if (!AddPolicyHistory(con, transaction, policyIndex.Path, policy.Tid, policy.Custom_Tag1, policy.Test_No, policy.Create_Date, policy.Work_Emp, policyIndex.Tid, fileStreamId))
                        {
                            transaction.Rollback();
                            return -1;
                        }

                        if (!AuditPolicyHistory(con, transaction, "I",  App._userCd, "신규 등록", fileStreamId))
                        {
                            transaction.Rollback();
                            return -1;
                        }

                        transaction.Commit();
                        return 1;
                    }
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return -1;
            }
        }

        /// <summary>
        /// 디렉토리 정책 히스토리 추가
        /// </summary>
        /// <param name="policy"></param>
        /// <param name="isRetry"></param>
        /// <param name="isHashCheck"></param>
        /// <param name="isLimit"></param>
        /// <returns></returns>
        public int AddDirectoryPolicyHistory(Policy policy, bool isRetry, bool isHashCheck, bool isLimit)
        {
            try
            {
                var policyDirectoryPathId = GetPolicyDirectory(policy.Tid);
                if (null == policyDirectoryPathId)
                {
                    log.Error($"[DIRECTORY]정책 디렉터리가 생성 실패 (PolicyTid={policy.Tid})");

                    return 0;
                }

                var policyHistoryDirectoryPathId = _fileTableService.CreateDirectory(policyDirectoryPathId);
                if (null == policyHistoryDirectoryPathId)
                {
                    log.Error($"[DIRECTORY]정책 히스토리 디렉터리 생성 실패 (PolicyTid={policy.Tid})");

                    return 0;
                }

                //20230130 
                //var result = BackupFullSearch(policy.Path, policyHistoryDirectoryPathId, policy.Tid, isRetry, isHashCheck, isLimit);
                var result = BackupFullSearch(policy.Path, policyHistoryDirectoryPathId, policy.Tid, policy.Custom_Tag1, policy.Test_No, policy.Create_Date, policy.Work_Emp, isRetry, isHashCheck, isLimit);

                if (result.IsNA())
                {
                    _fileTableService.RemoveDirectory(policyHistoryDirectoryPathId);
                }

                return result.Success;

            }
            catch (Exception e)
            {
                log.Error(e);

                return 0;
            }
        }

        /// <summary>
        /// 모든 파일 검색 후 백업
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="srcPath"></param>
        /// <param name="parentId"></param>
        /// <param name="policyTid"></param>
        /// <param name="policyTest_No"></param>
        /// <param name="policyCreate_Date"></param>
        /// <param name="policyWork_Emp"></param>
        /// <param name="isRetry"></param>
        /// <param name="isHashCheck"></param>
        /// <param name="isLimit"></param>
        /// <returns></returns>
        private DirectoryBackupResult BackupFullSearch(string srcPath, string parentId, int policyTid, string policyEquip_Cd, string policyTest_No, DateTime policyCreate_Date, string policyWork_Emp, bool isRetry, bool isHashCheck, bool isLimit)
        {
            var results = new DirectoryBackupResult();
            try
            {
                var directoryInfo = new FileInfo(srcPath);
                var directoryPath = _fileTableService.CreateDirectory(directoryInfo.Name, parentId);
                if (null == directoryPath)
                {
                    return results;
                }

                var files = Directory.GetFiles(srcPath).ToList();
                foreach (var file in files)
                {
                    Boolean Extension = false;
                    //string str = ".1vmdk, .pdf,.jpg, .vmdk";
                    string[] ExtCk = App._exceptExtension.Split(',');
                    string str1 = Path.GetExtension(file);
                    for (int i = 0; i < ExtCk.Length; i++)//초기화; 조건식; 반복식
                    {
                        if (str1.IndexOf(ExtCk[i].Trim()) >= 0)
                        {
                            Extension = true;
                            break;
                        }
                    }

                    if (Extension == true)
                    {
                        continue;
                    }

                    var fileName = Path.GetFileName(file);
                    var info = new FileInfo(file);
                    
                    policyCreate_Date = info.LastWriteTime;

                    // 데이터베이스에 넣기전 파싱하는 부분 (시험번호, 사용자) 명명규칙

                    string sFiletype = fileName.Substring(fileName.LastIndexOf('.') + 1);

                    if (sFiletype == "pdf")
                    {
                        String[] strfileName = fileName.Split('_');
                        if (strfileName.Length > 2)
                        {
                            policyWork_Emp = strfileName[0].ToString();
                            policyTest_No = strfileName[1].ToString();
                        }
                        else
                        {
                            policyWork_Emp = null;
                            policyTest_No = null;
                        }
                    }
                    else
                    {
                        policyWork_Emp = null;
                        policyTest_No = null;
                    }

                    //성공
                    int result = BackupDirectoryPolicyChild(file, directoryPath, policyTid, policyEquip_Cd, policyTest_No, policyCreate_Date, policyWork_Emp, isHashCheck, isLimit);
                    if (0 < result)
                    {
                        results.Success++;
                        if (1 == result)
                        {
                            _reportService.AddBackupReport(policyTid, fileName, file, true);
                        }

                        //if (2 == result || 3 == result)
                        //{
                        //    results.Duplicate++;
                        //}

                        continue;
                    }

                    //실패
                    _reportService.AddBackupReport(policyTid, fileName, file, false);

                    ////재시도 (재시도 옵션 활성화)
                    //result = BackupDirectoryPolicyChild(file, directoryPath, policyTid, policyEquip_Cd, policyTest_No, policyCreate_Date, policyWork_Emp, isHashCheck, isLimit);
                    //if (isRetry && 0 < result)
                    //{
                    //    results.Success++;
                    //    _reportService.AddRetryReport(policyTid, fileName, file, true);

                    //    if (2 == result || 3 == result)
                    //    {
                    //        results.Duplicate++;
                    //    }

                    //    continue;
                    //}

                    ////재시도 실패 (재시도 옵션 활성화)
                    //if (isRetry)
                    //{
                    //    _reportService.AddRetryReport(policyTid, fileName, file, false);

                    //    continue;
                    //}
                }

                var directories = Directory.GetDirectories(srcPath).ToList();
                foreach (var directory in directories)
                {
                    var tmp = BackupFullSearch(directory, directoryPath, policyTid, policyEquip_Cd, policyTest_No, policyCreate_Date, policyWork_Emp, isRetry, isHashCheck, isLimit);

                    results.Success += tmp.Success;
                    results.Duplicate += tmp.Duplicate;
                }

                return results;
            }
            catch (Exception)
            {
                throw;
                //return results;
            }
        }

        /// <summary>
        /// 디렉터리 정책 파일 백업
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="filePath"></param>
        /// <param name="directoryPath"></param>
        /// <param name="policyTid"></param>
        /// <param name="policyEquip_Cd"></param>
        /// <param name="policyTest_No"></param>
        /// <param name="policyCreate_Date"></param>
        /// <param name="policyWork_Emp"></param>
        /// <param name="isHashCheck"></param>
        /// <param name="isLimit"></param>
        /// <returns>
        /// -1: 실패
        /// 1: 성공
        /// 2: 중복 업로드 제한
        /// 3: 대용량 업로드 일일 제한
        /// </returns>
        private int BackupDirectoryPolicyChild(string filePath, string directoryPath, int policyTid, string policyEquip_Cd, string policyTest_No, DateTime policyCreate_Date, string policyWork_Emp, bool isHashCheck, bool isLimit)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    using (var transaction = con.BeginTransaction())
                    {
                        var policyIndex = GetPolicyIndex(con, transaction, policyTid, filePath);
                        if (null == policyIndex)
                        {
                            transaction.Rollback();
                            log.Error($"[DIRECTORY]인덱스 생성 실패 (PolicyTid={policyTid})");

                            return -1;
                        }

                        if (isHashCheck && FileUploadHashChk(con, transaction, policyTid, policyIndex.Tid, filePath, policyIndex.Name))
                        {
                            transaction.Rollback();
                            log.Info($"[DIRECTORY]중복 업로드 제한 (PolicyTid={policyTid})");

                            return 2;
                        }

                        if (isLimit && FileUploadLimitChk(con, transaction, filePath, policyTid, policyIndex.Tid))
                        {
                            transaction.Rollback();
                            log.Info($"[DIRECTORY]대용량 업로드 일일 제한 (PolicyTid={policyTid})");

                            return 3;
                        }

                        var fileStreamId = _fileTableService.UploadFile(con, transaction, filePath, directoryPath);
                        //var info = new FileInfo(filePath);
                        //policyCreate_Date = info.LastWriteTime;
                        //var result = AddPolicyHistory(con, transaction, filePath, policyTid, policyTest_No, policyCreate_Date, policyWork_Emp, policyIndex.Tid, fileStreamId);
                        if(!AddPolicyHistory(con, transaction, filePath, policyTid, policyEquip_Cd, policyTest_No, policyCreate_Date, policyWork_Emp, policyIndex.Tid, fileStreamId))
                        {
                            transaction.Rollback();
                            return -1;
                        }


                        if (!AuditPolicyHistory(con, transaction, "I", App._userCd, "신규 등록", fileStreamId))
                        {
                            transaction.Rollback();
                            return -1;
                        }

                        transaction.Commit();
                        return 1;
                        //transaction.Rollback();

                        //return -1;
                    }
                }
            }
            catch (Exception e)
            {
                log.Error(e);
                return -1;
            }
        }

        /// <summary>
        /// 정책 히스토리 추가
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="path"></param>
        /// <param name="policyTid"></param>
        /// <param name="policyEquip_Cd"></param>
        /// <param name="policyTest_No"></param>
        /// <param name="policyCreate_Date"></param>
        /// <param name="policyWork_Emp"></param>       
        /// <param name="policyIndexTid"></param>
        /// <param name="guid"></param>
        /// <returns></returns>
        private bool AddPolicyHistory(SqlConnection con, SqlTransaction transaction, string path, int policyTid, string policyEquip_Cd, string policyTest_No, DateTime policyCreate_Date, String policyWork_Emp, int policyIndexTid, Guid guid)
        {
            try
            {
                if (policyEquip_Cd == null)
                {
                    policyEquip_Cd = "";
                }

                var Equip_Cd = "";
                Equip_Cd = App._equipCd;

                if (policyEquip_Cd.Trim().Length > 2)
                {
                    Equip_Cd = policyEquip_Cd.Trim();
                }
                var sql = $@"
                    INSERT INTO [di_policy_history]
                        ([policy_tid]
                        ,[policy_index_tid]
                        ,[file_ck]
                        ,[hash]
                        ,[backup_file_id]
                        ,[equip_cd]
                        ,[test_no]
                        ,[create_date]
                        ,[use_yn]
                        ,[work_emp]
                        ,[upload_date]
                        ,[insert_emp]
                        ,[sys_plant_cd]
                       )
                    VALUES
                        (@PolicyTid
                        ,@PolicyIndexTid
                        ,@file_ck
                        ,@Hash
                        ,@BackupFileId
                        ,@equip_cd
                        ,@test_no
                        ,@create_date
                        ,@use_yn
                        ,@work_emp
                        ,GETDATE()
                        ,@userCd
                        ,@sysPlantCd)
                ";

                var data = new
                {
                    PolicyTid = policyTid,
                    PolicyIndexTid = policyIndexTid,
                    file_ck = "1",
                    Hash = _hashService.FileToMD5(path),
                    BackupFileId = guid,
                    equip_cd = Equip_Cd,
                    test_no = policyTest_No,
                    create_date = policyCreate_Date,
                    use_yn = 'Y',
                    work_emp = policyWork_Emp,
                    userCd = App._userCd,
                    sysPlantCd = App._sysPlantCd
                };

                return 0 < con.Execute(sql, data, transaction);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        /// <summary>
        /// 정책 히스토리 Audit 추가
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="path"></param>
        /// <param name="tid"></param>
        /// <param name="type"></param>
        /// <param name="policyCreate_Date"></param>
        /// <param name="Work_Emp"></param>       
        /// <param name="policyIndexTid"></param>
        /// <param name="guid"></param>
        /// <param name="dbNane"></param>
        /// <param name="Type"></param>
        /// <param name="audit_emp_cd"></param>
        /// <param name="audit_Remark"></param>
        /// <returns></returns>
        public bool AuditPolicyHistory(SqlConnection con, SqlTransaction transaction,  string type, String Work_Emp, String remark, Guid guid)
        {
            try
            {
                var sql = $@"
                    INSERT INTO chang_dbName 
                        (
                        [audit_Gubun]
                        ,[audit_emp_cd]
                        ,[audit_timestamp]
                        ,[audit_remark]
                        ,[tid]
                        ,[policy_tid]
                        ,[policy_index_tid]
                        ,[file_ck]
                        ,[hash]
                        ,[backup_file_id]
                        ,[equip_cd]
                        ,[test_no]
                        ,[create_date]
                        ,[use_yn]
                        ,[work_emp]
                        ,[upload_date]
                        ,[insert_emp]
                        ,[sys_plant_cd]

                       )
                    SELECT
                         @Type 
                        ,@audit_emp_cd 
                        ,GETDATE()
                        ,@audit_Remark
                        ,[tid]
                        ,[policy_tid]
                        ,[policy_index_tid]
                        ,[file_ck]
                        ,[hash]
                        ,[backup_file_id]
                        ,[equip_cd]
                        ,[test_no]
                        ,[create_date]
                        ,[use_yn]
                        ,[work_emp]
                        ,GETDATE()
                        ,[insert_emp]
                        ,[sys_plant_cd]

                    FROM [di_policy_history]
                    WHERE  [backup_file_id] = @backup_file_id
                ";

                var data = new
                {
                    Type = type,
                    audit_emp_cd = Work_Emp,
                    audit_Remark = remark,
                    backup_file_id = guid

                };

                var sql1 = sql.Replace("chang_dbName", App._dbName + "_at.[dbo].[di_policy_history_at] ");
                //var sql1 = sql.Replace("chang_dbName", "DI_HISTORY_TEST_240510_at.[dbo].[di_policy_history_at] ");

                return 0 < con.Execute(sql1, data, transaction);
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 백업
        /// </summary>
        /// <returns></returns>
        public BackupResult Backup()
        {
            var successFile = 0;
            var totalFile = 0;
            var isRetry = (bool)Application.Current.Properties["user_is_retry"];
            var isHashCheck = (bool)Application.Current.Properties["user_is_hash"];
            var isLimit = (bool)Application.Current.Properties["user_is_limit"];

            var userDirectory = GetUserDirectory();
            if (null == userDirectory)
            {
                Application.Current.Properties["backup_status"] = null;
                log.Error("사용자 디렉터리 생성 실패");

                return new BackupResult()
                {
                    Success = successFile,
                    Fail = totalFile - successFile
                };
            }

            Application.Current.Properties["backup_status"] = "OK";

            var policies = GetPolicies();
            if (0 == policies.Count)
            {
                log.Warn("정책 없음");

                return new BackupResult()
                {
                    Success = successFile,
                    Fail = totalFile - successFile
                };
            }

            var filePolicies = policies.Where(policy => "FILE".Equals(policy.Type)).ToList();
            totalFile += filePolicies.Count;

            foreach (var policy in filePolicies)
            {
                try
                {
                    var fileName = Path.GetFileName(policy.Path);
                    var info = new FileInfo(policy.Path);
                    
                    policy.Create_Date = info.LastWriteTime;

                    // 데이터베이스에 넣기전 파싱하는 부분 (시험번호, 사용자) 명명규칙

                    string sFiletype = fileName.Substring(fileName.LastIndexOf('.') + 1);

                    if (sFiletype == "pdf")
                    {
                        String[] strfileName = fileName.Split('_');
                        if (strfileName.Length > 2)
                        {
                            policy.Work_Emp = strfileName[0].ToString();
                            policy.Test_No = strfileName[1].ToString();
                        }
                        else
                        {
                            policy.Work_Emp = null;
                            policy.Test_No = null;
                        }
                    }
                    else
                    {
                        policy.Work_Emp = null;
                        policy.Test_No = null;
                    }
                    //성공
                    int result = AddFilePolicyHistory(policy, isHashCheck, isLimit);
                    if (0 < result)
                    {
                        successFile++;
                        if (1 == result)
                        {
                            _reportService.AddBackupReport(policy.Tid, fileName, policy.Path, true);
                        }

                        continue;
                    }

                    //실패
                    _reportService.AddBackupReport(policy.Tid, fileName, policy.Path, false);

                    ////재시도 (재시도 옵션 활성화)
                    //if (isRetry && 0 < AddFilePolicyHistory(policy, isHashCheck, isLimit))
                    //{
                    //    successFile++;
                    //    _reportService.AddRetryReport(policy.Tid, fileName, policy.Path, true);

                    //    continue;
                    //}

                    ////재시도 실패 (재시도 옵션 활성화)
                    //if (isRetry)
                    //{
                    //    _reportService.AddRetryReport(policy.Tid, fileName, policy.Path, false);

                    //    continue;
                    //}
                }catch(Exception e)
                {
                    log.Error($"File 백업 오류(PolicyTid={policy.Tid})" + ",  " + e.Message.ToString());

                    continue;
                }
            }

            var directoryPolicies = policies.Where(policy => "DIRECTORY".Equals(policy.Type)).ToList();
            foreach (var policy in directoryPolicies)
            {
                if (!Directory.Exists(policy.Path))
                {
                    log.Error($"[DIRECTORY]백업 경로가 없음 (PolicyTid={policy.Tid})");

                    continue;
                }
                int curTotalFile = Directory.GetFileSystemEntries(policy.Path).Count();
                totalFile += curTotalFile;
                successFile += AddDirectoryPolicyHistory(policy, isRetry, isHashCheck, isLimit);
            }

            return new BackupResult()
            {
                Success = successFile,
                Fail = totalFile - successFile
            };
        }

        /// <summary>
        /// 복구
        /// </summary>
        /// <param name="policyTid"></param>
        /// <param name="indexPath"></param>
        /// <param name="compareHash"></param>
        /// <param name="srcStreamId"></param>
        /// <returns></returns>
        public bool Restore(int policyTid, string indexPath, string compareHash, DateTime create_date, Guid srcStreamId, string type, string remark)
        {
            var result = PolicyFileDownload(policyTid, indexPath, compareHash, create_date, srcStreamId, type, remark);
            _reportService.AddRestoreReport(policyTid, Path.GetFileName(indexPath), indexPath, result);

            return result;
        }
        /// <summary>
        /// 정책 파일 다운로드
        /// </summary>
        /// <param name="policyTid"></param>
        /// <param name="indexPath"></param>
        /// <param name="compareHash"></param>
        /// <param name="srcStreamId"></param>
        /// <returns></returns>
        public bool PolicyFileDownload(int policyTid, string indexPath, string compareHash, DateTime create_date, Guid srcStreamId, string type, string remark)
        {
            try
            {

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

                    using (var transaction = con.BeginTransaction())
                    {
                        var tmpDstPath = _fileTableService.DownloadFile(con, transaction, indexPath, srcStreamId);
 
                        //transaction.Commit();

                        if (compareHash.Equals(_hashService.FileToMD5(tmpDstPath)))
                        {
                            //File.SetCreationTime(indexPath, File.GetCreationTime(tmpDstPath));
                            //File.SetCreationTime(tmpDstPath, File.GetCreationTime(tmpDstPath));
                            if (File.Exists(indexPath))
                            {
                                File.Delete(indexPath);
                            }
                            File.Move(tmpDstPath, indexPath);
                            //File.Copy(tmpDstPath, indexPath, true);

                            //File.SetCreationTime(indexPath, File.GetCreationTime(tmpDstPath));
                            //File.SetCreationTime(indexPath, Convert.ToDateTime(File.GetCreationTime(tmpDstPath).ToString()));
                            File.SetLastWriteTime(indexPath, create_date);
                            //File.SetCreationTime(indexPath, Convert.ToDateTime("2023-03-12 03:47:07"));
                            //File.SetLastWriteTime(indexPath, Convert.ToDateTime("2023-03-13 03:47:07"));
                            //if (!AuditPolicyHistory(con, transaction, type, App._userCd, remark, srcStreamId))
                            if (!AuditPolicyHistory(con, transaction, type, App._jobuserCd, remark, srcStreamId))
                            {
                                transaction.Rollback();
                                return false;
                            }
                            transaction.Commit();
                            return true;
                        }

                        File.Delete(tmpDstPath);
                        transaction.Commit();
                        return false;
                    }
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }

        /// <summary>
        /// 모니터링 추가
        /// </summary>
        /// <returns></returns>
        public int AddMonitor()
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

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

                    var sql = @"
                        INSERT INTO [di_policy_monitor]
                            ([user_tid]
                            ,[start_date]
                            ,[end_date]
                            ,[is_manual]
                            ,[is_done]
                            ,[success_count]
                            ,[fail_count]
                            ,[sys_plant_cd]
                            )
                        OUTPUT
                            INSERTED.tid AS tid
                        VALUES
                            (@UserTid
                            ,GETDATE()
                            ,NULL
                            ,0
                            ,0
                            ,0
                            ,0
                            ,@sysPlantCd
                           )
                    ";

                    return con.Query<int>(sql, new { UserTid = userTid, sysPlantCd = App._sysPlantCd}).SingleOrDefault();

                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return 0;
            }
        }

        /// <summary>
        /// 모니터링 변경
        /// </summary>
        /// <param name="policyMonitorTid"></param>
        /// <param name="successCount"></param>
        /// <param name="failCount"></param>
        public void ModifyMonitor(int policyMonitorTid, int successCount, int failCount)
        {
            try
            {
                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    var sql = @"
                        UPDATE [di_policy_monitor]
                        SET [end_date] = GETDATE()
                            ,[is_done] = 1
                            ,[success_count] = @Success
                            ,[fail_count] = @Fail
                        WHERE tid = @PolicyMonitorTid
                    ";

                    con.Query<int>(sql, new { PolicyMonitorTid = policyMonitorTid, Success = successCount, Fail = failCount }).SingleOrDefault();

                }
            }
            catch (Exception e)
            {
                log.Error(e);
            }
        }

        /// <summary>
        /// 마지막 백업 모니터링 조회
        /// </summary>
        /// <returns></returns>
        public PolicyMonitor GetLastMonitoring()
        {
            var userTid = Application.Current.Properties["user_tid"];

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

                    var sql = $@"
                        SELECT TOP 1 [tid]
                            ,[user_tid]
                            ,[start_date]
                            ,[end_date]
                            ,[is_manual]
                            ,[is_done]
                            ,[success_count]
                            ,[fail_count]
                        FROM [di_policy_monitor]
                        WHERE user_tid = @UserTid
                        AND is_done = 1
                        ORDER BY start_date DESC
                    ";

                    return con.Query<PolicyMonitor>(sql, new { UserTid = userTid }).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return null;
            }
        }

        /// <summary>
        /// 파일 용량 체크
        /// </summary>
        /// <param name="path"></param>
        /// <param name="order"></param>
        /// <param name="chkSize"></param>
        /// <returns></returns>
        public bool FileSizeCheck(string path, string order, long chkSize)
        {
            long size = 0;

            FileInfo info = new FileInfo(path);
            long fileSize = info.Length;

            switch (order)
            {
                case "KB":
                    size = (fileSize / 1024);
                    break;
                case "MB":
                    size = (long)(fileSize / Math.Pow(1024, 2));
                    break;
                case "GB":
                    size = (long)(fileSize / Math.Pow(1024, 3));
                    break;
            }
            
            if (size > chkSize)
            {
                return true;
            }
            else
            {
                return false;
            }            
        }

        /// <summary>
        /// 업로드 제한 체크
        /// (20MB 이상일 경우 하루에 한 번만 백업)
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="filePath"></param>
        /// <param name="policyTid"></param>
        /// <param name="policyIndexTid"></param>
        /// <returns></returns>
        public bool FileUploadLimitChk(SqlConnection con, SqlTransaction transaction, string filePath, int policyTid, int policyIndexTid)
        {
            try
            {
                if (!FileSizeCheck(filePath, "MB", 20))
                {
                    return false;
                }

                var sql = @"
                    SELECT COUNT(*)
                    FROM [di_policy_history]
                    WHERE file_ck = '1'
                    AND policy_tid = @PolicyTid
                    AND policy_index_tid = @PolicyIndexTid
                    AND CONVERT(CHAR(10), upload_date, 23) = CONVERT(CHAR(10), GETDATE(), 23)
                ";

                return 0 < con.Query<int>(sql, new { PolicyTid = policyTid, PolicyIndexTid = policyIndexTid }, transaction).SingleOrDefault();
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }

        /// <summary>
        /// 업로드 제한 체크
        /// (마지막 백업한 파일과 HASH 같으면 백업 안함)
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="policyTid"></param>
        /// <param name="policyIndexTid"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public bool FileUploadHashChk(SqlConnection con, SqlTransaction transaction, int policyTid, int policyIndexTid, string filePath, string fileName)
        {
            try
            {
                var willUploadFileHash = _hashService.FileToMD5(filePath);

                var sql = @"
                    SELECT TOP 1 hash
                    FROM [di_policy_history] a
                    INNER JOIN di_policy_index b ON a.policy_tid = b.policy_tid
                    WHERE 1=1
                        --AND policy_tid = @PolicyTid
                        --AND policy_index_tid = @PolicyIndexTid
                        AND hash = @willUploadFileHash
                        AND name = @FileName
                    
                    ORDER BY upload_date DESC
                ";

                //var willUploadFileHash = _hashService.FileToMD5(filePath);

                return willUploadFileHash.Equals(con.Query<string>
                    (sql, new { PolicyTid = policyTid, PolicyIndexTid = policyIndexTid, willUploadFileHash = willUploadFileHash, FileName = fileName }, transaction).SingleOrDefault());
            }
            catch (Exception e)
            {
                log.Error(e);

                return false;
            }
        }

        /// <summary>
        /// 사용자태그 확장 정책 인덱스 조회
        /// </summary>
        /// <returns></returns>
        public List<PolicyIndexDto> GetPolicyIndexesExtendTag()
        {
            try
            {
                var userTid = Application.Current.Properties["user_tid"];

                using (var con = new SqlConnection(App._myConnection))
                {
                    var sql = $@"
                        SELECT T2.[tid]
                            ,T2.[policy_tid]
                            ,T2.[name]
                            ,T2.[path]
                            ,T2.[create_date]
                            ,T2.[last_update]
                            ,T1.[custom_tag1]
                            ,T1.[custom_tag2]
                            ,T1.[custom_tag3]
                            ,T1.[custom_tag4]
                            ,T1.[custom_tag5]
                            ,T1.[custom_tag6]
                            ,T1.[custom_tag7]
                            ,T1.[custom_tag8]
                            ,T1.[custom_tag9]
                            ,T1.[custom_tag10]
                        FROM [di_policy] T1 
                        INNER JOIN [di_policy_index] T2 ON T1.tid = T2.policy_tid
                        WHERE 1=1
                        AND T1.user_tid = @UserTid
                        AND T1.is_delete = 0
                    ";

                    return con.Query<PolicyIndexDto>(sql, new { UserTid = userTid }).ToList();
                }
            }
            catch (Exception e)
            {
                log.Error(e);

                return new List<PolicyIndexDto>();
            }
        }

        public IJobDetail GetPolicyJob()
        {
            return JobBuilder.Create<PolicyJob>()
                    .WithIdentity(_jobKey)
                    .Build();
        }

        public ITrigger GetPolicyTrigger()
        {
            try
            {
                return TriggerBuilder.Create()
                    .WithIdentity(_triggerKey)
                    .StartNow()
                    .WithCronSchedule(GetPolicySchedule())
                    .Build();
            }
            catch (Exception e)
            {
                log.Error(e);
                return null;
            }
        }

        public void ScheduledPolicyJob()
        {
            App.Scheduler.ScheduleJob(GetPolicyJob(), GetPolicyTrigger());
        }

        public void UpdateScheduledPolicyJob()
        {
            App.Scheduler.DeleteJob(_jobKey);
            ScheduledPolicyJob();
        }

        private string GetPolicySchedule()
        {
            var backupType = Application.Current.Properties["user_backup_type"];
            log.Info($"[di_user]스케줄러 주기={backupType}");

            if ("REAL_TIME".Equals(backupType))
            {
                return "0 * * * * ?";
            }

            if ("EVERY_HOUR".Equals(backupType))
            {
                return "0 0 * * * ?";
            }

            if ("EVERY_DAY".Equals(backupType))
            {
                //return "0 30 16 * * ?";
                return _jobTimer;
            }

            return "0 0 0 * * ?";
        }

    }

    [DisallowConcurrentExecution]
    public partial class PolicyJob : IJob
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(PolicyJob));

        private PolicyService _policyService = new PolicyService();

        public void Execute(IJobExecutionContext context)
        {
            var policyMonitorTid = _policyService.AddMonitor();
            var result = _policyService.Backup();
            _policyService.ModifyMonitor(policyMonitorTid, result.Success, result.Fail);
        }
    }

}