public static List <rptHoaDon_TaiKhoanDTO> DoiMaNhanVienThanhTenNhanVien(DateTime timeStart, DateTime timeEnd)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien FROM HoaDon,TaiKhoan WHERE HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and ngay_lap between @timeStart and @timeEnd";
SqlCommand command = new SqlCommand();
command.Parameters.Add("@timeStart", System.Data.SqlDbType.DateTime, 0).Value = timeStart;
command.Parameters.Add("@timeEnd", System.Data.SqlDbType.DateTime, 0).Value = timeEnd;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO();
hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0);
hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1);
hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2);
hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3);
hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4);
hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5);
result.Add(hoaDon_TaiKhoanDTO);
}
}
connection.Close();
return(result);
}
public static List <rptHoaDon_TaiKhoanDTO> LayHoaDonMaMoiNhat()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien FROM HoaDon,TaiKhoan where HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and ma_hoa_don = (SELECT MAX(ma_hoa_don)FROM HoaDon)";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO();
hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0);
hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1);
hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2);
hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3);
hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4);
hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5);
result.Add(hoaDon_TaiKhoanDTO);
}
}
connection.Close();
return(result);
}
public static List <rptHoaDon_TaiKhoanDTO> DanhSachHoaDonVuaMoiLapTrongNgay()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "select HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien from HoaDon,TaiKhoan where HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and DAY(GETDATE()) = DAY(ngay_lap) and MONTH(GETDATE()) = MONTH(ngay_lap) and YEAR(GETDATE()) = YEAR(ngay_lap)";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO();
hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0);
hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1);
hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2);
hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3);
hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4);
hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5);
result.Add(hoaDon_TaiKhoanDTO);
}
}
connection.Close();
return(result);
}
