--select * from tblTemplates BEGIN TRAN declare @sgTemplateName nvarchar(50) declare @templateName nvarchar(30) declare @externalRef nvarchar(20) declare @bu int declare @switch nvarchar(20) ------------------------------------------------------------------------------------------- --Change according to the phase of importing --@switch can either be 'presite' or 'cons' ------------------------------------------------------------------------------------------- set @switch= 'presite' --set @switch= 'cons' set @bu = 3 -- set the business unit here print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- declare @bOfficeOnly int if @switch='presite' begin SET @bOfficeOnly=1 SET @templateName = 'Custom - Leads' set @externalRef = 'Custom - Leads' end else begin SET @bOfficeOnly=0 SET @templateName = 'Construction Template' set @externalRef = 'SS' end ---Here the Normal Code will start, you do not have to make any changes from here ------------------------------------------------------------------------------------------- print 'Load Template Begin' declare @cdJobType char if @switch='presite' SET @cdJobType= 'L' else SET @cdJobType= 'C' -- Create new template insert into tblTemplates (sgTemplateName, fkidBusinessUnit,inNormalDurn, inNormalPriority,cdJobType,bstdactive, sgStdextRef, inStdCustomOrder, fkidStdCreatedBy, dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn) values (@templateName, @bu,100,1,@cdJobType,1, @externalRef, 100, 0, getdate(),0,getdate()) declare @templateref int set @templateref = @@identity print '' print 'Load Template end' print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- --Insert Department that does not exist (this is for 3.12 version onwards - ensure you remove this if you are still on 3.11 version or earlier print 'Load Departments Begin - only available 3.12 version onwards' declare @sgdepartmentname nvarchar(200) declare department_cursor cursor for select department from tmpimportleadstemplate where department <> '' and department collate SQL_Latin1_General_CP1_CI_AS not in (select sgdepartmentname from tblDepartments) group by department open department_cursor fetch next from department_cursor into @sgdepartmentname while @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS (select * from tblDepartments where sgDepartmentName = @sgdepartmentname) BEGIN Insert into tbldepartments (sgDepartmentName,fkidbusinessunit,instdcustomorder,fkidStdCreatedBy,dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn) Values (@sgdepartmentname,0,100,0,getdate(),0,GETDATE()) END fetch next from department_cursor into @sgdepartmentname end close department_cursor deallocate department_cursor print '' print 'Load Departments end' print '------------------------------------------------------------------' --Insert Resource code that does not exist print 'Load Resource Code Begin' declare @sgresourcecode nvarchar(40) declare @sgresourcecodeDesc nvarchar(200) declare @iddepartment INT declare resourcecode_cursor cursor for select sgresourcecode,sgresourcecodeDesc,iddepartment from tmpimportleadstemplate left join tbldepartments on sgdepartmentname = department collate SQL_Latin1_General_CP1_CI_AS --remove this if on version before 3.12 where sgresourcecode <> '' and sgresourcecode collate SQL_Latin1_General_CP1_CI_AS not in (select cdresourceCode from tblResourceCodes) group by sgresourcecode,sgresourcecodeDesc,iddepartment --remove this if on version before 3.12 open resourcecode_cursor fetch next from resourcecode_cursor into @sgresourcecode,@sgresourcecodeDesc,@iddepartment --remove this if on version before 3.12 while @@fetch_status = 0 begin if not exists(select * from tblResourceCodes where cdResourceCode = @sgresourcecode) begin set @sgresourcecode = substring(@sgresourcecode,1,10) if len(@sgresourcecodeDesc) < 1 begin set @sgresourcecodeDesc = @sgresourcecode end Insert into tblResourceCodes(cdResourceCode,sgDescription,bOfficeOnly,fkiddepartment, --remove this if on version before 3.12 inStdCustomOrder,fkidStdCreatedBy,dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn ) VALUES(@sgresourcecode,isnull(@sgresourcecodeDesc,@sgresourcecode),@bOfficeOnly,@iddepartment, --remove this if on version before 3.12 100,0,getdate(),0,GETDATE()) end fetch next from resourcecode_cursor into @sgresourcecode,@sgresourcecodeDesc,@iddepartment --remove this if on version before 3.12 end close resourcecode_cursor deallocate resourcecode_cursor print '' print 'Load Resource Code end' print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- print 'Load Stage Begin' declare @stage nvarchar(100) declare stage_cursor cursor for select stage from tmpimportleadstemplate where stage <> '' and stage collate SQL_Latin1_General_CP1_CI_AS not in (select sgStageName from tblStages) group by stage open stage_cursor fetch next from stage_cursor into @stage while @@fetch_status = 0 begin if not exists(select * from tblstages where sgStageName = @stage) begin declare @stageMaxInorder int select @stageMaxInorder = max(inorder) from tmpimportleadstemplate where stage = @stage group by stage end if len(ltrim(rtrim(@stage)) ) > 50 begin set @stage = substring(ltrim(rtrim(@stage)),1,50) end insert into tblStages (sgStageName,bOfficeOnly,inStdCustomOrder,fkidStdCreatedBy,dtStdCreatedOn, fkidStdLastModifiedBy,dtStdLastModifiedOn,bstdActive) values(@stage,@bOfficeOnly,@stageMaxInorder,0,getdate(),0,getdate(),1) print @stage fetch next from stage_cursor into @stage end close stage_cursor deallocate stage_cursor declare @defaultStage int select top 1 @defaultStage = idStage from tblStages order by idStage desc print '' print 'Load stage end' print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- print 'Load Summary Begin' declare @summary nvarchar(100) declare summary_cursor cursor for select summary from tmpimportleadstemplate where summary <> '' and summary collate SQL_Latin1_General_CP1_CI_AS not in (select sgsummary from tblSummaries) group by summary open summary_cursor fetch next from summary_cursor into @summary while @@fetch_status = 0 begin if not exists(select * from tblSummaries where sgsummary = @summary) begin declare @summaryMaxInorder int select @summaryMaxInorder = max(inOrder) + 1 from tmpimportleadstemplate where summary = @summary group by summary insert into tblSummaries (sgSummary,inColumn,incolour,sgRptHeader,inStdCustomOrder,bStdActive,fkidStdCreatedBy, dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn,fkidbusinessunit) values(@summary,@summaryMaxInorder,10543232,substring(@summary,1,4),100,1,0, getdate(),0,GETDATE(),0) print @summary end fetch next from summary_cursor into @summary end close summary_cursor deallocate summary_cursor print '' print 'Load Summary End' print '------------------------------------------------------------------' print 'Load Milestone Begin' declare @Progresssummary nvarchar(100) declare summary_cursor cursor for select ProgressSummary from tmpimportleadstemplate where ProgressSummary <> '' and ProgressSummary collate SQL_Latin1_General_CP1_CI_AS not in (select sgsummary from tblSummaries) group by ProgressSummary open summary_cursor fetch next from summary_cursor into @Progresssummary while @@fetch_status = 0 begin if not exists(select * from tblSummaries where sgsummary = @Progresssummary) begin declare @progresssummaryMaxInorder int select @progresssummaryMaxInorder = max(inOrder) + 1 from tmpimportleadstemplate where ProgressSummary = @Progresssummary group by ProgressSummary insert into tblSummaries (sgSummary,inColumn,incolour,sgRptHeader,inStdCustomOrder,bStdActive,fkidStdCreatedBy, dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn,fkidbusinessunit) values(@Progresssummary,@progresssummaryMaxInorder,1,substring(@Progresssummary,1,4),100,1,0, getdate(),0,GETDATE(),0) print @Progresssummary end fetch next from summary_cursor into @Progresssummary end close summary_cursor deallocate summary_cursor print '' print 'Load Milestone End' print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- print 'Load Template Item Begin' declare @defaultresourcecode nvarchar(10) select top 1 @defaultresourcecode = cdResourceCode from tblResourceCodes where bstdActive = 1 order by cdResourceCode asc insert into tblTemplateItems ( sgItemname, inOrder, fkidTemplate, fkidSummary,fkidResourceCode,fkidSupplier,fkidStage, dcDuration, dcLeadTime,dcScaleFactor,dcAllowSlippage, cdDateCalc, dcDeltaDays,inReminderDays, cdDelivery, bDelivery, bOptional,bHidden,bAutoComplete, bAdministration,bAutoSchedule,bExtras,dcPoints, bPartialPoints,inWeatherScale,inStdCustomOrder,fkidStdCreatedBy, dtStdCreatedOn, fkidStdLastModifiedBy,dtStdLastModifiedOn,mMessageText ) select substring(i.taskname,1,100), i.inOrder, @templateref, idSummary, isnull(idresourcecode,(select top 1 idResourceCode from tblResourceCodes)) ,idsupplier, isnull(idStage,(select top 1 idstage from tblStages)), isnull(cast(i.Duration as int),0), ISNULL(CAST(i.LeadTime as int),0), 1, 0, case when tasktype = 'Normal' AND @bOfficeOnly=1 then 'F' when tasktype = 'Booking' AND @bOfficeOnly=1 then 'I' when TaskType = 'Meeting' AND @bOfficeOnly=1 then '1' -- need to ********bOfficeOnly=1 if meeting for supervisor when tasktype = 'Free' AND @bOfficeOnly=0 then 'F' when tasktype = 'Key' AND @bOfficeOnly=0 then 'I' when TaskType = 'Linked' AND @bOfficeOnly=0 then 'A' else 'F' end as cddatecalc, ISNULL((ltrim(rtrim(cast(linkedtodays as int)))),0) as inDeltaDays, ISNULL(CAST(i.ReminderDays as int),0), 'L', case when Delivery = 'y' then 1 else 0 end as bdelivery ,case when Optional = 'y' then 1 else 0 end as boptional ,case when ShowExternal = 'y' then 0 else 1 end as bHidden ,case when AutoComp = 'y' then 1 else 0 end as bAutoComplete, case when Admintask = 'y' then 1 else 0 end as bAdministration ,case when AutoSche = 'y' then 1 else 0 end as bAutoSchedule, 0 as bExtras, CASE WHEN sgcomment is not null then cast (sgcomment as int) else 0 end as dcPoints, 0 as bPartialPoints, 0 as inWeatherScale, 100 as inStdCustomOrder, 0, getdate(),0,getdate(), SuppInstructions from tmpimportleadstemplate i left join tblResourceCodes r on i.sgResourceCode = r.cdResourceCode collate SQL_Latin1_General_CP1_CI_AS and r.bstdactive = 1 left join tblSummaries s on s.sgSummary = i.summary collate SQL_Latin1_General_CP1_CI_AS and s.bstdactive = 1 left join tblStages st on st.sgStageName = i.Stage collate SQL_Latin1_General_CP1_CI_AS and st.bstdactive = 1 left join tblSuppliers sup on sgSupplierName = i.ResourceAssignment collate SQL_Latin1_General_CP1_CI_AS and LEN(i.resourceassignment)>3 and sup.bstdactive = 1 update t set sgcomment = idTemplateItem from tmpimportleadstemplate t left join tblTemplateItems ti on t.taskName = ti.sgItemNAme collate SQL_Latin1_General_CP1_CI_AS and ti.fkidTemplate = @templateref print '' print 'Load template item end' print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- print 'Process link task Begin' update ti set ti.fkidCalcFrom = tl.idtemplateItem --select * from tblTemplateITems ti inner join tmpimportleadstemplate t on ti.idTemplateItem = cast(t.sgcomment as int) inner join tblTemplateItems tl on ti.fkidTemplate = tl.fkidTemplate and tl.sgItemNAme = t.linkedTo collate SQL_Latin1_General_CP1_CI_AS print '' print 'Process link task end' print '-------------------------------------------------------------------' ------------------------------------------------------------------------------------------- print 'Process precedent Begin' declare @precedent nvarchar(1000) declare @deltaday nvarchar(1000) declare @idTemplateItem int declare precedent_cursor cursor for select cast(sgComment as int),ltrim(rtrim(Precedent)),ltrim(rtrim(DeltaDays)) from tmpimportleadstemplate where precedent <> '' order by inorder asc open precedent_cursor fetch next from precedent_cursor into @idTemplateItem,@precedent,@deltaday while @@fetch_status = 0 begin print 'Starting loading precedent for' --print cast(@idTemplateItem as nvarchar) --print @precedent --print @deltaday if @precedent <> '' begin declare @processString nvarchar(1000) declare @deltaString nvarchar(1000) set @processString = @precedent set @deltaString = @deltaday declare @bool int set @bool = 0 while @bool = 0 begin if @processString like '%|%' begin print replace(substring(@processString,1,patindex('%|%',@processString)),'|','') print replace(substring(@deltaString,1,patindex('%|%',@deltaString)),'|','') insert into tblPrecedents (fkidTemplateItem, fkidTemplateItemPrecedent,inDeltaDays, sgMessage, inPrecedentType,inStdCustomOrder,bStdActive, fkidStdCreatedBy,dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn) select ti.idtemplateitem, tip.idtemplateitem, isnull(cast(replace(substring(@deltaString,1,patindex('%|%',@deltaString)),'|','')as int),0), 'Auto created by import script',1,100,1, 0, getDate(), 0,GETDATE() from tblTemplateItems ti inner join tmpimportleadstemplate t on cast(t.sgComment as int) = ti.idTemplateItem inner join tblTemplateItems tip on tip.sgItemName = replace(substring(@processString,1,patindex('%|%',@processString)),'|','') and tip.fkidTemplate = ti.fkidTemplate where ti.idTemplateItem = @idTemplateItem set @processString = ltrim(stuff(@processString, 1, patindex('%|%',@processString), '')) set @deltaString = stuff(@deltaString, 1, patindex('%|%',@deltaString), '') print @processString print @deltaString --print '1' end else begin set @bool = 1 print @processString + ' processtring' print @deltaString + ' deltastring' --print patindex('%|%',@deltaString) --print isnull(cast(replace(@deltaString,'|','')as int),0) insert into tblPrecedents (fkidTemplateItem, fkidTemplateItemPrecedent,inDeltaDays, sgMessage, inPrecedentType,inStdCustomOrder,bStdActive, fkidStdCreatedBy,dtStdCreatedOn,fkidStdLastModifiedBy,dtStdLastModifiedOn) select ti.idtemplateitem, tip.idtemplateitem, isnull(cast(replace(@deltaString,'|','')as int),0), 'Auto created by import script',1,100,1, 0, getDate(), 0,GETDATE() from tblTemplateItems ti inner join tmpimportleadstemplate t on cast(t.sgComment as int) = ti.idTemplateItem inner join tblTemplateItems tip on tip.sgItemName = @processString --replace(substring(@processString,1,patindex('%|%',@processString)),'|','') and tip.fkidTemplate = ti.fkidTemplate where ti.idTemplateItem = @idTemplateItem --print '2' end end end print '===============================' fetch next from precedent_cursor into @idTemplateItem,@precedent,@deltaday end close precedent_cursor deallocate precedent_cursor print '' print 'Process precedent end' print '------------------------------------------------------------------' ------------------------------------------------------------------------------------------- if @switch <> 'Presite' begin print 'PO Matching Begin' insert into tblPOTemplateMatches ( [fkidTemplateItem] ,[cdMatchOn] ,[sgPOSearch] ,[snApprovePercent] ,[bPartialPayment] ,[inPriority] ,[bRequirement] ,[cdScaleType] ,[dcComparisonAmount] ,[snScaleRatio] ,[bDistribute] ,[inStdCustomOrder] ,[bStdActive] ,[fkidStdCreatedBy] ,[dtStdCreatedOn] ,[fkidStdLastModifiedBy] ,[dtStdLastModifiedOn]) select cast (sgcomment as int), Case when IncExc = 'Y' and POSupplier = 'Y' and Reference = 'Y' Then 'MAIR' When IncExc = 'Y' and POSupplier <> 'Y' and Reference = 'Y' then 'MIR' When IncExc <> 'Y' and POSupplier = 'Y' and Reference = 'Y' then 'MAR' Else 'MR' end as cdmatchon, -- To populate the right search pattern in case of Databuild load Case when isnull(sgPOCostCode,'')<>'' and (DBLoad='' or DBLoad is null)then '*|*|' + ltrim(rtrim(sgPOCostcode)) +'*|*' Else '*|*|' + ltrim(rtrim(sgPOCostcode)) +'.'+ ltrim(rtrim(DBLoad))+'|*' end as sgPOSearch, 0,0,100, Case when IncExc = 'Y' then 1 else 0 end as bRequirement, 'P',0,1,1,100,1,0,GETDATE(),0,GETDATE() from tmpimportleadstemplate Where Reference = 'y' -- check that all items with PO values have Reference 'Y' print '===============================' print 'PO Matching End' end print 'Import Process Finished please check all statements before committing' print '------------------------------------------------------------------' --rollback --commit