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