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

select   doc.*

from     dbo.Documents doc

where    doc.IsDeleted=0

order by doc.Seq;";

            return Query<Documents>(query);

        }

        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)

        {

            var dynamicParameters = new DynamicParameters();

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

            StringBuilder sbWhere = new StringBuilder();

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

            if (!string.IsNullOrEmpty(projectCode))

            {

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

            }

            string query = $@"

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

  (

    select markus.*

	from [markus_SEC].[dbo].[ViewMarkupData] markus where doc.DocumentNo = markus.DOCUMENT_ID

	FOR JSON PATH

  ) as MarkupText

from     dbo.Documents doc

where    doc.IsDeleted=0 {sbWhere}

order by doc.Seq

select @Total=count(*) from dbo.Documents

select @Total;";

            if (parameters.Count > 0)

            {

                dynamicParameters.AddDynamicParams(parameters);

            }

            var ret = Query<Documents>(query, dynamicParameters);

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

            return (ret, totalCount);

        }

        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)

        {

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

        }

    }

}