/*********************************************************************************************
 * Copyright 2004 - Volian Enterprises, Inc. All rights reserved.
 * Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
 * ------------------------------------------------------------------------------
 * $Workfile: vdb_Proc.cs $     $Revision: 11 $
 * $Author: Kathy $   $Date: 9/23/05 8:33a $
 *
 * $History: vdb_Proc.cs $
 * 
 * *****************  Version 11  *****************
 * User: Kathy        Date: 9/23/05    Time: 8:33a
 * Updated in $/LibSource/VDB
 * Fix B2005-043 - cannot select into for files with a '-' (dash).
 * 
 * *****************  Version 10  *****************
 * User: Jsj          Date: 9/16/05    Time: 9:54a
 * Updated in $/LibSource/VDB
 * handle dbase NDX file names that begin with a number.  SQL statement
 * could not create a NDX file that begins with a number.
 * 
 * *****************  Version 9  *****************
 * User: Kathy        Date: 8/16/05    Time: 2:53p
 * Updated in $/LibSource/VDB
 * B2005-030: error if missing ndx
 * 
 * *****************  Version 8  *****************
 * User: Kathy        Date: 7/28/05    Time: 2:06p
 * Updated in $/LibSource/VDB
 * for mod proc number, select into correct destination file
 * 
 * *****************  Version 7  *****************
 * User: Kathy        Date: 5/19/05    Time: 11:05a
 * Updated in $/LibSource/VDB
 * speed up approve
 * 
 * *****************  Version 6  *****************
 * User: Jsj          Date: 5/17/05    Time: 9:39a
 * Updated in $/LibSource/VDB
 * 
 * *****************  Version 5  *****************
 * User: Kathy        Date: 5/11/05    Time: 9:28a
 * Updated in $/LibSource/VDB
 * add selectinto support
 * 
 * *****************  Version 4  *****************
 * User: Kathy        Date: 4/21/05    Time: 10:19a
 * Updated in $/LibSource/VDB
 * always write inf file
 * 
 * *****************  Version 3  *****************
 * User: Kathy        Date: 3/08/05    Time: 1:47p
 * Updated in $/LibSource/VDB
 * Approval
 * 
 * *****************  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.IO;
using System.Windows.Forms;
using VDB;

namespace VDB_Proc
{
	/// <summary>
	/// Summary description for vdb_Proc.
	/// </summary>
	public class vdb_Proc:VDB_Base
	{
		public enum SortTypes {NotSorted,StepSeqTypeSort,RECIDsort}; // list of sorting types
		public string ProcNDX = "";
		public string TmpNDXFileName = "";

		public vdb_Proc(String strPath):base(strPath)
		{
		}

		public string ProcGetTextFromMemo(string RecId)
		{
			return GetMemo(RecId);
		}

		// Build a SQL statement. Optionally sort and filter the select statement.
		private void BuildSelectCommand(int SortingType,string WhereStr)
		{
			StringBuilder tmp = new StringBuilder();
			tmp.Append("SELECT [STEP], [SEQUENCE], [TEXT], [TEXTM], [TYPE], [DATE], [TIME], [INITIALS], [RECID] FROM [");
			tmp.Append(DBTable);
			tmp.Append("]");
			
			if (WhereStr.Length > 0) // Was a WHERE clause passed in?
			{
				// Add " WHERE " to the beginning if it is not already there
				string tmpstr = WhereStr.ToUpper();
				if (tmpstr[0] != ' ')
				{
					tmp.Append(" ");
					if (!tmpstr.StartsWith("WHERE "))
						tmp.Append("WHERE ");
				}
				else if (!tmpstr.StartsWith(" WHERE "))
					tmp.Append(" WHERE");
				// add the passed in WHERE clause
				tmp.Append(tmpstr);
			}

			switch (SortingType)
			{
				case 0: //NotSorted
					break;
				case 1: // StepSeqTypeSort - sorted by STEP+SEQUENCE+TYPE
					tmp.Append(" GROUP BY [STEP],[SEQUENCE],[TYPE],[TEXT],[TEXTM],[DATE],[TIME],[INITIALS],[RECID]");
					break;
				case 2: // RECIDsort - sorted by RECID
					tmp.Append(" GROUP BY [RECID],[STEP],[SEQUENCE],[TYPE],[TEXT],[TEXTM],[DATE],[TIME],[INITIALS]");
					break;
			}

			SelectCmd = tmp.ToString();		
		}

#if Upgrade2005_Print
		// Build a SQL statement for record count on select.
		private void BuildCountCommand(string WhereStr)
		{
			StringBuilder tmp = new StringBuilder();
			tmp.Append("SELECT COUNT(*) FROM [");
			tmp.Append(DBTable);
			tmp.Append("]");
			
			if (WhereStr.Length > 0) // Was a WHERE clause passed in?
			{
				// Add " WHERE " to the beginning if it is not already there
				string tmpstr = WhereStr.ToUpper();
				if (tmpstr[0] != ' ')
				{
					tmp.Append(" ");
					if (!tmpstr.StartsWith("WHERE "))
						tmp.Append("WHERE ");
				}
				else if (!tmpstr.StartsWith(" WHERE "))
					tmp.Append(" WHERE");
				// add the passed in WHERE clause
				tmp.Append(tmpstr);
			}

			SelectCmd = tmp.ToString();		
		}
#endif
		private void BuildNDXFileNames()
		{
			ProcNDX = DBTable;
		}


		// Build the SQL command needed to update a row in the Procedure 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("STEP") && 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 [STEP] IS NULL");
			}
			else
			{
				updateStr.Append(" WHERE [RECID] LIKE '");
				updateStr.Append(LikeRecID.ToString());
				updateStr.Append("'");
			}
			return updateStr.ToString();
		}


		// Build the SQL command needed to insert a row in the Procedure 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 Procedure file
		public override string RecDeleteStr(DataTable tbl,DataRow row)
		{
			int j = 0;
			String RecIDStr = "";
			String StepStr = "";
			String SeqStr = "";
			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("STEP"))
				{
					StepStr = row.ItemArray[j].ToString();
				}
				else if (col.ColumnName.Equals("SEQUENCE"))
				{
					SeqStr = row.ItemArray[j].ToString();
				}
				else if (col.ColumnName.Equals("RECID"))
				{
					RecIDStr = row.ItemArray[j].ToString();
				}
				j++;
			}
			// we might want to change this to delete via only the RECID
			// but that could be risky if we have duplicate RECIDs 
			deleteStr.Append("[STEP] = '");
			deleteStr.Append(StepStr);
			deleteStr.Append("' AND [SEQUENCE] = '");
			deleteStr.Append(SeqStr);
			deleteStr.Append("' AND [RECID] = '");
			deleteStr.Append(RecIDStr);
			deleteStr.Append("'");

			return deleteStr.ToString();

		}

		public override string GetCreateIndx1String()
		{
			StringBuilder index1Str = new StringBuilder();
			// Make a temporary index file name if dBase filename
			// for any procedure. (those that start with a number
			// or had a dash were having a problem here - see
			// B2005-040 & 043).
			TmpNDXFileName = "ndxtmp";
			index1Str.Append("CREATE INDEX [");
			index1Str.Append(TmpNDXFileName);
			index1Str.Append("] ON [");
			index1Str.Append(DBTable);
			index1Str.Append("] ([STEP],[SEQUENCE],[TYPE])");
			return (index1Str.ToString());
		}

		public override bool IndexFilesExist()
		{
			if (ProcNDX == "") BuildNDXFileNames();
			if(File.Exists(DBPath+"\\"+ProcNDX+".NDX")) return true;
			return false;
		}

		public override void DeleteIndexFiles()
		{
			if(File.Exists(DBPath+"\\"+ProcNDX+".NDX")) File.Delete(DBPath+"\\"+ProcNDX+".NDX");
		}

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

			if (strTblName.Length > 0) // was a table name passd in?
			{
				DBTable = strTblName; // this will create the INI file for dBase
			}

			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("([STEP] Char(2), [SEQUENCE] Char(10), [TEXT] Char(100), ");
			createStr.Append("[TEXTM] TEXT, [TYPE] Char(2), [DATE] Date, [TIME] Char(5), ");
			createStr.Append("INITIALS Char(5), [RECID] Char(8))");

			rtnStrColl.Add(createStr.ToString()); // add create table

			// If we are using dBase files, create the index files too

			if (isdBaseFile())
			{
				// Build the command that creates the index file
				
				rtnStrColl.Add(GetCreateIndx1String()); // add create index
			}
			return rtnStrColl;
		}

		// Build a list of SQL commands needed to create the first row (a.k.a record zero)
		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 insert first record
			return rtnStrColl;
		}

		// make statement to select into a new table. Table must not exist and
		// can include a path to the table.
		public override StringCollection SelectIntoStatements(string destdb)
		{
			StringCollection rtnStrColl = new StringCollection();
			StringBuilder str = new StringBuilder();
			str.Append("SELECT * INTO [");
			str.Append(destdb.Substring(destdb.LastIndexOf('\\')+1,destdb.Length-destdb.LastIndexOf('\\')-1));
			str.Append("] IN '");
			str.Append(destdb.Substring(0,destdb.LastIndexOf('\\')));
			str.Append("' 'dBase III;' from [");
			str.Append(DBTable);
			str.Append("]");
			rtnStrColl.Add(str.ToString());
			return rtnStrColl;
		}

		// After the table is created, create the associated INF file
		public override int PostCreateTableFunction()
		{
			CreateINF();  // should already be created
			return 0;
		}

		// This will be called from CreateTableStatements()
		// and this is called if the table name is assigned to the DBTable property
		public override int CreateINF()
		{
			// if we are using dBase files, create an INF file containing
			// a list of the associated NDX files.
			if (isdBaseFile())
			{
				StreamWriter infFile;
				string infFileStr;
				StringBuilder tmp = new StringBuilder();

				// build the ndx file names (w/o extensions)
				BuildNDXFileNames();

				//build INF file name
				tmp.Append(DBPath);
				tmp.Append("\\");
				tmp.Append(DBTable);
				tmp.Append(".INF");

				infFileStr = tmp.ToString();

				// always recreate it. Some plants' data had invalid files
				// and it was felt that it would be quicker to recreate always
				// rather than read and check.
				infFile = new StreamWriter(infFileStr,false);
				infFile.Write("NDX1=");
				infFile.Write(ProcNDX);
				infFile.Write(".NDX\r\n");
				infFile.Close();

				// a temp index file name was always used to create the index
				// because unusual file names, such as those beginning with
				// a number or those with a '-' caused an error to occur
				// during ndx file creation. This fixes, B2005-040 & B2005-043.
				if (TmpNDXFileName.Length > 0)
				{
					File.Copy(TmpNDXFileName+".NDX",ProcNDX+".NDX");
					File.Delete(TmpNDXFileName+".NDX");
					TmpNDXFileName = "";
				}
			}
			return 0;
		}

		public override StringCollection CreateIndexFilesStatements()
		{
			if (isdBaseFile())
			{
				StringCollection rtnStrColl = new StringCollection();
				string indx1 = GetCreateIndx1String();
				rtnStrColl.Add(indx1);
				return rtnStrColl;
			}
			return null;
		}

		// 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;
		}

		// return a dataset of procedure records, not sorted
		public System.Data.DataSet GetNotSorted(string WhereStr)
		{
			BuildSelectCommand((int)SortTypes.NotSorted,WhereStr);
			return DB_Data;
		}

		// return a dataset of procedure records, sorted by the [STEP] field
		public System.Data.DataSet GetSortedByStepSeqType(string WhereStr)
		{
			BuildSelectCommand((int)SortTypes.StepSeqTypeSort,WhereStr);
			return DB_Data;
		}

		// return a dataset of procedure records, sorted by the [RECID] field
		public System.Data.DataSet GetSortedByRECID(string WhereStr)
		{
			BuildSelectCommand((int)SortTypes.RECIDsort,WhereStr);
			return DB_Data;
		}

#if Upgrade2005_Print
		// return integer count of selected records only
		public int GetCount(string WhereStr)
		{
			BuildCountCommand(WhereStr);
			DataSet ds = DB_Data;
			return ds.Tables[0].Rows.Count;
		}
#endif
	}
}