GO /****** Object: StoredProcedure [dbo].[sp_ViewGCStatus] Script Date: 04/22/2016 14:03:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_ViewGCStatus] @GCProjectID as int as --select gcproject.calllistid,GCCallSchedule.personname, substring(USER_DATA_2,0,CHARINDEX('.',USER_DATA_2)) USER_DATA_2, PHONE_NUMBER, gcproject.transferphone, --convert(varchar(20),DATE_TIME_STAMP)DATE_TIME_STAMP,CALL_RESULT, --STATUS from GCPROJECT join GCCallSchedule on gcproject.gcprojectid=GCCallSchedule.gcprojectid join Call_List_History on GCCallSchedule.GCCALLID=Call_List_History.call_id --WHERE (left(SourceKey,1)='G' AND convert(varchar(12),gcproject.GCProjectID) = right(SourceKey,len(sourcekey)-1)) --and gcproject.gcprojectid= @GCProjectID and Status in ('D0','D3','M','R','X','H','T' ) --status <>'R' --order by USER_DATA_2 --Add by samir as per we disc select gcproject.calllistid,GCCallSchedule.personname, substring(USER_DATA_2,0,CHARINDEX('.',USER_DATA_2)) USER_DATA_2, PHONE_NUMBER, gcproject.transferphone, convert(varchar(20),DATE_TIME_STAMP)DATE_TIME_STAMP,CALL_RESULT,STATUS from GCPROJECT , GCCallSchedule,call_list where gcproject.gcprojectid=GCCallSchedule.GCprojectid and call_list.call_id in ( select gccallid from GCCallSchedule where gcprojectid=@GCProjectID ) or call_list.call_id in ( select gccallid from GCCallSchedulehistory where gcprojectid=@GCProjectID ) and gcproject.gcprojectid= @GCProjectID GO --rahul-- GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --This procedure inserts data in appointment master --Altered by swati B to add changes for payment notification (add param @CallTypeID) --Altered by swati B to add changes for WF based notification (add column in AppointmentMaster for WFStep and IsWF) ALTER PROCEDURE [dbo].[sp_insertAppointmentMaster] ( @DoctorID int, @PatientId int, @BooKingDate datetime, @AppointmentDate datetime,@RemindBeforeNDays tinyint, @StartTime DateTime, @EndTime Datetime, @Reason varchar(200), @OtherInfo varchar(200) ,@SubCallTypeID int, @ScheduleThisCall bit, @ispremimum_feature int, @CallTypeID int, --Added by swati B for payment notification @ApptID int output ---scope identitychanges by rahul ) AS --declare @ApptID int declare @IsWF as int if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID) begin if @ispremimum_feature=1 set @IsWF=1 else set @IsWF=0 end else set @IsWF=0 --To insert into appointment master Insert into AppointmentMaster (DoctorID , PatientID ,BookingDate ,AppointmentDate ,RemindBeforeNDays ,StartTime ,EndTime ,Reason , OtherInfo , Status ,StatusChangeDate ,StatusChangeReason ,SubCallTypeID,RecordStatus,CrDate,WFStep,iswf,IsPremium_Feature) values (@DoctorID ,@PatientId ,@BooKingDate ,@AppointmentDate ,@RemindBeforeNDays ,@StartTime ,@EndTime ,@Reason,@OtherInfo, 0,getDate(),'Appoinment Booked',@SubCallTypeID,0,getdate(),1,@IsWF,@ispremimum_feature) print 'Appointment Booked' --To get id of inserted appt select @ApptID=@@Identity --Get appt date declare @AppointmentDateTime datetime set @AppointmentDateTime=cast(convert(varchar(12), @AppointmentDate) +' '+ convert(varchar(12),@StartTime ,108 ) as datetime) --If scheduleThisCall=1 then call call scheduling procedure if @ScheduleThisCall = 1 exec sp_scheduleAppointment @ApptID,@DoctorID,@PatientId ,@AppointmentDateTime,@RemindBeforeNDays,@CallTypeID --replace by folowing procedure --exec sp_scheduleARSPRRCall 1 ,@ApptID,@DoctorID,@PatientId ,@AppointmentDateTime,@RemindBeforeNDays ,'' --last parameter is pharmacyphone which is required only for PRR*/ return @ApptID Go --end rahul--- Go -----------------Swati start--- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_insertToCallList') DROP PROCEDURE sp_insertToCallList GO /****** Object: StoredProcedure [dbo].[sp_insertToCallList] Script Date: 04/22/16 2:57:36 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- REVISED FOR NOT PLACING CALLS FOR DISABLED , DELETED USERS - 27 JUNE 06 -- Purpose: To insert record into Call_List table from ScheduledCalls table --Altered by Swati B to Add Payment notifications --Altered by swati to add changes for notification type=text (pick text message from Text_message table replace the variables with actual vaiables ---and set to set @filenm=@Message and insert into user_data_5 coloumn of call_list table ---Altered by Swati B to Add Workflow based notification ---Altered by Swati B to Add @DoctorEmail,@RecipientEmail for email notification 18/03/2016 ---Altered by Swati B to Add encrypt param of payment 19/03/2016 ---Altered by Swati B to Comment encryption param of payment 28/03/2016 since encrypted format is not sent through plumvoice --Altered by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply End on 29-03-2016 ---Rolled create procedure [dbo].[sp_insertToCallList] as Begin declare @process as int=0,@UserId as int,@UserName as varchar(100),@AudioFolder as varchar(256),@PatientID as int, @CallId as int,@Service as tinyint,@SourceKey as varchar(50),@TelNo as varchar(12),@CallDateTime as datetime, @Priority as smallint,@CallType as int,@SubType as tinyint,@patientnm as varchar(100),@AppDateTime as datetime, @DrOfficePh as varchar(12),@Status as varchar(3),@filenm as varchar(1000),@DigitalId as int,@DrFileName as varchar(51) declare @langID as char(2), @ErrorID as integer, @PersonName as varchar(100),@PharmacyPh as varchar(12),@CallerId as varchar(12) declare @TranferPh as varchar(12),@CallListId smallint declare @fullRecipientNm as varchar(100) Declare @NotificationPayment as varchar(3)----Added by Swati B for Payment notification Declare @WFStep as int ----Added by Swati B for WF based notification-- 9/2/15 dev1 declare @NotificationType as smallint,@BefAfter int declare @DoctorEmail as varchar(200),@RecipientEmail as varchar(200) ---Added by Swati B to Add @DoctorEmail,@RecipientEmail for email notification 18/03/2016 declare @encrParam as NVARCHAR(100) -- Added by Swati B to Add encrypt param of payment 19/03/2016 declare @Tempid3 int declare @InvoiceId as varchar(25)='' set @Status='U' declare @date1 as datetime set @date1=dateadd(minute,5,getdate()) set @NotificationPayment='N' declare @IsWF int=0 declare @appid int declare @IsPremium_Feature int declare @WFMessageTxt as varchar(200)='' DECLARE ScheduledCalls_Cursor CURSOR static FOR select CallType,CallID,Service,SourceKey,TelNo,CallDateTime,Priority,SubType,PersonName,WFStep,BefAfter from ScheduledCalls where CallDateTime < @date1 OPEN ScheduledCalls_Cursor FETCH NEXT FROM ScheduledCalls_Cursor into @CallType,@CallId,@Service,@SourceKey,@TelNo,@CallDateTime,@Priority,@SubType,@PersonName,@WFStep,@BefAfter WHILE @@FETCH_STATUS = 0 BEGIN -- print @Service -- selecting userid is done for getting processid. initially select userid statement for 3 services were not here. but were inside serive if @Service=1 select @UserId=DoctorID,@PatientID=a.PatientID,@patientnm=p.Fname,@fullRecipientNm=p.Fname+' '+p.MI+' '+p.Lname,@AppDateTime=AppointmentDateTime, @NotificationType= p.NotificationType ,@RecipientEmail=p.email from ARSCallSchedule a, PatientMaster p where a.PatientID=p.PatientID and ARSCallID=@CallId select @DrOfficePh=OfficePhone,@DrFileName=FirstNM from ARSUser_mst where UserId=@UserId select @DoctorEmail=isnull(email,'') from [LoginInfo] where UserId=@UserId select @IsPremium_Feature=IsPremium_Feature from arscallschedule s join AppointmentMaster a on a.appid=s.apptid where s.arscallid=@CallId ------added by swati for workflow start if @NotificationType<>2 and @IsPremium_Feature=1 begin declare @WFID as int,@NotificationId int if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@UserId and NotificationId=@CallType) begin set @IsWF=1 print '@WFstep' print @WFstep select @WFID=WFID from NotificationWorkFlow where ProviderId=@UserId and NotificationId=@CallType if @WFstep=1 begin declare @Tempid1 int select @Tempid1=TemplateId1,@NotificationType=Preference1 from NotificationWorkFlow where WFID=@WFID print '@Tempid1' print @Tempid1 print '@NotificationType from WF' print @NotificationType if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid1 set @process=21 end else begin set @process =@Tempid1 ---24 End end if @WFstep=2 begin declare @Tempid2 int select @Tempid2=TemplateId2,@NotificationType=Preference2 from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid2 set @process=21 end else begin set @process =@Tempid2 End end if @WFstep=3 and @BefAfter=1---before message in step 3 begin print '@BefAfter message' print @NotificationType select @Tempid3=[MsgIdBeforeDueDt],@NotificationType=Preference3,@NotificationId=NotificationId from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid3 if @NotificationId=5 set @process=21 else set @process=23 end else begin if @NotificationId=5 set @process=@Tempid3 else set @process=@Tempid3 End end if @WFstep=3 and @BefAfter=2---After message in step 3 begin select @Tempid3=[MsgIdAfterDueDt],@NotificationType=Preference3 from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid3 if @NotificationId=5 set @process=21 else set @process=23 end else begin if @NotificationId=5 set @process=@Tempid3 else set @process=@Tempid3 End end end ------added by swati for workflow end End ---Added by Swati B for Payment notification -Begin ---Check if notification is for payment if exists(select P.paymentid from PaymentDetails P join AppointmentMaster AP on AP.APPId=P.APPID join ARSCallSchedule AC on AC.APPtID=AP.APPId join ScheduledCalls S on S.CallID=AC.ARSCallID where S.CallID=@CallId) begin---if yes enter in this block print 'in payment block' set @NotificationPayment ='Y' declare @PaymentID int set @PaymentID = (select P.paymentid from ARSCallSchedule AC join AppointmentMaster AP on AP.Appid=AC.APPtID join PaymentDetails P on P.APPID=AP.APPId join ScheduledCalls S on S.CallID=AC.ARSCallID where S.CallID=@CallId) declare @Message as varchar(1000),@Param as varchar(500),@Amount as decimal,@ProviderNM varchar(25),@RecipientNM varchar(25),@PDueDt datetime if @NotificationType='0' begin select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @Message=Message_Text from TextTemplate S where s.NotificationType='voice' and s.MessageType=0 and Eventtype='Payment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @Amount=PM.Amount,@InvoiceId=PM.InvoiceId,@PDueDt=PM.PaymentDueDate,@ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM, @RecipientNM= R.Fname from PaymentDetails PM join AppointmentMaster A on PM.APPId =A.Appid join PatientMaster R on A.Patientid=R.Patientid join ARSUser_mst P on A.Doctorid=P.userID where PM.PaymentId=@PaymentID set @Param= convert(varchar(50), isnull(@PaymentID,0)) set @encrParam= dbo.ufn_EncryptString(@Param) --commented by swati B because encrypted message is not triggering from plumvoice --set @encrParam='Id='+ @encrParam --Added by swati B because encrypted message is not triggering from plumvoice set @encrParam='Id='+ @Param print @encrParam print @Message print @Amount print @InvoiceId set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@Amount,0))) set @Message= (SELECT REPLACE(@Message,'',isnull(@InvoiceId,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@PDueDt),101),''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) set @Message= (SELECT REPLACE(@Message,'',@encrParam)) print @Message end else if @NotificationType='1' begin --Prepare Message field for call list table for notification type Text (ITR) -- declare @Message as varchar(1000),@Amount as decimal,@InvoiceId as varchar(25),@ProviderNM varchar(25),@RecipientNM varchar(25) select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @Message=Message_Text from TextTemplate S where s.NotificationType='text' and s.MessageType=0 and Eventtype='Payment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @Amount=PM.Amount,@InvoiceId=PM.InvoiceId,@PDueDt=PM.PaymentDueDate,@ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM, @RecipientNM= R.Fname from PaymentDetails PM join AppointmentMaster A on PM.APPId =A.Appid join PatientMaster R on A.Patientid=R.Patientid join ARSUser_mst P on A.Doctorid=P.userID where PM.PaymentId=@PaymentID set @Param= convert(varchar(50), isnull(@PaymentID,0)) set @encrParam= dbo.ufn_EncryptString(@Param) --commented by swati B because encrypted message is not triggering from plumvoice --set @encrParam='Id='+ @encrParam --Added by swati B because encrypted message is not triggering from plumvoice set @encrParam='Id='+ @Param print @encrParam print @Message print @Amount print @InvoiceId set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@Amount,0))) set @Message= (SELECT REPLACE(@Message,'',isnull(@InvoiceId,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@PDueDt),101),''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) set @Message= (SELECT REPLACE(@Message,'',@encrParam)) print @Message end end ---Added by Swati B for Payment notification -End else if @Service=2 select @UserId=DoctorID,@PatientID=PatientID,@PharmacyPh=PharmacyPhoneNo from PRRCallSchedule where PrescriptionCallID=@CallId else if @Service =3 -- ***** PRASAD - Changed this Select Statement's Where Clause 12/31/2006 --select @UserId=gp.UserId,@TranferPh=gs.TransferNo,@filenm=gp.MessageFileName,@CallListId=gp.CallListId , @langID=gp.TransferLanguageID, @DrFileName=gp.ProviderName, @CallerId=gp.CallerIDNumber from GCCallSchedule gs,GCProject gp where gp.GCProjectId --=(select GCProjectID from GCCallSchedule where GCCallID=@CallId) select @UserId=gp.UserId,@TranferPh=gs.TransferNo,@filenm=gp.MessageFileName, @NotificationType=gp.NotificationType, @CallListId=gp.CallListId , @langID=gp.TransferLanguageID, @DrFileName=gp.ProviderName, @CallerId=gp.CallerIDNumber from GCCallSchedule gs,GCProject gp WHERE gp.GCprojectID = gs.GCprojectID AND gp.GCProjectId=(select GCProjectID from GCCallSchedule where GCCallID=@CallId) if (@process=0) ---------------------------Added by swati B to check if Process exist in case of WF -------------start -- get process id from processassignment table for @UserId begin if (select ProcessId from ProcessAssignment where UserId=@UserId and Service =@Service and CallType=@CallType and NotificationType=@NotificationType)is not null begin print 'Process exist' SELECT @process= ProcessId FROM ProcessAssignment WHERE (EffectiveFrom = (SELECT MAX(EffectiveFrom) FROM ProcessAssignment WHERE UserId = @UserId AND Service = @Service AND CallType = @CallType)) AND (UserId = @UserId) AND (Service = @Service) AND (CallType = @CallType) AND (NotificationType=@NotificationType) end else begin -- if specific processid is not found in processassignment table then Decide which process to run , using service & CallType as Input --Added by swati for @NotificationType=0 and 1 if @Service=1 and @CallType=5 and @NotificationType=0 --changed as told by sudip because he has changed the vxml set @process=20 --set @process=19 else if @Service=1 and @CallType=5 and @NotificationType=1 set @process=21 else if @Service=1 and @CallType=1 set @process=1 else if @Service=1 and @CallType=2 set @process=2 else if @Service=1 and @CallType=3 set @process=3 else if @Service=1 and @CallType=4 set @process=8 else if @Service=2 and @CallType=1 set @process=4 else if @Service=2 and @CallType=2 set @Process=5 else if @Service=3 and @CallType=2 --Non Transfer --set @process=6 set @process=18 else if @Service=3 and @CallType=1 --Transfer --set @process=7 set @process=17 --Added by Swati for Payment else if @Service=1 and @CallType=6 and @NotificationType=0 set @process=26 else if @Service=1 and @CallType=6 and @NotificationType=1 set @process=23 end End ---------------------------Added by swati B to check if Process exist in case of WF -------------end ---Set priority to priority-5 to calls in next 1 minute, priority=priority-4 to calls in next 2 minutes and so on-- if @CallDateTime between getdate() and dateadd(minute,1,getdate()) begin if (@Priority-5)> 0 set @Priority=@Priority-5 end else if @CallDateTime between dateadd(minute,1,getdate()) and dateadd(minute,2,getdate()) begin if (@Priority-4) >0 set @Priority=@Priority-4 end else if @CallDateTime between dateadd(minute,2,getdate()) and dateadd(minute,3,getdate()) begin if (@Priority-3)>0 set @Priority=@Priority-3 end else if @CallDateTime between dateadd(minute,3,getdate()) and dateadd(minute,4,getdate()) begin if (@Priority-2)>0 set @Priority=@Priority-2 end else if @CallDateTime between dateadd(minute,4,getdate()) and dateadd(minute,5,getdate()) begin if (@Priority-1)>0 set @Priority=@Priority-1 end print 'NotificationPayment ' +@NotificationPayment +'Not Type ' print @NotificationType print 'chk' print @NotificationPayment ---create Text for Notification type text(ITR) for Appointment event notification start --to uncomment for testing if @NotificationPayment<>'Y' and @NotificationType='1' begin -- declare @Message as varchar(1000),@Amount as decimal,@InvoiceId as varchar(25),@ProviderNM varchar(25),@RecipientNM varchar(25) select @Message=Message_Text from TextTemplate S where s.NotificationType='text' and s.MessageType=0 and Eventtype='Appointment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM from ARSUser_mst P where P.userID =@UserId select @RecipientNM=R.Fname from patientmaster R where R.Patientid = @PatientID print 'to check msg before000000000000000' print 'message' + @Message print 'Recipient name'+ @RecipientNM print 'Providername'+ @ProviderNM print '@AppDateTime' print @AppDateTime print '@PharmacyPh' print @PharmacyPh set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@AppDateTime),101),''))) --isnull(@AppDateTime,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(CONVERT(varchar(15),cast(@AppDateTime as time),100) ,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) print 'message after actual values' + @Message end ---create Text for Notification type text for Appointment event notification start declare @ClientFile as varchar(150) set @ClientFile='' if @Service=1 begin IF (select Status from ARSUser_mst where UserId=@UserId) IN ('E','T') BEGIN if @NotificationPayment ='Y' Begin --Added by Swati B for Payment notification -Begin print 'Enter Payment &&&&&' set @filenm=@Message print 'First insert' print @DrOfficePh print @CallId insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5, User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@fullRecipientNm,@DrOfficePh,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) End ---Added by Swati B for Payment notification -End select @DigitalId=DigitalId ,@UserName=UserName from logininfo where Userid=@UserId select @DrFileName=[FileName] from CallMessages where DigitalID=@DigitalId and Service=@Service and CallType=10 and Status='Audited' select @patientnm=Fname from PatientMaster where PatientID=@PatientID exec sp_SelectClientNameFile @patientnm,@ClientFile output set @patientnm=@ClientFile select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId --req. to transfer call to Dr's office-- select @langID= languageid,@filenm=[FileName],@NotificationType=[NotificationType] from CallMessages where CallMsgID=dbo.SelectARSPRRFileToPlay (@DigitalId,@PatientID,@CallType,@SubType,@Service,@NotificationType) if @filenm is null begin select @ErrorID=max(ErrorId) from errorlog set @ErrorID=@ErrorID+1 insert into errorlog (ErrorId,ErrorNumber,ErrorMessage,InnerException,ErrorCollectionString,LineNumber,SqlProceName,State,FormName,VbProceName,DtOccured,UserIdLoggedIn)values (@ErrorID,0,'Fatal Error.Recorded message not found.Calls made will have blank message','-','-',95,'sp_inserttocalllist',1,'-','-',getdate(),0) end if @CallType=5 --app reminder call-- begin if @NotificationType='1' set @filenm='' -- if @Iswf=1 -- begin ---- set @Message=@WFMessageTxt -- end set @filenm=@Message print '@filenm'+ @filenm print 'second insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_3,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@AppDateTime,@DrOfficePh,@Message,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType=1 --welcome call-- begin if @NotificationType='1' set @filenm=@Message print 'third insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType in(2,3) /*pT.can or Dr.Can*/ begin if @NotificationType='1' set @filenm=@Message print 'Fourth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@DrOfficePh,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType=4 --well visit call-- begin if @NotificationType='1' set @filenm=@Message print 'fifth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end END ELSE BEGIN ----to uncomment for testing by swati B --print 'Hi' DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end else if @Service=2 begin IF (SELECT STATUS FROM PRRUSER_MST WHERE USERID=@UserId) IN ('E','T') BEGIN select @DigitalId=DigitalId ,@UserName=UserName from logininfo where Userid=@UserId select @DrFileName=[FileName] from CallMessages where DigitalID=@DigitalId and Service=@Service and CallType=10 and Status='Audited' select @langID= languageid, @filenm=[FileName] from CallMessages where CallMsgID=dbo.SelectARSPRRFileToPlay (@DigitalId,@PatientID,1,0,@Service,DEFAULT) -- call type 1 used always select @patientnm=Fname from PatientMaster where PatientID=@PatientID exec sp_SelectClientNameFile @patientnm,@ClientFile output set @patientnm=@ClientFile if @filenm is null begin select @ErrorID=max(ErrorId) from errorlog set @ErrorID=@ErrorID+1 insert into errorlog (ErrorId,ErrorNumber,ErrorMessage,InnerException,ErrorCollectionString,LineNumber,SqlProceName,State,FormName,VbProceName,DtOccured,UserIdLoggedIn)values (@ErrorID,0,'Fatal Error.Recorded message not found.Calls made will have blank message','-','-',95,'sp_inserttocalllist',1,'-','-',getdate(),0) end if @PharmacyPh=null or rtrim(@PharmacyPh)='' or ltrim(@PharmacyPh)='' --non transfer- begin if @NotificationType='1' set @filenm=@Message print 'sixth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@PharmacyPh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else --transfer call- begin if @NotificationType='1' set @filenm=@Message print 'seventh insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5, User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@PharmacyPh,@filenm,@langID,@PharmacyPh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end END ELSE BEGIN print 'Hello' --to uncomment for testing by swati B DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end else if @Service=3 begin IF (SELECT STATUS FROM GCUSER_MST WHERE USERID=@UserId) IN ('E','T') BEGIN --commented by swati B as discussed with sachin pawar --set @PersonName=@PersonName+'.vox' ---select filename for gc----- if @filenm is not null begin if @TranferPh is null set @TranferPh='' if @TranferPh is null or ltrim(@TranferPh)='' or rtrim(@TranferPh)='' begin if @NotificationType='1' -- set @filenm=@Message print 'eightth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@PersonName,@filenm,@langID,@CallerId,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else begin if ltrim(@CallerId)='' or rtrim(@CallerId)='' or @CallerId is null set @CallerId = @TranferPh -- if CallerId is empty, show transfer phone as caller-id if @NotificationType='1' -- set @filenm=@Message print 'ninth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@PersonName,@TranferPh,@filenm,@langID,@CallerId,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end end END ELSE BEGIN DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end insert into scheduledCallsHistory select * from scheduledcalls where CallID=@CallId delete from scheduledCalls where CallID=@CallId --Added by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply start --if @NotificationType=1 --Begin --if not exists (select * from [Text_Msg_Log] where call_id=@CallId and status='U') -- INSERT INTO [Text_Msg_Log] SELECT * FROM Call_List where notificationid=1 AND call_id=@CallId --End --Added by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply End FETCH NEXT FROM ScheduledCalls_Cursor into @CallType,@CallId,@Service,@SourceKey,@TelNo,@CallDateTime,@Priority,@SubType,@PersonName,@WFStep,@BefAfter END CLOSE ScheduledCalls_Cursor DEALLOCATE ScheduledCalls_Cursor END Go -------------------- GO IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_MofifyProviderPurchaseAndUsage') DROP PROCEDURE sp_MofifyProviderPurchaseAndUsage GO /****** Object: StoredProcedure [dbo].[sp_MofifyProviderPurchaseAndUsage] Script Date: 04/22/16 2:59:16 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --created ny swati B on 22-04-2016 -- purpose: when user creates new custom package from sign-up / modify details form this procedure will add that package into Package_mst table. create PROCEDURE [dbo].[sp_MofifyProviderPurchaseAndUsage] ( @Invoice_ID int, @Customer_ID int , @PackageId int , @Status varchar(50), @StartDate datetime, -- package enable.disable flag @EndDate datetime, @Available_Cents_Credit float ) AS declare @Duration int select @Duration=Duration from package_mst where PackageID=@PackageId if @Duration=1 ---for monthly subscription set @EndDate= DATEADD(day,30,@StartDate) else if @Duration=2 --for yearly subscription set @EndDate= DATEADD(year,1,@StartDate) BEGIN TRAN BEGIN TRY declare @old_Available_credit float=0 if exists(select [Invoice_ID] from [Provider_Purchases] where [Customer_ID]=@Customer_ID and [Status]='Enabled') update [Provider_Purchases] set [Status]='Disable' where [Customer_ID]=@Customer_ID if exists(select [PackageId] from [Provider_Usage] where [Customer_ID]=@Customer_ID and [Status]='Enabled') begin select @old_Available_credit=[Available_Cents_Credit] from [Provider_Usage] where [Customer_ID]=@Customer_ID and [Status]='Enabled' update [Provider_Usage] set [Status]='Disable' where [Customer_ID]=@Customer_ID set @Available_Cents_Credit=@old_Available_credit+@Available_Cents_Credit End INSERT INTO [dbo].[Provider_Purchases] ([Invoice_ID] ,[Customer_ID] ,[PackageId] ,[Status] ,[StartDate] ,[EndDate]) VALUES (@Invoice_ID,@Customer_ID ,@PackageId ,@Status,@StartDate ,@EndDate) INSERT INTO [dbo].[Provider_Usage] ([Customer_ID] ,[PackageId] ,[Available_Cents_Credit] ,[Calls_Units_Consumed] ,[Text_Consumed] ,[Emails_Consumed] ,[AppNotes_Consumed] ,[Total_Cents_Consumed] ,[Remaining_$_Credit] ,[Alert_Threshold] ,[Status]) VALUES (@Customer_ID ,@PackageId ,@Available_Cents_Credit ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'Enabled') COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH go ------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_SaveProviderWorkflow') DROP PROCEDURE sp_SaveProviderWorkflow GO /****** Object: StoredProcedure [dbo].[sp_SaveProviderWorkflow] Script Date: 04/22/16 3:02:29 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: <17/01/2016> -- Description: -- ============================================= create PROCEDURE [dbo].[sp_SaveProviderWorkflow] @ProviderId int,@NotificationId int,@Preference1 int,@Preference2 int,@Preference3 int,@durdays1 int,@durdays2 int,@DurResBased varchar(5), @MsgIdBeforeDueDt int ,@MsgIdAfterDueDt int ,@TranferToProviderOff bit,@IsSkip bit,@CrDate datetime,@MdDate datetime,@DurBefore int,@DurAfter int AS BEGIN if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@ProviderId and NotificationId=@NotificationId) --check if record exists for that provider and notificationtype (APP 5 or Payment 6) begin delete from NotificationWorkFlow where ProviderId=@ProviderId end declare @TempId1 as int,@TempId2 as int ----Step 1 setting -- For text set messageid from TextTemplate table for Payment and Appointment if (@Preference1=1 and @NotificationId=5) set @TempId1=13 if (@Preference1=1 and @NotificationId=6) set @TempId1=2 -- For voice set Processid from Processmaster table for Payment and Appointment if (@Preference1=0 and @NotificationId=5 and @DurResBased=0) ---for duration based event ivr set @TempId1=24 else if (@Preference1=0 and @NotificationId=6 and @DurResBased=0) ---for duration based payment ivr set @TempId1=25 else if (@Preference1=0 and @NotificationId=5 and @DurResBased=1 and @TranferToProviderOff=0) ---for response based based event ivr with out transfer set @TempId1=20 else if (@Preference1=0 and @NotificationId=5 and @DurResBased=1 and @TranferToProviderOff=1) ---for response based based event ivr with transfer set @TempId1=19 else if (@Preference1=0 and @NotificationId=6 and @DurResBased=1 and @TranferToProviderOff=0) ---for response based based payment ivr with out transfer set @TempId1=26 else if (@Preference1=0 and @NotificationId=6 and @DurResBased=1 and @TranferToProviderOff=1) ---for response based based payment ivr with transfer set @TempId1=22 ----Step 2 setting -- For text set messageid from TextTemplate table for Payment and Appointment if (@Preference2=1 and @NotificationId=5) set @TempId2=13 if (@Preference2=1 and @NotificationId=6) set @TempId2=2 -- For voice set Processid from Processmaster table for Payment and Appointment if (@Preference2=0 and @NotificationId=5 and @DurResBased=0) set @TempId2=24 else if (@Preference2=0 and @NotificationId=5 and @DurResBased=1) set @TempId2=20 else if (@Preference2=0 and @NotificationId=6 and @DurResBased=1) set @TempId2=26 else if (@Preference2=0 and @NotificationId=6 and @DurResBased=0) set @TempId2=25 ----Step 3 setting --in case of voice reset msgid before n after to respective processids if (@Preference3=0 and @NotificationId=5) begin set @MsgIdBeforeDueDt=27 set @MsgIdAfterDueDt=28 end --else if (@Preference3=1 and @NotificationId=5) --begin --set @MsgIdBeforeDueDt=27 --set @MsgIdAfterDueDt=28 --end else if (@Preference3=0 and @NotificationId=6) begin set @MsgIdBeforeDueDt=29 set @MsgIdAfterDueDt=30 end -- else if (@Preference3=1 and @NotificationId=6) --begin --set @MsgIdBeforeDueDt=27 --set @MsgIdAfterDueDt=28 --end --insert INSERT INTO NotificationWorkFlow([ProviderId] ,[NotificationId],[Preference1],[Preference2],[Preference3],[durdays1] ,[durdays2] ,[DurResBased],[MsgIdBeforeDueDt] ,[MsgIdAfterDueDt] ,[DurBefore],[DurAfter],[TransferToProviderOff],[IsSkip] , [TemplateId1], [TemplateId2],[CrDate],[MdDate]) VALUES (@ProviderId,@NotificationId,@Preference1,@Preference2,@Preference3 ,@durdays1,@durdays2,@DurResBased ,@MsgIdBeforeDueDt,@MsgIdAfterDueDt,@DurBefore,@DurAfter,@TranferToProviderOff,@IsSkip,@TempId1,@TempId2 ,@CrDate,@MdDate) End Go ------------------------------ IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_scheduleAppointment') DROP PROCEDURE sp_scheduleAppointment GO /****** Object: StoredProcedure [dbo].[sp_scheduleAppointment] Script Date: 04/22/16 3:03:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --This procedure schedule calls for an appointment --This procedure schedule calls for an appointment --[sp_scheduleAppointment] create procedure [dbo].[sp_scheduleAppointment] ( @ApptID int, @DoctorID int, @PatientId int, @AppointmentDateTime datetime, @RemindBeforeNDays tinyint, @CallTypeID int---Added by Swati B for Payment notification (pass 5 for event notification and 8 for Payment notification ) as begin declare @CallDateTime datetime Print 'TTT Appointment date time is ' Print @AppointmentDateTime set @CallDateTime=DATEADD(day, -@RemindBeforeNDays, @AppointmentDateTime) ------added by swati B for workflow start declare @CallDateTimeWF datetime declare @AppStatus int=0 declare @IsWF bit declare @Step as int declare @Days int declare @IsPremium_Feature int select @IsWF=iswf,@AppStatus=status,@Step=wfstep,@IsPremium_Feature=IsPremium_Feature from AppointmentMaster where AppID=@ApptID print 'status' print @AppStatus --for workflow first step set call date time durdays1before event due date if (@IsWF=1 and @Step=1 and @IsPremium_Feature=1) begin select @Days=durdays1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID set @CallDateTime=DATEADD(day, -@Days, @AppointmentDateTime) if (@AppStatus<>0 and @IsWF=1) select @Step=wfstep+1 from appointmentmaster where appid=@ApptID else select @Step=wfstep from appointmentmaster where appid=@ApptID print @Step end ------added by swati B for workflow end Print 'TTT @CallDateTime date time is ' Print @CallDateTime declare @STZ as tinyint select @STZ=TimeZoneID from TimeZoneMaster where TimeDifferenceMins=0 declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId declare @DTZ tinyint declare @PrefTime tinyint declare @ScheduleCallsOnWeekEnds int select @DTZ=TimeZone , @PrefTime=PreferredCallTime , @ScheduleCallsOnWeekEnds=AllowWeekendCalls from ARSPreferences where userid=@DoctorID -- set the calldatetime at the middle of preferred call time slot declare @range_start datetime declare @range_end datetime declare @no_of_hrs int declare @callTime datetime select @range_start=range_start,@range_end=range_end from PreferredCallTimeList where PreferredCallTimeID=@PrefTime set @no_of_hrs=datediff(hh,@range_start,@range_end) set @no_of_hrs=@no_of_hrs/2 set @callTime=dateadd(hh,@no_of_hrs,@range_start) --set @CallDateTime=cast(convert(varchar(12), @CallDateTime) +' '+ convert(varchar(12),@callTime ,108 ) as datetime) print 'TTTT After pref slot calcs' print @CallDateTime -- set the calldatetime at the middle of preferred call time slot -- Check if it is Last Minute Appointment. Implement Last Minute Appointment Rules Check AppointmentTime and current time w.r.t. Patient declare @today datetime select @today= getdate() declare @PatientToday datetime set @PatientToday=dbo.GetPatientDate(@STZ ,@PTZ ,@today) print 'patient today' print @PatientToday --set @today=@PatientToday --set @CallDateTime= dbo.GetPatientDate(@DTZ ,@PTZ ,@CallDateTime) print 'CallDatetime in patient time zone - N days before' print @CallDateTime set @AppointmentDateTime= dbo.ChangeTimezone(@DTZ ,@PTZ ,@AppointmentDateTime) print 'Appointment Datetime in patient time zone' print @AppointmentDateTime if @CallDateTime <= @PatientToday or @CallDateTime >= @AppointmentDateTime begin print ' -- Schedule LastMinuteAppointment Call ' --set @CallDateTime=dbo.LastMinuteAppt(@AppointmentDateTime ,@CallDateTime,@PrefTime,@today ,@PatientToday, @PTZ, @DTZ) if @CallDateTime > @AppointmentDateTime begin print 'calldatetime becomes less than appttime - set it to patient today' if @PatientToday < @AppointmentDateTime --set @CallDateTime=@PatientToday print @CallDateTime end if @CallDateTime < @PatientToday begin set @CallDateTime=@PatientToday end print 'Calldatetime' print @CallDateTime -- Last Minute Appointment Rules Over end else begin --------------- --set @CallDateTime=dbo.checkCallSchedule(@DoctorID,@CallDateTime ,@PTZ ,@STZ , @ScheduleCallsOnWeekEnds , 0 ,1) -- last parameter 0 indicates decrement CalldateTime by 1 if it is WeekEnd , NoCallDay etc print 'Its not last minute appt - Calldatetime' print @CallDateTime if @CallDateTime <= @PatientToday or @CallDateTime >= @AppointmentDateTime begin print 'Its not last minute appt - but Calldatetime becomes less than patient today' print @CallDateTime -- Schedule LastMinuteAppointment Call --set @CallDateTime=dbo.LastMinuteAppt(@AppointmentDateTime ,@CallDateTime,@PrefTime,@today ,@PatientToday, @PTZ, @DTZ) if @CallDateTime > @AppointmentDateTime begin print 'calldatetime becomes less than appttime - set it to patient today' if @PatientToday < @AppointmentDateTime --set @CallDateTime=@PatientToday print @CallDateTime end if @CallDateTime < @PatientToday begin --set @CallDateTime=@PatientToday print 'Hi' end -- Last Minute Appointment Rules Over end print @CallDateTime end print 'patient & server time zone' print @PTZ print @STZ print @CallDateTime --set @CallDateTime=dbo.ChangeTimeZone(@PTZ,@DTZ,@CallDateTime) print 'As per doctors time zone' print @CallDateTime --Generate ARSCallID which is unique across all three sceduling tables declare @NewCallID int exec sp_generateCallID @NewCallID output print 'NewCallID ' print @NewCallID declare @CallMsgID int declare @Digitalid int select @Digitalid=digitalid from logininfo where UserId=@DoctorID --Change by sandip on 7 feb , to get SubCallTypeID Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID print '@Digitalid ' print @Digitalid print '@SubCallTypeID ' print @SubCallTypeID set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) print @CallMsgID declare @NoCallStart datetime declare @NoCallEnd datetime declare @CallTime1 datetime declare @TodayNoCallStart varchar(25) declare @TodayNoCallStartdt datetime declare @calldate datetime --If Calldatetime falls in NoCallTime schedule it at the end of NOCallTime Select @NoCallStart=ParameterValue from Systemsettings where Parameter= 'NoCallTimeStart' Select @NoCallEnd=ParameterValue from Systemsettings where Parameter= 'NoCallTimeEnd' set @CallTime1=convert(varchar(12),@CallDateTime,108) --get only time part-- set @calldate=convert(varchar(12),@CallDateTime,101) --get only date part-- if not (@CallTime1 < @NoCallStart and @CallTime1 > @NoCallEnd) -- if call time is in NoCallTime begin print '--inside NoCallTime check' --declare @CurrentTime datetime --set @CurrentTime=convert(varchar(12),getdate(),108) --get only time part-- set @TodayNoCallStart=convert(varchar(12),@calldate,101)+' ' +@NoCallEnd --get datetime-- set @TodayNoCallStartdt=convert(datetime,@TodayNoCallStart) --to convert varchar to datetime-- if @TodayNoCallStartdt= @AppointmentDateTime begin print 'After NocallTime check Calldatetime becomes future(greater than appttime) or pasttim' --print @CallDateTime if @CallDateTime > @AppointmentDateTime begin print 'calldatetime becomes less than appttime - set it to patient today' if @PatientToday < @AppointmentDateTime --set @CallDateTime=@PatientToday print @CallDateTime end if @CallDateTime < @PatientToday begin set @CallDateTime=@PatientToday end -- Last Minute Appointment Rules Over end --print @CallDateTime set @AppointmentDateTime= dbo.ChangeTimezone(@PTZ ,@DTZ ,@AppointmentDateTime) print 'Apptdatetime as per doctors time zone' print @AppointmentDateTime --set @CallDateTime=dbo.ChangeTimeZone(@PTZ,@STZ,@CallDateTime) ----------------------------------------------------------- print 'As per servers time zone - for patient to get call at above time 1' print 'CallDatetime Last before insertion to ARSCallSchedule' print @CallDateTime ------added by swati for workflow start if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID) begin --declare @CallDateTimeWF datetime --declare @AppStatus int=0 --declare @IsWF bit --declare @Step as int --declare @Days int select @IsWF=iswf,@AppStatus=status,@Step=wfstep from AppointmentMaster where AppID=@ApptID ------need to add condition basis of step.whether it is response based or duration based declare @DurResBased as int if (@Step=2) begin select @DurResBased=DurResBased,@Days=durdays2 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID if (@DurResBased=1) ---Response based begin --set default calldatetime set @CallDateTimeWF=@CallDateTime end else if (@DurResBased=0) ---Duration based begin declare @now1 date set @now1= getdate() print @now1 if (@Days=0) --set default calldatetime set @CallDateTimeWF=@CallDateTime else if (@Days >0) --set calldate time =currentdate+numberof days+default timestamp set @CallDateTimeWF=DATEADD(day, -@Days, @now1) --to do + defualt time end --check calculated @CallDateTimeWF is greater than apptduedt/paymentduedt the set deault calldatetime(with out ef) if (@CallDateTimeWF>@AppointmentDateTime) set @CallDateTimeWF=@CallDateTime End --end of endif print ' '+@NewCallID print ' ' + @ApptID print ' ' + @DoctorID print ' ' + @PatientID print ' ' + @DayPhone print ' ' + @DayTimeStart print ' ' + @DayTimeEnd print ' ' + @OtherPhone print ' ' + @AppointmentDateTime print ' ' + @CallDateTimeWF print ' ' + @SubCallTypeID print ' ' + @CallMsgID insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep) --Commented by Swati To Pass @CallTypeID param instead of hardcoded 5 for event notification -- values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, --@AppointmentDateTime,@CallDateTime,5,@SubCallTypeID,1,@CallMsgID,0) --Added by Swati To Pass @CallTypeID param instead of hardcoded 5 for event notification values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,1) end else begin ------added by swati for workflow end print ' '+@NewCallID print ' ' + @ApptID print ' ' + @DoctorID print ' ' + @PatientID print ' ' + @DayPhone print ' ' + @DayTimeStart print ' ' + @DayTimeEnd print ' ' + @OtherPhone print ' ' + @AppointmentDateTime print ' ' + @CallDateTime print ' ' + @SubCallTypeID print ' ' + @CallMsgID insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep) --Commented by Swati To Pass @CallTypeID param instead of hardcoded 5 for event notification -- values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, --@AppointmentDateTime,@CallDateTime,5,@SubCallTypeID,1,@CallMsgID,0) --Added by Swati To Pass @CallTypeID param instead of hardcoded 5 for event notification values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,1) end --Added to insert call_Id in payment details table to refer in future update paymentdetails set call_Id=@NewCallID where appid=@ApptID end Go ------------------ IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_scheduleAppointmentWF_Next') DROP PROCEDURE sp_scheduleAppointmentWF_Next GO /****** Object: StoredProcedure [dbo].[sp_scheduleAppointmentWF_Next] Script Date: 04/22/16 3:05:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_scheduleAppointmentWF_Next] ( @ApptID int, @DoctorID int, @PatientId int, @AppointmentDateTime datetime, @RemindBeforeNDays tinyint, @CallTypeID int---Added by Swati B for Payment notification (pass 5 for event notification and 8 for Payment notification --@Wfstep int ) as begin declare @CallDateTime datetime declare @CallDateTimeWF datetime declare @AppStatus int=0 declare @IsWF bit declare @Step as int declare @NewCallID int declare @CallMsgID int declare @Digitalid int declare @CallTr int select @Digitalid=digitalid from logininfo where UserId=@DoctorID --Change by sandip on 7 feb , to get SubCallTypeID Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID --print '@Digitalid ' --print @Digitalid --print '@SubCallTypeID ' --print @SubCallTypeID declare @sF int set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) --print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId select @IsWF=iswf,@AppStatus=status from AppointmentMaster where AppID=@ApptID --print 'status' --print @AppStatus if(@IsWF=1) begin if exists (select CALL_ID from call_list where CALL_ID=(select max (arscallid) from ARSCallSchedule a where apptid=@ApptID and a.wfstep in (1,2)) and status<>'U' and status<>'QUD') set @CallTr=1 else set @CallTr=0 --if (@AppStatus<>0) ----select @Step=wfstep+1 from appointmentmaster where appid=@ApptID --else select @Step=wfstep from appointmentmaster where appid=@ApptID select @sF=ScheduledFlag from ARSCallSchedule where apptid=@ApptID and wfstep=1 declare @ArsMaxwfcallid int,@Arswfstep int declare @arscalldt datetime select @ArsMaxwfcallid=max(ARSCallID) from ARSCallSchedule where apptid=@ApptID group by apptid --print @ArsMaxwfcallid select @Arswfstep=wfstep,@arscalldt=CallDateTime from ARSCallSchedule where arscallid=@ArsMaxwfcallid --print @Arswfstep --print 'step' --print @Step --print '@Arswfstep' --print @Arswfstep declare @DurResBased as int,@Days as int, @Days1 int --print '@DoctorID' --print @DoctorID --print '@CallTypeID' --print @CallTypeID select @DurResBased=DurResBased,@Days=durdays2,@Days1=durdays1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID --select @DurResBased=DurResBased,@Days=durdays2,@Days1=durdays1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID if (@Step in (1,2)) --if (@Step=2) and (@Arswfstep=1) begin --print 'enter inside step 2 main' declare @CallDateTimeWF1 datetime declare @CallDateTimeWF2 datetime declare @ARSMaxCallId int declare @now1 date set @now1= getdate() select @CallDateTimeWF1=CallDateTime from ARSCallSchedule where apptid=@ApptID and wfstep=1--DATEADD(day, -@Days1, @AppointmentDateTime) --print '@CallDateTimeWF1' --print @CallDateTimeWF1 if (@DurResBased=1) ---Response based begin --print @now1 select @CallDateTimeWF1=CallDateTime from ARSCallSchedule where apptid=@ApptID and wfstep=1 set @CallDateTime= DateAdd(day, 1, @now1) print 're' if (@AppStatus in (0,6)) --and --print 'inside @DurResBased =1' --set calldatetime after one day if exists (select ARSCallID from ARSCallSchedule where apptid=@ApptID and wfstep=2) begin select @ARSMaxCallId =max(ARSCallID) from ARSCallSchedule where apptid=@ApptID and wfstep=2 select @CallDateTimeWF2=calldatetime from ARSCallSchedule where ARSCallID=@ARSMaxCallId and wfstep=2 ---check the max arscallid response came and or not for wfstep2 if (@now1>@CallDateTimeWF2) set @CallDateTime= DateAdd(day, 1, @now1) else set @CallDateTime= DateAdd(day, 1, @CallDateTimeWF2) end else begin set @CallDateTime= DateAdd(day, 1, @CallDateTimeWF1) end --print 'previous triggered calldatetime' --print @CallDateTimeWF2 --print 'next triggered call date time id' --print @CallDateTime end else if (@DurResBased=0) and ((@Arswfstep=1) or (@Arswfstep=2 and CONVERT(VARCHAR(10),@arscalldt,111)@AppointmentDateTime) --set @CallDateTimeWF=@CallDateTime --End --end of endif --print '@AppointmentDateTime' --print @AppointmentDateTime --print '@CallDateTime' --print @CallDateTime --print '@CallDateTimeWF1' --print @CallDateTimeWF1 --print '@now' declare @print datetime set @print =CONVERT(VARCHAR(10),@now1,111) --print @print --print @now1 --print '@sF' --print @sF --print '@AppStatus' --print @AppStatus --if CONVERT(VARCHAR(10),@now1,111)>CONVERT(VARCHAR(10),@CallDateTimeWF1,111) --if CONVERT(VARCHAR(10),@arscalldt,111)>(CONVERT(VARCHAR(10),@now1,111)) begin print 'inside app date condition' --if (@DurResBased=0) and (CONVERT(VARCHAR(10),@arscalldt,111) >CONVERT(VARCHAR(10),@CallDateTime,111)) --if((@DurResBased=1) and (@AppStatus=0) and (CONVERT(VARCHAR(10),@now1,111) >CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)@CallDateTime) begin --print 'insert duration based' exec sp_generateCallID @NewCallID output --print 'NewCallID ' --print @NewCallID if not exists (select arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=2)--if record exist for step 2 then skip this step for duration based one time 2nd step begin --to uncomment for testing insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,2) end --else --print 'for duration based sec step reco already exist' end else if @AppStatus in (0,6) begin --print '@AppStatus' --print @AppStatus --print '@DurResBased' --print @DurResBased if (@DurResBased=1 and @sF=1 and @CallTr=1) and (@AppointmentDateTime>@CallDateTime) begin --print 'insert response based' exec sp_generateCallID @NewCallID output --print 'NewCallID ' --print @NewCallID --print 'inserted for date time' --print @CallDateTime --to uncomment for testing insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,2) end End --else --print 'Already responded' end End end --print 'exit' end Go ------------------ IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ScheduleMsgAfterWF') DROP PROCEDURE sp_ScheduleMsgAfterWF GO /****** Object: StoredProcedure [dbo].[sp_ScheduleMsgAfterWF] Script Date: 04/22/16 3:06:51 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 16-04-2016 -- Description: schedule Workflow calls for 3rd steps message before -- ============================================= create PROCEDURE [dbo].[sp_ScheduleMsgAfterWF] AS BEGIN declare @CallDateTime datetime,@msgAfterdttime datetime,@ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int,@NewCallID int,@Digitalid int,@CallMsgID int,@BefAfter int CREATE TABLE #AppId ( ApptID int, cusrrdate date, beforemsgdt date, DoctorID int, AppointmentDate datetime, MsgIdAfterDueDt int, msgAfterdttime datetime, CallTypeID int ) INSERT #AppId select AppID,CONVERT(VARCHAR(10),getdate(),111) cusrrdate,CONVERT(VARCHAR(10),DATEADD(day, N.DurAfter, AP.AppointmentDate),111) Aftermsgdt,ap.DoctorID,AppointmentDate,N.MsgIdAfterDueDt, DATEADD(day, N.DurAfter, AP.AppointmentDate) msgAfterdttime,n.notificationid from AppointmentMaster AP join NotificationWorkFlow N on AP.DoctorID=N.ProviderId where n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) and CONVERT(VARCHAR(10),DATEADD(day, N.DurAfter, AP.AppointmentDate),111)=CONVERT(VARCHAR(10),getdate(),111) and AP.[NoShow/UnPaid]=1 select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,msgAfterdttime,CallTypeID from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@msgAfterdttime,@CallTypeID WHILE @@FETCH_STATUS = 0 begin print '@@CallTypeID' print @CallTypeID --get fields from [AppointmentMaster] select @DoctorID=ap.DoctorID, @PatientId=ap.PatientID, @AppointmentDateTime=ap.AppointmentDate, @RemindBeforeNDays =ap.RemindBeforeNDays from [AppointmentMaster] ap join ARSCallSchedule a on a.apptid=ap.appid where appid=@ApptID and ISWF=1 and a.CallType=@CallTypeID print '@PatientId' print @PatientId Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID print '@Digitalid ' print @Digitalid print '@SubCallTypeID ' print @SubCallTypeID set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId select @CallDateTime=msgAfterdttime from #AppId select @CallTypeID=CallType from ARSCallSchedule where ApptID=@ApptID set @Step=3 set @BefAfter=2 --insert into ARSCallSchedule exec sp_generateCallID @NewCallID output print 'NewCallID ' print @NewCallID if not exists(select Arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=@Step and BefAfter=@BefAfter) begin print 'insert' insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep,BefAfter) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,@Step,@BefAfter) End else begin delete from #AppId where ApptID=@ApptID and CallTypeID=@CallTypeID end FETCH NEXT FROM Appcursor INTO @ApptID,@msgAfterdttime,@CallTypeID end CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END Go ------------------- /****** Object: StoredProcedure [dbo].[sp_ScheduleMsgBeforeWF] Script Date: 04/22/16 3:08:17 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 16-04-2016 -- Description: schedule Workflow calls for 3rd steps message before -- ============================================= ALTER PROCEDURE [dbo].[sp_ScheduleMsgBeforeWF] AS BEGIN declare @CallDateTime datetime,@msgbeforedttime datetime,@ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int,@NewCallID int,@Digitalid int,@CallMsgID int,@BefAfter int CREATE TABLE #AppId ( ApptID int, cusrrdate date, beforemsgdt date, DoctorID int, AppointmentDate datetime, MsgIdBeforeDueDt int, msgbeforedttime datetime, CallTypeID int ) INSERT #AppId select AppID,CONVERT(VARCHAR(10),getdate(),111) cusrrdate,CONVERT(VARCHAR(10),DATEADD(day, -N.DurBefore, AP.AppointmentDate),111) beforemsgdt,ap.DoctorID,AppointmentDate,N.MsgIdBeforeDueDt, DATEADD(day, -N.DurBefore, AP.AppointmentDate) msgbeforedttime,n.notificationid from AppointmentMaster AP join NotificationWorkFlow N on AP.DoctorID=N.ProviderId where CONVERT(VARCHAR(10),DATEADD(day, -N.DurBefore, AP.AppointmentDate),111)=CONVERT(VARCHAR(10),getdate(),111) and n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) --and AP.NoShow/UnPaid=1 --and AppID=21967 select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,msgbeforedttime,CallTypeID from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@msgbeforedttime,@CallTypeID WHILE @@FETCH_STATUS = 0 begin print '@@CallTypeID' print @CallTypeID --get fields from [AppointmentMaster] select @DoctorID=ap.DoctorID, @PatientId=ap.PatientID, @AppointmentDateTime=ap.AppointmentDate, @RemindBeforeNDays =ap.RemindBeforeNDays from [AppointmentMaster] ap join ARSCallSchedule a on a.apptid=ap.appid where appid=@ApptID and ISWF=1 and a.CallType=@CallTypeID print '@PatientId' print @PatientId Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID print '@Digitalid ' print @Digitalid print '@SubCallTypeID ' print @SubCallTypeID set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId print '@DayPhone' print @DayPhone select @CallDateTime=msgbeforedttime from #AppId select @CallTypeID=CallType from ARSCallSchedule where ApptID=@ApptID set @Step=3 set @BefAfter=1 --insert into ARSCallSchedule exec sp_generateCallID @NewCallID output print 'NewCallID ' print @NewCallID if isnull(@DoctorID,'')<>'' begin print '@@ApptID' print @ApptID if not exists(select Arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=@Step and BefAfter=@BefAfter) begin print 'insert' insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep,BefAfter) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,@Step,@BefAfter) End else begin delete from #AppId where ApptID=@ApptID and CallTypeID=@CallTypeID end end FETCH NEXT FROM Appcursor INTO @ApptID,@msgbeforedttime,@CallTypeID end CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END Go ------------------- /****** Object: StoredProcedure [dbo].[sp_scheduleWFCalls] Script Date: 04/22/16 3:10:01 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 23-02-2016 -- Description: schedule Workflow calls for all other steps -- ============================================= ALTER PROCEDURE [dbo].[sp_scheduleWFCalls] AS BEGIN declare @ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int CREATE TABLE #AppId ( ApptID int, CallType int, call_id int, --maxdate datetime ) INSERT #AppId --to comment for testing --select a.ApptID, CallType,max(c.call_id) callid from call_list c join ARSCallSchedule A join [AppointmentMaster] ap -- on ap.appid=a.ApptID -- on A.ARSCallID=c.call_id --where c.Status<>'U' and c.Status<>'QUD' and ap.Iswf=1 group by a.ApptID ,CallType,ap.wfstep select a.ApptID, CallType,max(A.ARSCallID) callid from ARSCallSchedule A join [AppointmentMaster] ap on ap.appid=a.ApptID join notificationworkflow N on N.providerid=A.DoctorID where ap.Iswf=1 and A.wfstep in (1,2) and ap.IsPremium_Feature=1 and N.isskip=0 group by a.ApptID ,CallType select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,CallType from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@CallTypeID WHILE @@FETCH_STATUS = 0 BEGIN select @DoctorID=DoctorID, @PatientId=PatientID,@AppointmentDateTime=AppointmentDate,@RemindBeforeNDays =RemindBeforeNDays--@Wfstep=wfstep from [AppointmentMaster] where appid=@ApptID and ISWF=1 ---------------------- select @Step=wfstep,@appStatus=status from appointmentmaster where appid=@ApptID select @ArsMaxwfcallid=max(ARSCallID) from ARSCallSchedule where apptid=@ApptID and wfstep in (1,2) group by apptid --print @ArsMaxwfcallid select @Arswfstep=wfstep,@arscalldt=CallDateTime from ARSCallSchedule where arscallid=@ArsMaxwfcallid --print @Arswfstep --print 'step' --print @Step --print '@Arswfstep' --print @Arswfstep ------------------ select @Isskip=isskip,@DurResBased=DurResBased from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID --print '@Isskip' --print @Isskip if @Isskip!=1 BEGIN if (@Step in (1,2)) --and ((@Arswfstep=1) or (@Arswfstep=2 and CONVERT(VARCHAR(10),@arscalldt,111)@now1 BEGIN if not exists (select arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=2 and scheduledflag=0) BEGIN --print 'exec' --insert data in schedule appointment table exec [sp_scheduleAppointmentWF_Next] @ApptID,@DoctorID,@PatientId,@AppointmentDateTime,@RemindBeforeNDays, @CallTypeID--,@Wfstep END else BEGIN --print '2nd step record already exists but not scheduled ' delete from #AppId where ApptID=@ApptID END END else Begin --print 'Appointment date is passed' delete from #AppId where ApptID=@ApptID End END else BEGIN delete from #AppId where ApptID=@ApptID END END FETCH NEXT FROM Appcursor INTO @ApptID,@CallTypeID END CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END Go --------------------------- /****** Object: StoredProcedure [dbo].[sp_updateCallStatus] Script Date: 04/22/16 3:11:20 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --to update from Call List to ARS/PRR/GC CallSchedule --sp_helptext sp_UpdateARSCallSchedule ALTER procedure [dbo].[sp_updateCallStatus] as declare @call_id as int,@Status as varchar(3),@Status1 as varchar(3),@CALL_RESULT as varchar(10),@Reason as varchar(200),@SourceKey as varchar(50),@RefillCount as tinyint,@PrescStartDate as datetime,@PrescDuration as tinyint,@Call_Time as datetime,@LastAttemptDateTime as datetime, @DoctorId as int,@PatientID as int,@AppDateTime as datetime DECLARE CallStatus_cursor CURSOR static For select Status,Call_Id,SourceKey,Call_Time,isnull(CALL_RESULT,'Null') CALL_RESULT from Call_List where Status not in('U','R') and Status not like 'Q%' OPEN CallStatus_cursor FETCH NEXT FROM CallStatus_cursor into @Status,@Call_id,@SourceKey,@Call_Time,@CALL_RESULT WHILE @@FETCH_STATUS = 0 BEGIN print '@CALL_RESULT' + @CALL_RESULT set @SourceKey=convert(varchar(1),@SourceKey) set @LastAttemptDateTime=@Call_Time print @SourceKey if @SourceKey='A' begin print 'A' print @call_id print @LastAttemptDateTime print '@Status' print @Status print @CALL_RESULT exec sp_UpdateARSCallSchedule @call_id,@LastAttemptDateTime,@Status,@CALL_RESULT end else if @SourceKey='P' begin print 'payment' print @call_id print @LastAttemptDateTime print @Call_Time print '@Status' print @Status --exec sp_UpdateARSCallSchedule @call_id,@LastAttemptDateTime,@Status,@Call_Time exec sp_UpdateARSCallSchedule @call_id,@LastAttemptDateTime,@Status,@CALL_RESULT end else if @SourceKey='G' exec sp_UpdateGCCallSchedule @Call_id,@LastAttemptDateTime,@Status --delete from Call_List after call is made --delete from Call_List where call_id=@Call_id FETCH NEXT FROM CallStatus_cursor into @Status,@Call_id,@SourceKey,@Call_Time,@CALL_RESULT END CLOSE CallStatus_cursor DEALLOCATE CallStatus_cursor Go -------------------------- /****** Object: StoredProcedure [dbo].[sp_updateARSCallSchedule] Script Date: 04/22/16 3:14:12 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALTER procedure [dbo].[sp_updateARSCallSchedule] @Call_id as int, @LastAttemptDateTime datetime, @Status varchar(3), @CALL_RESULT varchar(3) as declare @Status1 as int,@PatientID int,@AppDateTime datetime,@DoctorId int,@Call_Time datetime,@Reason as varchar(200),@AppId int,@CallType int,@statusofpayment varchar(50) select @DoctorId=DoctorID, @PatientID=PatientID ,@AppDateTime=AppointmentDateTime,@AppId=ApptID,@CallType=CallType from ARSCallSchedule where ARSCallID=@Call_id select @statusofpayment=statusofpayment from paymentdetails where appid=@AppId print 'inside' print @CALL_RESULT if @Status in('T','X','D0','D1','D2','D4','H','D3','M','ANS','SND','MCH') --update Appointment Master begin if @Status in ('H','X','MCH') begin set @Status1=6 set @Reason='No Reply' -- end if @Status in ('D0','M','D3','T') begin set @Status1=7 set @Reason='No Response' end if @Status in ('D1','ANS','SND') begin if @CALL_RESULT ='1' begin set @Status1=1 set @Reason='Confirmed' end if @CALL_RESULT ='2' begin if @CallType=5 Begin set @Status1=2 set @Reason='Cancelled' End else if @CallType=6 Begin if @statusofpayment='DONE' begin set @Status1=10 set @Reason='Paid' End else begin set @Status1=7 set @Reason='No Response' End End end if @CALL_RESULT ='3' begin set @Status1=3 set @Reason='Rescheduled' end else if @CALL_RESULT not in ('1','2','3') begin print 'inside other call_result' set @Status1=6--for not replied set @Reason='No Reply' end end if @Status IN ('D2','D4') --09 Dec --D4 should be considered as cancelled begin set @Status1=2 set @Reason='Cancelled' exec sp_insertPatientCancellationFollowupCall @DoctorId,@PatientID,@AppId,@AppDateTime --pat cancel place patient cancellation follow up call end print @Reason print '@Status1' print @Status1 -- When updating Appointment Master table , check that status of Appoinment is either booked or same as that of What u r trying to update. Then only update , otherwise do not UPDATE update AppointmentMaster set Status=@Status1,StatusChangeDate=getdate(),StatusChangeReason=@Reason,TrDate=getdate() where AppID=@AppId and Status in (0,6,@Status1) -- if @Status <> 'X' -- begin --update ArsCallSchedule set LastAttemptDateTime=@LastAttemptDateTime, LastAttemptStatus=@Status where ARSCallId=@Call_id delete from arscallschedulehistory where ARSCallID=@Call_id insert into arscallschedulehistory select * from arscallschedule where ARSCallID=@Call_id --delete from ARSCallSchedule where ARSCallID=@Call_id -- end end -- update ARSCallschedule /*IF @Status = 'X' --unsuccessful call to be retry begin exec sp_rescheduleUnSuccessfulCall @Call_id,@PatientID,@DoctorId,@Call_Time,@AppDateTime,1 -- to update CallScheduleStatus end*/ Go ------------------------------ IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateemailconsumed') DROP PROCEDURE sp_updateemailconsumed GO /****** Object: StoredProcedure [dbo].[sp_updateemailconsumed] Script Date: 04/22/16 3:20:30 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --alter table [Provider_Usage] alter column Available_Cents_Credit int --sp_updateemailconsumed 525,1 create PROCEDURE [dbo].[sp_updateemailconsumed] ( @UserId int --@packageid int ) AS begin declare @Available_Cents_Credit float; declare @Remaining_$_Credit float; declare @EmailConsume int; declare @CreditsPerText float; declare @CreditsPerCall float; declare @CallConsume int; declare @Text_Consumed int; declare @packageid int declare @TotCentsConsumed float; declare @creditsPeremail float; declare @Total_cents_consumed float; declare @Amount money; select @Available_Cents_Credit=[Available_Cents_Credit],@EmailConsume=[Emails_Consumed],@TotCentsConsumed=Total_Cents_Consumed,@CallConsume=[Calls_Units_Consumed],@Text_Consumed=Text_Consumed,@PackageId=packageid from [Provider_Usage] where [Customer_ID]= @userid print @Available_Cents_Credit print @EmailConsume select @creditsPeremail=CreditsPerEmail,@Amount=Amount,@CreditsPerCall=CreditsPerCall,@CreditsPerText=CreditsPerText from Package_mst where PackageID=@packageid print '@creditsPeremail' print @creditsPeremail set @EmailConsume=@EmailConsume+1 print @EmailConsume set @TotCentsConsumed=@TotCentsConsumed+@creditsPeremail print '@TotCentsConsumed' print @TotCentsConsumed set @Available_Cents_Credit=@Available_Cents_Credit-@creditsPeremail print '@Available_Cents_Credit' print @Available_Cents_Credit set @Remaining_$_Credit=CAST(@Available_Cents_Credit AS float) / CAST(100 AS float) set @Total_cents_consumed=(@CallConsume*@CreditsPerCall)+(@Text_Consumed)+(@EmailConsume*@CreditsPerEmail) print '@Total_cents_consumed' print @Total_cents_consumed --SELECT CAST(@@Available_Cents_Credit AS float) / CAST(100 AS float) UPDATE [dbo].[Provider_Usage] SEt [Available_Cents_Credit] =@Available_Cents_Credit ,[Emails_Consumed] = @EmailConsume ,Total_cents_consumed=@Total_cents_consumed ,[Remaining_$_Credit] = @Remaining_$_Credit -- ,[Alert_Threshold] = WHERE [Customer_ID]=@UserId and [PackageId]=@packageid and [Status]='Enabled' end --select * from [Provider_Usage] Go ------------------------ IF OBJECT_ID ('[Tr_EventAcknowlege]', 'TR') IS NOT NULL DROP TRIGGER [Tr_EventAcknowlege]; go /****** Object: Trigger [dbo].[Tr_EventAcknowlege] Script Date: 04/22/16 3:22:13 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create TRIGGER [dbo].[Tr_EventAcknowlege] ON [dbo].[Call_List] AFTER update AS declare @CALL_RESULT int; declare @NewCallID int; declare @Status varchar(20); declare @PHONE_NUMBER varchar(20); declare @PROVIDERId int; declare @PROVIDER_PHONE varchar(20); declare @WFStep int; declare @Appid int; declare @CallTypeID int; declare @PatientId int; declare @AppointmentDateTime datetime; declare @RemindBeforeNDays int; declare @ISWF int; if update (CALL_RESULT) select @CALL_RESULT= CALL_RESULT,@PHONE_NUMBER=[PHONE_NUMBER],@PROVIDER_PHONE=[PROVIDER_PHONE],@Status=status,@PROVIDERId=ProviderId from dbo.[Call_List] where [NotificationId] = 1 and status != 'U' if @Status!='U' begin exec sp_generateCallID @NewCallID output print '@NewCallID' print @NewCallID if @CALL_RESULT=1 begin INSERT INTO [dbo].[Call_List] ([CALL_ID] ,[DATE_TIME_STAMP] ,[PHONE_NUMBER] ,[PRIORITY] ,[EXTENSION] ,[PROVIDER_PHONE] ,[STATUS] ,[PROCESS] ,[AUDIO_FOLDER] ,[CALL_TIME] ,[CALL_RESULT] ,[USER_DATA_1] ,[USER_DATA_2] ,[USER_DATA_3] ,[USER_DATA_4] ,[USER_DATA_5] ,[USER_DATA_6] ,[USER_DATA_7] ,[USER_DATA_8] ,[USER_DATA_9] ,[USER_DATA_10] ,[SourceKey] ,[NotificationId] ,[From_Email] ,[To_Email] ,[ProviderId]) VALUES (@NewCallID ,getdate() ,@PHONE_NUMBER ,201 ,null ,@PROVIDER_PHONE ,'U' ,21 ,NULL ,getdate() ,1 ,NULL ,'IGN' ,Null ,@PROVIDER_PHONE ,'Your event has been confirmed. Thank you.' ,NULL ,NULL ,NULL ,NULL ,NULL ,'AAAA' ,1 ,NULL ,NULL ,@PROVIDERId) End else if @CALL_RESULT=2 begin INSERT INTO [dbo].[Call_List] ([CALL_ID] ,[DATE_TIME_STAMP] ,[PHONE_NUMBER] ,[PRIORITY] ,[EXTENSION] ,[PROVIDER_PHONE] ,[STATUS] ,[PROCESS] ,[AUDIO_FOLDER] ,[CALL_TIME] ,[CALL_RESULT] ,[USER_DATA_1] ,[USER_DATA_2] ,[USER_DATA_3] ,[USER_DATA_4] ,[USER_DATA_5] ,[USER_DATA_6] ,[USER_DATA_7] ,[USER_DATA_8] ,[USER_DATA_9] ,[USER_DATA_10] ,[SourceKey] ,[NotificationId] ,[From_Email] ,[To_Email] ,[ProviderId]) VALUES (@NewCallID ,getdate() ,@PHONE_NUMBER ,201 ,null ,@PROVIDER_PHONE ,'U' ,21 ,NULL ,getdate() ,1 ,NULL ,'IGN' ,Null ,@PROVIDER_PHONE ,'Your event has been Cancelled. To reschedule your event please call on ' + @PROVIDER_PHONE ,NULL ,NULL ,NULL ,NULL ,NULL ,'AAAA' ,1 ,NULL ,NULL ,@PROVIDERId) End End if update ([Status]) select @ISWF=AP.ISWF,@WFStep=a.WFStep,@Appid=a.ApptID,@Status=c.status,@PROVIDERId=c.ProviderId,@CallTypeID=CallType from dbo.[Call_List] c left outer join ARSCallSchedule a on a.ARSCallID=c.call_id join AppointmentMaster AP on AP.appid=a.ApptID where c.[status] != 'U' if @ISWF=1 begin select @PatientId=PatientID,@AppointmentDateTime=AppointmentDate,@RemindBeforeNDays =RemindBeforeNDays from [AppointmentMaster] where AppID=@Appid update AppointmentMaster set WFStep=@WFStep+1 where AppID=@Appid and ISWF=1 and @WFStep=1 -- exec [sp_scheduleAppointmentWF_Next] @Appid,@PROVIDERId,@PatientId,@AppointmentDateTime,@RemindBeforeNDays, @CallTypeID End GO ------------------------------------ IF OBJECT_ID ('[trgAfterInsertMsgLog]', 'TR') IS NOT NULL DROP TRIGGER trgAfterInsertMsgLog; go /****** Object: Trigger [dbo].[trgAfterInsertMsgLog] Script Date: 04/22/16 3:23:51 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[trgAfterInsertMsgLog] ON [dbo].[Text_Msg_Log] FOR INSERT AS ---[Text_Msg_Log] fields declare @Process int; declare @userid int; declare @CALL_ID int; --Packagemst fields declare @CreditsPerText float; declare @CreditsPerEmail float; declare @CreditsPerCall float; --provider purchases fields declare @CreditLimit bigint; declare @NotType int; declare @Remaining_$_Credit float declare @AvailCr float; declare @TotCetsConsumed float; declare @CallConsume int; declare @mail_Consumed int; declare @Text_Consumed int; declare @Packageid int; declare @Total_cents_consumed float declare @Amount money; ---get values select @CALL_ID=i.CALL_ID from inserted i; select @Process=i.Process from inserted i; select @userid=ProviderId from inserted i; select @NotType=NotificationId from inserted i; --select Packageid for that user --select @PackageId=NewPackageID from ARSUser_InvoiceInfo where userid=@userid select @PackageId=packageid from [Provider_Usage] where [Customer_ID]= @userid --Select CreditsPerCall and CreditsPerText for that package select @CreditsPerCall=CreditsPerCall,@Amount=Amount,@CreditsPerText=CreditsPerText,@CreditsPerEmail=CreditsPerEmail from Package_mst where PackageID=@PackageId print '@PackageId' print @PackageId print '@NotType' print @NotType if @NotType=1 begin select @AvailCr=Available_Cents_Credit,@CallConsume=[Calls_Units_Consumed],@Text_Consumed=Text_Consumed,@mail_Consumed=Emails_Consumed from [Provider_Usage] where [Customer_ID]= @userid print 'old Text consumed' print @Text_Consumed set @Text_Consumed=@Text_Consumed+1 print 'new [Text_Units Consumed]' print @Text_Consumed print 'old Available cents credits' print @AvailCr set @AvailCr=@AvailCr-(@CreditsPerText) print 'new Available cents credits' print @AvailCr set @Remaining_$_Credit=CAST(@AvailCr AS float) / CAST(100 AS float) print '@Remaining_$_Credit' print @Remaining_$_Credit set @Total_cents_consumed=(@CallConsume*@CreditsPerCall)+(@Text_Consumed*@CreditsPerText)+(@mail_Consumed*@CreditsPerEmail) print '@Total_cents_consumed' update [Provider_Usage] set Text_Consumed=@Text_Consumed,Available_Cents_Credit=@AvailCr,[Remaining_$_Credit]=@Remaining_$_Credit,Total_cents_consumed=@Total_cents_consumed where customer_id=@userid end GO -------------------- IF OBJECT_ID ('[trgAfterInsertCallLog]', 'TR') IS NOT NULL DROP TRIGGER [trgAfterInsertCallLog]; go /****** Object: Trigger [dbo].[trgAfterInsertCallLog] Script Date: 04/22/16 3:24:51 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create TRIGGER [dbo].[trgAfterInsertCallLog] ON [dbo].[Transaction] FOR INSERT AS declare @Process int; declare @userid int; declare @CALL_ID int; declare @CALL_START_TIME datetime; declare @CALL_END_TIME datetime; declare @duration varchar(100); declare @audit_action varchar(100); --Packagemst fields declare @CreditsPerCall int; declare @CreditsPerText int; declare @CreditsPerEmail float; --provider purchases fields declare @CreditLimit bigint; declare @NotType int; declare @NoOfCallconsidered bigint; declare @SecPerCallUnit float; declare @CreditDur float; declare @Remaining_$_Credit float declare @Total_cents_consumed float declare @AvailCr float; declare @CallConsume int; declare @TotCetsConsumed float; declare @Text_Consumed int; declare @mail_Consumed int; declare @Packageid int; declare @Amount money; select @CALL_ID=i.CALL_ID from inserted i; select @CALL_START_TIME=i.CALL_START_TIME from inserted i; select @CALL_END_TIME=i.CALL_END_TIME from inserted i; select @duration=i.Duration from inserted i; select @Process=i.Process from inserted i; select @userid=Providerid from call_List where call_ID= @CALL_ID; --select Packageid for that user select @PackageId=NewPackageID from ARSUser_InvoiceInfo where userid=@userid select @PackageId=packageid from [Provider_Usage] where [Customer_ID]= @userid --Select CreditsPerCall and CreditsPerText for that package select @CreditsPerCall=CreditsPerCall,@Amount=Amount,@CreditsPerText=CreditsPerText,@CreditsPerEmail=CreditsPerEmail,@Amount=Amount from Package_mst where PackageID=@PackageId print '@PackageId' print @PackageId print '@CreditDur' print @CreditDur select @NotType=notificationType from processmaster where processid=@Process print '@NotType' print @NotType if @NotType=0 begin --SELECT @CreditDur=DATEDIFF(SECOND, @CALL_START_TIME, @CALL_END_TIME) set @CreditDur= @duration --get no of seconds per call from system settings table select @SecPerCallUnit=ltrim(rtrim(isnull(ParameterValue,1))) from systemsettings where ltrim(rtrim(Parameter))='Seconds Per Call Unit' print '@SecPerCallUnit' print @SecPerCallUnit set @NoOfCallconsidered= CEILING (@CreditDur/@SecPerCallUnit) select @userid=providerid from call_list where call_id=@CALL_ID print 'NoOfCallconsidered' print @NoOfCallconsidered select @AvailCr=Available_Cents_Credit,@CallConsume=[Calls_Units_Consumed],@Text_Consumed=Text_Consumed,@mail_Consumed=Emails_Consumed from [Provider_Usage] where [Customer_ID]= @userid print 'old call consumed' print @CallConsume set @CallConsume=@CallConsume+@NoOfCallconsidered print 'new [Calls_Units Consumed]' print @CallConsume print 'old Available cents credits' print @AvailCr set @AvailCr=@AvailCr- (@NoOfCallconsidered*@CreditsPerCall) print 'new Available cents credits' print @AvailCr set @Remaining_$_Credit=CAST(@AvailCr AS float) / CAST(100 AS float) print '@Remaining_$_Credit' print @Remaining_$_Credit set @Total_cents_consumed=(@CallConsume*@CreditsPerCall)+(@Text_Consumed*@CreditsPerText)+(@mail_Consumed*@CreditsPerEmail) print '@Total_cents_consumed' update [Provider_Usage] set [Calls_Units_Consumed]=@CallConsume,Available_Cents_Credit=@AvailCr,[Remaining_$_Credit]=@Remaining_$_Credit,Total_cents_consumed=@Total_cents_consumed where customer_id=@userid print 'new [Calls_Units Consumed]' end PRINT 'AFTER INSERT trigger fired.' Go ---------------- /****** Object: StoredProcedure [dbo].[sp_scheduleAppointmentWF_Next] Script Date: 04/22/16 7:29:13 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[sp_scheduleAppointmentWF_Next] ( @ApptID int, @DoctorID int, @PatientId int, @AppointmentDateTime datetime, @RemindBeforeNDays tinyint, @CallTypeID int---Added by Swati B for Payment notification (pass 5 for event notification and 8 for Payment notification --@Wfstep int ) as begin declare @CallDateTime datetime declare @CallDateTimeWF datetime declare @AppStatus int=0 declare @IsWF bit declare @Step as int declare @NewCallID int declare @CallMsgID int declare @Digitalid int declare @CallTr int select @Digitalid=digitalid from logininfo where UserId=@DoctorID --Change by sandip on 7 feb , to get SubCallTypeID Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID --print '@Digitalid ' --print @Digitalid --print '@SubCallTypeID ' --print @SubCallTypeID declare @sF int set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) --print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId select @IsWF=iswf,@AppStatus=status from AppointmentMaster where AppID=@ApptID --print 'status' --print @AppStatus if(@IsWF=1) begin if exists (select CALL_ID from call_list where CALL_ID=(select max (arscallid) from ARSCallSchedule a where apptid=@ApptID and a.wfstep in (1,2)) and status<>'U' and status<>'QUD') set @CallTr=1 else set @CallTr=0 --if (@AppStatus<>0) ----select @Step=wfstep+1 from appointmentmaster where appid=@ApptID --else select @Step=wfstep from appointmentmaster where appid=@ApptID select @sF=ScheduledFlag from ARSCallSchedule where apptid=@ApptID and wfstep=1 declare @ArsMaxwfcallid int,@Arswfstep int declare @arscalldt datetime select @ArsMaxwfcallid=max(ARSCallID) from ARSCallSchedule where apptid=@ApptID group by apptid --print @ArsMaxwfcallid select @Arswfstep=wfstep,@arscalldt=CallDateTime from ARSCallSchedule where arscallid=@ArsMaxwfcallid --print @Arswfstep --print 'step' --print @Step --print '@Arswfstep' --print @Arswfstep declare @DurResBased as int,@Days as int, @Days1 int --print '@DoctorID' --print @DoctorID --print '@CallTypeID' --print @CallTypeID select @DurResBased=DurResBased,@Days=durdays2,@Days1=durdays1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID --select @DurResBased=DurResBased,@Days=durdays2,@Days1=durdays1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID if (@Step in (1,2)) --if (@Step=2) and (@Arswfstep=1) begin --print 'enter inside step 2 main' declare @CallDateTimeWF1 datetime declare @CallDateTimeWF2 datetime declare @ARSMaxCallId int declare @now1 date set @now1= getdate() select @CallDateTimeWF1=CallDateTime from ARSCallSchedule where apptid=@ApptID and wfstep=1--DATEADD(day, -@Days1, @AppointmentDateTime) --print '@CallDateTimeWF1' --print @CallDateTimeWF1 if (@DurResBased=1) ---Response based begin --print @now1 select @CallDateTimeWF1=CallDateTime from ARSCallSchedule where apptid=@ApptID and wfstep=1 set @CallDateTime= DateAdd(day, 1, @now1) print 're' if (@AppStatus in (0,6)) --and --print 'inside @DurResBased =1' --set calldatetime after one day if exists (select ARSCallID from ARSCallSchedule where apptid=@ApptID and wfstep=2) begin select @ARSMaxCallId =max(ARSCallID) from ARSCallSchedule where apptid=@ApptID and wfstep=2 select @CallDateTimeWF2=calldatetime from ARSCallSchedule where ARSCallID=@ARSMaxCallId and wfstep=2 ---check the max arscallid response came and or not for wfstep2 if (@now1>@CallDateTimeWF2) set @CallDateTime= DateAdd(day, 1, @now1) else set @CallDateTime= DateAdd(day, 1, @CallDateTimeWF2) end else begin set @CallDateTime= DateAdd(day, 1, @CallDateTimeWF1) end --print 'previous triggered calldatetime' --print @CallDateTimeWF2 --print 'next triggered call date time id' --print @CallDateTime end else if (@DurResBased=0)-- and ((@Arswfstep=1) or (@Arswfstep=2-- and CONVERT(VARCHAR(10),@arscalldt,111)@AppointmentDateTime) --set @CallDateTimeWF=@CallDateTime --End --end of endif --print '@AppointmentDateTime' --print @AppointmentDateTime --print '@CallDateTime' --print @CallDateTime --print '@CallDateTimeWF1' --print @CallDateTimeWF1 --print '@now' declare @print datetime set @print =CONVERT(VARCHAR(10),@now1,111) --print @print --print @now1 --print '@sF' --print @sF --print '@AppStatus' --print @AppStatus --if CONVERT(VARCHAR(10),@now1,111)>CONVERT(VARCHAR(10),@CallDateTimeWF1,111) --if CONVERT(VARCHAR(10),@arscalldt,111)>(CONVERT(VARCHAR(10),@now1,111)) begin print 'inside app date condition' --if (@DurResBased=0) and (CONVERT(VARCHAR(10),@arscalldt,111) >CONVERT(VARCHAR(10),@CallDateTime,111)) --if((@DurResBased=1) and (@AppStatus=0) and (CONVERT(VARCHAR(10),@now1,111) >CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)CONVERT(VARCHAR(10),@CallDateTimeWF1,111) and CONVERT(VARCHAR(10),@arscalldt,111)@CallDateTime) begin --print 'insert duration based' exec sp_generateCallID @NewCallID output --print 'NewCallID ' --print @NewCallID if not exists (select arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=2)--if record exist for step 2 then skip this step for duration based one time 2nd step begin --to uncomment for testing insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,2) end --else --print 'for duration based sec step reco already exist' end else if @AppStatus in (0,6) begin --print '@AppStatus' --print @AppStatus --print '@DurResBased' --print @DurResBased if (@DurResBased=1 and @sF=1 and @CallTr=1) and (@AppointmentDateTime>@CallDateTime) begin --print 'insert response based' exec sp_generateCallID @NewCallID output --print 'NewCallID ' --print @NewCallID --print 'inserted for date time' --print @CallDateTime --to uncomment for testing insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,2) end End --else --print 'Already responded' end End end --print 'exit' end Go ----------- ----End swati GO --- Set PK key On UserID Go ALTER TABLE logininfo ADD PRIMARY KEY (UserID) Go ALTER TABLE logininfo ADD DEFAULT 1 FOR FirstLogin update tbl_ParentMenu set Parentmenu ='Dashboard' where p_id=8 GO -------------------------------- ----swati start /****** Object: StoredProcedure [dbo].[sp_ScheduleMsgBeforeWF] Script Date: 04/24/16 9:59:10 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 16-04-2016 -- Description: schedule Workflow calls for 3rd steps message before -- ============================================= ALTER PROCEDURE [dbo].[sp_ScheduleMsgBeforeWF] AS BEGIN declare @CallDateTime datetime,@msgbeforedttime datetime,@ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int,@NewCallID int,@Digitalid int,@CallMsgID int,@BefAfter int CREATE TABLE #AppId ( ApptID int, cusrrdate date, beforemsgdt date, DoctorID int, AppointmentDate datetime, MsgIdBeforeDueDt int, msgbeforedttime datetime, CallTypeID int ) INSERT #AppId select AppID,CONVERT(VARCHAR(10),getdate(),111) cusrrdate,CONVERT(VARCHAR(10),DATEADD(day, -N.DurBefore, AP.AppointmentDate),111) beforemsgdt,ap.DoctorID,AppointmentDate,N.MsgIdBeforeDueDt, DATEADD(day, -N.DurBefore, AP.AppointmentDate) msgbeforedttime,n.notificationid from AppointmentMaster AP join NotificationWorkFlow N on AP.DoctorID=N.ProviderId --where CONVERT(VARCHAR(10),DATEADD(day, -N.DurBefore, AP.AppointmentDate),111)=CONVERT(VARCHAR(10),getdate(),111) and n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) where n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) --and AP.NoShow/UnPaid=1 --and AppID=21967 select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,msgbeforedttime,CallTypeID from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@msgbeforedttime,@CallTypeID WHILE @@FETCH_STATUS = 0 begin print '@@CallTypeID' print @CallTypeID --get fields from [AppointmentMaster] select @DoctorID=ap.DoctorID, @PatientId=ap.PatientID, @AppointmentDateTime=ap.AppointmentDate, @RemindBeforeNDays =ap.RemindBeforeNDays from [AppointmentMaster] ap join ARSCallSchedule a on a.apptid=ap.appid where appid=@ApptID and ISWF=1 and a.CallType=@CallTypeID print '@PatientId' print @PatientId Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID print '@Digitalid ' print @Digitalid print '@SubCallTypeID ' print @SubCallTypeID set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId print '@DayPhone' print @DayPhone select @CallDateTime=msgbeforedttime from #AppId select @CallTypeID=CallType from ARSCallSchedule where ApptID=@ApptID set @Step=3 set @BefAfter=1 --insert into ARSCallSchedule exec sp_generateCallID @NewCallID output print 'NewCallID ' print @NewCallID if isnull(@DoctorID,'')<>'' begin print '@@ApptID' print @ApptID if not exists(select Arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=@Step and BefAfter=@BefAfter) begin print 'insert' insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep,BefAfter) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,@Step,@BefAfter) End else begin delete from #AppId where ApptID=@ApptID and CallTypeID=@CallTypeID end end FETCH NEXT FROM Appcursor INTO @ApptID,@msgbeforedttime,@CallTypeID end CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END go ----------- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'UpdateStatusThrOtherChannel') DROP PROCEDURE UpdateStatusThrOtherChannel GO /****** Object: StoredProcedure [dbo].[UpdateStatusThrOtherChannel] Script Date: 04/24/16 12:50:39 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: <20-04-2016> -- Description: -- ============================================= create PROCEDURE [dbo].[UpdateStatusThrOtherChannel] @callid int AS BEGIN SET NOCOUNT ON; declare @paymentid int; if exists(select paymentid from paymentdetails where appid=(select apptid from ARSCallSchedule where ARSCallID=@callid)) begin select @paymentid=paymentid from paymentdetails where appid=(select apptid from ARSCallSchedule where ARSCallID=@callid) --update paymentdetails set statusofpayment='DONE' where paymentid=@paymentid UPDATE [PaymentDetails] set [StatusOfPayment]='DONE' where paymentid=@paymentid end END Go ------------------ ---Swati End CREATE TRIGGER [dbo].[AddDeletedRecordToCallListHistory] ON [dbo].[Call_List] FOR DELETE AS declare @CALL_ID int, @DATE_TIME_STAMP datetime declare @PHONE_NUMBER varchar(20), @PRIORITY int, @STATUS varchar(3) declare @PROCESS int, @AUDIO_FOLDER varchar(255), @CALL_TIME datetime declare @CALL_RESULT varchar(50),@USER_DATA_1 varchar(100) declare @USER_DATA_2 varchar(100), @USER_DATA_3 varchar(100) declare @USER_DATA_4 varchar(100), @USER_DATA_5 varchar(100) declare @USER_DATA_6 varchar(100), @USER_DATA_7 varchar(100) declare @USER_DATA_8 varchar(100), @USER_DATA_9 varchar(100) declare @USER_DATA_10 varchar(100), @SourceKey as varchar(50) select @CALL_ID=CALL_ID,@DATE_TIME_STAMP=DATE_TIME_STAMP, @PHONE_NUMBER=PHONE_NUMBER,@PRIORITY=PRIORITY, @STATUS=STATUS, @PROCESS=PROCESS, @AUDIO_FOLDER=AUDIO_FOLDER, @CALL_TIME=CALL_TIME, @CALL_RESULT=CALL_RESULT, @USER_DATA_1=USER_DATA_1, @USER_DATA_2=USER_DATA_2, @USER_DATA_3=USER_DATA_3, @USER_DATA_4=USER_DATA_4, @USER_DATA_5=USER_DATA_5, @USER_DATA_6=USER_DATA_6, @USER_DATA_7=USER_DATA_7, @USER_DATA_8=USER_DATA_8, @USER_DATA_9=USER_DATA_9, @USER_DATA_10=USER_DATA_10, @SourceKey=SourceKey from deleted insert into Call_List_History([CALL_ID] ,[DATE_TIME_STAMP] ,[PHONE_NUMBER] ,[PRIORITY] ,[STATUS],[PROCESS],[AUDIO_FOLDER],[CALL_TIME],[CALL_RESULT] ,[USER_DATA_1] ,[USER_DATA_2] ,[USER_DATA_3] ,[USER_DATA_4] ,[USER_DATA_5] ,[USER_DATA_6] ,[USER_DATA_7] ,[USER_DATA_8] ,[USER_DATA_9] ,[USER_DATA_10] ,[SourceKey],[DateTime]) values( @CALL_ID,@DATE_TIME_STAMP,@PHONE_NUMBER, @PRIORITY, @STATUS, @PROCESS, @AUDIO_FOLDER, @CALL_TIME, @CALL_RESULT, @USER_DATA_1, @USER_DATA_2, @USER_DATA_3, @USER_DATA_4, @USER_DATA_5, @USER_DATA_6, @USER_DATA_7, @USER_DATA_8, @USER_DATA_9, @USER_DATA_10, @SourceKey, getdate() ) GO CREATE TRIGGER [dbo].[UpdatePreferredCalltimelist1] ON [dbo].[Systemsettings] for update AS BEGIN if not (select convert(datetime,'1/1/1900 ' + parameterValue) from inserted where Parameter like 'NoCallTimeStart') is null begin update preferredCalltimelist set range_end= (select convert(datetime,'1/1/1900 ' + parameterValue) from inserted where Parameter like 'NoCallTimeStart') where PreferredCallTime like 'AnyTime' or PreferredCallTime like 'Evening' end END GO CREATE TRIGGER [dbo].[UpdatePreferredCalltimelist2] ON [dbo].[Systemsettings] for update AS BEGIN if not (select convert(datetime,'1/1/1900 ' + parameterValue) from inserted where Parameter like 'NoCallTimeEnd') is null begin update preferredCalltimelist set range_start= (select convert(datetime,'1/1/1900 ' + parameterValue) from inserted where Parameter like 'NoCallTimeEnd') where PreferredCallTime like 'AnyTime' or PreferredCallTime like 'Morning' end END GO CREATE TRIGGER [dbo].[Tr_PaymentAcknowlege] ON [dbo].[PaymentDetails] AFTER update AS declare @Call_ID int; declare @NewCallID int; declare @PaymentId varchar(20); declare @PHONE_NUMBER varchar(20); declare @PROVIDER_PHONE varchar(20); declare @PROVIDERId int; if update (StatusOfPayment) select @Call_ID= Call_ID,@PaymentId=PaymentID from Paymentdetails where ltrim(rtrim(StatusOfPayment)) = 'DONE' if exists(select call_Id from call_list where [CALL_ID]=@Call_ID) begin select @PHONE_NUMBER=[PHONE_NUMBER],@PROVIDER_PHONE=[PROVIDER_PHONE],@PROVIDERId=ProviderId from [dbo].[Call_List] where [CALL_ID]=@Call_ID End else if exists(select call_Id from [dbo].[Call_List_History] where [CALL_ID]=@Call_ID) begin select @PROVIDER_PHONE= a.OfficePhone from Paymentdetails p join AppointmentMaster ap on ap.appid=p.appid join ARSuser_mst A on a.userid=ap.DoctorID where p.PaymentID=@PaymentId select @PHONE_NUMBER=[PHONE_NUMBER],@PROVIDERId=ProviderId from [dbo].[Call_List_History] where [CALL_ID]=@Call_ID End exec sp_generateCallID @NewCallID output print '@NewCallID' print @NewCallID INSERT INTO [dbo].[Call_List] ([CALL_ID] ,[DATE_TIME_STAMP] ,[PHONE_NUMBER] ,[PRIORITY] ,[EXTENSION] ,[PROVIDER_PHONE] ,[STATUS] ,[PROCESS] ,[AUDIO_FOLDER] ,[CALL_TIME] ,[CALL_RESULT] ,[USER_DATA_1] ,[USER_DATA_2] ,[USER_DATA_3] ,[USER_DATA_4] ,[USER_DATA_5] ,[USER_DATA_6] ,[USER_DATA_7] ,[USER_DATA_8] ,[USER_DATA_9] ,[USER_DATA_10] ,[SourceKey] ,[NotificationId] ,[From_Email] ,[To_Email] ,[ProviderId]) VALUES (@NewCallID ,getdate() ,@PHONE_NUMBER ,201 ,null ,@PROVIDER_PHONE ,'U' ,21 ,NULL ,getdate() ,1 ,NULL ,'IGN' ,Null ,@PROVIDER_PHONE ,'Your Payment has been done. Thank you.' ,NULL ,NULL ,NULL ,NULL ,NULL ,'AAAA' ,1 ,NULL ,NULL ,@PROVIDERId) GO CREATE TRIGGER [dbo].[AddDeletedRecordToTransactionHistory] ON [dbo].[Transaction] FOR DELETE AS declare @CALL_ID int, @DATE_TIME_STAMP datetime declare @LINE int,@PHONE_NUMBER varchar(50), @STATUS varchar(10) declare @RESULT varchar(50),@DESCRIPTION varchar(255) declare @CALL_START_TIME DATETIME, @CALL_END_TIME DATETIME declare @SourceKey as varchar(50),@Process int , @DateTime datetime select @CALL_ID=CALL_ID,@DATE_TIME_STAMP=DATE_TIME_STAMP, @LINE = LINE,@PHONE_NUMBER= PHONE_NUMBER, @STATUS = STATUS,@RESULT= RESULT, @DESCRIPTION= [DESCRIPTION], @CALL_START_TIME = CALL_START_TIME, @CALL_END_TIME = CALL_END_TIME,@SourceKey= SourceKey, @Process= Process from deleted insert into TransactionHistory values( @CALL_ID, @DATE_TIME_STAMP, @LINE , @PHONE_NUMBER, @STATUS , @RESULT, @DESCRIPTION, @CALL_START_TIME , @CALL_END_TIME, @SourceKey, @Process, getdate() ) GO /****** Object: StoredProcedure [dbo].[sp_ViewGCStatus] Script Date: 04/25/2016 12:01:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_ViewGCStatus] @GCProjectID as int as --select gcproject.calllistid,GCCallSchedule.personname, substring(USER_DATA_2,0,CHARINDEX('.',USER_DATA_2)) USER_DATA_2, PHONE_NUMBER, gcproject.transferphone, --convert(varchar(20),DATE_TIME_STAMP)DATE_TIME_STAMP,CALL_RESULT, --STATUS from GCPROJECT join GCCallSchedule on gcproject.gcprojectid=GCCallSchedule.gcprojectid join Call_List_History on GCCallSchedule.GCCALLID=Call_List_History.call_id --WHERE (left(SourceKey,1)='G' AND convert(varchar(12),gcproject.GCProjectID) = right(SourceKey,len(sourcekey)-1)) --and gcproject.gcprojectid= @GCProjectID and Status in ('D0','D3','M','R','X','H','T' ) --status <>'R' --order by USER_DATA_2 --Add by samir as per we disc select gcproject.calllistid,GCCallSchedule.personname, substring(USER_DATA_2,0,CHARINDEX('.',USER_DATA_2)) USER_DATA_2, PHONE_NUMBER, gcproject.transferphone, CONVERT(VARCHAR(10), Getdate(), 101) + ' '+ LTRIM(RIGHT(CONVERT(CHAR(20), Getdate(), 22), 11))DATE_TIME_STAMP,CALL_RESULT,STATUS from GCPROJECT , GCCallSchedule,call_list where gcproject.gcprojectid=GCCallSchedule.GCprojectid and call_list.call_id in ( select gccallid from GCCallSchedule where gcprojectid=@GCProjectID ) or call_list.call_id in ( select gccallid from GCCallSchedulehistory where gcprojectid=@GCProjectID ) and gcproject.gcprojectid= @GCProjectID GO -------------------------- ---swati start /****** Object: StoredProcedure [dbo].[sp_updateARSCallSchedule] Script Date: 04/26/16 3:46:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALTER procedure [dbo].[sp_updateARSCallSchedule] @Call_id as int, @LastAttemptDateTime datetime, @Status varchar(3), @CALL_RESULT varchar(3) as declare @Status1 as int,@PatientID int,@AppDateTime datetime,@DoctorId int,@Call_Time datetime,@Reason as varchar(200),@AppId int,@CallType int,@statusofpayment varchar(50) select @DoctorId=DoctorID, @PatientID=PatientID ,@AppDateTime=AppointmentDateTime,@AppId=ApptID,@CallType=CallType from ARSCallSchedule where ARSCallID=@Call_id select @statusofpayment=statusofpayment from paymentdetails where appid=@AppId print 'inside' print @CALL_RESULT if @Status in('T','X','D0','D1','D2','D4','H','D3','M','ANS','SND','MCH') --update Appointment Master begin if @Status in ('H','X','MCH') begin set @Status1=6 set @Reason='No Reply' -- end if @Status in ('D0','M','D3','T') begin set @Status1=7 set @Reason='No Response' end if @Status in ('D1','ANS','SND') begin if @CALL_RESULT ='1' begin if @CallType=5 Begin set @Status1=1 set @Reason='Confirmed' End else if @CallType=6 if @statusofpayment='DONE' begin set @Status1=10 set @Reason='Paid' End End if @CALL_RESULT ='2' begin if @CallType=5 Begin set @Status1=2 set @Reason='Cancelled' End else if @CallType=6 Begin if @statusofpayment='DONE' begin set @Status1=10 set @Reason='Paid' End else begin set @Status1=7 set @Reason='No Response' End End end if @CALL_RESULT ='3' begin set @Status1=3 set @Reason='Rescheduled' end else if @CALL_RESULT not in ('1','2','3') begin print 'inside other call_result' set @Status1=6--for not replied set @Reason='No Reply' end end if @Status IN ('D2','D4') --09 Dec --D4 should be considered as cancelled begin set @Status1=2 set @Reason='Cancelled' exec sp_insertPatientCancellationFollowupCall @DoctorId,@PatientID,@AppId,@AppDateTime --pat cancel place patient cancellation follow up call end print @Reason print '@Status1' print @Status1 -- When updating Appointment Master table , check that status of Appoinment is either booked or same as that of What u r trying to update. Then only update , otherwise do not UPDATE update AppointmentMaster set Status=@Status1,StatusChangeDate=getdate(),StatusChangeReason=@Reason,TrDate=getdate() where AppID=@AppId and Status in (0,6,@Status1) -- if @Status <> 'X' -- begin --update ArsCallSchedule set LastAttemptDateTime=@LastAttemptDateTime, LastAttemptStatus=@Status where ARSCallId=@Call_id delete from arscallschedulehistory where ARSCallID=@Call_id insert into arscallschedulehistory select * from arscallschedule where ARSCallID=@Call_id --delete from ARSCallSchedule where ARSCallID=@Call_id -- end end -- update ARSCallschedule /*IF @Status = 'X' --unsuccessful call to be retry begin exec sp_rescheduleUnSuccessfulCall @Call_id,@PatientID,@DoctorId,@Call_Time,@AppDateTime,1 -- to update CallScheduleStatus end*/ Go ------------------- alter table [Provider_Usage] alter column Available_Cents_Credit float Go --------------- delete from [ProcessMaster] where [ProcessId]=31 go INSERT INTO [dbo].[ProcessMaster] ([ProcessId] ,[ProcessName] ,[Service] ,[CallType] ,[SetAsDefault] ,[NotificationType] ,[ApplicationUrl]) VALUES (31,'GC Text',3,1,NULL,1,NULL) GO ----------------------------- /****** Object: StoredProcedure [dbo].[sp_insertToCallList] Script Date: 04/27/16 11:36:56 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- REVISED FOR NOT PLACING CALLS FOR DISABLED , DELETED USERS - 27 JUNE 06 -- Purpose: To insert record into Call_List table from ScheduledCalls table --Altered by Swati B to Add Payment notifications --Altered by swati to add changes for notification type=text (pick text message from Text_message table replace the variables with actual vaiables ---and set to set @filenm=@Message and insert into user_data_5 coloumn of call_list table ---Altered by Swati B to Add Workflow based notification ---Altered by Swati B to Add @DoctorEmail,@RecipientEmail for email notification 18/03/2016 ---Altered by Swati B to Add encrypt param of payment 19/03/2016 ---Altered by Swati B to Comment encryption param of payment 28/03/2016 since encrypted format is not sent through plumvoice --Altered by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply End on 29-03-2016 ---Rolled --Altered by swati to insert entry for assign process master for GC text on 27-04-2016 ALTER procedure [dbo].[sp_insertToCallList] as Begin declare @process as int=0,@UserId as int,@UserName as varchar(100),@AudioFolder as varchar(256),@PatientID as int, @CallId as int,@Service as tinyint,@SourceKey as varchar(50),@TelNo as varchar(12),@CallDateTime as datetime, @Priority as smallint,@CallType as int,@SubType as tinyint,@patientnm as varchar(100),@AppDateTime as datetime, @DrOfficePh as varchar(12),@Status as varchar(3),@filenm as varchar(1000),@DigitalId as int,@DrFileName as varchar(51) declare @langID as char(2), @ErrorID as integer, @PersonName as varchar(100),@PharmacyPh as varchar(12),@CallerId as varchar(12) declare @TranferPh as varchar(12),@CallListId smallint declare @fullRecipientNm as varchar(100) Declare @NotificationPayment as varchar(3)----Added by Swati B for Payment notification Declare @WFStep as int ----Added by Swati B for WF based notification-- 9/2/15 dev1 declare @NotificationType as smallint,@BefAfter int declare @DoctorEmail as varchar(200),@RecipientEmail as varchar(200) ---Added by Swati B to Add @DoctorEmail,@RecipientEmail for email notification 18/03/2016 declare @encrParam as NVARCHAR(100) -- Added by Swati B to Add encrypt param of payment 19/03/2016 declare @Tempid3 int declare @InvoiceId as varchar(25)='' set @Status='U' declare @date1 as datetime set @date1=dateadd(minute,5,getdate()) set @NotificationPayment='N' declare @IsWF int=0 declare @appid int declare @IsPremium_Feature int declare @WFMessageTxt as varchar(200)='' DECLARE ScheduledCalls_Cursor CURSOR static FOR select CallType,CallID,Service,SourceKey,TelNo,CallDateTime,Priority,SubType,PersonName,WFStep,BefAfter from ScheduledCalls where CallDateTime < @date1 OPEN ScheduledCalls_Cursor FETCH NEXT FROM ScheduledCalls_Cursor into @CallType,@CallId,@Service,@SourceKey,@TelNo,@CallDateTime,@Priority,@SubType,@PersonName,@WFStep,@BefAfter WHILE @@FETCH_STATUS = 0 BEGIN -- print @Service -- selecting userid is done for getting processid. initially select userid statement for 3 services were not here. but were inside serive if @Service=1 select @UserId=DoctorID,@PatientID=a.PatientID,@patientnm=p.Fname,@fullRecipientNm=p.Fname+' '+p.MI+' '+p.Lname,@AppDateTime=AppointmentDateTime, @NotificationType= p.NotificationType ,@RecipientEmail=p.email from ARSCallSchedule a, PatientMaster p where a.PatientID=p.PatientID and ARSCallID=@CallId select @DrOfficePh=OfficePhone,@DrFileName=FirstNM from ARSUser_mst where UserId=@UserId select @DoctorEmail=isnull(email,'') from [LoginInfo] where UserId=@UserId select @IsPremium_Feature=IsPremium_Feature from arscallschedule s join AppointmentMaster a on a.appid=s.apptid where s.arscallid=@CallId ------added by swati for workflow start if @NotificationType<>2 and @IsPremium_Feature=1 begin declare @WFID as int,@NotificationId int if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@UserId and NotificationId=@CallType) begin set @IsWF=1 print '@WFstep' print @WFstep select @WFID=WFID from NotificationWorkFlow where ProviderId=@UserId and NotificationId=@CallType if @WFstep=1 begin declare @Tempid1 int select @Tempid1=TemplateId1,@NotificationType=Preference1 from NotificationWorkFlow where WFID=@WFID print '@Tempid1' print @Tempid1 print '@NotificationType from WF' print @NotificationType if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid1 set @process=21 end else begin set @process =@Tempid1 ---24 End end if @WFstep=2 begin declare @Tempid2 int select @Tempid2=TemplateId2,@NotificationType=Preference2 from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid2 set @process=21 end else begin set @process =@Tempid2 End end if @WFstep=3 and @BefAfter=1---before message in step 3 begin print '@BefAfter message' print @NotificationType select @Tempid3=[MsgIdBeforeDueDt],@NotificationType=Preference3,@NotificationId=NotificationId from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid3 if @NotificationId=5 set @process=21 else set @process=23 end else begin if @NotificationId=5 set @process=@Tempid3 else set @process=@Tempid3 End end if @WFstep=3 and @BefAfter=2---After message in step 3 begin select @Tempid3=[MsgIdAfterDueDt],@NotificationType=Preference3 from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid3 if @NotificationId=5 set @process=21 else set @process=23 end else begin if @NotificationId=5 set @process=@Tempid3 else set @process=@Tempid3 End end end ------added by swati for workflow end End ---Added by Swati B for Payment notification -Begin ---Check if notification is for payment if exists(select P.paymentid from PaymentDetails P join AppointmentMaster AP on AP.APPId=P.APPID join ARSCallSchedule AC on AC.APPtID=AP.APPId join ScheduledCalls S on S.CallID=AC.ARSCallID where S.CallID=@CallId) begin---if yes enter in this block print 'in payment block' set @NotificationPayment ='Y' declare @PaymentID int set @PaymentID = (select P.paymentid from ARSCallSchedule AC join AppointmentMaster AP on AP.Appid=AC.APPtID join PaymentDetails P on P.APPID=AP.APPId join ScheduledCalls S on S.CallID=AC.ARSCallID where S.CallID=@CallId) declare @Message as varchar(1000),@Param as varchar(500),@Amount as decimal,@ProviderNM varchar(25),@RecipientNM varchar(25),@PDueDt datetime if @NotificationType='0' begin select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @Message=Message_Text from TextTemplate S where s.NotificationType='voice' and s.MessageType=0 and Eventtype='Payment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @Amount=PM.Amount,@InvoiceId=PM.InvoiceId,@PDueDt=PM.PaymentDueDate,@ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM, @RecipientNM= R.Fname from PaymentDetails PM join AppointmentMaster A on PM.APPId =A.Appid join PatientMaster R on A.Patientid=R.Patientid join ARSUser_mst P on A.Doctorid=P.userID where PM.PaymentId=@PaymentID set @Param= convert(varchar(50), isnull(@PaymentID,0)) set @encrParam= dbo.ufn_EncryptString(@Param) --commented by swati B because encrypted message is not triggering from plumvoice --set @encrParam='Id='+ @encrParam --Added by swati B because encrypted message is not triggering from plumvoice set @encrParam='Id='+ @Param print @encrParam print @Message print @Amount print @InvoiceId set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@Amount,0))) set @Message= (SELECT REPLACE(@Message,'',isnull(@InvoiceId,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@PDueDt),101),''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) set @Message= (SELECT REPLACE(@Message,'',@encrParam)) print @Message end else if @NotificationType='1' begin --Prepare Message field for call list table for notification type Text (ITR) -- declare @Message as varchar(1000),@Amount as decimal,@InvoiceId as varchar(25),@ProviderNM varchar(25),@RecipientNM varchar(25) select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @Message=Message_Text from TextTemplate S where s.NotificationType='text' and s.MessageType=0 and Eventtype='Payment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @Amount=PM.Amount,@InvoiceId=PM.InvoiceId,@PDueDt=PM.PaymentDueDate,@ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM, @RecipientNM= R.Fname from PaymentDetails PM join AppointmentMaster A on PM.APPId =A.Appid join PatientMaster R on A.Patientid=R.Patientid join ARSUser_mst P on A.Doctorid=P.userID where PM.PaymentId=@PaymentID set @Param= convert(varchar(50), isnull(@PaymentID,0)) set @encrParam= dbo.ufn_EncryptString(@Param) --commented by swati B because encrypted message is not triggering from plumvoice --set @encrParam='Id='+ @encrParam --Added by swati B because encrypted message is not triggering from plumvoice set @encrParam='Id='+ @Param print @encrParam print @Message print @Amount print @InvoiceId set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@Amount,0))) set @Message= (SELECT REPLACE(@Message,'',isnull(@InvoiceId,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@PDueDt),101),''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) set @Message= (SELECT REPLACE(@Message,'',@encrParam)) print @Message end end ---Added by Swati B for Payment notification -End else if @Service=2 select @UserId=DoctorID,@PatientID=PatientID,@PharmacyPh=PharmacyPhoneNo from PRRCallSchedule where PrescriptionCallID=@CallId else if @Service =3 -- ***** PRASAD - Changed this Select Statement's Where Clause 12/31/2006 --select @UserId=gp.UserId,@TranferPh=gs.TransferNo,@filenm=gp.MessageFileName,@CallListId=gp.CallListId , @langID=gp.TransferLanguageID, @DrFileName=gp.ProviderName, @CallerId=gp.CallerIDNumber from GCCallSchedule gs,GCProject gp where gp.GCProjectId --=(select GCProjectID from GCCallSchedule where GCCallID=@CallId) select @UserId=gp.UserId,@TranferPh=gs.TransferNo,@filenm=gp.MessageFileName, @NotificationType=gp.NotificationType, @CallListId=gp.CallListId , @langID=gp.TransferLanguageID, @DrFileName=gp.ProviderName, @CallerId=gp.CallerIDNumber from GCCallSchedule gs,GCProject gp WHERE gp.GCprojectID = gs.GCprojectID AND gp.GCProjectId=(select GCProjectID from GCCallSchedule where GCCallID=@CallId) if (@process=0) ---------------------------Added by swati B to check if Process exist in case of WF -------------start -- get process id from processassignment table for @UserId begin if (select ProcessId from ProcessAssignment where UserId=@UserId and Service =@Service and CallType=@CallType and NotificationType=@NotificationType)is not null begin print 'Process exist' SELECT @process= ProcessId FROM ProcessAssignment WHERE (EffectiveFrom = (SELECT MAX(EffectiveFrom) FROM ProcessAssignment WHERE UserId = @UserId AND Service = @Service AND CallType = @CallType)) AND (UserId = @UserId) AND (Service = @Service) AND (CallType = @CallType) AND (NotificationType=@NotificationType) end else begin -- if specific processid is not found in processassignment table then Decide which process to run , using service & CallType as Input --Added by swati for @NotificationType=0 and 1 if @Service=1 and @CallType=5 and @NotificationType=0 --changed as told by sudip because he has changed the vxml set @process=20 --set @process=19 else if @Service=1 and @CallType=5 and @NotificationType=1 set @process=21 else if @Service=1 and @CallType=1 set @process=1 else if @Service=1 and @CallType=2 set @process=2 else if @Service=1 and @CallType=3 set @process=3 else if @Service=1 and @CallType=4 set @process=8 else if @Service=2 and @CallType=1 set @process=4 else if @Service=2 and @CallType=2 set @Process=5 else if @Service=3 and @CallType=2 and @NotificationType=0 --Non Transfer --set @process=6 set @process=18 else if @Service=3 and @CallType=1 and @NotificationType=0 --Transfer --set @process=7 set @process=17 --Added by Swati for Payment else if @Service=1 and @CallType=6 and @NotificationType=0 set @process=26 else if @Service=1 and @CallType=6 and @NotificationType=1 set @process=23 else if @Service=3 and @NotificationType=1 --GC Text set @process=31 end End ---------------------------Added by swati B to check if Process exist in case of WF -------------end ---Set priority to priority-5 to calls in next 1 minute, priority=priority-4 to calls in next 2 minutes and so on-- if @CallDateTime between getdate() and dateadd(minute,1,getdate()) begin if (@Priority-5)> 0 set @Priority=@Priority-5 end else if @CallDateTime between dateadd(minute,1,getdate()) and dateadd(minute,2,getdate()) begin if (@Priority-4) >0 set @Priority=@Priority-4 end else if @CallDateTime between dateadd(minute,2,getdate()) and dateadd(minute,3,getdate()) begin if (@Priority-3)>0 set @Priority=@Priority-3 end else if @CallDateTime between dateadd(minute,3,getdate()) and dateadd(minute,4,getdate()) begin if (@Priority-2)>0 set @Priority=@Priority-2 end else if @CallDateTime between dateadd(minute,4,getdate()) and dateadd(minute,5,getdate()) begin if (@Priority-1)>0 set @Priority=@Priority-1 end print 'NotificationPayment ' +@NotificationPayment +'Not Type ' print @NotificationType print 'chk' print @NotificationPayment ---create Text for Notification type text(ITR) for Appointment event notification start --to uncomment for testing if @NotificationPayment<>'Y' and @NotificationType='1' begin -- declare @Message as varchar(1000),@Amount as decimal,@InvoiceId as varchar(25),@ProviderNM varchar(25),@RecipientNM varchar(25) select @Message=Message_Text from TextTemplate S where s.NotificationType='text' and s.MessageType=0 and Eventtype='Appointment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM from ARSUser_mst P where P.userID =@UserId select @RecipientNM=R.Fname from patientmaster R where R.Patientid = @PatientID print 'to check msg before000000000000000' print 'message' + @Message print 'Recipient name'+ @RecipientNM print 'Providername'+ @ProviderNM print '@AppDateTime' print @AppDateTime print '@PharmacyPh' print @PharmacyPh set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@AppDateTime),101),''))) --isnull(@AppDateTime,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(CONVERT(varchar(15),cast(@AppDateTime as time),100) ,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) print 'message after actual values' + @Message end ---create Text for Notification type text for Appointment event notification start declare @ClientFile as varchar(150) set @ClientFile='' if @Service=1 begin IF (select Status from ARSUser_mst where UserId=@UserId) IN ('E','T') BEGIN if @NotificationPayment ='Y' Begin --Added by Swati B for Payment notification -Begin print 'Enter Payment &&&&&' set @filenm=@Message print 'First insert' print @DrOfficePh print @CallId insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5, User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@fullRecipientNm,@DrOfficePh,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) End ---Added by Swati B for Payment notification -End select @DigitalId=DigitalId ,@UserName=UserName from logininfo where Userid=@UserId select @DrFileName=[FileName] from CallMessages where DigitalID=@DigitalId and Service=@Service and CallType=10 and Status='Audited' select @patientnm=Fname from PatientMaster where PatientID=@PatientID exec sp_SelectClientNameFile @patientnm,@ClientFile output set @patientnm=@ClientFile select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId --req. to transfer call to Dr's office-- select @langID= languageid,@filenm=[FileName],@NotificationType=[NotificationType] from CallMessages where CallMsgID=dbo.SelectARSPRRFileToPlay (@DigitalId,@PatientID,@CallType,@SubType,@Service,@NotificationType) if @filenm is null begin select @ErrorID=max(ErrorId) from errorlog set @ErrorID=@ErrorID+1 insert into errorlog (ErrorId,ErrorNumber,ErrorMessage,InnerException,ErrorCollectionString,LineNumber,SqlProceName,State,FormName,VbProceName,DtOccured,UserIdLoggedIn)values (@ErrorID,0,'Fatal Error.Recorded message not found.Calls made will have blank message','-','-',95,'sp_inserttocalllist',1,'-','-',getdate(),0) end if @CallType=5 --app reminder call-- begin if @NotificationType='1' set @filenm='' -- if @Iswf=1 -- begin ---- set @Message=@WFMessageTxt -- end set @filenm=@Message print '@filenm'+ @filenm print 'second insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_3,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@AppDateTime,@DrOfficePh,@Message,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType=1 --welcome call-- begin if @NotificationType='1' set @filenm=@Message print 'third insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType in(2,3) /*pT.can or Dr.Can*/ begin if @NotificationType='1' set @filenm=@Message print 'Fourth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@DrOfficePh,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType=4 --well visit call-- begin if @NotificationType='1' set @filenm=@Message print 'fifth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end END ELSE BEGIN ----to uncomment for testing by swati B --print 'Hi' DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end else if @Service=2 begin IF (SELECT STATUS FROM PRRUSER_MST WHERE USERID=@UserId) IN ('E','T') BEGIN select @DigitalId=DigitalId ,@UserName=UserName from logininfo where Userid=@UserId select @DrFileName=[FileName] from CallMessages where DigitalID=@DigitalId and Service=@Service and CallType=10 and Status='Audited' select @langID= languageid, @filenm=[FileName] from CallMessages where CallMsgID=dbo.SelectARSPRRFileToPlay (@DigitalId,@PatientID,1,0,@Service,DEFAULT) -- call type 1 used always select @patientnm=Fname from PatientMaster where PatientID=@PatientID exec sp_SelectClientNameFile @patientnm,@ClientFile output set @patientnm=@ClientFile if @filenm is null begin select @ErrorID=max(ErrorId) from errorlog set @ErrorID=@ErrorID+1 insert into errorlog (ErrorId,ErrorNumber,ErrorMessage,InnerException,ErrorCollectionString,LineNumber,SqlProceName,State,FormName,VbProceName,DtOccured,UserIdLoggedIn)values (@ErrorID,0,'Fatal Error.Recorded message not found.Calls made will have blank message','-','-',95,'sp_inserttocalllist',1,'-','-',getdate(),0) end if @PharmacyPh=null or rtrim(@PharmacyPh)='' or ltrim(@PharmacyPh)='' --non transfer- begin if @NotificationType='1' set @filenm=@Message print 'sixth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@PharmacyPh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else --transfer call- begin if @NotificationType='1' set @filenm=@Message print 'seventh insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5, User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@PharmacyPh,@filenm,@langID,@PharmacyPh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end END ELSE BEGIN print 'Hello' --to uncomment for testing by swati B DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end else if @Service=3 begin IF (SELECT STATUS FROM GCUSER_MST WHERE USERID=@UserId) IN ('E','T') BEGIN --commented by swati B as discussed with sachin pawar --set @PersonName=@PersonName+'.vox' ---select filename for gc----- if @filenm is not null begin if @TranferPh is null set @TranferPh='' if @TranferPh is null or ltrim(@TranferPh)='' or rtrim(@TranferPh)='' begin if @NotificationType='1' -- set @filenm=@Message print 'eightth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@PersonName,@filenm,@langID,@CallerId,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else begin if ltrim(@CallerId)='' or rtrim(@CallerId)='' or @CallerId is null set @CallerId = @TranferPh -- if CallerId is empty, show transfer phone as caller-id if @NotificationType='1' -- set @filenm=@Message print 'ninth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@PersonName,@TranferPh,@filenm,@langID,@CallerId,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end end END ELSE BEGIN DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end insert into scheduledCallsHistory select * from scheduledcalls where CallID=@CallId delete from scheduledCalls where CallID=@CallId --Added by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply start --if @NotificationType=1 --Begin --if not exists (select * from [Text_Msg_Log] where call_id=@CallId and status='U') -- INSERT INTO [Text_Msg_Log] SELECT * FROM Call_List where notificationid=1 AND call_id=@CallId --End --Added by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply End FETCH NEXT FROM ScheduledCalls_Cursor into @CallType,@CallId,@Service,@SourceKey,@TelNo,@CallDateTime,@Priority,@SubType,@PersonName,@WFStep,@BefAfter END CLOSE ScheduledCalls_Cursor DEALLOCATE ScheduledCalls_Cursor END go --------------------- ----------swati end -------swati start IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ChkCreditRemaining') DROP PROCEDURE sp_ChkCreditRemaining GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: <26-04-2016 -- Description: ---Calling : --declare @Out bit --exec [sp_ChkCreditRemaining] 680,12896,5,0,1,null,@Out output --print @Out -- ============================================= create PROCEDURE [dbo].[sp_ChkCreditRemaining] ( @DoctorID int,@PatientId int,@CallTypeID int,@IsWF int,@Step int,@BefAfter int,@Out bit output ) AS Begin DECLARE @AvailCents float,@PackageId int,@NotType int,@MinCredit float, @CreditsPerText float,@CreditsPerEmail float, @CreditsPerCall float -- Add the T-SQL statements to compute the return value here select @AvailCents=Available_Cents_Credit,@PackageId=PackageId from [Provider_Usage] where customer_id=@DoctorID print @AvailCents print '@AvailCents' print @PackageId print '@PackageId' select @CreditsPerCall=CreditsPerCall,@CreditsPerText=CreditsPerText,@CreditsPerEmail=CreditsPerEmail from Package_mst where PackageID=@PackageId print '@CreditsPerCall' print @CreditsPerCall select @NotType=NotificationType from patientmaster where PatientID=@PatientId if (@IsWF=1) ---if workflow setting is there pick notification type from workflow begin print '@IsWF=1 insert' if (@Step=1) select @Nottype=Preference1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID else if (@Step=2) select @Nottype=Preference2 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID else if (@Step=3) select @Nottype=Preference3 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID end print '@NotType' print @NotType if (@NotType=0) begin print 'insert Call' set @MinCredit=@CreditsPerCall*3 end else if (@NotType=1) begin print 'insert Text' set @MinCredit=@CreditsPerText*3 end else if (@NotType=2) begin print 'insert mail' set @MinCredit=@CreditsPerEmail*3 end ----now check for available credits if (@AvailCents>=@MinCredit) set @Out= 1 else begin set @Out= 0 end print '@out' End go -------------------- /****** Object: StoredProcedure [dbo].[sp_insertAppointmentMaster] Script Date: 04/27/16 1:48:24 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --This procedure inserts data in appointment master --Altered by swati B to add changes for payment notification (add param @CallTypeID) --Altered by swati B to add changes for WF based notification (add column in AppointmentMaster for WFStep and IsWF) --Altered by swati B to add check for available credits ALTER PROCEDURE [dbo].[sp_insertAppointmentMaster] ( @DoctorID int, @PatientId int, @BooKingDate datetime, @AppointmentDate datetime,@RemindBeforeNDays tinyint, @StartTime DateTime, @EndTime Datetime, @Reason varchar(200), @OtherInfo varchar(200) ,@SubCallTypeID int, @ScheduleThisCall bit, @ispremimum_feature int, @CallTypeID int, --Added by swati B for payment notification @ApptID int output ---scope identitychanges by rahul ) AS --declare @ApptID int declare @IsCredRemaining bit declare @IsWF as int if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID) begin if @ispremimum_feature=1 set @IsWF=1 else set @IsWF=0 end else set @IsWF=0 exec [sp_ChkCreditRemaining] @DoctorID,@PatientId,@CallTypeID,@IsWF,1,Null,@IsCredRemaining output if (@IsCredRemaining=1) Begin --To insert into appointment master Insert into AppointmentMaster (DoctorID , PatientID ,BookingDate ,AppointmentDate ,RemindBeforeNDays ,StartTime ,EndTime ,Reason , OtherInfo , Status ,StatusChangeDate ,StatusChangeReason ,SubCallTypeID,RecordStatus,CrDate,WFStep,iswf,IsPremium_Feature) values (@DoctorID ,@PatientId ,@BooKingDate ,@AppointmentDate ,@RemindBeforeNDays ,@StartTime ,@EndTime ,@Reason,@OtherInfo, 0,getDate(),'Appoinment Booked',@SubCallTypeID,0,getdate(),1,@IsWF,@ispremimum_feature) print 'Appointment Booked' --To get id of inserted appt select @ApptID=@@Identity --Get appt date declare @AppointmentDateTime datetime set @AppointmentDateTime=cast(convert(varchar(12), @AppointmentDate) +' '+ convert(varchar(12),@StartTime ,108 ) as datetime) --If scheduleThisCall=1 then call call scheduling procedure if @ScheduleThisCall = 1 exec sp_scheduleAppointment @ApptID,@DoctorID,@PatientId ,@AppointmentDateTime,@RemindBeforeNDays,@CallTypeID --replace by folowing procedure --exec sp_scheduleARSPRRCall 1 ,@ApptID,@DoctorID,@PatientId ,@AppointmentDateTime,@RemindBeforeNDays ,'' --last parameter is pharmacyphone which is required only for PRR*/ return @ApptID End else begin print 'Your credit balance is not suuficient to schedule a event' return 0 end go ----------------------------------- /****** Object: StoredProcedure [dbo].[sp_ScheduleMsgAfterWF] Script Date: 04/27/16 4:32:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 16-04-2016 -- Description: schedule Workflow calls for 3rd steps message after --Altered by swati B to add check for available credits on 27-04-2016 -- ============================================= ALTER PROCEDURE [dbo].[sp_ScheduleMsgAfterWF] AS BEGIN declare @CallDateTime datetime,@msgAfterdttime datetime,@ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int,@NewCallID int,@Digitalid int,@CallMsgID int,@BefAfter int CREATE TABLE #AppId ( ApptID int, cusrrdate date, beforemsgdt date, DoctorID int, AppointmentDate datetime, MsgIdAfterDueDt int, msgAfterdttime datetime, CallTypeID int ) INSERT #AppId select AppID,CONVERT(VARCHAR(10),getdate(),111) cusrrdate,CONVERT(VARCHAR(10),DATEADD(day, N.DurAfter, AP.AppointmentDate),111) Aftermsgdt,ap.DoctorID,AppointmentDate,N.MsgIdAfterDueDt, DATEADD(day, N.DurAfter, AP.AppointmentDate) msgAfterdttime,n.notificationid from AppointmentMaster AP join NotificationWorkFlow N on AP.DoctorID=N.ProviderId where n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) and AP.ISWF=1 and AP.[NoShow/UnPaid]=1 -- and CONVERT(VARCHAR(10),DATEADD(day, N.DurAfter, AP.AppointmentDate),111)=CONVERT(VARCHAR(10),getdate(),111) select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,msgAfterdttime,CallTypeID from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@msgAfterdttime,@CallTypeID WHILE @@FETCH_STATUS = 0 begin print '@@CallTypeID' print @CallTypeID --get fields from [AppointmentMaster] select @DoctorID=ap.DoctorID, @PatientId=ap.PatientID, @AppointmentDateTime=ap.AppointmentDate, @RemindBeforeNDays =ap.RemindBeforeNDays from [AppointmentMaster] ap join ARSCallSchedule a on a.apptid=ap.appid where appid=@ApptID and ISWF=1 and a.CallType=@CallTypeID print '@PatientId' print @PatientId Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID print '@Digitalid ' print @Digitalid print '@SubCallTypeID ' print @SubCallTypeID set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId select @CallDateTime=msgAfterdttime from #AppId select @CallTypeID=CallType from ARSCallSchedule where ApptID=@ApptID set @Step=3 set @BefAfter=2 declare @IsCredRemaining bit exec [sp_ChkCreditRemaining] @DoctorID,@PatientId,@CallTypeID,1,3,@BefAfter,@IsCredRemaining output if (@IsCredRemaining=1) Begin --insert into ARSCallSchedule exec sp_generateCallID @NewCallID output print 'NewCallID ' print @NewCallID if not exists(select Arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=@Step and BefAfter=@BefAfter) begin if getdate()<@CallDateTime begin print 'insert' insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep,BefAfter) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,@Step,@BefAfter) end End else begin delete from #AppId where ApptID=@ApptID and CallTypeID=@CallTypeID end end FETCH NEXT FROM Appcursor INTO @ApptID,@msgAfterdttime,@CallTypeID end CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END go ----------------------------------- /****** Object: StoredProcedure [dbo].[sp_ScheduleMsgBeforeWF] Script Date: 04/27/16 4:26:35 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 16-04-2016 -- Description: schedule Workflow calls for 3rd steps message before --Altered by swati B to add check for available credits on 27-04-2016 -- ============================================= ALTER PROCEDURE [dbo].[sp_ScheduleMsgBeforeWF] AS BEGIN declare @CallDateTime datetime,@msgbeforedttime datetime,@ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int,@NewCallID int,@Digitalid int,@CallMsgID int,@BefAfter int CREATE TABLE #AppId ( ApptID int, cusrrdate date, beforemsgdt date, DoctorID int, AppointmentDate datetime, MsgIdBeforeDueDt int, msgbeforedttime datetime, CallTypeID int ) INSERT #AppId select AppID,CONVERT(VARCHAR(10),getdate(),111) cusrrdate,CONVERT(VARCHAR(10),DATEADD(day, -N.DurBefore, AP.AppointmentDate),111) beforemsgdt,ap.DoctorID,AppointmentDate,N.MsgIdBeforeDueDt, DATEADD(day, -N.DurBefore, AP.AppointmentDate) msgbeforedttime,n.notificationid from AppointmentMaster AP join NotificationWorkFlow N on AP.DoctorID=N.ProviderId --where CONVERT(VARCHAR(10),DATEADD(day, -N.DurBefore, AP.AppointmentDate),111)=CONVERT(VARCHAR(10),getdate(),111) and n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) where n.notificationid=(select top 1 (calltype) from ARSCallSchedule where apptid=ap.appid) and AP.ISWF=1 --and AP.NoShow/UnPaid=1 --and AppID=21967 select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,msgbeforedttime,CallTypeID from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@msgbeforedttime,@CallTypeID WHILE @@FETCH_STATUS = 0 begin print '@@CallTypeID' print @CallTypeID --get fields from [AppointmentMaster] select @DoctorID=ap.DoctorID, @PatientId=ap.PatientID, @AppointmentDateTime=ap.AppointmentDate, @RemindBeforeNDays =ap.RemindBeforeNDays from [AppointmentMaster] ap join ARSCallSchedule a on a.apptid=ap.appid where appid=@ApptID and ISWF=1 and a.CallType=@CallTypeID print '@PatientId' print @PatientId Declare @SubCallTypeID int Select @SubCallTypeID=SubCallTypeID from AppointmentMaster where AppID=@ApptID print '@Digitalid ' print @Digitalid print '@SubCallTypeID ' print @SubCallTypeID set @CallMsgID=dbo.SelectARSPRRFileToPlay(@Digitalid ,@PatientId ,5,@SubCallTypeID,1,DEFAULT) print @CallMsgID declare @DayPhone varchar(12) declare @DayTimeStart datetime declare @DayTimeEnd datetime declare @OtherPhone varchar(12) declare @PTZ as tinyint select @DayPhone=DayPhone , @DayTimeStart=DayTimeStart , @DayTimeEnd=DayTimeEnd , @OtherPhone=OtherPhone,@PTZ=TimeZone from PatientMaster where PatientID=@PatientId print '@DayPhone' print @DayPhone select @CallDateTime=msgbeforedttime from #AppId select @CallTypeID=CallType from ARSCallSchedule where ApptID=@ApptID set @Step=3 set @BefAfter=1 declare @IsCredRemaining bit exec [sp_ChkCreditRemaining] @DoctorID,@PatientId,@CallTypeID,1,3,@BefAfter,@IsCredRemaining output if (@IsCredRemaining=1) Begin --insert into ARSCallSchedule exec sp_generateCallID @NewCallID output print 'NewCallID ' print @NewCallID if isnull(@DoctorID,'')<>'' begin print '@@ApptID' print @ApptID if not exists(select Arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=@Step and BefAfter=@BefAfter) begin print 'insert' insert into ARSCallSchedule (ARSCallID,ApptID,DoctorID,PatientID,PatientDayPhone,DayTimeStart,DayTimeEnd,PatientOtherPhone, AppointmentDateTime,CallDateTime,CallType,CallSubType,CallScheduleStatus,CallMsgID,ScheduledFlag,wfstep,BefAfter) values(@NewCallID,@ApptID,@DoctorID,@PatientID,@DayPhone,@DayTimeStart,@DayTimeEnd,@OtherPhone, @AppointmentDateTime,@CallDateTime,@CallTypeID,@SubCallTypeID,1,@CallMsgID,0,@Step,@BefAfter) End else begin delete from #AppId where ApptID=@ApptID and CallTypeID=@CallTypeID end end End FETCH NEXT FROM Appcursor INTO @ApptID,@msgbeforedttime,@CallTypeID end CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END go ------------------- /****** Object: StoredProcedure [dbo].[sp_scheduleWFCalls] Script Date: 04/27/16 3:16:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 23-02-2016 -- Description: schedule Workflow calls for all other steps --Altered by swati B to add check for available credits on 27-04-2016 -- ============================================= ALTER PROCEDURE [dbo].[sp_scheduleWFCalls] AS BEGIN declare @ApptID int,@CallTypeID int,@DoctorID int,@PatientId int,@AppointmentDateTime datetime,@RemindBeforeNDays int, @Isskip bit,@DurResBased int, @ArsMaxwfcallid int,@Arswfstep int,@arscalldt datetime,@step int,@Days as int,@appStatus int CREATE TABLE #AppId ( ApptID int, CallType int, call_id int, --maxdate datetime ) INSERT #AppId --to comment for testing --select a.ApptID, CallType,max(c.call_id) callid from call_list c join ARSCallSchedule A join [AppointmentMaster] ap -- on ap.appid=a.ApptID -- on A.ARSCallID=c.call_id --where c.Status<>'U' and c.Status<>'QUD' and ap.Iswf=1 group by a.ApptID ,CallType,ap.wfstep select a.ApptID, CallType,max(A.ARSCallID) callid from ARSCallSchedule A join [AppointmentMaster] ap on ap.appid=a.ApptID join notificationworkflow N on N.providerid=A.DoctorID where ap.Iswf=1 and A.wfstep in (1,2) and ap.IsPremium_Feature=1 and N.isskip=0 group by a.ApptID ,CallType select * from #AppId DECLARE Appcursor cursor static For SELECT ApptID,CallType from #AppId OPEN Appcursor FETCH NEXT FROM Appcursor INTO @ApptID,@CallTypeID WHILE @@FETCH_STATUS = 0 BEGIN select @DoctorID=DoctorID, @PatientId=PatientID,@AppointmentDateTime=AppointmentDate,@RemindBeforeNDays =RemindBeforeNDays--@Wfstep=wfstep from [AppointmentMaster] where appid=@ApptID and ISWF=1 ---------------------- select @Step=wfstep,@appStatus=status from appointmentmaster where appid=@ApptID select @ArsMaxwfcallid=max(ARSCallID) from ARSCallSchedule where apptid=@ApptID and wfstep in (1,2) group by apptid --print @ArsMaxwfcallid select @Arswfstep=wfstep,@arscalldt=CallDateTime from ARSCallSchedule where arscallid=@ArsMaxwfcallid --print @Arswfstep --print 'step' --print @Step --print '@Arswfstep' --print @Arswfstep ------------------ select @Isskip=isskip,@DurResBased=DurResBased from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID declare @IsCredRemaining bit exec [sp_ChkCreditRemaining] @DoctorID,@PatientId,@CallTypeID,1,2,Null,@IsCredRemaining output if (@IsCredRemaining=1) Begin --print '@Isskip' --print @Isskip if @Isskip!=1 BEGIN if (@Step in (1,2)) --and ((@Arswfstep=1) or (@Arswfstep=2 and CONVERT(VARCHAR(10),@arscalldt,111)@now1 BEGIN if not exists (select arscallid from ARSCallSchedule where ApptID=@ApptID and wfstep=2 and scheduledflag=0) BEGIN --print 'exec' --insert data in schedule appointment table exec [sp_scheduleAppointmentWF_Next] @ApptID,@DoctorID,@PatientId,@AppointmentDateTime,@RemindBeforeNDays, @CallTypeID--,@Wfstep END else BEGIN --print '2nd step record already exists but not scheduled ' delete from #AppId where ApptID=@ApptID END END else Begin --print 'Appointment date is passed' delete from #AppId where ApptID=@ApptID End END else BEGIN delete from #AppId where ApptID=@ApptID END End End FETCH NEXT FROM Appcursor INTO @ApptID,@CallTypeID END CLOSE Appcursor DEALLOCATE Appcursor select * from #AppId drop table #AppId END Go ------------------ GO /****** Object: StoredProcedure [dbo].[sp_ViewGCStatus] Script Date: 04/28/2016 09:00:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_ViewGCStatus] @GCProjectID as int as --select gcproject.calllistid,GCCallSchedule.personname, substring(USER_DATA_2,0,CHARINDEX('.',USER_DATA_2)) USER_DATA_2, PHONE_NUMBER, gcproject.transferphone, --convert(varchar(20),DATE_TIME_STAMP)DATE_TIME_STAMP,CALL_RESULT, --STATUS from GCPROJECT join GCCallSchedule on gcproject.gcprojectid=GCCallSchedule.gcprojectid join Call_List_History on GCCallSchedule.GCCALLID=Call_List_History.call_id --WHERE (left(SourceKey,1)='G' AND convert(varchar(12),gcproject.GCProjectID) = right(SourceKey,len(sourcekey)-1)) --and gcproject.gcprojectid= @GCProjectID and Status in ('D0','D3','M','R','X','H','T' ) --status <>'R' --order by USER_DATA_2 --Add by samir create table #tmp (gcCallid int,gcprojectid int,calllistid varchar(10),personname varchar(350),USER_DATA_2 varchar(300),PHONE_NUMBER varchar(150),transferphone varchar(150),DATE_TIME_STAMP varchar(150),CALL_RESULT varchar(120),STATUS varchar(20) ) insert into #tmp (gcCallid,personname,gcprojectid) select gcCallid,personname,gcprojectid from GCCallSchedule where GCprojectid=@GCProjectID insert into #tmp (gcCallid,personname,gcprojectid) select gcCallid,personname,gcprojectid from GCCallSchedulehistory where GCprojectid=@GCProjectID Update #tmp set USER_DATA_2=CHARINDEX('.',call_list.USER_DATA_2),PHONE_NUMBER=call_list.PHONE_NUMBER, DATE_TIME_STAMP=CONVERT(VARCHAR(10), call_list.DATE_TIME_STAMP, 101) + ' '+ LTRIM(RIGHT(CONVERT(CHAR(20), call_list.DATE_TIME_STAMP, 22), 11)), CALL_RESULT=call_list.CALL_RESULT,STATUS=call_list.STATUS from call_list, #tmp where gcCallid=call_Id Update #tmp set transferphone=GCPROJECT.transferphone from GCPROJECT ,#tmp where GCPROJECT.gcprojectid=#tmp.gcprojectid select * from #tmp GO --Rahul Edit Appointmentaster--- USE [testigetnotified] GO /****** Object: StoredProcedure [dbo].[sp_insertAppointmentMaster] Script Date: 04/28/2016 10:30:19 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --This procedure inserts data in appointment master --Altered by swati B to add changes for payment notification (add param @CallTypeID) --Altered by swati B to add changes for WF based notification (add column in AppointmentMaster for WFStep and IsWF) --Altered by swati B to add check for available credits on 27-04-2016 ALTER PROCEDURE [dbo].[sp_insertAppointmentMaster] ( @DoctorID int, @PatientId int, @BooKingDate datetime, @AppointmentDate datetime,@RemindBeforeNDays tinyint, @StartTime DateTime, @EndTime Datetime, @Reason varchar(200), @OtherInfo varchar(200) ,@SubCallTypeID int, @ScheduleThisCall bit, @ispremimum_feature int, @CallTypeID int, --Added by swati B for payment notification @ApptID int output ---scope identitychanges by rahul ) AS --declare @ApptID int declare @IsCredRemaining bit declare @IsWF as int if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID) begin if @ispremimum_feature=1 set @IsWF=1 else set @IsWF=0 end else set @IsWF=0 exec [sp_ChkCreditRemaining] @DoctorID,@PatientId,@CallTypeID,@IsWF,1,Null,@IsCredRemaining output if (@IsCredRemaining=1) Begin --To insert into appointment master Insert into AppointmentMaster (DoctorID , PatientID ,BookingDate ,AppointmentDate ,RemindBeforeNDays ,StartTime ,EndTime ,Reason , OtherInfo , Status ,StatusChangeDate ,StatusChangeReason ,SubCallTypeID,RecordStatus,CrDate,WFStep,iswf,IsPremium_Feature) values (@DoctorID ,@PatientId ,@BooKingDate ,@AppointmentDate ,@RemindBeforeNDays ,@StartTime ,@EndTime ,@Reason,@OtherInfo, 0,getDate(),'Appoinment Booked',@SubCallTypeID,0,getdate(),1,@IsWF,@ispremimum_feature) print 'Appointment Booked' --To get id of inserted appt select @ApptID=@@Identity --Get appt date declare @AppointmentDateTime datetime set @AppointmentDateTime=cast(convert(varchar(12), @AppointmentDate) +' '+ convert(varchar(12),@StartTime ,108 ) as datetime) --If scheduleThisCall=1 then call call scheduling procedure if @ScheduleThisCall = 1 exec sp_scheduleAppointment @ApptID,@DoctorID,@PatientId ,@AppointmentDateTime,@RemindBeforeNDays,@CallTypeID --replace by folowing procedure --exec sp_scheduleARSPRRCall 1 ,@ApptID,@DoctorID,@PatientId ,@AppointmentDateTime,@RemindBeforeNDays ,'' --last parameter is pharmacyphone which is required only for PRR*/ return @ApptID End else begin print 'Your credit balance is not sufficient to schedule a event' select @ApptID=0 return @ApptID end go --end--- --------------------swati start /****** Object: StoredProcedure [dbo].[sp_updateemailconsumed] Script Date: 04/28/16 11:27:12 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --alter table [Provider_Usage] alter column Available_Cents_Credit int --sp_updateemailconsumed 525,1 ALTER PROCEDURE [dbo].[sp_updateemailconsumed] ( @UserId int ) AS begin declare @Available_Cents_Credit float; declare @Remaining_$_Credit float; declare @EmailConsume int; declare @CreditsPerText float; declare @CreditsPerCall float; declare @CallConsume int; declare @Text_Consumed int; declare @packageid int declare @TotCentsConsumed float; declare @creditsPeremail float; declare @Total_cents_consumed float; declare @Amount money; select @Available_Cents_Credit=[Available_Cents_Credit],@EmailConsume=[Emails_Consumed],@TotCentsConsumed=Total_Cents_Consumed,@CallConsume=[Calls_Units_Consumed],@Text_Consumed=Text_Consumed,@PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and [Service]=1 print @Available_Cents_Credit print @EmailConsume select @creditsPeremail=CreditsPerEmail,@Amount=Amount,@CreditsPerCall=CreditsPerCall,@CreditsPerText=CreditsPerText from Package_mst where PackageID=@packageid and [Service]=1 print '@creditsPeremail' print @creditsPeremail set @EmailConsume=@EmailConsume+1 print @EmailConsume --set @TotCentsConsumed=@TotCentsConsumed+@creditsPeremail --print '@TotCentsConsumed' --print @TotCentsConsumed set @Available_Cents_Credit=@Available_Cents_Credit-@creditsPeremail print '@Available_Cents_Credit' print @Available_Cents_Credit set @Remaining_$_Credit=CAST(@Available_Cents_Credit AS float) / CAST(100 AS float) print '@Total_cents_consumed' print @Total_cents_consumed set @Total_cents_consumed=(@CallConsume*@CreditsPerCall)+(@Text_Consumed*@CreditsPerText)+(@EmailConsume*@CreditsPerEmail) print '@Total_cents_consumed' print @Total_cents_consumed --SELECT CAST(@@Available_Cents_Credit AS float) / CAST(100 AS float) UPDATE [dbo].[Provider_Usage] SEt [Available_Cents_Credit] =@Available_Cents_Credit ,[Emails_Consumed] = @EmailConsume ,Total_cents_consumed=@Total_cents_consumed ,[Remaining_$_Credit] = @Remaining_$_Credit -- ,[Alert_Threshold] = WHERE [Customer_ID]=@UserId and [PackageId]=@packageid and [Status]='Enabled' end --select * from [Provider_Usage] go ----------------------- /****** Object: Trigger [dbo].[trgAfterInsertMsgLog] Script Date: 04/28/16 11:00:47 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[trgAfterInsertMsgLog] ON [dbo].[Text_Msg_Log] FOR INSERT AS ---[Text_Msg_Log] fields declare @Process int; declare @userid int; declare @CALL_ID int; --Packagemst fields declare @CreditsPerText float; declare @CreditsPerEmail float; declare @CreditsPerCall float; declare @SourceKey as varchar(50); --provider purchases fields declare @CreditLimit bigint; declare @NotType int; declare @Remaining_$_Credit float declare @AvailCr float; declare @TotCetsConsumed float; declare @CallConsume int; declare @mail_Consumed int; declare @Text_Consumed int; declare @Packageid int; declare @Total_cents_consumed float declare @Amount money; ---get values select @CALL_ID=i.CALL_ID from inserted i; select @Process=i.Process from inserted i; select @userid=ProviderId from inserted i; select @NotType=NotificationId from inserted i; select @SourceKey=convert(varchar(1),ltrim(rtrim(isnull(i.SourceKey,'')))) from inserted i; --select @PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and [Service]=1 if @SourceKey='G' begin select @PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and [Service]=3 end else begin select @PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and [Service]=1 end --Select CreditsPerCall and CreditsPerText for that package select @CreditsPerCall=CreditsPerCall,@Amount=Amount,@CreditsPerText=CreditsPerText,@CreditsPerEmail=CreditsPerEmail from Package_mst where PackageID=@PackageId print '@PackageId' print @PackageId print '@NotType' print @NotType if @NotType=1 begin select @AvailCr=Available_Cents_Credit,@CallConsume=[Calls_Units_Consumed],@Text_Consumed=Text_Consumed,@mail_Consumed=Emails_Consumed from [Provider_Usage] where [Customer_ID]= @userid and PackageID=@PackageId and ltrim(rtrim([Status]))='Enabled' print 'old Text consumed' print @Text_Consumed set @Text_Consumed=@Text_Consumed+1 print 'new [Text_Units Consumed]' print @Text_Consumed print 'old Available cents credits' print @AvailCr set @AvailCr=@AvailCr-(@CreditsPerText) print 'new Available cents credits' print @AvailCr set @Remaining_$_Credit=CAST(@AvailCr AS float) / CAST(100 AS float) print '@Remaining_$_Credit' print @Remaining_$_Credit set @Total_cents_consumed=(@CallConsume*@CreditsPerCall)+(@Text_Consumed*@CreditsPerText)+(@mail_Consumed*@CreditsPerEmail) print '@Total_cents_consumed' update [Provider_Usage] set Text_Consumed=@Text_Consumed,Available_Cents_Credit=@AvailCr,[Remaining_$_Credit]=@Remaining_$_Credit,Total_cents_consumed=@Total_cents_consumed where customer_id=@userid and PackageId=@PackageId and ltrim(rtrim([Status]))='Enabled' end go -------------------- /****** Object: Trigger [dbo].[trgAfterInsertCallLog] Script Date: 04/28/16 10:51:43 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[trgAfterInsertCallLog] ON [dbo].[Transaction] FOR INSERT AS declare @Process int; declare @userid int; declare @CALL_ID int; declare @CALL_START_TIME datetime; declare @CALL_END_TIME datetime; declare @duration varchar(100); declare @audit_action varchar(100); declare @SourceKey as varchar(50); --Packagemst fields declare @CreditsPerCall int; declare @CreditsPerText int; declare @CreditsPerEmail float; --provider purchases fields declare @CreditLimit bigint; declare @NotType int; declare @NoOfCallconsidered bigint; declare @SecPerCallUnit float; declare @CreditDur float; declare @Remaining_$_Credit float declare @Total_cents_consumed float declare @AvailCr float; declare @CallConsume int; declare @TotCetsConsumed float; declare @Text_Consumed int; declare @mail_Consumed int; declare @Packageid int; declare @Amount money; select @CALL_ID=i.CALL_ID from inserted i; select @CALL_START_TIME=i.CALL_START_TIME from inserted i; select @CALL_END_TIME=i.CALL_END_TIME from inserted i; select @duration=i.Duration from inserted i; select @Process=i.Process from inserted i; select @userid=Providerid from call_List where call_ID= @CALL_ID; select @SourceKey=convert(varchar(1),ltrim(rtrim(isnull(i.SourceKey,'')))) from inserted i; --select Packageid for that user --select @PackageId=NewPackageID from ARSUser_InvoiceInfo where userid=@userid --select @PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and [Service]=1 --Select CreditsPerCall and CreditsPerText for that package if @SourceKey='G' begin select @PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and p.[Service]=3 end else begin select @PackageId=p.packageid from [Provider_Usage] u join Package_mst p on p.packageid=u.packageid where [Customer_ID]= @userid and ltrim(rtrim([Status]))='Enabled' and p.[Service]=1 end select @CreditsPerCall=CreditsPerCall,@Amount=Amount,@CreditsPerText=CreditsPerText,@CreditsPerEmail=CreditsPerEmail,@Amount=Amount from Package_mst where PackageID=@PackageId print '@PackageId' print @PackageId print '@CreditDur' print @CreditDur select @NotType=notificationType from processmaster where processid=@Process print '@NotType' print @NotType if @NotType=0 begin --SELECT @CreditDur=DATEDIFF(SECOND, @CALL_START_TIME, @CALL_END_TIME) set @CreditDur= @duration --get no of seconds per call from system settings table select @SecPerCallUnit=ltrim(rtrim(isnull(ParameterValue,1))) from systemsettings where ltrim(rtrim(Parameter))='Seconds Per Call Unit' print '@SecPerCallUnit' print @SecPerCallUnit set @NoOfCallconsidered= CEILING (@CreditDur/@SecPerCallUnit) select @userid=providerid from call_list where call_id=@CALL_ID print 'NoOfCallconsidered' print @NoOfCallconsidered select @AvailCr=Available_Cents_Credit,@CallConsume=[Calls_Units_Consumed],@Text_Consumed=Text_Consumed,@mail_Consumed=Emails_Consumed from [Provider_Usage] where [Customer_ID]= @userid and PackageID=@PackageId and ltrim(rtrim([Status]))='Enabled' print 'old call consumed' print @CallConsume set @CallConsume=@CallConsume+@NoOfCallconsidered print 'new [Calls_Units Consumed]' print @CallConsume print 'old Available cents credits' print @AvailCr set @AvailCr=@AvailCr- (@NoOfCallconsidered*@CreditsPerCall) print 'new Available cents credits' print @AvailCr set @Remaining_$_Credit=CAST(@AvailCr AS float) / CAST(100 AS float) print '@Remaining_$_Credit' print @Remaining_$_Credit set @Total_cents_consumed=(@CallConsume*@CreditsPerCall)+(@Text_Consumed*@CreditsPerText)+(@mail_Consumed*@CreditsPerEmail) print '@Total_cents_consumed' update [Provider_Usage] set [Calls_Units_Consumed]=@CallConsume,Available_Cents_Credit=@AvailCr,[Remaining_$_Credit]=@Remaining_$_Credit,Total_cents_consumed=@Total_cents_consumed where customer_id=@userid and PackageId=@PackageId and ltrim(rtrim([Status]))='Enabled' print 'new [Calls_Units Consumed]' end PRINT 'AFTER INSERT trigger fired.' go ------------ ---------swati end Go GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Samir -- Description: Show Notifiction Mobile Service -- ============================================= ALTER PROCEDURE [dbo].[Sp_MobileNotification] -- Add the parameters for the stored procedure here @PHONE_NUMBER varchar(30) AS BEGIN SET NOCOUNT ON; create table #tmp( USER_DATA_5 varchar(max),Call_time datetime,Process varchar(50),Call_ID varchar(10) ,ApptID int, calltype int,SourceKey varchar(50),PaidStatus varchar(30)) truncate table #tmp insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey) select substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1),Call_time,Call_ID,SourceKey from call_list where USER_DATA_5 is not null and USER_DATA_5 !='' and status !='U' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey) select substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1),Call_time,Call_ID,SourceKey from Call_List_History where USER_DATA_5 is not null and USER_DATA_5 !='' and status !='U' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc update #tmp set Apptid=ARSCallSchedule.Apptid ,calltype= ARSCallSchedule.calltype from ARSCallSchedule ,#tmp where #tmp.call_id=ARSCallSchedule.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set Apptid=arscallschedulehistory.Apptid ,calltype= arscallschedulehistory.calltype from arscallschedulehistory ,#tmp where #tmp.call_id=arscallschedulehistory.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set PaidStatus= case when statusOfpayment='Done' or responsecode=1 then 'Done' Else 'Pending' end from PaymentDetails pd , #tmp tm where pd.Appid = tm.apptid update #tmp set process=case when calltype=6 then 'NOTIFICATION_TYPE_PAYMENT' else 'NOTIFICATION_TYPE_APPOINTMENT' end select * from #tmp order by Call_time desc END GO GO SET QUOTED_IDENTIFIER ON GO --created ny swati B on 22-04-2016 -- purpose: when user creates new custom package from sign-up / modify details form this procedure will add that package into Package_mst table. ALTER PROCEDURE [dbo].[sp_MofifyProviderPurchaseAndUsage] ( @Invoice_ID int, @Customer_ID int , @PackageId int , @Status varchar(50), @StartDate datetime, -- package enable.disable flag @EndDate datetime, @Available_Cents_Credit float, @oldPackageId int ) AS declare @Duration int select @Duration=Duration from package_mst where PackageID=@PackageId if @Duration=1 ---for monthly subscription set @EndDate= DATEADD(day,30,@StartDate) else if @Duration=2 --for yearly subscription set @EndDate= DATEADD(year,1,@StartDate) BEGIN TRAN BEGIN TRY declare @old_Available_credit float=0 if exists(select [Invoice_ID] from [Provider_Purchases] where [Customer_ID]=@Customer_ID and [Status]='Enabled') update [Provider_Purchases] set [Status]='Disable' where [Customer_ID]=@Customer_ID and packageid=@oldPackageId if exists(select [PackageId] from [Provider_Usage] where [Customer_ID]=@Customer_ID and [Status]='Enabled') begin select @old_Available_credit=[Available_Cents_Credit] from [Provider_Usage] where [Customer_ID]=@Customer_ID and [Status]='Enabled' and packageid=@oldPackageId update [Provider_Usage] set [Status]='Disable' where [Customer_ID]=@Customer_ID and packageid=@oldPackageId set @Available_Cents_Credit=@old_Available_credit+@Available_Cents_Credit End INSERT INTO [dbo].[Provider_Purchases] ([Invoice_ID] ,[Customer_ID] ,[PackageId] ,[Status] ,[StartDate] ,[EndDate]) VALUES (@Invoice_ID,@Customer_ID ,@PackageId ,@Status,@StartDate ,@EndDate) INSERT INTO [dbo].[Provider_Usage] ([Customer_ID] ,[PackageId] ,[Available_Cents_Credit] ,[Calls_Units_Consumed] ,[Text_Consumed] ,[Emails_Consumed] ,[AppNotes_Consumed] ,[Total_Cents_Consumed] ,[Remaining_$_Credit] ,[Alert_Threshold] ,[Status]) VALUES (@Customer_ID ,@PackageId ,@Available_Cents_Credit ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'Enabled') COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH Go /****** Object: StoredProcedure [dbo].[sp_insertMonthlyInvoiceARS] Script Date: 05/02/2016 13:10:46 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO --This procedure inserts monthly invoice for ARS service ALTER Procedure [dbo].[sp_insertMonthlyInvoiceARS] @User_id int, @disabled_deleted bit -- used when user is disabled or deleted AS declare @Address varchar(100), @Phone as varchar(12), @AddlCh as smallmoney, @AddlChDesc as varchar(50), @Disc_Perc as real, @Disc_Flat as smallmoney, @Credit_Perc as real, @Credit_Flat as smallmoney, @CardCompany as varchar(50), @CardNo as varchar(20), @ExpiryDt as varchar(10),@Paymode as bit, @remark as varchar(255), @RecipientNm as varchar(50), @email varchar(200), @PackageId as smallint, @TotalAmount as money, @PromotionKey as varchar(10), @City as char(50), @State as char(2), @CntID as char(3), @Zip as char(10), @PastDueCharges as money,@PastDueChargesDesc as varchar(255),@Print_Flag as bit,@InvoiceDueDate datetime, @Invoicedate as datetime,@StatusOfPayment as int,@count as int,@stdate as datetime,@eddate as datetime,@OrigId AS varchar(50) declare @prv as varchar(255),@UserType as varchar(1) ,@CardTransactionId as varchar(100),@NoOfPromotionMonths as int -----DATES CALCULATED------------------------------------------------------------------- set @stdate=getdate()- (DATEPART(day, GETDATE())-1) if @disabled_deleted=0 begin set @eddate=dateadd(month,1,@stdate) --change end dt to last date of that month (for disabled ad deleted account this has to change) set @eddate=dateadd(day,-1,@eddate) end else begin set @eddate=getdate() end set @stdate=convert(varchar(12),@stdate,101) set @eddate=convert(varchar(12),@eddate,101) -----GET INFORMATION FROM DB------------------------------------------------------------------------------------- declare @Packagenm as varchar(50),@ExtraCallsRate as smallmoney,@PackageCalls as int, @MonthlyCharges as smallmoney declare @Callsdone as int,@ExtraCalls as smallint set @ExtraCalls=0 ; set @CardNo=''; set @Credit_Flat=0 ; set @Credit_Perc=0 ; set @Disc_Flat=0 ; set @Disc_Perc=0;set @AddlCh=0 ; set @MonthlyCharges=0 ; set @RecipientNm='' ; set @Address=' ';set @Phone=''; set @Packagenm='' ; set @CardCompany='' ; set @Remark='' ; set @PastDueCharges=0 ; set @PastDueChargesDesc='' ; set @Invoicedate =getdate() ; declare @Temporary as bit, @IsFlat as bit, @PromotionDiscount as real,@Overdue as int Select @Overdue=ParameterValue from Systemsettings where Parameter= 'Past due invoice period' set @PromotionKey=''; set @PromotionDiscount=0 ; set @MonthlyCharges=0 select @Address=t2.Add1+','+t2.Add2,@City=t2.City, @State=t2.State, @CntID=t2.CntID, @Zip=t2.Zip, @RecipientNm=t2.FirstNM+' '+t2.LastNm, @Phone=t2.BillingPhone, @AddlCh=t3.AddCharges, @AddlChDesc=t3.ChargeDescription, @Disc_Perc=t3.DiscountPercent, @Disc_Flat=t3.FixDiscount, @Credit_Perc=t3.CreditPercent, @Credit_Flat=t3.FixCredit , @Paymode=t3.Paymode, @Remark=t3.Remark, @email=t1.Email, @PromotionKey=t3.PromotionKey, @UserType=t1.UserType from LoginInfo t1,ARSUser_mst t2, ARSUser_InvoiceInfo t3 where t3.UserID =@User_id and t2.UserID =@User_id And t1.UserID=@User_id ----SET PHONE MASK-------------------------------------------------------------------------------------------------------------- declare @phonemask as varchar(12) set @phonemask=substring(@Phone,1,3) set @phonemask = @phonemask + '-' set @phonemask = @phonemask + Substring(@Phone,4, 3) set @phonemask = @phonemask+ '-' set @phonemask = @phonemask+ Substring(@Phone,7, 4) ----SET STATUS OF PAYMENT-------------------------------------------------------------------------------------------------------- if @Paymode=1 --online invoice-- begin select @CardCompany=t4.CardType,@CardNo=t4.CardNo,@OrigId=ORIGID,@ExpiryDt=ExpiryDate from ARSUser_card_dtl t4 where UserID=@User_id if @OrigId is null --check if delayed transaction is to be done Set Flag according means For null origid flag=1 and for not null origid flag=6 begin set @StatusOfPayment=1 set @CardTransactionId=null end else begin set @StatusOfPayment=6 set @CardTransactionId=@OrigId end set @Print_Flag=1 --For online invoices print flag is set to 1 set @InvoiceDueDate=getdate() end else --offline invoice-- begin set @Print_Flag=0 set @StatusOfPayment=0 set @InvoiceDueDate=dateadd(day,@Overdue,@Invoicedate) --system setting for invoicedue date end ----MONTHLY CHARGES CALCULATED------------------------------------------------------------------------------------------- if @disabled_deleted=0 begin -- get monthly charges , package name from new packageid select @Packagenm=Packagenm,@MonthlyCharges=MonthlyCharges from Package_mst where PackageID=(select NewPackageID from ARSUser_InvoiceInfo where UserID=@User_id) end else begin select @Packagenm=Packagenm from Package_mst where PackageID=(select NewPackageID from ARSUser_InvoiceInfo where UserID=@User_id) set @MonthlyCharges=0 end ----EXTRA CALLS CALCULATED------------------------------------------------------------------------------------ set @Callsdone=dbo.GetTotalCallsForUser(1,@User_id,getdate(),@UserType) -- get extra calls from old packageid select @ExtraCallsRate=ExtraCallCharges,@PackageCalls=PackagesCalls from Package_mst where PackageID=(select OldPackageID from ARSUser_InvoiceInfo where UserID=@User_id) if @PackageCalls >= @Callsdone begin set @ExtraCalls=0 end else begin set @ExtraCalls=@Callsdone - @PackageCalls end if @ExtraCalls<0 begin set @ExtraCalls=0 end -----PROMOTION DISCOUNT CALCULATED------------------------------------------------------------------ if @PromotionKey <> '' begin --call function to get promotion key and promotion discount select @count=count(*) from InvoiceMaster where UserID=@User_id and PromotionKey=@PromotionKey select @NoOfPromotionMonths=NumberOfMonths from PromotionMaster where PromotionKey=@PromotionKey if @NoOfPromotionMonths=@count or @count>@NoOfPromotionMonths begin update ARSUser_Invoiceinfo set PromotionKey=' ' where UserID=@User_id set @PromotionDiscount=0.0 set @PromotionKey=' ' end else begin if @MonthlyCharges > 0 begin set @PromotionDiscount = dbo.GetPromotionDiscountForInvoice(@PromotionKey,@MonthlyCharges) end else set @PromotionDiscount=0.0 end end else begin set @PromotionDiscount=0.0 end if @PromotionDiscount is null begin print'null' set @PromotionDiscount=0 end -----TOTAL AMOUNT CALCULATED---------------------------------------------------------------------------------- set @TotalAmount=@MonthlyCharges+ (@ExtraCallsRate*@ExtraCalls) + @AddlCh - ((@Disc_Perc*@MonthlyCharges/100)+@Disc_Flat + (@Credit_Perc*@MonthlyCharges/100)+@Credit_Flat + @PromotionDiscount) ---if @TotalAmount < 0 samir if @TotalAmount < 0 or @TotalAmount is null begin set @TotalAmount=0 end -----GET ALREADY GENERATED INVOICE COUNT -------------------------------------------------------------------------------------- if @disabled_deleted=0 begin --count of invoices already present for the user for ARSservice with same enddate. select @Count= count(*) from InvoiceMaster where Service=1 and UserID=@User_id and EndDate=@eddate end else if @disabled_deleted=1 begin set @Count=0 end ----GENERATE INVOICE------------------------------------------------------------------------------ if @Count =0 --if count if zero then generate invoice begin PRINT @User_id --PastDueCharges are there for (0,1,2,4,7,8) status(0 offline ,4 'Fail' by Credit card company )and Full payment is not received if (select sum(TotalAmount)+sum(PastDueCharges) from InvoiceMaster where UserID=@User_id AND StatusOfPayment in(0,1,2,4,7,8) and Service=1 and PaymentClearanceFlag=0 and InvoiceDueDate < getdate())is not null begin set @prv=dbo.getPastDueCharges(@User_id,1,getdate()) select @PastDueCharges=sum(TotalAmount)+sum(PastDueCharges) from InvoiceMaster where UserID=@User_id AND StatusOfPayment in(0,1,2,4,7,8) and Service=1 and PaymentClearanceFlag=0 and InvoiceDueDate < getdate() update InvoiceMaster set StatusOfPayment=5 WHERE UserID=@User_id AND StatusOfPayment in(0,1,2,4,7,8) and Service=1 and PaymentClearanceFlag=0 and InvoiceDueDate < getdate() end else begin set @prv='' end if @PastDueCharges > 0 set @PastDueCharges = convert(decimal(15,2), @PastDueCharges) --Do not generate invoice for disabled/deleted if outstanding amount is zero. declare @canGenerate as bit set @canGenerate=1 if @disabled_deleted=1 begin print convert(varchar,@TotalAmount+@PastDueCharges) if convert(float,@TotalAmount+@PastDueCharges) <= 0.00 begin set @canGenerate=0 end end if @canGenerate=1 begin insert into InvoiceMaster (InvoiceDate, Service, StartDate, EndDate, UserID, RecipientName, BillingAddress, City, State, CntID, Zip, Phone, Email, RegistrationCharges,MonthlyCharges,NameOfPackage,ExtraCalls,ExtraCallsRate,AdditionalCharges, Additional_charges_Description,Discount_Percent,Flat_Discount,Credit_Percent,Flat_Credit, PastDueCharges,PastDueChargesDescr,InvoiceDueDate,TotalAmount,Remark,Print_flag,StatusOFPayment, PaymentClearanceFlag,PaymentReceived,PromotionKey,PromotionDiscount,CardTransactionId,Advance,CreditCardNo,CardCompany,ExpiryDate) values (@Invoicedate ,1, @stdate,@eddate ,@User_id ,@RecipientNm ,@Address ,@City,@State,@CntID, @Zip, @phonemask, @email ,0,@MonthlyCharges,@Packagenm ,@ExtraCalls ,@ExtraCallsRate ,@AddlCh ,@AddlChDesc , @Disc_Perc ,@Disc_Flat ,@Credit_Perc ,@Credit_Flat,@PastDueCharges ,@prv ,@InvoiceDueDate , @TotalAmount ,@Remark ,@Print_Flag,@StatusOfPayment,0,0,@PromotionKey,@PromotionDiscount,@OrigId,0,@CardNo,@CardCompany,@ExpiryDt) declare @InvoiceNo as int set @InvoiceNo =@@Identity exec sp_doChangesAfterInvoiceGeneration @User_id,1,@InvoiceNo, 0 end end Go USE [testigetnotified] GO /****** Object: StoredProcedure [dbo].[sp_providerstatusTest] Script Date: 05/03/2016 16:39:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_providerstatusTest] @drid int, @fromdate datetime, @todate datetime as begin SET NOCOUNT ON IF 1=0 BEGIN SET FMTONLY OFF END --print convert(varchar(10), @fromdate, 111) declare @totalcount int Create table #Tmp (StatusCount int ,StatusID int, EventStatus nvarchar(250) ,percentage Decimal(18,2) ) select @totalcount=count(status) from AppointmentMaster,patientmaster where Status IN (0,1,2,3,6,7) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) AND cast(appointmentdate as smalldatetime) BETWEEN @fromdate AND @todate Insert Into #Tmp (StatusID,StatusCount,Eventstatus) values (0,0,'Events Pending') Insert Into #Tmp (StatusID,StatusCount,Eventstatus)values (1,0,'Events Confirmed') Insert Into #Tmp (StatusID,StatusCount,Eventstatus)values (7,0,'Events With Calls Delivered') Insert Into #Tmp (StatusID,StatusCount,Eventstatus)values (2,0,'Events Cancelled') Insert Into #Tmp (StatusID,StatusCount,Eventstatus)values (3,0,'Rescheduled') Insert Into #Tmp (StatusID,StatusCount,Eventstatus)values (6,0,'Unsuccessful call') -- Events Pending Declare @pending int Select @pending=count(status) From AppointmentMaster,patientmaster Where Status IN (0) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) Group by status Update #Tmp Set StatusCount=@pending Where statusid=0 -- confirmed Declare @confirmed int Select @confirmed=count(status) From AppointmentMaster,patientmaster Where Status IN (1) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) Group by status Update #Tmp set StatusCount=@confirmed where statusid=1 -- Events With Calls Delivered Declare @EWD int Select @EWD=count(status) From AppointmentMaster,patientmaster Where Status IN (7) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) Group by status Update #Tmp Set StatusCount=@EWD Where statusid=7 -- Events Cancelled Declare @ECan int Select @ECan=count(status) From AppointmentMaster,patientmaster Where Status IN (2) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) Group by status Update #Tmp set StatusCount=@ECan where statusid=2 --- Rescheduled Declare @Rescheduled int Select @Rescheduled=count(status) From AppointmentMaster,patientmaster Where Status IN (3) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) group by status Update #Tmp set StatusCount=@Rescheduled where statusid=3 --- Unsuccessful call Declare @Unsuccessful int Select @Unsuccessful=count(status) from AppointmentMaster,patientmaster where Status IN (6) AND DoctorID=@drid AND AppointmentMaster.patientid=patientmaster.patientid AND convert(varchar(10), appointmentdate, 111) BETWEEN convert(varchar(10), @fromdate, 111) AND convert(varchar(10), @todate, 111) Group by status Update #Tmp Set StatusCount=@Unsuccessful Where statusid=6 --- Calc percentage Update #Tmp Set StatusCount = 0 Where StatusCount is null Update #Tmp set percentage = ((StatusCount)*100.00)/@totalcount Select StatusID,Eventstatus,StatusCount , percentage From #Tmp end GO ------------ --swati start /****** Object: StoredProcedure [dbo].[sp_ChkCreditRemaining] Script Date: 05/04/16 11:38:24 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: <26-04-2016 -- Description: --Altered by swati ro add 0 credits condition on 04-05-2016 ---Calling : --declare @Out bit --exec [sp_ChkCreditRemaining] 718,12907,5,0,1,null,@Out output --print @Out --select u.PackageId from [Provider_Usage] u join package_mst P on p.packageid=u.packageid where u.customer_id=718 and [status]='Enabled' and p.service=1 --select * from [Provider_Usage] where customer_id=718 -- ============================================= ALTER PROCEDURE [dbo].[sp_ChkCreditRemaining] ( @DoctorID int,@PatientId int,@CallTypeID int,@IsWF int,@Step int,@BefAfter int,@Out bit output ) AS Begin DECLARE @AvailCents float,@PackageId int,@NotType int,@MinCredit float, @CreditsPerText float,@CreditsPerEmail float, @CreditsPerCall float if exists (select u.PackageId from [Provider_Usage] u join package_mst P on p.packageid=u.packageid where u.customer_id=@DoctorID and [status]='Enabled' and p.service=1) begin -- Add the T-SQL statements to compute the return value here select @AvailCents=Available_Cents_Credit,@PackageId=u.PackageId from [Provider_Usage] u join package_mst P on p.packageid=u.packageid where u.customer_id=@DoctorID and [status]='Enabled' and p.service=1 if @AvailCents>0 Begin print @AvailCents print '@AvailCents' print @PackageId print '@PackageId' select @CreditsPerCall=CreditsPerCall,@CreditsPerText=CreditsPerText,@CreditsPerEmail=CreditsPerEmail from Package_mst where PackageID=@PackageId print '@CreditsPerCall' print @CreditsPerCall select @NotType=NotificationType from patientmaster where PatientID=@PatientId if (@IsWF=1) ---if workflow setting is there pick notification type from workflow begin print '@IsWF=1 insert' if (@Step=1) select @Nottype=Preference1 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID else if (@Step=2) select @Nottype=Preference2 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID else if (@Step=3) select @Nottype=Preference3 from NotificationWorkFlow where ProviderId=@DoctorID and NotificationId=@CallTypeID end print '@NotType' print @NotType if (@NotType=0) begin print 'insert Call' set @MinCredit=@CreditsPerCall*3 end else if (@NotType=1) begin print 'insert Text' set @MinCredit=@CreditsPerText*3 end else if (@NotType=2) begin print 'insert mail' set @MinCredit=@CreditsPerEmail*3 end ----now check for available credits if (@AvailCents>=@MinCredit) set @Out= 1 else begin set @Out= 0 end print '@out' end else begin set @Out= 0 end End else Begin set @Out= 0 End End go ----------- ----------swati End -----------swati start --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_InsertDoctorWorkingPrefrences') DROP PROCEDURE sp_InsertDoctorWorkingPrefrences GO -- This procedure insert records into doctor working prefrences table ---Altered by swati to save @EffectiveTill Create PROCEDURE sp_InsertDoctorWorkingPrefrences @DoctorId as int, @EffectiveFrom as datetime, @EffectiveTill as datetime, @MinAppointmentPeriod as int, @MaxAppointmentPerDay as int , @MaxDoubleAppointments as int, @MaxFutureMonths as int, @Add as int --if add flag =1 then insert record else modify AS --if add flag =1 then insert record if @add=1 begin --if effective from date is existing one then delete records for that delete from DoctorWorkingsPrefs where DoctorId=@DoctorId and @EffectiveFrom= EffectiveFrom --to insert in to working prefrences table INSERT INTO DoctorWorkingsPrefs (Doctorid, EffectiveFrom,MinAppointmentPeriod,MaxAppointmentPerDay,MaxDoubleAppointments,MaxFutureMonths,EffectiveTill) Values(@DoctorId,@EffectiveFrom,@MinAppointmentPeriod,@MaxAppointmentPerDay,@MaxDoubleAppointments,@MaxFutureMonths,@EffectiveTill) end else --if add flag <> 1 then update record begin Update DoctorWorkingsPrefs set MinAppointmentPeriod=@MinAppointmentPeriod,MaxAppointmentPerDay=@MaxAppointmentPerDay,MaxDoubleAppointments=@MaxDoubleAppointments,MaxFutureMonths=@MaxFutureMonths,EffectiveTill=@EffectiveTill where DoctorId=DoctorId and EffectiveFrom=@EffectiveFrom end Go --------------------------------------------------------------------------------- GO /****** Object: StoredProcedure [dbo].[sp_arsapptstausweekly] Script Date: 5/9/2016 12:51:43 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_arsapptstausweekly] @drid int, @startdate datetime, @enddate datetime as begin declare @appttable table(id int identity(1,1),apptdata varchar(max),apptday varchar(50)) declare @counter int declare @totalrows int declare @id int declare @apptdata varchar(max) declare @weekdays table(id int identity(1,1),weekdayname varchar(50)) declare @counter_weekdays int declare @totalrows_weekdays int declare @weekday_name varchar(50) IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable create table #temptable (id int identity(1,1),apptdata varchar(max),apptday varchar(50)) insert into @weekdays values('Monday') insert into @weekdays values('Tuesday') insert into @weekdays values('Wednesday') insert into @weekdays values('Thursday') insert into @weekdays values('Friday') insert into @weekdays values('Saturday') insert into @weekdays values('Sunday') set @counter_weekdays=1 select @totalrows_weekdays=count(*) from @weekdays while @counter_weekdays<=@totalrows_weekdays begin select @weekday_name=weekdayname from @weekdays where id=@counter_weekdays --truncate table before to insert truncate table #temptable --get data for the selected day insert into #temptable Select cast(Appointmentmaster.AppointmentDate as varchar(100))+ ' '+(PatientMaster.Lname +', '+ PatientMaster.FName),'Monday' From AppointmentMaster,PatientMaster Where AppointmentDate Between @startdate And @enddate And DoctorId=@drid And Appointmentmaster.Patientid=PatientMaster.Patientid And (Appointmentmaster.NoShowReschedule is NULL Or Appointmentmaster.NoShowReschedule=0) AND DATENAME(WEEKDAY,[AppointmentDate])=@weekday_name order by Appointmentmaster.AppointmentDate set @totalrows=0 set @counter=1 select @totalrows=count(*) from #temptable while @counter<=@totalrows begin set @apptdata=null select @apptdata=apptdata from #temptable where id=@counter if not exists(select * from @appttable where apptday=@weekday_name) insert into @appttable select @apptdata,@weekday_name else update @appttable set apptdata=apptdata+'/'+@apptdata where apptday=@weekday_name set @counter=@counter+1 end --increment the day set @counter_weekdays=@counter_weekdays+1 end --select * from @appttable SELECT * FROM ( SELECT [apptdata], [apptday] FROM @appttable ) AS source PIVOT ( max([apptdata]) FOR [apptday] IN ([Monday], [Tuesday], [Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) ) as pvt end Go ------------------------- ----------swati start /****** Object: StoredProcedure [dbo].[sp_insertToCallList] Script Date: 05/10/16 11:38:28 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- REVISED FOR NOT PLACING CALLS FOR DISABLED , DELETED USERS - 27 JUNE 06 -- Purpose: To insert record into Call_List table from ScheduledCalls table --Altered by Swati B to Add Payment notifications --Altered by swati to add changes for notification type=text (pick text message from Text_message table replace the variables with actual vaiables ---and set to set @filenm=@Message and insert into user_data_5 coloumn of call_list table ---Altered by Swati B to Add Workflow based notification ---Altered by Swati B to Add @DoctorEmail,@RecipientEmail for email notification 18/03/2016 ---Altered by Swati B to Add encrypt param of payment 19/03/2016 ---Altered by Swati B to Comment encryption param of payment 28/03/2016 since encrypted format is not sent through plumvoice --Altered by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply End on 29-03-2016 ---Rolled --Altered by swati to insert entry for assign process master for GC text on 27-04-2016 ---Altered by swati to insert langid for EN service. 10-04-2016 ALTER procedure [dbo].[sp_insertToCallList] as Begin declare @process as int=0,@UserId as int,@UserName as varchar(100),@AudioFolder as varchar(256),@PatientID as int, @CallId as int,@Service as tinyint,@SourceKey as varchar(50),@TelNo as varchar(12),@CallDateTime as datetime, @Priority as smallint,@CallType as int,@SubType as tinyint,@patientnm as varchar(100),@AppDateTime as datetime, @DrOfficePh as varchar(12),@Status as varchar(3),@filenm as varchar(1000),@DigitalId as int,@DrFileName as varchar(51) declare @langID as char(2), @ErrorID as integer, @PersonName as varchar(100),@PharmacyPh as varchar(12),@CallerId as varchar(12) declare @TranferPh as varchar(12),@CallListId smallint declare @fullRecipientNm as varchar(100) Declare @NotificationPayment as varchar(3)----Added by Swati B for Payment notification Declare @WFStep as int ----Added by Swati B for WF based notification-- 9/2/15 dev1 declare @NotificationType as smallint,@BefAfter int declare @DoctorEmail as varchar(200),@RecipientEmail as varchar(200) ---Added by Swati B to Add @DoctorEmail,@RecipientEmail for email notification 18/03/2016 declare @encrParam as NVARCHAR(100) -- Added by Swati B to Add encrypt param of payment 19/03/2016 declare @Tempid3 int declare @InvoiceId as varchar(25)='' set @Status='U' declare @date1 as datetime set @date1=dateadd(minute,5,getdate()) set @NotificationPayment='N' declare @IsWF int=0 declare @appid int declare @IsPremium_Feature int declare @WFMessageTxt as varchar(200)='' DECLARE ScheduledCalls_Cursor CURSOR static FOR select CallType,CallID,Service,SourceKey,TelNo,CallDateTime,Priority,SubType,PersonName,WFStep,BefAfter from ScheduledCalls where CallDateTime < @date1 OPEN ScheduledCalls_Cursor FETCH NEXT FROM ScheduledCalls_Cursor into @CallType,@CallId,@Service,@SourceKey,@TelNo,@CallDateTime,@Priority,@SubType,@PersonName,@WFStep,@BefAfter WHILE @@FETCH_STATUS = 0 BEGIN -- print @Service -- selecting userid is done for getting processid. initially select userid statement for 3 services were not here. but were inside serive if @Service=1 select @UserId=DoctorID,@langID=p.LanguageID,@PatientID=a.PatientID,@patientnm=p.Fname,@fullRecipientNm=p.Fname+' '+p.MI+' '+p.Lname,@AppDateTime=AppointmentDateTime, @NotificationType= p.NotificationType ,@RecipientEmail=p.email from ARSCallSchedule a, PatientMaster p where a.PatientID=p.PatientID and ARSCallID=@CallId select @DrOfficePh=OfficePhone,@DrFileName=FirstNM from ARSUser_mst where UserId=@UserId select @DoctorEmail=isnull(email,'') from [LoginInfo] where UserId=@UserId select @IsPremium_Feature=IsPremium_Feature from arscallschedule s join AppointmentMaster a on a.appid=s.apptid where s.arscallid=@CallId ------added by swati for workflow start if @NotificationType<>2 and @IsPremium_Feature=1 begin declare @WFID as int,@NotificationId int if EXISTS (select WFID from NotificationWorkFlow where ProviderId=@UserId and NotificationId=@CallType) begin set @IsWF=1 print '@WFstep' print @WFstep select @WFID=WFID from NotificationWorkFlow where ProviderId=@UserId and NotificationId=@CallType if @WFstep=1 begin declare @Tempid1 int select @Tempid1=TemplateId1,@NotificationType=Preference1 from NotificationWorkFlow where WFID=@WFID print '@Tempid1' print @Tempid1 print '@NotificationType from WF' print @NotificationType if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid1 set @process=21 end else begin set @process =@Tempid1 ---24 End end if @WFstep=2 begin declare @Tempid2 int select @Tempid2=TemplateId2,@NotificationType=Preference2 from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid2 set @process=21 end else begin set @process =@Tempid2 End end if @WFstep=3 and @BefAfter=1---before message in step 3 begin print '@BefAfter message' print @NotificationType select @Tempid3=[MsgIdBeforeDueDt],@NotificationType=Preference3,@NotificationId=NotificationId from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid3 if @NotificationId=5 set @process=21 else set @process=23 end else begin if @NotificationId=5 set @process=@Tempid3 else set @process=@Tempid3 End end if @WFstep=3 and @BefAfter=2---After message in step 3 begin select @Tempid3=[MsgIdAfterDueDt],@NotificationType=Preference3 from NotificationWorkFlow where WFID=@WFID if @NotificationType=1 begin select @WFMessageTxt=Message_Text from TextTemplate where messageid=@Tempid3 if @NotificationId=5 set @process=21 else set @process=23 end else begin if @NotificationId=5 set @process=@Tempid3 else set @process=@Tempid3 End end end ------added by swati for workflow end End ---Added by Swati B for Payment notification -Begin ---Check if notification is for payment if exists(select P.paymentid from PaymentDetails P join AppointmentMaster AP on AP.APPId=P.APPID join ARSCallSchedule AC on AC.APPtID=AP.APPId join ScheduledCalls S on S.CallID=AC.ARSCallID where S.CallID=@CallId) begin---if yes enter in this block print 'in payment block' set @NotificationPayment ='Y' declare @PaymentID int set @PaymentID = (select P.paymentid from ARSCallSchedule AC join AppointmentMaster AP on AP.Appid=AC.APPtID join PaymentDetails P on P.APPID=AP.APPId join ScheduledCalls S on S.CallID=AC.ARSCallID where S.CallID=@CallId) declare @Message as varchar(1000),@Param as varchar(500),@Amount as decimal,@ProviderNM varchar(25),@RecipientNM varchar(25),@PDueDt datetime if @NotificationType='0' begin select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @Message=Message_Text from TextTemplate S where s.NotificationType='voice' and s.MessageType=0 and Eventtype='Payment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @Amount=PM.Amount,@InvoiceId=PM.InvoiceId,@PDueDt=PM.PaymentDueDate,@ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM, @RecipientNM= R.Fname from PaymentDetails PM join AppointmentMaster A on PM.APPId =A.Appid join PatientMaster R on A.Patientid=R.Patientid join ARSUser_mst P on A.Doctorid=P.userID where PM.PaymentId=@PaymentID set @Param= convert(varchar(50), isnull(@PaymentID,0)) set @encrParam= dbo.ufn_EncryptString(@Param) --commented by swati B because encrypted message is not triggering from plumvoice --set @encrParam='Id='+ @encrParam --Added by swati B because encrypted message is not triggering from plumvoice set @encrParam='Id='+ @Param print @encrParam print @Message print @Amount print @InvoiceId set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@Amount,0))) set @Message= (SELECT REPLACE(@Message,'',isnull(@InvoiceId,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@PDueDt),101),''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) set @Message= (SELECT REPLACE(@Message,'',@encrParam)) print @Message end else if @NotificationType='1' begin --Prepare Message field for call list table for notification type Text (ITR) -- declare @Message as varchar(1000),@Amount as decimal,@InvoiceId as varchar(25),@ProviderNM varchar(25),@RecipientNM varchar(25) select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @Message=Message_Text from TextTemplate S where s.NotificationType='text' and s.MessageType=0 and Eventtype='Payment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @Amount=PM.Amount,@InvoiceId=PM.InvoiceId,@PDueDt=PM.PaymentDueDate,@ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM, @RecipientNM= R.Fname from PaymentDetails PM join AppointmentMaster A on PM.APPId =A.Appid join PatientMaster R on A.Patientid=R.Patientid join ARSUser_mst P on A.Doctorid=P.userID where PM.PaymentId=@PaymentID set @Param= convert(varchar(50), isnull(@PaymentID,0)) set @encrParam= dbo.ufn_EncryptString(@Param) --commented by swati B because encrypted message is not triggering from plumvoice --set @encrParam='Id='+ @encrParam --Added by swati B because encrypted message is not triggering from plumvoice set @encrParam='Id='+ @Param print @encrParam print @Message print @Amount print @InvoiceId set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@Amount,0))) set @Message= (SELECT REPLACE(@Message,'',isnull(@InvoiceId,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@PDueDt),101),''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) set @Message= (SELECT REPLACE(@Message,'',@encrParam)) print @Message end end ---Added by Swati B for Payment notification -End else if @Service=2 select @UserId=DoctorID,@PatientID=PatientID,@PharmacyPh=PharmacyPhoneNo from PRRCallSchedule where PrescriptionCallID=@CallId else if @Service =3 -- ***** PRASAD - Changed this Select Statement's Where Clause 12/31/2006 --select @UserId=gp.UserId,@TranferPh=gs.TransferNo,@filenm=gp.MessageFileName,@CallListId=gp.CallListId , @langID=gp.TransferLanguageID, @DrFileName=gp.ProviderName, @CallerId=gp.CallerIDNumber from GCCallSchedule gs,GCProject gp where gp.GCProjectId --=(select GCProjectID from GCCallSchedule where GCCallID=@CallId) select @UserId=gp.UserId,@TranferPh=gs.TransferNo,@filenm=gp.MessageFileName, @NotificationType=gp.NotificationType, @CallListId=gp.CallListId , @langID=gp.TransferLanguageID, @DrFileName=gp.ProviderName, @CallerId=gp.CallerIDNumber from GCCallSchedule gs,GCProject gp WHERE gp.GCprojectID = gs.GCprojectID AND gp.GCProjectId=(select GCProjectID from GCCallSchedule where GCCallID=@CallId) if (@process=0) ---------------------------Added by swati B to check if Process exist in case of WF -------------start -- get process id from processassignment table for @UserId begin if (select ProcessId from ProcessAssignment where UserId=@UserId and Service =@Service and CallType=@CallType and NotificationType=@NotificationType)is not null begin print 'Process exist' SELECT @process= ProcessId FROM ProcessAssignment WHERE (EffectiveFrom = (SELECT MAX(EffectiveFrom) FROM ProcessAssignment WHERE UserId = @UserId AND Service = @Service AND CallType = @CallType)) AND (UserId = @UserId) AND (Service = @Service) AND (CallType = @CallType) AND (NotificationType=@NotificationType) end else begin -- if specific processid is not found in processassignment table then Decide which process to run , using service & CallType as Input --Added by swati for @NotificationType=0 and 1 if @Service=1 and @CallType=5 and @NotificationType=0 --changed as told by sudip because he has changed the vxml set @process=20 --set @process=19 else if @Service=1 and @CallType=5 and @NotificationType=1 set @process=21 else if @Service=1 and @CallType=1 set @process=1 else if @Service=1 and @CallType=2 set @process=2 else if @Service=1 and @CallType=3 set @process=3 else if @Service=1 and @CallType=4 set @process=8 else if @Service=2 and @CallType=1 set @process=4 else if @Service=2 and @CallType=2 set @Process=5 else if @Service=3 and @CallType=2 and @NotificationType=0 --Non Transfer --set @process=6 set @process=18 else if @Service=3 and @CallType=1 and @NotificationType=0 --Transfer --set @process=7 set @process=17 --Added by Swati for Payment else if @Service=1 and @CallType=6 and @NotificationType=0 set @process=26 else if @Service=1 and @CallType=6 and @NotificationType=1 set @process=23 else if @Service=3 and @NotificationType=1 --GC Text set @process=31 end End ---------------------------Added by swati B to check if Process exist in case of WF -------------end ---Set priority to priority-5 to calls in next 1 minute, priority=priority-4 to calls in next 2 minutes and so on-- if @CallDateTime between getdate() and dateadd(minute,1,getdate()) begin if (@Priority-5)> 0 set @Priority=@Priority-5 end else if @CallDateTime between dateadd(minute,1,getdate()) and dateadd(minute,2,getdate()) begin if (@Priority-4) >0 set @Priority=@Priority-4 end else if @CallDateTime between dateadd(minute,2,getdate()) and dateadd(minute,3,getdate()) begin if (@Priority-3)>0 set @Priority=@Priority-3 end else if @CallDateTime between dateadd(minute,3,getdate()) and dateadd(minute,4,getdate()) begin if (@Priority-2)>0 set @Priority=@Priority-2 end else if @CallDateTime between dateadd(minute,4,getdate()) and dateadd(minute,5,getdate()) begin if (@Priority-1)>0 set @Priority=@Priority-1 end print 'NotificationPayment ' +@NotificationPayment +'Not Type ' print @NotificationType print 'chk' print @NotificationPayment ---create Text for Notification type text(ITR) for Appointment event notification start --to uncomment for testing if @NotificationPayment<>'Y' and @NotificationType='1' begin -- declare @Message as varchar(1000),@Amount as decimal,@InvoiceId as varchar(25),@ProviderNM varchar(25),@RecipientNM varchar(25) select @Message=Message_Text from TextTemplate S where s.NotificationType='text' and s.MessageType=0 and Eventtype='Appointment' if @Iswf=1 begin set @Message=@WFMessageTxt end select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId select @ProviderNM=P.FirstNM+' '+P.MidNm+ ' '+ P.LastNM from ARSUser_mst P where P.userID =@UserId select @RecipientNM=R.Fname from patientmaster R where R.Patientid = @PatientID print 'to check msg before000000000000000' print 'message' + @Message print 'Recipient name'+ @RecipientNM print 'Providername'+ @ProviderNM print '@AppDateTime' print @AppDateTime print '@PharmacyPh' print @PharmacyPh set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@ProviderNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull( convert(varchar(10),convert(date,@AppDateTime),101),''))) --isnull(@AppDateTime,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(CONVERT(varchar(15),cast(@AppDateTime as time),100) ,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@RecipientNM,''))) set @Message= (SELECT REPLACE(@Message,'',isnull(@DrOfficePh,''))) print 'message after actual values' + @Message end ---create Text for Notification type text for Appointment event notification start declare @ClientFile as varchar(150) set @ClientFile='' if @Service=1 begin IF (select Status from ARSUser_mst where UserId=@UserId) IN ('E','T') BEGIN if @NotificationPayment ='Y' Begin --Added by Swati B for Payment notification -Begin print 'Enter Payment &&&&&' set @filenm=@Message print 'First insert' print @DrOfficePh print @CallId insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5, User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@fullRecipientNm,@DrOfficePh,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) End ---Added by Swati B for Payment notification -End select @DigitalId=DigitalId ,@UserName=UserName from logininfo where Userid=@UserId select @DrFileName=[FileName] from CallMessages where DigitalID=@DigitalId and Service=@Service and CallType=10 and Status='Audited' select @patientnm=Fname from PatientMaster where PatientID=@PatientID exec sp_SelectClientNameFile @patientnm,@ClientFile output set @patientnm=@ClientFile select @DrOfficePh=OfficePhone from ARSUser_mst where UserId=@UserId --req. to transfer call to Dr's office-- select @langID= languageid,@filenm=[FileName],@NotificationType=[NotificationType] from CallMessages where CallMsgID=dbo.SelectARSPRRFileToPlay (@DigitalId,@PatientID,@CallType,@SubType,@Service,@NotificationType) if @filenm is null begin select @ErrorID=max(ErrorId) from errorlog set @ErrorID=@ErrorID+1 insert into errorlog (ErrorId,ErrorNumber,ErrorMessage,InnerException,ErrorCollectionString,LineNumber,SqlProceName,State,FormName,VbProceName,DtOccured,UserIdLoggedIn)values (@ErrorID,0,'Fatal Error.Recorded message not found.Calls made will have blank message','-','-',95,'sp_inserttocalllist',1,'-','-',getdate(),0) end if @CallType=5 --app reminder call-- begin if @NotificationType='1' set @filenm='' -- if @Iswf=1 -- begin ---- set @Message=@WFMessageTxt -- end set @filenm=@Message print '@filenm'+ @filenm print 'second insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_3,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@AppDateTime,@DrOfficePh,@Message,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType=1 --welcome call-- begin if @NotificationType='1' set @filenm=@Message print 'third insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType in(2,3) /*pT.can or Dr.Can*/ begin if @NotificationType='1' set @filenm=@Message print 'Fourth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@DrOfficePh,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else if @CallType=4 --well visit call-- begin if @NotificationType='1' set @filenm=@Message print 'fifth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@DrOfficePh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end END ELSE BEGIN ----to uncomment for testing by swati B --print 'Hi' DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end else if @Service=2 begin IF (SELECT STATUS FROM PRRUSER_MST WHERE USERID=@UserId) IN ('E','T') BEGIN select @DigitalId=DigitalId ,@UserName=UserName from logininfo where Userid=@UserId select @DrFileName=[FileName] from CallMessages where DigitalID=@DigitalId and Service=@Service and CallType=10 and Status='Audited' select @langID= languageid, @filenm=[FileName] from CallMessages where CallMsgID=dbo.SelectARSPRRFileToPlay (@DigitalId,@PatientID,1,0,@Service,DEFAULT) -- call type 1 used always select @patientnm=Fname from PatientMaster where PatientID=@PatientID exec sp_SelectClientNameFile @patientnm,@ClientFile output set @patientnm=@ClientFile if @filenm is null begin select @ErrorID=max(ErrorId) from errorlog set @ErrorID=@ErrorID+1 insert into errorlog (ErrorId,ErrorNumber,ErrorMessage,InnerException,ErrorCollectionString,LineNumber,SqlProceName,State,FormName,VbProceName,DtOccured,UserIdLoggedIn)values (@ErrorID,0,'Fatal Error.Recorded message not found.Calls made will have blank message','-','-',95,'sp_inserttocalllist',1,'-','-',getdate(),0) end if @PharmacyPh=null or rtrim(@PharmacyPh)='' or ltrim(@PharmacyPh)='' --non transfer- begin if @NotificationType='1' set @filenm=@Message print 'sixth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@filenm,@langID,@PharmacyPh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else --transfer call- begin if @NotificationType='1' set @filenm=@Message print 'seventh insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5, User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@patientnm,@PharmacyPh,@filenm,@langID,@PharmacyPh,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end END ELSE BEGIN print 'Hello' --to uncomment for testing by swati B DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end else if @Service=3 begin IF (SELECT STATUS FROM GCUSER_MST WHERE USERID=@UserId) IN ('E','T') BEGIN --commented by swati B as discussed with sachin pawar --set @PersonName=@PersonName+'.vox' ---select filename for gc----- if @filenm is not null begin if @TranferPh is null set @TranferPh='' if @TranferPh is null or ltrim(@TranferPh)='' or rtrim(@TranferPh)='' begin if @NotificationType='1' -- set @filenm=@Message print 'eightth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@PersonName,@filenm,@langID,@CallerId,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end else begin if ltrim(@CallerId)='' or rtrim(@CallerId)='' or @CallerId is null set @CallerId = @TranferPh -- if CallerId is empty, show transfer phone as caller-id if @NotificationType='1' -- set @filenm=@Message print 'ninth insert' insert into Call_List (Call_ID,Date_Time_Stamp,Phone_Number,Priority,Status,Process,SourceKey,Call_time,Call_result,Audio_Folder,User_Data_1,User_Data_2,User_Data_4,User_Data_5,User_Data_6,Provider_Phone,NotificationId,From_Email,To_Email,ProviderId,invoiceId) values(@CallId,getdate(),@TelNo,@Priority,@Status,@process,@SourceKey,null,null,null,@DrFileName,@PersonName,@TranferPh,@filenm,@langID,@CallerId,@NotificationType,@DoctorEmail,@RecipientEmail,@userid,@InvoiceId) end end END ELSE BEGIN DELETE FROM SCHEDULEDCALLS WHERE CallID = @CallId AND CallDateTime < GETDATE() END end insert into scheduledCallsHistory select * from scheduledcalls where CallID=@CallId delete from scheduledCalls where CallID=@CallId --Added by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply start --if @NotificationType=1 --Begin --if not exists (select * from [Text_Msg_Log] where call_id=@CallId and status='U') -- INSERT INTO [Text_Msg_Log] SELECT * FROM Call_List where notificationid=1 AND call_id=@CallId --End --Added by swati to insert entry in INSERT INTO [Text_Msg_Log] table for each Text message as wel as Reply End FETCH NEXT FROM ScheduledCalls_Cursor into @CallType,@CallId,@Service,@SourceKey,@TelNo,@CallDateTime,@Priority,@SubType,@PersonName,@WFStep,@BefAfter END CLOSE ScheduledCalls_Cursor DEALLOCATE ScheduledCalls_Cursor END go --------------swati end. GO /****** Object: StoredProcedure [dbo].[sp_GetScheduleDetails1] Script Date: 5/10/2016 2:41:05 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO -- This procedure is used to get the count of all calls and will show their status from transaction table between given date range ALTER proc [dbo].[sp_GetScheduleDetails1] @StDate as datetime, @EdDate as datetime, @UserID as integer as --select t.GCProjectId ,GcProject.GCProjectName, sum(t.CallListCount) as CallsCompleted , sum(t.CallsCompleted) as TotalCalls ,sum(t.Successful) Successful , sum(t.UnSuccessful) UnSuccessful , sum (t.Transfer) Transfer from -- COMMENTED BY PRASAD T on 2/5/2007. See new line below this line select t.GCProjectId ,GcProject.GCProjectName, sum(t.CallListCount) as TotalCalls , sum(t.CallsCompleted) as CallsCompleted ,sum(t.Successful) Successful , sum(t.UnSuccessful) UnSuccessful , sum (t.Transfer) Transfer from ( select GCProjectId, CallListCount , 0 as CallsCompleted, 0 as Successful , 0 as UnSuccessful , 0 as Transfer from GCProject where (Cast(StartDate as smalldatetime) BETWEEN @StDate AND @EdDate OR Cast(EndDate as smalldatetime) BETWEEN @StDate AND @EdDate) and Userid=@UserID ---Currently following 1 union i.e. for completed calls is not in use union all -- This will give the count of calls completed select right(sourcekey,len(sourcekey)-1) GCProjectId , 0 as CallListCount, count(*) as CallsCompleted, 0 as Successful , 0 as UnSuccessful , 0 as Transfer from [transaction] where left(sourcekey,1)='G' AND right(sourcekey,len(sourcekey)-1) in (select cast(GCProjectID as varchar(12)) from GCProject where Cast(StartDate as smalldatetime) BETWEEN @StDate AND @EdDate OR Cast(EndDate as smalldatetime) BETWEEN @StDate AND @EdDate and Userid=@UserID) and Status in ('ANS','SND', 'D2','OPT','D4','MCH','NOA','BSY','T') group by right(sourcekey,len(sourcekey)-1) --- union all -- This will give the count of successful calls select right(sourcekey,len(sourcekey)-1) GCProjectId , 0 as CallListCount, 0 as CallsCompleted, count(*) as Successful , 0 as UnSuccessful , 0 as Transfer from [transaction] where left(sourcekey,1)='G' AND right(sourcekey,len(sourcekey)-1) in (select cast(GCProjectID as varchar(12)) from GCProject where (Cast(StartDate as smalldatetime) BETWEEN @StDate AND @EdDate OR Cast(EndDate as smalldatetime) BETWEEN @StDate AND @EdDate) and Userid=@UserID) and status in ('ANS','SND', 'D2','OPT','D4','MCH','T') group by right(sourcekey,len(sourcekey)-1) union all -- This will give the count of unsuccessful calls select right(sourcekey,len(sourcekey)-1) GCProjectId , 0 as CallListCount, 0 as CallsCompleted, 0 as Successful , count(*) as UnSuccessful , 0 as Transfer from [transaction] where left(sourcekey,1)='G' AND right(sourcekey,len(sourcekey)-1) in (select cast(GCProjectID as varchar(12)) from GCProject where (Cast(StartDate as smalldatetime) BETWEEN @StDate AND @EdDate OR Cast(EndDate as smalldatetime) BETWEEN @StDate AND @EdDate) and Userid=@UserID) and status in ('NOA','BSY') group by right(sourcekey,len(sourcekey)-1) union all -- This will give the count of Successful Transfered calls select right(sourcekey,len(sourcekey)-1) GCProjectId , 0 as CallListCount, 0 as CallsCompleted, 0 as Successful , 0 as UnSuccessful , count(*) as Transfer from [transaction] where left(sourcekey,1)='G' AND right(sourcekey,len(sourcekey)-1) in (select cast(GCProjectID as varchar(12)) from GCProject where (Cast(StartDate as smalldatetime) BETWEEN @StDate AND @EdDate OR Cast(EndDate as smalldatetime) BETWEEN @StDate AND @EdDate) and Userid=@UserID) and status = 'OPT' group by right(sourcekey,len(sourcekey)-1) )as t ,GcProject where GcProject.GCProjectId= t.GCProjectId and GcProject.userid=@UserID group by t.GCProjectId,GcProject.GCProjectName order by gcprojectname --------------------------- samir GO /****** Object: StoredProcedure [dbo].[Sp_MobileNotification] Script Date: 5/12/2016 9:37:31 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Samir -- Description: Show Notifiction Mobile Service -- ============================================= ALTER PROCEDURE [dbo].[Sp_MobileNotification] -- Add the parameters for the stored procedure here @PHONE_NUMBER varchar(30) AS BEGIN SET NOCOUNT ON; create table #tmp( USER_DATA_5 varchar(max),Call_time datetime,Process varchar(50),Call_ID varchar(10) ,ApptID int, calltype int,SourceKey varchar(50),PaidStatus varchar(30)) truncate table #tmp insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Call_time,Call_ID,SourceKey from call_list where USER_DATA_5 is not null and USER_DATA_5 !='' and status !='U' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Call_time,Call_ID,SourceKey from Call_List_History where USER_DATA_5 is not null and USER_DATA_5 !='' and status !='U' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc update #tmp set Apptid=ARSCallSchedule.Apptid ,calltype= ARSCallSchedule.calltype from ARSCallSchedule ,#tmp where #tmp.call_id=ARSCallSchedule.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set Apptid=arscallschedulehistory.Apptid ,calltype= arscallschedulehistory.calltype from arscallschedulehistory ,#tmp where #tmp.call_id=arscallschedulehistory.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set PaidStatus= case when statusOfpayment='Done' or responsecode=1 then 'Done' Else 'Pending' end from PaymentDetails pd , #tmp tm where pd.Appid = tm.apptid update #tmp set process=case when calltype=6 then 'NOTIFICATION_TYPE_PAYMENT' else 'NOTIFICATION_TYPE_APPOINTMENT' end select * from #tmp order by Call_time desc END Go --------------- -- Samir new column Added for DeletedProjectCredits log Go ALTER TABLE [dbo].[GCProjectDeleted] ADD DeletedProjectCredits decimal(18,2) NULL Go ---- GO /****** Object: StoredProcedure [dbo].[sp_DeleteGCProject] Script Date: 05/17/2016 3:06:29 PM ******/ /** alter Credit charges on deleted GC project ***/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_DeleteGCProject] @prjid int AS BEGIN SET NOCOUNT ON; create table #tmp( GCProjectID int ,GCTransferNameID int ,GCProjectName varchar(250),UserID int,Recurring bit, Critical bit,StartDate datetime,EndDate datetime,TimeSlot tinyint,StartTime datetime,EndTime datetime, AllowAtNoCallTime bit,Frequency int,TransferLanguageID varchar(2),TransferPhone varchar(12),MessageFileName varchar(255) ,CallListID int,CallListCount int,AllowWeekEndCalls int,GCReceiverTZ tinyint,ProviderName varchar(50),CallerIDNumber varchar(12), MaxCriticalCall int,CreatedByBackOffice bit ,NotificationType smallint) Insert into #tmp(GCProjectID,GCTransferNameID,GCProjectName,UserID,Recurring, Critical,StartDate,EndDate,TimeSlot,StartTime,EndTime,AllowAtNoCallTime,Frequency,TransferLanguageID,TransferPhone,MessageFileName ,CallListID,CallListCount,AllowWeekEndCalls,GCReceiverTZ,ProviderName,CallerIDNumber,MaxCriticalCall,CreatedByBackOffice ,NotificationType) Select GCProjectID,GCTransferNameID,GCProjectName,UserID,Recurring, Critical,StartDate,EndDate,TimeSlot,StartTime,EndTime,AllowAtNoCallTime,Frequency,TransferLanguageID,TransferPhone,MessageFileName ,CallListID,CallListCount,AllowWeekEndCalls,GCReceiverTZ,ProviderName,CallerIDNumber,MaxCriticalCall,CreatedByBackOffice ,NotificationType From GCProject where GCProjectID=@prjid Declare @NotificationType int, @CallListCount int, @UserID int Declare @packageID int Declare @PerPrice decimal(18,2) Declare @FineAmt decimal(18,2) select @NotificationType=NotificationType, @CallListCount=CallListCount ,@UserID=UserID from gcproject where GCProjectID=@prjid select @packageID=p.packageID from Provider_Usage as p join package_mst as pm on p.packageid=pm.packageid where p.customer_id=@UserID and pm.service=3 and p.status='Enabled' if @NotificationType=0 begin select @PerPrice=Creditspercall from package_mst where packageid=@packageid end if @NotificationType=1 begin select @PerPrice=CreditsperText from package_mst where packageid=@packageid end declare @parametervalue decimal(18,2) select @parametervalue=parametervalue from systemsettings where parameter = 'GC Cancellation Charges' set @FineAmt = (@CallListCount*@PerPrice)*@parametervalue/100 Delete from gcproject where GCProjectID=@prjid update Provider_Usage set available_cents_credit=available_cents_credit-@FineAmt where customer_id=@UserID and packageID=@packageID Insert into GCProjectDeleted(GCProjectDeletedID,GCTransferNameID,GCProjectDeletedName,UserID,Recurring, Critical,StartDate,EndDate,TimeSlot,StartTime,EndTime,AllowAtNoCallTime,Frequency,TransferLanguageID,TransferPhone,MessageFileName ,CallListID,CallListCount,AllowWeekEndCalls,GCReceiverTZ,ProviderName,CallerIDNumber,MaxCriticalCall,CreatedByBackOffice ,NotificationType,DeletedProjectCredits) Select GCProjectID,GCTransferNameID,GCProjectName,UserID,Recurring, Critical,StartDate,EndDate,TimeSlot,StartTime,EndTime,AllowAtNoCallTime,Frequency,TransferLanguageID,TransferPhone,MessageFileName ,CallListID,CallListCount,AllowWeekEndCalls,GCReceiverTZ,ProviderName,CallerIDNumber,MaxCriticalCall,CreatedByBackOffice ,NotificationType,@FineAmt From #tmp where GCProjectID=@prjid truncate table #tmp END Go --------------------------------- GO /****** Object: Table [dbo].[ProviderAuthToken] Script Date: 05/23/2016 6:01:16 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProviderAuthToken]( [AuthTokenID] [int] IDENTITY(1,1) NOT NULL, [ProviderID] [int] NULL, [AuthToken] [nvarchar](max) NULL, [AuthTokenExpiry] [datetime] NULL, CONSTRAINT [PK_ProviderAuthToken] PRIMARY KEY CLUSTERED ( [AuthTokenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ----------------------------- GO /****** Object: StoredProcedure [dbo].[Sp_MobileNotification] Script Date: 05/23/2016 6:02:43 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Samir -- Description: Show Notifiction Mobile Service -- ============================================= ALTER PROCEDURE [dbo].[Sp_MobileNotification] -- Add the parameters for the stored procedure here @PHONE_NUMBER varchar(30) AS BEGIN SET NOCOUNT ON; create table #tmp( USER_DATA_5 varchar(max),Date_time_stamp datetime,Process varchar(50),Call_ID varchar(10) ,ApptID int, calltype int,SourceKey varchar(50),PaidStatus varchar(30)) truncate table #tmp insert into #tmp (USER_DATA_5,Date_time_stamp,Call_ID,SourceKey) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Date_time_stamp,Call_ID,SourceKey from call_list where USER_DATA_5 is not null and USER_DATA_5 !='' and USER_DATA_2<> 'IGN' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Date_time_stamp desc insert into #tmp (USER_DATA_5,Date_time_stamp,Call_ID,SourceKey) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Date_time_stamp,Call_ID,SourceKey from Call_List_History where USER_DATA_5 is not null and USER_DATA_5 !='' and USER_DATA_2<> 'IGN' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Date_time_stamp desc update #tmp set Apptid=ARSCallSchedule.Apptid ,calltype= ARSCallSchedule.calltype from ARSCallSchedule ,#tmp where #tmp.call_id=ARSCallSchedule.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set Apptid=arscallschedulehistory.Apptid ,calltype= arscallschedulehistory.calltype from arscallschedulehistory ,#tmp where #tmp.call_id=arscallschedulehistory.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set PaidStatus= case when statusOfpayment='Done' or responsecode=1 then 'Done' Else 'Pending' end from PaymentDetails pd , #tmp tm where pd.Appid = tm.apptid update #tmp set process=case when calltype=6 then 'NOTIFICATION_TYPE_PAYMENT' else 'NOTIFICATION_TYPE_APPOINTMENT' end select * from #tmp order by Date_time_stamp desc END Go ------------------ GO /****** Object: StoredProcedure [dbo].[Sp_ProviderNotification] Script Date: 05/23/2016 6:04:00 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Samir -- Description: Show Provider Notifiction Mobile Service -- ============================================= CREATE PROCEDURE [dbo].[Sp_ProviderNotification] -- Add the parameters for the stored procedure here @PHONE_NUMBER varchar(30), @SDate varchar(30), @EDate varchar(30), @ProviderID varchar(30) AS BEGIN SET NOCOUNT ON; create table #tmp( ServiceType varchar(50), USER_DATA_5 varchar(max),Call_time datetime,Calltype varchar(50),Call_ID varchar(10) ,ApptID int, calltypeID int,SourceKey varchar(50),PaidStatus varchar(30),ProviderID int,ProviderName Varchar(200),Date_time_stamp datetime ) truncate table #tmp insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey,Date_time_stamp) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Call_time,Call_ID,SourceKey,Date_time_stamp from call_list where USER_DATA_5 !='' and USER_DATA_5 is not null and USER_DATA_2<> 'IGN' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey,Date_time_stamp) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Call_time,Call_ID,SourceKey,Date_time_stamp from Call_List_History where USER_DATA_5 is not null and USER_DATA_5 !='' and USER_DATA_2<> 'IGN' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc update #tmp set ServiceType='EN',Apptid=ARSCallSchedule.Apptid ,calltypeID= ARSCallSchedule.calltype,ProviderID=DoctorID from ARSCallSchedule ,#tmp where #tmp.call_id=ARSCallSchedule.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set ServiceType='EN',Apptid=arscallschedulehistory.Apptid , calltypeID= arscallschedulehistory.calltype,ProviderID=DoctorID from arscallschedulehistory ,#tmp where #tmp.call_id=arscallschedulehistory.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set ServiceType='GN',Apptid=gcCallSchedule.GCProjectID ,calltype= gccallschedule.calltype from gccallschedule ,#tmp where #tmp.call_id=GCCallSchedule.gccallid and LEFT(LTRIM(SourceKey),1) = 'G' update #tmp set ServiceType='GN',Apptid=gccallschedulehistory.GCProjectID ,calltype= gccallschedulehistory.calltype from gccallschedulehistory ,#tmp where #tmp.call_id=gccallschedulehistory.gccallid and LEFT(LTRIM(SourceKey),1) = 'G' update #tmp set ProviderID=gcproject.UserID from gcproject ,#tmp where #tmp.ServiceType='GN' and gcproject.GCprojectID= #tmp.Apptid update #tmp set PaidStatus= case when statusOfpayment='Done' or responsecode=1 then 'Done' Else 'Pending' end from PaymentDetails pd , #tmp tm where pd.Appid = tm.apptid update #tmp set Calltype=case when calltypeID=6 then 'Payment Notification' end update #tmp set Calltype= 'EN Notification' where calltypeID<>6 and ServiceType='EN' update #tmp set Calltype= 'GN Notification' where calltype<>6 and ServiceType='GN' update #tmp set ProviderName=username from LoginInfo where userid=ProviderID --select * from #tmp select ServiceType,Calltype,Date_time_stamp,USER_DATA_5,ProviderID,ProviderName from #tmp where ProviderID=@ProviderID and Date_time_stamp >=@SDate and Date_time_stamp <=@EDate order by Date_time_stamp desc END GO -------------------------------------- GO /****** Object: StoredProcedure [dbo].[Sp_ProviderNotification] Script Date: 05/23/2016 6:16:57 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Samir -- Description: Show Provider Notifiction Mobile Service -- ============================================= ALTER PROCEDURE [dbo].[Sp_ProviderNotification] -- Add the parameters for the stored procedure here @PHONE_NUMBER varchar(30), @SDate varchar(30), @EDate varchar(30), @ProviderID varchar(30) AS BEGIN SET NOCOUNT ON; create table #tmp( ServiceType varchar(50), USER_DATA_5 varchar(max),Call_time datetime,Calltype varchar(50),Call_ID varchar(10) ,ApptID int, calltypeID int,SourceKey varchar(50),PaidStatus varchar(30),ProviderID int,ProviderName Varchar(200),Date_time_stamp datetime ) truncate table #tmp insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey,Date_time_stamp) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Call_time,Call_ID,SourceKey,Date_time_stamp from call_list where USER_DATA_5 !='' and USER_DATA_5 is not null and USER_DATA_2<> 'IGN' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc insert into #tmp (USER_DATA_5,Call_time,Call_ID,SourceKey,Date_time_stamp) select case when charindex('.', USER_DATA_5)=0 then USER_DATA_5 else substring(USER_DATA_5, 1, charindex('.', USER_DATA_5)-1) end,Call_time,Call_ID,SourceKey,Date_time_stamp from Call_List_History where USER_DATA_5 is not null and USER_DATA_5 !='' and USER_DATA_2<> 'IGN' and PHONE_NUMBER=@PHONE_NUMBER and notificationid is not null and LEFT(LTRIM(USER_DATA_5),11) !='MsgRecords/' Order by Call_time desc update #tmp set ServiceType='EN',Apptid=ARSCallSchedule.Apptid ,calltypeID= ARSCallSchedule.calltype,ProviderID=DoctorID from ARSCallSchedule ,#tmp where #tmp.call_id=ARSCallSchedule.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set ServiceType='EN',Apptid=arscallschedulehistory.Apptid , calltypeID= arscallschedulehistory.calltype,ProviderID=DoctorID from arscallschedulehistory ,#tmp where #tmp.call_id=arscallschedulehistory.arscallid and LEFT(LTRIM(SourceKey),1) != 'G' update #tmp set ServiceType='GN',Apptid=gcCallSchedule.GCProjectID ,calltype= gccallschedule.calltype from gccallschedule ,#tmp where #tmp.call_id=GCCallSchedule.gccallid and LEFT(LTRIM(SourceKey),1) = 'G' update #tmp set ServiceType='GN',Apptid=gccallschedulehistory.GCProjectID ,calltype= gccallschedulehistory.calltype from gccallschedulehistory ,#tmp where #tmp.call_id=gccallschedulehistory.gccallid and LEFT(LTRIM(SourceKey),1) = 'G' update #tmp set ProviderID=gcproject.UserID from gcproject ,#tmp where #tmp.ServiceType='GN' and gcproject.GCprojectID= #tmp.Apptid update #tmp set PaidStatus= case when statusOfpayment='Done' or responsecode=1 then 'Done' Else 'Pending' end from PaymentDetails pd , #tmp tm where pd.Appid = tm.apptid update #tmp set Calltype=case when calltypeID=6 then 'Payment Notification' end update #tmp set Calltype= 'EN Notification' where calltypeID<>6 and ServiceType='EN' update #tmp set Calltype= 'GN Notification' where ServiceType='GN' update #tmp set ProviderName=username from LoginInfo where userid=ProviderID --select * from #tmp select ServiceType,Calltype,Date_time_stamp,USER_DATA_5,ProviderID,ProviderName from #tmp where ProviderID=@ProviderID and Date_time_stamp >=@SDate and Date_time_stamp <=@EDate order by Date_time_stamp desc END