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

namespace AdminDAL.Query.Draft
{
    public static class DraftQB
    {
        public const string GET_DRAFT = @"SELECT
                            D.DRAFTID AS DraftId,
                            D.ENTITYID AS EntityId,
                            E.ENTITYCODE AS EntityCode,
                            E.ENTITYNAME AS EntityName,
                            D.MENUID AS MenuId,
                            M.MENUCODE AS MenuCode,
                            M.MENUNAME AS MenuName,
                            D.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                            B.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
                            B.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
                            D.OUID AS OuId,
                            O.ORGANIZATIONUNITCODE AS OuCode,
                            O.ORGANIZATIONUNITNAME AS OuName,
                            D.CODE AS DraftDraftCode,
                            D.NAME AS DraftName,
                            D.PARTICULARS AS DraftParticulars,
                            D.DRAFTNUMBER AS DraftNumber,
                            D.DRAFTDATE AS DraftDate,
                            D.REFERENCENUMBER AS DraftReferenceNumber,
                            D.REFERENCEDATE AS DraftReferenceDate,
                            D.JSONOBJECT AS DraftJsonObject,
                            D.STATUS AS DraftStatus,
                            D.VERSION AS DraftVersion,
                            D.CREATEDBYID AS DraftCreatedById,
                            D.CREATEDON AS DraftCreatedOn,
                            D.MODIFIEDBYID AS DraftModifiedById,
                            D.MODIFIEDON AS DraftModifiedOn
                            FROM TDRAFT D
                            LEFT JOIN MENTITY E ON D.ENTITYID = E.ENTITYID
                            LEFT JOIN MMENU M ON D.MENUID = M.MENUID
                            LEFT JOIN MBIZTRANSACTIONTYPE B ON D.BIZTRANSACTIONTYPEID = B.BIZTRANSACTIONTYPEID
                            LEFT JOIN MORGANIZATIONUNIT O ON D.OUID = O.OUID
                            WHERE D.DRAFTID = @draftid;
                            ";

        public const string GET_SELECTLIST_DRAFT = @"
                            WITH PagedDraft AS (
                            SELECT 
                            D.DRAFTID AS DraftId,
                            D.ENTITYID AS EntityId,
                            E.ENTITYCODE AS EntityCode,
                            E.ENTITYNAME AS EntityName,
                            D.MENUID AS MenuId,
                            M.MENUCODE AS MenuCode,
                            M.MENUNAME AS MenuName,
                            D.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                            B.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
                            B.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
                            D.OUID AS OuId,
                            O.ORGANIZATIONUNITCODE AS OuCode,
                            O.ORGANIZATIONUNITNAME AS OuName,
                            D.CODE AS DraftDraftCode,
                            D.NAME AS DraftName,
                            D.PARTICULARS AS DraftParticulars,
                            D.DRAFTNUMBER AS DraftNumber,
                            D.DRAFTDATE AS DraftDate,
                            D.REFERENCENUMBER AS DraftReferenceNumber,
                            D.REFERENCEDATE AS DraftReferenceDate,
                            D.STATUS AS DraftStatus,
                            D.VERSION AS DraftVersion,
                            D.CREATEDBYID AS DraftCreatedById,
                            D.CREATEDON AS DraftCreatedOn,
                            D.MODIFIEDBYID AS DraftModifiedById,
                            D.MODIFIEDON AS DraftModifiedOn,
                            ROW_NUMBER() OVER (ORDER BY D.DRAFTID) AS RowNum
                            FROM TDRAFT D
                            LEFT JOIN MENTITY E ON D.ENTITYID = E.ENTITYID
                            LEFT JOIN MMENU M ON D.MENUID = M.MENUID
                            LEFT JOIN MBIZTRANSACTIONTYPE B ON D.BIZTRANSACTIONTYPEID = B.BIZTRANSACTIONTYPEID
                            LEFT JOIN MORGANIZATIONUNIT O ON D.OUID = O.OUID
                            )
                            SELECT
                            DraftId,
                            EntityId,
                            EntityCode,
                            EntityName,
                            MenuId,
                            MenuCode,
                            MenuName,
                            BizTransactionTypeId,
                            BizTransactionTypeCode,
                            BizTransactionTypeName,
                            OuId,
                            OuCode,
                            OuName,
                            DraftDraftCode,
                            DraftName,
                            DraftParticulars,
                            DraftNumber,
                            DraftDate,
                            DraftReferenceNumber,
                            DraftReferenceDate,
                            DraftStatus,
                            DraftVersion,
                            DraftCreatedById,
                            DraftCreatedOn,
                            DraftModifiedById,
                            DraftModifiedOn
                            FROM PagedDraft
                            WHERE  
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        public const string SAVE_DRAFT = @"INSERT 
                            INTO TDRAFT (
                                 DRAFTID,
                                 ENTITYID,
                                 MENUID,
                                 BIZTRANSACTIONTYPEID,
                                 OUID,
                                 CODE,
                                 NAME,
                                 PARTICULARS,
                                 DRAFTNUMBER,
                                 DRAFTDATE,
                                 REFERENCENUMBER,
                                 REFERENCEDATE,
                                 JSONOBJECT,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) VALUES (
                                 @DraftId,
                                 @EntityId,
                                 @MenuId,
                                 @BizTransactionTypeId,
                                 @OuId,
                                 @DraftDraftCode,
                                 @DraftName,
                                 @DraftParticulars,
                                 @DraftNumber,
                                 @DraftDate,
                                 @DraftReferenceNumber,
                                 @DraftReferenceDate,
                                 @DraftJsonObject,
                                 @DraftStatus,
                                 @DraftVersion,
                                 @DraftCreatedById,
                                 @DraftCreatedOn,
                                 @DraftModifiedById,
                                 @DraftModifiedOn
                            );
                            ";

        public const string UPDATE_DRAFT = @"UPDATE 
                            TDRAFT SET
                            ENTITYID = @EntityId,
                            MENUID = @MenuId,
                            BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
                            OUID = @OuId,
                            CODE = @DraftDraftCode,
                            NAME = @DraftName,
                            PARTICULARS = @DraftParticulars,
                            DRAFTNUMBER = @DraftNumber,
                            DRAFTDATE = @DraftDate,
                            REFERENCENUMBER = @DraftReferenceNumber,
                            REFERENCEDATE = @DraftReferenceDate,
                            JSONOBJECT = @DraftJsonObject,
                            STATUS = @DraftStatus,
                            VERSION = @DraftVersion,
                            MODIFIEDBYID = @DraftModifiedById,
                            MODIFIEDON = @DraftModifiedOn
                            WHERE DRAFTID = @DraftId;
                            ";

        public const string DELETE_DRAFT = @"DELETE 
                            FROM TDRAFT
                            WHERE DRAFTID = @draftid;
                            ";
    }
}