HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using ID2.Manager.Data.Models;

using Dapper;

using System.ComponentModel;

using System.Reflection;

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 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(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.*,

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

markus.PROJECT_NO as PROJECT_NO, markus.*

from     dbo.Documents doc

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.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.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 "prodvalidation":

                                                    doc.ProdCapture++;

                                                    break;

                                                case "clientvalidation":

                                                    doc.ClientCapture++;

                                                    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)

        {

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

            }

            if (newDwgNos.Count > 0)

            {

                string dwgNoList = string.Join("','", newDwgNos.Where(x => !string.IsNullOrEmpty(x)).Select(x => x).ToList());

                if (dwgNoList.Length > 0)

                {

                    if (!string.IsNullOrEmpty(projectGroupID))

                    {

                        sbWhere.Append($@" and doc.DocumentNo in ('{dwgNoList}') ");

                    }

                }

            }

            try

            {

                string query = $@"

select   count(*) cnt

from     dbo.Documents doc

where    doc.IsDeleted=0 {sbWhere}";

                if (parameters.Count > 0)

                {

                    dynamicParameters.AddDynamicParams(parameters);

                }

                return ExecuteScalar<int>(query, dynamicParameters);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

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

        {

            bool isSuccess = false;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    string query = string.Empty;

                    if (delDocList.Count > 0)

                    {

                        string docIDList = string.Join("','", delDocList.Where(x => !string.IsNullOrEmpty(x.DocID)).Select(x => x.DocID).ToList());

                        if (docIDList.Length > 0)

                        {

                            query = $@"

update dbo.Documents

set    IsDeleted=1

      ,DeletedDate=getdate(),

      ,DeletedUser=@DeletedUser

where  DocID in ('{docIDList}');";

                            base.Execute(query, new { DeletedUser = userId }, transaction);

                        }

                    }

                    foreach (Documents doc in docList)

                    {

                        if (string.IsNullOrEmpty(doc.DocID))

                        {

                            doc.RegisteredUser = userId;

                            query = $@"

declare @tbdoc table(docid varchar(36))

insert into dbo.Documents

(

     DocID

    ,DocumentNo

    ,RevisonNo

    ,System

    ,SubSystemCode

    ,RefProjectCode

    ,JobLevel

    ,PersonInCharge

    ,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

    ,@RevisonNo

    ,@System

    ,@SubSystemCode

    ,@RefProjectCode

    ,@JobLevel

    ,@PersonInCharge

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

                        }

                        else

                        {

                            doc.ModifiedUser = userId;

                            query = $@"

update dbo.Documents

set    DocumentNo=@DocumentNo

      ,RevisonNo=@RevisonNo

      ,System=@System

      ,SubSystemCode=@SubSystemCode

      ,RefProjectCode=@RefProjectCode

      ,JobLevel=@JobLevel

      ,PersonInCharge=@PersonInCharge

      ,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 (doc.Worker != null)

                        {

                            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;

            }

        }

    }

}