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