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();
}
}
}