internal WorkRequest[] GetWorkRequest([Optional] WorkRequest matchOnValues)
{
List <WorkRequest> results = new List <WorkRequest>();
string connString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
try
{
string sql = "Select * from [data].[Requests] ";
if (matchOnValues != null)
{
var properties = matchOnValues.GetType().GetProperties();
sql += "Where ";
int numProps = properties.Length;
for (int i = 0; i < numProps; i++)
{
var key = properties[i].Name;
var value = properties[i].GetValue(matchOnValues);
if (value == null)
{
continue;
}
sql += " " + key + " = '" + value + "' ";
if (numProps > 0 && i < numProps - 1)
{
sql += " AND";
}
}
if (sql.EndsWith("AND"))
{
sql = sql.Remove(sql.Length - 3);
}
}
DataTable dt = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(sql, connString))
da.Fill(dt);
foreach (var row in dt.AsEnumerable())
{
try
{
results.Add(
new WorkRequest
{
RequestID = row.Field <dynamic>("RequestID"),
Requestor = row.Field <dynamic>("Requestor"),
Title = unescape(row.Field <dynamic>("Title")),
Manager = row.Field <dynamic>("Manager"),
Goal = unescape(row.Field <dynamic>("Goal")),
// Problem field deprecated, Iteration 9, January 2018
// Problem = unescape(row.Field<dynamic>("Problem")),
BusinessValueUnit = row.Field <dynamic>("BusinessValueUnit"),
BusinessValueAmount = row.Field <dynamic>("BusinessValueAmount"),
RequestedCompletionDate = row.Field <dynamic>("RequestedCompletionDate"),
// Quantification field deprecated, Iteration 6, December 2017, replaced by BusinessValueUnit/BusinessValueAmount
// Quantification = unescape(row.Field<dynamic>("Quantification")),
// Benefit field deprecated, Iteration 9, January 2018
// Benefit = unescape(row.Field<dynamic>("Benefit")),
Status = row.Field <dynamic>("Status"),
StatusDate = row.Field <dynamic>("StatusDate"),
LastModified = row.Field <dynamic>("LastModified"),
CorporateGoals = JsonConvert.DeserializeObject <List <string> >(row.Field <dynamic>("CorporateGoals")),
GoalSupport = unescape(row.Field <dynamic>("GoalSupport")),
SupportsDept = row.Field <dynamic>("SupportsDept"),
DeptGoalSupport = unescape(row.Field <dynamic>("DeptGoalSupport")),
ConditionsOfSatisfaction = unescape(row.Field <dynamic>("ConditionsOfSatisfaction")),
NonImplementImpact = unescape(row.Field <dynamic>("NonImplementImpact")),
RealizationOfImpact = unescape(row.Field <dynamic>("RealizationOfImpact"))
});
}
catch (Exception ex)
{
Error.Log(ex);
}
}
if (results.Count < 1)
{
throw new KeyNotFoundException("No matching Work Request was returned from your search: " +
JsonConvert.SerializeObject(matchOnValues));
}
return(results.ToArray <WorkRequest>());
}
catch (Exception ex)
{
Error.Log(ex);
return(new WorkRequest[0]); //empty
}
}