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
{
OleDbConnection con;
OleDbDataAdapter da;
DataTable dt;
private string BuildQuery()
{
string query = "select " + this.textBox4.Text + " from tbl";
string join = " where";
if(this.textBox1.Text != "")
{
query += join + " 納期 >= #" + this.textBox1.Text + "#";
join = " and";
}
if (this.textBox2.Text != "")
{
query += join + " 納期 <= #" + this.textBox2.Text + "#";
join = " and";
}
if (this.textBox3.Text != "")
{
query += join + " " + this.comboBox3.Text + " like '%" + this.textBox3.Text + "%'";
}
if(this.comboBox2.Text != "")
{
query += " order by " + this.comboBox2.Text;
}
return query;
}
private bool ConnectDatabase()
{
try
{
con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\dat.mdb");
con.Open();
}
catch(OleDbException e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
con.Close();
}
return true;
}
public void DataSourceReset(string query)
{
da = new OleDbDataAdapter(query, con);
dt = new DataTable();
dataGridView1.Columns.Clear();
try
{
da.Fill(dt);
}
catch(OleDbException)
{
MessageBox.Show("列が正しく取得できませんでした。表示列の設定が正しくない可能性があります。");
}
dataGridView1.DataSource = dt;
this.comboBox2.Items.Clear();
this.comboBox3.Items.Clear();
List<string> colsName = new List<string>();
foreach (DataColumn col in dt.Columns)
{
colsName.Add(col.ColumnName);
}
this.comboBox2.Items.AddRange(colsName.ToArray());
this.comboBox3.Items.AddRange(colsName.ToArray());
}
public void InitControl()
{
this.radioButton1.Checked = true;
this.textBox1.Leave += new EventHandler(FormatDate);
this.textBox2.Leave += new EventHandler(FormatDate);
this.comboBox1.Items.AddRange(new string[] { "月", "週", "日" });
this.comboBox1.Text = "日";
this.button1.Click += new EventHandler(ClickSearch);
this.button2.Click += new EventHandler(ClickUpdate);
this.button3.Click += new EventHandler(SearchFormClear);
this.button4.Click += new EventHandler(UpdateSettings);
this.dataGridView1.DataError += new DataGridViewDataErrorEventHandler(DataGridViewError);
}
private void SearchFormClear(object sender, EventArgs e)
{
this.comboBox2.Text = "";
this.comboBox3.Text = "";
this.textBox1.Text = "";
this.textBox2.Text = "";
this.textBox3.Text = "";
DataSourceReset(BuildQuery());
}
private void FormatDate(object sender, EventArgs e)
{
DateTime dt;
try
{
dt = DateTime.Parse(((TextBox)sender).Text);
}
catch(FormatException)
{
((TextBox)sender).Text = "";
return;
}
((TextBox)sender).Text = dt.ToString("yyyy/MM/dd");
}
private void DataGridViewError(object sender, DataGridViewDataErrorEventArgs e)
{
MessageBox.Show("入力が正しくありません。");
e.Cancel = false;
}
public Form1()
{
InitializeComponent();
Settings settings = new Settings(this);
if (!settings.InitCheck) return;
if (!ConnectDatabase()) return;
DataSourceReset(BuildQuery());
InitControl();
}
private void ClickSearch(object sender, EventArgs e)
{
if (this.textBox1.Text != "")
{
try
{
DateTime dt = DateTime.Parse(this.textBox1.Text);
}
catch (FormatException)
{
MessageBox.Show("開始日(納期)が正しい日付ではありません。");
return;
}
}
if (this.textBox2.Text != "")
{
try
{
DateTime dt = DateTime.Parse(this.textBox2.Text);
}
catch (FormatException)
{
MessageBox.Show("終了日(納期)が正しい日付ではありません。");
return;
}
}
if (this.textBox3.Text != "" && this.comboBox3.Text == "")
{
MessageBox.Show("検索対象を選択してください。");
return;
}
DataSourceReset(BuildQuery());
}
private void ClickUpdate(object sender, EventArgs e)
{
try
{
con.Open();
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.Update(dt);
}
catch(InvalidOperationException)
{
MessageBox.Show("正しく更新できませんでした。テーブルにIDを表示させていない可能性があります。");
return;
}
finally
{
con.Close();
}
dt.AcceptChanges();
DataSourceReset(BuildQuery());
MessageBox.Show("更新しました。");
}
private void UpdateSettings(object sender, EventArgs e)
{
if (this.textBox4.Text == "") this.textBox4.Text = "*";
try
{
con.Open();
OleDbCommand dc = new OleDbCommand("update settings set select_string = '" + this.textBox4.Text + "' where id = 1",con);
dc.ExecuteNonQuery();
}
catch(InvalidOperationException)
{
MessageBox.Show("正しく更新できませんでした。");
return;
}
finally
{
con.Close();
}
DataSourceReset(BuildQuery());
MessageBox.Show("更新しました。");
}
}
}
