SUB sqlQVPR

ODBC CONNECT TO SQLQVPR;

LET vSQLDB='QVPR';
LET vDBO= 'dbo';
LET vDBDBO= '$(vSQLDB)' & '.' & '$(vDBO)' & '.'  ;


					mapSDFR:			
					Mapping LOAD 
						mapTaskId,
						mappedPath;
					SQL SELECT RTRIM(LTRIM(ID))		as mapTaskId, //TRIM can be used on SQL Server 2017 and above
						Path as mappedPath //? XML doesn't have a mappedPath field, not sure if it should be path the field to retrieve here
					FROM $(vDBDBO)SourceDocumentFolderResource;
					
					trace 'ngm SOURCE DOCUMENT LOADED';


					mapSourceDocument:
					Mapping LOAD
						mapTaskId,
						ApplyMap('mapSDFR',trim(FolderID)) &Path as mappedTaskFileName;
					SQL SELECT
						LTRIM(RTRIM(ID))		as mapTaskId,
						FolderID,
						Path
					FROM $(vDBDBO)SourceDocument;
				

					TaskTrigger:
					LOAD TaskId,
						TaskTriggerEnabled,
						replace(TriggerType,'Trigger','') 	as [Task Trigger Type];
					SQL SELECT  
						LTRIM(RTRIM(taskID))	as TaskId,
					    [Enabled] 		as TaskTriggerEnabled,
					    TriggerType 
					FROM $(vDBDBO)[Trigger] ;
	
				// Keep DocumentTask as separate table due to inability to join to existing Tasks table (root cause still unknown)
					DocumentTask:
					LOAD 
					    TaskId,	// Only join on TaskId
					    // These fields exist already on Tasks table and will be checked later and 'combined' with any existing field values
					    ApplyMap('mapSourceDocument',SourceDocumentID)	as docTask_Task_FileName,
					    //ApplyMap('mapTaskCategory',trim(ID))			as [Task CategoryDT],
					    SubField(replace(ApplyMap('mapSourceDocument',SourceDocumentID),'/','\'),'\',-1) 	as docTask_DocName,
						docTask_TaskName,
					    docTask_TaskEnabled,
						alt(timestamp(ModifiedTime),timestamp(timestamp#(ModifiedTime,'$(TimestampFormat1)')),timestamp(timestamp#(ModifiedTime,'$(TimestampFormat2)')))	as docTask_TaskModified,
					    // These fields are unique to DocumentTask and are joined into the Tasks table directly							
					    AllowPluginClient,
					    AllowMobileClient,
					    AllowZeroFootprintClient,
					    AllowPDFGeneration,
					    [Task PDF ReportName],
					    AllowDownload,
					    [Task Distribute],
					    SectionAccessUserName,
					    SessionTimeout,
					    DocumentTimeout,
					    replace(ReloadOption,'Reload','')	as ReloadOption;
					SQL SELECT 
						LTRIM(RTRIM(ID))										as TaskId,	// Only join on TaskId
						Name					as docTask_TaskName,
					    [Enabled]					as docTask_TaskEnabled,
					    // These fields are unique to DocumentTask and are joined into the Tasks table directly							
					    AllowPluginClient,
					    AllowMobileClient,
					    AllowZeroFootprintClient,
					    AllowPDFGeneration,
					    PDFReportName			as [Task PDF ReportName],
					    DownloadAccess			AS AllowDownload,
					    Distribute				as [Task Distribute],
					    SectionAccessUserName,
					    SessionTimeout,
					    DocumentTimeout,
					    ReloadOption,
					    SourceDocumentID,
					    ModifiedTime
					FROM $(vDBDBO)DocumentTask;
		
					DistributionDetail:
					LOAD   
						TaskId,
						Distribution,
						'Distribute to ' & RecipientType &': ' & RecipientName as DistributionDetail,
					    RecipientName,
					    RecipientType,
					    DistributionType;
					SQL SELECT 
						LTRIM(RTRIM(DocumentTaskID))	as TaskId,
						1 as Distribution,
						RecipientType,
						RecipientName,
					    DistributionType
					FROM $(vDBDBO)DistributionDetail;


					Concatenate (Tasks)
					SQL SELECT
					    LTRIM(RTRIM(ID))				as TaskId,					
//					    ApplyMap('mapTaskCategory',trim(ID))	as [Task Category],					
						CommandLine			 	as [Task CommandLine],
					    Name 					as [Task Name],
					    Enabled			 		as [Task Enabled],
					    Description 			as [Task Description],
					    IgnoreErrors			as [Task IgnoreErrors],
					    1						as TaskExternalProgram
					FROM $(vDBDBO)ExternalProgramTask;

				
		
	/*	About the QVPR files
	
	DistributionDetail	>> Keep this separate with Recipient Type and Name because can have multiple entries per Task
		> in DistributionDetail entity:
			DocumentTaskID 					as TaskId 	> links to >	TaskId in Tasks and ID in DocumentTask, 
			RecipientName="All Users" + RecipientType="Anonymous" 
			Unused Data: DistributionType,DDDField,DDDValueType,FSPath,SubPath,QvsResourceID,ValidateEmails,ID,ModifiedByUser,ModifiedTime,IsDynamicDistribution
	
	DocumentTask	
		> in DocumentTask entity:
			ID							as TaskId	> to link with DistributionDetail for recipient name and type
			Name as DT_Name 			< Already have this from Tasks!
			Enabled as DT_Enabled 		< Already have this in Tasks
			Description as DT_Description	< Missing from tasks
			AllowPluginClient,AllowMobileClient,AllowZeroFootprintClient,AllowPDFGeneration,
			SourceDocumentID			< Links with ID from SourceDocument.xml
			Distribute="true"
	
	maybe: 	SectionAccessUserName,SectionAccessPassword
	
			Unused Data: NameIsAutoGenerated,PDFReportID,AlwaysOpenable,ClearLocks,ClearAll,ClearAlwaysOneSelected,ReapplySelections,
			EnableAuditLogging,SetScript,OverrideXSSectionAccess,MaxOpenSessions,SessionTimeout,DocumentTimeout,ReloadOption, DistributionServiceID,
			TaskTrack,TimeoutMinutes,AjaxUrl,NumberOfAttempts,ScriptParameterName,ScriptParameterValueField,ScriptParameterValueFilename,NameTemplate,
			CreationMode,CreatorUserNames,DownloadAccess,DownloadUsers,ExportAccess,ExportUsers,VersionID,EnableSessionCollaboration,DocumentDescription,
			SendNotificationEmail,ModifiedByUser,ModifiedTime 
	
	ExternalProgramTask	>>> Concatenate this onto the Tasks table. No doc will be directly associated with it.
		>> Might not have any entries (common)
			Name				as [Task Name],
			Enabled				as [Task Enabled],
			Description			as [Task Description],
			ID					as TaskId,
			IgnoreErrors		as [Task IgnoreErrors],
			CommandLine			as [Task CommandLine]
	
	SourceDocument	>> We have most of this data in Tasks & TaskExecutionHistory xmls from QDS. However, that is just for tasks which executed. Need this to show
						Information for Tasks that have not executed (or whose last execution was before the history cutoff date (too old).
						Just concanetante this SourceDocument info into SessionTaskAuditMaster to get DocName linked to TaskId
			
			ID						as SourceDocumentID		> link with DocumentTask
			applymap('mapFolderResource','FolderID') & Path			as Task_FileName

			FolderID		> Need this linked to ____ to get full path and name of Task_FileName (as shown in Tasks table)
			Not used: DistributionServiceID, ModifiedByUser, ModifiedTime
			
	SourceDocumentFolderResource.xml		> to get the full path of the SourceDocument, map Path to SourceDocument via ID and FolderID
			mapFolderResource:
			Load ID, Path 
			....			
			Path	= full path (up to the "path" on SourceDocument)
			ID 						as FolderID > Link to SourceDocument 
	
	Trigger		>> Map this into the Tasks Table (after other QVPR tasks are added to it) to get trigger information	
		> in Trigger:
			TaskID			links to TaskId
			Enabled				as [Task Trigger Enabled],
			TriggerType			as [Task Trigger Type]
			
			Data not used (yet)
			EnableAt,ExpireAt,RunTaskID,MaxCount,Count,StartAt,Days="" EDXPassword,TimeContraintFrom,TimeContraintTo,DayNumbers,Months,Occourence,AndTimeConstraintMinutes,
			MainTriggerID,ID,ModifiedByUser,ModifiedTime,	
	*/

ENDSUB