using System;

namespace MMDAL.Query.DutyTariff
{
    // Required indexes:
    //   MDUTYTARIFF: (DUTYTARIFFID)                           -- PK; covers paged ORDER BY
    //   MDUTYTARIFF: (DUTYDRAWBACKID) INCLUDE (DUTYTARIFFID, DUTYTARIFFCODE, DUTYTARIFFNAME)
    //   MGCM: (GCMID) INCLUDE (GCMCODE, GCMNAME)              -- DUTYDRAWBACKID is a GCM reference, not a dedicated table (see DutyTariff.hbm.xml / GB4 MMReportQueryBuilder: dutytariff.DUTYDRAWBACKID = dutydrawbackgcm.GCMID)
    //   MUSER: (USERID)                                       -- covers LEFT JOIN on CREATEDBYID / MODIFIEDBYID
    public static class DutyTariffQB
    {
        public const string GET_DUTYTARIFF = @"
SELECT
    dt.DUTYTARIFFID         AS DutyTariffId,
    dt.DUTYTARIFFCODE       AS DutyTariffCode,
    dt.DUTYTARIFFNAME       AS DutyTariffName,
    dt.REMARKS              AS DutyTariffRemarks,
    dt.TARIFFTYPE           AS DutyTariffTariffType,

    dt.DUTYDRAWBACKID       AS DutyDrawBackId,
    ddb.GCMCODE             AS DutyDrawBackCode,
    ddb.GCMNAME             AS DutyDrawBackName,

    dt.SORTORDER            AS DutyTariffSortOrder,
    dt.STATUS               AS DutyTariffStatus,
    dt.VERSION              AS DutyTariffVersion,

    dt.CREATEDBYID          AS DutyTariffCreatedById,
    cb.USERNAME             AS DutyTariffCreatedByName,
    dt.CREATEDON            AS DutyTariffCreatedOn,

    dt.MODIFIEDBYID         AS DutyTariffModifiedById,
    mb.USERNAME             AS DutyTariffModifiedByName,
    dt.MODIFIEDON           AS DutyTariffModifiedOn,

    dt.SOURCETYPE           AS DutyTariffSourceType,
    dt.TENANTID             AS DutyTariffTenantId

FROM dbo.MDUTYTARIFF dt

LEFT JOIN dbo.MGCM ddb
       ON dt.DUTYDRAWBACKID = ddb.GCMID

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

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

WHERE dt.DUTYTARIFFID = @DutyTariffId;
";

        public const string SAVE_DUTYTARIFF = @"
INSERT INTO MDUTYTARIFF
(
    DUTYTARIFFCODE,
    DUTYTARIFFNAME,
    REMARKS,
    TARIFFTYPE,
    DUTYDRAWBACKID,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @DutyTariffCode,
    @DutyTariffName,
    @DutyTariffRemarks,
    @DutyTariffTariffType,
    @DutyDrawBackId,
    @DutyTariffSortOrder,
    @DutyTariffStatus,
    @DutyTariffVersion,
    @DutyTariffCreatedById,
    @DutyTariffCreatedOn,
    @DutyTariffModifiedById,
    @DutyTariffModifiedOn,
    @DutyTariffSourceType,
    @DutyTariffTenantId
);";

        public const string UPDATE_DUTYTARIFF = @"
UPDATE MDUTYTARIFF
SET
    DUTYTARIFFCODE = @DutyTariffCode,
    DUTYTARIFFNAME = @DutyTariffName,
    REMARKS = @DutyTariffRemarks,
    TARIFFTYPE = @DutyTariffTariffType,
    DUTYDRAWBACKID = @DutyDrawBackId,
    SORTORDER = @DutyTariffSortOrder,
    STATUS = @DutyTariffStatus,
    VERSION = @DutyTariffVersion,
    MODIFIEDBYID = @DutyTariffModifiedById,
    MODIFIEDON = @DutyTariffModifiedOn,
    SOURCETYPE = @DutyTariffSourceType
WHERE
    DUTYTARIFFID = @DutyTariffId;";

        public const string DELETE_DUTYTARIFF = @"
DELETE FROM MDUTYTARIFF
WHERE DUTYTARIFFID = @DutyTariffId;";

        public const string GET_SELECTLIST_DUTYTARIFF = @"
WITH DutyTariff AS
(
    SELECT
        dt.DUTYTARIFFID         AS DutyTariffId,
        dt.DUTYTARIFFCODE       AS DutyTariffCode,
        dt.DUTYTARIFFNAME       AS DutyTariffName,
        dt.REMARKS              AS DutyTariffRemarks,
        dt.TARIFFTYPE           AS DutyTariffTariffType,

        dt.DUTYDRAWBACKID       AS DutyDrawBackId,
        ddb.GCMCODE             AS DutyDrawBackCode,
        ddb.GCMNAME             AS DutyDrawBackName,

        dt.SORTORDER            AS DutyTariffSortOrder,
        dt.STATUS               AS DutyTariffStatus,
        dt.VERSION              AS DutyTariffVersion,

        dt.CREATEDBYID          AS DutyTariffCreatedById,
        cb.USERNAME             AS DutyTariffCreatedByName,
        dt.CREATEDON            AS DutyTariffCreatedOn,

        dt.MODIFIEDBYID         AS DutyTariffModifiedById,
        mb.USERNAME             AS DutyTariffModifiedByName,
        dt.MODIFIEDON           AS DutyTariffModifiedOn,

        dt.SOURCETYPE           AS DutyTariffSourceType,
        dt.TENANTID             AS DutyTariffTenantId,

        ROW_NUMBER() OVER (ORDER BY dt.DUTYTARIFFID) AS RowNum

    FROM dbo.MDUTYTARIFF dt

    LEFT JOIN dbo.MGCM ddb
           ON dt.DUTYDRAWBACKID = ddb.GCMID

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

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

SELECT
    DutyTariffId,
    DutyTariffCode,
    DutyTariffName,
    DutyTariffRemarks,
    DutyTariffTariffType,
    DutyDrawBackId,
    DutyDrawBackCode,
    DutyDrawBackName,
    DutyTariffSortOrder,
    DutyTariffStatus,
    DutyTariffVersion,
    DutyTariffCreatedById,
    DutyTariffCreatedByName,
    DutyTariffCreatedOn,
    DutyTariffModifiedById,
    DutyTariffModifiedByName,
    DutyTariffModifiedOn,
    DutyTariffSourceType,
    DutyTariffTenantId,
    RowNum
FROM DutyTariff
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_DUTYTARIFF_LIST = @"
SELECT
    dt.DUTYTARIFFID         AS DutyTariffId,
    dt.DUTYTARIFFCODE       AS DutyTariffCode,
    dt.DUTYTARIFFNAME       AS DutyTariffName,
    dt.REMARKS              AS DutyTariffRemarks,
    dt.TARIFFTYPE           AS DutyTariffTariffType,

    dt.DUTYDRAWBACKID       AS DutyDrawBackId,

    dt.SORTORDER            AS DutyTariffSortOrder,
    dt.STATUS               AS DutyTariffStatus,
    dt.VERSION              AS DutyTariffVersion,

    dt.CREATEDBYID          AS DutyTariffCreatedById,
    dt.CREATEDON            AS DutyTariffCreatedOn,

    dt.MODIFIEDBYID         AS DutyTariffModifiedById,
    dt.MODIFIEDON           AS DutyTariffModifiedOn,

    dt.SOURCETYPE           AS DutyTariffSourceType,
    dt.TENANTID             AS DutyTariffTenantId

FROM dbo.MDUTYTARIFF dt;";
    }
}