public void Update()
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
//insert into mach_op table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE mach_op SET MachID = ?MachID, OpLevel = ?OpLevel, SetupT = ?SetupT, CyclT = ?CyclT, LdUldT = ?LdUldT, TBtwnOps = ?TBtwnOps, EffPct = ?EffPct, OpAttPct = ?OpAttPct, MachHrCst = ?MachHrCst, Description = ?Description WHERE InfoID = " + InfoID + " AND OpNo = " + OpNo;
command.Parameters.Add("?MachID", MySqlDbType.Int32).Value = MachID;
command.Parameters.Add("?OpLevel", MySqlDbType.Int32).Value = OpLevel;
command.Parameters.Add("?SetupT", MySqlDbType.Double).Value = SetupT;
command.Parameters.Add("?CyclT", MySqlDbType.Double).Value = CyclT;
command.Parameters.Add("?LdUldT", MySqlDbType.Double).Value = LdUldT;
command.Parameters.Add("?TBtwnOps", MySqlDbType.Double).Value = TBtwnOps;
command.Parameters.Add("?EffPct", MySqlDbType.Double).Value = (EffPct / 100);
command.Parameters.Add("?OpAttPct", MySqlDbType.Double).Value = (OpAttPct / 100);
command.Parameters.Add("?MachHrCst", MySqlDbType.Double).Value = MachHrCst;
command.Parameters.Add("?Description", MySqlDbType.Text).Value = Description;
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
// System.Threading.Thread.Sleep(3000);
setMathFactors();
}
private void ListPOs()
{
listPOs.Items.Clear();
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT PO_No FROM po";
if (chkShowOpen.Checked == true)
{
cmdString = "SELECT PO_No FROM po WHERE PO_Status = 'Open'";
}
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
string pono = dataReader["PO_No"].ToString();
listPOs.Items.Add(pono);
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
}
public void buildQuoteList()
{
DBConnection db = new DBConnection();
string cmdString = "SELECT QuoteID from quote";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
try
{
string quoteid = dataReader["QuoteID"].ToString();
stringlist.Add(quoteid);
}
catch (Exception q) { }
}
//close everything
dataReader.Close();
db.CloseConnection();
foreach(string s in stringlist)
{
Quote newquote = new Quote(s, "load");
quotelist.Add(newquote);
}
}
}
//get list of customer names from the database, then store it and populate the combobox
private void PopulateCustomers()
{
//create a database connection
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT name FROM customer";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the names and add them to our names list.
while (dataReader.Read())
{
names.Add(dataReader["name"].ToString());
}
//close everything
dataReader.Close();
db.CloseConnection();
}
//add the names from our local list to the combobox
foreach(String s in names)
{
cmbCustomerName.Items.Add(s);
}
}
public void Upload()
{
DBConnection conn = new DBConnection();
if(conn.OpenConnection())
{
try
{
//insert into mach_op table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO mach_op(InfoID, OpNo, MachID, OpLevel, SetupT, CyclT, LdUldT, TBtwnOps, EffPct, OpAttPct, MachHrCst, Description) VALUES(?InfoID, ?OpNo, ?MachID, ?OpLevel, ?SetupT, ?CyclT, ?LdUldT, ?TBtwnOps, ?EffPct, ?OpAttPct, ?MachHrCst, ?Description)";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?OpNo", MySqlDbType.Int32).Value = OpNo;
command.Parameters.Add("?MachID", MySqlDbType.Int32).Value = MachID;
command.Parameters.Add("?OpLevel", MySqlDbType.Int32).Value = OpLevel;
command.Parameters.Add("?SetupT", MySqlDbType.Double).Value = SetupT;
command.Parameters.Add("?CyclT", MySqlDbType.Double).Value = CyclT;
command.Parameters.Add("?LdUldT", MySqlDbType.Double).Value = LdUldT;
command.Parameters.Add("?TBtwnOps", MySqlDbType.Double).Value = TBtwnOps;
command.Parameters.Add("?EffPct", MySqlDbType.Double).Value = (EffPct / 100);
command.Parameters.Add("?OpAttPct", MySqlDbType.Double).Value = (OpAttPct/100);
command.Parameters.Add("?MachHrCst", MySqlDbType.Double).Value = MachHrCst;
command.Parameters.Add("?Description", MySqlDbType.Text).Value = Description;
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
// System.Threading.Thread.Sleep(3000);
setMathFactors();
}
public JobRun(string pPO, string pJob, int pRun, string mode)
{
PO_No = pPO;
JobID = pJob;
RunID = pRun;
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT * FROM job_run WHERE PO_No = '" + PO_No + "' AND JobID = '" + JobID + "' AND RunID = '" + RunID + "'";
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
RunStatus = dataReader["RunStatus"].ToString();
//get the date, db returns in mm/dd/yyyy so get that string
string date = dataReader["DueDate"].ToString();
//split it on the /, giving us "MM", "DD", "YYYY"
string[] datePieces = date.Split('/');
//the third piece will have the time, we need to pull that out, looks like "2015 12:00:00 AM", so split on space and pull first
string[] dayPieces = datePieces[2].Split(' ');
//create a new datetime out of the integer versions of those values (year, month, day)
int month = Convert.ToInt32(datePieces[0]);
int day = Convert.ToInt32(datePieces[1]);
int year = Convert.ToInt32(dayPieces[0]);
DueDate = new DateTime(year, month, day);
//get the date, db returns in mm/dd/yyyy so get that string
date = dataReader["ShipDate"].ToString();
//split it on the /, giving us "MM", "DD", "YYYY"
datePieces = date.Split('/');
//the third piece will have the time, we need to pull that out, looks like "2015 12:00:00 AM", so split on space and pull first
dayPieces = datePieces[2].Split(' ');
//create a new datetime out of the integer versions of those values (year, month, day)
month = Convert.ToInt32(datePieces[0]);
day = Convert.ToInt32(datePieces[1]);
year = Convert.ToInt32(dayPieces[0]);
ShipDate = new DateTime(year, month, day);
OrderQty = Convert.ToInt32(dataReader["OrderQty"].ToString());
MakeQty = Convert.ToInt32(dataReader["MakeQty"].ToString());
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
}
//fill combo box with quotes + previous jobs
private void populateList()
{
//start with quotes
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT QuoteID FROM quote";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the quotes and add them to our quotes+jobs list.
while (dataReader.Read())
{
quotesAndJobs.Add(dataReader["QuoteID"].ToString());
}
//close everything
dataReader.Close();
db.CloseConnection();
}
if (db.OpenConnection())
{
cmdString = "SELECT PO_No from job";
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
quotesAndJobs.Add(dataReader["PO_No"].ToString());
}
//close everything
dataReader.Close();
db.CloseConnection();
foreach (string s in quotesAndJobs)
{
cmbJobsAndQuotes.Items.Add(s);
}
}
}
//load in
public Job(string number, string mode)
{
saved = true;
JobID = number;
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT * FROM job WHERE JobID = '" + JobID + "'";
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
QuoteID = dataReader["QuoteID"].ToString();
//get the date, db returns in mm/dd/yyyy so get that string
string date = dataReader["LastShipped"].ToString();
//split it on the /, giving us "MM", "DD", "YYYY"
string[] datePieces = date.Split('/');
//the third piece will have the time, we need to pull that out, looks like "2015 12:00:00 AM", so split on space and pull first
string[] dayPieces = datePieces[2].Split(' ');
//create a new datetime out of the integer versions of those values (year, month, day)
int month = Convert.ToInt32(datePieces[0]);
int day = Convert.ToInt32(datePieces[1]);
int year = Convert.ToInt32(dayPieces[0]);
LastShipped = new DateTime(year, month, day);
NextRunID = Convert.ToInt32(dataReader["NextRunID"].ToString());
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
}
public Dashboard()
{
InitializeComponent();
//get next quoteid num
try
{
DBConnection db = new DBConnection();
if (db.OpenConnection())
{
string cmdString = "SELECT Number from nextid where ID = 'nextInfo'";
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
MachineOperation newOp;
//loop through results, and pull out the info and save it to the machineop object
while (dataReader.Read())
{
try
{
nextquote = dataReader["Number"].ToString();
}
catch (Exception q)
{
}
}
//close everything
setQuotes();
dataReader.Close();
db.CloseConnection();
}
}
catch (Exception q) { }
}
private void btnSavePO_Click(object sender, EventArgs e)
{
po.PO_Status = cmbPOStatus.Text;
po.SaveToDatabase();
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
foreach (string j in listAssociatedJobs.Items)
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO po_job(PO_No, JobID) VALUES(?PO_No, ?JobID)";
command.Parameters.Add("?PO_No", MySqlDbType.VarChar).Value = po.PO_No;
command.Parameters.Add("?JobID", MySqlDbType.VarChar).Value = j;
command.ExecuteNonQuery();
}
catch (Exception q) { }
}
conn.CloseConnection();
}
}
private void btnRemoveJob_Click(object sender, EventArgs e)
{
//remove selected job from associated jobs.
try
{
string target = listAssociatedJobs.SelectedItem.ToString();
//remove it from the list on the screen
listAssociatedJobs.Items.Remove(target);
listJobs.Items.Remove(target);
//we actually won't remove it from the PO object's list because it's hard, but we don't need to.
//The associated jobs list is only pulled from the PO object when we build this form.
//Since we will only be inserting into po_job from here, and then when the PO object is rebuilt the next time it is viewed
//it will pull down the po_job table and be updated. It's sneaky, i know.
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "DELETE FROM po_job WHERE PO_No = '" + po.PO_No + "' AND JobID = '" + target + "'";
command.ExecuteNonQuery();
}
conn.CloseConnection();
}
catch(Exception q)
{
}
}
private void buildLists()
{
machines.Clear();
levels.Clear();
opcosts.Clear();
#region Machines
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT * FROM machine";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the names and add them to our names list.
machineInfo info;
while (dataReader.Read())
{
info = new machineInfo();
info.MachID = Convert.ToInt32(dataReader["MachID"].ToString());
info.Name = dataReader["Name"].ToString();
info.dfltSetupT = Convert.ToDouble(dataReader["dfltSetupT"].ToString());
info.dfltLdUldT = Convert.ToDouble(dataReader["dfltLdUldT"].ToString());
info.dfltTBtwnOps = Convert.ToDouble(dataReader["dfltTBtwnOps"].ToString());
info.dfltEffPct = Convert.ToDouble(dataReader["dfltEffPct"].ToString());
info.dfltOpAttPct = Convert.ToDouble(dataReader["dfltOpAttPct"].ToString());
info.dfltMachHrCst = Convert.ToDouble(dataReader["dfltMachHrCst"].ToString());
info.dfltOpLvl = Convert.ToInt32(dataReader["dfltOpLvl"].ToString());
machines.Add(info);
}
//close everything
dataReader.Close();
db.CloseConnection();
}
cmdString = "SELECT * FROM op_lvl";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the names and add them to our names list.
while (dataReader.Read())
{
levels.Add(dataReader["OpLvl"].ToString());
opcosts.Add(Convert.ToDouble(dataReader["OpCstHr"]));
}
//close everything
dataReader.Close();
db.CloseConnection();
}
//now make our comboboxes read from the lists\
listMachines.Items.Clear();
cmbOp1Operator.Items.Clear();
cmbOpLevelEdit.Items.Clear();
foreach (machineInfo m in machines)
{
listMachines.Items.Add(m.Name);
}
foreach (string lvl in levels)
{
cmbOp1Operator.Items.Add(lvl);
cmbOpLevelEdit.Items.Add(lvl);
}
numMachines = machines.Count;
#endregion
}
public void buildLists()
{
listQuotesAndJobs.Items.Clear();
//the quotes that dont have a job made from them yet
#region unused quotes
//build the list of unused quotes
DBConnection db = new DBConnection();
string cmdString = "SELECT Q.QuoteID FROM quote Q, quote_info I WHERE Q.InfoID = I.InfoID AND I.FirstJobID IS NULL";
if(chkShowSame.Checked == true)
{
cmdString = "SELECT Q.QuoteID FROM quote Q, quote_info I WHERE Q.InfoID = I.InfoID AND I.FirstJobID IS NULL AND Q.CustID = '" + po.CustID + "'";
}
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
try
{
string quoteid = dataReader["QuoteID"].ToString();
listQuotesAndJobs.Items.Add(quoteid);
}
catch (Exception q) { }
}
//close everything
dataReader.Close();
db.CloseConnection();
}
#endregion
//all jobs
#region jobs
db = new DBConnection();
cmdString = "SELECT JobID FROM job";
if(chkShowSame.Checked == true)
{
cmdString = "SELECT J.JobID FROM job J, quote Q WHERE J.QuoteID = Q.QuoteID AND Q.CustID = '" + po.CustID + "'";
}
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
try
{
string jobid = dataReader["JobID"].ToString();
bool exists = false;
foreach(Job j in po.associatedJobs)
{
if(j.JobID == jobid)
{
exists = true;
}
}
if (!exists)
{
listQuotesAndJobs.Items.Add(jobid);
}
}
catch (Exception q) { }
}
//close everything
dataReader.Close();
db.CloseConnection();
}
#endregion
}
public void Upload()
{
DBConnection conn = new DBConnection();
#region quote_info
if (conn.OpenConnection())
{
try
{
//insert into quote table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_info(InfoID, FirstCustID) VALUES(?InfoID, ?FirstCustID)";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?FirstCustID", MySqlDbType.VarChar).Value = CustID;
command.ExecuteNonQuery();
int newInfo = InfoID + 1;
command.CommandText = "update nextid set nextid.Number = " + newInfo + " where nextid.ID = 'nextInfo'";
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
#endregion
#region Quote table
if(conn.OpenConnection())
{
try
{
//insert into quote table
MySqlCommand command = conn.getConnection().CreateCommand();
// command.CommandText = "INSERT INTO quote(QuoteID, ToolType, OtherToolName, CoolThru, ShankDiam, OAL, NoOfFlute, FluteLength, NoOfStep, NoOfProf, Description, AdminNote, MatVend, MatOrderNo, MatDesc, MatSize, MatGrade, MatLeadT, BPVend, BPDesc, BPLeadT, Coating, CoatVend, CoatDesc, CoatLeadT, HrPerDay, QuoteDate, QuoteImage) VALUES (?QuoteID, ?ToolType, ?OtherToolName, ?CoolThru, ?ShankDiam, ?OAL, ?NoOfFlute, ?FluteLength, ?NoOfStep, ?NoOfProf, ?Description, ?AdminNote, ?MatVend, ?MatOrderNo, ?MatDesc, ?MatSize, ?MatGrade, ?MatLeadT, ?BPVend, ?BPDesc, ?BPLeadT, ?Coating, ?CoatVend, ?CoatDesc, ?CoatLeadT, ?HrPerDay, ?QuoteDate, ?QuoteImage)";
command.CommandText = "INSERT INTO QUOTE(QuoteID, InfoID, CustID, HrPerDay, SafeT, QuoteDate) VALUES(?QuoteID, ?InfoID, ?CustID, ?HrPerDay, ?SafeT, ?QuoteDate)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?CustID", MySqlDbType.VarChar).Value = CustID;
command.Parameters.Add("?HrPerDay", MySqlDbType.Double).Value = HrPerDay;
command.Parameters.Add("?SafeT", MySqlDbType.Int32).Value = SafeT;
command.Parameters.Add("?QuoteDate", MySqlDbType.Date).Value = QuoteDate.ToString("yyyy-MM-dd");
command.ExecuteNonQuery();
//increment the id tracker number
int newQuoteID = QuoteIDINT + 1;
command.CommandText = "update nextid set nextid.Number = " + newQuoteID + " where nextid.ID = 'nextJob'";
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
#endregion
#region tool_info
if(conn.OpenConnection())
{
try
{
//insert into quote table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO tool_info(InfoID, ToolType, OtherToolName, CoolThru, ShankDiam, OAL, NoOfFlute, FluteLength, NoOfStep, NoOfProf, Description, AdminNote, MatVend, MatOrderNo, MatDesc, MatSize, MatGrade, MatLeadT, BPVend, BPDesc, BPLeadT, Coating, CoatVend, CoatDesc, CoatLeadT, ToolImage) VALUES (?InfoID, ?ToolType, ?OtherToolName, ?CoolThru, ?ShankDiam, ?OAL, ?NoOfFlute, ?FluteLength, ?NoOfStep, ?NoOfProf, ?Description, ?AdminNote, ?MatVend, ?MatOrderNo, ?MatDesc, ?MatSize, ?MatGrade, ?MatLeadT, ?BPVend, ?BPDesc, ?BPLeadT, ?Coating, ?CoatVend, ?CoatDesc, ?CoatLeadT, ?ToolImage)";
// command.CommandText = "INSERT INTO tool_info(InfoID, ToolType, OtherToolName, CoolThru, ShankDiam, OAL, NoOfFlute, FluteLength, NoOfStep, NoOfProf, Description, AdminNote, MatVend, MatOrderNo, MatDesc, MatSize, MatGrade, MatLeadT, BPVend, BPDesc, BPLeadT, Coating, CoatVend, CoatDesc, CoatLeadT) VALUES (?QuoteID, ?ToolType, ?OtherToolName, ?CoolThru, ?ShankDiam, ?OAL, ?NoOfFlute, ?FluteLength, ?NoOfStep, ?NoOfProf, ?Description, ?AdminNote, ?MatVend, ?MatOrderNo, ?MatDesc, ?MatSize, ?MatGrade, ?MatLeadT, ?BPVend, ?BPDesc, ?BPLeadT, ?Coating, ?CoatVend, ?CoatDesc, ?CoatLeadT)";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?ToolType", MySqlDbType.VarChar).Value = ToolType;
command.Parameters.Add("?OtherToolName", MySqlDbType.VarChar).Value = OtherToolName;
command.Parameters.Add("?CoolThru", MySqlDbType.VarChar).Value = CoolThru;
command.Parameters.Add("?ShankDiam", MySqlDbType.VarChar).Value = ShankDiam;
command.Parameters.Add("?OAL", MySqlDbType.VarChar).Value = OAL;
command.Parameters.Add("?NoOfFlute", MySqlDbType.VarChar).Value = NoOfFlute;
command.Parameters.Add("?FluteLength", MySqlDbType.VarChar).Value = FluteLength;
command.Parameters.Add("?NoOfStep", MySqlDbType.VarChar).Value = NoOfStep;
command.Parameters.Add("?NoOfProf", MySqlDbType.VarChar).Value = NoOfProf;
command.Parameters.Add("?Description", MySqlDbType.Text).Value = Description;
command.Parameters.Add("?AdminNote", MySqlDbType.Text).Value = AdminNote;
command.Parameters.Add("?MatVend", MySqlDbType.VarChar).Value = MatVend;
command.Parameters.Add("?MatOrderNo", MySqlDbType.VarChar).Value = MatOrderNo;
command.Parameters.Add("?MatDesc", MySqlDbType.VarChar).Value = MatDesc;
command.Parameters.Add("?MatSize", MySqlDbType.VarChar).Value = MatSize;
command.Parameters.Add("?MatGrade", MySqlDbType.VarChar).Value = MatGrade;
command.Parameters.Add("?MatLeadT", MySqlDbType.Int32).Value = MatLeadT;
command.Parameters.Add("?BPVend", MySqlDbType.VarChar).Value = BPVend;
command.Parameters.Add("?BPDesc", MySqlDbType.VarChar).Value = BPDesc;
command.Parameters.Add("?BPLeadT", MySqlDbType.Int32).Value = BPLeadT;
command.Parameters.Add("?Coating", MySqlDbType.VarChar).Value = UseCoating;
command.Parameters.Add("?CoatVend", MySqlDbType.VarChar).Value = CoatVend;
command.Parameters.Add("?CoatDesc", MySqlDbType.VarChar).Value = CoatDesc;
command.Parameters.Add("?CoatLeadT", MySqlDbType.Int32).Value = CoatLeadT;
command.Parameters.Add("?ToolImage", MySqlDbType.MediumBlob).Value = ToolImage;
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
#endregion
#region quote_cost
//insert into quote_cost
//first row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 1;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty1;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost1;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost1;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost1;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct1;
command.ExecuteNonQuery();
}
catch(Exception q)
{
}
conn.CloseConnection();
}
//second row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 2;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty2;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost2;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost2;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost2;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct2;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//third row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 3;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty3;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost3;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost3;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost3;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct3;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//fourth row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 4;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty4;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost4;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost4;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost4;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct4;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//fifth row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 5;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty5;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost5;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost5;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost5;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct5;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//sixth row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 6;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty6;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost6;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost6;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost6;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct6;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//seventh row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO quote_cost(QuoteID, QtyID, Qty, MatCost, BPCost, CoatCost, MarkUpPct) VALUES (?QuoteID, ?QtyID, ?Qty, ?MatCost, ?BPCost, ?CoatCost, ?MarkUpPct)";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?QtyID", MySqlDbType.Int32).Value = 7;
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty7;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost7;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost7;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost7;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct7;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
#endregion
#region drill_step table
//insert into drill_step table
//get the number of steps as an int
int numsteps = 0;
string stepnopk = "0";
try { numsteps = Convert.ToInt32(NoOfStep); }
catch (Exception q) { }
//first row
if (numsteps >= 1)
{
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
stepnopk = "1";
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO drill_step(InfoID, StepNo, StepDiam, StepLeng, StepAngle) VALUES (?InfoID, ?StepNo, ?StepDiam, ?StepLeng, ?StepAngle)";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?StepNo", MySqlDbType.VarChar).Value = stepnopk;
command.Parameters.Add("?StepDiam", MySqlDbType.VarChar).Value = step1Dia;
command.Parameters.Add("?StepLeng", MySqlDbType.VarChar).Value = step1Length;
command.Parameters.Add("?StepAngle", MySqlDbType.VarChar).Value = step1Angle;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
}
//second row
if(numsteps >=2)
{
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
stepnopk = "2";
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO drill_step(InfoID, StepNo, StepDiam, StepLeng, StepAngle) VALUES (?InfoID, ?StepNo, ?StepDiam, ?StepLeng, ?StepAngle)";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?StepNo", MySqlDbType.VarChar).Value = stepnopk;
command.Parameters.Add("?StepDiam", MySqlDbType.VarChar).Value = step2Dia;
command.Parameters.Add("?StepLeng", MySqlDbType.VarChar).Value = step2Length;
command.Parameters.Add("?StepAngle", MySqlDbType.VarChar).Value = step2Angle;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
}
//third row
if (numsteps == 3)
{
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
stepnopk = "3";
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO drill_step(InfoID, StepNo, StepDiam, StepLeng, StepAngle) VALUES (?InfoID, ?StepNo, ?StepDiam, ?StepLeng, ?StepAngle)";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?StepNo", MySqlDbType.VarChar).Value = stepnopk;
command.Parameters.Add("?StepDiam", MySqlDbType.VarChar).Value = step3Dia;
command.Parameters.Add("?StepLeng", MySqlDbType.VarChar).Value = step3Length;
command.Parameters.Add("?StepAngle", MySqlDbType.VarChar).Value = step3Angle;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
}
#endregion
#region machine operations
//insert into mach_op table
foreach(MachineOperation op in ops)
{
op.Upload();
}
#endregion
Dashboard.lastquote = QuoteID;
Dashboard.nextquote = (InfoID + 1).ToString();
}
public void setMathFactors()
{
DBConnection db = new DBConnection();
string cmdString = "SELECT * from math_factors where InfoID = '" + InfoID + "' AND OpNo = '" + OpNo + "'";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the machineoperation object
while (dataReader.Read())
{
try
{
MTm = Convert.ToDouble(dataReader["MTm"].ToString());
MTb = Convert.ToDouble(dataReader["MTb"].ToString());
MCTm = Convert.ToDouble(dataReader["MCTm"].ToString());
MCTb = Convert.ToDouble(dataReader["MCTb"].ToString());
OCTm = Convert.ToDouble(dataReader["OCTm"].ToString());
OCTb = Convert.ToDouble(dataReader["OCTb"].ToString());
}
catch(Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
db = new DBConnection();
cmdString = "SELECT OpCstHr from op_lvl where OpLvl = '" + OpLevel + "'";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the machineoperation object
while (dataReader.Read())
{
try
{
OperatorCostPerHour = Convert.ToDouble(dataReader["OpCstHr"].ToString());
}
catch(Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
}
private void btnSaveMachine_Click(object sender, EventArgs e)
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
string target = "";
try
{
//insert into mach_op table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE machine SET Name = ?Name, dfltSetupT = ?dfltSetupT, dfltLdUldT = ?dfltLdUldT, dfltTBtwnOps = ?dftlTBtwnOps, dfltEffPct = ?dfltEffPct, dfltOpAttPct = ?dfltOpAttPct, dfltMachHrCst = ?dfltMachHrCst, dfltOpLvl = ?dfltOpLvl WHERE MachID = " + curMachID;
command.Parameters.Add("?Name", MySqlDbType.VarChar).Value = txtMachineName;
command.Parameters.Add("?dfltSetupT", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1Setup.Text);
command.Parameters.Add("?dfltLdUldT", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1Load.Text);
command.Parameters.Add("?dfltTBtwnOps", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1TimeBetween.Text);
command.Parameters.Add("?dfltEffPct", MySqlDbType.Double).Value = (Convert.ToDouble(txtOp1Efficiency.Text) / 100);
command.Parameters.Add("?dfltOpAttPct", MySqlDbType.Double).Value = (Convert.ToDouble(txtOp1OpAttend.Text) / 100);
command.Parameters.Add("?dfltMachHrCst", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1MachineCost.Text);
command.Parameters.Add("?dfltOpLvl", MySqlDbType.Int32).Value = Convert.ToInt32(cmbOp1Operator.Text);
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
}
public string getNextQuoteID()
{
//create a database connection
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT Number FROM nextid WHERE ID = 'nextJob'";
//our result variable
int nextJobId = -1;
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the names and add them to our names list.
while (dataReader.Read())
{
try
{
nextJobId = Convert.ToInt32(dataReader["number"].ToString());
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
QuoteIDINT = nextJobId;
return ("Q" + CustID + nextJobId.ToString());
}
//update job
public void Update()
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE job SET QuoteID = ?QuoteID, LastShipped = ?LastShipped WHERE JobID = '" + JobID + "'";
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?LastShipped", MySqlDbType.Date).Value = LastShipped.ToString("yyyy-MM-dd");
command.ExecuteNonQuery();
conn.CloseConnection();
}
catch (Exception q)
{
}
}
}
public void Upload()
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
//insert into po table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO job_run(PO_No, JobID, DueDate, ShipDate, OrderQty, MakeQty) VALUES(?PO_No, ?JobID, ?DueDate, ?ShipDate, ?OrderQty, ?MakeQty)";
command.Parameters.Add("?PO_No", MySqlDbType.VarChar).Value = PO_No;
command.Parameters.Add("?JobID", MySqlDbType.VarChar).Value = JobID;
command.Parameters.Add("?DueDate", MySqlDbType.Date).Value = DueDate.ToString("yyyy-MM-dd");
command.Parameters.Add("?ShipDate", MySqlDbType.Date).Value = ShipDate.ToString("yyyy-MM-dd");
command.Parameters.Add("?OrderQty", MySqlDbType.Int32).Value = OrderQty;
command.Parameters.Add("?MakeQty", MySqlDbType.Int32).Value = MakeQty;
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
conn = new DBConnection();
//the query to run
string cmdString = "SELECT NextRunID FROM job WHERE JobID = '" + JobID + "'";
//open the connection and execute our query
if (conn.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, conn.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
RunID = (Convert.ToInt32(dataReader["NextRunID"].ToString())) - 1;
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
conn.CloseConnection();
}
}
}
//insert into job
public void Upload()
{
saved = true;
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO job(JobID, QuoteID, LastShipped) VALUES (?JobID, ?QuoteID, ?LastShipped)";
command.Parameters.Add("?JobID", MySqlDbType.VarChar).Value = JobID;
command.Parameters.Add("?QuoteID", MySqlDbType.VarChar).Value = QuoteID;
command.Parameters.Add("?LastShipped", MySqlDbType.Date).Value = LastShipped.ToString("yyyy-MM-dd");
command.ExecuteNonQuery();
conn.CloseConnection();
}
catch (Exception q)
{
}
}
}
public bool Upload()
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
//insert into po table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO PO(PO_No, CustID, PO_Date, PO_Status) VALUES(?PO_No, ?CustID, ?PO_Date, PO_Status)";
command.Parameters.Add("?PO_No", MySqlDbType.VarChar).Value = PO_No;
command.Parameters.Add("?CustID", MySqlDbType.VarChar).Value = CustID;
command.Parameters.Add("?PO_Date", MySqlDbType.Date).Value = PO_Date.ToString("yyyy-MM-dd");
command.Parameters.Add("?PO_Staus", MySqlDbType.VarChar).Value = PO_Status;
command.ExecuteNonQuery();
}
catch (MySqlException q) { return false; }
conn.CloseConnection();
foreach(Job j in associatedJobs)
{
j.saveToDatabase();
}
saved = true;
return true;
}
return false;
}
public void Update()
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
//update po table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE PO SET CustID = ?CustID, PO_Date = ?PO_Date, PO_Status = ?PO_Status WHERE PO_No = '" + PO_No + "'";
command.Parameters.Add("?CustID", MySqlDbType.VarChar).Value = CustID;
command.Parameters.Add("?PO_Date", MySqlDbType.Date).Value = PO_Date.ToString("yyyy-MM-dd");
command.Parameters.Add("?PO_Status", MySqlDbType.VarChar).Value = PO_Status;
command.ExecuteNonQuery();
}
catch (Exception q) { }
conn.CloseConnection();
foreach(Job j in associatedJobs)
{
j.saveToDatabase();
}
}
}
private void btnDeleteRun_Click(object sender, EventArgs e)
{
try
{
string[] rundisplay = listJobRuns.SelectedItem.ToString().Split(',');
string targetrun = rundisplay[0];
string runjob = rundisplay[1].Substring(1);
int targetrunid = Convert.ToInt32(targetrun);
JobRun victim = new JobRun(po.PO_No, runjob, targetrunid, "load");
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "DELETE FROM job_run WHERE PO_No = '" + victim.PO_No + "' AND JobID = '" + victim.JobID + "' AND RunID = " + victim.RunID ;
command.ExecuteNonQuery();
}
conn.CloseConnection();
po.jobRuns.Remove(victim);
listJobRuns.Items.Remove(listJobRuns.SelectedItem);
}
catch (Exception q)
{ }
}
private void btnAddMachine_Click(object sender, EventArgs e)
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
string target = "";
try
{
//insert into mach_op table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO machine(MachID, Name, dfltSetupT, dfltLdUldT, dfltTBtwnOps, dfltEffPct, dfltOpAttPct, dfltMachHrCst, dfltOpLvl) VALUES(?MachID, ?Name, ?dfltSetupT, ?dfltLdUldT, ?dfltTBtwnOps, ?dfltEffPct, ?dfltOpAttPct, ?dfltMachHrCst, ?dfltOpLvl)";
command.Parameters.Add("?MachID", MySqlDbType.Int32).Value = numMachines + 1;
command.Parameters.Add("?Name", MySqlDbType.VarChar).Value = txtMachineName.Text;
command.Parameters.Add("?dfltSetupT", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1Setup.Text);
command.Parameters.Add("?dfltLdUldT", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1Load.Text);
command.Parameters.Add("?dfltTBtwnOps", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1TimeBetween.Text);
command.Parameters.Add("?dfltEffPct", MySqlDbType.Double).Value = (Convert.ToDouble(txtOp1Efficiency.Text) / 100);
command.Parameters.Add("?dfltOpAttPct", MySqlDbType.Double).Value = (Convert.ToDouble(txtOp1OpAttend.Text) / 100);
command.Parameters.Add("?dfltMachHrCst", MySqlDbType.Double).Value = Convert.ToDouble(txtOp1MachineCost.Text);
command.Parameters.Add("?dfltOpLvl", MySqlDbType.Int32).Value = Convert.ToInt32(cmbOp1Operator.Text);
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
buildLists();
}
}
private void btnAddJob_Click(object sender, EventArgs e)
{
//take selected from listQuotesAndJobs
//if job get the quoteid
//create new job
//if quote then make folder and job
try
{
string target = listQuotesAndJobs.SelectedItem.ToString();
Job newjob;
if (target.Substring(0, 1) == "Q" || chkRevision.Checked == true)
{
newjob = new Job(target, chkRevision.Checked);
Quote targetQuote = new Quote(target, "load");
try
{
System.IO.Directory.CreateDirectory("C:\\xampp\\htdocs\\AcuTwistJobs\\" + newjob.myQuote.CustID + "\\" + newjob.JobID);
}
catch (Exception q)
{
}
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_info SET FirstJobID = '" + newjob.JobID + "' WHERE InfoID = '" + targetQuote.InfoID + "'";
command.ExecuteNonQuery();
}
catch (Exception q) { }
}
conn.CloseConnection();
}
else
{
newjob = new Job(target, "load");
}
po.associatedJobs.Add(newjob);
listAssociatedJobs.Items.Add(newjob.JobID);
listJobs.Items.Add(newjob.JobID);
po.PO_Status = cmbPOStatus.Text;
po.SaveToDatabase();
DBConnection connect = new DBConnection();
if (connect.OpenConnection())
{
foreach (string j in listAssociatedJobs.Items)
{
try
{
MySqlCommand command = connect.getConnection().CreateCommand();
command.CommandText = "INSERT INTO po_job(PO_No, JobID) VALUES(?PO_No, ?JobID)";
command.Parameters.Add("?PO_No", MySqlDbType.VarChar).Value = po.PO_No;
command.Parameters.Add("?JobID", MySqlDbType.VarChar).Value = j;
command.ExecuteNonQuery();
}
catch (Exception q) { }
}
connect.CloseConnection();
}
buildLists();
}
catch (Exception q) { }
}
private void btnSaveLevel_Click(object sender, EventArgs e)
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
int target = Convert.ToInt32(cmbOpLevelEdit.Text);
try
{
//insert into mach_op table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE op_lvl SET OpCsthr = ?OpCstHr WHERE OpLvl = " + target;
command.Parameters.Add("?OpCstHr", MySqlDbType.Double).Value = Convert.ToDouble(txtOpCost.Text);
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
}
//constructor taking a passed in id, will use this to create and populate a quote given an id
public Quote(string id, string mode)
{
ops = new List<MachineOperation>();
QuoteID = id;
#region quote table
//get everything from quote, then other tables
DBConnection db = new DBConnection();
string cmdString = "SELECT * from quote where QuoteID = '" + id + "'";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
try
{
InfoID = Convert.ToInt32(dataReader["InfoID"].ToString());
CustID = dataReader["CustID"].ToString();
//get the date, db returns in mm/dd/yyyy so get that string
string date = dataReader["QuoteDate"].ToString();
//split it on the /, giving us "MM", "DD", "YYYY"
string[] datePieces = date.Split('/');
//the third piece will have the time, we need to pull that out, looks like "2015 12:00:00 AM", so split on space and pull first
string[] dayPieces = datePieces[2].Split(' ');
//create a new datetime out of the integer versions of those values (year, month, day)
int month = Convert.ToInt32(datePieces[0]);
int day = Convert.ToInt32(datePieces[1]);
int year = Convert.ToInt32(dayPieces[0]);
QuoteDate = new DateTime(year, month, day);
HrPerDay = Convert.ToDouble(dataReader["HrPerDay"].ToString());
SafeT = Convert.ToInt32(dataReader["SafeT"].ToString());
}
catch (Exception q) { }
}
//close everything
dataReader.Close();
db.CloseConnection();
}
#endregion
#region quote_cost
//quote_cost table
try
{
cmdString = "SELECT * from quote_cost where QuoteID = '" + QuoteID + "' order by QtyID ASC";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
int rowCount = 1;
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
try
//each row gets a BreakDownQty, matcostX, bpcostx, coatcostx, and breakdownpct
{
if (rowCount == 1)
{
BreakDownQty1 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost1 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost1 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost1 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct1 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
else if (rowCount == 2)
{
BreakDownQty2 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost2 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost2 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost2 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct2 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
else if (rowCount == 3)
{
BreakDownQty3 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost3 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost3 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost3 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct3 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
else if (rowCount == 4)
{
BreakDownQty4 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost4 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost4 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost4 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct4 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
else if (rowCount == 5)
{
BreakDownQty5 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost5 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost5 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost5 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct5 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
else if (rowCount == 6)
{
BreakDownQty6 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost6 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost6 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost6 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct6 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
else if (rowCount == 7)
{
BreakDownQty7 = Convert.ToInt32(dataReader["Qty"].ToString());
matCost7 = Convert.ToDouble(dataReader["MatCost"].ToString());
BPCost7 = Convert.ToDouble(dataReader["BPCost"].ToString());
CoatCost7 = Convert.ToDouble(dataReader["CoatCost"].ToString());
BreakDownPct7 = Convert.ToDouble(dataReader["MarkUpPct"].ToString());
}
}
catch (Exception q) { }
rowCount++;
}
//close everything
dataReader.Close();
db.CloseConnection();
}
}
catch (Exception q) { }
#endregion
//quote_info table? I dont think i need to do anything here because all its storing is references for the customer/job ids which be shown
// so i will only need to worry about this table when inserting, but not selecting
#region tool_info
//tool_info table
cmdString = "SELECT * from tool_info where InfoID = '" + InfoID + "'";
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
ToolType = dataReader["ToolType"].ToString();
OtherToolName = dataReader["OtherToolName"].ToString();
CoolThru = Convert.ToChar(dataReader["CoolThru"].ToString());
ShankDiam = dataReader["ShankDiam"].ToString();
OAL = dataReader["OAL"].ToString();
NoOfFlute = dataReader["NoOfFlute"].ToString();
FluteLength = dataReader["FluteLength"].ToString();
NoOfStep = dataReader["NoOfStep"].ToString();
NoOfProf = dataReader["NoOfProf"].ToString();
Description = dataReader["Description"].ToString();
AdminNote = dataReader["AdminNote"].ToString();
MatVend = dataReader["MatVend"].ToString();
MatOrderNo = dataReader["MatOrderNo"].ToString();
MatDesc = dataReader["MatDesc"].ToString();
MatSize = dataReader["MatSize"].ToString();
MatGrade = dataReader["MatGrade"].ToString();
MatLeadT = Convert.ToInt32(dataReader["MatLeadT"].ToString());
BPVend = dataReader["BPVend"].ToString();
BPDesc = dataReader["BPDesc"].ToString();
BPLeadT = Convert.ToInt32(dataReader["BPLeadT"].ToString());
UseCoating = Convert.ToChar(dataReader["Coating"].ToString());
CoatVend = dataReader["CoatVend"].ToString();
CoatDesc = dataReader["CoatDesc"].ToString();
CoatLeadT = Convert.ToInt32(dataReader["CoatLeadT"].ToString());
ToolNotes = dataReader["ToolNotes"].ToString();
//NEED CODE TO PULL DOWN IMAGE, LIKELY ANOTHER STATEMENT USING ADAPTERS
}
//close everything
dataReader.Close();
db.CloseConnection();
}
#endregion
#region drill_step
try
{
//drill step table
if (db.OpenConnection())
{
cmdString = "SELECT * from drill_step where InfoID = '" + InfoID + "' order by StepNo ASC";
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
int rowCount = 1;
//loop through results, and pull out the info and save it to the quote object
while (dataReader.Read())
{
if (rowCount == 1)
{
step1Dia = dataReader["StepDiam"].ToString();
step1Length = dataReader["StepLeng"].ToString();
step1Angle = dataReader["StepDiam"].ToString();
}
else if (rowCount == 2)
{
step2Dia = dataReader["StepDiam"].ToString();
step2Length = dataReader["StepLeng"].ToString();
step2Angle = dataReader["StepDiam"].ToString();
}
else if (rowCount == 3)
{
step3Dia = dataReader["StepDiam"].ToString();
step3Length = dataReader["StepLeng"].ToString();
step3Angle = dataReader["StepDiam"].ToString();
}
rowCount++;
}
//close everything
dataReader.Close();
db.CloseConnection();
}
}
catch (Exception q) { }
#endregion
#region mach_op
try
{
if (db.OpenConnection())
{
cmdString = "SELECT * from mach_op where InfoID = " + InfoID + " order by OpNo ASC";
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
MachineOperation newOp;
//loop through results, and pull out the info and save it to the machineop object
while (dataReader.Read())
{
try
{
int number = Convert.ToInt32(dataReader["OpNo"].ToString());
newOp = new MachineOperation(InfoID, number);
newOp.MachID = Convert.ToInt32(dataReader["MachID"].ToString());
newOp.OpLevel = Convert.ToInt32(dataReader["OpLevel"].ToString());
newOp.SetupT = Convert.ToDouble(dataReader["SetupT"].ToString());
newOp.CyclT = Convert.ToDouble(dataReader["CyclT"].ToString());
newOp.LdUldT = Convert.ToDouble(dataReader["LdUldT"].ToString());
newOp.TBtwnOps = Convert.ToDouble(dataReader["TBtwnOps"].ToString());
newOp.Description = dataReader["Description"].ToString();
newOp.EffPct = Convert.ToDouble(dataReader["EffPct"].ToString());
newOp.OpAttPct = Convert.ToDouble(dataReader["OpAttPct"].ToString());
newOp.MachHrCst = Convert.ToDouble(dataReader["MachHrCst"].ToString());
ops.Add(newOp);
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
foreach (MachineOperation op in ops)
{
op.setMathFactors();
}
}
}
catch (Exception q) { }
#endregion
#region quoteimage
//get the quoteimage
if(db.OpenConnection())
{
cmdString = "SELECT ToolImage from tool_info where InfoID = " + InfoID;
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
MachineOperation newOp;
//loop through results, and pull out the info and save it to the machineop object
while (dataReader.Read())
{
try
{
ToolImage = (byte[]) dataReader["ToolImage"];
}
catch (Exception q)
{
}
}
}
#endregion
}
private void btnAddLevel_Click(object sender, EventArgs e)
{
DBConnection conn = new DBConnection();
if (conn.OpenConnection())
{
int target = Convert.ToInt32(cmbOpLevelEdit.Text);
try
{
//insert into mach_op table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "INSERT INTO op_lvl(OpLvl, OpCstHr) VALUES(?OpLvl, ?OpCstHr)";
command.Parameters.Add("?OpLvl", MySqlDbType.Int32).Value = target;
command.Parameters.Add("?OpCstHr", MySqlDbType.Double).Value = Convert.ToDouble(txtOpCost.Text);
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
buildLists();
}
}
//NEED TO FINISH
public void Update()
{
DBConnection conn = new DBConnection();
#region Quote table
if (conn.OpenConnection())
{
try
{
//update quote table
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote SET InfoID = ?InfoID, CustID = ?CustID, HrPerDay = ?HrPerDay, SafeT = ?SafeT, QuoteDate = ?QuoteDate WHERE QuoteID = '"+ QuoteID + "'";
command.Parameters.Add("?InfoID", MySqlDbType.Int32).Value = InfoID;
command.Parameters.Add("?CustID", MySqlDbType.VarChar).Value = CustID;
command.Parameters.Add("?HrPerDay", MySqlDbType.Double).Value = HrPerDay;
command.Parameters.Add("?SafeT", MySqlDbType.Int32).Value = SafeT;
command.Parameters.Add("?QuoteDate", MySqlDbType.Date).Value = QuoteDate.ToString("yyyy-MM-dd");
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
#endregion
#region tool_info
if (conn.OpenConnection())
{
try
{
//update tool_info
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE tool_info SET ToolType = ?ToolType, OtherToolName = ?OtherToolName, CoolThru = ?CoolThru, ShankDiam = ?ShankDiam, OAL = ?OAL, NoOfFlute = ?NoOfFlute, FluteLength = ?FluteLength, NoOfStep = ?NoOfStep, NoOfProf = ?NoOfProf, Description = ?Description, AdminNote = ?AdminNote, MatVend = ?MatVend, MatOrderNo = ?MatOrderNo, MatDesc = ?MatDesc, MatSize = ?MatSize, MatGrade = ?MatGrade, MatLeadT = ?MatLeadT, BPVend = ?BPVend, BPDesc = ?BPDesc, BPLeadT = ?BPLeadT, Coating = ?Coating, CoatVend = ?CoatVend, CoatDesc = ?CoatDesc, CoatLeadT = ?CoatLeadT, ToolImage = ?ToolImage WHERE InfoID = '" +InfoID + "'";
command.Parameters.Add("?ToolType", MySqlDbType.VarChar).Value = ToolType;
command.Parameters.Add("?OtherToolName", MySqlDbType.VarChar).Value = OtherToolName;
command.Parameters.Add("?CoolThru", MySqlDbType.VarChar).Value = CoolThru;
command.Parameters.Add("?ShankDiam", MySqlDbType.VarChar).Value = ShankDiam;
command.Parameters.Add("?OAL", MySqlDbType.VarChar).Value = OAL;
command.Parameters.Add("?NoOfFlute", MySqlDbType.VarChar).Value = NoOfFlute;
command.Parameters.Add("?FluteLength", MySqlDbType.VarChar).Value = FluteLength;
command.Parameters.Add("?NoOfStep", MySqlDbType.VarChar).Value = NoOfStep;
command.Parameters.Add("?NoOfProf", MySqlDbType.VarChar).Value = NoOfProf;
command.Parameters.Add("?Description", MySqlDbType.Text).Value = Description;
command.Parameters.Add("?AdminNote", MySqlDbType.Text).Value = AdminNote;
command.Parameters.Add("?MatVend", MySqlDbType.VarChar).Value = MatVend;
command.Parameters.Add("?MatOrderNo", MySqlDbType.VarChar).Value = MatOrderNo;
command.Parameters.Add("?MatDesc", MySqlDbType.VarChar).Value = MatDesc;
command.Parameters.Add("?MatSize", MySqlDbType.VarChar).Value = MatSize;
command.Parameters.Add("?MatGrade", MySqlDbType.VarChar).Value = MatGrade;
command.Parameters.Add("?MatLeadT", MySqlDbType.Int32).Value = MatLeadT;
command.Parameters.Add("?BPVend", MySqlDbType.VarChar).Value = BPVend;
command.Parameters.Add("?BPDesc", MySqlDbType.VarChar).Value = BPDesc;
command.Parameters.Add("?BPLeadT", MySqlDbType.Int32).Value = BPLeadT;
command.Parameters.Add("?Coating", MySqlDbType.VarChar).Value = UseCoating;
command.Parameters.Add("?CoatVend", MySqlDbType.VarChar).Value = CoatVend;
command.Parameters.Add("?CoatDesc", MySqlDbType.VarChar).Value = CoatDesc;
command.Parameters.Add("?CoatLeadT", MySqlDbType.Int32).Value = CoatLeadT;
command.Parameters.Add("?ToolImage", MySqlDbType.MediumBlob).Value = ToolImage;
command.ExecuteNonQuery();
}
catch (MySqlException q) { }
conn.CloseConnection();
}
#endregion
#region quote_cost
//insert into quote_cost
//first row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" +QuoteID + "' AND QtyID = 1";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty1;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost1;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost1;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost1;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct1;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//second row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" + QuoteID + "' AND QtyID = 2";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty2;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost2;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost2;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost2;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct2;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//third row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" + QuoteID + "' AND QtyID = 3";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty3;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost3;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost3;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost3;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct3;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//fourth row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" + QuoteID + "' AND QtyID = 4";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty4;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost4;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost4;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost4;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct4;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//fifth row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" + QuoteID + "' AND QtyID = 5";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty5;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost5;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost5;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost5;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct5;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//sixth row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" + QuoteID + "' AND QtyID = 6";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty6;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost6;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost6;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost6;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct6;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
//seventh row
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE quote_cost SET Qty = ?Qty, MatCost = ?MatCost, BPCost = ?BPCost, CoatCost = ?CoatCost, MarkUpPct = ?MarkUpPct WHERE QuoteID = '" + QuoteID + "' AND QtyID = 7";
command.Parameters.Add("?Qty", MySqlDbType.Int32).Value = BreakDownQty7;
command.Parameters.Add("?MatCost", MySqlDbType.Double).Value = matCost7;
command.Parameters.Add("?BPCost", MySqlDbType.Double).Value = BPCost7;
command.Parameters.Add("?CoatCost", MySqlDbType.Double).Value = CoatCost7;
command.Parameters.Add("?MarkUpPct", MySqlDbType.Double).Value = BreakDownPct7;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
#endregion
#region drill_step table
//insert into drill_step table
//get the number of steps as an int
int numsteps = 0;
string stepnopk = "0";
try { numsteps = Convert.ToInt32(NoOfStep); }
catch (Exception q) { }
//first row
if (numsteps >= 1)
{
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
stepnopk = "1";
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE drill_step SET StepDiam = ?StepDiam, StepLeng = ?StepLeng, StepAngle = ?StepAngle WHERE InfoID = " + InfoID + " AND StepNo ='1'";
command.Parameters.Add("?StepDiam", MySqlDbType.VarChar).Value = step1Dia;
command.Parameters.Add("?StepLeng", MySqlDbType.VarChar).Value = step1Length;
command.Parameters.Add("?StepAngle", MySqlDbType.VarChar).Value = step1Angle;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
}
//second row
if (numsteps >= 2)
{
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
stepnopk = "2";
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE drill_step SET StepDiam = ?StepDiam, StepLeng = ?StepLeng, StepAngle = ?StepAngle WHERE InfoID = '" + InfoID +"' AND StepNo ='2'";
command.Parameters.Add("?StepDiam", MySqlDbType.VarChar).Value = step2Dia;
command.Parameters.Add("?StepLeng", MySqlDbType.VarChar).Value = step2Length;
command.Parameters.Add("?StepAngle", MySqlDbType.VarChar).Value = step2Angle;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
}
//third row
if (numsteps == 3)
{
conn = new DBConnection();
if (conn.OpenConnection())
{
try
{
stepnopk = "3";
MySqlCommand command = conn.getConnection().CreateCommand();
command.CommandText = "UPDATE drill_step SET StepDiam = ?StepDiam, StepLeng = ?StepLeng, StepAngle = ?StepAngle WHERE InfoID = '" + InfoID +"' AND StepNo ='3'";
command.Parameters.Add("?StepDiam", MySqlDbType.VarChar).Value = step3Dia;
command.Parameters.Add("?StepLeng", MySqlDbType.VarChar).Value = step3Length;
command.Parameters.Add("?StepAngle", MySqlDbType.VarChar).Value = step3Angle;
command.ExecuteNonQuery();
}
catch (Exception q)
{
}
conn.CloseConnection();
}
}
#endregion
#region machine operations
//update mach_op table
foreach (MachineOperation op in ops)
{
op.Update();
}
#endregion
}
//loading existing
public PO (string number, string mode)
{
jobsToBuild = new List<string>();
runsToBuild = new List<runInfo>();
associatedJobs = new List<Job>();
jobRuns = new List<JobRun>();
saved = true;
PO_No = number;
#region PO info
//get the po info
DBConnection db = new DBConnection();
//the query to run
string cmdString = "SELECT * FROM po WHERE PO_No = '" + PO_No + "'";
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
CustID = dataReader["CustID"].ToString();
//get the date, db returns in mm/dd/yyyy so get that string
string date = dataReader["PO_Date"].ToString();
//split it on the /, giving us "MM", "DD", "YYYY"
string[] datePieces = date.Split('/');
//the third piece will have the time, we need to pull that out, looks like "2015 12:00:00 AM", so split on space and pull first
string[] dayPieces = datePieces[2].Split(' ');
//create a new datetime out of the integer versions of those values (year, month, day)
int month = Convert.ToInt32(datePieces[0]);
int day = Convert.ToInt32(datePieces[1]);
int year = Convert.ToInt32(dayPieces[0]);
PO_Date = new DateTime(year, month, day);
PO_Status = dataReader["PO_Status"].ToString();
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
}
#endregion
#region Associated Jobs
db = new DBConnection();
//the query to run
cmdString = "SELECT JobID FROM po_job WHERE PO_No = '" + PO_No + "'";
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
string newJobID = dataReader["JobID"].ToString();
jobsToBuild.Add(newJobID);
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
//we use this seperate list rather than add the jobs inside the above loop because the job has to "load" itself as well
//this means that job is opening up a database connection, and we would get an error for having multiple connections
foreach(string s in jobsToBuild)
{
Job newJob = new Job(s, "load");
associatedJobs.Add(newJob);
}
}
#endregion
#region Job Runs
db = new DBConnection();
//the query to run
cmdString = "SELECT RunID, JobID FROM job_run WHERE PO_No = '" + PO_No + "'";
//open the connection and execute our query
if (db.OpenConnection())
{
MySqlCommand cmd = new MySqlCommand(cmdString, db.getConnection());
MySqlDataReader dataReader = cmd.ExecuteReader();
//grab the fields from the row
while (dataReader.Read())
{
try
{
//use the struct
runInfo newrun;
newrun.jobid = dataReader["JobID"].ToString();
newrun.runid = Convert.ToInt32(dataReader["RunID"].ToString());
runsToBuild.Add(newrun);
}
catch (Exception q)
{
}
}
//close everything
dataReader.Close();
db.CloseConnection();
//we use this seperate list rather than add the jobs inside the above loop because the job has to "load" itself as well
//this means that job is opening up a database connection, and we would get an error for having multiple connections
if (runsToBuild.Count > 0)
{
foreach (runInfo s in runsToBuild)
{
JobRun newjobrun = new JobRun(PO_No, s.jobid, s.runid, "load");
jobRuns.Add(newjobrun);
}
}
}
#endregion
}
