// サブプロシージャ：新規バッチの発行
private string getNewBatch()
{
string subAssyNo = cmbSubAssyNo.Text;
string sql;
TfSQL tf = new TfSQL();
sql = "select batch_prefix from t_model_sub_assy where sub_assy_no = '" + subAssyNo + "'";
string batchPrefix = tf.sqlExecuteScalarString(sql);
sql = "select max(batch_no) from t_batch_no where batch_no like '" + batchPrefix + "%'";
string batchOld = tf.sqlExecuteScalarString(sql);
DateTime dateOld = new DateTime(0);
long numberOld = 0;
string batchNew;
if (batchOld != string.Empty)
{
dateOld = DateTime.ParseExact(VBStrings.Mid(batchOld, 10, 6), "yyMMdd", CultureInfo.InvariantCulture);
numberOld = long.Parse(VBStrings.Right(batchOld, 4));
}
if (dateOld != DateTime.Today)
{
batchNew = batchPrefix + "#" + DateTime.Today.ToString("yyMMdd") + "#" + "0001";
}
else
{
batchNew = batchPrefix + "#" + DateTime.Today.ToString("yyMMdd") + "#" + (numberOld + 1).ToString("0000");
}
return(batchNew);
}
// コンボボックス項目選択時の処理（サブ組ＮＯ）
private void cmbSubAssyNo_SelectedIndexChanged(object sender, EventArgs e)
{
string subAssy = cmbSubAssyNo.Text;
string sql = "select sub_assy_name FROM t_model_sub_assy where sub_assy_no ='" + subAssy + "'";
TfSQL tf = new TfSQL();
txtSubAssyName.Text = tf.sqlExecuteScalarString(sql);
}
// バッチ削除ボタン押下時の処理（ヘッダーと明細全ての削除）
private void btnDeleteBatch_Click(object sender, EventArgs e)
{
// 念のため、本当に削除するのか、２度ユーザーに問う
DialogResult result1 = MessageBox.Show("Do you really want to delete the batch data?",
"Notice", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (result1 == DialogResult.No)
{
return;
}
DialogResult result2 = MessageBox.Show("Again, is it OK to delete the batch?",
"Notice", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (result2 == DialogResult.No)
{
return;
}
string batchNo = txtBatchNo.Text;
string[] sql = { "delete from t_batch_no where batch_no='" + batchNo + "'",
"delete from t_operator where batch_no='" + batchNo + "'",
"delete from t_parts_invoice where batch_no='" + batchNo + "'",
"delete from t_sub_mat_invoice where batch_no='" + batchNo + "'" };
TfSQL tf = new TfSQL();
for (int i = 0; i < sql.Length; i++)
{
System.Diagnostics.Debug.Print(sql[i]);
tf.sqlExecuteNonQuery(sql[0], false);
}
//親フォームForm1のデータグリットビューを更新するため、デレゲートイベントを発生させる
this.RefreshEvent(this, new EventArgs());
// コントロールの無効化
disableControlsExceptCloseButton(this);
// 登録完了フラグオン（誤って閉じることを防止する機能、オフ）
b_headerComplete = true;
b_operatorComplete = true;
b_partsComplete = true;
b_subMatComplete = true;
// ＴＢＩテーブルのパーツ情報を削除するため、参照すべきＴＢＩテーブル名を特定する
string model = cmbModelNo.Text;
DateTime month = dtpBatchDate.Value;
string tbiTable = decideReferenceTable(model, month);
// ＴＢＩテーブルの同バッチ既存レコード削除
string sql2 = "delete from " + tbiTable + " where lot = '" + batchNo + "'";
System.Diagnostics.Debug.Print(sql2);
tf.sqlExecuteNonQueryToPqmDb(sql2, false);
}
// ロード時の処理（コンボボックスに、オートコンプリート機能の追加）
private void Form5_Load(object sender, EventArgs e)
{
string sql = "select leader_id FROM t_leader_id ORDER BY leader_id";
System.Diagnostics.Debug.Print(sql);
TfSQL tf = new TfSQL();
tf.getComboBoxData(sql, ref cmbLeaderId);
}
// コンボボックス選択時、リーダー名を表示する
private void cmbLeaderId_SelectedIndexChanged(object sender, EventArgs e)
{
string sql = "select leader_name FROM t_leader_id where leader_id ='" + cmbLeaderId.Text + "'";
System.Diagnostics.Debug.Print(sql);
TfSQL tf = new TfSQL();
string name = tf.sqlExecuteScalarString(sql);
txtLeaderName.Text = name;
}
// サブプロシージャ：部品マスタ情報を取得する
private void readPartsMasterTable(ref DataTable dt)
{
string subAssyNo = cmbSubAssyNo.Text;
string sql = "select parts_no, parts_name, ratio from t_parts where sub_assy_no ='" + subAssyNo + "'";
System.Diagnostics.Debug.Print(sql);
dt.Rows.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql, ref dt);
}
// ロード時の処理
private void Form1_Load(object sender, EventArgs e)
{
//フォームの場所を指定
this.Left = 10;
this.Top = 10;
dtBatchNo = new DataTable();
defineAndReadDatatable(ref dtBatchNo);
updateDataGridViews(ref dtBatchNo, ref dgvBatchNo);
// コンボボックスへ候補をセットする（モデルＮＯ）
string sql = "select model_no FROM t_model_sub_assy group by model_no order by model_no";
TfSQL tf = new TfSQL();
tf.getComboBoxData(sql, ref cmbModelNo);
}
// �@オペレーター
// サブプロシージャ：オペレーター情報をデータグリッドビューに反映する
private void readOperatorTable(ref DataTable dt, ref DataGridView dgv, bool load)
{
string batchNo = txtBatchNo.Text;
string sql = "select process, operator, machine from t_operator " + "where batch_no='" + batchNo + "'";
System.Diagnostics.Debug.Print(sql);
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql, ref dt);
updateDataGridViewsub(dt, ref dgv);
//// グリッドビューにコンボボックスを追加する
//if(load) insertComboBoxToGridView();
}
// サブプロシージャ：新規登録モード時、工程リストを表示する
private void showDefaultProcess(ref DataTable dt, ref DataGridView dgv, bool load)
{
string subAssyNo = cmbSubAssyNo.Text;
// string line = "mc_line_" + cmbLine.Text; マシン番号取得機能は、使用停止。将来的に再度使用する可能性はあり。
// string sql = "select process, " + line + " as machine from t_process " + "where sub_assy_no='" + subAssyNo + "'";
string sql = "select process from t_process where sub_assy_no='" + subAssyNo + "' order by process";
System.Diagnostics.Debug.Print(sql);
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql, ref dt);
updateDataGridViewsub(dt, ref dgv);
//// グリッドビューにコンボボックスを追加する
//if (load) insertComboBoxToGridView();
}
// バッチヘッダー情報更新ボタン押下時の処理
private void btnUpdateBatch_Click(object sender, EventArgs e)
{
string batchNo = txtBatchNo.Text;
double inQty;
double.TryParse(txtInputQty.Text, out inQty);
double outQty;
double.TryParse(txtOutputQty.Text, out outQty);
DateTime inTime = dtpInputTime.Value.Date;
DateTime outTime = dtpOutputTime.Value.Date;
string remark = txtRemark.Text;
bool[] cr = { txtInputQty.Text == string.Empty ? false : true,
txtOutputQty.Text == string.Empty ? false : true,
true,
true,
remark == string.Empty ? false : true };
string sql1 = "update t_batch_no set " +
(cr[0] ? "in_qty=" + inQty + "," : "in_qty = null,") +
(cr[1] ? "out_qty=" + outQty + "," : "out_qty = null,") +
(cr[2] ? "in_time='" + inTime + "'," : string.Empty) +
(cr[3] ? "out_time='" + outTime + "'," : string.Empty) +
(cr[4] ? "remark='" + remark + "'," : "remark = null,");
string sql2 = " where batch_no='" + batchNo + "'";
string sql3 = VBStrings.Left(sql1, sql1.Length - 1) + sql2;
System.Diagnostics.Debug.Print(sql3);
TfSQL tf = new TfSQL();
b_headerComplete = tf.sqlExecuteNonQuery(sql3, false);
if (b_headerComplete)
{
MessageBox.Show("Step 1: Batch general info register completed", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//親フォームForm1のデータグリットビューを更新するため、デレゲートイベントを発生させる
this.RefreshEvent(this, new EventArgs());
}
// �B副資材
// サブプロシージャ：副資材情報をデータグリッドビューに反映する
private void readSubMaterialTable(ref DataTable dt, ref DataGridView dgv)
{
string batchNo = txtBatchNo.Text;
string sql = "select sub_mat_no, sub_mat_name, sub_mat_supplier, sub_mat_invoice, validity from t_sub_mat_invoice " + "where batch_no='" + batchNo + "'";
System.Diagnostics.Debug.Print(sql);
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql, ref dt);
updateDataGridViewsub(dt, ref dgv);
// スキャン登録ができるよう、カレントセルを設定し、ＤＩＲＴＹにする
int r = dt.Rows.Count;
dt.Rows.Add(dt.NewRow());
dgv.CurrentCell = dgv[0, r];
dgv.NotifyCurrentCellDirty(true);
dgv.NotifyCurrentCellDirty(false);
}
// ユーザーログイン
private void btnLogIn_Click(object sender, EventArgs e)
{
string leaderId = cmbLeaderId.Text;
string leaderName = txtLeaderName.Text;
leaderId = cmbLeaderId.Text;
if (leaderId != null)
{
TfSQL tf = new TfSQL();
string sql1 = "select pass FROM t_leader_id WHERE leader_id='" + leaderId + "'";
string pass = tf.sqlExecuteScalarString(sql1);
if (pass == txtPassword.Text)
{
// 子フォームForm1を表示し、デレゲートイベントを追加：
Form1 f1 = new Form1();
f1.RefreshEvent += delegate(object sndr, EventArgs excp)
{
// 子フォームForm1を閉じる際、当フォームを表示する
txtPassword.Text = string.Empty;
this.Visible = true;
};
string sql2 = "select adminflag FROM t_leader_id WHERE leader_id='" + leaderId + "'";
bool adminUser = tf.sqlExecuteScalarBool(sql2);
f1.updateControls(leaderId, leaderName, adminUser);
f1.Show();
this.Visible = false;
}
else if (pass != txtPassword.Text)
{
MessageBox.Show("Password does not match", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
// サブプロシージャ：グリッドビューにコンボボックスを追加する
private void insertComboBoxToGridView()
{
//dgvOperator.Columns["process"].Visible = false;
DataGridViewComboBoxColumn cmbCol = new DataGridViewComboBoxColumn();
cmbCol.HeaderText = "select_process";
cmbCol.Name = "cmbProcess";
string sql = "select process from t_process where " + "sub_assy_no = '" + cmbSubAssyNo.Text + "'";
System.Diagnostics.Debug.Print(sql);
DataTable dtProcessList = new DataTable();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql, ref dtProcessList);
foreach (DataRow row in dtProcessList.Rows)
{
cmbCol.Items.Add(row[0].ToString());
}
dgvOperator.Columns.Add(cmbCol);
dgvOperator.Columns["cmbProcess"].DisplayIndex = 0;
}
// コンボボックス項目選択時の処理（モデルＮＯ）
private void cmbModelNo_SelectedIndexChanged(object sender, EventArgs e)
{
string sql;
TfSQL tf = new TfSQL();
string model = cmbModelNo.Text;
sql = "select model_name FROM t_model_sub_assy where model_no ='" + model + "'";
System.Diagnostics.Debug.Print(sql);
txtModelName.Text = tf.sqlExecuteScalarString(sql);
// コンボボックスへ候補をセットする（サブ組ＮＯ）
sql = "select sub_assy_no FROM t_model_sub_assy where model_no ='" + model + "'";
System.Diagnostics.Debug.Print(sql);
tf.getComboBoxData(sql, ref cmbSubAssyNo);
cmbSubAssyNo.Enabled = true;
// コンボボックスへ候補をセットする（ライン）
sql = "select line FROM t_model_line where model_no ='" + model + "'";
System.Diagnostics.Debug.Print(sql);
tf.getComboBoxData(sql, ref cmbLine);
cmbLine.Enabled = true;
}
// �A部品
// サブプロシージャ：部品情報をデータグリッドビューに反映する
private void readPartsTable(ref DataTable dt, ref DataGridView dgv)
{
string batchNo = txtBatchNo.Text;
string sql = "select parts_no, parts_name, parts_supplier, parts_invoice, qty, note from t_parts_invoice " + "where batch_no='" + batchNo + "'";
System.Diagnostics.Debug.Print(sql);
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql, ref dt);
updateDataGridViewsub(dt, ref dgv);
// スキャン登録ができるよう、カレントセルを設定し、ＤＩＲＴＹにする
int r = dt.Rows.Count;
dt.Rows.Add(dt.NewRow());
dgv.CurrentCell = dgv[0, r];
dgv.NotifyCurrentCellDirty(true);
dgv.NotifyCurrentCellDirty(false);
// 部品マスタ情報を取得する
readPartsMasterTable(ref dtPartsMaster);
}
// サブプロシージャ：データグリットビューの更新
public void updateDataGridViews(ref DataTable dt, ref DataGridView dgv)
{
string batchNo = txtBatchNo.Text;
string modelNo = cmbModelNo.Text;
string modelName = txtModelName.Text;
string subAssyNo = cmbSubAssyNo.Text;
string subAssyName = txtSubAssyName.Text;
DateTime batchDate = dtpBatchDate.Value.Date;
DateTime batchNextDate = dtpBatchDate.Value.Date.AddDays(1);
string shift = cmbShift.Text;
string line = cmbLine.Text;
string leader = txtLeaderId.Text;
string leaderName = txtLeaderName.Text;
bool b_batch = chkBatch.Checked;
bool b_model = chkModel.Checked;
bool b_subAssy = chkSubAssy.Checked;
bool b_batchDate = chkBatchDate.Checked;
bool b_shift = chkShift.Checked;
bool b_line = chkLine.Checked;
bool b_leader = chkLeader.Checked;
string sql1 = "select batch_no, model_no, model_name, sub_assy_no, sub_assy_name, batch_date, " +
"shift, line, leader_id, leader_name, in_qty, out_qty, in_time, out_time, remark from t_batch_no where ";
bool[] cr = { batchNo == String.Empty ? false : true,
modelNo == String.Empty ? false : true,
subAssyNo == String.Empty ? false : true,
true,
shift == String.Empty ? false : true,
line == String.Empty ? false : true,
leader == String.Empty ? false : true };
bool[] ck = { b_batch,
b_model,
b_subAssy,
b_batchDate,
b_shift,
b_line,
b_leader };
string sql2 = (!(cr[0] && ck[0]) ? String.Empty : "batch_no like '" + batchNo + "%' AND ") +
(!(cr[1] && ck[1]) ? String.Empty : "model_no = '" + modelNo + "' AND ") +
(!(cr[2] && ck[2]) ? String.Empty : "sub_assy_no = '" + subAssyNo + "' AND ") +
(!(cr[3] && ck[3]) ? String.Empty : "batch_date >= '" + batchDate + "' AND batch_date < '" + batchNextDate + "' AND ") +
(!(cr[4] && ck[4]) ? String.Empty : "shift = '" + shift + "' AND ") +
(!(cr[5] && ck[5]) ? String.Empty : "line = '" + line + "' AND ") +
(!(cr[6] && ck[6]) ? String.Empty : "leader_id = '" + leader + "' 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]);
System.Diagnostics.Debug.Print(b_all.ToString());
System.Diagnostics.Debug.Print(cr[0].ToString() + " " + ck[0].ToString() + " " + cr[1].ToString() + " " + ck[1].ToString() + " " +
cr[2].ToString() + " " + ck[2].ToString() + " " + cr[3].ToString() + " " + ck[3].ToString() + " " +
cr[4].ToString() + " " + ck[4].ToString() + cr[5].ToString() + " " + ck[5].ToString() + " " + cr[6].ToString() + " " + ck[6].ToString());
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 + VBStrings.Left(sql2, sql2.Length - 5);
System.Diagnostics.Debug.Print(sql3);
dt.Clear();
TfSQL tf = new TfSQL();
tf.sqlDataAdapterFillDatatable(sql3, ref dt);
// データグリットビューへＤＴＡＡＴＡＢＬＥを格納
dgv.DataSource = dt;
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
//行ヘッダーに行番号を表示する
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;
}
}
// サブプロシージャ：バッチ番号テーブル用、インサート文実行
private void insertBatchNo()
{
string batchNo = txtBatchNo.Text;
string modelNo = cmbModelNo.Text;
string modelName = txtModelName.Text;
string subAssyNo = cmbSubAssyNo.Text;
string subAssyName = txtSubAssyName.Text;
DateTime batchDate = dtpBatchDate.Value;
string shift = cmbShift.Text;
string line = cmbLine.Text;
string leader = txtLeaderId.Text;
string leaderName = txtLeaderName.Text;
double inQty;
double.TryParse(txtInputQty.Text, out inQty);
double outQty;
double.TryParse(txtOutputQty.Text, out outQty);
DateTime inTime = dtpInputTime.Value;
DateTime outTime = dtpOutputTime.Value;
string remark = txtRemark.Text;
bool[] cr = { batchNo == string.Empty ? false : true,
modelNo == string.Empty ? false : true,
modelName == string.Empty ? false : true,
subAssyNo == string.Empty ? false : true,
subAssyName == string.Empty ? false : true,
true,
shift == string.Empty ? false : true,
line == string.Empty ? false : true,
leader == string.Empty ? false : true,
leaderName == string.Empty ? false : true,
txtInputQty.Text == string.Empty ? false : true,
txtOutputQty.Text == string.Empty ? false : true,
true,
true,
remark == string.Empty ? false : true, };
string sql1 = "insert into t_batch_no(" +
(cr[0] ? "batch_no," : string.Empty) +
(cr[1] ? "model_no," : string.Empty) +
(cr[2] ? "model_name," : string.Empty) +
(cr[3] ? "sub_assy_no," : string.Empty) +
(cr[4] ? "sub_assy_name," : string.Empty) +
(cr[5] ? "batch_date," : string.Empty) +
(cr[6] ? "shift," : string.Empty) +
(cr[7] ? "line," : string.Empty) +
(cr[8] ? "leader_id," : string.Empty) +
(cr[9] ? "leader_name," : string.Empty) +
(cr[10] ? "in_qty," : string.Empty) +
(cr[11] ? "out_qty," : string.Empty) +
(cr[12] ? "in_time," : string.Empty) +
(cr[13] ? "out_time," : string.Empty) +
(cr[14] ? "remark," : string.Empty);
string sql2 = ") VALUES(" +
(cr[0] ? "'" + batchNo + "'," : string.Empty) +
(cr[1] ? "'" + modelNo + "'," : string.Empty) +
(cr[2] ? "'" + modelName + "'," : string.Empty) +
(cr[3] ? "'" + subAssyNo + "'," : string.Empty) +
(cr[4] ? "'" + subAssyName + "'," : string.Empty) +
(cr[5] ? "'" + batchDate + "'," : string.Empty) +
(cr[6] ? "'" + shift + "'," : string.Empty) +
(cr[7] ? "'" + line + "'," : string.Empty) +
(cr[8] ? "'" + leader + "'," : string.Empty) +
(cr[9] ? "'" + leaderName + "'," : string.Empty) +
(cr[10] ? " " + inQty + " ," : string.Empty) +
(cr[11] ? " " + outQty + " ," : string.Empty) +
(cr[12] ? "'" + inTime + "'," : string.Empty) +
(cr[13] ? "'" + outTime + "'," : string.Empty) +
(cr[14] ? "'" + remark + "'," : string.Empty);
string sql3 = VBStrings.Left(sql1, sql1.Length - 1) + VBStrings.Left(sql2, sql2.Length - 1) + ")";
System.Diagnostics.Debug.Print(sql3);
TfSQL tf = new TfSQL();
tf.sqlExecuteNonQuery(sql3, false);
//親フォームForm1のデータグリットビューを更新するため、デレゲートイベントを発生させる
this.RefreshEvent(this, new EventArgs());
}