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

namespace MMDAL.Query.CertificationType
{
    public class CertificationTypeQB
    {
        public const string GET_CERTIFICATIONTYPE = @"SELECT
                                                        CT.CERTIFICATIONTYPEID        AS CertificationTypeId,
                                                        CT.CERTIFICATIONTYPECODE      AS CertificationTypeCode,
                                                        CT.CERTIFICATIONTYPENAME      AS CertificationTypeName,
                                                        CT.REMARKS                    AS CertificationTypeRemarks,
                                                        CT.ISVALIDITY                 AS CertificationTypeIsValidity,
                                                        CT.SORTORDER                  AS CertificationTypeSortOrder,
                                                        CT.STATUS                     AS CertificationTypeStatus,
                                                        CT.VERSION                    AS CertificationTypeVersion,
                                                        CT.SOURCETYPE                 AS CertificationTypeSourceType,
                                                        CT.CREATEDBYID                AS CertificationTypeCreatedById,
                                                        CT.CREATEDON                  AS CertificationTypeCreatedOn,
                                                        CU.USERNAME                  AS CertificationTypeCreatedByName,
                                                        CT.MODIFIEDBYID               AS CertificationTypeModifiedById,
                                                        CT.MODIFIEDON                 AS CertificationTypeModifiedOn,
                                                        MU.USERNAME                  AS CertificationTypeModifiedByName        
                                                    FROM MCERTIFICATIONTYPE CT
                                                    LEFT JOIN MUSER CU ON CU.USERID = CT.CREATEDBYID
                                                    LEFT JOIN MUSER MU ON MU.USERID = CT.MODIFIEDBYID
                                                    WHERE CT.CERTIFICATIONTYPEID =  @certificationtypeid;";

        public const string SAVE_CERTIFICATIONTYPE = @"INSERT INTO MCERTIFICATIONTYPE
                                                    (
                                                        CERTIFICATIONTYPEID,
                                                        CERTIFICATIONTYPECODE,
                                                        CERTIFICATIONTYPENAME,
                                                        REMARKS,
                                                        ISVALIDITY,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        SOURCETYPE                                                    
                                                    )
                                                    VALUES
                                                    (
                                                        @CertificationTypeId,
                                                        @CertificationTypeCode,
                                                        @CertificationTypeName,
                                                        @CertificationTypeRemarks,
                                                        @CertificationTypeIsValidity,
                                                        @CertificationTypeSortOrder,
                                                        @CertificationTypeStatus,
                                                        @CertificationTypeVersion,
                                                        @CertificationTypeCreatedById,
                                                        @CertificationTypeCreatedOn,
                                                        @CertificationTypeModifiedById,
                                                        @CertificationTypeModifiedOn,
                                                        @CertificationTypeSourceType
                                                    );";

        public const string UPDATE_CERTIFICATIONTYPE = @"UPDATE MCERTIFICATIONTYPE
                                                            SET
                                                                CERTIFICATIONTYPECODE   = @CertificationTypeCode,
                                                                CERTIFICATIONTYPENAME   = @CertificationTypeName,
                                                                REMARKS                 = @CertificationTypeRemarks,
                                                                ISVALIDITY              = @CertificationTypeIsValidity,
                                                                SORTORDER               = @CertificationTypeSortOrder,
                                                                STATUS                  = @CertificationTypeStatus,
                                                                VERSION                 = @CertificationTypeVersion,
                                                                MODIFIEDBYID            = @CertificationTypeModifiedById,
                                                                MODIFIEDON              = @CertificationTypeModifiedOn,
                                                                SOURCETYPE              = @CertificationTypeSourceType
                                                            WHERE CERTIFICATIONTYPEID = @CertificationTypeId;";

        public const string DELETE_CERTIFICATIONTYPE = @"DELETE FROM MCERTIFICATIONTYPE
                                                        WHERE CERTIFICATIONTYPEID = @certificationtypeid;";

        public const string GET_SELECTLIST_CERTIFICATIONTYPE = @"WITH PagedCertificationType AS (
                                                        SELECT
                                                            CT.CERTIFICATIONTYPEID           AS Id,
                                                            CT.CERTIFICATIONTYPECODE         AS Code,
                                                            CT.CERTIFICATIONTYPENAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY CT.CERTIFICATIONTYPENAME) AS RowNum
                                                        FROM 
                                                            MCERTIFICATIONTYPE CT                                                   )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedCertificationType
                                                   WHERE(@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult)";

    }
}