?using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace account
{
    public partial class Form1 : Form
    {
        DataSet dtset;
        OleDbCommand cmd;
        OleDbConnection conn;
        OleDbDataAdapter dtada;
        OleDbDataReader dtread;
        string sql, cnn;
        public Form1()
        {
            InitializeComponent();
           
            cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=account.mdb"; 
            conn = new OleDbConnection(cnn );
            conn.Open();
        }

        private void dateTimePickerfrom_ValueChanged(object sender, EventArgs e)
        {

        }

        private void 查詢ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.dataGridViewInquery.Visible = false;
            this.richTextBoxInquery.Visible = false;
            this.comboBoxInqueryType.Text =comboBoxInqueryType.Items[1].ToString();
            this.GroupInquery.Top = this.GroupInquery.Left = 50;
        }

        private void buttonInqueryOk_Click(object sender, EventArgs e)
        {
            System.DateTime dtFrom, dtTo;
            dtFrom = DateTime.Parse (this.dateTimePickerFrom.Text);
            dtTo = DateTime.Parse (this.dateTimePickerTo.Text);
            string itemkey = this.comboBoxInqueryType.Text.Trim(),inquerresult;
            decimal   shouru,zhichu,total;
            if(itemkey!="全部")
                sql = "select  * from data where riqi >=#" +dtFrom+"# and riqi<=#"+ dtTo+"# and shouzhi=\'"+itemkey+"\' order by shouzhi,riqi";
            else
                sql = "select  * from data where riqi >=#" + dtFrom + "# and riqi<=#" + dtTo + "#  order by shouzhi,riqi";
            dtada = new OleDbDataAdapter(sql, conn);
            dtset = new DataSet();
            dtset.Tables.Add("data");
            dtada.Fill(dtset.Tables[0]);
            this.dataGridViewInquery.DataSource = dtset;
            this.dataGridViewInquery.DataMember = dtset.Tables[0].TableName;
            this.dataGridViewInquery.Columns[0].HeaderText = "日期";
            this.dataGridViewInquery.Columns[1].HeaderText = "收支";
            this.dataGridViewInquery.Columns[2].HeaderText = "金額";
            this.dataGridViewInquery.Columns[3].HeaderText = "摘要";
            this.dataGridViewInquery.Columns[3].Width = 300;
            sql = "select jine from data where riqi >=#" + dtFrom + "# and riqi<=#" + dtTo + "# and shouzhi=\'收入\'";
            cmd = new OleDbCommand(sql, conn);
            dtread = cmd.ExecuteReader();
            shouru= 0;
            if (dtread.HasRows)
            {
                while (dtread.Read())
                    shouru += dtread.GetDecimal(dtread.GetOrdinal("jine"));
            }
            dtread.Close();
            sql = "select jine from data where riqi >=#" + dtFrom + "# and riqi<=#" + dtTo + "# and shouzhi=\'支出\'";

            cmd = new OleDbCommand(sql, conn);
            dtread = cmd.ExecuteReader();
            zhichu = 0;
            if (dtread.HasRows)
            {
                while (dtread.Read())
                    zhichu  += dtread.GetDecimal(dtread.GetOrdinal("jine"));
            }
            dtread.Close();
            total = shouru - zhichu;
            inquerresult = dtFrom.Date.ToShortDateString() + " 到 " + dtTo.Date.ToShortDateString() + " 期間：\r\n\r\n" +
                "收入=" + shouru.ToString("￥0.00\r\n支出=") + zhichu.ToString("￥0.00\r\n收支=")+
                total.ToString("￥0.00");
            richTextBoxInquery.Text = inquerresult;
            this.dataGridViewInquery.Visible = true;
            this.richTextBoxInquery.Visible = true;
        }

        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
            conn.Close();
        }

        private void buttonInqueryClose_Click(object sender, EventArgs e)
        {
            GroupInquery.Top = 10000;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            
        }

        private void 數據輸入ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.groupBoxInput.Top = groupBoxInput.Left = 100;
        }

        private void buttonInputOK_Click(object sender, EventArgs e)
        {
            //帳目登記
            System.DateTime dt;
            dt = DateTime.Parse(this.dateTimePickerInput.Text);
            string abs = this.textBoxAbs.Text.Trim(), sz = this.comboBoxInput.Text;
            decimal szcny=decimal.Parse(this.textBoxCNY.Text.Trim());
            sql = "insert into data (riqi,shouzhi,jine,zhaiyao) values (#"+ dt.Date.ToShortDateString()+"#,\'"+sz
                +"\',"+szcny.ToString()+",\'"+ abs+"\')";
            cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();
            System.Windows.Forms.MessageBox.Show("記錄已更新");
        }

        private void buttonInputClose_Click(object sender, EventArgs e)
        {
            this.groupBoxInput.Top = 5000;
        }
    }
}