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;
using System.IO;
namespace 預かり在庫管理ver2
{
public partial class Form1 : Form
{
public void dgv1_setup()
{
string[] header = new string[] {
"ID",
"登録日",
"伝票番号",
"得意先",
"仕入先",
"品目名",
"PO",
"INV",
"数量",
"備考"
};
foreach (string h in header)
{
DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
col.HeaderText = h;
dataGridView1.Columns.Add(col);
}
for (int i = 1; i <= 2; i++)
{
DataGridViewButtonColumn col_btn = new DataGridViewButtonColumn();
col_btn.HeaderText = " ";
dataGridView1.Columns.Add(col_btn);
}
dataGridView1.Columns[0].Visible = false;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
dataGridView1.RowTemplate.Height = 28;
dataGridView1.Font = new Font("Meiryo UI", 9);
dataGridView1.MultiSelect = false;
dataGridView1.CellClick += new DataGridViewCellEventHandler(cell_click);
}
public void dgv2_setup()
{
string[] header = new string[] {
"ID",
"出庫日",
"出庫数"
};
foreach (string h in header)
{
DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
col.Name = h;
col.HeaderText = h;
dataGridView2.Columns.Add(col);
}
DataGridViewButtonColumn col_btn = new DataGridViewButtonColumn();
col_btn.HeaderText = " ";
dataGridView2.Columns.Add(col_btn);
dataGridView2.Columns[0].Visible = false;
dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
dataGridView2.RowTemplate.Height = 28;
dataGridView2.Font = new Font("Meiryo UI", 9);
dataGridView2.MultiSelect = false;
dataGridView2.CellClick += new DataGridViewCellEventHandler(sub_cell_click);
}
public Form1()
{
InitializeComponent();
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd1 = new OleDbCommand("select distinct customer_name from orders;", con);
using (OleDbDataReader dr = cmd1.ExecuteReader())
{
while (dr.Read())
{
comboBox1.Items.Add(dr["customer_name"].ToString());
}
}
OleDbCommand cmd2 = new OleDbCommand("select distinct supplier_name from orders;", con);
using (OleDbDataReader dr = cmd2.ExecuteReader())
{
while (dr.Read())
{
comboBox2.Items.Add(dr["supplier_name"].ToString());
}
}
OleDbCommand cmd3 = new OleDbCommand("select distinct item_name from orders;", con);
using (OleDbDataReader dr = cmd3.ExecuteReader())
{
while (dr.Read())
{
comboBox3.Items.Add(dr["item_name"].ToString());
}
}
}
catch (OleDbException e)
{
foreach (Control c in this.Controls)
{
c.Enabled = false;
}
MessageBox.Show(e.Message);
return;
}
finally
{
con.Close();
}
}
textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {
button1.Text = textBox1.Text == "" ? "登録" : "更新";
});
dgv1_setup();
dgv2_setup();
dgv_load("select * from orders where visible <> 'hide';", "完了");
form_clear();
}
private string rp(string s)
{
s = s.Replace("\'", "’");
s = s.Replace("\"", "”");
return s;
}
private void sub_cell_click(object sender, DataGridViewCellEventArgs e)
{
int btn_ci = 3;
int ri = e.RowIndex;
int ci = e.ColumnIndex;
if (ri == -1 || ci == -1) return;
string id = dataGridView2.Rows[ri].Cells[0].Value.ToString();
if (ci == btn_ci)
{
DialogResult yn = MessageBox.Show("削除しますか？", "", MessageBoxButtons.YesNo);
if (yn == DialogResult.No) return;
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand("delete from shipments where shipments_id = " + id + ";", con);
cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
sub_dgv_load(textBox1.Text);
}
}
private void cell_click(object sender, DataGridViewCellEventArgs e)
{
int btn1_ci = 10;
int btn2_ci = 11;
int btn3_ci = 6;
int ri = e.RowIndex;
int ci = e.ColumnIndex;
if (ri == -1 || ci == -1) return;
string id = dataGridView1.Rows[ri].Cells[0].Value.ToString();
string po = dataGridView1.Rows[ri].Cells[6].Value.ToString();
string btn_cap = dataGridView1.Rows[ri].Cells[btn1_ci].Value.ToString();
if (ci == btn1_ci)
{
rows_change(id, btn_cap);
dgv_load("select * from orders where visible <> 'hide';", "完了");
form_clear();
return;
}
else if (ci == btn2_ci)
{
DialogResult yn = MessageBox.Show("削除しますか？", "", MessageBoxButtons.YesNo);
if (yn == DialogResult.No) return;
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand("delete from orders where orders_id = " + id + ";", con);
cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
dgv_load("select * from orders where visible <> 'hide';", "完了");
form_clear();
return;
}
else if (ci == btn3_ci)
{
open_Folder(po);
return;
}
textBox1.Text = dataGridView1.Rows[ri].Cells[0].Value.ToString();
textBox8.Text = dataGridView1.Rows[ri].Cells[1].Value.ToString();
textBox2.Text = dataGridView1.Rows[ri].Cells[2].Value.ToString();
comboBox1.Text = dataGridView1.Rows[ri].Cells[3].Value.ToString();
comboBox2.Text = dataGridView1.Rows[ri].Cells[4].Value.ToString();
comboBox3.Text = dataGridView1.Rows[ri].Cells[5].Value.ToString();
textBox5.Text = dataGridView1.Rows[ri].Cells[6].Value.ToString();
textBox6.Text = dataGridView1.Rows[ri].Cells[7].Value.ToString();
textBox9.Text = dataGridView1.Rows[ri].Cells[8].Value.ToString();
textBox7.Text = dataGridView1.Rows[ri].Cells[9].Value.ToString();
sub_dgv_load(id);
}
private void sub_dgv_load(string id)
{
dataGridView2.Rows.Clear();
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand("select * from shipments where orders_id = " + id + ";", con);
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dataGridView2.Rows.Add(
dr["shipments_id"].ToString(),
DateTime.Parse(dr["ship_date"].ToString()).ToString("yyyy/MM/dd"),
dr["quantity"].ToString(),
"削除");
}
}
}
catch (OleDbException e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
}
int stock_count = 0;
for (int r = 0; r < dataGridView2.Rows.Count; r++)
{
stock_count += int.Parse(dataGridView2.Rows[r].Cells[2].Value.ToString());
}
textBox12.Text = stock_count.ToString();
}
private void rows_change(string id,string btn_cap)
{
string tmp = (btn_cap == "完了") ? "hide" : "show";
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand("update orders set visible = '" + tmp + "' where orders_id = " + id + ";", con);
cmd.ExecuteNonQuery();
}
catch (OleDbException e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
}
}
private void form_clear()
{
textBox1.Text = "";
textBox8.Text = DateTime.Now.ToString("yyyy/MM/dd");
textBox2.Text = "";
comboBox1.Text = "";
comboBox2.Text = "";
comboBox3.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox9.Text = "";
textBox7.Text = "";
textBox14.Text = DateTime.Now.ToString("yyyy/MM/dd");
textBox12.Text = "";
textBox10.Text = "";
dataGridView2.Rows.Clear();
}
private bool form_validate()
{
if (textBox8.Text == "" | textBox2.Text == "" | comboBox1.Text == "" | comboBox2.Text == "" |
comboBox3.Text == "" | textBox5.Text == "" | textBox6.Text == "" | textBox9.Text == "")
{
MessageBox.Show("入力が不足しています。");
return false;
}
try
{
DateTime.Parse(textBox8.Text);
}
catch
{
MessageBox.Show("日付が正しくありません。");
return false;
}
try
{
int.Parse(textBox9.Text);
}
catch
{
MessageBox.Show("数量が正しくありません。");
return false;
}
textBox2.Text = rp(textBox2.Text);
comboBox1.Text = rp(comboBox1.Text);
comboBox2.Text = rp(comboBox2.Text);
comboBox3.Text = rp(comboBox3.Text);
textBox5.Text = rp(textBox5.Text);
textBox6.Text = rp(textBox6.Text);
textBox7.Text = rp(textBox7.Text);
return true;
}
private bool change_tbl()
{
string query;
if (button1.Text == "登録")
{
query = "insert into orders (register_date,order_no,customer_name,supplier_name,item_name,po_no,inv_no,quantity,notes,visible) values (" +
"'" + textBox8.Text + "'," +
"'" + textBox2.Text + "'," +
"'" + comboBox1.Text + "'," +
"'" + comboBox2.Text + "'," +
"'" + comboBox3.Text + "'," +
"'" + textBox5.Text + "'," +
"'" + textBox6.Text + "'," +
"" + textBox9.Text + "," +
"'" + textBox7.Text + "','show');";
}
else
{
query = "update orders set " +
"register_date='" + textBox8.Text + "'," +
"order_no='" + textBox2.Text + "'," +
"customer_name='" + comboBox1.Text + "'," +
"supplier_name='" + comboBox2.Text + "'," +
"item_name='" + comboBox3.Text + "'," +
"po_no='" + textBox5.Text + "'," +
"inv_no='" + textBox6.Text + "'," +
"quantity=" + textBox9.Text + "," +
"notes='" + textBox7.Text + "'" +
"where orders_id = " + textBox1.Text + ";";
}
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand(query, con);
cmd.ExecuteNonQuery();
}
catch (OleDbException e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
con.Close();
}
}
return true;
}
private void dgv_load(string query,string btn_cap)
{
dataGridView1.Rows.Clear();
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand(query,con);
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dataGridView1.Rows.Add(
dr["orders_id"].ToString(),
DateTime.Parse( dr["register_date"].ToString()).ToString("yyyy/MM/dd"),
dr["order_no"].ToString(),
dr["customer_name"].ToString(),
dr["supplier_name"].ToString(),
dr["item_name"].ToString(),
dr["po_no"].ToString(),
dr["inv_no"].ToString(),
dr["quantity"].ToString(),
dr["notes"].ToString(),
btn_cap,"削除");
}
}
}
catch (OleDbException e)
{
MessageBox.Show(e.Message);
}
finally
{
con.Close();
}
}
if (btn_cap == "完了")
{
button6.Enabled = false;
button5.Enabled = true;
}
if (btn_cap == "戻入")
{
button6.Enabled = true;
button5.Enabled = false;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (!form_validate()) return;
if (!change_tbl()) return;
if (button1.Text == "登録")
{
dgv_load("select * from orders where visible <> 'hide';", "完了");
}
else if (button1.Text == "更新" && button5.Enabled == true)
{
dgv_load("select * from orders where visible <> 'hide';", "完了");
}
else if (button1.Text == "更新" && button5.Enabled == false)
{
dgv_load("select * from orders where visible = 'hide';", "戻入");
}
form_clear();
MessageBox.Show("完了しました。");
}
private void button3_Click(object sender, EventArgs e)
{
form_clear();
}
private bool sub_form_validate()
{
if (textBox14.Text == "" | textBox13.Text == "")
{
MessageBox.Show("入力が不足しています。");
return false;
}
try
{
DateTime.Parse(textBox14.Text);
}
catch
{
MessageBox.Show("日付が正しくありません。");
return false;
}
try
{
int.Parse(textBox13.Text);
}
catch
{
MessageBox.Show("数量が正しくありません。");
return false;
}
return true;
}
private void button4_Click(object sender, EventArgs e)
{
if (!form_validate()) return;
if (!sub_form_validate()) return;
if (textBox1.Text == "") return;
string query = "insert into shipments (orders_id,ship_date,quantity) values " +
"(" + textBox1.Text + ",'" + textBox14.Text + "'," + textBox13.Text + ");";
using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; data source=" + Application.StartupPath + @"\dat.mdb"))
{
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand(query, con);
cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
sub_dgv_load(textBox1.Text);
textBox14.Text = DateTime.Now.ToString("yyyy/MM/dd");
textBox13.Text = "";
}
private void button6_Click(object sender, EventArgs e)
{
dgv_load("select * from orders where visible <> 'hide';","完了");
form_clear();
}
private void button5_Click_1(object sender, EventArgs e)
{
dgv_load("select * from orders where visible = 'hide';","戻入");
form_clear();
}
private void button2_Click(object sender, EventArgs e)
{
string q = "";
textBox10.Text = rp(textBox10.Text);
if (textBox10.Text == "" && button5.Enabled == true)
{
dgv_load("select * from orders where visible <> 'hide';", "完了");
}
else if (textBox10.Text == "" && button5.Enabled == false)
{
dgv_load("select * from orders where visible = 'hide';", "戻入");
}
else if (textBox10.Text != "" && button5.Enabled == true)
{
q = "select * from orders where visible <> 'hide' and " +
"(order_no like '%" + textBox10.Text + "%' or " +
"customer_name like '%" + textBox10.Text + "%' or " +
"supplier_name like '%" + textBox10.Text + "%' or " +
"item_name like '%" + textBox10.Text + "%' or " +
"po_no like '%" + textBox10.Text + "%' or " +
"inv_no like '%" + textBox10.Text + "%' or " +
"notes like '%" + textBox10.Text + "%')";
dgv_load(q, "完了");
}
else if (textBox10.Text != "" && button5.Enabled == false)
{
q = "select * from orders where visible = 'hide' and " +
"(order_no like '%" + textBox10.Text + "%' or " +
"customer_name like '%" + textBox10.Text + "%' or " +
"supplier_name like '%" + textBox10.Text + "%' or " +
"item_name like '%" + textBox10.Text + "%' or " +
"po_no like '%" + textBox10.Text + "%' or " +
"inv_no like '%" + textBox10.Text + "%' or " +
"notes like '%" + textBox10.Text + "%')";
dgv_load(q, "戻入");
}
}
void open_Folder(string po)
{
string path = "";
try
{
using (StreamReader sr = new StreamReader(Application.StartupPath + @"\setting.txt", Encoding.GetEncoding("shift_jis")))
{
path = sr.ReadLine() + @"\" + po;
if (Directory.Exists(path))
{
System.Diagnostics.Process.Start(path);
}
else
{
DialogResult yn = MessageBox.Show("フォルダを作成します。実行しますか？", "", MessageBoxButtons.YesNo);
if (yn == DialogResult.No) return;
Directory.CreateDirectory(path);
System.Diagnostics.Process.Start(path);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
}
}