namespace AccountsDAL.Query.ChequeBook
{
    public static class ChequeBookQB
    {
        public const string GET_CHEQUEBOOK = @"
            SELECT
                CB.CHEQUEBOOKID         AS ChequeBookId,
                CB.BANKID               AS BankId,
                B.BANKCODE              AS BankCode,
                B.BANKNAME              AS BankName,
                CB.INSTRUMENTID         AS InstrumentId,
                I.INSTRUMENTNAME        AS InstrumentName,
                CB.BOOKNO               AS ChequeBookBookNo,
                CB.NOOFLEAVES           AS ChequeBookNoOfLeaves,
                CB.STARTNO              AS ChequeBookStartNo,
                CB.REMARKS              AS ChequeBookRemarks,
                CB.BOOKTYPE             AS ChequeBookBookType,
                CB.CHEQUEPREFIX         AS ChequeBookChequePrefix,
                CB.STATUS               AS ChequeBookStatus,
                CB.VERSION              AS ChequeBookVersion,
                CB.CREATEDBYID          AS ChequeBookCreatedById,
                CB.CREATEDON            AS ChequeBookCreatedOn,
                CB.MODIFIEDBYID         AS ChequeBookModifiedById,
                CB.MODIFIEDON           AS ChequeBookModifiedOn
            FROM   MCHEQUEBOOK CB
            JOIN   MBANK       B ON CB.BANKID       = B.BANKID
            LEFT JOIN MINSTRUMENT I ON CB.INSTRUMENTID = I.INSTRUMENTID
            WHERE  CB.CHEQUEBOOKID = @ChequeBookId
            AND    CB.TENANTID     = @TenantId;";

        public const string GET_CHEQUEBOOK_DETAILS = @"
            SELECT
                CBD.CHEQUEBOOKDETAILID      AS ChequeBookDetailId,
                CBD.CHEQUEBOOKID            AS ChequeBookId,
                CBD.CHEQUENO                AS ChequeBookDetailChequeNo,
                CBD.STATUS                  AS ChequeBookDetailStatus,
                CBD.CANCELREASON            AS ChequeBookDetailCancelReason
            FROM MCHEQUEBOOKDETAIL CBD
            WHERE CBD.CHEQUEBOOKID = @ChequeBookId
            AND   CBD.TENANTID     = @TenantId
            ORDER BY CBD.CHEQUENO;";

        public const string SAVE_CHEQUEBOOK = @"
            INSERT INTO MCHEQUEBOOK
            (
                CHEQUEBOOKID, BANKID, INSTRUMENTID,
                BOOKNO, NOOFLEAVES, STARTNO, REMARKS, BOOKTYPE, CHEQUEPREFIX,
                STATUS, VERSION,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @ChequeBookId, @BankId, @InstrumentId,
                @ChequeBookBookNo, @ChequeBookNoOfLeaves, @ChequeBookStartNo,
                @ChequeBookRemarks, @ChequeBookBookType, @ChequeBookChequePrefix,
                @ChequeBookStatus, @ChequeBookVersion,
                @ChequeBookCreatedById, @ChequeBookCreatedOn,
                @ChequeBookModifiedById, @ChequeBookModifiedOn, @TenantId
            );";

        public const string UPDATE_CHEQUEBOOK = @"
            UPDATE MCHEQUEBOOK SET
                BANKID          = @BankId,
                INSTRUMENTID    = @InstrumentId,
                BOOKNO          = @ChequeBookBookNo,
                NOOFLEAVES      = @ChequeBookNoOfLeaves,
                STARTNO         = @ChequeBookStartNo,
                REMARKS         = @ChequeBookRemarks,
                BOOKTYPE        = @ChequeBookBookType,
                CHEQUEPREFIX    = @ChequeBookChequePrefix,
                STATUS          = @ChequeBookStatus,
                VERSION         = @ChequeBookVersion,
                MODIFIEDBYID    = @ChequeBookModifiedById,
                MODIFIEDON      = @ChequeBookModifiedOn
            WHERE CHEQUEBOOKID = @ChequeBookId
            AND   TENANTID     = @TenantId;";

        public const string DELETE_CHEQUEBOOK = @"
            DELETE FROM MCHEQUEBOOKDETAIL WHERE CHEQUEBOOKID = @ChequeBookId AND TENANTID = @TenantId;
            DELETE FROM MCHEQUEBOOK        WHERE CHEQUEBOOKID = @ChequeBookId AND TENANTID = @TenantId;";

        public const string DELETE_CHEQUEBOOK_DETAILS = @"
            DELETE FROM MCHEQUEBOOKDETAIL
            WHERE CHEQUEBOOKID = @ChequeBookId;";

        public const string SAVE_CHEQUEBOOK_DETAIL = @"
            INSERT INTO MCHEQUEBOOKDETAIL
            (
                CHEQUEBOOKDETAILID, CHEQUEBOOKID,
                CHEQUENO, STATUS, CANCELREASON
            )
            VALUES
            (
                @ChequeBookDetailId, @ChequeBookId,
                @ChequeBookDetailChequeNo, @ChequeBookDetailStatus, @ChequeBookDetailCancelReason
            );";

        public const string GET_SELECTLIST_CHEQUEBOOK = @"
            SELECT
                CHEQUEBOOKID AS Id,
                BOOKNO       AS BookNo
            FROM MCHEQUEBOOK
            WHERE TENANTID = @TenantId
            ORDER BY BOOKNO;";
    }
}