//what to do with the modify
public void modifyList(OCObject oC)
{
string input = "";
Console.WriteLine("Choices:");
Console.WriteLine("1. Add to the Report");
Console.WriteLine("2. Delete field from the Report");
Console.WriteLine("3. Reorder current Report");
Console.WriteLine("What kind of modification is to be done:");
input = Console.ReadLine();
switch (input)
{
case "1":
addReport(oC);
break;
case "2":
delReport(oC);
break;
case "3":
reorderReport(oC);
break;
default:
Console.WriteLine("Not a choice");
break;
}
}
private void delReport(OCObject oC)
{
Console.WriteLine("Input column to be Deleted: ex. (THEC_SSN)");
string delShorthand = Console.ReadLine().ToUpper();
int loc = oC.shortHands.IndexOf(delShorthand);
if (loc == -1)
{
Console.WriteLine("Column not found");
}
else
{
if (oC.queries[loc].Contains(":" + delShorthand))
{
oC.shortHands.RemoveAt(loc);
oC.queries.RemoveAt(loc);
Console.WriteLine("Removed Column");
}
else
{
for (int i = 0; i < oC.queries.Count; i++)
{
if (oC.queries[i].Contains(":" + delShorthand))
{
oC.shortHands.RemoveAt(i);
oC.queries.RemoveAt(i + 1);
Console.WriteLine("Removed Column");
break;
}
}
}
}
}
public List <List <string> > runReport(OCObject oC)
{
List <List <string> > results = new List <List <string> >();
List <string> nullLocations = new List <string>();
string ok = "n";
Console.WriteLine("This is the current number of queries: " + oC.shortHands.Count);
Console.Write("Would you like to list the current THEC list (y/n): ");
if (Console.ReadLine().ToLower() == "y")
{
shortReport(oC);
Console.WriteLine();
}
Console.Write("\nIs This OK? (y/n): ");
ok = Console.ReadLine();
if ((ok.ToLower() == "y") || (ok.ToLower() == "yes"))
{
try { results = oC.fullExecute(); }
catch (Exception e)
{
Console.WriteLine("Problem with main Execution\n");
throw e;
}
//TO be changed to function to make into a .csv file later
for (int i = 0; i < results.Count; i++)
{
//Console.WriteLine("Entry: ");
for (int j = 0; j < results[i].Count; j++)
{
if (results[i][j].ToLower() == "null")
{
nullLocations.Add((i + 1) + ", " + (j + 1));
}
}
}
//List all Nulls found and their location
foreach (string item in nullLocations)
{
string entry = item.Substring(0, item.IndexOf(','));
string loc = item.Substring(entry.Length + 2);
Console.Write("Null at row: " + entry);
Console.WriteLine(" and column: " + loc);
}
}
else
{
Console.WriteLine("Confirmed: No report ran.");
}
return(results);
}
public void sqlChoice(OCObject oC)
{
Console.Write("Type Test SQL funct (w/o ;): ");
string testSql = Console.ReadLine();
List <string> testResults = oC.generalSqlCommand(testSql);
foreach (string tt in testResults)
{
Console.WriteLine("Test result: " + tt.ToString());
}
}
public void runPLSQLFile(OCObject oC)
{
List <List <string> > Test = oC.fullExecute();
foreach (List <string> item in Test)
{
Console.WriteLine("Entry: ");
foreach (string i in item)
{
Console.WriteLine("Row: " + i);
}
}
}
/*
* public List<string> directGrab(string column, string table)
* {
* string sqlSelect = "SELECT " + column + " FROM " + table + ";";
* List<string> output = oCObject.generalSqlCommand(sqlSelect);
* return output;
* }
*
* public List<string> quickSelect(List<string> columns , string table)
* {
* string sqlSelect = "SELECT ";
* for (int i=0; i < (columns.Count-1); i++)
* {
* sqlSelect += columns[i] + ", ";
* }
* sqlSelect += columns[(columns.Count - 1)] + " FROM " + table + ";";
*
* List<string> output = oCObject.generalSqlCommand(sqlSelect);
* return output;
* }
*/
public void listTables(OCObject oC)
{
Console.WriteLine("Tables:");
string owner = "C##GHAY";
string tableSQL1 = "SELECT owner FROM all_tables WHERE owner IN ('" + owner + "')";
string tableSQL2 = "SELECT table_name FROM all_tables WHERE owner IN ('" + owner + "')";
List <string> testResults1 = oC.generalSqlCommand(tableSQL1);
List <string> testResults2 = oC.generalSqlCommand(tableSQL2);
for (int i = 0; i < testResults1.Count; i++)
{
Console.WriteLine(testResults1[i] + "/" + testResults2[i]);
}
}
public void shortReport(OCObject oC)
{
for (int i = 0; i < oC.shortHands.Count; i++)
{
if ((i % 3 == 2) || (i == oC.shortHands.Count - 1))
{
Console.WriteLine(oC.shortHands[i]);
}
else
{
Console.Write(oC.shortHands[i] + ", ");
}
}
//foreach (string item in oC.shortHands) { Console.WriteLine(item); }
}
public void runSQLFile(OCObject oC)
{
Console.Write("\nFileName: ");
string sqlFile = Console.ReadLine();
List <List <string> > testResults = oC.ScriptExec(sqlFile);
foreach (var i in testResults)
{
Console.WriteLine("------- Query ------");
foreach (string tt in i)
{
Console.WriteLine("Script Result: " + tt);
}
}
}
//Function to check if a column in table
public void checkCol(OCObject oC)
{
Console.Write("\nTable Name: ");
string table = Console.ReadLine();
Console.Write("Column Name: ");
string column = Console.ReadLine();
bool result = oC.columnExists(column, table);
if (result)
{
Console.WriteLine(column + " Exists in " + table);
}
else
{
Console.WriteLine("Does Not Exist");
}
}
private void addReport(OCObject oC)
{
string manual = "n";
Console.Write("Do you want to add the query manually? (y/n): ");
manual = Console.ReadLine().ToLower();
string newQuery = "";
if (manual == "y")
{
Console.WriteLine("Input the new query to add: ex. (select <Column> into :<VARI> from <Table> where ...)");
newQuery = Console.ReadLine();
}
else
{
templateType();
string ans = Console.ReadLine();
if (ans == "1")
{
newQuery = baseTemplate();
}
else if (ans == "2")
{
newQuery = ConditionTemplate();
}
else
{
Console.WriteLine("Not a reconized choice");
}
}
if (newQuery.Length > 5)
{
oC.queries.Add(newQuery);
oC.shortHands = oC.shorting(oC.queries)[0];
}
else
{
Console.WriteLine("No query added");
}
}
private void reorderReport(OCObject oC)
{
Console.WriteLine("Input column to be Moved: ex. (SSN)");
string movShorthand = Console.ReadLine().ToUpper();
int loc = oC.shortHands.IndexOf(movShorthand);
int pos;
//check if real shorthand
if (loc == -1)
{
Console.WriteLine("Column not found");
}
//if so
else
{
//what position to move to
Console.WriteLine("The column is in order: " + (loc + 1).ToString() + " out of " + oC.shortHands.Count.ToString());
Console.WriteLine("What position should it be moved to: ex. (" + oC.shortHands.Count.ToString() + ") for last");
//check if position is number
if (int.TryParse(Console.ReadLine(), out pos))
{
//if so, find shorthand in queries and remove, the replace in the list
if (oC.queries[loc].Contains(":" + movShorthand))
{
//remove and replace shorthands
string tempShorthand = oC.shortHands[loc];
oC.shortHands.RemoveAt(loc);
//insert needs to be checked
oC.shortHands.Insert(pos - 1, tempShorthand);
//remove and replace query
string tempQuery = oC.queries[loc];
oC.queries.RemoveAt(loc);
oC.queries.Insert(pos - 1, tempQuery);
Console.WriteLine("Moved Column");
}
//if so, but not in same order as the shorthand list find the remove/replace
else
{
for (int i = 0; i < oC.queries.Count; i++)
{
if (oC.queries[i].Contains(":" + movShorthand))
{
//remove and replace shorthands
string tempShorthand = oC.shortHands[loc];
oC.shortHands.RemoveAt(loc);
oC.shortHands.Insert(pos - 1, tempShorthand);
//remove and replace query
string tempQuery = oC.queries[loc];
oC.queries.RemoveAt(loc);
oC.queries.Insert(pos - 1, tempQuery);
Console.WriteLine("Moved Column");
break;
}
}
}
}
//if not a number
else
{
Console.WriteLine("That is not a Number.");
}
}
}
static void Main(string[] args)
{
string userId = "hr";
string password = "******";
string hostname = "localhost";
string portNum = "1521";
string serviceName = "ORCL.HAYWORKS";
string termcode = "201940";
//OCObject oC = new OCObject(userId, password, "localhost", "1521", "ORCL.HAYWORKS", termcode);
bool again = true;
Reporting report = new Reporting();
OCObject oC = new OCObject();
Console.WriteLine(structure);
Console.WriteLine("Welcome to the THECR Program");
Console.WriteLine(structure2);
Console.WriteLine(structure);
while (!oC.isConnect)
{
Console.Write("Do you need the manual login? (y/n): ");
string manual = Console.ReadLine();
Console.Write("Input username: "******"Input password: "******"y")
{
Console.Write("Input hostname: ");
hostname = Console.ReadLine();
Console.Write("Input port number: ");
portNum = Console.ReadLine();
Console.Write("Input service name: ");
serviceName = Console.ReadLine();
Console.WriteLine(structure2);
try { oC = new OCObject(userId, password, hostname, portNum, serviceName); }
catch (Exception e)
{
Console.WriteLine(report.connectionError(e.Message));
Console.WriteLine(e.Message);
}
}
else
{
Console.WriteLine(structure2);
try { oC = new OCObject(userId, password); }
catch (Oracle.ManagedDataAccess.Client.OracleException e)
{
Console.WriteLine(report.connectionError(e.Message));
Console.WriteLine(e.Message);
}
}
Console.WriteLine(structure2);
Console.Write("Input query file (default if empty): ");
string mFile = Console.ReadLine();
Console.Write("Input PIDM file (default if empty): ");
string sFile = Console.ReadLine();
Console.Write("Input termcode: ");
string term = Console.ReadLine();
if (mFile.Length > 0)
{
oC.masterFile = mFile;
}
if (sFile.Length > 0)
{
oC.sqlFile = sFile;
}
oC.termcode = term;
}
while (again)
{
menu();
string choice = "8";
Console.Write("What to do?: ");
choice = Console.ReadLine();
Console.WriteLine(structure2);
switch (choice)
{
case "1":
List <List <string> > results = new List <List <string> >();
Console.WriteLine(structure2);
try { results = report.runReport(oC); }
catch (Exception) { Console.WriteLine("Problem Running the Report"); }
if (results.Count > 0)
{
Console.WriteLine("Successfully made THEC Report");
Console.Write("Would you like to save the report to a *.csv file? (y/n): ");
string saving = Console.ReadLine().ToLower();
if (saving == "y")
{
Console.Write("What file name would like save the report: ");
string saveName = Console.ReadLine();
report.csvBuilder(results, saveName, oC.shortHands);
}
Console.Write("Would you like save the last used queries? (y/n): ");
saving = Console.ReadLine().ToLower();
if (saving == "y")
{
Console.Write("What file name would like save the queries under: ");
string saveName = Console.ReadLine();
report.saveReport(oC.ReportBlock, saveName);
}
}
else
{
Console.WriteLine("Report was empty");
}
break;
case "2":
Console.Write("Would you like to list the current THEC list (y/n): ");
if (Console.ReadLine().ToLower() == "y")
{
Console.WriteLine();
report.shortReport(oC);
Console.WriteLine();
}
report.modifyList(oC);
break;
case "3":
report.checkCol(oC);
break;
case "T":
case "t":
report.sqlChoice(oC);
break;
case "L":
case "l":
report.listTables(oC);
break;
case "F":
case "f":
report.runSQLFile(oC);
break;
default:
Console.WriteLine("default");
break;
}
Console.WriteLine(structure2);
Console.WriteLine("Again? (y/n): ");
string a = Console.ReadLine();
if (a.ToLower() == "y")
{
again = true;
}
else
{
again = false;
}
}
Console.WriteLine("Ending Program: Hit the Enter key to end");
Console.ReadLine();
}
