public bool xoa(ThuTienDTO tt)
{
string query = string.Empty;
query += "delete from [PhieuThuTien] where [PhieuThuTien].[maPhieuThuTien] = @maPhieuThuTien";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maPhieuThuTien", tt.maPhieuThuTien);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public static List <ThuTienDTO> GetDS()
{
List <ThuTienDTO> DS = new List <ThuTienDTO>();
using (dbMainDataContext dbMain = new dbMainDataContext())
{
var query = from p in dbMain.PhieuThus select p;
foreach (var row in query)
{
ThuTienDTO tt = new ThuTienDTO();
tt.Maphieu = row.MaPhieuThu;
tt.Ngaythu = row.NgayThu.ToString();
tt.Sotienthu = row.SoTienThu.ToString();
tt.Makh = row.MaKH;
var getKH = from h in dbMain.KhachHangs where h.MaKh == row.MaKH select h;
foreach (var row2 in getKH)
{
tt.Tenkh = row2.HoTen;
tt.Email = row2.Email;
tt.Diachi = row2.DiaChi;
tt.Sdt = row2.SDT;
// tt.Sotienno = row2.TongNo;
}
DS.Add(tt);
}
}
return(DS);
}
private void btXoa_Click(object sender, EventArgs e)
{
DialogResult = MessageBox.Show("Bạn có chắc chắn muốn xóa ?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (DialogResult == DialogResult.Yes)
{
ThuTienDTO tt = new ThuTienDTO();
tt.ID = int.Parse(txtID.Text);
bool kq2 = ttBus.xoa(tt);
if (kq2 == false)
{
MessageBox.Show("Xóa thông tin thất bại. Vui lòng kiểm tra lại dữ liệu");
}
else
{
MessageBox.Show("Xóa thông tin thành công");
}
this.loadData_Vao_GridView();
txtBienso.Text = "";
txtEmail.Text = "";
txtHT.Text = "";
txtSdt.Text = "";
txtSotienthu.Text = "";
txtID.Text = "";
}
}
private void maDaiLy_txt_TextChanged(object sender, EventArgs e)
{
ThuTienDTO tt = new ThuTienDTO();
if (maDaiLy_txt.Text.Trim().Length != 0)
{
try
{
tt.maDaiLy = int.Parse(maDaiLy_txt.Text);
dt_txt.Text = ttBus.getDienThoai(tt).ToString();
diaChi_txt.Text = ttBus.getDiaChi(tt);
Email_txt.Text = ttBus.getEmail(tt);
tenDaiLy_txt.Text = ttBus.getTen(tt);
}
catch (Exception ex)
{
MessageBox.Show("Mã đại lý không tồn tại");
}
}
else
{
ClearAllDisableBox();
}
}
public bool xoa(ThuTienDTO tt)
{
string query = string.Empty;
query += "DELETE FROM PhieuThuTien WHERE [id] = @id";;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@id", tt.ID);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
public List <ThuTienDTO> select()
{
string query = string.Empty;
query += "SELECT [ht], [bienso], [sdt], [ngaythutien], [sotienthu], [email], [id]";
query += "FROM [PhieuThuTien]";
List <ThuTienDTO> lsThuTien = new List <ThuTienDTO>();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
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())
{
ThuTienDTO tt = new ThuTienDTO();
tt.HT = reader["ht"].ToString();
tt.Bienso = reader["bienso"].ToString();
tt.Sdt = reader["sdt"].ToString();
tt.Ngaythutien = reader["ngaythutien"].ToString();
tt.Sotienthu = Decimal.Parse(reader["sotienthu"].ToString());
tt.Email = reader["email"].ToString();
tt.ID = int.Parse(reader["id"].ToString());
lsThuTien.Add(tt);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(lsThuTien);
}
private void xoa_btn_Click(object sender, EventArgs e)
{
ThuTienDTO tt = new ThuTienDTO();
if (maPhieuThuTien_txt.Text.Trim().Length != 0)
{
tt.maPhieuThuTien = int.Parse(maPhieuThuTien_txt.Text);
bool kq = ttBus.xoa(tt);
if (kq == true)
{
MessageBox.Show("Xóa thành công");
}
else
{
MessageBox.Show("Xóa thất bại");
}
PhieuThuTienGrid.DataSource = ttBus.loadPhieuThuTien();
}
}
private void Them_btn_Click(object sender, EventArgs e)
{
ThuTienDTO tt = new ThuTienDTO();
tt.maPhieuThuTien = int.Parse(maPhieuThuTien_txt.Text);
tt.maDaiLy = int.Parse(maDaiLy_txt.Text);
tt.ngayThuTien = ngayThuTien.Value;
tt.soTienThu = int.Parse(soTienThu_txt.Text);
bool kq = ttBus.them(tt);
if (kq == true)
{
MessageBox.Show("Thêm thành công");
}
else
{
MessageBox.Show("Thêm thất bại");
}
PhieuThuTienGrid.DataSource = ttBus.loadPhieuThuTien();
}
public bool sua(ThuTienDTO tt)
{
string query = string.Empty;
query += "Update [PhieuThuTien] set ";
query += "[ngayThuTien] = @ngayThuTien, ";
query += "[maDaiLy] = @maDaiLy, ";
query += "[soTienThu] = @soTienThu, ";
query += "[tienNo] = (select [DaiLy].[tienNo] from [DaiLy] where [DaiLy].[maDaiLy] = @maDaiLy)";
query += " where [maPhieuThuTien] = @maPhieuThuTien";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maDaiLy", tt.maDaiLy);
cmd.Parameters.AddWithValue("@maPhieuThuTien", tt.maPhieuThuTien);
cmd.Parameters.AddWithValue("@soTienThu", tt.soTienThu);
cmd.Parameters.AddWithValue("@ngayThuTien", tt.ngayThuTien);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public bool them(ThuTienDTO tt)
{
string query = string.Empty;
query += "Insert into [PhieuThuTien]([maPhieuThuTien],[maDaiLy], [ngayThuTien],[tienNo],[soTienThu])";
query += "values(@maPhieuThuTien,@maDaiLy , @ngayThuTien, (select [DaiLy].[tienNo] from [DaiLy] where [DaiLy].[maDaiLy] = @maDaiLy) ,@soTienThu)";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maPhieuThuTien", tt.maPhieuThuTien);
cmd.Parameters.AddWithValue("@maDaiLy", tt.maDaiLy);
cmd.Parameters.AddWithValue("@ngayThuTien", tt.ngayThuTien);
cmd.Parameters.AddWithValue("@soTienThu", tt.soTienThu);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public bool them(ThuTienDTO tt)
{
string query = string.Empty;
query += "INSERT INTO [PhieuThuTien] (ht, bienso, sdt, ngaythutien, sotienthu, email, id)";
query += "VALUES (@ht,@bienso,@sdt,@ngaythutien,@sotienthu,@email,@id)";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ht", tt.HT);
cmd.Parameters.AddWithValue("@bienso", tt.Bienso);
cmd.Parameters.AddWithValue("@sdt", tt.Sdt);
cmd.Parameters.AddWithValue("@ngaythutien", tt.Ngaythutien);
cmd.Parameters.AddWithValue("@sotienthu", tt.Sotienthu);
cmd.Parameters.AddWithValue("@email", tt.Email);
cmd.Parameters.AddWithValue("@id", tt.ID);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
public string getEmail(ThuTienDTO tt)
{
string email = string.Empty;
string query = string.Empty;
query += "Select [DaiLy].[email] from [DaiLy] where [DaiLy].[maDaiLy] = @maDaiLy";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maDaiLy", tt.maDaiLy);
con.Open();
email = (string)cmd.ExecuteScalar();
con.Close();
con.Dispose();
}
}
return(email);
}
public int getDienThoai(ThuTienDTO tt)
{
int dt = new int();
string query = string.Empty;
query += "Select [DaiLy].[dienThoai] from [DaiLy] where [DaiLy].[maDaiLy] = @maDaiLy";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maDaiLy", tt.maDaiLy);
con.Open();
dt = (int)cmd.ExecuteScalar();
con.Close();
con.Dispose();
}
}
return(dt);
}
public bool sua(ThuTienDTO tt)
{
string query = string.Empty;
query += "UPDATE PhieuThuTien SET [ht] = @ht, [bienso] = @bienso, [sdt] = @sdt, [ngaythutien] = @ngaythutien, [sotienthu] = @sotienthu, [email] = @email WHERE [id] = @id";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ht", tt.HT);
cmd.Parameters.AddWithValue("@bienso", tt.Bienso);
cmd.Parameters.AddWithValue("@sdt", tt.Sdt);
cmd.Parameters.AddWithValue("@ngaythutien", tt.Ngaythutien);
cmd.Parameters.AddWithValue("@sotienthu", tt.Sotienthu);
cmd.Parameters.AddWithValue("@email", tt.Email);
cmd.Parameters.AddWithValue("@id", tt.ID);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
public bool xoa(ThuTienDTO tt)
{
bool re = ttDAL.xoa(tt);
return(re);
}
public string getEmail(ThuTienDTO tt)
{
return(ttDAL.getEmail(tt));
}
public string getTen(ThuTienDTO tt)
{
return(ttDAL.getTenDaiLy(tt));
}
public string getDiaChi(ThuTienDTO tt)
{
return(ttDAL.getDiaChi(tt));
}
public bool sua(ThuTienDTO tt)
{
bool re = ttDao.sua(tt);
return(re);
}
public bool them(ThuTienDTO tt)
{
bool re = ttDao.them(tt);
return(re);
}
private void btSua_Click(object sender, EventArgs e)
{
string email = @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*";
Regex reg = new Regex(email);
bool match = reg.IsMatch(this.txtEmail.Text);
errorProvider1.Clear();
errorProvider2.Clear();
errorProvider4.Clear();
errorProvider5.Clear();
errorProvider6.Clear();
if (txtHT.Text == "")
{
errorProvider1.SetError(txtHT, "Tên khách hàng không được để trống!");
}
else if (txtBienso.Text == "")
{
errorProvider2.SetError(txtBienso, "Biển số xe không được để trống!");
}
else if (txtSotienthu.Text == "")
{
errorProvider3.SetError(txtSotienthu, "Số tiền thu không được để trống!");
}
else if (txtSdt.Text == "")
{
errorProvider4.SetError(txtSdt, "Số điện thoại không được để trống!");
}
else if (txtSdt.Text.Length > 11 || txtSdt.Text.Length < 9)
{
errorProvider5.SetError(txtSdt, "Số điện thoại không hợp lệ!");
}
else if (txtEmail.Text == "")
{
errorProvider6.SetError(txtEmail, "Email không được để trống!");
}
else if (match == false)
{
errorProvider7.SetError(txtEmail, "Email có định dạng sai");
}
else
{
ThuTienDTO tt = new ThuTienDTO();
tt.ID = int.Parse(txtID.Text);
tt.HT = txtHT.Text;
tt.Ngaythutien = dtNgaythutien.Text;
tt.Sotienthu = Decimal.Parse(txtSotienthu.Text);
tt.Email = txtEmail.Text;
tt.Sdt = txtSdt.Text;
tt.Bienso = txtBienso.Text;
bool kq1 = ttBus.sua(tt);
if (kq1 == false)
{
MessageBox.Show("Sửa thông tin thất bại. Vui lòng kiểm tra lại dữ liệu");
}
else
{
MessageBox.Show("Sửa thông tin thành công");
}
this.loadData_Vao_GridView();
}
}
public int getDienThoai(ThuTienDTO tt)
{
int re = ttDAL.getDienThoai(tt);
return(re);
}
