namespace MMDAL.Query.MRP
{
    public static class MRPQB
    {
        // Index: MMRP (TENANTID, STATUS) covering MRPID, MRPCODE, MRPNAME

        public const string GET_MRP = @"
            SELECT  m.MRPID                AS MRPId,
                    m.MRPCODE              AS MRPCode,
                    m.MRPNAME              AS MRPName,
                    m.ISAUTORELEASE        AS MRPIsAutoRelease,
                    m.ISTRACKCONVERSION    AS MRPIsTrackConversion,
                    m.OVERWRITETYPE        AS MRPOverWriteType,
                    m.ORGANIZATIONTYPE     AS MRPOrganizationType,
                    ISNULL(m.OUID, -1)    AS OUId,
                    ISNULL(ou.ORGANIZATIONUNITCODE, '')  AS OUCode,
                    ISNULL(ou.ORGANIZATIONUNITNAME, '')  AS OUName,
                    ISNULL(m.OUGROUPID, -1) AS OUGroupId,
                    ISNULL(og.ORGANIZATIONGROUPCODE, '') AS OUGroupCode,
                    ISNULL(og.ORGANIZATIONGROUPNAME, '') AS OUGroupName,
                    ISNULL(m.MASTERSCHEDULEID, -1) AS MasterScheduleId,
                    ISNULL(ms.MASTERSCHEDULECODE, '') AS MasterScheduleCode,
                    ISNULL(ms.MASTERSCHEDULENAME, '') AS MasterScheduleName,
                    m.ISPENDING            AS MRPIsPending,
                    m.INCLUDELEADTIME      AS MRPIncludeLeadTime,
                    m.INCLUDELOTSIZE       AS MRPIncludeLotSize,
                    m.INCLUDESTOCKINTRANSIT AS MRPIncludeStockInTransit,
                    m.INCLUDESTOCK         AS MRPIncludeStock,
                    m.SORTORDER            AS MRPSortOrder,
                    m.STATUS               AS MRPStatus,
                    m.VERSION              AS MRPVersion,
                    m.CREATEDBYID          AS MRPCreatedById,
                    m.CREATEDON            AS MRPCreatedOn,
                    m.MODIFIEDBYID         AS MRPModifiedById,
                    m.MODIFIEDON           AS MRPModifiedOn,
                    ISNULL(cu.USERNAME, '') AS MRPCreatedByName,
                    ISNULL(mu.USERNAME, '') AS MRPModifiedByName,
                    m.TENANTID             AS TenantId
            FROM    MMRP m
            LEFT JOIN MORGANIZATIONUNIT  ou ON ou.OUID                 = m.OUID      AND ou.TENANTID  = m.TENANTID
            LEFT JOIN MORGANIZATIONGROUP og ON og.ORGANIZATIONGROUPID  = m.OUGROUPID AND og.TENANTID  = m.TENANTID
            LEFT JOIN MMASTERSCHEDULE ms ON ms.MASTERSCHEDULEID = m.MASTERSCHEDULEID AND ms.TENANTID = m.TENANTID
            LEFT JOIN MUSER      cu ON cu.USERID    = m.CREATEDBYID  AND cu.TENANTID = m.TENANTID
            LEFT JOIN MUSER      mu ON mu.USERID    = m.MODIFIEDBYID AND mu.TENANTID = m.TENANTID
            WHERE   m.MRPID   = @MRPId
            AND     m.TENANTID = @TenantId";

        public const string SAVE_MRP = @"
            INSERT INTO MMRP
                (MRPID, MRPCODE, MRPNAME,
                 ISAUTORELEASE, ISTRACKCONVERSION, OVERWRITETYPE, ORGANIZATIONTYPE,
                 OUID, OUGROUPID, MASTERSCHEDULEID,
                 ISPENDING, INCLUDELEADTIME, INCLUDELOTSIZE, INCLUDESTOCKINTRANSIT, INCLUDESTOCK,
                 SORTORDER, STATUS, VERSION,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES
                (@MRPId, @MRPCode, @MRPName,
                 @MRPIsAutoRelease, @MRPIsTrackConversion, @MRPOverWriteType, @MRPOrganizationType,
                 NULLIF(@OUId, -1), NULLIF(@OUGroupId, -1), NULLIF(@MasterScheduleId, -1),
                 @MRPIsPending, @MRPIncludeLeadTime, @MRPIncludeLotSize, @MRPIncludeStockInTransit, @MRPIncludeStock,
                 @MRPSortOrder, @MRPStatus, @MRPVersion,
                 @MRPCreatedById, @MRPCreatedOn, @MRPModifiedById, @MRPModifiedOn, @TenantId)";

        public const string UPDATE_MRP = @"
            UPDATE MMRP SET
                MRPCODE                = @MRPCode,
                MRPNAME                = @MRPName,
                ISAUTORELEASE          = @MRPIsAutoRelease,
                ISTRACKCONVERSION      = @MRPIsTrackConversion,
                OVERWRITETYPE          = @MRPOverWriteType,
                ORGANIZATIONTYPE       = @MRPOrganizationType,
                OUID                   = NULLIF(@OUId, -1),
                OUGROUPID              = NULLIF(@OUGroupId, -1),
                MASTERSCHEDULEID       = NULLIF(@MasterScheduleId, -1),
                ISPENDING              = @MRPIsPending,
                INCLUDELEADTIME        = @MRPIncludeLeadTime,
                INCLUDELOTSIZE         = @MRPIncludeLotSize,
                INCLUDESTOCKINTRANSIT  = @MRPIncludeStockInTransit,
                INCLUDESTOCK           = @MRPIncludeStock,
                SORTORDER              = @MRPSortOrder,
                STATUS                 = @MRPStatus,
                VERSION                = @MRPVersion,
                MODIFIEDBYID           = @MRPModifiedById,
                MODIFIEDON             = @MRPModifiedOn
            WHERE MRPID    = @MRPId
            AND   TENANTID = @TenantId";

        public const string DELETE_MRP = @"
            DELETE FROM MMRP
            WHERE MRPID    = @MRPId
            AND   TENANTID = @TenantId";

        // Index: MMRP (TENANTID, STATUS, MRPID DESC) for sorted paged list
        public const string GET_MRP_LIST = @"
            SELECT  m.MRPID               AS MRPId,
                    m.MRPCODE             AS MRPCode,
                    m.MRPNAME             AS MRPName,
                    m.ORGANIZATIONTYPE    AS MRPOrganizationType,
                    ISNULL(ou.ORGANIZATIONUNITCODE, '') AS OUCode,
                    ISNULL(og.ORGANIZATIONGROUPCODE, '') AS OUGroupCode,
                    ISNULL(ms.MASTERSCHEDULECODE, '') AS MasterScheduleCode,
                    m.STATUS              AS MRPStatus,
                    m.TENANTID            AS TenantId
            FROM    MMRP m
            LEFT JOIN MORGANIZATIONUNIT  ou ON ou.OUID                = m.OUID      AND ou.TENANTID  = m.TENANTID
            LEFT JOIN MORGANIZATIONGROUP og ON og.ORGANIZATIONGROUPID = m.OUGROUPID AND og.TENANTID  = m.TENANTID
            LEFT JOIN MMASTERSCHEDULE ms ON ms.MASTERSCHEDULEID = m.MASTERSCHEDULEID AND ms.TENANTID = m.TENANTID
            WHERE   m.TENANTID = @TenantId
            ORDER BY m.MRPID DESC
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_MRP_LIST_COUNT = @"
            SELECT COUNT(1) FROM MMRP WHERE TENANTID = @TenantId";

        // TOP 50 — dropdown; ordered by most recently created
        public const string GET_SELECTLIST_MRP = @"
            SELECT TOP 50
                    m.MRPID   AS Id,
                    m.MRPCODE AS Code,
                    m.MRPNAME AS Name
            FROM    MMRP m
            WHERE   m.TENANTID = @TenantId
            ORDER BY m.MRPID DESC";
    }
}