自用商品统计程序

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Data.Common;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace 商品统计
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //链接数据库准备
        OleDbConnection Olecon;
        OleDbDataAdapter OleDat;
        DataTable dt;
        string strSql;
        string ConStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"/Content.mdb";

        //增加商品名称
        private void button1_Click(object sender, EventArgs e)
        {
            Olecon = new OleDbConnection(ConStr);
            Olecon.Open();
            if (textBox1.Text.Length != 0)
            {
                if (radioButton1.Checked)
                {
                    strSql = "insert into 商品表(商品名称) Values('" + textBox1.Text.Trim() + "')";
                }
                else if (radioButton2.Checked)
                {
                    strSql = "delete From 商品表 where 商品名称='" + textBox1.Text.Trim() + "'";
                }
                OleDbCommand cmd = new OleDbCommand(strSql.ToString(), Olecon);
                cmd.ExecuteNonQuery();
                Olecon.Close();
                //重新加载商品目录
                Load商品();
                comboBox1.Refresh();
                textBox1.Text = string.Empty;
            }
            else
            {
                MessageBox.Show("商品名不能为空,请输入商品名");
            }
        }

        //增加销售人员名单
        private void button2_Click(object sender, EventArgs e)
        {
            Olecon = new OleDbConnection(ConStr);
            Olecon.Open();
            if (textBox2.Text.Length != 0)
            {
                if (radioButton4.Checked)
                {
                    strSql = "insert into 销售员表(销售员名) Values('" + textBox2.Text.Trim() + "')";
                }
                else if (radioButton3.Checked)
                {
                    strSql = "delete From 销售员表 where 销售员名='" + textBox2.Text.Trim() + "'";
                }
                OleDbCommand cmd = new OleDbCommand(strSql.ToString(), Olecon);
                cmd.ExecuteNonQuery();
                Olecon.Close();
                //重新加载销售人员
                Load销售员();
                comboBox2.Refresh();
                textBox2.Text = string.Empty;
            }
            else
            {
                MessageBox.Show("姓名不能为空,请输入姓名");
            }
        }

        //增加销售记录
        private void button3_Click(object sender, EventArgs e)
        {
            string s商品ID = comboBox1.Text;
            s商品ID = s商品ID.Substring(0, s商品ID.IndexOf(","));
            string s销售员ID = comboBox2.Text;
            s销售员ID = s销售员ID.Substring(0, s销售员ID.IndexOf(','));

            if (textBox3.Text.Length != 0)
            {
                Olecon = new OleDbConnection(ConStr);
                Olecon.Open();
                strSql = "insert into 销售记录表 (销售员ID,商品ID,销售数量,销售时间) values ('" + s销售员ID + "','" + s商品ID + "','" + textBox3.Text.Trim() + "','" + textBox4.Text.Trim() + "')";
                OleDbCommand cmd = new OleDbCommand(strSql.ToString(), Olecon);
                cmd.ExecuteNonQuery();
                Olecon.Close();
            }
            else
            {
                MessageBox.Show("请填写销售数量!");
            }
        }

        //模糊查询  商品ID
        private void button4_Click(object sender, EventArgs e)
        {
            if (textBox5.Text.Length != 0)
            {
                OleDbDataAdapter oleDa = new OleDbDataAdapter("select * From 商品表 where 商品名称 Like '%" + textBox5.Text.Trim() + "%'", ConStr);
                DataSet ds = new DataSet();
                oleDa.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0].DefaultView;
            }
            else
            {
                MessageBox.Show("请填写模糊查询“商品名”的关键字!");
            }
        }

        //模糊查询  销售人员ID
        private void button5_Click(object sender, EventArgs e)
        {
            if (textBox6.Text.Length != 0)
            {
                OleDbDataAdapter oleDa = new OleDbDataAdapter("select * From 销售员表 where 销售员名 Like '%" + textBox6.Text.Trim() + "%'", ConStr);
                DataSet ds = new DataSet();
                oleDa.Fill(ds);
                dataGridView2.DataSource = ds.Tables[0].DefaultView;
            }
            else
            {
                MessageBox.Show("请填写模糊查询“销售员”的关键字!");
            }
        }

        //初始化载入
        private void Form1_Load(object sender, EventArgs e)
        {
            //载入商品名
            Load商品();

            //载入销售员
            Load销售员();

            //Load当前日期
            textBox4.Enabled = false;
            textBox4.Text = DateTime.Now.ToShortDateString();
        }


        #region 自定义的方法(1.加载商品 2.加载销售员 3.将DataGridView控件中数据导出到Excel)
        //加载商品
        void Load商品()
        {
            OleDbDataAdapter oleDa = new OleDbDataAdapter("select [商品ID]&','&[商品名称] as 商品 From 商品表", ConStr);
            dt = new DataTable();
            oleDa.Fill(dt);
            comboBox1.DataSource = dt;
            comboBox1.DisplayMember = "商品";

            oleDa = new OleDbDataAdapter("select * From 商品表", ConStr);
            DataSet ds = new DataSet();
            oleDa.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0].DefaultView;
        }

        //加载销售员
         void Load销售员()
        {
            OleDbDataAdapter oleDa = new OleDbDataAdapter("select [销售员ID]&','&[销售员名] as 销售员 From 销售员表", ConStr);
            DataTable dt2 = new DataTable();
            oleDa.Fill(dt2);
            comboBox2.DataSource = dt2;
            comboBox2.DisplayMember = "销售员";

            oleDa = new OleDbDataAdapter("select * From 销售员表", ConStr);
            DataSet ds = new DataSet();
            oleDa.Fill(ds);
            dataGridView2.DataSource = ds.Tables[0].DefaultView;
        }
       
        // 将DataGridView控件中数据导出到Excel   DataGridView对象     isShowExcle是否显示Excel界面
        public bool ExportDataGridview(DataGridView gridView,bool isShowExcle)
         {
            if (gridView.Rows.Count == 0)
                return false;
            //建立Excel对象
             Excel.Application excel = new Excel.Application();
             excel.Application.Workbooks.Add(true);
             excel.Visible = isShowExcle;
            //生成字段名称
            for (int i = 0; i < gridView.ColumnCount; i++)
             {
                 excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
             }
            //填充数据
            for (int i = 0; i < gridView.RowCount-1; i++)
             {
                for (int j = 0; j < gridView.ColumnCount; j++)
                 {
                    if (gridView[j, i].ValueType == typeof(string))
                     {
                         excel.Cells[i + 2, j + 1] = "'" + gridView[j, i].Value.ToString();
                     }
                    else
                     {
                         excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                     }
                 }
             }
            return true;
         }    
        #endregion

        //按商品统计  销售人员  对此项商品 的销售情况
        private void button6_Click(object sender, EventArgs e)
        {
            if (textBox7.Text.Length != 0)
            {
                //select [销售员表].[销售员名],[商品表].[商品名称],[销售记录表].[销售数量],[销售记录表].[销售时间] from
                //(Select * From [销售记录表] Inner Join [商品表] ON [销售记录表].[商品ID] = [商品表].[商品ID])bb
                //Inner Join [销售员表] ON bb.[销售员ID] = [销售员表].[销售员ID]

                OleDbDataAdapter oleDa = new OleDbDataAdapter("select 商品名称,销售员名,SUM(销售数量) as 销售数量 from (Select * From (select * from 销售记录表 where 销售时间 between #" + dateTimePicker1.Value.Date + "# and #" + dateTimePicker2.Value.Date + "#) A Inner Join 商品表 B ON A.商品ID = B.商品ID)bb Inner Join [销售员表] C ON bb.[销售员ID] = C.[销售员ID] where A.商品ID=" + textBox7.Text.Trim() + " GROUP BY 商品名称,销售员名", ConStr);

                //OleDbDataAdapter oleDa = new OleDbDataAdapter("select 商品名称,销售员名,SUM(销售数量) as 销售数量 from (Select * From 销售记录表 A Inner Join 商品表 B ON A.商品ID = B.商品ID)bb Inner Join [销售员表] C ON bb.[销售员ID] = C.[销售员ID] where A.商品ID=" + textBox7.Text.Trim() + " GROUP BY 商品名称,销售员名", ConStr);

                // OleDbDataAdapter oleDa = new OleDbDataAdapter("select 销售员名,SUM(销售数量) as 销售数量 from (select * from 销售记录表 where 销售时间 between #" + dateTimePicker1.Value.Date + "# and #" + dateTimePicker2.Value.Date + "#) A INNER JOIN 销售员表 B ON A.销售员ID =B.销售员ID where 商品ID=" + textBox7.Text.Trim() + " GROUP BY 销售员名", ConStr);
                DataSet ds = new DataSet();
                oleDa.Fill(ds);
                dataGridView3.DataSource = ds.Tables[0].DefaultView;

                //oleDa = new OleDbDataAdapter("select 商品名称 from 商品表 where 商品ID=" + textBox7.Text.Trim(), ConStr);
                //DataSet ds1 = new DataSet();
                //oleDa.Fill(ds1);
                //dataGridView4.DataSource = ds1.Tables[0].DefaultView;
            }
            else
            {
                MessageBox.Show("请填写_商品ID");
            }
        }

        //按单个销售人员统计 每个人卖出的各项商品 的销售情况
        private void button7_Click(object sender, EventArgs e)
        {
            if (textBox8.Text.Length != 0)
            {
                OleDbDataAdapter oleDa = new OleDbDataAdapter("select 销售员名,商品名称,SUM(销售数量) as 销售数量 from (Select * From (select * from 销售记录表 where 销售时间 between #" + dateTimePicker1.Value.Date + "# and #" + dateTimePicker2.Value.Date + "#) A Inner Join 商品表 B ON A.商品ID = B.商品ID)bb Inner Join 销售员表 C ON bb.销售员ID = C.销售员ID where A.销售员ID=" + textBox8.Text.Trim() + " GROUP BY 销售员名,商品名称", ConStr);

              //  OleDbDataAdapter oleDa = new OleDbDataAdapter("select 商品名称,SUM(销售数量) as 销售数量 from (select * from 销售记录表 where 销售时间 between #" + dateTimePicker4.Value.Date + "# and #" + dateTimePicker3.Value.Date + "#) A INNER JOIN 商品表 B ON A.商品ID =B.商品ID where 销售员id=" + textBox8.Text.Trim() + " GROUP BY 商品名称", ConStr);
                DataSet ds = new DataSet();
                oleDa.Fill(ds);
                dataGridView6.DataSource = ds.Tables[0].DefaultView;

                //oleDa = new OleDbDataAdapter("select 销售员名 from 销售员表 where 销售员ID=" + textBox8.Text.Trim(), ConStr);
                //DataSet ds1 = new DataSet();
                //oleDa.Fill(ds1);
                //dataGridView5.DataSource = ds1.Tables[0].DefaultView;
            }
            else
            {
                MessageBox.Show("请填写_销售人员ID");
            }
        }

        //一览表  交叉表
        private void button8_Click(object sender, EventArgs e)
        {
           // strSql = "Transform sum(C) as 数量 Select B From (Select [商品表].[商品名称] as A,[销售员表].[销售员名] as B,[销售记录表].[销售数量] as C,[销售记录表].[销售时间] as D From [销售记录表],[商品表],[销售员表] Where [销售记录表].[商品ID] = [商品表].[商品ID] and [销售记录表].[销售员ID] = [销售员表].[销售员ID]) Group by (B) pivot A";

            OleDbDataAdapter oleDa = new OleDbDataAdapter("Transform sum(C) as 数量 Select 销售员 From (Select [商品表].[商品名称] as A,[销售员表].[销售员名] as 销售员,[销售记录表].[销售数量] as C,[销售记录表].[销售时间] as D From (select * from 销售记录表 where 销售时间 between #" + dateTimePicker1.Value.Date + "# and #" + dateTimePicker2.Value.Date + "#),[商品表],[销售员表] Where [销售记录表].[商品ID] = [商品表].[商品ID] and [销售记录表].[销售员ID] = [销售员表].[销售员ID]) Group by (销售员) pivot A", ConStr);
            DataSet ds = new DataSet();
            oleDa.Fill(ds);
            dataGridView7.DataSource = ds.Tables[0].DefaultView;
        }


        #region (TextBox必须为数字,打印,导出到Excel)

        //必须为数字 该TextBox
        private void textBox3_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((e.KeyChar != 8 && !char.IsDigit(e.KeyChar)) && e.KeyChar != 13)
            {
                MessageBox.Show("只能输入数字", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                e.Handled = true;
            }
        }
        //必须为数字 该TextBox
        private void textBox7_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((e.KeyChar != 8 && !char.IsDigit(e.KeyChar)) && e.KeyChar != 13)
            {
                MessageBox.Show("只能输入数字", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                e.Handled = true;
            }
        }
        //必须为数字 该TextBox
        private void textBox8_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((e.KeyChar != 8 && !char.IsDigit(e.KeyChar)) && e.KeyChar != 13)
            {
                MessageBox.Show("只能输入数字", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                e.Handled = true;
            }
        }

        //打印一览
        private void button9_Click(object sender, EventArgs e)
        {
            Lucker.DataPrinter.DataPrintSet dps = new Lucker.DataPrinter.DataPrintSet("打印者123", dataGridView7);
            dps.ShowDialog();
        }

        //打印 输入商品ID 统计 销售人员
        private void button11_Click(object sender, EventArgs e)
        {
            Lucker.DataPrinter.DataPrintSet dps = new Lucker.DataPrinter.DataPrintSet("打印者123", dataGridView3);
            dps.ShowDialog();
        }

        //打印 输入销售人员 统计 商品
        private void button10_Click(object sender, EventArgs e)
        {
            Lucker.DataPrinter.DataPrintSet dps = new Lucker.DataPrinter.DataPrintSet("打印者123", dataGridView6);
            dps.ShowDialog();
        }

        //导出Excel 输入商品ID 统计 销售人员
        private void button12_Click(object sender, EventArgs e)
        {
            ExportDataGridview(dataGridView3, true);
        }

        //导出Excel 输入销售人员 统计 商品
        private void button13_Click(object sender, EventArgs e)
        {
            ExportDataGridview(dataGridView6, true);
        }
        //导出Excel 一览表
        private void button14_Click(object sender, EventArgs e)
        {
            ExportDataGridview(dataGridView7, true);
        }

        #endregion

        //按日期显示销售数据表
        private void button15_Click(object sender, EventArgs e)
        {

            //select [销售员表].[销售员名],[商品表].[商品名称],[销售记录表].[销售数量],[销售记录表].[销售时间] from
            //(Select * From [销售记录表] Inner Join [商品表] ON [销售记录表].[商品ID] = [商品表].[商品ID])bb
            //Inner Join [销售员表] ON bb.[销售员ID] = [销售员表].[销售员ID]

            OleDbDataAdapter oleDa = new OleDbDataAdapter("select A.销售记录ID,C.销售员名,B.商品名称,A.销售数量,A.销售时间 from (Select * From (select * from 销售记录表 where 销售时间 between #" + dateTimePicker1.Value.Date + "# and #" + dateTimePicker2.Value.Date + "#) A Inner Join 商品表 B ON A.商品ID = B.商品ID)bb Inner Join 销售员表 C ON bb.销售员ID = C.销售员ID order by A.销售记录ID", ConStr);

         //   OleDbDataAdapter oleDa = new OleDbDataAdapter("select * from 销售记录表 where 销售时间 between #" + dateTimePicker1.Value.Date + "# and #" + dateTimePicker2.Value.Date + "#", ConStr);
            DataSet ds = new DataSet();
            oleDa.Fill(ds);
            dataGridView4.DataSource = ds.Tables[0].DefaultView;
        }

        private void dataGridView4_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            textBox3.Text = dataGridView4[e.ColumnIndex, e.RowIndex].Value.ToString();
            textBox3.Refresh();
        }
    }
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值