///$tab Main
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='#,##0.00;-#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';


///$tab SmartCall_Init
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Generic set up
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SET HidePrefix = '_';
LET vDocumentName = Documentname();
LET vCounter = 0; // Each tab will increment this variable

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Variables for calculation of the total elapsed time (see before the EXIT)
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LET vScriptStartTimestampNumeric = Num(Now());
LET vScriptStartTimestamp = Timestamp($(vScriptStartTimestampNumeric));

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Initialization of the Metadata table. Used for concatenation.
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Tables_Metadata:
LOAD
'' AS TM_TableName
AutoGenerate 0;


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// This Tab is the place where everything happens
// The INLINE table called SubNameList contains the list of the existing SUBROUTINES.
// The loop runs for each record in this table.
// Each round of the loop compares the current SUBROUTINE XXX with the smart variable string.
// If _XXX_ is contained in the string, then the SUBROUTINE XXX will be called, otherwise nothing will happen
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// List of SUBs. In general it should be all uncommented: if they are not in the vSmartVar, they will be ignored in any case
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SubNameList:
LOAD 
SubName AS _KEY_SUB,
Rowno() AS _SubNameListSort,
* 
INLINE [
SubName, SubNote
INPUT_A
INPUT_B
INPUT_C
]
WHERE LEFT(SubName,2)<>'//'
;

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Smart Variables:
//
// We have three smart variables:
//	vSmartVarInternal	- this gets reset by a trigger to '*' on opening the qvd.
//	vSmartVarExternal	- this can be passed as a command line setting with vSmartVarExternal. It is reset to '*' after reloading so that it doesn't get saved as something else.
//	vSmartVar			- this is generated from the two above. If vSmartVarInternal  = '*', take vSmartVarExternal else, take vSmartVarInternal.
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If '$(vSmartVarInternal)' = '*' THEN
	set vSmartVar = $(vSmartVarExternal); //Use external.
ELSE 
	set vSmartVar = $(vSmartVarInternal);	//Use internal.
ENDIF

IF '$(vSmartVar)' = '*' THEN //Convert * to concatenated SubNameList
	TMP_Concat:
	Load '_' & Concat(SubName,'_') & '_' as TMP_Concat Resident SubNameList;
	let vSmartVar = peek('TMP_Concat',0,'TMP_Concat'); //Default to all.
ENDIF

///$tab SUB_MetaData
// Tab Start

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// SUB TablesMetadata
// It runs at the end of each extraction, capturing the metadata info and adding it to one record of the metadata table
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SUB TablesMetadata

LET vExtractCurrentEnd = num(Now()); 													// The vExtractCurrentStart must be declared at the beginning of the new tab
LET vExtractCurrentElapsedTime = ($(vExtractCurrentEnd)-$(vExtractCurrentStart))*86400; // Elapsed time in seconds
LET vNoOfRows = NoOfRows('$(vTable)'); 													// Based on the table in memory. In general it can be smaller than the one in the DB
LET vNoOfFields = NoOfFields('$(vTable)'); 												// Based on the table in memory. In general it can be smaller than the one in the DB

Concatenate (Tables_Metadata) // Requires the existance of a table called Tables_Metadata
LOAD
'$(vCurrentSub)' 								AS _KEY_SUB,		// Used as a key, not visible in front end
$(vCounter)										AS TM_SubID,
'$(vCurrentSub)'								AS TM_SubName,
'$(vCounter)'&' '&'$(vCurrentSub)'				AS TM_Sub,
'$(vTable)' 									AS TM_TableName, 	// Table Name
Date($(vExtractCurrentStart))					AS TM_Date,			//May want to have start and end date for very long extracts...
Time($(vExtractCurrentStart)) 					AS TM_StartTime,
Time($(vExtractCurrentEnd)) 					AS TM_EndTime,
Time($(vExtractCurrentElapsedTime)/86400 ) 		AS TM_ElapsedTime, 
Num('$(vExtractCurrentElapsedTime)')			AS TM_ElapsedSeconds, // Easy to sum in the chart but not easy to read when the number is big
Num('$(vNoOfRows)') 							AS TM_NoOfRows,
Num('$(vNoOfFields)') 							AS TM_NoOfColumns,
'ALL_TABLE_METADATA'							AS ALL_TABLE_METADATA
AutoGenerate 1;

END SUB

// Tab End

///$tab <

///$tab INPUT_A
SUB INPUT_A

LET vTable = 'Test1';
LET vCounter = $(vCounter)+1;
TRACE $(vCounter) - Running tab $(vTable) of document $(vDocumentName) with vSmartVar $(vSmartVar);
LET vExtractCurrentStart = num(Now());

/*
Notes:
*/

Trace #### TAB A ### ;
Test1:
Load
	'a' as Cat,
	1	as Bat,
	Today() as Date
AutoGenerate 1;

SLEEP RAND()*5000;

CALL TablesMetadata
END SUB

///$tab INPUT_B
SUB INPUT_B

LET vTable = 'Test2';
LET vCounter = $(vCounter)+1;
TRACE $(vCounter) - Running tab $(vTable) of document $(vDocumentName) with vSmartVar $(vSmartVar);
LET vExtractCurrentStart = num(Now());

/*
Notes:
*/

Test2:
Load
	'a' as BCat,
	1	as BBat,
	Today() as BDate
AutoGenerate 1;

SLEEP RAND()*5000;

CALL TablesMetadata
END SUB

///$tab INPUT_C
SUB INPUT_C

LET vTable = 'NONE';
LET vCounter = $(vCounter)+1;
TRACE $(vCounter) - Running tab $(vTable) of document $(vDocumentName) with vSmartVar $(vSmartVar);
LET vExtractCurrentStart = num(Now());

/*
Notes:
*/

CALL TablesMetadata
END SUB
///$tab >

///$tab SmartCall_Run
// Tab Start    

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Loop initialization
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


LET vNumberOfRowsOfINLINE = NoOfRows('SubNameList');
LET vNumberOfLoops=$(vNumberOfRowsOfINLINE);
FOR vCurrentLoop = 1 to $(vNumberOfLoops)
	LET vCurrentRecord = $(vCurrentLoop)-1;  //The first record of a table by convention is 0, not 1
	LET vCurrentSub = Peek('SubName',$(vCurrentRecord),'SubNameList');  //Takes one by one the records of the column "SubName" of the table "SubNameList"
	TRACE Execution of loop $(vCurrentLoop) of $(vNumberOfLoops)  Launching Sub $(vCurrentSub); //Gives in the dialogue box a feedback of what's happening

	//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	// ------------------------------------> LOOP <-----------------------------------
	//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	IF SubStringCount(UPPER('$(vSmartVar)'),'_'&UPPER('$(vCurrentSub)')&'_') THEN // NOTE: the separator is needed, because otherwise the string FTM would also execute FT 
	Call $(vCurrentSub);
	ELSE 
	TRACE The SUB called "$(vCurrentSub)" is not in the variable, therefore it will not be executed;
	END IF

NEXT vCurrentLoop;


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Cleanup of variables that are not needed in the front end
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LET vCurrentLoop = ;
LET vNumberOfRowsOfINLINE = ;
LET vNumberOfLoops = ;
LET vCurrentRecord = ;
LET vCurrentSub = ;

// Tab End
