SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

//LIB CONNECT TO 'NP Rest GET Connection Test (domain_administrator)';
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://QlikServer3:4993/api/v1/login/ntlm;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=1;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;PaginationType=None;allowresponseHeaders=1;allowHttpsOnly=false;XUserId=dMPYDSdNNLYSGXRLODcGXSZNTLZGHbNMSB;XPassword=SKUZeSUEQLYGXZdOPTcIWSIGFJJAXQNOTbdeGZRMRRMGTQUERJbB;";

RestConnectorMasterTable:
SQL SELECT 
	"Set-Cookie",
	"__KEY__response_header"
FROM JSON "_response_header" PK "__KEY__response_header";

[_response_header]:
LOAD		[Set-Cookie] AS [Set-Cookie]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY__response_header]);

//Extracts session cookie from the API response 
let vCookieRaw = Peek('Set-Cookie',0,'_response_header');  
let vCookie = TextBetween('$(vCookieRaw)','Secure,','Path=/',2); 

DROP TABLE RestConnectorMasterTable;

//NP GET Users

RestUserMasterTable: 
SQL SELECT   
"__KEY_data",
(SELECT   
"id",   
"email",
"__FK_items"  
FROM "items" FK "__FK_items") 
FROM JSON (wrap off) "data" PK "__KEY_data" 
WITH CONNECTION( URL "https://qlikserver3.domain.local:4993/api/v1/users",HTTPHEADER "cookie" "$(vCookie)" ); 
 
[users_items]: 
LOAD [id] AS [users_userId],  
[email] AS [users_userEmail] 
RESIDENT RestUserMasterTable 
WHERE NOT IsNull([__FK_items]) 
AND SubStringCount([email], 'usera@qlik.com') <> 0; 
 
//Extracts the userId of the desired NP User 
let vUserId = Peek('users_userId',0,'users_items'); 
 
DROP TABLE RestUserMasterTable; 

//NP create update user

if IsNull(vUserId) then  
//create user     
//LIB CONNECT TO 'NP Rest POST test connection (domain_administrator)'; 
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://qlikserver3.domain.local:4993/api/v1/login/ntlm;timeout=30;method=POST;autoDetectResponseType=true;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=1;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;queryHeaders=Origin%2https://qlikserver1.domain.local;PaginationType=None;allowresponseHeaders=1;allowHttpsOnly=false;XUserId=ETMYFSdNNLYSGXRLBDZaWUZNJbcIHZFMUbbEXDB;XPassword=WOGDZSUEQLYGXZdOPTcIWSIGFJJAXQNOTbdeGZRMRRMGTQUERRXB;";

    set vUserBody = '{"Username":"usera","Email":"usera@qlik.com","Password":"123","Enabled":"true","Folder":"","Subfolder":"","DomainAccount":"","Timezone":"Canada/Pacific","Locale":"en"}';   
	let vUserBody = replace(vUserBody,'"', chr(34)&chr(34)); 
    set vPostUserULR = 'https://qlikserver3.domain.local:4993/api/v1/users'; 
 
    RestNPPOSTandPUTTestTable:     
	SQL SELECT         
    "__KEY_data"     
	FROM JSON (wrap off) "data" PK "__KEY_data"     
	WITH CONNECTION( URL "$(vPostUserULR)", BODY "$(vUserBody)",         
	HTTPHEADER "Origin" "https://qlikserver1.domain.local",         
	HTTPHEADER "Content-Type" "application/json",        
	HTTPHEADER "cookie" "$(vCookie)"); 
 
    [post_and_put_items]:     
	LOAD [__KEY_data] AS [__KEY_data]     
	RESIDENT RestNPPOSTandPUTTestTable     
	WHERE NOT IsNull([__KEY_data]); 
 
    DROP TABLE 
    RestNPPOSTandPUTTestTable; 
/*	else  
	//update user
	//LIB CONNECT TO 'NP Rest POST test connection (domain_administrator)';          
	set vUserBody = '{"Username":"UserB","Email":"userb@qlik.com","Password":"123","Enabled":"true","Folder":"","Subfolder":"","DomainAccount":"","Timezone":"Canada/Pacific","Locale":"en"}';
	let vUserBody = replace(vUserBody,'"', chr(34)&chr(34)); 
 
    let vPostUserULR = 'https://qlikserver3.domain.local:4993/api/v1/users/'&'$(vUserId)'; 
 

    RestNPPOSTandPUTTestTable:     
    SQL SELECT      
    "__KEY_data"
	FROM JSON (wrap off) "data" PK "__KEY_data"     
	WITH CONNECTION( URL "$(vPostUserULR)", BODY "$(vUserBody)",         
	HTTPHEADER "Origin" "https://qlikserver1.domain.local",
    HTTPHEADER "Content-Type" "application/json",
    HTTPHEADER "X-HTTP-Method-Override" "PUT",         
	HTTPHEADER "cookie" "$(vCookie)"); 
 
    [post_and_put_items]:     
	LOAD [__KEY_data] AS [__KEY_data]     
	RESIDENT RestNPPOSTandPUTTestTable     
	WHERE NOT IsNull([__KEY_data]); 
 
    DROP TABLE RestNPPOSTandPUTTestTable; 
      */
   endif;

//GET the list of the NPrinting Apps that match the desired condition 
//LIB CONNECT TO 'NP Rest GET Connection Test (domain_administrator)'; 
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://QlikServer3:4993/api/v1/login/ntlm;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=1;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;PaginationType=None;allowresponseHeaders=1;allowHttpsOnly=false;XUserId=dMPYDSdNNLYSGXRLODcGXSZNTLZGHbNMSB;XPassword=SKUZeSUEQLYGXZdOPTcIWSIGFJJAXQNOTbdeGZRMRRMGTQUERJbB;";

RestAppsMasterTable: 
SQL SELECT   "__KEY_data",
  (SELECT    "id",
  "name",
  "__FK_items"  FROM "items" FK "__FK_items") 
  FROM JSON (wrap off) "data" PK "__KEY_data" WITH CONNECTION( URL "https://qlikserver3.domain.local:4993/api/v1/apps", HTTPHEADER "cookie" "$(vCookie)" ); 
 
[apps_items]: LOAD [id] AS [apps_appId],  [name] AS [apps_appName] RESIDENT RestAppsMasterTable WHERE NOT IsNull([__FK_items]) AND SubStringCount([name], 'QVWS') <> 0; 
 
//Extracts the appId of the desired NP App 
let vAppId = Peek('apps_appId',0,'apps_items'); 
 
DROP TABLE RestAppsMasterTable; 
    
    RestConnectionMasterTable: SQL SELECT   "__KEY_data",
    (SELECT    "id",
    "name",
    "appId",
    "__FK_items"
    FROM "items" FK "__FK_items")
    FROM JSON (wrap off) "data" PK "__KEY_data" WITH CONNECTION( URL "https://qlikserver3.domain.local:4993/api/v1/connections", HTTPHEADER "cookie" "$(vCookie)" );
    
    [connection_items]:
 LOAD [id] AS [connection_id],
 [name] AS [connection_name],
 [appId] AS [connection_appId] RESIDENT RestConnectionMasterTable 
 WHERE NOT IsNull([__FK_items]) AND [appId] = '$(vAppId)'; 
 


//Extracts the desired Connection ID 
let vConnectionId = Peek('connection_id',0,'connection_items'); 
 
//Compose the URL for the POST call that triggers a reload metadata 
let vReloadMetadataURL = 'https://qlikserver3.domain.local:4993/api/v1/connections/'&'$(vConnectionId)'&'/reload'; 
 
//Compose the URL for the GET call that checks the connection status 
let vConnectionStatusURL = 'https://qlikserver3.domain.local:4993/api/v1/connections/'&'$(vConnectionId)'; 

 
DROP TABLE RestConnectionMasterTable; 

/*
//Reload MetaData for "QVWS" app

//LIB CONNECT TO 'NP Rest POST test connection (domain_administrator)';  
 
RestNPReloadMetadataTable: 
SQL SELECT  "__KEY_data" FROM JSON (wrap off) "data" PK "__KEY_data" 
WITH CONNECTION( URL "$(vReloadMetadataURL)", HTTPHEADER "cookie" "$(vCookie)"); 
 
[metadata_items]: 
LOAD [__KEY_data] AS [__KEY_data] RESIDENT RestNPReloadMetadataTable 
WHERE NOT IsNull([__KEY_data]); 
 
DROP TABLE RestNPReloadMetadataTable; 
*/

//This is where we left off with Gianluca

//NP GET Tasks This sections contains the instructions needed to obtain the list of all the available NPrinting Publish Tasks. 
//We then selected only the Task related to the previosly extracted appID: 

//LIB CONNECT TO 'NP Rest GET Connection Test (domain_administrator)';
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://QlikServer3:4993/api/v1/login/ntlm;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=1;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;PaginationType=None;allowresponseHeaders=1;allowHttpsOnly=false;XUserId=dMPYDSdNNLYSGXRLODcGXSZNTLZGHbNMSB;XPassword=SKUZeSUEQLYGXZdOPTcIWSIGFJJAXQNOTbdeGZRMRRMGTQUERJbB;";


RestNPTasksMasterTable: 
SQL SELECT   
"__KEY_data",  
(SELECT
"id",
"name",
"enabled",
"appId",
"__FK_items"
FROM "items" FK "__FK_items")
FROM JSON (wrap off) "data" PK "__KEY_data" 
WITH CONNECTION( URL "https://qlikserver3.domain.local:4993/api/v1/tasks/",HTTPHEADER "cookie" "$(vCookie)" ); 

[number_task_items]: 
LOAD Count(id) AS [number_tasks_taskId]
RESIDENT RestNPTasksMasterTable 
WHERE NOT IsNull([__FK_items]) 
AND [appId] = '$(vAppId)' 
AND [enabled] = 'True';

[task_items]: 
LOAD [id] AS [tasks_taskId],  
[name] AS [tasks_taskName],  
[appId] AS [tasks_appId],
[enabled] AS [tasks_enabled] 
RESIDENT RestNPTasksMasterTable 
WHERE NOT IsNull([__FK_items]) 
AND [appId] = '$(vAppId)' 
AND [enabled] = 'True';

DROP TABLE RestNPTasksMasterTable;
let vNumberTaskId = Peek('number_tasks_taskId',0,'number_task_items');

  If vNumberTaskId > 0 AND vNumberTaskId <> 'NULL' then
	  for numIteraction=0 to vNumberTaskId-1
	
				If '$(vTaskId)' <> 'NULL' then
		      		let vTaskId = Peek('tasks_taskId',numIteraction,'task_items'); 
					let vPublshTaskURL = 'https://qlikserver3.domain.local:4993/api/v1/tasks/'&'$(vTaskId)'&'/executions'; 
					 
					//NP POST Task execution This section contains the instructions needed to trigger the desired NPrinting Publish Task: 
					 
					//LIB CONNECT TO 'NPrinting REST Login (POST) (qtsel_gpr)'; 
					//LIB CONNECT TO 'NP Rest POST test connection (domain_administrator)'; 
						CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://qlikserver3.domain.local:4993/api/v1/login/ntlm;timeout=30;method=POST;autoDetectResponseType=true;keyGenerationStrategy=0;authSchema=ntlm;skipServerCertificateValidation=1;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;queryHeaders=Origin%2https://qlikserver1.domain.local;PaginationType=None;allowresponseHeaders=1;allowHttpsOnly=false;XUserId=ETMYFSdNNLYSGXRLBDZaWUZNJbcIHZFMUbbEXDB;XPassword=WOGDZSUEQLYGXZdOPTcIWSIGFJJAXQNOTbdeGZRMRRMGTQUERRXB;";
						
						RestNPTaskTriggerTable: 
						SQL SELECT
						"__KEY_data" 
						FROM JSON (wrap off) "data" PK "__KEY_data" 
						WITH CONNECTION( URL "$(vPublshTaskURL)", HTTPHEADER "cookie" "$(vCookie)");
				End If
	   next
   End If
 
 
[_post_items]:
LOAD [__KEY_data] AS [__KEY_data] 
RESIDENT RestNPTaskTriggerTable 
WHERE NOT IsNull([__KEY_data]); 


DROP TABLE RestNPTaskTriggerTable;