Gift Aid - seeking new declarations in bulk

*Gasp*

That’s super-smart. What a brilliant idea! You are a very clever cookie, Mark Ridley. I shall investigate immediately!

C.//
X.

From: Tessitura Finance Forum [mailto:forums-finance@tessituranetwork.com] On Behalf Of Mark Ridley
Sent: 23 March 2015 08:05
To: Chris Campbell
Subject: RE: [Tessitura Finance Forum] Gift Aid - seeking new declarations in bulk

Hi Chris

Our Development department have done this using Wordfly emails, as I have amended our LP_UPDATE_EMAIL_PROMOTION to look for click throughs on two urls (a yes and no) to quickly add a gift aid declaration to their accounts. As Wordfly passes in customer number etc mean customer doesn't need to login, the pages are static html as there is no need for code on the page and so Wordfly and the SP do all the work. Also add a check in to see if a customer was changign their declaration status and send an email to the dev team to investigate.

If @response_code = 4

Begin  

   If @url in ( 'http://www.nationaltheatre.org.uk/yestogiftaid','http://www.nationaltheatre.org.uk/notogiftaid')

   Begin

      if @url='http://www.nationaltheatre.org.uk/yestogiftaid'

      begin

         select @declare='Y'

      end

      else

      begin

         select @declare='N'

      end

      if not exists (select 1 from T_GIFTAID_DECLARATION (NOLOCK) where customer_no=@customer_no)

      begin

         Execute @ga_decl_no = AP_Get_NextID_function 'GD'    

         insert into T_GIFTAID_DECLARATION(ga_declaration_no, customer_no,giftaid_type,eligible_ind,contact_method,document_status,recd_dt,start_dt, ineligible_reason)

         select @ga_decl_no,@customer_no,1,@declare,3,6,getdate(),dateadd(year,-4,GETDATE()), case when @declare='N' then 5 else 0 end

      end
     else

      begin

         if exists (select 1 from T_GIFTAID_DECLARATION (NOLOCK) where customer_no=@customer_no and eligible_ind<>@declare)

         begin

            select @subject='Email Gift Aid Declaration Eligible (' + @declare +') for customer '   + convert(varchar(10), @customer_no),

            @body = 'Customer '   + convert(varchar(10), @customer_no) + ' has submitted a second declaration but this time set Eligible to ' + @declare +'.

Please check this account.

Thank you'

            exec msdb.dbo.sp_send_dbmail 

            @recipients='devteam',

            @subject = @subject,

            @body = @body,

            @body_format = 'TEXT'

         end

      end

   End

End

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Finance Forum. You may reply to this message to post to the Finance forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

*Gasp*

That’s super-smart. What a brilliant idea! You are a very clever cookie, Mark Ridley. I shall investigate immediately!

C.//
X.

From: Tessitura Finance Forum [mailto:forums-finance@tessituranetwork.com] On Behalf Of Mark Ridley
Sent: 23 March 2015 08:05
To: Chris Campbell
Subject: RE: [Tessitura Finance Forum] Gift Aid - seeking new declarations in bulk

Hi Chris

Our Development department have done this using Wordfly emails, as I have amended our LP_UPDATE_EMAIL_PROMOTION to look for click throughs on two urls (a yes and no) to quickly add a gift aid declaration to their accounts. As Wordfly passes in customer number etc mean customer doesn't need to login, the pages are static html as there is no need for code on the page and so Wordfly and the SP do all the work. Also add a check in to see if a customer was changign their declaration status and send an email to the dev team to investigate.

If @response_code = 4

Begin  

   If @url in ( 'http://www.nationaltheatre.org.uk/yestogiftaid','http://www.nationaltheatre.org.uk/notogiftaid')

   Begin

      if @url='http://www.nationaltheatre.org.uk/yestogiftaid'

      begin

         select @declare='Y'

      end

      else

      begin

         select @declare='N'

      end

      if not exists (select 1 from T_GIFTAID_DECLARATION (NOLOCK) where customer_no=@customer_no)

      begin

         Execute @ga_decl_no = AP_Get_NextID_function 'GD'    

         insert into T_GIFTAID_DECLARATION(ga_declaration_no, customer_no,giftaid_type,eligible_ind,contact_method,document_status,recd_dt,start_dt, ineligible_reason)

         select @ga_decl_no,@customer_no,1,@declare,3,6,getdate(),dateadd(year,-4,GETDATE()), case when @declare='N' then 5 else 0 end

      end
     else

      begin

         if exists (select 1 from T_GIFTAID_DECLARATION (NOLOCK) where customer_no=@customer_no and eligible_ind<>@declare)

         begin

            select @subject='Email Gift Aid Declaration Eligible (' + @declare +') for customer '   + convert(varchar(10), @customer_no),

            @body = 'Customer '   + convert(varchar(10), @customer_no) + ' has submitted a second declaration but this time set Eligible to ' + @declare +'.

Please check this account.

Thank you'

            exec msdb.dbo.sp_send_dbmail 

            @recipients='devteam',

            @subject = @subject,

            @body = @body,

            @body_format = 'TEXT'

         end

      end

   End

End

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Finance Forum. You may reply to this message to post to the Finance forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!