Merge data from 2 tables

[jolle]

Hello,

I have one table with attendees data and one table with course dates.
Now I want to generate an attendance list where the course leader can mark if an attendee took part in the couse on each date. See SampleTable.PNG built manually in Excel.

I thought to build a PDF Form and feed it with XFDF data.
I already used a List PHP Plugin to get the data needed and generate the XFDF file that is returned to the user.
I ran into problem because the referenced form in the XFDF is not opened and data loaded.
With both files stored locally it does work.

Now I am looking for alternative solutions. Any ideas?
Any help is appreciated.

BR,
J?rn

 

[jolle]

This is the code is use to get the data and generate the file.

<?php
// To prevent long file from getting cut off from    //max_execution_time
error_reporting(0);
@set_time_limit(0);
jimport('joomla.filesystem.file');
 
$item = $model->getTable();
$app = JFactory::getApplication();
 
// getting array of current Fabrik list's main table pk values and imploding them to comma separated list:
$ids = $app->input->get('ids', array(), 'array');
$id = implode(',', $ids);
 
// need course number, course title and semester
$fullCourseTitle = "";
 
// connecting to the default database:
$db = FabrikWorker::getDbo();
 
// At first get the dates of the course
// Defining needed fields. $db->quoteName ensures that the field names would be surrounded by appropriate quotes ``:
$c_id = $db->quoteName('id');
$c_tag = $db->quoteName('tag');
$c_kurs = $db->quoteName('kurs');
 
// Let's query
$query = "SELECT $c_id, DATE_FORMAT($c_tag, GET_FORMAT(DATE,'EUR')) AS tag FROM fab_kurstage WHERE $c_kurs IN($id)";
$db->setQuery($query);
 
// As this query is set to return multiple rows of data from multiple fields:
$rows_days = $db->loadObjectList();
//echo "<pre>";var_dump($rows_days);echo "</pre>";
 
// Next get attendees of the course who are confirmed
$c_firstanme = $db->quoteName('vorname');
$c_lastname = $db->quoteName('nachname');
$c_name = $db->quoteName('name');
$c_email = $db->quoteName('email');
$c_phone = $db->quoteName('telefon');
$c_teilnehmer = $db->quoteName('teilnehmer');
$c_table_teilnehmer = $db->quoteName('fab_teilnehmer');
$c_table_kursteilnahme = $db->quoteName('fab_kursteilnahme');
$c_status = $db->quoteName('status');
 
// Let's query
$query = "";
$query = "SELECT $c_table_teilnehmer.$c_id, CONCAT($c_firstanme, ' ', $c_lastname) AS $c_name, $c_table_teilnehmer.$c_phone, $c_table_teilnehmer.$c_email";
$query = $query . " FROM $c_table_teilnehmer, $c_table_kursteilnahme";
$query = $query . " WHERE $c_table_teilnehmer.$c_id = $c_table_kursteilnahme.$c_teilnehmer";
$query = $query . " AND $c_table_kursteilnahme.$c_kurs IN ($id)";
$query = $query . " AND $c_table_kursteilnahme.$c_status = 2";
$db->setQuery($query);
 
// As this query is set to return multiple rows of data from multiple fields:
$rows_participants = $db->loadObjectList();
//echo "<pre>";var_dump($rows_participants);echo "</pre>";
// Check if data is more than fits on one page
// TODO
 
 
// Now we got all the data we need
 
// Prepare Data for XFDF generation
// static fields
$fieldCourseName = "FieldCourseName";
$fieldPrintDate = "FrieldPrintDate";
// numbered fields
$fieldParticipant = "FieldParticipant";
$fieldDay = "FieldDay";
$fieldPhone = "FieldPhone";
$fieldEmail = "FieldEmail";
 
// Static file header information
$pdfFormUrl = JURI::base()."images/documents/TeilnehmerlisteTest2Xml.pdf";
//echo "<pre>";var_dump($pdfFormUrl);echo "</pre>";
 
// XML snippets
$xmlHeader = '<?xml version="1.0" encoding="UTF-8"?><xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">' . "\n";
$xmlFooter = '</xfdf>' . "\n";
$xmlHref = '  <f href="%s"/>' . "\n";
$xmlFieldsStart = '  <fields>' . "\n";
$xmlFieldsEnd = '  </fields>' . "\n";
$xmlField = '    <field name="%s"><value>%s</value></field>' . "\n";
 
// build XFDF file
$fileContent = $xmlHeader;
$fileContent = $fileContent . sprintf($xmlHref, $pdfFormUrl);
$fileContent = $fileContent . $xmlFieldsStart;
$i = 1;
foreach ($rows_participants as $participant) {
    $fileContent = $fileContent . sprintf($xmlField, $fieldParticipant . $i, $participant->name);
    $fileContent = $fileContent . sprintf($xmlField, $fieldPhone . $i, $participant->telefon);
    $fileContent = $fileContent . sprintf($xmlField, $fieldEmail . $i, $participant->email);
    $i++;
}
 
$i = 1;
foreach ($rows_days as $day) {
    $fileContent = $fileContent . sprintf($xmlField, $fieldDay . $i, $day->tag);
    $i++;
}
$fileContent = $fileContent . sprintf($xmlField, $fieldPrintDate, date('d.m.Y H:i'));
$fileContent = $fileContent . $xmlFieldsEnd;
$fileContent = $fileContent . $xmlFooter;
 
// Send the file
/*$document = JFactory::getDocument();
$document->setMimeEncoding('text/html; charset=utf-8');
 
JResponse::clearHeaders();
 
// Set the response to indicate a file download
JResponse::setHeader('Content-Type', 'application/vnd.adobe.xfdf');
JResponse::setHeader('Content-Disposition', "attachment;filename=\"Anwesenheitsliste.xfdf\"");
JResponse::setHeader('charset', 'utf-8');
JResponse::setBody($fileContent);
echo JResponse::toString(false);
*/
 
$pdfFormFile = "images/documents/TeilnehmerlisteTest2Xml.pdf";
$config = JFactory::getConfig();
$tmppath = $config->get('tmp_path');
$filename = "Anwesenheitsliste-Kurs-" . $id . "-" . date('YmdHi');
$filepath = $tmppath . '/' . $filename;
$ok = JFile::write($filepath . '.xfdf', $fileContent);
if (!$ok)
{
    echo "<pre>Could not write XFDF File to " . $filepath . ".xfdf</pre>";
    exit;
}
 
exec('pdftk '. $pdfFormFile . ' fill_form ' . $filepath . '.xfdf output ' . $filepath . '.pdf flatten'); 
 
// $statusMsg = "";
//echo "<pre>";var_dump($fileContent);echo "</pre>";
 
// $$$ rob 21/02/2012 - need to exit otherwise Chrome give 349 download error
exit;
?>

As you can see I already tried to merge the PDF form and data on the server, but I think that pdftk is not installed or at least not in the path.

 

[jolle]

Nobody any idea how to adress this problem?