// 印刷ボタン押下時処理
public void printStart(string labelType1, string id, DataTable dtLot, string batch, DateTime pdate,
string labelType2, string labelType3, short copies, string bin)
{
ltype2 = labelType2;
ltype3 = labelType3;
trayPackCartonId = id;
dtLotSumary = dtLot;
qty = dtLotSumary.Rows.Count >= 0 ? dtLotSumary.Rows[0]["total"].ToString() : string.Empty;
trayIdQtyCombo = id + " " + qty.ToString();
// ロットサマリーテーブルの格納、および、ＤＢ上ラベルコンテンツ情報の取得
if (labelType1 != "tray")
{
rev = batch;
packdate = pdate.ToString("yyyy/M/d");
dtLblContent = new DataTable();
string sql = "select model, header, content from t_label_content";
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dtLblContent);
vendor = pickUpFromDataTable(dtLblContent, "VENDOR");
config = pickUpFromDataTable(dtLblContent, "CONFIG");
desc = pickUpFromDataTable(dtLblContent, "DESC");
footnote = pickUpFromDataTable(dtLblContent, "FOOTNOTE");
model = pickUpFromDataTable(dtLblContent, "MODEL");
string pnFox = pickUpFromDataTable(dtLblContent, "P/N_FOX");
string pnPega = pickUpFromDataTable(dtLblContent, "P/N_PEGA");
apn = pnFox;
pn = (ltype3 == "Fox") ? apn : (ltype3 == "Pega") ? pnPega : (ltype3 == "Non") ? "Non" : "Error!";
}
// PrintDocumentコンポーネントオブジェクトを生成
System.Drawing.Printing.PrintDocument pd = new System.Drawing.Printing.PrintDocument();
pd.PrinterSettings.PrinterName = printerName; // 出力先プリンタ名を指定
pd.PrinterSettings.Copies = copies; // 印字枚数を指定
// PrintPageイベントハンドラに追加
if (labelType1 == "tray")
{
pd.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(printTray);
}
else if (labelType1 == "packCartonInternal")
{
// 2016.07.28 PEC FUJIKI (印刷部分の仕様変更対応で追加) FROM
page_counter = 0;
data_counter = 0;
// 2016.07.28 PEC FUJIKI (印刷部分の仕様変更対応で追加) TO
pd.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(printPackCartonPalletInternal);
}
else if (labelType1 == "packCartonPega")
{
pd.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(printPackCartonPalletPega);
}
pd.Print(); // 印刷の選択ダイアログを表示
}
// サブプロシージャ：ＤＢからデータテーブルへの読み込み
private void readModuleInfo(ref DataTable dt)
{
dt.Rows.Clear();
string sql = "select module_id, lot, bin, tester_id, test_result, test_date, r_mode " +
"from t_module where tray_id='" + txtTrayId.Text + "'";
TfSQL tf = new TfSQL();
System.Diagnostics.Debug.Print(sql);
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
}
// サブプロシージャ：ＤＢからデータテーブルへの読み込み
private void readTrayInfo(ref DataTable dt)
{
dt.Rows.Clear();
string sql = "select tray_id, lot, qty, register_date, rg_dept, multi_lot, " +
"'OK' as check from t_tray where pack_id='" + txtPackId.Text + "'";
TfSQL tf = new TfSQL();
System.Diagnostics.Debug.Print(sql);
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
}
// サブプロシージャ：ＤＢからデータテーブルへの読み込み
private void readCartonInfo(ref DataTable dt)
{
dt.Rows.Clear();
string sql = "select carton_id, lot, m_qty, batch, register_date, rg_user, " +
"case when l_cnt >= 2 then 'T' else 'F' end as multi_lot, " +
"'OK' as check from t_carton where pallet_id='" + txtPallet.Text + "'";
TfSQL tf = new TfSQL();
System.Diagnostics.Debug.Print(sql);
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
}
// サブプロシージャ：ロット集計グリッドビューを更新し、数量の最も多いロット番号を返す
public string updateLotSummary(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
dtLot = new DataTable();
dgvLotSummary.DataSource = dtLot;
return(string.Empty);
}
// 一時テーブルに、ロット集計を格納する
string sql1 = "select lot, count(lot) as qty from t_module where tray_id in (";
string sql2 = string.Empty;
var query1 = dt.AsEnumerable()
.Select(row => new { tray_id = row.Field <string>("tray_id") });
foreach (var q in query1)
{
sql2 += "'" + q.tray_id + "', ";
}
string sql3 = sql1 + VBS.Left(sql2, sql2.Length - 2) + ") group by lot order by qty desc, lot";
System.Diagnostics.Debug.Print(sql3);
DataTable dtTemp = new DataTable();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql3, ref dtTemp);
// ロット集計表示テーブルに、一時テーブルの情報を移す
dgvLotSummary.DataSource = null;
dgvLotSummary.Refresh();
dtLot = new DataTable();
var query2 = dtTemp.AsEnumerable().Select(r => new { lot = r.Field <string>("lot"), qty = r.Field <Int64>("qty") });
// 列の追加
foreach (var q in query2)
{
dtLot.Columns.Add(q.lot, typeof(int));
}
dtLot.Columns.Add("total", typeof(int));
// 行の追加
dtLot.Rows.Add();
foreach (var q in query2)
{
dtLot.Rows[0][q.lot] = q.qty;
}
dtLot.Rows[0]["total"] = query2.Sum(a => a.qty);
dgvLotSummary.DataSource = dtLot;
return(query2.First().lot);
}
// ＬＯＴ集計グリッドビュー左のボタンを押下時、対象のモジュールをエクセルファイルへ出力する
private void btnExportModule_Click(object sender, EventArgs e)
{
// 2016.08.29 FUJII 別スレッドで処理（処理スピード対策）
var task = Task.Factory.StartNew(() =>
{
DataTable dt = new DataTable();
string sql = "select *, '" + txtPackId.Text + "' as pack_id from t_module where tray_id in (" +
"select tray_id from t_tray where pack_id = '" + txtPackId.Text + "')";
TfSQL tf = new TfSQL();
System.Diagnostics.Debug.Print(sql);
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
ExcelClass xl = new ExcelClass();
// 2016.08.29 FUJII エクセルへの出力から、デスクトップＣＳＶへの出力へ変更
xl.ExportToCsv(dt, System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\pack.csv");
});
}
// 変更後モジュールがスキャンされたときの処理
private void txtAfter_KeyDown(object sender, KeyEventArgs e)
{
// エンターキーの場合、テキストボックスの桁数が１５桁の場合のみ、処理を行う
if (e.KeyCode != Keys.Enter || txtAfter.Text.Length != 15)
{
return;
}
// 置換モード、または、追加モードの場合のみ、処理を行う
if (mode == "replace" || mode == "add")
{
string carton = txtAfter.Text;
string sql = "select carton_id, lot, m_qty, batch, register_date, rg_user, " +
"case when l_cnt >= 2 then 'T' else 'F' end as multi_lot, " +
"case when cancel_date is not null then to_char(cancel_date,'YYYY/MM/DD') " +
"when pallet_id is not null then pallet_id else 'OK' end as check " +
"from t_carton where carton_id='" + carton + "'";
System.Diagnostics.Debug.Print(sql);
DataTable dt = new DataTable();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
// テスターデータに該当がない場合でも、ユーザー認識用に表示するための処理
dtCarton.Rows.Clear();
DataRow dr = dtCarton.NewRow();
dr["carton_id"] = carton;
// テスターデータに該当がある場合の処理
if (dt.Rows.Count != 0)
{
dr["lot"] = (string)dt.Rows[0]["lot"];
dr["m_qty"] = (int)dt.Rows[0]["m_qty"];
dr["batch"] = (string)dt.Rows[0]["batch"];
dr["register_date"] = (DateTime)dt.Rows[0]["register_date"];
dr["rg_user"] = (string)dt.Rows[0]["rg_user"];
dr["multi_lot"] = (string)dt.Rows[0]["multi_lot"];
dr["check"] = (string)dt.Rows[0]["check"];
}
// メモリ上のテーブルにレコードを追加
dtCarton.Rows.Add(dr);
// データグリットビューの更新
updateDataGridViews(dtCarton, ref dgvCarton);
}
}
// ディープキャンセル対象のカートンを、デスクトップのCSVをインポートして選択する
private void btnImportForDeepCancel_Click(object sender, EventArgs e)
{
string sql1 = "select carton_id, lot, l_cnt, m_qty, batch, register_date, rg_user, cancel_date, cl_user, pallet_id from t_carton where carton_id in ( ";
string sql2 = string.Empty;
//クラスTfImportを使用し、SQL文を作成する
List <TfImport> cartonList = TfImport.loadCartonListFromDesktopCsv(
System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\CartonList.csv");
foreach (var carton in cartonList)
{
sql2 += "'" + carton.CartonNumber + "', ";
}
string sql3 = sql1 + VBS.Left(sql2, sql2.Length - 2) + ") order by carton_id";
System.Diagnostics.Debug.Print(sql3);
// ＳＱＬ結果を、ＤＴＡＡＴＡＢＬＥへ格納
dtCarton.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql3, ref dtCarton);
// データグリットビューへＤＴＡＡＴＡＢＬＥを格納
dgvCarton.DataSource = dtCarton;
//行ヘッダーに行番号を表示する
for (int i = 0; i < dgvCarton.Rows.Count; i++)
{
dgvCarton.Rows[i].HeaderCell.Value = (i + 1).ToString();
}
//行ヘッダーの幅を自動調節する
dgvCarton.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders);
// 一番下の行を表示する
if (dgvCarton.Rows.Count != 0)
{
dgvCarton.FirstDisplayedScrollingRowIndex = dgvCarton.Rows.Count - 1;
}
}
// サブプロシージャ：シリアルの構成要素のパターンが適正か、ユーザーデスクトップの設定ファイルを使用して確認する
private string matchSerialNumberingPattern(string tray)
{
string result = string.Empty;
string sql = "select module_id, substr(module_id, 1, 3) as plant, substr(module_id, 4, 1) as year, substr(module_id, 5, 2) as week, " +
"substr(module_id, 7, 1) as day, substr(module_id, 8, 1) as line, substr(module_id, 12, 4) as eeee, " +
"substr(module_id, 16, 1) as revision, substr(module_id, 19, 1) as mass, substr(module_id, 20, 1) as flexure, " +
"substr(module_id, 21, 1) as cover_base, substr(module_id, 22, 2) as dframe, substr(module_id, 23, 1) as fpc, " +
"substr(module_id, 24, 1) as shift from t_module where tray_id = '" + tray + "'";
System.Diagnostics.Debug.Print(sql);
DataTable dt = new DataTable();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
// ＤＢから取得したモジュール構成要素のリストを、デスクトップで指定されたリストでフィルターを掛け、該当文字列を警告メッセージに追加する
if (!plant1.Any(s => s == "XXX"))
{
List <string> plant2 = dt.AsEnumerable().Select(r => r.Field <string>("plant")).Except(plant1).ToList();
foreach (var e in plant2)
{
result += "Plant '" + e + "'" + Environment.NewLine;
}
}
if (!year1.Any(s => s == "XXX"))
{
List <string> year2 = dt.AsEnumerable().Select(r => r.Field <string>("year")).Except(year1).ToList();
foreach (var e in year2)
{
result += "Year '" + e + "'" + Environment.NewLine;
}
}
if (!week1.Any(s => s == "XXX"))
{
List <string> week2 = dt.AsEnumerable().Select(r => r.Field <string>("week")).Except(week1).ToList();
foreach (var e in week2)
{
result += "Week '" + e + "'" + Environment.NewLine;
}
}
if (!day1.Any(s => s == "XXX"))
{
List <string> day2 = dt.AsEnumerable().Select(r => r.Field <string>("day")).Except(day1).ToList();
foreach (var e in day2)
{
result += "Day '" + e + "'" + Environment.NewLine;
}
}
if (!line1.Any(s => s == "XXX"))
{
List <string> line2 = dt.AsEnumerable().Select(r => r.Field <string>("line")).Except(line1).ToList();
foreach (var e in line2)
{
result += "Line '" + e + "'" + Environment.NewLine;
}
}
if (!eeee1.Any(s => s == "XXX"))
{
List <string> eeee2 = dt.AsEnumerable().Select(r => r.Field <string>("eeee")).Except(eeee1).ToList();
foreach (var e in eeee2)
{
result += "4E '" + e + "'" + Environment.NewLine;
}
}
if (!revision1.Any(s => s == "XXX"))
{
List <string> revision2 = dt.AsEnumerable().Select(r => r.Field <string>("revision")).Except(revision1).ToList();
foreach (var e in revision2)
{
result += "Revision '" + e + "'" + Environment.NewLine;
}
}
if (!mass1.Any(s => s == "XXX"))
{
List <string> mass2 = dt.AsEnumerable().Select(r => r.Field <string>("mass")).Except(mass1).ToList();
foreach (var e in mass2)
{
result += "Mass '" + e + "'" + Environment.NewLine;
}
}
if (!flexure1.Any(s => s == "XXX"))
{
List <string> flexure2 = dt.AsEnumerable().Select(r => r.Field <string>("flexure")).Except(flexure1).ToList();
foreach (var e in flexure2)
{
result += "Flexure '" + e + "'" + Environment.NewLine;
}
}
if (!cover_base1.Any(s => s == "XXX"))
{
List <string> cover_base2 = dt.AsEnumerable().Select(r => r.Field <string>("cover_base")).Except(cover_base1).ToList();
foreach (var e in cover_base2)
{
result += "Cover/base '" + e + "'" + Environment.NewLine;
}
}
if (!dframe1.Any(s => s == "XXX"))
{
List <string> dframe2 = dt.AsEnumerable().Select(r => r.Field <string>("dframe")).Except(dframe1).ToList();
foreach (var e in dframe2)
{
result += "Dframe '" + e + "'" + Environment.NewLine;
}
}
if (!fpc1.Any(s => s == "XXX"))
{
List <string> fpc2 = dt.AsEnumerable().Select(r => r.Field <string>("fpc")).Except(fpc1).ToList();
foreach (var e in fpc2)
{
result += "Fpc '" + e + "'" + Environment.NewLine;
}
}
if (!shift1.Any(s => s == "XXX"))
{
List <string> shift2 = dt.AsEnumerable().Select(r => r.Field <string>("shift")).Except(shift1).ToList();
foreach (var e in shift2)
{
result += "Shift '" + e + "'" + Environment.NewLine;
}
}
return(result);
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// if (!plant1.Any(s=> s=="XXX") && dt.Rows[i]["plant"].ToString() != plant) result += module + " Plant '" + dt.Rows[i]["plant"].ToString() + "'" + Environment.NewLine;
// else if (!year1.Any(s=> s=="XXX") && dt.Rows[i]["year"].ToString() != year) result += module + " Year '" + dt.Rows[i]["year"].ToString() + "'" + Environment.NewLine;
// else if (!week1.Any(s=> s=="XXX") && dt.Rows[i]["week"].ToString() != week) result += module + " Week '" + dt.Rows[i]["week"].ToString() + "'" + Environment.NewLine;
// else if (!day1.Any(s=> s=="XXX") && dt.Rows[i]["day"].ToString() != day) result += module + " Day '" + dt.Rows[i]["day"].ToString() + "'" + Environment.NewLine;
// else if (!line1.Any(s=> s=="XXX") && dt.Rows[i]["line"].ToString() != line) result += module + " Line '" + dt.Rows[i]["line"].ToString() + "'" + Environment.NewLine;
// else if (!eeee1.Any(s=> s=="XXX") && dt.Rows[i]["eeee"].ToString() != eeee) result += module + " 4E '" + dt.Rows[i]["eeee"].ToString() + "'" + Environment.NewLine;
// else if (!revision1.Any(s=> s=="XXX") && dt.Rows[i]["revision"].ToString() != revision) result += module + " Revision '" + dt.Rows[i]["revision"].ToString() + "'" + Environment.NewLine;
// else if (!mass1.Any(s=> s=="XXX") && dt.Rows[i]["mass"].ToString() != mass) result += module + " Mass '" + dt.Rows[i]["mass"].ToString() + "'" + Environment.NewLine;
// else if (!flexure1.Any(s=> s=="XXX") && dt.Rows[i]["flexure"].ToString() != flexure) result += module + " Flexure '" + dt.Rows[i]["flexure"].ToString() + "'" + Environment.NewLine;
// else if (!cover_base1.Any(s=> s=="XXX") && dt.Rows[i]["cover_base"].ToString() != cover_base) result += module + " Cover/base '" + dt.Rows[i]["cover_base"].ToString() + "'" + Environment.NewLine;
// else if (!dframe1.Any(s=> s=="XXX") && dt.Rows[i]["dframe"].ToString() != dframe) result += module + " D-Frame '" + dt.Rows[i]["dframe"].ToString() + "'" + Environment.NewLine;
// else if (!fpc1.Any(s=> s=="XXX") && dt.Rows[i]["fpc"].ToString() != fpc) result += module + " FPC '" + dt.Rows[i]["fpc"].ToString() + "'" + Environment.NewLine;
// else if (!shift1.Any(s=> s=="XXX") && dt.Rows[i]["shift"].ToString() != shift) result += module + " Shift '" + dt.Rows[i]["shift"].ToString() + "'" + Environment.NewLine;
//}
}
// シリアルがスキャンされた時の処理
private void txtModuleId_KeyDown(object sender, KeyEventArgs e)
{
// エンターキーの場合、テキストボックスの桁数が１５桁の場合のみ、処理を行う
if (e.KeyCode != Keys.Enter || txtTrayId.Text.Length != 21)
{
return;
}
string tray = txtTrayId.Text;
string sql = "select tray_id, lot, qty, register_date, rg_dept, multi_lot, " +
"case when cancel_date is not null then to_char(cancel_date,'YYYY/MM/DD') " +
"when pack_id is not null then pack_id else 'OK' end as check " +
"from t_tray where tray_id='" + tray + "'";
System.Diagnostics.Debug.Print(sql);
DataTable dt = new DataTable();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
if (dt.Rows.Count != 0)
{
bool warnedAlready = false;
string sql2 = " select sum(case when tm1.test_result in ('n/a', 'PASS') then 1 else 0 end) as okcount" +
" , count(tm1.test_result) as totalcount" +
" from t_tray " +
" left join t_module tm1 on tm1.tray_id = t_tray.tray_id " +
" where t_tray.tray_id = '" + tray + "'";
DataTable dt2 = new DataTable();
TfSQL tf2 = new TfSQL();
tf2.sqlDataAdapterFillDatatableFromTrayGuardDb(sql2, ref dt2);
if ((long)dt2.Rows[0]["okcount"] != 24)
{
MessageBox.Show("OK module is not 24", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Stop);
warnedAlready = true;
//if (txtLoginDept.Text != "PC") return;
}
if ((long)dt2.Rows[0]["totalcount"] != 24 && !warnedAlready)
{
MessageBox.Show("Module data is not 24", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Stop);
warnedAlready = true;
//if (txtLoginDept.Text != "PC") return;
}
if ((int)dt.Rows[0]["qty"] != 24 && !warnedAlready)
{
MessageBox.Show("Module in tray is not 24.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Stop);
warnedAlready = true;
//if (txtLoginDept.Text != "PC") return;
}
}
// 先ずは、シリアルの構成要素のパターンが適正か、ユーザーデスクトップの設定ファイルを使用して確認する
// 2017/03/07 Fujii 構成要素チェックのオフ
//string matchResult = matchSerialNumberingPattern(tray);
string matchResult = string.Empty;
if (matchResult != string.Empty)
{
MessageBox.Show(matchResult + " does not match with desktop file's setting.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
// テスターデータに該当がない場合でも、ユーザー認識用に表示するための処理
DataRow dr = dtTray.NewRow();
dr["tray_id"] = tray;
// テスターデータに該当がある場合の処理
if (dt.Rows.Count != 0)
{
dr["lot"] = (string)dt.Rows[0]["lot"];
dr["qty"] = (int)dt.Rows[0]["qty"];
dr["register_date"] = (DateTime)dt.Rows[0]["register_date"];
dr["rg_dept"] = (string)dt.Rows[0]["rg_dept"];
dr["multi_lot"] = (string)dt.Rows[0]["multi_lot"];
dr["check"] = (string)dt.Rows[0]["check"];
}
// メモリ上のテーブルにレコードを追加
dtTray.Rows.Add(dr);
// データグリットビューの更新
updateDataGridViews(dtTray, ref dgvTray, false);
}
// サブプロシージャ：データグリットビューの更新
public void updateDataGridViews(DataTable dt, ref DataGridView dgv, bool load)
{
DateTime registerDateFrom = dtpRegsterDateFrom.Value;
DateTime registerDateTo = dtpRegisterDateTo.Value.AddDays(1);
string invoiceNo = txtInvoiceNo.Text;
string palletId = txtPalletId.Text;
string lot = txtLot.Text;
string cartonId = txtCartonId.Text;
string batch = txtBatch.Text;
bool b_registerDateFrom = cbxRegisterDateFrom.Checked;
bool b_registerDateTo = cbxRegisterDateTo.Checked;
bool b_invoiceNo = cbxInvoiceNo.Checked;
bool b_palletId = cbxPalletId.Checked;
bool b_lot = cbxLot.Checked;
bool b_cartonId = cbxCartonId.Checked;
bool b_batch = cbxBatch.Checked;
bool b_multi_lot = cbxMultiLot.Checked;
bool b_hideCancel = cbxHideCancel.Checked;
// ユーザーがパックＩＤを検索条件として指定した場合は、個別のＳＱＬ文を使用する
string sqlX = "select pallet_id, lot, l_cnt, m_qty, batch, register_date, rg_user, cancel_date, cl_user, invoice_no from t_pallet " +
"where pallet_id in (select pallet_id from t_carton where carton_id like '" + cartonId + "%')";
// ユーザーが選択した検索条件を、ＳＱＬ文に反映する
string sql1 = "select pallet_id, lot, l_cnt, m_qty, batch, register_date, rg_user, cancel_date, cl_user, invoice_no from t_pallet where ";
bool[] cr = { true,
true,
invoiceNo == string.Empty ? false : true,
palletId == string.Empty ? false : true,
lot == string.Empty ? false : true,
cartonId == string.Empty ? false : true,
batch == string.Empty ? false : true,
true,
true };
bool[] ck = { b_registerDateFrom,
b_registerDateTo,
b_invoiceNo,
b_palletId,
b_lot,
b_cartonId,
b_batch,
b_multi_lot,
b_hideCancel };
string sql2 = (!(cr[0] && ck[0]) ? string.Empty : "register_date >= '" + registerDateFrom + "' AND ") +
(!(cr[1] && ck[1]) ? string.Empty : "register_date < '" + registerDateTo + "' AND ") +
(!(cr[2] && ck[2]) ? string.Empty : "invoice_no like '%" + invoiceNo + "%' AND ") +
(!(cr[3] && ck[3]) ? string.Empty : "pallet_id like '%" + palletId + "%' AND ") +
(!(cr[4] && ck[4]) ? string.Empty : "lot like '%" + lot + "%' AND ") +
(!(cr[5] && ck[5]) ? string.Empty : "carton_id like '%" + cartonId + "%' AND ") +
(!(cr[6] && ck[6]) ? string.Empty : "batch = '" + batch + "' AND ") +
(!(cr[7] && ck[7]) ? string.Empty : "l_cnt >= 2 AND ") +
(!(cr[8] && ck[8]) ? string.Empty : "cancel_date is null AND ");
bool b_all = (cr[0] && ck[0]) || (cr[1] && ck[1]) || (cr[2] && ck[2]) || (cr[3] && ck[3]) || (cr[4] && ck[4]) || (cr[5] && ck[5]) || (cr[6] && ck[6]);
if (!b_all)
{
MessageBox.Show("Please select at least one check box and fill the criteria.", "Notice",
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
return;
}
string sql3 = sql1 + VBS.Left(sql2, sql2.Length - 5) + " order by pallet_id";
string sql4 = string.Empty;
if (cartonId != string.Empty && b_cartonId)
{
sql4 = sqlX;
}
else
{
sql4 = sql3;
}
System.Diagnostics.Debug.Print(sql4);
// ＳＱＬ結果を、ＤＴＡＡＴＡＢＬＥへ格納
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql4, ref dt);
// データグリットビューへＤＴＡＡＴＡＢＬＥを格納
dgv.DataSource = dt;
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
// グリットビュー右端にボタンを追加（初回のみ）
if (load)
{
addButtonsToDataGridView(dgv);
}
//行ヘッダーに行番号を表示する
for (int i = 0; i < dgv.Rows.Count; i++)
{
dgv.Rows[i].HeaderCell.Value = (i + 1).ToString();
}
//行ヘッダーの幅を自動調節する
dgv.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders);
// 一番下の行を表示する
if (dgv.Rows.Count != 0)
{
dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
}
}
// サブプロシージャ：ロット集計グリッドビューを更新し、数量の最も多いロット番号を返す
public string updateLotSummary(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
dtLot = new DataTable();
dgvLotSummary.DataSource = dtLot;
return(string.Empty);
}
// 各カートンに含まれる、バックＩＤリストを取得する
TfSQL tf = new TfSQL();
string sql1 = "select pack_id from t_pack where carton_id in (";
string sql2 = string.Empty;
var query1 = dt.AsEnumerable().Select(row => new { carton_id = row.Field <string>("carton_id") });
foreach (var q in query1)
{
sql2 += "'" + q.carton_id + "', ";
}
string sql3 = sql1 + VBS.Left(sql2, sql2.Length - 2) + ")";
System.Diagnostics.Debug.Print(sql3);
DataTable dtTemp1 = new DataTable();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql3, ref dtTemp1);
// 各パックに含まれる、トレーＩＤリストを取得する
sql1 = "select tray_id from t_tray where pack_id in (";
sql2 = string.Empty;
var query2 = dtTemp1.AsEnumerable().Select(row => new { pack_id = row.Field <string>("pack_id") });
foreach (var q in query2)
{
sql2 += "'" + q.pack_id + "', ";
}
sql3 = sql1 + VBS.Left(sql2, sql2.Length - 2) + ")";
System.Diagnostics.Debug.Print(sql3);
DataTable dtTemp2 = new DataTable();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql3, ref dtTemp1);
// 上記で取得したトレーＩＤが持つモジュールから、ロット集計を作成する
sql1 = "select lot, count(lot) as qty from t_module where tray_id in (";
sql2 = string.Empty;
var query3 = dtTemp1.AsEnumerable().Select(row => new { tray_id = row.Field <string>("tray_id") });
foreach (var q in query3)
{
sql2 += "'" + q.tray_id + "', ";
}
sql3 = sql1 + VBS.Left(sql2, sql2.Length - 2) + ") group by lot order by qty desc, lot";
System.Diagnostics.Debug.Print(sql3);
DataTable dtTemp3 = new DataTable();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql3, ref dtTemp3);
// ロット集計表示テーブルに、一時テーブルの情報を移す
dgvLotSummary.DataSource = null;
dgvLotSummary.Refresh();
dtLot = new DataTable();
var query4 = dtTemp3.AsEnumerable().Select(r => new { lot = r.Field <string>("lot"), qty = r.Field <Int64>("qty") });
// 列の追加
foreach (var q in query4)
{
dtLot.Columns.Add(q.lot, typeof(int));
}
dtLot.Columns.Add("total", typeof(int));
// 行の追加
dtLot.Rows.Add();
foreach (var q in query4)
{
dtLot.Rows[0][q.lot] = q.qty;
}
dtLot.Rows[0]["total"] = query4.Sum(a => a.qty);
// dgvLotSummary_ControlAdded が発生する、６５５より多い列を取り扱うため、ＷＥＩＧＨＴ設定を１００から１へ変更
dgvLotSummary.DataSource = dtLot;
return(query4.First().lot);
}
// ログインボタン押下時の処理
private void btnLogIn_Click(object sender, EventArgs e)
{
string uid = cmbUserId.Text;
string menu = cmbMenu.Text;
string dept = cmbDept.Text;
if (uid == string.Empty || menu == string.Empty)
{
return;
}
TfSQL tf = new TfSQL();
DataTable dt = new DataTable();
string sql = "select pass, user_name, dept, u_role from t_user where user_id='" + uid + "'";
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql, ref dt);
string pass = dt.Rows[0]["pass"].ToString();
string uname = dt.Rows[0]["user_name"].ToString();
string udept = dt.Rows[0]["dept"].ToString();
string urole = dt.Rows[0]["u_role"].ToString();
if (pass != txtPassword.Text)
{
MessageBox.Show("Password does not match.", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (menu == "1. Tray")
{
// 子フォームfrmTrayを表示し、デレゲートイベントを追加：
frmTray fT = new frmTray();
fT.RefreshEvent += delegate(object sndr, EventArgs excp)
{
// 子フォームfrmTrayを閉じる際、当フォームを表示する
this.Visible = true;
};
fT.updateControls(uid, uname, udept, urole);
fT.Show();
this.txtPassword.Text = string.Empty;
if (dept != "PC")
{
this.Visible = false;
}
}
if (menu == "2. Pack" && (dept == "PC" || dept == "MFG"))
{
// 子フォームfrmTrayを表示し、デレゲートイベントを追加：
frmPack fP = new frmPack();
fP.RefreshEvent += delegate(object sndr, EventArgs excp)
{
// 子フォームfrmTrayを閉じる際、当フォームを表示する
this.Visible = true;
};
fP.updateControls(uid, uname, udept, urole);
fP.Show();
this.txtPassword.Text = string.Empty;
if (dept != "PC")
{
this.Visible = false;
}
}
if (menu == "3. Carton" && dept == "PC")
{
// 子フォームfrmTrayを表示し、デレゲートイベントを追加：
frmCarton fC = new frmCarton();
fC.RefreshEvent += delegate(object sndr, EventArgs excp)
{
// 子フォームfrmTrayを閉じる際、当フォームを表示する
this.Visible = true;
};
fC.updateControls(uid, uname, udept, urole);
fC.Show();
this.txtPassword.Text = string.Empty;
if (dept != "PC")
{
this.Visible = false;
}
}
if (menu == "4. Pallet" && dept == "PC")
{
// 子フォームfrmTrayを表示し、デレゲートイベントを追加：
frmPallet fC = new frmPallet();
fC.RefreshEvent += delegate(object sndr, EventArgs excp)
{
// 子フォームfrmTrayを閉じる際、当フォームを表示する
this.Visible = true;
};
fC.updateControls(uid, uname, udept, urole);
fC.Show();
this.txtPassword.Text = string.Empty;
if (dept != "PC")
{
this.Visible = false;
}
}
}
// サブプロシージャ：データグリットビューの更新
public void updateDataGridViews(DataTable dt, ref DataGridView dgv, bool load)
{
DateTime registerDateFrom = dtpRegsterDateFrom.Value;
DateTime registerDateTo = dtpRegisterDateTo.Value.AddDays(1);
string registerDept = cmbRegisterDept.Text;
DateTime updateDateFrom = dtpUpdateDateFrom.Value;
DateTime updateDateTo = dtpUpdateDateTo.Value.AddDays(1);
string updateDept = cmbUpdateDept.Text;
string trayId = txtTrayId.Text;
string line = cmbLine.Text;
string shift = txtShift.Text;
string moduleId = txtModuleId.Text;
string lot = txtLot.Text;
bool b_registerDateFrom = cbxRegisterDateFrom.Checked;
bool b_registerDateTo = cbxRegisterDateTo.Checked;
bool b_registerDept = cbxRegisterDept.Checked;
bool b_updateDateFrom = cbxUpdateDateFrom.Checked;
bool b_updateDateTo = cbxUpdateDateTo.Checked;
bool b_updateDept = cbxUpdateDept.Checked;
bool b_trayId = cbxTrayId.Checked;
bool b_line = cbxLine.Checked;
bool b_shift = cbxShift.Checked;
bool b_multiLot = cbxMultiLot.Checked;
bool b_hideCancel = cbxHideCancel.Checked;
bool b_moduleId = cbxModuleId.Checked;
bool b_lot = cbxLot.Checked;
// ユーザーがモジュールＩＤを検索条件として指定した場合は、個別のＳＱＬ文を使用する
string sqlX = "select tray_id, lot, qty, register_date, rg_dept, update_date, up_dept, cancel_date, cl_dept, multi_lot, pack_id from t_tray " +
"where tray_id in (select tray_id from t_module where module_id like '" + moduleId + "%')";
// ユーザーがロットＤを検索条件として指定した場合は、個別のＳＱＬ文を使用する
string sqlY = "select tray_id, lot, qty, register_date, rg_dept, update_date, up_dept, cancel_date, cl_dept, multi_lot, pack_id from t_tray " +
"where tray_id in (select tray_id from t_module where lot like '" + lot + "%')";
// モジュールＩＤ・ロットＩＤが選択されなかった場合は、その他全ての選択条件を、ＳＱＬ文に盛り込む
string sql1 = "select tray_id, lot, qty, register_date, rg_dept, update_date, up_dept, cancel_date, cl_dept, multi_lot, pack_id from t_tray where ";
bool[] cr = { true,
true,
registerDept == string.Empty ? false : true,
true,
true,
updateDept == string.Empty ? false : true,
trayId == string.Empty ? false : true,
line == string.Empty ? false : true,
shift == string.Empty ? false : true,
true,
true };
bool[] ck = { b_registerDateFrom,
b_registerDateTo,
b_registerDept,
b_updateDateFrom,
b_updateDateTo,
b_updateDept,
b_trayId,
b_line,
b_shift,
b_multiLot,
b_hideCancel };
string sql2 = (!(cr[0] && ck[0]) ? string.Empty : "register_date >= '" + registerDateFrom + "' AND ") +
(!(cr[1] && ck[1]) ? string.Empty : "register_date < '" + registerDateTo + "' AND ") +
(!(cr[2] && ck[2]) ? string.Empty : "rg_dept = '" + registerDept + "' AND ") +
(!(cr[3] && ck[3]) ? string.Empty : "update_date >= '" + updateDateFrom + "' AND ") +
(!(cr[4] && ck[4]) ? string.Empty : "update_date < '" + updateDateTo + "' AND ") +
(!(cr[5] && ck[5]) ? string.Empty : "up_dept = '" + updateDept + "' AND ") +
(!(cr[6] && ck[6]) ? string.Empty : "tray_id like '%" + trayId + "%' AND ") +
(!(cr[7] && ck[7]) ? string.Empty : "line = '" + line + "' AND ") +
(!(cr[8] && ck[8]) ? string.Empty : "shift like '%" + shift + "%' AND ") +
(!(cr[9] && ck[9]) ? string.Empty : "multi_lot = 'T' AND ") +
(!(cr[10] && ck[10]) ? string.Empty : "cancel_date is null AND ");
bool b_all = (cr[0] && ck[0]) || (cr[1] && ck[1]) || (cr[2] && ck[2]) || (cr[3] && ck[3]) || (cr[4] && ck[4]) || (cr[5] && ck[5]) ||
(cr[6] && ck[6]) || (cr[7] && ck[7]) || (cr[8] && ck[8]) ||
(moduleId != string.Empty && b_moduleId) || (lot != string.Empty && b_lot);
if (!b_all)
{
MessageBox.Show("Please select at least one check box and fill the criteria.", "Notice",
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
return;
}
string sql3 = sql1 + VBS.Left(sql2, sql2.Length - 5) + " order by tray_id";
string sql4 = string.Empty;
if (moduleId != string.Empty && b_moduleId)
{
sql4 = sqlX;
}
else if (lot != string.Empty && b_lot)
{
sql4 = sqlY;
}
else
{
sql4 = sql3;
}
System.Diagnostics.Debug.Print(sql4);
// ＳＱＬ結果を、ＤＴＡＡＴＡＢＬＥへ格納
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatableFromTrayGuardDb(sql4, ref dt);
// データグリットビューへＤＴＡＡＴＡＢＬＥを格納
dgv.DataSource = dt;
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
// グリットビュー右端にボタンを追加（初回のみ）
if (load)
{
addButtonsToDataGridView(dgv);
}
//行ヘッダーに行番号を表示する
for (int i = 0; i < dgv.Rows.Count; i++)
{
dgv.Rows[i].HeaderCell.Value = (i + 1).ToString();
}
//行ヘッダーの幅を自動調節する
dgv.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders);
// 一番下の行を表示する
if (dgv.Rows.Count != 0)
{
dgv.FirstDisplayedScrollingRowIndex = dgv.Rows.Count - 1;
}
}