Example #1
0
 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();
 }
Example #2
0
 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();
 }
Example #3
0
        /////出库汇总
        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();
        }
Example #4
0
        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, "选择日期商品明细");
        }
Example #5
0
 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);
 }
Example #6
0
        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);
        }
Example #7
0
        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();
        }
Example #8
0
        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);
        }
Example #9
0
        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();
        }
Example #10
0
        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();
        }