//iDB2Connection connection;
// iDB2Command command;
// iDB2DataReader reader;
private void btnGetmtnLog_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())
{
txtMtnLog.Text = reader.GetString(0);
}
else
{
txtMtnLog.Text = "No results found for specified work order id.";
}
reader.Close();
}
catch (Exception ex) { txtMtnLog.Text = ex.Message; }
}
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; }
}
public static void ShippingReport()
{
using (iDB2Connection conn = new iDB2Connection(Get("iSeries")))
{
using (iDB2Command cmd = conn.CreateCommand())
{
cmd.CommandText = "VT2662AP.ShippingReport";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
iDB2Parameter parm = cmd.Parameters.Add("@SHIPDATE", iDB2DbType.iDB2Decimal);
cmd.Parameters["@SHIPDATE"].Value = 20151219;
conn.Open();
iDB2DataReader myshipment = cmd.ExecuteReader();
if (myshipment.HasRows)
{
while (myshipment.Read())
{
Console.WriteLine("{0} has {1} - {2} pick {3} {4}", myshipment.GetString(0),
myshipment.GetString(1), myshipment.GetString(2), myshipment.GetString(3),
myshipment.GetString(8));
}
}
myshipment.Close();
conn.Close();
}
}
}
private Boolean checkValidClient(iDB2Connection DBConnection, DataRow myDR, Int32 rowCnt)
{
if (myDR[0].ToString() == null || myDR[0].ToString() == "" || myDR[0].ToString() == " " || myDR[2].ToString() == null || myDR[2].ToString() == "" || myDR[2].ToString() == " ")
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red;
Application.DoEvents();
return(false);
}
try
{
iDB2Command cmd = DBConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT CLTSTS FROM {0}.CLTMST WHERE CLTCAS = @CLIENT_ID", SCHEMA);
cmd.DeriveParameters();
cmd.Parameters["@CLIENT_ID"].Value = myDR[0].ToString();
iDB2DataReader db2r = cmd.ExecuteReader(CommandBehavior.Default);
DataTable myDT = new DataTable();
myDT.Load(db2r);
if (myDT.Rows.Count > 0)
{
if (myDT.Rows[0][0].ToString() == "A")
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.LightGreen;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.LightGreen;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.LightGreen;
Application.DoEvents();
return(true);
}
else
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red;
Application.DoEvents();
return(false);
}
}
else
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red;
Application.DoEvents();
return(false);
}
}
catch (Exception)
{
throw;
}
}
public DataTable getVitalType(iDB2Connection DBConnection, string SCHEMA, string vitalDescription)
{
try
{
iDB2Command cmd = DBConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT EVLTYPE FROM {0}.EMRVTLMST WHERE EVLDSC = '{1}'", SCHEMA, vitalDescription);
iDB2DataReader db2r = cmd.ExecuteReader(CommandBehavior.Default);
DataTable myMasterDT = new DataTable();
myMasterDT.Load(db2r);
return(myMasterDT);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string ejecutarInsertUpdateDelete(string consulta)
{
try
{
int resul = 0;
AS400ConnectionString.Open();
iDB2Command command = AS400ConnectionString.CreateCommand();
command = new iDB2Command(consulta, AS400ConnectionString);
resul = command.ExecuteNonQuery();
return(resul.ToString());
}
catch (Exception ex)
{
throw new Exception("Se produjo un problema al realizar un insert en AS400: ", ex);
}
finally { AS400ConnectionString.Close(); }
}
private bool InsertData(EntityShipment objES)
{
DataTable dtHeader;
bool InsertStatus;
int x = 0;
int intTotalContainers = objES.lstContainer.Count();
int intBOL = 0;
int intCarrier = 0;
int intPlant = 0;
string strWorkStation = objES.strRequesterID.Substring(0, 10);
string strSQLSource = objES.objDetails.objMiscellaneous.strMisc1.Trim() + "_PS";
Int32.TryParse(objES.objDetails.objMiscellaneous.strMisc2, out intBOL);
Int32.TryParse(objES.objDetails.objMiscellaneous.strMisc3, out intCarrier);
Int32.TryParse(objES.objDetails.objMiscellaneous.strMisc4, out intPlant);
string strInstructions = objES.objDetails.strShippingInstructions;
if (!string.IsNullOrWhiteSpace(objES.objDetails.strShippingInstructions) && objES.objDetails.strShippingInstructions.Length >= 255)
{
strInstructions = objES.objDetails.strShippingInstructions.Substring(0, 255);
}
dtHeader = GetShipmentInformation(objES.objDetails.strDeliveryDocNumber);
do
{
iDB2Command sqlCMD = objSQLCon.CreateCommand();
{
sqlCMD.Connection = objSQLCon;
sqlCMD.CommandType = CommandType.Text;
sqlCMD.CommandText = "INSERT INTO SHLIB.FRF01050 (FIREF1, FIREF2, FIREF3, FIREF4, FIREF5, FIVOID, FICOMP, FIATTN, FIADD1, FIADD2, FIADD3, FICITY, FISTAT, FIZIPC, FICNTY, FITRK, FIWGHT, FICTOT, FICTHS, FICOST, FILIST, FISRCE, FISQLSTS, FISHPD, FISQLSTAMP, FIBOL, FICARR, FIPLNT, FIPHON, FISRVT, FISHPV, FISACT, FIADCH, FIPRDT, FIWSID, FIPSTS, FIACCT, FICREF, FIEML1, FIEMN1, FIEML2, FIEMN2, FIEML3, FIEMN3, FIRSTS, FITEXT, FISIGN, FISDLV, FIRACM, FIRAAT, FIRAA1, FIRAA2, FIRACT, FIRAST, FIRAZP, FIINST, FIBTCM, FIBTAT, FIBTA1, FIBTA2, FIBTCT, FIBTST, FIBTZP) VALUES (@FIREF1, @FIREF2, @FIREF3, @FIREF4, @FIREF5, @FIVOID, @FICOMP, @FIATTN, @FIADD1, @FIADD2, @FIADD3, @FICITY, @FISTAT, @FIZIPC, @FICNTY, @FITRK, @FIWGHT, @FICTOT, @FICTHS, @FICOST, @FILIST, @FISRCE, @FISQLSTS, @FISHPD, @FISQLSTAMP, @FIBOL, @FICARR, @FIPLNT, @FIPHON, @FISRVT, @FISHPV, @FISACT, @FIADCH, @FIPRDT, @FIWSID, @FIPSTS, @FIACCT, @FICREF, @FIEML1, @FIEMN1, @FIEML2, @FIEMN2, @FIEML3, @FIEMN3, @FIRSTS, @FITEXT, @FISIGN, @FISDLV, @FIRACM, @FIRAAT, @FIRAA1, @FIRAA2, @FIRACT, @FIRAST, @FIRAZP, @FIINST, @FIBTCM, @FIBTAT, @FIBTA1, @FIBTA2, @FIBTCT, @FIBTST, @FIBTZP)";
sqlCMD.Parameters.AddWithValue("@FIREF1", TestIfNull(objES.objDetails.strDeliveryDocNumber));
sqlCMD.Parameters.AddWithValue("@FIREF2", TestIfNull(objES.objDetails.strPONumber));
sqlCMD.Parameters.AddWithValue("@FIREF3", TestIfNull(objES.objDetails.strInvoiceNumber));
sqlCMD.Parameters.AddWithValue("@FIREF4", " ");
sqlCMD.Parameters.AddWithValue("@FIREF5", " ");
sqlCMD.Parameters.AddWithValue("@FIVOID", " ");
sqlCMD.Parameters.AddWithValue("@FICOMP", TestIfNull(objES.objShipTo.strCompanyName));
sqlCMD.Parameters.AddWithValue("@FIATTN", TestIfNull(objES.objShipTo.strContactName));
sqlCMD.Parameters.AddWithValue("@FIADD1", TestIfNull(objES.objShipTo.strAddressLine1));
sqlCMD.Parameters.AddWithValue("@FIADD2", TestIfNull(objES.objShipTo.strAddressLine2));
sqlCMD.Parameters.AddWithValue("@FIADD3", TestIfNull(objES.objShipTo.strAddressLine3));
sqlCMD.Parameters.AddWithValue("@FICITY", TestIfNull(objES.objShipTo.strCity));
sqlCMD.Parameters.AddWithValue("@FISTAT", TestIfNull(objES.objShipTo.strState));
sqlCMD.Parameters.AddWithValue("@FIZIPC", TestIfNull(objES.objShipTo.strPostalCode));
sqlCMD.Parameters.AddWithValue("@FICNTY", TestIfNull(objES.objShipTo.strCountryCode));
sqlCMD.Parameters.AddWithValue("@FITRK", TestIfNull(objES.lstContainer[x].strTrackingNumber));
sqlCMD.Parameters.AddWithValue("@FIWGHT", objES.lstContainer[x].dblTotalWeight);
sqlCMD.Parameters.AddWithValue("@FICTOT", intTotalContainers);
sqlCMD.Parameters.AddWithValue("@FICTHS", (x + 1));
sqlCMD.Parameters.AddWithValue("@FICOST", objES.lstContainer[x].objRates.dblTotalDiscountedPrice);
sqlCMD.Parameters.AddWithValue("@FILIST", objES.lstContainer[x].objRates.dblTotalPublishedPrice);
sqlCMD.Parameters.AddWithValue("@FISRCE", strSQLSource);
sqlCMD.Parameters.AddWithValue("@FISQLSTS", "R");
sqlCMD.Parameters.AddWithValue("@FISHPD", objES.dtShipDate.ToString("MM/dd/yyyy"));
sqlCMD.Parameters.AddWithValue("@FISQLSTAMP", DateTime.Now);
sqlCMD.Parameters.AddWithValue("@FIBOL", intBOL);
sqlCMD.Parameters.AddWithValue("@FICARR", intCarrier);
sqlCMD.Parameters.AddWithValue("@FIPLNT", intPlant);
sqlCMD.Parameters.AddWithValue("@FIPHON", TestIfNull(objES.objShipTo.strPhoneNumber));
sqlCMD.Parameters.AddWithValue("@FISRVT", TestIfNull(objES.objShipMethod.strShipViaCode));
sqlCMD.Parameters.AddWithValue("@FISHPV", TestIfNull(objES.objShipMethod.strServiceLevel));
sqlCMD.Parameters.AddWithValue("@FISACT", TestIfNull(objES.objShipMethod.strAccountNumber));
sqlCMD.Parameters.AddWithValue("@FIADCH", " ");
sqlCMD.Parameters.AddWithValue("@FIPRDT", " ");
sqlCMD.Parameters.AddWithValue("@FIWSID", TestIfNull(strWorkStation));
sqlCMD.Parameters.AddWithValue("@FIPSTS", " ");
sqlCMD.Parameters.AddWithValue("@FICREF", " ");
sqlCMD.Parameters.AddWithValue("@FIEML1", " ");
sqlCMD.Parameters.AddWithValue("@FIEMN1", " ");
sqlCMD.Parameters.AddWithValue("@FIEML2", " ");
sqlCMD.Parameters.AddWithValue("@FIEMN2", " ");
sqlCMD.Parameters.AddWithValue("@FIEML3", " ");
sqlCMD.Parameters.AddWithValue("@FIEMN3", " ");
sqlCMD.Parameters.AddWithValue("@FIRSTS", " ");
sqlCMD.Parameters.AddWithValue("@FITEXT", " ");
sqlCMD.Parameters.AddWithValue("@FISIGN", " ");
sqlCMD.Parameters.AddWithValue("@FISDLV", " ");
sqlCMD.Parameters.AddWithValue("@FIPMTT", PayTypeToCMS(objES.objShipMethod.strPaymentTerms));
if (objES.objBillTo != null)
{
sqlCMD.Parameters.AddWithValue("@FIACCT", TestIfNull(objES.objBillTo.strAccountNumber));
sqlCMD.Parameters.AddWithValue("@FIBTCM", TestIfNull(objES.objBillTo.strCompanyName));
sqlCMD.Parameters.AddWithValue("@FIBTAT", TestIfNull(objES.objBillTo.strContactName));
sqlCMD.Parameters.AddWithValue("@FIBTA1", TestIfNull(objES.objBillTo.strAddressLine1));
sqlCMD.Parameters.AddWithValue("@FIBTA2", TestIfNull(objES.objBillTo.strAddressLine2));
sqlCMD.Parameters.AddWithValue("@FIBTCT", TestIfNull(objES.objBillTo.strCity));
sqlCMD.Parameters.AddWithValue("@FIBTST", TestIfNull(objES.objBillTo.strState));
sqlCMD.Parameters.AddWithValue("@FIBTZP", TestIfNull(objES.objBillTo.strPostalCode));
}
else
{
sqlCMD.Parameters.AddWithValue("@FIACCT", " ");
sqlCMD.Parameters.AddWithValue("@FIBTCM", " ");
sqlCMD.Parameters.AddWithValue("@FIBTAT", " ");
sqlCMD.Parameters.AddWithValue("@FIBTA1", " ");
sqlCMD.Parameters.AddWithValue("@FIBTA2", " ");
sqlCMD.Parameters.AddWithValue("@FIBTCT", " ");
sqlCMD.Parameters.AddWithValue("@FIBTST", " ");
sqlCMD.Parameters.AddWithValue("@FIBTZP", " ");
}
sqlCMD.Parameters.AddWithValue("@FIRACM", TestIfNull(objES.objShipFrom.strCompanyName));
sqlCMD.Parameters.AddWithValue("@FIRAAT", TestIfNull(objES.objShipFrom.strContactName));
sqlCMD.Parameters.AddWithValue("@FIRAA1", TestIfNull(objES.objShipFrom.strAddressLine1));
sqlCMD.Parameters.AddWithValue("@FIRAA2", TestIfNull(objES.objShipFrom.strAddressLine2));
sqlCMD.Parameters.AddWithValue("@FIRACT", TestIfNull(objES.objShipFrom.strCity));
sqlCMD.Parameters.AddWithValue("@FIRAST", TestIfNull(objES.objShipFrom.strState));
sqlCMD.Parameters.AddWithValue("@FIRAZP", TestIfNull(objES.objShipFrom.strPostalCode));
sqlCMD.Parameters.AddWithValue("@FIINST", TestIfNull(strInstructions));
string strQuery = sqlCMD.CommandText;
foreach (iDB2Parameter p in sqlCMD.Parameters)
{
strQuery = strQuery.Replace(p.ParameterName, p.Value.ToString());
}
try
{
lstResponse.Add(SetResponse(strQuery, string.Empty, ResponseStatusType.LOG));
if (objSQLCon.State == ConnectionState.Closed)
{
objSQLCon.Open();
}
int recordsAffected = sqlCMD.ExecuteNonQuery();
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
InsertStatus = true;
}
catch (iDB2Exception ex)
{
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
lstResponse.Add(SetResponse(strQuery, ex.Message, ResponseStatusType.CRITICAL));
InsertStatus = false;
}
catch (Exception ex)
{
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
lstResponse.Add(SetResponse(strQuery, ex.Message, ResponseStatusType.CRITICAL));
InsertStatus = false;
}
}
x++;
} while (x < intTotalContainers);
return(InsertStatus);
}
//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);
}
public Int32 addVital(iDB2Connection DBConnection, string SCHEMA, string clientID, string vitalType, string vitalDescription, string vitalValue1, string vitalValue2, string myInterviewDate)
{
try
{
iDB2Command cmd = DBConnection.CreateCommand();
cmd.CommandText = string.Format("INSERT INTO {0}.CLTCVL (CVLCAS, CVLTYPE, CVLDATE, CVLVAL, CVLVAL2, CVLMODE, CVLNOTES, CVLCRTUSR, CVLCRTDAT, CVLCHGUSR, CVLCHGDAT, CVLACTPLN) VALUES (@CLIENT_ID, @VITAL_TYPE, @DATE_ENTERED, @VITAL_VALUE_1, @VITAL_VALUE_2, ' ', @CVLNOTES, 'Nashua Vitals Loader', CURRENT TIMESTAMP, 'Nashua Vitals Loader', CURRENT TIMESTAMP, 0)", SCHEMA);
cmd.DeriveParameters();
cmd.Parameters["@CLIENT_ID"].Value = clientID;
cmd.Parameters["@VITAL_TYPE"].Value = vitalType;
cmd.Parameters["@DATE_ENTERED"].Value = myInterviewDate;
switch (vitalDescription)
{
case "8 Hour Fasting Vital Loader":
string HourFastingInd = string.Empty;
if (vitalValue1 == "Y")
{
HourFastingInd = "1";
}
else
{
HourFastingInd = "2";
}
cmd.Parameters["@VITAL_VALUE_1"].Value = Convert.ToDouble(HourFastingInd);
cmd.Parameters["@VITAL_VALUE_2"].Value = null;
break;
case "Blood Pressure Vital Loader":
cmd.Parameters["@VITAL_VALUE_1"].Value = vitalValue1;
cmd.Parameters["@VITAL_VALUE_2"].Value = vitalValue2;
break;
case "Abdominal Girth Vital Loader":
double abdominalGirthcm = Convert.ToDouble(vitalValue1);
double abdominalGirth = abdominalGirthcm * .39;
cmd.Parameters["@VITAL_VALUE_1"].Value = Math.Round(abdominalGirth, 1);
cmd.Parameters["@VITAL_VALUE_2"].Value = null;
break;
case "Height Vital Loader":
double heightKilo = Convert.ToDouble(vitalValue1);
double heightInches = heightKilo * .39;
cmd.Parameters["@VITAL_VALUE_1"].Value = Math.Round(heightInches, 1);
cmd.Parameters["@VITAL_VALUE_2"].Value = null;
break;
case "Weight Vital Loader":
double weightKilo = Convert.ToDouble(vitalValue1);
double weightLBS = weightKilo * 2.2046;
cmd.Parameters["@VITAL_VALUE_1"].Value = Math.Round(weightLBS, 1);
cmd.Parameters["@VITAL_VALUE_2"].Value = null;
break;
default:
cmd.Parameters["@VITAL_VALUE_1"].Value = Convert.ToDouble(vitalValue1);
cmd.Parameters["@VITAL_VALUE_2"].Value = null;
break;
}
cmd.Parameters["@CVLNOTES"].Value = vitalDescription;
//cmd.Parameters["@VITAL_MASTER_UID"].Value = vitalUID;
iDB2DataReader db2r1 = cmd.ExecuteReader(CommandBehavior.Default);
Int32 insertedRow = db2r1.RecordsAffected;
return(insertedRow);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
