
import jet.api.*;
import java.io.*;

/**
*This class is used to test the CatalogAPI.
*It will create a catalog which contains similar objects as JReport\Designer\Demo\Reports\TutorialReports\JinfonetGourmetJava.cat
*  Note: Some parts are commented out because they require external resources
*
*Requires this HSQLDB database be available: c:\\JReport\\Designer\\Demo\\db\\DemoDB
*   be sure hsqldb.jar is in your classpath
*
*Usage: TestCatalogAPI -path=<path> -catalog=<catalog> [-log=logfile]
*/

public class TestCatalogAPI
{
	jet.api.Designer desg = null;
	jet.api.CatalogAPI cat = null;
	public static int index = 0;
	int i=0;
	/**
	* Constructor creates a catalog file
	*/
	public TestCatalogAPI(String path, String catName)
	{	
		
		File catalogFolder = new File(path);
		if(!catalogFolder.exists())
		{
			catalogFolder.mkdirs();
		}
		try
		{
			// Use the license Key for Designer API or Server Designer API depending on classpath setting
			DesignerUserInfo userInfo=new DesignerUserInfo("Jinfonet", "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");
			desg = new Designer(path, catName, userInfo);
			cat = desg.getCatalogAPI();
			desg.setLog(new FileOutputStream(path+"\\Design.log"), "8859_1"); //default Designer Message
			cat.setLog(new FileOutputStream(path+"\\Catalog.log"), "8859_1"); //default Catalog Message

			ConnectionInfo info = new ConnectionInfo("sa", "");
			// Change the location of the HSQLDB database as needed
			String con = cat.insert("", false,"Jinfonet demo", "demo db", 
					"jdbc:hsqldb:C:\\JReport\\Designer\\Demo\\db\\DemoDB", "org.hsqldb.jdbcDriver", info);

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
			
	}
	public void setlog(String logName)
	{
		try
		{
			if( logName != null)
			{
				desg.setLog(new FileOutputStream(logName), "8859_1"); //Designer Message
				logName = logName.substring(0, logName.length() - 5) + "_cat"+".log";
				cat.setLog(new FileOutputStream(logName), "8859_1");//Catalog Message
			}
		}
		catch(Exception e)
		{
		}
	}
	public String[] getQueries()
	{
		return cat.getQueries();
	}
	/**
	* Gets "QBE" information in the query
	* @param		qryName	the selected table in the query
	* @return	QBE info array note:String[i][0] = table Name, String[i][1] = column Name, String[i][j](j>1) = condition expression of this column
	*/
	public String[][] PgetQBEInfo(String queryName)
	{
			return cat.getQBEInfo(queryName);
	}
	/**
	* Gets "And conditions" information in the query
	* @param		qryName the selected table in the query
	* @return	condition info array. note:String[i][0] = "Expression1", String[i][1] = "Operator", String[i][2] = "Expression2" String[i][3] = "Logic"
	*/
	public String[][] PgetAndConditions(String queryName)
	{
		return cat.getAndConditions(queryName);
	}
	/**
	* Gets "joins" information in the query
	* @param		qryName	the selected table in the query
	* @return	join info array. note:String[i][0] = Table "From" Name, String[i][1] = Column "From" Name, String[i][2] = Table "To" Name, String[i][3] = Column "To" Name String[i][4] = operator
	*/
	public String[][] PgetJoins(String queryName)
	{
		return cat.getJoins(queryName);
	}
	/**
	* Gets column names in the query
	* @param		qryName	the selected table in the query
	* @param		allColumns	select all columns in selected tables
	* @return	table name and column mapping names
	*/
	public QueryTableInfo getColumns(java.lang.String queryName, boolean allColumns)
	{
		return cat.getColumns(queryName , allColumns);
	}
	/**
	* Deletes an existing catalog file
	* @param		FileName	 the catalog file name
	*/
	public void deleteCatalog(String FileName)
	{
		System.out.println("** delete catalog : " + FileName);
		File f = new File(FileName + ".cat");
		f.delete();
	}
	/**
	* Deletes an object by handles
	* @param		handles	catalog object's handles
	* @return  true if deletes successfully, else returns false
	*/
	public boolean delete(java.lang.String handle)
	{
		System.out.println("** delete handle is " + handle);
		return cat.delete(handle); 
	}
	/**
	* Deletes joins in the query
	* @param		qryName the selected table to be deleted in the query
	* @Param		tablename	the name of the table to be deleted
	* @return  true if deletes successfully, else returns false
	*/
	public boolean delete(java.lang.String qryName, java.lang.String tablename)
	{
		System.out.println("** delete table name " + tablename);
		return cat.delete(qryName, tablename); 
	}
	/**
	* Deletes a selected fields in the query
	* @param		qryName	the selected table to be deleted in the query
	* @Param		tablename	the name of the table to be deleted
	* @Param		columnname	the column name of the deleted field
	* @return	true if deletes successfully, else returns false
	*/
	public boolean delete(java.lang.String qryName, java.lang.String tablename,
		java.lang.String columnname)
	{
		System.out.println("** delete field " + columnname);
		return cat.delete(qryName,tablename, columnname); 
	}
	/**
	* Deletes a QBE condition in the query
	* @param		qryName	the selected table to be deleted in the query
	* @Param		tablename	the name of the table to be deleted
	* @Param		columnname	the column name of the deleted field
	* @param		expression	the expression of the QBE condition
	* @return	true if deletes successfully, else returns false
	*/
	public boolean delete(java.lang.String qryName, java.lang.String tablename,
		java.lang.String columnname, java.lang.String expression)
	{
		System.out.println("** delete QBE " + tablename + "." + columnname+expression);
		return cat.delete(qryName, tablename, columnname, expression); 
	}
	/**
	* Deletes a where condition in the query
	* @param		qryName	the selected table to be deleted in the query
	* @param		sExpression1	the first expression of the where condition to be deleted
	* @Param		sOperator	the operator of the where condition to be deleted
	* @Param		sExpression2	the second expression of the where condition to be deleted
	* @param		sLogic	the logic string of the where condition to be deleted
	* @return	true if deletes successfully, else returns false
	*/
	public boolean delete(java.lang.String qryName, java.lang.String sExpression1,
		java.lang.String sOperator, java.lang.String sExpression2,
		java.lang.String sLogic)
	{
		System.out.println("** delete And condition : " + sExpression1 + sOperator 
			+ sExpression2);
		return cat.delete(qryName, sExpression1, sOperator, sExpression2, sLogic);
	}
	/**
	* Deletes a where condition in the query
	* @param		qryName	the selected table to be deleted in the query
	* @param		tableFrom	the name of the table from which the join links
	* @Param		columnFrom	the name of the column from which the join links
	* @Param		operator	the name of the table to which the join links
	* @param		tableTo	the name of the table to which the join links
	* @param		columnTo	the operator of the join to be deleted
	* @return	true if deletes successfully, else returns false
	*/
	public boolean delete(java.lang.String qryName, java.lang.String tableFrom,
		java.lang.String columnFrom, java.lang.String operator,
		java.lang.String tableTo, java.lang.String columnTo)
	{
		System.out.println("** delete And condition : " + qryName + tableFrom 
			+ columnFrom + operator + tableTo + columnTo);
		return cat.delete(qryName, tableFrom, columnFrom, operator, tableTo, columnTo);
	}
	/**
	* Adds a table in the catalog
	* @param		TableName	table name
	* @return	table handles
	*/
	public String addTable(String datasource, String catalog, String schema, String TableName, String tableMappingName)
	{
		String[] table = cat.getTables();
		String s = null;
		for (int i=0; i < table.length; i++)
		{
			if ( TableName.compareTo(table[i]) == 0 )
			{
				s=table[i];
			}
		}
		if (s == null)
		{
			String tablehandles = cat.insert(datasource, tableMappingName, catalog, schema, TableName, CatalogAPI.TABLE, null);
			return tablehandles;
		}
		else
		{
			System.out.println("Current table already has a " + TableName + "TABLE");
		}
		return null;
	}
	/**
	* Adds an Integer parameter in the catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of the parameter
	* @param		defaultValue	the default value
	* @param		min	the min value
	* @param		min	the max value
	* @return	parameter handles
	*/
	public String addParatemerINTEGER(String Pname, String Text, 
		String defultValue, String min,String max)
	{
		String[] name = cat.getParameter();
		String s = null;
		for (int i = 0; i < name.length; i++)
		{
			if ( Pname.compareTo(name[i]) == 0 )
			{
				s=name[i];
			}
		}
		if (s == null)
		{
			String ParameterHandles = cat.insert(Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo(Pname, "PARAMETER"+(index+1), 
				Text, CatalogAPI.INTEGER);
			if ( defultValue != null)
			{
				pinfo.setDefaultValue(defultValue);
			}
			if (min != null)
			{
				pinfo.setLimit(min, ParameterInfo.MIN);
			}
			if (max != null)
			{
				pinfo.setLimit(max, ParameterInfo.MAX);
			}
			cat.set(ParameterHandles, pinfo);
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.INTEGER,defultValue); Raplace insert(String name ,String Type)
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  " + Pname + " PARAMETER");
		}
		return null;
	}
	/**
	* Adds a Boolean parameter in the Catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of the parameter
	* @param		defaultValue	the default value
	* @return	parameter handles
	*/
	public String addParatemerBOOLEAN(String Pname, String Text, String defultValue)
	{
		String[] name = cat.getParameter();
		String s = null;
		for (int i = 0; i<name.length; i++)
		{
			if ( Pname.compareTo(name[i]) == 0 )
			{
				s=name[i];
			}
		}
		if ( s == null )
		{
			String ParameterHandles = cat.insert(Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo(Pname, "PARAMETER" + (index + 1), Text,
					CatalogAPI.BOOLEAN);
			if (defultValue != null)
			{
				pinfo.setDefaultValue(defultValue);
			}
			cat.set( ParameterHandles, pinfo);
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.BOOLEAN,defultValue);
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  "+Pname+" PARAMETER");
		}
		return null;
	}
	/**
	* Adds a Currency parameter in the Catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of  the parameter
	* @param		defaultValue	the default Value
	* @param		min	the min Value
	* @param		min	the max Value
	* @return	parameter handles
	*/
	public String addParatemerCURRENCY(String Pname, String Text, String defultValue, 
		String min, String max)
	{
		String[] name = cat.getParameter();
		String s = null;
		for (int i = 0; i < name.length; i++)
		{
			if (Pname.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String ParameterHandles = cat.insert(Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo(Pname, "PARAMETER" + (index+1), 
				Text, CatalogAPI.CURRENCY);
			if ( defultValue != null )
			{
				pinfo.setDefaultValue(defultValue);
			}
			if ( min != null)
			{
				pinfo.setLimit(min, ParameterInfo.MIN);
			}
			if ( max != null)
			{
				pinfo.setLimit(max,ParameterInfo.MAX);
			}
			cat.set( ParameterHandles, pinfo);
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.CURRENCY,defultValue); Replace(String name, int Type)
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  " + Pname + " PARAMETER");
		}
		return null;
	}
	/**
	* Adds a Date parameter in the Catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of the parameter
	* @param		defaultValue	the default value
	* @param		min	the Min value
	* @param		max	the Max value
	* @return	parameter handles
	*/
	public String addParatemerDATE(String Pname, String Text, String defultValue, 
		String min, String max)
	{
		String[] name=cat.getParameter();
		String s=null;
		for ( int i = 0; i <name.length; i++ )
		{
			if ( Pname.compareTo(name[i]) == 0)
			{
				s=name[i];
			}
		}
		if ( s == null)
		{
			String ParameterHandles = cat.insert( Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo( Pname, "PARAMETER" + (index+1), 
				Text, CatalogAPI.DATE);
			if ( defultValue != null)
			{
				pinfo.setDefaultValue(defultValue);
			}
			if ( min != null)
			{
				pinfo.setLimit(min, ParameterInfo.MIN);
			}
			if ( max != null)
			{
				pinfo.setLimit( max, ParameterInfo.MAX);
			}
			cat.set( ParameterHandles, pinfo);
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.DATE,defultValue); Replaced by insert(String name , int Type)
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  "+Pname+" PARAMETER");
		}
		return null;
	}
	/**
	* Adds a DateTime parameter in the Catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of the parameter
	* @param		defaultValue	the default value
	* @param		min	the Min value
	* @param		max	the Max value
	* @return	parameter handles
	*/
	public String addParatemerDATETIME(String Pname, String Text, String defultValue, 
		String min, String max)
	{
		String[] name = cat.getParameter();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( Pname.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null)
		{
			String ParameterHandles = cat.insert( Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo( Pname, "PARAMETER" + (index + 1), 
				Text, CatalogAPI.DATETIME);
			if ( defultValue != null)
			{
				pinfo.setDefaultValue( defultValue);
			}
			if( min != null)
			{
				pinfo.setLimit( min, ParameterInfo.MIN);
			}
			if ( max != null)
			{
				pinfo.setLimit( max, ParameterInfo.MAX);
			}
			cat.set( ParameterHandles, pinfo);
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.DATETIME,defultValue); Replaced by insert(String name , int Type)
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  "+Pname+" PARAMETER");
		}
		return null;
	}
	/**
	* Adds a Number parameter in the Catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of the parameter
	* @param		defaultValue	the default value
	* @param		min	the min value
	* @param		max	the max value
	* @return	parameter handles
	*/
	public String addParatemerNUMBER( String Pname, String Text, String defultValue, 
		String min, String max)
	{
		String[] name = cat.getParameter();
		String s = null;
		for ( int i=0; i < name.length; i++)
		{
			if ( Pname.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String ParameterHandles = cat.insert( Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo( Pname, "PARAMETER" + (index + 1), 
				Text, CatalogAPI.NUMBER);
			if ( defultValue != null)
			{
				pinfo.setDefaultValue(defultValue);
			}
			if ( min != null)
			{
				pinfo.setLimit( min, ParameterInfo.MIN);
			}
			if ( max != null)
			{
				pinfo.setLimit( max, ParameterInfo.MAX);
			}
			cat.set( ParameterHandles, pinfo);
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.NUMBER,defultValue); Replace(String name , int Type);
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  " + Pname + " PARAMETER");
		}
		return null;
	}
	/**
	* Adds a String parameter in the Catalog
	* @param		Pname	the parameter name
	* @param		Text	the description of the parameter
	* @param		defaultValue	the default value
	* @return	parameter handles
	*/
	public String addParatemerSTRING( String Pname, String Text, String defultValue)
	{
		String[] name = cat.getParameter();
		String s = null;
		for (int i = 0; i < name.length; i++)
		{
			if ( Pname.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String ParameterHandles = cat.insert( Pname, CatalogAPI.PARAMETER);
			ParameterInfo pinfo = new ParameterInfo( Pname, "PARAMETER" + (index + 1),
				Text, CatalogAPI.STRING);
			if ( defultValue != null )
			{
				pinfo.setDefaultValue( defultValue );
			}
			cat.set( ParameterHandles, pinfo );
			//String ParameterHandles=cat.insert(Pname,"intpa",Text,cat.STRING,defultValue); Replace by insert(String name,int type);
			return ParameterHandles;
		}
		else
		{
			System.out.println("Current table already has a  " + Pname + " PARAMETER");
		}
		return null;
	}
	/**
	* Adds a formula to the database according to the specified name
	* @param		formullaName	the formula name
	* @param		formula	the expression
	* @return	the handle of the newly inserted formula. If fails, a null value will be returned.
	*/
	public String addFormula( String formulaName, String formula)
	{
		String[] name = cat.getFormulae();
		String s = null;
		for ( int i=0; i < name.length; i++)
		{
			if ( formulaName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			//String formulahandles=cat.insert(formulaName,cat.FORMULA);
			//FormulaInfo formulainfo=new FormulaInfo(formulaName,"Formula"+(index+1),formula);
			//cat.set(formulahandles,formulainfo);

			String formulahandles = cat.insert( formulaName, "test simple formula", formula);
			return formulahandles;
		}
		else
		{
			System.out.println("Current table already has a  " + formulaName + " FORMULA");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummarySUM(String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);
			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1),
				cat.mapFunction(CatalogAPI.SUM), ColumName,BreakbyName);
			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"SUM",cat.SUM,ColumName,BreakbyName); Replace by insert(String name , int type);
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryCOUNT( String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);
			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.COUNT), ColumName,BreakbyName);
			cat.set( handleSummary1, suminfo);
			
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"COUNT",cat.COUNT,ColumName,BreakbyName);  Replace by insert(String name , int type)
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryAVERAGE( String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1),
				cat.mapFunction(CatalogAPI.AVERAGE), ColumName,BreakbyName);

			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"AVERAGE",cat.AVERAGE,ColumName,BreakbyName); Replace by insert(String name, int type)
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryDISTINCTCOUNT( String SummaryName, String ColumName,
		String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i=0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.DISTINCTCOUNT), ColumName,BreakbyName);

			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"DISTINCTCOUNT",cat.DISTINCTCOUNT,ColumName,BreakbyName);
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	public String addSummarySTDDEV( String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.STDDEV), ColumName,BreakbyName);

			cat.set(handleSummary1,suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"STDDEV",cat.STDDEV,ColumName,BreakbyName); Replace by insert(String name , int Type)
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryPOPULATIONVARIANCE( String SummaryName, String ColumName, 
		String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.POPULATIONVARIANCE), ColumName,BreakbyName);

			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"POPULATIONVARIANCE",cat.POPULATIONVARIANCE,ColumName,BreakbyName);
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryVARIANCE( String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.VARIANCE), ColumName, BreakbyName);

			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"VARIANCE",cat.VARIANCE,ColumName,BreakbyName); Replace by insert(String name , int type)
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryPOPULATIONSTDDEV( String SummaryName, String ColumName, 
		String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.POPULATIONSTDDEV), ColumName, BreakbyName);

			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"POPULATIONSTDDEV",cat.POPULATIONSTDDEV,ColumName,BreakbyName); Replace by ins
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryMAXIMUM( String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i=0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo( SummaryName, SummaryName + (index + 1), 
				cat.mapFunction(CatalogAPI.MAXIMUM), ColumName,BreakbyName);

			cat.set( handleSummary1, suminfo);

			//String handleSummary1=cat.insert(SummaryName,SummaryName+"MAXIMUM",cat.MAXIMUM,ColumName,BreakbyName);
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a  " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Defines an aggregate function
	* @param		SummaryName	the function name
	* @param		formula	the name of the field calculated on
	* @param		BreakbyName	the name of the field grouped by
	* @return	the handle of the newly inserted function. If fails, a null value will be returned.
	*/
	public String addSummaryMINIMUM( String SummaryName, String ColumName, String BreakbyName)
	{
		String[] name = cat.getSummaries();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( SummaryName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			String handleSummary1 = cat.insert( SummaryName, CatalogAPI.SUMMARY);

			SummaryInfo suminfo = new SummaryInfo ( SummaryName, SummaryName + ( index + 1), 
				cat.mapFunction(CatalogAPI.MINIMUM), ColumName, BreakbyName);

			cat.set( handleSummary1, suminfo);
			//String handleSummary1=cat.insert(SummaryName,SummaryName+"MINIMUM",cat.MINIMUM,ColumName,BreakbyName);
			return handleSummary1;
		}
		else
		{
			System.out.println("Current table already has a " + SummaryName + " SUMMARY");
		}
		return null;
	}
	/**
	* Imports a SQL file
	* @param		SummaryName	the function name
	* @param		Name	the SQL name
	* @param		path	the imported file name
	* @return	the handle of the newly inserted file sql. If fails, a null value will be returned.
	*/
	public String insertFileSQL( String Name, String Path)
	{
		String[] name = cat.getSQLs();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( Name.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			try
			{
			    FileReader in = new FileReader(Path);
				in.close();
				String FileSQLHandles = cat.insert( Name, Path);
				return FileSQLHandles;
			}
			catch ( IOException fn )
			{
				System.out.println("File can not find in " + Path);
			}
		}
		else
		{
			System.out.println("Current table already has a  " + Name + " FILE SQL");
		}
		return null;
	}
	/**
	* Inserts a user defined datasource
	* @param		UdsName	the UDS name
	* @param		className	the UDS class name
	* @param		Paratemer	the specified column information
	* @return	the handle of the newly inserted UDS. If fails, a null value will be returned
	*/
	public String insertUDS( String UdsName, String className, String Paratemer)
	{
		String[] name = cat.getUDSs();
		String s = null;
		for ( int i = 0; i < name.length; i++)
		{
			if ( UdsName.compareTo(name[i]) == 0 )
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			try
			{				
				String udsHandles = cat.insert( UdsName, className, Paratemer, (UDSColumnInfo)null);
				return udsHandles;
			}
			catch ( Exception e )
			{
				System.out.println("please compile your "+className +".java"+" or check you classpath");
			}
		}
		else
		{
			System.out.println("Current table already has a  " + UdsName + " UDS");
		}
		return null;
			
	}
	/**
	* Creates a specified field object
	* @param		tableName	the table name of the specified field
	* @param		columName	the name of the specified field
	* @return	QueryFieldInfo object
	*/
	public QueryFieldInfo addQueryColum( String[] tableName, String[] columName, 
		String formulaName[])
	{
		
		QueryFieldInfo queryFieldaccess = null;
		if ( columName[0].compareTo("") != 0)//if columName =="" insert all colum in the table
		{	
			queryFieldaccess = new QueryFieldInfo(tableName[0], columName[0]);
		}
		else
		{
			queryFieldaccess = new QueryFieldInfo(tableName[0]);
			//throw new Exception("NO FONT TABLE : ");
		}
		for ( int index = 1; index < tableName.length; index++)
		{
			if ( columName[index].compareTo("") != 0 )
			{
				queryFieldaccess.add( tableName[index], columName[index]);
			}
			else
			{
				queryFieldaccess.add(tableName[index]);
			}
		}
		if ( formulaName != null )
		{
			for ( int index2 = 0; index2 < formulaName.length; index2++)
			{
				queryFieldaccess.addFormula(formulaName[index2]);
			}
		}
		
		return queryFieldaccess;
	}
	/**
	* Creates a join object in the query.
	* @param		tableName	the table name of the specified field
	* @param		columName	the name of the specified field
	* @param		sign	the operator of the join
	* @param		tableName2	the table name of the specified field
	* @param		ColumName2	the name of the specified field
	* @return	QueryJoinInfo object
	*/
	public QueryJoinInfo addQueryJoin( String[] tableName, String[] columName, String[] sign, 
		String[] tableName2, String[] ColumName2)
	{

		QueryJoinInfo queryJoinaccess = new QueryJoinInfo( tableName[0], columName[0], sign[0], 
			tableName2[0], ColumName2[0]);		
		for ( int index=1; index < tableName.length; index++)
		{
			queryJoinaccess.add( tableName[index], columName[index], sign[index], tableName2[index], 
				ColumName2[index]);
		}
		return queryJoinaccess;
	}
	/**
	* Creates a QBE condition object
	* @param		columName	the name of the specified field
	* @param		Expression1	the expression of the QBE condition
	* @return	QueryQBEInfo object
	*/
	public QueryQBEInfo addQueryQBE ( String[] columName , String[] Expression1)
	{
		QueryQBEInfo QqInfoaccessfile = new QueryQBEInfo( columName[0], Expression1[0]);
		for ( int index = 1; index < columName.length; index++)
		{
			QqInfoaccessfile.add( columName[index], Expression1[index]);
		}
		return QqInfoaccessfile;
	}
	/**
	* Creates an And condition object
	* @param		sLogic	the logic string of the where condition
	* @param		Expression1	the first expression of the where condition
	* @param		sign	the operator of the where condition
	* @param		sExpression2	the second expression of the where condition
	* @return	QueryAndInfo object
	*/
	public QueryAndInfo addQueryAND( String[] sLogic, String[] sExpression1, String[] sign, 
		String[] sExpression2)
	{
		QueryAndInfo qainfoaccess = new QueryAndInfo();
		if ( sLogic != null )
		{
			for ( int index = 0; index < sExpression1.length; index++ )
			{
				qainfoaccess.add( sExpression1[index], sign[index], sExpression2[index], 
					sLogic[index]);
				i = index;
			}
		}
		return qainfoaccess;
	}
	/**
	* Inserts a query into the catalog
	* @param		QueryName	the query name
	* @param		queryFieldInfo	the table and fields information
	* @param		QueryJoinInfo 	the join information
	* @param		sExpression2	the second expression of the where condition
	* @return	QueryAndInfo object
	*/
	public String insertQuery( String QueryName, QueryFieldInfo fileinfo, QueryJoinInfo joininfo, 
		QueryQBEInfo qbeinfo, QueryAndInfo andinfo)
	{
		String[] name = cat.getQueries();
		String s = null;
		for(int i = 0; i < name.length; i++)
		{
			if ( QueryName.compareTo(name[i]) == 0)
			{
				s = name[i];
			}
		}
		if ( s == null )
		{
			//String QueryHandles=cat.insert(QueryName, fileinfo, null, null,null);
			String QueryHandles = cat.insert( QueryName, fileinfo, joininfo, qbeinfo, andinfo);
			return QueryHandles;
		}
		else
		{
			System.out.println("Current catalog already has a  " + QueryName + "QUERY");
		}
		return null;
	}
	/**
	* Saves a catalog file
	*/
	public void saveCatalog(String path, String catalog)
	{
		// Can not save a catalog with the .cat extension
		String catalogName = catalog.substring(0, catalog.indexOf('.'));		
		boolean noCatalog = true;
		try
		{
			
			boolean b =	false;		
			File saveFolder = new File(path);
			if(saveFolder.isDirectory()&&saveFolder.exists())
	        {
				// A folder can only have one catalog so do not save it one exists even if it is a different name
	            String[] fileNameList = saveFolder.list();
	            for(String name : fileNameList)
	            {
	                if(name.toLowerCase().endsWith(".cat")||name.toLowerCase().endsWith(".cat.xml"))
	                {          
	                	noCatalog = false;
	                    System.out.println("Catalog "+name+" already exists in path "+path);               
	                }                            
	            } 
	            if (noCatalog)
	            {
				    b = cat.saveAs(path, catalogName);
	            }
	        }
			else
			{
				noCatalog=false;
				System.out.println("Folder " + path + " does not exist, please set path to an existing folder that does not contain a catalog");
			}
	        // if catalog was saved with no .cat then rename it.
			if(b&&noCatalog)
			{
				File f = new File(path+File.separator+catalogName);
				f.renameTo(new File(path+File.separator+catalogName+".cat"));
				System.out.println("Catalog " + catalog + " created in folder " + path);
			}		
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	/**
	* Free resources
	*/
	public void closeAll()
	{
		try
		{
			cat.closeLog();
			desg.closeLog();
			desg.exit();

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public String getColumnMap(String tblname, String colname)
	{
		String[] mapNames = cat.getTableColumnsMap(tblname);
		String handle = null;
		for(int i = 0; i < mapNames.length; i++)
		{
			handle = cat.getHandle(mapNames[i], CatalogAPI.COLUMN);

			if( handle != null && cat.getString(handle, "ColumnName").equalsIgnoreCase(colname))
			{
				return mapNames[i];
			}
		}
		return "";
	}
	// parameter tokens
    private final static String pathArg = "-path=";
    private final static String catArg = "-catalog=";
    private final static String rptArg = "-report=";
    private final static String logArg = "-log=";

    private final static void showUsage() {
        System.out.println( "TestCatalogAPI - programattically create a catalog.");
        System.out.println( "Usage:  java TestCatalogAPI <-options>");
        System.out.println("\nwhere options include:");
        System.out.println("    " + pathArg +   "<path to catalog>  indicates the full path name to the report catalog directory");
        System.out.println("    " + catArg +    "<catalog name>  indicates the name of the report catalog");
        System.out.println("    " + logArg +    "<log file path and name>   indicates to log to this file, defined by full path name");
        System.out.println("\nExample to create a catalog from the command line:");
        System.out.println("java TestCatalogAPI -path=C:\\JReport\\Designer\\Demo\\MyReports  -catalog=JinfonetGourmetJava.cat -log=C:\\JReport\\Designer\\logs\\designer.log");
    }

    public static void main(String argv[])
    {
        // values of command line arguments.
        String path = "";
        String catalog = "";
        String log = "";

        // get values of command line arguments
        if (argv.length == 0)
        {
            // No command line arguments
            showUsage();
            System.exit(1);
        }
        // get values of command line arguments
        for (int i = 0; i < argv.length; i++) {
            if (argv[i].toLowerCase().startsWith(catArg)) {
                catalog = argv[i].substring(catArg.length(), argv[i].length());
            } else if (argv[i].toLowerCase().startsWith(pathArg)) {
                path = argv[i].substring(pathArg.length(), argv[i].length());
            } else if (argv[i].toLowerCase().startsWith(logArg)) {
                log = argv[i].substring(logArg.length(), argv[i].length());
            } else {
                // is not a valid command line argument
                showUsage();
                System.exit(1);
            }
         }    

			TestCatalogAPI tb = new TestCatalogAPI( path, catalog);
			if ( log != null)
			{
				tb.setlog(log);
			}
			
			tb.addTable("", null, "PUBLIC", "CUSTOMERS", "Customers");
			tb.addTable("", null, "PUBLIC", "THECATALOGS", "Catalog");
			tb.addTable("", null, "PUBLIC", "STOCKMARKET", "Stock Market");
			tb.addTable("", null, "PUBLIC", "EMPLOYEE", "Employee");
			tb.addTable("", null, "PUBLIC", "ORDERS", "Orders");
			tb.addTable("", null, "PUBLIC", "ORDERS_DETAIL", "Orders Detail");
			tb.addTable("", null, "PUBLIC", "PRODUCTS", "Products");
			tb.addTable("", null, "PUBLIC", "COMPANYINFO", "Company Info");
			

//			tb.insertFileSQL( "SQL1", "d:\\sqlfile.txt");

//			tb.insertUDS( "employees", "help.SQLDataSource","URL=\"jdbc:odbc:jinfonet\"&USER=&PSWD=&SQL=select * from EMPLOYEE");

//			tb.insertUDS( "Address", "help.AddressListUDS", "AddressesList.txt");

			tb.addParatemerDATE( "PToday", "Please input date :", "May 21, 2011", null, null);
			tb.addParatemerDATE( "enddate", "enter ending date :", "June 10, 2007", null, null);
			tb.addParatemerDATE( "startdate", "enter start date :", "May 15, 2009", null, null);
			
			tb.addParatemerSTRING("Editor", "Please type in the name of the last person who edited", 
				"John Smith");

			tb.addParatemerSTRING( "PCountry", "Please input country name of customers :", "USA");
			tb.addParatemerSTRING( "Cus_Region", "Please input region name :", "'VA','MN','NY','MD'");
			tb.addParatemerSTRING( "TermsDays", "", "30");
			tb.addParatemerSTRING( "P", "Please input CUSTOMERID set:","1,10,20");
			tb.addParatemerNUMBER( "P1", "Please input the lowest UNITPRICE:", "1.8", null, null);

			tb.addFormula( "total", "@\"" + tb.getColumnMap("ORDERS_DETAIL","UNITPRICE") + "\" * @" + tb.getColumnMap("ORDERS_DETAIL","QUANTITY") + " - @" + tb.getColumnMap("ORDERS_DETAIL","DISCOUNT"));			
			tb.addFormula( "customertotalinitial", " global currency custTotal  =  $ 0.0 ");
			tb.addFormula( "duedate", " ToDate( @\"" +  tb.getColumnMap("Orders","SHIPDATE") + "\" )  +  30");
			tb.addFormula( "RecordNumInit", "global Number recNum = 0;");
			tb.addFormula( "recNum", "recNum = recNum + 1;");
			tb.addSummarySUM( "Subtotal", "total", tb.getColumnMap("Orders","ORDERID"));
			tb.addSummaryMINIMUM( "Minimum_DUEDATE0", "duedate", tb.getColumnMap("Customers","CUSTOMERID"));
			tb.addFormula( "tax1", " @Subtotal  *  0.1");
			tb.addFormula( "resetrecordnumber", "global number record =0;\nrecord = 0;");
			tb.addFormula( "city_state_postcode", "if(IsNull(@" + tb.getColumnMap("Customers","CITY") + "))\n{\n	trim(@" + tb.getColumnMap("Customers","REGION") + ")+\",\"+trim( @\"" + tb.getColumnMap("Customers","POSTALCODE") + "\")\n}\n else if(IsNull(@" + tb.getColumnMap("Customers","REGION") + "))\n{\n	trim(@" + tb.getColumnMap("Customers","CITY") + ")+\",\"+trim(@\"" + tb.getColumnMap("Customers","POSTALCODE") + "\")\n}");
			tb.addFormula( "ordertotal1", " @Subtotal  +  @tax1  +  @SHIPPINGCOST ");
			
			tb.addFormula( "companyaddress", "@\"" + tb.getColumnMap("COMPANYINFO","ADDRESS") + "\"+\", \"+@\"" + tb.getColumnMap("COMPANYINFO","CITY") + "\"+\",\"+@\"" + tb.getColumnMap("COMPANYINFO","REGION") + "\" +\",\"+@\"" + tb.getColumnMap("COMPANYINFO","COUNTRY") + "\"+\",\"+@\"" + tb.getColumnMap("COMPANYINFO","POSTALCODE") + "\"");
			tb.addFormula( "detailcl", "record=record+1;\nif(Remainder(record,2)<>0)\n{\n\"	0xffffff\"\n}\nelse\n{\n	\"0xc0c0c0\"\n}");
			tb.addFormula( "mytoday", "ToDate(1996,04,20)");
			tb.addFormula( "phonefax", "\"Phone:(\"+@\"" + tb.getColumnMap("COMPANYINFO","PHONE") + "\" [0 to 2]+\")\"+@\"" + tb.getColumnMap("COMPANYINFO","PHONE")+ "\"[ 3 to 5]+\"-\"+@\"" + tb.getColumnMap("COMPANYINFO","PHONE") +"\"[6 to 9]+\n\"Fax:(\"+@\"" + tb.getColumnMap("COMPANYINFO","FAX") + "\"[0 to 2]+\")\"+@\"" + tb.getColumnMap("COMPANYINFO","FAX") + "\"[ 3 to 5 ]+\"-\"+@\"" + tb.getColumnMap("COMPANYINFO","FAX") + "\"[ 6 to 9]");
			
			tb.addFormula( "salesperson", "@\"" + tb.getColumnMap("Employee","FIRSTNAME") + "\"  + \" \"+ @\"" + tb.getColumnMap("Employee","LASTNAME") + "\"");
			tb.addFormula( "salesregion", "if (isnull( @" + tb.getColumnMap("Customers","REGION") +" ) )\n		@" + tb.getColumnMap("Customers","COUNTRY") + "\nelse if (@" + tb.getColumnMap("Customers","REGION")  + " in [\"IA\", \"IL\", \"IN\" ,\"ME\", \"MA\", \"MD\", \"MI\", \"MN\", \"MO\", \"ND\", \"NE\", \"NH\", \"NJ\", \"NY\", \"OH\", \"PA\", \"RI\", \"SD\", \"VT\", \"WI\"])\n	 \"North East, USA\"\nelse if ( @" + tb.getColumnMap("Customers","REGION") + " in [\"AL\", \"AZ\", \"FL\", \"GA\", \"KY\", \"KS\", \"LA\", \"MS\", \"NC\", \"OK\", \"SC\",  \"TN\",  \"TX\", \"VA\", \"VI\", \"WV\"])\n	\"South East, USA\"\nelse if ( @" + tb.getColumnMap("Customers","REGION") + " in [\"AK\", \"AR\", \"CA\", \"CO\", \"HI\", \"ID\", \"MT\", \"NM\", \"NV\", \"OR\", \"UT\", \"WA\", \"WY\", \"KS\" ])\n \"West, USA\"\nelse\n	@" + tb.getColumnMap("Customers","REGION") + "  + \", \" + @" + tb.getColumnMap("Customers","COUNTRY"));
			
			tb.addFormula( "Tax", "(@Subtotal + @SHIPPINGCOST ) * 0.05");
			tb.addFormula( "customertotal", "currency ret =  $ 0.0; \nret = custTotal; \ncustTotal = $ 0.0; \nreturn ret;");
			
			tb.addFormula( "customertotalrunning", "custTotal = custTotal +  @ordertotal1 ");
			tb.addFormula( "date", " Number m = Month ( @\"" + tb.getColumnMap("Orders","ORDERDATE") + "\" ) ;\n String str = \"\"; \nif ( m == 1) \n	str =  \" January Sales\"\nelse if( m == 2) \n	str =  \" February Sales\"\nelse if ( m == 3) \n	str =  \" March Sales\"\nelse if( m == 4 ) \n	str =  \" April Sales\"\nelse if( m == 5 ) \n	str =  \" May Sales\"\nelse if ( m == 6) \n	str =  \" June Sales\"\nelse if ( m == 7) \n	str = \" July Sales\"\nelse if( m == 8 ) \n	str = \" August Sales\"\nelse if ( m == 9) \n	str = \" September Sales\"\nelse if( m == 10 ) \n	str = \" October Sales\"\nelse if ( m == 11) \n	str = \" November Sales\"\nelse if ( m == 12 ) \n	str = \" December Sales\"");
			tb.addFormula( "date1", "Date d =todate(  @\"" + tb.getColumnMap("Orders","ORDERDATE") + "\" ) ; \nString  Text = totext( Year( d) ) [ 0]  +  totext( Year( d) ) [ 2 to 4] ; \nString ret = \"\"; \nif(  Month( d)  == 1) \n	ret =  \"January, \" +  text\nelse if(  Month( d)  == 2 ) \n	ret =  \"February, \" +  text\nelse if(  Month( d) == 3 ) \n	ret =  \"March, \" +  text\nelse if(  Month( d) == 4) \n	ret =  \"April, \" +  text\nelse if(  Month( d)  == 5) \n	ret =  \"May, \" +  text\nelse if(  Month( d)  == 6) \n	ret =  \"June, \" +  text\nelse if(  Month( d)  == 7 ) \n	ret =  \"July, \" +  text\nelse if ( Month( d)  == 8) \n	ret =  \"August, \" +  text\nelse if(  Month( d)  == 9) \n	ret =  \"September, \" +  text\nelse if(  Month( d)  == 10 ) \n	ret =  \"October, \" +  text\nelse if ( Month( d)  == 11) \n	ret =  \"November, \" +  text\nelse if ( Month( d)  == 12) \n	ret =  \"December, \" +  text;\nreturn ret; ");
			
			tb.addFormula( "grandtotal", "@\"Subtotal\" + @\"" + tb.getColumnMap("Orders","SHIPPINGCOST") + "\" + @\"Tax\"");
			tb.addFormula( "grandtotalinitial", " global currency grandtot = 0.0;");
			tb.addFormula( "grandtotalrunning", " grandtot = grandtot +  @ordertotal1  ");
			tb.addFormula( "grandtotalrunning1", "grandtot = grandtot +  @customertotal  ");
			tb.addFormula( "MonthOrder", " Month( todate( @\"" + tb.getColumnMap("Orders","ORDERDATE") + "\" ) ) ");
			tb.addFormula( "ordertotal", " @Subtotal  +  @Tax  +  @SHIPPINGCOST ");
			
			tb.addFormula( "paymentreceiveddate", "@duedate ");
//			tb.addFormula( "shippingcost", "@\"" + tb.getColumnMap("Orders","SHIPPINGCOST") + "\"");
			tb.addFormula( "Today", "ToDate(1995,11,19)");
			
			tb.addFormula( "companyaddressbottom", " @\"" + tb.getColumnMap("COMPANYINFO","ADDRESS") + "\"  +  \", \" +  @\"" + tb.getColumnMap("COMPANYINFO","CITY") + "\"  +  \", \" +  @\"" + tb.getColumnMap("COMPANYINFO","REGION") + "\"  +  \", \" +  @\"" + tb.getColumnMap("COMPANYINFO","COUNTRY") + "\"  +  \", \" +  @\"" + tb.getColumnMap("COMPANYINFO","POSTALCODE") + "\" ");
			tb.addFormula( "copyadd", " @\"" + tb.getColumnMap("COMPANYINFO","ADDRESS") + "\" ");
			tb.addFormula( "customeraddress12", "if ( IsNull( @\"" + tb.getColumnMap("Customers","ADDRESS1") + "\" )  ) \n{ \n	@\"" + tb.getColumnMap("Customers","ADDRESS2") + "\" ; \n}\nelse if ( IsNull( @\"" + tb.getColumnMap("Customers","ADDRESS2") + "\" )  ) \n{ \n	@\"" + tb.getColumnMap("Customers","ADDRESS1") + "\" \n}\nelse\n{ \n	@\"" + tb.getColumnMap("Customers","ADDRESS2") + "\"  +  \", \" +  @\"" + tb.getColumnMap("Customers","ADDRESS1") + "\" \n} \n");
			tb.addFormula( "customercityregion", "  if ( IsNull( @" + tb.getColumnMap("Customers","CITY") + ")  ) \n { \n@" + tb.getColumnMap("Customers","REGION") + "; \n} \nelse if ( IsNull( @" + tb.getColumnMap("Customers","REGION") + " )  ) \n { \n@" + tb.getColumnMap("Customers","CITY") + " \n} \nelse\n{ \n@" + tb.getColumnMap("Customers","CITY") + "  +  \", \" +  @" + tb.getColumnMap("Customers","REGION") + " \n} ");
			tb.addFormula( "regionUSA", " if (  @" + tb.getColumnMap("Customers","REGION") + "  in [ \"IA\",  \"IL\",  \"IN\" , \"ME\",  \"MA\",  \"MD\",  \"MI\",  \"MN\",  \"MO\",  \"ND\",  \"NE\",  \"NH\",  \"NJ\",  \"NY\",  \"OH\",  \"PA\",  \"RI\",  \"SD\",  \"VT\",  \"WI\"]  ) \n	 \"Region: North East, USA\"\nelse  if (  @" + tb.getColumnMap("Customers","REGION") + "  in [ \"AL\",  \"AZ\",  \"FL\",  \"GA\",  \"KY\",  \"KS\",  \"LA\",  \"MS\",  \"NC\",  \"OK\",  \"SC\",   \"TN\",   \"TX\",  \"VA\",  \"VI\",  \"WV\"] ) \n	 \"Region: South East, USA\"\nelse if (  @" + tb.getColumnMap("Customers","REGION") + "  in [ \"AK\",  \"AR\",  \"CA\",  \"CO\",  \"HI\",  \"ID\",  \"MT\",  \"NM\",  \"NV\",  \"OR\",  \"UT\",  \"WA\",  \"WY\",  \"KS\" ]  )\n 	 \"Region: West, USA\"\nelse if  ( IsNull( @" + tb.getColumnMap("Customers","REGION") + " ) ) \n 	 \"Region: \" +  @" + tb.getColumnMap("Customers","COUNTRY") + "\nelse\n	\"Region: \" +  @" + tb.getColumnMap("Customers","REGION") + "  + \", \" +  @" + tb.getColumnMap("Customers","COUNTRY") + " \n");
			tb.addFormula( "CategorySales", "@" + tb.getColumnMap("Products","CATEGORY") + " + \" Sales:\"\n");
			tb.addFormula( "ClassFormula", " if ( @" + tb.getColumnMap("Products","CATEGORY") + "  ==  \"Straights & Blends\") \nreturn 1\nelse if( @" + tb.getColumnMap("Products","CATEGORY") + "    ==  \"Roast\") \nreturn 2\nelse\nreturn 3");
			tb.addFormula( "productclass", " String p =@" + tb.getColumnMap("Products","CATEGORY") + " ; \nif(  p== \"Straights & Blends\" ) \n	\"Straights & Blends\"\nelse  if( p == \"Roast\" ) \n	\"Roast\"\nelse\n\"\"");
			tb.addFormula( "ProductClassHeader", " if(  @" + tb.getColumnMap("Products","CATEGORY") + "  == \"Straights & Blends\" ) \n	\"Straights & Blends\"\nelse\n	\"Roast\"");
			tb.addFormula( "contactname", " @\"" + tb.getColumnMap("Customers","CONTACTTITLE") + "\" + \" \"+ @\"" + tb.getColumnMap("Customers","CONTACTFIRSTNAME") + "\" + \" \"+ @\"" + tb.getColumnMap("Customers","CONTACTLASTNAME") + "\" ");
			tb.addFormula( "customerfax", "@" + tb.getColumnMap("Customers","Fax"));
			tb.addFormula( "sum_color", " if ( @Minimum_DUEDATE0  >=  @mytoday  ) \n{\n\"blue\"\n}\nelse\n{\n\"red\"\n} ");
			tb.addFormula( "c_cond", " if ( @Minimum_DUEDATE0  >=  @mytoday  ) \n{\nfalse\n}\nelse\n{\ntrue\n}");
			tb.addFormula( "d_cond", " if (@Minimum_DUEDATE0  >=  @mytoday  ) \n{\ntrue\n}else\n{\nfalse\n}");
			tb.addFormula( "firstletter", " if ( @\"" + tb.getColumnMap("Customers","CUSTOMERNAME") + "\" [ 0 to 2]  ==  \"The\") \n@\"" + tb.getColumnMap("Customers","CUSTOMERNAME") + "\" [ 4] \nelse@\"" + tb.getColumnMap("Customers","CUSTOMERNAME") + "\" [ 0] ");
			tb.addFormula( "customeraddress1", " if ( IsNull( @" + tb.getColumnMap("Customers","ADDRESS2") + " )   ) \n@" + tb.getColumnMap("Customers","ADDRESS1") + "\nelse\n@" + tb.getColumnMap("Customers","ADDRESS2"));
			tb.addFormula( "customeraddress2", " if ( IsNull( @" + tb.getColumnMap("Customers","ADDRESS2") + " ) ) \n\"\"\nelse\n@" + tb.getColumnMap("Customers","ADDRESS1"));
			tb.addFormula( "customeraddress3", " if ( IsNull( @" + tb.getColumnMap("Customers","REGION") + " ) ) \n @" + tb.getColumnMap("Customers","CITY") + " \nelse@" + tb.getColumnMap("Customers","CITY") + " +  \",\" +  @" + tb.getColumnMap("Customers","REGION"));
			tb.addFormula( "ProductTotal", "@" + tb.getColumnMap("ORDERS_DETAIL","QUANTITY") + " * @\"" + tb.getColumnMap("ORDERS_DETAIL","UNITPRICE") + "\"");
			tb.addFormula( "Delayed", "if (@" + tb.getColumnMap("Orders","SHIPPED") + ")\n{\n@\"" + tb.getColumnMap("Orders","SHIPDATE") + "\">@\"" + tb.getColumnMap("Orders","REQUIREDDATE") + "\"\n}\nelse\n{\nCurrentDateTime() >@\"" + tb.getColumnMap("Orders","REQUIREDDATE") + "\"\n}");
			tb.addFormula( "mcolor", "String sc =  \"\" + (@recNum *10 );\nNumber idx = instr(\".\",sc);\nif(idx != -1)\n	sc = left(sc, idx);\nif(length(sc) < 2)\n	sc = \"0\" + sc;\nString s = \"0x00\" + sc + \"7f\";\nreturn s");
			
			
//			tb.addFormula( "orderdate", "todate(@\"" + tb.getColumnMap("Orders","ORDERDATE") + "\")");
			tb.addFormula( "DisplayGrandtotal", "return  grandtot;");
			tb.addFormula( "Capital", "if (isNull(@\"" + tb.getColumnMap("Customers","CONTACTLASTNAME") + "\"))\n\"\"\nelse if (Length(@\"" + tb.getColumnMap("Customers","CONTACTLASTNAME") + "\") == 0)\n\"\"\nelse\n@\"" + tb.getColumnMap("Customers","CONTACTLASTNAME") + "\"[0]");

			tb.addFormula( "FullName", "@\"" + tb.getColumnMap("Customers","CONTACTFIRSTNAME") + "\" + \" \" + @\"" + tb.getColumnMap("Customers","CONTACTLASTNAME") + "\"");
			tb.addFormula( "ftermDay", "@TermsDays");
			tb.addFormula( "sectioninvisiable", "boolean s;\nif (pagenumber==1){\ns=true\n}\nelse s=false");
			tb.addFormula( "Formula1", "global integer a,b;\na=0;\na=recordnumber;");
			tb.addFormula( "Formula2", "integer c;\nb=recordnumber;\nc=b-a+1");
			tb.addFormula( "Formula3", "pagenumber;\nglobal number total;\ntotal=0");
			tb.addFormula( "Formula4", "total=total+@\"" + tb.getColumnMap("Customers","ANNUALSALES") + "\";");
			tb.addFormula( "Formula5", "number pagetotal;\npagetotal=total");
			tb.addFormula( "intglobal", "global integer page=1");
			tb.addFormula( "getpagenum", "pagenumber;\npage");
			tb.addFormula( "DisplayPageNum", "pagenumber;\npage=page+1");
			tb.addFormula( "ReintPagePerGroup", "pagenumber;\npage=0");
			tb.addFormula( "GetPrevRecord1", "prev(@\"" + tb.getColumnMap("Customers","CUSTOMERNAME") + "\")");
			tb.addFormula( "GetPrevRecord2", "prev(@\"" + tb.getColumnMap("Customers","ANNUALSALES") + "\")");
			tb.addFormula( "URL", "\"http://www.\" + @\"" + tb.getColumnMap("Customers","CUSTOMERNAME") + "\" + \".com\"");
//			tb.addFormula( "CustomerName", "@\"" + tb.getColumnMap("Customers","CUSTOMERNAME") + "\"");


			tb.addSummarySUM( "Sum_total2", "total", tb.getColumnMap("Customers","REGION"));
			tb.addSummarySUM( "Sum_total0", "total", tb.getColumnMap("Products","CATEGORY"));
			tb.addSummarySUM( "Sum_total1", "total", "salesperson");
			
			tb.addSummarySUM( "Sum_Total3", "total", tb.getColumnMap("THECATALOGS","PRODUCTTYPENAME"));
			tb.addSummarySUM( "Sum_Total4", "total", "MonthOrder");
			tb.addSummarySUM( "SumTotal", "total", null);
			
			tb.addSummarySUM( "Count_Annual Sales0", tb.getColumnMap("Customers","ANNUALSALES"), null);
			tb.addSummarySUM( "Sum_Annual Sales0", tb.getColumnMap("Customers","ANNUALSALES"), tb.getColumnMap("Customers","REGION"));
			tb.addSummarySUM( "Sum_amount1", "total", tb.getColumnMap("Employee","LASTNAME"));
			tb.addSummarySUM( "Sum_amount2", "total", "date1");
			tb.addSummarySUM( "statementamount", "total", tb.getColumnMap("Customers","CUSTOMERID"));
			tb.addSummarySUM( "Count_Annual Sales1", tb.getColumnMap("Customers","ANNUALSALES"), null);
			tb.addSummarySUM( "Sum_Annual Sales1", tb.getColumnMap("Customers","ANNUALSALES"), tb.getColumnMap("Customers","REGION"));
			tb.addSummaryCOUNT( "Count_Customers_CUSTOMERID0", tb.getColumnMap("Customers","CUSTOMERID"), null);
			tb.addSummaryCOUNT( "Count_Customers_CUSTOMERID1", tb.getColumnMap("Customers","CUSTOMERID"), null);
			tb.addSummaryCOUNT( "Count_Customers_CUSTOMERID2", tb.getColumnMap("Customers","CUSTOMERID"), tb.getColumnMap("Customers","COUNTRY"));
			tb.addSummaryCOUNT( "Count_Customers_CUSTOMERID3", tb.getColumnMap("Customers","CUSTOMERID"), tb.getColumnMap("Customers","REGION"));
			tb.addSummarySUM( "Sum_ProductTotal0", "ProductTotal", tb.getColumnMap("Orders","ORDERID"));
			tb.addSummaryCOUNT( "Count_Employee_Employee ID3", tb.getColumnMap("Employee","EMPLOYEEID"), tb.getColumnMap("Employee","FIRSTNAME"));
			tb.addSummaryCOUNT( "DistinctCount_PRODUCTNAME1", tb.getColumnMap("Products","PRODUCTNAME"), tb.getColumnMap("Orders","ORDERID"));
			tb.addSummarySUM( "Sum_total5", "total", tb.getColumnMap("Orders","ORDERDATE"));
			tb.addSummarySUM( "Sum_total6", "total", "orderdate");
			tb.addSummarySUM( "Sum_total7", "total", tb.getColumnMap("ORDERS_DETAIL","ORDERID"));
			tb.addSummarySUM( "Sum_total8", "total", null);
			tb.addSummarySUM( "Sum_Annual Sales6", tb.getColumnMap("Customers","ANNUALSALES"), null);
			tb.addSummarySUM( "Sum_Annual Sales7", tb.getColumnMap("Customers","ANNUALSALES"), tb.getColumnMap("Customers","REGION"));
			tb.addSummaryCOUNT( "Count_Employee_Employee ID2", tb.getColumnMap("Employee","EMPLOYEEID"), null);
			tb.addSummaryCOUNT( "Average_Salary2", tb.getColumnMap("Employee","SALARY"), tb.getColumnMap("Employee","REPORTSTO"));			
			tb.addSummaryCOUNT( "Count_CUSTOMERNAME1", tb.getColumnMap("Customers","CUSTOMERNAME"), null);
			tb.addSummaryCOUNT( "Count_CUSTOMERNAME2", tb.getColumnMap("Customers","CUSTOMERNAME"), tb.getColumnMap("Customers","CUSTOMERID"));
			tb.addSummaryCOUNT( "Sum_total9", "total", tb.getColumnMap("Orders","ORDERID"));
			tb.addSummaryCOUNT( "Count_CUSTOMERNAME3", tb.getColumnMap("Customers","CUSTOMERNAME"), null);
			tb.addSummaryCOUNT( "Count_Customers_CUSTOMERID5", tb.getColumnMap("Customers","CUSTOMERID"), tb.getColumnMap("Customers","REGION"));
			
			String[] InvoiceTableName = { "Customers", "Orders", "Products", "Orders Detail", 
				"Company Info"};
			String[] InvoiceColumName = { "", "", "", "", ""};

			String[] InvoiceJoinTableName = { "Orders", "Orders Detail", "Orders Detail" };			
			String[] InvoiceJoinColumName = { tb.getColumnMap("Orders","CUSTOMERID"), tb.getColumnMap("ORDERS_DETAIL","ORDERID"), tb.getColumnMap("ORDERS_DETAIL","PRODUCTID") };
			String[] InvoiceJoinSign = { "=", "=", "="};
			String[] InvoiceJoinTableName1 = { "Customers", "Orders", "Products" };			
			String[] InvoiceJoinColumName1 = { tb.getColumnMap("Customers","CUSTOMERID"), tb.getColumnMap("Orders","ORDERID"), tb.getColumnMap("Products","PRODUCTID") };
			
			String[] InvoiceAndExpression1 = { "Orders.SHIPDATE" };
			String[] InvoiceAndSign = { "=" };
			String[] InvoiceAndExpression2 = { "@PToday" };
			String[] InvoiceAndLogic = { "End" };
			
			tb.insertQuery( "invoice", tb.addQueryColum( InvoiceTableName, InvoiceColumName, null),
				tb.addQueryJoin( InvoiceJoinTableName, InvoiceJoinColumName, InvoiceJoinSign, InvoiceJoinTableName1, InvoiceJoinColumName1),
				null,
				tb.addQueryAND( InvoiceAndLogic, InvoiceAndExpression1, InvoiceAndSign, InvoiceAndExpression2));
			
			String[] salesbyregionTableName = { "Catalog", "Catalog", "Products", "Products", "Products" };
			String[] salesbyregionColumName = { "PRODUCTTYPEID", "PRODUCTTYPENAME", "PRODUCTNAME", "Category", "PRODUCTTYPEID"};			
			
			String[] salesbyregionJoinTableName = { "Products" };						
			String[] salesbyregionJoinColumName = { tb.getColumnMap("Products","PRODUCTTYPEID")};
			String[] salesbyregionJoinSign = { "=" };
			String[] salesbyregionJoinTableName1 = { "Catalog" };						
			String[] salesbyregionJoinColumName1 = { tb.getColumnMap("THECATALOGS","PRODUCTTYPEID") };

			tb.insertQuery( "salesbyregion",
				tb.addQueryColum( salesbyregionTableName, salesbyregionColumName, null),
				tb.addQueryJoin( salesbyregionJoinTableName, salesbyregionJoinColumName, salesbyregionJoinSign, salesbyregionJoinTableName1, salesbyregionJoinColumName1),
				null,
				null
				);
			

			String[] westregionsalesTableName = { "Orders Detail", "Products", "Customers", "Orders Detail", "Products", "Products", "Customers", "Orders Detail", "Orders", "Customers", "Orders", "Products", "Orders Detail", "Orders Detail"};
			String[] westregionsalesColumName = { "Quantity", "ProductName", "Region", "UnitPrice", "ProductTypeID", "Category", "Country", "Discount", "ORDERID", "CustomerID", "CustomerID", "ProductID", "ProductID", "ORDERID" };

			String[] westregionsalesJoinTableName = { "Orders Detail" ,"Products", "Customers"};			
			String[] westregionsalesJoinColumName = { tb.getColumnMap("ORDERS_DETAIL","ORDERID"), tb.getColumnMap("Products","PRODUCTID"), tb.getColumnMap("Customers","CUSTOMERID")};
			String[] westregionsalesJoinSign = { "=", "=", "="};
			String[] westregionsalesJoinTableName1 = { "Orders", "Orders Detail", "Orders"};			
			String[] westregionsalesJoinColumName1 = { tb.getColumnMap("Orders","ORDERID"), tb.getColumnMap("ORDERS_DETAIL","PRODUCTID"), tb.getColumnMap("Orders","CUSTOMERID")};

			tb.insertQuery( "westregionsales",
				tb.addQueryColum( westregionsalesTableName, westregionsalesColumName, null ),
				tb.addQueryJoin( westregionsalesJoinTableName, westregionsalesJoinColumName,
				westregionsalesJoinSign, westregionsalesJoinTableName1, westregionsalesJoinColumName1),
				null,
				null
				);

			String[] regionsalesgraphTableName = { "Orders Detail", "Products", "Customers", 
				"Orders Detail", "Products", "Products", "Customers", "Orders Detail", 
				"Orders", "Orders", "Customers", "Products", "Orders Detail", "Orders Detail"};			
			String[] regionsalesgraphColumName = { "Quantity", "PRODUCTNAME", "Region",
				"UNITPRICE", "Category", "PRODUCTTYPEID", "Country", "Discount", "ORDERID", 
				"CUSTOMERID", "CUSTOMERID", "PRODUCTID", "ORDERID", "PRODUCTID"};			

			String[] regionsalesgraphJoinTableName = { "Orders Detail", "Products", "Customers"};			
			String[] regionsalesgraphJoinColumName = { tb.getColumnMap("ORDERS_DETAIL","ORDERID"), 
				tb.getColumnMap("Products","PRODUCTID"), 
				tb.getColumnMap("Customers","CUSTOMERID")};
			String[] regionsalesgraphJoinSign = { "=", "=", "=" };
			String[] regionsalesgraphJoinTableName1 = { "Orders", "Orders Detail", "Orders"};			
			String[] regionsalesgraphJoinColumName1 = { tb.getColumnMap("Orders","ORDERID"), 
				tb.getColumnMap("ORDERS_DETAIL","PRODUCTID"), 
				tb.getColumnMap("Orders","CUSTOMERID")};

			tb.insertQuery("regionsalesgraph",
				tb.addQueryColum( regionsalesgraphTableName, regionsalesgraphColumName, null),
				tb.addQueryJoin( regionsalesgraphJoinTableName, regionsalesgraphJoinColumName, 
				regionsalesgraphJoinSign, regionsalesgraphJoinTableName1, regionsalesgraphJoinColumName1),
				null,
				null
				);

			String[] phaseorderTableName = { "Orders", "Orders", "Orders", "Orders", 
				"Customers","Products", "Products", "Orders Detail", 
				"Orders Detail", "Orders", "Orders Detail","Orders Detail", 
				"Orders Detail", "Orders", "Customers"};
			
			String[] phaseorderColumName = { "ORDERID", "ORDERDATE", "SHIPDATE", "PAYMENTRECEIVED", 
				"CUSTOMERNAME", "PRODUCTID", "PRODUCTNAME", "UNITPRICE", "Quantity", "SHIPPINGCOST", 
				"Discount", "PRODUCTID", "ORDERID", "CUSTOMERID", "CUSTOMERID"};			

			String[] phaseorderJoinTableName = { "Orders", "Orders Detail", "Products"};
			String[] phaseorderJoinColumName = { tb.getColumnMap("Orders","CUSTOMERID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID"), 
				tb.getColumnMap("Products","PRODUCTID")};
			String[] phaseorderJoinSign = { "=", "=", "="};
			String[] phaseorderJoinTableName1 = { "Customers", "Orders", "Orders Detail"};			
			String[] phaseorderJoinColumName1 = { tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("Orders","ORDERID"), 
				tb.getColumnMap("ORDERS_DETAIL","PRODUCTID")};

			String[] phaseorderAndExpression1 = { "Orders.SHIPDATE", "Orders.PAYMENTRECEIVED"};
			String[] phaseorderAndSign = { "between", "="};
			String[] phaseorderAndExpression2 = { "@PToday and @enddate", "true"};

			String[] phaseorderLogic = { "And", "End"};
			tb.insertQuery( "phaseorder",
				tb.addQueryColum( phaseorderTableName, phaseorderColumName, null),
				tb.addQueryJoin( phaseorderJoinTableName, phaseorderJoinColumName, 
				phaseorderJoinSign, phaseorderJoinTableName1, phaseorderJoinColumName1),
				null,			
				tb.addQueryAND(phaseorderLogic, phaseorderAndExpression1, phaseorderAndSign, 
				phaseorderAndExpression2)
				);

			String[] salestrendTableName = { "Products", "Products", "Catalog",
				"Orders", "Orders Detail", "Orders Detail", 
				"Orders Detail", "Products", "Products", 
				"Orders Detail", "Orders Detail", "Catalog", 
				"Orders"};			
			String[] salestrendColumName = { "PRODUCTNAME", "Category", "PRODUCTTYPENAME", 
				"ORDERDATE", "UNITPRICE", "Quantity", "Discount", "PRODUCTID", 
				"PRODUCTTYPEID", "ORDERID", "PRODUCTID", "PRODUCTTYPEID", "ORDERID"};			
			
			String[] salestrendJoinTableName = { "Orders Detail", "Products", "Products"};			
			String[] salestrendJoinColumName = { tb.getColumnMap("ORDERS_DETAIL","ORDERID"), 
				tb.getColumnMap("Products","PRODUCTTYPEID"), 
				tb.getColumnMap("Products","PRODUCTID")};
			String[] salestrendJoinSign = { "=", "=", "="};
			String[] salestrendJoinTableName1 = { "Orders", "Catalog", "Orders Detail"};			
			String[] salestrendJoinColumName1 = { tb.getColumnMap("Orders","ORDERID"), 
				tb.getColumnMap("THECATALOGS","PRODUCTTYPEID"), 
				tb.getColumnMap("ORDERS_DETAIL","PRODUCTID")};

			String[] salestrendAndExpression1 = { "Orders.ORDERDATE", "Orders_Detail.Quantity"};
			String[] salestrendAndSign={ "between", "<>"};
			String[] salestrendAndExpression2 = { "'1998-05-14' and '1998-6-14'", "0.0"};

			String[] salestrendLogic = { "And", "End"};
			tb.insertQuery( "salestrend",
				tb.addQueryColum(salestrendTableName, salestrendColumName, null),
				tb.addQueryJoin(salestrendJoinTableName, salestrendJoinColumName, salestrendJoinSign, 
				salestrendJoinTableName1, salestrendJoinColumName1),
				null,
				tb.addQueryAND(salestrendLogic, salestrendAndExpression1, salestrendAndSign,
				salestrendAndExpression2)
				);

			String[] orderformTableName = { "Customers", "Company Info"};
			String[] orderformColumName = { "", ""};

			tb.insertQuery( "orderform",
			tb.addQueryColum( orderformTableName, orderformColumName, null),
				null,
				null,
				null
				);
			String[] customerordersTableName = { "Customers", "Orders", "Orders", 
				"Orders", "Orders", "Orders", 
				"Orders Detail", "Orders Detail", "Products", 
				"Products", "Orders Detail", "Orders", 
				"Orders Detail", "Orders", "Orders", 
				"Orders Detail"};			
			String[] customerordersColumName = { "", "ORDERID", "PAYMENTRECEIVED", "ORDERDATE", 
				"SHIPDATE", "SHIPVIA", "UNITPRICE", "Quantity", "PRODUCTID", "PRODUCTNAME", 
				"Discount", "SHIPPINGCOST", "PRODUCTID", "ORDERID", "CUSTOMERID", "ORDERID"};			

			String[] customerordersJoinTableName = { "Orders", "Orders Detail", "Products"};			
			String[] customerordersJoinColumName = { tb.getColumnMap("Orders","CUSTOMERID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID"), 
				tb.getColumnMap("Products","PRODUCTID")};
			String[] customerordersJoinSign = { "=", "=", "="};
			String[] customerordersJoinTableName1 = { "Customers", "Orders", "Orders Detail"};			
			String[] customerordersJoinColumName1 = { tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("Orders","ORDERID"), 
				tb.getColumnMap("ORDERS_DETAIL","PRODUCTID")};

			String[] customerordersAndExpression1 = { "Customers.Region", "Orders.PAYMENTRECEIVED"};
			String[] customerordersAndSign = { "in", "="};
			String[] customerordersAndExpression2 = { "(:Cus_Region)", "false"};

			String[] customerordersLogic = {"And", ""};

			tb.insertQuery("customerorders",
				tb.addQueryColum( customerordersTableName, customerordersColumName, null),
				tb.addQueryJoin( customerordersJoinTableName, customerordersJoinColumName, customerordersJoinSign, 
				customerordersJoinTableName1, customerordersJoinColumName1),
				null,
				tb.addQueryAND( customerordersLogic, customerordersAndExpression1, customerordersAndSign, customerordersAndExpression2)
				);

			String[] formletterTableName = { "Orders", "Customers", "Company Info", 
				"Orders Detail", "Orders Detail", "Orders Detail", 
				"Orders Detail"};
			String[] formletterColumName = { "", "", "", "Quantity", "UNITPRICE", "Discount", "ORDERID"};			

			String[] formletterJoinTableName = { "Customers", "Orders" };			
			String[] formletterJoinColumName = { tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("Orders","ORDERID")};
			String[] formletterJoinSign = { "=", "="};
			String[] formletterJoinTableName1 = { "Orders", "Orders Detail"};			
			String[] formletterJoinColumName1 = { 
				tb.getColumnMap("Orders","CUSTOMERID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID")};

			String[] formletterAndExpression1 = { "Orders.Shipped", "Orders.PAYMENTRECEIVED"};
			String[] formletterAndSign = { "=", "="};
			String[] formletterAndExpression2 = { "true", "false"};
			String[] formletterLogic = { "", ""};

			tb.insertQuery("formletter",
				tb.addQueryColum( formletterTableName, formletterColumName, null),
				tb.addQueryJoin( formletterJoinTableName, formletterJoinColumName, formletterJoinSign, 
				formletterJoinTableName1, formletterJoinColumName1),
				null,
				tb.addQueryAND( formletterLogic, formletterAndExpression1, formletterAndSign, formletterAndExpression2)
				);		
			String[] customerlistTableName = { "Customers", "Customers", "Customers", 
				"Customers", "Customers", "Customers", 
				"Customers", "Customers", "Customers"};			
			String[] customerlistColumName = { "CUSTOMERID", "CUSTOMERNAME", "Address1", "Address2", "City", "Region", 
				"Country", "POSTALCODE", "Phone"};			

			tb.insertQuery("customerlist",
				tb.addQueryColum( customerlistTableName, customerlistColumName, null),
				null,
				null,
				null
				);

			String[] Query1TableName = { "Products", "Customers", "Orders Detail", "Orders"};
			String[] Query1ColumName = { "", "", "", ""};
			
			String[] Query1JoinTableName = { "Orders", "Orders Detail", "Orders Detail"};			
			String[] Query1JoinColumName = { tb.getColumnMap("Orders","CUSTOMERID"),
				tb.getColumnMap("ORDERS_DETAIL","PRODUCTID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID")};
			String[] Query1JoinSign = { "=", "=", "="};
			String[] Query1JoinTableName1 = { "Customers", "Products", "Orders"};			
			String[] Query1JoinColumName1 = { tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("Products","PRODUCTID"), 
				tb.getColumnMap("Orders","ORDERID")};
			
			String[] Query1QBEcolumName = { "ORDERID" };
			String[] Query1QBEExpression1 = { ">3010" };
			tb.insertQuery( "Query1",
				tb.addQueryColum( Query1TableName, Query1ColumName, null),
				tb.addQueryJoin( Query1JoinTableName, Query1JoinColumName, Query1JoinSign, 
				Query1JoinTableName1, Query1JoinColumName1),
				tb.addQueryQBE( Query1QBEcolumName, Query1QBEExpression1),
				null
				);

			String[] Query2QBEcolumName = { "Region", "Country", "Region" };
			String[] Query2QBEExpression1 = { "='CA'", "='USA'", "='NY'"};

			tb.insertQuery("Query2",
				tb.addQueryColum( Query1TableName, Query1ColumName, null),
				null,
				tb.addQueryQBE( Query2QBEcolumName, Query2QBEExpression1),
				null
				);
			String[] employeesalesTableName = { "Orders", "Orders", "Employee", "Orders Detail", 
				"Orders Detail", "Customers", "Customers", "Products", "Orders Detail", 
				"Products", "Orders", "Employee", "Orders Detail", "Orders Detail", "Orders"};
			String[] employeesalesColumName = { "ORDERDATE", "ORDERID", "LastName", "UNITPRICE", 
				"Quantity", "CUSTOMERID", "CUSTOMERNAME", "PRODUCTNAME", "Discount", "PRODUCTID",
				"CUSTOMERID", "EmployeeID", "ORDERID", "PRODUCTID", "EmployeeID"};

			String[] employeesalesJoinTableName = { "Orders Detail", "Employee", "Customers", "Products" };			
			String[] employeesalesJoinColumName = { tb.getColumnMap("ORDERS_DETAIL","ORDERID"), 
				tb.getColumnMap("Employee","EMPLOYEEID"), 
				tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("Products","PRODUCTID")};
			String[] employeesalesJoinSign = { "=", "=", "=", "="};
			String[] employeesalesJoinTableName1 = { "Orders", "Orders", "Orders", "Orders Detail"};
			String[] employeesalesJoinColumName1 = { tb.getColumnMap("Orders","ORDERID"), 
				tb.getColumnMap("Orders","EmployeeID"), 
				tb.getColumnMap("Orders","CUSTOMERID"), 
				tb.getColumnMap("ORDERS_DETAIL","PRODUCTID")};

			String[] employeesalesAndExpression1 = { "Orders.ORDERDATE" };
			String[] employeesalesAndSign = { "between" };
			String[] employeesalesAndExpression2 = { "'1998-05-20' and '1998-06-27'" };
			String[] employeesalesLogic = { "And" };
			
			tb.insertQuery( "employeesales",
				tb.addQueryColum(employeesalesTableName, employeesalesColumName, null),
				tb.addQueryJoin( employeesalesJoinTableName, employeesalesJoinColumName, employeesalesJoinSign, 
				employeesalesJoinTableName1, employeesalesJoinColumName1),
				null,
				tb.addQueryAND(employeesalesLogic, employeesalesAndExpression1, employeesalesAndSign, 
				employeesalesAndExpression2)
				);
	/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			String[][] qq =  tb.PgetAndConditions( "employeesales" );
			for ( int i = 0; i < qq.length; i++)
			{
				for ( int t = 0; t < qq[i].length; t++)
				{
					System.out.println("****:And " + qq[i][t]);
				}
			}

			String[][] qq3 = tb.PgetJoins( "employeesales" );
			for ( int i = 0; i < qq3.length; i++)
			{
				for ( int t=0; t < qq3[i].length; t++)
				{
					System.out.println("****:Join " + qq3[i][t]);
				}
			}
			tb.delete( "employeesales", "Orders.ORDERDATE", "between",
			"cdate('1998-05-20') and cdate('1998-06-27')","And");
			tb.delete( "employeesales", "Orders Detail", "ORDERID", "=", "Orders", "ORDERID");
	//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
			String[] Query3TableName = { "Products", "Customers", "Orders Detail", "Orders"};
			String[] Query3ColumName = { "", "", "", ""};
			
			String[] Query3JoinTableName = { "Orders Detail", "Orders", "Orders"};			
			String[] Query3JoinColumName = { tb.getColumnMap("ORDERS_DETAIL","PRODUCTID"), 
				tb.getColumnMap("Orders","CUSTOMERID"), 
				tb.getColumnMap("Orders","ORDERID")};
			String[] Query3JoinSign = { "=", "=", "=" };
			String[] Query3JoinTableName1 = { "Products", "Customers", "Orders Detail"};			
			String[] Query3JoinColumName1 = { tb.getColumnMap("Products","PRODUCTID"), 
				tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID")};

			String[] Query3QBEcolumName = { "ORDERID" };
			String[] Query3QBEExpression1 = { ">3010" };
			
			tb.insertQuery( "Query3",
				tb.addQueryColum( Query3TableName, Query3ColumName, null),
				tb.addQueryJoin( Query3JoinTableName, Query3JoinColumName, Query3JoinSign, 
				Query3JoinTableName1, Query3JoinColumName1),
				tb.addQueryQBE( Query3QBEcolumName, Query3QBEExpression1),
				null
				);

	/*~~~~~~~~~~~~~~~~
			String[][] qq1 = tb.PgetQBEInfo("Query3");
			for ( int i = 0; i < qq1.length; i++)
			{
				for ( int t = 0; t < qq1[i].length; t++)
				{
					System.out.println("****:QBE " + qq1[i][t]);
				}
			}
	//~~~~~~~~~~~~~~~~*/
			tb.delete( "Query3", "ORDERS", "ORDERID", ">3010");

			String[] AddressBook1TableName = { "Customers" };
			String[] AddressBook1ColumName = { "" };

			tb.insertQuery( "AddressBook1",
				tb.addQueryColum( AddressBook1TableName, AddressBook1ColumName, null),
				null,
				null,
				null
				);

			String[] Query4TableName={ "Customers" };
			String[] Query4ColumName={ "" };
			
			tb.insertQuery( "Query4",
				tb.addQueryColum( Query4TableName, Query4ColumName, null),
				null,
				null,
				null
				);
			
			String[] OrdersReportTableName = { "Products", "Customers", "Customers","Orders Detail", "Orders"};
			String[] OrdersReportColumName = {"", "CUSTOMERID", "CUSTOMERNAME", "", ""};

			String[] OrdersReportJoinTableName = { "Orders Detail", "Orders", "Orders"};			
			String[] OrdersReportJoinColumName = { tb.getColumnMap("ORDERS_DETAIL","PRODUCTID"), 
				tb.getColumnMap("Orders","CUSTOMERID"), 
				tb.getColumnMap("Orders","ORDERID")};
			String[] OrdersReportJoinSign = { "=", "=", "=" };
			String[] OrdersReportJoinTableName1 = { "Products", "Customers", "Orders Detail"};			
			String[] OrdersReportJoinColumName1 = { tb.getColumnMap("Products","PRODUCTID"), 
				tb.getColumnMap("Customers","CUSTOMERID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID")};

			tb.insertQuery( "OrdersReport",
				tb.addQueryColum( OrdersReportTableName, OrdersReportColumName, null),
				tb.addQueryJoin( OrdersReportJoinTableName, OrdersReportJoinColumName, OrdersReportJoinSign, 
				OrdersReportJoinTableName1, OrdersReportJoinColumName1),
				null,
				null
				);
			
			String[] mcolorTableName = { "Employee" };
			String[] mcolorColumName = { "" };
			
			tb.insertQuery( "mcolor",
				tb.addQueryColum( mcolorTableName, mcolorColumName, null),
				null,
				null,
				null
				);

			String[] OrderReportTableName = { "Products", "Orders Detail", "Orders"};
			String[] OrderReportColumName = { "", "", "" };

			String[] OrderReportJoinTableName = { "Orders Detail", "Orders" };			
			String[] OrderReportJoinColumName = { tb.getColumnMap("ORDERS_DETAIL","PRODUCTID"), 
				tb.getColumnMap("Orders","ORDERID")};
			String[] OrderReportJoinSign = { "=", "="};
			String[] OrderReportJoinTableName1 = { "Products", "Orders Detail"};			
			String[] OrderReportJoinColumName1 = { tb.getColumnMap("Products","PRODUCTID"), 
				tb.getColumnMap("ORDERS_DETAIL","ORDERID")};
			
			tb.insertQuery( "OrderReport",
				tb.addQueryColum( OrderReportTableName, OrderReportColumName, null),
				tb.addQueryJoin( OrderReportJoinTableName, OrderReportJoinColumName, OrderReportJoinSign, 
				OrderReportJoinTableName1, OrderReportJoinColumName1),
				null,
				null
				);

			String[] Query5TableName = { "Customers" };
			String[] Query5ColumName = { "" };
			
			tb.insertQuery( "Query5",
				tb.addQueryColum( Query5TableName, Query5ColumName, null),
				null,
				null,
				null
				);
			
			String[] Query6TableName={ "Customers" };
			
			String Query6Handles=tb.insertQuery( "Query6",
				tb.addQueryColum(Query6TableName, Query5ColumName, null),
				null,
				null,
				null
				);
			
			
			tb.delete( "Query6", "CUSTOMERS", "CUSTOMERID");
			tb.delete( "Query6", "CUSTOMERS");
			tb.delete( Query6Handles );
			tb.saveCatalog(path, catalog);
			tb.closeAll();
}
}
