public static DataTable LoadFood()
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("LoadFood", cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static void AddFoodCategory(string NameCategory)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("AddFoodCategory", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@NameCategory", SqlDbType.NVarChar, 100);
cmd.Parameters["@NameCategory"].Value = NameCategory;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static void DeleteFood(string IdFood)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("DeleteFood", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdFood", SqlDbType.Int);
cmd.Parameters["@IdFood"].Value = IdFood;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static int GetLastIdBill()
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("GetLastIdBill", cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(Int32.Parse(dtb.Rows[0][0].ToString()));
}
public static void DeleteAllOfBill(int idBill)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("DeleteAllOfBill", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@idBill", SqlDbType.Int);
cmd.Parameters["@idBill"].Value = idBill;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static void AddTableIntoBill(BillTable_DTO bt)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("AddTableIntoBill", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdBill", SqlDbType.Int);
cmd.Parameters["@IdBill"].Value = bt.IdBill;
cmd.Parameters.Add("@IdTable", SqlDbType.Int);
cmd.Parameters["@IdTable"].Value = bt.IdTable;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static DataTable LoadBillInfoToReceiptByIdBill(int IdBill)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("LoadBillInfoToReceiptByIdBill", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdBill", SqlDbType.Int);
cmd.Parameters["@IdBill"].Value = IdBill;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static DataTable SearchByCategory(string category)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("searchByCategory", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@NameOfCategory", SqlDbType.NVarChar, 100);
cmd.Parameters["@NameOfCategory"].Value = category;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static int CountTable()
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("CountTable", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Number", SqlDbType.Int).Direction = ParameterDirection.Output;
cnn.Open();
cmd.ExecuteNonQuery();
int soluong = Convert.ToInt32(cmd.Parameters["@Number"].Value);
cnn.Close();
return(soluong);
}
public static DataTable FindBillByTable(int IdTable)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("FindBillByTable", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdTable", SqlDbType.Int);
cmd.Parameters["@IdTable"].Value = IdTable;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
//Thong ke
public static DataTable StatisticByRevenue(int year)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("StatisticByRevenue", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Year", SqlDbType.Int);
cmd.Parameters["@Year"].Value = year;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
//Nhung san pham ban khong được
public static DataTable UnmarketableProducts(int month, int year)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("UnmarketableProducts", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Month", SqlDbType.Int);
cmd.Parameters.Add("@Year", SqlDbType.Int);
cmd.Parameters["@Month"].Value = month;
cmd.Parameters["@Year"].Value = year;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static void AddTable(Table_DTO table)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("AddTable", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@Location", SqlDbType.NVarChar, 200);
cmd.Parameters.Add("@Status", SqlDbType.NVarChar, 20);
cmd.Parameters["@Name"].Value = table.Name;
cmd.Parameters["@Location"].Value = table.Location;
cmd.Parameters["@Status"].Value = table.Status;;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static void InsertBill(Bill_DTO Bill)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("AddBill", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Total", SqlDbType.Int);
cmd.Parameters["@Total"].Value = Bill.Total;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Value = Bill.Status;
cmd.Parameters.Add("@Date", SqlDbType.Date);
cmd.Parameters["@Date"].Value = Bill.Date.ToShortDateString();
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static DataTable EditStatusOfBill(int IdBill, int Status)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("EditStatusOfBill", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdBill", SqlDbType.Int);
cmd.Parameters["@IdBill"].Value = IdBill;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Value = Status;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static DataTable FindBillByDate(DateTime fromDate, DateTime toDate)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("FindBillByDate", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@fromDate", SqlDbType.Date);
cmd.Parameters.Add("@toDate", SqlDbType.Date);
cmd.Parameters["@fromDate"].Value = fromDate.ToShortDateString();
cmd.Parameters["@toDate"].Value = toDate.ToShortDateString();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static void EditNumberOfInfo(BillDetails_DTO bd)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("EditNumberOfInfo", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdBill", SqlDbType.Int);
cmd.Parameters["@IdBill"].Value = bd.IdBill;
cmd.Parameters.Add("@IdFood", SqlDbType.Int);
cmd.Parameters["@IdFood"].Value = bd.IdFood;
cmd.Parameters.Add("@Number", SqlDbType.Int);
cmd.Parameters["@Number"].Value = bd.Number;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static int getTypeByName(string nameAccount)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("getTypeByName", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Type", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@NameAccount", SqlDbType.NVarChar, 100);
cmd.Parameters["@NameAccount"].Value = nameAccount;
cnn.Open();
cmd.ExecuteNonQuery();
int type = Convert.ToInt32(cmd.Parameters["@Type"].Value);
cnn.Close();
return(type);
}
public static int getIdCategoryByName(string NameCategory)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("getIDCategoryByName", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IdCategory", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@NameCategory", SqlDbType.NVarChar, 100);
cmd.Parameters["@NameCategory"].Value = NameCategory;
cnn.Open();
cmd.ExecuteNonQuery();
int idCategory = Convert.ToInt32(cmd.Parameters["@IdCategory"].Value);
cnn.Close();
return(idCategory);
}
public static int isExistedFood(string name)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("isExistedFood", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FoodName", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@FoodName"].Value = name;
cnn.Open();
cmd.ExecuteNonQuery();
int kq = Convert.ToInt32(cmd.Parameters["@Result"].Value);
cnn.Close();
return(kq);
}
public static void AddFood(Food_DTO food)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("AddFood", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@IdCategory", SqlDbType.Int);
cmd.Parameters.Add("@IdUnit", SqlDbType.Int);
cmd.Parameters.Add("@Price", SqlDbType.Float);
cmd.Parameters["@Name"].Value = food.Name;
cmd.Parameters["@IdCategory"].Value = food.IdCategory;
cmd.Parameters["@IdUnit"].Value = food.IdUnit;
cmd.Parameters["@Price"].Value = food.Price;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public static int Status(string name, string password)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("LogIn", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 1000);
cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@UserName"].Value = name;
cmd.Parameters["@Password"].Value = password;;
cnn.Open();
cmd.ExecuteNonQuery();
int kq = Convert.ToInt32(cmd.Parameters["@Result"].Value);
cnn.Close();
return(kq);
}
public static void EditShopInfo(ShopInfo_DTO shop)
{
SqlConnection cnn = ConnectToSQL.Connect();
SqlCommand cmd = new SqlCommand("editShopInfo", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd.Parameters["@Name"].Value = shop.Name;
cmd.Parameters.Add("@Wifi", SqlDbType.NVarChar, 200);
cmd.Parameters["@Wifi"].Value = shop.Wifi;
cmd.Parameters.Add("@Facebook", SqlDbType.NVarChar, 200);
cmd.Parameters["@Facebook"].Value = shop.Facebook;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 200);
cmd.Parameters["@Address"].Value = shop.Address;
cmd.Parameters.Add("@OpenAndCloseTime", SqlDbType.NVarChar, 200);
cmd.Parameters["@OpenAndCloseTime"].Value = shop.Time;
cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 200);
cmd.Parameters["@Phone"].Value = shop.Phone;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}