if (not (group exist "0001_folder1_folder2_folder3")) {
	group add "0001_folder1_folder2_folder3";
};

script merge "folder1.folder2.folder3.step_1_source_connect.exe" true 5
  (param
  )
  "Connect to the source"
{

	# CONFIGURATION ;
	-> "[CONF_NAME_OF_THE_FLOW]" "name_of_the_flow";

	# INITIALIZATION ;
	-> "[FLOW_PID]" [PID];
	
	# HANDLE ;
	try {
	
		# Flow initialization;
		stack flow_init [FLOW_PID] [CONF_NAME_OF_THE_FLOW] "{}";
		stack flow_step [FLOW_PID] 1 "source_connect...";

		# Local connection: don't use step 1;
		log write "Source: Local connection" OK null null;
		
		#Step 1 is valid;
		stack flow_step [FLOW_PID] 1 "source_connect_ok";
		
		include "folder1.folder2.folder3.step_2_extract.exe";
	
	} {

		#Step 1 is not valid;
		stack flow_step [FLOW_PID] 1 "source_connect_KO";
		
		# Give the error to the stack and stop the process;
		exception (1) ([global_err]);
	
	} "[global_err]";
	
} "Return nothing";
if (not (group is granted script "folder1.folder2.folder3.step_1_source_connect.exe" "0001_folder1_folder2_folder3")) {
	group grant script "folder1.folder2.folder3.step_1_source_connect.exe" "0001_folder1_folder2_folder3";
};

script merge "folder1.folder2.folder3.step_2_extract.exe" true 1
  (param
  )
  "Extract files from the source"
{
	
	# HANDLE ;
	try {

		# Mark the flow as step 2;
		stack flow_step [FLOW_PID] 2 "extract...";

		# Create the local directory PID;
		file mkdir (concat "home/" [FLOW_PID]);
		
		#Step 2 is valid;
		stack flow_step [FLOW_PID] 2 "extract_ok";

		stack (date now) "folder1.folder2.folder3.step_3_transform.exe" "[FLOW_PID]" [FLOW_PID] "[filename]" "";
	
	} {

		#Step 2 is not valid;
		stack flow_step [FLOW_PID] 2 "extract_KO";
		
		# Give the error to the stack and stop the process;
		exception (1) ([global_err]);
	
	} "[global_err]";

} "Return nothing";
if (not (group is granted script "folder1.folder2.folder3.step_2_extract.exe" "0001_folder1_folder2_folder3")) {
	group grant script "folder1.folder2.folder3.step_2_extract.exe" "0001_folder1_folder2_folder3";
};

script merge "folder1.folder2.folder3.step_3_transform.exe" true 1
  (param
  	(var "[FLOW_PID]" {true} "The flow PID" is_null:true is_empty:true "1")
  	(var "[filename]" {true} "The filename" is_null:true is_empty:true "file.csv")
  )
  "Transform the file"
{
	
	# HANDLE ;
	try {

		# Mark the flow as step 3;
		stack flow_step [FLOW_PID] 3 "transform...";

		# SQL: don't use step 3;
		log write "SQL: don't use step 3" OK null null;

		#Step 3 is valid;
		stack flow_step [FLOW_PID] 3 "transform_ok";

		stack (date now) "folder1.folder2.folder3.step_4_destination_connect.exe" "[FLOW_PID]" [FLOW_PID] "[filename]" [filename];
		
	} {

		#Step 5 is not valid;
		stack flow_step [FLOW_PID] 5 "transform_and_load_KO";
		
		# Give the error to the stack and stop the process;
		exception (1) ([global_err]);
	
	} "[global_err]";

} "Return nothing";
if (not (group is granted script "folder1.folder2.folder3.step_3_transform.exe" "0001_folder1_folder2_folder3")) {
	group grant script "folder1.folder2.folder3.step_3_transform.exe" "0001_folder1_folder2_folder3";
};

script merge "folder1.folder2.folder3.step_4_destination_connect.exe" true 5
  (param
  	(var "[FLOW_PID]" {true} "The flow PID" is_null:true is_empty:true "1")
  	(var "[filename]" {true} "The filename" is_null:true is_empty:true "file.xxx")
  )
  "Connect to the destination"
{

	# CONFIGURATION ;
	-> "[CONF_DESTINATION_CM_SQL]" "MENTDB";
	
	# HANDLE ;
	try {
	
		# Flow initialization;
		stack flow_step [FLOW_PID] 4 "destination_connect...";

		sql connect "destination_sess" {cm get [CONF_DESTINATION_CM_SQL];};
		sql auto_commit "destination_sess" false;
		
		#Step 4 is valid;
		stack flow_step [FLOW_PID] 4 "destination_connect_ok";
		
		include "folder1.folder2.folder3.step_5_load.exe";
	
	} {

		try {sql disconnect "destination_sess";} {} "[err]";

		#Step 4 is not valid;
		stack flow_step [FLOW_PID] 4 "destination_connect_KO";
		
		# Give the error to the stack and stop the process;
		exception (1) ([global_err]);
	
	} "[global_err]";
	
} "Return nothing";
if (not (group is granted script "folder1.folder2.folder3.step_4_destination_connect.exe" "0001_folder1_folder2_folder3")) {
	group grant script "folder1.folder2.folder3.step_4_destination_connect.exe" "0001_folder1_folder2_folder3";
};

script merge "folder1.folder2.folder3.step_5_load.exe" true 1
  (param
  )
  "Load file to the destination"
{

	# CONFIGURATION ;
	-> "[CONF_DESTINATION_CM_SQL]" "MENTDB";
	-> "[CONF_DESTINATION_COMMIT_BY_NB_ROW]" 5000;
	
	# HANDLE ;
	try {

		# Mark the flow as step 5;
		stack flow_step [FLOW_PID] 5 "load...";

		log write (concat "Send data to SQL Database from SOAP API...") OK null null;
		
		xml load "flow_source" (soap https "https://www.dneonline.com/calculator.asmx" "{}" "" "text/xml; charset=utf-8" (concat "<soap:Envelope xmlns:soap=\"http://www.w3.org/2003/05/soap-envelope\" xmlns:tem=\"http://tempuri.org/\">
		   <soap:Header/>
		   <soap:Body>
		      <tem:Add>
		         <tem:intA>3</tem:intA>
		         <tem:intB>4</tem:intB>
		      </tem:Add>
		   </soap:Body>
		</soap:Envelope>"));
		
		# BEGIN METHOD 1;
		-> "[T_A]" (xml select text "flow_source" "/data/A");
		-> "[T_B]" (xml select text "flow_source" "/data/B");
		-> "[T_C]" (xml select text "flow_source" "/data/C");

		sql dml "destination_sess" (concat 
			"INSERT INTO `test` (
				`A`,
				`B`,
				`C`
			) VALUES (
				" (sql encode [T_A]) " ,
				" (sql encode [T_B]) " ,
				" (sql encode [T_C]) "
			);"
		);
		# END METHOD 1;
		
		# BEGIN METHOD 2;
		-> "[index]" 0;
		-> "[index_all]" 0;
		-> "[nb_rows]" (xml count "flow_source" "/data/rows/item");
		for (-> "[i_xml]" 1) (<= [i_xml] [nb_rows]) (++ "[i_xml]") {
		
			-> "[T_A]" (xml select text "flow_source" (concat "/data/rows/item[" [i_xml] "]/A"));
			-> "[T_B]" (xml select text "flow_source" (concat "/data/rows/item[" [i_xml] "]/B"));
			-> "[T_C]" (xml select text "flow_source" (concat "/data/rows/item[" [i_xml] "]/C"));

			sql dml "destination_sess" (concat 
				"INSERT INTO `test` (
					`A`,
					`B`,
					`C`
				) VALUES (
					" (sql encode [T_A]) " ,
					" (sql encode [T_B]) " ,
					" (sql encode [T_C]) "
				);"
			);

			++ "[index]";
			++ "[index_all]";

			if (== [index] [CONF_DESTINATION_COMMIT_BY_NB_ROW]) {
				-> "[index]" 0;
				sql commit "destination_sess";
				log write (concat "Current row >>> " [index_all]) OK null null;
			};
		
		};
		# END METHOD 2;

		sql commit "destination_sess";

		sql disconnect "destination_sess";
		
		log write (concat "Integrated.") OK null null;
		
		#Step 5 is valid;
		stack flow_step [FLOW_PID] 5 "load_ok";
		
	} {

		try {sql rollback "destination_sess";} {} "[err]";
		try {sql disconnect "destination_sess";} {} "[err]";

		#Step 5 is not valid;
		stack flow_step [FLOW_PID] 5 "load_KO";
		
		# Give the error to the stack and stop the process;
		exception (1) ([global_err]);
	
	} "[global_err]";

} "Return nothing";
if (not (group is granted script "folder1.folder2.folder3.step_5_load.exe" "0001_folder1_folder2_folder3")) {
	group grant script "folder1.folder2.folder3.step_5_load.exe" "0001_folder1_folder2_folder3";
};