SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Nick Ashton

-- Create date: 15/04/2013

-- Description:updates tbl_directdebits ready for next months payment

--

-- =============================================

ALTER PROCEDURE [dbo].[failedDD]

-- Add the parameters for the stored procedure here

@processdate datetime, /-- date of pocess

@id int, /-- id of current record

@duplicate int /--add one or two duplicates

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Declare @period varchar(50)

--remove any previous duplicates

delete from tbl_directdebits where duplicateof=@id

--update unpaid

update tbl_directdebits set

dnotes='Unpaid ' +CAST(nextdue AS VARCHAR) + ',duplicate made' + dnotes

where id =@id and nextdue=@processdate

-- copy payment record to paid directdebits table (paid amount=0)

insert into tbl_paid_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, notes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel) select patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, 0, servicelevel from tbl_directdebits where id =@id

--update current record for next month

update tbl_directdebits set

nextdue=dateadd(month, 1, convert(datetime, nextdue, 101))

where id =@id

--make duplicates

if(@duplicate=1)

Begin

insert into tbl_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, duplicateof) select patientid, ref, recurperiod, 1, paytodent, smilecarefee, smilecarevat, 0, 0, 0, 0, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, 'Duplicate payment', transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, @id from tbl_directdebits where id= @id

End

if(@duplicate=2)

Begin

insert into tbl_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, duplicateof) select patientid, ref, recurperiod, 1, paytodent, smilecarefee, smilecarevat, 0, 0, 0, 0, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, 'Duplicate payment', transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, @id from tbl_directdebits where id= @id

insert into tbl_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, duplicateof) select patientid, ref, recurperiod, 1, paytodent, smilecarefee, smilecarevat, 0, 0, 0, 0, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, 'Duplicate payment', transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, @id from tbl_directdebits where id= @id

End