Example #1
0
        private void HandleEmpEdit()
        {
            MySqlConnection conn = null;

            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                #region
                string pwd = string.Empty;
                if (checkBoxEmpPwd.Checked == true)
                {
                    pwd = textBoxEmpPwd.Text;
                }
                else
                {
                    pwd = listViewEmpInfo.SelectedItems[0].SubItems[3].Text;
                }
                pwd             = PswdHelper.EncryptString(pwd); // 加密
                cmd.CommandText = "update employee set password=?pwd, acl= ?acl where id=?id;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("?pwd", pwd);
                string acl = GetEmpAcl();
                cmd.Parameters.AddWithValue("?acl", acl);
                string id = listViewEmpInfo.SelectedItems[0].SubItems[0].Text;
                cmd.Parameters.AddWithValue("?id", id);
                conn.Open();
                cmd.ExecuteNonQuery();

                // 更新列表
                ResetEmp();
                RefreshEmpList();
                #region 记录操作日志
                StringBuilder sb = new StringBuilder(512);
                sb.AppendFormat("U employee {0}=[{1}], ", "name", PswdHelper.EncryptString(textBoxEmpName.Text));
                sb.AppendFormat("{0}=[{1}], ", "pwd", PswdHelper.EncryptString(textBoxEmpPwd.Text));
                sb.AppendFormat("{0}=[{1}], ", "acl", acl);
                sb.AppendFormat("{0}=[{1}], ", "id", id);
                WriteLog(sb.ToString());
                #endregion


                #endregion
            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser(ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #2
0
        private void HandleEmpAdd()
        {
            MySqlConnection conn = null;

            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                #region
                // 不检查是否已经有相同的姓名
                //cmd.CommandText = "select id, name, acl from employee where name=?name;";
                //cmd.Parameters.Clear();
                //cmd.Parameters.AddWithValue("?name", textBoxEmpName.text);
                //conn.Open();
                //MySqlDataReader dr = cmd.ExecuteReader();
                //if(dr.Read())
                //{
                //    dr.Close();
                //    conn.Close();
                //    NotifyHelper.NotifyUser("该姓名已存在");
                //    return;
                //}
                cmd.CommandText = "insert into employee(name, password, acl) values(?name, ?pwd, ?acl);";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("?name", textBoxEmpName.Text);
                cmd.Parameters.AddWithValue("?pwd", PswdHelper.EncryptString(textBoxEmpPwd.Text));
                string acl = GetEmpAcl();
                cmd.Parameters.AddWithValue("?acl", acl);
                conn.Open();
                #region log
                StringBuilder sb = new StringBuilder(512);
                sb.AppendFormat("C employee {0}=[{1}], ", "name", PswdHelper.EncryptString(textBoxEmpName.Text));
                sb.AppendFormat("{0}=[{1}], ", "pwd", PswdHelper.EncryptString(textBoxEmpPwd.Text));
                sb.AppendFormat("{0}=[{1}], ", "acl", acl);
                WriteLog(sb.ToString());
                #endregion
                cmd.ExecuteNonQuery();

                // 更新列表
                ResetEmp();
                RefreshEmpList();
                #endregion
            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("操作失败: " + ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #3
0
        private void InfoForm_Shown(object sender, EventArgs e)
        {
            MySqlConnection conn = null;

            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = @"select t.id as person_id, t.name as person_name, t.upper as person_upper,
t.upper_used as person_upper_used, s.name as org_name from pur_customer t, pur_organization s 
where t.tag=?tag and t.org_id=s.id;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("?tag", customerID);

                conn.Open();
                MySqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("姓名: ");
                    sb.AppendLine(dr["person_name"].ToString());
                    sb.Append("个人限额: ");
                    sb.AppendLine(dr["person_upper"].ToString());
                    sb.Append("已使用限额: ");
                    sb.AppendLine(dr["person_upper_used"].ToString());
                    sb.Append("所属组织: ");
                    sb.AppendLine(dr["org_name"].ToString());
                    richTextBox1.Text = sb.ToString();

                    personID = int.Parse(dr["person_id"].ToString());
                }
                else
                {
                    richTextBox1.Text = "未在系统中找到这个编号";
                }
                dr.Close();
            }
            catch (MySqlException sqlEx)
            {
                UI.WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器: " + sqlEx.Message);
            }
            catch (Exception ex)
            {
                UI.WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("获取数据失败: " + ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #4
0
        private void RefreshEmpList()
        {
            listViewEmpInfo.Items.Clear();
            MySqlConnection conn = null;

            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                MySqlCommand     cmd     = conn.CreateCommand();
                cmd.CommandType       = CommandType.Text;
                cmd.CommandText       = "select id, name, acl, password from employee order by id asc;";
                adapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                conn.Open();
                adapter.Fill(dt);
                conn.Close();
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows.Count > 0)
                    {
                        listViewEmpInfo.BeginUpdate();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            ListViewItem item = new ListViewItem(dt.Rows[i]["id"].ToString());
                            item.SubItems.Add(dt.Rows[i]["name"].ToString());
                            //item.SubItems.Add(dt.Rows[i]["level"].ToString());
                            string acl = dt.Rows[i]["acl"].ToString();
                            item.SubItems.Add(acl);
                            item.SubItems.Add(dt.Rows[i]["password"].ToString());

                            listViewEmpInfo.Items.Add(item);
                        }
                        listViewEmpInfo.EndUpdate();
                    }
                }
            }
//            catch (MySqlException sqlEx)
//            {
//#warning 记录日志
//                NotifyHelper.NotifyUser("无法连接服务器");
//            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("操作失败: " + ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #5
0
        private void buttonOk_Click(object sender, EventArgs e)
        {
            // 先检查所有的项都有值
            if (CheckItems() == false)
            {
                return;
            }
            FillCash();
            MySqlConnection  conn = null;
            MySqlTransaction tran = null;

            try
            {
                // 写入数据库
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                conn.Open();
                tran = conn.BeginTransaction();

                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = @"insert into pur_detail(counter, emp_id, deal_time, material_id, quantity, customer_id, price)  
 values(?counter, ?emp_id, now(), ?material_id, ?quantity, ?customer_id, ?price);";
                cmd.Parameters.Clear();
                //cmd.Parameters.AddWithValue("?factory_id", facotry_id);
                cmd.Parameters.AddWithValue("?counter", Properties.Settings.Default.CounterName);
                cmd.Parameters.AddWithValue("?emp_id", employeeID);
                cmd.Parameters.AddWithValue("?material_id", (comboBoxMaterialLevel.SelectedItem as ListItem).ID);
                cmd.Parameters.AddWithValue("?quantity", textBoxQuantity.Text);
                cmd.Parameters.AddWithValue("?customer_id", personID);
                cmd.Parameters.AddWithValue("?price", textBoxPrice.Text);
                #region log
                StringBuilder sb = new StringBuilder(512);
                sb.AppendFormat("C pur_detail {0}=[{1}], ", "counter", Properties.Settings.Default.CounterName);
                sb.AppendFormat("{0}=[{1}], ", "emp_id", employeeID);
                sb.AppendFormat("{0}=[{1}], ", "material_id", (comboBoxMaterialLevel.SelectedItem as ListItem).ID);
                sb.AppendFormat("{0}=[{1}], ", "quantity", textBoxQuantity.Text);
                sb.AppendFormat("{0}=[{1}], ", "customer_id", personID);
                sb.AppendFormat("{0}=[{1}]", "price", textBoxPrice.Text);
                WriteLog(sb.ToString());
                #endregion
                cmd.ExecuteNonQuery();

                cmd             = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update pur_customer set upper_used = upper_used+" + textBoxQuantity.Text + " where id=" + personID;
                cmd.Parameters.Clear();

                string s = string.Format("U pur_customer upper_used=[{0}]", textBoxQuantity.Text);
                WriteLog(s);

                cmd.ExecuteNonQuery();
                tran.Commit();

                WriteLog("transaction done");

                // 加入到列表中
                string[] tmp = new string[6];
                tmp[0] = textBoxCustomerTag.Text;
                tmp[1] = comboBoxMaterialName.SelectedItem.ToString();
                tmp[2] = comboBoxMaterialLevel.SelectedItem.ToString();
                tmp[3] = textBoxPrice.Text;
                tmp[4] = textBoxQuantity.Text;
                tmp[5] = textBoxCash.Text;
                ListViewItem item = new ListViewItem(tmp);
                if (listView1.Items.Count > 4)
                {
                    listView1.Items.RemoveAt(listView1.Items.Count - 1);
                }
                listView1.Items.Add(item);

                // 清空输入框
                Reset();
                textBoxCustomerTag.Focus();
            }
            catch (MySqlException sqlEx)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }

                WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器");
                return;
            }
            catch (Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("访问服务器出错");
                return;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #6
0
        //private void button1_Click(object sender, EventArgs e)
        //{
        //    MySqlConnectionStringBuilder connBuilder =
        //        new MySqlConnectionStringBuilder();

        //    connBuilder.Add("Database", "zjzl");
        //    connBuilder.Add("Data Source", "localhost");
        //    connBuilder.Add("User Id", "root");
        //    connBuilder.Add("Password", "123");
        //    //connBuilder.Add("Charset", "utf8");

        //    MySqlConnection connection =
        //        new MySqlConnection(connBuilder.ConnectionString);

        //    try
        //    {
        //        MySqlCommand cmd = connection.CreateCommand();
        //        cmd.CommandType = CommandType.Text;
        //        cmd.CommandText = "select id, name, level, price from material;";

        //        connection.Open();
        //        MySqlDataReader dr = cmd.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            comboBoxMaterialName.Items.Add(dr["name"].ToString());
        //            comboBoxMaterialLevel.Items.Add(dr["level"].ToString());
        //            //com
        //        }
        //        dr.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.ToString());
        //    }
        //    finally
        //    {
        //        if (connection != null)
        //        {
        //            connection.Close();
        //        }
        //    }
        //}

        //private void button2_Click(object sender, EventArgs e)
        //{
        //    MySqlConnection connection = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);

        //    try
        //    {
        //        MySqlCommand cmd = connection.CreateCommand();
        //        cmd.CommandType = CommandType.Text;
        //        cmd.CommandText = "insert into material(name, level, price) values(@name, @level, 0.05);";
        //        cmd.Parameters.Clear();
        //        cmd.Parameters.AddWithValue("@name", "紫荆泽兰");
        //        cmd.Parameters.AddWithValue("@level", "5");

        //        connection.Open();
        //        cmd.ExecuteNonQuery();
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.ToString());
        //    }
        //    finally
        //    {
        //        if (connection != null)
        //        {
        //            connection.Close();
        //        }
        //    }
        //}
        #endregion

        private void Init()
        {
            #region 数量单位/Units
            //string s = Properties.Settings.Default["Units"].ToString();
            //if (!string.IsNullOrEmpty(s))
            //{
            //    string[] ss = s.Split(seperator, StringSplitOptions.RemoveEmptyEntries);
            //    if (ss != null)
            //    {
            //        comboBoxUnits.Items.AddRange(ss);
            //        comboBoxUnits.SelectedIndex = 0;
            //    }
            //}
            #endregion

            MySqlConnection conn = null;
            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                MySqlCommand     cmd     = conn.CreateCommand();
                cmd.CommandType       = CommandType.Text;
                cmd.CommandText       = "select id, name, level, price from material order by name asc, level asc;";
                adapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                conn.Open();
                adapter.Fill(dt);
                conn.Close();
                if (dt.Rows.Count > 0)
                {
                    tableMaterial = new ArrayList();
                    string pastName = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        TableMaterial tm = new TableMaterial();
                        tm.id    = int.Parse(dt.Rows[i]["id"].ToString());
                        tm.name  = dt.Rows[i]["name"].ToString();
                        tm.level = dt.Rows[i]["level"].ToString();
                        tm.price = decimal.Parse(dt.Rows[i]["price"].ToString());
                        tableMaterial.Add(tm);

                        if (pastName != tm.name)
                        {
                            comboBoxMaterialName.Items.Add(tm.name);
                        }
                        pastName = tm.name;
                    }
                }
            }
            catch (MySqlException sqlEx)
            {
                WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器");
            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("获取数据失败");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #7
0
        private void buttonLogin_Click(object sender, EventArgs e)
        {
            #region 检查不为空
            textBoxUserId.Text = textBoxUserId.Text.Trim();
            textBoxPwd.Text    = textBoxPwd.Text.Trim();
            if (string.IsNullOrEmpty(textBoxUserId.Text))
            {
                NotifyHelper.NotifyUser("工号");
                return;
            }
            if (string.IsNullOrEmpty(textBoxPwd.Text))
            {
                NotifyHelper.NotifyUser("密码");
                return;
            }
            #endregion

            MySqlConnection conn = null;
            try
            {
                conn = MySqlConnHelper.GetMySqlConn(connStr);
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select password, acl from employee where id=?id;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("?id", textBoxUserId.Text);
                MySqlDataReader dr = null;
                conn.Open();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    string acl = dr["acl"].ToString();

                    string tmp = string.Format(",{0},", product.ToString("D"));
                    if (acl.Contains(tmp) == false)
                    {
                        NotifyHelper.NotifyUser("不好意思,您没有权限");
                        return;
                    }
#warning 发布时要加入密码验证
                    // 验证密码
                    //if(PswdHelper.EncryptString(textBoxPwd.Text)!=dr["password"].ToString())
                    //{
                    //    NotifyHelper.NotifyUser("用户名或密码有误");
                    //    return;
                    //}

                    this.DialogResult = DialogResult.OK;
                    empId             = textBoxUserId.Text;
                    this.Close();
                }
                else
                {
                    NotifyHelper.NotifyUser("用户名或密码有误");
                    return;
                }
            }
            catch (Exception ex)
            {
                NotifyHelper.NotifyUser(ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #8
0
        private void Init()
        {
            MySqlConnection conn = null;

            try
            {
                eraseTimer.Interval = 2000;
                eraseTimer.Elapsed += new ElapsedEventHandler(eraseTimer_Elapsed);
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                MySqlCommand     cmd     = conn.CreateCommand();
                cmd.CommandType       = CommandType.Text;
                cmd.CommandText       = "select id, name, level, price from product order by name asc, level asc;";
                adapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                conn.Open();
                adapter.Fill(dt);
                #region 物品
                if (dt.Rows.Count > 0)
                {
                    tableMaterial = new ArrayList();
                    string pastName = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        TableMaterial tm = new TableMaterial();
                        tm.id    = int.Parse(dt.Rows[i]["id"].ToString());
                        tm.name  = dt.Rows[i]["name"].ToString();
                        tm.level = dt.Rows[i]["level"].ToString();
                        decimal tmpPrice = 0.0M;
                        if (decimal.TryParse(dt.Rows[i]["price"].ToString(), out tmpPrice))
                        {
                            tm.price = tmpPrice;
                        }

                        tableMaterial.Add(tm);

                        if (pastName != tm.name)
                        {
                            comboBoxMaterialName.Items.Add(tm.name);
                        }
                        pastName = tm.name;
                    }
                }
                #endregion

                cmd.CommandText       = "select id, name from sale_customer order by id asc";
                adapter.SelectCommand = cmd;
                dt = new DataTable();
                adapter.Fill(dt);
                conn.Close();
                if (dt.Rows.Count > 0)
                {
                    //customers = new ArrayList();
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        SaleCustomer saleCustomer = new SaleCustomer();
                        saleCustomer.id   = int.Parse(dt.Rows[j]["id"].ToString());
                        saleCustomer.name = dt.Rows[j]["name"].ToString();

                        comboBoxCorpName.Items.Add(saleCustomer);
                    }
                    comboBoxCorpName.DisplayMember = "name";
                    comboBoxCorpName.ValueMember   = "id";
                }
            }
            catch (MySqlException sqlEx)
            {
                WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器: " + sqlEx.Message);
            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("获取数据失败: " + ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #9
0
        private void buttonCommit_Click(object sender, EventArgs e)
        {
            if (CheckItems() == false)
            {
                return;
            }

            // 写入数据库
            MySqlConnection  conn = null;
            MySqlTransaction tran = null;

            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                conn.Open();
                tran = conn.BeginTransaction();
                MySqlCommand comm = conn.CreateCommand();
                comm.CommandText = @"insert into sale_detail(counter, emp_id, deal_time, 
product_id, quantity, customer_id, price) 
values(?counter, ?emp_id, now(), ?product_id, ?quantity, ?customer_id, ?price)";
                comm.Parameters.Clear();
                comm.Parameters.AddWithValue("?counter", Properties.Settings.Default.CounterName);
                comm.Parameters.AddWithValue("?emp_id", employeeID);
                string product_id;
                product_id = (comboBoxMaterialLevel.SelectedItem as ListItem).ID.ToString();
                comm.Parameters.AddWithValue("?product_id", product_id);
                string quantity;
                quantity = textBoxQuantity.Text;
                comm.Parameters.AddWithValue("?quantity", quantity);
                string customer_id;
                customer_id = (comboBoxCorpName.SelectedItem as SaleCustomer).id.ToString();
                comm.Parameters.AddWithValue("?customer_id", customer_id);
                string price;
                price = textBoxPrice.Text;
                comm.Parameters.AddWithValue("?price", price);
                comm.Transaction = tran;
                #region log
                StringBuilder sb = new StringBuilder(512);
                sb.AppendFormat("C sale_detail {0}=[{1}], ", "counter", Properties.Settings.Default.CounterName);
                sb.AppendFormat("{0}=[{1}], ", "emp_id", employeeID);
                sb.AppendFormat("{0}=[{1}], ", "product_id", product_id);
                sb.AppendFormat("{0}=[{1}], ", "quantity", quantity);
                sb.AppendFormat("{0}=[{1}], ", "customer_id", customer_id);
                sb.AppendFormat("{0}=[{1}]", "price", price);
                WriteLog(sb.ToString());
                #endregion
                comm.ExecuteNonQuery();

                tran.Commit();
                WriteLog("transaction done");

                Reset();
                labelCommitOK.Text      = "提交成功";
                labelCommitOK.ForeColor = Color.DodgerBlue;
                eraseTimer.Start();
            }
            catch (MySqlException sqlEx)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
                WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器");
                return;
            }
            catch (Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("访问服务器出错");
                return;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #10
0
        private void Init()
        {
            MySqlConnection conn = null;

            try
            {
                Reset();
                eraseTimer.Elapsed += new ElapsedEventHandler(eraseTimer_Elapsed);
                eraseTimer.Interval = 2500;

                string pastName = null;
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                MySqlDataAdapter adapter = null;
                adapter = new MySqlDataAdapter();
                DataTable dt = null;
                conn.Open();

                #region 操作员
                //adapter = new MySqlDataAdapter();
                cmd.CommandText       = "select id, name from employee";
                adapter.SelectCommand = cmd;
                dt = new DataTable();
                adapter.Fill(dt);
                conn.Close();
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ListItem mt = new ListItem();
                        mt.ID   = dt.Rows[i]["id"].ToString();
                        mt.Name = string.Format("{0} -- {1}", dt.Rows[i]["name"].ToString(), mt.ID);
                        comboBoxOperator.Items.Add(mt);
                    }
                    comboBoxOperator.DisplayMember = "Name";
                    comboBoxOperator.ValueMember   = "ID";
                }
                #endregion

                #region 原料
                cmd.CommandText       = "select id, name, level, price from material order by name asc, level asc;";
                adapter.SelectCommand = cmd;
                dt = new DataTable();
                adapter.Fill(dt);
                conn.Close();
                if (dt.Rows.Count > 0)
                {
                    tableMaterial = new ArrayList();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        TableMaterial tm = new TableMaterial();
                        tm.id    = int.Parse(dt.Rows[i]["id"].ToString());
                        tm.name  = dt.Rows[i]["name"].ToString();
                        tm.level = dt.Rows[i]["level"].ToString();
                        tableMaterial.Add(tm);

                        if (pastName != tm.name)
                        {
                            comboBoxMaterialName.Items.Add(tm.name);
                        }
                        pastName = tm.name;
                    }
                }
                #endregion

                #region 产出
                //adapter = new MySqlDataAdapter();
                cmd.CommandText       = "select id, name, level, price from product order by name asc, level asc;";
                adapter.SelectCommand = cmd;
                dt = new DataTable();
                adapter.Fill(dt);
                conn.Close();
                if (dt.Rows.Count > 0)
                {
                    tableProduct = new ArrayList();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        TableMaterial tm = new TableMaterial();
                        tm.id    = int.Parse(dt.Rows[i]["id"].ToString());
                        tm.name  = dt.Rows[i]["name"].ToString();
                        tm.level = dt.Rows[i]["level"].ToString();
                        tableProduct.Add(tm);

                        if (pastName != tm.name)
                        {
                            comboBoxProductName.Items.Add(tm.name);
                        }
                        pastName = tm.name;
                    }
                }
                #endregion
            }
            catch (MySqlException sqlEx)
            {
                WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器");
            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("获取数据失败");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #11
0
        private void buttonCommit_Click(object sender, EventArgs e)
        {
            // 先检查所有的项都有值
            if (CheckAllItems() == false)
            {
                return;
            }
            MySqlConnection  conn = null;
            MySqlTransaction tran = null;

            try
            {
                conn = MySqlConnHelper.GetMySqlConn(Properties.Settings.Default.DbConn);
                conn.Open();
                tran = conn.BeginTransaction();
                MySqlCommand comm = null;
                #region 基本信息
                comm = conn.CreateCommand();
                // mysql 日期格式参见 http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date
                comm.CommandText = @"insert into operation_detail(counter, emp_id, begin_time, 
end_time) 
values(?counter, ?emp_id, str_to_date(?begin_time, '%Y%m%d %H%i'), 
str_to_date(?end_time, '%Y%m%d %H%i') )";
                comm.Parameters.Clear();
                comm.Parameters.AddWithValue("?counter", textBoxCounter.Text);
                ListItem li = (comboBoxOperator.SelectedItem as ListItem);
                comm.Parameters.AddWithValue("?emp_id", li.ID);
                comm.Parameters.AddWithValue("?begin_time", textBoxBeginTime.Text);
                comm.Parameters.AddWithValue("?end_time", textBoxEndTime.Text);
                comm.Transaction = tran;
                #region log
                StringBuilder sb = new StringBuilder(512);
                sb.AppendFormat("C operation_detail ");
                sb.AppendFormat("{0}=[{1}], ", "counter", textBoxCounter.Text);
                sb.AppendFormat("{0}=[{1}], ", "emp_id", li.ID);
                sb.AppendFormat("{0}=[{1}], ", "begin_time", textBoxBeginTime.Text);
                sb.AppendFormat("{0}=[{1}]", "end_time", textBoxEndTime.Text);
                WriteLog(sb.ToString());
                #endregion
                comm.ExecuteNonQuery();
                #endregion
                comm = conn.CreateCommand();
#warning 设置一个断点,进行测试
                comm.CommandText = "select max(id) from operation_detail";
                string operationID = comm.ExecuteScalar().ToString();
                #region 原料消耗
                for (int i = 0; i < listViewMaterialCost.Items.Count; i++)
                {
                    comm             = conn.CreateCommand();
                    comm.CommandText = @"insert into material_cost
(material_id, quantity, operation_id)
values(?material_id, ?quantity, ?operation_id)";
                    comm.Parameters.Clear();
                    string material_id;
                    material_id = listViewMaterialCost.Items[i].SubItems[3].Text;
                    comm.Parameters.AddWithValue("?material_id", material_id);
                    string quantity;
                    quantity = listViewMaterialCost.Items[i].SubItems[2].Text;
                    comm.Parameters.AddWithValue("?quantity", quantity);
                    comm.Parameters.AddWithValue("?operation_id", operationID);
                    #region log
                    sb = new StringBuilder(512);
                    sb.AppendFormat("C material_cost ");
                    sb.AppendFormat("{0}=[{1}], ", "material_id", material_id);
                    sb.AppendFormat("{0}=[{1}], ", "quantity", quantity);
                    sb.AppendFormat("{0}=[{1}], ", "operation_id", operationID);
                    WriteLog(sb.ToString());
                    #endregion
                    comm.ExecuteNonQuery();
                }
                #endregion
                #region 产品产出
                for (int i = 0; i < listViewProductOut.Items.Count; i++)
                {
                    comm             = conn.CreateCommand();
                    comm.CommandText = @"insert into product_out
(product_id, quantity, operation_id)
values(?product_id, ?quantity, ?operation_id)";
                    comm.Parameters.Clear();
                    string product_id;
                    product_id = listViewProductOut.Items[i].SubItems[3].Text;
                    comm.Parameters.AddWithValue("?product_id", product_id);
                    string quantity;
                    quantity = listViewProductOut.Items[i].SubItems[2].Text;
                    comm.Parameters.AddWithValue("?quantity", quantity);
                    comm.Parameters.AddWithValue("?operation_id", operationID);
                    #region log
                    sb = new StringBuilder(512);
                    sb.AppendFormat("C product_out ");
                    sb.AppendFormat("{0}=[{1}], ", "product_id", product_id);
                    sb.AppendFormat("{0}=[{1}], ", "quantity", quantity);
                    sb.AppendFormat("{0}=[{1}], ", "operation_id", operationID);
                    WriteLog(sb.ToString());
                    #endregion
                    comm.ExecuteNonQuery();
                }
                #endregion
                tran.Commit();
                WriteLog("transaction done");

                Reset();
                labelCommitOK.Text      = "提交成功";
                labelCommitOK.ForeColor = Color.DodgerBlue;
                eraseTimer.Start();
            }
            catch (MySqlException sqlEx)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }

                WriteLog(sqlEx.ToString());
                NotifyHelper.NotifyUser("无法连接服务器");
                return;
            }
            catch (Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }

                WriteLog(ex.ToString());
                NotifyHelper.NotifyUser("访问服务器出错");
                return;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }