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); }