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); }