function formulize_getJoinHandles($elementArrays)
{
static $cachedJoinHandles = array();
foreach ($elementArrays as $elementArray) {
// must be a multidimensional array, ie: even if we're only asking for one element, it's got to be $elementsArrays[0][0] = idnumber
foreach ($elementArray as $element) {
if (!isset($cachedJoinHandles[$element])) {
$metaData = formulize_getElementMetaData($element);
$cachedJoinHandles[$element] = $metaData['ele_handle'];
}
}
}
return $cachedJoinHandles;
}
function prepareDataForDataset($value, $handle, $entry_id)
{
$elementArray = formulize_getElementMetaData($handle, true);
$ele_value = unserialize($elementArray['ele_value']);
$listtype = key($ele_value[2]);
if ($listtype === "{USERNAMES}" or $listtype === "{FULLNAMES}") {
$uids = explode("*=+*:", $value);
if (count($uids) > 0) {
if (count($uids) > 1) {
array_shift($uids);
}
$uidFilter = extract_makeUidFilter($uids);
$listtype = $listtype == "{USERNAMES}" ? 'uname' : 'name';
$names = go("SELECT uname, name FROM " . DBPRE . "users WHERE {$uidFilter} ORDER BY {$listtype}");
$value = "";
foreach ($names as $thisname) {
if ($thisname[$listtype]) {
$value .= "*=+*:" . $thisname[$listtype];
} else {
$value .= "*=+*:" . $thisname['uname'];
}
}
} else {
$value = "";
}
}
return $value;
// we're not making any modifications for this element type
}
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 formulize_setupPreviousEntryUI($screen, $element_id, $type, $owner, $de = false, $entryId = "", $ele_handle, $fid)
{
// 1. need to get and cache the values of the entry for this screen
// 2. need to put the values into a dropdown list with an onchange event that populates the actual form element
// this should be cached in some other way, since every instance of the renderer will need to cache this. If it were a GLOBAL or this whole thing were in some other function, that would work.
static $cachedEntries = array();
if (!isset($cachedEntries[$screen->getVar('sid')])) {
// identify the entry belonging to this user's group(s) in the other form. Currently only group correspondence is supported.
global $xoopsUser;
$groups = $xoopsUser ? $xoopsUser->getGroups() : array(0 => XOOPS_GROUP_ANONYMOUS);
$member_handler =& xoops_gethandler('member');
$gperm_handler =& xoops_gethandler('groupperm');
$mid = getFormulizeModId();
$owner_groups =& $member_handler->getGroupsByUser($owner, FALSE);
// in this particular case, it's okay to make the owner_groups based on the users's memberships, since we want to present the single entry that belongs to whichever groups the user is a member of...I think. :-)
$singleData = getSingle($screen->getVar('paraentryform'), $owner, $owner_groups, $member_handler, $gperm_handler, $mid);
if ($singleData['flag'] == "group" and $singleData['entry'] > 0) {
// only proceed if there is a one-entry-per-group situation in the target form
formulize_benchmark("Ready to do previous entry query.");
$cachedEntries[$screen->getVar('sid')] = getData("", $screen->getVar('paraentryform'), $singleData['entry']);
formulize_benchmark("Done query.");
} else {
return "";
}
}
$entries = $cachedEntries[$screen->getVar('sid')];
// big assumption below is corresponding captions. In future there will be more ad hoc ways of describing which elements align to which other ones.
// 1. figure out the corresponding element ID based on matching captions
// 2. grab the previous value from the $entry/entries
// 3. create the dropdown list with these values, including javascript
$formHandler =& xoops_getmodulehandler('forms', 'formulize');
$currentForm = $formHandler->get($screen->getVar('fid'));
$previousForm = $formHandler->get($screen->getVar('paraentryform'));
$currentCaptions = $currentForm->getVar('elementCaptions');
$captionToMatch = $currentCaptions[$ele_handle];
$previousCaptions = $previousForm->getVar('elementCaptions');
$previousElementHandle = array_search($captionToMatch, $previousCaptions);
if (!$previousElementHandle) {
return "";
}
$elementName = $de ? "de_" . $fid . "_" . $entryId . "_" . $element_id : "ele_" . $element_id;
// displayElement elements have different names from regular elements
$previousElementId = formulize_getIdFromElementHandle($previousElementHandle);
// function is in extract.php
// setup the javascript based on the type of question, and setup other data that is required
switch ($type) {
case "text":
case "date":
$javascript = "onchange='javascript:this.form." . $elementName . ".value=this.form.prev_" . $element_id . ".value;'";
break;
case "radio":
// need to get the options of the question so we know what to match
$prevElementMetaData = formulize_getElementMetaData($previousElementId);
// use this function in extract instead of the get element method in handler, since this is guaranteed to be already be cached in memory
$prevElement_ele_value = unserialize($prevElementMetaData['ele_value']);
$prevElementOptions = array_keys($prevElement_ele_value);
$javascript = "onchange='javascript:if(this.form.prev_" . $element_id . ".value !== \"\") { this.form." . $elementName . "[this.form.prev_" . $element_id . ".value].checked=true; }'";
break;
case "yn":
$javascript = "onchange='javascript:if(this.form.prev_" . $element_id . ".value !== \"\") { this.form." . $elementName . "[this.form.prev_" . $element_id . ".value].checked=true; }'";
break;
}
$previousOptions = array();
$prevOptionsExist = false;
foreach ($entries as $id => $entry) {
$value = htmlspecialchars(strip_tags(display($entry, $previousElementHandle)));
if (is_array($value)) {
$value = printSmart(implode(", ", $value));
}
if (trim($value) === "" or trim($value) == "0000-00-00") {
continue;
}
$prevOptionsExist = true;
switch ($type) {
case "text":
case "date":
$previousOptions[$value] = $value;
break;
case "radio":
$prevElementPosition = array_search($value, $prevElementOptions);
// need to figure out which option matches the text of the value
if ($prevElementPosition !== false) {
$previousOptions[$prevElementPosition] = $value;
// for radio buttons, we need to pass the position of the option
}
break;
case "yn":
if ($value == _formulize_TEMP_QYES) {
$previousOptions[0] = $value;
} elseif ($value == _formulize_TEMP_QNO) {
$previousOptions[1] = $value;
}
break;
}
}
if (!$prevOptionsExist) {
return "";
}
$prevUI = new xoopsFormSelect('', 'prev_' . $element_id, '123qweasdzxc', 1, false);
// 123qweasdzxc is meant to be a unique value that will never be selected, since we don't ever want a previous selection showing by default
$prevUI->addOption('', _AM_FORMULIZE_PREVIOUS_OPTION);
$prevUI->addOptionArray($previousOptions);
$prevUI->setExtra($javascript);
return $prevUI;
}
## Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA ##
###############################################################################
## Author of this file: Freeform Solutions ##
## Project: Formulize ##
###############################################################################
include_once "../../../mainfile.php";
// get the info for this element
// format it onto the screen
// we're interested in the full caption and any options that there might be
include_once XOOPS_ROOT_PATH . "/modules/formulize/include/extract.php";
// load the formulize language constants if they haven't been loaded already
include_once XOOPS_ROOT_PATH . "/modules/formulize/language/english/main.php";
if (file_exists(XOOPS_ROOT_PATH . "/modules/formulize/language/" . $xoopsConfig['language'] . "/main.php")) {
include_once XOOPS_ROOT_PATH . "/modules/formulize/language/" . $xoopsConfig['language'] . "/main.php";
}
$elementMetaData = formulize_getElementMetaData($_GET['col'], true);
print "<HTML>";
print "<head>";
print "<title>" . _formulize_DE_MOREINFO_TITLE . "</title>\n";
print "<link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"" . XOOPS_URL . "/xoops.css\" />\n";
$themecss = xoops_getcss();
print "<link rel=\"stylesheet\" type=\"text/css\" media=\"screen\" href=\"{$themecss}\" />\n";
print "</head>";
print "<body style=\"background: white; margin-top:20px;\"><center>";
print "<table width=100%><tr><td width=5%></td><td width=90%>";
?>
<br><br>
<table class="outer">
<tr><td class="head"><center><p>
<?php
print _formulize_DE_MOREINFO_QUESTION . "<br>" . trans($elementMetaData['ele_caption']);
function patch40()
{
// CHECK THAT THEY ARE AT 3.1 LEVEL, IF NOT, LINK TO PATCH31
// Check for ele_handle being 255 in formulize table
global $xoopsDB;
// note very odd use of LIKE as a clause of its own in SHOW statements, very strange, but that's what MySQL does
$fieldStateSQL = "SHOW COLUMNS FROM " . $xoopsDB->prefix("formulize") . " LIKE 'ele_handle'";
if (!($fieldStateRes = $xoopsDB->queryF($fieldStateSQL))) {
print "Error: could not determine if your Formulize database structure is up to date. Please contact <a href=\"mailto:formulize@freeformsolutions.ca\">Freeform Solutions</a> for assistance.<br>\n";
return false;
}
$fieldStateData = $xoopsDB->fetchArray($fieldStateRes);
$dataType = $fieldStateData['Type'];
if ($dataType != "varchar(255)") {
print "<h1>Your database schema is out of date. You must run \"patch31\" before running the current patch.</h1>\n";
print "<p><a href=\"" . XOOPS_URL . "/modules/formulize/admin/formindex.php?op=patch31\">Click here to run \"patch31\".</a></p>\n";
return;
}
/* ======================================
* We must check here for the latest change, so we can tell the user whether they need to update or not!!
* We set needsPatch = false, and the alter to true if a patch is necessary
* When false, we return nothing from this function, as a cue that no patch is required
*
* To specify what to check, simply update the four variables declared below this comment.
* Set to false to ignore that level of checking
*
* THIS NEEDS TO BE UPDATED WHENEVER THERE IS A PATCH TO THE DATA STRUCTURE!!!
*
* IN ADDITION TO THE UPDATE HERE, THE mysql.sql FILE MUST BE UPDATED WITH THE REQUIRED CHANGES SO NEW INSTALLATIONS ARE UP TO DATE
*
* IT IS ALSO CRITICAL THAT THE PATCH PROCESS CAN BE RUN OVER AND OVER AGAIN NON-DESTRUCTIVELY
*
* ====================================== */
$checkThisTable = 'formulize_screen_template';
$checkThisField = false;
// no particular field to check.
$checkThisProperty = false;
$checkPropertyForValue = false;
$needsPatch = false;
$tableCheckSql = "SELECT 1 FROM information_schema.tables WHERE table_name = '" . $xoopsDB->prefix(formulize_db_escape($checkThisTable)) . "'";
$tableCheckRes = formulize_DBPatchCheckSQL($tableCheckSql, $needsPatch);
// may modify needsPatch!
if ($tableCheckRes and !$needsPatch and $checkThisField) {
// table was found, and we're looking for a field in it
$fieldCheckSql = "SHOW COLUMNS FROM " . $xoopsDB->prefix(formulize_db_escape($checkThisTable)) . " LIKE '" . formulize_db_escape($checkThisField) . "'";
// note very odd use of LIKE as a clause of its own in SHOW statements, very strange, but that's what MySQL does
$fieldCheckRes = formulize_DBPatchCheckSQL($fieldCheckSql, $needsPatch);
// may modify needsPatch!
}
if ($fieldCheckRes and !$needsPatch and $checkPropertyForValue) {
$fieldCheckArray = $xoopsDB->fetchArray($fieldCheckRes);
if ($fieldCheckArray[$checkThisProperty] != $checkPropertyForValue) {
$needsPatch = true;
}
}
if (!$needsPatch and (!isset($_GET['op']) or $_GET['op'] != 'patch40' and $_GET['op'] != 'patchDB')) {
return;
}
if (!isset($_POST['patch40'])) {
print "<form action=\"ui.php?op=patchDB\" method=post>";
print "<h1>Your database structure is not up to date! Click the button below to apply the necesssary patch to the database.</h1>";
print "<h2>Warning: this patch makes several changes to the database. Backup your database prior to applying this patch!</h2>";
print "<input type = submit name=patch40 value=\"Apply Database Patch for Formulize\">";
print "</form>";
} else {
// PATCH LOGIC GOES HERE
print "<h2>Patch Results:</h2>";
$testsql = "SHOW TABLES";
$resultst = $xoopsDB->query($testsql);
while ($table = $xoopsDB->fetchRow($resultst)) {
$existingTables[] = $table[0];
}
$sql = array();
if (!in_array($xoopsDB->prefix("formulize_groupscope_settings"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_groupscope_settings") . "` (\n `groupscope_id` int(11) NOT NULL auto_increment,\n `groupid` int(11) NOT NULL default 0,\n `fid` int(11) NOT NULL default 0,\n `view_groupid` int(11) NOT NULL default 0,\n PRIMARY KEY (`groupscope_id`),\n INDEX i_groupid (`groupid`),\n INDEX i_fid (`fid`),\n INDEX i_view_groupid (`view_groupid`)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_group_filters"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_group_filters") . "` (\n `filterid` int(11) NOT NULL auto_increment,\n `fid` int(11) NOT NULL default 0,\n `groupid` int(11) NOT NULL default 0,\n `filter` text NOT NULL,\n PRIMARY KEY (`filterid`),\n INDEX i_fid (`fid`),\n INDEX i_groupid (`groupid`)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_applications"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_applications") . "` (\n `appid` int(11) NOT NULL auto_increment,\n `name` varchar(255) NOT NULL default '',\n `description` text NOT NULL,\n PRIMARY KEY (`appid`)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_application_form_link"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_application_form_link") . "` (\n `linkid` int(11) NOT NULL auto_increment,\n `appid` int(11) NOT NULL default 0,\n `fid` int(11) NOT NULL default 0,\n PRIMARY KEY (`linkid`),\n INDEX i_fid (`fid`),\n INDEX i_appid (`appid`)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_screen_form"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_screen_form") . "` (\n `formid` int(11) NOT NULL auto_increment,\n `sid` int(11) NOT NULL default 0,\n `donedest` varchar(255) NOT NULL default '',\n `savebuttontext` varchar(255) NOT NULL default '',\n `alldonebuttontext` varchar(255) NOT NULL default '',\n `displayheading` tinyint(1) NOT NULL default 0,\n `reloadblank` tinyint(1) NOT NULL default 0,\n `formelements` text,\n PRIMARY KEY (`formid`),\n INDEX i_sid (`sid`)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_advanced_calculations"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_advanced_calculations") . "` (\n `acid` int(11) NOT NULL auto_increment,\n `fid` int(11) NOT NULL default '0',\n `name` varchar(255) NOT NULL default '',\n `description` text NOT NULL,\n `input` text NOT NULL,\n `output` text NOT NULL,\n `steps` text NOT NULL,\n `steptitles` text NOT NULL,\n `fltr_grps` text NOT NULL,\n `fltr_grptitles` text NOT NULL,\n PRIMARY KEY (`acid`),\n KEY `i_fid` (`fid`)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_procedure_logs"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_procedure_logs") . "` (\n `proc_log_id` int(11) unsigned NOT NULL auto_increment,\n `proc_id` int(11) NOT NULL,\n `proc_datetime` datetime NOT NULL,\n `proc_uid` mediumint(8) unsigned NOT NULL,\n PRIMARY KEY (`proc_log_id`),\n INDEX i_proc_id (proc_id),\n INDEX i_proc_uid (proc_uid)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_procedure_logs_params"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_procedure_logs_params") . "` (\n `proc_log_param_id` int(11) unsigned NOT NULL auto_increment,\n `proc_log_id` int(11) unsigned NOT NULL,\n `proc_log_param` varchar(255),\n `proc_log_value` varchar(255),\n PRIMARY KEY (`proc_log_param_id`),\n INDEX i_proc_log_id (proc_log_id)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_resource_mapping"), $existingTables)) {
$sql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_resource_mapping") . "` (\n mapping_id int(11) NOT NULL auto_increment,\n internal_id int(11) NOT NULL,\n external_id int(11) NOT NULL,\n resource_type int(4) NOT NULL,\n mapping_active tinyint(1) NOT NULL,\n PRIMARY KEY (mapping_id),\n INDEX i_internal_id (internal_id),\n INDEX i_external_id (external_id),\n INDEX i_resource_type (resource_type)\n) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_deletion_logs"), $existingTables)) {
$sql[] = "CREATE TABLE " . $xoopsDB->prefix("formulize_deletion_logs") . " (\n del_log_id int(11) unsigned NOT NULL auto_increment,\n form_id int(11) NOT NULL,\n entry_id int(7) NOT NULL,\n user_id mediumint(8) NOT NULL,\n context text,\n deletion_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n PRIMARY KEY (del_log_id),\n INDEX i_del_id (del_log_id)\n ) ENGINE=MyISAM;";
}
if (!in_array($xoopsDB->prefix("formulize_screen_template"), $existingTables)) {
$sql[] = "CREATE TABLE " . $xoopsDB->prefix("formulize_screen_template") . " (\n templateid int(11) NOT NULL auto_increment,\n sid int(11) NOT NULL default 0,\n custom_code text NOT NULL,\n template text NOT NULL,\n PRIMARY KEY (`templateid`),\n INDEX i_sid (`sid`)\n ) ENGINE=MyISAM;";
}
// if this is a standalone installation, then we want to make sure the session id field in the DB is large enough to store whatever session id we might be working with
if (file_exists(XOOPS_ROOT_PATH . "/integration_api.php")) {
$sql['increase_session_id_size'] = "ALTER TABLE " . $xoopsDB->prefix("session") . " CHANGE `sess_id` `sess_id` varchar(60) NOT NULL";
}
$sql['add_encrypt'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " ADD `ele_encrypt` tinyint(1) NOT NULL default '0'";
$sql['add_lockedform'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `lockedform` tinyint(1) NULL default NULL";
$sql['drop_from_formulize_id_admin'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `admin`";
$sql['drop_from_formulize_id_groupe'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `groupe`";
$sql['drop_from_formulize_id_email'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `email`";
$sql['drop_from_formulize_id_expe'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `expe`";
$sql['drop_from_formulize_id_maxentries'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `maxentries`";
$sql['drop_from_formulize_id_even'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `even`";
$sql['drop_from_formulize_id_odd'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `odd`";
$sql['drop_from_formulize_id_groupscope'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `groupscope`";
$sql['drop_from_formulize_id_showviewentries'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " DROP `showviewentries`";
$sql['add_filtersettings'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " ADD `ele_filtersettings` text NOT NULL";
$sql['ele_type_100'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " CHANGE `ele_type` `ele_type` varchar(100) NOT NULL default ''";
$sql['ele_disabled_text'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " CHANGE `ele_disabled` `ele_disabled` text NOT NULL ";
$sql['ele_display_dropindex'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " DROP INDEX `ele_display`";
$sql['ele_display_text'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " CHANGE `ele_display` `ele_display` text NOT NULL ";
$sql['ele_display_addindex'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " ADD INDEX `ele_display` ( `ele_display` ( 255 ) )";
$sql['sep_to_areamodif'] = "UPDATE " . $xoopsDB->prefix("formulize") . " SET ele_type='areamodif' WHERE ele_type='sep'";
$sql['add_defaultform'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `defaultform` int(11) NOT NULL default 0";
$sql['add_defaultlist'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `defaultlist` int(11) NOT NULL default 0";
$sql['add_menutext'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `menutext` varchar(255) default 'Use the form\\'s title'";
$sql['add_useadvcalcs'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_listofentries") . " ADD `useadvcalcs` varchar(255) NOT NULL default ''";
$sql['add_not_elementemail'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_notification_conditions") . " ADD `not_cons_elementemail` smallint(5) NOT NULL default 0";
$sql['add_form_handle'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `form_handle` varchar(255) NOT NULL";
$sql['id_form_to_form_handle'] = "UPDATE " . $xoopsDB->prefix("formulize_id") . " SET form_handle = id_form WHERE form_handle IS NULL OR form_handle = ''";
$sql['add_dedisplay'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_listofentries") . " ADD `dedisplay` int(1) NOT NULL";
$sql['add_store_revisions'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `store_revisions` tinyint(1) NOT NULL default '0'";
$sql['add_finishisdone'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_multipage") . " ADD `finishisdone` tinyint(1) NOT NULL default 0";
$sql['add_toptext'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_multipage") . " ADD `toptemplate` text NOT NULL";
$sql['add_elementtext'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_multipage") . " ADD `elementtemplate` text NOT NULL";
$sql['add_bottomtext'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_multipage") . " ADD `bottomtemplate` text NOT NULL";
$sql['add_formelements'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_screen_form") . " ADD `formelements` text";
$sql['add_on_before_save'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `on_before_save` text";
$sql['add_on_after_save'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `on_after_save` text";
$sql['add_custom_edit_check'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `custom_edit_check` text";
$sql['add_form_note'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_id") . " ADD `note` text";
$sql['add_use_default_when_blank'] = "ALTER TABLE " . $xoopsDB->prefix("formulize") . " ADD `ele_use_default_when_blank` tinyint(1) NOT NULL default '0'";
$sql['add_global_search_to_saved_view'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_saved_views") . " ADD `sv_global_search` text";
$sql['add_application_code'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_applications") . " ADD `custom_code` mediumtext";
$sql['add_note_to_menu_links'] = "ALTER TABLE " . $xoopsDB->prefix("formulize_menu_links") . " ADD `note` text";
foreach ($sql as $key => $thissql) {
if (!($result = $xoopsDB->query($thissql))) {
if ($key === "add_encrypt") {
print "ele_encrypt field already added. result: OK<br>";
} elseif ($key === "add_lockedform") {
print "lockedform field already added. result: OK<br>";
} elseif ($key === "add_filtersettings") {
print "element filtersettings field already added. result: OK<br>";
} elseif ($key === "add_defaultform") {
print "defaultform field already added. result: OK<br>";
} elseif ($key === "add_defaultlist") {
print "defaultlist field already added. result: OK<br>";
} elseif ($key === "add_menutext") {
print "menutext field already added. result: OK<br>";
} elseif ($key === "add_useadvcalcs") {
print "useadvcalcs field already added. result: OK<br>";
} elseif ($key === "add_not_elementemail") {
print "elementemail notification option already added. result: OK<br>";
} elseif ($key === "add_form_handle") {
print "form handles already added. result: OK<br>";
} elseif ($key === "add_dedisplay") {
print "dedisplay already added. result: OK<br>";
} elseif ($key === "add_store_revisions") {
print "store_revisions already added. result: OK<br>";
} elseif ($key === "add_finishisdone") {
print "finishisdone for multipage forms already added. result: OK<br>";
} elseif ($key === "add_toptext") {
print "toptemplate already added for multipage screens. result: OK<br>";
} elseif ($key === "add_elementtext") {
print "elementtemplate already added for multipage screens. result: OK<br>";
} elseif ($key === "add_bottomtext") {
print "bottomtemplate already added for multipage screens. result: OK<br>";
} elseif ($key === "add_formelements") {
print "formelements field already added for single page screens. result: OK<br>";
} elseif ($key === "add_on_before_save") {
print "on_before_save field already added. result: OK<br>";
} elseif ($key === "add_on_after_save") {
print "on_after_save field already added. result: OK<br>";
} elseif ($key === "add_custom_edit_check") {
print "custom_edit_check field already added. result: OK<br>";
} elseif ($key === "add_form_note") {
print "form note field already added. result: OK<br>";
} elseif ($key === "add_use_default_when_blank") {
print "use default when blank already added. result: OK<br>";
} elseif ($key === "add_global_search_to_saved_view") {
print "global search saved view already added. result: OK<br>";
} elseif ($key === "add_application_code") {
print "application custom_code field added. result: OK<br>";
} elseif ($key === "add_note_to_menu_links") {
print "note already added for menu links. result: OK<br>";
} elseif (strstr($key, 'drop_from_formulize_id_')) {
continue;
} else {
exit("Error patching DB for Formulize 4.0. SQL dump:<br>" . $thissql . "<br>" . $xoopsDB->error() . "<br>Please contact <a href=mailto:formulize@freeformsolutions.ca>Freeform Solutions</a> for assistance.");
}
}
}
// if there is a framework handles table present, then we need to check for a few things to ensure the integrity of code and our ability to disambiguate inputs to the API
if (in_array($xoopsDB->prefix("formulize_framework_elements"), $existingTables)) {
// need to change rules...framework handles must now be globally unique, so we can disambiguate them from each other when we are passed just a framework handle
$uniqueSQL = "SELECT elements.ele_caption, elements.ele_id, elements.ele_handle, handles.fe_handle, handles.fe_frame_id FROM " . $xoopsDB->prefix("formulize") . " as elements, " . $xoopsDB->prefix("formulize_framework_elements") . " as handles WHERE EXISTS (SELECT 1 FROM " . $xoopsDB->prefix("formulize_framework_elements") . " as checkhandles WHERE handles.fe_handle = checkhandles.fe_handle AND handles.fe_element_id != checkhandles.fe_element_id) AND handles.fe_element_id = elements.ele_id AND handles.fe_handle != \"\" ORDER BY handles.fe_handle";
$uniqueRes = $xoopsDB->query($uniqueSQL);
$haveWarning = false;
$warningIdentifier = array();
$warningContents = array();
if ($xoopsDB->getRowsNum($uniqueRes)) {
$haveWarning = true;
$warningIdentifier[] = "<li>You have some \"framework handles\" which are the same between different frameworks.</li>";
ob_start();
print "<ul>";
$prevHandle = "";
while ($uniqueArray = $xoopsDB->fetchArray($uniqueRes)) {
if ($uniqueArray['fe_handle'] != $prevHandle) {
if ($prevHandle != "") {
// need to finish previous set and print out what's missing
print "</li>";
}
print "<li>Framework handle: <b>" . $uniqueArray['fe_handle'] . "</b> is used in more than one place:<br>";
}
$prevHandle = $uniqueArray['fe_handle'];
print " In framework " . $uniqueArray['fe_frame_id'] . ", it is used for element " . $uniqueArray['ele_id'] . " (" . $uniqueArray['ele_caption'] . ")<br>";
if ($uniqueArray['fe_handle'] != $uniqueArray['ele_handle']) {
print " For element " . $uniqueArray['ele_id'] . ", use the element's data handle instead: <b>" . $uniqueArray['ele_handle'] . "</b><br>";
}
}
// dump the last stuff we had found in the loop
print "</li>";
print "</ul>";
$warningContents[] = ob_get_clean();
}
// need to disambiguate framework handles and elements' data handles.
// no framework handle can be identical to the text of any data handle, unless they refer to the same element
// So look up all the elements that have a data handle that matches a framework handle, which is not referring to the same element
$handleSQL = "SELECT elements.ele_id, elements.ele_caption, elements.ele_handle, handles.fe_frame_id, handles.fe_handle, handles.fe_element_id, e2.ele_caption as handlecap, e2.ele_handle as newhandle FROM " . $xoopsDB->prefix("formulize") . " AS elements, " . $xoopsDB->prefix("formulize_framework_elements") . " AS handles, " . $xoopsDB->prefix("formulize") . " AS e2 WHERE elements.ele_handle = handles.fe_handle AND handles.fe_element_id != elements.ele_id AND handles.fe_element_id = e2.ele_id ORDER BY elements.id_form, elements.ele_order";
$handleRes = $xoopsDB->query($handleSQL);
if ($xoopsDB->getRowsNum($handleRes) > 0) {
$haveWarning = true;
$warningIdentifier[] = "<li>You have some \"data handles\" which are identical to the \"framework handles\" of other elements.</li>";
ob_start();
print "<ul>";
while ($handleArray = $xoopsDB->fetchArray($handleRes)) {
print "<li>" . $handleArray['handlecap'] . " (element " . $handleArray['fe_element_id'] . ") &mdash framework handle: <b>" . $handleArray['fe_handle'] . "</b> in framework " . $handleArray['fe_frame_id'] . "<br>";
print " Use the element's data handle instead: <b>" . $handleArray['newhandle'] . "</b></li>";
}
print "</ul>";
$warningContents[] = ob_get_clean();
}
}
if ($haveWarning) {
print "<hr><p>MAJOR WARNING:</p>";
print "<ol>";
print implode("\n", $warningIdentifier);
print "</ol>";
print "<p>Framework handles are deprecated in Formulize 4, and having framework handles that are not entirely unique can now lead to serious errors in some situations. However, we cannot automatically fix this situation for you, because you may have used the framework handles in programming code in your website. If we try an automatic fix, we could break some other parts of your website.</p><p>Recommended actions:</p><p>1. Note down the following framework handles for the following elements (copy this information to a file, print this page, etc):<br>";
print implode("\n", $warningContents);
print "</p><p>2. Determine if you have any saved views based on a framework, that include the elements (columns) mentioned above.</p>";
print "<p>3. For any elements mentioned above that are included in framework-based saved views, change their framework handles as suggested above.</p>";
print "<p>4. Open up the affected saved views, and re-add the changed elements (columns) to them. Search terms on changed columns will need to be respecified too, as well as sorting options. Then re-save the view. <b>Your users will need to do this too, if they have personal saved views which you cannot access.</b></p>";
print "<p>5. Determine where you may be using the <b>framework handles</b> mentioned above in any programming code in your website.</p>";
print "<p>6. For any elements where you are using the framework handles in programming code, change those framework handles as suggested above. You will need to make these changes in the framework configuration settings, as well as in the actual programming code where you are referring to the handle.</p>";
print "<p> --- </p>";
print "<p><b>You probably don't need to make all of the changes suggested above!</b> You only need to make changes in places where there are saved views and/or programming code that uses the elements mentioned above. For some websites, that will mean you don't have to make any changes (ie: if there are no saved views based on a framework, and you have no programming code referring to frameworks).</p>";
print "<p> --- </p>";
print "<p><b>You can re-run this patch after making changes. If you do not get this warning, then your site should be OK.</b></p>";
print "<p>If you have any questions about this upgrade issue, please contact <a href=mailto:formulize@freeformsolutions.ca>Freeform Solutions</a> for assistance.</p>";
}
//create new menus table
if (!in_array($xoopsDB->prefix("formulize_menu_links"), $existingTables)) {
$menusql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_menu_links") . "` (\n `menu_id` int(11) unsigned NOT NULL auto_increment,\n `appid` int(11) unsigned NOT NULL,\n `screen` varchar(11),\n `rank` int(11),\n `url` varchar(255),\n `link_text` varchar(255),\n PRIMARY KEY (`menu_id`),\n INDEX i_menus_appid (appid)\n );";
$menusql[] = "CREATE TABLE `" . $xoopsDB->prefix("formulize_menu_permissions") . "` (\n `permission_id` int(11) unsigned NOT NULL auto_increment,\n `menu_id` int(11) unsigned NOT NULL,\n `group_id` int(11) unsigned NOT NULL,\n `default_screen` tinyint(1) NOT NULL default '0',\n PRIMARY KEY (`permission_id`),\n INDEX i_menu_permissions (menu_id)\n );";
print "Creating new menu_links and menu_permissions tables...<br>";
foreach ($menusql as $key => $thissql) {
if (!($result = $xoopsDB->query($thissql))) {
exit("Error patching DB for Formulize 4.0. SQL dump:<br>" . $thissql . "<br>" . $xoopsDB->error() . "<br>Please contact <a href=mailto:formulize@freeformsolutions.ca>Freeform Solutions</a> for assistance.");
}
}
// populate new menus tables with existing menu entries
$application_handler = xoops_getmodulehandler('applications', 'formulize');
$form_handler = xoops_getmodulehandler('forms', 'formulize');
$allApplications = $application_handler->getAllApplications();
$menuTexts = array();
$i = 0;
foreach ($allApplications as $thisApplication) {
$forms = $thisApplication->getVar('forms');
foreach ($forms as $thisForm) {
$thisFormObject = $form_handler->get($thisForm);
if ($menuText = $thisFormObject->getVar('menutext')) {
saveMenuEntryAndPermissionsSQL($thisFormObject->getVar('id_form'), $thisApplication->getVar("appid"), $i, $menuText);
}
$i++;
}
$i = 0;
}
$formsWithNoApplication = $form_handler->getFormsByApplication(0, true);
// true forces ids not objects to be returned
foreach ($formsWithNoApplication as $thisForm) {
$thisFormObject = $form_handler->get($thisForm);
if ($menuText = $thisFormObject->getVar('menutext')) {
saveMenuEntryAndPermissionsSQL($thisFormObject->getVar('id_form'), 0, $i, $menuText);
}
$i++;
}
}
// need to update multiple select boxes for new data structure
// $xoopsDB->prefix("formulize")
// 1. get a list of all elements that are linked selectboxes that support only single values
$selectBoxesSQL = "SELECT id_form, ele_id FROM " . $xoopsDB->prefix("formulize") . " WHERE ele_type = 'select'";
$selectBoxRes = $xoopsDB->query($selectBoxesSQL);
if ($xoopsDB->getRowsNum($selectBoxRes) > 0) {
while ($handleArray = $xoopsDB->fetchArray($selectBoxRes)) {
$metaData = formulize_getElementMetaData($handleArray['ele_id']);
$ele_value = unserialize($metaData['ele_value']);
// select only single option, linked select boxes
if (!$ele_value[1] and strstr($ele_value[2], "#*=:*")) {
$successSelectBox = convertSelectBoxToSingle($xoopsDB->prefix('formulize_' . $handleArray['id_form']), $handleArray['ele_id']);
if (!$successSelectBox) {
print "could not convert column " . $handleArray['ele_id'] . " in table " . $xoopsDB->prefix('formulize_' . $handleArray['id_form']) . "<br>";
}
}
}
}
// if the relationship link option, unified_delete, does not exist, create the field and default to the unified_display setting value
$sql = $xoopsDB->query("show columns from " . $xoopsDB->prefix("formulize_framework_links") . " where Field = 'fl_unified_delete'");
if (0 == $xoopsDB->getRowsNum($sql)) {
$sql = "ALTER TABLE " . $xoopsDB->prefix("formulize_framework_links") . " ADD `fl_unified_delete` smallint(5)";
if ($udres1 = $xoopsDB->query($sql)) {
$sql = "update " . $xoopsDB->prefix("formulize_framework_links") . " set `fl_unified_delete` = `fl_unified_display`";
$udres2 = $xoopsDB->query($sql);
}
if (!$udres1 or !$udres2) {
print "Error updating relationships with unified delete option. SQL dump:<br>" . $thissql . "<br>" . $xoopsDB->error() . "<br>Please contact <a href=mailto:formulize@freeformsolutions.ca>Freeform Solutions</a> for assistance.";
} else {
print "Updating relationships with unified delete option. result: OK<br>";
}
}
// CONVERTING EXISTING TEMPLATES IN DB TO TEMPLATE FILES
$screenpathname = XOOPS_ROOT_PATH . "/modules/formulize/templates/screens/default/";
$templateSQL = "SELECT sid, toptemplate, listtemplate, bottomtemplate FROM " . $xoopsDB->prefix("formulize") . "_screen_listofentries";
$templateRes = $xoopsDB->query($templateSQL);
if ($xoopsDB->getRowsNum($templateRes) > 0) {
while ($handleArray = $xoopsDB->fetchArray($templateRes)) {
if (!file_exists($screenpathname . $handleArray['sid'])) {
$pathname = $screenpathname . $handleArray['sid'] . "/";
mkdir($pathname, 0777, true);
if (!is_writable($pathname)) {
chmod($pathname, 0777);
}
saveTemplate($handleArray['toptemplate'], $handleArray['sid'], "toptemplate");
saveTemplate($handleArray['bottomtemplate'], $handleArray['sid'], "bottomtemplate");
saveTemplate($handleArray['listtemplate'], $handleArray['sid'], "listtemplate");
} else {
print "screen templates for screen " . $handleArray['sid'] . " already exist. result: OK<br>";
}
}
}
$multitemplateSQL = "SELECT sid, toptemplate, elementtemplate, bottomtemplate FROM " . $xoopsDB->prefix("formulize") . "_screen_multipage";
$multitemplateRes = $xoopsDB->query($multitemplateSQL);
if ($xoopsDB->getRowsNum($multitemplateRes) > 0) {
while ($handleArray = $xoopsDB->fetchArray($multitemplateRes)) {
if (!file_exists($screenpathname . $handleArray['sid'])) {
$pathname = $screenpathname . $handleArray['sid'] . "/";
mkdir($pathname, 0777, true);
if (!is_writable($pathname)) {
chmod($pathname, 0777);
}
saveTemplate($handleArray['toptemplate'], $handleArray['sid'], "toptemplate");
saveTemplate($handleArray['bottomtemplate'], $handleArray['sid'], "bottomtemplate");
saveTemplate($handleArray['elementtemplate'], $handleArray['sid'], "elementtemplate");
} else {
print "screen templates for screen " . $handleArray['sid'] . " already exist. result: OK<br>";
}
}
}
print "DB updates completed. result: OK";
}
}
function printResults($masterResults, $blankSettings, $groupingSettings, $groupingValues, $masterResultsRaw, $filename = "", $title = "")
{
$output = "";
foreach ($masterResults as $elementId => $calcs) {
$output .= "<tr><td class=head colspan=2>\n";
$output .= printSmart(trans(getCalcHandleText($elementId)), 100);
$output .= "\n</td></tr>\n";
foreach ($calcs as $calc => $groups) {
$countGroups = count($groups);
$rowspan = ($countGroups > 1 and $calc != "count" and $calc != "sum") ? $countGroups : 1;
$output .= "<tr><td class=even rowspan={$rowspan}>\n";
// start of row with calculation results (possibly first row among many)
switch ($calc) {
case "sum":
$calc_name = _formulize_DE_CALC_SUM;
break;
case "avg":
$calc_name = _formulize_DE_CALC_AVG;
break;
case "min":
$calc_name = _formulize_DE_CALC_MIN;
break;
case "max":
$calc_name = _formulize_DE_CALC_MAX;
break;
case "count":
$calc_name = _formulize_DE_CALC_COUNT;
break;
case "per":
$calc_name = _formulize_DE_CALC_PER;
break;
}
$output .= "<p><b>{$calc_name}</b></p>\n";
switch ($blankSettings[$elementId][$calc]) {
case "all":
$bsetting = _formulize_DE_INCLBLANKS;
break;
case "noblanks":
$bsetting = _formulize_DE_EXCLBLANKS;
break;
case "onlyblanks":
$bsetting = _formulize_DE_INCLONLYBLANKS;
break;
case "justnoblanks":
$bsetting = _formulize_DE_EXCLONLYBLANKS;
break;
case "justnozeros":
$bsetting = _formulize_DE_EXCLONLYZEROS;
break;
default:
// must be custom
$bsetting = _formulize_DE_EXCLCUSTOM;
$setting = explode(",", substr(str_replace("!@^%*", ",", $blankSettings[$elementId][$calc]), 6));
// replace back the commas and remove the word custom from the front, and explode it into an array
$start = 1;
foreach ($setting as $thissetting) {
if (!$start) {
$bsetting .= ", ";
}
$start = 0;
if (substr($thissetting, 0, 1) == "!") {
$notText = strtolower(_formulize_NOT) . " ";
$thissetting = substr($thissetting, 1);
} else {
$notText = "";
}
$bsetting .= $notText . $thissetting;
}
break;
}
$output .= "<p class='formulize_blank_setting'>{$bsetting}</p>\n</td>\n";
// start of right hand column for calculation results
if ($calc == "count") {
$output .= "<td class=odd>\n";
// start of cell with calculations results
if ($countGroups > 1) {
$theseGroupSettings = explode("!@^%*", $groupingSettings[$elementId][$calc]);
$firstGroupSettingText = printSmart(trans(getCalcHandleText($theseGroupSettings[0], true)));
$output .= "<table style='width: auto;'><tr><th>{$firstGroupSettingText}</th><td class='count-total' style='padding-left: 2em;'><center><b>" . _formulize_DE_CALC_NUMENTRIES . "</b><center></td><td class='count-unique' style='padding-left: 2em;'><center><b>" . _formulize_DE_CALC_NUMUNIQUE . "</b><center></td></tr>\n";
$totalCount = 0;
$totalUnique = 0;
foreach ($masterResultsRaw[$elementId][$calc] as $group => $rawResult) {
foreach ($theseGroupSettings as $id => $thisGroupSetting) {
if ($thisGroupSetting === "none") {
continue;
}
$elementMetaData = formulize_getElementMetaData($thisGroupSetting, false);
$groupText = formulize_swapUIText($groupingValues[$elementId][$calc][$group][$id], unserialize($elementMetaData['ele_uitext']));
$output .= "<tr><td>" . printSmart(trans($groupText)) . "</td><td class='count-total' style='text-align: right;'>" . $rawResult['count'] . "</td><td class='count-unique' style='text-align: right;'>" . $rawResult['countunique'] . "</td></tr>";
$totalCount += $rawResult['count'];
$totalUnique += $rawResult['countunique'];
}
}
$output .= "<tr><td style='border-top: 1px solid black;'><b>" . _formulize_DE_CALC_GRANDTOTAL . "</b></td><td style='border-top: 1px solid black; text-align: right;' class='count-total'><b>{$totalCount}</b></td><td style='border-top: 1px solid black; text-align: right;' class='count-unique'><b>{$totalUnique}</b></td></tr>\n";
$output .= "</table>";
} else {
$rawResult = $masterResultsRaw[$elementId][$calc][0];
$output .= "<div class='count-total'><p><b>" . _formulize_DE_CALC_NUMENTRIES . " . . . " . $rawResult['count'] . "</b></p></div><div class='count-unique'><p><b>" . _formulize_DE_CALC_NUMUNIQUE . " . . . " . $rawResult['countunique'] . "</b></p></div>\n";
}
$output .= "</td></tr>";
// end of the main row, and the specific cell with the calculations results
} elseif ($calc == "sum") {
$output .= "<td class=odd>\n";
// start of cell with calculations results
$handle = convertElementIdsToElementHandles($elementId);
// returns an array, since it might be passed multiple values
$handle = $handle[0];
if ($countGroups > 1) {
$theseGroupSettings = explode("!@^%*", $groupingSettings[$elementId][$calc]);
$firstGroupSettingText = printSmart(trans(getCalcHandleText($theseGroupSettings[0], true)));
$output .= "<table style='width: auto;'><tr><th>{$firstGroupSettingText}</th><td class='sum-total' style='padding-left: 2em;'><center><b>" . _formulize_DE_CALC_SUM . "</b><center></td></tr>\n";
$totalSum = 0;
foreach ($masterResultsRaw[$elementId][$calc] as $group => $rawResult) {
foreach ($theseGroupSettings as $id => $thisGroupSetting) {
if ($thisGroupSetting === "none") {
continue;
}
$elementMetaData = formulize_getElementMetaData($thisGroupSetting, false);
$groupText = formulize_swapUIText($groupingValues[$elementId][$calc][$group][$id], unserialize($elementMetaData['ele_uitext']));
$output .= "<tr><td>" . printSmart(trans($groupText)) . "</td><td class='sum-total' style='text-align: right;'>" . formulize_numberFormat($rawResult['sum'], $handle) . "</td></tr>";
$totalSum += $rawResult['sum'];
}
}
$output .= "<tr><td style='border-top: 1px solid black;'><b>" . _formulize_DE_CALC_GRANDTOTAL . "</b></td><td style='border-top: 1px solid black; text-align: right;' class='sum-total'><b>" . formulize_numberFormat($totalSum, $handle) . "</b></td></tr>\n";
$output .= "</table>";
} else {
$rawResult = $masterResultsRaw[$elementId][$calc][0];
$output .= "<div class='sum-total'><p><b>" . _formulize_DE_CALC_SUM . " . . . " . formulize_numberFormat($rawResult['sum'], $handle) . "</b></p></div>\n";
}
$output .= "</td></tr>";
// end of the main row, and the specific cell with the calculations results
} else {
$start = 1;
foreach ($groups as $group => $result) {
//foreach($result as $resultID=>$thisResult) {
if (!$start) {
$output .= "<tr>\n";
}
$start = 0;
$output .= "<td class=odd>\n";
//if(count($groups)>1) { // OR count($groups)>1) { // output the heading section for this group of results
$output .= "<p><b>";
$start2 = true;
foreach (explode("!@^%*", $groupingSettings[$elementId][$calc]) as $id => $thisGroupSetting) {
if ($thisGroupSetting === "none") {
continue;
}
if (!$start2) {
$output .= "<br>\n";
}
$start2 = false;
$elementMetaData = formulize_getElementMetaData($thisGroupSetting, false);
$groupText = formulize_swapUIText($groupingValues[$elementId][$calc][$group][$id], unserialize($elementMetaData['ele_uitext']));
$output .= printSmart(trans(getCalcHandleText($thisGroupSetting, true))) . ": " . printSmart(trans($groupText)) . "\n";
}
$output .= "</b></p>\n";
//}
$output .= "<p>{$result}</p>\n";
$output .= "</td></tr>\n";
//}
}
}
}
}
print $output;
// addition of calculation download, August 22 2006
if ($filename) {
// get the current CSS values for head, even and odd
global $xoopsConfig;
$head = "";
$odd = "";
$even = "";
formulize_benchmark("before reading stylesheet");
if (file_exists(XOOPS_ROOT_PATH . "/themes/" . $xoopsConfig['theme_set'] . "/style.css")) {
if (!class_exists('csstidy')) {
// use supplied csstidy in parent if one exists...
if (file_exists(XOOPS_ROOT_PATH . "/plugins/csstidy/class.csstidy.php")) {
include_once XOOPS_ROOT_PATH . "/plugins/csstidy/class.csstidy.php";
} else {
include_once XOOPS_ROOT_PATH . "/modules/formulize/class/class.csstidy.php";
}
}
$css = new csstidy();
$css->set_cfg('merge_selectors', 0);
$css->parse_from_url(XOOPS_ROOT_PATH . "/themes/" . $xoopsConfig['theme_set'] . "/style.css");
$parsed_css = $css->css;
// parsed_css seems to have only one key when looking at the default template...key is the number of styles?
foreach ($parsed_css as $thiscss) {
$head = isset($thiscss['.head']['background-color']) ? $thiscss['.head']['background-color'] : isset($thiscss['.head']['background']) ? $thiscss['.head']['background'] : "";
$even = isset($thiscss['.even']['background-color']) ? $thiscss['.even']['background-color'] : isset($thiscss['.even']['background']) ? $thiscss['.even']['background'] : "";
$odd = isset($thiscss['.odd']['background-color']) ? $thiscss['.odd']['background-color'] : isset($thiscss['.odd']['background']) ? $thiscss['.odd']['background'] : "";
}
}
formulize_benchmark("after reading stylesheet");
unset($css);
// if we couldn't find any values, use these:
$head = $head ? $head : "#c2cdd6";
$even = $even ? $even : "#dee3e7";
$odd = $odd ? $odd : "#E9E9E9";
// create the file
formulize_benchmark("before creating file");
$outputfile = "<HTML>\n<head>\n<meta name=\"generator\" content=\"Formulize -- form creation and data management for XOOPS\" />\n<title>" . _formulize_DE_EXPORTCALC_TITLE . " '{$title}'</title>\n<style type=\"text/css\">\n.outer {border: 1px solid silver;}\n.head { background-color: {$head}; padding: 5px; font-weight: bold; }\n.even { background-color: {$even}; padding: 5px; }\t\t\n.odd { background-color: {$odd}; padding: 5px; }\nbody {color: black; background: white; margin-top: 30px; margin-bottom: 30px; margin-left: 30px; margin-right: 30px; padding: 0; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10pt;}\ntd { vertical-align: top; }\n</style>\n</head>\n<body>\n<h1>" . _formulize_DE_EXPORTCALC_TITLE . " '{$title}'</h1>\n<table class=outer>\n{$output}\n</table>\n</body>\n</html>";
// output the file
$exfilename = strrchr($filename, "/");
$wpath = XOOPS_ROOT_PATH . SPREADSHEET_EXPORT_FOLDER . "{$exfilename}";
$exportfile = fopen($wpath, "w");
fwrite($exportfile, $outputfile);
fclose($exportfile);
}
formulize_benchmark("after creating file");
}
function buildEvaluationCondition($match, $indexes, $filterElements, $filterOps, $filterTerms, $entry, $entryData)
{
$evaluationCondition = "";
// convert the internal database representation to the displayed value, if this element has uitext
foreach ($filterElements as $key => $element) {
$element_metadata = formulize_getElementMetaData($element, true);
if (isset($element_metadata['ele_uitext'])) {
$filterTerms[$key] = formulize_swapUIText($filterTerms[$key], unserialize($element_metadata['ele_uitext']));
}
}
for ($io = 0; $io < count($indexes); $io++) {
$i = $indexes[$io];
if (!($evaluationCondition == "")) {
$evaluationCondition .= " {$match} ";
}
switch ($filterOps[$i]) {
case "=":
$thisOp = "==";
break;
case "NOT":
$thisOp = "!=";
break;
default:
$thisOp = $filterOps[$i];
}
if ($filterTerms[$i] === "{BLANK}") {
$filterTerms[$i] = "";
}
if (isset($GLOBALS['formulize_asynchronousFormDataInAPIFormat'][$entry][$filterElements[$i]])) {
$compValue = $GLOBALS['formulize_asynchronousFormDataInAPIFormat'][$entry][$filterElements[$i]];
} elseif ($entry == "new") {
// for textboxes, let's try to get their default value
// for other elements, generate the default is too tricky to get it to work at present, not enough time available
$element_handler = xoops_getmodulehandler('elements', 'formulize');
$elementObject = $element_handler->get($filterElements[$i]);
if (is_object($elementObject)) {
$ele_type = $elementObject->getVar('ele_type');
if ($ele_type == "text" or $ele_type == "textarea") {
$ele_value = $elementObject->getVar('ele_value');
$defaultKey = $ele_type == "text" ? 2 : 0;
// default key is in different places for different types of elements
$compValue = getTextboxDefault($ele_value[$defaultKey], $elementObject->getVar('id_form'), $entry);
} else {
$compValue = "";
}
} else {
$compValue = "";
}
} else {
$compValue = display($entryData[0], $filterElements[$i]);
}
if (is_array($compValue)) {
if ($thisOp == "=") {
$thisOp = "LIKE";
}
if ($thisOp == "!=") {
$thisOp = "NOT LIKE";
}
$compValue = implode(",", $compValue);
} else {
$compValue = addslashes($compValue);
}
if ($thisOp == "LIKE") {
$evaluationCondition .= "strstr('" . $compValue . "', '" . addslashes($filterTerms[$i]) . "')";
} elseif ($thisOp == "NOT LIKE") {
$evaluationCondition .= "!strstr('" . $compValue . "', '" . addslashes($filterTerms[$i]) . "')";
} else {
$evaluationCondition .= "'" . $compValue . "' {$thisOp} '" . addslashes($filterTerms[$i]) . "'";
}
}
return $evaluationCondition;
}
function formulize_numberFormat($value, $handle, $decimalOverride = 0)
{
if (!is_numeric($value) or substr($value, 0, 1) == "0") {
return $value;
}
$id = formulize_getIdFromElementHandle($handle);
$elementMetaData = formulize_getElementMetaData($id, false);
if ($elementMetaData['ele_type'] == "text") {
$ele_value = unserialize($elementMetaData['ele_value']);
// value, decimaloverride, decimals, decsep exists, decsep, sep exists, sep, prefix exists, prefix
return _formulize_numberFormat($value, $decimalOverride, $ele_value[5], isset($ele_value[7]), $ele_value[7], isset($ele_value[8]), $ele_value[8], isset($ele_value[6]), $ele_value[6], isset($ele_value[10]), $ele_value[10]);
} elseif ($elementMetaData['ele_type'] == "derived") {
$ele_value = unserialize($elementMetaData['ele_value']);
// value, decimaloverride, decimals, decsep exists, decsep, sep exists, sep, prefix exists, prefix
return _formulize_numberFormat($value, $decimalOverride, $ele_value[1], isset($ele_value[3]), $ele_value[3], isset($ele_value[4]), $ele_value[4], isset($ele_value[2]), $ele_value[2], isset($ele_value[5]), $ele_value[5]);
} else {
return $value;
}
}
