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