/*********************************************************************************************
 * Copyright 2004 - Volian Enterprises, Inc. All rights reserved.
 * Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
 * ------------------------------------------------------------------------------
 * $Workfile: vdb_Set.cs $     $Revision: 2 $
 * $Author: Jsj $   $Date: 8/23/04 10:12a $
 *
 * $History: vdb_Set.cs $
 * 
 * *****************  Version 2  *****************
 * User: Jsj          Date: 8/23/04    Time: 10:12a
 * Updated in $/LibSource/VDB
 * Fixed replace of single quote
 * 
 * *****************  Version 1  *****************
 * User: Kathy        Date: 7/27/04    Time: 8:40a
 * Created in $/LibSource/VDB
 *********************************************************************************************/

using System;
using System.Data;
using System.Text;
using System.Collections.Specialized;
using System.Windows.Forms;
using VDB;

namespace VDB_Set
{
	/// <summary>
	/// Open, Create, Delete, Update functions for SET files (tables)
	/// </summary>
	public class vdb_Set:VDB_Base
	{
		public vdb_Set(String strPath):base(strPath)
		{
			// if no table (file name) specified, then default to SET (SET.DBF)
			if (DBTable.Equals(""))
				DBTable = "SET";
		}

		// Build the SQL command needed to update a row in the SET file
		public override string GetRecUpdateStr(DataTable tbl,DataRow row)
		{
			int j = 0;
			bool FirstOne = true;
			StringBuilder updateStr = new StringBuilder();
			StringBuilder LikeRecID = new StringBuilder();
			bool NullEntry = false;

			updateStr.Append("UPDATE [");
			updateStr.Append(DBTable);
			updateStr.Append("] SET");

			foreach (DataColumn col in tbl.Columns)
			{
				bool isString = (col.DataType == Type.GetType("System.String"));
				if (col.ColumnName.Equals("ENTRY") && row.ItemArray[j].ToString().Equals(""))
				{
					NullEntry = true;
					j++;
					continue;
				}
				if (col.ColumnName.Equals("RECID"))
				{
					LikeRecID.Append("___"); // ignore the first 3 positions
					LikeRecID.Append(row.ItemArray[j].ToString(),3,5);
				}
				if (FirstOne)
				{
					updateStr.Append(" [");
					FirstOne = false;
				}
				else
					updateStr.Append(", [");
				updateStr.Append(col.ColumnName);
				updateStr.Append("]=");

				if (row.ItemArray[j].ToString().Equals(""))
				{
					updateStr.Append("NULL");
				}
				else if (isString)
				{
					String tmpstr = row.ItemArray[j].ToString();
					updateStr.Append("'");
					tmpstr = tmpstr.Replace("'","''");
					updateStr.Append(tmpstr);
					updateStr.Append("'");
				}
				else if (col.DataType == Type.GetType("System.DateTime"))
				{
					int jj;
					String tmpstr = row.ItemArray[j].ToString();
					updateStr.Append("'");
					jj=tmpstr.IndexOf(" ");
					if (jj<0) jj = tmpstr.Length;
					updateStr.Append(tmpstr.Substring(0,jj));
					updateStr.Append("'");
				}
				j++;
			}
			if (NullEntry)
			{
				// So that we can change the RECID number on the first entry
				updateStr.Append(" WHERE [ENTRY] IS NULL");
			}
			else
			{
				updateStr.Append(" WHERE [RECID] LIKE '");
				updateStr.Append(LikeRecID.ToString());
				updateStr.Append("' AND [ENTRY] IS NOT NULL");
			}
			return updateStr.ToString();
		}


		// Build the SQL command needed to insert a row in the SET file
		public override string GetRecInsertStr(DataTable tbl,DataRow row)
		{
			int j = 0;
			StringBuilder insrtStr = new StringBuilder();
			StringBuilder valueStr = new StringBuilder();

			insrtStr.Append("INSERT INTO [");
			insrtStr.Append(DBTable);
			insrtStr.Append("] (");

			valueStr.Append(" VALUES (");

			foreach (DataColumn col in tbl.Columns)
			{
				bool isString = (col.DataType == Type.GetType("System.String"));
				insrtStr.Append("[");
				insrtStr.Append(col.ColumnName);
				insrtStr.Append("],");

				if (row.ItemArray[j].ToString().Equals(""))
				{
					valueStr.Append("NULL");
				}
				else if (isString)
				{
					String tmpstr = row.ItemArray[j].ToString();
					tmpstr = tmpstr.Replace("'","''");
					valueStr.Append("'");
					valueStr.Append(tmpstr);
					valueStr.Append("'");
				}
				else if (col.DataType == Type.GetType("System.DateTime"))
				{
					int jj;
					String tmpstr = row.ItemArray[j].ToString();
					valueStr.Append("'");
					jj = tmpstr.IndexOf(" ");
					if (jj<0) jj=tmpstr.Length;
					valueStr.Append(tmpstr.Substring(0,jj));
					valueStr.Append("'");
				}
				else
				{
					valueStr.Append(row.ItemArray[j].ToString());
				}
				valueStr.Append(",");
				j++;
			}

			insrtStr = insrtStr.Replace(',',')',insrtStr.Length-1,1);
			valueStr = valueStr.Replace(',',')',valueStr.Length-1,1);
			insrtStr.Append(valueStr.ToString());

			return insrtStr.ToString();
		}

		// Build the SQL command needed to delete a row in the SET file
		public override string RecDeleteStr(DataTable tbl,DataRow row)
		{
			int j = 0;
			String RecIDStr = "";
			String NumberStr = "";
			StringBuilder deleteStr = new StringBuilder();

			deleteStr.Append("DELETE FROM [");
			deleteStr.Append(DBTable);
			deleteStr.Append("] WHERE ");

			row.RejectChanges();
			foreach (DataColumn col in tbl.Columns)
			{
				if (col.ColumnName.Equals("NUMBER"))
				{
					NumberStr = row.ItemArray[j].ToString();
					NumberStr = NumberStr.Replace("'","''");
				}
				else if (col.ColumnName.Equals("RECID"))
				{
					RecIDStr = row.ItemArray[j].ToString();
				}
				j++;
			}
			deleteStr.Append("[NUMBER] = '");
			deleteStr.Append(NumberStr);
			deleteStr.Append("' AND [RECID] = '");
			deleteStr.Append(RecIDStr);
			deleteStr.Append("' AND [ENTRY] IS NOT NULL");

			return deleteStr.ToString();

		}

		// Build a list of SQL commands needed to create a new SET table (file)
		// and add the first row
		public override StringCollection CreateTableStatements(string strTblName)
		{
			StringCollection rtnStrColl = new StringCollection();
			StringBuilder createStr = new StringBuilder();
//			StringBuilder firstRecStr = new StringBuilder();

			if (strTblName.Length > 0) // was a table name passd in?
			{
				DBTable = strTblName;
			}

			if (DBTable.Equals(""))
			{
				MessageBox.Show("Trying to Create a new Table without a Name","Create Table Error");
				return rtnStrColl;
			}

			// Build the command that creates a new SET table (file)
			createStr.Append("CREATE TABLE [");
			createStr.Append(DBTable);
			createStr.Append("] ");
			createStr.Append("([TITLE] Char(250), [NUMBER] Char(20), [FORMAT] Char(1), ");
			createStr.Append("[SUBDIR] Char(1), [ENTRY] Char(30), [RECID] Char(8), ");
			createStr.Append("[PROCCODE] Char(1), [DATE] Date, [TIME] Char(5), ");
			createStr.Append("INITIALS Char(5), SERIES Char(100))");

//			// Build the command that adds the first (blank) record 
//			// with the RECID initialize to 00000001
//			firstRecStr.Append("INSERT INTO [");
//			firstRecStr.Append(DBTable);
//			firstRecStr.Append("] ([RECID]) VALUES ('00000001')");

			rtnStrColl.Add(createStr.ToString()); // add create table
//			rtnStrColl.Add(firstRecStr.ToString()); // add inserst first record

			return rtnStrColl;
		}

		// Build a list of SQL commands needed to create the first data row
		public override StringCollection CreateFirstRecordStatement(string strTblName)
		{
			StringCollection rtnStrColl = new StringCollection();
			StringBuilder firstRecStr = new StringBuilder();

			// Build the command that adds the first (blank) record 
			// with the RECID initialize to 00000001
			firstRecStr.Append("INSERT INTO [");
			firstRecStr.Append(DBTable);
			firstRecStr.Append("] ([RECID]) VALUES ('00000001')");

			rtnStrColl.Add(firstRecStr.ToString()); // add inserst first record

			return rtnStrColl;
		}

		// return a list of SQL commands that will drop (delete) a database table (file)
		public override StringCollection DeleteTableStatements(string strTblName)
		{
			StringCollection rtnStrColl = new StringCollection();
			StringBuilder DropTableStr = new StringBuilder();

			DropTableStr.Append("Drop Table [");
			DropTableStr.Append(DBTable);
			DropTableStr.Append("]");

			rtnStrColl.Add(DropTableStr.ToString());
			return rtnStrColl;
		}

	}
		
}