public void Sua_TongTien(PhieuNhap pn)
{
string sql = "update PhieuNhap set TongTien=@TongTien where MaPhieuNhap=@MaPhieuNhap";
SqlParameter maphieunhap = new SqlParameter("@MaPhieuNhap", pn.MaPhieuNhap);
SqlParameter tongtien = new SqlParameter("@TongTien", pn.TongTien);
SqlDatabase.ExecuteNonQuery(sql, CommandType.Text, maphieunhap, tongtien);
}
public List<PhieuNhap> TimKiem(PhieuNhap pn)
{
List<PhieuNhap> data = new List<PhieuNhap>();
string sql = "select PhieuNhap_ID,MaPhieuNhap,pn.NhaSX_ID,TenNhaSX,NgayNhap,pn.TongTien"
+ " from PhieuNhap pn,NhaSX nsx where pn.NhaSX_ID=nsx.NhaSX_ID"
+ " and (MaPhieuNhap=@MaPhieuNhap or pn.NhaSX_ID=@NhaSX_ID or NgayNhap=@NgayNhap)";
SqlParameter maphieunhap = new SqlParameter("@MaPhieuNhap", pn.MaPhieuNhap);
SqlParameter nhasxid = new SqlParameter("@NhaSX_ID", pn.NhaSX_ID);
SqlParameter ngaynhap = new SqlParameter("@NgayNhap", pn.NgayNhap);
SqlDataReader rd = SqlDatabase.ExecuteQueryWithDataReader(sql, CommandType.Text, maphieunhap, nhasxid, ngaynhap);
if (rd.HasRows)
{
while (rd.Read())
{
pn = new PhieuNhap()
{
PhieuNhap_ID = int.Parse(rd[0].ToString()),
MaPhieuNhap = rd[1].ToString(),
NhaSX_ID = int.Parse(rd[2].ToString()),
TenNhaSX = rd[3].ToString(),
NgayNhap = DateTime.Parse(rd[4].ToString()),
TongTien = float.Parse(rd[5].ToString())
};
data.Add(pn);
}
}
return data;
}
public void Them(PhieuNhap pn)
{
string sql = "insert into PhieuNhap values(@MaPhieuNhap,@NhaSX_ID,@NgayNhap,@TongTien)";
SqlParameter maphieunhap = new SqlParameter("@MaPhieuNhap", pn.MaPhieuNhap);
SqlParameter nhasxid = new SqlParameter("@NhaSX_ID", pn.NhaSX_ID);
SqlParameter ngaynhap = new SqlParameter("@NgayNhap", pn.NgayNhap);
SqlParameter tongtien = new SqlParameter("@TongTien", pn.TongTien);
SqlDatabase.ExecuteNonQuery(sql, CommandType.Text, maphieunhap, nhasxid, ngaynhap, tongtien);
}
public List<PhieuNhap> HienThiPhieuNhap()
{
List<PhieuNhap> data = new List<PhieuNhap>();
string sql = "select PhieuNhap_ID,MaPhieuNhap,pn.NhaSX_ID,TenNhaSX,NgayNhap,pn.TongTien from PhieuNhap pn,NhaSX nsx where pn.NhaSX_ID=nsx.NhaSX_ID";
SqlDataReader rd = SqlDatabase.ExecuteQueryWithDataReader(sql, CommandType.Text);
if (rd.HasRows)
{
while (rd.Read())
{
PhieuNhap pn = new PhieuNhap()
{
PhieuNhap_ID = int.Parse(rd[0].ToString()),
MaPhieuNhap = rd[1].ToString(),
NhaSX_ID = int.Parse(rd[2].ToString()),
TenNhaSX = rd[3].ToString(),
NgayNhap = DateTime.Parse(rd[4].ToString()),
TongTien = float.Parse(rd[5].ToString())
};
data.Add(pn);
}
}
return data;
}
public DataSet ThongTinNhapSP(PhieuNhap pn)
{
CtPhieuNhapService1 dal = new CtPhieuNhapService1();
string mapn = pn.MaPhieuNhap.ToString();
string sql = "";
bool kt = dal.KiemTraMaPhieuNhap(mapn);
if (kt == true)
{
sql = string.Format("select SP_ID,TenSP,NhaSX_ID from SanPham where NhaSX_ID=@NhaSX_ID"
+ " and SP_ID not in"
+ " (select ct_pn.SP_ID from SanPham sp, PhieuNhap pn, CT_PhieuNhap ct_pn"
+ " where pn.MaPhieuNhap=ct_pn.MaPhieuNhap and ct_pn.SP_ID=sp.SP_ID"
+ " and ct_pn.MaPhieuNhap='{0}')", mapn);
}
else
{
sql = "select SP_ID,TenSP,NhaSX_ID from SanPham where NhaSX_ID=@NhaSX_ID";
}
SqlParameter nhasxid = new SqlParameter("@NhaSX_ID", pn.NhaSX_ID);
return(SqlDatabase.ExecuteQueryWithDataSet(sql, CommandType.Text, nhasxid));
}
public List <PhieuNhap> HienThiPhieuNhap()
{
List <PhieuNhap> data = new List <PhieuNhap>();
string sql = "select PhieuNhap_ID,MaPhieuNhap,pn.NhaSX_ID,TenNhaSX,NgayNhap,pn.TongTien from PhieuNhap pn,NhaSX nsx where pn.NhaSX_ID=nsx.NhaSX_ID";
SqlDataReader rd = SqlDatabase.ExecuteQueryWithDataReader(sql, CommandType.Text);
if (rd.HasRows)
{
while (rd.Read())
{
PhieuNhap pn = new PhieuNhap()
{
PhieuNhap_ID = int.Parse(rd[0].ToString()),
MaPhieuNhap = rd[1].ToString(),
NhaSX_ID = int.Parse(rd[2].ToString()),
TenNhaSX = rd[3].ToString(),
NgayNhap = DateTime.Parse(rd[4].ToString()),
TongTien = float.Parse(rd[5].ToString())
};
data.Add(pn);
}
}
return(data);
}
public DataSet ThongTinNhapSP(PhieuNhap pn)
{
CtPhieuNhapService1 dal = new CtPhieuNhapService1();
string mapn = pn.MaPhieuNhap.ToString();
string sql = "";
bool kt = dal.KiemTraMaPhieuNhap(mapn);
if (kt == true)
{
sql = string.Format("select SP_ID,TenSP,NhaSX_ID from SanPham where NhaSX_ID=@NhaSX_ID"
+ " and SP_ID not in"
+ " (select ct_pn.SP_ID from SanPham sp, PhieuNhap pn, CT_PhieuNhap ct_pn"
+ " where pn.MaPhieuNhap=ct_pn.MaPhieuNhap and ct_pn.SP_ID=sp.SP_ID"
+ " and ct_pn.MaPhieuNhap='{0}')", mapn);
}
else
{
sql = "select SP_ID,TenSP,NhaSX_ID from SanPham where NhaSX_ID=@NhaSX_ID";
}
SqlParameter nhasxid = new SqlParameter("@NhaSX_ID", pn.NhaSX_ID);
return SqlDatabase.ExecuteQueryWithDataSet(sql, CommandType.Text, nhasxid);
}