/*********************************************************************************************
 * Copyright 2002 - Volian Enterprises, Inc. All rights reserved.
 * Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
 * ------------------------------------------------------------------------------
 * $Workfile: RODBRecordInterface.cs $     $Revision: 4 $
 * $Author: Jsj $   $Date: 1/27/03 2:56p $
 *
 * $History: RODBRecordInterface.cs $
 * 
 * *****************  Version 4  *****************
 * User: Jsj          Date: 1/27/03    Time: 2:56p
 * Updated in $/EXE/RefObj/ParadoxConversion
 * using OLEDB for the connection
 * 
 * *****************  Version 3  *****************
 * User: Jsj          Date: 10/04/02   Time: 2:02p
 * Updated in $/EXE/RefObj/ParadoxConversion
 * added better user interface
 * 
 * *****************  Version 2  *****************
 * User: Jsj          Date: 9/25/02    Time: 10:20a
 * Updated in $/EXE/RefObj/ParadoxConversion
 * added header
 *********************************************************************************************/

using System;
//using System.Collections;
using System.IO;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DBEncapsulation;

namespace ParadoxConversion
{
	/// <summary>
	/// This class contains a very simple interface to the RO Database.
	/// All the database conversion module needs is to create a new table
	/// and write a database record.
	/// </summary>
	public class RODBRecordInterface
	{
		bool success;

		DBEncapsulation.DBEncapsulate DBE;
		DBEncapsulation.OLEDB_DBEncap DBE_OLEDB;
		DBEncapsulation.ODBC_DBEncap DBE_ODBC;

		private string strAccOLEDBconn = "";
		private string strSQLOLEDBconn = "";
		private string strAccODBCconn = "";
		private string strodbcConn = "";

		public RODBRecordInterface(int conntype, string connectstring)
		{
			// Setup the connection string
//			if (connectstring.Equals(""))
//				GetConnectionStringFromFile();
//			else
				BuildConnectionString(connectstring);

			// Initialize the connection depending on OLE-DB or ODBC:
			conntype = 2;   // for now - till valid data passed in.
//			conntype = 1;   // for now - till valid data passed in.
			//END OF TEMPORARY

			// Either use an OLE-DB or ODBC connection, depending on what was passed in.
			if ( conntype == 1) 
			{
				DBE_ODBC = new ODBC_DBEncap();
				DBE = DBE_ODBC;
			}
			else
			{
				DBE_OLEDB = new OLEDB_DBEncap();
				DBE = DBE_OLEDB;
			}
			
			try
			{
				DBE.Connection(strAccOLEDBconn);   
//				DBE.Connection(strodbcConn);   
			}
			catch (Exception e)
			{
				Console.WriteLine("error {0}", e.Message);
			}
		}

		public void RODB_OpenConnection()
		{
			try
			{
				DBE.OpenConnection();
			}
			catch (Exception e)
			{
				Console.WriteLine("Error opening database connection.\n{0}",e.Message);
			}
		}

		public void RODB_CloseConnection()
		{
			try
			{
				DBE.CloseConnection();
			}
			catch (Exception e)
			{
				Console.WriteLine("Error closing database connection.\n",e.Message);
			}
		}
		
		/*
		 * Create a new Referenced Objects table in the current database
		 */
		public void RODB_AddNewTable(String TblName)
		{

			string strMkTable = "CREATE TABLE "+ TblName + " (";
//			strMkTable = strMkTable + "UID Integer NULL ,";
			strMkTable = strMkTable + "RecID Char(8) NULL ,";
			strMkTable = strMkTable + "RecType Integer ,";
			strMkTable = strMkTable + "ParentID char(8) NULL ,";
			strMkTable = strMkTable + "AccPageID char(32) NULL ,";
			strMkTable = strMkTable + "ModDateTime char(14) NULL ,";
			strMkTable = strMkTable + "Info Memo NULL)"; 
			try
			{
				DBE.Command(strMkTable);
//				DBE.Reader();
			}
			catch (Exception e)
			{
				Console.WriteLine("Command error:\n{0}", e.Message);
				//				bool retval = false;
			}
			//			DBE.ReaderClose();
			try
			{
				DBE.NonQuery();
			}
			catch (Exception e)
			{
				Console.WriteLine("NonQuery error:\n{0}", e.Message);
				//				bool retval = false;
			}
			DBE.CommandDispose();
		}

		/*
		 * Write an RO Database record
		 */
		public void RODB_WriteDbRecord(String DBTable, String RecID, int RecType, String ParID, String AcPgID, String DateTime, String Info)
		{
			String DbRecCmd;

			// if no RecID provided, then get the next available one.
			if (RecID.CompareTo("") == 0)
			{
				RecID = RODB_GetNextRecid(DBTable);
			}
			
			DbRecCmd = "INSERT INTO " + DBTable + " (RecID, RecType, ParentID, AccPageID, ModDateTime, Info) VALUES ('";
			DbRecCmd += RecID + "', " + RecType + ", '" + ParID + "', '" + AcPgID + "', '" + DateTime + "', '" + Info	+ "');"; 
			try
			{
				DBE.Command(DbRecCmd);
			}
			catch (Exception e)
			{
				success = false;
				Console.WriteLine("RODB_WriteDbRecord  Caught an exception:\n{0}", e.Message);
			}
			try
			{
				DBE.NonQuery();
			}
			catch (Exception e)
			{
				Console.WriteLine("NonQuery error:\n{0}", e.Message);
				//				bool retval = false;
			}
			DBE.CommandDispose();
		}

		public void GetConnectionStringFromFile()
		{
			/* This file should contain the connection strings in the followin order:
			 * AccOLEDBconn, SQLOLEDBconn, AccODBCconn, odbcConn.
			 */
			// append "ConnectList.txt" to the path of the location of the exe being run
			// for example: VE-PROMS.NET\BIN or BIN\DEBUG
			// This set in Visual Studio .NET in the properties dialog of the project, select
			// Configuration Properties.  You will see an option call Output Path.
			string FileName = Application.StartupPath + "\\ConnectList.txt";

			if (!File.Exists(FileName))
			{
				// File does not exist
				Console.WriteLine("Could not find ConnectList.txt file");
				return;
			}

			/*
			 * Open the text file for reading
			 */
			StreamReader sr = File.OpenText(FileName);

			/*
			 * Connection string to MS-access use with oledb directly
			 */
			strAccOLEDBconn = sr.ReadLine();

			/* 
			 * connection string to SQL-server use with oledb
			 */
			strSQLOLEDBconn = sr.ReadLine();

			/* 
			 * connection string to for oledb to odbc dsn
			 */
			strAccODBCconn = sr.ReadLine();

			/* 
			 * ODBC connection:
			 * ACCESS:
			 */
			strodbcConn = sr.ReadLine();
			
			sr.Close(); // close ConnectList.txt

		}

		public void BuildConnectionString(string DataConnectionPath)
		{
			/*
			 *  Connect to MSAccess using OLEDB directly
			 */

			//			strAccOLEDBconn = "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=I:\\RO Design\\RO\\ROMaster.mdb;Mode=Share Deny None;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Database Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";

			strAccOLEDBconn = "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + DataConnectionPath +"\\ROMaster.mdb;Mode=Share Deny None;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Database Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";

			/*
			 *  Connect to SQL-server using OLEDB
			 */
			// !!!!! do we need the Workstation ID?  if so, we need to pass this in too!

			//			strSQLOLEDBconn = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Initial Catalog=ROMaster;Data Source=DELL-W2K-SERVER\\SQL2000;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=KATHY;Use Encryption for Data=False;Tag with column collation when possible=False";

			strSQLOLEDBconn = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Initial Catalog=ROMaster;Data Source=" + DataConnectionPath +"\\ROMaster.mdb;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=KATHY;Use Encryption for Data=False;Tag with column collation when possible=False";

			/*
			 *  Connect to MSAccess using OLEDB and ODBC DSN
			 */

			//			strAccODBCconn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Access for RO;Extended Properties=\"DSN=Access for RO;DBQ=I:\\RO Design\\RO\\ROMaster.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;\"";

			strAccODBCconn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Access for RO;Extended Properties=\"DSN=Access for RO;DBQ=" + DataConnectionPath + "\\ROMaster.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;\"";

			/* 
			 * Connection to MSAccess using an ODBC connection
			 */
			//			strodbcConn = "Driver={Microsoft Access Driver (*.mdb)};DBQ=i:\\Ro Design\\RO\\ROMaster.mdb";
			strodbcConn = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DataConnectionPath + "\\ROMaster.mdb";

			// Connection to SQL Server using an ODBC connection
			//strodbcConn = "Driver={SQL Server};Server=DELL-W2K-SERVER\\SQL2000;UID=sa;Database=ROMaster";

			//SQL - DSN - worked: string strodbcConn = "DSN=SQL RO;UID=sa";
			//Initial odbc tests on strodbcconn			
		}

		/*
		 * Get the next available RO Database RecID
		 */
		public String RODB_GetNextRecid(String DBTable)
		{
			String NextRecID="";
			String RtnStr = "";
			String SpcStr = " ";
			String ZeroStr = "0";
			String DbCmd = "SELECT Info FROM " + DBTable + " WHERE RecID = '00000000'";

			try
			{
				DBE.Command(DbCmd);
			}
			catch (Exception e)
			{
				success = false;
				Console.WriteLine("RODB_GetNextRecid - Read NextRecID Caught an exception:\n{0}", e.Message);
			}


			if (DBE.Reader())
			{
				if (DBE.Read())
				{
					RtnStr = DBE.GetString(0);
					DBE.ReaderClose();
				}
			}
			else
			{
				System.Windows.Forms.MessageBox.Show("Could Not Open Reader");
			}

			// Create the new next RecID
			if (RtnStr.CompareTo("") != 0)
			{
				uint nxID = Convert.ToUInt32(RtnStr,16);
				nxID++;
				NextRecID = String.Format("{0,8:X}",nxID);// 8 char field padded on left with blanks
				NextRecID = NextRecID.Replace(SpcStr,ZeroStr);
			}
			else
			{
				// there is no next RecID
				Console.WriteLine("RODB_GetNextRecid  No Next RecID!");
			}

			// Write the new next RecID
			DbCmd = "UPDATE " + DBTable + " SET " + DBTable + ".Info = '" + NextRecID + "'";
			DbCmd = DbCmd + " WHERE RecID = '00000000';";

			try
			{
				DBE.Command(DbCmd);
			}
			catch (Exception e)
			{
				success = false;
				Console.WriteLine("RODB_GetNextRecid - Write new NextRecID  Caught an exception:\n{0}", e.Message);
			}

			try
			{
				DBE.NonQuery();
			}
			catch (Exception e)
			{
				Console.WriteLine("NonQuery error:\n{0}", e.Message);
				//				bool retval = false;
			}
			DBE.CommandDispose();
			
			return RtnStr;
		}
	}
}