function isHandleUnique($handle, $element_id = "")
{
$handle = formulizeForm::sanitize_handle_name($handle);
if (isMetaDataField($handle)) {
return false;
// don't allow reserved words that will be used in the main data extraction queries
}
global $xoopsDB;
$element_id_condition = $element_id ? " AND ele_id != " . intval($element_id) : "";
$sql = "SELECT count(ele_handle) FROM " . $xoopsDB->prefix("formulize") . " WHERE ele_handle = '" . formulize_db_escape($handle) . "' {$element_id_condition}";
if (!($res = $xoopsDB->query($sql))) {
print "Error: could not verify uniqueness of handle '{$handle}' in form {$fid}";
} else {
$row = $xoopsDB->fetchRow($res);
if ($row[0] == 0) {
// zero rows found with that handle in this form
return true;
} else {
return false;
}
}
}
function prepvalues($value, $field, $entry_id)
{
global $xoopsDB;
// return metadata values without putting them in an array
if (isMetaDataField($field)) {
return $value;
}
$elementArray = formulize_getElementMetaData($field, true);
$type = $elementArray['ele_type'];
// handle yes/no cases
if ($type == "yn") {
// if we've found one
if ($value == "1") {
$value = _formulize_TEMP_QYES;
} elseif ($value == "2") {
$value = _formulize_TEMP_QNO;
} else {
$value = "";
}
}
// decrypt encrypted values...pretty inefficient to do this here, one query in the DB per value to decrypt them....but we'd need proper select statements with field names specified in them, instead of *, in order to be able to swap in the AES DECRYPT at the time the data is retrieved in the master query
if ($elementArray['ele_encrypt']) {
$decryptSQL = "SELECT AES_DECRYPT('" . formulize_db_escape($value) . "', '" . getAESPassword() . "')";
if ($decryptResult = $xoopsDB->query($decryptSQL)) {
$decryptRow = $xoopsDB->fetchRow($decryptResult);
return $decryptRow[0];
} else {
return "";
}
}
// handle cases where the value is linked to another form
if ($source_ele_value = formulize_isLinkedSelectBox($field, true)) {
// value is an entry id in another form
// need to get the form id by checking the ele_value[2] property of the element definition, to get the form id from the first part of that
$sourceMeta = explode("#*=:*", $source_ele_value[2]);
// [0] will be the fid of the form we're after, [1] is the handle of that element
if ($value and $sourceMeta[1]) {
// need to check if an alternative value field has been defined, or if we're in an export and an alterative field for exports has been defined
// save the value before convertElementIdsToElementHandles()
$before_conversion = $sourceMeta[1];
$altFieldSource = "";
if ($GLOBALS['formulize_doingExport'] and isset($source_ele_value[11]) and $source_ele_value[11] != "none") {
$altFieldSource = $source_ele_value[11];
} elseif (isset($source_ele_value[EV_MULTIPLE_LIST_COLUMNS]) and $source_ele_value[EV_MULTIPLE_LIST_COLUMNS] != "none") {
$altFieldSource = $source_ele_value[EV_MULTIPLE_LIST_COLUMNS];
}
if ($altFieldSource) {
$altFieldSource = is_array($altFieldSource) ? $altFieldSource : array($altFieldSource);
$sourceMeta[1] = convertElementIdsToElementHandles($altFieldSource, $sourceMeta[0]);
// remove empty entries, which can happen if the "use the linked field selected above" option is selected
$sourceMeta[1] = array_filter($sourceMeta[1]);
// unfortunately, sometimes sourceMeta[1] seems to be saved as element handles rather than element IDs, and in that case,
// convertElementIdsToElementHandles() returns array(0 => 'none') which causes an error in the query below.
// check for that case here and revert back to the value of sourceMeta[1] before convertElementIdsToElementHandles()
if (1 == count($sourceMeta[1]) and isset($sourceMeta[1][0]) and "none" == $sourceMeta[1][0] or $sourceMeta[1] == "none") {
$sourceMeta[1] = $before_conversion;
}
}
$form_handler = xoops_getmodulehandler('forms', 'formulize');
$sourceFormObject = $form_handler->get($sourceMeta[0]);
$sourceMeta[1] = is_array($sourceMeta[1]) ? $sourceMeta[1] : array($sourceMeta[1]);
$query_columns = array();
foreach ($sourceMeta[1] as $key => $handle) {
// check if this is a link to a link
if ($second_source_ele_value = formulize_isLinkedSelectBox($handle, true)) {
$secondSourceMeta = explode("#*=:*", $second_source_ele_value[2]);
$secondFormObject = $form_handler->get($secondSourceMeta[0]);
$sql = "SELECT t1.`" . $secondSourceMeta[1] . "` FROM " . DBPRE . "formulize_" . $secondFormObject->getVar('form_handle') . " as t1, " . DBPRE . "formulize_" . $sourceFormObject->getVar('form_handle') . " as t2 WHERE t2.`entry_id` IN (" . trim($value, ",") . ") AND t1.`entry_id` IN (TRIM(',' FROM t2.`" . $handle . "`)) ORDER BY t2.`entry_id`";
if (!($res = $xoopsDB->query($sql))) {
print "Error: could not retrieve the source values for a linked linked selectbox ({$field}) during data extraction for entry number {$entry_id}. SQL:<br>{$sql}<br>";
} else {
$row = $xoopsDB->fetchRow($res);
$linkedvalue = prepvalues($row[0], $handle, $entry_id);
$query_columns[] = "'" . formulize_db_escape($linkedvalue[0]) . "'";
}
} else {
$query_columns[] = "`{$handle}`";
}
}
$sql = "SELECT " . implode(", ", $query_columns) . " FROM " . DBPRE . "formulize_" . $sourceFormObject->getVar('form_handle') . " WHERE entry_id IN (" . trim($value, ",") . ") ORDER BY entry_id";
if (!($res = $xoopsDB->query($sql))) {
print "Error: could not retrieve the source values for a linked selectbox during data extraction for entry number {$entry_id}. SQL:<br>{$sql}<br>";
} else {
$value = "";
while ($row = $xoopsDB->fetchRow($res)) {
$value .= "*=+*:" . implode(" - ", $row);
}
}
} elseif ($value) {
$value = "";
// if there was no sourceMeta[1], which is the handle for the field in the source form, then the value should be empty, ie: we cannot make a link...this probably only happens in cases where there's a really old element that had its caption changed, and that happened before Formulize automatically updated all the linked selectboxes that rely on that element's caption, back when captions mattered in the pre F3 days
}
}
// check if this is fullnames/usernames box
// wickedly inefficient to go to DB for each value!! This loop executes once per datapoint in the result set!!
if ($type == "select") {
$ele_value = unserialize($elementArray['ele_value']);
if (is_array($ele_value[2])) {
$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';
$value = "";
if (strlen($uidFilter) > 4) {
// skip this when $uidFilter = "uid=" becaues the query will fail
$names = go("SELECT uname, name FROM " . DBPRE . "users WHERE {$uidFilter} ORDER BY {$listtype}");
foreach ($names as $thisname) {
if ($thisname[$listtype]) {
$value .= "*=+*:" . $thisname[$listtype];
} else {
$value .= "*=+*:" . $thisname['uname'];
}
}
}
} else {
$value = "";
}
}
}
}
//and remove any leading *=+*: while we're at it...
if (substr($value, 0, 5) == "*=+*:") {
$value = substr_replace($value, "", 0, 5);
}
// Convert 'Other' options into the actual text the user typed
if (($type == "radio" or $type == "checkbox") and preg_match('/\\{OTHER\\|+[0-9]+\\}/', $value)) {
// convert ffcaption to regular and then query for id
$realcap = str_replace("`", "'", $ffcaption);
$newValueq = go("SELECT other_text FROM " . DBPRE . "formulize_other, " . DBPRE . "formulize WHERE " . DBPRE . "formulize_other.ele_id=" . DBPRE . "formulize.ele_id AND " . DBPRE . "formulize.ele_handle=\"" . formulize_db_escape($field) . "\" AND " . DBPRE . "formulize_other.id_req='" . intval($entry_id) . "' LIMIT 0,1");
//$value_other = _formulize_OPT_OTHER . $newValueq[0]['other_text'];
// removing the "Other: " part...we just want to show what people actually typed...doesn't have to be flagged specifically as an "other" value
$value_other = $newValueq[0]['other_text'];
$value = preg_replace('/\\{OTHER\\|+[0-9]+\\}/', $value_other, $value);
} else {
$value = formulize_swapUIText($value, unserialize($elementArray['ele_uitext']));
}
if (file_exists(XOOPS_ROOT_PATH . "/modules/formulize/class/" . $type . "Element.php")) {
$elementTypeHandler = xoops_getmodulehandler($type . "Element", "formulize");
$preppedValue = $elementTypeHandler->prepareDataForDataset($value, $field, $entry_id);
if (!is_array($preppedValue)) {
return array($preppedValue);
} else {
return $preppedValue;
}
}
return explode("*=+*:", $value);
}
function formatLinks($matchtext, $handle, $textWidth = 35, $entryBeingFormatted)
{
formulize_benchmark("start of formatlinks");
global $xoopsDB, $myts;
static $cachedValues = array();
static $cachedTypes = array();
$matchtext = $myts->undoHtmlSpecialChars($matchtext);
if (isMetaDataField($handle)) {
return printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth);
}
if (!isset($cachedValues[$handle])) {
$elementMetaData = formulize_getElementMetaData($handle, true);
$ele_value = unserialize($elementMetaData['ele_value']);
$ele_type = $elementMetaData['ele_type'];
if (!$ele_value) {
return _formatLinksRegularElement($matchtext, $textWidth, $ele_type, $handle, $entryBeingFormatted);
}
if (!isset($ele_value[4])) {
$ele_value[4] = 0;
}
if (!isset($ele_value[3])) {
$ele_value[3] = 0;
}
$cachedValues[$handle] = $ele_value;
$cachedTypes[$handle] = $ele_type;
} else {
$ele_value = $cachedValues[$handle];
$ele_type = $cachedTypes[$handle];
}
formulize_benchmark("got element info");
// dealing with a textbox where an associated element has been set
if ($ele_value[4] > 0 and $ele_type == 'text' or $ele_value[3] > 0 and $ele_type == 'textarea') {
$formulize_mgr = xoops_getmodulehandler('elements', 'formulize');
if ($ele_type == 'text') {
$target_element = $formulize_mgr->get($ele_value[4]);
} else {
$target_element = $formulize_mgr->get($ele_value[3]);
}
$target_fid = $target_element->getVar('id_form');
// if user has no perm in target fid, then do not make link!
if (!($target_allowed = security_check($target_fid))) {
return printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth);
}
$matchtexts = explode(";", $matchtext);
// have to breakup the textbox's text since it may contain multiple matches. Note no space after semicolon spliter, but we trim the results in the foreach loop below.
$printText = "";
$start = 1;
foreach ($matchtexts as $thistext) {
$thistext = trim($thistext);
if (!$start) {
$printText .= ", ";
}
if ($id_req = findMatchingIdReq($target_element, $target_fid, $thistext)) {
$printText .= "<a href='" . XOOPS_URL . "/modules/formulize/index.php?fid={$target_fid}&ve={$id_req}' target='_blank'>" . printSmart(trans($myts->htmlSpecialChars($thistext)), $textWidth) . "</a>";
} else {
$printText .= $myts->htmlSpecialChars($thistext);
}
$start = 0;
}
return $printText;
} elseif ($ele_type == 'select' and is_string($ele_value[2]) and strstr($ele_value[2], "#*=:*") and $ele_value[7] == 1) {
// dealing with a linked selectbox
$boxproperties = explode("#*=:*", $ele_value[2]);
// NOTE:
// boxproperties[0] is form_id
// [1] is handle of linked field
$target_fid = $boxproperties[0];
// if user has no perm in target fid, then do not make link!
if (!($target_allowed = security_check($target_fid))) {
return printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth);
}
static $cachedQueryResults = array();
if (isset($cachedQueryResults[$boxproperties[0]][$boxproperties[1]][$entryBeingFormatted][$handle])) {
$id_req = $cachedQueryResults[$boxproperties[0]][$boxproperties[1]][$entryBeingFormatted][$handle];
} else {
// should only be one match anyway, so limit 0,1 ought to be unnecessary
$element_id_q = q("SELECT ele_id FROM " . $xoopsDB->prefix("formulize") . " WHERE id_form='" . $boxproperties[0] . "' AND ele_handle='" . formulize_db_escape($boxproperties[1]) . "' LIMIT 0,1");
$formulize_mgr = xoops_getmodulehandler('elements', 'formulize');
$target_element =& $formulize_mgr->get($element_id_q[0]['ele_id']);
// get the targetEntry by checking in the entry we're processing, for the actual value recorded in the DB for the entry id we're pointing to
$elementHandle = $handle;
if (is_array($elementHandle)) {
$elementHandle = $elementHandle[0];
}
$currentElementObject = $formulize_mgr->get($elementHandle);
$currentFormId = $currentElementObject->getVar('id_form');
$data_handler = new formulizeDataHandler($currentFormId);
$matchEntryList = explode(",", trim($data_handler->getElementValueInEntry($entryBeingFormatted, $elementHandle), ","));
$id_req = $matchEntryList[0];
$cachedQueryResults[$boxproperties[0]][$boxproperties[1]][$entryBeingFormatted][$handle] = $id_req;
}
if ($id_req) {
return "<a href='" . XOOPS_URL . "/modules/formulize/index.php?fid={$target_fid}&ve={$id_req}' target='_blank'>" . printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth) . "</a>";
} else {
// no id_req found
return printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth);
}
} elseif ($ele_type == 'select' and (isset($ele_value[2]['{USERNAMES}']) or isset($ele_value[2]['{FULLNAMES}'])) and $ele_value[7] == 1) {
$nametype = isset($ele_value[2]['{USERNAMES}']) ? "uname" : "name";
$archiveFilter = $GLOBALS['formulize_archived_available'] ? " AND archived = 0" : "";
static $cachedUidResults = array();
if (isset($cachedUidResults[$matchtext])) {
$uids = $cachedUidResults[$matchtext];
} else {
$uids = q("SELECT uid FROM " . $xoopsDB->prefix("users") . " WHERE {$nametype} = '" . formulize_db_escape($matchtext) . "' {$archiveFilter}");
$cachedUidResults[$matchtext] = $uids;
}
if (count($uids) == 1) {
return "<a href='" . XOOPS_URL . "/userinfo.php?uid=" . $uids[0]['uid'] . "' target=_blank>" . printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth) . "</a>";
} else {
return printSmart(trans($myts->htmlSpecialChars($matchtext)), $textWidth);
}
} elseif ($ele_type == 'derived') {
return formulize_text_to_hyperlink($matchtext, $textWidth);
// allow HTML codes in derived values
} else {
// regular element
formulize_benchmark("done formatting, about to print");
return _formatLinksRegularElement($matchtext, $textWidth, $ele_type, $handle, $entryBeingFormatted);
}
}