static StaffReportData CreateSchoolStaffEntry(DataRow dr, string QuestionID) { StaffReportData ret = new StaffReportData(); ret.ResponseID = dr["ResponseID"].ToString(); ret.JobCode = dr["JC"].ToString(); ret.Group = dr["SSQ2"].ToString(); ret.QuestionID = QuestionID; if (dr[QuestionID].ToString().Length > 0) { int Score; bool res1 = int.TryParse(dr[QuestionID].ToString(), out Score); if (res1) { ret.Score = Score; } else { ret.QualResponse = dr[QuestionID].ToString(); } } return(ret); }
static void runOEGStaff() { try { //https://survey.qualtrics.com/WRAPI/ControlPanel/api.php?Request=getLegacyResponseData&User=apiuser&Token=lksjdfJdJklajdf3asdae3&Format=XML&SurveyID=SV_123456789 string[] QuestionIDs = new string[] { "OEGS1R", "OEGS1Days", "OEGS2EN", "OEGS2EN_TEXT", "OEGS2R", "OEGS2Days", "OEGS3EN", "OEGS3EN_TEXT", "OEGS3R", "OEGS3Days", "GLQ4", "GLQ5", "GLQ6", "GLQ7", "GLQ7_TEXT", "GLQ8", "GLQ9", "GLQ9_TEXT", "GLQ10", "GLQ11", "GLQ11_TEXT", "GLQ12", "GLQ13", "GLQ13_TEXT", "GLQ14", "GLQ15", "GLQ16", "GLQ17", "GLQ18", "GLQ19", "GLQ20", "GLQ21", "GLQ22", "GLQ23", "GLQ24", "GLQ25", "GLQ26" }; oeg_reportsEntities db = new oeg_reportsEntities(); IEnumerable <Surveys> sv = db.Surveys.Where(x => x.SurveyType == "OEG Staff").ToList(); int sCount = 0; foreach (Surveys s in sv) { sCount++; System.Diagnostics.Debug.WriteLine("Starting OEG Staff survey " + s.SurveyName + "(" + sCount + "/" + sv.Count() + ")"); string Url = "https://survey.qualtrics.com/WRAPI/ControlPanel/api.php?"; string function = "Request=getLegacyResponseData"; string user = "******"; string token = "Token=zlHrARdXDOn6ep1ZKo2Jb8vpBVUMb6odWPkbEPQL"; string list = "SurveyID=" + s.SurveyCode.Trim(); //st caths pre string format = "Format=XML"; string Version = "Version=2.5"; string requestUrl = Url + function + "&" + user + "&" + token + "&" + format + "&" + list + "&" + Version; HttpWebRequest request = WebRequest.Create(requestUrl) as HttpWebRequest; try { HttpWebResponse response = request.GetResponse() as HttpWebResponse; XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(response.GetResponseStream()); XmlNodeReader reader = new XmlNodeReader(xmlDoc); DataSet ds = new DataSet(); ds.ReadXml(reader); reader.Close(); int rCount = 0; foreach (DataRow dr in ds.Tables[0].Rows) { rCount++; //if (rCount == 52) System.Diagnostics.Debug.WriteLine("Now"); //for each response we need to write a record for each question in the response. //only deal with finished repsonses if (dr["Finished"].ToString() == "1") { //does response exist in data string rep = dr["ResponseID"].ToString(); StaffReportData r = db.StaffReportData.Where(x => x.ResponseID == rep).FirstOrDefault(); if (r == null) { System.Diagnostics.Debug.WriteLine("Creating Response Record"); foreach (string q in QuestionIDs) { if (dr.Table.Columns.Contains(q)) { db.StaffReportData.Add(CreateOEGStaffEntry(dr, q)); } } System.Diagnostics.Debug.WriteLine("Adding Response (" + rCount + "/" + ds.Tables[0].Rows.Count + ")"); } } } System.Diagnostics.Debug.WriteLine("Saving survey to DB"); db.SaveChanges(); System.Diagnostics.Debug.WriteLine("Finished survey " + s.SurveyName); } catch (Exception ex) { //problem with this survey kick onto next System.Diagnostics.Debug.WriteLine("PROBLEM!"); } } System.Diagnostics.Debug.WriteLine("Finished Upload"); } catch (Exception e) { System.Diagnostics.Debug.WriteLine(e.Message); } }