<?php
$fields = array(
'np.title `partner`',
'fm.fmid',
'n.nid',
'ws.sid',
'n.language',
);
$extra = array('adgroup', 'campaign', 'content', 'source', 'term', 'mobile', 'tablet');
foreach ($extra as $field) {
$fields[] = "{$field}.`value` `{$field}`";
}
$extra = array_combine($extra, $extra);
$sql = 'INSERT INTO {report_generic_report} SELECT ' . implode(', ', $fields) . ", ws.submitted, ps.doi FROM {form_map_history{$suffix}} fmh JOIN {form_map} fm ON fmh.fmid = fm.fmid JOIN {node} np ON fm.vid = np.vid JOIN {webform_submissions{$suffix}} ws ON fmh.sid = ws.sid JOIN {partner_submission{$suffix}} ps ON ws.sid = ps.sid AND fm.vid = ps.vid JOIN {node} n ON ws.nid = n.nid";
foreach ($extra as $field) {
$sql .= " LEFT JOIN {webform_submission_extra{$suffix}} `{$field}` ON ws.sid = `{$field}`.sid AND `{$field}`.`name` = '{$field}'";
}
$sql .= " WHERE ws.submitted BETWEEN %d AND %d";
if ($form_state['values']['language']) {
$sql .= " AND n.`language` = '".$form_state['values']['language']."'";
}
$sql .= " ORDER BY `partner`, sid";
print $sql;
preferences:
35.96 ms | 402 KiB | 5 Q