· 9 years ago · Oct 13, 2016, 03:00 PM
1IF NOT EXISTS(SELECT * from ERPIntegrations
2WHERE ERPI_Deleted IS NULL)
3BEGIN
4 RAISERROR ('******* You must set up an integration before you can run this script ******',20,-1) WITH LOG
5END
6
7IF object_id('tempdb..#CRMMissingAccounts') IS NOT NULL
8BEGIN
9 DROP TABLE #CRMMissingAccounts
10END
11
12IF object_id('tempdb..#CRMMissingAddressLinks') IS NOT NULL
13BEGIN
14 DROP TABLE #CRMMissingAddressLinks
15END
16
17IF object_id('tempdb..#CRMMissingPhone') IS NOT NULL
18BEGIN
19 DROP TABLE #CRMMissingPhone
20END
21
22IF object_id('tempdb..#CRMMissingEmail') IS NOT NULL
23BEGIN
24 DROP TABLE #CRMMissingEmail
25END
26
27IF object_id('tempdb..#CRMMissingPeople') IS NOT NULL
28BEGIN
29 DROP TABLE #CRMMissingPeople
30END
31
32--------------------------------------------------------------------------------------------------------------------------
33--
34-- Create the stored procedure to create the Account for the Company.
35--
36--------------------------------------------------------------------------------------------------------------------------
37
38IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateAccountFromCompany]') AND type in (N'P', N'PC'))
39DROP PROCEDURE [dbo].spCreateAccountFromCompany
40GO
41
42CREATE PROCEDURE [dbo].spCreateAccountFromCompany
43(
44 @companyID INT
45)
46
47AS
48
49BEGIN
50 -- SET NOCOUNT ON added to prevent extra result sets from
51 -- interfering with SELECT statements.
52
53 SET NOCOUNT ON;
54 PRINT 'spCreateAccountFromCompany started with ID:' + CONVERT(VARCHAR, @companyID) + ' at ' + CONVERT(VARCHAR(24), GETDATE(), 113)
55
56 -- Declare required variables.
57
58 DECLARE @accountID INT
59 DECLARE @GBPCurrencyID BIGINT
60 -- Get the GBP Currency
61 SELECT TOP 1 @GBPCurrencyID = [Currency].[Curr_CurrencyID] FROM [Currency] WHERE [Currency].[Curr_Symbol] LIKE 'GBP%'
62
63 DECLARE @intID INT
64 -- Get the default integration ID
65 SELECT TOP 1 @intID = ERPIntegrations.ERPI_IntegrationID FROM ERPIntegrations where ERPIntegrations.ERPI_Deleted IS NULL
66
67 -- Get the next id for the Account table.
68
69 -- @accountID = [dbo].[eware_get_identity_id] @table_name = N'account'
70
71 -- Create the Account record.
72
73 INSERT INTO [Account]
74 (
75 --[Acc_AccountID]
76 [Acc_CreatedBy]
77 ,[Acc_CreatedDate]
78 ,[Acc_UpdatedBy]
79 ,[Acc_UpdatedDate]
80 ,[Acc_TimeStamp]
81 ,[Acc_Deleted]
82 ,[Acc_WorkflowId]
83 ,[Acc_Secterr]
84 ,[Acc_Name]
85 ,[Acc_PrimaryUserId]
86 ,[Acc_CompanyId]
87 ,[Acc_PrimaryPersonID]
88 ,[Acc_PrimaryAddressID]
89 ,[Acc_ChannelId]
90 ,[Acc_Type]
91 ,[Acc_LibraryDir]
92 ,[Acc_Promote]
93 ,[Acc_Balance]
94 ,[Acc_Balance_CID]
95 ,[Acc_CreditLimit]
96 ,[Acc_CreditLimit_CID]
97 ,[Acc_OnHold]
98 ,[Acc_CurrencyID]
99 ,[Acc_TaxRate]
100 ,[Acc_PayTerms]
101 ,[Acc_PricingListID]
102 ,[Acc_intforeignid]
103 ,[Acc_intid]
104 ,[Acc_intlastsyncdate]
105 ,[Acc_SyncErrorDateTime]
106 ,[Acc_SynchStatus]
107 ,[acc_ConflictResDate]
108 ,[acc_accountingtype]
109 ,[acc_availablecredit]
110 ,[acc_countrycodeid]
111 ,[acc_currencycodeid]
112 ,[acc_department]
113 ,[acc_discountgroupid]
114 ,[acc_earlysettdays]
115 ,[acc_earlysettlementdiscperc]
116 ,[acc_invoicediscountperc]
117 ,[acc_linediscountperc]
118 ,[acc_orderpriority]
119 ,[acc_paymentterms]
120 ,[acc_paytermsbasisid]
121 ,[acc_erppricinglistid]
122 ,[acc_reference]
123 ,[acc_salutation]
124 ,[acc_shortname]
125 ,[acc_source]
126 ,[acc_status]
127 ,[acc_subtype]
128 ,[acc_suffix]
129 ,[acc_taxrateid]
130 ,[acc_taxregnumber]
131 ,[acc_territory]
132 ,[acc_title]
133 ,[acc_titlecode]
134 ,[acc_valuediscountgroupid]
135 ,[acc_website]
136 ,[acc_termsagreed]
137 )
138 SELECT TOP 1
139 --@accountID -- [Acc_AccountID]
140 [Comp_CreatedBy] -- [Acc_CreatedBy]
141 ,GETDATE() -- [Acc_CreatedDate]
142 ,1 -- [Acc_UpdatedBy]
143 ,GETDATE() -- [Acc_UpdatedDate]
144 ,GETDATE() -- [Acc_TimeStamp]
145 ,[Comp_Deleted] -- [Acc_Deleted]
146 ,[Comp_WorkflowId] -- [Acc_WorkflowId]
147 ,[Comp_SecTerr] -- [Acc_Secterr]
148 ,[Comp_Name] -- [Acc_Name]
149 ,[Comp_PrimaryUserId] -- [Acc_PrimaryUserId]
150 ,[Comp_CompanyId] -- [Acc_CompanyId]
151 ,[Comp_PrimaryPersonId] -- [Acc_PrimaryPersonID]
152 ,[Comp_PrimaryAddressId] -- [Acc_PrimaryAddressID]
153 ,[Comp_ChannelID] -- [Acc_ChannelId]
154 ,( CASE [Comp_Type] WHEN 'Supplier' THEN 'Supplier' WHEN 'Customer' THEN 'Customer' ELSE 'Customer' END ) -- [Acc_Type]
155 ,[Comp_LibraryDir] -- [Acc_LibraryDir]
156 ,NULL -- [Acc_promote]
157 ,NULL -- [Acc_Balance]
158 ,@GBPCurrencyID -- [Acc_Balance_CID]
159 ,NULL -- [Acc_CreditLimit]
160 ,@GBPCurrencyID -- [Acc_CreditLimit_CID]
161 ,NULL -- [Acc_OnHold]
162 ,@GBPCurrencyID -- [Acc_CurrencyID]
163 ,NULL -- [Acc_TaxRate]
164 ,NULL -- [Acc_PayTerms]
165 ,NULL -- [Acc_PricingListID]
166 ,NULL -- [Acc_intforeignid]
167 ,@intID -- [Acc_intid]
168 ,NULL -- [Acc_intlastsyncdate]
169 ,NULL -- [Acc_syncErrorDateTime]
170 ,'notlinked' -- [Acc_synchstatus]
171 ,NULL -- [Acc_conflictresdate]
172 ,NULL -- [Acc_accountingtype]
173 ,NULL -- [Acc_availablecredit]
174 ,NULL -- [Acc_countrycodeid]
175 ,NULL -- [acc_currencycodeid]
176 ,NULL -- [Acc_department]
177 ,NULL -- [Acc_discountgroupid]
178 ,NULL -- [Acc_earlysettdays]
179 ,NULL -- [Acc_earlysettlementdisperc]
180 ,NULL -- [Acc_invoicediscountperc]
181 ,NULL -- [Acc_linediscountperc]
182 ,NULL -- [Acc_orderpriority]
183 ,NULL -- [Acc_paymentterms]
184 ,NULL -- [Acc_paytermsbasisid]
185 ,NULL -- [Acc_erppricinglistid]
186 ,NULL -- [Acc_reference]
187 ,NULL -- [Acc_salutation]
188 ,NULL -- [Acc_shortname]
189 ,[Comp_Source] -- [acc_source]
190 ,[Comp_Status] -- [acc_status]
191 ,NULL -- [Acc_subtype]
192 ,NULL -- [Acc_suffix]
193 ,NULL -- [Acc_taxrateid]
194 ,NULL -- [Acc_taxregnumber]
195 ,[Comp_Territory] -- [acc_territory]
196 ,NULL -- [Acc_title]
197 ,NULL -- [Acc_titlecode]
198 ,NULL -- [Acc_valuediscountgroupid]
199 ,[Comp_WebSite] -- [acc_website]
200 ,NULL -- [Acc_termsagreed]
201 FROM
202 [Company]
203 WHERE
204 [comp_companyid] = @companyid
205
206 -- Update the Company with the Account ID
207 select @accountID = acc_accountid from account where Acc_CompanyId = @companyID ;
208
209 UPDATE
210 [Company]
211 SET
212 [Comp_PrimaryAccountId] = @accountID
213 WHERE
214 [Comp_CompanyId] = @companyID
215
216 PRINT 'spCreateAccountFromCompany Complete at ' + CONVERT(VARCHAR(24), GETDATE(), 113)
217
218 RETURN @accountID
219END
220
221GO
222
223
224--------------------------------------------------------------------------------------------------------------------------
225--
226-- Create the stored procedure to create an addresses link
227--
228--------------------------------------------------------------------------------------------------------------------------
229
230IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateAddressLink]') AND type in (N'P', N'PC'))
231DROP PROCEDURE [dbo].spCreateAddressLink
232GO
233
234CREATE PROCEDURE [dbo].spCreateAddressLink
235(
236 @companyID BIGINT,
237 @accountID BIGINT,
238 @addressID BIGINT,
239 @personID BIGINT,
240 @addressType VARCHAR(40)
241)
242
243AS
244
245BEGIN
246
247--PRINT 'Entered spCreateAddressLink'
248
249DECLARE @AddressLinkID BIGINT
250
251--EXEC @AddressLinkID = [dbo].[eware_get_identity_id] @table_name = N'Address_Link'
252
253PRINT 'Inserting account address link id for actual type:' + CONVERT(VARCHAR,@AddressLinkID) + ' - ' + CONVERT(VARCHAR(24), GETDATE(), 113)
254
255INSERT INTO
256 [Address_Link] (
257 --[AdLi_AddressLinkId],
258 [AdLi_AddressId], [AdLi_CompanyID],
259 [AdLi_PersonID], [AdLi_Type], [AdLi_CreatedBy], [AdLi_CreatedDate],
260 [AdLi_UpdatedBy], [AdLi_UpdatedDate], [AdLi_TimeStamp], [AdLi_Deleted], [AdLi_AccountId] )
261VALUES
262(
263 --@AddressLinkID
264 @AddressID
265 ,@companyID
266 ,@personID
267 ,@AddressType
268 ,NULL
269 ,GETDATE()
270 ,NULL
271 ,GETDATE()
272 ,GETDATE()
273 ,NULL
274 ,@accountID
275)
276
277--PRINT 'Exited spCreateAddressLink'
278END
279
280GO
281
282--------------------------------------------------------------------------------------------------------------------------
283--
284-- Create the stored procedure to create an person link
285--
286--------------------------------------------------------------------------------------------------------------------------
287
288IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreatePersonLink]') AND type in (N'P', N'PC'))
289DROP PROCEDURE [dbo].spCreatePersonLink
290GO
291
292CREATE PROCEDURE [dbo].spCreatePersonLink
293(
294 @companyID BIGINT,
295 @accountID BIGINT,
296 @personID BIGINT,
297 @personType VARCHAR(40)
298)
299
300AS
301
302BEGIN
303
304--PRINT 'Entered spCreatePersonLink'
305
306DECLARE @NewPersonLinkID BIGINT
307
308--EXEC @NewPersonLinkID = [dbo].[eware_get_identity_id] @table_name = N'Person_Link'
309
310INSERT INTO [Person_Link]
311 (--[PeLi_PersonLinkId],
312 [PeLi_PersonId]
313 ,[PeLi_CompanyID]
314 ,[PeLi_Type]
315 ,[PeLi_CreatedBy]
316 ,[PeLi_CreatedDate]
317 ,[PeLi_UpdatedBy]
318 ,[PeLi_UpdatedDate]
319 ,[PeLi_TimeStamp]
320 ,[PeLi_Deleted]
321 ,[PeLi_AccountId])
322VALUES
323(
324 --@NewPersonLinkID,
325 @personID,
326 @companyID,
327 NULL,
328 NULL,
329 GETDATE(),
330 NULL,
331 GETDATE(),
332 GETDATE(),
333 NULL,
334 @accountID
335 )
336
337
338--PRINT 'Exited spCreatePersonLink'
339END
340
341GO
342
343
344--------------------------------------------------------------------------------------------------------------------------
345--
346-- Create the stored procedure to create an phone record
347--
348--------------------------------------------------------------------------------------------------------------------------
349
350IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreatePhone]') AND type in (N'P', N'PC'))
351DROP PROCEDURE [dbo].spCreatePhone
352GO
353
354CREATE PROCEDURE [dbo].spCreatePhone
355(
356 @companyID BIGINT,
357 @accountID BIGINT,
358 @type VARCHAR(40),
359 @phoneID BIGINT,
360 @sourceEntityID BIGINT,
361 @targetEntityID BIGINT
362)
363
364AS
365
366BEGIN
367
368--PRINT 'Entered spCreatePhone'
369DECLARE @NewPhoneID BIGINT
370--EXEC @NewPhoneID = [dbo].[eware_get_identity_id] @table_name = N'Phone'
371
372INSERT INTO [Phone]
373 (--[Phon_PhoneID],
374 [Phon_CountryCode]
375 ,[Phon_AreaCode]
376 ,[Phon_Number]
377 ,[Phon_CreatedBy]
378 ,[Phon_CreatedDate]
379 ,[Phon_UpdatedBy]
380 ,[Phon_UpdatedDate]
381 ,[Phon_TimeStamp]
382 ,[Phon_Deleted]
383 ,[Phon_ChannelID]
384 ,[phon_segmentid]
385 )
386SELECT
387 --@NewPhoneID,
388 [Phon_CountryCode]
389 ,[Phon_AreaCode]
390 ,[Phon_Number]
391 ,NULL
392 ,GETDATE()
393 ,NULL
394 ,GETDATE()
395 ,GETDATE()
396 ,NULL
397 ,[Phon_ChannelID]
398 ,[phon_segmentid]
399
400 FROM PHONE
401 WHERE Phon_PhoneId = @phoneID
402
403select top 1 @NewPhoneID = phon_phoneid from phone order by Phon_CreatedDate desc
404
405DECLARE @NewPhoneLinkID BIGINT
406--EXEC @NewPhoneLinkID = [dbo].[eware_get_identity_id] @table_name = N'PhoneLink'
407
408INSERT INTO PhoneLink
409 (--PLink_LinkID,
410 PLink_CreatedBy,
411 PLink_CreatedDate,
412 PLink_UpdatedBy,
413 PLink_UpdatedDate,
414 PLink_TimeStamp,
415 PLink_Deleted,
416 PLink_RecordID,
417 PLink_EntityID,
418 PLink_Type,
419 PLink_PhoneId)
420SELECT
421 --@NewPhoneLinkID,
422 NULL,
423 GETDATE(),
424 NULL,
425 GETDATE(),
426 GETDATE(),
427 NULL,
428 @accountID,
429 @targetEntityID,
430 plink_type,
431 @NewPhoneID
432FROM PhoneLink
433WHERE PLink_RecordID = @companyID
434AND PLink_EntityID = @sourceEntityID
435AND PLink_Type = @type
436AND PLink_Deleted IS NULL
437
438--PRINT 'Exited spCreatePhone'
439END
440
441GO
442
443
444
445--------------------------------------------------------------------------------------------------------------------------
446--
447-- Create the stored procedure to create an email record
448--
449--------------------------------------------------------------------------------------------------------------------------
450
451IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateEmail]') AND type in (N'P', N'PC'))
452DROP PROCEDURE [dbo].spCreateEmail
453GO
454
455CREATE PROCEDURE [dbo].spCreateEmail
456(
457 @companyID BIGINT,
458 @accountID BIGINT,
459 @type VARCHAR(40),
460 @emailID BIGINT,
461 @sourceEntityID BIGINT,
462 @targetEntityID BIGINT
463)
464
465AS
466
467BEGIN
468
469--PRINT 'Entered spCreatePhone'
470DECLARE @NewEmailID BIGINT
471--EXEC @NewEmailID = [dbo].[eware_get_identity_id] @table_name = N'Email'
472
473INSERT INTO Email
474 (--[Emai_EmailId] ,
475 [Emai_EmailAddress]
476 ,[Emai_CreatedBy]
477 ,[Emai_CreatedDate]
478 ,[Emai_UpdatedBy]
479 ,[Emai_UpdatedDate]
480 ,[Emai_TimeStamp]
481 ,[Emai_Deleted]
482 ,[Emai_ChannelID]
483 ,[Emai_segmentid]
484 )
485SELECT
486 --@NewEmailID,
487 [Emai_EmailAddress]
488 ,NULL
489 ,GETDATE()
490 ,NULL
491 ,GETDATE()
492 ,GETDATE()
493 ,NULL
494 ,[Emai_ChannelID]
495 ,[emai_segmentid]
496
497 FROM Email
498 WHERE Emai_EmailId = @emailID
499
500 select top 1 @NewemailID = Emai_EmailId from Email order by Emai_CreatedDate desc
501
502DECLARE @NewEmailLinkID BIGINT
503--EXEC @NewEmailLinkID = [dbo].[eware_get_identity_id] @table_name = N'EmailLink'
504
505INSERT INTO EmailLink
506 (--ELink_LinkID,
507 ELink_CreatedBy,
508 ELink_CreatedDate,
509 ELink_UpdatedBy,
510 ELink_UpdatedDate,
511 ELink_TimeStamp,
512 ELink_Deleted,
513 ELink_RecordID,
514 ELink_EntityID,
515 ELink_Type,
516 ELink_EmailId)
517SELECT
518 --@NewEmailLinkID,
519 NULL,
520 GETDATE(),
521 NULL,
522 GETDATE(),
523 GETDATE(),
524 NULL,
525 @accountID,
526 @targetEntityID,
527 Elink_Type,
528 @NewEmailID
529FROM EmailLink
530WHERE ELink_RecordID = @companyID
531AND ELink_EntityID = @sourceEntityID
532AND ELink_Type = @type
533AND ELink_Deleted IS NULL
534
535--PRINT 'Exited spCreateEmail'
536END
537
538GO
539
540
541
542-------------------------------------------------------------------------------------------------------------------------
543--
544-- Create the Stored procedure to process all of the records for a specific table and create journal records
545--
546--------------------------------------------------------------------------------------------------------------------------
547
548
549IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateMissingJournalEntries]') AND type in (N'P', N'PC'))
550DROP PROCEDURE [dbo].[spCreateMissingJournalEntries]
551GO
552
553CREATE PROCEDURE [dbo].[spCreateMissingJournalEntries]
554 @TableJournalID BIGINT,
555 @TableName VARCHAR(40),
556 @PKFieldName VARCHAR(80),
557 @LastUpdateFieldName VARCHAR(100),
558 @FieldPrefix VARCHAR(100)
559AS
560
561BEGIN
562 DECLARE @RowCount BIGINT
563 DECLARE @CurrentRow BIGINT
564 DECLARE @JournalCreatedBY BIGINT
565 DECLARE @JournalChangeType BIGINT
566 --DECLARE @JournalIntegrationID BIGINT
567 DECLARE @LastJournalEntryID BIGINT
568 DECLARE @UpdatedDate DATETIME
569 DECLARE @SelectString VARCHAR(1000)
570 DECLARE @UpdateString VARCHAR(1000)
571
572 IF object_id('tempdb..#MissingEntryTable') IS NOT NULL
573 BEGIN
574 DROP TABLE #MissingEntryTable
575 END
576
577 -- Normally a table variable would be used here, but we need to reference this table in a EXEC statement,
578 -- by creating a temp table we give it scope beyond this SP and so the EXEC will recognise it
579 CREATE TABLE #MissingEntryTable
580 (
581 RowNumber BIGINT IDENTITY(1,1),
582 MissingEntryID BIGINT NOT NULL,
583 IntID BIGINT
584 )
585
586 IF object_id('tempdb..#TempJournal') IS NOT NULL
587 BEGIN
588 DROP TABLE #TempJournal
589 END
590
591 CREATE TABLE #TempJournal
592 (
593 [Jour_Id] [int] NOT NULL,
594 [Jour_CreatedBy] [int] NULL,
595 [Jour_CreatedDate] [datetime] NULL,
596 [Jour_UpdatedBy] [int] NULL,
597 [Jour_UpdatedDate] [datetime] NULL,
598 [Jour_TimeStamp] [datetime] NULL,
599 [Jour_Deleted] [int] NULL,
600 [Jour_RecordID] [int] NULL,
601 [Jour_TableID] [int] NULL,
602 [Jour_DBChangeType] [int] NULL,
603 [Jour_IntegrationID] [int] NULL
604 )
605
606 SET @JournalCreatedBY = 1 -- 1 is the system admin
607 SET @JournalChangeType = 1 -- 1 is created, must always be created
608 SET @UpdatedDate = GETDATE() -- Sets the updated date as right now
609
610 -- Get the first enabled integration
611 --SELECT TOP 1 @JournalIntegrationID = ERPIntegrations.ERPI_IntegrationID FROM ERPIntegrations WHERE ERPIntegrations.ERPI_Deleted IS NULL
612
613 -- lets get a list of records that haven't been added to the table
614 SET @SelectString = 'SELECT [' + RTRIM(@TableName) + '].[' + RTRIM(@PKFieldName) + '], [' + RTRIM(@TableName) + '].[' + RTRIM(@FieldPrefix) + '_IntID] FROM [' + RTRIM(@TableName) + '] LEFT OUTER JOIN [Journal] ON [' + RTRIM(@TableName) + '].[' + RTRIM(@PKFieldName) + '] = [Journal].[Jour_RecordID] AND [Journal].[Jour_TableID] = ' + CONVERT(VARCHAR(10), @TableJournalID) + ' WHERE [Journal].[Jour_ID] IS NULL'
615
616 -- Get a list of missing entries from the journal table
617 INSERT INTO #MissingEntryTable (MissingEntryID, IntID) EXEC(@SelectString)
618
619 SET @CurrentRow = 1
620 SELECT @RowCount = COUNT(*) FROM #MissingEntryTable
621
622 -- for each missing entry
623 WHILE @CurrentRow <= @RowCount
624 BEGIN
625 DECLARE @CurrentRecordID BIGINT
626 DECLARE @CurrentIntID BIGINT
627
628 -- get the primary key for the entity
629 SELECT @CurrentRecordID = MissingEntryID, @CurrentIntID = IntID FROM #MissingEntryTable WHERE RowNumber = @CurrentRow
630
631 --EXEC @LastJournalEntryID = [dbo].[eware_get_identity_id] @table_name = N'Journal'
632 -- Create a journal entry for this entity
633 INSERT INTO #TempJournal
634 (--Jour_Id,
635 Jour_CreatedBy, Jour_CreatedDate, Jour_UpdatedBy, Jour_UpdatedDate, Jour_TimeStamp, Jour_RecordID, Jour_TableID, Jour_DBChangeType, Jour_IntegrationID)
636 VALUES
637 (--@LastJournalEntryID,
638 @JournalCreatedBy, @UpdatedDate, @JournalCreatedBy, @UpdatedDate, @UpdatedDate, @CurrentRecordID, @TableJournalID, @JournalChangeType, @CurrentIntID)
639
640 SET @CurrentRow = @CurrentRow + 1
641 END
642
643
644 DECLARE @RangeStart BIGINT
645 DECLARE @RangeEnd BIGINT
646 DECLARE @NextRangeStart BIGINT
647 DECLARE @NextRangeEnd BIGINT
648 DECLARE @NextRange BIGINT
649
650 -- we inserted the values to update into a table variable, so now we need to move them into the actual table,
651 -- we do this so we can wrap it into a transaction to ensure data integrity
652 INSERT INTO Journal
653 (Jour_Id, Jour_CreatedBy, Jour_CreatedDate, Jour_UpdatedBy, Jour_UpdatedDate, Jour_TimeStamp, Jour_RecordID, Jour_TableID, Jour_DBChangeType, Jour_IntegrationID)
654 SELECT Jour_Id, Jour_CreatedBy, Jour_CreatedDate, Jour_UpdatedBy, Jour_UpdatedDate, Jour_TimeStamp, Jour_RecordID, Jour_TableID, Jour_DBChangeType, Jour_IntegrationID FROM #TempJournal
655
656 -- Update the last updated date fields so the next synchornise will pick it up, joining to the temp table means only the ones we used will be updated
657 SET @UpdateString = 'UPDATE [' + RTRIM(@TableName) + '] SET [' + RTRIM(@TableName) + '].[' + RTRIM(@LastUpdateFieldName) + '] = GETDATE() FROM [' + RTRIM(@TableName) + '] INNER JOIN #MissingEntryTable ON [' + RTRIM(@TableName) + '].[' + RTRIM(@PKFieldName) + '] = #MissingEntryTable.MissingEntryID'
658 EXEC(@UpdateString)
659
660 IF object_id('tempdb..#MissingEntryTable') IS NOT NULL
661 BEGIN
662 DROP TABLE #MissingEntryTable
663 END
664
665 IF object_id('tempdb..#TempJournal') IS NOT NULL
666 BEGIN
667 DROP TABLE #TempJournal
668 END
669
670END
671
672GO
673
674
675
676
677--------------------------------------------------------------------------------------------------------------------------
678--
679-- Main processing
680--
681--------------------------------------------------------------------------------------------------------------------------
682
683DECLARE @RowCount BIGINT
684DECLARE @CurrentRow BIGINT
685DECLARE @CurrentAccountName VARCHAR(400)
686DECLARE @CurrentCompanyID BIGINT
687DECLARE @CurrentCompanyName VARCHAR(400)
688DECLARE @StartDateTime DATETIME
689DECLARE @CreatedAccountID BIGINT
690DECLARE @CreatedAddressLinkID BIGINT
691DECLARE @AddressRowCount BIGINT
692DECLARE @CurrentAddressID BIGINT
693DECLARE @AddressRow BIGINT
694DECLARE @CurrentAddressType VARCHAR(40)
695DECLARE @PhoneRowCount BIGINT
696DECLARE @CurrentPhoneID BIGINT
697DECLARE @CurrentPhoneType VARCHAR(40)
698DECLARE @PhoneRow BIGINT
699DECLARE @EmailRowCount BIGINT
700DECLARE @CurrentEmailID BIGINT
701DECLARE @CurrentEmailType VARCHAR(40)
702DECLARE @EmailRow BIGINT
703DECLARE @PersonRowCount BIGINT
704DECLARE @CurrentPersonID BIGINT
705DECLARE @CurrentPersonType VARCHAR(40)
706DECLARE @PersonRow BIGINT
707DECLARE @sourceEntityID BIGINT
708DECLARE @targetEntityID BIGINT
709
710SET @StartDateTime = GetDate()
711
712PRINT @StartDateTime
713--------------------------------------------------------------------------------------------------------
714--
715-- Create an account for each company that has no account
716--
717--------------------------------------------------------------------------------------------------------
718
719
720CREATE TABLE #CRMMissingAccounts
721(
722 RowNumber BIGINT IDENTITY(1,1),
723 CompanyID BIGINT,
724 CompanyName VARCHAR(400)
725)
726
727CREATE TABLE #CRMMissingAddressLinks
728(
729 RowNumber BIGINT IDENTITY(1,1),
730 CompanyID BIGINT,
731 AccountID BIGINT,
732 PersonID BIGINT,
733 AddressID BIGINT,
734 AddressType VARCHAR(40)
735)
736
737CREATE TABLE #CRMMissingPhone
738(
739 RowNumber BIGINT IDENTITY(1,1),
740 PhoneID BIGINT,
741 PhoneType VARCHAR(40)
742)
743
744CREATE TABLE #CRMMissingEmail
745(
746 RowNumber BIGINT IDENTITY(1,1),
747 EmailID BIGINT,
748 EmailType VARCHAR(40)
749)
750
751CREATE TABLE #CRMMissingPeople
752(
753 RowNumber BIGINT IDENTITY(1,1),
754 PersonID BIGINT,
755 PersonType VARCHAR(40)
756)
757
758-- Get a list of companies that have no acount
759
760INSERT INTO #CRMMissingAccounts (CompanyID, CompanyName)
761SELECT DISTINCT Company.Comp_CompanyId, Company.Comp_Name
762 FROM Company
763 LEFT OUTER JOIN Account ON Company.Comp_CompanyId = Account.Acc_CompanyId AND Account.Acc_Deleted IS NULL
764WHERE
765 Account.Acc_CompanyId IS NULL
766 AND Company.Comp_Deleted IS NULL
767
768-- Prepare row counter
769SET @CurrentRow = 1
770SELECT @RowCount = COUNT(*) FROM #CRMMissingAccounts
771
772PRINT 'Found ' + CONVERT(VARCHAR, @RowCount) + ' companies with no accounts - Started processing at: ' + CONVERT(VARCHAR(24), GETDATE(), 113)
773
774-- For each company
775WHILE(@CurrentRow <= @RowCount)
776BEGIN
777 SELECT @CurrentCompanyID = CompanyID, @CurrentCompanyName = CompanyName FROM #CRMMissingAccounts WHERE RowNumber = @CurrentRow
778
779 PRINT 'Processing ' + @CurrentCompanyName + ' : row ' + CONVERT(VARCHAR, @CurrentRow) + ' of ' + CONVERT(VARCHAR, @RowCount) + ' at ' + CONVERT(VARCHAR(24), GETDATE(), 113)
780
781 -- Create a new account with company name for account name
782 EXEC @CreatedAccountID = spCreateAccountFromCompany @companyID = @CurrentCompanyID
783
784 UPDATE Company SET Comp_PrimaryAccountId = @CreatedAccountID WHERE Comp_CompanyId = @CurrentCompanyID
785
786 -- For each company addresses create an address link to link the account
787
788 INSERT INTO #CRMMissingAddressLinks(CompanyID, AccountID, PersonID, AddressID, AddressType)
789 SELECT AdLi_CompanyID, AdLi_AccountId, AdLi_PersonID, AdLi_AddressId, AdLi_Type
790 FROM Address_Link
791 WHERE adli_companyid = @CurrentCompanyID
792 AND adli_personID IS NULL
793 AND adli_accountID IS NULL
794
795 SET @AddressRow = 1
796 SELECT @AddressRowCount = COUNT(*) FROM #CRMMissingAddressLinks
797
798 -- loop addresses
799 WHILE (@AddressRow <= @AddressRowCount)
800 BEGIN
801 SELECT @CurrentAddressID = AddressID, @CurrentAddressType = AddressType FROM #CRMMissingAddressLinks WHERE RowNumber = @AddressRow
802
803 EXEC spCreateAddressLink @companyID = NULL , @accountID = @CreatedAccountID , @addressID = @CurrentAddressID , @personID = NULL , @addressType = @CurrentAddressType
804
805 SET @AddressRow = @AddressRow + 1
806 END
807 TRUNCATE TABLE #CRMMissingAddressLinks
808
809 -- Create a copy of the phone record and the related phone link record
810
811 SELECT @sourceEntityID = Bord_TableId from Custom_Tables where Bord_Name = 'Company'
812 SELECT @targetEntityID = Bord_TableId from Custom_Tables where Bord_Name = 'Account'
813
814 INSERT INTO #CRMMissingPhone(PhoneID, PhoneType)
815 SELECT PLink_PhoneId, PLink_Type
816 FROM PhoneLink
817 WHERE PLink_EntityID = @sourceEntityID
818 AND PLink_RecordID = @CurrentCompanyID
819 AND PLink_Deleted IS NULL
820
821 SET @PhoneRow = 1
822 SELECT @PhoneRowCount = COUNT(*) FROM #CRMMissingPhone
823
824 -- loop company phones
825 WHILE @PhoneRow <= @PhoneRowCount
826 BEGIN
827 SELECT @CurrentPhoneID = PhoneID, @CurrentPhoneType = PhoneType FROM #CRMMissingPhone WHERE RowNumber = @PhoneRow
828
829 EXEC spCreatePhone @companyID = @CurrentCompanyID, @accountID = @CreatedAccountID, @type = @CurrentPhoneType, @phoneID = @CurrentPhoneID, @sourceEntityID = @sourceEntityID, @targetEntityID = @targetEntityID
830
831 SET @PhoneRow = @PhoneRow + 1
832 END
833 TRUNCATE TABLE #CRMMissingPhone
834
835 -- Create a copy of the email record and the related email link record
836
837 INSERT INTO #CRMMissingEmail(EmailID, EmailType)
838 SELECT ELink_EmailId, ELink_Type
839 FROM EmailLink
840 WHERE ELink_EntityID = @sourceEntityID
841 AND ELink_RecordID = @CurrentCompanyID
842 AND ELink_Deleted IS NULL
843
844 SET @EmailRow = 1
845 SELECT @EmailRowCount = COUNT(*) FROM #CRMMissingEmail
846
847 -- loop company emails
848 WHILE @EmailRow <= @EmailRowCount
849 BEGIN
850 SELECT @CurrentEmailID = EmailID, @CurrentEmailType = EmailType FROM #CRMMissingEmail WHERE RowNumber = @EmailRow
851
852 EXEC spCreateEmail @companyID = @CurrentCompanyID, @accountID = @CreatedAccountID, @type = @CurrentEmailType, @emailID = @CurrentEmailID, @sourceEntityID = @sourceEntityID, @targetEntityID = @targetEntityID
853
854 SET @EmailRow = @EmailRow + 1
855 END
856 TRUNCATE TABLE #CRMMissingEmail
857
858 -- Update the Person records so that they have the new Account ID...
859 UPDATE [Person] SET
860 [Pers_AccountId] = @CreatedAccountID,
861 Pers_TimeStamp = @StartDateTime,
862 Pers_UpdatedDate = @StartDateTime
863 WHERE
864 [Pers_CompanyId] = @CurrentCompanyID
865
866 INSERT INTO #CRMMissingPeople(PersonID, PersonType)
867 SELECT PeLi_PersonId, PeLi_Type FROM Person_Link
868 WHERE PeLi_CompanyID = @CurrentCompanyID
869 AND PeLi_AccountId IS NULL
870 AND PeLi_Deleted IS NULL
871
872 SET @PersonRow = 1
873 SELECT @PersonRowCount = COUNT(*) FROM #CRMMissingPeople
874
875 -- loop people
876 WHILE @PersonRow <= @PersonRowCount
877 BEGIN
878 SELECT @CurrentPersonID = PersonID, @CurrentPersonType = PersonType FROM #CRMMissingPeople WHERE RowNumber = @PersonRow
879
880 EXEC spCreatePersonLink @companyID = @CurrentCompanyID, @accountID = @CreatedAccountID, @personID = @CurrentPersonID, @personType = @CurrentPersonType
881 -- The company and account share the same person so the phones and emails are also shared
882 -- We do not sync addresses on people so do not need to create a new address
883
884 SET @PersonRow = @PersonRow + 1
885 END
886 TRUNCATE TABLE #CRMMissingPeople
887
888 SET @CurrentRow = @CurrentRow + 1
889END
890
891 -- Clear company ID's if account is deleted
892
893UPDATE
894[Account]
895SET
896[Acc_CompanyId] = NULL
897WHERE
898[Acc_Deleted] = '1'
899
900-- Cases: Set the Account id for those cases previously linked to the Company.
901
902UPDATE
903 [Cases]
904SET
905 [Cases].[Case_PrimaryAccountId] = [Company].[Comp_PrimaryAccountId]
906FROM
907 [Cases]
908 INNER JOIN [Company] ON [Cases].[Case_PrimaryCompanyId] = [Company].[Comp_CompanyId]
909
910-- Opportunity: Set the Account id for those cases previously linked to the Company.
911
912UPDATE
913 [Opportunity]
914SET
915 [Opportunity].[Oppo_PrimaryAccountId] = [Company].[Comp_PrimaryAccountId]
916FROM
917 [Opportunity]
918 INNER JOIN [Company] ON [Opportunity].[Oppo_PrimaryCompanyId] = [Company].[Comp_CompanyId]
919
920-- Comm_Link: Set the Account id for those cases previously linked to the Company.
921
922UPDATE
923 [Comm_Link]
924SET
925 [Comm_Link].[CmLi_Comm_AccountId] = [Company].[Comp_PrimaryAccountId]
926FROM
927 [Comm_Link]
928 INNER JOIN [Company] ON [Comm_Link].[CmLi_Comm_CompanyId] = [Company].[Comp_CompanyId]
929
930-- Library: Set the Account id for those cases previously linked to the Company.
931
932UPDATE
933 [Library]
934SET
935 [Library].[Libr_AccountId] = [Company].[Comp_PrimaryAccountId]
936FROM
937 [Library]
938 INNER JOIN [Company] ON [Library].[Libr_CompanyId] = [Company].[Comp_CompanyId]
939
940-- Marketing: Set the Account id for those cases previously linked to the Company.
941
942UPDATE
943 [Marketing]
944SET
945 [Marketing].[Mrkt_AccountId] = [Company].[Comp_PrimaryAccountId]
946FROM
947 [Marketing]
948 INNER JOIN [Company] ON [Marketing].[Mrkt_CompanyId] = [Company].[Comp_CompanyId]
949
950-- Create journal records
951
952DECLARE @JournaledTables TABLE
953(
954 RowNumber BIGINT IDENTITY(1,1),
955 TableID BIGINT NOT NULL,
956 TableName VARCHAR(40) NOT NULL,
957 PKFieldName VARCHAR(80) NOT NULL,
958 FieldPrefix VARCHAR(100) NOT NULL
959)
960
961-- Get a list of tables that are meant to be in the journal table
962INSERT INTO @JournaledTables SELECT Bord_TableId, Bord_Name, Bord_IdField, Bord_Prefix FROM Custom_Tables WHERE Custom_Tables.bord_IntegrationTable = 'Y'
963
964SET @CurrentRow = 1
965SELECT @RowCount = COUNT(*) FROM @JournaledTables
966
967-- for each table that is meant to be synchronised
968WHILE @CurrentRow <= @RowCount
969BEGIN
970 DECLARE @TableID BIGINT -- the id of the table in question
971 DECLARE @TableName VARCHAR(40) -- the actual name of the table
972 DECLARE @PKFieldName VARCHAR(80) -- the name of the primary key field
973 DECLARE @FieldPrefix VARCHAR(100) -- the name of the prefix field, has _UpdatedDate appended to to it so we can update the date so that the syncrhonisation will pick it up
974 DECLARE @UpdatedDate VARCHAR(100) -- The name of the prefix
975
976 -- set our values that change from table to table
977 SELECT @TableID = TableID, @TableName = TableName, @PKFieldName = PKFieldName, @UpdatedDate = RTRIM(FieldPrefix) + '_UpdatedDate', @FieldPrefix = FieldPrefix FROM @JournaledTables WHERE RowNumber = @CurrentRow
978
979 -- run the store procedure we have created to add the records in to the journal table
980 EXEC spCreateMissingJournalEntries @TableID, @TableName, @PKFieldName, @UpdatedDate, @FieldPrefix
981
982 SET @CurrentRow = @CurrentRow + 1
983END
984
985
986UPDATE Journal SET Jour_UpdatedDate = GETDATE(), Jour_TimeStamp = GETDATE() where Jour_IntegrationID IS NULL
987
988
989PRINT 'Completed update of companies with no accounts at: ' + CONVERT(VARCHAR(24), GETDATE(), 113)
990
991--------------------------------------------------------------------------------------------------------------------------
992--
993-- Drop the stored procedures & temporary tables
994--
995--------------------------------------------------------------------------------------------------------------------------
996--
997
998IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateAccountFromCompany]') AND type in (N'P', N'PC'))
999DROP PROCEDURE [dbo].spCreateAccountFromCompany
1000GO
1001
1002IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateAddressLink]') AND type in (N'P', N'PC'))
1003DROP PROCEDURE [dbo].spCreateAddressLink
1004GO
1005
1006IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreatePersonLink]') AND type in (N'P', N'PC'))
1007DROP PROCEDURE [dbo].spCreatePersonLink
1008GO
1009
1010IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreatePhone]') AND type in (N'P', N'PC'))
1011DROP PROCEDURE [dbo].spCreatePhone
1012GO
1013
1014IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateEmail]') AND type in (N'P', N'PC'))
1015DROP PROCEDURE [dbo].spCreateEmail
1016GO
1017
1018IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCreateMissingJournalEntries]') AND type in (N'P', N'PC'))
1019DROP PROCEDURE [dbo].[spCreateMissingJournalEntries]
1020GO
1021
1022IF object_id('tempdb..#CRMMissingAccounts') IS NOT NULL
1023BEGIN
1024 DROP TABLE #CRMMissingAccounts
1025END
1026
1027IF object_id('tempdb..#CRMMissingAddressLinks') IS NOT NULL
1028BEGIN
1029 DROP TABLE #CRMMissingAddressLinks
1030END
1031
1032IF object_id('tempdb..#CRMMissingPhone') IS NOT NULL
1033BEGIN
1034 DROP TABLE #CRMMissingPhone
1035END
1036
1037IF object_id('tempdb..#CRMMissingEmail') IS NOT NULL
1038BEGIN
1039 DROP TABLE #CRMMissingEmail
1040END
1041
1042IF object_id('tempdb..#CRMMissingPeople') IS NOT NULL
1043BEGIN
1044 DROP TABLE #CRMMissingPeople
1045END