HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

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

  (

    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

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: System.Data.DbType.Int32, direction: System.Data.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.*,

  (

    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)

        {

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

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

                            base.Execute(query, transaction);

                        }

                    }

                    foreach (Documents doc in docList)

                    {

                        if (string.IsNullOrEmpty(doc.DocID))

                        {

                            query = $@"

insert into dbo.Documents

(

     [DocID]

    ,[DocumentNo]

    ,[RevisonNo]

    ,[RefProjectCode]

    ,[DocFilePath]

    ,[DocFileName]

    ,[JobLevel]

    ,[IsTypical]

    ,[PersonInCharge]

    ,[ToIsDiscussion]

    ,[ToRemarks]

    ,[ToCreator]

    ,[ToCapture]

    ,[ToIsMarkup]

    ,[FrReviewStatus]

    ,[FrRemarks]

    ,[FrCreator]

    ,[FrCapture]

    ,[FrIsMarkup]

    ,[IsID2Work]

    ,[ID2Connection]

    ,[ID2StartDate]

    ,[ID2EndDate]

    ,[ID2JobTime]

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

    ,@ToIsDiscussion

    ,@ToRemarks

    ,@ToCreator

    ,@ToCapture

    ,@ToIsMarkup

    ,@FrReviewStatus

    ,@FrRemarks

    ,@FrCreator

    ,@FrCapture

    ,@FrIsMarkup

    ,@IsID2Work

    ,@ID2Connection

    ,@ID2StartDate

    ,@ID2EndDate

    ,@ID2JobTime

    ,@ID2Status

    ,@ID2Issues

    ,@AVEVAConnection

    ,@AVEVAConvertDate

    ,@AVEVAReviewDate

    ,@AVEVAStatus

    ,@AVEVAIssues

    ,@ReviewFilePath

    ,@ReviewFileName

    ,@ProdReviewer

    ,@ProdIsResult

    ,@ProdRemarks

    ,@ClientReviewer

    ,@ClientIsResult

    ,@ClientRemarks

    ,@DTIsGateWay

    ,@DTIsImport

    ,@DTIsRegSystem

    ,@DTRemarks

);";

                        }

                        else

                        {

                            query = $@"

update dbo.Documents

set    [DocumentNo]=@DocumentNo

      ,[RevisonNo]=@RevisonNo

      ,[RefProjectCode]=@RefProjectCode

      ,[DocFilePath]=@DocFilePath

      ,[DocFileName]=@DocFileName

      ,[JobLevel]=@JobLevel

      ,[IsTypical]=@IsTypical

      ,[PersonInCharge]=@PersonInCharge

      ,[ModifiedDate]=getdate()

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

      ,[ID2JobTime]=@ID2JobTime

      ,[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=@DateTimeNow ");

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

                        }

                        #endregion

                        if (parameters.Count > 0)

                        {

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

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

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

                            parameters.Add("ModifiedUser", userId);

                            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;

        }

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

        }

    }

}