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;
// ＵＳＥＲ９がＬＩＭＩＴを設定した場合は、それに従う
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();
// ＵＳＥＲ９がＬＩＭＩＴを設定した場合は、それに従う
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 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.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);
}
}
