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