public List <string> GetAllSurveyNames()
{
SurveyInformationDataContext repository = SurveyDataSource.ContactDataContext();
return((from surveyTemplate
in repository.SurveyTemplates
join survey
in repository.Surveys
on surveyTemplate.FormName equals survey.FormName
select surveyTemplate.FormName).Distinct <string>().ToList <string>());
}
public List <string> GetSurveyNames(DateTime?startDate, DateTime?endDate)
{
SurveyInformationDataContext repository = SurveyDataSource.ContactDataContext();
return((from surveyTemplate
in repository.SurveyTemplates
join survey
in repository.Surveys
on surveyTemplate.FormName equals survey.FormName
where survey.DateCreated >= startDate &&
survey.DateCreated <= endDate
select surveyTemplate.FormName).Distinct <string>().ToList <string>());
}
public string GetSurveyPath(string surveyName)
{
string surveyPath = "";
using (SurveyInformationDataContext repository = SurveyDataSource.ContactDataContext())
{
List <Survey> surveys = (from survey in repository.Surveys select survey).ToList <Survey>();
surveyPath = (from surveyTemplate
in repository.SurveyTemplates
where (surveyTemplate.FormName == surveyName)
select surveyTemplate.SurveyPath).FirstOrDefault <string>();
}
return(surveyPath);
}
private int InsertItem(SurveyForm form)
{
ValidateForm(form);
using (SurveyInformationDataContext repository = SurveyDataSource.ContactDataContext())
{
Survey survey = new Survey
{
FormName = form.FormName,
FormData = form.FormData,
DateCreated = DateTime.Now
};
repository.Surveys.InsertOnSubmit(survey);
repository.SubmitChanges();
return(survey.SurveyID);
}
}
public List <AggregateResult> GetSurveySummary(string surveyName, DateTime?startDate, DateTime?endDate)
{
XmlDocument xml = new XmlDocument();
string surveyPath = GetSurveyPath(surveyName);
object[] parameters = new object[2];
List <AggregateResult> results;
StringBuilder allQuestionQuery = new StringBuilder();
string singleQuestionQuery = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" '{0}' AS QuestionName,")
.AppendLine(" CONVERT(VARCHAR, FormData.query('data(/root/{0})')) AS Answer")
.AppendLine("FROM")
.AppendLine(" Surveys")
.AppendLine("WHERE")
.AppendLine(" DateCreated >= {1}")
.AppendLine("AND")
.AppendLine(" DateCreated <= {2}")
.ToString();
string outerQuery = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" S.QuestionName,")
.AppendLine(" S.Answer,")
.AppendLine(" COUNT(*) AS Total")
.AppendLine("FROM")
.AppendLine(" ({0}) AS S")
.AppendLine("WHERE")
.AppendLine(" RTRIM(LTRIM(S.Answer)) <> ''")
.AppendLine("GROUP BY")
.AppendLine(" S.QuestionName, S.Answer")
.AppendLine("ORDER BY")
.AppendLine("S.QuestionName")
.ToString();
StringBuilder query = new StringBuilder();
parameters[0] = startDate;
parameters[1] = endDate;
xml.Load(surveyPath);
XmlNodeList nodes = xml.SelectNodes("//Option");
foreach (XmlNode node in nodes)
{
if (node.Attributes["type"] != null && (node.Attributes["type"].Value == "radio" || node.Attributes["type"].Value == "check"))
{
string questionName = node.Attributes["id"].Value;
if (allQuestionQuery.ToString() != "")
{
allQuestionQuery.AppendLine("UNION ALL");
}
allQuestionQuery.AppendFormat(singleQuestionQuery, questionName, "{0}", "{1}");
}
}
query.AppendFormat(outerQuery, allQuestionQuery);
using (SurveyInformationDataContext repository = SurveyDataSource.ContactDataContext())
{
results = repository.ExecuteQuery <AggregateResult>(query.ToString(), parameters).ToList <AggregateResult>();
}
return(results);
}
public DataTable GetSurveyDetails(string surveyName, DateTime?startDate, DateTime?endDate)
{
DataTable returnValue = new DataTable(surveyName);
string surveyPath = GetSurveyPath(surveyName);
XDocument surveyDefinition = XDocument.Load(surveyPath);
XmlDocument xml = new XmlDocument();
//var results;
Dictionary <string, string> columns = new Dictionary <string, string>();
returnValue.Columns.Add("Date");
using (SurveyInformationDataContext repository = SurveyDataSource.ContactDataContext())
{
var results = (from survey
in repository.Surveys
where survey.FormName == surveyName &&
startDate < survey.DateCreated &&
survey.DateCreated < endDate
select new { Date = survey.DateCreated, FormDate = XDocument.Parse(survey.FormData.ToString()) });
xml.Load(surveyPath);
XmlNodeList nodes = xml.SelectNodes("//Option");
foreach (XmlNode node in nodes)
{
XmlAttribute type = node.Attributes["type"];
XmlAttribute id = node.Attributes["id"];
if (type != null && id != null)
{
string prefix;
switch (type.Value)
{
case "name":
case "email":
case "company":
case "phone":
case "address":
case "city":
case "zip":
case "website":
prefix = "t";
break;
case "usstates":
case "states":
case "allstates":
case "usterritories":
case "canadianprovinces":
prefix = "dd";
break;
default:
prefix = "";
break;
}
string questionName = id.Value;
columns.Add(questionName, prefix + questionName);
returnValue.Columns.Add(questionName);
XmlNodeList otherNodes = node.SelectNodes("Option[@type='other']");
foreach (XmlNode otherNode in otherNodes)
{
string value = otherNode.Attributes["value"] == null ? "" : otherNode.Attributes["value"].Value;
questionName = value + "_other";
columns.Add(questionName, questionName);
returnValue.Columns.Add(questionName);
}
}
}
foreach (var survey in results)
{
DataRow row = returnValue.NewRow();
row["Date"] = survey.Date.ToString();
foreach (KeyValuePair <string, string> column in columns)
{
string response = (from answer
in survey.FormDate.Descendants(column.Value)
select answer.Value.ToString()).FirstOrDefault <string>();
row[column.Key] = response;
}
returnValue.Rows.Add(row);
}
}
return(returnValue);
}