public static List <DTO.Bill_DTO> GetBillByIdRoom(int id)
{
string stringquery = "select *, DATEDIFF(MINUTE, time_In, GETDATE()) as timeUse from dbo.BILL where payment_Status = 0 and id_Room = " + id;
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
List <DTO.Bill_DTO> listBill = new List <DTO.Bill_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DTO.Bill_DTO b = new DTO.Bill_DTO();
b.Id = int.Parse(dt.Rows[i]["id_Bill"].ToString());
b.TimeIn = (DateTime?)dt.Rows[i]["time_In"];
var gioratmp = dt.Rows[i]["time_Out"];
if (gioratmp.ToString() != "")
{
b.TimeOut = (DateTime?)gioratmp;
}
b.PaymentStatus = int.Parse(dt.Rows[i]["payment_Status"].ToString());
b.TimeUse = int.Parse(dt.Rows[i]["timeUse"].ToString());
//b.Discount = int.Parse(dt.Rows[i]["discount"].ToString());
listBill.Add(b);
}
return(listBill);
}
public static List <DTO.Bill_DTO> GetAllBillByDate(string date)
{
string stringquery = "select b.id_Bill,r.name, b.time_In, b.time_Out,DATEDIFF(MINUTE, b.time_In, b.time_Out) as time_Use, room_Fee,service_Fee, b.discount, (room_Fee + service_Fee) + (((room_Fee + service_Fee) * 0.1) - (((room_Fee + service_Fee) + ((room_Fee + service_Fee) * 0.1)) * discount) / 100.0) as total_money from bill as b, ROOM as r where payment_Status = 1 and b.id_Room = r.id_Room and time_In between '" + date + "' and '" + date + " 23:59:59'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
List <DTO.Bill_DTO> listBill = new List <DTO.Bill_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DTO.Bill_DTO b = new DTO.Bill_DTO();
b.Id = int.Parse(dt.Rows[i]["id_Bill"].ToString());
b.TimeIn = (DateTime?)dt.Rows[i]["time_In"];
var gioratmp = dt.Rows[i]["time_Out"];
if (gioratmp.ToString() != "")
{
b.TimeOut = (DateTime?)gioratmp;
}
//b.PaymentStatus = int.Parse(dt.Rows[i]["payment_Status"].ToString());
b.TimeUse = int.Parse(dt.Rows[i]["time_Use"].ToString());
b.Room_Fee = float.Parse(dt.Rows[i]["room_Fee"].ToString());
b.Services_Fee = float.Parse(dt.Rows[i]["service_Fee"].ToString());
b.Discount = int.Parse(dt.Rows[i]["discount"].ToString());
b.Total_Money = float.Parse(dt.Rows[i]["total_money"].ToString());
listBill.Add(b);
}
return(listBill);
}
public static List <DTO.itemInBill_DTO> GetItemOfRoom(int id)
{
string stringquery = "select m.name, bi.count_menu, m.sale_price, m.sale_price * bi.count_menu As ToMoney from BILL as b, BILL_INFO as bi, MENU as m, ROOM as r where bi.id_Bill = b.id_Bill and bi.Id_nenu = m.Id_nenu and r.id_Room = b.id_Room and r.status = 1 and b.payment_Status = 0 and r.id_Room =" + id;
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
//if (dt.Rows.Count == 0)
//{
// return null;
//}
List <DTO.itemInBill_DTO> listItem = new List <DTO.itemInBill_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DTO.itemInBill_DTO item = new DTO.itemInBill_DTO();
item.Name = dt.Rows[i]["name"].ToString();
item.Amout = int.Parse(dt.Rows[i]["count_menu"].ToString());
item.Price = float.Parse(dt.Rows[i]["sale_price"].ToString());
item.ToMoney = float.Parse(dt.Rows[i]["ToMoney"].ToString());
listItem.Add(item);
}
return(listItem);
}
public static void payment(int id, int discount, float ser_fee, float r_Fee)
{
string stringquery = "update BILL set time_Out = getdate(), payment_Status = 1, discount = " + discount + ", service_Fee = " + ser_fee + ", room_Fee = " + r_Fee + " where id_Bill = " + id;
con = DataProvider.OpenConnection();
DataProvider.ExcuteQuery(stringquery, con);
}
public static List <DTO.User_DTO> GetUser(string userName, string passWord)
{
string stringquery = "SELECT * FROM USER_ACCOUNT WHERE username = '******' AND password = '******' ";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <User_DTO> User = new List <User_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
User_DTO u = new User_DTO();
u.Id = int.Parse(dt.Rows[i]["id_User"].ToString());
u.Username = dt.Rows[i]["username"].ToString();
u.Password = dt.Rows[i]["password"].ToString();
u.NameUser = dt.Rows[i]["nameUser"].ToString();
u.Permission = int.Parse(dt.Rows[i]["id_Permission"].ToString());
User.Add(u);
}
return(User);
}
public static List <DTO.RoomDTO> GetRooms()
{
string stringquery = "select* from ROOM";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <RoomDTO> listRoom = new List <RoomDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
RoomDTO room = new RoomDTO();
room.IdRoom = int.Parse(dt.Rows[i]["id_Room"].ToString());
room.Name = dt.Rows[i]["name"].ToString();
room.Status = int.Parse(dt.Rows[i]["status"].ToString());
room.Kind_Of_Room = int.Parse(dt.Rows[i]["kind_Of_Room"].ToString());
room.Price = float.Parse(dt.Rows[i]["price"].ToString());
listRoom.Add(room);
}
return(listRoom);
}
public static List <DTO.User_DTO> GetAllUsers()
{
string stringquery = "select u.id_User, u.username, u.password, u.nameUser, u.id_Permission, p.name_Permission from USER_ACCOUNT AS u, PERMISSION as p where u.id_Permission = p.id_Permission";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <User_DTO> listUsers = new List <User_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
User_DTO u = new User_DTO();
u.Id = int.Parse(dt.Rows[i]["id_User"].ToString());
u.Username = dt.Rows[i]["username"].ToString();
u.Password = dt.Rows[i]["password"].ToString();
u.NameUser = dt.Rows[i]["nameUser"].ToString();
u.Permission = int.Parse(dt.Rows[i]["id_Permission"].ToString());
u.NamePermission = dt.Rows[i]["name_Permission"].ToString();
listUsers.Add(u);
}
return(listUsers);
}
public static List <DTO.servicesSold_DTO> GetAllServicesSoldByDate(string date)
{
string stringquery = "select r.name, m.name as nameSV, bi.count_menu, (m.original_Price * bi.count_menu) as total_original_Price, (m.sale_Price * bi.count_menu) as total_sale_Price, ((m.sale_Price * bi.count_menu) - (m.original_Price * bi.count_menu)) as revenue from BILL_INFO as bi, BILL as b, MENU as m, ROOM as r where b.id_Bill = bi.id_Bill and r.id_Room = b.id_Room and bi.Id_nenu = m.Id_nenu and b.payment_Status = 1 and time_In between '" + date + "' and '" + date + " 23:59:59'";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <servicesSold_DTO> listServices = new List <servicesSold_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
servicesSold_DTO s = new servicesSold_DTO();
s.NameOfRoomBuy = dt.Rows[i]["name"].ToString();
s.NameOfServices = dt.Rows[i]["nameSV"].ToString();
s.Count = int.Parse(dt.Rows[i]["count_menu"].ToString());
s.Total_original_price = float.Parse(dt.Rows[i]["total_original_Price"].ToString());
s.Total_sale_price = float.Parse(dt.Rows[i]["total_sale_Price"].ToString());
s.Revenue = float.Parse(dt.Rows[i]["Revenue"].ToString());
listServices.Add(s);
}
return(listServices);
}
public static List <DTO.Menu_DTO> GetInfoMenuByID(int id)
{
string stringquery = "select * from MENU where Id_nenu = " + id;
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DTO.Menu_DTO> listInfo = new List <DTO.Menu_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DTO.Menu_DTO menu = new DTO.Menu_DTO();
menu.Id = int.Parse(dt.Rows[i]["Id_nenu"].ToString());
menu.Name = dt.Rows[i]["name"].ToString();
menu.Original_price = float.Parse(dt.Rows[i]["original_price"].ToString());
menu.Sale_price = float.Parse(dt.Rows[i]["sale_Price"].ToString());
menu.Amout = int.Parse(dt.Rows[i]["amout"].ToString());
menu.Kind = int.Parse(dt.Rows[i]["id_Kind"].ToString());
listInfo.Add(menu);
}
return(listInfo);
}
public static bool Login(string userName, string passWord)
{
string stringquery = "SELECT * FROM USER_ACCOUNT WHERE username = '******' AND password = '******' ";
con = DataProvider.OpenConnection();
DataTable result = DataProvider.ExcuteQuery(stringquery, con); //dung nonquery tu nhien bi loi~ :v
return(result.Rows.Count > 0);
}
public static DataTable LoadAll()
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_LoadAllAccounts";
DataTable table = provider.ExcuteQuery(cmdStr);
provider.Disconnect();
return(table);
}
public static DataTable LoadAllExtendedBillDetails(int maHoaDon)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_LoadAllExtendedBillDetails " + maHoaDon;
DataTable table = provider.ExcuteQuery(cmdStr);
provider.Disconnect();
return(table);
}
public static int GetBillNotPaymentByIdRoom(int id)
{
string stringquery = "select * from BILL where id_Room =" + id + "and payment_Status = 0 ";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count > 0)
{
DTO.Bill_DTO b = new DTO.Bill_DTO(dt.Rows[0]);
return(b.Id);
}
return(-1); //id no la -1: ko co thang nao het
//thanh cong thi se la stt hoa don, fail thi -1
}
public static List <DTO.billInFo_DTO> GetListBillInfo(int id)
{
string stringquery = "select * from dbo.chitiethoadon where mahoadon = " + id;
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
List <DTO.billInFo_DTO> listBillInfo = new List <DTO.billInFo_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DTO.billInFo_DTO bi = new DTO.billInFo_DTO();
bi.Id = int.Parse(dt.Rows[i]["id_Bill_Info"].ToString());
bi.IdBill = int.Parse(dt.Rows[i]["id_Bill"].ToString());
bi.IdMenu = int.Parse(dt.Rows[i]["id_menu"].ToString());
bi.Count = int.Parse(dt.Rows[i]["count_menu"].ToString());
listBillInfo.Add(bi);
}
return(listBillInfo);
}
public static List <DTO.Kind_DTO> GetKindOfDkink()
{
string stringquery = "select * from KIND";
con = DataProvider.OpenConnection();
DataTable dt = DataProvider.ExcuteQuery(stringquery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DTO.Kind_DTO> listKind = new List <DTO.Kind_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DTO.Kind_DTO kind = new DTO.Kind_DTO();
kind.Id = int.Parse(dt.Rows[i]["id_Kind"].ToString());
kind.Name = dt.Rows[i]["name"].ToString();
listKind.Add(kind);
}
return(listKind);
}
