namespace TMSDAL.Query.TrainingVenue
{
    /// <summary>
    /// Required indexes:
    ///   IX_MTRAININGVENUE_TENANTID on (TENANTID)
    ///   IX_MTRAININGVENUE_ISACTIVE_TENANTID on (ISACTIVE, TENANTID)
    /// </summary>
    public static class TrainingVenueQB
    {
        public const string GET_VENUE = @"
SELECT  
    tv.TRAININGVENUEID      AS TrainingVenueId,
    tv.VENUECODE            AS VenueCode,
    tv.VENUENAME            AS VenueName,
    tv.VENUETYPE            AS VenueType,
    tv.CAPACITY             AS Capacity,
    tv.VENUELOCATION        AS Location,
    tv.CONTACTPERSON        AS ContactPerson,
    tv.CONTACTPHONE         AS ContactPhone,

    tv.ISAIRCONDITIONING    AS IsAirConditioning,
    tv.ISPANTRY             AS IsPantry,
    tv.ISSCREENSHARING      AS IsScreenSharing,
    tv.ISRECORDING          AS IsRecording,

    tv.HASPROJECTOR         AS HasProjector,
    tv.HASWHITEBOARD        AS HasWhiteBoard,

    tv.VIRTUALLINK          AS VirtualLink,

    tv.CONTACTID            AS ContactId,
    c.NAME                  AS ContactName,

    tv.ISACTIVE             AS IsActive,
    tv.TENANTID             AS TenantId,
    tv.CREATEDBYID          AS CreatedById,
    tv.CREATEDON            AS CreatedOn,
    tv.MODIFIEDBYID         AS ModifiedById,
    tv.MODIFIEDON           AS ModifiedOn

FROM DBO.MTRAININGVENUE tv
LEFT JOIN DBO.MCONTACT c ON c.CONTACTID = tv.CONTACTID
WHERE tv.TRAININGVENUEID = @TrainingVenueId
  AND tv.TENANTID        = @TenantId";

        public const string SAVE_VENUE = @"
INSERT INTO DBO.MTRAININGVENUE
(
    TRAININGVENUEID, VENUECODE, VENUENAME, VENUETYPE, CAPACITY,
    VENUELOCATION, CONTACTPERSON, CONTACTPHONE,

    ISAIRCONDITIONING, ISPANTRY, ISSCREENSHARING, ISRECORDING,
    HASPROJECTOR, HASWHITEBOARD,
    VIRTUALLINK,
    CONTACTID,
    ISACTIVE,
    TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @TrainingVenueId, @VenueCode, @VenueName, @VenueType, @Capacity,
    @Location, @ContactPerson, @ContactPhone,

    @IsAirConditioning, @IsPantry, @IsScreenSharing, @IsRecording,
    @HasProjector, @HasWhiteBoard,
    @VirtualLink,
    @ContactId,
    @IsActive,
    @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";

        public const string UPDATE_VENUE = @"
UPDATE DBO.MTRAININGVENUE
SET
    VENUECODE           = @VenueCode,
    VENUENAME           = @VenueName,
    VENUETYPE           = @VenueType,
    CAPACITY            = @Capacity,
    VENUELOCATION       = @Location,
    CONTACTPERSON       = @ContactPerson,
    CONTACTPHONE        = @ContactPhone,

    ISAIRCONDITIONING   = @IsAirConditioning,
    ISPANTRY            = @IsPantry,
    ISSCREENSHARING     = @IsScreenSharing,
    ISRECORDING         = @IsRecording,

    HASPROJECTOR        = @HasProjector,
    HASWHITEBOARD       = @HasWhiteBoard,

    VIRTUALLINK         = @VirtualLink,
    CONTACTID           = @ContactId,
    ISACTIVE            = @IsActive,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn

WHERE TRAININGVENUEID = @TrainingVenueId
  AND TENANTID        = @TenantId";

        public const string DELETE_VENUE = @"
            DELETE FROM DBO.MTRAININGVENUE
            WHERE   TRAININGVENUEID = @TrainingVenueId
            AND     TENANTID        = @TenantId";

        public const string GET_SELECTLIST_VENUE = @"
SELECT  
    TRAININGVENUEID AS TrainingVenueId,
    VENUECODE AS VenueCode,
    VENUENAME AS VenueName
FROM DBO.MTRAININGVENUE
WHERE ISACTIVE = 0
  AND TENANTID = @TenantId
ORDER BY VENUENAME";

        public const string GET_VENUE_LIST = @"SELECT  
    tv.TRAININGVENUEID      AS TrainingVenueId,
    tv.VENUECODE            AS VenueCode,
    tv.VENUENAME            AS VenueName,
    tv.VENUETYPE            AS VenueType,
    tv.CAPACITY             AS Capacity,
    tv.VENUELOCATION        AS Location,
    tv.CONTACTPERSON        AS ContactPerson,
    tv.CONTACTPHONE         AS ContactPhone,

    tv.ISAIRCONDITIONING    AS IsAirConditioning,
    tv.ISPANTRY             AS IsPantry,
    tv.ISSCREENSHARING      AS IsScreenSharing,
    tv.ISRECORDING          AS IsRecording,

    tv.HASPROJECTOR         AS HasProjector,
    tv.HASWHITEBOARD        AS HasWhiteBoard,

    tv.VIRTUALLINK          AS VirtualLink,

    tv.CONTACTID            AS ContactId,
    c.NAME                  AS ContactName,

    tv.ISACTIVE             AS IsActive,
    tv.TENANTID             AS TenantId,
    tv.CREATEDBYID          AS CreatedById,
    tv.CREATEDON            AS CreatedOn,
    tv.MODIFIEDBYID         AS ModifiedById,
    tv.MODIFIEDON           AS ModifiedOn

FROM DBO.MTRAININGVENUE tv
LEFT JOIN DBO.MCONTACT c ON c.CONTACTID = tv.CONTACTID
WHERE tv.TENANTID        = @TenantId";

    }
}