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(List<string> projectGroupIDs)

        {

            var dynamicParameters = new DynamicParameters();

            StringBuilder sbWhere = new StringBuilder();

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

            if (projectGroupIDs.Count > 0)

            {

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

                parameters.Add("RefGroupIDs", projectGroupIDs);

            }

            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(List<string> projectGroupIDs, 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 (projectCode == null)

            {

                sbWhere.Append(" and prj.Code is null ");

                sbTotalWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID in @RefGroupIDs) ");

                parameters.Add("RefGroupIDs", projectGroupIDs);

            }

            else if (projectCode.Equals(string.Empty))

            {

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

                sbTotalWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID in @RefGroupIDs) ");

                parameters.Add("RefGroupIDs", projectGroupIDs);

            }

            else

            {

                sbTotalWhere.Append(" and doc.RefProjectCode in (select Code from dbo.Projects where ParentID in @RefGroupIDs) ");

                parameters.Add("RefGroupIDs", projectGroupIDs);

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

                parameters.Add("RefProjectCode", projectCode);

            }

            if (!string.IsNullOrEmpty(personIncharge))

            {

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

                parameters.Add("PersonInCharge", personIncharge);

                parameters.Add("AVEVAPersonInCharge", 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

            {

                if (parameters.Count > 0)

                {

                    dynamicParameters.AddDynamicParams(parameters);

                }

                string query = $@"

select   doc.*

		,prj.Team

from     dbo.Documents doc

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

where    doc.IsDeleted=0 {sbWhere}

order by doc.Seq;

select files.*

from   dbo.AttachFIles files

           inner join

           (

	           select   doc.DocID

	           from     dbo.Documents doc

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

	           where    doc.IsDeleted=0 {sbWhere}

           ) doc on files.RefID=doc.DocID;

select PROJECT_NO

      ,DOCUMENT_ID

      ,PAGENUMBER

      ,[Text] as [TEXT]

      ,CREATE_DATE

      ,[NAME] as CREATE_USER

from   ViewMarkupData;

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

                var results = QueryMultiple(query, dynamicParameters);

                var docs = results.Read<Documents>();

                var docFiles = results.Read<AttFileInfo>();

                var markupData = results.Read<MarkupText>();

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

                var docFileGroups = docFiles.GroupBy(g => g.RefID)

                                            .Select(x => new

                                            {

                                                RefDocID = x.Key,

                                                List = x.AsEnumerable<AttFileInfo>().ToList(),

                                                ToCaptureCount = x.Where(y => y.Category.Equals("toreview")).Count(),

                                                FrCaptureCount = x.Where(y => y.Category.Equals("frreview")).Count(),

                                                ID2CaptureCount = x.Where(y => y.Category.Equals("id2work")).Count()

                                            });

                var markupGroups = markupData.GroupBy(g => new { g.PROJECT_NO, g.DOCUMENT_ID })

                                             .Select(x => new

                                             {

                                                 x.Key.PROJECT_NO,

                                                 x.Key.DOCUMENT_ID,

                                                 List = x.Where(y => y.TEXT != null && y.CREATE_DATE != null && y.CREATE_USER != null).OrderByDescending(o => o.CREATE_DATE).ToList() //x.AsEnumerable<MarkupText>().ToList()

                                             });

                var ret = (from d in docs

                           join fg in docFileGroups on d.DocID equals fg.RefDocID into dfg

                           from fdata in dfg.DefaultIfEmpty()

                           join mg in markupGroups on new { pj = d.RefProjectCode, dn = d.DocumentNo } equals new { pj = mg.PROJECT_NO, dn = mg.DOCUMENT_ID } into dmg

                           from mdata in dmg.DefaultIfEmpty()

                           select new { d, fdata, mdata }).Select(x =>

                           {

                               x.d.ToCapture = x.fdata?.ToCaptureCount ?? x.d.ToCapture;

                               x.d.FrCapture = x.fdata?.FrCaptureCount ?? x.d.FrCapture;

                               x.d.ID2Capture = x.fdata?.ID2CaptureCount ?? x.d.ID2Capture;

                               x.d.Markups = x.mdata?.List.Count > 0 ? x.mdata.List : x.d.Markups;

                               x.d.AttFiles = x.fdata?.List ?? new List<AttFileInfo>();

                               x.d.ConvertStatus = (x.mdata == null ? x.d.ConvertStatus : 4);

                               return x.d;

                           });

                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 ExistsNotRegID2ProjectDocuments()

//        {

//            try

//            {

//                string query = $@"

//select top 1 *

//from   dbo.Documents doc

//where  doc.IsDeleted=0

//   and doc.RefProjectCode not in (select Code from dbo.Projects prj);";

//                bool isRet = ExecuteScalar<int>(query, null) > 0;

//                return isRet;

//            }

//            catch (Exception ex)

//            {

//                throw ex;

//            }

//        }

        public bool SetDocumentData(List<string> projectGroupIDs, 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

          ,Simularity2=@Simularity2

          ,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

        ,Simularity2

        ,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

        ,@Simularity2

        ,@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

      ,Simularity2=@Simularity2

      ,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;

            }

        }

        /// <summary>

        /// 주어진 이름을 가진 심볼을 조회한다.

        /// </summary>

        /// <param name="id2Info"></param>

        /// <param name="Name"></param>

        /// <returns></returns>

        public IEnumerable<ID2Symbol> FindID2Symbols(string Name)

        {

            string query = $@"select Name, T.[Type], OriginalPoint, ConnectionPoint, Width, Height 

                    from Symbol A join SymbolType T on A.SymbolType_UID=T.UID where A.Name like @Name";

            return Query<ID2Symbol>(query, new { Name = $"%{Name}%" });

        }

        //Transactions

        public int GetTranKey(string userId, string projectIDs)

        {

            int result = -1;

            try

            {

                var dynamicParameters = new DynamicParameters();

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

                {

                    { "UserId", userId },

                    { "ProjectIDs", projectIDs }

                };

                dynamicParameters.AddDynamicParams(parameters);

                using (var transaction = base.BeginTransaction())

                {

                    string query = $@"

--log master 입력

insert into dbo.Transactions (UserId, ProjectID)

values (@UserId, @ProjectIDs);

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(4000)

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  RefProjectCode is not null and ((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; }

        }

    }

}