· 7 years ago · Jan 15, 2019, 04:18 AM
1USE [ProfessionalServices02]
2GO
3/****** Object: StoredProcedure [dbo].[usp_Publish_DetailData_Way] Script Date: 15/01/2019 01:17:36 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [dbo].[usp_Publish_DetailData_Way]
9 @LinkedServer VARCHAR(MAX), -- É o alias do Linked Server do ExportData neste servidor (passar NULL quando o banco for local)
10 @Destination_DatabaseName VARCHAR(MAX), -- É o nome do database onde fica a tabela CallData
11 @LinkedServerOnRemote VARCHAR(MAX), -- É o alias do Linked Server do @Source_DatabaseName no servidor do ExportData (passar NULL quando o banco for local)
12 @Source_DatabaseName VARCHAR(MAX), -- É o nome do database onde fica os dados da CallControl (default "CampaignControl... nos casos de envio manual, é o nome do banco restaurado)
13 @LinkedServerForLogOnRemote VARCHAR(MAX), -- É o alias do Linked Server do @Log_DatabaseName no servidor do ExportData (passar NULL quando o banco for local)
14 @Log_DatabaseName VARCHAR(MAX),
15 @Job_Name VARCHAR(MAX),
16 @ManualSending BIT, -- 0-Envio automático (job), 1-Envio manual (reenvio) -- Default: 0
17 @RowsCount BIGINT,
18 @PrintCode BIT
19AS
20BEGIN
21 SET NOCOUNT ON
22 DECLARE @SQL VARCHAR(MAX)
23 DECLARE @SQL2 VARCHAR(MAX)
24 DECLARE @Enter VARCHAR(2) = (CHAR(13) + CHAR(10))
25 DECLARE @ErrorMessage VARCHAR(MAX)
26 DECLARE @isRemote BIT
27 DECLARE @FirstDayOfTheMonth VARCHAR(MAX)
28 DECLARE @LastDayOfTheMonth VARCHAR(MAX)
29 DECLARE @GetDate DATETIME
30
31 SET @GetDate = GETDATE()
32 SET @Source_DatabaseName = ISNULL(@Source_DatabaseName, 'CampaignControl')
33 SET @ManualSending = ISNULL(@ManualSending, 0)
34
35 IF @LinkedServer IS NULL AND @LinkedServerOnRemote IS NOT NULL
36 BEGIN
37 SET @ErrorMessage = 'Parameter @LinkedServer cannot be NULL when the parameter @LinkedServerOnRemote is not NULL.'
38 RAISERROR(@ErrorMessage, 0, 1) WITH NOWAIT
39 RETURN
40 END
41 IF @LinkedServer IS NOT NULL AND @LinkedServerOnRemote IS NULL
42 BEGIN
43 SET @ErrorMessage = 'Parameter @LinkedServerOnRemote cannot be NULL when the parameter @LinkedServer is not NULL.'
44 RAISERROR(@ErrorMessage, 0, 1) WITH NOWAIT
45 RETURN
46 END
47 IF (@LinkedServer IS NOT NULL)
48 BEGIN
49 SET @isRemote = 1
50 SET @LinkedServer = @LinkedServer + '.'
51 SET @LinkedServerOnRemote = @LinkedServerOnRemote + '.'
52 END
53 ELSE
54 BEGIN
55 SET @isRemote = 0
56 SET @LinkedServer = SPACE(0)
57 SET @LinkedServerOnRemote = SPACE(0)
58 END
59 SET @FirstDayOfTheMonth = CONVERT(VARCHAR(MAX), DATEADD(month, DATEDIFF(month, 0, @GetDate), 0), 112) + ' ' +
60 CONVERT(VARCHAR(MAX), DATEADD(month, DATEDIFF(month, 0, @GetDate), 0), 114)
61 SET @LastDayOfTheMonth = CONVERT(VARCHAR(MAX), DATEADD(d, -1, DATEADD(ms, -3, DATEADD(m, DATEDIFF(m, 0, @GetDate) + 1, 1))), 112) + ' ' +
62 CONVERT(VARCHAR(MAX), DATEADD(d, -1, DATEADD(ms, -3, DATEADD(m, DATEDIFF(m, 0, @GetDate) + 1, 1))), 114)
63
64 DECLARE @STARTDATE VARCHAR(10) = CONVERT(VARCHAR(10),GETDATE(), 120)
65 DECLARE @ENDDATE VARCHAR(10) = CONVERT(VARCHAR(10), GETDATE() + 1, 120)
66
67 -- Envia os dados do mês atual para a tabela CallData
68 BEGIN
69 SET @SQL =
70 '
71 DECLARE @WAY_RESULT TABLE(
72 [Seq] [int],
73 [CallId] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS,
74 [Node] [varchar](50),
75 [Instance] [varchar](50),
76 [CallDisconnectedByUser] [bit],
77 [CallStartDt] [datetime],
78 [CallEndDt] [datetime],
79 [MailingField] [varchar](50),
80 [CustomerId] [varchar](50),
81 [CampaignId] [int],
82 [OriginalPN] [varchar](20),
83 [TableName] [varchar](200),
84 [CPC] [int] ,
85 [Transferred] [bit],
86 [InfoStatus] [int],
87 [NavigationIndex] [varchar](100),
88 [DispositionId] [int],
89 [AgentId] [int]
90 )
91
92 INSERT @WAY_RESULT (CallId, CallDisconnectedByUser, CallStartDt, CallEndDt, CustomerId, CampaignId, OriginalPN, TableName, CPC, InfoStatus, NavigationIndex, DispositionId, AgentId)
93 EXEC (''''EXEC [192.168.150.225].WaylogDB.dbo.[PROC_GetDataWaySummaryByPeriodToExport_2] ''' + '''''''' + @STARTDATE + '''''''' + ''', ''' + '''''''' + @ENDDATE + '''''''' + ''' '''')
94
95
96 IF OBJECT_ID(''''tempdb..#CallData_' + @@SERVERNAME + ''''') IS NOT NULL
97 DROP TABLE #CallData_' + @@SERVERNAME + '
98
99 CREATE TABLE #CallData_' + @@SERVERNAME + ' (
100 [ID] [bigint] IDENTITY(1,1) NOT NULL,
101 [CallId] [int] NOT NULL,
102 [CampaignId] [int] NOT NULL,
103 [DispositionId] [int] NOT NULL,
104 [CallStart] [datetime] NOT NULL,
105 [CallEnd] [datetime] NOT NULL,
106 [AgentStart] [datetime] NULL,
107 [AgentEnd] [datetime] NULL,
108 [WrapStart] [datetime] NULL,
109 [WrapEnd] [datetime] NULL,
110 [PhoneNumber] [varchar](max) NULL,
111 [ANI] VARCHAR(255) NULL,
112 [DNIS] VARCHAR(255) NULL,
113 [TypePhoneId] [int] NULL,
114 [Route] [varchar](max) NULL,
115 [Redialed] [char](1) NULL,
116 [AgentId] [int] NULL,
117 [QueueStart] [datetime] NULL,
118 [QueueEnd] [datetime] NULL,
119 [MailingName] [varchar](max) NULL,
120 [ExtraFields] [varchar](max) NULL,
121 [RecordId] [int] NULL,
122 [PhoneNumberId] [int] NULL,
123 [CustomerId] VARCHAR(255) NULL,
124 [CallIdTelecom] [varchar](50) NULL,
125 [FieldControlId] int,
126 [IDSRV] int
127 )
128
129
130 DECLARE @startCallSent int
131 DECLARE @endCallSent int
132 DECLARE @COUNT BIGINT
133 DECLARE @maxEndCallSent bigint
134 DECLARE @CurrentID BIGINT
135
136 SELECT @startCallSent = LastCallSent FROM ' + @LinkedServerOnRemote + @Source_DatabaseName + '.[dbo].[ExportDataLastCallSent_WAY] WITH(NOLOCK)
137 SELECT @endCallSent = MAX(FieldControlId) FROM ' + @LinkedServerOnRemote + @Source_DatabaseName + '.[dbo].[CallControl] WITH(NOLOCK)
138 WHERE StartCallDate <= dateadd(minute, -80, getdate())
139
140 set @maxEndCallSent = @startCallSent + ' + CONVERT(varchar(max), @RowsCount) + '
141
142 if(@endCallSent > @maxEndCallSent)
143 begin
144 set @endCallSent = @maxEndCallSent
145 end
146
147 if(@endCallSent > @maxEndCallSent)
148 begin
149 set @endCallSent = @maxEndCallSent
150 end
151
152 DECLARE @dataReferencia datetime = getdate()
153
154 INSERT INTO ' + @LinkedServerOnRemote + @Source_DatabaseName + '.[dbo].[LogJOB]
155 values(@dataReferencia, @startCallSent, @maxEndCallSent, @EndCallSent)
156
157 INSERT INTO #CallData_' + @@SERVERNAME + ' (
158 CallId
159 ,CampaignId
160 ,DispositionId
161 ,CallStart
162 ,CallEnd
163 ,AgentStart
164 ,AgentEnd
165 ,WrapStart
166 ,WrapEnd
167 ,PhoneNumber
168 ,ANI
169 ,DNIS
170 ,TypePhoneId
171 ,Route
172 ,Redialed
173 ,AgentId
174 ,QueueStart
175 ,QueueEnd
176 ,MailingName
177 ,ExtraFields
178 ,RecordId
179 ,PhoneNumberId
180 ,CustomerId
181 ,CallIdTelecom
182 ,FieldControlID
183 ,IDSRV)
184 SELECT CC.CallId
185 ,CC.CampaignId
186 ,CASE when cc.DispositionId = 17 THEN coalesce(WAY.DispositionId, CC.DispositionId) ELSE cc.DispositionId END AS DispositionId
187 /*
188 ,COALESCE(WAY.CallStartDt, CC.StartCallDate)
189 ,COALESCE(WAY.CallEndDt, CC.EndCallDate)
190 */
191 ,CC.StartCallDate
192 ,CC.EndCallDate
193 ,WAY.CallStartDt
194 ,WAY.CallEndDt
195 ,NULL
196 ,NULL
197 ,CC.OriginalPhoneNumber
198 ,NULL
199 ,CC.PhoneNumber
200 ,CM.TypePhoneId
201 ,CC.Route
202 ,CC.Redialed
203 ,WAY.AgentId
204 ,NULL
205 ,NULL
206 ,CM.TableName
207 ,CM.ExtraFields
208 ,CM.RecordId
209 ,CM.PhoneNumberId
210 ,CM.CustomerId
211 ,CC.CallIdTelecom
212 ,CC.FieldControlID
213 ,(case when charindex(''''<IDSRV>'''', CM.Extrafields) > 0 then
214 substring(CM.Extrafields,
215 charindex(''''<IDSRV>'''', CM.Extrafields) + 7,
216 charindex(''''</IDSRV>'''', CM.Extrafields) - charindex(''''<IDSRV>'''', CM.Extrafields) - 7 ) else '''''''' end) as IDSRV
217 FROM ' + @LinkedServerOnRemote + 'CampaignControl.dbo.CallControl CC WITH (NOLOCK)
218 left join ' + @LinkedServerOnRemote + 'CampaignControl.dbo.CallMailingDetail CM WITH (NOLOCK) ON CC.CallId = CM.CallId
219 left join @WAY_RESULT WAY ON WAY.CallId = CC.CallIdTelecom
220
221 WHERE CC.FieldControlID between @startCallSent and @endCallSent
222 and CC.DispositionId = 17
223 '
224
225
226 IF (@ManualSending = 1)
227 SET @SQL = @SQL + '
228 AND NOT EXISTS (SELECT 1
229 WHERE CD.CallID = CC.CallID) '
230 SET @SQL = @SQL +
231 '
232
233 INSERT INTO ' + @Destination_DatabaseName + '.[dbo].[CallData] (
234 CallId
235 ,CampaignId
236 ,DispositionId
237 ,CallStart
238 ,CallEnd
239 ,AgentStart
240 ,AgentEnd
241 ,WrapStart
242 ,WrapEnd
243 ,PhoneNumber
244 ,ANI
245 ,DNIS
246 ,TypePhoneId
247 ,Route
248 ,Redialed
249 ,AgentId
250 ,QueueStart
251 ,QueueEnd
252 ,MailingName
253 ,ExtraFields
254 ,RecordId
255 ,PhoneNumberId
256 ,CustomerId
257 ,CallIdTelecom
258 ,IDSRV)
259 SELECT
260 CallId
261 ,CampaignId
262 ,DispositionId
263 ,CallStart
264 ,CallEnd
265 ,AgentStart
266 ,AgentEnd
267 ,WrapStart
268 ,WrapEnd
269 ,PhoneNumber
270 ,ANI
271 ,DNIS
272 ,TypePhoneId
273 ,Route
274 ,Redialed
275 ,AgentId
276 ,QueueStart
277 ,QueueEnd
278 ,MailingName
279 ,ExtraFields
280 ,RecordId
281 ,PhoneNumberId
282 ,CustomerId
283 ,CallIdTelecom
284 ,IDSRV
285 FROM #CallData_' + @@SERVERNAME + ''
286
287 SET @SQL = @SQL + '
288
289 DROP TABLE #CallData_' + @@SERVERNAME + @Enter + @Enter
290
291 SET @SQL = @SQL + '
292
293 UPDATE ' + @LinkedServerOnRemote + @Source_DatabaseName + '.[dbo].ExportDataLastCallSent_WAY SET LastCallSent = @endCallSent
294
295 INSERT INTO ' + @LinkedServerOnRemote + @Source_DatabaseName + '.[dbo].[LogJOB2]
296 values(@dataReferencia, @endCallSent)
297
298 declare @Inicio Datetime = convert(varchar(10), getdate(), 120)
299 declare @Fim Datetime = convert(varchar(10), DateAdd(DD, 1, getdate()), 120)
300
301 Select * into #CallData_AD_NEW_P2 from ' + @Destination_DatabaseName + '.[dbo].CALLDATA_AD_NEW with(nolock) where Data between @Inicio and @Fim
302 Select * into #CallData_P2 from ' + @Destination_DatabaseName + '.[dbo].CallData with(nolock) where CallStart between @Inicio and @Fim and CampaignId in (select distinct IdCampanha from #CallData_AD_NEW_P2)
303
304 select CDT.CallId
305 into #Atualizar
306 from #CallData_P2 CDT with(nolock)
307 inner join #CALLDATA_AD_NEW_P2 ADN with(nolock) on CDT.CallID = ADN.CallId
308 where Isnull(CDT.DispositionId, 0) = 17 and ISNULL(ADN.IdOcorrencia, 0) = 16
309
310 select CDT.CallId
311 into #Atualizar2
312 from #CallData_P2 CDT with(nolock)
313 inner join #CALLDATA_AD_NEW_P2 ADN with(nolock) on CDT.CallID = ADN.CallId
314 where Isnull(CDT.DispositionId, 0) = 17 and ISNULL(ADN.IdOcorrencia, 0) = 11
315
316 if exists(select callId from #Atualizar)
317 begin
318 Update ' + @Destination_DatabaseName + '.[dbo].CallData
319 set DispositionId = 16
320 where callId in (select callId from #Atualizar)
321 and CallStart between @Inicio and @Fim
322 end
323
324 if exists(select callId from #Atualizar2)
325 begin
326 Update ' + @Destination_DatabaseName + '.[dbo].CallData
327 set DispositionId = 11
328 where callId in (select callId from #Atualizar2)
329 and CallStart between @Inicio and @Fim
330 end
331
332 '
333
334 IF (ISNULL(@PrintCode, 0) = 0)
335 BEGIN
336 IF (@isRemote = 1)
337 SET @SQL2 = 'EXECUTE (''' + @SQL + ''') AT ' + LEFT(@LinkedServer, LEN(@LinkedServer)-1)
338 ELSE
339 SET @SQL2 = 'EXECUTE (''' + @SQL + ''')'
340 END
341 ELSE
342 SET @SQL2 = 'SELECT (''' + @SQL + ''')'
343
344 EXECUTE (@SQL2)
345
346
347 END
348END