// 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();
}
// フォームの条件をＡＮＤで結合し、ＳＱＬ問い合わせ結果をデータグリッドビューに反映
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;
}
