// This method will not execute unless txtJob passes validation private void btnExport_Click(object sender, EventArgs e) { string job = txtJob.Text; // Clear lblError.Text = ""; lblFiles.Text = ""; pBar.Value = 0; // Not sure why, but neither will clear the ListViews lvHeader.Clear(); lvMults.Clear(); /* * Find Job in schedule iptpsh_rec. If not found, a null * value is returned with a generic exception message. Format * error message and return on catch so program does not continue. */ #region FindJob DataAccess objFindJob = new DataAccess(); bool fnd = false; try { fnd = objFindJob.Find_Job(job); } catch (Exception ex) { string msg = ex.Message; if (msg.Substring(0, 6) == "Object") { msg = "Job not found in schedule"; } lblError.Text = msg; return; } #endregion #region Consume /* * Get Planned Consumption from Stratix. * The number of rows is the number of setups on this Job * Sort by Seq. Use Pos information to determine where * at what arbor position to start each setup */ DataAccess objConsume = new DataAccess(); List <Consume> lstConsume = new List <Consume>(); try { lstConsume = objConsume.Get_Consumed(job); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } // test //Console.WriteLine("==== CONSUME ===="); //foreach (Consume c in lstConsume) //{ // Console.WriteLine(c.Job.ToString() + " / " + c.Seq.ToString() + " / " + c.Tag + " / " + c.Wdth.ToString() + " / " + c.Stp.ToString() + " / " + c.Pos.ToString()); //} pBar.Value = 12; #endregion #region SO /* * Get Unique SOs from Stratix. * Planned Production iptjpp_rec will list each SO on the Job * Parse this into a Prefix, Ref, Item and SubItem. * This will be used to query Transaction Common Items joined with * CPS Tolerances to get item details. */ DataAccess objSO = new DataAccess(); List <SO> lstSO = new List <SO>(); try { lstSO = objSO.Get_SOs(job); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } // test //Console.WriteLine("==== SO ===="); //foreach (SO s in lstSO) //{ // Console.WriteLine(s.Trgt + " / " + s.Pfx + " / " + s.Ref + " / " + s.Itm + " / " + s.SItm); //} // Build a list of Ref + Item to be used in the query for Item Details string inQry = ""; foreach (SO s in lstSO) { inQry = inQry + string.Concat(s.Ref, s.Itm, ","); } inQry = inQry.TrimEnd(','); pBar.Value = 24; // test //Console.WriteLine("inQry: " + inQry); #endregion #region MultDetail /* * Transaction Common Item Product joined with CPS Tolerances will * give you all the detail you need about the cut for each SO (ex:56572-54) * on the Job */ DataAccess objDetail = new DataAccess(); List <MultDetail> lstDetail = new List <MultDetail>(); try { lstDetail = objDetail.Get_Details(inQry); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } // test //Console.WriteLine("==== MULT DETAIL ===="); //foreach (MultDetail m in lstDetail) //{ // Console.WriteLine(m.Pfx + " / " + m.Ref + " / " + m.Itm + " / " + m.Cus + " / " + m.Part + " / " + m.CtlNo.ToString() + " / " + m.Frm + " / " + m.Ga.ToString() + " / " + m.GaP.ToString() + " / " + m.GaN.ToString() + " / " + m.Wdth.ToString() + " / " + m.WdthP.ToString() + " / " + m.WdthN.ToString()); //} pBar.Value = 36; #endregion #region Ga /* * Get the Gauge listed on the Job * * Determine the most constrained gauge range from the CPS on the Job */ // Consume query was ordered by seq, so first member contains the TagNo for the job string tag = lstConsume.Select(x => x.Tag).First().ToString(); // NEED TO FIX. FIND A BETTER WAY TO FIND GA ON JOB // If Toll or some instance I have not figured out, thre will be // NO tag in the planned consumption. Join to Transaction Common by PO will not work. if (string.IsNullOrEmpty(tag)) { lblError.Text = "No tag found on Job"; return; } DataAccess objGa = new DataAccess(); Ga g = new Ga(); try { g = objGa.Get_Ga(tag, lstDetail); // If Ga is not found, it could be tolling or a closed PO if (g.NumSize == 0) { throw new Exception("Ga not found for Job"); } } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } pBar.Value = 48; #endregion #region Build HdrFile /* * Build List<HdrFile> */ // Determine number of setups int numSetups = lstConsume.Count; List <string> lstNumSetups = new List <string>(); //Add sufix of 1,2,3... to end of Job if (numSetups == 1) { lstNumSetups.Add(string.Concat(job.ToString(), "-", "1")); } else { for (int i = 0; i < numSetups; i++) { lstNumSetups.Add(string.Concat(job.ToString(), "-", (i + 1).ToString())); } } // lstNumSetups will have same count of members as lstConsume int setupCnt = 0; List <HdrFile> lstHdr = new List <HdrFile>(); foreach (string j in lstNumSetups) { HdrFile h = new HdrFile(); h.Job = j; h.Mtl = lstDetail[0].Frm; h.Wdth = lstConsume[0].Wdth; h.Ga = g.NumSize; h.Clr = h.KnifeClr * h.Ga; h.GaP = g.GaP; h.GaN = g.GaN; // Get Weight from lstConsume at current position h.Wgt = lstConsume[setupCnt].Wgt; setupCnt++; // After Pos = 1, if consecutive Pos are same value, you are reslitting //FIX THIS TO ADD RESLIT TO NOTE //if (lstConsume[j].Pos != 1) //{ // if (lstConsume[j].Pos == lstConsume[j - 1].Pos) // h.Note = "RESLIT"; //} lstHdr.Add(h); } pBar.Value = 60; //testing //Console.WriteLine("==== HDR FILE ===="); //foreach (HdrFile h in lstHdr) //{ // Console.WriteLine(h.Job + " / " + h.Cust + " / " + h.Mtl + " / " + h.Wdth.ToString() + " / " + h.Ga.ToString() + " / " + h.KnifeClr.ToString() + " / " + h.Clr.ToString() + " / " + h.GaP.ToString() + " / " + h.GaN.ToString() + " / " + h.Note); //} #endregion #region Build MultFile /* * Build MultFile */ // Get Arbor from Stratix DataAccess objArbor = new DataAccess(); List <ArborStratix> lstArborStratix = new List <ArborStratix>(); try { lstArborStratix = objArbor.Get_Arbors(job); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } // test //Console.WriteLine("==== ARBOR ===="); //foreach (ArborStratix a in lstArborStratix) //{ // Console.WriteLine(a.Job.ToString() + " / " + a.Wdth.ToString() + " / " + a.Nbr.ToString()); //} // Get list of start Pos for each setup List <int> lstStartPos = new List <int>(); foreach (Consume c in lstConsume) { lstStartPos.Add(c.Pos); } // Expand lstArborStratix to assign Job, setup and position List <ArborExp> lstExp = new List <ArborExp>(); int aPos = 0; //Arbor position 1 to X int aSetupCount = 0; // Suffix on Job is current setup on Job // First setup Pos=1, but 1st object in List<> = 0 int aSetup = lstStartPos[aSetupCount]; try { foreach (ArborStratix a in lstArborStratix) { //Look at each cut in the setup and expand into setup with only single cuts for (int i = 1; i <= a.Nbr; i++) { // Start position counter with 1, then ++ for each pass aPos++; // If there is only on setup start Pos if (lstStartPos.Count == 1) { aSetup = 1; } else { // Look ahead to start Pos of next setup if (aSetupCount <= lstStartPos.Count) { // If current position = start Pos of next setup, increment the setup count if (lstStartPos[aSetupCount + 1] == aPos) { aSetup++; } } } ArborExp aExp = new ArborExp(); aExp.Job = string.Concat(job.ToString(), "-", aSetup.ToString()); aExp.Wdth = a.Wdth; aExp.Pos = aPos; lstExp.Add(aExp); } } } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } pBar.Value = 72; //Console.WriteLine("==== ARBOR EXPANDED ===="); //foreach (ArborExp f in lstExp) //{ // Console.WriteLine(f.Job + " / " + f.Wdth.ToString() + " / " + f.Pos.ToString()); //} int indexClp = 0; // Collapse Arbor counting number of repeat sizes as you go List <ArborStratix> lstArborClp = new List <ArborStratix>(); for (int i = 0; i < lstExp.Count(); i++) { // 1st Position always gets added to the List if (lstExp[i].Pos == 1) { ArborStratix a = new ArborStratix(); a.Job = lstExp[i].Job; a.Wdth = lstExp[i].Wdth; a.Nbr = 1; // Default to get the count started lstArborClp.Add(a); } else { // If Wdth of next element is same as previous if (lstExp[i].Wdth == lstArborClp[indexClp].Wdth) { // lstExp.Wdth is same as previous lstArborClp, so just ++ Nbr lstArborClp[indexClp].Nbr++; } else { // New wdth, so add to lstArborClp ArborStratix a = new ArborStratix(); a.Job = lstExp[i].Job; a.Wdth = lstExp[i].Wdth; a.Nbr = 1; // Default to get the count started lstArborClp.Add(a); // Inc index of lstArborClp indexClp++; } } } //Console.WriteLine("==== ARBOR COLLAPSED ===="); //foreach (ArborStratix f in lstArborClp) //{ // Console.WriteLine(f.Job + " / " + f.Wdth.ToString() + " / " + f.Nbr.ToString()); //} // Build MultFile List <MultFile> lstMults = new List <MultFile>(); foreach (ArborStratix a in lstArborClp) { MultFile m = new MultFile(); m.Job = a.Job; m.Qty = a.Nbr; m.Size = a.Wdth; m.WdthP = Convert.ToDecimal(lstDetail.Where(x => x.Wdth == a.Wdth).Select(x => x.WdthP).FirstOrDefault()); m.WdthN = Convert.ToDecimal(lstDetail.Where(x => x.Wdth == a.Wdth).Select(x => x.WdthN).FirstOrDefault()); lstMults.Add(m); } pBar.Value = 90; // testing //Console.WriteLine("==== MULT FILE ===="); //foreach (MultFile h in lstMults) //{ // Console.WriteLine(h.Job + " / " + h.Cust + " / " + h.Qty.ToString() + " / " + h.Size.ToString() + " / " + h.WdthP.ToString() + " / " + h.WdthN.ToString() + " / " + h.Knife); //} #endregion #region XLSExports ExcelExport objXLS = new ExcelExport(); try { objXLS.WriteHdr(lstHdr); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } try { objXLS.WriteMults(lstMults); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } #endregion // Write Hdr and Mults to ListViews #region FillListViews try { ListView_Fill(lstHdr, lstMults); } catch (Exception ex) { lblError.Text = ex.Message; pBar.Value = 0; return; } // Progress complete pBar.Value = 100; // Show where files were written string hdrFileName = ConfigurationManager.AppSettings.Get("HdrFileName"); string multFileName = ConfigurationManager.AppSettings.Get("MultFileName"); string destPath = ConfigurationManager.AppSettings.Get("DestPath"); lblFiles.Text = "Files written:\n" + Path.Combine(destPath, hdrFileName) + "\n" + Path.Combine(destPath, multFileName); #endregion }
public List <ArborStratix> Get_Arbors(string job) { List <ArborStratix> lstArbor = new List <ArborStratix>(); OdbcConnection conn = new OdbcConnection(STRATIXDataConnString); //OdbcConnection conn = new OdbcConnection("DSN=Invera;UID=livcalod;Pwd=livcalod"); try { conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from iptfra_rec where fra_job_no = " + job; OdbcDataReader rdr = cmd.ExecuteReader(); using (rdr) { int c = 0; decimal w = 0; int f = rdr.FieldCount; // Only one long record with 60 fra_wdth and fra_nbr_slit pairs while (rdr.Read()) { // Go thru each pair and dynamically create the field name for (int i = 1; i < f; i++) { ArborStratix a = new ArborStratix(); c += 1; w = (decimal)rdr["fra_wdth_" + c.ToString()]; // Only want a pair with a value. When Wdth = 0, we are done if (w != 0) { a.Job = rdr["fra_job_no"].ToString(); a.Wdth = (decimal)rdr["fra_wdth_" + c.ToString()]; a.Nbr = Convert.ToInt32(rdr["fra_nbr_slit_" + c.ToString()]); lstArbor.Add(a); } else { // Jump to end of loop i = f; } } } } } catch (OdbcException ex) { throw; //Console.WriteLine("arbor odbc ex: " + ex.Message); } catch (Exception ex) { throw; //Console.WriteLine("arbor other ex: " + ex.Message); } finally { // No matter what close and dispose of the connetion conn.Close(); conn.Dispose(); } return(lstArbor); }