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