//huy
public static DataTable LayDSHoaDon()
{
string sql = "select hd.id as 'Số HĐ', kh.name_cus as 'Tên KH',kh.address_cus as 'Địa Chỉ',kh.phone as 'SĐT', hd.date_delivery as 'Ngày Lập', hd.date_order as 'Ngày giao', hd.total as 'Tổng Tiền' from Bill hd, Customer kh where hd.id_cus = kh.id";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
//lấy mã hóa đơn tự động tăng
public static string MaTuTang()
{
string sql = @"select * from Bill";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
string maTuTang = "";
if (dt.Rows.Count <= 0)
{
maTuTang = "BL0001";
}
else
{
int k;
maTuTang = "BL";
k = Convert.ToInt32(dt.Rows[dt.Rows.Count - 1][0].ToString().Substring(2, 4));
k = k + 1;
if (k < 10)
{
maTuTang = maTuTang + "000";
}
else if (k < 100)
{
maTuTang = maTuTang + "00";
}
else if (k < 1000)
{
maTuTang = maTuTang + "0";
}
maTuTang = maTuTang + k.ToString();
}
return(maTuTang);
}
//huy
public static DataTable LayDSCTHD(string soHD)
{
string sql = string.Format("select * from BillDetails where id_bill = '{0}'", soHD);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable LayDanhSachTBTheoMaLoai(string maLoai)
{
string sql = string.Format("select tb.id as 'Mã TB', tb.name_pro as 'Tên Thiết Bị', tb.Price as 'Đơn Giá', tb.qty as 'Số Lượng', c.name_cate as 'Loại TB' from Product tb, Category c where tb.id_cate = c.id and tb.id_cate = '{0}'", maLoai);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
//lay dstb
public static DataTable LayDanhSachTB()
{
string sql = "select tb.id as 'Mã TB', tb.name_pro as 'Tên Thiết Bị', tb.Price as 'Đơn Giá', tb.qty as 'Số Lượng', c.name_cate as 'Loại TB' from Product tb, Category c where tb.id_cate = c.id";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable TraCuuThietBiTheoTen(string tenTB)
{
string sql = string.Format("select tb.id as 'Mã TB', tb.name_pro as 'Tên Thiết Bị', tb.Price as 'Đơn Giá', tb.qty as 'Số Lượng', c.name_cate as 'Loại TB' from Product tb, Category c where tb.name_pro like N'%{0}%' and tb.id_cate = c.id", tenTB);
DataTable kq = SqlDataAccessHelper.ExecuteQuery(sql);
return(kq);
}
public static DataTable LayDSCTHDTuMaHD(string maHD)
{
string sql = string.Format("select tb.name_pro as 'Tên TB', tb.price as 'Đơn Giá', ct.qty as 'Số Lượng', ct.discount as 'Chiết Khấu', ct.amount as 'Thành Tiền' from Product tb, BillDetails ct where tb.id = ct.id_pro and ct.id_bill = '{0}'", maHD);
//string sql = string.Format("select name_pro as 'Tên TB', price as 'Đơn Giá', qty as 'Số Lượng', discount as 'Chiết Khấu', ct.amount as 'Thành Tiền' from BillDetails ct, Product tb where ct.id_pro = tb.id and ct.id_bill = {0} ", maHD);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static string LaySoHoaDonTuMaKH(string maKH)
{
string soHD = "";
string sql = "select * from Bill where id_cus = " + maKH;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
soHD = dt.Rows[0]["id"].ToString();
}
return(soHD);
}
public static List <String> LayDSKHChuaThanhToan()
{
List <string> _ds = new List <string>();
string sql = "select * from Bill";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
string maKH = dt.Rows[i]["id_cus"].ToString();
_ds.Add(maKH);
}
return(_ds);
}
public static List <Category> LayDSLoaiThietBi()
{
List <Category> _ds = new List <Category>();
string sql = "select * from Category";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
Category loai = new Category();
loai.Id = dt.Rows[i]["id"].ToString();
loai.Name_cate = dt.Rows[i]["name_cate"].ToString();
_ds.Add(loai);
}
return(_ds);
}
public static List <Product> LayDSMaTBVaTenTBTheoMaLoai(string maLoai)
{
List <Product> ds = new List <Product>();
string sql = "select * from Product where id_cate = " + maLoai;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
Product td = new Product();
td.Id = dt.Rows[i]["id"].ToString();
td.Name_pro = dt.Rows[i]["name_pro"].ToString();
ds.Add(td);
}
return(ds);
}
//// huy
public static string LayMaLoaiTuTenLoai(string tenLoai)
{
string maLoai;
string sql = string.Format("select id from Category where name_cate = N'{0}'", tenLoai);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
maLoai = (dt.Rows[0]["id"].ToString());
}
else
{
return("");
}
return(maLoai);
}
public static bool KiemTraTenTBCapNhat(string tenTB, string maTB)
{
bool kq;
string sql = string.Format("select * from Product where name_pro = N'{0}' and id = '{1}'", tenTB, maTB);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
kq = false;
}
else
{
kq = true;
}
return(kq);
}
public static bool KiemTraTrungTenThietBi(string tenTB)
{
bool kq;
string sql = "select * from Product where name_pro = N'" + tenTB + "'";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
kq = false;
}
else
{
kq = true;
}
return(kq);
}
public static List <Product> LayDSThietBiTheoMaLoai(string maLoai)
{
List <Product> _ds = new List <Product>();
string sql = "select * from Product where id_cate = " + maLoai;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
Product td = new Product();
td.Id = dt.Rows[i]["id"].ToString();
td.Id_cate = dt.Rows[i]["id_cate"].ToString();
td.Name_pro = dt.Rows[i]["name_pro"].ToString();
td.Qty = int.Parse(dt.Rows[i]["qty"].ToString());
td.Price = int.Parse(dt.Rows[i]["price"].ToString());
_ds.Add(td);
}
return(_ds);
}