private void cbb_chon_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbb_chon.Text == "Mua linh kiện")
{
DataTable dt = new DataTable();
string sql = "select MALK, TENLK from TBLLINHKIEN ";
ConnectData con = new ConnectData();
dt = con.Getdatatable(sql);
cbb_masp.DataSource = dt;
cbb_masp.DisplayMember = "MALK";
cbb_masp.ValueMember = "MALK";
cbb_tensp.DataSource = dt;
cbb_tensp.DisplayMember = "TENLK";
cbb_tensp.ValueMember = "MALK";
}
else
{
DataTable dt = new DataTable();
string sql = "select MAMAY, TENMAY from TBLMAYTINH ";
ConnectData con = new ConnectData();
dt = con.Getdatatable(sql);
cbb_masp.DataSource = dt;
cbb_masp.DisplayMember = "MAMAY";
cbb_masp.ValueMember = "MAMAY";
cbb_tensp.DataSource = dt;
cbb_tensp.DisplayMember = "TENMAY";
cbb_tensp.ValueMember = "MAMAY";
}
}
private void bt_tinh_Click(object sender, EventArgs e)
{
ConnectData con = new ConnectData();
String kt = "select MAHNANG from TBL_CTHD where sohd = " + txt_sohd.Text + " and MAHNANG like 'lk%'";
DataTable dt = con.Getdatatable(kt);
String kt2 = "select MAHNANG from TBL_CTHD where sohd = " + txt_sohd.Text + " and MAHNANG like 'mt%'";
DataTable dt2 = con.Getdatatable(kt2);
if (dt.Rows.Count > 0)
{
string sql2 = "select sum((SOLUONG*lk.DONGIA)) as tienlk " +
" from TBL_CTHD ct, TBLLINHKIEN lk, TBLHOADON hd " +
" where ct.MAHNANG = lk.MALK and ct.SOHD = hd.SOHD and ct.SOHD = " + txt_sohd.Text;
tienlk = con.GetScalar(sql2);
}
if (dt2.Rows.Count > 0)
{
string sql = "select sum((SOLUONG*mt.DONGIA)) as tienmay " +
" from TBL_CTHD ct, TBLMAYTINH mt, TBLHOADON hd " +
" where ct.MAHNANG = mt.MAMAY and ct.SOHD = hd.SOHD and ct.SOHD = " + txt_sohd.Text;
tienmay = con.GetScalar(sql);
}
txt_thanhtien.Text = (tienlk + tienmay).ToString();
string sql3 = "select MAHNANG as [Mã mặt hàng], SOLUONG as [Số lượng mua] from TBL_CTHD where sohd=" + txt_sohd.Text;
dataGridView1.DataSource = con.Getdatatable(sql3);
}
public int GetScalar(string sqlquery)
{
ConnectData con = new ConnectData();
SqlConnection conn = new SqlConnection();
conn = con._CreateConnect();
conn.Open();
SqlCommand cmd = new SqlCommand(sqlquery, conn);
int scalar = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return(scalar);
}
public DataTable Getdatatable(string sqlquery)
{
ConnectData con = new ConnectData();
SqlConnection conn = new SqlConnection();
conn = con._CreateConnect();
conn.Open();
SqlCommand cmd = new SqlCommand(sqlquery, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
private void bt_them_Click(object sender, EventArgs e)
{
ConnectData con = new ConnectData();
SqlConnection conn = new SqlConnection();
conn = con._CreateConnect();
if (cbb_chon.Text == "")
{
MessageBox.Show("Chưa chọn loại chức năng thêm !!!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (cbb_chon.Text == "Thêm máy tính")
{
StringBuilder builder = new StringBuilder();
builder.Append(RandomNumber(0, 9999));
string sql = "insert into TBLMAYTINH(MAMAY, TENMAY, DONGIA, GHICHU, TINHTRANG) VALUES('MT" + builder.ToString() + "',N'" + txt_ten.Text + "'," + txt_dongia.Text + ",N'" + txt_ghichu.Text + "',1)";
string sql2 = "insert into TBLCAUHINH(MAMAY, HANG,CPU ,GPU ,RAM ,HARDDISK) values('MT" + builder.ToString() + "',N'" + cbb_hang.Text + "',N'" + txt_CPU.Text + "',N'" + txt_GPU.Text + "',N'" + cbb_ram.Text + "',N'" + txt_hardisk.Text + "')";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Thêm máy tính thành công !!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (cbb_chon.Text == "Thêm linh kiện")
{
StringBuilder builder = new StringBuilder();
builder.Append(RandomNumber(0, 9999));
string sql = "insert into TBLLINHKIEN(MALK, TENLK, DONGIA, GHICHU, TINHTRANG) VALUES('LK" + builder.ToString() + "',N'" + txt_ten.Text + "'," + txt_dongia.Text + ",N'" + txt_ghichu.Text + "',1)";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Thêm linh kiện thành công !!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Chọn sai chức năng !!!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void bt_hd_Click(object sender, EventArgs e)
{
ConnectData con = new ConnectData();
dataGridView1.DataSource = con.Getdatatable("select SOHD, sum(thanhtien) as [Số tiền mua] from TBLHOADON group by SOHD having sum(THANHTIEN) >100000");
}
private void bt_mt_Click(object sender, EventArgs e)
{
ConnectData con = new ConnectData();
dataGridView1.DataSource = con.Getdatatable("select TENMAY as [Tên máy tính], SUM(soluong) as [Tổng số lượng mua] from TBL_CTHD ct, TBLMAYTINH mt where ct.MAHNANG = mt.MAMAY group by TENMAY having sum(soluong) >5");
}
private void bt_lk_Click(object sender, EventArgs e)
{
ConnectData con = new ConnectData();
dataGridView1.DataSource = con.Getdatatable("select TENLK as [Tên linh kiện], SUM(soluong) as [Tổng số lượng mua] from TBL_CTHD ct, TBLLINHKIEN lk where ct.MAHNANG = lk.MALK group by TENLK having sum(soluong) >10");
}
