HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.ComponentModel;

using System.Reflection;

using ID2.Manager.Data.Models;

using Dapper;

using Newtonsoft.Json;

namespace ID2.Manager.Dapper.Repository

{

    public class DocumentRepository : BaseRepository

    {

        public DocumentRepository(string connectionStr) : base(connectionStr) { }

        public IEnumerable<Documents> GetAllDocuments(string projectGroupID)

        {

            var dynamicParameters = new DynamicParameters();

            StringBuilder sbWhere = new StringBuilder();

            var parameters = new Dictionary<string, object>();

            if (!string.IsNullOrEmpty(projectGroupID))

            {

                sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");

                parameters.Add("RefGroupID", projectGroupID);

            }

            try

            {

                string query = $@"

select   doc.*

from     dbo.Documents doc

where    doc.IsDeleted=0 {sbWhere}

order by doc.Seq;";

                if (parameters.Count > 0)

                {

                    dynamicParameters.AddDynamicParams(parameters);

                }

                return Query<Documents>(query, dynamicParameters);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        static string GetDescriptionFromAttribute(MemberInfo member)

        {

            if (member == null) return null;

            var attrib = (DescriptionAttribute)Attribute.GetCustomAttribute(member, typeof(DescriptionAttribute), false);

            return (attrib?.Description ?? member.Name).ToLower();

        }

        public (IEnumerable<Documents> dwgs, int totalCnt) GetDocuments(string projectGroupID, List<string> dateTypes, DateTime? frDate, DateTime? toDate,  string team, string projectCode, string personIncharge, string jobLevel, string documentNo, string isToIsDiscussion, string isFrReviewStatus, string id2Status, string id2Issues, string avevaStatus, string avevaIssues, string prodIsResult, string clientIsResult, string isGateWay, string isRegSystem)

        {

            var map = new CustomPropertyTypeMap(typeof(AttFileInfo), (type, columnName)

     => type.GetProperties().FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName.ToLower()));

            SqlMapper.SetTypeMap(typeof(AttFileInfo), map);

            var dynamicParameters = new DynamicParameters();

            dynamicParameters.Add("Total", dbType: DbType.Int32, direction: ParameterDirection.Output);

            StringBuilder sbWhere = new StringBuilder();

            StringBuilder sbTotalWhere = new StringBuilder();

            var parameters = new Dictionary<string, object>();

            if (dateTypes.Count > 0 && (frDate != null || toDate != null))

            {

                List<string> subWheres = new List<string>();

                foreach (string dateType in dateTypes)

                {

                    StringBuilder sbSubWhere = new StringBuilder();

                    if (frDate != null)

                    {

                        sbSubWhere.Insert(0, $@"convert(datetime, '{Convert.ToDateTime(frDate).AddDays(-1):yyyy-MM-dd 23:59:59.997}') < doc.{dateType}");

                    }

                    if (toDate != null)

                    {

                        if (frDate != null)

                            sbSubWhere.Append($@" and ");

                        sbSubWhere.Append($@"doc.{dateType} < convert(datetime, '{Convert.ToDateTime(toDate).AddDays(1):yyyy-MM-dd 00:00:00.000}')");

                    }

                    if (sbSubWhere.Length > 0)

                    {

                        subWheres.Add("(" + sbSubWhere.ToString() + ")");

                    }

                }

                if (subWheres.Count > 0)

                {

                    sbWhere.Append(" and (" + string.Join(" or ", subWheres) + ") ");

                }

            }

            if (!string.IsNullOrEmpty(team))

            {

                sbWhere.Append(" and prj.Team=@Team ");

                parameters.Add("Team", team);

            }

            if (string.IsNullOrEmpty(projectCode))

            {

                sbWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') ");

                sbTotalWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') ");

                parameters.Add("RefGroupID", projectGroupID);

            }

            else

            {

                sbTotalWhere.Append(" and isnull(doc.RefProjectCode,'') in (select Code from dbo.Projects where ParentID=@RefGroupID union all select '') ");

                parameters.Add("RefGroupID", projectGroupID);

                sbWhere.Append(" and doc.RefProjectCode=@RefProjectCode ");

                parameters.Add("RefProjectCode", projectCode);

            }

            if (!string.IsNullOrEmpty(personIncharge))

            {

                sbWhere.Append(" and doc.PersonInCharge=@PersonInCharge ");

                parameters.Add("PersonInCharge", personIncharge);

            }

            if (!string.IsNullOrEmpty(jobLevel))

            {

                sbWhere.Append(" and doc.JobLevel=@JobLevel ");

                parameters.Add("JobLevel", jobLevel);

            }

            if (!string.IsNullOrEmpty(documentNo))

            {

                sbWhere.Append(" and doc.DocumentNo like '%' + @DocumentNo +'%' ");

                parameters.Add("DocumentNo", documentNo);

            }

            if (!string.IsNullOrEmpty(isToIsDiscussion))

            {

                sbWhere.Append(" and doc.ToIsDiscussion=@ToIsDiscussion ");

                parameters.Add("ToIsDiscussion", isToIsDiscussion);

            }

            if (!string.IsNullOrEmpty(isFrReviewStatus))

            {

                sbWhere.Append(" and doc.FrReviewStatus=@FrReviewStatus ");

                parameters.Add("FrReviewStatus", isFrReviewStatus);

            }

            if (!string.IsNullOrEmpty(id2Status))

            {

                sbWhere.Append(" and doc.ID2Status=@ID2Status ");

                parameters.Add("ID2Status", id2Status);

            }

            if (!string.IsNullOrEmpty(id2Issues))

            {

                sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.ID2Issues)),'') = '' then 'No' else 'Yes' end)=@ID2Issues ");

                parameters.Add("ID2Issues", id2Issues);

            }

            if (!string.IsNullOrEmpty(avevaStatus))

            {

                sbWhere.Append(" and doc.AVEVAStatus=@AVEVAStatus ");

                parameters.Add("AVEVAStatus", avevaStatus);

            }

            if (!string.IsNullOrEmpty(avevaIssues))

            {

                sbWhere.Append(" and (case when isnull(ltrim(rtrim(doc.AVEVAIssues)),'') = '' then 'No' else 'Yes' end)=@AVEVAIssues ");

                parameters.Add("AVEVAIssues", avevaIssues);

            }

            if (!string.IsNullOrEmpty(prodIsResult))

            {

                sbWhere.Append(" and doc.ProdIsResult=@ProdIsResult ");

                parameters.Add("ProdIsResult", prodIsResult);

            }

            if (!string.IsNullOrEmpty(clientIsResult))

            {

                sbWhere.Append(" and doc.ClientIsResult=@ClientIsResult ");

                parameters.Add("ClientIsResult", clientIsResult);

            }

            if (!string.IsNullOrEmpty(isGateWay))

            {

                sbWhere.Append(" and doc.DTIsGateWay=@DTIsGateWay ");

                parameters.Add("DTIsGateWay", isGateWay);

            }

            if (!string.IsNullOrEmpty(isRegSystem))

            {

                sbWhere.Append(" and doc.DTIsRegSystem=@DTIsRegSystem ");

                parameters.Add("DTIsRegSystem", isRegSystem);

            }

            try

            {

                //files.[FileID],files.[FileID] as AttFileID, files.RefID, files.Category, files.FileType, files.FileName, files.FilePath, files.FileExtension, files.FileData, files.Remark, files.CreatedDate, files.Creator,

                string query = $@"

select  doc.*,

prj.Team,

files.[FileID] as FileID, files.*,

markus.PROJECT_NO as PROJECT_NO, markus.*

from     dbo.Documents doc

left outer join dbo.Projects prj on doc.RefProjectCode=prj.Code

LEFT OUTER JOIN AttachFIles files ON doc.DocID = fIles.RefID 

LEFT OUTER JOIN 

        (SELECT [PROJECT_NO] as PROJECT_NO

                ,[DOCUMENT_ID] as DocumentNo

                ,[PAGENUMBER]

                ,[Text] as TEXT

                ,[CREATE_DATE] as CREATE_DATE

                ,[NAME] as CREATE_USER

            FROM ViewMarkupData) markus 

ON doc.RefProjectCode = markus.Project_NO AND doc.DocumentNo = markus.DocumentNo

where    doc.IsDeleted=0 {sbWhere}

order by doc.Seq

select @Total=count(*) from dbo.Documents doc where doc.IsDeleted=0 {sbTotalWhere}

select @Total;";

                if (parameters.Count > 0)

                {

                    dynamicParameters.AddDynamicParams(parameters);

                }

                var docDictionary = new Dictionary<string, Documents>();

                var ret = MultiQuery<Documents, AttFileInfo, MarkupText, Documents>(query,

                                (document, attfile, markusText) =>

                                {

                                    Documents doc;

                                    if (!docDictionary.TryGetValue(document.DocID, out doc))

                                    {

                                        doc = document;

                                        docDictionary.Add(doc.DocID, doc);

                                    }

                                    if (markusText != null)

                                    {

                                        doc.ConvertStatus = 4;

                                        if (markusText.TEXT != null && markusText.CREATE_DATE != null && markusText.CREATE_USER != null)

                                        {

                                            doc.Markups = doc.Markups ?? new List<MarkupText>();

                                            if (!doc.Markups.Any(x => x.Equals(markusText)))

                                            {

                                                doc.Markups.Add(markusText);

                                            }

                                        }

                                    }

                                    if (attfile != null)

                                    {

                                        doc.AttFiles = doc.AttFiles ?? new List<AttFileInfo>();

                                        System.Diagnostics.Debug.WriteLine(attfile.FileName);

                                        if (!doc.AttFiles.Any(x => x.FileID == attfile.FileID))

                                        {

                                            switch (attfile.Category)

                                            {

                                                case "toreview":

                                                    doc.ToCapture++;

                                                    break;

                                                case "frreview":

                                                    doc.FrCapture++;

                                                    break;

                                                case "id2work":

                                                    doc.ID2Capture++;

                                                    break;

                                            }

                                            doc.AttFiles.Add(attfile);

                                        }

                                    }

                                    return doc;

                                }, dynamicParameters, splitOn: "DocID,FileID,PROJECT_NO").Distinct();

                int totalCount = dynamicParameters.Get<int>("Total");

                return (ret, totalCount);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public int ExistsDocument(string projectGroupID, List<string> newDwgNos)

        {

            int existCount = 0;

            int paramMaxCount = 2000;

            int execCount = (newDwgNos.Count / paramMaxCount) + 1;

            for (int i = 0; i < execCount; i++)

            {

                var dynamicParameters = new DynamicParameters();

                StringBuilder sbWhere = new StringBuilder();

                var parameters = new Dictionary<string, object>();

                if (!string.IsNullOrEmpty(projectGroupID))

                {

                    sbWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID=@RefGroupID) ");

                    parameters.Add("RefGroupID", projectGroupID);

                }

                var limitDwgNos = newDwgNos.Skip(paramMaxCount * i).Take(paramMaxCount).ToList();

                sbWhere.Append($@" and doc.DocumentNo in @limitDwgNos ");

                parameters.Add("limitDwgNos", limitDwgNos);

                try

                {

                    string query = $@"

select   count(*) cnt

from     dbo.Documents doc

where    doc.IsDeleted=0 {sbWhere}";

                    if (parameters.Count > 0)

                    {

                        dynamicParameters.AddDynamicParams(parameters);

                    }

                    existCount += ExecuteScalar<int>(query, dynamicParameters);

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

            return existCount;

        }

        public bool SetDocumentData(string projectGroupID, List<Documents> docList, List<string> delDocList, string userId)

        {

            bool isSuccess = false;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    string query = string.Empty;

                    if (delDocList.Count > 0)

                    {

                        int paramMaxCount = 2000;

                        int execCount = (delDocList.Count / paramMaxCount) + 1;

                        for (int i = 0; i < execCount; i++)

                        {

                            var dynamicParameters = new DynamicParameters();

                            StringBuilder sbWhere = new StringBuilder();

                            var parameters = new Dictionary<string, object>();

                            parameters.Add("DeletedUser", userId);

                            var limitDwgNos = delDocList.Skip(paramMaxCount * i).Take(paramMaxCount).ToList();

                            sbWhere.Append($@" and DocID in @limitDwgNos ");

                            parameters.Add("limitDwgNos", limitDwgNos);

                            query = $@"

update dbo.Documents

set    IsDeleted=1

      ,DeletedDate=getdate()

      ,DeletedUser=@DeletedUser

where  IsDeleted=0 {sbWhere}";

                            if (parameters.Count > 0)

                            {

                                dynamicParameters.AddDynamicParams(parameters);

                            }

                            base.Execute(query, dynamicParameters, transaction);

                        }

                    }

                    foreach (Documents doc in docList)

                    {

                        if (string.IsNullOrEmpty(doc.DocID))

                        {

                            doc.RegisteredUser = userId;

                            doc.ModifiedUser = userId;

                            query = $@"

if exists(select * from dbo.Documents where RefProjectCode=@RefProjectCode and DocumentNo=@DocumentNo and IsDeleted=0)

begin

    --update

    update dbo.Documents

    set    RevisonNo=case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end

          ,System=@System

          ,SubSystemCode=@SubSystemCode

          ,JobLevel=@JobLevel

          ,Simularity=@Simularity

          ,PersonInCharge=@PersonInCharge

          ,Worker=@Worker

          ,ModifiedDate=getdate()

          ,ModifiedUser=@ModifiedUser

          ,ToIsDiscussion=@ToIsDiscussion

          ,ToRemarks=@ToRemarks

          ,ToCreator=@ToCreator

          ,FrReviewStatus=@FrReviewStatus

          ,FrRemarks=@FrRemarks

          ,FrCreator=@FrCreator

          ,ID2StartDate=@ID2StartDate

          ,ID2EndDate=@ID2EndDate

          ,ID2Status=@ID2Status

          ,ID2Issues=@ID2Issues

          ,ReplyModifications=@ReplyModifications

          ,ReplyRequester=@ReplyRequester

          ,IsConvert=@IsConvert

          ,AVEVAPersonInCharge=@AVEVAPersonInCharge

          ,AVEVAWorker=@AVEVAWorker

          ,AVEVAConvertDate=@AVEVAConvertDate

          ,AVEVAReviewDate=@AVEVAReviewDate

          ,AVEVAWorkDate=@AVEVAWorkDate

          ,AVEVAStatus=@AVEVAStatus

          ,AVEVAIssues=@AVEVAIssues

          ,ProdReviewer=@ProdReviewer

          ,ProdIsResult=@ProdIsResult

          ,ProdRemarks=@ProdRemarks

          ,ClientReviewer=@ClientReviewer

          ,ClientIsResult=@ClientIsResult

          ,ClientRemarks=@ClientRemarks

          ,DTIsGateWay=@DTIsGateWay

          ,DTIsImport=@DTIsImport

          ,DTIsRegSystem=@DTIsRegSystem

          ,DTRemarks=@DTRemarks

    where  RefProjectCode=@RefProjectCode and DocumentNo=@DocumentNo and IsDeleted=0

    if @@rowcount > 0

    begin

        select @DocID

    end

    else

    begin

        select ''

    end

end

else

begin

    --insert

    declare @tbdoc table(docid varchar(36))

    insert into dbo.Documents

    (

         DocID

        ,DocumentNo

        ,RevisonNo

        ,System

        ,SubSystemCode

        ,RefProjectCode

        ,JobLevel

        ,Simularity

        ,PersonInCharge

        ,Worker

        ,RegisteredDate

        ,RegisteredUser

        ,ToIsDiscussion

        ,ToRemarks

        ,ToCreator

        ,FrReviewStatus

        ,FrRemarks

        ,FrCreator

        ,ID2StartDate

        ,ID2EndDate

        ,ID2Status

        ,ID2Issues

        ,ReplyModifications

        ,ReplyRequester

        ,IsConvert

        ,AVEVAPersonInCharge

        ,AVEVAWorker

        ,AVEVAConvertDate

        ,AVEVAReviewDate

        ,AVEVAWorkDate

        ,AVEVAStatus

        ,AVEVAIssues

        ,ProdReviewer

        ,ProdIsResult

        ,ProdRemarks

        ,ClientReviewer

        ,ClientIsResult

        ,ClientRemarks

        ,DTIsGateWay

        ,DTIsImport

        ,DTIsRegSystem

        ,DTRemarks

    )

    output inserted.DocID into @tbdoc

    values 

    (

         lower(newid())

        ,@DocumentNo

        ,case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end

        ,@System

        ,@SubSystemCode

        ,@RefProjectCode

        ,@JobLevel

        ,@Simularity

        ,@PersonInCharge

        ,@Worker

        ,getdate()

        ,@RegisteredUser

        ,@ToIsDiscussion

        ,@ToRemarks

        ,@ToCreator

        ,@FrReviewStatus

        ,@FrRemarks

        ,@FrCreator

        ,@ID2StartDate

        ,@ID2EndDate

        ,@ID2Status

        ,@ID2Issues

        ,@ReplyModifications

        ,@ReplyRequester

        ,@IsConvert

        ,@AVEVAPersonInCharge

        ,@AVEVAWorker

        ,@AVEVAConvertDate

        ,@AVEVAReviewDate

        ,@AVEVAWorkDate

        ,@AVEVAStatus

        ,@AVEVAIssues

        ,@ProdReviewer

        ,@ProdIsResult

        ,@ProdRemarks

        ,@ClientReviewer

        ,@ClientIsResult

        ,@ClientRemarks

        ,@DTIsGateWay

        ,@DTIsImport

        ,@DTIsRegSystem

        ,@DTRemarks

    )

    if @@rowcount > 0

    begin

        select docid from @tbdoc

    end

    else

    begin

        select ''

    end

end";

                        }

                        else

                        {

                            doc.ModifiedUser = userId;

                            query = $@"

update dbo.Documents

set    RevisonNo=case when isnull(@RevisonNo,'')='' then '0' else @RevisonNo end

      ,System=@System

      ,SubSystemCode=@SubSystemCode

      ,RefProjectCode=@RefProjectCode

      ,JobLevel=@JobLevel

      ,Simularity=@Simularity

      ,PersonInCharge=@PersonInCharge

      ,Worker=@Worker

      ,ModifiedDate=getdate()

      ,ModifiedUser=@ModifiedUser

      ,ToIsDiscussion=@ToIsDiscussion

      ,ToRemarks=@ToRemarks

      ,ToCreator=@ToCreator

      ,FrReviewStatus=@FrReviewStatus

      ,FrRemarks=@FrRemarks

      ,FrCreator=@FrCreator

      ,ID2StartDate=@ID2StartDate

      ,ID2EndDate=@ID2EndDate

      ,ID2Status=@ID2Status

      ,ID2Issues=@ID2Issues

      ,ReplyModifications=@ReplyModifications

      ,ReplyRequester=@ReplyRequester

      ,IsConvert=@IsConvert

      ,AVEVAPersonInCharge=@AVEVAPersonInCharge

      ,AVEVAWorker=@AVEVAWorker

      ,AVEVAConvertDate=@AVEVAConvertDate

      ,AVEVAReviewDate=@AVEVAReviewDate

      ,AVEVAWorkDate=@AVEVAWorkDate

      ,AVEVAStatus=@AVEVAStatus

      ,AVEVAIssues=@AVEVAIssues

      ,ProdReviewer=@ProdReviewer

      ,ProdIsResult=@ProdIsResult

      ,ProdRemarks=@ProdRemarks

      ,ClientReviewer=@ClientReviewer

      ,ClientIsResult=@ClientIsResult

      ,ClientRemarks=@ClientRemarks

      ,DTIsGateWay=@DTIsGateWay

      ,DTIsImport=@DTIsImport

      ,DTIsRegSystem=@DTIsRegSystem

      ,DTRemarks=@DTRemarks

where  DocID=@DocID

if @@rowcount > 0

begin

    select @DocID

end

else

begin

    select ''

end;";

                        }

                        string refID = base.QueryFirstOrDefault<string>(query, doc, transaction);

                        if (doc.AttFiles != null && doc.AttFiles.Count > 0)

                        {

                            string attDelIDList = string.Join("','", doc.AttFiles.Where(x => !string.IsNullOrEmpty(x.FileID)).Select(x => x.FileID).ToList());

                            if (!string.IsNullOrEmpty(refID) && attDelIDList.Length > 0)

                            {

                                query = $@"

delete from dbo.AttachFIles

where  RefID=@RefID and FileID in ('{attDelIDList}');";

                                base.Execute(query, new { RefID = refID }, transaction);

                            }

                            foreach (AttFileInfo attFile in doc.AttFiles)

                            {

                                if (string.IsNullOrEmpty(attFile.RefID))

                                {

                                    attFile.RefID = refID;

                                    attFile.Creator = userId;

                                    query = $@"

insert into dbo.AttachFIles (FileID,RefID,Category,FileType,FileName,FilePath,FileExtension,FileData,Creator)

values

(

    lower(newid())

   ,@RefID

   ,@Category

   ,@FileType

   ,@FileName

   ,@FilePath

   ,@FileExtension

   ,@FileData

   ,@Creator

)";

                                    base.Execute(query, attFile, transaction);

                                }

                            }

                        }

                    }

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

        public Documents SetDocumentDataField(Documents doc, string userId)

        {

            Documents resultData = null;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    string query = string.Empty;

                    if (!string.IsNullOrEmpty(doc.DocID))

                    {

                        StringBuilder sbSet = new StringBuilder();

                        var parameters = new Dictionary<string, object>();

                        #region Update 할 목록

                        if (doc.ID2StartDate != null)

                        {

                            sbSet.Append(" ,ID2StartDate=(case when ID2StartDate is null then @DateTimeNow else ID2StartDate end) ");

                            parameters.Add("ID2StartDate", doc.ID2StartDate);

                        }

                        if (!string.IsNullOrEmpty(doc.Worker))

                        {

                            sbSet.Append(" ,Worker=@Worker ");

                            parameters.Add("Worker", doc.Worker);

                        }

                        #endregion

                        if (parameters.Count > 0)

                        {

                            sbSet.Append(" ,ModifiedUser=@ModifiedUser ");

                            parameters.Add("ModifiedUser", userId);

                            parameters.Add("DocID", doc.DocID);

                            query = $@"

declare @DateTimeNow datetime

set @DateTimeNow = getdate()

update dbo.Documents

set    ModifiedDate=@DateTimeNow {sbSet}

where  [DocID]=@DocID

if @@rowcount > 0

begin

    select * from dbo.Documents where DocID=@DocID

end

else

begin

    select * from dbo.Documents where 1=2

end;";

                            resultData = base.QueryFirstOrDefault<Documents>(query, parameters, transaction);

                        }

                    }

                    transaction.Commit();

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return resultData;

        }

        public bool SetDocumentDatasField(List<Documents> docs, string userId)

        {

            bool isSuccess = false;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    foreach (Documents doc in docs)

                    {

                        string query = string.Empty;

                        if (!string.IsNullOrEmpty(doc.DocID))

                        {

                            StringBuilder sbSet = new StringBuilder();

                            var parameters = new Dictionary<string, object>();

                            #region Update 할 목록

                            if (doc.ID2EndDate != null)

                            {

                                sbSet.Append(" ,ID2EndDate=@ID2EndDate ");

                                parameters.Add("ID2EndDate", doc.ID2EndDate);

                            }

                            #endregion

                            if (parameters.Count > 0)

                            {

                                sbSet.Append(" ,ModifiedUser=@ModifiedUser ");

                                parameters.Add("ModifiedUser", userId);

                                parameters.Add("DocID", doc.DocID);

                                query = $@"

update dbo.Documents

set    ModifiedDate=getdate() {sbSet}

where  [DocID]=@DocID;";

                                base.Execute(query, parameters, transaction);

                            }

                        }

                    }

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

        //ID2

        public IEnumerable<ID2Drawings> GetID2DrawingsByProject(ID2ProjectInfo id2Info)

        {

            try

            {

                string query = $@"

select @Name PROJECTNAME

      ,dw.[UID], dw.[NAME], left(dw.[NAME], len(dw.[NAME]) - dw.lastidx) DOCNAME

      ,case when rtrim(ltrim(isnull(convert(varchar, dw.[DATETIME]),''))) = '' then null else convert(datetime, convert(varchar, dw.[DATETIME])) end [DATETIME]

      ,dw.OCCUPIED, dw.[Image]

from

(

    select [UID], [NAME], [DATETIME], OCCUPIED, [Image], charindex('.', reverse([NAME])) lastidx

    from   dbo.Drawings

) dw;";

                return Query<ID2Drawings>(query, id2Info);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        //Transactions

        public int GetTranKey(string userId, string projectID)

        {

            int result = -1;

            try

            {

                var dynamicParameters = new DynamicParameters();

                var parameters = new Dictionary<string, object>()

                {

                    { "UserId", userId },

                    { "ProjectID", projectID }

                };

                dynamicParameters.AddDynamicParams(parameters);

                using (var transaction = base.BeginTransaction())

                {

                    string query = $@"

--log master 입력

insert into dbo.Transactions (UserId, ProjectID)

values (@UserId, @ProjectID);

select scope_identity();";

                    result = base.ExecuteScalar<int>(query, dynamicParameters, transaction);

                    transaction.Commit();

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return result;

        }

        public bool SetTran(int seq, bool isMgt, bool isStart, int itemCount, int markusItemCount)

        {

            bool result = false;

            try

            {

                var dynamicParameters = new DynamicParameters();                

                var parameters = new Dictionary<string, object>()

                {

                    { "Seq", seq },

                    { "ItemCount", itemCount },

                    { "MarkusItemCount", markusItemCount }

                };

                dynamicParameters.AddDynamicParams(parameters);

                StringBuilder sbWhere = new StringBuilder();

                if (isMgt)

                {

                    if (isStart)

                    {

                        sbWhere.Append("StartDate=getdate(), ItemCount=@ItemCount");

                    }

                    else

                    {

                        sbWhere.Append("EndDate=getdate()");

                    }

                }

                else

                {

                    if (isStart)

                    {

                        sbWhere.Append("MarkusStartDate=getdate(), MarkusItemCount=@MarkusItemCount");

                    }

                    else

                    {

                        sbWhere.Append("MarkusEndDate=getdate()");

                    }

                }

                using (var transaction = base.BeginTransaction())

                {

                    string query = $@"

update dbo.Transactions

set    {sbWhere}

where Seq=@Seq;";

                    base.Execute(query, dynamicParameters, transaction);

                    transaction.Commit();

                    result = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return result;

        }

        public string GetTranData(int seq)

        {

            try

            {

                var dynamicParameters = new DynamicParameters();

                var parameters = new Dictionary<string, object>()

                {

                    { "Seq", seq }

                };

                dynamicParameters.AddDynamicParams(parameters);

                string query = $@"

select *

from   dbo.Transactions

where  Seq=@Seq";

                IEnumerable<dynamic> rows = base.Query<dynamic>(query, dynamicParameters);

                return this.ToSerializeData(rows);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public string ToSerializeData(IEnumerable<dynamic> items)

        {

            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();

            if (items == null) return string.Empty;

            var datas = items.ToArray();

            if (datas.Length == 0) return string.Empty;

            foreach (var data in datas)

            {

                var dicItem = new Dictionary<string, object>();

                foreach (var pair in ((IDictionary<string, object>)data))

                {

                    dicItem.Add(pair.Key, pair.Value);

                }

                list.Add(dicItem);

            }

            return JsonConvert.SerializeObject(list);

        }

        public IEnumerable<Documents> GetTrDocuments(int seq)

        {

            var dynamicParameters = new DynamicParameters();

            StringBuilder sbWhere = new StringBuilder();

            var parameters = new Dictionary<string, object>()

            {

                { "Seq", seq }

            };

            dynamicParameters.AddDynamicParams(parameters);

            try

            {

                string query = $@"

declare @CreatedDate datetime

declare @EndDate datetime

declare @ProjectGroupID varchar(36)

select top 1 @CreatedDate=CreatedDate, @EndDate=EndDate, @ProjectGroupID=ProjectID

from   dbo.Transactions

where  Seq=@Seq

--select RefProjectCode, DocumentNo

--from   dbo.Documents

--where  RefProjectCode in (select Code from dbo.Projects where ParentID=@ProjectGroupID)

--   and RegisteredDate between @CreatedDate and @EndDate

select RefProjectCode, DocumentNo

from   dbo.Documents

where  (RegisteredDate between @CreatedDate and @EndDate) or (ModifiedDate between @CreatedDate and @EndDate)";

                if (parameters.Count > 0)

                {

                    dynamicParameters.AddDynamicParams(parameters);

                }

                return Query<Documents>(query, dynamicParameters);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public class MarkusConvert

        {

            public string PROJECT_NO { get; set; }

            public string DOCUMENT_ID { get; set; }

            public int STATUS { get; set; }

        }

    }

}