public bool Insert(HopDong hopDong)
{
try
{
string query = "SELECT * FROM HopDong";
SqlDataAdapter dataAdapter = new SqlDataAdapter(query, connection);
DataTable table = GetData();
DataRow row = table.NewRow();
row["tenHopDong"] = hopDong.TenHopDong;
row["tuNgay"] = hopDong.TuNgay;
row["denNgay"] = hopDong.DenNgay;
row["thoiHanTheoNam"] = hopDong.ThoiHanTheoNam;
row["thoiHanTheoThang"] = hopDong.ThoiHanTheoThang;
row["noiDung"] = hopDong.NoiDung;
row["DoiTuongId"] = hopDong.DoiTuongId;
table.Rows.Add(row);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(table);
return(true);
}
catch
{
return(false);
}
}
public List <HopDong> DSHopDong()
{
SqlDataReader reader = ReadData("select * from HopDong");
List <HopDong> dsHD = new List <HopDong>();
while (reader.Read())
{
string mahd = reader.GetString(0);
DateTime ngaythue = reader.GetDateTime(1);
DateTime ngaytra = reader.GetDateTime(2);
string maphong = reader.GetString(3);
string chuthich = reader.GetString(4);
string cmnd = reader.GetString(5);
double coc = reader.GetDouble(6);
HopDong hd = new HopDong();
hd.MaHopDong = mahd;
hd.NgayThue = ngaythue;
hd.NgayTra = ngaytra;
hd.MaPhong = maphong;
hd.ChuThich = chuthich;
hd.CMND = cmnd;
hd.Coc = coc;
dsHD.Add(hd);
}
reader.Close();
return(dsHD);
}
public bool Update(HopDong hopDong)
{
try
{
string query = "SELECT * FROM HopDong";
SqlDataAdapter dataAdapter = new SqlDataAdapter(query, connection);
table = GetData();
table.PrimaryKey = new DataColumn[] { table.Columns[0] };
DataRow row = table.Rows.Find(hopDong.HopDongId);
if (row != null)
{
row["tenHopDong"] = hopDong.TenHopDong;
row["tuNgay"] = hopDong.TuNgay;
row["denNgay"] = hopDong.DenNgay;
row["thoiHanTheoNam"] = hopDong.ThoiHanTheoNam;
row["thoiHanTheoThang"] = hopDong.ThoiHanTheoThang;
row["noiDung"] = hopDong.NoiDung;
row["DoiTuongId"] = hopDong.DoiTuongId;
}
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(table);
return(true);
}
catch
{
return(false);
}
}
public bool ThemHopDong(HopDong hd)
{
OpenConnection();
string sql = "insert into HopDong values(@mahd, @ngaythue, @ngaytra, @maphong, @chuthich, @cmnd, @coc)";
SqlParameter parMaHD = new SqlParameter("@mahd", SqlDbType.VarChar);
parMaHD.Value = hd.MaHopDong;
SqlParameter parMaPhong = new SqlParameter("@maphong", SqlDbType.VarChar);
parMaPhong.Value = hd.MaPhong;
SqlParameter parCMND = new SqlParameter("@cmnd", SqlDbType.VarChar);
parCMND.Value = hd.CMND;
SqlParameter parNgayThue = new SqlParameter("@ngaythue", SqlDbType.DateTime);
parNgayThue.Value = hd.NgayThue;
SqlParameter parNgayTra = new SqlParameter("@ngaytra", SqlDbType.DateTime);
parNgayTra.Value = hd.NgayTra;
SqlParameter parChuThich = new SqlParameter("@chuthich", SqlDbType.NVarChar);
parChuThich.Value = hd.ChuThich;
SqlParameter parCoc = new SqlParameter("@coc", SqlDbType.Float);
parCoc.Value = hd.Coc;
bool kq = WriteData(sql, new[] { parMaHD, parNgayThue, parNgayTra, parMaPhong, parChuThich, parCMND, parCoc });
return(kq);
}
//có sử dụng cho frmHopDong Tìm
public List <HopDong> LayThongTinHopDongMaHopDong(string chuoi)
{
string sql = "select * from HopDong where MaHopDong = @mahd or MaPhong like N'%@map%'";
List <HopDong> dshd = new List <HopDong>();
SqlParameter parMaHopDong = new SqlParameter("@mahd", SqlDbType.VarChar);
parMaHopDong.Value = chuoi;
SqlParameter parMaPhong = new SqlParameter("@map", SqlDbType.VarChar);
parMaPhong.Value = chuoi;
SqlDataReader reader = ReadDataPars(sql, new[] { parMaHopDong });
if (reader.Read())
{
HopDong hd = new HopDong();
hd.MaHopDong = reader.GetString(0);
hd.MaPhong = reader.GetString(1);
hd.NgayThue = reader.GetDateTime(3);
hd.NgayTra = reader.GetDateTime(4);
hd.Coc = reader.GetDouble(2);
dshd.Add(hd);
}
reader.Close();
return(dshd);
}
public List <HopDong> ViewHopDong(string sql)
{
try
{
List <HopDong> list = new List <HopDong>();
SqlDataReader dr = p.View(sql);
if (dr != null)
{
string MaHD;
string LoaiHopDong, KyHan;
while (dr.Read())
{
MaHD = dr.GetString(0);
LoaiHopDong = dr.GetString(1);
KyHan = dr.GetString(2);
HopDong hd = new HopDong(MaHD, LoaiHopDong, KyHan);
list.Add(hd);
}
}
return(list);
}
catch (SqlException p)
{
throw p;
}
}
public bool ThemHopDong(HopDong pHopDong)
{
string query = "INSERT INTO dbo.HOPDONG_QUANGCAO " +
"(TENDOITAC, NGAYLAP, VITRIDANGQC,THOIHAN) " +
"VALUES (@tendoitac, @ngaylap , @vitridang, @thoihan) ";
try
{
SqlConnection conn = DBUtils.GetDBConnection();
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@tendoitac", SqlDbType.VarChar).Value = pHopDong.TenDoiTac;
cmd.Parameters.Add("@ngaylap", SqlDbType.Date).Value = pHopDong.NgayLap;
cmd.Parameters.Add("@vitridang", SqlDbType.Text).Value = pHopDong.ViTriDang;
cmd.Parameters.Add("@thoihan", SqlDbType.Int).Value = pHopDong.ThoiHan;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
}
public int UpdateHopDong(HopDong hopdong)
{
List <SqlParameter> paras = new List <SqlParameter>();
paras.Add(new SqlParameter("@mahopdong", hopdong.MaHD));
paras.Add(new SqlParameter("@loaihopdong", hopdong.LoaiHopDong));
paras.Add(new SqlParameter("@kyhan", hopdong.KyHan));
try
{
return(p.ExecNonQuery("updatehopdong", CommandType.StoredProcedure, paras));
}
catch (SqlException p)
{
throw p;
}
}
//lấy mã Phòng, mã hop73 đồng, ngày trả, ngày thuê theo cmnd đưa vào
public HopDong LayThongTinKH(string cm)
{
string sql = "select * from HopDong where CMND=@cm";
HopDong hd = new HopDong();
SqlParameter parCM = new SqlParameter("@cm", SqlDbType.VarChar);
parCM.Value = cm;
SqlDataReader reader = ReadDataPars(sql, new[] { parCM });
if (reader.Read())
{
hd.MaPhong = reader.GetString(3);
hd.MaHopDong = reader.GetString(0);
hd.NgayThue = reader.GetDateTime(1);
hd.NgayTra = reader.GetDateTime(2);
}
return(hd);
}
public HopDong LayThongTinHopDong(string mhd)
{
string sql = "select * from HopDong where MaHopDong = @mhd";
SqlParameter parMaHopDong = new SqlParameter("@mhd", SqlDbType.VarChar);
parMaHopDong.Value = mhd;
HopDong hd = new HopDong();
SqlDataReader reader = ReadDataPars(sql, new[] { parMaHopDong });
if (reader.Read())
{
hd.MaHopDong = reader.GetString(0);
hd.MaPhong = reader.GetString(1);
hd.NgayThue = reader.GetDateTime(3);
hd.NgayTra = reader.GetDateTime(4);
hd.Coc = reader.GetDouble(2);
}
reader.Close();
return(hd);
}
public List <HopDong> TaiDSHopDong()
{
string sql = "select * from HopDong";
SqlDataReader reader = ReadData(sql);
List <HopDong> dshd = new List <HopDong>();
while (reader.Read())
{
HopDong hd = new HopDong();
hd.MaHopDong = reader.GetString(0);
hd.MaPhong = reader.GetString(1);
hd.NgayThue = reader.GetDateTime(3);
hd.NgayTra = reader.GetDateTime(4);
hd.Coc = reader.GetDouble(2);
dshd.Add(hd);
}
reader.Close();
return(dshd);
}
public bool SuaHopDong(HopDong hd)
{
OpenConnection();
string sql = "update HopDong set NgayThue = @nthue, NgayTra = @nt, Coc = @coc, MaPhong = @mp where MaHopDong = @mhd";
SqlParameter parMaPhong = new SqlParameter("@mp", SqlDbType.VarChar);
parMaPhong.Value = hd.MaPhong;
SqlParameter parCoc = new SqlParameter("@coc", SqlDbType.Float);
parCoc.Value = hd.Coc;
SqlParameter parNgayThue = new SqlParameter("@nthue", SqlDbType.DateTime);
parNgayThue.Value = hd.NgayThue;
SqlParameter parNgayTra = new SqlParameter("@nt", SqlDbType.DateTime);
parNgayTra.Value = hd.NgayTra;
SqlParameter parMaHopDong = new SqlParameter("@mhd", SqlDbType.VarChar);
parMaHopDong.Value = hd.MaHopDong;
bool kq = WriteData(sql, new[] { parNgayThue, parNgayTra, parCoc, parMaPhong, parMaHopDong });
return(kq);
}
