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