using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CRMDAL.Query.SLA
{
    public class SLAQB
    {

        public const string GET_SLA = @"SELECT
                                        -- Parent : MSLA
                                        SLA.SLAID                              AS SLAId,
                                        SLA.SLACODE                            AS SLACode,
                                        SLA.SLANAME                            AS SLAName,
                                        SLA.DESCRIPTION                        AS Description,
                                        SLA.CREATEDBYID                        AS SLACreatedById,
                                        SLA.CREATEDON                          AS SLACreatedOn,
                                        SLA.MODIFIEDBYID                       AS SLAModifiedById,
                                        SLA.MODIFIEDON                         AS SLAModifiedOn,
                                        SLA.SORTORDER                          AS SLASortOrder,
                                        SLA.STATUS                             AS SLAStatus,
                                        SLA.VERSION                            AS SLAVersion,
                                        SLA.SOURCETYPE                         AS SourceType,
                                        SLA.TENANTID                           AS SLATenantId,

                                        -- Child : MSLADetail
                                        SD.SLADETAILID                         AS SLADetailId,
                                        SD.SLAID                               AS SLAId,
                                        SD.SLAMETRICID                         AS SLAMetricID,
                                        MGCM.GCMCODE                           AS SLAMetricCode,
                                        MGCM.GCMNAME                           AS SLAMetricName,
                                        SD.SLACONDITION                        AS SLACondition,
                                        SD.THRESHOLDVALUE                      AS ThresholdValue,
                                        SD.THREHOLDUNIT                        AS ThresholdUnit,
                                        SD.ESCALATIONLEVEL                     AS EscalationLevel

                                    FROM MSLA SLA
                                    LEFT JOIN MSLADETAIL SD ON SLA.SLAID = SD.SLAID
                                    LEFT JOIN MUSER CU ON CU.USERID = SLA.CREATEDBYID
                                    LEFT JOIN MUSER MUU ON MUU.USERID = SLA.MODIFIEDBYID
                                    LEFT JOIN MGCM ON MGCM.GCMID = SD.SLAMETRICID
                                    WHERE SLA.SLAID = @SLAid;";

        //public const string GET_SELECTLIST_SLA = @"SELECT
        //                                            -- Parent : MSLA
        //                                            SLA.SLAID                              AS SLAId,
        //                                            SLA.SLACODE                            AS SLACode,
        //                                            SLA.SLANAME                            AS SLAName,
        //                                            SLA.DESCRIPTION                        AS Description,
        //                                            SLA.RESPONSETIMEHOURS                  AS SLAResponseTimeHours,
        //                                            SLA.RESOLUTIONTIMEHOURS                AS SLAResolutionTimeHours,
        //                                            SLA.AVAILABILITYPERCENTAGE             AS SLAAvailabilityPercentage,
        //                                            SLA.PENALTYCLAUSE                      AS SLAPenaltyClause,
        //                                            SLA.CREATEDBYID                        AS SLACreatedById,
        //                                            SLA.CREATEDON                          AS SLACreatedOn,
        //                                            SLA.MODIFIEDBYID                       AS SLAModifiedById,
        //                                            SLA.MODIFIEDON                         AS SLAModifiedOn,
        //                                            SLA.SORTORDER                          AS SLASortOrder,
        //                                            SLA.STATUS                             AS SLAStatus,
        //                                            SLA.VERSION                            AS SLAVersion,
        //                                            SLA.SOURCETYPE                         AS SourceType,
        //                                            SLA.TENANTID                           AS SLATenantId,

        //                                                        -- Child : MSLADetail
        //                                                        SD.SLADETAILID                         AS SLADetailId,
        //                                                        SD.SLAID                               AS SLAId,
        //                                                        SD.SLAMETRICID                         AS SLAMetricID,
        //                                                        SD.SLACONDITION                        AS SLACondition,
        //                                                        SD.THRESHOLDVALUE                      AS ThresholdValue,
        //                                                        SD.THREHOLDUNIT                        AS ThresholdUnit,
        //                                                        SD.ESCALATIONLEVEL                     AS EscalationLevel

        //                                        FROM MSLA SLA
        //                                        LEFT JOIN MSLADETAIL SD ON SLA.SLAID = SD.SLAID
        //                                        LEFT JOIN MUSER CU ON CU.USERID = SLA.CREATEDBYID
        //                                        LEFT JOIN MUSER MUU ON MUU.USERID = SLA.MODIFIEDBYID
        //                                        LEFT JOIN MGCM ON MGCM.GCMID = SD.SLAMETRICID;";

        public const string SAVE_SLA = @"INSERT INTO MSLA
                                            (
                                                SLAID,
                                                SLACODE,
                                                SLANAME,
                                                DESCRIPTION,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                SOURCETYPE,
                                                TENANTID
                                            )
                                            VALUES
                                            (
                                                @SLAId,
                                                @SLACode,
                                                @SLAName,
                                                @Description,
                                                @SLACreatedById,
                                                @SLACreatedOn,
                                                @SLAModifiedById,
                                                @SLAModifiedOn,
                                                @SLASortOrder,
                                                @SLAStatus,
                                                @SLAVersion,
                                                @SourceType,
                                                @SLATenantId
                                            );";

        public const string SAVE_SLA_DETAIL = @"INSERT INTO MSLADETAIL
                                                (
                                                    SLADETAILID,
                                                    SLAID,
                                                    SLAMETRICID,
                                                    SLACONDITION,
                                                    THRESHOLDVALUE,
                                                    THREHOLDUNIT,
                                                    ESCALATIONLEVEL
                                                )
                                                VALUES
                                                (
                                                    @SLADetailId,
                                                    @SLAId,
                                                    @SLAMetricID,
                                                    @SLACondition,
                                                    @ThresholdValue,
                                                    @ThresholdUnit,
                                                    @EscalationLevel
                                                );";

        public const string UPDATE_SLA= @"UPDATE MSLA
                                        SET
                                            SLACODE                = @SLACode,
                                            SLANAME                = @SLAName,
                                            DESCRIPTION            = @Description,
                                            MODIFIEDBYID           = @SLAModifiedById,
                                            MODIFIEDON             = @SLAModifiedOn,
                                            SORTORDER              = @SLASortOrder,
                                            STATUS                 = @SLAStatus,
                                            VERSION                = @SLAVersion,
                                            SOURCETYPE             = @SourceType,
                                            TENANTID               = @SLATenantId
                                        WHERE SLAID = @SLAId;";

        public const string UPDATE_SLA_DETAIL = @"UPDATE MSLADETAIL
                                                SET
                                                    SLAID           = @SLAId,
                                                    SLAMETRICID     = @SLAMetricID,
                                                    SLACONDITION    = @SLACondition,
                                                    THRESHOLDVALUE  = @ThresholdValue,
                                                    THREHOLDUNIT    = @ThresholdUnit,
                                                    ESCALATIONLEVEL = @EscalationLevel
                                                WHERE SLADETAILID = @SLADetailId;";

        public const string DELETE_SLA = @"DELETE FROM MSLA WHERE SLAID = @SLAid;";

        public const string DELETE_SLA_DETAIL = @"DELETE FROM MSLADETAIL WHERE SLAID = @SLAid;";


        public const string GET_SLA_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MSLA
                                                    WHERE
                                                        (
                                                            @SLAId <> -1 AND SLAID = @SLAId
                                                        )
                                                        AND SLAID <> @SLAId;";

        public const string GET_SELECTLIST_SLA = @"SELECT
                                                            S.SLAID               AS Id,
                                                            S.SLACODE             AS Code,
                                                            S.SLANAME             AS Name
                                                    FROM MSLA S;";
    }
}