using System;

namespace MMDAL.Query.TaxTable
{
    // Required indexes:
    //   MTAXTABLE: (TAXTABLEID)                               -- PK; covers paged ORDER BY
    //   MTAXTABLE: (TENANTID, STATUS) INCLUDE (TAXTABLEID, TAXTABLECODE, TAXTABLENAME)
    //   MUSER: (USERID)                                       -- covers LEFT JOIN on CREATEDBYID / MODIFIEDBYID
    public static class TaxTableQB
    {
        public const string GET_TAXTABLE = @"
SELECT
    tt.TAXTABLEID           AS TaxTableId,
    tt.TAXTABLECODE         AS TaxTableCode,
    tt.TAXTABLENAME         AS TaxTableName,

    tt.SORTORDER            AS TaxTableSortOrder,
    tt.STATUS               AS TaxTableStatus,
    tt.VERSION              AS TaxTableVersion,

    tt.CREATEDBYID          AS TaxTableCreatedById,
    cb.USERNAME             AS TaxTableCreatedByName,
    tt.CREATEDON            AS TaxTableCreatedOn,

    tt.MODIFIEDBYID         AS TaxTableModifiedById,
    mb.USERNAME             AS TaxTableModifiedByName,
    tt.MODIFIEDON           AS TaxTableModifiedOn,

    tt.SOURCETYPE           AS TaxTableSourceType,
    tt.TENANTID             AS TaxTableTenantId

FROM dbo.MTAXTABLE tt

LEFT JOIN dbo.MUSER cb
       ON tt.CREATEDBYID = cb.USERID

LEFT JOIN dbo.MUSER mb
       ON tt.MODIFIEDBYID = mb.USERID

WHERE tt.TAXTABLEID = @TaxTableId;
";

        public const string SAVE_TAXTABLE = @"
INSERT INTO MTAXTABLE
(
    TAXTABLECODE,
    TAXTABLENAME,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @TaxTableCode,
    @TaxTableName,
    @TaxTableSortOrder,
    @TaxTableStatus,
    @TaxTableVersion,
    @TaxTableCreatedById,
    @TaxTableCreatedOn,
    @TaxTableModifiedById,
    @TaxTableModifiedOn,
    @TaxTableSourceType,
    @TaxTableTenantId
);";

        public const string UPDATE_TAXTABLE = @"
UPDATE MTAXTABLE
SET
    TAXTABLECODE = @TaxTableCode,
    TAXTABLENAME = @TaxTableName,
    SORTORDER = @TaxTableSortOrder,
    STATUS = @TaxTableStatus,
    VERSION = @TaxTableVersion,
    MODIFIEDBYID = @TaxTableModifiedById,
    MODIFIEDON = @TaxTableModifiedOn,
    SOURCETYPE = @TaxTableSourceType
WHERE
    TAXTABLEID = @TaxTableId;";

        public const string DELETE_TAXTABLE = @"
DELETE FROM MTAXTABLE
WHERE TAXTABLEID = @TaxTableId;";

        public const string GET_SELECTLIST_TAXTABLE = @"
WITH TaxTable AS
(
    SELECT
        tt.TAXTABLEID           AS TaxTableId,
        tt.TAXTABLECODE         AS TaxTableCode,
        tt.TAXTABLENAME         AS TaxTableName,

        tt.SORTORDER            AS TaxTableSortOrder,
        tt.STATUS               AS TaxTableStatus,
        tt.VERSION              AS TaxTableVersion,

        tt.CREATEDBYID          AS TaxTableCreatedById,
        cb.USERNAME             AS TaxTableCreatedByName,
        tt.CREATEDON            AS TaxTableCreatedOn,

        tt.MODIFIEDBYID         AS TaxTableModifiedById,
        mb.USERNAME             AS TaxTableModifiedByName,
        tt.MODIFIEDON           AS TaxTableModifiedOn,

        tt.SOURCETYPE           AS TaxTableSourceType,
        tt.TENANTID             AS TaxTableTenantId,

        ROW_NUMBER() OVER (ORDER BY tt.TAXTABLEID) AS RowNum

    FROM dbo.MTAXTABLE tt

    LEFT JOIN dbo.MUSER cb
           ON tt.CREATEDBYID = cb.USERID

    LEFT JOIN dbo.MUSER mb
           ON tt.MODIFIEDBYID = mb.USERID
)

SELECT
    TaxTableId,
    TaxTableCode,
    TaxTableName,
    TaxTableSortOrder,
    TaxTableStatus,
    TaxTableVersion,
    TaxTableCreatedById,
    TaxTableCreatedByName,
    TaxTableCreatedOn,
    TaxTableModifiedById,
    TaxTableModifiedByName,
    TaxTableModifiedOn,
    TaxTableSourceType,
    TaxTableTenantId,
    RowNum
FROM TaxTable
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_TAXTABLE_LIST = @"
SELECT
    tt.TAXTABLEID           AS TaxTableId,
    tt.TAXTABLECODE         AS TaxTableCode,
    tt.TAXTABLENAME         AS TaxTableName,

    tt.SORTORDER            AS TaxTableSortOrder,
    tt.STATUS               AS TaxTableStatus,
    tt.VERSION              AS TaxTableVersion,

    tt.CREATEDBYID          AS TaxTableCreatedById,
    tt.CREATEDON            AS TaxTableCreatedOn,

    tt.MODIFIEDBYID         AS TaxTableModifiedById,
    tt.MODIFIEDON           AS TaxTableModifiedOn,

    tt.SOURCETYPE           AS TaxTableSourceType,
    tt.TENANTID             AS TaxTableTenantId

FROM dbo.MTAXTABLE tt;";
    }
}