I have some results from a mysql table that I would like to export, I am currently able to click a download link and download an xls but I would like to be able to run this via a cron job and have the weekly results email to me.
I have looked at doing this from Mysql and save it out as a csv directly.
However I am struggling with the SQL, the table format is as follows
btFormQuestions (some columns ommitted)
+-------+---------------+----------+-----------+
| msqID | questionSetId | Question | InputType |
|-------+---------------+----------+-----------+
| 1 | 123456 | Name | field |
| 2 | 123456 | Telephone| field |
| 3 | 123456 | Email | email |
| 4 | 123456 | Enquiry | test |
btFormAnswers
+-----+------+-------+-----------------+
| aID | asID | msqID | answer |
+-----+------+-------+-----------------+
| 1 | 1 | 1 | Sean |
| 2 | 1 | 2 | 0800 0 |
| 3 | 1 | 3 | [email protected] |
| 4 | 1 | 4 | Asking Question |
btFormAnswersSet
+------+---------------+---------------------+
| asID | questionSetId | created |
+------+---------------+---------------------+
| 1 | 123456 | 2013-04-30 11:07:55 |
The sql queries, I am currently using to get the information into PHP and into an array is as follows:
//get answers sets
$sql='SELECT * FROM btFormAnswerSet AS aSet '.
'WHERE aSet.questionSetId='.$questionSet.' ORDER BY created DESC LIMIT 0, 100;
$answerSetsRS=$db->query($sql);
//load answers into a nicer multi-dimensional array
$answerSets=array();
$answerSetIds=array(0);
while( $answer = $answerSetsRS->fetchRow() ){
//answer set id - question id
$answerSets[$answer['asID']]=$answer;
$answerSetIds[]=$answer['asID'];
}
//get answers
$sql='SELECT * FROM btFormAnswers AS a WHERE a.asID IN ('.join(',',$answerSetIds).')';
$answersRS=$db->query($sql);
//load answers into a nicer multi-dimensional array
while( $answer = $answersRS->fetchRow() ){
//answer set id - question id
$answerSets[$answer['asID']]['answers'][$answer['msqID']]=$answer;
}
return $answerSets;
I would like to be able to do one of the following
A.) Move all of this into one query to be able to get the following sort of result
+---------------+------+-----------+-----------+-----------------+
| QuestionSetID | Name | Telephone | Email | Enquiry |
+---------------+------+-----------+-----------+-----------------+
| 123456 | Sean | 0800 0 | [email protected] | Asking Question |
(I did try this with various joins but could not get them quite right) If I could get this to work I would not mind saving as a CSV
B.) Output the returned array as excel file that can be saved to a location on the server,
The current code creates a html table from the array
The code is a little long so I am only pasting the top and bottom bits here
//fwrite($handle, $excelHead);
//fwrite($handle, $row);
//fflush($handle);
ob_start();
header("Content-Type: application/vnd.ms-excel");
echo "<table>\r\n";
//Question headers go here
foreach($answerSets as $answerSetId=>$answerSet){
$questionNumber=0;
$numQuestionsToShow=2;
echo "\t<tr>\r\n";
echo "\t\t<td>". $dateHelper->getSystemDateTime($answerSet['created'])."</td>\r\n";
foreach($questions as $questionId=>$question){
$questionNumber++;
if ($question['inputType'] == 'checkboxlist'){
$options = explode('%%', $question['options']);
$subanswers = explode(',', $answerSet['answers'][$questionId]['answer']);
for ($i = 1; $i <= count($options); $i++)
{
echo "\t\t<td align='center'>\r\n";
if (in_array(trim($options[$i-1]), $subanswers)) {
// echo "\t\t\t".$options[$i-1]."\r\n";
echo "x";
} else {
echo "\t\t\t \r\n";
}
echo "\t\t</td>\r\n";
//fwrite($handle, $node);
//fflush($handle);
}
}elseif($question['inputType']=='fileupload'){
echo "\t\t<td>\r\n";
$fID=intval($answerSet['answers'][$questionId]['answer']);
$file=File::getByID($fID);
if($fID && $file){
$fileVersion=$file->getApprovedVersion();
echo "\t\t\t".'<a href="'. $fileVersion->getDownloadURL() .'">'.$fileVersion->getFileName().'</a>'."\r\n";
}else{
echo "\t\t\t".t('File not found')."\r\n";
}
echo "\t\t</td>\r\n";
}else{
echo "\t\t<td>\r\n";
echo "\t\t\t".$answerSet['answers'][$questionId]['answer'].$answerSet['answers'][$questionId]['answerLong']."\r\n";
echo "\t\t</td>\r\n";
}
//fwrite($handle, $node);
//fflush($handle);
}
echo "\t</tr>\r\n";
//fwrite($handle, $row);
//fflush($handle);
}
echo "</table>\r\n";
//fwrite($handle, $excelFoot);
//fflush($handle);
//fclose($handle);
file_put_contents($filePath, ob_get_clean());
I can get the file to save to the directory but I am having issues setting it as an Excel file, I have also tried, playing with Fwrite (instead of the buffer) with the similar results
can anyone help, or point me in the right location.
Thank you, Sean
I would do this from within concrete5. That way you get all the form-results-related models, plus the various helpers (like email).
For more info about jobs, see http://www.concrete5.org/documentation/developers/system/jobs/ . To run from a cron job, see http://www.concrete5.org/documentation/how-tos/developers/how-to-run-certain-jobs-via-cron/ .
It looks like you've got the code to generate the answers, and put it into an array, but you might want to look at something like https://github.com/concrete5/concrete5/blob/master/web/concrete/core/controllers/blocks/form_statistics.php#L32 . I'm not positive that's exactly what you need, but I do know that the dashboard page builds that answers table for you, so the code clearly exists somewhere.
Finally, to create an excel file, elsewhere c5 uses the "put it into a table and call it .xls" method, which works with excel and open office. I'm not sure exactly what you mean by "having issues setting it as Excel", but it sounds like this is your issue at the moment. If something is getting saved to the file, then you should post the file contents and you/we can work backwards as to what is causing the issue. It's probably just misformatted HTML or something.
Finally, to send the email, you can use the Mail Helper, but that doesn't currently allow for attachments (there's a pull request in github that does, and that you could use to override the mail helper with). Typically, the "best practice" would be to send it as a link.