public Data.Table Learning()
{
Data.Table tbl = new Data.Table();
tbl.Add("name", new Data.Object()
{
{ "name", "Summary" }, { "key", "Web Service" }, { "analyze", false }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Topics" }, { "key", "Learning Topic Name*" }, { "analyze", false }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Instructors" }, { "key", "Learning Instructor" }, { "analyze", false }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Blended Courses" }, { "key", "Course Title*" }, { "analyze", true }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Course Offerings" }, { "key", "Blended Course*" }, { "analyze", true }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Digital Course" }, { "key", "Course Title*_1" }, { "analyze", true }
});
return(tbl);
}
protected async Task <Data.TableSet> FormatData(DataTableCollection tables)
{
Data.Table config = (new Config()).Learning();
return(await Task.Run(() =>
{
Data.TableSet tableSet = new Data.TableSet();
foreach (DataTable _temp in tables)
{
Data.TableList liTable = new Data.TableList();
foreach (DataRow row in _temp.Rows)
{
var obj = new Data.Object();
foreach (var attribute in row.Table.Columns)
{
obj.Add(attribute.ToString(), row[attribute.ToString()]);
}
;
if (!String.IsNullOrEmpty(obj[config[row.Table.TableName]["key"].ToString()].ToString()))
{
liTable.Add(config[_temp.TableName.ToString()]["key"].ToString(), obj);
}
}
;
Data.Table tbl = new Data.Table();
foreach (var list in liTable.Objects)
{
var properties = new Dictionary <string, List <string> >();
foreach (var item in list)
{
foreach (var p in item)
{
if (properties.ContainsKey(p.Key) && !String.IsNullOrEmpty(p.Value.ToString()))
{
properties[p.Key.ToString()].Add(p.Value.ToString());
List <string> li = properties[p.Key]
.Distinct()
.ToList();
properties[p.Key.ToString()] = li;
}
else if (!String.IsNullOrEmpty(p.Value.ToString()))
{
List <string> li = new List <string>();
li.Add(p.Value.ToString());
properties.Add(p.Key.ToString(), li);
}
}
}
tbl.Add(config[_temp.TableName.ToString()]["key"].ToString(), new Data.Object()
{
{ config[_temp.TableName.ToString()]["key"].ToString(), list.First()[config[_temp.TableName.ToString()]["key"].ToString()] }, { "properties", properties }
});
}
tableSet.Add(_temp.TableName.ToString(), tbl);
}
;
return tableSet;
}));
}
protected async Task <DataTableCollection> LoadData(string fiPath)
{
Data.Table config = (new Config()).Learning();
ExcelDataSetConfiguration _loadConfig = new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
FilterSheet = (tableReader, sheetIndex) =>
{
if (tableReader.VisibleState == "visible" && config.ContainsKey(tableReader.Name))
{
return(true);
}
else
{
return(false);
}
},
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
EmptyColumnNamePrefix = "Column",
UseHeaderRow = true,
ReadHeaderRow = (rowReader) =>
{
switch (rowReader.Name)
{
case "Topics":
for (int i = 0; i < 15; i++)
{
rowReader.Read();
}
break;
default:
for (int i = 0; i < 4; i++)
{
rowReader.Read();
}
break;
}
}
}
};
return(await Task.Run(() =>
{
Excel xl = new Excel(fiPath, _loadConfig);
return xl.Data;
}));
}
public Data.Table AnalyzeClick()
{
Data.Table tbl = new Data.Table();
tbl.Add("name", new Data.Object()
{
{ "name", "Load" }, { "message", "Loading Data..." }, { "data", null }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Format" }, { "message", "Formatting Data..." }, { "data", null }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Analyze" }, { "message", "Analyzing Data..." }, { "data", null }
});
tbl.Add("name", new Data.Object()
{
{ "name", "Export" }, { "message", "Exporting Data..." }, { "data", null }
});
return(tbl);
}
protected async Task <DataTable> AnalyzeData(Data.TableSet tblSet)
{
return(await Task.Run(() =>
{
List <string> CheckPropertyValue(string k, string v, string s, KeyValuePair <string, Data.Object> obj, Dictionary <string, List <string> > _properties)
{
List <string> _defects = new List <string>();
List <string> _list = new List <string>();
bool _checker = true;
switch (k)
{
case "Allowed Instructor+":
case "Instructors*+*":
_checker = tblSet["instructors"].ContainsKey(v);
if (!_checker)
{
_defects.Add("Instructor not found in lookup table");
}
break;
case "Topic*+*":
_checker = tblSet["topics"].ContainsKey(v);
if (!_checker)
{
_defects.Add("Topic not found in lookup table");
}
break;
case "Effective Date*" when s == "Blended Courses" || s == "Digital Course":
_checker = (v == "01/01/1900" || v == "1/1/1900");
if (!_checker)
{
_defects.Add("Topic not found in lookup table");
}
break;
case "Blended Course*" when s == "Course Offerings":
_checker = tblSet["Blended Courses"].ContainsKey(v);
if (!_checker)
{
_defects.Add("Blended Course not found in lookup table");
}
if (_properties.ContainsKey("Min Enrollment Capacity") || _properties.ContainsKey("Max Enrollment Capacity") || _properties.ContainsKey("Enable Auto Enrollment from Waitlist") || _properties.ContainsKey("Waitlist Capacity"))
{
if (_properties.ContainsKey("Min Enrollment Capacity") && _properties.ContainsKey("Max Enrollment Capacity") && _properties.ContainsKey("Enable Auto Enrollment from Waitlist") && _properties.ContainsKey("Waitlist Capacity"))
{
_defects.Add("Min, Max, Auto, or Waitlist Enrollment is undefined");
}
foreach (var trackProperty in _properties["Enable Auto Enrollment from Waitlist"])
{
if (String.IsNullOrEmpty(trackProperty) || (trackProperty != "Y" && trackProperty != "N"))
{
_defects.Add("Enable Auto Enrollment from Waitlist not properly defined");
}
}
}
break;
case "Unlimited Capacity" when s == "Course Offerings":
if (_properties.ContainsKey("Min Enrollment Capacity") || _properties.ContainsKey("Max Enrollment Capacity") || _properties.ContainsKey("Enable Auto Enrollment from Waitlist") || _properties.ContainsKey("Waitlist Capacity"))
{
if (String.IsNullOrEmpty(v) || v != "N")
{
_defects.Add("Unlimited Capacity not properly defined");
}
}
else
{
if (String.IsNullOrEmpty(v) || v != "Y")
{
_defects.Add("Unlimited Capacity not properly defined");
}
}
break;
case "Location" when s == "Course Offerings":
if (tblSet["Blended Courses"].ContainsKey(obj.Key))
{
var _luProperties = tblSet["Blended Courses"][obj.Key]["properties"] as Dictionary <string, List <string> >;
if (_luProperties.TryGetValue("Allowed Location+", out List <string> _location))
{
_checker = _location.Contains(v);
}
}
if (!_checker)
{
_defects.Add("Location for Blended Course not found in value list");
}
break;
case "Title*" when s == "Course Offerings":
if ((!_properties.ContainsKey("Start Date*") || _properties["Start Date*"].Count == 0) || (!_properties.ContainsKey("End Date*") || _properties["End Date*"].Count == 0))
{
_defects.Add("Start or end date undefined for classroom lesson");
}
if (_properties.ContainsKey("Start Date*") || _properties.ContainsKey("End Date*"))
{
if (_properties["Start Date*"].Count > 0)
{
_list.AddRange(_properties["Start Date*"]);
}
if (_properties["End Date*"].Count > 0)
{
_list.AddRange(_properties["End Date*"]);
}
foreach (var dDate in _list)
{
string[] formats =
{
"MM/dd/yyyy hh:mm", "MM/dd/yyyy h:mm", "MM/d/yyyy hh:mm", "MM/d/yyyy h:mm",
"M/dd/yyyy hh:mm", "M/dd/yyyy h:mm", "M/d/yyyy hh:mm", "M/d/yyyy h:mm",
"MM/dd/yyyy hh:mm tt", "MM/dd/yyyy h:mm tt", "MM/d/yyyy hh:mm tt", "MM/d/yyyy h:mm tt",
"M/dd/yyyy hh:mm tt", "M/dd/yyyy h:mm tt", "M/d/yyyy hh:mm tt", "M/d/yyyy h:mm tt",
"MM/dd/yyyy hh:mm:ss", "MM/dd/yyyy h:mm:ss", "MM/d/yyyy hh:mm:ss", "MM/d/yyyy h:mm:ss",
"M/dd/yyyy hh:mm:ss", "M/dd/yyyy h:mm:ss", "M/d/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
"MM/dd/yyyy hh:mm:ss tt", "MM/dd/yyyy h:mm:ss tt", "MM/d/yyyy hh:mm:ss tt", "MM/d/yyyy h:mm:ss tt",
"M/dd/yyyy hh:mm:ss tt", "M/dd/yyyy h:mm:ss tt", "M/d/yyyy hh:mm:ss tt", "M/d/yyyy h:mm:ss tt",
};
DateTime dateValue;
if (!DateTime.TryParseExact(dDate, formats, new CultureInfo("en-US"), DateTimeStyles.None, out dateValue))
{
_defects.Add("Incorrect start or end date format");
}
_list = new List <string>();
}
}
if (_properties.ContainsKey("Instructor Led Unit Track Grades"))
{
if (_properties["Instructor Led Unit Track Grades"].First().ToString() == "Y")
{
if (_properties.ContainsKey("Learning Grading Scheme for Activity Offering"))
{
_checker = _properties["Learning Grading Scheme for Activity Offering"].Contains("126ed79d86c01000101743d2b76e00c6");
}
else
{
_checker = false;
}
if (!_checker)
{
_defects.Add("Instructor Led Unit Track Learning Grading Scheme not properly defined");
}
}
else if (_properties.ContainsKey("Instructor Led Unit Track Grades"))
{
foreach (var trackProperty in _properties["Instructor Led Unit Track Grades"])
{
if (String.IsNullOrEmpty(trackProperty) || (trackProperty != "Y" && trackProperty != "N"))
{
_defects.Add("Instructor Led Unit Track Grades not properly defined");
}
}
}
}
if (_properties.ContainsKey("Instructor Led Unit Track Attendance"))
{
foreach (var trackProperty in _properties["Instructor Led Unit Track Attendance"])
{
if (obj.Key == "Student Living | Turn")
{
Console.WriteLine("test");
}
if (String.IsNullOrEmpty(trackProperty) || (trackProperty != "Y" && trackProperty != "N"))
{
_defects.Add("Instructor Led Unit Track Attendance not properly defined");
}
}
}
break;
case "Title*_1" when s == "Course Offerings":
if (!_properties.ContainsKey("Timezone*") || _properties["Timezone*"].Count == 0)
{
_defects.Add("Webinar timezone undefined");
}
if (!_properties.ContainsKey("Webinar URL*") || _properties["Webinar URL*"].Count == 0)
{
_defects.Add("Webinar URL undefined");
}
if ((_properties.ContainsKey("Webinar URL*") || _properties.ContainsKey("Instructors*+*_1") || _properties.ContainsKey("Title*_1")) && _properties.ContainsKey("Webinar Login Details"))
{
_defects.Add("Webinar Login details defined");
}
if (_properties.ContainsKey("Webinar URL*") && _properties.ContainsKey("Instructors*+*_1") && _properties.ContainsKey("Title*_1"))
{
if (_properties.ContainsKey("Start Date*_1") || _properties.ContainsKey("End Date*_1"))
{
if (_properties["Start Date*_1"].Count > 0)
{
_list.AddRange(_properties["Start Date*_1"]);
}
if (_properties["End Date*_1"].Count > 0)
{
_list.AddRange(_properties["End Date*_1"]);
}
foreach (var dDate in _list)
{
string[] formats =
{
"MM/dd/yyyy hh:mm", "MM/dd/yyyy h:mm", "MM/d/yyyy hh:mm", "MM/d/yyyy h:mm",
"M/dd/yyyy hh:mm", "M/dd/yyyy h:mm", "M/d/yyyy hh:mm", "M/d/yyyy h:mm",
"MM/dd/yyyy hh:mm tt", "MM/dd/yyyy h:mm tt", "MM/d/yyyy hh:mm tt", "MM/d/yyyy h:mm tt",
"M/dd/yyyy hh:mm tt", "M/dd/yyyy h:mm tt", "M/d/yyyy hh:mm tt", "M/d/yyyy h:mm tt",
"MM/dd/yyyy hh:mm:ss", "MM/dd/yyyy h:mm:ss", "MM/d/yyyy hh:mm:ss", "MM/d/yyyy h:mm:ss",
"M/dd/yyyy hh:mm:ss", "M/dd/yyyy h:mm:ss", "M/d/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
"MM/dd/yyyy hh:mm:ss tt", "MM/dd/yyyy h:mm:ss tt", "MM/d/yyyy hh:mm:ss tt", "MM/d/yyyy h:mm:ss tt",
"M/dd/yyyy hh:mm:ss tt", "M/dd/yyyy h:mm:ss tt", "M/d/yyyy hh:mm:ss tt", "M/d/yyyy h:mm:ss tt",
};
DateTime dateValue;
if (!DateTime.TryParseExact(dDate, formats, new CultureInfo("en-US"), DateTimeStyles.None, out dateValue))
{
_defects.Add("Incorrect Webinar start or end date format");
}
_list = new List <string>();
}
}
}
if (_properties.ContainsKey("Instructors*+*_1"))
{
foreach (var instructor in _properties["Instructors*+*_1"])
{
_checker = tblSet["instructors"].ContainsKey(instructor);
if (!_checker)
{
_defects.Add("Webinar Instructor not found in lookup table");
}
}
}
else
{
_defects.Add("Webinar Instructor not found in lookup table");
}
if (_properties.ContainsKey("Webinar Lesson Unit Track Grades"))
{
if (_properties["Webinar Lesson Unit Track Grades"].First().ToString() == "Y")
{
if (_properties.ContainsKey("Learning Grading Scheme for Activity Offering_1"))
{
_checker = _properties["Learning Grading Scheme for Activity Offering_1"].Contains("126ed79d86c01000101743d2b76e00c6");
}
else
{
_checker = false;
}
if (!_checker)
{
_defects.Add("Webinar Learning Grading Scheme not properly defined");
}
}
else if (_properties.ContainsKey("Webinar Lesson Unit Track Grades"))
{
foreach (var trackProperty in _properties["Webinar Lesson Unit Track Grades"])
{
if (String.IsNullOrEmpty(trackProperty) || (trackProperty != "Y" && trackProperty != "N"))
{
_defects.Add("Webinar Lesson Unit Track Grades not properly defined");
}
}
}
}
if (_properties.ContainsKey("Webinar Lesson Unit Track Attendance"))
{
foreach (var trackProperty in _properties["Webinar Lesson Unit Track Attendance"])
{
if (String.IsNullOrEmpty(trackProperty) || (trackProperty != "Y" && trackProperty != "N"))
{
_defects.Add("Webinar Lesson Unit Track Attendance not properly defined");
}
}
}
break;
case "External Content URL*" when s == "Course Offerings":
if (!(_properties.ContainsKey("Learning Course Lesson Title*") && _properties["Learning Course Lesson Title*"].Count > 0))
{
_checker = false;
}
if (!_checker)
{
_defects.Add("Learning course lession title undefined");
}
break;
case "Learning Course Lesson Title*" when s == "Course Offerings":
if (!(_properties.ContainsKey("External Content URL*") && _properties["External Content URL*"].Count > 0))
{
_checker = false;
}
if (!_checker)
{
_defects.Add("External Content URL undefined");
}
break;
case "Media*" when s == "Course Offerings":
if (_properties.ContainsKey("Row ID*_2"))
{
if (_properties["Row ID*_2"].Count < 2)
{
_defects.Add("Media file does not have unique lesson line");
}
}
if (!_properties.ContainsKey("Learning Course Lesson Title*_1"))
{
_defects.Add("Media file does not have unique lesson line");
}
break;
case "Registerable Status" when s == "Course Offerings":
if (!string.Equals(v, "Open", StringComparison.CurrentCultureIgnoreCase) && !string.Equals(v, "Closed", StringComparison.CurrentCultureIgnoreCase))
{
_defects.Add("Registerable status not properly defined");
}
break;
case "Update Status from Offering Dates" when s == "Course Offerings":
if (_properties.ContainsKey("Registerable Status"))
{
if (v == "Y" && _properties["Registerable Status"].Count > 0)
{
_defects.Add("Registerable status defined when Update Status from Offering Dates is true");
}
}
break;
}
return _defects;
}
using (DataTable dt = new DataTable())
{
foreach (var col in new string[] { "sheet", "course", "defect", "property", "value" })
{
dt.Columns.Add(col);
}
Data.Table config = (new Config()).Learning();
foreach (var sheet in tblSet)
{
if ((dynamic)config[sheet.Key]["analyze"])
{
foreach (var obj in sheet.Value)
{
var properties = obj.Value["properties"] as Dictionary <string, List <string> >;
foreach (var property in properties)
{
foreach (var p in property.Value)
{
List <string> _defects = CheckPropertyValue(property.Key, p.ToString(), sheet.Key, obj, properties);
if (_defects.Count > 0)
{
foreach (var _defect in _defects)
{
DataRow dr = dt.NewRow();
dr["sheet"] = sheet.Key;
dr["course"] = obj.Key;
dr["defect"] = _defect;
dr["property"] = property.Key;
dr["value"] = p.ToString();
dt.Rows.Add(dr);
}
}
}
}
}
}
}
return dt;
}
}));
}
private async void Analyze_Click(object sender, EventArgs e)
{
Data.Table config = (new Config()).AnalyzeClick();
Control.Tab tab = new Control.Tab()
{
Button = (Button)sender,
Instance = Activator.CreateInstance(Type.GetType(Assembly.GetExecutingAssembly().GetName().Name + "." + (tabControl.SelectedItem as TabItem).Name))
};
tab.Grid = ((tabControl.SelectedItem as TabItem).Content as Grid).Children.OfType <Grid>().Single(Child => Child.Tag != null && Child.Tag.ToString().ToLower().Contains(tab.Button.Tag.ToString().ToLower()));
tab.ProgressBar = tab.Grid.Children.OfType <ProgressBar>().First();
tab.ProgressDetail = tab.Grid.Children.
OfType <TextBlock>().
Single(Child => Child.Tag != null && Child.Tag.ToString().ToLower().Contains("detail"));
tab.FilePathInput = ((tabControl.SelectedItem as TabItem).Content as Grid).Children.
OfType <TextBox>().
Single(Child => Child.Tag != null && Child.Tag.ToString().ToLower().Contains("input"));
tab.FilePathOutput = ((tabControl.SelectedItem as TabItem).Content as Grid).Children.
OfType <TextBox>().
Single(Child => Child.Tag != null && Child.Tag.ToString().ToLower().Contains("output"));
if (String.IsNullOrEmpty(tab.FilePathInput.Text.ToString()))
{
MessageBox.Show("Import file undefined!", "Confirmation", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else if (String.IsNullOrEmpty(tab.FilePathOutput.Text.ToString()))
{
MessageBox.Show("Export path undefined!", "Confirmation", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
await Dispatcher.InvokeAsync(new Action(() =>
{
tab.Button.IsEnabled = false;
tab.Grid.Visibility = Visibility.Visible;
tab.ProgressBar.IsIndeterminate = true;
}), DispatcherPriority.ContextIdle);
foreach (var task in config)
{
GC.Collect();
await Dispatcher.InvokeAsync(new Action(() =>
{
tab.ProgressDetail.Text = task.Value["message"].ToString();
}), DispatcherPriority.ContextIdle);
MethodInfo mi = tab.Instance.GetType().GetMethod(task.Key + "Data", BindingFlags.NonPublic | BindingFlags.Instance);
if (mi != null)
{
switch (task.Key)
{
case "Load":
tab.Instance.GetType().GetProperty("Load").SetValue(tab.Instance, await(Task <DataTableCollection>) mi.Invoke(tab.Instance, new object[] { tab.FilePathInput.Text.ToString() }), null);
break;
case "Format":
tab.Instance.GetType().GetProperty("Format").SetValue(tab.Instance, await(Task <Data.TableSet>) mi.Invoke(tab.Instance, new object[] { tab.Instance.GetType().GetProperty("Load").GetValue(tab.Instance, null) }), null);
break;
case "Analyze":
tab.Instance.GetType().GetProperty("Analyze").SetValue(tab.Instance, await(Task <DataTable>) mi.Invoke(tab.Instance, new object[] { tab.Instance.GetType().GetProperty("Format").GetValue(tab.Instance, null) }), null);
break;
case "Export":
DataTable dt = tab.Instance.GetType().GetProperty("Analyze").GetValue(tab.Instance, null) as DataTable;
if (dt.Rows.Count > 0)
{
try
{
mi.Invoke(tab.Instance, new object[] { tab.FilePathOutput.Text.ToString(), dt });
await Dispatcher.InvokeAsync(new Action(() =>
{
MessageBox.Show("Complete - Defects exported to file.", "Confirmation", MessageBoxButton.OK, MessageBoxImage.Information);
tab.ProgressDetail.Text = "Complete!";
}), DispatcherPriority.ContextIdle);
try
{
using (FileStream fs = File.Open(tab.FilePathOutput.Text.ToString(), FileMode.Open))
{
Process.Start("excel.exe", tab.FilePathOutput.Text.ToString());
}
}
catch (SecurityException ex)
{
MessageBox.Show($"Security error.\n\nError message: {ex.Message}\n\n" +
$"Details:\n\n{ex.StackTrace}");
}
}
catch
{
await Dispatcher.InvokeAsync(new Action(() =>
{
tab.ProgressDetail.Text = "Failed!";
}), DispatcherPriority.ContextIdle);
}
}
else
{
await Dispatcher.InvokeAsync(new Action(() =>
{
MessageBox.Show("Complete - No defects identified.", "Confirmation", MessageBoxButton.OK, MessageBoxImage.Information);
tab.ProgressDetail.Text = "No defects found!";
}), DispatcherPriority.ContextIdle);
}
break;
default:
break;
}
}
else
{
throw new ArgumentNullException(paramName: nameof(mi), message: "invoke method cannot be null");
}
GC.Collect();
}
await Dispatcher.InvokeAsync(new Action(() =>
{
tab.Grid.Visibility = Visibility.Hidden;
tab.Button.IsEnabled = true;
}), DispatcherPriority.ContextIdle);
GC.Collect();
}