using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.CommentHistory
{
    public class CommentHistoryQB
    {
        public const string GET_COMMENTHISTORY = @"SELECT 
                                                    a.COMMENTID AS CommentId,
                                                    a.USERID AS UserId,
                                                    b.USERCODE AS UserCode,
                                                    b.USERNAME AS UserName,
                                                    a.TIMESTAMP AS TimeStamp,
                                                    CASE a.COMMENTSTATUS 
                                                        WHEN 0 THEN 'Open' 
                                                        WHEN 1 THEN 'Resolved' 
                                                        WHEN 2 THEN 'Archive' 
                                                        ELSE '*' 
                                                    END AS CommentStatus,
                                                    CASE WHEN a.ACCESSTYPE = 0 THEN 'Public' ELSE 'Private' END AS AccessType,
                                                    a.PRIVATEUSERGROUPID AS UserGroupId,
                                                    c.USERGROUPCODE AS UserGroupCode,
                                                    c.USERGROUPNAME AS UserGroupName,
                                                    a.OBJECTTYPEID AS ObjectTypeId,
                                                    a.OBJECTID AS ObjectId,
                                                    a.COMMENTTEXT AS CommentText,
                                                    a.SECURITYLEVEL AS SecurityLevel,
                                                    a.PARENTCOMMENTID AS ParentCommentId,
                                                    CASE a.STATUS
                                                        WHEN 0 THEN 'Pending'
                                                        WHEN 1 THEN 'Active'
                                                        WHEN 2 THEN 'Deleted'
                                                        WHEN 3 THEN 'Amended'
                                                        WHEN 4 THEN 'Inactive'
                                                        WHEN 5 THEN 'Archived'
                                                        ELSE '*' 
                                                    END AS Status,
                                                    a.VERSION AS Version,
                                                    a.CREATEDBYID AS CreatedByUserId,
                                                    created.USERCODE AS CreatedByUserCode,
                                                    created.USERNAME AS CreatedByUserName,
                                                    a.CREATEDON AS CreatedOn,
                                                    a.MODIFIEDBYID AS ModifiedByUserId,
                                                    modified.USERCODE AS ModifiedByUserCode,
                                                    modified.USERNAME AS ModifiedByUserName,
                                                    a.BIZTRANSACTIONID AS BizTransactionId,
                                                    d.BIZTRANSACTIONCODE AS BizTransactionCode,
                                                    d.BIZTRANSACTIONNAME AS BizTransactionName,
                                                    a.OUID AS OuId,
                                                    e.ORGANIZATIONUNITCODE AS OuCode,
                                                    e.ORGANIZATIONUNITNAME AS OuName
                                                FROM 
                                                    TCOMMENT a
                                                    JOIN MUSER b ON a.USERID = b.USERID
                                                    LEFT JOIN MUSERGROUP c ON a.PRIVATEUSERGROUPID = c.USERGROUPID
                                                    LEFT JOIN TCOMMENT parent ON a.PARENTCOMMENTID = parent.COMMENTID
                                                    LEFT JOIN MBIZTRANSACTION d ON a.BIZTRANSACTIONID = d.BIZTRANSACTIONID
                                                    LEFT JOIN MORGANIZATIONUNIT e ON a.OUID = e.OUID
                                                    LEFT JOIN MUSER created ON a.CREATEDBYID = created.USERID
                                                    LEFT JOIN MUSER modified ON a.MODIFIEDBYID = modified.USERID
                                                WHERE 
                                                    a.OBJECTID = @ObjectId
                                                    AND a.OBJECTTYPEID = @ObjectTypeId;";

    }
}