496 lines
15 KiB
C#
496 lines
15 KiB
C#
/*********************************************************************************************
|
|
* Copyright 2004 - Volian Enterprises, Inc. All rights reserved.
|
|
* Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
* ------------------------------------------------------------------------------
|
|
* $Workfile: vdb_TransUsage.cs $ $Revision: 7 $
|
|
* $Author: Kathy $ $Date: 5/01/06 11:17a $
|
|
*
|
|
* $History: vdb_TransUsage.cs $
|
|
*
|
|
* ***************** Version 7 *****************
|
|
* User: Kathy Date: 5/01/06 Time: 11:17a
|
|
* Updated in $/LibSource/VDB
|
|
* Fix B2006-018: single quote in proc number causes problem on data
|
|
* request
|
|
*
|
|
* ***************** Version 6 *****************
|
|
* User: Kathy Date: 8/16/05 Time: 2:54p
|
|
* Updated in $/LibSource/VDB
|
|
* B2005-030: error if missing ndx
|
|
*
|
|
* ***************** Version 5 *****************
|
|
* User: Kathy Date: 4/21/05 Time: 10:20a
|
|
* Updated in $/LibSource/VDB
|
|
* always write inf file
|
|
*
|
|
* ***************** Version 4 *****************
|
|
* User: Kathy Date: 3/08/05 Time: 1:48p
|
|
* Updated in $/LibSource/VDB
|
|
* Approval
|
|
*
|
|
* ***************** Version 3 *****************
|
|
* User: Jsj Date: 8/30/04 Time: 12:16p
|
|
* Updated in $/LibSource/VDB
|
|
* upper cased WHERE string did not find mixed cased database items, now
|
|
* use the WHERE string as it is passed in.
|
|
*
|
|
* ***************** Version 2 *****************
|
|
* User: Jsj Date: 8/23/04 Time: 10:13a
|
|
* 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;
|
|
using VDB_ConnType;
|
|
|
|
namespace VDB_TransUsage
|
|
{
|
|
/// <summary>
|
|
/// Open, Create, Delete, Update functions for Transition Usage files (tables)
|
|
/// </summary>
|
|
public class vdb_TransUsage:VDB_Base
|
|
{
|
|
public enum SortTypes {NotSorted,FromSort,ToSort}; // list of sorting types
|
|
|
|
public string TransFromNDX = "";
|
|
public string TransToNDX = "";
|
|
|
|
public vdb_TransUsage(String strPath):base(strPath)
|
|
{
|
|
// if no table (file name) specified, then default to TRAN (TRAN.DBF)
|
|
if (DBTable.Equals(""))
|
|
DBTable = "TRAN";
|
|
}
|
|
|
|
// 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 [FROMNUMBER],[FROMSEQUEN],[FROMINSTAN],[TYPE],");
|
|
tmp.Append("[TONUMBER],[TOSEQUENCE],[TOINSTANCE],[DTI],[OLDTO] 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
|
|
// When the WhereStr was upper cased, procedure numbers with mixed and/or lower
|
|
// cased letters would not be found. Therefore, use the WhereStr exactly as it
|
|
// was sent in.
|
|
// tmp.Append(tmpstr);
|
|
tmp.Append(WhereStr);
|
|
}
|
|
|
|
switch (SortingType)
|
|
{
|
|
case 0: //NotSorted
|
|
break;
|
|
case 1: // FromSort - sorted by FROMNUMBER
|
|
tmp.Append(" GROUP BY [FROMNUMBER],[FROMSEQUEN],[FROMINSTAN],[TYPE],");
|
|
tmp.Append("[TONUMBER],[TOSEQUENCE],[TOINSTANCE],[DTI],[OLDTO]");
|
|
break;
|
|
case 2: // Tosort - sorted by TONUMBER
|
|
tmp.Append(" GROUP BY [TONUMBER],[TOSEQUENCE],[TOINSTANCE],");
|
|
tmp.Append("[FROMNUMBER],[FROMSEQUEN],[FROMINSTAN],[TYPE],[DTI],[OLDTO]");
|
|
break;
|
|
}
|
|
SelectCmd = tmp.ToString();
|
|
}
|
|
|
|
|
|
private void BuildNDXFileNames()
|
|
{
|
|
TransFromNDX = DBTable +"FROM";
|
|
TransToNDX = DBTable + "TO";
|
|
}
|
|
|
|
|
|
// Build the SQL command needed to update a row in the TRAN file
|
|
public override string GetRecUpdateStr(DataTable tbl,DataRow row)
|
|
{
|
|
int j = 0;
|
|
bool FirstOne = true;
|
|
StringBuilder updateStr = new StringBuilder();
|
|
string strNumber = "";
|
|
string strSequence = "";
|
|
string strInstance = "";
|
|
|
|
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("FROMNUMBER"))
|
|
{
|
|
strNumber = row.ItemArray[j].ToString();
|
|
strNumber = strNumber.Replace("'","''");
|
|
}
|
|
else if (col.ColumnName.Equals("FROMSEQUEN"))
|
|
{
|
|
strSequence = row.ItemArray[j].ToString();
|
|
strSequence = strSequence.Replace("'","''");
|
|
}
|
|
else if (col.ColumnName.Equals("FROMINSTAN"))
|
|
{
|
|
strInstance = row.ItemArray[j].ToString();
|
|
strInstance = strInstance.Replace("'","''");
|
|
}
|
|
|
|
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("'");
|
|
}
|
|
j++;
|
|
}
|
|
// Add the WHERE clause
|
|
updateStr.Append(" WHERE [FROMNUMBER] = '");
|
|
updateStr.Append(strNumber.ToString());
|
|
updateStr.Append("' AND [FROMSEQUEN] = '");
|
|
updateStr.Append(strSequence.ToString());
|
|
updateStr.Append("' AND [FROMINSTAN] = '");
|
|
updateStr.Append(strInstance.ToString());
|
|
updateStr.Append("'");
|
|
return updateStr.ToString();
|
|
}
|
|
|
|
|
|
// Build the SQL command needed to insert a row in the TRAN 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
|
|
{
|
|
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 TRAN file
|
|
public override string RecDeleteStr(DataTable tbl,DataRow row)
|
|
{
|
|
int j = 0;
|
|
string strNumber = "";
|
|
string strSequence = "";
|
|
string strInstance = "";
|
|
StringBuilder deleteStr = new StringBuilder();
|
|
|
|
deleteStr.Append("DELETE FROM [");
|
|
deleteStr.Append(DBTable);
|
|
deleteStr.Append("]");
|
|
|
|
row.RejectChanges();
|
|
foreach (DataColumn col in tbl.Columns)
|
|
{
|
|
if (col.ColumnName.Equals("FROMNUMBER"))
|
|
{
|
|
strNumber = row.ItemArray[j].ToString();
|
|
strNumber = strNumber.Replace("'","''");
|
|
}
|
|
else if (col.ColumnName.Equals("FROMSEQUEN"))
|
|
{
|
|
strSequence = row.ItemArray[j].ToString();
|
|
strSequence = strSequence.Replace("'","''");
|
|
}
|
|
else if (col.ColumnName.Equals("FROMINSTAN"))
|
|
{
|
|
strInstance = row.ItemArray[j].ToString();
|
|
strInstance = strInstance.Replace("'","''");
|
|
}
|
|
j++;
|
|
}
|
|
// Add the WHERE clause
|
|
deleteStr.Append(" WHERE [FROMNUMBER] = '");
|
|
deleteStr.Append(strNumber.ToString());
|
|
deleteStr.Append("' AND [FROMSEQUEN] = '");
|
|
deleteStr.Append(strSequence.ToString());
|
|
deleteStr.Append("' AND [FROMINSTAN] = '");
|
|
deleteStr.Append(strInstance.ToString());
|
|
deleteStr.Append("'");
|
|
|
|
return deleteStr.ToString();
|
|
|
|
}
|
|
|
|
public override string GetCreateIndx1String()
|
|
{
|
|
StringBuilder index1Str = new StringBuilder();
|
|
index1Str.Append("CREATE INDEX [");
|
|
index1Str.Append(TransFromNDX);
|
|
index1Str.Append("] ON ");
|
|
index1Str.Append(DBTable);
|
|
index1Str.Append(".DBF ([FROMNUMBER],[FROMSEQUEN],[FROMINSTAN])");
|
|
return (index1Str.ToString());
|
|
}
|
|
|
|
public override string GetCreateIndx2String()
|
|
{
|
|
StringBuilder index2Str = new StringBuilder();
|
|
index2Str.Append("CREATE INDEX [");
|
|
index2Str.Append(TransToNDX);
|
|
index2Str.Append("] ON ");
|
|
index2Str.Append(DBTable);
|
|
index2Str.Append(".DBF ([TONUMBER], [TOSEQUENCE])");
|
|
return (index2Str.ToString());
|
|
}
|
|
|
|
public override bool IndexFilesExist()
|
|
{
|
|
if (TransFromNDX==""||TransToNDX=="")BuildNDXFileNames();
|
|
if(!File.Exists(DBPath+"\\"+TransFromNDX+".NDX") || !File.Exists(DBPath+"\\"+TransToNDX+".NDX")) return false;
|
|
return true;
|
|
}
|
|
public override void DeleteIndexFiles()
|
|
{
|
|
if(File.Exists(DBPath+"\\"+TransFromNDX+".NDX")) File.Delete(DBPath+"\\"+TransFromNDX+".NDX");
|
|
if(File.Exists(DBPath+"\\"+TransToNDX+".NDX")) File.Delete(DBPath+"\\"+TransToNDX+".NDX");
|
|
if(File.Exists(DBPath+"\\"+DBTable+".INF")) File.Delete(DBPath+"\\"+DBTable+".INF");
|
|
}
|
|
|
|
public override StringCollection CreateIndexFilesStatements()
|
|
{
|
|
if (isdBaseFile())
|
|
{
|
|
StringCollection rtnStrColl = new StringCollection();
|
|
string indx1 = GetCreateIndx1String();
|
|
rtnStrColl.Add(indx1);
|
|
string indx2 = GetCreateIndx2String();
|
|
rtnStrColl.Add(indx2);
|
|
return rtnStrColl;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
// Build a list of SQL commands needed to create a new TRAN table (file)
|
|
// and add the first row
|
|
public override StringCollection CreateTableStatements(string strTblName)
|
|
{
|
|
StringCollection rtnStrColl = new StringCollection();
|
|
StringBuilder createStr = new StringBuilder();
|
|
//StringBuilder index1Str = new StringBuilder();
|
|
//StringBuilder index2Str = 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("([FROMNUMBER] Char(20), [FROMSEQUEN] Char(12), ");
|
|
createStr.Append("[FROMINSTAN] Char(1), [TYPE] Char(1),");
|
|
createStr.Append("[TONUMBER] Char(20), [TOSEQUENCE] Char(12), ");
|
|
createStr.Append("[TOINSTANCE] Char(2), [DTI] Char(18), [OLDTO] Char(32))");
|
|
|
|
rtnStrColl.Add(createStr.ToString()); // add create table
|
|
|
|
if (isdBaseFile())
|
|
{
|
|
// Build the command that creates the first index file
|
|
rtnStrColl.Add(GetCreateIndx1String()); // add create 1st index
|
|
|
|
// Build the commmand that creates the second index file
|
|
//string tmp = DBTable;
|
|
|
|
rtnStrColl.Add(GetCreateIndx2String()); // add create 2nd index
|
|
}
|
|
|
|
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();
|
|
|
|
// if the INF file does not already exist create it
|
|
//if (!File.Exists(infFileStr))
|
|
//{
|
|
// always recreate it. Some plants' data had invalid files
|
|
// and it was felt that it would be quicker to recreate always
|
|
// rather than
|
|
infFile = new StreamWriter(infFileStr,false);
|
|
infFile.Write("NDX1=");
|
|
infFile.Write(TransFromNDX); // TRANFROM.NDX
|
|
infFile.Write(".NDX\r\n");
|
|
infFile.Write("NDX2=");
|
|
infFile.Write(TransToNDX); //TRANTO.NDX
|
|
infFile.Write(".NDX\r\n");
|
|
infFile.Close();
|
|
//}
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
// 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 Transition Usage records, not sorted
|
|
public System.Data.DataSet GetNotSorted(string WhereStr)
|
|
{
|
|
BuildSelectCommand((int)SortTypes.NotSorted,WhereStr);
|
|
return DB_Data;
|
|
}
|
|
|
|
// return a dataset or Transition Usage records, sorted by the [FROMNUMBER] field
|
|
public System.Data.DataSet GetSortedByFromTrans(string WhereStr)
|
|
{
|
|
BuildSelectCommand((int)SortTypes.FromSort,WhereStr);
|
|
return DB_Data;
|
|
}
|
|
|
|
// return a dataset or Transition Usage records, sorted by the [TONUMBER] field
|
|
public System.Data.DataSet GetSortedByToTrans(string WhereStr)
|
|
{
|
|
BuildSelectCommand((int)SortTypes.ToSort,WhereStr);
|
|
return DB_Data;
|
|
}
|
|
|
|
public void UpdateProcNumber(string oldnum, string newnum)
|
|
{
|
|
// Change fromnumber first, then tonumber
|
|
StringBuilder updateStr = new StringBuilder();
|
|
updateStr.Append("UPDATE [");
|
|
updateStr.Append(DBTable);
|
|
updateStr.Append("] SET [FROMNUMBER] = '");
|
|
updateStr.Append(newnum.Replace("'","''"));
|
|
updateStr.Append("' WHERE [FROMNUMBER] = '");
|
|
updateStr.Append(oldnum.Replace("'","''"));
|
|
updateStr.Append("'");
|
|
ProcessACommand(updateStr.ToString());
|
|
|
|
// now to...
|
|
updateStr.Remove(0,updateStr.Length);
|
|
updateStr.Append("UPDATE [");
|
|
updateStr.Append(DBTable);
|
|
updateStr.Append("] SET [TONUMBER] = '");
|
|
updateStr.Append(newnum.Replace("'","''"));
|
|
updateStr.Append("' WHERE [TONUMBER] = '");
|
|
updateStr.Append(oldnum.Replace("'","''"));
|
|
updateStr.Append("'");
|
|
ProcessACommand(updateStr.ToString());
|
|
}
|
|
}
|
|
}
|