1 <?php
  2 /**
  3  * Displays a summary of instances of a form submitted to the website
  4  *
  5  * @package userforms
  6  */
  7 
  8 class SubmittedFormReportField extends FormField {
  9     
 10     function Field() {
 11         Requirements::css(SAPPHIRE_DIR . "/css/SubmittedFormReportField.css");
 12         Requirements::javascript("userforms/javascript/UserForm.js");
 13         Requirements::add_i18n_javascript('userforms/javascript/lang');
 14         return $this->renderWith("SubmittedFormReportField");
 15     }
 16     
 17     /**
 18      * Return the submissions from the site
 19      *
 20      * @return ComponentSet
 21      */ 
 22     function Submissions() {
 23         $pageStart = isset($_REQUEST['start']) && is_numeric($_REQUEST['start']) ? $_REQUEST['start'] : 0;
 24         $pageLength = 10;
 25 
 26         $items = $this->form->getRecord()->getComponents('Submissions', null, "\"Created\" DESC", null, "$pageStart,$pageLength");
 27         $formId = $this->form->getRecord()->ID;
 28 
 29         foreach(DB::query("SELECT COUNT(*) AS \"CountRows\" FROM \"SubmittedForm\" WHERE \"ParentID\" = $formId") as $r) $totalCount = $r['CountRows'];
 30         
 31         $items->setPageLimits($pageStart, $pageLength, $totalCount);
 32         $items->NextStart = $pageStart + $pageLength;
 33         $items->PrevStart = $pageStart - $pageLength;
 34         $items->Start = $pageStart;
 35         $items->StartPlusOffset = $pageStart+$pageLength;
 36         $items->TotalCount = $totalCount;
 37 
 38         return $items;
 39     }
 40     
 41     function getSubmissions() {
 42         return $this->customise(array(
 43             'Submissions' => $this->Submissions()
 44         ))->renderWith(array('SubmittedFormReportField'));
 45     }
 46 
 47     /**
 48      * ID of this forms record
 49      * 
 50      * @return int
 51      */
 52     function RecordID() {
 53         return $this->form->getRecord()->ID;
 54     }
 55     
 56     /**
 57      * Export each of the form submissions for this UserDefinedForm
 58      * instance into a CSV file.
 59      * 
 60      * In order to run this export function, the user must be
 61      * able to edit the page, so we check canEdit()
 62      *
 63      * @return HTTPResponse / bool
 64      */
 65     public function export($id = false) {
 66         if($id && is_int($id)) {
 67             $SQL_ID = $id;
 68         }
 69         else {
 70             if(isset($_REQUEST['id'])) {
 71                 $SQL_ID = Convert::raw2sql($_REQUEST['id']);
 72             }
 73             else {
 74                 user_error("No UserDefinedForm Defined.", E_USER_ERROR);
 75                 
 76                 return false;
 77             }
 78         }
 79 
 80         $now = Date("Y-m-d_h.i.s");
 81         $fileName = "export-$now.csv";
 82         $separator = ",";
 83 
 84         $udf = DataObject::get_by_id("UserDefinedForm", $SQL_ID);
 85         
 86         if($udf) {
 87             $submissions = $udf->Submissions("", "\"ID\"");
 88 
 89             // Collect unique columns for use in the CSV.
 90             // Do it separately as we need a fixed number of columns for the file.
 91             // Include all fields that have ever existed in this form.
 92             // Preserve the ordering: the most recent form setup should be dominant.
 93             $inClause = array();
 94             foreach($submissions as $submission) { 
 95                 $inClause[] = $submission->ID; 
 96             }
 97             $csvHeaders = DB::query("SELECT \"Name\" , \"Title\" FROM \"SubmittedFormField\" 
 98                                      LEFT JOIN \"SubmittedForm\" ON \"SubmittedForm\".\"ID\" = \"SubmittedFormField\".\"ParentID\"
 99                                      WHERE \"SubmittedFormField\".\"ParentID\" IN (" . implode(',', $inClause) . ") 
100                                      ORDER BY \"SubmittedFormField\".\"ParentID\" DESC, \"SubmittedFormField\".\"ID\"
101                                     ");
102             if ($csvHeaders) $csvHeaders = $csvHeaders->map();
103 
104             if($submissions && $submissions->exists()) {
105                 $data = array();
106 
107                 // Create CSV rows out of submissions. Fields on those submissions will become columns.
108                 foreach($submissions as $submission) {
109                     $fields = $submission->Values();
110 
111                     $row = array();
112                     foreach($fields as $field) {
113                         $row[$field->Name] = $field->Value;
114                     }
115 
116                     $row['Submitted'] = $submission->Created;
117                     $data[] = $row;
118                 }
119                 
120                 // Create the CSV header line first:
121                 $csvData = '"' . implode('","', $csvHeaders) . '"' . ',"Submitted"'."\n";
122 
123                 // Now put the collected data under relevant columns
124                 foreach($data as $row) {
125                     $csvRowItems = array();
126                     foreach ($csvHeaders as $columnName=>$columnTitle) {
127                         if (!isset($row[$columnName])) $csvRowItems[] = ""; // This submission did not have that column, insert blank
128                         else $csvRowItems[] = str_replace('"', '""', $row[$columnName]);
129                     }
130                     $csvRowItems[] = $row['Submitted'];
131 
132                     // Encode the row by hand (fputcsv fails to encode newlines properly)
133                     if (count($csvRowItems)) $csvData .= "\"".implode($csvRowItems, "\",\"")."\"\n";
134                 }
135             } else {
136                 user_error("No submissions to export.", E_USER_ERROR);
137             }
138 
139             if(class_exists('SapphireTest', false) && SapphireTest::is_running_test()) {
140                 return $csvData;
141             }
142             else {
143                 SS_HTTPRequest::send_file($csvData, $fileName, 'text/csv')->output();   
144                 exit;
145             }
146         } else {
147             user_error("'$SQL_ID' is a valid type, but we can't find a UserDefinedForm in the database that matches the ID.", E_USER_ERROR);
148         }
149     }
150     
151     /**
152      * Delete all the submissions listed in the user defined form
153      *
154      * @return Redirect|Boolean
155      */
156     public function deletesubmissions($id = false) {
157         $isRunningTests = (class_exists('SapphireTest', false) && SapphireTest::is_running_test());
158         
159         if($id && is_int($id)) {
160             $SQL_ID = $id;
161         }
162         else {
163             if(isset($_REQUEST['id'])) {
164                 $SQL_ID = Convert::raw2sql($_REQUEST['id']);
165             }
166         }
167         
168         if(isset($SQL_ID)) {
169             $udf = DataObject::get_by_id("UserDefinedForm", $SQL_ID);
170             $submissions = $udf->Submissions();
171             
172             if($submissions) {
173                 foreach($submissions as $submission) {
174                     $submission->delete();
175                 }
176                 return (Director::is_ajax() || $isRunningTests) ? true : Director::redirectBack();
177             }
178         }
179         return (Director::is_ajax() || $isRunningTests) ? false : Director::redirectBack();
180     }
181     
182     /**
183      * Delete a given submission from a user defined form
184      *
185      * @return Redirect|Boolean
186      */
187     public function deletesubmission($id = false) {
188         $isRunningTests = (class_exists('SapphireTest', false) && SapphireTest::is_running_test());
189         
190         if($id && is_int($id)) {
191             $SQL_ID = $id;
192         }
193         else {
194             if(isset($_REQUEST['id'])) {
195                 $SQL_ID = Convert::raw2sql($_REQUEST['id']);
196             }
197         }
198         
199         if(isset($SQL_ID)) {
200             $submission = DataObject::get_by_id("SubmittedForm", $SQL_ID);
201             if($submission) {
202                 $submission->delete();
203                 
204                 return (Director::is_ajax() || $isRunningTests) ? true : Director::redirectBack();
205             }
206         }
207         return (Director::is_ajax() || $isRunningTests) ? false : Director::redirectBack();
208     }
209 }
210