using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace 積算_原価管理
{
public partial class Form1 : Form
{
Boolean isLoad = false;
private List GetControls(string pattern)
{
List l = new List();
foreach (Control c in this.Controls)
{
if (pattern == "DecimalText")
{
switch (c.Name)
{
case "textBox14":
case "textBox15":
case "textBox6":
case "textBox8":
case "textBox9":
case "textBox10":
case "textBox11":
case "textBox12":
case "textBox13":
l.Add(c);
break;
}
}
else if(pattern == "ProductInputs")
{
switch (c.Name)
{
case "textBox1":
case "comboBox1":
case "comboBox2":
case "comboBox3":
case "textBox14":
case "textBox15":
case "textBox4":
case "textBox5":
case "textBox6":
case "textBox7":
case "textBox8":
case "textBox9":
case "textBox10":
case "textBox11":
case "textBox12":
case "textBox13":
case "textBox16":
l.Add(c);
break;
}
}
else if (pattern == "PartsInput")
{
switch (c.Name)
{
case "textBox2":
case "textBox18":
case "textBox19":
case "textBox20":
case "textBox21":
case "textBox3":
case "textBox17":
l.Add(c);
break;
}
}
}
return l;
}
private string BuildQuery(string pattern)
{
if (pattern == "新規")
{
return "INSERT INTO 製品 (向先,型式,製作先,販売価格,営業手配分,外注費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考) VALUES (" +
"'" + comboBox1.Text + "'," +
"'" + comboBox2.Text + "'," +
"'" + comboBox3.Text + "'," +
"'" + textBox14.Text + "'," +
"'" + textBox15.Text + "'," +
"'" + textBox6.Text + "'," +
"'" + textBox8.Text + "'," +
"'" + textBox9.Text + "'," +
"'" + textBox10.Text + "'," +
"'" + textBox11.Text + "'," +
"'" + textBox12.Text + "'," +
"'" + textBox13.Text + "'," +
"'" + textBox16.Text + "')";
}
else if (pattern == "更新")
{
return "UPDATE 製品 SET " +
"向先 = '" + comboBox1.Text + "'," +
"型式 = '" + comboBox2.Text + "'," +
"製作先 = '" + comboBox3.Text + "'," +
"販売価格 = '" + textBox14.Text + "'," +
"営業手配分 = '" + textBox15.Text + "'," +
"外注費 = '" + textBox6.Text + "'," +
"輸送費荷受 = '" + textBox8.Text + "'," +
"輸送費納品 = '" + textBox9.Text + "'," +
"試験費 = '" + textBox10.Text + "'," +
"品証費用 = '" + textBox11.Text + "'," +
"品証経費 = '" + textBox12.Text + "'," +
"社内製作分 = '" + textBox13.Text + "'," +
"備考 = '" + textBox16.Text + "' " +
"WHERE ID = " + textBox1.Text + "";
}
else if (pattern =="削除")
{
return "DELETE FROM 製品 WHERE ID = " + textBox1.Text + "";
}
return "";
}
private void InitGridView()
{
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
dataGridView1.AllowUserToAddRows = false;
//dataGridView1.ReadOnly = true;
dataGridView1.Font = new Font("MS UI Gothic", 12);
dataGridView1.Columns.Add("部品表ID", "部品表ID");
dataGridView1.Columns.Add("集計ID", "集計ID");
dataGridView1.Columns.Add("部品ID", "部品ID");
dataGridView1.Columns.Add("名称", "名称");
dataGridView1.Columns.Add("材質", "材質");
dataGridView1.Columns.Add("寸法", "寸法");
dataGridView1.Columns.Add("単価", "単価");
dataGridView1.Columns.Add("数量", "数量");
DataGridViewButtonColumn col = new DataGridViewButtonColumn();
col.Name = "更新";
col.UseColumnTextForButtonValue = true;
col.Text = "更新";
dataGridView1.Columns.Add(col);
col = new DataGridViewButtonColumn();
col.Name = "削除";
col.UseColumnTextForButtonValue = true;
col.Text = "削除";
dataGridView1.Columns.Add(col);
dataGridView1.Columns["部品表ID"].Visible = false;
}
public Form1()
{
InitializeComponent();
LoadMaster();
InitGridView();
//読込
button1.Click += new EventHandler((object sender ,EventArgs e) => { LoadTable("読込"); });
//検索
button2.Click += new EventHandler((object sender, EventArgs e) => { LoadTable("検索"); });
//一覧
button8.Click += new EventHandler((object sender, EventArgs e) => {
Form f = new Form3(this);
f.StartPosition = FormStartPosition.Manual;
f.Location = new Point(Cursor.Position.X + 50, Cursor.Position.Y);
f.ShowDialog();
});
//新規
button7.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("新規"); });
//更新
button4.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("更新"); });
//削除
button6.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("削除"); });
//部品ID検索
button5.Click += new EventHandler((object sender, EventArgs e) => {
Form f = new Form2(this);
f.StartPosition = FormStartPosition.Manual;
f.Location = new Point(Cursor.Position.X + 50,Cursor.Position.Y - 550);
f.ShowDialog();
});
//部品追加
button3.Click += new EventHandler(AddRow);
dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);
textBox1.ForeColor = Color.Red;
textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {
isLoad = false;
textBox1.ForeColor = Color.Red;
});
}
private Boolean TryDecimal()
{
try
{
foreach (Control c in GetControls("DecimalText"))
{
if (c.Text == "") c.Text = "0";
Decimal.Parse(c.Text);
}
return true;
}
catch
{
return false;
}
}
private Boolean ProductsInputCheck(string sender)
{
if (sender == "新規")
{
if (textBox1.Text != "")
{
MessageBox.Show("製品IDが入力されています。");
return false;
}
if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")
{
MessageBox.Show("向先・型式・製作先が空白です");
return false;
}
if (!TryDecimal())
{
MessageBox.Show("金額が正しくありません。");
return false;
}
}
else if (sender == "更新")
{
if (textBox1.Text == "")
{
MessageBox.Show("製品IDが入力されていません。");
return false;
}
if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")
{
MessageBox.Show("向先・型式・製作先が空白です");
return false;
}
if (!isLoad)
{
MessageBox.Show("製品IDが正しくない可能性があります。");
return false;
}
if (!TryDecimal())
{
MessageBox.Show("金額が正しくありません。");
return false;
}
}
else if (sender == "削除")
{
if (textBox1.Text == "")
{
MessageBox.Show("製品IDが入力されていません。");
return false;
}
if (!isLoad)
{
MessageBox.Show("製品IDが正しくない可能性があります。");
return false;
}
}
return true;
}
private void DataChanges(string sender)
{
if (!ProductsInputCheck(sender)) return;
using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand(BuildQuery(sender), con);
cmd.ExecuteNonQuery();
if (sender == "新規")
{
cmd = new OleDbCommand("SELECT @@IDENTITY", con);
using (OleDbDataReader dr = cmd.ExecuteReader())
{
if (dr.Read()) textBox1.Text = dr[0].ToString();
}
}
}
catch
{
MessageBox.Show("データベースに接続できませんでした。");
return;
}
finally
{
con.Close();
}
}
LoadMaster();
if (sender == "削除")
{
MessageBox.Show("削除しました。");
InputClear();
}
else
{
LoadTable(sender);
}
}
private void dgvClick(object sender, DataGridViewCellEventArgs e)
{
int col = e.ColumnIndex;
int row = e.RowIndex;
//削除
if (col == 9 && row >= 0)
{
if (!isLoad)
{
MessageBox.Show("製品IDが正しくない可能性があります。");
return;
}
string id = dataGridView1.Rows[row].Cells[0].Value.ToString();
using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品表 WHERE 部品表ID = " + id + "", con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("データベースに接続できませんでした。");
return;
}
finally
{
con.Close();
}
}
LoadTable("削除");
}
//更新
if (col == 8 && row >= 0)
{
if (!isLoad)
{
MessageBox.Show("製品IDが正しくない可能性があります。");
return;
}
string id = dataGridView1.Rows[row].Cells[0].Value.ToString();
string quantity = dataGridView1.Rows[row].Cells[7].Value.ToString();
string calculateId = dataGridView1.Rows[row].Cells[1].Value.ToString();
try
{
decimal.Parse(quantity);
int i = int.Parse(calculateId);
if (i > 5 || i < 1) throw new Exception(); } catch { MessageBox.Show("集計ID・数量が正しくありません。"); return; } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand( "UPDATE 部品表 SET 集計ID = " + calculateId + ", 数量 = " + quantity + " WHERE 部品表ID = " + id + "", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); InputClear(); return; } finally { con.Close(); } } LoadTable("更新"); } } private void InputClear() { dataGridView1.Rows.Clear(); foreach (Control c in GetControls("ProductInputs")) { c.Text = ""; } } private void LoadMaster() { comboBox1.Items.Clear(); comboBox2.Items.Clear(); comboBox3.Items.Clear(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd; OleDbDataReader dr; foreach (string str in new string[] {"向先","型式","製作先" }) { cmd = new OleDbCommand("SELECT DISTINCT " + str +" FROM 製品", con); dr = cmd.ExecuteReader(); while(dr.Read()) { if(str == "向先") this.comboBox1.Items.Add(dr["向先"].ToString()); if(str == "型式") this.comboBox2.Items.Add(dr["型式"].ToString()); if(str == "製作先") this.comboBox3.Items.Add(dr["製作先"].ToString()); } } } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } } private Boolean TryInt() { try { decimal.Parse(textBox17.Text); int i = int.Parse(textBox3.Text); if (i > 5 || i < 1) return false; } catch { return false; } return true; } private Boolean PartsInputCheck() { if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox17.Text == "") { MessageBox.Show("製品ID・部品ID・集計ID・数量を入力してください。"); return false; } if (!isLoad) { MessageBox.Show("製品IDが正しくない可能性があります。"); return false; } if (!TryInt()) { MessageBox.Show("集計ID・数量が正しくありません。"); return false; } return true; } private void AddRow(object sender, EventArgs e) { if (!PartsInputCheck()) return; using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品表 (製品ID,集計ID,部品ID_,数量) VALUES (" + textBox1.Text + "," + textBox3.Text + "," + textBox2.Text + "," + textBox17.Text + ")", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } foreach (Control c in GetControls("PartsInput")) { c.Text = ""; } LoadTable("追加"); } private void LoadTable(string sender) { dataGridView1.Rows.Clear(); if(sender == "読込") { if (this.textBox1.Text == "") { MessageBox.Show("IDを入力してください。"); InputClear(); return; } } else if (sender == "検索") { if (this.comboBox1.Text == "" || this.comboBox2.Text == "" || this.comboBox3.Text == "") { MessageBox.Show("向先・型式・製作先を入力してください。"); InputClear(); return; } } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); string q = ""; switch (sender) { case "検索": case "新規": q = "SELECT * FROM 製品 WHERE 向先 = '" + comboBox1.Text + "' AND 型式 = '" + comboBox2.Text + "' AND 製作先 = '" + comboBox3.Text + "'"; break; case "読込": case "削除": case "追加": case "更新": q = "SELECT * FROM 製品 WHERE ID = " + this.textBox1.Text + ""; break; } OleDbCommand cmd = new OleDbCommand(q, con); OleDbDataReader dr = cmd.ExecuteReader(); if (!dr.Read()) { MessageBox.Show("データがありませんでした。"); InputClear(); return; } else { int i = 0; do { this.textBox1.Text = dr["ID"].ToString(); this.comboBox1.Text = dr["向先"].ToString(); this.comboBox2.Text = dr["型式"].ToString(); this.comboBox3.Text = dr["製作先"].ToString(); this.textBox14.Text = string.Format("{0:f0}", dr["販売価格"]); this.textBox15.Text = string.Format("{0:f0}", dr["営業手配分"]); this.textBox6.Text = string.Format("{0:f0}", dr["外注費"]); this.textBox8.Text = string.Format("{0:f0}", dr["輸送費荷受"]); this.textBox9.Text = string.Format("{0:f0}", dr["輸送費納品"]); this.textBox10.Text = string.Format("{0:f0}", dr["試験費"]); this.textBox11.Text = string.Format("{0:f0}", dr["品証費用"]); this.textBox12.Text = string.Format("{0:f0}", dr["品証経費"]); this.textBox13.Text = string.Format("{0:f0}", dr["社内製作分"]); this.textBox16.Text = dr["備考"].ToString(); i++; } while (dr.Read()); if (i > 1) MessageBox.Show("向先・型式・製作先の組合わせが重複しているので注意してください。");
}
cmd = new OleDbCommand("SELECT * FROM 部品表 LEFT OUTER JOIN 部品 ON 部品表.部品ID_ = 部品.部品ID WHERE 製品ID = " + this.textBox1.Text + "" , con);
dr = cmd.ExecuteReader();
while(dr.Read())
{
dataGridView1.Rows.Add(
dr["部品表ID"].ToString(),
dr["集計ID"].ToString(),
dr["部品ID"].ToString(),
dr["名称"].ToString(),
dr["材質"].ToString(),
dr["寸法"].ToString(),
dr["単価"].ToString(),
string.Format("{0:f2}", dr["数量"]), "");
}
}
catch
{
MessageBox.Show("データベースに接続できませんでした。");
InputClear();
return;
}
finally
{
con.Close();
}
try
{
decimal ManHour = 0;
decimal PaintingCost = 0;
decimal PartsCost1 = 0;
decimal PartsCost2 = 0;
decimal PartsCost3 = 0;
decimal YieldRatio = 0;
for (int k = 0; k < dataGridView1.Rows.Count; k++)
{
decimal UnitPrice = decimal.Parse(dataGridView1.Rows[k].Cells[6].Value.ToString());
decimal Quantity = decimal.Parse(dataGridView1.Rows[k].Cells[7].Value.ToString());
switch (dataGridView1.Rows[k].Cells[1].Value.ToString())
{
case "1":
ManHour += UnitPrice * Quantity;
break;
case "2":
PaintingCost += UnitPrice * Quantity;
break;
case "3":
PartsCost1 += UnitPrice * Quantity;
break;
case "4":
PartsCost2 += UnitPrice * Quantity;
break;
case "5":
PartsCost3 += UnitPrice * Quantity;
break;
}
}
textBox4.Text = string.Format("{0:f0}", ManHour * (decimal)4000);
textBox5.Text = string.Format("{0:f0}", PaintingCost);
textBox22.Text = string.Format("{0:f0}", PartsCost1);//鋼材(3)
textBox23.Text = string.Format("{0:f0}", PartsCost2);//購入品(4)
textBox25.Text = string.Format("{0:f0}", PartsCost3);//その他(5)
YieldRatio = PartsCost1 * (decimal)0.2;
textBox24.Text = string.Format("{0:f0}", YieldRatio);//歩留
textBox7.Text = string.Format("{0:f0}", PartsCost1 + PartsCost2 + PartsCost3 + YieldRatio);
}
catch
{
MessageBox.Show("部品の単価・数量が不正な値です。修正してください。");
}
switch (sender)
{
case "読込":
case "検索":
MessageBox.Show("取得しました。");
break;
case "削除":
MessageBox.Show("削除しました。");
break;
case "追加":
MessageBox.Show("追加しました。");
break;
case "新規":
MessageBox.Show("作成しました。");
break;
case "更新":
MessageBox.Show("更新しました。");
break;
}
isLoad = true;
dataGridView1.Columns[0].ReadOnly = true;
dataGridView1.Columns[1].ReadOnly = false;
dataGridView1.Columns[2].ReadOnly = true;
dataGridView1.Columns[3].ReadOnly = true;
dataGridView1.Columns[4].ReadOnly = true;
dataGridView1.Columns[5].ReadOnly = true;
dataGridView1.Columns[6].ReadOnly = true;
dataGridView1.Columns[7].ReadOnly = false;
dataGridView1.Columns[8].ReadOnly = true;
dataGridView1.Columns[8].ReadOnly = true;
textBox1.ForeColor = Color.Blue;
}
}
}
}