private void TimeView(DateTime start, DateTime end) { string startDate = start.ToShortDateString() + " "; string endDate = end.ToShortDateString() + " "; DataTable dt = new DataTable(); dt.Columns.Add("时间段"); dt.Columns.Add("来客数"); dt.Columns.Add("营业额"); dt.Columns.Add("客单价"); int lks;//来客数 float yye;//营业客 float kdj;//客单价 int total_lks = 0;//合计总来客数 float total_yye = 0;//合计总营业客 string startTime; string endTime; for (int i = 6; i < 22; i += 2) { startTime = i.ToString("00") + ":00:00"; endTime = (i + 1).ToString("00") + ":59:59"; command.CommandText = "select ifnull(count(*),0) from sale_db where date(rq)>='" + startDate; command.CommandText += "' and date(rq)<='" + endDate; command.CommandText += "' and time(rq)>='" + startTime; command.CommandText += "' and time(rq)<='" + endTime + "'"; lks = int.Parse(command.ExecuteScalar().ToString()); command.CommandText = "select ifnull(sum(je),0) from sale_db where date(rq)>='" + startDate; command.CommandText += "' and date(rq)<='" + endDate; command.CommandText += "' and time(rq)>='" + startTime; command.CommandText += "' and time(rq)<='" + endTime + "'"; yye = float.Parse(command.ExecuteScalar().ToString()); if (lks > 0) kdj = yye / lks; else kdj = 0; total_lks += lks; total_yye += yye; DataRow dr = dt.NewRow(); dr[0] = i.ToString("00") + ":00:00 —— " + (i + 2).ToString("00") + ":00:00"; dr[1] = lks; dr[2] = yye.ToString("N2"); dr[3] = kdj.ToString("N2"); dt.Rows.Add(dr); } Form_MDIChild f = new Form_MDIChild(); f.Text = "本日时段"; f.MdiParent = this; this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; this.dataGridView1.Columns["营业额"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["客单价"].DefaultCellStyle.Format = "N2"; if (total_lks > 0) kdj = total_yye / total_lks; else kdj = 0; f.toolStripStatusLabel1.Text = "查询日期【" + startDate + "—" + endDate + "】"; f.toolStripStatusLabel1.Text += ",来客数【" + total_lks.ToString(); f.toolStripStatusLabel1.Text += "】,营业额【" + total_yye.ToString("N2"); f.toolStripStatusLabel1.Text += "】,客单价【" + kdj.ToString("N2") + "】"; this.SetColumnsWidth(); f.Show(); }
private void MX_DB(DateTime start, DateTime end, string FormText) { string _start, _end; _start = start.ToShortDateString(); _end = end.ToShortDateString(); string s = "select djh as 单据号,"; s += "sl as 数量,"; s += "je as 金额,"; s += "ss as 实收,"; s += "zl as 找零,"; s += "sk as 刷卡,"; s += "hy as 会员号,"; s += "syy as 收银员,"; s += "rq as 日期 "; s += "from sale_db "; s += "where (date(sale_db.rq)>='" + _start + "' "; s += "and date(sale_db.rq)<='" + _end + "')"; command.CommandText = s; MySqlDataAdapter a = new MySqlDataAdapter(command); DataTable dt = new DataTable("销售"); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); f.MdiParent = this; f.items.AddRange(new string[] { "单据号", "会员号" }); this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; f.Text = FormText; string sl = "0"; float je = 0; if (dt.Rows.Count > 0) { sl = dt.Compute("sum(数量)", null).ToString(); je = float.Parse(dt.Compute("sum(金额)", null).ToString()); } this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["实收"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["找零"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["日期"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; f.toolStripStatusLabel1.Text = "查询日期【" + _start + "—" + _end + "】"; f.toolStripStatusLabel1.Text += ",数量共【" + sl + "】"; f.toolStripStatusLabel1.Text += ",金额【" + je.ToString("N2") + "】"; this.SetColumnsWidth(); f.Show(); }
private void MX_Goods(DateTime start, DateTime end, string FormText) { string _start, _end; _start = start.ToShortDateString(); _end = end.ToShortDateString(); string s = "select sale_mx.djh as 单据号,"; s += "sale_mx.tm as 条码,"; s += "goods.pm as 品名,"; s += "sale_mx.sl as 数量,"; s += "sale_mx.sj as 售价,"; s += "sale_mx.zqfs as 折扣方式,"; s += "sale_mx.zq as 折扣,"; s += "sale_mx.je as 金额,"; s += "sale_db.hy as 会员, "; s += "sale_mx.syy as 收银员, "; s += "sale_db.rq as 时间 "; s += "from sale_mx left join(sale_db,goods) "; s += "on(goods.tm=sale_mx.tm and sale_db.djh=sale_mx.djh) "; s += "where (date(sale_db.rq)>='" + _start + "' "; s += "and date(sale_db.rq)<='" + _end + "')"; command.CommandText = s; MySqlDataAdapter a = new MySqlDataAdapter(command); DataTable dt = new DataTable("销售"); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); f.MdiParent = this; f.Text = FormText; f.items.AddRange(new string[] { "单据号", "条码", "会员", "收银员" }); this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; string sl = "0"; float je = 0; if (dt.Rows.Count > 0) { sl = dt.Compute("sum(数量)", null).ToString(); je = float.Parse(dt.Compute("sum(金额)", null).ToString()); } this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["折扣"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["时间"].DefaultCellStyle.Format = "yy-MM-dd HH:mm:ss"; f.toolStripStatusLabel1.Text = "查询日期【" + _start + "—" + _end + "】"; f.toolStripStatusLabel1.Text += ",数量共【" + sl + "】件"; f.toolStripStatusLabel1.Text += ",金额共【" + je.ToString("N2") + "】元"; this.SetColumnsWidth(); f.Show(); }
private void ViewGHS(string ghsID) { string s; if (this.worker.qx == "低") s = "select tm as 条码,pm as 品名,sj as 售价,kc as 库存,ghs as 供货商 from goods where ghs='" + ghsID + "'"; else s = "select tm as 条码,pm as 品名,jj as 进价,sj as 售价,kc as 库存,ghs as 供货商 from goods where ghs='" + ghsID + "'"; command.CommandText = s; MySqlDataAdapter a = new MySqlDataAdapter(command); DataTable dt = new DataTable(); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); if (ghsID == "1001") f.Text = "专卖库存"; else f.Text = "自营库存"; f.MdiParent = this; f.items.AddRange(new string[] { "条码", "品名" }); this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; this.SetColumnsWidth(); s = "select ifnull(sum(kc),0) from goods where ghs='" + ghsID + "'"; command.CommandText = s; int kc = int.Parse(command.ExecuteScalar().ToString()); s = "select ifnull(sum(jj*kc),0.0) from goods where ghs='" + ghsID + "'"; command.CommandText = s; float jjkc = float.Parse(command.ExecuteScalar().ToString()); s = "select ifnull(sum(sj*kc),0.0) from goods where ghs='" + ghsID + "'"; command.CommandText = s; float sjkc = float.Parse(command.ExecuteScalar().ToString()); s = "select ifnull(sum(sj*zq*kc),0.0) from goods where ghs='" + ghsID + "'"; command.CommandText = s; float zqkc = float.Parse(command.ExecuteScalar().ToString()); s = "select ifnull(sum(sj*hyzq*kc),0) from goods where ghs='" + ghsID + "'"; command.CommandText = s; float hyzqkc = float.Parse(command.ExecuteScalar().ToString()); f.toolStripStatusLabel1.Text = "当前共【" + dt.Rows.Count + "】条记录"; f.toolStripStatusLabel1.Text += ",共【" + kc.ToString() + "】件商品"; if (this.worker.qx != "低") { f.toolStripStatusLabel1.Text += ",总成本【" + jjkc.ToString("N2"); f.toolStripStatusLabel1.Text += "】,按售价总额【" + sjkc.ToString("N2"); f.toolStripStatusLabel1.Text += "】,折扣后总额【" + zqkc.ToString("N2"); f.toolStripStatusLabel1.Text += "】,会员折扣后总额【" + hyzqkc.ToString("N2") + "】"; } f.Show(); }
private void 入库汇总ToolStripMenuItem_Click_工具(object sender, EventArgs e) { // if (this.worker.qx == "低") return; var today = DateTime.Now; string s = string.Format("select rk_temp.tm as 条码,goods.pm as 品名,sum(rk_temp.sl) as 数量,goods.sj as 售价,goods.sj*sum(rk_temp.sl) as 金额,rk_temp.rq as 日期,rk_temp.czy as 操作员 from rk_temp join goods using(tm) where date(rk_temp.rq)>='{0}' and date(rk_temp.rq)<='{1}' group by rk_temp.tm order by rk_temp.rq asc", today.ToShortDateString(), today.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 = today.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") + "】元"; this.toolStripMenuItem_真实入库.Enabled = true; child.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 toolStripMenuItem_小类_Click(object sender, EventArgs e) { if (this.worker.qx == "低") return; string s = "select fl.dnm as 小类,fl.pm as 品名,"; s += "sum(goods.kc) as 数量,sum(goods.sj*goods.kc) as 金额 "; s += "from goods join(fl) "; s += "on(fl.dnm=substring(goods.tm,1,6)) "; s += "group by fl.dnm order by fl.dnm"; command.CommandText = s; MySqlDataAdapter adapter = new MySqlDataAdapter(command); DataTable dt = new DataTable(); adapter.Fill(dt); Form_MDIChild mdi = new Form_MDIChild(); mdi.MdiParent = this; mdi.Text = "库存中小汇总"; this.dataGridView1 = mdi.dataGridView; this.dataGridView1.DataSource = dt; this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; string sl = "0"; float je = 0f; if (mdi.dataGridView.Rows.Count > 0) { sl = dt.Compute("sum(数量)", null).ToString(); je = float.Parse(dt.Compute("sum(金额)", null).ToString()); } mdi.toolStripStatusLabel1.Text = string.Format("合计:【{0}】件,【{1}】元", sl, je.ToString("N2")); SetColumnsWidth(); mdi.Show(); }
public void ViewFL(int lb) { int length = lb * 2; string sql = string.Format("select bh as 编号,pm as 品名,dnm as 店内码 from fl where char_length(dnm)={0} order by dnm asc", length); command.CommandText = sql; DataTable dt = new DataTable(); MySqlDataAdapter da = new MySqlDataAdapter(command); da.Fill(dt); Form_MDIChild child = new Form_MDIChild(); switch (lb) { case 1: child.Text = "大类浏览"; break; case 2: child.Text = "中类浏览"; break; case 3: child.Text = "小类浏览"; break; default: break; } child.MdiParent = this; child.toolStripStatusLabel1.Text = "商品分类浏览"; child.dataGridView.DataSource = dt; this.dataGridView1 = child.dataGridView; this.SetColumnsWidth(); child.Show(); }
private void 按日统计_toolStripMenuItem4_Click(object sender, EventArgs e) { //if (this.worker.qx == "低") return; string start, end; end = DateTime.Today.ToShortDateString(); start = DateTime.Today.AddDays(-29).ToShortDateString(); string s = "select date(rq) as 日期, "; s += "ifnull(sum(sl),0) as 数量, "; s += "ifnull(sum(je),0) as 金额 "; s += "from sale_db where(date(rq)>='" + start + "' "; s += "and date(rq)<='" + end + "') group by date(rq) order by rq desc"; command.CommandText = s; DataTable dt = new DataTable(); MySqlDataAdapter a = new MySqlDataAdapter(command); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); f.MdiParent = this; f.Text = "按日统计"; this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; this.SetColumnsWidth(); f.toolStripStatusLabel1.Text = "统计起始日【" + start + "】,截止日【" + end + "】,共【30】天"; this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; f.Show(); }
private void 员工浏览toolStripMenuItem11_Click(object sender, EventArgs e) { string s = "select bh as 编号,xm as 姓名,dh as 手机,qx as 权限,rq as 创建时间 from worker order by rq"; command.CommandText = s; DataTable dt = new DataTable(); MySqlDataAdapter a = new MySqlDataAdapter(command); a.Fill(dt); Form_MDIChild child = new Form_MDIChild(); child.MdiParent = this; child.Text = "员工浏览"; child.items.AddRange(new string[] { "姓名", "手机" }); this.dataGridView1 = child.dataGridView; child.dataGridView.DataSource = dt; child.toolStripStatusLabel1.Text = "共【" + dt.Rows.Count.ToString(); child.toolStripStatusLabel1.Text += "】员工"; this.dataGridView1.Columns["创建时间"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; this.SetColumnsWidth(); child.Show(); }
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(); }
private void 全部ToolStripMenuItem_Click(object sender, EventArgs e) { //查看所有商品资料 string s; if (this.worker.qx == "低") s = "select tm as 条码,pm as 品名,sj as 售价,zq as 折扣,hyzq as 会员折扣,ghs as 供货商 from goods"; else s = "select tm as 条码,pm as 品名,jj as 进价,sj as 售价,zq as 折扣,hyzq as 会员折扣,ghs as 供货商 from goods"; command.CommandText = s; MySqlDataAdapter a = new MySqlDataAdapter(command); DataTable dt = new DataTable(); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); f.Text = "商品资料"; f.MdiParent = this; f.items.AddRange(new string[] { "售价", "品名", "条码" }); this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2"; if (this.dataGridView1.Columns["进价"] != null) this.dataGridView1.Columns["进价"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["折扣"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["会员折扣"].DefaultCellStyle.Format = "N2"; this.SetColumnsWidth(); f.toolStripStatusLabel1.Text = "当前共【" + dt.Rows.Count + "】条记录"; f.Show(); }
public void FengLeiHuiZong(DateTime start, DateTime end) { string _start = start.ToShortDateString(); string _end = end.ToShortDateString(); string s = "select sale_mx.tm as 条码,goods.pm as 品名,sum(sale_mx.sl) as 数量,sum(sale_mx.je) as 金额 "; s += "from sale_mx join goods on(goods.tm=sale_mx.tm) join sale_db on(sale_db.djh=sale_mx.djh)"; s += " where date(sale_db.rq)>='" + _start; s += "' and date(sale_db.rq)<='" + _end; s += "' group by sale_mx.tm"; command.CommandText = s; MySqlDataAdapter a = new MySqlDataAdapter(command); DataTable dt = new DataTable(); a.Fill(dt); if (dt.Rows.Count < 1) return; Form_MDIChild child = new Form_MDIChild(); child.MdiParent = this; child.Text = "条码汇总"; child.items.Add("条码"); this.dataGridView1 = child.dataGridView; child.dataGridView.DataSource = dt; child.toolStripStatusLabel1.Text = "条码汇总【" + _start + "—" + _end + "】"; string sumsl, sumje; sumsl = dt.Compute("sum(数量)", null).ToString(); sumje = float.Parse(dt.Compute("sum(金额)", null).ToString()).ToString("N2"); child.toolStripStatusLabel1.Text += " 合计:【" + sumsl + "】件,【" + sumje + "】元"; this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; this.SetColumnsWidth(); child.Show(); }
private void toolStripButton_本日入库明细_Click(object sender, EventArgs e) { var now = DateTime.Now.ToShortDateString(); // if (this.worker.qx == "低") return; string s = string.Format("select rk.tm as 条码,goods.pm as 品名,rk.sl as 数量,goods.sj as 售价,rk.sl*goods.sj as 金额,rk.rq as 日期,rk.czy as 操作员 from rk join goods using(tm) where date(rk.rq)>='{0}' and date(rk.rq)<='{1}' order by rk.rq asc", now, now); 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 = now + "--" + now + "【入库明细】"; 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 操作历史ToolStripMenuItem_Click(object sender, EventArgs e) { if (this.worker.qx == "低") return; string s = "select jfcz.bh as 会员号,people.xm as 会员姓名, jfcz.cz as 操作, "; s += "jfcz.czjf as 操作积分, jfcz.syjf as 剩余积分, jfcz.czyy as 备注, jfcz.rq as 操作日期, "; s += "jfcz.czy as 操作员 from jfcz left join(people) on(people.bh=jfcz.bh) order by jfcz.rq"; command.CommandText = s; DataTable dt = new DataTable(); MySqlDataAdapter a = new MySqlDataAdapter(command); a.Fill(dt); Form_MDIChild child = new Form_MDIChild(); child.MdiParent = this; child.Text = "积分操作历史"; child.items.AddRange(new string[] { "会员号", "会员姓名", "操作员" }); this.dataGridView1 = child.dataGridView; child.dataGridView.DataSource = dt; child.toolStripStatusLabel1.Text = "共【" + dt.Rows.Count.ToString(); child.toolStripStatusLabel1.Text += "】记录"; this.dataGridView1.Columns["操作日期"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; this.SetColumnsWidth(); child.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 激励报告ToolStripMenuItem_Click(object sender, EventArgs e) { Form_DatePicker form = new Form_DatePicker(); if (form.ShowDialog(this) != DialogResult.OK) return; string s = "select sale_mx.djh as 单据号,"; s += "goods.tm as 条码,"; s += "goods.pm as 品名,"; s += "sale_mx.sl as 数量,"; s += "sale_mx.je as 金额,"; s += "sale_db.rq as 时间,"; s += "sale_mx.syy as 收银员 "; s += "from sale_mx left join(sale_db,goods) "; s += "on(sale_mx.tm=goods.tm and sale_mx.djh=sale_db.djh) "; s += "where sale_mx.syy='" + form.worker.bh + "' "; s += "and date(sale_db.rq)>='" + form.start + "' "; s += "and date(sale_db.rq)<='" + form.end + "' order by sale_db.rq"; command.CommandText = s; DataTable dt = new DataTable(); MySqlDataAdapter a = new MySqlDataAdapter(command); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); f.Text = "员工业绩"; f.MdiParent = this; f.items.AddRange(new string[] { "条码", "单据号" }); this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["时间"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; int sl = 0; float je = 0; if (dt.Rows.Count > 0) { sl = int.Parse(dt.Compute("sum(数量)", null).ToString()); je = float.Parse(dt.Compute("sum(金额)", null).ToString()); } f.toolStripStatusLabel1.Text = "查询员工【" + form.worker.xm + "】的业绩:从" + form.start + "至" + form.end + ","; f.toolStripStatusLabel1.Text += "共售出【" + sl.ToString() + "】件商品,"; f.toolStripStatusLabel1.Text += "总金额【" + je.ToString("N2") + "】"; this.SetColumnsWidth(); f.Show(); }
private void toolStripMenuItem_价格分类_Click(object sender, EventArgs e) { string sql = "select sj as 售价,sum(kc) as 数量,sj*sum(kc) as 金额 from goods where kc!=0 group by sj order by sj asc"; command.CommandText = sql; 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[] { "售价" }); this.dataGridView1 = child.dataGridView; child.dataGridView.DataSource = dt; string sumsl; string sumje; sumsl = dt.Compute("sum(数量)", null).ToString(); sumje = float.Parse(dt.Compute("sum(金额)", null).ToString()).ToString("N2"); child.toolStripStatusLabel1.Text = " 合计:【" + sumsl + "】件,【" + sumje + "】元"; this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2"; this.dataGridView1.Columns["金额"].DefaultCellStyle.Format = "N2"; this.SetColumnsWidth(); child.Show(); }
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 ViewFenLenZM(DateTime start, DateTime end, bool zm) { string _start = start.ToShortDateString(); string _end = end.ToShortDateString(); string _zm; if (zm) _zm = "1001"; else _zm = "1002"; string s = "select sale_mx.djh as 单据号,sale_mx.tm as 条码, "; s += "goods.pm as 品名,sale_mx.sl as 数量,sale_mx.sj as 售价, "; s += "sale_mx.zqfs as 折扣方式,sale_mx.zq as 折扣,sale_mx.je as 金额 "; s += "from sale_mx left join(sale_db, goods) on(goods.tm=sale_mx.tm and sale_db.djh=sale_mx.djh) "; s += "where goods.ghs='" + _zm + "' and date(sale_db.rq)>='" + _start + "' "; s += "and date(sale_db.rq)<='" + _end + "'"; command.CommandText = s; MySqlDataAdapter a = new MySqlDataAdapter(command); DataTable dt = new DataTable("销售"); a.Fill(dt); Form_MDIChild f = new Form_MDIChild(); f.Text = "本日分类" + _zm.ToString(); f.MdiParent = this; this.dataGridView1 = f.dataGridView; this.dataGridView1.DataSource = dt; this.SetColumnsWidth(); f.toolStripStatusLabel1.Text = "【本日分类" + _zm + "】当前共【" + dt.Rows.Count + "】条记录"; s = "select ifnull(sum(sale_mx.sl),0) from sale_mx left join(sale_db,goods) on(sale_db.djh=sale_mx.djh and goods.tm=sale_mx.tm) where goods.ghs='" + _zm + "' and date(sale_db.rq)>='" + _start + "' and date(sale_db.rq)<='" + _end + "'"; command.CommandText = s; int hjsl = int.Parse(command.ExecuteScalar().ToString()); f.toolStripStatusLabel1.Text += ", 共【" + hjsl.ToString() + "】件商品"; f.Show(); }
private void 赠品库存浏览ToolStripMenuItem1_Click(object sender, EventArgs e) { string sql = "select tm as 条码,pm as 品名,jj as 进价,sj as 售价,kc as 库存 from zp_goods"; command.CommandText = sql; MySqlDataAdapter adapter = new MySqlDataAdapter(command); DataTable dt = new DataTable(); adapter.Fill(dt); Form_MDIChild child = new Form_MDIChild(); child.Text = "赠品库存浏览"; child.MdiParent = this; this.dataGridView1 = child.dataGridView; this.dataGridView1.DataSource = dt; child.items.AddRange(new string[] { "售价", "条码", "品名", "库存", "供货商" }); this.dataGridView1 = child.dataGridView; this.dataGridView1.DataSource = dt; this.dataGridView1.Columns["售价"].DefaultCellStyle.Format = "N2"; if (this.dataGridView1.Columns["进价"] != null) this.dataGridView1.Columns["进价"].DefaultCellStyle.Format = "N2"; sql = "select ifnull(sum(kc),0) from goods"; command.CommandText = sql; int kc = int.Parse(command.ExecuteScalar().ToString()); sql = "select ifnull(sum(jj*kc),0.0) from goods"; command.CommandText = sql; float jjkc = float.Parse(command.ExecuteScalar().ToString()); sql = "select ifnull(sum(sj*kc),0.0) from goods"; command.CommandText = sql; float sjkc = float.Parse(command.ExecuteScalar().ToString()); sql = "select ifnull(sum(sj*zq*kc),0.0) from goods"; command.CommandText = sql; float zqkc = float.Parse(command.ExecuteScalar().ToString()); sql = "select ifnull(sum(sj*hyzq*kc),0) from goods"; command.CommandText = sql; float hyzqkc = float.Parse(command.ExecuteScalar().ToString()); child.toolStripStatusLabel1.Text = "当前共【" + dt.Rows.Count + "】条记录"; child.toolStripStatusLabel1.Text += ",共【" + kc.ToString() + "】件商品"; if (this.worker.qx != "低") { child.toolStripStatusLabel1.Text += ",总成本【" + jjkc.ToString("N2"); child.toolStripStatusLabel1.Text += "】,按售价总额【" + sjkc.ToString("N2"); child.toolStripStatusLabel1.Text += "】,折扣后总额【" + zqkc.ToString("N2"); child.toolStripStatusLabel1.Text += "】,会员折扣后总额【" + hyzqkc.ToString("N2") + "】"; } this.SetColumnsWidth(); child.Show(); }
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 会员浏览ToolStripMenuItem_Click(object sender, EventArgs e) { if (this.worker.qx == "低") return; string s = "select bh as 编号, "; s += "xm as 姓名, "; s += "ljxf as `累计消费(元)`, "; s += "jf as 积分, "; s += "dh as 手机, "; s += "rmb as `充值(元)`, "; s += "rq as 创建日期, "; s += "czy as 操作员 "; s += "from people order by rq"; command.CommandText = s; DataTable dt = new DataTable(); MySqlDataAdapter a = new MySqlDataAdapter(command); a.Fill(dt); Form_MDIChild child = new Form_MDIChild(); child.MdiParent = this; child.Text = "会员浏览"; child.items.AddRange(new string[] { "编号", "姓名", "手机" }); this.dataGridView1 = child.dataGridView; child.dataGridView.DataSource = dt; child.toolStripStatusLabel1.Text = "共【" + dt.Rows.Count.ToString(); child.toolStripStatusLabel1.Text += "】会员"; this.SetColumnsWidth(); child.Show(); }