public DTO_Customer searchPhone(string phone)
{
try
{
cus = null;
DataTable dt = new DataTable();
cnn = DataProvider.ConnectData();
string load = string.Format("Select CusPhone, Fullname , Address From Customer where CusPhone = {0}", phone);
SqlCommand cmd = new SqlCommand(load, cnn);
SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dataReader.HasRows)
{
if (dataReader.Read())
{
phone = dataReader["CusPhone"].ToString();
string Fullname = dataReader["Fullname"].ToString();
string Address = dataReader["Address"].ToString();
cus = new DTO_Customer(phone, Fullname, Address);
return(cus);
}
}
cnn.Close();
return(cus);
}
catch (Exception ex)
{
throw ex;
}
}
public DTO_Employee getDetail(String username)
{
cnn = DataProvider.ConnectData();
string sqlQuery = string.Format("select IdEmployee, Password, NameEmployee, NumberPhoneEmployee, AddressEmployee, sp.Address, SupervisorEmployee, Role, emp.Status from Employee emp, Shops sp where emp.IDShops = sp.IDShops and emp.IdEmployee = '{0}'", username);
SqlCommand command = new SqlCommand(sqlQuery, cnn);
SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
if (dataReader.HasRows)
{
if (dataReader.Read())
{
string Username = dataReader["IdEmployee"].ToString();
string Password = dataReader["Password"].ToString();
string Fullname = dataReader["NameEmployee"].ToString();
string role = dataReader["Role"].ToString();
string Shop = dataReader["Address"].ToString();
string Address = dataReader["AddressEmployee"].ToString();
string Supervisor = dataReader["SupervisorEmployee"].ToString();
string Phone = dataReader["NumberPhoneEmployee"].ToString();
string Status = dataReader["Status"].ToString();
emp = new DTO_Employee(Username, Password, Fullname, Phone, Address, Shop, Supervisor, role, Status);
return(emp);
}
}
cnn.Close();
return(emp);
}
/// <summary>
/// kiem tra ten tai khoan, mat khau va trang thai khi dang nhap vao
/// </summary>
/// <param name="tk"></param>
/// <returns></returns>
public static string Dang_nhap(TaiKhoanDTO tk)
{
try
{
DataTable dt = new DataTable();
cnn = DataProvider.ConnectData();
string load = string.Format("SELECT db_taikhoan.ma_cv FROM db_taikhoan inner join db_chucvu on db_taikhoan.ma_cv = db_chucvu.ma_cv WHERE username='******' and pass='******'and trang_thai='false'", tk.username, tk.password);
MySqlCommand cmd = new MySqlCommand(load, cnn);
tk.ma_cv = cmd.ExecuteScalar().ToString();
cnn.Close();
if (tk.ma_cv != "")
{
//string select = string.Format("SELECT * FROM db_taikhoan,db_chucvu WHERE username='******' and pass='******'and db_taikhoan.ma_cv = '{2}' and trang_thai='{3}';UPDATE db_taikhoan SET trang_thai = true WHERE username='******'", tk.username, tk.password, tk.ma_cv, tk.trang_thai);
//DataProvider.Execute(cnn, select);
//cnn.Close();
return(tk.ma_cv);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void Delete_KH(KhachHangDTO kh)
{
try
{
HoaDonDTO hd = new HoaDonDTO();
PhieuDatHangDTO pdh = new PhieuDatHangDTO();
List <string> ds = new List <string>();
string delete_kh = string.Format("DELETE FROM db_khach_hang WHERE ma_kh='{0}'", kh.ma_kh);
string delete_hd_in_kh = string.Format("SELECT ma_hd FROM db_hoa_don WHERE ma_kh = '{0}'", kh.ma_kh);
string delete_pdh_in_kh = string.Format("SELECT ma_pdh FROM db_phieu_dat_hang WHERE ma_kh = '{0}'", kh.ma_kh);
ds = Xoa_thong_tin(delete_hd_in_kh);
for (int i = 0; i < ds.Count; i++)
{
hd.ma_hd = ds[0];
ChiTietHoaDonDAO.delete_SPHD(hd);
HoaDonDAO.Delete_HD(hd);
}
ds = Xoa_thong_tin(delete_pdh_in_kh);
for (int i = 0; i < ds.Count; i++)
{
pdh.ma_pdh = ds[0];
ChiTietPhieuDatHangDAO.delete_SPPDH(pdh);
PhieuDatHangDAO.Delete_PDH(pdh);
}
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, delete_kh);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
//Tỉm Sản Phẩm
public static SanPhamDTO Search_SP(string masp)
{
try
{
DataTable dt = new DataTable();
SanPhamDTO sp = new SanPhamDTO();
cnn = DataProvider.ConnectData();
string searchsp = string.Format("SELECT * FROM `db_sanpham` WHERE db_sanpham.ma_sp='" + masp + "'");
dt = DataProvider.Load_database(searchsp, cnn);
cnn.Close();
sp.ma_sp = dt.Rows[0][0].ToString();
sp.ma_loai = dt.Rows[0][1].ToString();
sp.ten_sp = dt.Rows[0][2].ToString();
sp.don_vi_tinh = dt.Rows[0][3].ToString();
sp.gia = double.Parse(dt.Rows[0][4].ToString());
sp.thoi_gian_bh = int.Parse(dt.Rows[0][5].ToString());
sp.soluong = int.Parse(dt.Rows[0][6].ToString());
sp.hang_san_xuat = dt.Rows[0][7].ToString();
return(sp);
}
catch (Exception ex)
{
throw ex;
}
}
public static void Dang_xuat(TaiKhoanDTO tk)
{
cnn = DataProvider.ConnectData();
string select = string.Format("UPDATE db_taikhoan SET trang_thai = false WHERE username='******'", tk.username);
DataProvider.Execute(cnn, select);
cnn.Close();
}
/// <summary>
/// load dữ liệu
/// </summary>
/// <returns></returns>
public static DataTable LoadDSNV()
{
DataTable dtb = new DataTable();
string strmysql = "SELECT ma_nv,ten_nv,ngay_sinh,gioi_tinh,sdt_nv,dia_chi_nv,email,db_chucvu.ten_cv FROM db_nhanvien inner join db_chucvu on db_nhanvien.ma_cv = db_chucvu.ma_cv ";
cnn = DataProvider.ConnectData();
dtb = DataProvider.Load_database(strmysql, cnn);
cnn.Close();
return(dtb);
}
public static DataTable Load_DSCV()
{
DataTable dt = new DataTable();
string select = "SELECT * FROM db_chucvu";
cnn = DataProvider.ConnectData();
dt = DataProvider.Load_database(select, cnn);
cnn.Close();
return(dt);
}
public static int createOrder(DTO_Order order)
{
SqlConnection cnn = DataProvider.ConnectData();
var cmd = new SqlCommand("createordert", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CusPhone", SqlDbType.Char).Value = order.cusPhone;
cmd.Parameters.Add("@IdEmployee", SqlDbType.NVarChar).Value = order.idEmp;
cmd.Parameters.Add("@PriceFinal", SqlDbType.Float).Value = order.total;
cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = order.quantity;
return((int)cmd.ExecuteScalar());
}
/// <summary>
/// thêm 1 tài khoản vào csdl
/// </summary>
/// <param name="tk"></param>
public static void Insert_TK(TaiKhoanDTO tk)
{
try
{
string Insert = string.Format("INSERT INTO db_taikhoan(username,pass,ma_cv,trang_thai) VALUES ('{0}','{1}','{2}','false');", tk.username, tk.password, tk.ma_cv);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, Insert);
cnn.Close();
}catch (Exception ex)
{
throw ex;
}
}
public int createCustomer(DTO_Customer cus)
{
SqlConnection cnn = DataProvider.ConnectData();
var cmd = new SqlCommand("createCustomer", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CusPhone", SqlDbType.Char).Value = cus.cusPhonenumber;
cmd.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = cus.cusName;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = cus.cusAddress;
int count = cmd.ExecuteNonQuery();
return(count);
}
/// <summary>
/// sữa thông tin chi tiết hóa đơn
/// </summary>
/// <param name="cthd"></param>
public static void Update_CTHD(ChiTietHoaDonDTO cthd)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format(" UPDATE chi_tiet_hd SET ma_sp = '{2}', so_luong='{3}' WHERE ma_hd = '{1}' and id = '{0}'; UPDATE chi_tiet_hd SET thanh_tien='{4}' WHERE id = '{0}'; ", cthd.id, cthd.ma_hd, cthd.ma_sp, cthd.soluong, cthd.thanhtien);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert_PDH(PhieuDatHangDTO pdh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_phieu_dat_hang(ma_pdh,ma_nv,ma_kh,ngay_lap) values ('{0}','{1}','{2}','{3}/{4}/{5}');", pdh.ma_pdh, pdh.ma_nv, pdh.ma_kh, pdh.year, pdh.month, pdh.day);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// delete nhan vien
/// </summary>
/// <param name="nv"></param>
public static void Delete_NV(NhanVienDTO nv)
{
try
{
string delete = string.Format("DELETE FROM db_nhanvien where ma_nv ='{0}'", nv.ma_nv);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, delete);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable loadTop5Revenue(string dateBegin, string dateEnd)
{
DataTable dt = new DataTable();
SqlConnection cnn = DataProvider.ConnectData();
var cmd = new SqlCommand("printStatiscalOrderbyDate", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dateBegin", SqlDbType.DateTime).Value = dateBegin;
cmd.Parameters.Add("@dateEnd", SqlDbType.DateTime).Value = dateEnd;
var da = new SqlDataAdapter(cmd);
da.Fill(dt);
return(dt);
}
public static void Delete_LSP(LoaiSanPhamDTO loaisp)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("DELETE FROM db_nhom_sp WHERE ma_loai='{0}';", loaisp.ma_loai, loaisp.ten_loai_sp);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void insert_CTPN(ChiTietPNDTO ctpn)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_chi_tiet_pn(ma_sp,ma_phieu_nhap,so_luong,gianhap,tong) values('{0}','{1}','{2}','{3}','{4}');",
ctpn.ma_sp, ctpn.ma_phieu_nhap, ctpn.soluong, ctpn.gianhap, ctpn.tong);
DataProvider.Execute(cnn, insert);
cnn.Close();
}catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// delete NCC
/// </summary>
/// <param name="ncc"></param>
public static void Delete_NCC(NhaCCDTO ncc)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("DELETE FROM db_nha_cung_cap WHERE ma_ncc='{0}'", ncc.ma_ncc);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// xoa thong tin 1 phieu nhap
/// </summary>
/// <param name="pn"></param>
public static void Delete_PN(PhieuNhapDTO pn)
{
try
{
cnn = DataProvider.ConnectData();
string select = string.Format("delete from db_chi_tiet_pn where ma_phieu_nhap='{0}';delete from db_phieu_nhap where ma_phieu_nhap='{0}';", pn.ma_phieu_nhap);
DataProvider.Execute(cnn, select);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// them ncc
/// </summary>
/// <param name="ncc"></param>
public static void Insert_NCC(NhaCCDTO ncc)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_nha_cung_cap(ma_ncc,ten_ncc,sdt_ncc,email) values ('{0}','{1}','{2}','{3}')", ncc.ma_ncc, ncc.ten_ncc, ncc.sdt_ncc, ncc.email);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// update nha cung cap
/// </summary>
/// <param name="ncc"></param>
public static void Update_NCC(NhaCCDTO ncc)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("UPDATE db_nha_cung_cap SET ten_ncc='{1}',sdt_ncc='{2}',email='{3}' WHERE ma_ncc='{0}'", ncc.ma_ncc, ncc.ten_ncc, ncc.sdt_ncc, ncc.email);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert_KH(KhachHangDTO kh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_khach_hang(ma_kh,ten_kh,diachi,sdt) value ('{0}','{1}', '{2}','{3}');", kh.ma_kh, kh.ten_kh, kh.diachi, kh.sdt);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void update_hd(HoaDonDTO hd)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("UPDATE db_hoa_don SET tonggiatri = '{1}' WHERE ma_hd='{0}'", hd.ma_hd, hd.tonggiatri);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Update_KH(KhachHangDTO kh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("UPDATE db_khach_hang SET ten_kh='{1}',diachi='{2}',sdt='{3}'WHERE ma_kh='{0}';", kh.ma_kh, kh.ten_kh, kh.diachi, kh.sdt);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void update_Gia(PhieuDatHangDTO pdh)
{
try
{
cnn = DataProvider.ConnectData();
string update_gia = string.Format("UPDATE db_phieu_dat_hang SET tonggiatri='{0}' WHERE ma_pdh='{1}'", pdh.tonggiatri, pdh.ma_pdh);
DataProvider.Execute(cnn, update_gia);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// sửa chi tiết phiếu đặt hàng
/// </summary>
/// <param name="ctpdh"></param>
public static void Update_CTPDH(ChiTietPDHDTO ctpdh)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("UPDATE chi_tiet_pdh SET ma_sp='{1}',soluong={2},thanhtien='{3}' WHERE id = '{0}'", ctpdh.id, ctpdh.ma_sp, ctpdh.soluong, ctpdh.thanhtien);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// xóa 1 phiếu đặt hàng
/// </summary>
/// <param name="pdh"></param>
public static void Delete_PDH(PhieuDatHangDTO pdh)
{
try
{
cnn = DataProvider.ConnectData();
string delete = string.Format("DELETE FROM chi_tiet_pdh WHERE ma_pdh = '{0}';DELETE FROM db_phieu_dat_hang WHERE ma_pdh = '{0}';", pdh.ma_pdh);
DataProvider.Execute(cnn, delete);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert_CTPDH(ChiTietPDHDTO ctpdh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into chi_tiet_pdh(ma_sp,ma_pdh,soluong,thanhtien) values('{0}','{1}','{2}','{3}');", ctpdh.ma_sp, ctpdh.ma_pdh, ctpdh.soluong, ctpdh.thanhtien);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Delete_HD(HoaDonDTO hd)
{
try
{
cnn = DataProvider.ConnectData();
string delete = string.Format("DELETE FROM chi_tiet_hd WHERE ma_hd = '{0}'; DELETE FROM db_hoa_don WHERE ma_hd = '{0}'; ", hd.ma_hd);
DataProvider.Execute(cnn, delete);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// them vao csdl
/// </summary>
/// <param name="loaisp"></param>
public static void Insert_LSP(LoaiSanPhamDTO loaisp)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_nhom_sp values ('{0}','{1}');", loaisp.ma_loai, loaisp.ten_loai_sp);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}