namespace MMDAL.Query.NDTMethod
{
    public class NDTMethodQB
    {
        public const string GET_NDTMETHOD = @"
            SELECT
                NM.NDTMETHODID              AS NDTMethodId,
                NM.METHODCODE               AS NDTMethodCode,
                NM.METHODNAME               AS NDTMethodName,
                NM.REPORTTITLETEMPLATE      AS NDTMethodReportTitleTemplate,
                NM.PARAMETERSET             AS NDTMethodParameterSet,
                NM.HASSTARTENDTIME          AS NDTMethodHasStartEndTime,
                NM.REQUIRESCALIBRATION      AS NDTMethodRequiresCalibration,
                NM.REMARKS                  AS NDTMethodRemarks,
                NM.SORTORDER                AS NDTMethodSortOrder,
                NM.STATUS                   AS NDTMethodStatus,
                NM.VERSION                  AS NDTMethodVersion,
                NM.SOURCETYPE               AS NDTMethodSourceType,
                NM.CREATEDBYID              AS NDTMethodCreatedById,
                NM.CREATEDON                AS NDTMethodCreatedOn,
                CU.USERNAME                 AS NDTMethodCreatedByName,
                NM.MODIFIEDBYID             AS NDTMethodModifiedById,
                NM.MODIFIEDON               AS NDTMethodModifiedOn,
                MU.USERNAME                 AS NDTMethodModifiedByName
            FROM MNDTMETHOD NM
            LEFT JOIN MUSER CU ON CU.USERID = NM.CREATEDBYID
            LEFT JOIN MUSER MU ON MU.USERID = NM.MODIFIEDBYID
            WHERE NM.NDTMETHODID = @ndtmethodid
              AND NM.TENANTID    = @tenantid;";

        public const string SAVE_NDTMETHOD = @"
INSERT INTO MNDTMETHOD
(
    METHODCODE,
    METHODNAME,
    REPORTTITLETEMPLATE,
    PARAMETERSET,
    HASSTARTENDTIME,
    REQUIRESCALIBRATION,
    REMARKS,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    TENANTID
)
VALUES
(
    @NDTMethodCode,
    @NDTMethodName,
    @NDTMethodReportTitleTemplate,
    @NDTMethodParameterSet,
    @NDTMethodHasStartEndTime,
    @NDTMethodRequiresCalibration,
    @NDTMethodRemarks,
    @NDTMethodSortOrder,
    @NDTMethodStatus,
    @NDTMethodVersion,
    @NDTMethodSourceType,
    @NDTMethodCreatedById,
    @NDTMethodCreatedOn,
    @NDTMethodModifiedById,
    @NDTMethodModifiedOn,
    @tenantid
);
-- Return the generated identity value
SELECT CAST(SCOPE_IDENTITY() AS INT) AS NDTMETHODID;
";

        public const string UPDATE_NDTMETHOD = @"
            UPDATE MNDTMETHOD
            SET
                METHODCODE              = @NDTMethodCode,
                METHODNAME              = @NDTMethodName,
                REPORTTITLETEMPLATE     = @NDTMethodReportTitleTemplate,
                PARAMETERSET            = @NDTMethodParameterSet,
                HASSTARTENDTIME         = @NDTMethodHasStartEndTime,
                REQUIRESCALIBRATION     = @NDTMethodRequiresCalibration,
                REMARKS                 = @NDTMethodRemarks,
                SORTORDER               = @NDTMethodSortOrder,
                STATUS                  = @NDTMethodStatus,
                VERSION                 = @NDTMethodVersion,
                SOURCETYPE              = @NDTMethodSourceType,
                MODIFIEDBYID            = @NDTMethodModifiedById,
                MODIFIEDON              = @NDTMethodModifiedOn
            WHERE NDTMETHODID = @NDTMethodId
              AND TENANTID    = @tenantid;";

        public const string DELETE_NDTMETHOD = @"
            DELETE FROM MNDTMETHOD
            WHERE NDTMETHODID = @ndtmethodid
              AND TENANTID    = @tenantid;";

        public const string GET_SELECTLIST_NDTMETHOD = @"
            WITH PagedNDTMethod AS
            (
                SELECT
                    NM.NDTMETHODID      AS Id,
                    NM.METHODCODE       AS Code,
                    NM.METHODNAME       AS Name,
                    ROW_NUMBER() OVER (ORDER BY NM.METHODNAME) AS RowNum
                FROM MNDTMETHOD NM
                WHERE NM.TENANTID = @tenantid
                  AND NM.STATUS   = 1
            )
            SELECT Id, Code, Name
            FROM   PagedNDTMethod
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}