public static int getTrackedPlanByCustID(int custID)
{
dlPlan dlP = new dlPlan();
int trackedPlanID = 0;
List <int> customerPlanIDs = dlP.getCustomerPlanIDs(custID);
List <int> trackedPlanIDs = dlP.getTrackedPlanIDs();
List <int> trackedCustomerPlanIDs = customerPlanIDs.Intersect(trackedPlanIDs).ToList();
if (trackedCustomerPlanIDs.Count() == 1)
{
trackedPlanID = trackedCustomerPlanIDs[0];
}
return(trackedPlanID);
}
public static int getTrackedPlanByCustID(int custID)
{
dlPlan dlP = new dlPlan();
int trackedPlanID = 0;
List<int> customerPlanIDs = dlP.getCustomerPlanIDs(custID);
List<int> trackedPlanIDs = dlP.getTrackedPlanIDs();
List<int> trackedCustomerPlanIDs = customerPlanIDs.Intersect(trackedPlanIDs).ToList();
if (trackedCustomerPlanIDs.Count() == 1)
{
trackedPlanID = trackedCustomerPlanIDs[0];
}
return trackedPlanID;
}
//copy system provided plan into a new plan owned by user
public static int convertSystemPlanToUserPlan(int PlanID, int CustID, String planDesc)
{
int rowsAffected = 0;
dlPlan dlP = new dlPlan();
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
SqlConnection conn = GetConnection(builder);
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();
conn.Open();
DataTable FoodDetail = dlP.getFoodDetailTable(PlanID);
DataTable Meal = dlP.getMealTable(PlanID);
DataTable Plan = dlP.getPlanTable(PlanID);
//the planID user is gonig to generate
int newPlanID = dlP.getMaxPlanID() + 1;
DateTime now = DateTime.Now;
for (int i = 0; i < Plan.Rows.Count; i++)
{
Plan.Rows[i]["PlanID"] = newPlanID;
Plan.Rows[i]["CreatedDate"] = now;
Plan.Rows[i]["LastModifiedDate"] = now;
Plan.Rows[i]["Customed"] = true;
Plan.Rows[i]["PlanDesc"] = planDesc;
}
for (int i = 0; i < Meal.Rows.Count; i++)
{
Meal.Rows[i]["PlanID"] = newPlanID;
if (Meal.Columns.Contains("MealID"))
{
Meal.Columns.Remove("MealID");
}
}
//populate Plan table
string sql1 = "INSERT INTO [Plan] (PlanID, CreatedDate, LastModifiedDate, Customed, Categories, PlanDesc, Tracked ) VALUES (@PlanID, @CreatedDate, @LastModifiedDate, @Customed, @Categories, @PlanDesc, @Tracked)";
foreach (DataRow r in Plan.Rows)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql1;
cmd.Parameters.AddWithValue("@PlanID", r["PlanID"]);
cmd.Parameters.AddWithValue("@CreatedDate", r["CreatedDate"]);
cmd.Parameters.AddWithValue("@LastModifiedDate", r["LastModifiedDate"]);
cmd.Parameters.AddWithValue("@Customed", r["Customed"]);
cmd.Parameters.AddWithValue("@Categories", r["Categories"]);
cmd.Parameters.AddWithValue("@PlanDesc", r["PlanDesc"]);
cmd.Parameters.AddWithValue("@Tracked", r["Tracked"]);
rowsAffected += cmd.ExecuteNonQuery();
}
//populate Meal table
string sql2 = "INSERT INTO Meal (MealType, Day, PlanID) VALUES (@MealType, @Day, @PlanID)";
foreach (DataRow r in Meal.Rows)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql2;
cmd.Parameters.AddWithValue("@MealType", r["MealType"]);
cmd.Parameters.AddWithValue("@Day", r["Day"]);
cmd.Parameters.AddWithValue("@PlanID", r["PlanID"]);
rowsAffected += cmd.ExecuteNonQuery();
}
conn.Close();
int j = 0;
//after insert meal table
List<int> MealIDs = getMealIDsInMeal(newPlanID);
for (int i = 0; i < FoodDetail.Rows.Count; i++)
{
int nextMealID = 0;
int currentMealID = (int)FoodDetail.Rows[i]["MealID"];
if (i < FoodDetail.Rows.Count - 1)
{
nextMealID = (int)FoodDetail.Rows[i + 1]["MealID"];
}
if (currentMealID != nextMealID)
{
FoodDetail.Rows[i]["MealID"] = MealIDs[j];
j++;
}
else
{
FoodDetail.Rows[i]["MealID"] = MealIDs[j];
}
}
//populate FoodDetail table
string sql3 = "INSERT INTO FoodDetail (MealID, FoodID, Quantity, Weight) VALUES (@MealID, @FoodID, @Quantity,@Weight)";
conn.Open();
foreach (DataRow r in FoodDetail.Rows)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql3;
cmd.Parameters.AddWithValue("@MealID", r["MealID"]);
cmd.Parameters.AddWithValue("@FoodID", r["FoodID"]);
cmd.Parameters.AddWithValue("@Quantity", r["Quantity"]);
cmd.Parameters.AddWithValue("@Weight", r["Weight"]);
rowsAffected += cmd.ExecuteNonQuery();
}
conn.Close();
rowsAffected += dlP.insert_Into_CustomerPlan(newPlanID, CustID);
return rowsAffected;
}
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;
}
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);
}
//copy system provided plan into a new plan owned by user
public static int convertSystemPlanToUserPlan(int PlanID, int CustID, String planDesc)
{
int rowsAffected = 0;
dlPlan dlP = new dlPlan();
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
SqlConnection conn = GetConnection(builder);
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();
conn.Open();
DataTable FoodDetail = dlP.getFoodDetailTable(PlanID);
DataTable Meal = dlP.getMealTable(PlanID);
DataTable Plan = dlP.getPlanTable(PlanID);
//the planID user is gonig to generate
int newPlanID = dlP.getMaxPlanID() + 1;
DateTime now = DateTime.Now;
for (int i = 0; i < Plan.Rows.Count; i++)
{
Plan.Rows[i]["PlanID"] = newPlanID;
Plan.Rows[i]["CreatedDate"] = now;
Plan.Rows[i]["LastModifiedDate"] = now;
Plan.Rows[i]["Customed"] = true;
Plan.Rows[i]["PlanDesc"] = planDesc;
}
for (int i = 0; i < Meal.Rows.Count; i++)
{
Meal.Rows[i]["PlanID"] = newPlanID;
if (Meal.Columns.Contains("MealID"))
{
Meal.Columns.Remove("MealID");
}
}
//populate Plan table
string sql1 = "INSERT INTO [Plan] (PlanID, CreatedDate, LastModifiedDate, Customed, Categories, PlanDesc, Tracked ) VALUES (@PlanID, @CreatedDate, @LastModifiedDate, @Customed, @Categories, @PlanDesc, @Tracked)";
foreach (DataRow r in Plan.Rows)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql1;
cmd.Parameters.AddWithValue("@PlanID", r["PlanID"]);
cmd.Parameters.AddWithValue("@CreatedDate", r["CreatedDate"]);
cmd.Parameters.AddWithValue("@LastModifiedDate", r["LastModifiedDate"]);
cmd.Parameters.AddWithValue("@Customed", r["Customed"]);
cmd.Parameters.AddWithValue("@Categories", r["Categories"]);
cmd.Parameters.AddWithValue("@PlanDesc", r["PlanDesc"]);
cmd.Parameters.AddWithValue("@Tracked", r["Tracked"]);
rowsAffected += cmd.ExecuteNonQuery();
}
//populate Meal table
string sql2 = "INSERT INTO Meal (MealType, Day, PlanID) VALUES (@MealType, @Day, @PlanID)";
foreach (DataRow r in Meal.Rows)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql2;
cmd.Parameters.AddWithValue("@MealType", r["MealType"]);
cmd.Parameters.AddWithValue("@Day", r["Day"]);
cmd.Parameters.AddWithValue("@PlanID", r["PlanID"]);
rowsAffected += cmd.ExecuteNonQuery();
}
conn.Close();
int j = 0;
//after insert meal table
List <int> MealIDs = getMealIDsInMeal(newPlanID);
for (int i = 0; i < FoodDetail.Rows.Count; i++)
{
int nextMealID = 0;
int currentMealID = (int)FoodDetail.Rows[i]["MealID"];
if (i < FoodDetail.Rows.Count - 1)
{
nextMealID = (int)FoodDetail.Rows[i + 1]["MealID"];
}
if (currentMealID != nextMealID)
{
FoodDetail.Rows[i]["MealID"] = MealIDs[j];
j++;
}
else
{
FoodDetail.Rows[i]["MealID"] = MealIDs[j];
}
}
//populate FoodDetail table
string sql3 = "INSERT INTO FoodDetail (MealID, FoodID, Quantity, Weight) VALUES (@MealID, @FoodID, @Quantity,@Weight)";
conn.Open();
foreach (DataRow r in FoodDetail.Rows)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql3;
cmd.Parameters.AddWithValue("@MealID", r["MealID"]);
cmd.Parameters.AddWithValue("@FoodID", r["FoodID"]);
cmd.Parameters.AddWithValue("@Quantity", r["Quantity"]);
cmd.Parameters.AddWithValue("@Weight", r["Weight"]);
rowsAffected += cmd.ExecuteNonQuery();
}
conn.Close();
rowsAffected += dlP.insert_Into_CustomerPlan(newPlanID, CustID);
return(rowsAffected);
}
