private void btnGetLog_Click(object sender, EventArgs e)
{
//ceonnect to the database
try
{
connection = new iDB2Connection("DataSource=deathstar.gtc.edu;DefaultCollection=FLIGHT2019");
command = connection.CreateCommand();
//grab the data
command.CommandText = "SELECT * FROM MAINTLOG "
+ "WHERE PLANENO = '" + txtPlaneNum.Text.ToUpper() + "'";
connection.Open();
dataReader = command.ExecuteReader();
//Display to listbox
if (dataReader.Read())
{
listBox1.Items.Add("Log Number: " + dataReader.GetString(1));
listBox1.Items.Add("Task ID: " + dataReader.GetString(2));
listBox1.Items.Add("Maintence Start Date: " + dataReader.GetString(3));
listBox1.Items.Add("Maintence End Date: " + dataReader.GetString(4));
listBox1.Items.Add("Status: " + dataReader.GetString(5));
listBox1.Items.Add("Maintence Cost: " + dataReader.GetString(6));
}
else
{
txtPlaneNum.Text = "No results found for specified Plane Number id.";
}
dataReader.Close();
}
catch (Exception ex) { txtPlaneNum.Text = ex.Message; }
}
private void btnGetTask_Click(object sender, EventArgs e)
{
try
{
connection = new iDB2Connection("DataSource=deathstar.gtc.edu");
command = connection.CreateCommand();
command.CommandText =
"SELECT taskDesc " +
"FROM Tasks T JOIN WorkOrder WO ON T.taskId = WO.taskId " +
"WHERE WO.orderId = '" + txtWorkOrder.Text.ToUpper() + "'";
connection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
txtTask.Text = reader.GetString(0);
}
else
{
txtTask.Text = "No results found for specified work order id.";
}
reader.Close();
}
catch (Exception ex) { txtTask.Text = ex.Message; }
}
/// <summary>
/// Returns downloaded Dictioanry with UPC codes matching items.
/// </summary>
public Dictionary <string, string> DownloadUpcForItemsAsync(BindableCollection <IpgModel> _ipgsCollection)
{
Console.WriteLine("Trying to connect to Reflex for downloading UPC codes...");
// Dictionary for holding Ean to Upc map.
Dictionary <string, string> Ean_Upc = new Dictionary <string, string>();
// Preparing a formatted list of items.
string _items = ConcatenateItemsIntoList(_ipgsCollection);
try
{
conn.Open();
if (conn != null)
{
Console.WriteLine("Successfully connected to Reflex for downloading UPC codes");
// Below are DB2 functions needed for executing query
string _queryString = $"SELECT VICART, VICIVL FROM {Environment}.HLVLIDP WHERE VICART IN {_items} and VICTYI = 'EAN_1' Order by VICIVL ";
iDB2Command comm = conn.CreateCommand();
comm.CommandText = _queryString;
iDB2DataReader reader = comm.ExecuteReader();
// Reader in while goes through all rows of results from Reflex.
while (reader.Read())
{
// Adds new key-value to a Dictionary.
Ean_Upc.Add(reader.GetString(0).ToString().Trim(), reader.GetString(1).ToString().Trim());
}
Console.WriteLine("Dictionary EAN_UPC created");
// Some cleaning needed.
reader.Close();
comm.Dispose();
// Return Dictionary
return(Ean_Upc);
}
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex);
Console.WriteLine(ex.StackTrace);
return(null);
}
finally
{
conn.Close();
}
// This will never reach by needs to be here because of error "Not all is returning value".
return(Ean_Upc);
}
/// <summary>
/// Ends the transaction and disposes any unhandled transaction object and closes the reader object
/// </summary>
/// <param name="err">The error object as ref</param>
/// <param name="givenTransaction">The given transaction.</param>
/// <param name="readerToClose">The reader to close.</param>
public void EndTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, iDB2Transaction givenTransaction, iDB2DataReader readerToClose)
{
if (!(givenTransaction.Connection == null))
{
givenTransaction.Dispose();
givenTransaction = null;
}
if (!(readerToClose == null))
{
if (!(readerToClose.IsClosed))
{
readerToClose.Close();
}
readerToClose.Dispose();
}
err = ConnectionByDestinationDBClose(destinationDatabase);
}
//This function returns true if the invoice found in the FSTINV table
private bool InvoiceExists(iDB2Command db2Command, string st)
{
bool exists = false;
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT * FROM FSTINV WHERE SIID = {0}", st);
iDB2DataReader reader = db2Command.ExecuteReader();
exists = reader.HasRows;
reader.Close();
db2Command.Transaction.Dispose();
}
return(exists);
}
//Update Invoice in service Trade to Done
private void SendInvoiceUpdates(iDB2Command db2Command)
{
st.insertLog("Sending invoice update to Service Trade.", "info", "NA", LogId);
Dictionary <string, Invoice> jdeInvList = new Dictionary <string, Invoice>();
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT DISTINCT SIID, SIAN8, SIJOB, SIDOCO, SIDCTO, SICRDT FROM FSTINV WHERE SISTAT = 'P'");
iDB2DataReader reader = db2Command.ExecuteReader();
while (reader.Read())
{
Invoice temp = new Invoice();
temp.SIID = ((string)reader["SIID"]).Trim();
temp.type = "invoice";
//always set this to processed status to avoid breaking the status in ST
temp.status = "processed";
temp.jobId = Int32.Parse((string)reader["SIJOB"]);
//Change Invoice Number (Concat JDE Document Number and Document Type with -)
temp.invoiceNumber = string.Format("{0}-{1}", reader["SIDOCO"], reader["SIDCTO"]);
temp.transactionDate = (Int32)((DateTime)reader["SICRDT"] - unixBaseTime).TotalSeconds;
//temp.assignedUserId= Int32.Parse((string)reader["SITECH"]);
//09/18/2015 - This limits the invoices being sent for update in ST.
//if (!jdeInvList.ContainsKey((string)reader["SIAN8"]))
//{
// jdeInvList.Add((string)reader["SIAN8"], temp);
// sent++;
//}
if (reader["SIAN8"] != null)
{
//12/19/16 JC: This will avoid duplicate records.
if (!jdeInvList.ContainsKey(temp.SIID))
{
jdeInvList.Add((string)temp.SIID, temp);
}
}
}
reader.Close();
}
db2Command.Transaction.Dispose();
if (jdeInvList.Count > 0)
{
st.insertLog(string.Format("Found {0} invoices for update", jdeInvList.Count), "Info", "NA", LogId);
foreach (var jde in jdeInvList)
{
//var status = jde.Value.status;
//Query the single invoice from the service Trade
var invoice = serviceTrade.GetInvoice(jde.Key);
//Mark the invoice as done in FSTINV, if the status of the invoice is void in Service Trade
if (invoice.Status == "void" || invoice.Status != "pending_accounting")
{
st.insertLog(string.Format("The status of invoice number {0} is not <pending_accounting> in service Trade.", jde.Key), "Error", jde.Key.ToString(), LogId);
SetInvoiceToDone(db2Command, jde.Value);
}
else
{
if (serviceTrade.UpdateInvoice(jde.Value))
{
st.insertLog(string.Format("The status of invoice number {0} is set to processed in Service Trade.", jde.Key), "Info", jde.Key.ToString(), LogId);
SetInvoiceToDone(db2Command, jde.Value);
}
}
}
}
else
{
st.insertLog("No invoice update to be sent.", "Info", "NA", LogId);
}
}
private void MigrateUnprocessedLocations(iDB2Command db2Command)
{
Dictionary <string, Location> jdeLocList = new Dictionary <string, Location>();
// log.Debug("Migrating Location");
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT * FROM FSTLOCN WHERE SLSTAT != '{0}'", "P");
iDB2DataReader reader = db2Command.ExecuteReader();
while (reader.Read())
{
Location temp = new Location();
temp.slan8 = ((string)reader["SLAN8"]).Trim();
temp.slpan8 = ((string)reader["SLPAN8"]).Trim();
temp.name = ((string)reader["SLNAME"]).Trim();
temp.addressStreet = ((string)reader["SLADDR"]).Trim();
temp.addressCity = ((string)reader["SLCITY"]).Trim();
temp.addressState = ((string)reader["SLST"]).Trim();
temp.addressPostalCode = ((string)reader["SLZIP"]).Trim();
temp.phoneNumber = ((string)reader["SLPHON"]).Trim();
temp.type = Char.Parse(((string)reader["SLACTN"]).Trim());
temp.officeIds = new int[] { Int32.Parse(((string)reader["SLMCU"]).Trim()) };
jdeLocList.Add(temp.slan8, temp);
}
reader.Close();
db2Command.Transaction.Dispose();
}
if (jdeLocList.Count > 0)
{
// log.Info(string.Format("Found {0} locations for update", jdeLocList.Count));
foreach (var jde in jdeLocList)
{
// log.Debug("Migrating " + jde.Value.name);
bool success = jde.Value.type == 'A' ? serviceTrade.AddLocation(jde.Value) : serviceTrade.UpdateLocation(jde.Value);
if (success)
{
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos))
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("UPDATE FSTLOCN SET SLSTAT = @slstat WHERE SLAN8 = @key", jde.Key);
db2Command.DeriveParameters();
db2Command.Parameters["@slstat"].Value = 'P';
db2Command.Parameters["@key"].Value = jde.Key;
try
{
db2Command.ExecuteNonQuery();
}
catch (Exception ex)
{
// log.Error("Location update unsuccessful ", ex);
}
db2Command.Transaction.Dispose();
}
}
}
}
else
{
// log.Info("No location found for processing.");
}
}
//WCSACCDB
/// <summary>
/// Returns true if HD exist, and saves this hd in OriginalHdModel instance created in this class.
/// If hd is Unknown returns false.
/// </summary>
public bool DownloadHdFromReflex(string _hd)
{
Console.WriteLine("Trying to connect to Reflex for downloading HD informations.");
try
{
conn.Open();
if (conn != null)
{
Console.WriteLine("Successfully connected to Reflex for downloading HD informations.");
// Below are DB2 functions needed for executing query
// Query join table where we can see lines by items. A2CFAN is telling to DB2 to show only lines value.
// You can change it to show CAPO or something else. Result will be in field A2CFAR.
// This also needs to be ordered by Item.
string _queryString = $"SELECT GECART, GECQAL, A.A2CFAR, GEQGEI, B.A2CFAR FROM {Environment}.HLGEINP " +
$"inner join {Environment}.HLCDFAP A on GECART = A.A2CART " +
$"inner join {Environment}.HLCDFAP B on GECART = B.A2CART " +
$"WHERE GENSUP = '{_hd}' and A.A2CFAN = 'LINE' AND B.A2CFAN = 'STAGIONE'" +
"Order by GECART";
iDB2Command comm = conn.CreateCommand();
comm.CommandText = _queryString;
iDB2DataReader reader = comm.ExecuteReader();
// Below if checks if there is some data in result. If no then it return false.
// That means that HD is Unknown.
if (reader.HasRows)
{
// Reader in while goes through all rows of results from Reflex.
while (reader.Read())
{
// Here we are adding new IPG to HD object.
OriginalHdModel.ListOfIpgs.Add(new IpgModel()
{
Item = reader.GetString(0).ToString().Trim(),
Grade = reader.GetString(1).ToString().Trim(),
// Lines is an enum so we need parse string to enum here.
Line = (Lines)Enum.Parse(typeof(Lines), reader.GetString(2)),
Quantity = reader.GetInt32(3),
Season = reader.GetString(4).ToString().Trim()
});
}
// some cleaning.
reader.Close();
comm.Dispose();
// Returns true so we have our data in "OriginalHdModel" instance.
return(true);
}
else
{
// When there is no data from Reflex
return(false);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex);
Console.WriteLine(ex.StackTrace);
return(false);
}
finally
{
conn.Close();
}
// This will never reach but needs to be here because of error "Not all is returning value".
return(false);
}
static void Main(string[] args)
{
//Nomi tabelle
//SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DBNAME';
//Nomi colonne
//SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;
//numerocolonne
//SELECT max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?;
Console.WriteLine("host: ");
string host = Console.ReadLine();
Console.WriteLine("username: "******"password: "******"DB Name: ");
string dbname = Console.ReadLine();
Console.WriteLine("Namespace: ");
string namespacename = Console.ReadLine();
conn = new iDB2Connection("Data Source="+host+";user id="+user+";password="******";");
conn.Open();
cmd = new iDB2Command("", conn);
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + dbname + "'";
row = cmd.ExecuteReader();
List<string> tablenames = new List<string>();
while (row.Read())
{
tablenames.Add(row["TABLE_NAME"].ToString());
}
row.Close();
foreach (var nome in tablenames)
{
Console.WriteLine(nome);
cmd = new iDB2Command("", conn);
cmd.CommandText = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= '" + nome + "' AND TABLE_SCHEMA = '" + dbname + "' ORDER BY ORDINAL_POSITION";
List<string> columnNames = new List<string>();
List<string> columnDefaults = new List<string>();
List<string> areNullables = new List<string>();
List<string> dataTypes = new List<string>();
row = cmd.ExecuteReader();
while (row.Read())
{
columnNames.Add(row["COLUMN_NAME"].ToString());
columnDefaults.Add(row["COLUMN_DEFAULT"].ToString());
areNullables.Add(row["IS_NULLABLE"].ToString());
dataTypes.Add(row["DATA_TYPE"].ToString());
}
row.Close();
for (int k = 0; k < columnNames.Count; k++)
{
if (dataTypes[k].ToLower() == "varchar" || dataTypes[k].ToLower() == "date" || dataTypes[k].ToLower() == "timestamp" || dataTypes[k].ToLower() == "text" || dataTypes[k].ToLower() == "datetime" || dataTypes[k].ToLower() == "national character large object" || dataTypes[k].ToLower() == "national character varying")
dataTypes[k] = "string";
if (dataTypes[k].ToLower() == "tinyint" || dataTypes[k].ToLower() == "bigint" || dataTypes[k].ToLower() == "smallint" || dataTypes[k].ToLower() == "mediumint" || dataTypes[k].ToLower() == "bit" || dataTypes[k].ToLower() == "integer")
dataTypes[k] = "int";
if (dataTypes[k].ToLower() == "decimal" || dataTypes[k].ToLower() == "numeric")
dataTypes[k] = "double";
if (dataTypes[k].ToLower() == "character" || dataTypes[k].ToLower() == "character varying")
dataTypes[k] = "string";
if (columnNames[k].ToLower() == "class")
columnNames[k] = "class_var";
if (columnNames[k].ToLower() == "int")
columnNames[k] = "int_var";
}
string pathstring = "./";
pathstring = Path.Combine(pathstring, nome + ".cs");
if (!File.Exists(pathstring))
{
StreamWriter sw = File.AppendText(pathstring);
sw.WriteLine("using System;");
sw.WriteLine("using System.Collections.Generic;");
sw.WriteLine("using System.Linq;");
sw.WriteLine("using System.Threading.Tasks;");
sw.WriteLine("using System.Text;");
sw.WriteLine("using IBM.Data.DB2.iSeries;");
sw.WriteLine("");
sw.WriteLine("namespace " + namespacename);
sw.WriteLine("{");
sw.WriteLine("\tclass " + UppercaseFirst(nome));
sw.WriteLine("\t{");
int i = 0;
foreach (var columnName in columnNames)
{
string defaultvalue = "null";
if (columnDefaults[i] != "")
{
if (Regex.IsMatch(columnDefaults[i], @"\d"))
defaultvalue = columnDefaults[i];
else
defaultvalue = "\"" + columnDefaults[i] + "\"";
}
if ((dataTypes[i].ToLower() == "int" || dataTypes[i].ToLower() == "double") && columnDefaults[i] == "")
defaultvalue = "0";
if ((dataTypes[i].ToLower() == "char" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "char" && columnDefaults[i].ToLower() == ""))
defaultvalue = "Char.MinValue";
if ((dataTypes[i].ToLower() == "char" && columnDefaults[i] != ""))
defaultvalue = "'" + columnDefaults[i] + "'";
if (((dataTypes[i].ToLower() == "float" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "float" && columnDefaults[i] == "")) || ((dataTypes[i].ToLower() == "double" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "double" && columnDefaults[i] == "")))
defaultvalue = "0.0f";
if ((dataTypes[i].ToLower() == "float" && columnDefaults[i] != "") || (dataTypes[i].ToLower() == "double" && columnDefaults[i] != ""))
defaultvalue = columnDefaults[i] + "f";
if (defaultvalue == "\"''\"" || defaultvalue == "\"CURRENT_DATE\"" || defaultvalue == "\"' '\"")
defaultvalue = "\"\"";
sw.WriteLine("\t\t" + dataTypes[i] + " " + columnName.ToLower() + " { get; set; } = " + defaultvalue + ";");
sw.WriteLine("\t\t" + dataTypes[i] + " OLD_" + columnName.ToLower() + " = " + defaultvalue + ";");
sw.WriteLine("");
i++;
}
sw.WriteLine("\t\tiDB2Connection conn = new iDB2Connection(\"Data Source=<HOST>;user id=<USER>;password=<PASSWORD>;\");");
sw.WriteLine("");
List<string> parametri = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
parametri.Add(dataTypes[k] + " _" + columnNames[k].ToLower());
}
var parametristring = String.Join(", ", parametri);
sw.WriteLine("\t\tpublic " + UppercaseFirst(nome) + "() { }");
sw.WriteLine("");
sw.WriteLine("\t\tpublic " + UppercaseFirst(nome) + "(" + parametristring + ")");
sw.WriteLine("\t\t{");
for (int k = 0; k < parametri.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + " = _" + columnNames[k].ToLower() + ";");
}
sw.WriteLine("\t\t\tupdateOldValues();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tpublic void delete()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tconn.Open();");
sw.WriteLine("\t\t\tiDB2Command cmd = new iDB2Command(\"\", conn);");
List<string> whereStatementArray = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
whereStatementArray.Add(columnNames[k] + " = @" + columnNames[k]);
}
var whereStatementString = String.Join(" AND ", whereStatementArray);
sw.WriteLine("\t\t\tcmd.CommandText = \"DELETE FROM " + dbname + "." + nome + " WHERE " + whereStatementString + "\";");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter " + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);"); //always varchar so i don't have problem to handle strings
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + "Parameter.Value = " + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(" + columnNames[k].ToLower() + "Parameter);");
}
sw.WriteLine("\t\t\tcmd.ExecuteNonQuery();");
sw.WriteLine("\t\t\tconn.Close();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tpublic void update()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tconn.Open();");
sw.WriteLine("\t\t\tiDB2Command cmd = new iDB2Command(\"\", conn);");
List<string> updateStatementArray = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
updateStatementArray.Add(columnNames[k] + " = @new" + columnNames[k]);
}
var updateStatementString = String.Join(", ", updateStatementArray);
sw.WriteLine("\t\t\tcmd.CommandText = \"UPDATE " + dbname + "." + nome + " SET " + updateStatementString + " WHERE " + whereStatementString + "\";");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter OLD_" + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter " + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@new" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + "Parameter.Value = " + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tOLD_" + columnNames[k].ToLower() + "Parameter.Value = OLD_" + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(" + columnNames[k].ToLower() + "Parameter);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(OLD_" + columnNames[k].ToLower() + "Parameter);");
}
sw.WriteLine("\t\t\tcmd.ExecuteNonQuery();");
sw.WriteLine("\t\t\tconn.Close();");
sw.WriteLine("\t\t\tupdateOldValues();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tprivate void updateOldValues()");
sw.WriteLine("\t\t{");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tOLD_" + columnNames[k].ToLower() + " = " + columnNames[k].ToLower() + ";");
}
sw.WriteLine("\t\t}");
sw.WriteLine("\t}");
sw.WriteLine("}");
sw.Close();
}
}
conn.Close();
Console.ReadLine();
}