protected void Page_Load(object sender, EventArgs e)
{
//(string keywords, string formAlias, string occurring, int maxResults=25
SimpleFormsWebService service = new SimpleFormsWebService();
service.GetSubmissions(request.Params["keywords"], request.Params["formAlias"], request.Params["occurring"], Convert.ToInt32(request.Params["maxResults"]));
string attachment = "attachment; filename=" + request.Params["formAlias"] + ".xls";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Pragma", "public");
StringBuilder sb = new StringBuilder();
//headers
FormSchema formSchema = new FormSchema(request.Params["formAlias"], false);
sb.Append("Id\t");
sb.Append("Date/Time\t");
sb.Append("IP\t");
foreach (FormField formField in formSchema.FormFields)
{
sb.Append(HttpUtility.UrlDecode(formField.name) + "\t");
}
sb.Append(Environment.NewLine);
//data
foreach (ResultRow row in service.Submissions)
{
sb.Append(row.ID + "\t");
sb.Append(row.dateTime + "\t");
sb.Append(row.IP + "\t");
foreach (string value in row.values)
{
//strip out html breaks
sb.Append(value.Replace("<br/>", " ")+"\t");
}
sb.Append(Environment.NewLine);
}
HttpContext.Current.Response.Write(sb.ToString());
HttpContext.Current.Response.End();
}
public SimpleForm(UserControl userControl)
{
this.userControl = userControl;
this.form = FindForm(userControl);
if (form == null)
{
throw new Exception("Form could not be found.");
}
if (form.ID == null)
{
throw new Exception("Form ID not set. The form ID is needed as this is the name of the table which the data will be stored.");
}
//grab the form controls
LoadControls();
//create a schema
formSchema = new FormSchema(form.ID);
}
public Dictionary<string, string> GetSubmissions(string keywords, string formAlias, string occurring, int maxResults=25)
{
SimpleFormsCore.Authorize();
List<Entry> entries = new List<Entry>();
string[] keywordList = new string[3]{"","",""};
int index = 0;
foreach (string keyword in HttpUtility.UrlDecode(keywords).Split(' '))
{
keywordList[index] = keyword;
index++;
}
string begDate = "";
string endDate = "";
string occuringSQL = "";
if (occurring != "")
{
begDate = DateTime.Now.ToString("yyyy-MM-dd");
endDate = DateTime.Now.AddDays(Convert.ToInt32(occurring)).ToString("yyyy-MM-dd");
occuringSQL = " AND [datetime]<='" + begDate + " 23:59:59'" + " AND [datetime]>='" + endDate + " 00:00:00'";
}
try
{
IParameter[] parameters = new IParameter[4];
parameters[0] = FormSchema.SqlHelper.CreateParameter("@alias", formAlias);
parameters[1] = FormSchema.SqlHelper.CreateParameter("@key1", "%"+keywordList[0]+"%");
parameters[2] = FormSchema.SqlHelper.CreateParameter("@key2", "%" + keywordList[1] + "%");
parameters[3] = FormSchema.SqlHelper.CreateParameter("@key3", "%" + keywordList[2] + "%");
IRecordsReader reader = FormSchema.SqlHelper.ExecuteReader(@"
SELECT
fs.submissionID, ff.formID, IP, [datetime], entryID, ff.fieldID, value, sortOrder
FROM SimpleFormsSubmissions fs
LEFT JOIN
SimpleForms sf on sf.formID=fs.formID
LEFT JOIN
SimpleFormsEntries fe on fe.submissionID=fs.submissionID
LEFT JOIN
SimpleFormsFields ff on ff.fieldID=fe.fieldID
WHERE sf.alias=@alias
AND fs.submissionID IN (
SELECT submissionID
FROM SimpleFormsEntries
WHERE value LIKE @key1
)
AND fs.submissionID IN (
SELECT submissionID
FROM SimpleFormsEntries
WHERE value LIKE @key2
)
AND fs.submissionID IN (
SELECT submissionID
FROM SimpleFormsEntries
WHERE value LIKE @key3
)
"+occuringSQL+@"
ORDER BY submissionID DESC
", parameters);
int submissionID=0;
Entry thisEntry = new Entry();//ini
while (reader.Read())
{
int recordSubmissionID=reader.Get<int>("submissionID");
//create a new Entry if a new submissionID
if (submissionID != recordSubmissionID)
{
if (entries.Count() == maxResults)
{
break;
}
thisEntry= new Entry()
{
ID=reader.Get<int>("submissionID"),
dateTime = reader.Get<DateTime>("datetime").ToUniversalTime().ToString(),
IP = reader.Get<string>("IP")
};
entries.Add(thisEntry);
thisEntry.fields.Add(new SavedValue() { value = reader.Get<string>("value"), fieldID = reader.Get<int>("fieldID")});
submissionID = recordSubmissionID;
}
else
{
//otherwise add in the fields only
thisEntry.fields.Add(new SavedValue() { value = reader.Get<string>("value"), fieldID = reader.Get<int>("fieldID")});
}
}
reader.Dispose();
FormSchema.SqlHelper.Dispose();
//get the form schema for headers
FormSchema formSchema = new FormSchema(formAlias, false);
List<ResultRow> finalResults = new List<ResultRow>();
foreach (Entry entry in entries)
{
ResultRow resultRow = new ResultRow()
{
ID=entry.ID,
dateTime = entry.dateTime,
IP = entry.IP,
values=new List<string>()
};
//match up the fields
foreach (FormField formField in formSchema.FormFields.OrderBy(o=>o.sortOrder))
{
bool foundField = false;
foreach (SavedValue thisSavedValue in entry.fields)
{
if (thisSavedValue.fieldID == formField.ID)
{
resultRow.values.Add(thisSavedValue.value.Replace(Environment.NewLine, "<br/>"));
foundField = true;
}
}
if (foundField == false)
{
resultRow.values.Add("");
}
}
/*
foreach (SavedValue thisSavedValue in entry.fields)
{
resultRow.values.Add(thisSavedValue.value.Replace(Environment.NewLine, "<br/>"));
}
*/
finalResults.Add(resultRow);
}
//save the results to a class member
submissionResult = finalResults;
returnValue.Add("status", status.SUCCESS.ToString());
returnValue.Add("entries", jsonSerializer.Serialize(finalResults));
return returnValue;
}
catch (Exception e)
{
returnValue.Add("status", status.ERROR.ToString());
returnValue.Add("message", e.Message);
Log.Add(LogTypes.Custom, 0, e.Message+"<br/>\n"+e.StackTrace);
return returnValue;
}
}
protected void Page_Load(object sender, EventArgs e)
{
//set the user and check access
SimpleFormsCore.Authorize();
currentUser = umbraco.BusinessLogic.User.GetCurrent();
userCanPublish = currentUser.GetPermissions(formDocument.Path).Contains('R');
//handle any postbacks
if (Page.IsPostBack)
{
HandlePostback();
}
//get the form schema
formSchema = new FormSchema(template.Alias);
h2 = new HtmlGenericControl("h2");
h2.InnerHtml = "<a class='formFields' href='#'>" + t("Form Fields") + "</a>";
if (currentUser.IsAdmin())
{
settings.Controls.Add(h2);
BuildFieldTable();
}
formIDBox.Text = formSchema.ID.ToString();
formName.Text = formSchema.Name;
BuildDocumentButtons();
//update the url
string niceurl = umbraco.library.NiceUrl(formDocument.Id);
string url = "<a target='_blank' href='" + niceurl + "'>" + niceurl + "</a>";
urlTD.InnerHtml = url;
BuildSubmissionsUI();
BuildSubmissionsTable();
}
