C# VBA タイムカードアプリ

以前作ったタイムカードアプリ。氏名などはフォームに直接。

Form1.cs

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.Collections;
using System.Data.OleDb;
using System.Drawing.Text;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.WindowState = FormWindowState.Maximized;
            this.label2.Font = new System.Drawing.Font("7barP",110);
            this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");
            this.label2.Text = DateTime.Now.ToString("HH:mm:ss");
            attach_function();
            this.timer1.Start();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");
            this.label2.Text = DateTime.Now.ToString("HH:mm:ss");
        }
        private Control[] get_controls(Control control_top)
        {
            ArrayList tmp = new ArrayList();
            foreach(Control ctrl in control_top.Controls)
            {
                tmp.Add(ctrl);
                if (ctrl is TabControl)
                {
                    foreach (Control tb in ctrl.Controls)
                    {
                        foreach (Control btn in tb.Controls) tmp.Add(btn);
                    } 
                }
            }
            return (Control[])tmp.ToArray(typeof(Control));
        }
        public void attach_function()
        {
            Control[] controls = get_controls(this);
            foreach (Control ctrl in controls)
            {
                if (ctrl is Button) ((Button)ctrl).Click += new EventHandler(click_button);
                if (ctrl is RadioButton) ((RadioButton)ctrl).CheckedChanged += new EventHandler(clear_labels);
            }
        }
        public void click_button(object sender, EventArgs e)
        {
            clear_labels();
            if (((Button)sender).Text == "リセット") return;
            if (get_check_state() != null)
            {
                connction_database(get_check_state().Text, ((Button)sender).Text);
            }
            else
            {
                connction_database("", ((Button)sender).Text);
            }
        }
        public void clear_labels(object sender = null, EventArgs e = null)
        {
            this.label3.Text = "00/00 00:00:00";
            this.label4.Text = "00/00 00:00:00";
            this.label5.Text = "00/00 00:00:00";
            this.label6.Text = "00/00 00:00:00";
        }
        public void clear_check_state()
        {
            this.radioButton1.Checked = false;
            this.radioButton2.Checked = false;
            this.radioButton3.Checked = false;
            this.radioButton4.Checked = false;
            this.checkBox1.Checked = false;
        }
        public void save_log(string check_text, string user)
        {
            using (System.IO.StreamWriter s = new System.IO.StreamWriter(System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\log.txt", true, System.Text.Encoding.GetEncoding("shift_jis")))
            {
                s.Write(user + "," + check_text + "," + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\r\n");
            }
        }
        public RadioButton get_check_state()
        {
            Control[] controls = get_controls(this);
            foreach (Control ctrl in controls)
            {
                if (ctrl is RadioButton)
                {
                    if (((RadioButton)ctrl).Checked) return (RadioButton)ctrl;
                }
            }
            return null;
        }
        public void connction_database(String checked_text, String button_text)
        {
            String start_day = DateTime.Now.ToString("yyyy/MM/dd 00:00:01");
            if (this.checkBox1.Checked == true) start_day = DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd 00:00:01");
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    if (checked_text != "")
                    {
                        OleDbCommand cmd_main = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);
                        using (OleDbDataReader dr_main = cmd_main.ExecuteReader())
                        {
                            if (dr_main.Read())
                            {
                                if (dr_main[checked_text].ToString() == "")
                                {
                                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                                    OleDbCommand cmd = new OleDbCommand("UPDATE Tc_Tbl SET " + checked_text + " = '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "' WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);
                                    cmd.Transaction = transaction;
                                    cmd.ExecuteNonQuery();
                                    transaction.Commit();
                                    save_log(checked_text, button_text);
                                    //MessageBox.Show(checked_text + "を登録しました。");
                                }
                                else if (dr_main[checked_text].ToString() != "")
                                {
                                    MessageBox.Show("既に" + checked_text + "は登録されています。");
                                }
                                if (dr_main.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");
                            }
                            else
                            {
                                transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                                OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (日付," + checked_text + ",氏名) VALUES ('" + start_day + "', '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', '" + button_text + "')", cn);
                                cmd.Transaction = transaction;
                                cmd.ExecuteNonQuery();
                                transaction.Commit();
                                save_log(checked_text, button_text);
                                //MessageBox.Show(checked_text + "を登録しました。");
                            }
                        }
                        clear_check_state();
                    }
                    OleDbCommand cmd_to_labels = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);
                    using(OleDbDataReader dr_to_labels = cmd_to_labels.ExecuteReader())
                    {
                        if (dr_to_labels.Read())
                        {
                            if (dr_to_labels["出社"].ToString() != "") label3.Text = DateTime.Parse(dr_to_labels["出社"].ToString()).ToString("MM/dd HH:mm:ss");
                            if (dr_to_labels["退社"].ToString() != "") label4.Text = DateTime.Parse(dr_to_labels["退社"].ToString()).ToString("MM/dd HH:mm:ss");
                            if (dr_to_labels["外出"].ToString() != "") label5.Text = DateTime.Parse(dr_to_labels["外出"].ToString()).ToString("MM/dd HH:mm:ss");
                            if (dr_to_labels["帰社"].ToString() != "") label6.Text = DateTime.Parse(dr_to_labels["帰社"].ToString()).ToString("MM/dd HH:mm:ss");
                        }
                        if (dr_to_labels.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {
            clear_labels();
            clear_check_state();
        }
        private void 編集ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2(get_controls(this));
            f.ShowDialog();
        }
        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //button3.PerformClick();
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

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.Collections;

using System.Data.OleDb;

using System.Drawing.Text;

namespace WindowsFormsApplication1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.WindowState = FormWindowState.Maximized;

this.label2.Font = new System.Drawing.Font("7barP",110);

this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");

this.label2.Text = DateTime.Now.ToString("HH:mm:ss");

attach_function();

this.timer1.Start();

}

private void timer1_Tick(object sender, EventArgs e)

{

this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");

this.label2.Text = DateTime.Now.ToString("HH:mm:ss");

}

private Control[] get_controls(Control control_top)

{

ArrayList tmp = new ArrayList();

foreach(Control ctrl in control_top.Controls)

{

tmp.Add(ctrl);

if (ctrl is TabControl)

{

foreach (Control tb in ctrl.Controls)

{

foreach (Control btn in tb.Controls) tmp.Add(btn);

}

return (Control[])tmp.ToArray(typeof(Control));

}

public void attach_function()

{

Control[] controls = get_controls(this);

foreach (Control ctrl in controls)

{

if (ctrl is Button) ((Button)ctrl).Click += new EventHandler(click_button);

if (ctrl is RadioButton) ((RadioButton)ctrl).CheckedChanged += new EventHandler(clear_labels);

}

public void click_button(object sender, EventArgs e)

{

clear_labels();

if (((Button)sender).Text == "リセット") return;

if (get_check_state() != null)

{

connction_database(get_check_state().Text, ((Button)sender).Text);

}

else

{

connction_database("", ((Button)sender).Text);

}

public void clear_labels(object sender = null, EventArgs e = null)

{

this.label3.Text = "00/00 00:00:00";

this.label4.Text = "00/00 00:00:00";

this.label5.Text = "00/00 00:00:00";

this.label6.Text = "00/00 00:00:00";

}

public void clear_check_state()

{

this.radioButton1.Checked = false;

this.radioButton2.Checked = false;

this.radioButton3.Checked = false;

this.radioButton4.Checked = false;

this.checkBox1.Checked = false;

}

public void save_log(string check_text, string user)

{

using (System.IO.StreamWriter s = new System.IO.StreamWriter(System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\log.txt", true, System.Text.Encoding.GetEncoding("shift_jis")))

{

s.Write(user + "," + check_text + "," + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\r\n");

}

public RadioButton get_check_state()

{

Control[] controls = get_controls(this);

foreach (Control ctrl in controls)

{

if (ctrl is RadioButton)

{

if (((RadioButton)ctrl).Checked) return (RadioButton)ctrl;

}

return null;

}

public void connction_database(String checked_text, String button_text)

{

String start_day = DateTime.Now.ToString("yyyy/MM/dd 00:00:01");

if (this.checkBox1.Checked == true) start_day = DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd 00:00:01");

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

if (checked_text != "")

{

OleDbCommand cmd_main = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);

using (OleDbDataReader dr_main = cmd_main.ExecuteReader())

{

if (dr_main.Read())

{

if (dr_main[checked_text].ToString() == "")

{

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("UPDATE Tc_Tbl SET " + checked_text + " = '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "' WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

save_log(checked_text, button_text);

//MessageBox.Show(checked_text + "を登録しました。");

}

else if (dr_main[checked_text].ToString() != "")

{

MessageBox.Show("既に" + checked_text + "は登録されています。");

}

if (dr_main.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");

}

else

{

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (日付," + checked_text + ",氏名) VALUES ('" + start_day + "', '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', '" + button_text + "')", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

save_log(checked_text, button_text);

//MessageBox.Show(checked_text + "を登録しました。");

}

clear_check_state();

}

OleDbCommand cmd_to_labels = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);

using(OleDbDataReader dr_to_labels = cmd_to_labels.ExecuteReader())

{

if (dr_to_labels.Read())

{

if (dr_to_labels["出社"].ToString() != "") label3.Text = DateTime.Parse(dr_to_labels["出社"].ToString()).ToString("MM/dd HH:mm:ss");

if (dr_to_labels["退社"].ToString() != "") label4.Text = DateTime.Parse(dr_to_labels["退社"].ToString()).ToString("MM/dd HH:mm:ss");

if (dr_to_labels["外出"].ToString() != "") label5.Text = DateTime.Parse(dr_to_labels["外出"].ToString()).ToString("MM/dd HH:mm:ss");

if (dr_to_labels["帰社"].ToString() != "") label6.Text = DateTime.Parse(dr_to_labels["帰社"].ToString()).ToString("MM/dd HH:mm:ss");

}

if (dr_to_labels.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

private void button3_Click(object sender, EventArgs e)

{

clear_labels();

clear_check_state();

}

private void 編集ToolStripMenuItem1_Click(object sender, EventArgs e)

{

Form2 f = new Form2(get_controls(this));

f.ShowDialog();

}

private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)

{

//button3.PerformClick();

}

Form2.cs

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.Collections;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        private String database_path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);

        public Form2(Control[] parent_contorls)
        {
            InitializeComponent();
            foreach (Control ctrl in parent_contorls) if (ctrl is Button && ctrl.Text != "リセット") this.comboBox1.Items.Add(ctrl.Text);
            for (int i = -2; i <= 2; i++) this.comboBox2.Items.Add(DateTime.Now.Year + i);
            for (int i = 1; i <= 12; i++) this.comboBox3.Items.Add(i.ToString().PadLeft(2, '0'));
            for (int i = 1; i <= 31; i++) this.comboBox4.Items.Add(i.ToString().PadLeft(2, '0'));
            this.comboBox2.Text = DateTime.Now.ToString("yyyy");
            this.comboBox3.Text = DateTime.Now.ToString("MM");
            this.comboBox4.Text = DateTime.Now.ToString("dd");
            this.WindowState = FormWindowState.Maximized;
            //this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            //this.dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            if (this.textBox1.Text != "1234") return;
            if (this.comboBox1.Text == "")
            {
                MessageBox.Show("名前を選択してください。");
                return;
            }
            DateTime dt;
            string[] check_date = new string[2];
            if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/" + this.comboBox4.Text, out dt))
            {
                check_date[0] = dt.ToString("yyyy/MM/dd 00:00:01");
                check_date[1] = dt.ToString("yyyy/MM/dd 08:25:00");
            }
            else
            {
                MessageBox.Show("日付が適切ではありません。");
                return;
            }
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                    OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (氏名,日付) VALUES ('" + this.comboBox1.Text + "','" + check_date[0] + "')", cn);
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
            }
            button1.PerformClick();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if(this.comboBox1.Text == "")
            {
                MessageBox.Show("名前を選択してください。");
                return;
            }
            this.dataGridView1.Rows.Clear();
            DateTime tmp;
            DateTime[] chk_date = new DateTime[2];
            if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/1", out tmp))
            {
                chk_date[0] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text), 1, 00, 00, 01);
                chk_date[1] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text),DateTime.DaysInMonth(int.Parse(this.comboBox2.Text),int.Parse(this.comboBox3.Text)), 00, 00, 01);
            }
            else
            {
                MessageBox.Show("日付が適切ではありません。");
                return;
            }
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                try
                {
                    cn.Open();
                    for (DateTime i = chk_date[0]; i <= chk_date[1]; i = i.AddDays(1))
                    {
                        OleDbCommand cmd = new OleDbCommand("SELECT ID,氏名,日付,出社,退社,外出,帰社,早出申請,残業申請,休出申請,有給申請,無給（遅・早・欠）申請,代休申請,備考 FROM Tc_Tbl WHERE" + " 日付 = #" + i.ToString("yyyy/MM/dd 00:00:01") + "# AND 氏名 = '" + this.comboBox1.Text + "'", cn);
                        using(OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            if (!dr.Read())
                            {
                                this.dataGridView1.Rows.Add(
                                    "", //Button
                                    "", //ID
                                    "", //氏名
                                    i.ToString("yyyy/MM/dd 00:00:01"),
                                    "", //出社
                                    "", //退社
                                    "", //外出
                                    "", //帰社
                                    "", //早出申請
                                    "", //残業申請
                                    "", //休出申請
                                    "", //有給申請
                                    "", //無給（遅・早・欠）申請
                                    "", //代休申請
                                    "", //備考
                                    "", //内容不備(NoDb)
                                    "" //Button
                                    );

                                // 2016/04/21 追加
                                if (6 == (int)i.DayOfWeek)
                                {
                                    this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220,220,255);
                                }
                                else if (0 == (int)i.DayOfWeek)
                                {
                                    this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);
                                }
                                
                            }
                            else
                            {
                                int j = 0;
                                do
                                {
                                    if (j != 0) MessageBox.Show(i.ToString("yyyy/MM/dd") + "が重複しています。");
                                    this.dataGridView1.Rows.Add(
                                        "更新", //Button
                                        dr[0], //ID
                                        dr[1], //氏名
                                        DateTime.Parse(dr[2].ToString()).ToString("yyyy/MM/dd 00:00:01"),
                                        dr[3], //出社
                                        dr[4], //退社
                                        dr[5], //外出
                                        dr[6], //帰社
                                        dr[7], //早出申請
                                        dr[8], //残業申請
                                        dr[9], //休出申請
                                        dr[10], //有給申請
                                        dr[11], //無給（遅・早・欠）申請
                                        dr[12], //代休申請
                                        dr[13], //備考
                                        DateCheck.Do(new string[] { dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString() }),
                                        "削除" //Button
                                        );

                                    // 2016/04/21 追加
                                    if (6 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)
                                    {
                                        this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220, 220, 255);
                                    }
                                    else if (0 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)
                                    {
                                        this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);
                                    }

                                    j++;
                                }
                                while (dr.Read());
                            }
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    return;
                }
                finally
                {
                    cn.Close();
                }
            }
            MessageBox.Show("取得しました。");
        }
        private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0 && dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == "更新")
            {
                if (this.textBox1.Text != "1234") return;
                DialogResult YesNo = MessageBox.Show("更新しますか？", "確認", MessageBoxButtons.YesNo);
                if (YesNo == DialogResult.Yes) data_update(e);
            }
            else if (e.ColumnIndex == 16 && dataGridView1.Rows[e.RowIndex].Cells[16].Value.ToString() == "削除")
            {
                if (this.textBox1.Text != "1234") return;
                DialogResult YesNo = MessageBox.Show("削除しますか？","確認", MessageBoxButtons.YesNo);
                if(YesNo == DialogResult.Yes) data_delete(e);
            }
        }
        private void data_delete(DataGridViewCellEventArgs e)
        {
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                    OleDbCommand cmd = new OleDbCommand("DELETE FROM Tc_Tbl WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn);
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
                button1.PerformClick();
            }
        }
        private void data_update(DataGridViewCellEventArgs e)
        {
            string[] row_val = new string[12];
            for (int i = 0; i < 12; i++)
            {
                row_val[i] = "null";
                if (i < 4)
                {
                    DateTime dt;
                    if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "" && DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString(), out dt))
                    {
                        row_val[i] = "'" + dt.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    }
                    else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")
                    {
                        MessageBox.Show(i + 4 + "列目が不正な文字列です。");
                        return;
                    }
                }
                else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")
                {
                    row_val[i] = "'" + dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() + "'";
                }
            }
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                    OleDbCommand cmd = new OleDbCommand(
                        "UPDATE Tc_Tbl SET" + 
                        " 出社 = " + row_val[0] +"," + 
                        " 退社 = " + row_val[1] + "," + 
                        " 外出 = " + row_val[2] + "," + 
                        " 帰社 = " + row_val[3] + "," + 
                        " 早出申請 = " + row_val[4] + "," + 
                        " 残業申請 = " + row_val[5] + "," + 
                        " 休出申請 = " + row_val[6] + "," + 
                        " 有給申請 = " + row_val[7] + "," +
                        " 無給（遅・早・欠）申請 = " + row_val[8] + "," +
                        " 代休申請 = " + row_val[9] + "," + 
                        " 備考 = " + row_val[10] + 
                        " WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn
                        );
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
                button1.PerformClick();
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

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.Collections;

using System.Data.OleDb;

namespace WindowsFormsApplication1

{

public partial class Form2 : Form

{

private String database_path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);

public Form2(Control[] parent_contorls)

{

InitializeComponent();

foreach (Control ctrl in parent_contorls) if (ctrl is Button && ctrl.Text != "リセット") this.comboBox1.Items.Add(ctrl.Text);

for (int i = -2; i <= 2; i++) this.comboBox2.Items.Add(DateTime.Now.Year + i);

for (int i = 1; i <= 12; i++) this.comboBox3.Items.Add(i.ToString().PadLeft(2, '0'));

for (int i = 1; i <= 31; i++) this.comboBox4.Items.Add(i.ToString().PadLeft(2, '0'));

this.comboBox2.Text = DateTime.Now.ToString("yyyy");

this.comboBox3.Text = DateTime.Now.ToString("MM");

this.comboBox4.Text = DateTime.Now.ToString("dd");

this.WindowState = FormWindowState.Maximized;

//this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

//this.dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;

}

private void button2_Click(object sender, EventArgs e)

{

if (this.textBox1.Text != "1234") return;

if (this.comboBox1.Text == "")

{

MessageBox.Show("名前を選択してください。");

return;

}

DateTime dt;

string[] check_date = new string[2];

if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/" + this.comboBox4.Text, out dt))

{

check_date[0] = dt.ToString("yyyy/MM/dd 00:00:01");

check_date[1] = dt.ToString("yyyy/MM/dd 08:25:00");

}

else

{

MessageBox.Show("日付が適切ではありません。");

return;

}

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (氏名,日付) VALUES ('" + this.comboBox1.Text + "','" + check_date[0] + "')", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

button1.PerformClick();

}

private void button1_Click(object sender, EventArgs e)

{

if(this.comboBox1.Text == "")

{

MessageBox.Show("名前を選択してください。");

return;

}

this.dataGridView1.Rows.Clear();

DateTime tmp;

DateTime[] chk_date = new DateTime[2];

if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/1", out tmp))

{

chk_date[0] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text), 1, 00, 00, 01);

chk_date[1] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text),DateTime.DaysInMonth(int.Parse(this.comboBox2.Text),int.Parse(this.comboBox3.Text)), 00, 00, 01);

}

else

{

MessageBox.Show("日付が適切ではありません。");

return;

}

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

try

{

cn.Open();

for (DateTime i = chk_date[0]; i <= chk_date[1]; i = i.AddDays(1))

{

OleDbCommand cmd = new OleDbCommand("SELECT ID,氏名,日付,出社,退社,外出,帰社,早出申請,残業申請,休出申請,有給申請,無給（遅・早・欠）申請,代休申請,備考 FROM Tc_Tbl WHERE" + " 日付 = #" + i.ToString("yyyy/MM/dd 00:00:01") + "# AND 氏名 = '" + this.comboBox1.Text + "'", cn);

using(OleDbDataReader dr = cmd.ExecuteReader())

{

if (!dr.Read())

{

this.dataGridView1.Rows.Add(

"", //Button

"", //ID

"", //氏名

i.ToString("yyyy/MM/dd 00:00:01"),

"", //出社

"", //退社

"", //外出

"", //帰社

"", //早出申請

"", //残業申請

"", //休出申請

"", //有給申請

"", //無給（遅・早・欠）申請

"", //代休申請

"", //備考

"", //内容不備(NoDb)

"" //Button

);

// 2016/04/21 追加

if (6 == (int)i.DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220,220,255);

}

else if (0 == (int)i.DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);

}

else

{

int j = 0;

{

if (j != 0) MessageBox.Show(i.ToString("yyyy/MM/dd") + "が重複しています。");

this.dataGridView1.Rows.Add(

"更新", //Button

dr[0], //ID

dr[1], //氏名

DateTime.Parse(dr[2].ToString()).ToString("yyyy/MM/dd 00:00:01"),

dr[3], //出社

dr[4], //退社

dr[5], //外出

dr[6], //帰社

dr[7], //早出申請

dr[8], //残業申請

dr[9], //休出申請

dr[10], //有給申請

dr[11], //無給（遅・早・欠）申請

dr[12], //代休申請

dr[13], //備考

DateCheck.Do(new string[] { dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString() }),

"削除" //Button

);

// 2016/04/21 追加

if (6 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220, 220, 255);

}

else if (0 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);

}

j++;

}

while (dr.Read());

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

return;

}

finally

{

cn.Close();

}

MessageBox.Show("取得しました。");

}

private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

{

if (e.ColumnIndex == 0 && dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == "更新")

{

if (this.textBox1.Text != "1234") return;

DialogResult YesNo = MessageBox.Show("更新しますか？", "確認", MessageBoxButtons.YesNo);

if (YesNo == DialogResult.Yes) data_update(e);

}

else if (e.ColumnIndex == 16 && dataGridView1.Rows[e.RowIndex].Cells[16].Value.ToString() == "削除")

{

if (this.textBox1.Text != "1234") return;

DialogResult YesNo = MessageBox.Show("削除しますか？","確認", MessageBoxButtons.YesNo);

if(YesNo == DialogResult.Yes) data_delete(e);

}

private void data_delete(DataGridViewCellEventArgs e)

{

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("DELETE FROM Tc_Tbl WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

button1.PerformClick();

}

private void data_update(DataGridViewCellEventArgs e)

{

string[] row_val = new string[12];

for (int i = 0; i < 12; i++)

{

row_val[i] = "null";

if (i < 4)

{

DateTime dt;

if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "" && DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString(), out dt))

{

row_val[i] = "'" + dt.ToString("yyyy/MM/dd HH:mm:ss") + "'";

}

else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")

{

MessageBox.Show(i + 4 + "列目が不正な文字列です。");

return;

}

else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")

{

row_val[i] = "'" + dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() + "'";

}

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand(

"UPDATE Tc_Tbl SET" +

" 出社 = " + row_val[0] +"," +

" 退社 = " + row_val[1] + "," +

" 外出 = " + row_val[2] + "," +

" 帰社 = " + row_val[3] + "," +

" 早出申請 = " + row_val[4] + "," +

" 残業申請 = " + row_val[5] + "," +

" 休出申請 = " + row_val[6] + "," +

" 有給申請 = " + row_val[7] + "," +

" 無給（遅・早・欠）申請 = " + row_val[8] + "," +

" 代休申請 = " + row_val[9] + "," +

" 備考 = " + row_val[10] +

" WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn

);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

button1.PerformClick();

}

DataCheck.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApplication1
{
    class DateCheck
    {
        static public string Do(string[] tmp)
        {
            //出社がない
            if (tmp[1] == "") return "NG";

            //出社が日付と同日か
            if(tmp[0] != "" && tmp[1] != "")
            {
                if (DateTime.Parse(tmp[0]).Date != DateTime.Parse(tmp[1]).Date) return "NG";
            }

            //出社したら退社しているか
            if (tmp[1] != "" && tmp[2] == "") return "NG";

            //退社してて出社がない
            if (tmp[1] == "" && tmp[2] != "") return "NG";

            //退社が出社と同日あるいは翌日か、退社が出社より前ではないか
            if (tmp[1] != "" && tmp[2] != "")
            {
                if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[2]) || DateTime.Parse(tmp[1]).Date != DateTime.Parse(tmp[2]).Date && DateTime.Parse(tmp[1]).AddDays(1).Date != DateTime.Parse(tmp[2]).Date) return "NG";
            }

            //外出が出社より後で退出より前か
            if (tmp[1] != "" && tmp[2] != "" && tmp[3] != "")
            {
                if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[3]) || DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[3])) return "NG";
            }

            //外出したら帰社しているか
            if (tmp[3] != "" && tmp[4] == "") return "NG";

            //帰社してて外出がない
            if (tmp[3] == "" && tmp[4] != "") return "NG";

            //帰社が外出より後で退出より前か
            if (tmp[2] != "" && tmp[3] != "" && tmp[4] != "")
            {
                if (DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[4]) || DateTime.Parse(tmp[3]) > DateTime.Parse(tmp[4])) return "NG";
            }
            return "OK";
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace WindowsFormsApplication1

{

class DateCheck

{

static public string Do(string[] tmp)

{

//出社がない

if (tmp[1] == "") return "NG";

//出社が日付と同日か

if(tmp[0] != "" && tmp[1] != "")

{

if (DateTime.Parse(tmp[0]).Date != DateTime.Parse(tmp[1]).Date) return "NG";

}

//出社したら退社しているか

if (tmp[1] != "" && tmp[2] == "") return "NG";

//退社してて出社がない

if (tmp[1] == "" && tmp[2] != "") return "NG";

//退社が出社と同日あるいは翌日か、退社が出社より前ではないか

if (tmp[1] != "" && tmp[2] != "")

{

if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[2]) || DateTime.Parse(tmp[1]).Date != DateTime.Parse(tmp[2]).Date && DateTime.Parse(tmp[1]).AddDays(1).Date != DateTime.Parse(tmp[2]).Date) return "NG";

}

//外出が出社より後で退出より前か

if (tmp[1] != "" && tmp[2] != "" && tmp[3] != "")

{

if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[3]) || DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[3])) return "NG";

}

//外出したら帰社しているか

if (tmp[3] != "" && tmp[4] == "") return "NG";

//帰社してて外出がない

if (tmp[3] == "" && tmp[4] != "") return "NG";

//帰社が外出より後で退出より前か

if (tmp[2] != "" && tmp[3] != "" && tmp[4] != "")

{

if (DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[4]) || DateTime.Parse(tmp[3]) > DateTime.Parse(tmp[4])) return "NG";

}

return "OK";

}

C#でmdbに登録してエクセルで抜き出して集計

Sub test()

Application.CommandBars("cell").Reset

End Sub
Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("TC集計開始").Delete

With Application.CommandBars("cell").Controls.Add
  .OnAction = "main"
  .Caption = "TC集計開始"
End With

Application.CommandBars("cell").Controls("確認表示").Delete
Application.CommandBars("cell").Controls("確認表示").Delete

With Application.CommandBars("cell").Controls.Add
  .OnAction = "check_layout"
  .Caption = "確認表示"
End With


End Sub
Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("TC集計開始").Delete

Application.CommandBars("cell").Controls("確認表示").Delete
Application.CommandBars("cell").Controls("確認表示").Delete

End Sub

Sub check_layout()

tmp = MsgBox("実行後に保存しないよう注意してください。実行しますか？", vbYesNo)
If tmp = vbNo Then Exit Sub

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then
  
    w.Columns("A:X").ColumnWidth = 0
    w.Rows("1:3").RowHeight = 0
    
  End If
  
Next

MsgBox "終了しました。"

End Sub

Sub init()

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then

    w.Activate
    w.Unprotect
    w.Range("W4").Select
    
    ActiveWindow.Zoom = 85
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1

    w.Range("B5:B35").ClearContents
    w.Range("C5:C35").ClearContents
    w.Range("E5:E35").ClearContents
    w.Range("F5:F35").ClearContents
    w.Range("S5:S35").ClearContents
    w.Range("W4:AK35").ClearContents

    w.Range("B5:C35").NumberFormatLocal = "0"
    w.Range("E5:F35").NumberFormatLocal = "0"
    w.Range("N5:Q35").NumberFormatLocal = "0.00"
    w.Range("W5:W35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
    w.Range("X5:X35").NumberFormatLocal = "0"
    w.Range("Z5:AD35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
    w.Range("AE5:AK35").NumberFormatLocal = "@"
  
    For r = 5 To 35
    
      w.Cells(r, 2).Value = "=HOUR(AA" & r & ")"
      w.Cells(r, 3).Value = "=MINUTE(AA" & r & ")"
      w.Cells(r, 5).Value = "=HOUR(AB" & r & ") + (INT(AB" & r & ")-INT(AA" & r & ")) * 24"
      w.Cells(r, 6).Value = "=MINUTE(AB" & r & ")"
      
      w.Cells(r, 14).Value = "=IF(D" & r & "="""","""",(IF(AND(VALUE(D" & r & ")<VALUE(""7:31""),VALUE(D" & r & ")>=VALUE(""6:31"")),1,IF(AND(VALUE(D" & r & ")<VALUE(""6:31""),VALUE(D" & r & ")>=VALUE(""5:31"")),2,IF(AND(VALUE(D" & r & ")<VALUE(""5:31""),VALUE(D" & r & ")>=VALUE(""4:30"")),3,0)))))"
      
      'w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),8,IF(AND(NOT(VALUE(D" & r & ")<=VALUE(""8:30"")),NOT(VALUE(G" & r & ")>VALUE(""17:21""))),FLOOR(H" & r & "*24-I" & r & ",0.5),IF(AND(VALUE(D" & r & ")>=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),IF(FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)>4,4,FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)),IF(VALUE(G" & r & ")<=VALUE(""17:21""),FLOOR(VALUE(G" & r & ")*24-(""8:30"")*24-I" & r & ",0.5))))))"
      'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(M" & r & "="""",IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0),IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""17:31""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0)))"
      'w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",(IF(VALUE(G" & r & ")*24>TIMEVALUE(""22:01"")*24,FLOOR(G" & r & "-""22:01"",""0:30"")*24,0)))"
      
      ' rev2.3 2016/4/26追加
      w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),8,IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")<VALUE(""17:20"")),FLOOR(VALUE(H" & r & ")*24-I" & r & ",0.25),IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),FLOOR((VALUE(G" & r & ")*24-VALUE(D" & r & ")*24)-I" & r & ",0.25),FLOOR((VALUE(G" & r & ")*24-VALUE(""8:30"")*24)-I" & r & ",0.25)))))"
      'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:30""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""18:00"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:30"")*24,0.25)),0))"
      w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",IF(VALUE(G" & r & ")*24>VALUE(""22:00"")*24,FLOOR(VALUE(G" & r & ")*24-VALUE(""22:00"")*24,0.25),0))"
      
      ' rev2.5.1
      w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:14:59""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:59:59"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:29:59"")*24,0.25)),0))"
      
    Next r

    DoEvents

  End If
  
Next w

End Sub

Sub ed()

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then
    
    w.Activate
    
    For r = 5 To 35
    
      If w.Cells(r, 27).Value = "" Then w.Cells(r, 2).Value = ""
      If w.Cells(r, 27).Value = "" Then w.Cells(r, 3).Value = ""
      If w.Cells(r, 28).Value = "" Then w.Cells(r, 5).Value = ""
      If w.Cells(r, 28).Value = "" Then w.Cells(r, 6).Value = ""

      If w.Cells(r, 31).Value = "" And w.Cells(r, 13).Text = "" Then
      
          w.Cells(r, 14).Value = ""
          
      End If
      
      If w.Cells(r, 32).Value = "" And w.Cells(r, 13).Text = "" Then
      
          w.Cells(r, 16).Value = ""
          w.Cells(r, 17).Value = ""
          
      End If

      If w.Cells(r, 33).Value = "" And w.Cells(r, 13).Text <> "" Then
      
          w.Cells(r, 14).Value = ""
          w.Cells(r, 15).Value = ""
          w.Cells(r, 16).Value = ""
          w.Cells(r, 17).Value = ""
          
      End If
      
      Select Case w.Name
      
          Case "xxx"
    
          Case Else
          
              If w.Cells(r, 13).Text = "" Then w.Cells(r, 15).NumberFormatLocal = """-"""
          
      End Select

    Next r

    DoEvents

  End If

  w.Columns("W:AK").AutoFit

Next w

End Sub

Sub ck()

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then
    
    For r = 5 To 35
    
    Select Case w.Name
    
      Case "xxx"
      Case Else
      
        If IsError(w.Cells(r, 15)) Then
        
          w.Activate: w.Cells(r, 15).Select
          MsgBox w.Name & "の" & r & "行目の定時時間が正しく取得できません。"
        
        Else
        
          If w.Cells(r, 13).Value = "" And w.Cells(r, 15).Value <> "" And w.Cells(r, 15).Value < 8 Then
    
            If w.Cells(r, 19).Value = "" Then
            
              w.Activate: w.Cells(r, 15).Select
              MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "申請がありません。"
              
            ElseIf w.Cells(r, 19).Value <> "" And w.Cells(r, 19).Value <> "有給" And w.Cells(r, 19).Value <> "半休" Then
            
              w.Activate: w.Cells(r, 15).Select
              MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "有給・半休が申請されていません。"
              
            End If
          
          End If
        
        End If
      
    End Select
      
    Next r
      
  End If
Next w

End Sub

Private Sub main()

Call init

f = "#" & Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01#"
tmp = DateAdd("d", -1, DateAdd("m", 1, Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1"))
t = "#" & tmp & " 00:00:01#"

For Each w In Worksheets

    If w.Name <> "MAIN MENU" Then
    
      w.Activate

      If False = fnc(w.Name, f, t) Then Exit Sub
      
      DoEvents
      
    End If

Next w

Call ed
Call ck

MsgBox "done"

End Sub

Function n(fld As Variant)

If False = IsNull(fld) Then

    n = CStr(fld)
    Exit Function

End If

n = ""

End Function

Function fnc(u, f, t)

On Error GoTo try

p = ActiveWorkbook.Path & "\Tc_Db.mdb"
s = "SELECT * FROM Tc_Tbl WHERE 氏名 = '" & u & "' AND 日付 BETWEEN " & f & " AND " & t & ""

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & p & ";"
rs.Open s, cn

k = 0
tmp = Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01"

For i = 5 To 35
      If Month(tmp) = Month(DateAdd("d", k, tmp)) Then Sheets(u).Cells(i, 23) = DateAdd("d", k, tmp)
      k = k + 1
Next

For i = 24 To 37
    Sheets(u).Cells(4, i) = rs(i - 24).Name
Next i

Do Until rs.EOF
    
    For r = 5 To 35
    
      If Sheets(u).Cells(r, 23) <> "" And Day(Sheets(u).Cells(r, 23)) = Day(n(rs(2))) Then

        For c = 24 To 37
        
            Sheets(u).Cells(r, c).Value = n(rs(c - 24))
    
        Next c

        If Sheets(u).Cells(r, 31).Value <> "" Then Sheets(u).Cells(r, 19).Value = "早出申請有"
        If Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残業申請有"
        If Sheets(u).Cells(r, 31).Value <> "" And Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残・早申請有"
        If Sheets(u).Cells(r, 33).Value <> "" Then Sheets(u).Cells(r, 19).Value = "休日出勤申請有"
        If Sheets(u).Cells(r, 34).Value <> "" Then Sheets(u).Cells(r, 19).Value = "有休"
        If InStr(Sheets(u).Cells(r, 34).Value, "(") > 0 Then Sheets(u).Cells(r, 19).Value = "半休"
        If Sheets(u).Cells(r, 35).Value <> "" Then Sheets(u).Cells(r, 19).Value = "欠勤"
        If Sheets(u).Cells(r, 36).Value <> "" Then Sheets(u).Cells(r, 19).Value = "代休"
        If InStr(Sheets(u).Cells(r, 37).Value, "申請不要") > 0 Then Sheets(u).Cells(r, 19).Value = "申請不要"
        
      End If
    
    Next r

    rs.MoveNext
    
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

fnc = True

Exit Function

try:

    MsgBox "error"
    fnc = False
    
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

Sub test()

Application.CommandBars("cell").Reset

End Sub

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "main"

.Caption = "TC集計開始"

End With

Application.CommandBars("cell").Controls("確認表示").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "check_layout"

.Caption = "確認表示"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete

Application.CommandBars("cell").Controls("確認表示").Delete

End Sub

Sub check_layout()

tmp = MsgBox("実行後に保存しないよう注意してください。実行しますか？", vbYesNo)

If tmp = vbNo Then Exit Sub

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Columns("A:X").ColumnWidth = 0

w.Rows("1:3").RowHeight = 0

End If

MsgBox "終了しました。"

End Sub

Sub init()

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Activate

w.Unprotect

w.Range("W4").Select

ActiveWindow.Zoom = 85

ActiveWindow.ScrollColumn = 1

ActiveWindow.ScrollRow = 1

w.Range("B5:B35").ClearContents

w.Range("C5:C35").ClearContents

w.Range("E5:E35").ClearContents

w.Range("F5:F35").ClearContents

w.Range("S5:S35").ClearContents

w.Range("W4:AK35").ClearContents

w.Range("B5:C35").NumberFormatLocal = "0"

w.Range("E5:F35").NumberFormatLocal = "0"

w.Range("N5:Q35").NumberFormatLocal = "0.00"

w.Range("W5:W35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

w.Range("X5:X35").NumberFormatLocal = "0"

w.Range("Z5:AD35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

w.Range("AE5:AK35").NumberFormatLocal = "@"

For r = 5 To 35

w.Cells(r, 2).Value = "=HOUR(AA" & r & ")"

w.Cells(r, 3).Value = "=MINUTE(AA" & r & ")"

w.Cells(r, 5).Value = "=HOUR(AB" & r & ") + (INT(AB" & r & ")-INT(AA" & r & ")) * 24"

w.Cells(r, 6).Value = "=MINUTE(AB" & r & ")"

w.Cells(r, 14).Value = "=IF(D" & r & "="""","""",(IF(AND(VALUE(D" & r & ")<VALUE(""7:31""),VALUE(D" & r & ")>=VALUE(""6:31"")),1,IF(AND(VALUE(D" & r & ")<VALUE(""6:31""),VALUE(D" & r & ")>=VALUE(""5:31"")),2,IF(AND(VALUE(D" & r & ")<VALUE(""5:31""),VALUE(D" & r & ")>=VALUE(""4:30"")),3,0)))))"

'w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),8,IF(AND(NOT(VALUE(D" & r & ")<=VALUE(""8:30"")),NOT(VALUE(G" & r & ")>VALUE(""17:21""))),FLOOR(H" & r & "*24-I" & r & ",0.5),IF(AND(VALUE(D" & r & ")>=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),IF(FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)>4,4,FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)),IF(VALUE(G" & r & ")<=VALUE(""17:21""),FLOOR(VALUE(G" & r & ")*24-(""8:30"")*24-I" & r & ",0.5))))))"

'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(M" & r & "="""",IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0),IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""17:31""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0)))"

'w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",(IF(VALUE(G" & r & ")*24>TIMEVALUE(""22:01"")*24,FLOOR(G" & r & "-""22:01"",""0:30"")*24,0)))"

' rev2.3 2016/4/26追加

w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),8,IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")<VALUE(""17:20"")),FLOOR(VALUE(H" & r & ")*24-I" & r & ",0.25),IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),FLOOR((VALUE(G" & r & ")*24-VALUE(D" & r & ")*24)-I" & r & ",0.25),FLOOR((VALUE(G" & r & ")*24-VALUE(""8:30"")*24)-I" & r & ",0.25)))))"

'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:30""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""18:00"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:30"")*24,0.25)),0))"

w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",IF(VALUE(G" & r & ")*24>VALUE(""22:00"")*24,FLOOR(VALUE(G" & r & ")*24-VALUE(""22:00"")*24,0.25),0))"

' rev2.5.1

w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:14:59""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:59:59"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:29:59"")*24,0.25)),0))"

Next r

DoEvents

End If

Next w

End Sub

Sub ed()

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Activate

For r = 5 To 35

If w.Cells(r, 27).Value = "" Then w.Cells(r, 2).Value = ""

If w.Cells(r, 27).Value = "" Then w.Cells(r, 3).Value = ""

If w.Cells(r, 28).Value = "" Then w.Cells(r, 5).Value = ""

If w.Cells(r, 28).Value = "" Then w.Cells(r, 6).Value = ""

If w.Cells(r, 31).Value = "" And w.Cells(r, 13).Text = "" Then

w.Cells(r, 14).Value = ""

End If

If w.Cells(r, 32).Value = "" And w.Cells(r, 13).Text = "" Then

w.Cells(r, 16).Value = ""

w.Cells(r, 17).Value = ""

End If

If w.Cells(r, 33).Value = "" And w.Cells(r, 13).Text <> "" Then

w.Cells(r, 14).Value = ""

w.Cells(r, 15).Value = ""

w.Cells(r, 16).Value = ""

w.Cells(r, 17).Value = ""

End If

Select Case w.Name

Case "xxx"

Case Else

If w.Cells(r, 13).Text = "" Then w.Cells(r, 15).NumberFormatLocal = """-"""

End Select

Next r

DoEvents

End If

w.Columns("W:AK").AutoFit

Next w

End Sub

Sub ck()

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

For r = 5 To 35

Select Case w.Name

Case "xxx"

Case Else

If IsError(w.Cells(r, 15)) Then

w.Activate: w.Cells(r, 15).Select

MsgBox w.Name & "の" & r & "行目の定時時間が正しく取得できません。"

Else

If w.Cells(r, 13).Value = "" And w.Cells(r, 15).Value <> "" And w.Cells(r, 15).Value < 8 Then

If w.Cells(r, 19).Value = "" Then

w.Activate: w.Cells(r, 15).Select

MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "申請がありません。"

ElseIf w.Cells(r, 19).Value <> "" And w.Cells(r, 19).Value <> "有給" And w.Cells(r, 19).Value <> "半休" Then

w.Activate: w.Cells(r, 15).Select

MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "有給・半休が申請されていません。"

End If

End Select

Next r

End If

Next w

End Sub

Private Sub main()

Call init

f = "#" & Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01#"

tmp = DateAdd("d", -1, DateAdd("m", 1, Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1"))

t = "#" & tmp & " 00:00:01#"

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Activate

If False = fnc(w.Name, f, t) Then Exit Sub

DoEvents

End If

Next w

Call ed

Call ck

MsgBox "done"

End Sub

Function n(fld As Variant)

If False = IsNull(fld) Then

n = CStr(fld)

Exit Function

End If

n = ""

End Function

Function fnc(u, f, t)

On Error GoTo try

p = ActiveWorkbook.Path & "\Tc_Db.mdb"

s = "SELECT * FROM Tc_Tbl WHERE 氏名 = '" & u & "' AND 日付 BETWEEN " & f & " AND " & t & ""

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & p & ";"

rs.Open s, cn

k = 0

tmp = Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01"

For i = 5 To 35

If Month(tmp) = Month(DateAdd("d", k, tmp)) Then Sheets(u).Cells(i, 23) = DateAdd("d", k, tmp)

k = k + 1

For i = 24 To 37

Sheets(u).Cells(4, i) = rs(i - 24).Name

Next i

Do Until rs.EOF

For r = 5 To 35

If Sheets(u).Cells(r, 23) <> "" And Day(Sheets(u).Cells(r, 23)) = Day(n(rs(2))) Then

For c = 24 To 37

Sheets(u).Cells(r, c).Value = n(rs(c - 24))

Next c

If Sheets(u).Cells(r, 31).Value <> "" Then Sheets(u).Cells(r, 19).Value = "早出申請有"

If Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残業申請有"

If Sheets(u).Cells(r, 31).Value <> "" And Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残・早申請有"

If Sheets(u).Cells(r, 33).Value <> "" Then Sheets(u).Cells(r, 19).Value = "休日出勤申請有"

If Sheets(u).Cells(r, 34).Value <> "" Then Sheets(u).Cells(r, 19).Value = "有休"

If InStr(Sheets(u).Cells(r, 34).Value, "(") > 0 Then Sheets(u).Cells(r, 19).Value = "半休"

If Sheets(u).Cells(r, 35).Value <> "" Then Sheets(u).Cells(r, 19).Value = "欠勤"

If Sheets(u).Cells(r, 36).Value <> "" Then Sheets(u).Cells(r, 19).Value = "代休"

If InStr(Sheets(u).Cells(r, 37).Value, "申請不要") > 0 Then Sheets(u).Cells(r, 19).Value = "申請不要"

End If

Next r

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

fnc = True

Exit Function

try:

MsgBox "error"

fnc = False

End Function