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