using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GB5Shared.DTO.PubSub;

namespace AdminDAL.Query.MailList
{
    public class MailListQB
    {
        public const string GET_MAILLIST = @"SELECT 
                                ML.LISTID                     AS MailListId,
                                ML.LISTNAME                   AS MailListName,
                                ML.PARTICULARS                AS MailListParticulars,
                                ML.TYPE                       AS MailListType,
                                ML.PICKLISTID                 AS PicklistId,
                                PL.PICKLISTCODE               AS PicklistCode,
                                PL.PICKLISTNAME               AS PicklistName,
                                ML.WEBSERVICEID               AS WebServiceId,
                                WS.WEBSERVICENAME             AS WebServiceName,
                                ML.MAILIDS                    AS MailListMailIds,
                                ML.SORTORDER                  AS MailListSortOrder,
                                ML.STATUS                     AS MailListStatus,
                                ML.VERSION                    AS MailListVersion,
                                ML.CREATEDBYID                AS MailListCreatedById,
                                CU.USERNAME                   AS MailListCreatedByName,
                                ML.CREATEDON                  AS MailListCreatedOn,
                                ML.MODIFIEDBYID               AS MailListModifiedById,
                                MU.USERNAME                   AS MailListModifiedByName,
                                ML.MODIFIEDON                 AS MailListModifiedOn,
                                ML.SOURCETYPE                 AS MailListSourceType,
                                ML.TENANTID                   AS MailListTenantId,
                                ML.URIPARAMETERVALUE          AS MailListUriParameterValue
                            FROM MMAILLIST ML
                            LEFT JOIN MPICKLIST PL ON ML.PICKLISTID = PL.PICKLISTID
                            LEFT JOIN MWEBSERVICE WS ON ML.WEBSERVICEID = WS.WEBSERVICEID
                            LEFT JOIN MUSER CU ON ML.CREATEDBYID = CU.USERID
                            LEFT JOIN MUSER MU ON ML.MODIFIEDBYID = MU.USERID
                            WHERE ML.LISTID = @maillistid;";

        public const string SAVE_MAILLIST = @"INSERT INTO MMAILLIST (
                                                LISTID,
                                                LISTNAME,
                                                PARTICULARS,
                                                TYPE,
                                                PICKLISTID,
                                                WEBSERVICEID,
                                                MAILIDS,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON,
                                                SOURCETYPE,
                                                URIPARAMETERVALUE
                                            )
                                            VALUES (
                                                @MailListId,
                                                @MailListName,
                                                @MailListParticulars, 
                                                @MailListType,
                                                @PicklistId,         
                                                @WebServiceId,           
                                                @MailListMailIds, 
                                                @MailListSortOrder,
                                                @MailListStatus,    
                                                @MailListVersion, 
                                                @MailListCreatedById,         
                                                @MailListCreatedOn,            
                                                @MailListModifiedById,           
                                                @MailListModifiedOn,
                                                @MailListSourceType,
                                                @MailListUriParameterValue       
                                            );";

        public const string  GET_SELECTLIST_MAILLIST= @"SELECT 
                                            ML.LISTID         AS Id,
                                            ML.LISTNAME       AS Name,
                                            ML.TYPE           AS Type
                                        FROM MMAILLIST ML
                                        ORDER BY ML.LISTNAME;";

        public const string UPDATE_MAILLIST = @"UPDATE MMAILLIST
                                            SET
                                                LISTNAME           = @MailListName,
                                                PARTICULARS        = @MailListParticulars,
                                                TYPE               = @MailListType,
                                                PICKLISTID         = @PicklistId,
                                                WEBSERVICEID       = @WebServiceId,
                                                MAILIDS            = @MailListMailIds,
                                                SORTORDER          = @MailListSortOrder,
                                                STATUS             = @MailListStatus,
                                                VERSION            = @MailListVersion,
                                                MODIFIEDBYID       = @MailListModifiedById,
                                                MODIFIEDON         = @MailListModifiedOn,
                                                SOURCETYPE         = @MailListSourceType,
                                                URIPARAMETERVALUE  = @MailListUriParameterValue
                                            WHERE 
                                                LISTID = @MailListId;";

        public const string DELETE_MAILLIST = @"DELETE FROM MMAILLIST
                                                WHERE LISTID = @maillistid;";
    }
}