HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ID2.Manager.Data.Models;

namespace ID2.Manager.Dapper.Repository

{

    public class DocumentRepository : BaseRepository

    {

        public DocumentRepository(string connectionStr) : base(connectionStr) { }

        public IEnumerable<Documents> GetAllDocuments()

        {

            string query = $@"

select   doc.Seq

        ,doc.DocumentNo

        ,rev.ProcNo

        ,rev.RevNo

        ,rev.PhysicalPath

        ,rev.VirtualPath

        ,doc.JobLevel

        ,doc.Typical

        ,doc.PersonInCharge

        ,rev.Created

        ,toreview.IsDiscussion

        ,toreview.Remarks ToRemarks

        ,toreview.Capture ToCapture

        ,toreview.MarkupLink ToMarkupLink

        ,isnull(toreview.ModifiedUser,toreview.CreatedUser) ToUserID

        ,frreview.ReviewStatus

        ,frreview.Remarks FrRemarks

        ,frreview.Capture FrCapture

        ,frreview.MarkupLink FrMarkupLink

        ,isnull(frreview.ModifiedUser,frreview.CreatedUser) FromUserID

from     dbo.Documents doc

             inner join dbo.DocRevisions rev on doc.Seq=rev.RefDocSeq

		     left outer join dbo.ToReview toreview on rev.ProcNo=toreview.RefProcNo

		     left outer join dbo.FromReview frreview on rev.ProcNo=frreview.RefProcNo

where    doc.IsDeleted=0 and  rev.IsLatest=1

order by doc.DocumentNo, rev.ProcNo desc";

            return Query<Documents>(query);

        }

    }

}