private string decideReferenceTable(string serno) { string tablekey = string.Empty; string filterkey = string.Empty; if (VBStrings.Mid(serno, 3, 2) == "3D") { tablekey = "ls12_003d"; filterkey = "LS3D"; } else if (VBStrings.Mid(serno, 3, 2) == "3E") { tablekey = "ls12_003e"; filterkey = "LS3E"; } else if (VBStrings.Mid(serno, 3, 2) == "3F") { tablekey = "ls12_003f"; filterkey = "LS3F"; } else if (VBStrings.Mid(serno, 3, 2) == "3J") { tablekey = "ls12_003j"; filterkey = "LS3J"; } else if (VBStrings.Mid(serno, 3, 2) == "4A") { tablekey = "ls12_004a"; filterkey = "LS4A"; } testerTableThisMonth = tablekey + DateTime.Today.ToString("yyyyMM"); testerTableLastMonth = tablekey + ((VBStrings.Right(DateTime.Today.ToString("yyyyMM"), 2) != "01") ? (long.Parse(DateTime.Today.ToString("yyyyMM")) - 1).ToString() : (long.Parse(DateTime.Today.ToString("yyyy")) - 1).ToString() + "12"); return(filterkey); }
// Sub procedure: Check if datatable's product serial is included in the database table // (actually, database itself blocks the duplicate, so this process is not needed) private string checkDataTableWithRealTable(DataTable dt1) { string result = String.Empty; string sql = "select serial_short, boxid " + "FROM product_serial_printdate WHERE testtime BETWEEN '" + System.DateTime.Today.AddDays(-7) + "' AND '" + System.DateTime.Today.AddDays(1) + "'"; DataTable dt2 = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatable(sql, ref dt2); for (int i = 0; i < dt1.Rows.Count; i++) { string serial = VBStrings.Left(dt1.Rows[i]["serialno"].ToString(), 17); DataRow[] dr = dt2.Select("serial_short = '" + serial + "'"); if (dr.Length >= 1) { string boxid = dr[0]["boxId"].ToString(); result += (i + 1 + ": " + serial + " / " + boxid + Environment.NewLine); } } if (result == String.Empty) { return(String.Empty); } else { return(result); } }
private void btnPrint_Click(object sender, EventArgs e) { string boxId = txtBoxId.Text; if (!formEditMode) { if (okCount == limit && dgvProductSerial.Rows.Count == limit) { config = dtOverall.Rows[0]["config"].ToString(); printBarcode(directory, boxId, config, m_model, dgvDateCode, ref dgvDateCode2, ref txtBoxIdPrint); } } else { string boxIdNew = getNewBoxId(); if (okCount == limit && dgvProductSerial.Rows.Count == limit) { // Print barcode printBarcode(directory, boxIdNew, config, m_model, dgvDateCode, ref dgvDateCode2, ref txtBoxIdPrint); // Clear the datatable dtOverall.Clear(); txtBoxId.Text = boxIdNew; if (boxIdNew.Contains("BMA60")) { dtpPrintDate.Value = DateTime.ParseExact(VBStrings.Mid(boxIdNew, 7, 6), "yyMMdd", CultureInfo.InvariantCulture); } else { dtpPrintDate.Value = DateTime.ParseExact(VBStrings.Mid(boxIdNew, 6, 6), "yyMMdd", CultureInfo.InvariantCulture); } } } }
// Sub procedure: Issue new box id private string getNewBoxId() { m_model = dtOverall.Rows[0]["model"].ToString(); string sql = "select MAX(boxid) FROM box_id where boxid like '" + m_model + "%'"; System.Diagnostics.Debug.Print(sql); ShSQL yn = new ShSQL(); string boxIdOld = yn.sqlExecuteScalarString(sql); DateTime dateOld = new DateTime(0); long numberOld = 0; string boxIdNew; if (m_model == "BMS69") { if (!string.IsNullOrEmpty(boxIdOld)) { dateOld = DateTime.ParseExact(VBStrings.Mid(boxIdOld, 7, 6), "yyMMdd", CultureInfo.InvariantCulture); numberOld = long.Parse(VBStrings.Right(boxIdOld, 2)); } if (dateOld != DateTime.Today) { boxIdNew = m_model + "-" + DateTime.Today.ToString("yyMMdd") + "01"; } else { boxIdNew = m_model + "-" + DateTime.Today.ToString("yyMMdd") + (numberOld + 1).ToString("00"); } } else { if (boxIdOld != string.Empty) { dateOld = DateTime.ParseExact(VBStrings.Mid(boxIdOld, 6, 6), "yyMMdd", CultureInfo.InvariantCulture); numberOld = long.Parse(VBStrings.Right(boxIdOld, 2)); } if (dateOld != DateTime.Today) { boxIdNew = m_model + "-" + DateTime.Today.ToString("yyMMdd") + "01"; } else { boxIdNew = m_model + "-" + DateTime.Today.ToString("yyMMdd") + (numberOld + 1).ToString("00"); } } sql = "INSERT INTO box_id(" + "boxid," + "suser," + "printdate) " + "VALUES(" + "'" + boxIdNew + "'," + "'" + user + "'," + "'" + DateTime.Now.ToString() + "')"; System.Diagnostics.Debug.Print(sql); yn.sqlExecuteNonQuery(sql, false); return(boxIdNew); }
// Select datatable private string decideReferenceTable(string serno, DateTime tabledate) { string tablekey = string.Empty; string filterkey = string.Empty; if (VBStrings.Mid(serno, 1, 2) == "1C" || VBStrings.Mid(serno, 1, 2) == "1D") { tablekey = "ld4"; filterkey = "LD4"; } else if (serno.Length == 8) { tablekey = "laa10_003"; filterkey = "LA10"; } else { tablekey = "ld25"; filterkey = "LD25"; } // エラー対策 testerTableThisMonth = tablekey + tabledate.ToString("yyyyMM"); //testerTableLastMonth = tablekey + ((VBStrings.Right(DateTime.Today.ToString("yyyyMM"), 2) != "01") ? // (long.Parse(DateTime.Today.ToString("yyyyMM")) - 1).ToString() : (long.Parse(DateTime.Today.ToString("yyyy")) - 1).ToString() + "12"); testerTableLastMonth = tablekey + tabledate.AddMonths(-1).ToString("yyyyMM"); return(filterkey); }
// Sub procedure: Update datagridview public void updateDataGripViews(ref DataGridView dgv, bool load) { string boxId = txtBoxIdFrom.Text; DateTime printDate = dtpPrintDate.Value; DateTime shipDate = dtpShipDate.Value; string buff = txtProductSerial.Text; string serialNo = (buff.IndexOf("+") == -1? buff : VBStrings.Left(buff, buff.IndexOf("+") - 1)); string sql = String.Empty; // Store the sql query result into datatable ShSQL tf = new ShSQL(); if (rdbBoxId.Checked) { sql = "select boxid, suser, printdate, shipdate FROM box_id_cfg" + (boxId == String.Empty ? String.Empty : " WHERE boxid='" + boxId + "'"); } else if (rdbPrintDate.Checked) { sql = "select boxid, suser, printdate, shipdate FROM box_id_cfg WHERE printdate " + "BETWEEN '" + printDate.Date + "' AND '" + printDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59) + "'"; } else if (rdbProductSerial.Checked) { sql = "select boxid FROM product_serial WHERE serialno='" + serialNo + "'"; boxId = tf.sqlExecuteScalarString(sql); txtBoxIdFrom.Text = boxId; sql = "select boxid, suser, printdate, shipdate FROM box_id_cfg" + (boxId == String.Empty ? String.Empty : " WHERE boxid='" + boxId + "'"); } else if (dtpShipDate.Checked) { sql = "select boxid, suser, printdate, shipdate FROM box_id_cfg WHERE shipdate " + "BETWEEN '" + shipDate.Date + "' AND '" + shipDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59) + "'"; } DataTable dt1 = new DataTable(); tf.sqlDataAdapterFillDatatable(sql, ref dt1); // Bind the datatable data into datagridview dgv.DataSource = dt1; dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; // Add button to the datagridview, only when loading the form (only during first time update) if (load) { addButtonsToDataGridView(dgv); } // Set row number in the row header for (int i = 0; i < dgv.Rows.Count; i++) { dgv.Rows[i].HeaderCell.Value = (i + 1).ToString(); } // Adjust the width of the row header dgv.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders); // show the botton of the datagridview if (dgv.Rows.Count != 0) { dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1; } // show barcode graphic in the pannel (not a meaningful function, better delete) pnlBarcode.Refresh(); }
// Issue new box id, register product serials, and save text file for barcode printing private void btnRegisterBoxId_Click(object sender, EventArgs e) { btnRegisterBoxId.Enabled = false; btnDeleteSelection.Enabled = false; btnDeleteAll.Enabled = false; btnCancel.Enabled = false; if (m_model == "LA10") { directory = @"Z:\(01)Motor\(00)Public\11-Suka-Sugawara\LD model\printer\print"; } string boxId = txtBoxId.Text; // If this form' mode is not for EDIT, this botton works for RE-PRINTING barcode label if (!formEditMode) { // バーコードファイルの生成 m_lot = dtOverall.Rows[0]["lot"].ToString(); printBarcode(directory, boxId, m_model, m_lot, dgvDateCode, ref dgvDateCode2, ref txtBoxIdPrint); btnRegisterBoxId.Enabled = true; btnCancel.Enabled = true; return; } // Check if the product serials had already registered in the database table string checkResult = checkDataTableWithRealTable(dtOverall); if (checkResult != String.Empty) { MessageBox.Show("The following serials are already registered with box id:" + Environment.NewLine + checkResult + Environment.NewLine + "Please check and delete.", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2); btnRegisterBoxId.Enabled = true; btnDeleteSelection.Enabled = true; btnDeleteAll.Enabled = true; btnCancel.Enabled = true; return; } // Issue new box id string boxIdNew = getNewBoxId(); // As the first step, add new box id information to the product serial datatable DataTable dt = dtOverall.Copy(); dt.Columns.Add("boxid", Type.GetType("System.String")); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["boxid"] = boxIdNew; } // As the second step, register datatables' each record into database table ShSQL tf = new ShSQL(); bool res1 = tf.sqlMultipleInsertOverall(dt); bool res2 = true; //tf.sqlMultipleInsertNoise(dtSI); if (res1 & res2) { // Print barcode printBarcode(directory, boxIdNew, m_lot, m_model, dgvDateCode, ref dgvDateCode2, ref txtBoxIdPrint); // Clear the datatable dtOverall.Clear(); dt = null; txtBoxId.Text = boxIdNew; dtpPrintDate.Value = DateTime.ParseExact(VBStrings.Mid(boxIdNew, 6, 6), "yyMMdd", CultureInfo.InvariantCulture); // Generate delegate event to update parant form frmBoxid's datagridview (box id list) this.RefreshEvent(this, new EventArgs()); this.Focus(); MessageBox.Show("The box id " + boxIdNew + " and " + Environment.NewLine + "its product serials were registered.", "Process Result", MessageBoxButtons.OK, MessageBoxIcon.Information); txtBoxId.Text = String.Empty; txtProductSerial.Text = String.Empty; updateDataGripViews(dtOverall, ref dgvProductSerial); btnRegisterBoxId.Enabled = false; btnDeleteSelection.Enabled = false; btnDeleteAll.Enabled = false; btnCancel.Enabled = true; } else { MessageBox.Show("Box id and product serials were not registered." + System.Environment.NewLine + @"Please try again by clicking ""Register Box ID"".", "Process Result", MessageBoxButtons.OK, MessageBoxIcon.Warning); btnRegisterBoxId.Enabled = true; btnDeleteSelection.Enabled = true; btnDeleteAll.Enabled = true; btnCancel.Enabled = true; } }
// Select datatable private string decideReferenceTable(string serno, DateTime tbldate) { string tablekey = string.Empty; string filterkey = string.Empty; switch (VBStrings.Mid(serno, 3, 2)) { case "4A": tablekey = "ls12_004a"; filterkey = "LS4A"; break; case "4D": tablekey = "ls12_004d"; filterkey = "LS4D"; break; case "3D": tablekey = "ls12_003d"; filterkey = "LS3D"; break; case "3E": tablekey = "ls12_003e"; filterkey = "LS3E"; break; case "3F": tablekey = "ls12_003f"; filterkey = "LS3F"; break; case "3K": tablekey = "ls12_003k"; filterkey = "LS3K"; break; case "3J": tablekey = "ls12_003j"; filterkey = "LS3J"; break; case "3P": tablekey = "ls12_003p"; filterkey = "LS3P"; break; default: if (serno.Length == 8) { tablekey = "laa10_003"; } filterkey = "LA10"; if (serno.Length == 13) { tablekey = "bms_0069"; } filterkey = "BMS69"; if (VBStrings.Mid(serno, 6, 1) == "L") { tablekey = "ls12_003l"; } filterkey = "LS3L"; if (VBStrings.Left(serno, 1) == "M") { tablekey = "ls12_003mod"; } filterkey = "LMOD"; break; } //if (VBStrings.Mid(serno, 3, 2) == "3D") //{ tablekey = "ls12_003d"; filterkey = "LS3D"; } //else if (VBStrings.Mid(serno, 3, 2) == "3E") //{ tablekey = "ls12_003e"; filterkey = "LS3E"; } //else if (VBStrings.Mid(serno, 3, 2) == "3F") //{ tablekey = "ls12_003f"; filterkey = "LS3F"; } //else if (VBStrings.Mid(serno, 3, 2) == "3J") //{ tablekey = "ls12_003j"; filterkey = "LS3J"; } //else if (VBStrings.Mid(serno, 3, 2) == "3K") //{ tablekey = "ls12_003k"; filterkey = "LS3K"; } //else if (VBStrings.Mid(serno, 3, 2) == "4A") //{ tablekey = "ls12_004a"; filterkey = "LS4A"; } //else if (VBStrings.Mid(serno, 3, 2) == "3L") //{ tablekey = "ls12_003l"; filterkey = "LS3L"; } //else if (serno.Length == 8) //{ tablekey = "laa10_003"; filterkey = "LA10"; } ShSQL sql = new ShSQL(); //testerTableLastMonth = tablekey + ((VBStrings.Right(DateTime.Today.ToString("yyyyMM"), 2) != "01") ? // (long.Parse(DateTime.Today.ToString("yyyyMM")) - 1).ToString() : (long.Parse(DateTime.Today.ToString("yyyy")) - 1).ToString() + "12"); int n = 3; B: testerTableThisMonth = tablekey + tbldate.ToString("yyyyMM"); if (!sql.CheckTableExist(testerTableThisMonth) && n > 0) { n--; tbldate = tbldate.AddMonths(-1); goto B; } n = 3; C: testerTableLastMonth = tablekey + tbldate.AddMonths(-1).ToString("yyyyMM"); if (!sql.CheckTableExist(testerTableLastMonth) && n > 0) { n--; tbldate = tbldate.AddMonths(-1); goto C; } else if (n == 0) { testerTableLastMonth = testerTableThisMonth; } return(filterkey); }
// Event when a module is scanned private void txtProductSerial_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { // Disenalbe the extbox to block scanning txtProductSerial.Enabled = false; string serLong = txtProductSerial.Text; string serShort; string m_short = VBStrings.Mid(serLong, 3, 2); //switch (m_short) //{ // case "3L": serShort = serLong; //} DateTime sDate = DateTime.Today; A: string filterkey = decideReferenceTable(serShort, sDate); if (serLong != String.Empty) { // Get the tester data from current month's table and store it in datatable string filterLine = string.Empty; if (filterkey == "LA10") { filterLine = fltlaa; } else { filterLine = fltls; } string sql = "select serno, process, judge, inspectdate from " + "(select serno, process, judge, max(inspectdate) as inspectdate, row_number() OVER (PARTITION BY process ORDER BY max(inspectdate) desc) as flag from (" + "(select serno, process, case when tjudge = '0' then 'PASS' else 'FAIL' end as judge, inspectdate from " + testerTableThisMonth + " where " + filterLine + " and serno = '" + serShort + "') union all " + "(select serno, process, case when tjudge = '0' then 'PASS' else 'FAIL' end as judge, inspectdate from " + testerTableLastMonth + " where " + filterLine + " and serno = '" + serShort + "')" + ") d group by serno, judge, process order by judge desc, process) b where flag = 1"; DataTable dt1 = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); if (dt1.Rows.Count <= 0) { if (sDate.Year.ToString() == "2015") { MessageBox.Show("Not Found!"); goto B; } sDate = sDate.AddMonths(-1); goto A; } B: System.Diagnostics.Debug.Print(sql); // Get the tester data from last month's table and store it in the same datatable //sql = "SELECT serno, process, tjudge, inspectdate" + // " FROM " + testerTableLastMonth + // " WHERE serno = '" + serShort + "'"; //tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); System.Diagnostics.Debug.Print(sql); DataView dv = new DataView(dt1); //dv.RowFilter = filterLine; //dv.Sort = "tjudge, inspectdate desc"; System.Diagnostics.Debug.Print(System.Environment.NewLine + "Inline:"); printDataView(dv); DataTable dt2 = dv.ToTable(); //�@インライン // 一時テーブルへの登録準備 string lot = string.Empty; string fact = "2A"; string model = string.Empty; switch (m_short) { case "4A": model = "LS4A"; lot = VBStrings.Mid(serShort, 5, 3); break; case "4D": model = "LS4D"; lot = VBStrings.Mid(serShort, 5, 3); break; case "3D": model = "LS3D"; lot = VBStrings.Mid(serShort, 5, 3); break; case "3E": model = "LS3E"; lot = VBStrings.Mid(serShort, 5, 3); break; case "3F": model = "LS3F"; lot = VBStrings.Mid(serShort, 5, 3); break; case "3K": model = "LS3K"; lot = VBStrings.Mid(serShort, 5, 3); break; case "3J": model = "LS3J"; lot = VBStrings.Mid(serShort, 5, 3); break; case "3P": model = "LS3P"; lot = VBStrings.Mid(serShort, 5, 3); break; default: if (serLong.Length == 13) { model = "BMS69"; lot = VBStrings.Mid(serShort, 3, 3); } else if (serLong.Length == 8) { model = "LA10"; lot = VBStrings.Mid(serShort, 5, 3); } else if (VBStrings.Mid(serLong, 6, 1) == "L") { model = "LS3L"; lot = VBStrings.Mid(serShort, 3, 3); } else if (VBStrings.Left(serLong, 1) == "M") { model = "LMOD"; lot = VBStrings.Mid(serShort, 5, 3); } else { model = "Error"; } break; } // Even when no tester data is found, the module have to appear in the datagridview DataRow newrow = dtOverall.NewRow(); newrow["serialno"] = serLong; newrow["model"] = model; newrow["lot"] = lot; newrow["fact"] = fact; // If tester data exists, show it in the datagridview if (dt2.Rows.Count != 0) { string process = dt2.Rows[0][1].ToString(); string linepass = dt2.Rows[0][2].ToString(); //string buff = //if (buff == "0") linepass = "******"; //else if (buff == "1") linepass = "******"; //else linepass = "******"; DateTime testtime = (DateTime)dt2.Rows[0][3]; newrow["process"] = process; newrow["linepass"] = linepass; newrow["testtime"] = testtime; } // Add the row to the datatable dtOverall.Rows.Add(newrow); //Set limit if (dtOverall.Rows.Count == 1) { string m = dtOverall.Rows[0]["model"].ToString(); switch (m) { case "LS4A": limit = limitls4a; break; case "LS4D": limit = limitls4a; break; case "LS3D": case "LS3E": case "LS3F": case "LS3J": case "LS3K": case "LS3L": case "LS3P": case "LMOD": case "BMS69": limit = limitls12; break; case "LA10": limit = limitlaa; break; default: limit = 9999; break; } //if (m == "LS4A") limit = limitls4a; //else if (m == "LS3D" || m == "LS3E" || m == "LS3F" || m == "LS3J" || m == "LS3K" || m == "LS3L") limit = limitls12; //else if (m == "LA10") limit = limitlaa; //else limit = 9999; // USER9がLIMITを設定した場合は、それに従う if (limit1 != 0) { limit = limit1; } } // データグリットビューの更新 updateDataGripViews(dtOverall, ref dgvProductSerial); } // For the operator to continue scanning, enable the scan text box and select the text in the box if (okCount >= limit) { txtProductSerial.Enabled = false; } else { txtProductSerial.Enabled = true; txtProductSerial.Focus(); txtProductSerial.SelectAll(); } } }
// シリアル付帯情報を取得する private void setSerialInfoAndTesterResult(string serLong) { DateTime tbdate = DateTime.Today; B: string filterkey = decideReferenceTable(serLong, tbdate); if (serLong != String.Empty) { // Get the tester data from current month's table and store it in datatable string sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableThisMonth + " WHERE serno = '" + serLong + "'"; DataTable dt1 = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); System.Diagnostics.Debug.Print(sql); // Get the tester data from last month's table and store it in the same datatable sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableLastMonth + " WHERE serno = '" + serLong + "'"; tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); if (dt1.Rows.Count <= 0) { tbdate = tbdate.AddMonths(-1); goto B; } System.Diagnostics.Debug.Print(sql); string filterLine = string.Empty; if (filterkey == "LD4") { filterLine = fltld4; } else { filterLine = fltld25; } DataView dv = new DataView(dt1); dv.RowFilter = filterLine; dv.Sort = "tjudge, inspectdate desc"; System.Diagnostics.Debug.Print(System.Environment.NewLine + "In-Line:"); printDataView(dv); DataTable dt2 = dv.ToTable(); string lot = VBStrings.Mid(serLong, 3, 4); string line = VBStrings.Mid(serLong, 7, 1); string config = VBStrings.Mid(serLong, 1, 2); string model = string.Empty; if (VBStrings.Mid(serLong, 1, 2) == "1C" || VBStrings.Mid(serLong, 1, 2) == "1D") { model = "LD04"; } else { model = "LD25"; } // Even when no tester data is found, the module have to appear in the datagridview DataRow newr = dtReplace.NewRow(); newr["serialno"] = serLong; newr["model"] = model; newr["lot"] = lot; newr["line"] = line; newr["config"] = config; // If tester data exists, show it in the datagridview if (dt2.Rows.Count != 0) { string process = dt2.Rows[0][1].ToString(); string linepass = String.Empty; string buff = dt2.Rows[0][2].ToString(); if (buff == "0") { linepass = "******"; } else if (buff == "1") { linepass = "******"; } else { linepass = "******"; } DateTime testtime = (DateTime)dt2.Rows[0][3]; newr["process"] = process; newr["linepass"] = linepass; newr["testtime"] = testtime; } dtReplace.Rows.Add(newr); } }
// Issue new box id, register product serials, and save text file for barcode printing private void btnRegisterBoxId_Click(object sender, EventArgs e) { btnRegisterBoxId.Enabled = false; btnDeleteSelection.Enabled = false; btnDeleteAll.Enabled = false; btnCancel.Enabled = false; string boxId = txtBoxId.Text; // If this form' mode is not for EDIT, this botton works for RE-PRINTING barcode lable if (btnRegisterBoxId.Text == "Register Again") { DataTable dt_c = dtOverall.Copy(); dt_c.Columns.Add("boxid", Type.GetType("System.String")); for (int i = 0; i < dt_c.Rows.Count; i++) { dt_c.Rows[i]["boxid"] = txtBoxId.Text; } ShSQL sh = new ShSQL(); bool res = sh.sqlMultipleInsert(dt_c); btnCancel.Enabled = true; MessageBox.Show("The box id " + txtBoxId.Text + " and " + Environment.NewLine + "its product serials were registered.", "Process Result", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } // Check if the product serials had already registered in the database table string checkResult = checkDataTableWithRealTable(dtOverall); if (checkResult != String.Empty) { MessageBox.Show("The following serials are already registered with box id:" + Environment.NewLine + checkResult + Environment.NewLine + "Please check and delete.", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2); btnRegisterBoxId.Enabled = true; btnDeleteSelection.Enabled = true; btnDeleteAll.Enabled = true; btnCancel.Enabled = true; return; } // Issue new box id string boxIdNew; if (btnRegisterBoxId.Text == "Register Box ID") { boxIdNew = getNewBoxId(); } else { boxIdNew = boxId; } // As the first step, add new box id information to the product serial datatable DataTable dt = dtOverall.Copy(); dt.Columns.Add("boxid", Type.GetType("System.String")); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["boxid"] = boxIdNew; } // As the second step, register datatables' each record into database table ShSQL tf = new ShSQL(); bool res1 = tf.sqlMultipleInsertOverall(dt); bool res2 = false; if ((VBStrings.Left(boxIdNew, 4) == "LA10" || VBStrings.Left(boxIdNew, 5) == "BMA60") && txtOkCount.Text == "3000") { res2 = true; } if (res1 & res2) { if (okCount == limit && dgvProductSerial.Rows.Count == limit) { // Print barcode printBarcode(directory, boxIdNew, config, m_model, dgvDateCode, ref dgvDateCode2, ref txtBoxIdPrint); // Clear the datatable dtOverall.Clear(); txtBoxId.Text = boxIdNew; if (boxIdNew.Contains("BMA60")) { dtpPrintDate.Value = DateTime.ParseExact(VBStrings.Mid(boxIdNew, 7, 6), "yyMMdd", CultureInfo.InvariantCulture); } else { dtpPrintDate.Value = DateTime.ParseExact(VBStrings.Mid(boxIdNew, 6, 6), "yyMMdd", CultureInfo.InvariantCulture); } } // Generate delegate event to update parant form frmBoxid's datagridview (box id list) this.RefreshEvent(this, new EventArgs()); this.Focus(); MessageBox.Show("The box id " + boxIdNew + " and " + Environment.NewLine + "its product serials were registered.", "Process Result", MessageBoxButtons.OK, MessageBoxIcon.Information); txtBoxId.Text = String.Empty; txtProductSerial.Text = String.Empty; updateDataGripViews(dtOverall, ref dgvProductSerial); btnRegisterBoxId.Enabled = false; btnPrint.Enabled = false; btnDeleteSelection.Enabled = false; btnDeleteAll.Enabled = false; btnCancel.Enabled = true; } else { MessageBox.Show("Box id and product serials were registered without print the label.", "Process Result", MessageBoxButtons.OK, MessageBoxIcon.Warning); //btnRegisterBoxId.Enabled = true; btnDeleteSelection.Enabled = true; btnDeleteAll.Enabled = true; btnCancel.Enabled = true; } }
// Event when a module is scanned private void txtProductSerial_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { // Disenalbe the textbox to block scanning txtProductSerial.Enabled = false; string serLong = txtProductSerial.Text; string serShort = serLong; DateTime tbDate = DateTime.Today; A: string filterkey = decideReferenceTable(serShort, tbDate); if (serLong != String.Empty) { // Get the tester data from current month's table and store it in datatable string sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableThisMonth + " WHERE serno = '" + serShort + "'"; DataTable dt1 = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); System.Diagnostics.Debug.Print(sql); // Get the tester data from last month's table and store it in the same datatable sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableLastMonth + " WHERE serno = '" + serShort + "'"; tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); if (dt1.Rows.Count <= 0) { tbDate = tbDate.AddMonths(-1); goto A; } System.Diagnostics.Debug.Print(sql); string filterLine = string.Empty; if (filterkey == "LD4") { filterLine = fltld4; } else if (filterkey == "LA10" || filterkey == "BMA60" || filterkey == "BMA-") { filterLine = fltlaa; } else { filterLine = fltld25; } DataView dv = new DataView(dt1); dv.RowFilter = filterLine; dv.Sort = "tjudge, inspectdate desc"; System.Diagnostics.Debug.Print(System.Environment.NewLine + "In-Line:"); printDataView(dv); DataTable dt2 = dv.ToTable(); //�@インライン // 一時テーブルへの登録準備 string lot = string.Empty; string line = string.Empty; string config = VBStrings.Mid(serShort, 1, 2); string model = string.Empty; if (VBStrings.Mid(serLong, 1, 2) == "1C" || VBStrings.Mid(serLong, 1, 2) == "1D") { model = "LD04"; } else if (serLong.Length == 8) { //model = "LA10"; model = "BMA60"; } else { model = "LD25"; } if (model == "BMA60") { line = "1"; lot = VBStrings.Mid(serShort, 1, 4); } else { line = VBStrings.Mid(serShort, 7, 1); lot = VBStrings.Mid(serShort, 3, 4); } // Even when no tester data is found, the module have to appear in the datagridview DataRow newrow = dtOverall.NewRow(); newrow["serialno"] = serLong; newrow["model"] = model; newrow["lot"] = lot; newrow["line"] = line; newrow["config"] = config; // If tester data exists, show it in the datagridview if (dt2.Rows.Count != 0) { string process = dt2.Rows[0][1].ToString(); string linepass = String.Empty; string buff = dt2.Rows[0][2].ToString(); if (buff == "0") { linepass = "******"; } else if (buff == "1") { linepass = "******"; } else { linepass = "******"; } DateTime testtime = (DateTime)dt2.Rows[0][3]; newrow["process"] = process; newrow["linepass"] = linepass; newrow["testtime"] = testtime; } // Add the row to the datatable dtOverall.Rows.Add(newrow); //Set limit if (dtOverall.Rows.Count >= 1) { string m = dtOverall.Rows[0]["model"].ToString(); switch (m) { case "LD04": limit = limitld4; break; case "LD25": limit = limitld; break; case "LA10": case "BMA60": case "BMA-": limit = limitlaa; break; default: limit = 9999; break; } txtLimit.Text = limit.ToString(); // USER9がLIMITを設定した場合は、それに従う if (limit1 != 0) { limit = limit1; } } // データグリットビューの更新 updateDataGripViews(dtOverall, ref dgvProductSerial); } // For the operator to continue scanning, enable the scan text box and select the text in the box if (okCount >= limit) { txtProductSerial.Enabled = false; } else { txtProductSerial.Enabled = true; txtProductSerial.Focus(); txtProductSerial.SelectAll(); } } }
// フォームの条件をANDで結合し、SQL問い合わせ結果をデータグリッドビューに反映 private void btnSearch_Click(object sender, EventArgs e) { string idFrom = txtBoxIdFrom.Text; string idTo = txtBoxIdTo.Text; DateTime dateFrom = dtpPrintDateFrom.Value; DateTime dateTo = dtpPrintDateTo.Value; string serFrom = txtProductSerialFrom.Text; string serTo = txtProductSerialTo.Text; string config = cmbConfig.Text; string sql1 = "select boxid, printdate, serialno, lot, fact, process, linepass, testtime FROM product_serial_printdate WHERE "; bool[] cr = { idFrom == String.Empty ? false : true, idTo == String.Empty ? false : true, true, true, serFrom == String.Empty ? false : true, serTo == String.Empty ? false : true, config == String.Empty ? false : true }; string sql2 = (!cr[0] ? String.Empty : "boxid >= '" + idFrom + "' AND ") + (!cr[1] ? String.Empty : "boxid <= '" + idTo + "' AND ") + "printdate >= '" + dateFrom.ToString() + "' AND " + "printdate <= '" + dateTo.ToString() + "' AND " + (!cr[4] ? String.Empty : "serialno >= '" + serFrom + "' AND ") + (!cr[5] ? String.Empty : "serialno <= '" + serTo + "' AND ") + (!cr[6] ? String.Empty : "config2 = '" + config + "' AND "); string sql3 = sql1 + VBStrings.Left(sql2, sql2.Length - 5); System.Diagnostics.Debug.Print(sql3); btnSearch.Enabled = false; if (rdbOn.Checked) { DialogResult result1 = MessageBox.Show("With the summary function On, the process takes time." + System.Environment.NewLine + "Do you poceed with the summary function On ?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2); if (result1 == DialogResult.No) { return; } } DataTable dataTable = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatable(sql3, ref dataTable); bool count = dataTable.Rows.Count > 200000 ? true : false; if (rdbOn.Checked && count) { MessageBox.Show("The record count is over 200,000. The summary function is turned off.", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2); rdbOff.Checked = true; } if (rdbOn.Checked) { updateDataGripViews(dataTable, ref dgvProductSerial, true); } else if (rdbOff.Checked) { updateDataGripViews(dataTable, ref dgvProductSerial, false); } btnSearch.Enabled = true; }
private void txtAfterSerial_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { string serLong = txtAfterSerial.Text; string filterkey = decideReferenceTable(serLong); if (serLong != String.Empty) { // Get the tester data from current month's table and store it in datatable string sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableThisMonth + " WHERE serno = '" + serLong + "' order by tjudge, inspectdate desc limit 1"; DataTable dtReplace = new DataTable(); defineAndReadDtOverall(ref dtReplace); DataTable dt1 = new DataTable(); tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); // Get the tester data from last month's table and store it in the same datatable sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableLastMonth + " WHERE serno = '" + serLong + "' order by tjudge, inspectdate desc limit 1"; tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); // Even when no tester data is found, the module have to appear in the datagridview DataRow newr = dtReplace.NewRow(); string lot = VBStrings.Mid(serLong, 3, 4); string line = string.Empty; string config = VBStrings.Mid(serLong, 1, 2); string model = string.Empty; if (VBStrings.Mid(serLong, 1, 2) == "1C" || VBStrings.Mid(serLong, 1, 2) == "1D") { model = "LD04"; } else if (serLong.Length == 8) { model = "LA10"; } else { model = "LD25"; } if (model == "LA10") { line = "1"; lot = VBStrings.Mid(serLong, 1, 4); } else { line = VBStrings.Mid(serLong, 7, 1); lot = VBStrings.Mid(serLong, 3, 4); } // If tester data exists, show it in the datagridview if (dt1.Rows.Count != 0) { string process = dt1.Rows[0][1].ToString(); string linepass = String.Empty; string buff = dt1.Rows[0][2].ToString(); if (buff == "0") { linepass = "******"; } else if (buff == "1") { linepass = "******"; } else { linepass = "******"; } DateTime testtime = (DateTime)dt1.Rows[0][3]; newr["process"] = process; newr["linepass"] = linepass; newr["testtime"] = testtime; } newr["serialno"] = serLong; newr["model"] = model; newr["lot"] = lot; newr["line"] = line; newr["config"] = config; //Add the row to the datatable dtReplace.Rows.Add(newr); dgvProductSerial.DataSource = dtReplace; txtRep.Focus(); bool col = colorViewForFailAndBlank(ref dgvProductSerial); if (col) { btnReplace.Enabled = true; txtAfterSerial.Enabled = false; txtBeforeSerial.Enabled = false; } else { txtAfterSerial.Focus(); txtAfterSerial.SelectAll(); } } } }
// シリアル付帯情報を取得する private void setSerialInfoAndTesterResult(string serLong) { if (serLong != String.Empty) { // Get the tester data from current month's table and store it in datatable string sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableThisMonth + " WHERE serno = '" + serLong + "'"; DataTable dt1 = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); System.Diagnostics.Debug.Print(sql); // Get the tester data from last month's table and store it in the same datatable sql = "SELECT serno, process, tjudge, inspectdate" + " FROM " + testerTableLastMonth + " WHERE serno = '" + serLong + "'"; tf.sqlDataAdapterFillDatatableFromTesterDb(sql, ref dt1); System.Diagnostics.Debug.Print(sql); DataView dv = new DataView(dt1); dv.Sort = "tjudge desc, inspectdate"; DataTable dt2 = dv.ToTable(); // 一時テーブルへの登録準備 string lot = VBStrings.Mid(serLong, 5, 3); string fact = VBStrings.Mid(serLong, 8, 1); string model = string.Empty; if (VBStrings.Mid(serLong, 3, 2) == "4A") { model = "LS4A"; } else if (VBStrings.Mid(serLong, 3, 2) == "3D") { model = "LS3D"; } else if (VBStrings.Mid(serLong, 3, 2) == "3E") { model = "LS3E"; } else if (VBStrings.Mid(serLong, 3, 2) == "3F") { model = "LS3F"; } else if (VBStrings.Mid(serLong, 3, 2) == "3J") { model = "LS3J"; } else { model = "Error"; } // テスターデータに該当がない場合でも、ユーザーに認識させるために表示する // また、本フォームのDATATABLEは、常に1件のみのデータ保持でよい dataTable.Clear(); DataRow newrow = dataTable.NewRow(); newrow["serialno"] = serLong; newrow["model"] = model; newrow["lot"] = lot; newrow["fact"] = fact; // テスターデータに該当がある場合、当然表示する if (dt2.Rows.Count != 0) { string process = dt2.Rows[0][1].ToString(); string linepass = String.Empty; string buff = dt2.Rows[0][2].ToString(); if (buff == "0") { linepass = "******"; } else if (buff == "1") { linepass = "******"; } else { linepass = "******"; } DateTime testtime = (DateTime)dt2.Rows[0][3]; newrow["process"] = process; newrow["linepass"] = linepass; newrow["testtime"] = testtime; } // メモリ上のテーブルにレコードを追加 dataTable.Rows.Add(newrow); } }