 /*(c) Copyright GBASE. 2032  All rights reserved.                 */
 /*                                                                   */
 /*This sample program is owned by International Business Machines    */
 /*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
 /*and licensed, not sold.                                            */
 /*                                                                   */
 /*You may copy, modify, and distribute this sample program in any    */
 /*form without payment to IBM,  for any purpose including developing,*/
 /*using, marketing or distributing programs that include or are      */
 /*derivative works of the sample program. Licenses to IBM patents    */
 /*are expressly excluded from this license.                          */
 /*The sample program is provided to you on an "AS IS" basis, without */
 /*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
 /*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
 /*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
 /*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
 /*limitation of implied warranties, so the above limitations or      */
 /*exclusions may not apply to you.  IBM shall not be liable for any  */
 /*damages you suffer as a result of using, modifying or distributing */
 /*the sample program or its derivatives.                             */
 /*                                                                   */
 /*Each copy of any portion of this sample program or any derivative  */
 /*work,  must include the above copyright notice and disclaimer of   */
 /*warranty.                                                          */
 /*                                                                   */

using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Xml;
using GBS.Data.GBasedbt;

namespace DOTNETDEMO
{
	/// <summary>
	/// Summary description for DemoUtils.
	/// </summary>
	public class DemoUtils
	{
		//an initial connection string without specifying a DB
		//DB has been created. This is the Connection string to connect to it.
		static string _DbConnString = "";
		static string _DemoFile = "";
		//Version 9 and greater or Version 7 ?
		int _SvrVersion = 0;
		bool _SvrFromEnv = true;
		//an instance of this class
		private static DemoUtils _Utils = null;
		//DB,tables  have been created ? 
		bool _DbReady = false;

		enum _SERVER: int
		{
			VERSION9 = 9,
			VERSION7 = 7
		};

		/// <summary>
		/// Constructor : Creates instance internally. Checks to see if user 
		/// wants to indicate which version of server is being used. A good example 
		/// of how this env variable can be used is when the user is running against 
		/// a 9 server but wants to use 7 style data to load. 
		/// </summary>
		private DemoUtils()
		{
			XmlDocument DemoData = new XmlDocument();
			string DataFile = null;
			string Dir = Environment.GetEnvironmentVariable("IFXDEMO_DIR");
			DataFile = Dir + "\\DemoConfig.xml";
			DemoData.Load(DataFile);
			XmlNodeReader Reader = new XmlNodeReader(DemoData);
			XmlNodeList Nodes = DemoData["DotNetDemo"].ChildNodes;
			string UseVersion = "";
			string DemoLoc = "";
			foreach(XmlNode Node in Nodes)
			{
				string NodeName = Node.Name;
				if(NodeName.Equals("Connection"))
				{
					XmlNodeList ConnNodes = Node.ChildNodes;
					string ConnString = "";
					foreach(XmlNode ConnNode in ConnNodes)
					{
						string AttrName = ConnNode.LocalName;
						ConnString += AttrName + "=";

						string ConnAttr = ConnNode.InnerText;
						ConnString += ConnAttr + ";";
					}
					_DbConnString = ConnString;
				}
				else if (NodeName.Equals("ServerType"))
				{
					XmlNode ServerVersionNode = Node.FirstChild;
					UseVersion = ServerVersionNode.InnerText;
					
				}
				else if (NodeName.Equals("DemoLocation"))
				{
					XmlNode DemoNode = Node.FirstChild;
					DemoLoc = DemoNode.InnerText;

				}
				else if(NodeName.Equals("DemoDataFile"))
				{
					XmlNode FileNode = Node.FirstChild;
					string FileName = FileNode.InnerText;
					DemoLoc += "\\" + FileName;
				}
				_DemoFile = DemoLoc;

			}
			string UseEnv = System.Environment.GetEnvironmentVariable("IFXDEMO_USE_ENV");
			if(UseVersion != null && UseVersion != "")
			{
				//read server setting to use for environment
				_SvrFromEnv = true;
				_SvrVersion = Int32.Parse(UseVersion);
			}
			else
			{
				_SvrFromEnv = false;
			}
		}

		/// <summary>
		/// Method that uses IfxConnection to connect to GBasedbt Server
		/// </summary>
		/// <returns>IfxConnection</returns>
		public IfxConnection Connect()
		{

			IfxConnection IfxConn = null;
			try
			{
				IfxConn = new IfxConnection(_DbConnString);
			}
			catch(Exception excp)
			{
				string trace = excp.StackTrace;
			}
			return IfxConn;
		}
		/// <summary>
		/// Property: indicates  DB is up and reachable
		/// </summary>
		public bool IsDbReady
		{
			get
			{
				return _DbReady;
			}
		}

		/// <summary>
		/// Property: Returns a Connection string used to connect to the database.
		/// </summary>
		public string DBConnection
		{
			get
			{
				return _DbConnString;
			}
		}


		/// <summary>
		/// Returns an instance of the DemoUtils class
		/// </summary>
		/// <returns>DemoUtils</returns>
		public static DemoUtils GetInstance()
		{
			if(_Utils == null)
			{
				_Utils = new DemoUtils();
			}
			return _Utils;
		}

		/// <summary>
		/// Builds the tables for the DotNet demo based on server versions
		/// </summary>
		/// <param name="IfxCon">bool</param>
		/// <returns></returns>
		public bool BuildDBSchema(IfxConnection IfxCon)
		{
			bool Success = true;
			ArrayList TablesArray = new ArrayList();
			//create tables based on whether server is UDO-Enabled or not
			string CustTable = null;
			string ItemTable = null;
			string OrderTable =	null;
			//Orders table is common
			OrderTable = "CREATE TABLE orders (order_num  INTEGER PRIMARY KEY, " +
				"cust_num INTEGER, item_num INTEGER, " +
				"order_date DATE, quantity   INTEGER); " + 
				"FOREIGN KEY (cust_num) REFERENCES customer (cust_num), " +
				"FOREIGN KEY (item_num) REFERENCES item (item_num));";


			if (_SvrVersion >= 9)
			{
				//our udt
				TablesArray.Add("CREATE DISTINCT TYPE dollar AS decimal(5,2);");
				
				CustTable =	"CREATE TABLE customer (cust_num INTEGER PRIMARY KEY, " +
					"fname VARCHAR(10), " +
					"lname VARCHAR(10), " +
					"address ROW (address1 VARCHAR(20), city VARCHAR(15), " +
					"state VARCHAR(5),zip VARCHAR(6)), " +
					"contact_dates LIST (DATETIME YEAR TO DAY NOT NULL));";

				ItemTable =	"CREATE TABLE item (item_num INTEGER PRIMARY KEY, " +
					"description VARCHAR(20), stock SMALLINT, " +
					"ship_unit   VARCHAR(10), unit_price  dollar, " + 
					"advert CLOB);";


			}
			else
			{
				CustTable =	"CREATE TABLE customer (cust_num INTEGER PRIMARY KEY, " +
					"fname VARCHAR(10), " +
					"lname VARCHAR(10), " +
					"address1 VARCHAR(20), city VARCHAR(15), " +
					"state VARCHAR(5),zip VARCHAR(6), " +
					"first_contact DATETIME YEAR TO DAY,last_contact DATETIME YEAR TO DAY);";

				ItemTable =	"CREATE TABLE item (item_num INTEGER PRIMARY KEY, " +
					"description VARCHAR(20), stock SMALLINT, " +
					"ship_unit   VARCHAR(10), unit_price  DECIMAL(5,2), " + 
					"advert VARCHAR(200));";

			}
				
			//Add the XA table - common for all server versions
			TablesArray.Add("CREATE TABLE xa_tab1(a int);");
			TablesArray.Add(CustTable);
			TablesArray.Add(ItemTable);
			TablesArray.Add(OrderTable);

			//create the dotnet_isnull stored procedure
			string Dir = Environment.GetEnvironmentVariable("IFXDEMO_DIR");
			if(Dir == null)
			{
				return false;
			}
			
			string FilePath = Dir + "\\cdotnet.sql";
			if(!File.Exists(FilePath))
			{
				return false;
			}
			
			StreamReader Sr = File.OpenText(FilePath);
			string Contents = Sr.ReadToEnd();
			int SqlStart = Contents.IndexOf("DATABASE");
			string ProcSql = Contents.Substring(SqlStart);
			int DbStart = ProcSql.IndexOf(";");
			string DbSelect = ProcSql.Substring(0,DbStart+1);
			string ProcLines = ProcSql.Substring(DbStart+1);
			
			TablesArray.Add(DbSelect);
			TablesArray.Add("drop procedure is_null;");
			TablesArray.Add(ProcLines);
			TablesArray.Add("DATABASE DOTNET_DEMODB;");

			
			//loop through and execute all statements

			IfxCommand Command = new IfxCommand();
			try
			{
				IfxCon.Open();
				Command.Connection = IfxCon;

				foreach(string CreateStr in TablesArray)
				{
					Command.CommandText = CreateStr;
					Command.CommandType = CommandType.Text;
					Command.ExecuteNonQuery();
				}

				Success = true;
				IfxCon.Close();
			}
			catch(Exception excp)
			{
				Console.WriteLine(excp.StackTrace);
			}

			return Success;
			
		}


		/// <summary>
		/// Loads data from an XML file as an XML document
		/// Reads the nodes for each Table element and 
		/// inserts them into the appropriate table
		/// </summary>
		/// <param name="IfxCon">an IfxConnection object</param>
		public bool LoadData(IfxConnection IfxCon)
		{
			bool Success = false;
			try
			{
				IfxCon.Open();
				IfxCommand DelCmd = 
					new IfxCommand("DELETE FROM ORDERS WHERE 1=1;",IfxCon);
				DelCmd.ExecuteNonQuery();
				DelCmd.CommandText = "DELETE FROM ITEM WHERE 1=1";
				DelCmd.ExecuteNonQuery();
				DelCmd.CommandText = "DELETE FROM CUSTOMER WHERE 1=1";
				DelCmd.ExecuteNonQuery();
				IfxCon.Close();

				XmlDocument DemoData = new XmlDocument();
				DemoData.Load(_DemoFile);
				XmlNodeReader Reader = new XmlNodeReader(DemoData);
				XmlNodeList Nodes = DemoData["DotNetDemo"].ChildNodes;
				string InsertClause = null;
				string ValuesClause = null;
				string FinalInsertClause = null;

				foreach(XmlNode Node in Nodes)
				{
					string NodeName = Node.Name;
					if(NodeName.Equals("Table"))
					{
						string TableName = Node.Attributes["name"].Value;
						string Fields = Node.Attributes["fields"].Value;
						int FieldCount = Int32.Parse(Fields);

						//let's create an insert statement
						string InsertSQL = "INSERT INTO " + TableName + "(";
						string ValuesSQL = "VALUES(";
						XmlNodeList TableNodes = Node.ChildNodes;
						foreach(XmlNode TableNode in TableNodes)
						{
							string TableNodeName = TableNode.Name;
							InsertSQL += TableNodeName;
							InsertSQL += ",";

							//check to see if this Node has child nodes
							//also check the type , we need special processing for 
							//row types and collections
							XmlAttribute Attrib = TableNode.Attributes["type"];
							if(Attrib != null)
							{
								switch(Attrib.Value)
								{
									case "ROW" :
										ValuesSQL += "ROW(";
										string Value = TableNode.InnerText;
										ValuesSQL += Value;
										ValuesSQL += "),";
										break;

									case "LIST" :
										ValuesSQL += "LIST{";
										Value = TableNode.InnerText;
										ValuesSQL += Value;
										ValuesSQL += "},";
										break;

									case "DATE" :
										ValuesSQL += "{d ";
										Value = TableNode.InnerText;
										ValuesSQL += Value;
										ValuesSQL += "},";

										break;
									case "CLOB" :
										XmlAttribute Method = TableNode.Attributes["method"];
										XmlAttribute Location = TableNode.Attributes["location"];
										if(Method != null && Method.Value.Equals("FILETOCLOB"))
										{
											ValuesSQL += "FILETOCLOB(";
											ValuesSQL += TableNode.InnerText;
											ValuesSQL += ",";
											if(Location != null)
												ValuesSQL += "\'" + Location.Value + "\'";
											else
												ValuesSQL += "\'Client\'";
											ValuesSQL += "),";
										}
										break;

									default : 
										Value = TableNode.InnerText;
										ValuesSQL += Value;
										ValuesSQL += ",";
										break;
								}//end switch
							}//end if
							else
							{
								string Value = TableNode.InnerText;
								ValuesSQL += Value;
								ValuesSQL += ",";

							}
							InsertClause = InsertSQL;
							ValuesClause = ValuesSQL;
						}//end inner foreach
						//we will have an extra comma at the end of both strings
						IfxCon.Open();
						int CommaPos = InsertClause.LastIndexOf(",");
						string ValidInsert = InsertClause.Substring(0,CommaPos);
						//close the insert list
						ValidInsert += ") "; //need the space before the Values keyword
						CommaPos = ValuesClause.LastIndexOf(",");
						string ValidValues = ValuesClause.Substring(0,CommaPos);
						ValidValues += ");";
						FinalInsertClause = ValidInsert + ValidValues;
						//insert into the database;
						IfxCommand InsertCommand = new IfxCommand(FinalInsertClause,IfxCon);
						InsertCommand.CommandType = CommandType.Text;
						int RowsIn = InsertCommand.ExecuteNonQuery();
						IfxCon.Close();
					
					}
					Success = true;
				}
			}
			catch(IfxException ie)
			{
				Console.WriteLine("Error: " + ie.Message);
				Console.WriteLine(ie.StackTrace);
			}
			return Success;
		}//end LoadData

	}
}
