function export_data($queryData, $frid, $fid, $groups, $columns, $include_metadata) { global $xoopsDB; // generate the export filename, which the user will see $form_handler = xoops_getmodulehandler('forms', 'formulize'); $formObject = $form_handler->get($fid); if (is_object($formObject)) { $formTitle = "'" . str_replace(array(" ", "-", "/", "'", "`", "\\", ".", "?", ",", ")", "(", "[", "]"), "_", trans($formObject->getVar('title'))) . "'"; } else { $formTitle = "a_form"; } $export_filename = _formulize_EXPORT_FILENAME_TEXT . "_" . $formTitle . "_" . date("M_j_Y_Hi") . ".csv"; // output http headers header('Content-Description: File Transfer'); header('Content-Type: text/csv; charset=' . _CHARSET); header('Content-Disposition: attachment; filename=' . $export_filename); header('Content-Transfer-Encoding: binary'); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); // get a list of columns for export $headers = array(); if ($include_metadata) { // include metadata columns if the user requested them $headers = array(_formulize_ENTRY_ID, _formulize_DE_CALC_CREATOR, _formulize_DE_CALC_MODIFIER, _formulize_DE_CALC_CREATEDATE, _formulize_DE_CALC_MODDATE); } else { if (in_array("entry_id", $columns)) { $headers[] = _formulize_ENTRY_ID; } if (in_array("uid", $columns) or in_array("creation_uid", $columns)) { $headers[] = _formulize_DE_CALC_CREATOR; } if (in_array("proxyid", $columns) or in_array("mod_uid", $columns)) { $headers[] = _formulize_DE_CALC_MODIFIER; } if (in_array("creation_date", $columns) or in_array("creation_datetime", $columns)) { $headers[] = _formulize_DE_CALC_CREATEDATE; } if (in_array("mod_date", $columns) or in_array("mod_datetime", $columns)) { $headers[] = _formulize_DE_CALC_MODDATE; } } foreach ($columns as $thiscol) { if ("creator_email" == $thiscol) { $headers[] = _formulize_DE_CALC_CREATOR_EMAIL; } else { $colMeta = formulize_getElementMetaData($thiscol, true); $headers[] = $colMeta['ele_colhead'] ? trans($colMeta['ele_colhead']) : trans($colMeta['ele_caption']); } } if ($include_metadata) { // include metadata columns if the user requested them $columns = array_merge(array("entry_id", "uid", "proxyid", "creation_date", "mod_date"), $columns); } if (strstr(strtolower(_CHARSET), 'utf') and $_POST['excel'] == 1) { echo ""; // necessary to trigger certain versions of Excel to recognize the file as unicode } // output export header $output_handle = fopen('php://output', 'w'); // open a file handle to stdout because fputcsv() needs it fputcsv($output_handle, $headers); // output export data $GLOBALS['formulize_doingExport'] = true; unset($queryData[0]); // get rid of the fid and userid lines unset($queryData[1]); $data_sql = implode(" ", $queryData); // merge all remaining lines into one string to send to getData $limitStart = 0; $limitSize = 50; // export in batches of 50 records at a time do { // load part of the data, since a very large dataset could exceed the PHP memory limit $data = getData($frid, $fid, $data_sql, "AND", null, $limitStart, $limitSize); if (is_array($data)) { foreach ($data as $entry) { $row = array(); foreach ($columns as $column) { switch ($column) { case "entry_id": $formhandle = getFormHandlesFromEntry($entry); $ids = internalRecordIds($entry, $formhandle[0]); $row[] = $ids[0]; break; case "uid": $c_uid = display($entry, 'creation_uid'); $c_name_q = q("SELECT name, uname FROM " . $xoopsDB->prefix("users") . " WHERE uid='{$c_uid}'"); $row[] = isset($c_name_q[0]['name']) ? $c_name_q[0]['name'] : $c_name_q[0]['uname']; break; case "proxyid": $m_uid = display($entry, 'mod_uid'); if ($m_uid) { $m_name_q = q("SELECT name, uname FROM " . $xoopsDB->prefix("users") . " WHERE uid='{$m_uid}'"); $row[] = isset($m_name_q[0]['name']) ? $m_name_q[0]['name'] : $m_name_q[0]['uname']; } else { $row[] = ""; } break; case "creation_date": $row[] = display($entry, 'creation_datetime'); break; case "mod_date": $row[] = display($entry, 'mod_datetime'); break; default: $row[] = trans(html_entity_decode(displayTogether($entry, $column, ", "), ENT_QUOTES)); } } // output this row to the browser fputcsv($output_handle, $row); } // get the next set of data set_time_limit(90); $limitStart += $limitSize; } } while (is_array($data) and count($data) > 0); fclose($output_handle); }
function internalRecordIds($entry, $formhandle = "", $id = "NULL", $fidAsKeys = false) { if (is_numeric($id)) { $entry = $entry[$id]; } if (!$formhandle) { $formhandle = getFormHandlesFromEntry($entry); } else { // need to convert possible legacy framework form handles to form ids $formhandle = dealWithDeprecatedFormHandles($formhandle); } if (is_array($formhandle)) { foreach ($formhandle as $handle) { $handle = _parseInternalRecordIdsFormHandle($handle); foreach ($entry[$handle] as $id => $element) { if ($fidAsKeys) { $fid = formulize_getFormIdFromName($handle); $ids[$fid][] = $id; } else { $ids[$handle][] = $id; } } } } else { $formhandle = _parseInternalRecordIdsFormHandle($formhandle); if (is_array($entry[$formhandle])) { foreach ($entry[$formhandle] as $id => $element) { $ids[] = $id; } } } return $ids; }
function prepExport($headers, $cols, $data, $fdchoice, $custdel = "", $title, $template = false, $fid, $groups) { // export of calculations added August 22 2006 // come up with a filename and then return it // rest of logic in entriesdisplay.php will take the filename and create a file with the calculations in it once they are performed if ($fdchoice == "calcs") { $tempfold = microtime(true); $exfilename = _formulize_DE_XF . $tempfold . ".html"; return XOOPS_URL . SPREADSHEET_EXPORT_FOLDER . "{$exfilename}"; } global $xoopsDB; include_once XOOPS_ROOT_PATH . "/modules/formulize/include/extract.php"; if ($fdchoice == "update") { // reset headers and cols to include all data -- when creating a blank template, this reset has already happened before prepexport is called $fdchoice = "comma"; $template = "update"; $cols1 = getAllColList($fid, "", $groups); // $cols1 will be a multidimensional array, one "entry" per column, and for each column the entry is an assoc array with ele_id, ele_colhead, ele_caption and ele_handle. unset($cols); $cols = array(); foreach ($cols1[$fid] as $col) { $cols[] = $col['ele_handle']; } unset($headers); $headers = getHeaders($cols, true); // array of element handles, boolean for if the cols are handles (or ids, which is the default assumption) } if ($fdchoice == "comma") { $fd = ","; $fxt = ".csv"; } if ($fdchoice == "tab") { $fd = "\t"; $fxt = ".tabDelimited"; } if ($fdchoice == "custom") { $fd = $custdel; if (!$fd) { $fd = "**"; } $fxt = ".customDelimited"; } $lineStarted = false; if ($template) { // add in other profile fields -- username, realname, e-mail, password, registration code if ($template == "blankprofile") { $csvfile = "\"" . _formulize_DE_IMPORT_USERNAME . "\"{$fd}\"" . _formulize_DE_IMPORT_FULLNAME . "\"{$fd}\"" . _formulize_DE_IMPORT_PASSWORD . "\"{$fd}\"" . _formulize_DE_IMPORT_EMAIL . "\"{$fd}\"" . _formulize_DE_IMPORT_REGCODE . "\""; $lineStarted = true; } else { if ($template == "update") { $csvfile = "\"" . _formulize_DE_IMPORT_IDREQCOL . "\"{$fd}\"" . _formulize_DE_CALC_CREATOR . "\""; $lineStarted = true; } else { $csvfile = "\"" . _formulize_DE_CALC_CREATOR . "\""; $lineStarted = true; } } } elseif ($_POST['metachoice'] == 1) { // only include metadata columns if the user requested them $csvfile = "\"" . _formulize_ENTRY_ID . "\"{$fd}\"" . _formulize_DE_CALC_CREATOR . "\"{$fd}\"" . _formulize_DE_CALC_CREATEDATE . "\"{$fd}\"" . _formulize_DE_CALC_MODIFIER . "\"{$fd}\"" . _formulize_DE_CALC_MODDATE . "\""; $lineStarted = true; } else { if (in_array("entry_id", $cols)) { $csvfile .= "\"" . _formulize_ENTRY_ID . "\""; $lineStarted = true; } if (in_array("uid", $cols) or in_array("creation_uid", $cols)) { $csvfile .= $lineStarted ? $fd : ""; $csvfile .= "\"" . _formulize_DE_CALC_CREATOR . "\""; $lineStarted = true; } if (in_array("proxyid", $cols) or in_array("mod_uid", $cols)) { $csvfile .= $lineStarted ? $fd : ""; $csvfile .= "\"" . _formulize_DE_CALC_MODIFIER . "\""; $lineStarted = true; } if (in_array("creation_date", $cols) or in_array("creation_datetime", $cols)) { $csvfile .= $lineStarted ? $fd : ""; $csvfile .= "\"" . _formulize_DE_CALC_CREATEDATE . "\""; $lineStarted = true; } if (in_array("mod_date", $cols) or in_array("mod_datetime", $cols)) { $csvfile .= $lineStarted ? $fd : ""; $csvfile .= "\"" . _formulize_DE_CALC_MODDATE . "\""; $lineStarted = true; } } foreach ($headers as $header) { // ignore the metadata columns if they are selected, since we already handle them better above. as long as the user requested that they be included if ($header == "" or $_POST['metachoice'] == 1 and ($header == _formulized_ENTRY_ID or $header == _formulize_DE_CALC_CREATOR or $header == _formulize_DE_CALC_MODIFIER or $header == _formulize_DE_CALC_CREATEDATE or $header == _formulize_DE_CALC_MODDATE)) { continue; } $header = str_replace("\"", "\"\"", $header); $header = "\"" . trans($header) . "\""; if ($lineStarted) { $csvfile .= $fd; } $csvfile .= $header; $lineStarted = true; } $csvfile .= "\r\n"; $colcounter = 0; $i = 0; foreach ($data as $entry) { $formhandle = getFormHandlesFromEntry($entry); $ids = internalRecordIds($entry, $formhandle[0]); $id = $ids[0]; $id_req[] = $id; $c_uid = display($entry, 'creation_uid'); $c_name_q = q("SELECT name, uname FROM " . $xoopsDB->prefix("users") . " WHERE uid='{$c_uid}'"); $c_name = $c_name_q[0]['name']; if (!$c_name) { $c_name = $c_name_q[0]['uname']; } $c_date = display($entry, 'creation_datetime'); $m_uid = display($entry, 'mod_uid'); if ($m_uid) { $m_name_q = q("SELECT name, uname FROM " . $xoopsDB->prefix("users") . " WHERE uid='{$m_uid}'"); $m_name = $m_name_q[0]['name']; if (!$m_name) { $m_name = $m_name_q[0]['uname']; } } else { $m_name = $c_name; } $m_date = display($entry, 'mod_datetime'); // write in metadata $lineStarted = false; // will be update only, since blank ones have no data if ($template) { $csvfile .= $id . $fd . "\"{$c_name}\""; $lineStarted = true; } elseif ($_POST['metachoice'] == 1) { $csvfile .= "\"{$id}\"" . $fd . "\"{$c_name}\"" . $fd . "\"{$c_date}\"" . $fd . "\"{$m_name}\"" . $fd . "\"{$m_date}\""; $lineStarted = true; } // write in data foreach ($cols as $col) { // ignore the metadata columns if they are selected, since we already handle them better above if (isMetaDataField($col) and $_POST['metachoice'] == 1) { continue; } if ($col == "creation_uid" or $col == "mod_uid" or $col == "uid" or $col == "proxyid") { $name_q = q("SELECT name, uname FROM " . $xoopsDB->prefix("users") . " WHERE uid=" . intval(display($entry, $col))); $data_to_write = $name_q[0]['name']; if (!$data_to_write) { $data_to_write = $name_q[0]['uname']; } } else { $data_to_write = displayTogether($entry, $col, ", "); $data_to_write = str_replace(""", "\"\"", $data_to_write); $data_to_write = "\"" . trans($data_to_write) . "\""; $data_to_write = str_replace("\r\n", "\n", $data_to_write); } if ($lineStarted) { $csvfile .= $fd; } $csvfile .= $data_to_write; $lineStarted = true; } $csvfile .= "\r\n"; // end of a line } $tempfold = microtime(true); $exfilename = _formulize_DE_XF . $tempfold . $fxt; // open the output file for writing $wpath = XOOPS_ROOT_PATH . SPREADSHEET_EXPORT_FOLDER . "{$exfilename}"; $csvfile = html_entity_decode($csvfile, ENT_QUOTES); $exportfile = fopen($wpath, "w"); fwrite($exportfile, $csvfile); fclose($exportfile); // garbage collection. delete files older than 6 hours formulize_scandirAndClean(XOOPS_ROOT_PATH . SPREADSHEET_EXPORT_FOLDER, _formulize_DE_XF); // write id_reqs and tempfold to the DB if we're making an update template if ($template == "update") { $sql = "INSERT INTO " . $xoopsDB->prefix("formulize_valid_imports") . " (file, id_reqs) VALUES (\"{$tempfold}\", \"" . serialize($id_req) . "\")"; if (!($res = $xoopsDB->queryF($sql))) { exit("Error: could not write import information to the database. SQL: {$sql}<br>" . $xoopsDB->error()); } } // need to add in logic to cull old files return XOOPS_URL . SPREADSHEET_EXPORT_FOLDER . "{$exfilename}"; }