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;

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;

            }

        }

        public (IEnumerable<Documents> dwgs, int totalCnt) GetDocuments(string projectCode, string personIncharge, string jobLevel, string documentNo, string isToIsDiscussion, string isFrReviewStatus, string isID2Work, string id2Status, string avevaStatus, string prodIsResult, string clientIsResult, string isGateWay, string isRegSystem)

        {

            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 (!string.IsNullOrEmpty(projectCode))

            {

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

                sbTotalWhere.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(isID2Work))

            {

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

                parameters.Add("IsID2Work", isID2Work);

            }

            if (!string.IsNullOrEmpty(id2Status))

            {

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

                parameters.Add("ID2Status", id2Status);

            }

            if (!string.IsNullOrEmpty(avevaStatus))

            {

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

                parameters.Add("AVEVAStatus", avevaStatus);

            }

            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

            {

                string query = $@"

                            select   doc.*, datediff(SECOND, doc.ID2StartDate, doc.ID2EndDate) as ID2JobTime,

                            markus.*

                            from     dbo.Documents doc

                            LEFT OUTER JOIN 

                                    (SELECT [PROJECT_NO] as PROJECT_NO

                                          ,[DOCUMENT_ID] as DocumentNo

                                          ,[MARKUP_DATA_ID]

                                          ,[PAGENUMBER]

                                          ,[Text] as TEXT

                                          ,[CREATE_DATE] as CREATE_DATE

                                          ,[NAME] as CREATE_USER

                                      FROM [markus_SEC].dbo.[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, MarkupText, Documents>(query,

                                (document, markusText) =>

                                {

                                    Documents doc;

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

                                    {

                                        doc = document;

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

                                        docDictionary.Add(doc.DocumentNo, doc);

                                    }

                                    doc.Markups.Add(markusText);

                                    return doc;

                                }, dynamicParameters, splitOn: "DocumentNo").Distinct();

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

                return (ret, totalCount);

            }

            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 = $@"

insert into dbo.Documents

(

     DocID

    ,DocumentNo

    ,RevisonNo

    ,RefProjectCode

    ,DocFilePath

    ,DocFileName

    ,JobLevel

    ,IsTypical

    ,PersonInCharge

    ,RegisteredDate

    ,RegisteredUser

    ,ToIsDiscussion

    ,ToRemarks

    ,ToCreator

    ,ToCapture

    ,ToIsMarkup

    ,FrReviewStatus

    ,FrRemarks

    ,FrCreator

    ,FrCapture

    ,FrIsMarkup

    ,IsID2Work

    ,ID2Connection

    ,ID2StartDate

    ,ID2EndDate

    ,ID2Status

    ,ID2Issues

    ,AVEVAConnection

    ,AVEVAConvertDate

    ,AVEVAReviewDate

    ,AVEVAStatus

    ,AVEVAIssues

    ,ReviewFilePath

    ,ReviewFileName

    ,ProdReviewer

    ,ProdIsResult

    ,ProdRemarks

    ,ClientReviewer

    ,ClientIsResult

    ,ClientRemarks

    ,DTIsGateWay

    ,DTIsImport

    ,DTIsRegSystem

    ,DTRemarks

)

values 

(

     lower(newid())

    ,@DocumentNo

    ,@RevisonNo

    ,@RefProjectCode

    ,@DocFilePath

    ,@DocFileName

    ,@JobLevel

    ,@IsTypical

    ,@PersonInCharge

    ,getdate()

    ,@RegisteredUser

    ,@ToIsDiscussion

    ,@ToRemarks

    ,@ToCreator

    ,@ToCapture

    ,@ToIsMarkup

    ,@FrReviewStatus

    ,@FrRemarks

    ,@FrCreator

    ,@FrCapture

    ,@FrIsMarkup

    ,@IsID2Work

    ,@ID2Connection

    ,@ID2StartDate

    ,@ID2EndDate

    ,@ID2Status

    ,@ID2Issues

    ,@AVEVAConnection

    ,@AVEVAConvertDate

    ,@AVEVAReviewDate

    ,@AVEVAStatus

    ,@AVEVAIssues

    ,@ReviewFilePath

    ,@ReviewFileName

    ,@ProdReviewer

    ,@ProdIsResult

    ,@ProdRemarks

    ,@ClientReviewer

    ,@ClientIsResult

    ,@ClientRemarks

    ,@DTIsGateWay

    ,@DTIsImport

    ,@DTIsRegSystem

    ,@DTRemarks

);";

                        }

                        else

                        {

                            doc.ModifiedUser = userId;

                            query = $@"

update dbo.Documents

set    DocumentNo=@DocumentNo

      ,RevisonNo=@RevisonNo

      ,RefProjectCode=@RefProjectCode

      ,DocFilePath=@DocFilePath

      ,DocFileName=@DocFileName

      ,JobLevel=@JobLevel

      ,IsTypical=@IsTypical

      ,PersonInCharge=@PersonInCharge

      ,ModifiedDate=getdate()

      ,ModifiedUser=@ModifiedUser

      ,ToIsDiscussion=@ToIsDiscussion

      ,ToRemarks=@ToRemarks

      ,ToCreator=@ToCreator

      ,ToCapture=@ToCapture

      ,ToIsMarkup=@ToIsMarkup

      ,FrReviewStatus=@FrReviewStatus

      ,FrRemarks=@FrRemarks

      ,FrCreator=@FrCreator

      ,FrCapture=@FrCapture

      ,FrIsMarkup=@FrIsMarkup

      ,IsID2Work=@IsID2Work

      ,ID2Connection=@ID2Connection

      ,ID2StartDate=@ID2StartDate

      ,ID2EndDate=@ID2EndDate

      ,ID2Status=@ID2Status

      ,ID2Issues=@ID2Issues

      ,AVEVAConnection=@AVEVAConnection

      ,AVEVAConvertDate=@AVEVAConvertDate

      ,AVEVAReviewDate=@AVEVAReviewDate

      ,AVEVAStatus=@AVEVAStatus

      ,AVEVAIssues=@AVEVAIssues

      ,ReviewFilePath=@ReviewFilePath

      ,ReviewFileName=@ReviewFileName

      ,ProdReviewer=@ProdReviewer

      ,ProdIsResult=@ProdIsResult

      ,ProdRemarks=@ProdRemarks

      ,ClientReviewer=@ClientReviewer

      ,ClientIsResult=@ClientIsResult

      ,ClientRemarks=@ClientRemarks

      ,DTIsGateWay=@DTIsGateWay

      ,DTIsImport=@DTIsImport

      ,DTIsRegSystem=@DTIsRegSystem

      ,DTRemarks=@DTRemarks

where  DocID=@DocID;";

                        }

                        base.Execute(query, doc, 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 *, datediff(SECOND, ID2StartDate, ID2EndDate) as ID2JobTime from dbo.Documents where DocID=@DocID

end

else

begin

    select *, 0 as ID2JobTime 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;

            }

        }

    }

}