/// <summary> /// Reads route set decisions from the last 26 hours. /// </summary> /// <returns></returns> public static IList<GrittingDecision> ReadLatestRouteSetDecisions() { var decisions = new List<GrittingDecision>(); using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionReader"].ConnectionString)) { using (var reader = conn.ExecuteReader("usp_RouteSetDecision_SelectSummary", commandType: CommandType.StoredProcedure)) { while (reader.Read()) { var decision = new RouteSetDecision(); decision.DecisionId = Int32.Parse(reader["RouteSetDecisionId"].ToString(), CultureInfo.InvariantCulture); decision.RouteSet.RouteSetName = reader["RouteSet_Web"].ToString(); decision.Action = reader["Action_Web"].ToString(); decision.DecisionTime = DateTime.Parse(reader["DecisionTime"].ToString(), CultureInfo.CurrentCulture); if (reader["ActionTime"] != DBNull.Value) decision.ActionTime = DateTime.Parse(reader["ActionTime"].ToString(), CultureInfo.CurrentCulture); decisions.Add(decision); } } } return decisions; }
public static IList<GrittingDecision> ReadRouteSetDecisions(int pageSize, int pageNumber, out int totalDecisions) { var decisions = new List<GrittingDecision>(); var parameters = new DynamicParameters(); if (pageSize == -1) { parameters.Add("@pageSize", null); } else { parameters.Add("@pageSize", pageSize); } if (pageNumber == -1) { parameters.Add("@pageNumber", null); } else { parameters.Add("@pageNumber", pageNumber); } parameters.Add("@totalDecisions", null, DbType.Int32, ParameterDirection.Output); // Read decisions. Important to include CommandType.StoredProcedure otherwise output parameter always null using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionReader"].ConnectionString)) { using (var reader = conn.ExecuteReader("usp_RouteSetDecision_Select", parameters, commandType: CommandType.StoredProcedure)) { while (reader.Read()) { var decision = new RouteSetDecision(); decision.DecisionId = Int32.Parse(reader["RouteSetDecisionId"].ToString(), CultureInfo.InvariantCulture); decision.RouteSet.RouteSetName = reader["RouteSet_Web"].ToString(); decision.OriginalAction = reader["Action_Original"].ToString(); decision.Action = reader["Action_Web"].ToString(); decision.DecisionTime = DateTime.Parse(reader["DecisionTime"].ToString(), CultureInfo.CurrentCulture); if (reader["ActionTime"] != DBNull.Value) decision.ActionTime = DateTime.Parse(reader["ActionTime"].ToString(), CultureInfo.CurrentCulture); decisions.Add(decision); } } } // Wait until reader closed before getting access to output parameter totalDecisions =parameters.Get<int>("@totalDecisions"); return decisions; }
/// <summary> /// Save a decision for a route set /// </summary> /// <param name="decision">The decision to save</param> /// <param name="decisionText">The original decision text from the supplier's system, if any.</param> public static void SaveRouteSetDecision(RouteSetDecision decision, string decisionText) { var parameters = new DynamicParameters(); parameters.Add("@decisionText", decisionText); parameters.Add("@messageType", decision.MessageType); parameters.Add("@routeSetInternal", decision.RouteSet.RouteSetNameInternal); parameters.Add("@routeSetWeb", decision.RouteSet.RouteSetName); parameters.Add("@actionOriginal", decision.OriginalAction); parameters.Add("@actionWeb", decision.Action); parameters.Add("@decisionTime", decision.DecisionTime); parameters.Add("@actionTime", decision.ActionTime); parameters.Add("@notes", decision.Notes); parameters.Add("@routeSetDecisionId", null, DbType.Int32, ParameterDirection.Output); using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionWriter"].ConnectionString)) { conn.Execute("usp_RouteSetDecision_Save", parameters, commandType: CommandType.StoredProcedure); var decisionId = parameters.Get<int>("@routeSetDecisionId"); foreach (RouteDecision route in decision.RouteDecisions) { var routeParameters = new DynamicParameters(); routeParameters.Add("@routeSetDecisionId", decisionId); routeParameters.Add("@supplierDecisionId", route.SupplierDecisionId); conn.Execute("usp_RouteSetDecision_LinkToRouteDecision", routeParameters, commandType: CommandType.StoredProcedure); } } }