public static Boolean ThemPhieuThue(PhieuThueDTO pt)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sqlPara = "INSERT INTO phieuthue(maphieuthue, makhachhang, manhanvien, ngaylap, soluongphong)" +
" VALUES('" + pt.MaPhieuThue + "', '" + pt.MaKhachHang + "', '" + pt.MaNhanVien + "', '" + pt.NgayLap + "', '" + pt.SoLuongPhong + "'); ";
NpgsqlCommand cmd = new NpgsqlCommand(sqlPara, conn);
cmd.ExecuteNonQuery();
conn.Close();
//MessageBox.Show("okie");
return(true);
}
catch (Exception)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
return(false);
throw;
}
}
private void btnLapHoaDon_Them_Click(object sender, EventArgs e)
{
String maPhieuThue = cbLapHoaDon_MaPhieuThue.Text;
if (maPhieuThue != "")
{
dgv_LapHoaDon.DataSource = null;
PhieuThueDTO pt = PhieuThueBUS.layPT(maPhieuThue);
dspt.Add(pt);
TimeSpan d = DateTime.Now - pt.NgayThue;
thanhTien += pt.TienDV + pt.Phong.LoaiPhong.DonGia * d.Days;
tbLapHoaDon_ThanhTien.Text = thanhTien.ToString("00.00");
List <PhongDTO> dsp = new List <PhongDTO>();
foreach (PhieuThueDTO pth in dspt)
{
dsp.Add(pth.Phong);
}
dgv_LapHoaDon.DataSource = dspt;
dgv_LapHoaDon.Columns.Remove("Phong");
DataGridViewColumn column = new DataGridViewColumn();
column.CellTemplate = new DataGridViewTextBoxCell();
column.Name = "Phong";
column.ValueType = dsp[0].TenPhong.GetType();
dgv_LapHoaDon.Columns.Add(column);
for (int i = 0; i < dsp.Count; ++i)
{
dgv_LapHoaDon.Rows[i].Cells[dgv_LapHoaDon.Columns.Count - 1].Value = dsp[i].TenPhong;
}
}
else
{
MessageBox.Show("Bạn chưa chọn mã phiếu thuê.", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static List <PhieuThueDTO> layDSPT()
{
OleDbConnection link = null;
List <PhieuThueDTO> dsPhieu = new List <PhieuThueDTO>();
try
{
link = KetNoi();
string chuoiLenh = "select * from PhieuThue,Phong,LoaiPhong where PhieuThue.MaPhong=Phong.MaPhong and Phong.MaLoaiPhong=LoaiPhong.MaLoaiPhong";
OleDbCommand lenh = new OleDbCommand(chuoiLenh, link);
OleDbDataReader Doc = lenh.ExecuteReader();
while (Doc.Read())
{
PhieuThueDTO phieu = new PhieuThueDTO();
phieu.MaPhieuThue = Doc.GetString(0);
phieu.NgayThue = Doc.GetDateTime(2);
phieu.TienDV = Doc.GetInt32(3);
phieu.DaThanhToan = Doc.GetString(4);
PhongDTO phg = new PhongDTO();
phg.MaPhong = Doc.GetString(5);
phg.TenPhong = Doc.GetString(6);
phg.GhiChu = Doc.GetString(8);
phg.TinhTrang = Doc.GetString(9);
phg.SLKhach = Doc.GetInt32(10);
LoaiPhongDTO loai = new LoaiPhongDTO();
loai.MaLoaiPhong = Doc.GetInt32(11);
loai.TenLoaiPhong = Doc.GetString(12);
loai.DonGia = Doc.GetInt32(13);
loai.SLKhachToiDa = Doc.GetInt32(14);
phg.LoaiPhong = loai;
phieu.Phong = phg;
dsPhieu.Add(phieu);
}
for (int i = 0; i < dsPhieu.Count; i++)
{
List <KhachHangDTO> dskh = new List <KhachHangDTO>();
dskh = PhieuThueDAO.NhapDSKhach(dsPhieu[i].MaPhieuThue);
dsPhieu[i].DsKH = dskh;
}
}
catch (Exception ex)
{
dsPhieu = new List <PhieuThueDTO>();
}
finally
{
if (link != null && link.State == System.Data.ConnectionState.Open)
{
link.Close();
}
}
return(dsPhieu);
}
public static PhieuThueDTO layPT(string MPT)
{
OleDbConnection link = null;
PhieuThueDTO Phieu = new PhieuThueDTO();
try
{
link = KetNoi();
string chuoiLenh = "select * from PhieuThue,Phong,LoaiPhong where PhieuThue.MaPhong=Phong.MaPhong and Phong.MaLoaiPhong=LoaiPhong.MaLoaiPhong and MaPhieuThue=@MPT";
OleDbCommand lenh = new OleDbCommand(chuoiLenh, link);
OleDbParameter thamso = new OleDbParameter("@MPT", OleDbType.LongVarChar);
thamso.Value = MPT;
lenh.Parameters.Add(thamso);
OleDbDataReader Doc = lenh.ExecuteReader();
Doc.Read();
Phieu.MaPhieuThue = Doc.GetString(0);
Phieu.NgayThue = Doc.GetDateTime(2);
Phieu.TienDV = Doc.GetInt32(3);
Phieu.DaThanhToan = Doc.GetString(4);
PhongDTO phg = new PhongDTO();
phg.MaPhong = Doc.GetString(5);
phg.TenPhong = Doc.GetString(6);
phg.GhiChu = Doc.GetString(8);
phg.TinhTrang = Doc.GetString(9);
phg.SLKhach = Doc.GetInt32(10);
LoaiPhongDTO loai = new LoaiPhongDTO();
loai.MaLoaiPhong = Doc.GetInt32(11);
loai.TenLoaiPhong = Doc.GetString(12);
loai.DonGia = Doc.GetInt32(13);
loai.SLKhachToiDa = Doc.GetInt32(14);
phg.LoaiPhong = loai;
Phieu.Phong = phg;
List <KhachHangDTO> dskh = new List <KhachHangDTO>();
dskh = PhieuThueDAO.NhapDSKhach(Phieu.MaPhieuThue);
Phieu.DsKH = dskh;
}
catch (Exception ex)
{
Phieu = new PhieuThueDTO();
}
finally
{
if (link != null && link.State == System.Data.ConnectionState.Open)
{
link.Close();
}
}
return(Phieu);
}
public static PhieuThueDTO LayPhieuThueMoiNhat()
{
PhieuThueDTO phieuthue = new PhieuThueDTO();
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "SELECT * FROM phieuthue ORDER BY maphieuthue DESC LIMIT 1";
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
phieuthue.MaPhieuThue = reader[0].ToString();
phieuthue.MaKhachHang = reader[1].ToString();
phieuthue.MaNhanVien = reader[2].ToString();
phieuthue.NgayLap = DateTime.Parse(reader[3].ToString());
phieuthue.SoLuongPhong = Int32.Parse(reader[4].ToString());
if (reader[2].ToString() == "true")
{
phieuthue.TinhTrang = true;
}
else
{
phieuthue.TinhTrang = false;
}
}
conn.Close();
return(phieuthue);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
throw msg;
}
}
private void btnLuuPhieuThue_Click(object sender, RoutedEventArgs e)
{
Boolean kt = true;
// check validate
if (dschitietphieuthue.Count < 1)
{
MessageBox.Show("Phải thuê ít nhất một phòng");
return;
}
// prepare insert phieuthue
PhieuThueDTO pt = new PhieuThueDTO();
pt.MaPhieuThue = txtboxMaPhieuThue.Text.ToString();
pt.MaKhachHang = txtboxMaKhachHangPhieuThue.Text.ToString();
pt.MaNhanVien = nhanvien.MaNhanVien;
pt.NgayLap = DateTime.Today;
//pt.NgayLap = DateTime.ParseExact(Convert.ToDateTime(
// txtboxNgayLapPhieuThue.Text).ToString("dd/MM/yyyy"), "dd/MM/yyyy",
// CultureInfo.InvariantCulture);
pt.SoLuongPhong = dschitietphieuthue.Count;
pt.TinhTrang = true;
//insert phieuthue
kt = LapPhieuThueBUS.ThemPhieuThue(pt);
// insert danhsachphieuthue
kt = LapPhieuThueBUS.ThemDanhSachChiTietPhieuThue(dschitietphieuthue);
if (kt == false)
{
MessageBox.Show("Lập phiếu thuê không thành công");
// error did'n insert database
}
else
{
MessageBox.Show("Lập phiếu thuê thành công");
// dialog
// Lập phiếu thuê thành công
// shoe dialog insert success
}
}
public DataTable ConvertPhieuTHueDTOArrayToDataTable(PhieuThueDTO[] ptDTOArr)
{
DataTable dt = new DataTable();
int row = ptDTOArr.Length;
dt.Columns.Add("MaPhieuThue", typeof(string));
dt.Columns.Add("NgayThue", typeof(DateTime));
dt.Columns.Add("NgayTra", typeof(DateTime));
dt.Columns.Add("HeSo", typeof(float));
dt.Columns.Add("TienPhuThu", typeof(float));
dt.Columns.Add("TienThue", typeof(float));
dt.Columns.Add("MaPhong", typeof(string));
dt.Columns.Add("ThanhToan", typeof(string));
dt.Columns.Add("KhachHangDaiDien", typeof(string));
for (int i = 0; i < row; i++)
{
dt.Rows.Add(ptDTOArr[i].MaPhieuThue, ptDTOArr[i].NgayThue, ptDTOArr[i].NgayTra, ptDTOArr[i].Heso, ptDTOArr[i].Tienphuthu, ptDTOArr[i].Tienthue, ptDTOArr[i].MaPhong, ptDTOArr[i].ThanhToan, ptDTOArr[i].KhachHangDaiDien);
}
return dt;
}
public static List <ChiTietHoaDonDTO> layCTHD(string MHD)
{
OleDbConnection link = null;
List <ChiTietHoaDonDTO> dsChiTiet = new List <ChiTietHoaDonDTO>();
try
{
link = KetNoi();
string chuoiLenh = "select * from ChiTietHoaDon where MaHoaDon =@MHD";
OleDbCommand lenh = new OleDbCommand(chuoiLenh, link);
OleDbParameter thamso = new OleDbParameter("@MHD", OleDbType.LongVarChar);
thamso.Value = MHD;
lenh.Parameters.Add(thamso);
OleDbDataReader Doc = lenh.ExecuteReader();
while (Doc.Read())
{
ChiTietHoaDonDTO chitiet = new ChiTietHoaDonDTO();
chitiet.SoNgayThue = Doc.GetInt32(2);
chitiet.TienThue = Doc.GetInt32(3);
chitiet.TongCong = Doc.GetInt32(4);
string MPT = Doc.GetString(1);
PhieuThueDTO pt = new PhieuThueDTO();
pt = PhieuThueDAO.layPT(MPT);
chitiet.Phieuthue = pt;
dsChiTiet.Add(chitiet);
}
}
catch (Exception ex)
{
dsChiTiet = new List <ChiTietHoaDonDTO>();
}
finally
{
if (link != null && link.State == System.Data.ConnectionState.Open)
{
link.Close();
}
}
return(dsChiTiet);
}
public static NpgsqlDataAdapter TimKiemPhieuThue(PhieuThueDTO pt)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sql = "select phieuthue.maphieuthue, phieuthue.makhachhang, hoten, manhanvien, ngaylap, soluongphong from phieuthue, khachhang WHERE phieuthue.makhachhang = khachhang.makhachhang AND maphieuthue LIKE '%" + pt.MaPhieuThue + "%' AND phieuthue.makhachhang LIKE '%" + pt.MaKhachHang + "%' AND hoten LIKE '%" + pt.HoTenKhachHang + "%'";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
conn.Close();
return(da);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
throw msg;
}
}
private void LayDanhSachPhieuThueTimKiemTheoNgayLap()
{
PhieuThueDTO pt = new PhieuThueDTO();
pt.MaPhieuThue = txtboxMaPhieuThue.Text;
pt.MaKhachHang = txtboxMaKhachHangPhieuThue.Text;
pt.HoTenKhachHang = txtboxTenKhachHangPhieuThue.Text;
//pt.NgayLap = DateTime.Parse(txtboxNgayLapPhieuThue.Text);
//DateTime myDate = DateTime.ParseExact("2009-05-08 14:40:52,531", "yyyy-MM-dd HH:mm:ss,fff",
// System.Globalization.CultureInfo.InvariantCulture);
//pt.NgayLap = DateTime.ParseExact(txtboxNgayLapPhieuThue.Text, "d", null);
String s = dpkNgayLap.SelectedDate.ToString();
pt.NgayLap = DateTime.ParseExact(Convert.ToDateTime(dpkNgayLap.Text).ToString("dd/MM/yyyy"), "dd/MM/yyyy",
CultureInfo.InvariantCulture);
NpgsqlDataAdapter da = BUS.LapPhieuThueBUS.TimKiemPhieuThueTheoNgayLap(pt);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
dgvDanhSachPhieuThue.ItemsSource = dt.DefaultView;
// create point debug here
//while (true) ;
}
catch (Exception)
{
throw;
}
}
public static NpgsqlDataAdapter TimKiemPhieuThueTheoNgayLap(PhieuThueDTO pt)
{
return(LapPhieuThueDAO.TimKiemPhieuThueTheoNgayLap(pt));
}
public int InsertPhieuThue(PhieuThueDTO ptDTO)
{
return ws.InsertPhieuThue(ptDTO);
}
public bool Thanhtoan_Phieuthue(string maHD, string maphong, PhieuThueDTO dtoPT)
{
return ws.Thanhtoan_Phieuthue(maHD, maphong, dtoPT);
}
private void btThanhToan_Click(object sender, EventArgs e)
{
PhieuThueDTO dtoPT = new PhieuThueDTO();
if (kiemtra_click_item())
{
ListViewItem item = listViewHD.SelectedItems[0];
string thanhtoan = item.SubItems[4].Text.ToString();
if (thanhtoan == "0")
{
bool kt = ws.Thanhtoan_HD(mahd,tongtien);
for(int i=0;i<listViewCTHD.Items.Count;i++)
{
dtoPT.Songaythue = int.Parse(listViewCTHD.Items[i].SubItems[3].Text.ToString());
dtoPT.Heso =float.Parse(listViewCTHD.Items[i].SubItems[5].Text.ToString());
dtoPT.Tienphuthu =float.Parse(listViewCTHD.Items[i].SubItems[6].Text.ToString());
dtoPT.Tienthue =float.Parse(listViewCTHD.Items[i].SubItems[7].Text.ToString());
string maphong = listViewCTHD.Items[i].SubItems[0].Text.ToString();
bool kt2 = ws.Thanhtoan_Phong(maphong);
bool kt1 = ws.Thanhtoan_Phieuthue(mahd,maphong,dtoPT);
}
if (kt == true)
{
MessageBox.Show("Thanh toán hóa đơn " + mahd + " thành công!");
frmDSHoaDon_Load(null, null);
}
}
else
{
MessageBox.Show("Hóa đơn này đã được thanh toán");
}
}
}
private void btAdd_Click(object sender, EventArgs e)
{
if (!Kiemtrarong())
{
infoHD();
PhieuThueDTO dtoPT = new PhieuThueDTO();
// kiem tra xem hoa don nay co ton tai chi tiet hoa don ko
// new ko ton tai thi se ko cho lap hoa don
bool kt_CTHD = false;
for (int k = 0; k < listViewPhieuThue.Items.Count; k++)
{
if (listViewPhieuThue.Items[k].Checked == true)
{
kt_CTHD = true;
break;
}
}
if (kt_CTHD == true)
{
//lap hoa don
bool kt1 = ws.LapHoaDon(dtoHD);
//them vao bang CTHD
dtoCTHD.MaHoaDon = txt_MaHoaDon.Text.Trim();
for (int i = 0; i < listViewPhieuThue.Items.Count; i++)
{
if (listViewPhieuThue.Items[i].Checked == true)
{
dtoCTHD.MaPhieuThue = listViewPhieuThue.Items[i].SubItems[0].Text.ToString();
dtoCTHD.MaHoaDon = txt_MaHoaDon.Text.Trim();
bool kt = ws.InsertCTHOADON(dtoCTHD);
}
}
// update lai ngay tra thuc te
if (kt_CTHD == true)
{
DateTime ngaytra_thucte = DateTime.Parse(cmd_ngaytra_thucte.Text.ToString());
if (checkNgayTra_ThucTe.Checked == true)
{
int kt_update_ngaytra_thucte = ws.Update_ngaytra_thucte(dtoHD.MaHoaDon, ngaytra_thucte);
}
if (kt1 == true)
{
MessageBox.Show("Lập hóa đơn " + dtoCTHD.MaHoaDon + " thành công!");
frmHoaDonThanhToan_Load(null, null);
listViewPhieuThue.Items.Clear();
listViewCTPhong.Items.Clear();
checkmatudong.Checked = false;
}
}
}
else
{
MessageBox.Show("Bạn chưa chọn phòng cần thanh toán!");
}
}
else
{
MessageBox.Show("Bạn chưa điền đầy đủ thông tin");
}
}
public static Boolean ThemPhieuThue(PhieuThueDTO pt)
{
return(LapPhieuThueDAO.ThemPhieuThue(pt));
}
public int UpdatePhieuThue(PhieuThueDTO ptDTO)
{
return ws.UpdatePhieuThue(ptDTO);
}
