namespace TMSDAL.Query.ExternalVendor
{
    /// <summary>
    /// Required indexes:
    ///   IX_MEXTERNALVENDOR_TENANTID on (TENANTID)
    ///   IX_MEXTERNALVENDOR_ISACTIVE_TENANTID on (ISACTIVE, TENANTID)
    /// </summary>
    public static class ExternalVendorQB
    {
        public const string GET_VENDOR = @"
SELECT
    ev.VENDORID             AS ExternalVendorId,
    ev.VENDORCODE           AS VendorCode,
    ev.VENDORNAME           AS VendorName,
    ev.VENDORTYPE           AS VendorType,
    ev.CONTACTNAME          AS ContactName,
    ev.CONTACTEMAIL         AS ContactEmail,
    ev.CONTACTPHONE         AS ContactPhone,

    ev.STARTDATE            AS StartDate,
    ev.ENDDATE              AS EndDate,

    ev.VENDORADDRESS        AS Address,
    ev.SPECIALISATIONS      AS Specialisations,
    ev.ACCREDITEDBY         AS AccreditedBy,
    ev.CONTRACTEXPIRYDATE   AS ContractExpiryDate,
    ev.ISACTIVE             AS IsActive,
    ev.VERSION              AS Version,
    ev.STATUS               AS Status,
    ev.SORTORDER            AS SortOrder,
    ev.SOURCETYPE           AS SourceType,

    ev.CONTACTID            AS ContactId,
    c.NAME                  AS ContactRefName,

    ev.TENANTID             AS TenantId,
    ev.CREATEDBYID          AS CreatedById,
    ev.CREATEDON            AS CreatedOn,
    ev.MODIFIEDBYID         AS ModifiedById,
    ev.MODIFIEDON           AS ModifiedOn
FROM DBO.MEXTERNALVENDOR ev
LEFT JOIN DBO.MCONTACT c ON c.CONTACTID = ev.CONTACTID
WHERE ev.VENDORID = @ExternalVendorId
  AND ev.TENANTID = @TenantId";

        public const string SAVE_VENDOR = @"
        INSERT INTO DBO.MEXTERNALVENDOR
        (
            VENDORID,
            VENDORCODE,
            VENDORNAME,
            VENDORTYPE,
            CONTACTNAME,
            CONTACTEMAIL,
            CONTACTPHONE,
            VENDORADDRESS,
            SPECIALISATIONS,
            ACCREDITEDBY,
            CONTRACTEXPIRYDATE,
            ISACTIVE,
            STARTDATE,
            ENDDATE,
            VERSION,
            STATUS,
            SORTORDER,
            SOURCETYPE,
            TENANTID,
            CREATEDBYID,
            CREATEDON,
            CONTACTID,
            MODIFIEDBYID,
            MODIFIEDON
        )
        VALUES
        (
            @ExternalVendorId,
            @VendorCode,
            @VendorName,
            @VendorType,
            @ContactName,
            @ContactEmail,
            @ContactPhone,
            @Address,
            @Specialisations,
            @AccreditedBy,
            @ContractExpiryDate,
            @IsActive,
            @StartDate,
            @EndDate,
            @Version,
            @Status,
            @SortOrder,
            5,
            @TenantId,
            @CreatedById,
            @CreatedOn,
            @ContactId,
            @ModifiedById,
            @ModifiedOn
        )";

        public const string UPDATE_VENDOR = @"
        UPDATE DBO.MEXTERNALVENDOR
        SET
            VENDORCODE          = @VendorCode,
            VENDORNAME          = @VendorName,
            VENDORTYPE          = @VendorType,
            CONTACTNAME         = @ContactName,
            CONTACTEMAIL        = @ContactEmail,
            CONTACTPHONE        = @ContactPhone,
            VENDORADDRESS       = @Address,
            STARTDATE        = @StartDate,
            ENDDATE          = @EndDate,
            SPECIALISATIONS     = @Specialisations,
            ACCREDITEDBY        = @AccreditedBy,
            CONTRACTEXPIRYDATE  = @ContractExpiryDate,
            CONTACTID           = @ContactId,
            ISACTIVE            = @IsActive,
            MODIFIEDBYID        = @ModifiedById,
            MODIFIEDON          = @ModifiedOn
        WHERE VENDORID = @ExternalVendorId
          AND TENANTID = @TenantId";

        public const string DELETE_VENDOR = @"
        DELETE FROM DBO.MEXTERNALVENDOR
        WHERE VENDORID = @ExternalVendorId
          AND TENANTID = @TenantId";

        public const string GET_SELECTLIST_VENDOR = @"
        SELECT
            VENDORID     AS ExternalVendorId,
            VENDORCODE   AS VendorCode,
            VENDORNAME   AS VendorName
        FROM DBO.MEXTERNALVENDOR
        WHERE ISACTIVE = 0
          AND TENANTID = @TenantId
        ORDER BY VENDORNAME";

        public const string GET_VENDOR_LIST = @"SELECT
    ev.VENDORID             AS ExternalVendorId,
    ev.VENDORCODE           AS VendorCode,
    ev.VENDORNAME           AS VendorName,
    ev.VENDORTYPE           AS VendorType,
    ev.CONTACTNAME          AS ContactName,
    ev.CONTACTEMAIL         AS ContactEmail,
    ev.CONTACTPHONE         AS ContactPhone,

    ev.STARTDATE            AS StartDate,
    ev.ENDDATE              AS EndDate,

    ev.VENDORADDRESS        AS Address,
    ev.SPECIALISATIONS      AS Specialisations,
    ev.ACCREDITEDBY         AS AccreditedBy,
    ev.CONTRACTEXPIRYDATE   AS ContractExpiryDate,
    ev.ISACTIVE             AS IsActive,
    ev.VERSION              AS Version,
    ev.STATUS               AS Status,
    ev.SORTORDER            AS SortOrder,
    ev.SOURCETYPE           AS SourceType,

    ev.CONTACTID            AS ContactId,
    c.NAME                  AS ContactRefName,

    ev.TENANTID             AS TenantId,
    ev.CREATEDBYID          AS CreatedById,
    ev.CREATEDON            AS CreatedOn,
    ev.MODIFIEDBYID         AS ModifiedById,
    ev.MODIFIEDON           AS ModifiedOn
FROM DBO.MEXTERNALVENDOR ev
LEFT JOIN DBO.MCONTACT c ON c.CONTACTID = ev.CONTACTID
WHERE ev.TENANTID = @TenantId";
    }
}