public string InsertHDB(string _MaKH, DateTime _ngayban, long _tongtien)
{
string str = " ";
string sql = "InsertHDB";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaKH", _MaKH);
cmd.Parameters.AddWithValue("@NgayBan", _ngayban);
cmd.Parameters.AddWithValue("@TongTien", _tongtien);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
str = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return(str);
}
public string InsertSanPham(EC_SANPHAM et)
{
string sql = "ThemSP";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tensp", et.TenSP);
cmd.Parameters.AddWithValue("@malh", et.MaLH);
cmd.Parameters.AddWithValue("@soluong", et.SoLuong);
cmd.Parameters.AddWithValue("@loinhuan", et.LoiNhuan);
cmd.Parameters.AddWithValue("@gianhap", et.GiaNhap);
cmd.Parameters.AddWithValue("@mota", et.MoTa);
cmd.Parameters.AddWithValue("@nsx", et.NSX);
cmd.Parameters.AddWithValue("@hinhanh", et.HinhAnh);
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
string ma = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return(ma);
}
public string InsertHDN(EC_HOADONNHAP et)
{
string sql = "ThemHDN";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@mancc", et.MaNCC);
cmd.Parameters.AddWithValue("@ngaynhap", et.NgayNhap);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
string ma = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
if (ma != null)
{
return(ma);
}
return("error");
}
public string InsertHangHoa(string tenhh, int soluong, long giannhap, long giaxuat, string nsx, string thongtin)
{
string sql = "ThemHH";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tenhh", tenhh);
cmd.Parameters.AddWithValue("@soluong", soluong);
cmd.Parameters.AddWithValue("@gianhap", giannhap);
cmd.Parameters.AddWithValue("@giaxuat", giaxuat);
cmd.Parameters.AddWithValue("@nsx", nsx);
cmd.Parameters.AddWithValue("@thongtin", thongtin);
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
string ma = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return(ma);
}
public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
{
string str = "";
string sql = "InsertKH";
// string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenKH", TenKH);
cmd.Parameters.AddWithValue("@gt", Gioitinh);
cmd.Parameters.AddWithValue("@DC", Diachi);
cmd.Parameters.AddWithValue("@SDT", SDT);
cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
cmd.Parameters.AddWithValue("@ghichu", Ghichu);
//cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
str = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return(str);
}
//public DataTable PhieuNhap(string _MaNCC)
//{
// string sql = "PhieuNhap";
// DataTable dt = new DataTable();
// SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
// con.Open();
// SqlCommand cmd = new SqlCommand(sql, con);
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@mancc", _MaNCC);
// SqlDataAdapter ad = new SqlDataAdapter(cmd);
// ad.Fill(dt);
// return dt;
//}
public string InsertPN(string mancc, DateTime ngaynhap)
{
string sql = "ThemPN";
SqlConnection conn = new SqlConnection(KetNoiDB.getconnect());
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@mancc", mancc);
cmd.Parameters.AddWithValue("@ngaynhap", ngaynhap);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
string ma = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
conn.Close();
if (ma != null)
{
return(ma);
}
return("error");
}
public DataTable ShowLoaiHang()
{
string sql = @"select * from LOAIHANG ";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable HienThiKhachHang()
{
string sql = @"SELECT * FROM CHINHANH ";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable ShowPN(string DieuKien)
{
string sql = @"SELECT MaPN, MaNCC, NgayNhap FROM dbo.PHIEUNHAP " + DieuKien;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(dt);
return(dt);
}
public DataTable ShowCTHD(string DieuKien)
{
string sql = @"SELECT MaSP, SoLuong, DonGia, ThanhTien FROM dbo.CHITIETHOADONNHAP " + DieuKien;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable ShowCTPX(string DieuKien)
{
string sql = @"SELECT MaHH, SoLuong, DonGia, ThanhTien FROM CHITIETPHIEUXUAT " + DieuKien;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable ShowPX(string DieuKien)
{
string sql = @"SELECT MaPX, MaCN, NgayXuat FROM dbo.PHIEUXUAT " + DieuKien;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable DisplayCustomers()
{
string sql = @"SELECT * FROM dbo.CHINHANH ";
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(dt);
return(dt);
}
public DataTable ShowPBH()
{
string sql = @"SELECT * FROM PHIEUBAOHANH ";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable HienThiTien(string DieuKien)
{
string sql = @"SELECT TongTien FROM PHIEUXUAT WHERE MaPX = '" + DieuKien + "'";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable HienThi(string DieuKien)
{
string sql = @"SELECT * FROM CHITIETPHIEUNHAP WHERE MaPN = '" + DieuKien + "'";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable ShowCTPN(string DieuKien)
{
string sql = @"SELECT MaHH, SoLuong, DonGia, ThanhTien FROM dbo.CHITIETPHIEUNHAP " + DieuKien;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(dt);
return(dt);
}
public DataTable HienThiNhanVien()
{
string sql = "SELECT * FROM NHANVIEN ";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
return(dt);
}
public DataTable ShowLoaiHang()
{
string sql = @"SELECT * FROM LOAIHANG";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
return(dt);
}
public DataTable ShowNCC(string DieuKien)
{
string sql = @"SELECT * FROM NHACUNGCAP " + DieuKien;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable ShowHangHoa(string DieuKien)
{
string sql = @"SELECT MaHH, TenHH, SoLuong, GiaNhap, GiaXuat, NSX, ThongTin
FROM HangHoa " + DieuKien;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public void SuaSP(string _MaSP, int _SoLg)
{
string str = @"UPDATE SANPHAM SET SoLuong = " + _SoLg + " WHERE MaSP = N'" + _MaSP + "'";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
public DataTable ShowSanPham(string DieuKien)
{
string sql = @"SELECT MaSP, TenSP, SoLuong, LoiNhuan, GiaNhap, GiaBan, MoTa, NSX, TenLH
FROM SANPHAM sp, LOAIHANG lh
WHERE sp.MaLH = lh.MaLH " + DieuKien;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
return(dt);
}
public DataTable HienThiKhachHang()
{
string sql = "select * from KhachHang";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
return(dt);
}
public DataTable LaySP(string _MaSP)
{
DataTable dt = new DataTable();
string str = @"SELECT * FROM SANPHAM WHERE MaSP = " + _MaSP;
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlDataAdapter ad = new SqlDataAdapter(str, con);
ad.Fill(dt);
con.Close();
return(dt);
}
public void UpdateHDB(string _MaHDB)
{
string str = "UpdateHDB";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaHDB", _MaHDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
public void XoaNhanVien(string TenDN)
{
string sql = "Xoa_NV";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenDN", TenDN);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
public void addTien(string MaPN)
{
string sql = "TienNhap";
SqlConnection conn = new SqlConnection(KetNoiDB.getconnect());
conn.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaPN", MaPN);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
public bool DangNhapHT(string Username, string Pass)
{
string sql = "SELECT * FROM DANGNHAP WHERE UserName='******' AND Pass='******'";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
public bool Login(string _Name, string _Pass)
{
string sql = @"select * from DANGNHAP WHERE Username = '******' AND Pass = '******'";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
SqlDataAdapter ad = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable();
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
