private void PersonalInfo_Load(object sender, EventArgs e)
{
treeView1.ExpandAll();
InitTree(this.treeView1.Nodes, "0");
treeView1.ExpandAll();
toolStrip_save.Enabled = false;
toolStripButton2.Enabled = false;
MySQLConnection1.OpenConn();
}
void ca_updatePic(bool topmost)
{
DataView dv = new DataView();
DataTable dt = MySQLConnection1.GetDataTableValue("select * from staff_info where id = '" + currentID + "'");
dt.TableName = "staff_info";
dv.Table = dt;
DataRowView drv = dv[0];
Byte[] Files = (Byte[])drv["pic"];
MemoryStream mysm = new MemoryStream((byte[])drv["pic"]);
Image ims = Image.FromStream(mysm);
this.pictureBox1.Image = ims;
}
private void btn_search_Click(object sender, EventArgs e)
{
string sql;
string type;
if (!IsNumberic(txt_id.Text) && txt_id.Text != "")
{
MessageBox.Show("ID must be a number");
}
else
{
if (comboB_type.Text == "All")
{
type = "";
}
else
{
type = comboB_type.Text;
}
if (txt_id.Text == "")
{
sql = "select * from stock where itemID like '%" + txt_id.Text + "%' and itemName like '%" + txt_name.Text + "%' and type like '%" + type + "%'";
}
else
{
sql = "select * from stock where itemID = '" + txt_id.Text + "' and itemName like '%" + txt_name.Text + "%' and type like '%" + type + "%'";
}
if (local)
{
DataSet ds = GetDataSet(sql);
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tb";
ColumnOrder();
}
else
{
DataSet ds = MySQLConnection1.GetDataSetValue(sql, "tb");
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tb";
ColumnOrder();
}
}
isSearchOrAddIsClick = true;
}
private void btn_savpic_Click(object sender, EventArgs e)
{
string strPath = txt_pic.Text.Trim();
FileStream fs = new FileStream(strPath, FileMode.Open, FileAccess.Read);
byte[] byteFile = new byte[fs.Length];
fs.Read(byteFile, 0, (int)fs.Length);
fs.Close();
using (MySqlConnection conn = new MySqlConnection(MySQLConnection1.SqlCon))
{
string UpdateSql = "update staff_info" +
" set pic = @ImgData" +
" where id = '" + currentID + "'";
// string sql = "Insert Into picTest Values (@ImgData) ";
using (MySqlCommand cmd = new MySqlCommand(UpdateSql))
{
MySqlParameter param = new MySqlParameter("ImgData", MySqlDbType.VarBinary, byteFile.Length);
param.Value = byteFile;
cmd.Parameters.Add(param);
cmd.Connection = conn;
conn.Open();
int i = cmd.ExecuteNonQuery();
MessageBox.Show("success!");
}
}
DataView dv = new DataView();
DataTable dt = MySQLConnection1.GetDataTableValue("select * from staff_info where id = '" + currentID + "'");
dt.TableName = "staff_info";
dv.Table = dt;
DataRowView drv = dv[0];
Byte[] Files = (Byte[])drv["pic"];
MemoryStream mysm = new MemoryStream((byte[])drv["pic"]);
Image ims = Image.FromStream(mysm);
this.pictureBox1.Image = ims;
}
private void InitialGrid()
{
DataSet ds;
if (local)
{
ds = GetDataSet("select * from stock");
}
else
{
ds = MySQLConnection1.GetDataSetValue("select * from stock", "tb");
}
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tb";
ColumnOrder();
style();
}
private void deleteData(int id)
{
try
{
string DeleteSql = "Delete from stock where itemID = '" + id + "'";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(DeleteSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from stock", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(DeleteSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from stock", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
MySQLConnection1.CloseConn();
}
MySqlConnection MySqlCon1 = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlCon1.Open();
MySqlCommand com1 = new MySqlCommand("ALTER TABLE stock AUTO_INCREMENT = 1;", MySqlCon1);
com1.ExecuteNonQuery();
MySqlDataAdapter ada1 = new MySqlDataAdapter("select * from stock", MySqlCon1);
DataSet ds1 = new DataSet();
ada1.Fill(ds1, "stock");
MySQLConnection1.CloseConn();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void InitTree(TreeNodeCollection Nds, string parentId)
{
DataView dv = new DataView();
TreeNode tmpNd;
string intId;
if (local)
{
DataSet ds = GetDataSet("select * from staff_info");
dv.Table = ds.Tables[0];
}
else
{
DataTable dt = MySQLConnection1.GetDataTableValue("select * from staff_info");
dt.TableName = "staff_info";
dv.Table = dt;
}
dv.RowFilter = "parent_id='" + parentId + "'";
foreach (DataRowView drv in dv)
{
tmpNd = new TreeNode();
tmpNd.Tag = drv["id"].ToString();
tmpNd.Text = drv["last_name"].ToString();
if (drv["parent_id"].ToString() == "1") //10 means admin
{
tmpNd.ImageIndex = tmpNd.SelectedImageIndex = 0;
}
else
{
tmpNd.ImageIndex = tmpNd.SelectedImageIndex = 1;
}
this.treeView1.BeginUpdate();
Nds.Add(tmpNd);
this.treeView1.EndUpdate();
intId = drv["parent_id"].ToString();
InitTree(tmpNd.Nodes, tmpNd.Tag.ToString());
}
}
private void updateData(int id, string name, int safe, int qty)
{
try
{
string UpdateSql = "update stock" +
" set itemName = '" + name + "'," +
"itemSafeStock = '" + safe + "'," +
"quantity = '" + qty + "'" +
"where itemID = '" + id + "'";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(UpdateSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from stock", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(UpdateSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from stock", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
MySQLConnection1.CloseConn();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void deleteData()
{
try
{
string DeleteSql = "Delete from staff_info where id = '" + treeView1.SelectedNode.Tag + "'";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(DeleteSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from staff_info", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(DeleteSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from staff_info", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
MySQLConnection1.CloseConn();
}
treeView1.Nodes.Clear();
InitTree(this.treeView1.Nodes, "0");
treeView1.ExpandAll();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
void initialChart(string type, Boolean showAll)
{
string sql;
chart1.Series.Clear();//清理掉默认的图表 然后重新添加
DataTable ds = new DataTable();
if (showAll)
{
sql = "select * from statistics";
}
else
{
sql = "SELECT * FROM statistics WHERE DATE BETWEEN '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "'";
}
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlDataAdapter sda = new MySqlDataAdapter(sql, MySqlCon);
MySQLConnection1.OpenConn();
sda.Fill(ds);
if (type == "line")
{
Series ss = new Series("income"); //这里 dt1 ,dt2 任意取名称，但要唯一
ss.Points.DataBind(ds.AsEnumerable(), "date", "income", "");
ss.XValueType = ChartValueType.DateTime; //设置X轴
ss.ChartType = SeriesChartType.Spline; //设置Y轴为折线
chart1.Series.Add(ss);
if (checkBox_cost.Checked)
{
Series ss1 = new Series("cost"); //这里 dt1 ,dt2 任意取名称，但要唯一
ss1.Points.DataBind(ds.AsEnumerable(), "date", "cost", "");
ss1.XValueType = ChartValueType.DateTime; //设置X轴
ss1.ChartType = SeriesChartType.Spline; //设置Y轴为折线
chart1.Series.Add(ss1);
}
}
else if (type == "pie")
{
Series s = chart1.Series.Add("饼图");
//
//设置图标名称
//s.ChartArea = "ChartArea" + j;
//图表类型 折线 柱状 圆形
s.ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Pie;
s.BorderWidth = 3;
s.ShadowOffset = 1;
s.IsValueShownAsLabel = true; //是否在图表上显示数据的值
s.XValueType = ChartValueType.Int32;
s.MarkerStyle = MarkerStyle.Circle; //数据点样式
List <string> xData1 = new List <string>(); //声明两个集合将数据先放在集合中
List <string> yData1 = new List <string>();
for (int i = 0; i < ds.Rows.Count; i++) //
{
//将数据先放在集合中
xData1.Add(ds.Rows[i]["date"].ToString());
yData1.Add(ds.Rows[i]["income"].ToString());
}
//循环结束后，绑定数据源
chart1.Series[0].Points.DataBindXY(xData1, yData1);
}
// chart2.Series.Add(ss);
//第二条数据
////第三条数据
//string sql2 = "SELECT * FROM APiece WHERE APiece.Piece_PId=3";
//DataTable ds2 = new DataTable();
//SqlDataAdapter sda2 = new SqlDataAdapter(sql2, con);
//sda2.Fill(ds2);
//Chart1.DataSource = ds2;
//Series ss3 = new Series("产品3");
//ss3.Points.DataBind(ds2.AsEnumerable(), "Piece_Time", "Piece_Level", "");
//ss3.XValueType = ChartValueType.DateTime; //设置X轴
//ss3.ChartType = SeriesChartType.Spline; //设置Y轴为折线
//Chart1.Series.Add(ss3);
}
private void insertData()
{
try
{
if (!IsNumberic(txt_safe.Text))
{
MessageBox.Show("Safe stock must be a number");
return;
}
if (!IsNumberic(txt_qty.Text))
{
MessageBox.Show("Qty must be be a number");
return;
}
if (comboB_type1.Text == "")
{
MessageBox.Show("Must select the type of item");
return;
}
else
{
string InsertSql = "insert into stock(itemName,itemSafeStock,quantity,type) " +
"values ('" + txt_name1.Text + "'" +
",'" + txt_safe.Text + "'" +
",'" + txt_qty.Text + "'" +
",'" + comboB_type1.Text + "')";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(InsertSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from stock", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(InsertSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from stock", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
MySQLConnection1.CloseConn();
}
MessageBox.Show("successful");
txt_name1.Text = "";
txt_safe.Text = "";
txt_qty.Text = "";
comboB_type1.Text = "";
InitialGrid();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void updateData()
{
int parentID = 0;
if (cmb_position.SelectedIndex == 0)
{
parentID = 1;
}
if (cmb_position.SelectedIndex == 1)
{
parentID = 2;
}
if (cmb_position.SelectedIndex == 2)
{
parentID = 3;
}
if (cmb_position.SelectedIndex == 3)
{
parentID = 4;
}
if (!IsEmail(txt_Email.Text))
{
MessageBox.Show("the email address is invalid");
return;
}
else
{
string UpdateSql = "update staff_info" +
" set last_name = '" + txt_name.Text + "'," +
"address_id = '" + txt_Address.Text + "'," +
"phone = '" + txt_Phone.Text + "'," +
"parent_id = '" + parentID + "'," +
"gender = '" + comBox_gender.Text + "'," +
"birthday = '" + dateTimePicker1.Value.Date.ToString("yyyy-MM-dd HH:mm") + "'," +
"ssn = '" + txt_SSN.Text + "'," +
"email = '" + txt_Email.Text + "' " +
"where id = '" + treeView1.SelectedNode.Tag + "'";
try
{
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(UpdateSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from staff_info", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection1.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(UpdateSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from staff_info", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
MySQLConnection1.CloseConn();
}
MessageBox.Show("successful");
treeView1.Nodes.Clear();
InitTree(this.treeView1.Nodes, "0");
treeView1.ExpandAll();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
toolStripButton1.Text = "Add";
toolStripButton1.Image = imageList1.Images[2];
toolStripButton1.Enabled = true;
initial();
}
}
private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
{
currentID = int.Parse(e.Node.Tag.ToString());
if (e.Action == TreeViewAction.ByMouse)
{
DataView dv = new DataView();
if (local)
{
DataSet ds = GetDataSet("select * from staff_info where id = '" + e.Node.Tag + "'");
dv.Table = ds.Tables[0];
}
else
{
DataTable dt = MySQLConnection1.GetDataTableValue("select * from staff_info where id = '" + e.Node.Tag + "'");
dt.TableName = "staff_info";
dv.Table = dt;
}
DataRowView drv = dv[0];
if (e.Node.Parent != null)
{
txt_name.Text = drv["last_name"].ToString();
if (drv["gender"].ToString() == "Male")
{
comBox_gender.SelectedIndex = 0;
}
else
{
comBox_gender.SelectedIndex = 1;
}
txt_SSN.Text = drv["ssn"].ToString();
if (drv["parent_id"].ToString() == "1")
{
cmb_position.SelectedIndex = 0;
}
if (drv["parent_id"].ToString() == "2")
{
cmb_position.SelectedIndex = 1;
}
if (drv["parent_id"].ToString() == "3")
{
cmb_position.SelectedIndex = 2;
}
if (drv["parent_id"].ToString() == "4")
{
cmb_position.SelectedIndex = 3;
}
txt_Phone.Text = drv["phone"].ToString();
txt_Address.Text = drv["address_id"].ToString();
if (!drv["birthday"].Equals(System.DBNull.Value))
{
dateTimePicker1.Value = Convert.ToDateTime(drv["Birthday"]);
}
else
{
dateTimePicker1.Value = System.DateTime.Today;
}
if (!drv["pic"].Equals(System.DBNull.Value))
{
Byte[] Files = (Byte[])drv["pic"];
MemoryStream mysm = new MemoryStream((byte[])drv["pic"]);
Image ims = Image.FromStream(mysm);
this.pictureBox1.Image = ims;
}
else
{
this.pictureBox1.Image = null;
}
txt_Email.Text = drv["email"].ToString();
toolStripButton2.Enabled = true;
btn_del.Enabled = true;
}
else
{
toolStripButton2.Enabled = false;
btn_del.Enabled = false;
}
}
}