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