public static bool CheckNhanVienByMaNhanVien(string strMaNhanVien)
{
bool result = false;
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@MaNhanVien", strMaNhanVien)
};
var dt = DataAccessHelper.ExecuteQuery(
"SELECT MaNhanVien From NHANVIEN WHERE MaNhanVien=@MaNhanVien", parameters
);
if (dt.Rows.Count == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static bool CheckDonHangTamByMaDonHangTam(string strMaDonHangTam)
{
bool result = false;
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@MaDonHangTam", strMaDonHangTam)
};
var dt = DataAccessHelper.ExecuteQuery
(
"SELECT MaDonHangTam FROM DONHANGTAM WHERE MaDonHangTam=@MaDonHangTam"
, parameters
);
if (dt.Rows.Count == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static NhanVienDTO SelectNhanVienByTenNhanVien(string strTenNhanVien)
{
NhanVienDTO nvDTO = new NhanVienDTO();
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@TenNhanVien", strTenNhanVien)
};
var dt = DataAccessHelper.ExecuteQuery
(
"SELECT MaNhanVien,TenNhanVien From NHANVIEN WHERE TenNhanVien=@TenNhanVien", parameters
);
var dr = dt.Rows[0];
nvDTO.MaNhanVien = dr["MaNhanVien"].ToString();
nvDTO.TenNhanVien = dr["TenNhanVien"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(nvDTO);
}
public static List <DonHangTamDTO> SelectDonHangTamAll()
{
var list = new List <DonHangTamDTO>();
try
{
var dt = DataAccessHelper.ExecuteQuery("SELECT * FROM DONHANGTAM ");
list.AddRange(from DataRow dr in dt.Rows
select new DonHangTamDTO
{
MaDonHangTam = dr["MaDonHangTam"].ToString(),
NgayDatHang = DateTime.Parse(dr["NgayDatHang"].ToString()),
TenNguoiBan = dr["TenNguoiBan"].ToString(),
LienHeNguoiBan = dr["LienHeNguoiBan"].ToString(),
SDTNguoiBan = dr["SDTNguoiBan"].ToString(),
DiaDiemGiao = dr["DiaDiemGiaoHang"].ToString(),
TenNguoiMua = dr["TenNguoiMua"].ToString(),
SDTNguoiMua = dr["SDTNguoiMua"].ToString(),
DiaDiemNhan = dr["DiaDiemNhanHang"].ToString(),
TienThuHo = double.Parse(dr["TienThuHo"].ToString()),
GhiChu = dr["GhiChu"].ToString(),
}
);
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static DTO.DonHangTamDTO SelectDonHangTamByMaDonHangTam(string strMaDonHangTam)
{
DTO.DonHangTamDTO dhtDTO = new DTO.DonHangTamDTO();
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@MaDonHangTam", strMaDonHangTam)
};
var dt = DataAccessHelper.ExecuteQuery
(
"SELECT NgayDatHang,TenNguoiBan,LienHeNguoiBan,SDTNguoiBan,DiaDiemNhanHang," +
"TenNguoiMua,SDTNguoiMua,DiaDiemGiaoHang,TienThuHo,GhiChu" +
" FROM DONHANGTAM WHERE MaDonHangTam=@MaDonHangTam"
, parameters
);
var dr = dt.Rows[0];
dhtDTO.MaDonHangTam = dr["MaDonHangTam"].ToString();
dhtDTO.NgayDatHang = DateTime.Parse(dr["NgayDatHang"].ToString());
dhtDTO.TenNguoiBan = dr["TenNguoiBan"].ToString();
dhtDTO.LienHeNguoiBan = dr["LienHeNguoiBan"].ToString();
dhtDTO.SDTNguoiBan = dr["SDTNguoiBan"].ToString();
dhtDTO.DiaDiemGiao = dr["DiaDiemGiaoHang"].ToString();
dhtDTO.TenNguoiMua = dr["TenNguoiMua"].ToString();
dhtDTO.SDTNguoiMua = dr["SDTNguoiMua"].ToString();
dhtDTO.DiaDiemNhan = dr["DiaDiemNhanHang"].ToString();
dhtDTO.TienThuHo = double.Parse(dr["TienThuHo"].ToString());
dhtDTO.GhiChu = dr["GhiChu"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(dhtDTO);
}
public static bool CheckTaiKhoanByTenTaiKhoan(string tenTaiKhoan)
{
bool result = false;
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@TenTaiKhoan", tenTaiKhoan)
};
var dt = DataAccessHelper.ExecuteQuery
(
"SELECT * FROM TAIKHOAN WHERE TenTaiKhoan=@TenTaiKhoan"
, parameters
);
if (dt.Rows.Count == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static TaiKhoanDTO SelectTaiKhoanByTenTaiKhoan(string tenTaiKhoan)
{
TaiKhoanDTO taiKhoan = new TaiKhoanDTO();
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@TenTaiKhoan", tenTaiKhoan)
};
var dt = DataAccessHelper.ExecuteQuery
(
"SELECT * FROM TAIKHOAN WHERE TenTaiKhoan=@TenTaiKhoan", parameters
);
var dr = dt.Rows[0];
taiKhoan.TenTaiKhoan = dr["TenTaiKhoan"].ToString();
taiKhoan.MatKhau = dr["MatKhau"].ToString();
taiKhoan.LoaiTaiKhoan = dr ["LoaiTaiKhoan"].ToString( );
}
catch (Exception ex)
{
throw ex;
}
return(taiKhoan);
}
public static List <DTO.DonHangDTO> SelectDonHangByNgayThangNam(int ngay, int thang, int nam)
{
var list = new List <DTO.DonHangDTO>();
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@Ngay", ngay),
new SQLiteParameter("@Thang", thang),
new SQLiteParameter("@Nam", nam),
};
var dt = DataAccessHelper.ExecuteQuery(@"Select * from DONHANG WHERE cast(strftime('%Y', NgayGiaoHang) as int) = @Nam and
cast(strftime('%m', NgayGiaoHang) as int) = @Thang and
cast(strftime('%d', NgayGiaoHang) as int) = @Ngay ",
parameters
);
list.AddRange(from DataRow dr in dt.Rows
select new DTO.DonHangDTO
{
MaDonHang = dr["MaDonHang"].ToString(),
LoaiDonHang = dr["LoaiDonHang"].ToString(),
KhuVuc = dr["KhuVuc"].ToString(),
NgayDatHang = DateTime.Parse(dr["NgayDatHang"].ToString()),
NgayNhanHang = DateTime.Parse(dr["NgayNhanHang"].ToString()),
NgayGiaoHang = DateTime.Parse(dr["NgayGiaoHang"].ToString()),
MaNhanVienNhan = dr["MaNhanVienNhan"].ToString(),
MaNhanVienGiao = dr["MaNhanVienGiao"].ToString(),
TrangThai = dr["TrangThai"].ToString(),
TenNguoiBan = dr["TenNguoiBan"].ToString(),
LienHeNguoiBan = dr["LienHeNguoiBan"].ToString(),
SDTNguoiBan = dr["SDTNguoiBan"].ToString(),
DiaDiemGiao = dr["DiaDiemGiaoHang"].ToString(),
TenNguoiMua = dr["TenNguoiMua"].ToString(),
SDTNguoiMua = dr["SDTNguoiMua"].ToString(),
DiaDiemNhan = dr["DiaDiemNhanHang"].ToString(),
TienThuHo = double.Parse(dr["TienThuHo"].ToString()),
PhiVanChuyen = double.Parse(dr["PhiVanChuyen"].ToString()),
PhiPhatSinh = double.Parse(dr["PhiPhatSinh"].ToString()),
TongThanhTien = double.Parse(dr["TongThanhTien"].ToString()),
GhiChu = dr["GhiChu"].ToString(),
});
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static double GetPhiThuHo()
{
double phithuho = 0;
try
{
var dt = DataAccessHelper.ExecuteQuery("Select PhiThuHo from THAMSO");
var dr = dt.Rows[0];
phithuho = double.Parse(dr["PhiThuHo"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(phithuho);
}
public static double GetSoTienTrenKm()
{
var sotientrenkm = 0;
try
{
var dt = DataAccessHelper.ExecuteQuery("Select SoTienTrenKm from THAMSO");
var dr = dt.Rows[0];
sotientrenkm = int.Parse(dr["SoTienTrenKm"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(sotientrenkm);
}
public static List <DTO.DonHangDTO> SelectDonHangByTrangThai(string strTrangThai)
{
var list = new List <DTO.DonHangDTO>();
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@TrangThai", strTrangThai)
};
var dt = DataAccessHelper.ExecuteQuery("SELECT * FROM DonHang WHERE TrangThai=@TrangThai", parameters);
list.AddRange(from DataRow dr in dt.Rows
select new DTO.DonHangDTO
{
MaDonHang = dr["MaDonHang"].ToString(),
LoaiDonHang = dr["LoaiDonHang"].ToString(),
KhuVuc = dr["KhuVuc"].ToString(),
NgayDatHang = DateTime.Parse(dr["NgayDatHang"].ToString()),
NgayNhanHang = DateTime.Parse(dr["NgayNhanHang"].ToString()),
NgayGiaoHang = DateTime.Parse(dr["NgayGiaoHang"].ToString()),
MaNhanVienNhan = dr["MaNhanVienNhan"].ToString(),
MaNhanVienGiao = dr["MaNhanVienGiao"].ToString(),
TrangThai = dr["TrangThai"].ToString(),
TenNguoiBan = dr["TenNguoiBan"].ToString(),
LienHeNguoiBan = dr["LienHeNguoiBan"].ToString(),
SDTNguoiBan = dr["SDTNguoiBan"].ToString(),
DiaDiemGiao = dr["DiaDiemGiaoHang"].ToString(),
TenNguoiMua = dr["TenNguoiMua"].ToString(),
SDTNguoiMua = dr["SDTNguoiMua"].ToString(),
DiaDiemNhan = dr["DiaDiemNhanHang"].ToString(),
TienThuHo = double.Parse(dr["TienThuHo"].ToString()),
PhiVanChuyen = double.Parse(dr["PhiVanChuyen"].ToString()),
PhiPhatSinh = double.Parse(dr["PhiPhatSinh"].ToString()),
TongThanhTien = double.Parse(dr["TongThanhTien"].ToString()),
GhiChu = dr["GhiChu"].ToString(),
}
);
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static int GenerateNewMaNhanVien()
{
var newMaNhanVien = 0;
try
{
var dt = DataAccessHelper.ExecuteQuery(
"Select MAX(CAST(REPLACE(MaNhanVien , 'NV', '') as int)) + 1 as NewMaNhanVien from NhanVien");
newMaNhanVien = dt.Rows[0]["NewMaNhanVien"].ToString() == "" ?
1 :
int.Parse(dt.Rows[0]["NewMaNhanVien"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(newMaNhanVien);
}
public static int GenerateNewMaDonHangTam()
{
var newMaDonHangTam = 0;
try
{
var dt = DataAccessHelper.ExecuteQuery(
"Select MAX(CAST(REPLACE(MaDonHangTam , 'DHT', '') as int)) + 1 as NewMaDonHangTam from DonHangTam");
newMaDonHangTam = dt.Rows[0]["NewMaDonHangTam"].ToString() == "" ?
1 :
int.Parse(dt.Rows[0]["NewMaDonHangTam"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(newMaDonHangTam);
}
public static DTO.DonHangDTO SelectDonHangByMaDonHang(string strMaDonHang)
{
DTO.DonHangDTO dhDTO = new DTO.DonHangDTO();
try
{
var parameters = new List <SQLiteParameter>
{
new SQLiteParameter("@MaDonHang", strMaDonHang)
};
var dt = DataAccessHelper.ExecuteQuery
(
"SELECT MaDonHang FROM DONHANG WHERE MaDonHang=@MaDonHang"
, parameters
);
var dr = dt.Rows[0];
dhDTO.MaDonHang = dr["MaDonHang"].ToString();
dhDTO.LoaiDonHang = dr["LoaiDonHang"].ToString();
dhDTO.KhuVuc = dr["KhuVuc"].ToString();
dhDTO.NgayDatHang = DateTime.Parse(dr["NgayDatHang"].ToString());
dhDTO.NgayNhanHang = DateTime.Parse(dr["NgayNhanHang"].ToString());
dhDTO.NgayGiaoHang = DateTime.Parse(dr["NgayGiaoHang"].ToString());
dhDTO.MaNhanVienNhan = dr["MaNhanVienNhan"].ToString();
dhDTO.MaNhanVienGiao = dr["MaNhanVienGiao"].ToString();
dhDTO.TrangThai = dr["TrangThai"].ToString();
dhDTO.TenNguoiBan = dr["TenNguoiBan"].ToString();
dhDTO.LienHeNguoiBan = dr["LienHeNguoiBan"].ToString();
dhDTO.SDTNguoiBan = dr["SDTNguoiBan"].ToString();
dhDTO.DiaDiemGiao = dr["DiaDiemGiaoHang"].ToString();
dhDTO.TenNguoiMua = dr["TenNguoiMua"].ToString();
dhDTO.SDTNguoiMua = dr["SDTNguoiMua"].ToString();
dhDTO.DiaDiemNhan = dr["DiaDiemNhanHang"].ToString();
dhDTO.TienThuHo = double.Parse(dr["TienThuHo"].ToString());
dhDTO.PhiVanChuyen = double.Parse(dr["PhiVanChuyen"].ToString());
dhDTO.PhiPhatSinh = double.Parse(dr["PhiPhatSinh"].ToString());
dhDTO.TongThanhTien = double.Parse(dr["TongThanhTien"].ToString());
dhDTO.GhiChu = dr["GhiChu"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(dhDTO);
}
public static List <NhanVienDTO> SelectNhanVienAll()
{
var list = new List <NhanVienDTO>();
try
{
var dt = DataAccessHelper.ExecuteQuery("SELECT * FROM NHANVIEN ");
list.AddRange(from DataRow dr in dt.Rows
select new NhanVienDTO
{
MaNhanVien = dr["MaNhanVien"].ToString(),
TenNhanVien = dr["TenNhanVien"].ToString()
}
);
}
catch (Exception ex)
{
throw ex;
}
return(list);
}