// 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); } }
// Sub procedure: Read product serial records from database to datatable private void readDatatable(ref DataTable dt) { string boxId = txtBoxId.Text; string sql = "select serialno, lot, fact, process, linepass, testtime " + "FROM product_serial WHERE boxid='" + boxId + "'"; ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatable(sql, ref dt); }
private void txtSerial_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { // Disenalbe the textbox to block scanning txtSerial.Enabled = false; string serno = txtSerial.Text; if (serno != String.Empty) { string sql = "select boxid, serno, model, ship_date, status from shipment where serno = '" + serno + "'"; DataTable dt1 = new DataTable(); ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatable(sql, ref dt1); System.Diagnostics.Debug.Print(sql); DataView dv = new DataView(dt1); //System.Diagnostics.Debug.Print(System.Environment.NewLine); printDataView(dv); DataTable dt2 = dv.ToTable(); // Even when no tester data is found, the module have to appear in the datagridview DataRow newrow = dtHistory.NewRow(); newrow["serial"] = serno; // If tester data exists, show it in the datagridview if (dt1.Rows.Count != 0) { string model = dt1.Rows[0][2].ToString(); string ship_date = dt1.Rows[0][3].ToString(); string status = dt1.Rows[0][4].ToString(); string boxid = dt1.Rows[0][0].ToString(); newrow["boxid"] = boxid; newrow["model"] = model; newrow["shipdate"] = ship_date; newrow["status"] = status; } // Add the row to the datatable dtHistory.Rows.Add(newrow); // ƒf[ƒ^ƒOƒŠƒbƒgƒrƒ…[‚ÌXV updateDataGridViews(dtHistory, ref dgvHistory); } txtSerial.Enabled = true; txtSerial.Focus(); txtSerial.SelectAll(); } }
// Sub procedure: Get module recors from database and set them into this form's datatable private void defineAndReadDtOverall(ref DataTable dt) { string boxId = txtBoxId.Text; dt.Columns.Add("serialno", Type.GetType("System.String")); dt.Columns.Add("model", Type.GetType("System.String")); dt.Columns.Add("lot", Type.GetType("System.String")); dt.Columns.Add("fact", Type.GetType("System.String")); dt.Columns.Add("process", Type.GetType("System.String")); dt.Columns.Add("linepass", Type.GetType("System.String")); dt.Columns.Add("testtime", Type.GetType("System.DateTime")); if (!formEditMode) { string sql = "select serialno, lot, fact, process, linepass, testtime, model " + "FROM product_serial WHERE boxid='" + boxId + "'"; ShSQL tf = new ShSQL(); tf.sqlDataAdapterFillDatatable(sql, ref dt); } }
// 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(); }
// フォームの条件を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; }