public bool add(csvcDTO CSVC, string maold)
{
string queryadd = string.Empty;
queryadd += " update [tblCSVC]";
queryadd += " set maNhanVien=@maNhanVien,tencsvc=@tencsvc,csvctot=@csvctot,csvchong=@csvchong,dongianhap=@dongianhap where maCSVC=@maold";
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=QLSB;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryadd;
cmd.Parameters.AddWithValue("@maNhanVien", CSVC.MaNhanVien);
cmd.Parameters.AddWithValue("@tencsvc", CSVC.tenCSVC);
cmd.Parameters.AddWithValue("@csvctot", CSVC.CSVCtot);
cmd.Parameters.AddWithValue("@csvchong", CSVC.CSVChong);
cmd.Parameters.AddWithValue("@dongianhap", CSVC.DonGiaNhap);
cmd.Parameters.AddWithValue("@maold", maold);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public List <csvcDTO> selectedCSVCchance(string sel_item)
{
string query = string.Empty;
string val = sel_item;
query += "SELECT * ";
query += "FROM tblCSVC Where tencsvc = '" + val + "'";
List <csvcDTO> lscsvc = new List <csvcDTO>();
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=QLSB;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
csvcDTO CSVC = new csvcDTO();
CSVC.MaCSVC = reader["MaCSVC"].ToString();
CSVC.tenCSVC = reader["tenCSVC"].ToString();
CSVC.CSVChong = int.Parse(reader["CSVChong"].ToString());
CSVC.CSVCtot = int.Parse(reader["CSVCtot"].ToString());
lscsvc.Add(CSVC);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(lscsvc);
}
public bool facilities_payment(csvcDTO PAY)
{
string queryHD = string.Empty;
queryHD += " INSERT INTO [tblHOADON] ([maHoaDon],[maNhanVien],[maKH],[ngaytaohoadon]) ";
queryHD += " VALUES (@maHoaDon,@maNhanVien,@maKH,@ngaytaohoadon)";
string queryLHD = string.Empty;
queryLHD += " SELECT *";
queryLHD += " FROM [tblLOAIHOADON] where maLoaiHoaDon=2";
string queryCTHD = string.Empty;
queryCTHD += " INSERT INTO [tblCHITIETHOADON] ([maHoaDon],[maLoaiHoaDon],[trigiahoadon]) ";
queryCTHD += " VALUES (@maHoaDon,@maLoaiHoaDon,@trigiahoadon)";
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=QLSB;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryHD;
cmd.Parameters.AddWithValue("@maHoaDon", PAY.MaHoaDon);
cmd.Parameters.AddWithValue("@maNhanVien", PAY.MaNhanVien);
cmd.Parameters.AddWithValue("@maKH", PAY.MaKH);
cmd.Parameters.AddWithValue("@ngaytaohoadon", PAY.NgayTaoHoaDon);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
}
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=QLSB;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryLHD;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
PAY.MaLoaiHoaDon = reader["MaLoaiHoaDon"].ToString();
PAY.LoaiHoaDon = reader["LoaiHoaDon"].ToString();
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=QLSB;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = queryCTHD;
cmd.Parameters.AddWithValue("@maHoaDon", PAY.MaHoaDon);
cmd.Parameters.AddWithValue("@maLoaiHoaDon", PAY.MaLoaiHoaDon);
cmd.Parameters.AddWithValue("@trigiahoadon", PAY.TriGiaHoaDon);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public bool add(csvcDTO CSVC, string maold)
{
bool isAdd = csvcDAL.add(CSVC, maold);
return(isAdd);
}
public bool facilities_payment(csvcDTO csvc)
{
bool ispayment = csvcDAL.facilities_payment(csvc);
return(ispayment);
}
private void button1_Click(object sender, EventArgs e)
{
string ma1 = null;
int gia = 0;
switch (comboBox1.Text)
{
case "bong":
ma1 = 1.ToString();
gia = 50000;
break;
case "luoi":
ma1 = 2.ToString();
gia = 100000;
break;
case "san co":
ma1 = 3.ToString();
gia = 1000000;
break;
case "den":
ma1 = 4.ToString();
gia = 10000;
break;
}
csvcDTO csvcDTO = new csvcDTO();
temp = ma1;
string val = comboBox1.SelectedValue.ToString();
oldtot = csvcBUS.CSVCTOT(val);
oldhong = csvcBUS.CSVCHONG(val);
csvcDTO.MaHoaDon = csvcBUS.autogenerate_maHOADON().ToString();
csvcDTO.MaKH = 1.ToString();
csvcDTO.NgayTaoHoaDon = dateTimePicker1.Value;
csvcDTO.TriGiaHoaDon = (Decimal.ToInt32(numericUpDown1.Value) * gia).ToString();
csvcDTO.MaNhanVien = 2.ToString();
csvcDTO.tenCSVC = comboBox1.Text;
csvcDTO.CSVCtot = Decimal.ToInt32(numericUpDown1.Value) + oldtot;
if (oldhong <= Decimal.ToInt32(numericUpDown1.Value))
{
csvcDTO.CSVChong = 0;
}
else
{
csvcDTO.CSVChong = oldhong - Decimal.ToInt32(numericUpDown1.Value);
}
csvcDTO.DonGiaNhap = gia;
bool resultHD = csvcBUS.facilities_payment(csvcDTO);
if (resultHD == true)
{
MessageBox.Show("Thêm hoá đơn thành công");
}
else
{
MessageBox.Show("Thêm hoá đơn thất bại");
}
bool result = csvcBUS.add(csvcDTO, temp);
if (result == true)
{
MessageBox.Show("Thêm CSVC thành công");
}
else
{
MessageBox.Show("Thêm CSVC thất bại");
}
List <csvcDTO> listCSVC = csvcBUS.selectedCSVCchance(val);
if (listCSVC == null)
{
MessageBox.Show("Chưa có thông tin CSVC");
}
dgv1.Columns.Clear();
dgv1.DataSource = null;
dgv1.ReadOnly = true;
dgv1.AutoGenerateColumns = false;
dgv1.AllowUserToAddRows = false;
dgv1.DataSource = listCSVC;
DataGridViewTextBoxColumn clCSVCCode = new DataGridViewTextBoxColumn();
clCSVCCode.Name = "maCSVC";
clCSVCCode.HeaderText = "Mã CSVC";
clCSVCCode.DataPropertyName = "MaCSVC";
dgv1.Columns.Add(clCSVCCode);
DataGridViewTextBoxColumn clTCVSCName = new DataGridViewTextBoxColumn();
clTCVSCName.Name = "tencsvc";
clTCVSCName.HeaderText = "Tên CSVC";
clTCVSCName.DataPropertyName = "tenCSVC";
dgv1.Columns.Add(clTCVSCName);
DataGridViewTextBoxColumn clTCVSCval = new DataGridViewTextBoxColumn();
clTCVSCval.Name = "csvctot";
clTCVSCval.HeaderText = "CSVC Tốt";
clTCVSCval.DataPropertyName = "CSVCtot";
dgv1.Columns.Add(clTCVSCval);
DataGridViewTextBoxColumn clTCVSCStatus = new DataGridViewTextBoxColumn();
clTCVSCStatus.Name = "csvchong";
clTCVSCStatus.HeaderText = "CSVC Hỏng";
clTCVSCStatus.DataPropertyName = "CSVChong";
dgv1.Columns.Add(clTCVSCStatus);
}
