sell.cs

进存销系统..适合书店的进销存..功能可以满足一般的需求.很简单

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace BookStore.Forms
{
    public partial class Sell : Form
    {
        private DataTable newTable;

        private DateTime gridMouseDownTime;

        public static string[] inputDataGridArray = new string[] { null, null, null };

        private int i = 0;

        public Sell()
        {
            InitializeComponent();
        }

        private void Sell_Load(object sender, EventArgs e)
        {
            SetEmployeesList();
            SetCustomersList();
            SetUsersList();
            DataGridStateControl();
        }

        //设置newTable的格式
        private void DataGridStateControl()
        {
            newTable = new DataTable();
            newTable.Columns.Add("货号(双击)", typeof(decimal));
            newTable.Columns.Add("名称", typeof(string));
            newTable.Columns.Add("数量", typeof(decimal));
            newTable.Columns.Add("单价", typeof(decimal));
            newTable.Columns.Add("金额", typeof(decimal), "数量*单价");
            newTable.Columns.Add("税率", typeof(decimal));
            newTable.Columns.Add("税后金额", typeof(decimal), "金额/1.17");
            newTable.Columns.Add("税额", typeof(decimal), "金额-税后金额");
            this.dg_Sell.DataSource = newTable;
            newTable.Rows.Add(newTable.NewRow());

            DataGridTableStyle ts = new DataGridTableStyle();
            DataGridTextBoxColumn aColumnText;
            ts.AllowSorting = false;
            ts.AlternatingBackColor = Color.LightCyan;
            ts.MappingName = newTable.TableName;
            int numCols = newTable.Columns.Count;
            for (int i = 0; i < numCols; i++)
            {
                aColumnText = new DataGridTextBoxColumn();
                if (i != 0 && i != 2 && i != 3)
                {
                    aColumnText.ReadOnly = true;
                }
                if (i == 0)//当单击鼠标时相应
                {
                    aColumnText.TextBox.MouseDown += new MouseEventHandler(TextBoxMouseDownHandler);
                }
                aColumnText.MappingName = newTable.Columns[i].ColumnName;
                aColumnText.HeaderText = newTable.Columns[i].ColumnName;
                aColumnText.NullText = "";
                ts.GridColumnStyles.Add(aColumnText);
            }
            this.dg_Sell.TableStyles.Add(ts);
        }

        //—— 处理DataGrid中的双击事件
        private void TextBoxMouseDownHandler(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left && e.Clicks == 2 || DateTime.Now < gridMouseDownTime.AddMilliseconds(SystemInformation.DoubleClickTime))
            {
                if (dg_Sell.CurrentCell.ColumnNumber == 0)
                {
                    i = this.dg_Sell.CurrentCell.RowNumber;
                    ProductsInfo newfrm = new ProductsInfo(true);
                    newfrm.DataGridReadOnly();
                    newfrm.ShowDialog();
                    if (inputDataGridArray[0] != null)
                    {
                        SetProduct();
                    }
                    SendKeys.Send("{Tab}");
                    SendKeys.Send("{Tab}");
                }
            }
        }

        //——处理手工输入产品信息
        private void SelectProInfo()
        {
            int CurrentRowNum = this.dg_Sell.CurrentCell.RowNumber;
            //判断修改的是不商品编号信息，是的话去连接数据库，查询商品信息
            if (this.dg_Sell.CurrentCell.ColumnNumber == 0)
            {
                if (this.dg_Sell[CurrentRowNum, 0].ToString() != "")
                {
                    int strSeachWord = Convert.ToInt32(this.dg_Sell[CurrentRowNum, 0].ToString().Trim());
                    string select = "select ProductName,UnitPrice from Products where ProductID='" + strSeachWord + "'";
                    DataTable tempTable = DataAccess.Narnu.GetDataTableBySql(select);
                    inputDataGridArray[0] = this.dg_Sell[CurrentRowNum, 0].ToString();
                    inputDataGridArray[1] = tempTable.Rows[0][0].ToString();
                    inputDataGridArray[2] = tempTable.Rows[0][1].ToString();
                    SetProduct();
                }
                else
                {
                    inputDataGridArray[0] = "0";
                    inputDataGridArray[1] = "";
                    inputDataGridArray[2] = "0";
                    SetProduct();
                }
            }
        }

        private void SetProduct()
        {
            int CurrentRowNum = i;
            this.dg_Sell[CurrentRowNum, 0] = inputDataGridArray[0];
            this.dg_Sell[CurrentRowNum, 1] = inputDataGridArray[1];
            this.dg_Sell[CurrentRowNum, 3] = inputDataGridArray[2];
            this.dg_Sell[CurrentRowNum, 5] = 17;
        }

        private void SetEmployeesList()
        {
            string select = "select EmployeeID,Name from Employees";
            DataTable DT = DataAccess.Narnu.GetDataTableBySql(select);
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                this.cb_EmployeeID.Items.Add(DT.Rows[i][0].ToString() + ":" + DT.Rows[i][1].ToString());
            }
        }

        private void SetCustomersList()
        {
            string select = "select CustomerID,Name from Customers";
            DataTable DT = DataAccess.Narnu.GetDataTableBySql(select);
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                this.cb_CustomerID.Items.Add(DT.Rows[i][0].ToString() + ":" + DT.Rows[i][1].ToString());
            }
        }

        private void SetUsersList()
        {
            string select = "select UserID,UserName from Users";
            DataTable DT = DataAccess.Narnu.GetDataTableBySql(select);
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                this.cb_User.Items.Add(DT.Rows[i][0].ToString() + ":" + DT.Rows[i][1].ToString());
            }
        }

        private void Calculate()
        {
           
            //为底部三个文本框计算总金额
            decimal intTotalCount = 0;
            decimal intNoTax = 0;
            decimal intTax = 0;

            //首先判断列是否为空
            object obj = newTable.Compute("SUM (金额)", "");
            if (obj.GetType().ToString() != "System.DBNull")
            {
                intTotalCount = (decimal)this.newTable.Compute("SUM (金额)", "");
                intNoTax = (decimal)this.newTable.Compute("SUM (税后金额)", "");
                intTax = (decimal)this.newTable.Compute("SUM (税额)", "");
            }


            this.tb_TotalPrice.Text = intTotalCount.ToString("N");
            this.tb_NoTax.Text = intNoTax.ToString("N");
            this.tb_Tax.Text = intTax.ToString("N");
        }


        //截取冒号前的数字
        private int GetNumber(string str)
        {
            int index;
            string temp="1001";
            index = str.IndexOf(":");
            if (index != -1)
            {
                temp = str.Substring(0, index);
            }
            return (int.Parse(temp));
        }

        private void cb_CustomerID_SelectedIndexChanged(object sender, EventArgs e)
        {
            string str="";
            int CusID = GetNumber(this.cb_CustomerID.Text.ToString());
            string select = "select Name from Customers where CustomerID=" + CusID;
            str = DataAccess.Narnu.GetObjectBySqlString(select).ToString();
            this.tb_CustomerName.Text = "客户名称：" + str;
        }

        private void dg_Sell_DoubleClick(object sender, EventArgs e)
        {
            i = this.dg_Sell.CurrentCell.RowNumber;
            ProductsInfo newfrm = new ProductsInfo(true);
            newfrm.DataGridReadOnly();
            newfrm.ShowDialog();
            SetProduct();
            SendKeys.Send("{Tab}");
            SendKeys.Send("{Tab}");
        }

        private void btn_Calculate_Click(object sender, EventArgs e)
        {
            Calculate();
        }

        private void btn_Save_Click(object sender, EventArgs e)
        {
            Calculate();
            string SelectMaxSellID = "select max(SellID) from Sells";
            //插入表头
            int SellID = 0;
            object strMaxID = DataAccess.Narnu.GetObjectBySqlString(SelectMaxSellID);

            if (strMaxID.ToString() == "")
            {
                SellID = 1;
            }
            else
            {
                SellID = int.Parse(strMaxID.ToString());
                SellID++;
            }
            int intEmployeeID = this.GetNumber(this.cb_EmployeeID.Text.ToString());
            int intCustomerID = this.GetNumber(this.cb_CustomerID.Text.ToString());
            string SellTime = DateTime.Now.ToString();
            decimal decTotalPrice = decimal.Parse(this.tb_TotalPrice.Text.Trim());
            decimal decTotalTax = decimal.Parse(this.tb_Tax.Text.Trim());
            decimal decNoTax = decimal.Parse(this.tb_NoTax.Text.Trim());
            string ZDR = "红猪";
            string SetValue = "(" + SellID + "," + intCustomerID + "," + intEmployeeID + "," + decTotalPrice + ",'" + ZDR + "','" +
                            SellTime + "'," + decTotalTax + "," + decNoTax + ")";
            string insertSell = "insert into Sells values " + SetValue;
            try
            {
                DataAccess.Narnu.DoSqlString(insertSell);
                
            }
            catch
            {
                
                MessageBox.Show("表头插入出现错误！");
                return;
            }

            //插入表身
            SelectMaxSellID = "Select Max(DetailID) from SellDetails";
            int DetailSellID = 0;
            object strMaxDetailID = DataAccess.Narnu.GetObjectBySqlString(SelectMaxSellID);
            if (strMaxDetailID.ToString() == "")
            {
                DetailSellID = 1;
            }
            else
            {

                DetailSellID = int.Parse(strMaxDetailID.ToString());
                DetailSellID++;
            }
            for (int i = 0; i < newTable.Rows.Count; i++)
            {
                int intProductID = int.Parse(this.dg_Sell[i, 0].ToString().Trim());
                decimal decUnitPrice = decimal.Parse(this.dg_Sell[i, 4].ToString().Trim());
                int intUnits = int.Parse(this.dg_Sell[i, 2].ToString().Trim());
                decimal decTotalDetailPrice = decimal.Parse(this.dg_Sell[i, 4].ToString().Trim());
                string strProductName = this.dg_Sell[i, 1].ToString().Trim();
                string SetDetailValue = "(" + DetailSellID + "," + SellID + "," + intProductID + "," + decUnitPrice + "," +
                                        intUnits + "," + decTotalDetailPrice +",'"+strProductName+ "')";
                string insertDetail = "insert into SellDetails values " + SetDetailValue;
                try
                {
                    DataAccess.Narnu.DoSqlString(insertDetail);
                    
                }
                catch
                {
                    
                    MessageBox.Show("表身插入出现错误！");
                    return;
                }
                DetailSellID++;
            }

            //在商品表中更新数量
            for (int i = 0; i < newTable.Rows.Count; i++)
            {
                int intProductID = int.Parse(this.dg_Sell[i, 0].ToString().Trim());
                string select = "select Units from Products where ProductID=" + intProductID;
                int intUnits = int.Parse(this.dg_Sell[i, 2].ToString().Trim());
                intUnits -= int.Parse(DataAccess.Narnu.GetObjectBySqlString(select).ToString());
                string update = "update products set Units=" + intUnits + "where ProductID=" + intProductID;
                try
                {
                    DataAccess.Narnu.DoSqlString(update);
                    
                }
                catch
                {
                    MessageBox.Show("更新商品数量时出现错误！");
                    return;
                }
            }
            MessageBox.Show("销售单保存成功！");
            
        }
    }
}