USP_AMPROEXPORT_GETREGISTRANTDATA

            CREATE procedure dbo.USP_AMPROEXPORT_GETREGISTRANTDATA
            (
                @AUCTIONEVENTID uniqueidentifier
            )
            as
            set nocount on;

            declare @SEQUENCEIDOFFSET int = 9999999;

            with [REGISTRANT_CTE]
            as
            (
                select 
                    REGISTRANT.ID,
                    REGISTRANT.SEQUENCEID - @SEQUENCEIDOFFSET as [REGISTRANTSEQUENCEID],
                    REGISTRANT.LOOKUPID as [LOOKUPID],
                    (select coalesce(sum(AMOUNT),0) from dbo.REGISTRANTREGISTRATION where REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID) as [REGISTRANTAMOUNT],
                    (select coalesce(sum(RECEIPTAMOUNT),0) from REGISTRANTREGISTRATION where REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID) as [REGISTRANTRECEIPTAMOUNT],
                    REGISTRANT.CONSTITUENTID,
                    REGISTRANT.EVENTID
                from dbo.REGISTRANT 
                where REGISTRANT.EVENTID = @AUCTIONEVENTID
            )
            select 
                REGISTRANT.REGISTRANTSEQUENCEID,
                REGISTRANT.ID as [REGISTRANTID],
                REGISTRANT.LOOKUPID as [REGISTRANTLOOKUPID],
                coalesce((select top 1 EVENTSEATINGSEAT.NAME from dbo.EVENTSEATINGSEAT where EVENTSEATINGSEAT.REGISTRANTID = REGISTRANT.ID order by SEQUENCE asc, DATEADDED desc), '') as [REGISTRANTSEAT],

                CONSTITUENT.SEQUENCEID - @SEQUENCEIDOFFSET as [CONSTITUENTSEQUENCEID],
                CONSTITUENT.LOOKUPID as [CONSTITUENTLOOKUPID],
                CONSTITUENT.ISORGANIZATION as [CONSTITUENTISORGANIZATION],
                (select DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID) as [TITLE],
                CONSTITUENT.FIRSTNAME,
                CONSTITUENT.KEYNAME,
                case 
                    when NAMEFORMAT.ID is null then CONSTITUENT.NAME 
                    else NAMEFORMAT.FORMATTEDNAME 
                end as [PRIMARYSALUTATION],

                coalesce((select DESCRIPTION from ADDRESSTYPECODE where ADDRESSTYPECODE.ID = [PRIMARYADDRESS].ADDRESSTYPECODEID), '') as [PRIMARYADDRESSTYPE],
                coalesce([PRIMARYADDRESS].ADDRESSBLOCK, '') as [PRIMARYADDRESSBLOCK],
                coalesce([PRIMARYADDRESS].CITY, '') as [PRIMARYADDRESSCITY],
                coalesce((select STATE.ABBREVIATION from dbo.STATE where STATE.ID = [PRIMARYADDRESS].STATEID), '') as [PRIMARYADDRESSSTATE],
                coalesce(PRIMARYADDRESS.POSTCODE, '') as [PRIMARYADDRESSPOSTCODE],
                coalesce((select COUNTRY.DESCRIPTION from dbo.COUNTRY where COUNTRY.ID = [PRIMARYADDRESS].COUNTRYID), '') as [PRIMARYADDRESSCOUNTRY],

                coalesce((select DESCRIPTION from dbo.PHONETYPECODE where PHONETYPECODE.ID = [PRIMARYPHONE].PHONETYPECODEID), '') as [PRIMARYPHONETYPE],
                coalesce([PRIMARYPHONE].NUMBER, '') as [PRIMARYPHONE],

                coalesce((select DESCRIPTION from dbo.EMAILADDRESSTYPECODE where EMAILADDRESSTYPECODE.ID = [PRIMARYEMAIL].EMAILADDRESSTYPECODEID), '') as [PRIMARYEMAILTYPE],
                coalesce([PRIMARYEMAIL].EMAILADDRESS, '') as [PRIMARYEMAIL],


                case 
                    when REGISTRANT.REGISTRANTAMOUNT > 0 then 
                        case 
                            when (select count(ID) from dbo.EVENTREGISTRANTPAYMENT as [PAY] where [PAY].REGISTRANTID = REGISTRANT.ID) = 0 then 
                                cast(0 as bit)
                            else 
                                cast(1 as bit)
                        end
                    else
                        cast(1 as bit)
                end as [ISPAID],
                case 
                    when REGISTRANT.REGISTRANTAMOUNT > 0 then 
                        (
                            select top 1 REVENUE.DATE 
                            from dbo.REVENUE 
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID 
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID 
                            where EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
                            order by REVENUE.DATEADDED asc
                        ) 
                    else
                        null
                end as [PAIDDATE],
                REGISTRANT.REGISTRANTAMOUNT as [REGISTRATIONAMOUNT],
                REGISTRANT.REGISTRANTRECEIPTAMOUNT as [REGISTRATIONRECEIPTAMOUNT],
                EVENTPRICE.NAME as [REGISTRATIONOPTION]
            from [REGISTRANT_CTE] as [REGISTRANT]
            inner join dbo.EVENTAUCTION on EVENTAUCTION.ID = REGISTRANT.EVENTID
            inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
            left join dbo.ADDRESS as [PRIMARYADDRESS] on [PRIMARYADDRESS].CONSTITUENTID = CONSTITUENT.ID and [PRIMARYADDRESS].ISPRIMARY = 1
            left join dbo.PHONE as [PRIMARYPHONE] on [PRIMARYPHONE].CONSTITUENTID = CONSTITUENT.ID and [PRIMARYPHONE].ISPRIMARY = 1
            left join dbo.EMAILADDRESS as [PRIMARYEMAIL] on [PRIMARYEMAIL].CONSTITUENTID = CONSTITUENT.ID and [PRIMARYEMAIL].ISPRIMARY = 1
            left join dbo.NAMEFORMAT on NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1
            left join dbo.REGISTRANTREGISTRATION on REGISTRANT.ID = REGISTRANTREGISTRATION.REGISTRANTID
            left join dbo.EVENTPRICE on EVENTPRICE.ID = REGISTRANTREGISTRATION.EVENTPRICEID