USP_CONSTITUENCYDATERANGE_REFRESHPATRONCONSTITUENCY

CREATE procedure dbo.USP_CONSTITUENCYDATERANGE_REFRESHPATRONCONSTITUENCY
(
    @CONSTITUENTID uniqueidentifier = null,
    @CONSTITUENCYREFRESHPROCESSID uniqueidentifier = null,
    @RECORDCOUNT int = 0 output
)
as
begin
    set nocount on;

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();

    declare @PATRONDEFINITIONID uniqueidentifier = 'A843B859-4C6B-445B-97F3-179582E270A5';

    begin try
        if @CONSTITUENTID is not null and @CONSTITUENCYREFRESHPROCESSID is not null
        begin
            raiserror('ERR_REFRESH_CONSTITUENTANDSELECTION : A constituency refresh cannot be run with both a specific constituent and a selection.', 13, 1);
        end

        if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                REFRESHDATE
            )
            select
                newid(),
                SALESORDER.CONSTITUENTID,
                @PATRONDEFINITIONID,
                cast(min(SALESORDER.TRANSACTIONDATE) as date),
                @CURRENTDATE
            from
                dbo.SALESORDERITEM
                inner join dbo.SALESORDER
                    on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = SALESORDER.CONSTITUENTID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null
                and SALESORDER.STATUSCODE <> 0                -- Orders that aren't Pending

                and (
                    SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                    or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                )
                and SALESORDER.CONSTITUENTID is not null
            group by
                SALESORDER.CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = PATRON.DATEFROM,
                REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        SALESORDER.CONSTITUENTID,
                        cast(min(SALESORDER.TRANSACTIONDATE) as date) as DATEFROM
                    from
                        dbo.SALESORDERITEM
                        inner join dbo.SALESORDER
                            on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    where
                        SALESORDER.STATUSCODE <> 0                    -- Orders that aren't Pending

                        and (
                            SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                            or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                        )
                    group by
                        SALESORDER.CONSTITUENTID
                ) PATRON
                    on PATRON.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        SALESORDER.CONSTITUENTID
                    from
                        dbo.SALESORDERITEM
                        inner join dbo.SALESORDER
                            on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    where
                        SALESORDER.STATUSCODE <> 0                    -- Orders that aren't Pending

                        and (
                            SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                            or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                        )
                ) PATRON
                    on PATRON.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID
                and PATRON.CONSTITUENTID is null;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                REFRESHDATE
            )
            select
                newid(),
                SALESORDER.CONSTITUENTID,
                @PATRONDEFINITIONID,
                cast(min(SALESORDER.TRANSACTIONDATE) as date),
                @CURRENTDATE
            from
                dbo.SALESORDERITEM
                inner join dbo.SALESORDER
                    on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = SALESORDER.CONSTITUENTID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID
            where
                SALESORDER.CONSTITUENTID = @CONSTITUENTID
                and CONSTITUENCYDATERANGE.ID is null
                and SALESORDER.STATUSCODE <> 0                -- Orders that aren't Pending

                and (
                    SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                    or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                )
            group by
                SALESORDER.CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = PATRON.DATEFROM,
                REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        SALESORDER.CONSTITUENTID,
                        cast(min(SALESORDER.TRANSACTIONDATE) as date) as DATEFROM
                    from
                        dbo.SALESORDERITEM
                        inner join dbo.SALESORDER
                            on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    where
                        SALESORDER.STATUSCODE <> 0                    -- Orders that aren't Pending

                        and (
                            SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                            or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                        )
                    group by
                        SALESORDER.CONSTITUENTID
                ) PATRON
                    on PATRON.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                PATRON.CONSTITUENTID = @CONSTITUENTID
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        SALESORDER.CONSTITUENTID
                    from
                        dbo.SALESORDERITEM
                        inner join dbo.SALESORDER
                            on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    where
                        SALESORDER.STATUSCODE <> 0                    -- Orders that aren't Pending

                        and (
                            SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                            or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                        )
                ) PATRON
                    on PATRON.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID
                and PATRON.CONSTITUENTID is null;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                REFRESHDATE
            )
            select
                newid(),
                SALESORDER.CONSTITUENTID,
                @PATRONDEFINITIONID,
                cast(min(SALESORDER.TRANSACTIONDATE) as date),
                @CURRENTDATE
            from
                dbo.SALESORDERITEM
                inner join dbo.SALESORDER
                    on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = SALESORDER.CONSTITUENTID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = SALESORDER.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.ID is null
                and SALESORDER.STATUSCODE <> 0                -- Orders that aren't Pending

                and (
                    SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                    or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                )
            group by
                SALESORDER.CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = PATRON.DATEFROM,
                REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        SALESORDER.CONSTITUENTID,
                        cast(min(SALESORDER.TRANSACTIONDATE) as date) as DATEFROM
                    from
                        dbo.SALESORDERITEM
                        inner join dbo.SALESORDER
                            on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    where
                        SALESORDER.STATUSCODE <> 0                    -- Orders that aren't Pending

                        and (
                            SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                            or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                        )
                    group by
                        SALESORDER.CONSTITUENTID
                ) PATRON
                    on PATRON.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        SALESORDER.CONSTITUENTID
                    from
                        dbo.SALESORDERITEM
                        inner join dbo.SALESORDER
                            on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    where
                        SALESORDER.STATUSCODE <> 0                    -- Orders that aren't Pending

                        and (
                            SALESORDER.SALESMETHODTYPECODE = 3        -- Group Sales

                            or SALESORDERITEM.TYPECODE in (0, 14)    -- Ticket or Merchandise

                        )
                ) PATRON
                    on PATRON.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @PATRONDEFINITIONID
                and PATRON.CONSTITUENTID is null;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end