private void 价格汇总toolStripMenuItem13_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
string s = "select goods.sj as 售价,";
s += "sum(sale_mx.sl) as 数量,sum(sale_mx.je) as 金额 ";
s += "from sale_mx join(goods,sale_db) ";
s += "on(goods.tm=sale_mx.tm and sale_db.djh=sale_mx.djh) ";
s += string.Format("where date(sale_db.rq)>='{0}' ", sd.dateTimePicker1.Value.ToShortDateString());
s += string.Format("and date(sale_db.rq)<='{0}'", sd.dateTimePicker2.Value.ToShortDateString());
s += "group by goods.sj";
command.CommandText = s;
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count < 1)
return;
Form_MDIChild mdi = new Form_MDIChild();
mdi.MdiParent = this;
mdi.Text = "价格汇总";
mdi.toolStripStatusLabel1.Text = sd.dateTimePicker1.Value.ToShortDateString();
mdi.toolStripStatusLabel1.Text += "--";
mdi.toolStripStatusLabel1.Text += sd.dateTimePicker2.Value.ToShortDateString();
this.dataGridView1 = mdi.dataGridView;
this.dataGridView1.DataSource = dt;
string sumsl;
string sumje;
sumsl = dt.Compute("sum(数量)", null).ToString();
sumje = float.Parse(dt.Compute("sum(金额)", null).ToString()).ToString("N2");
mdi.toolStripStatusLabel1.Text += " 合计：【" + sumsl + "】件，【" + sumje + "】元";
this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2";
this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2";
SetColumnsWidth();
mdi.Show();
}
private void toolStripMenuItem15业绩汇总_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
string sql = "select sum(sale_mx.sl) as `数量`, ";
sql += "sum(sale_mx.je) as `金额`, ";
sql += "worker.xm as `收银员` from sale_mx ";
sql += "join sale_db using(djh) ";
sql += "join worker on (worker.bh=sale_mx.syy) ";
sql += "where date(sale_db.rq)>='" + sd.dateTimePicker1.Value.ToShortDateString() + "' ";
sql += "and date(sale_db.rq)<='" + sd.dateTimePicker2.Value.ToShortDateString() + "' ";
sql += "group by sale_mx.syy order by `金额`";
command.CommandText = sql;
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
Form_MDIChild mdi = new Form_MDIChild();
mdi.Text = "业绩汇总";
mdi.MdiParent = this;
mdi.dataGridView.DataSource = dt;
this.dataGridView1 = mdi.dataGridView;
this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2";
mdi.toolStripStatusLabel1.Text = "查询所有员工的业绩，从";
mdi.toolStripStatusLabel1.Text += sd.dateTimePicker1.Value.ToShortDateString() + "至";
mdi.toolStripStatusLabel1.Text += sd.dateTimePicker2.Value.ToShortDateString();
this.SetColumnsWidth();
mdi.Show();
}
/////出库汇总
private void toolStripMenuItem1_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
string s = string.Format("select ck.tm as 条码,goods.pm as 品名,sum(ck.sl) as 数量,goods.sj as 售价,goods.sj*sum(ck.sl) as 金额,ck.rq as 日期,ck.czy as 操作员 from ck join goods using(tm) where date(ck.rq)>='{0}' and date(ck.rq)<='{1}' group by ck.tm order by ck.rq asc",
sd.dateTimePicker1.Value.Date.ToShortDateString(),
sd.dateTimePicker2.Value.Date.ToShortDateString());
command.CommandText = s;
MySqlDataAdapter a = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
a.Fill(dt);
Form_MDIChild child = new Form_MDIChild();
child.MdiParent = this;
child.Text = sd.dateTimePicker1.Value.Date.ToShortDateString() + "--" + sd.dateTimePicker2.Value.Date.ToShortDateString() + "【出库汇总】";
child.items.AddRange(new string[] { "售价", "数量", "条码", "品名" });
child.dataGridView.DataSource = dt;
this.dataGridView1 = child.dataGridView;
this.dataGridView1.Columns["日期"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2";
this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2";
this.SetColumnsWidth();
int sumsl = 0;
float je = 0f;
if (dt.Rows.Count > 0)
{
sumsl = int.Parse(dt.Compute("sum(数量)", null).ToString());
je = float.Parse(dt.Compute("sum(金额)", null).ToString());
}
child.toolStripStatusLabel1.Text = "共【" + dt.Rows.Count + "】种商品，【" + sumsl + "】件商品，【" + je.ToString("N2") + "】元";
child.Show();
}
private void 选择日期_商品明细ToolStripMenuItem4_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
this.MX_Goods(sd.dateTimePicker1.Value.Date, sd.dateTimePicker2.Value.Date, "选择日期商品明细");
}
private void 选择日期_本日时段toolStripMenuItem7_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
this.TimeView(sd.dateTimePicker1.Value.Date, sd.dateTimePicker2.Value.Date);
}
private void 选择日期ToolStripMenuItem_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
this.FengLeiHuiZong(sd.dateTimePicker1.Value.Date, sd.dateTimePicker2.Value.Date);
}
private void 赠品出库历史toolStripMenuItem2_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
string s = string.Format("select zp_ck.tm as 条码," +
"zp_goods.pm as 品名," +
"zp_ck.sl as 数量," +
"zp_goods.sj as 售价," +
"zp_ck.sl*zp_goods.sj as 金额," +
"zp_ck.rq as 日期," +
"zp_ck.czy as 操作员 " +
"from zp_ck join zp_goods using(tm) where date(zp_ck.rq)>='{0}' and date(zp_ck.rq)<='{1}' order by zp_ck.rq asc",
sd.dateTimePicker1.Value.Date.ToShortDateString(),
sd.dateTimePicker2.Value.Date.ToShortDateString());
command.CommandText = s;
MySqlDataAdapter a = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
a.Fill(dt);
Form_MDIChild child = new Form_MDIChild();
child.MdiParent = this;
child.Text = sd.dateTimePicker1.Value.Date.ToShortDateString() + "--" + sd.dateTimePicker2.Value.Date.ToShortDateString() + "赠品出库明细";
child.items.AddRange(new string[] { "条码", "品名" });
child.dataGridView.DataSource = dt;
this.dataGridView1 = child.dataGridView;
this.SetColumnsWidth();
child.toolStripStatusLabel1.Text = "共【" + dt.Rows.Count + "】条记录";
child.Show();
}
private void 导出销售数据toolStripMenuItem12_Click(object sender, EventArgs e)
{
if (this.worker.qx == "低")
return;
SaveFileDialog sd = new SaveFileDialog();
sd.DefaultExt = "xls";
sd.AddExtension = true;
sd.Title = "指定要要导出的文件名及存放位置";
sd.Filter = "电子表格文件(*.xls)|*.xls";
if (sd.ShowDialog() != DialogResult.OK)
return;
///////////////
Form_SelectDate sdf = new Form_SelectDate();
if (sdf.ShowDialog(this) != DialogResult.OK)
return;
string _start = sdf.dateTimePicker1.Value.Date.ToShortDateString();
string _end = sdf.dateTimePicker2.Value.Date.ToShortDateString();
string s = "select sale_mx.tm,sum(sale_mx.sl),goods.sj,goods.pm,goods.jj ";
s += "from sale_mx join sale_db on(sale_db.djh=sale_mx.djh) join goods on(goods.tm=sale_mx.tm)";
s += " where date(sale_db.rq)>='" + _start;
s += "' and date(sale_db.rq)<='" + _end;
s += "' group by sale_mx.tm";
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("导出的销售数据");
command.CommandText = s;
MySqlDataReader reader = command.ExecuteReader();
int i = 0;
ICellStyle style = book.CreateCellStyle();
IDataFormat format = book.CreateDataFormat();
style.DataFormat = format.GetFormat("0.00");
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
ICellStyle style_all = book.CreateCellStyle();
style_all.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
while (reader.Read())
{
IRow row = sheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.CellStyle = style_all;
cell.SetCellValue(reader.GetString(0));
cell = row.CreateCell(1);
cell.CellStyle = style_all;
cell.SetCellValue(reader.GetInt32(1));
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(reader.GetFloat(2));
cell = row.CreateCell(3);
cell.CellStyle = style_all;
cell.SetCellValue(reader.GetString(3));
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(reader.GetFloat(4));
i++;
}
reader.Close();
sheet.SetColumnWidth(0, 18 * 256);
sheet.SetColumnWidth(1, 8 * 256);
sheet.SetColumnWidth(2, 10 * 256);
sheet.SetColumnWidth(3, 26 * 256);
sheet.SetColumnWidth(4, 10 * 256);
FileStream fs = new FileStream(sd.FileName, FileMode.Create);
book.Write(fs);
fs.Close();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void 大类汇总ToolStripMenuItem_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
/*
string s = "select fl.dnm as 大类,fl.pm as 品名,";
s += "sum(sale_mx.sl) as 数量,sum(sale_mx.je) as 金额 ";
s += "from sale_mx join(sale_db,fl) ";
s += "on(sale_db.djh=sale_mx.djh and ";
s += "fl.dnm=substring(sale_mx.tm,1,2)) ";
s += string.Format("where date(sale_db.rq)>='{0}' ", sd.dateTimePicker1.Value.ToShortDateString());
s += string.Format("and date(sale_db.rq)<='{0}'", sd.dateTimePicker2.Value.ToShortDateString());
s += "group by fl.dnm";
**/
string s = "select fl.dnm as 大类,fl.pm as 品名,";
s += "sum(sale_mx.sl) as 数量,sum(sale_mx.je) as 金额 ";
s += "from sale_mx join(sale_db) using(djh) ";
s += "join fl on( ";
s += "fl.dnm=substring(sale_mx.tm,1,2)) where ";
s += string.Format("date(sale_db.rq)>='{0}' ", sd.dateTimePicker1.Value.ToShortDateString());
s += string.Format("and date(sale_db.rq)<='{0}'", sd.dateTimePicker2.Value.ToShortDateString());
s += "group by fl.dnm";
command.CommandText = s;
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count < 1)
return;
Form_MDIChild mdi = new Form_MDIChild();
mdi.MdiParent = this;
mdi.Text = "大类汇总";
mdi.toolStripStatusLabel1.Text = "时间范围：";
mdi.toolStripStatusLabel1.Text += sd.dateTimePicker1.Value.ToShortDateString();
mdi.toolStripStatusLabel1.Text += "--";
mdi.toolStripStatusLabel1.Text += sd.dateTimePicker2.Value.ToShortDateString();
this.dataGridView1 = mdi.dataGridView;
this.dataGridView1.DataSource = dt;
this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2";
string sumsl, sumje;
sumsl = dt.Compute("sum(数量)", null).ToString();
sumje = float.Parse(dt.Compute("sum(金额)", null).ToString()).ToString("N2");
mdi.toolStripStatusLabel1.Text += " 合计：【" + sumsl + "】件，【" + sumje + "】元";
SetColumnsWidth();
mdi.Show();
}
private void 出库明细ToolStripMenuItem1_Click(object sender, EventArgs e)
{
Form_SelectDate sd = new Form_SelectDate();
if (sd.ShowDialog(this) != DialogResult.OK)
return;
string s = "select ck.tm as 条码,goods.pm as 品名,ck.sl as 数量,goods.sj as 售价,goods.sj*ck.sl as 金额,ck.bz as 备注,ck.rq as 日期,ck.czy as 操作员 from ck left join goods using(tm) ";
s += string.Format("where date(ck.rq)>='{0}' and date(ck.rq)<='{1}'",
sd.dateTimePicker1.Value.Date.ToShortDateString(),
sd.dateTimePicker2.Value.Date.ToShortDateString());
command.CommandText = s;
MySqlDataAdapter a = new MySqlDataAdapter(command);
DataTable dt = new DataTable();
a.Fill(dt);
Form_MDIChild child = new Form_MDIChild();
child.MdiParent = this;
child.Text = "出库明细";
child.items.AddRange(new string[] { "条码", "品名", "售价" });
child.dataGridView.DataSource = dt;
this.dataGridView1 = child.dataGridView;
this.dataGridView1.Columns["日期"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2";
this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2";
this.SetColumnsWidth();
string text = sd.dateTimePicker1.Value.Date.ToShortDateString() + "--" + sd.dateTimePicker2.Value.Date.ToShortDateString();
text += "，共【" + dt.Rows.Count + "】条记录，【" + dt.Compute("sum(数量)", null);
float sum_je = 0.0F;
if (dt.Rows.Count > 0)
sum_je = float.Parse(dt.Compute("sum(金额)", null).ToString());
text += "】件商品，【" + sum_je.ToString("N2") + "】元";
child.toolStripStatusLabel1.Text = text;
child.Show();
}