//Reference current table
dt1 = Current Data Table();

//flag to output a bunch of status info to the log file
debug = 0;

//window box, all code is inside this box
nw = New Window( "Custom Column Transformation",

	//box to hold spacers on left and right side of data
	H List Box ( 
		
		//spacer on left side of window
		Spacer Box( Size( 12, 12) ),
	
		//box to hold all other parts of window
		V List Box(
			
			//heading and instructions
			Text Box( "Choose lag settings " ), 
			Spacer Box( Size( 12, 12 ) ),
			V List Box( Text Box( "Directions:" ||
								  "\!N1. Select Columns to transform in the data table." ||
								  "\!N2. Type a formula below, or select an example." ||
								  "\!N3. Select a phrase to be appended to each new column name." ||
								  "\!N4. Press OK." ||
								  "\!N5. Repeat 2-4 as necessary." ||
								  "\!N6. Press Close/Cancel to exit."), 
			Spacer Box( Size( 12, 12 ) ) ),
			
			//radio buttons with examples, when selected the text in the formula edit box is modified
			panelbox("Example Equations",
				rb1 = radio box({"Lag", "Change", "Date Dep", "Column Only"},
					one_rb = rb1 << get selected;
					if (one_rb == "Lag",
						FormulaIn << set text("Lag(<column>,1)");,
						one_rb == "Change",
						FormulaIn << set text("<column> - Lag(<column>,1)");,
						one_rb == "Date Dep",
						FormulaIn << set text("If( As Column( \!"Date\!" ) < Date DMY( 1, 1, 2016 )," ||
											  "\!N\!t<column> / 10,\!N\!t<column>\!N)");,
						FormulaIn << set text("<column>");,
					);
				),
			),
			
			//Formula edit box with instructions
			Spacer Box( Size( 6, 6 ) ),
			V List Box( Text Box( "FORMULA\!NCreate a new column in the data table and then copy/paste the formula from " ||
								  "the formula editor.  Then use <column> up to 10 times to indicate where column names " ||
								  "should be substituted. Reference other columns using As Column(\!"Column Name\!")." ),
			Spacer Box( Size( 12, 12 ) ), 
			V List Box( 
				FormulaIn = Text Edit Box(
					"Lag(<column>,1)",
					<<Justify Text( Left ),
					<<Set Width( 400 ),
					<<setNlines( 5 )
				)
			),
					   
			//Description edit box with instructions
			Spacer Box( Size( 6, 6 ) ),
			V List Box ( Text Box( "DESCRIPTION\!NText appended to each new column name:" ) ),
			Spacer Box( Size( 12, 12 ) ) ),
			V List Box ( DescIn = Text Edit Box( " TRANSFORMED",
							<<Justify Text( Left ),
							<<Set Width( 400 ),
							<<setNlines( 1 )
						 )
					   ),
			Spacer Box( Size( 25, 25 ) ),
			
			//Box to hold command buttons
			H List Box(
			
				//Button to create new columns
				Button Box( "OK",
				
					//get list of colums selected in the data table
					ColumnList = dt1 << Get Selected Columns();
					
					//TODO: loop through this section for multiple input columns, list of <column1>, <column2>, etc.
					
					//variable to hold text string to be replaced with column names
					ReplaceTarget = "<column>";
					
					//Get formula from text box
					FormulaInput = FormulaIn << Get Text;
					
					//get column description from text box
					DescInput = DescIn << Get Text;
					
					//output debug info
					if (debug == 1,Show( FormulaInput );Show( DescInput );Show( ColumnList ););
					
					//get number of selected columns
					n = N Items( ColumnList );
					
					//for each column
					For( i = 1, i <= n, i++,
					
						//get data type of column
						ColumnDataType = Column( ColumnList[i] ) << Get Data Type;
						
						//output debug info
						if (debug == 1,Show( columnList[i] );Show( ColumnDataType ););
						
						//create new variable for modified formula
						FormulaComplete = FormulaInput;
						
						//TODO: stop itterating when string no longer found or after a huge number of cycles
						
						//replace column reference for up to 10 occurances (change this for large formulas)
						For( j = 1, j <= 10, j++,
						
							//output debug info
							if (debug == 1,Show( FormulaComplete );Show( Contains( FormulaComplete, ReplaceTarget ) );
								Show( Length( ReplaceTarget ) ););
								
							//if the formula contains the target string
							If( Contains( FormulaComplete, ReplaceTarget ) > 0,
								
								//replace the first occurance of the target string
								FormulaComplete = Eval(
									Munger(
										FormulaComplete,
										Contains( FormulaComplete, ReplaceTarget ),
										Length( ReplaceTarget ),
										"As Column(\!"" || Char( ColumnList[i] ) || "\!")"
									)
								)
							); //end if formula contains reference string
						); //loop for each occurance of the reference string
						
						//output debug info
						if (debug == 1,Show( FormulaComplete ););
						
						//create string containing expression that will create the new column reference
						newColExpr = Expr(
							dt1 << New Column( Char( ColumnList[i] ) || Char( DescInput ),
								Expr( ColumnDataType ),
								Formula( Eval( Parse( FormulaComplete ) ) )
							)
						);
						
						//output debug info
						if (debug == 1,Show( Eval Expr( newColExpr ) ););
						
						//Try creating the new column reference
						Try( Eval( Eval Expr( newColExpr ) ) );
						
						//TODO: Check if new column was created and it has a formula, inform user
						
						//output debug info
						if( debug == 1, Show( "done" ););
						
					); //loop each selected column
				), //end of 'OK' command button
				
				//button to close the window
				Button Box( "Close/Cancel",
					Show( "Closed" );
					nw << Close Window;
				)
				
			), //end of box to hold command buttons
			
			//spacer on bottom of window
			Spacer Box( Size( 12, 12 ) )
		), //end of vertical box with all window content except left and right spacers
		
		//spacer in right side of window
		Spacer Box( Size( 12, 12 ) )
		
	) //end of horizontal box for spacers on left and right side
	
); //end of window box