public DataTable reportByMonth(int month) { int year = DateTime.Now.Year; int monthB = month; int monthC = month; int yearB = year; int yearC = year; string query = "SELECT SUM(T1.Quantity) * 1000.00 / (select sum(A.Quantity) from BillInfo A " + "where A.idBill in (select B.idBill from Bill B where MONTH(B.Date) = @month and YEAR(B.Date) = @year) ) AS PERCENTAGE_DRINK, " + "SUM(T1.Quantity)* T2.Price * 1000.00 / (select SUM(C.TotalPrice) from Bill C " + "where MONTH(C.Date) = @monthB and YEAR(C.Date) = @yearB) AS PERCENTAGE_MONEY, " + "SUM(T1.Quantity)* T2.Price AS MONEY, " + "SUM(T1.Quantity) AS SO_LUONG, " + "T2.Name, T1.idDrink " + "FROM BillInfo T1 " + "INNER JOIN Drink T2 " + "ON T1.idDrink = T2.idDrink" + " WHERE T1.idBill in (select T3.idBill from Bill T3 where MONTH(T3.Date) = @monthC and YEAR(T3.Date) = @yearC)" + "GROUP BY T1.idDrink, T2.Name, T2.Price"; object[] value = new object[] { month, year, monthB, yearB, monthC, yearC }; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query, value); return(dt); }
public DataTable getAllAccounts() { string query = "SELECT * FROM ACCOUNT"; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query); return(dt); }
public DataTable GetAllDrinksDetailed() { string query = "SELECT DrinkCategory.Name as Loại, Drink.Name as Tên, Drink.Price as Giá, Drink.idCategory, Drink.idDrink FROM Drink inner join DrinkCategory on Drink.idCategory = DrinkCategory.idCategory order by Drink.idCategory"; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query); return(dt); }
public DataTable GetAllDrinks() { string query = "SELECT * FROM DRINK"; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query); return(dt); }
public DataTable getLoginAccount(string username, string password) { string query = "select * from ACCOUNT where username = @username and password = @password"; object[] value = new object[] { username, password }; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query, value); return(dt); }
public DataTable GetDrinksByCategory(string idCategory) { string query = "SELECT * FROM DRINK WHERE IDCATEGORY = @IDCATEGORY"; DBConnect db = new DBConnect(); object[] value = new object[] { idCategory }; DataTable dt = db.ExecuteQuery(query, value); return(dt); }
public Account getAccountByUsername(string username) { string query = "select * from ACCOUNT where username = @username"; object[] value = new object[] { username }; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query, value); Account account = new Account(dt.Rows[0]); return(account); }
public Drink GetDrinkByName(string name) { string query = "SELECT * FROM DRINK WHERE NAME = @NAME"; object[] value = new object[] { name }; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query, value); Drink drink = new Drink(dt.Rows[0]); return(drink); }
public Drink GetDrinkByID(int idDrink) { string query = "SELECT * FROM DRINK WHERE IDDRINK = @IDDRINK"; object[] value = new object[] { idDrink }; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query, value); Drink drink = new Drink(dt.Rows[0]); return(drink); }
// truy van toi username trong table Accout public string getPasswordByUsername(string username) { string password; string query = "select Password from Account where Username = @username"; object[] value = new object[] { username }; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query, value); password = dt.Rows[0]["password"].ToString(); return(password); }
public List <DrinkCategory> GetDrinkCategories() { List <DrinkCategory> list = new List <DrinkCategory>(); string query = "SELECT * FROM DrinkCategory"; DBConnect db = new DBConnect(); DataTable dt = db.ExecuteQuery(query); foreach (DataRow row in dt.Rows) { list.Add(new DrinkCategory(row)); } return(list); }