public static double getMealCalorie(String day, int PlanID, String MealType)
{
DataTable MealDetail = new DataTable("MealDetail");
MealDetail = getMealDetail(PlanID, day, MealType);
double totalCalorie = 0;
decimal foodCalorie = 0;
decimal quantity = 0;
double weight = 0;
double unit = 0;
for (int i = 0; i < MealDetail.Rows.Count; i++)
{
foodCalorie = (decimal)MealDetail.Rows[i]["FoodCalorie"];
unit = CatalogAccess.getFoodUnit((String)MealDetail.Rows[i]["FoodName"]);
// decimal.TryParse((String)MealDetail.Rows[i]["Quantity"], out quantity);
if (!String.IsNullOrEmpty(MealDetail.Rows[i]["Quantity"].ToString()))
{
quantity = (System.Decimal)MealDetail.Rows[i]["Quantity"];
}
else if (!String.IsNullOrEmpty(MealDetail.Rows[i]["Weight"].ToString()))
{
weight = Convert.ToDouble((System.Decimal)MealDetail.Rows[i]["Weight"]);
}
// weight = 1;
if (quantity != 0)
{
totalCalorie += Convert.ToDouble((decimal)foodCalorie * quantity);
}
else if (weight != 0)
{
totalCalorie += ((double)foodCalorie * weight) / unit;
}
}
return(totalCalorie);
}
//for gridview with selection function
public static double getGridViewSumCalorieWithSelection(GridView GridView1)
{
decimal foodCalorie = 0;
int quantity = 0;
decimal weight = 0;
double totalCalorie = 0;
double unit = 0;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
if (decimal.TryParse(GridView1.Rows[i].Cells[4].Text, out foodCalorie) && int.TryParse(GridView1.Rows[i].Cells[2].Text, out quantity))
{
totalCalorie += (double)foodCalorie * quantity;
}
else if (decimal.TryParse(GridView1.Rows[i].Cells[4].Text, out foodCalorie) && decimal.TryParse(GridView1.Rows[i].Cells[3].Text, out weight))
{
unit = CatalogAccess.getFoodUnit(GridView1.Rows[i].Cells[1].Text);
totalCalorie += (double)(foodCalorie * weight) / unit;
}
}
return(totalCalorie);
}
public static String deleteCustomerPlan(int planID, int custID)
{
String message = "";
int effectedRows = 0;
dlPlan dlPlan = new dlPlan();
List <int> mealIDs = CatalogAccess.getMealIDsInMeal(planID);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
SqlConnection conn = GetConnection(builder);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = conn.BeginTransaction("DeleteCustomerPlan");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
cmd.Connection = conn;
cmd.Transaction = transaction;
try
{
//CustomerPlan
cmd.CommandText = "delete from CustomerPlan where PlanID=@PlanID and CustID=@CustID";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PlanID", planID);
cmd.Parameters.AddWithValue("@CustID", custID);
effectedRows += cmd.ExecuteNonQuery();
//FoodDetail
cmd.CommandText = "delete from FoodDetail where MealID=@MealID";
foreach (int mealID in mealIDs)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MealID", mealID);
effectedRows += cmd.ExecuteNonQuery();
}
//Meal
cmd.CommandText = "delete from Meal where PlanID=@PlanID";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PlanID", planID);
effectedRows += cmd.ExecuteNonQuery();
//Plan
cmd.CommandText = "delete from [Plan] where PlanID=@PlanID";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PlanID", planID);
effectedRows += cmd.ExecuteNonQuery();
if (effectedRows > 0)
{
message += "Effeced Rows: " + effectedRows;
}
else
{
message += "Plan " + planID + " does not exist";
}
// Attempt to commit the transaction.
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
message += "Commit Exception Type: " + ex.GetType() + "\tMessage: " + ex.Message;
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
message += "Rollback Exception Type: {0}" + ex2.GetType() + " Message: {0}" + ex2.Message;
}
}
finally
{
conn.Close();
}
return(message);
}