using System;

namespace MMDAL.Query.MeasureType
{
    // Required indexes:
    //   MMEASURETYPE: (MEASURETYPEID)                          -- PK; covers paged ORDER BY
    //   MMEASURETYPE: (TENANTID, STATUS) INCLUDE (MEASURETYPEID, MEASURETYPECODE, MEASURETYPENAME)
    //   MUSER: (USERID)                                        -- covers LEFT JOIN on CREATEDBYID / MODIFIEDBYID
    internal static class MeasureTypeQB
    {
        public const string GET_MEASURETYPE = @"
SELECT
    mt.MEASURETYPEID        AS MeasureTypeId,
    mt.MEASURETYPECODE      AS MeasureTypeCode,
    mt.MEASURETYPENAME      AS MeasureTypeName,

    mt.NOOFMEASURE          AS MeasureTypeNoOfMeasure,
    mt.D1NAME               AS MeasureTypeD1Name,
    mt.D2NAME               AS MeasureTypeD2Name,
    mt.D3NAME               AS MeasureTypeD3Name,
    mt.D4NAME               AS MeasureTypeD4Name,
    mt.D5NAME               AS MeasureTypeD5Name,

    mt.EXPRESSION           AS MeasureTypeExpression,
    mt.UOMID                AS MeasureTypeUOMId,

    mt.SORTORDER            AS MeasureTypeSortOrder,
    mt.STATUS               AS MeasureTypeStatus,
    mt.VERSION              AS MeasureTypeVersion,

    mt.CREATEDBYID          AS MeasureTypeCreatedById,
    cb.USERNAME             AS MeasureTypeCreatedByName,
    mt.CREATEDON            AS MeasureTypeCreatedOn,

    mt.MODIFIEDBYID         AS MeasureTypeModifiedById,
    mb.USERNAME             AS MeasureTypeModifiedByName,
    mt.MODIFIEDON           AS MeasureTypeModifiedOn,

    mt.SOURCETYPE           AS MeasureTypeSourceType,
    mt.TENANTID             AS MeasureTypeTenantId

FROM dbo.MMEASURETYPE mt

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

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

WHERE mt.MEASURETYPEID = @MeasureTypeId;
";

        public const string SAVE_MEASURETYPE = @"
INSERT INTO MMEASURETYPE
(
    MEASURETYPECODE,
    MEASURETYPENAME,
    NOOFMEASURE,
    D1NAME,
    D2NAME,
    D3NAME,
    D4NAME,
    D5NAME,
    EXPRESSION,
    UOMID,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @MeasureTypeCode,
    @MeasureTypeName,
    @MeasureTypeNoOfMeasure,
    @MeasureTypeD1Name,
    @MeasureTypeD2Name,
    @MeasureTypeD3Name,
    @MeasureTypeD4Name,
    @MeasureTypeD5Name,
    @MeasureTypeExpression,
    @MeasureTypeUOMId,
    @MeasureTypeSortOrder,
    @MeasureTypeStatus,
    @MeasureTypeVersion,
    @MeasureTypeCreatedById,
    @MeasureTypeCreatedOn,
    @MeasureTypeModifiedById,
    @MeasureTypeModifiedOn,
    @MeasureTypeSourceType,
    @MeasureTypeTenantId
);";

        public const string UPDATE_MEASURETYPE = @"
UPDATE MMEASURETYPE
SET
    MEASURETYPECODE = @MeasureTypeCode,
    MEASURETYPENAME = @MeasureTypeName,
    NOOFMEASURE = @MeasureTypeNoOfMeasure,
    D1NAME = @MeasureTypeD1Name,
    D2NAME = @MeasureTypeD2Name,
    D3NAME = @MeasureTypeD3Name,
    D4NAME = @MeasureTypeD4Name,
    D5NAME = @MeasureTypeD5Name,
    EXPRESSION = @MeasureTypeExpression,
    UOMID = @MeasureTypeUOMId,
    SORTORDER = @MeasureTypeSortOrder,
    STATUS = @MeasureTypeStatus,
    VERSION = @MeasureTypeVersion,
    MODIFIEDBYID = @MeasureTypeModifiedById,
    MODIFIEDON = @MeasureTypeModifiedOn,
    SOURCETYPE = @MeasureTypeSourceType
WHERE
    MEASURETYPEID = @MeasureTypeId;";

        public const string DELETE_MEASURETYPE = @"
DELETE FROM MMEASURETYPE
WHERE MEASURETYPEID = @MeasureTypeId;";

        public const string GET_SELECTLIST_MEASURETYPE = @"
WITH MeasureType AS
(
    SELECT
        mt.MEASURETYPEID        AS Id,
        mt.MEASURETYPECODE      AS Code,
        mt.MEASURETYPENAME      AS Name,

        mt.NOOFMEASURE          AS MeasureTypeNoOfMeasure,
        mt.D1NAME               AS MeasureTypeD1Name,
        mt.D2NAME               AS MeasureTypeD2Name,
        mt.D3NAME               AS MeasureTypeD3Name,
        mt.D4NAME               AS MeasureTypeD4Name,
        mt.D5NAME               AS MeasureTypeD5Name,

        mt.EXPRESSION           AS MeasureTypeExpression,
        mt.UOMID                AS MeasureTypeUOMId,

        mt.SORTORDER            AS MeasureTypeSortOrder,
        mt.STATUS               AS MeasureTypeStatus,
        mt.VERSION              AS MeasureTypeVersion,

        mt.CREATEDBYID          AS MeasureTypeCreatedById,
        cb.USERNAME             AS MeasureTypeCreatedByName,
        mt.CREATEDON            AS MeasureTypeCreatedOn,

        mt.MODIFIEDBYID         AS MeasureTypeModifiedById,
        mb.USERNAME             AS MeasureTypeModifiedByName,
        mt.MODIFIEDON           AS MeasureTypeModifiedOn,

        mt.SOURCETYPE           AS MeasureTypeSourceType,
        mt.TENANTID             AS MeasureTypeTenantId,

        ROW_NUMBER() OVER (ORDER BY mt.MEASURETYPEID) AS RowNum

    FROM dbo.MMEASURETYPE mt

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

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

SELECT
    MeasureTypeId,
    MeasureTypeCode,
    MeasureTypeName,
    MeasureTypeNoOfMeasure,
    MeasureTypeD1Name,
    MeasureTypeD2Name,
    MeasureTypeD3Name,
    MeasureTypeD4Name,
    MeasureTypeD5Name,
    MeasureTypeExpression,
    MeasureTypeUOMId,
    MeasureTypeSortOrder,
    MeasureTypeStatus,
    MeasureTypeVersion,
    MeasureTypeCreatedById,
    MeasureTypeCreatedByName,
    MeasureTypeCreatedOn,
    MeasureTypeModifiedById,
    MeasureTypeModifiedByName,
    MeasureTypeModifiedOn,
    MeasureTypeSourceType,
    MeasureTypeTenantId,
    RowNum
FROM MeasureType
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_MEASURETYPE_LIST = @"
SELECT
    mt.MEASURETYPEID        AS MeasureTypeId,
    mt.MEASURETYPECODE      AS MeasureTypeCode,
    mt.MEASURETYPENAME      AS MeasureTypeName,

    mt.NOOFMEASURE          AS MeasureTypeNoOfMeasure,
    mt.D1NAME               AS MeasureTypeD1Name,
    mt.D2NAME               AS MeasureTypeD2Name,
    mt.D3NAME               AS MeasureTypeD3Name,
    mt.D4NAME               AS MeasureTypeD4Name,
    mt.D5NAME               AS MeasureTypeD5Name,

    mt.EXPRESSION           AS MeasureTypeExpression,
    mt.UOMID                AS MeasureTypeUOMId,

    mt.SORTORDER            AS MeasureTypeSortOrder,
    mt.STATUS               AS MeasureTypeStatus,
    mt.VERSION              AS MeasureTypeVersion,

    mt.CREATEDBYID          AS MeasureTypeCreatedById,
    mt.CREATEDON            AS MeasureTypeCreatedOn,

    mt.MODIFIEDBYID         AS MeasureTypeModifiedById,
    mt.MODIFIEDON           AS MeasureTypeModifiedOn,

    mt.SOURCETYPE           AS MeasureTypeSourceType,
    mt.TENANTID             AS MeasureTypeTenantId

FROM dbo.MMEASURETYPE mt;";
    }
}