Example #1
0
        protected void Page_Load(object sender, EventArgs e)
        {
            //(string keywords, string formAlias, string occurring, int maxResults=25

            FormStorageWebService service = new FormStorageWebService();
            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 (string formField in formSchema.FormFields)
            {
                sb.Append(HttpUtility.UrlDecode(FormStorageCore.GetDictionaryItem(formField)) + "\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 Dictionary<string, string> GetSubmissions(string keywords, string formAlias, string occurring, int maxResults = 25)
        {
            FormStorageCore.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] = FormStorageCore.SqlHelper.CreateParameter("@alias", formAlias);
                parameters[1] = FormStorageCore.SqlHelper.CreateParameter("@key1", "%" + keywordList[0] + "%");
                parameters[2] = FormStorageCore.SqlHelper.CreateParameter("@key2", "%" + keywordList[1] + "%");
                parameters[3] = FormStorageCore.SqlHelper.CreateParameter("@key3", "%" + keywordList[2] + "%");

                IRecordsReader reader = FormStorageCore.SqlHelper.ExecuteReader(@"
                    SELECT
                        fs.submissionID, fs.formID, IP, [datetime], entryID, value, fieldAlias
                    FROM FormStorageSubmissions fs
                    LEFT JOIN
                        FormStorageForms sf on sf.formID=fs.formID
                    LEFT JOIN
                        FormStorageEntries fe on fe.submissionID=fs.submissionID
                    WHERE sf.alias=@alias
                      AND fs.submissionID IN (
                            SELECT submissionID
                            FROM FormStorageEntries
                            WHERE value LIKE @key1
                          )

                      AND fs.submissionID IN (
                            SELECT submissionID
                            FROM FormStorageEntries
                            WHERE value LIKE @key2
                          )

                      AND fs.submissionID IN (
                            SELECT submissionID
                            FROM FormStorageEntries
                            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"), fieldAlias = reader.Get<string>("fieldAlias") });

                        submissionID = recordSubmissionID;
                    }
                    else
                    {
                        //otherwise add in the fields only
                        thisEntry.fields.Add(new SavedValue() { value = reader.Get<string>("value"), fieldAlias = reader.Get<string>("fieldAlias") });
                    }
                }

                reader.Dispose();
                FormStorageCore.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 (string fieldAlias in formSchema.FormFields)
                    {
                        bool foundField = false;
                        foreach (SavedValue thisSavedValue in entry.fields)
                        {
                            if (thisSavedValue.fieldAlias == fieldAlias)
                            {
                                resultRow.values.Add(thisSavedValue.value.Replace(Environment.NewLine, "<br/>"));
                                foundField = true;
                            }
                        }
                        if (foundField == false)
                        {
                            resultRow.values.Add("");
                        }
                    }

                    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;
            }
        }
Example #3
0
        private void buildControls()
        {
            formSchema = new FormSchema(savedOptions.alias);

            wrapperDiv.Attributes["alias"] = savedOptions.alias;

            BuildSubmissionsUI();
            BuildSubmissionsTable();
        }