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

namespace FrameworkDAL.Query.EventChannel
{
    public class EventChannelQB
    {

        public const string GET_EVENTCHANNEL = @"SELECT
                                                EC.EVENTCHANNELID          AS EventChannelId,
                                                EC.EVENTCHANNELCODE        AS EventChannelCode,
                                                EC.EVENTCHANNELNAME        AS EventChannelName,

                                                EC.OWNERID                 AS OwnerId,
                                                U1.USERCODE                AS OwnerCode,
                                                U1.USERNAME                AS OwnerName,

                                                EC.ADMINISTRATORID         AS AdministratorId,
                                                U2.USERCODE                AS AdministratorCode,
                                                U2.USERNAME                AS AdministratorName,

                                                EC.SORTORDER               AS EventChannelSortOrder,
                                                EC.STATUS                  AS EventChannelStatus,
                                                EC.VERSION                 AS EventChannelVersion,
                                                EC.SOURCETYPE              AS EventChannelSourceType,

                                                EC.CREATEDBYID             AS EventChannelCreatedById,
                                                EC.CREATEDON               AS EventChannelCreatedOn,
                                                EC.MODIFIEDBYID            AS EventChannelModifiedById,
                                                EC.MODIFIEDON              AS EventChannelModifiedOn,

                                                C1.USERNAME                AS EventChannelCreatedByName,
                                                C2.USERNAME                AS EventChannelModifiedByName

                                            FROM MEVENTCHANNEL EC
                                            LEFT JOIN MUSER U1 ON EC.OWNERID = U1.USERID
                                            LEFT JOIN MUSER U2 ON EC.ADMINISTRATORID = U2.USERID
                                            LEFT JOIN MUSER C1 ON EC.CREATEDBYID = C1.USERID
                                            LEFT JOIN MUSER C2 ON EC.MODIFIEDBYID = C2.USERID
                                            WHERE EC.EVENTCHANNELID = @eventchannelid;";

        public const string SAVE_EVENTCHANNEL = @"INSERT INTO MEVENTCHANNEL
                                                (
                                                    EVENTCHANNELID,
                                                    EVENTCHANNELCODE,
                                                    EVENTCHANNELNAME,
                                                    OWNERID,
                                                    ADMINISTRATORID,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (
                                                    @EventChannelId,
                                                    @EventChannelCode,
                                                    @EventChannelName,
                                                    @OwnerId,
                                                    @AdministratorId,
                                                    @EventChannelSortOrder,
                                                    @EventChannelStatus,
                                                    @EventChannelVersion,
                                                    @EventChannelSourceType,
                                                    @EventChannelCreatedById,
                                                    @EventChannelCreatedOn,
                                                    @EventChannelModifiedById,
                                                    @EventChannelModifiedOn
                                                );";

        public const string UPDATE_EVENTCHANNEL = @"UPDATE MEVENTCHANNEL
                                                SET
                                                    EVENTCHANNELCODE   = @EventChannelCode,
                                                    EVENTCHANNELNAME   = @EventChannelName,
                                                    OWNERID            = @OwnerId,
                                                    ADMINISTRATORID    = @AdministratorId,
                                                    SORTORDER          = @EventChannelSortOrder,
                                                    STATUS             = @EventChannelStatus,
                                                    VERSION            = @EventChannelVersion,
                                                    SOURCETYPE         = @EventChannelSourceType,
                                                    MODIFIEDBYID       = @EventChannelModifiedById,
                                                    MODIFIEDON         = @EventChannelModifiedOn
                                                WHERE EVENTCHANNELID   = @EventChannelId;";

        public const string DELETE_EVENTCHANNEL = @"DELETE FROM MEVENTCHANNEL
                                                    WHERE EVENTCHANNELID = @eventchannelid;";

        public const string GET_SELECTLIST_EVENTCHANNEL = @"
                            WITH PagedEventChannel AS (
                            SELECT 
                            EC.EVENTCHANNELID AS Id,
                            EC.EVENTCHANNELCODE AS Code,
                            EC.EVENTCHANNELNAME AS Name,
                            ROW_NUMBER() OVER (ORDER BY EC.EVENTCHANNELID) AS RowNum
                            FROM MEVENTCHANNEL EC
                            )
                            SELECT
                            Id,
                            Code,
                            Name
                            FROM PagedEventChannel
                            WHERE  
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

    }
}