public static List <CTHDNhap_DTO> LoadCTHDNhap(String s)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("CTHDNhap_Load", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAHDNH", SqlDbType.Char).Value = s;
var dap = new SqlDataAdapter(cmd);
var dt = new DataTable();
dap.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
//Tao list HDNhap_DTP
List <CTHDNhap_DTO> lstcthdn = new List <CTHDNhap_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CTHDNhap_DTO cthdn = new CTHDNhap_DTO();
cthdn.SMaCTHDN = dt.Rows[i]["MACTHDNH"].ToString();
cthdn.SMaHDN = dt.Rows[i]["MAHDNH"].ToString();
cthdn.SMaSP = dt.Rows[i]["MASP"].ToString();
cthdn.STenSP = dt.Rows[i]["TENSP"].ToString();
cthdn.FGiaNhap = float.Parse(dt.Rows[i]["GIANHAP"].ToString());
cthdn.ISoLuong = int.Parse(dt.Rows[i]["SOLUONG"].ToString());
lstcthdn.Add(cthdn);
}
DataProvider.Dongketnoi(con);
return(lstcthdn);
}
public static List <HDNhaphang_DTO> LoadHDNhap()
{
//Khai bao truy cap SQL
con = DataProvider.Ketnoi();
DataTable dt = DataProvider.GetDataTable("HoaDonNhap_Load", con);
if (dt.Rows.Count == 0)
{
return(null);
}
//Tao list HDNhap_DTP
List <HDNhaphang_DTO> lsthdn = new List <HDNhaphang_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
HDNhaphang_DTO hdn = new HDNhaphang_DTO();
hdn.SMaHDN = dt.Rows[i]["MAHDNH"].ToString();
hdn.SMaNV = dt.Rows[i]["MANV"].ToString();
hdn.STenNV = dt.Rows[i]["TENNV"].ToString();
hdn.SMaNCC = dt.Rows[i]["MANCC"].ToString();
hdn.STenNCC = dt.Rows[i]["TENNCC"].ToString();
hdn.DtNgayNhap = dt.Rows[i]["NGAYNH"].ToString();
hdn.FTongTien = dt.Rows[i]["TONGTIEN"].ToString();
lsthdn.Add(hdn);
}
DataProvider.Dongketnoi(con);
return(lsthdn);
}
public static void XoaCTHDBH(string sMaCTHDBH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("CTHDBanHang_Delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MACTHDBH", SqlDbType.Char).Value = sMaCTHDBH;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static void XoaNV(string sMaNV)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("Nhanvien_Detele", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MANV", SqlDbType.Char).Value = sMaNV;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static void Update_Tongtien(Hoadonban_DTO HDBH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("HDBANHANG_Update_ThanhTien", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAHDBH", SqlDbType.Char).Value = HDBH.SMaHDB;
cmd.Parameters.Add("@THANHTIEN", SqlDbType.Int).Value = HDBH.LThanhtien;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static void SP_Update_SL(Sanpham_DTO SP)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("SANPHAM_CapnhatSL", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MASP", SqlDbType.Char).Value = SP.SMaSP;
cmd.Parameters.Add("@SOLUONG", SqlDbType.Int).Value = SP.SSoLuong;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static void UpdateSL_CTHDBH(string sMaCTHDBH, string sMaSP, int iSL)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("CTHDBanHang_UpdateSL", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MACTHDBH", SqlDbType.Char).Value = sMaCTHDBH;
cmd.Parameters.Add("@MASP", SqlDbType.Char).Value = sMaSP;
cmd.Parameters.Add("@SOLUONG", SqlDbType.Int).Value = iSL;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static void CapNhapTK(TaikhoanNV_DTO TK)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("TKNhanVien_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MATK", SqlDbType.Char).Value = TK.SMaTK;
cmd.Parameters.Add("@USERNAME", SqlDbType.Char).Value = TK.STenDangNhap;
cmd.Parameters.Add("@PASSWORD", SqlDbType.Char).Value = TK.SMatKhau;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static DataTable GetNCC()
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("NCC_SelectAll", con);
cmd.CommandType = CommandType.StoredProcedure;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
//Load TENKH va MAKH
public static DataTable Khachhang_LoadMaTen()
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("Khachhang_LoadMaTen", con);
cmd.CommandType = CommandType.StoredProcedure;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
//Update Khach hang
public static void Khachhang_Update(Khachhang_DTO KH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("Khachhang_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAKH", SqlDbType.Char).Value = KH.SMaKH;
cmd.Parameters.Add("@TENKH", SqlDbType.NVarChar).Value = KH.STenKH;
cmd.Parameters.Add("@SDT", SqlDbType.NVarChar).Value = KH.SSDT;
cmd.Parameters.Add("@DIACHI", SqlDbType.NVarChar).Value = KH.SDiachi;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
//load loai NV
public static DataTable LoadLoaiNV()
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("PLNhanVien_SelectLoaiNV", con);
cmd.CommandType = CommandType.StoredProcedure;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
public static void Update_CTHDBH(CTHDBanhang_DTO CTHDBH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("CTHDBanHang_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MACTHDBH", SqlDbType.Char).Value = CTHDBH.SMaCTHDB;
cmd.Parameters.Add("@MAHDBH", SqlDbType.Char).Value = CTHDBH.SMaHDB;
cmd.Parameters.Add("@MASP", SqlDbType.Char).Value = CTHDBH.SMaSP;
cmd.Parameters.Add("@SOLUONG", SqlDbType.Int).Value = CTHDBH.ISoLuong;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static DataTable SP_LaySL(string sMaSP)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("SANPHAM_LaySL", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MASP", SqlDbType.Char).Value = sMaSP;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
public static DataTable HDBH_SumMoney(string sMaHD)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("HDBH_SumMoney", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAHDBH", SqlDbType.Char).Value = sMaHD;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
public static DataTable Sanpham_LoadSearch(string sChuoi)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("SP_LoadSearch", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CHUOI", SqlDbType.Char).Value = sChuoi;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
//Load DIACHI va SDT
public static DataTable Khachhang_LoadThongtinKH(string sMaKH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("Khachhang_LoadThongtinKH", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAKH", SqlDbType.Char).Value = sMaKH;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
public static void HDBanhang_Insert(Hoadonban_DTO HDBH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("HDBanHang_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAHDBH", SqlDbType.Char).Value = HDBH.SMaHDB;
cmd.Parameters.Add("@MANV", SqlDbType.Char).Value = HDBH.SMaNV;
cmd.Parameters.Add("@MAKH", SqlDbType.Char).Value = HDBH.SMaKH;
cmd.Parameters.Add("@NGAYBH", SqlDbType.SmallDateTime).Value = HDBH.DtNgayBH;
cmd.Parameters.Add("@THANHTIEN", SqlDbType.Money).Value = 0;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
//Load Chi tiet hoa don ban hang
public static DataTable CTHDBANHANG_Load(string sMaHDBH)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("CTHDBanHang_Load", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MAHDBH", SqlDbType.Char).Value = sMaHDBH;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
//Loc hoa don theo ngay
public static DataTable HDBANHANG_Loc(DateTime dtStart, DateTime dtEnd)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("HDBANHANG_Loc", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@START", SqlDbType.SmallDateTime).Value = dtStart;
cmd.Parameters.Add("@END", SqlDbType.SmallDateTime).Value = dtEnd;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
public static DataTable GetUser(string sUserName, string sPassWord)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("TKNhanVien_Check", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = sUserName;
cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = sPassWord;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
return(table);
}
public static DataTable LoadMaNV(string sTenNV)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("sp_timkiem", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@name", SqlDbType.Char).Value = sTenNV;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
DataProvider.Dongketnoi(con);
DataProvider.Dongketnoi(con);
return(table);
}
public static void CapNhatNV(Nhanvien_DTO NV)
{
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("Nhanvien_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MANV", SqlDbType.Char).Value = NV.SMaNV;
cmd.Parameters.Add("@TENNV", SqlDbType.NVarChar).Value = NV.STenNV;;
cmd.Parameters.Add("@NGSINH", SqlDbType.SmallDateTime).Value = NV.DTNgaysinh;;
cmd.Parameters.Add("@GIOITINH", SqlDbType.NVarChar).Value = NV.SGioitinh;;
cmd.Parameters.Add("@DIACHI", SqlDbType.NVarChar).Value = NV.SDiachi;;
cmd.Parameters.Add("@SDT", SqlDbType.NVarChar).Value = NV.SSDT;;
cmd.Parameters.Add("@MALOAI", SqlDbType.Char).Value = NV.SMaLoai;;
cmd.ExecuteNonQuery();
DataProvider.Dongketnoi(con);
}
public static String MaSPTutang()
{
Sanpham_DTO TGBH = new Sanpham_DTO();
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("SanPham_MASP_TOP1", con);
cmd.CommandType = CommandType.StoredProcedure;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
string sMaSP = table.Rows[0]["MASP"].ToString();
DataProvider.Dongketnoi(con);
return(sMaSP);
}
public static String MaNCCTutang()
{
Nhacungcap_DTO TGBH = new Nhacungcap_DTO();
con = DataProvider.Ketnoi();
var cmd = new SqlCommand("NhaCungCap_TOP", con);
cmd.CommandType = CommandType.StoredProcedure;
var Adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
Adapter.Fill(table);
string sMaNCC = table.Rows[0]["MANCC"].ToString();
DataProvider.Dongketnoi(con);
return(sMaNCC);
}
public static bool SuaNCC(Nhacungcap_DTO SuaNCC)
{
string sTruyvan = string.Format(" Update NHACUNGCAP set TENNCC=N'{0}',SDT=N'{1}',DIACHI=N'{2}' WHERE MANCC='{3}' ", SuaNCC.STencc, SuaNCC.SSodienthoaincc, SuaNCC.SDiachincc, SuaNCC.SMancc);
//string sTruyvan = "insert into khoa values('" + txtMASP+ "',N'" + txtTENSP.Text + "')";
con = DataProvider.Ketnoi();
try
{
DataProvider.ThucThiTruyVanNonQuerry(sTruyvan, con);
DataProvider.Dongketnoi(con);
return(true);
}
catch (Exception ex)
{
DataProvider.Dongketnoi(con);
return(false);
}
}
public static bool ThemNCC(Nhacungcap_DTO ThemNCC)
{
string sTruyvan = string.Format(@"insert into NHACUNGCAP (MANCC,TENNCC,SDT, DIACHI) values ('{0}',N'{1}',N'{2}',N'{3}') ", ThemNCC.SMancc, ThemNCC.STencc, ThemNCC.SSodienthoaincc, ThemNCC.SDiachincc);
//string sTruyvan = "insert into khoa values('" + txtMASP+ "',N'" + txtTENSP.Text + "')";
con = DataProvider.Ketnoi();
try
{
DataProvider.ThucThiTruyVanNonQuerry(sTruyvan, con);
DataProvider.Dongketnoi(con);
return(true);
}
catch (Exception ex)
{
DataProvider.Dongketnoi(con);
return(false);
}
}
public static bool ThemSP(Sanpham_DTO ThemSP)
{
string sTruyvan = string.Format(@"insert into SANPHAM (MASP,TENSP, TGBAOHANH, SOLUONG, TRANGTHAI, DONGIA, MALOAI, MANCC) values ('{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}','N{7}') ", ThemSP.SMaSP, ThemSP.STenSP, ThemSP.STGBH, ThemSP.SSoLuong, ThemSP.STrangThai, ThemSP.SDonGia, ThemSP.SMaLoai, ThemSP.SMaNCC);
//string sTruyvan = "insert into khoa values('" + txtMASP+ "',N'" + txtTENSP.Text + "')";
con = DataProvider.Ketnoi();
try
{
DataProvider.ThucThiTruyVanNonQuerry(sTruyvan, con);
DataProvider.Dongketnoi(con);
return(true);
}
catch (Exception ex)
{
DataProvider.Dongketnoi(con);
return(false);
}
}
public static bool XoaNCC(Nhacungcap_DTO XoaNCC)
{
string sTruyvan = string.Format(" DELETE FROM NHACUNGCAP WHERE MANCC='{0}'", XoaNCC.SMancc);
//string sTruyvan = "insert into khoa values('" + txtMASP+ "',N'" + txtTENSP.Text + "')";
con = DataProvider.Ketnoi();
try
{
DataProvider.ThucThiTruyVanNonQuerry(sTruyvan, con);
DataProvider.Dongketnoi(con);
return(true);
}
catch (Exception ex)
{
DataProvider.Dongketnoi(con);
return(false);
}
}
public static bool XoaSP(Sanpham_DTO XoaSP)
{
string sTruyvan = string.Format(" DELETE FROM SANPHAM WHERE MASP='{0}'", XoaSP.SMaSP);
//string sTruyvan = "insert into khoa values('" + txtMASP+ "',N'" + txtTENSP.Text + "')";
con = DataProvider.Ketnoi();
try
{
DataProvider.ThucThiTruyVanNonQuerry(sTruyvan, con);
DataProvider.Dongketnoi(con);
return(true);
}
catch (Exception ex)
{
DataProvider.Dongketnoi(con);
return(false);
}
}
