/**
* Make sure the float column is in the database in the entry/last entry tables.
*/
public function action_initialize()
{
//check to see that the large blobs are there.
foreach (array('entry', 'last_entry') as $table) {
$qry_show = "SHOW COLUMNS FROM {$table} LIKE '%_value'";
$qry_alter = "ALTER TABLE {$table} ADD COLUMN `float_value` float";
$results = $this->db->query($qry_show);
if (I2CE::pearError($results, "Error getting columns from {$table} table: on {$qry_show}")) {
return false;
}
$found = false;
while ($row = $results->fetchRow()) {
if ($row->field == 'float_value') {
$found = true;
}
}
if (!$found) {
//add the blob column to last_entry
if (I2CE::pearError($this->db->exec($qry_alter), "Error adding float column to {$table}:")) {
return false;
}
}
}
return true;
}
/**
* Adds the last_modifed column to the uuid_map table if it is not there
* @returns true on success
*/
protected function addLastModifiedColumn()
{
$db = MDB2::singleton();
$rows = $db->queryAll("SHOW FULL COLUMNS FROM `uuid_map` WHERE Field='last_modifed'");
if (count($rows) > 0) {
I2CE::raiseError("uuid_map table already has last_modifed");
} else {
$qry_alter = "ALTER TABLE `uuid_map` ADD COLUMN `last_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP;";
if (I2CE::pearError($db->exec($qry_alter), "Error adding parent_id, parent_form column to {$table} table:")) {
return false;
}
}
return true;
}
public function createTable()
{
$qry = 'CREATE TABLE IF NOT EXISTS form_relationship_importer (
hash char(32) NOT NULL,
relationship text NOT NULL,
id text NOT NULL,
KEY hash_rey ( hash, relationship (130) )
) ENGINE=InnoDB DEFAULT CHARSET=utf8
';
$db = MDB2::singleton();
$result = $db->query($qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$qry}")) {
I2CE::raiseError("Could not create form_relationship_importer table");
return false;
}
return true;
}
/**
* Run the upgrade for this module.
* @param string $old_vers
* @param string $new_vers
* @return boolean
*/
public function upgrade($old_vers, $new_vers)
{
if (I2CE_Validate::checkVersion($old_vers, '<', '4.0.3.2')) {
I2CE::raiseError("Upgrading sample data for license end_date");
$db = MDB2::singleton();
$updated = $db->exec("UPDATE last_entry SET date = NOW(), date_value = '2015-01-01 00:00:00' WHERE form_field = 75 AND date_value = '2010-01-01 00:00:00'");
if (!I2CE::pearError($updated, "Unable to update license end_date for Qualify Sample Data.")) {
I2CE::raiseError("Update {$updated} entries.");
$last_mod = $db->exec("UPDATE record SET last_modified = NOW() WHERE form = 16");
if (!I2CE::pearError($last_mod, "Unable to update license end_date for Qualify Sample Data record last modified.")) {
I2CE::raiseError("Updated {$last_mod} records.");
} else {
return false;
}
} else {
return false;
}
I2CE::getConfig()->modules->CachedForms->dirty->license = time();
}
return parent::upgrade($old_vers, $new_vers);
}
/**
* Sets the role in the ldap hierarchy for the indicated user
* @param string $username
* @param string $role
* @returns boolean. true on success
*/
protected function setRole($username, $role)
{
if (!$this->options['can_change_user_role']) {
return false;
}
$qry = "INSERT INTO " . $this->options['user_table'] . " (role,username) VALUES (?, ?) ON DUPLICATE KEY UPDATE role = ?";
$params = array($role, $username, $role);
if (I2CE::pearError($this->db->execParam($qry, $params, array('text', 'text', 'text')), "Cannot update user role")) {
return false;
}
return true;
}
/**
* Create user worker method
* @param string $username
* @param string $password
* @param string $role
* @param array $details. Defatuls to empty array,
* @returns boolean. true on success
*/
public function _createUser($username, $password, $role = false, $details = array())
{
$qry = "INSERT INTO " . $this->passTable . " ( username, password) VALUES (?,?)";
$params = array($username, $this->encryptPassword($password));
if (I2CE::pearError($this->db->execParam($qry, $params, array('text', 'text')), "Cannot create user {$username}")) {
return false;
}
return $this->setUserInfo($username, $role, $details);
}
/**
* Ensure that the user table is presnet, if not attempt to create it
* @returns boolean
*/
protected function ensureUserTable()
{
//initialize the user tables
$init = I2CE::getUserAccessInit('DEFAULT');
if (empty($init)) {
$options = array();
} else {
$options = json_decode($init, true);
if (!is_array($options)) {
I2CE::raiseError("Invalid user access initilization string for Default");
$options = array();
}
}
$options = self::ensureDefaultOptions($options);
$db = MDB2::singleton();
$qry = 'CREATE TABLE IF NOT EXISTS ' . $options['user_table'] . ' ' . '(`id` int(11) NOT NULL auto_increment,' . ' `username` varchar(20) NOT NULL,' . ' `role` varchar(255) collate utf8_bin NOT NULL,' . ' PRIMARY KEY (`id`),' . ' UNIQUE KEY `username` (`username`)' . ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin';
I2CE::raiseError("Initializing LDA-DB User Table. Users' details table se stored in database {$options['userDB']}");
if (I2CE::pearError($db->query($qry), "Cannot create user access table: {$qry}")) {
I2CE::raiseError("Could not initialize LDAP-DB user table");
return false;
}
return true;
}
/**
* Query the last list count and set it in FormStorage
* @param string $form The form name to set.
*/
protected function queryLastListCount($form)
{
$num_rows = $this->db->queryRow("SELECT FOUND_ROWS() AS num_rows");
if (!I2CE::pearError($num_rows, "Couldn't get total number of results.")) {
I2CE_FormStorage::setLastListCount($form, (int) $num_rows->num_rows);
}
}
/**
* Create a data tree from a report of the selectable forms. Deisgned to be fed into tree select
* @param array $fields
* @param array $forms An unorderd array of form names whose values we allow to be selected
* @param array $displayed The displayed forms for the tree
* @param array $limits An array with keys form names and value limit data
* @param array $orders An array with keys form names and values array of field orders for that form.
* If the form name has no orders, we use default ordering for that form based on its displayed firelds
* @param int $show_hidden 0=non-hidden, 1=All, 2=hidden only. Defaults to 0
* @param string $report A report name to use for the query instead of building it from form cache or directly.
* @return array
*/
public static function buildReportTree($fields, $forms, $displayed, $limits, $orders = array(), $show_hidden = 0, $report = null)
{
if (!is_array($report) || !array_key_exists('report', $report)) {
return array();
}
$report_table = I2CE_CustomReport::getCachedTableName($report['report']);
if (!$report_table) {
return array();
}
$map = array();
if (array_key_exists('map', $report)) {
$map = $report['map'];
}
$where = array();
$displays = array();
$report_alias = array();
$formObjs = array();
$use_link = '';
foreach ($fields as $formfield) {
list($form, $link_field) = $formfield;
$ff = $form . ($link_field ? "+{$link_field}" : "");
if (array_key_exists($ff, $map)) {
$report_alias[$ff] = $map[$ff];
} elseif (array_key_exists($form, $map)) {
$report_alias[$form] = $map[$form];
$report_alias[$ff] = $map[$form];
} else {
$report_alias[$form] = $form;
$report_alias[$ff] = $form;
}
//if ( array_key_exists( $ff, $limits ) ) {
//$limit = $limits[$ff];
//} else
if (array_key_exists($report_alias[$ff], $limits)) {
$limit = $limits[$report_alias[$ff]];
} elseif (array_key_exists($form, $limits)) {
$limit = $limits[$form];
} else {
$limit = array();
}
self::addFormIdToLimit($form, $limit);
$limit = self::showHiddenLimit($limit, $show_hidden);
/*
if ( !$show_hidden ) {
$hidden = $report_alias[$ff] . '+i2ce_hidden';
$where[] = "( `$hidden` = 0 OR ISNULL( `$hidden` ) )";
}
*/
$disp_fields = I2CE_List::getDisplayFields($form);
if (array_key_exists($form, $orders)) {
$order = $orders[$form];
} else {
$order = I2CE_List::getSortFields($form);
}
$sort_list = array();
//if ( array_key_exists( $form, $displayed ) && $displayed[$form] ) {
if (array_key_exists($form, $displayed)) {
$alias_form = $report_alias[$ff];
if ($displayed[$form]) {
$displays[$alias_form]['form'] = $form;
if ($use_link == '') {
$displays[$alias_form]['link_field'] = $link_field == '' ? $link_field : "{$alias_form}+{$link_field}";
} else {
$displays[$alias_form]['link_field'] = $use_link;
$use_link = '';
}
foreach ($disp_fields as $disp) {
$displays[$alias_form]['fields'][$disp] = $alias_form . "+{$disp}";
}
foreach ($order as $i => $ord) {
if (!is_string($ord)) {
unset($order[$i]);
continue;
}
if ($ord[0] == '-') {
$field = substr($ord, 1);
$all_orders[] = "`{$alias_form}+{$field}` DESC";
} else {
$field = $ord;
$all_orders[] = "`{$alias_form}+{$field}` ASC";
}
}
} elseif ($use_link == '') {
$use_link = $link_field == '' ? $link_field : "{$alias_form}+{$link_field}";
}
}
if (!array_key_exists($form, $formObjs)) {
$formObjs[$form] = I2CE_FormFactory::instance()->createContainer($form);
if (!$formObjs[$form] instanceof I2CE_Form) {
I2CE::raiseError("Could not instantiate {$form}");
return array();
}
}
self::$curr_alias = $report_alias[$ff];
$where[] = $formObjs[$form]->generateWhereClause($limit, array("I2CE_DataTree", "getSQLField"));
}
$where_clause = "";
$order_by = "";
if (count($where) > 0) {
$where_clause = " WHERE " . implode(' AND ', $where);
}
if (count($all_orders) > 0) {
$order_by = " ORDER BY " . implode(',', $all_orders);
}
$qry = "SELECT * FROM {$report_table} {$where_clause} {$order_by}";
$db = MDB2::singleton();
I2CE::raiseMessage($qry);
$res = $db->query($qry);
if (I2CE::pearError($res, "Invalid report data tree query: ")) {
return array();
}
$phonebook = array();
$results = array();
$display_string = array();
$display_copy = $displays;
while ($data = $res->fetchRow()) {
foreach ($displays as $alias => $disp_data) {
$id_field = strtolower($alias . "+id");
if ($data->{$id_field} == null || array_key_exists($data->{$id_field}, $phonebook)) {
continue;
}
$curr = array();
$add_this = false;
if (in_array($disp_data['form'], $forms)) {
$curr['value'] = $data->{$id_field};
$add_this = true;
}
if (!$add_this) {
$check_ok = false;
foreach ($display_copy as $alias_copy => $disp_data_copy) {
if ($alias_copy == $alias) {
$check_ok = true;
continue;
}
if (!$check_ok) {
continue;
}
if (array_key_exists('link_field', $disp_data_copy) && $disp_data_copy['link_field'] != '') {
$link_field_copy = $disp_data_copy['link_field'];
if ($data->{$id_field} == $data->{$link_field_copy}) {
$add_this = true;
break;
}
}
}
}
if (!$add_this) {
continue;
}
if (!array_key_exists($disp_data['form'], $display_string)) {
$display_string[$disp_data['form']] = I2CE_List::getDisplayString($disp_data['form']);
}
$disp_array = array();
$disp_str = $display_string[$disp_data['form']];
$disp_str_arr = explode('%s', $disp_str);
$fo = $formObjs[$disp_data['form']];
$disp_count = 0;
foreach ($disp_data['fields'] as $field => $dbfield) {
$disp_count++;
if ($dbfield == $disp_data['link_field']) {
// Don't include the data from the link field since it will already be there.
if ($disp_count == 1) {
unset($disp_str_arr[$disp_count]);
} else {
unset($disp_str_arr[$disp_count - 1]);
}
continue;
}
$dbfield = strtolower($dbfield);
$fieldObj = $fo->getField($field);
if (!$fieldObj instanceof I2CE_FormField) {
I2CE::raiseError("Could not get field {$field}");
continue;
}
if (isset($data->{$dbfield})) {
$fieldObj->setFromDB($data->{$dbfield});
$disp_array[$field] = $fieldObj->getDisplayValue();
} else {
$disp_array[$field] = null;
}
}
$disp_str = implode('%s', $disp_str_arr);
$display = vsprintf($disp_str, $disp_array);
$curr['display'] = $display;
$phonebook[$data->{$id_field}] =& $curr;
if ($disp_data['link_field'] != '') {
$link_field = $disp_data['link_field'];
if (array_key_exists($data->{$link_field}, $phonebook)) {
$add_to =& $phonebook[$data->{$link_field}];
if (!array_key_exists('children', $phonebook[$data->{$link_field}])) {
$phonebook[$data->{$link_field}]['children'] = array();
}
$phonebook[$data->{$link_field}]['children'][] =& $curr;
} else {
//I2CE::raiseMessage( "Couldn't find $link_field " . $data->$link_field . " in phonebook " );
}
} else {
$results[] =& $curr;
}
unset($curr);
}
}
return $results;
}
protected static function mail_pear($recipients, $headers, $body, $html = false, $attachments = array())
{
try {
if ($html || count($attachments) > 0) {
//$headers['Content-Type'] = 'multipart/mixed';
$mime = new Mail_mime(array('eol' => "\n", 'text_charset' => 'UTF-8', 'html_charset' => 'UTF-8'));
//array('eol' => "\n"));
//I2CE::raiseError($html);
$mime->setHTMLBody($html);
$mime->setTXTBody($body);
foreach ($attachments as $attachment) {
$mime->addAttachment($attachment['data'], $attachment['type'], $attachment['name'], false);
}
$body = $mime->get();
$headers = $mime->headers($headers);
//adapted from http://pear.php.net/manual/en/package.mail.mail-mime.example.php#10541
// $boundary = array();
// preg_match("/boundary=\"(.[^\"]*)\"/e", $headers["Content-Type"], $boundary);
// //echo "<pre>"; var_dump($headers); var_dump($boundary); die();
// $boundary = $boundary[1];
// $boundaryNew = uniqid() ;
// $headers["Content-Type"] = preg_replace('/boundary="(.[^"]*)"/', 'boundary="' . $boundaryNew . '"', $headers["Content-Type"]);
// $body = preg_replace("/^\-\-" . $boundary . "/s", "--" . $boundaryNew, $body);
// $body = preg_replace("/" . $boundary . "--$/s", $boundaryNew . "--", $body);
// $body = preg_replace("/" . $boundary . "--(\s*)--" . $boundary . "/s", $boundary . "--$1--" . $boundaryNew, $body);
// // Workaround, because "\r" breaks the e-mails (possibly a problem with Pear::Mail_Mime and Postfix)
// foreach($headers as $key=>$header) {
// $headers[$key] = str_replace("\r\n", "\n", $header);
// }
}
$result = self::$pear_mailer->send($recipients, $headers, $body);
if (I2CE::pearError($result, "Could not send message via pear mailer")) {
return false;
}
} catch (Exception $e) {
I2CE::raiseError("Could not send message via pear mailer:\n" . $e->getMessage());
return false;
}
return true;
}
/**
* Build the data tree for the given list of fields
* and limits. This is called by I2CE_List::buildDataTree.
* See that for more details
* @see I2CE_List::buildDataTree
* @param array $fields The fields to build the tree
* @param array $forms The selectable forms
* @param array $displayed The displayed forms for the tree
* @param array $limits The list of limits for each form.
* @param array $orders The order fields for each given form
* @param int $show_hidden 0=non-hidden, 1=All, 2=hidden only. Defaults to 0
* @return array The ordered list of all entries in the tree.
*/
public function buildDataTree($fields, $forms, $displayed, $limits, $orders, $show_hidden = 0)
{
$prev_form = false;
$form_aliases = array();
$selects = array();
$displays = array();
$all_orders = array();
$formObjs = array();
$skip_links = array();
$skip_link_fields = array();
$this->preserve_ids = true;
foreach ($fields as $formfield) {
list($form, $link_field) = $formfield;
$cachedForm = new I2CE_CachedForm($form);
$cachedForm->generateCachedTable(false);
unset($cachedForm);
$alias_form = $form . ($link_field ? "+{$link_field}" : "");
if (array_key_exists($alias_form, $limits)) {
$limit = $limits[$alias_form];
} elseif (array_key_exists($form, $limits)) {
$limit = $limits[$form];
} else {
$limit = array();
}
self::addFormIdToLimit($form, $limit);
$limit = I2CE_List::showHiddenLimit($limit, $show_hidden);
$disp_fields = I2CE_List::getDisplayFields($form);
$field_list = $disp_fields;
$field_list[] = 'id';
if ($link_field && !in_array($link_field, $field_list)) {
$field_list[] = $link_field;
}
if (array_key_exists($form, $orders)) {
$order = $orders[$form];
} else {
$order = I2CE_List::getSortFields($form);
}
$sort_list = array();
if (array_key_exists($form, $displayed) && $displayed[$form]) {
$displays[$alias_form]['form'] = $form;
$displays[$alias_form]['link_field'] = $link_field;
foreach ($disp_fields as $disp) {
$displays[$alias_form]['fields'][$disp] = "{$alias_form}+{$disp}";
}
foreach ($order as $i => $ord) {
if (!is_string($ord)) {
unset($order[$i]);
continue;
}
if ($ord[0] == '-') {
$field = substr($ord, 1);
$all_orders[] = "`{$alias_form}`.`{$field}` DESC";
} else {
$field = $ord;
$all_orders[] = "`{$alias_form}`.`{$field}` ASC";
}
if (!in_array($field, $field_list)) {
$sort_list[] = $field;
}
}
} else {
$skip_link_fields[$alias_form] = $link_field;
}
if (!array_key_exists($form, $formObjs)) {
$formObjs[$form] = I2CE_FormFactory::instance()->createContainer($form);
if (!$formObjs[$form] instanceof I2CE_Form) {
I2CE::raiseError("Could not instantiate {$form}");
return array();
}
}
$where = false;
if (is_array($limit) && count($limit) > 0) {
$where = $formObjs[$form]->generateWhereClause($limit, array("I2CE_FormStorage_cached", "getSQLField"));
}
$query = $this->getRequiredFieldsQuery($form, array_merge($field_list, $sort_list), null, false, array("I2CE_FormStorage_cached", "getSQLField"));
if (is_array($prev_form) && array_key_exists('form', $prev_form)) {
if ($prev_form['form'] == $form) {
$froms[$alias_form] = " LEFT JOIN ";
} else {
$froms[$alias_form] = " LEFT JOIN ";
}
} else {
$froms[$alias_form] = "";
}
$froms[$alias_form] .= "({$query}" . ($where ? " WHERE " . $where : "") . ") AS `{$alias_form}`";
if ($link_field) {
$join_ons = array();
$ff = $formObjs[$form]->getField($link_field);
if ($ff instanceof I2CE_FormField_MAPPED) {
foreach ($ff->getSelectableForms() as $link_form) {
if (array_key_exists($link_form, $form_aliases)) {
foreach ($form_aliases[$link_form] as $link_alias) {
$join_ons[] = "`{$alias_form}`.`{$link_field}` = `{$link_alias}`.`id`";
if (!array_key_exists($link_alias, $displays) && array_key_exists($link_alias, $skip_link_fields)) {
$skip_links[$alias_form . '+' . $link_field] = $link_alias . '+' . $skip_link_fields[$link_alias];
}
}
}
}
} else {
I2CE::raiseMessage("Not sure what to link for {$form} {$link_field} so guessing.");
$join_ons[] = "`{$alias_form}`.`{$link_field}` = `" . $prev_form['alias'] . "`.`id`";
}
if (count($join_ons) > 0) {
$froms[$alias_form] .= " ON (" . implode(' OR ', $join_ons) . ") ";
} else {
I2CE::raiseError("Don't know how to join on {$form} {$link_field}");
return array();
}
}
foreach ($field_list as $field) {
$selects[] = "`{$alias_form}`.`{$field}` AS `{$alias_form}+{$field}`";
}
$prev_form = array('form' => $form, 'alias' => $alias_form);
$form_aliases[$form][] = $alias_form;
}
$or_wheres = array();
foreach ($forms as $selectable) {
if (array_key_exists($selectable, $form_aliases)) {
foreach ($form_aliases[$selectable] as $required_form) {
$or_wheres[] = "`{$required_form}`.`id` IS NOT NULL";
}
}
}
$join_query = "SELECT " . implode(',', $selects) . " FROM " . implode('', $froms) . (count($or_wheres) > 0 ? " WHERE " . implode(' OR ', $or_wheres) : "") . (count($all_orders) > 0 ? " ORDER BY " . implode(',', $all_orders) : "");
//I2CE::raiseMessage( $join_query );
$res = $this->db->query($join_query);
if (I2CE::pearError($res, "Bad query -- {$join_query}")) {
return array();
}
$prev_ids = array();
$results = array();
$phonebook = array();
$display_string = array();
foreach ($displays as $fix_link_alias => &$fix_link_disp_data) {
if (array_key_exists('link_field', $fix_link_disp_data) && $fix_link_disp_data['link_field'] != '') {
$full_link_field = strtolower($fix_link_alias . "+" . $fix_link_disp_data['link_field']);
while (array_key_exists($full_link_field, $skip_links)) {
$full_link_field = $skip_links[$full_link_field];
}
$fix_link_disp_data['link_field'] = $full_link_field;
}
}
$display_copy = $displays;
while ($data = $res->fetchRow()) {
unset($prev_disp);
foreach ($displays as $alias => $disp_data) {
$id_field = strtolower($alias . "+id");
if ($data->{$id_field} == null || array_key_exists($data->{$id_field}, $phonebook)) {
continue;
}
$curr = array();
$add_this = false;
if (in_array($disp_data['form'], $forms)) {
$curr['value'] = $data->{$id_field};
$add_this = true;
}
if (!$add_this) {
$check_ok = false;
foreach ($display_copy as $alias_copy => $disp_data_copy) {
if ($alias_copy == $alias) {
$check_ok = true;
continue;
}
if (!$check_ok) {
continue;
}
if (array_key_exists('link_field', $disp_data_copy) && $disp_data_copy['link_field'] != '') {
$link_field_copy = $disp_data_copy['link_field'];
if ($data->{$id_field} == $data->{$link_field_copy}) {
$add_this = true;
break;
}
}
}
}
if (!$add_this) {
continue;
}
if (!array_key_exists($disp_data['form'], $display_string)) {
$display_string[$disp_data['form']] = I2CE_List::getDisplayString($disp_data['form']);
}
$disp_array = array();
$disp_str = $display_string[$disp_data['form']];
$disp_str_arr = explode("%s", $disp_str);
$fo = $formObjs[$disp_data['form']];
$disp_count = 0;
foreach ($disp_data['fields'] as $field => $dbfield) {
$disp_count++;
if ($field == $disp_data['link_field']) {
// Don't include the data from the link field since it will already
// be in the tree above it.
if ($disp_count == 1) {
unset($disp_str_arr[$disp_count]);
} else {
unset($disp_str_arr[$disp_count - 1]);
}
continue;
}
$dbfield = strtolower($dbfield);
$fieldObj = $fo->getField($field);
if (!$fieldObj instanceof I2CE_FormField) {
I2CE::raiseError("Could not get field {$field}");
continue;
}
if (isset($data->{$dbfield})) {
$fieldObj->setFromDB($data->{$dbfield});
$disp_array[$field] = $fieldObj->getDisplayValue();
} else {
$disp_array[$field] = null;
}
}
$disp_str = implode('%s', $disp_str_arr);
$display = vsprintf($disp_str, $disp_array);
//$display = $data->$disp_data['fields']['name'];
$curr['display'] = $display;
$phonebook[$data->{$id_field}] =& $curr;
if ($disp_data['link_field'] != '') {
$link_field = $disp_data['link_field'];
if (array_key_exists($data->{$link_field}, $phonebook)) {
$add_to =& $phonebook[$data->{$link_field}];
if (!array_key_exists('children', $phonebook[$data->{$link_field}])) {
$phonebook[$data->{$link_field}]['children'] = array();
}
$phonebook[$data->{$link_field}]['children'][] =& $curr;
} else {
I2CE::raiseMessage("Couldn't find {$link_field} " . $data->{$link_field} . " in phonebook");
}
} else {
$results[] =& $curr;
}
unset($curr);
}
}
$this->preserve_ids = false;
return $results;
}
/**
* Store the history for a form
* I2CE_Form $form
*
*/
public function storeHistory($form)
{
if (!$form instanceof I2CE_Form) {
I2CE::raiseError("Not a form");
return false;
}
if ($form->getId() == '0' || $form->getId() == '') {
I2CE::raiseError("Invalid form id");
return false;
}
$db = MDB2::singleton();
if (!$this->store_stmt) {
$this->store_stmt = $db->prepare("INSERT INTO form_history (formid,history,date,version) VALUES (?,?,NOW(),1)", array('text', 'blob'), MDB2_PREPARE_MANIP);
if (I2CE::pearError($this->store_stmt, "Could prepare store statment")) {
return false;
}
}
$form->populateHistory();
$history = $form->getHistory(true);
if (!is_array($history) || count($history) == 0) {
I2CE::raiseError("No history");
return false;
}
$this->recursiveEncode64($history);
if (version_compare(PHP_VERSION, '5.3.0') >= 0) {
$history = json_encode($history, JSON_FORCE_OBJECT);
} else {
$history = json_encode($history);
}
$formID = $form->getFormID();
if (I2CE::pearError($this->store_stmt->execute(array($formID, $history)), "Could not store form data for {$formID}")) {
return false;
}
return true;
}
/**
* Evaluate a given relationship function on the given form data
* @param string $funciton
* @param array $formData of I2CE_Form as returend by the @getFormsSatisfyingRelationship()
* @param boolean $displayValue. Defaults to true, in which case it returns the display value of the function. If false, it returns the DB value
* @returns string
*/
public function evaluateFunction($function, $formData, $displayValue = true)
{
//this DOES NOT handle dependent functions.
$details = $this->getFunctionDetails($function);
if (!is_array($details) || !array_key_exists($function, $details) || !is_array($details[$function])) {
return '';
}
$details = $details[$function];
$tmp_table = "`tmp_rel_func_eval_" . $this->relationship . '+' . $function . "`";
$fieldDefs = array();
$fieldVals = array();
foreach ($details['required_fields'] as $formfield) {
list($namedform, $field) = array_pad(explode("+", $formfield, 2), 2, '');
if (!$namedform || !$field || !$formData) {
I2CE::raiseError("{$formfield} not found in form data");
return '';
}
if ($namedform == $this->relationship) {
$namedform = 'primary_form';
}
if (!array_key_exists($namedform, $formData) || !$formData[$namedform] instanceof I2CE_Form) {
I2CE::raiseError("{$namedform} is not in form data");
return '';
}
$fieldObj = $formData[$namedform]->getField($field);
if (!$fieldObj instanceof I2CE_FormField) {
I2CE::raiseError("Invalid field {$field} for named form {$namedform}");
return '';
}
$fieldDefs[] = '`' . $formfield . "` " . $fieldObj->getDBType();
$fieldVals[] = "'" . addslashes($fieldObj->getDBValue()) . "'";
}
$qrys = array("CREATE TEMPORARY TABLE IF NOT EXISTS {$tmp_table} (" . implode(",", $fieldDefs) . ") ", "TRUNCATE {$tmp_table}", "INSERT INTO {$tmp_table} VALUES( " . implode(",", $fieldVals) . ")");
$db = MDB2::singleton();
foreach ($qrys as $qry) {
$res = $db->exec($qry);
if (I2CE::pearError($res, "Could not evaluate {$qry}")) {
return '';
}
}
$qry = 'SELECT ' . $details['qry'] . ' AS res FROM ' . $tmp_table;
$res = $db->queryRow($qry);
if (I2CE::pearError($res, "Could not evaluate {$qry}")) {
return '';
}
//$db->exec("DROP TABLE $tmp_table");
$details['field']->setFromDB($res->res);
if ($displayValue) {
return $details['field']->getDisplayValue();
} else {
return $details['field']->getDBValue();
}
}
protected function update_keys()
{
$db = MDB2::singleton();
$qry = 'TRUNCATE TABLE csd_cache';
if (I2CE::pearError($db->exec($qry), "Error truncatingo csd_cache")) {
return false;
}
$qry = 'ALTER TABLE csd_cache ADD UNIQUE KEY `unique` (`record`,`relationship`,`transform`)';
if (I2CE::pearError($db->exec($qry), "Error adding unique key to csd_cache")) {
return false;
}
return true;
}
/**
* Process results
* @param array $results_data an array of results. indices are 'results' and MDB2 Buffered result and 'num_results' the
* number of results. (these values may be false on failure)
* @param DOMNode $contentNode. Default to null a node to append the results onto
* @return boolean
*/
protected function processResults($results_data, $contentNode = null)
{
if (!$results_data['num_results']) {
$this->noData($contentNode);
return true;
}
$too_much = 1000;
I2CE::getConfig()->setIfIsSet($too_much, "/modules/CustomReports/displays/CrossTab/too_much");
if ($results_data['num_results'] > $too_much) {
$this->tooMuchData($contentNode);
return true;
}
$this->template->addHeaderLink("customReports_display_Default.css");
$tableContainerNode = $this->template->appendFileByNode("customReports_display_CrossTab_table.html", "div", $contentNode);
if (!$tableContainerNode instanceof DOMNode) {
I2CE::raiseError("Could not add table container template");
return false;
}
$reportBodyNode = $this->template->getElementById('report_body', $tableContainerNode);
if (!$reportBodyNode instanceof DOMNode) {
I2CE::raiseError("Could not find report body");
return false;
}
$this->data = array();
$left = array();
$top = array();
$this->headers = array();
$displayOrder = '';
if (array_key_exists('display_order', $this->defaultOptions) && $this->defaultOptions['display_order']) {
$displayOrder = $this->defaultOptions['display_order'];
} else {
$this->config->setIfIsSet($displayOrder, 'display_order');
}
$displayOrderArr = explode(',', $displayOrder);
foreach ($this->defaultOptions['displayFieldsTab'] as $reportfield => $side) {
if (!in_array($reportfield, $displayOrderArr)) {
$displayOrderArr[] = $reportfield;
}
if ($side == self::CROSSTAB_LEFT) {
$left[] = $reportfield;
} elseif ($side == self::CROSSTAB_TOP) {
$top[] = $reportfield;
}
}
$left = array_intersect($displayOrderArr, $left);
$top = array_intersect($displayOrderArr, $top);
$total = 'Total';
I2CE::getConfig()->setIfIsSet($total, "/modules/CustomReports/text/headers/count");
$num_results = 0;
while ($row = $results_data['results']->fetchRow()) {
if (I2CE::pearError($row, "Error getting cross tab results: ")) {
$results_data['results']->free();
return false;
}
$mapped_row = $this->mapResults($row);
$left_arr = array();
foreach ($left as $left_field) {
$left_arr[$left_field] = $mapped_row[$left_field];
$this->headers[$left_field] = true;
}
$left_key = json_encode($left_arr);
if (!array_key_exists($left_key, $this->data)) {
$this->data[$left_key] = array();
$num_results++;
}
if (!array_key_exists('total', $this->data)) {
$this->data['total'] = array();
$num_results++;
}
$data_row =& $this->data[$left_key];
foreach ($top as $top_field) {
if (!array_key_exists($top_field, $data_row)) {
$data_row[$top_field] = array();
}
if (!array_key_exists($top_field, $this->headers)) {
$this->headers[$top_field] = array();
}
$this->headers[$top_field][$mapped_row[$top_field]] = true;
if (!array_key_exists($mapped_row[$top_field], $data_row[$top_field])) {
$data_row[$top_field][$mapped_row[$top_field]] = $mapped_row['total'];
} else {
$data_row[$top_field][$mapped_row[$top_field]] += $mapped_row['total'];
}
if (!array_key_exists($total, $data_row[$top_field])) {
$data_row[$top_field][$total] = $mapped_row['total'];
} else {
$data_row[$top_field][$total] += $mapped_row['total'];
}
if (!array_key_exists($top_field, $this->data['total'])) {
$this->data['total'][$top_field] = array();
}
if (!array_key_exists($mapped_row[$top_field], $this->data['total'][$top_field])) {
$this->data['total'][$top_field][$mapped_row[$top_field]] = $mapped_row['total'];
} else {
$this->data['total'][$top_field][$mapped_row[$top_field]] += $mapped_row['total'];
}
if (!array_key_exists($total, $this->data['total'][$top_field])) {
$this->data['total'][$top_field][$total] = $mapped_row['total'];
} else {
$this->data['total'][$top_field][$total] += $mapped_row['total'];
}
}
}
$results_data['results']->free();
// Now add the total columns to the end of the header.
foreach ($top as $top_field) {
ksort($this->headers[$top_field]);
$this->headers[$top_field][$total] = true;
}
$this->doCrossTabHeaders($tableContainerNode);
$this->template->setDisplayDataImmediate('num_results', $num_results, $tableContainerNode);
$row_num = 1;
foreach ($this->data as $left => $rights) {
if ($left == 'total') {
continue;
}
$row = array_merge(json_decode($left, true), $rights);
if (!$this->processResultRow($row, $row_num, $reportBodyNode)) {
return false;
}
$row_num++;
}
$row = array_merge(array('total' => $total), $this->data['total']);
if (!$this->processResultRow($row, $row_num, $reportBodyNode)) {
return false;
}
return true;
}
/**
* Checks to make sure there is an index on the date column last_entry table named date. If it does
* not exist it adds it.
*
* Note: this should seem to need be in the last entry mdoule, but this existed before it was created. probably msotyl dead code at this point as noone should be less than 3.1
*/
protected function createDateIndexOnLastEntry()
{
$db = MDB2::singleton();
$qry = "SELECT null FROM information_schema.statistics WHERE\ntable_schema = '{$db->database_name}'\nand table_name = 'last_entry'\nand index_name = 'date'";
$result = $db->query($qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$qry}")) {
return false;
}
if ($result->numRows() > 0) {
//the index has already been created.
return true;
}
//the index has not been created.
ini_set('max_execution_time', 6000);
I2CE::raiseError("Creating index 'date' on last_entry");
$qry = "CREATE INDEX date ON last_entry (date)";
$result = $db->query($qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$qry}")) {
return false;
}
return true;
}
/**
* update value of each instance of a given form field by a sql function call
* @param I2CE_FormField $form_field
* @param array $where Array of where data
* @param string $set_sql sql used to update the field
*/
public function globalFieldUpdateBySQL($form_field, $where, $set_sql)
{
if (!$form_field instanceof I2CE_FormField) {
I2CE::raiseError("Not passed form_field");
return false;
}
$formObj = $form_field->getContainer();
if (!$formObj instanceof I2CE_Form) {
I2CE::raiseError("No form as a container for the form field");
return false;
}
$form = $formObj->getName();
if (!$set_sql) {
I2CE::raiseError("No SQL provided to update {$form}+{$field}");
return false;
}
$sub_qry = $this->getRequiredFieldsQuery($form, array($form_field->getName()));
$where_qry = $formObj->generateWhereClause($where);
if (!$where_qry) {
I2CE::raiseError("Could not gernerate where clasue for {$form} by\n" . print_r($where, true));
return false;
}
$qry = "UPDATE config_alt JOIN ({$sub_qry}) AS data " . "ON parent = CONCAT( '/I2CE/formsData/forms/{$form}/', `{$form}+id` , '/fields' ) " . "SET value = {$set_sql} " . " WHERE (name = '" . $form_field->getName() . "' AND ({$where_qry}))";
//I2CE::raiseError("Updating by $qry");
$res = $this->db->exec($qry);
I2CE::getConfig()->clearCache(false);
//since we did a write to the DB, need to clear cache
if (I2CE::pearError("Cannot update by:\n{$qry}", $res)) {
return false;
}
return true;
}
/**
* Clear the all keys/values associated with this storage
* @return boolean
*/
public function clear()
{
return !I2CE::pearError($this->db->exec("DELETE FROM " . $this->name), "Unable to clear DB magic data table {$this->name}");
}
protected function addConfigPathIndex()
{
// AVG(length(path)) count(distinct(substr(path,1,120))) / count(distinct(path)) * 100
// 72.7333 97.9721
// AVG(length(path)) count(distinct(substr(path,1,130))) / count(distinct(path)) * 100
// 72.7333 99.9036
// AVG(length(path)) count(distinct(substr(path,1,140))) / count(distinct(path)) * 100
// 72.7333 99.9825
$db = MDB2::singleton();
$check_qry = "SELECT * FROM information_schema.statistics WHERE table_schema = '" . addslashes($db->database_name) . "'" . " AND table_name = 'config' and index_name = 'path' ";
$result = $db->query($check_qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$check_qry}")) {
return false;
}
if ($result->numRows() > 0) {
//the index has already been created.
I2CE::raiseError("Index on config.path has already been created");
return true;
}
//create the index
ini_set('max_execution_time', 60000);
I2CE::raiseError("Creating index 'path' on config.path");
$qry = "ALTER TABLE config ADD INDEX `path` ( `path` ( 130 ) )";
$result = $db->query($qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$qry}")) {
return false;
}
return true;
}
/**
* Returns mysql results set for given time
* @returns false on failire
*/
public function getCache($ids = false, $last_modified = -1)
{
$qry = "SELECT xml_entry from `csd_cache` WHERE " . "`relationship` = '" . mysql_real_escape_string($this->relationship) . "' " . "and `transform` = '" . mysql_real_escape_string($this->transform) . "' ";
if (is_numeric($last_modified) && $last_modified >= 0) {
$qry .= "and `last_modified` >= '" . date('Y-m-d H:i:s', $last_modified) . "'";
} else {
if (is_string($last_modified) && $last_modified) {
$mod_time = date("Y-m-d H:i:s", strtotime($last_modified));
$qry .= "and `last_modified` >= '" . $mod_time . "'";
}
}
if (is_array($ids)) {
$ids = array_map(function ($id) {
return "'" . mysql_real_escape_string($id) . "'";
}, $ids);
if (count($ids) > 0) {
$qry .= 'and record IN (' . implode(' , ', $ids) . ') ';
}
}
$db = MDB2::singleton();
$results = $db->query($qry);
if (I2CE::pearError($results, "Cannot access cached results for {$this->relationship} {$this->transform}")) {
return false;
} else {
return $results;
}
}
/**
* Explode and execute a
* @param string $sql a nuch of sql queries
* @param boolean $transact defaults to true meaning that the whole script is executed in a transaction. If a string, it is the name of a savepoint to rollback to/release
* @param MDB2 connection $db
* @param string $delimiter. Defaults to ';'. Needs to be exactly one character
* @returns boolean true on sucess or false
*/
public static function explodeAndExecuteSQLQueries($sql, $db, $transact = true, $delimiter = ';')
{
if (!is_string($sql)) {
I2CE::raiseError("Invalid SQL script");
return false;
}
I2CE::longExecution();
if ($db->supports('transactions')) {
if ($transact === true) {
$db->beginTransaction();
I2CE::raiseError("Beginning transaction");
} else {
if (is_string($transact)) {
if ($db->in_transaction) {
$db->query('SAVEPOINT $transact');
} else {
//we aren't in a transaction, so don't use the savepoint
$transact = true;
$db->beginTransaction();
I2CE::raiseError("Beginning transaction");
}
}
}
}
$len = strlen($sql);
$in_string_single = false;
$in_string_double = false;
$in_string_back = false;
$in_comment = false;
$in_comment_ml = false;
$beg_qry = 0;
$t_qry = '';
for ($i = 0; $i < $len; $i++) {
switch ($sql[$i]) {
case "\n":
if ($in_comment) {
$in_comment = false;
$beg_qry = $i + 1;
//beginning of query is next line
}
if ($in_comment || $in_comment_ml || $in_string_single !== false || $in_string_double || $in_string_back) {
continue;
}
if (preg_match('/^DELIMITER (.)$/', trim(substr($sql, $beg_qry, $i - $beg_qry)), $matches)) {
$delimiter = $matches[1];
$beg_qry = $i + 1;
//beginning of query is next line
}
continue;
case "-":
if ($in_comment || $in_comment_ml || $in_string_single !== false || $in_string_double || $in_string_back) {
continue;
}
if ($i > 0 && $sql[$i - 1] == '-') {
$in_comment = true;
$t_qry .= $t_qry . trim(substr($sql, $beg_qry, $i - 1 - $beg_qry));
$beg_qry = -1;
}
break;
case "#":
if ($in_comment || $in_comment_ml || $in_string_single !== false || $in_string_double || $in_string_back) {
continue;
}
$in_comment = true;
$t_qry .= $t_qry . trim(substr($sql, $beg_qry, $i - $beg_qry));
$beg_qry = $i + 1;
break;
case '*':
if ($in_comment || $in_string_single !== false || $in_string_double || $in_string_back) {
continue;
}
if ($in_comment_ml) {
if ($i + 2 < $len && $sql[$i + 1] == '/') {
$in_comment_ml = false;
$beg_qry = $i + 2;
}
} else {
if ($i > 0 && $sql[$i - 1] == '/') {
$in_comment_ml = true;
$t_qry .= $t_qry . trim(substr($sql, $beg_qry, $i - 1 - $beg_qry));
$beg_qry = $i + 1;
}
}
continue;
break;
case "`":
if ($in_comment || $in_comment_ml) {
continue;
}
if ($in_string_back) {
$in_escape_slash = false;
$in_escape_back = false;
if ($i > 0) {
if ($sql[$i - 1] == '\\') {
$in_escape_slash = true;
//can't have a \ apearing in a table or field name
}
$j = $i - 1;
while ($sql[$j] == '`') {
$in_escape_back = !$in_escape_back;
$j--;
}
}
if (!$in_escape_slash && !$in_escape_back) {
$in_string_back = false;
}
} else {
if ($in_string_single === FALSE && !$in_string_double) {
$in_string_back = true;
}
}
continue;
break;
case '"':
if ($in_comment || $in_comment_ml) {
continue;
}
if ($in_string_double) {
$in_escape_slash = false;
$in_escape_double = false;
if ($i > 9) {
$j = $i - 1;
while ($sql[$j] == '\\') {
$in_escape_slash = !$in_escape_slash;
$j--;
}
$j = $i - 1;
while ($sql[$j] == '"') {
$in_escape_double = !$in_escape_double;
$j--;
}
}
if (!$in_escape_slash && !$in_escape_double) {
$in_string_double = false;
}
} else {
if ($in_string_single === false && !$in_string_back) {
$in_string_double = true;
}
}
continue;
break;
case "'":
if ($in_comment || $in_comment_ml) {
continue;
}
if ($in_string_single !== false) {
$in_escape_slash = false;
if ($i > 0) {
$j = $i - 1;
while ($j > $in_string_single) {
if ($sql[$j] == '\\') {
$in_escape_slash = !$in_escape_slash;
$j--;
} else {
break;
}
}
}
if (!$in_escape_slash) {
$in_string_single = false;
}
} else {
if (!$in_string_back && !$in_string_double) {
$in_string_single = $i;
}
}
continue;
break;
case $delimiter:
if ($in_string_single !== false || $in_string_double || $in_string_back || $in_comment || $in_comment_ml) {
continue;
}
$qry = trim($t_qry . trim(substr($sql, $beg_qry, $i - $beg_qry)));
$beg_qry = $i + 1;
$t_qry = '';
if (!$qry) {
continue;
}
$result = $db->query($qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$qry}")) {
if ($db->in_transaction) {
if ($transact === true) {
I2CE::raiseError("Rolling Back");
$db->rollback();
} else {
if (is_string($transact)) {
I2CE::raiseError("Rolling Back to savepoint {$transact}");
$db->query("ROLLBACK TO SAVEPOINT {$transact}");
}
}
}
if ($transact) {
I2CE::getConfig()->clearCache();
}
return false;
}
continue;
break;
default:
break;
}
}
//now make sure we pick up the last query
$qry = trim($t_qry . trim(substr($sql, $beg_qry)));
if ($qry) {
$result = $db->query($qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$qry}")) {
if ($db->in_transaction) {
if ($transact === true) {
I2CE::raiseError("Rolling Back");
$db->rollback();
} else {
if (is_string($transact)) {
I2CE::raiseError("Rolling Back to savepoint {$transact}");
$db->query("ROLLBACK TO SAVEPOINT {$transact}");
}
}
}
if ($transact) {
I2CE::getConfig()->clearCache();
}
return false;
}
}
//we are done
if ($db->in_transaction) {
if ($transact === true) {
I2CE::raiseError("Commiting");
return $db->commit() == MDB2_OK;
}
if (is_string($transact)) {
$db->query("RELEASE SAVEPOINT {$transact}");
I2CE::raiseError("Released savepoint {$transact}");
}
return true;
} else {
return true;
}
}
protected function updateUserDetailTable()
{
$db = MDB2::singleton();
$qry_show = "SHOW COLUMNS FROM user LIKE 'default_password'";
$qry_alter = "ALTER TABLE user ADD COLUMN `default_password` int(11) default 1";
$results = $db->query($qry_show);
if (I2CE::pearError($results, "Error getting columns from user table: on {$qry_show}")) {
return false;
}
if ($results->numRows() != 1) {
//the logic is vague: I could not find another logic to confirm the field is found
$qry_alter_results = $db->query($qry_alter);
if (I2CE::pearError($qry_alter_results, "Error getting columns from user table: on {$qry_alter}")) {
return false;
}
}
return true;
}
/**
* Return the list of scheduled course for the given course id.
* @param integer $course_id. Defaults to zero meaning we get all courses
* @param boolean $flat. defaults to false
* @return array the keys are the id of the scheduled course, the values are the string "$start_date -- $end_date"
*/
public static function getScheduledCourses($course_id = 0, $flat = false)
{
if ($course_id > 0) {
$flat = true;
}
$values = array();
foreach (array('start_date', 'end_date') as $field) {
$data = I2CE_FormField::getFormFieldIdAndType('scheduled_training_course', $field);
if (!is_array($data)) {
I2CE::raiseError("Could not available courses b/c could not find field {$field} in form scheduled_training_course");
return array();
}
$values[] = $data['id'];
}
$query = "SELECT le_start_date.record AS id, le_start_date.date_value AS start_date, le_end_date.date_value AS end_date, r.parent AS parent ";
$query .= "FROM last_entry le_start_date ";
$query .= "JOIN last_entry le_end_date ON le_start_date.record = le_end_date.record ";
$query .= "JOIN record r ON le_start_date.record = r.id ";
$query .= "WHERE le_start_date.form_field = ? ";
$query .= "AND le_end_date.form_field = ? ";
if ($course_id > 0) {
$query .= "AND r.parent = ? ";
$values[] = $course_id;
}
$query .= "ORDER BY le_start_date.date_value DESC, le_end_date.date_value ASC";
$db = MDB2::singleton();
$sth = $db->prepare($query, array('integer', 'integer'), MDB2_PREPARE_RESULT);
if (I2CE::pearError($sth, "Could not setup statement to get available courses")) {
return array();
}
$results = $sth->execute($values);
if (I2CE::pearError($results, "Could not get available courses")) {
return array();
}
$scheduled_courses = array();
while ($result =& $results->fetchRow()) {
$start_date = I2CE_Date::fromDB($result->start_date);
$end_date = I2CE_Date::fromDB($result->end_date);
if ($flat) {
$scheduled_courses[$result->id] = $start_date->displayDate() . " - " . $end_date->displayDate();
} else {
$scheduled_courses[$result->parent][$result->id] = $start_date->displayDate() . " - " . $end_date->displayDate();
}
}
return $scheduled_courses;
}
protected function updateContactTypes()
{
$db = MDB2::singleton();
$factory = I2CE_FormFactory::instance();
if ($db->supports('transactions')) {
$db->beginTransaction();
}
$contactFormId = I2CE_Form::getFormId("contact");
if ($contactFormId == 0) {
I2CE::raiseError("Unable to get contact form id");
if ($db->in_transaction) {
$db->rollback();
}
return false;
}
$adminUser = I2CE_User::findUser('role', 'admin', false);
if (!$adminUser instanceof I2CE_User) {
I2CE::raiseError("Cannot find an administrative user");
if ($db->in_transaction) {
$db->rollback();
}
return false;
}
$changes = array(4 => 5, 3 => 4);
// TYPE_OTHER = 3 => TYPE_OTHER = 4
// TYPE_FACILITY = 4 => TYPE_FACILITY = 5
$qry = $db->prepare('SELECT id from record where form = ?', array('integer'), MDB2_PREPARE_RESULT);
if (I2CE::pearError($qry, "Error preping select records")) {
if ($db->in_transaction) {
$db->rollback();
}
return false;
}
$results = $qry->execute($contactFormId);
if (I2CE::pearError($results, "Error getting records")) {
if ($db->in_transaction) {
$db->rollback();
}
return false;
}
while ($row = $results->fetchRow()) {
$contact = $factory->createContainer('contact' . '|' . $row->id);
if (!$contact instanceof iHRIS_Contact) {
I2CE::raiseError("Unable to create contact with id " . $row->id);
if ($db->in_transaction) {
$db->rollback();
}
return false;
}
$contact->populate();
foreach ($changes as $old => $new) {
if ($contact->contact_type == $old) {
I2CE::raiseError("Changing contact type {$old} to {$new} for record " . $row->id);
$contact->contact_type = $new;
if (!$contact->save($adminUser)) {
I2CE::raiseError("Unable to save record " . $row->id);
if ($db->in_transaction) {
$db->rollback();
}
return false;
}
$contact->cleanup();
continue 2;
}
}
$contact->cleanup();
}
if ($db->in_transaction) {
return $db->commit() == MDB2_OK;
} else {
return true;
}
}
/**
* setup of the queries used to create and populate the cached table
* @returns boolean. True on success, false on error
*/
protected function createCacheTable()
{
I2CE::raiseError("(Re)Creating cached table schema for {$this->form} as it either does not exist or is out of date");
$timeConfig = I2CE::getConfig()->traverse("/modules/CachedForms/times/generation/{$this->form}", false, false);
if ($timeConfig instanceof I2CE_MagicDataNode) {
$timeConfig->erase();
}
$createFields = array('`id` varchar(255) NOT NULL', 'PRIMARY KEY (`id`)');
$createFields[] = '`parent` varchar(255) default "|" ';
$createFields[] = 'INDEX (`parent`)';
$createFields[] = '`last_modified` datetime default \'1900-01-01 00:00:00\'';
$createFields[] = '`created` datetime default \'0000-00-00 00:00:00\'';
$createFields[] = 'INDEX (`last_modified`)';
$field_defs = array();
foreach ($this->formObj as $field => $fieldObj) {
if (!$fieldObj->isInDB()) {
continue;
}
$createFields[] = '`' . $field . '` ' . $fieldObj->getDBType();
if ($fieldObj instanceof I2CE_FormField_MAPPED) {
$createFields[] = 'INDEX (`' . $field . '`) ';
}
}
$createQuery = "CREATE TABLE " . $this->table_name . " ( " . implode(',', $createFields) . ") ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin";
I2CE::raiseError("Creating table for {$this->form} as:\n{$createQuery}");
$db = MDB2::singleton();
$result = $db->query($createQuery);
if (I2CE::pearError($result, "Cannot create cached table for {$this->form}:\n{$createQuery}")) {
return false;
}
return true;
}
protected function getCreateField($form, $field, $name)
{
$result = $this->get_field_def->execute(array(I2CE_CachedForm::getCachedTableName($form, false), $field));
if (I2CE::pearError($result, "Unable to get field defintion for " . I2CE_CachedForm::getCachedTableName($form, false) . ".{$field}")) {
return false;
}
if ($result->numRows() != 1) {
I2CE::raiseError("Unexpected number of rows " . $result->numRows() . " when determining field defintion for {$form}.{$field}");
return false;
}
$result = $result->fetchRow();
$field = "`{$name}` " . $result->column_type . ' ';
if ($result->character_set_name) {
$field .= ' CHARACTER SET ' . $result->character_set_name . ' ';
}
if (isset($result->collation_name)) {
$field .= ' COLLATE ' . $result->collation_name;
}
return array('type' => $result->column_type, 'field' => $field);
}
/**
* Construct a query (to be used as a sub-select) to view the fields of the given form. It always will return the id of the form as well
* @param string $form
* @param mixed $fields. Either a string, the field, or an array of string, the fields.
* @param mixed $id. Defaults to null. If non-null it is the id that we wish to limit to.
* @param boolean $parent. Defaults to false. If true, we include the parent id as a referenced field
* If it is scalar and non-boolean, it is consider to be the ID of the parent, and then we get all forms with parent the given id.
* @param callback $field_refernece_callback. A callback function whose first arguement is the form, the second arguements
* is the field and which returns the way the field value should be references as a field. If the callback is null (the default) then
* the reference used is "$form+$field"
* @param integer $mod_time. Defaults to -1. If non-negative, we only list the requested fields for an id if at least one of them has a modification
* time greater than or equal to $mod_time. If the form storage has no way of tracking modifucation time, all entries are listed.
* @param mixed $user The user id of the user to limit the results to so it only returns results limited to that user. This can be a single value or an array
* @returns string the query or false on failed.
*/
public function getRequiredFieldsQuery($form, $fields, $id = null, $parent = false, $field_reference_callback = null, $mod_time = -1, $user = false)
{
if ($fields === null) {
$fields = array();
}
if (is_string($fields)) {
$fields = array($fields);
}
if (!is_array($fields)) {
I2CE::raiseError("Invalid fields");
return false;
}
if (!($options = $this->getStorageOptions($form))) {
I2CE::raiseError("No valid multi_flat storage options for {$form}");
return false;
}
if (!is_array($this->databases) || count($this->databases) == 0) {
I2CE::raiseError("No databases specified");
return false;
}
$formObj = $this->getFormObj($form);
if (!$formObj instanceof I2CE_Form) {
I2CE::raiseError("Could not instantiate form {$form}");
return false;
}
if (!in_array($form, $this->componentized_forms)) {
I2CE::raiseError("Form {$form} is not specified as being componentized. Valid componentized forms:" . print_r($this->componentized_forms, true));
return false;
}
$table = '';
$options->setIfIsSet($table, 'table');
$table = trim($table);
if (strlen($table) == 0) {
if (array_key_exists('table_prefix', $this->global_options) && is_string($this->global_options['table_prefix']) && strlen(trim($this->global_options['table_prefix'])) > 0) {
$table_prefix = trim($this->global_options['table_prefix']);
} else {
$table_prefix = 'hippo_';
}
$table = $table_prefix . $form;
}
if (strlen($table) == 0) {
I2CE::raiseError("No table specified for {$form}");
return false;
}
$id_ref = false;
if ($field_reference_callback != null) {
if (!is_string($id_ref = call_user_func($field_reference_callback, $form, 'id'))) {
I2CE::raiseError("Invalid field reference callback function");
return false;
}
} else {
$id_ref = "`{$form}+id`";
}
$id_qry = 'id';
if ($options->is_parent('id')) {
$id_qry = $this->getFieldData($options->id);
}
$form_prepended = true;
$options->setIfIsSet($form_prepended, "id/form_prepended");
$unions = array();
$p_componentized = false;
if (is_scalar($id)) {
if (($pos = strrpos($id, '@')) === false) {
//get the last @ sign
I2CE::raiseError("No component specified in id: {$id} for form {$form}");
return false;
}
$component = substr($id, $pos + 1);
$id_no_comp = substr($id, 0, $pos);
if (strlen($component) == 0) {
I2CE::raiseError("zero length component specified in id:{$id}");
return false;
}
if (!array_key_exists($component, $this->databases)) {
I2CE::raiseError("Component {$component} is not associated to a database in id {$id} for form {$form}");
return false;
}
$databases = array($component => $this->databases[$component]);
$componentParentForms = array_intersect(I2CE_Form::getAllowedParentForms($form), $this->componentized_forms);
} else {
if (!is_bool($parent) && is_scalar($parent)) {
list($p_form, $p_id) = explode('|', $parent, 2);
if (strlen($p_form) == 0) {
I2CE::raiseError("sNo parent form given in {$parent}");
return false;
}
if (strlen($p_id) == 0) {
I2CE::raiseError("No parent id given in {$parent}");
return false;
}
if (in_array($p_form, $this->componentized_forms)) {
$p_componentized = true;
if (($pos = strrpos($p_id, '@')) === false) {
//get the last @ sign
I2CE::raiseError("No component specified in parent id: {$parent}");
return false;
}
$component = substr($p_id, $pos + 1);
$p_id_no_comp = substr($p_id, 0, $pos);
if (strlen($component) == 0) {
I2CE::raiseError("zero length component specified in parent id:{$parent}");
return false;
}
if (!array_key_exists($component, $this->databases)) {
I2CE::raiseError("Component {$component} is not associated to a database in parent id: {$parent}");
return false;
}
$databases = array($component => $this->databases[$component]);
$componentParentForms = array($p_form);
} else {
$componentParentForms = array_intersect(I2CE_Form::getAllowedParentForms($form), $this->componentized_forms);
}
} else {
$databases = $this->databases;
$componentParentForms = array_intersect(I2CE_Form::getAllowedParentForms($form), $this->componentized_forms);
}
}
//now verify that the databases and associated table are indeed present:
$db = MDB2::singleton();
foreach ($databases as $component => $database) {
$check_qry = "SELECT null FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" . addslashes($database) . "'" . " AND TABLE_NAME = '" . addslashes($table) . "'";
$result = $db->query($check_qry);
if (I2CE::pearError($result, "Cannot execute query:\n{$check_qry}")) {
return false;
}
if ($result->numRows() > 0) {
//the table exists.
continue;
}
unset($databases[$component]);
}
if (count($databases) == 0) {
I2CE::raiseError("No databases defined for multi-flat formstorage on table {$table}");
return false;
}
foreach ($databases as $component => $database) {
$select_list = array();
if ($form_prepended) {
$select_list[] = "CONCAT( SUBSTRING(" . $id_qry . "," . (strlen($form) + 2) . "),'@','" . mysql_real_escape_string($component) . "') AS {$id_ref}";
} else {
$select_list[] = "CONCAT( " . $id_qry . ",'@','" . mysql_real_escape_string($component) . "') AS {$id_ref}";
}
foreach ($formObj as $field => $fieldObj) {
if (!in_array($field, $fields)) {
continue;
}
if (!$fieldObj->isInDB()) {
continue;
}
if ($options->is_parent("fields/{$field}")) {
$data = $options->traverse("fields/{$field}");
if ($data->is_scalar('enabled') && !$data->enabled) {
continue;
}
$f_qry = $this->getFieldData($data, $database, $table);
} else {
if (!$this->hasColumn($field, $database, $table)) {
$f_qry = ' NULL ';
} else {
$f_qry = "`{$field}`";
//default to the field name
}
}
if ($fieldObj instanceof I2CE_FormField_MAPPED) {
$comp_map_forms = array_intersect($fieldObj->getSelectableForms(), $this->componentized_forms);
$f_qry = $fieldObj->getSQLComponentization($f_qry, $comp_map_forms, $component);
}
$f_ref = false;
if ($field_reference_callback != null) {
if (!is_string($f_ref = call_user_func($field_reference_callback, $form, $field))) {
I2CE::raiseError("Invalid field reference callback function");
return false;
}
} else {
$f_ref = "`{$form}+{$field}`";
}
$select_list[] = "{$f_qry} AS {$f_ref}";
}
$wheres = array();
if (is_array($mod_time) && array_key_exists('mod_time', $mod_time)) {
$mod_time = $mod_time['mod_time'];
}
$get_mod_time = is_scalar($mod_time) && $mod_time >= 0 || in_array('last_modified', $fields);
if ($get_mod_time) {
if ($options->is_scalar('last_modified/enabled') && !$options->last_modified->enabled) {
$mod_qry = "NULL";
} else {
if ($options->is_scalar('last_modified/col') && $options->last_modified->col) {
if ($this->hasColumn($options->last_modified->col, $database, $table)) {
$mod_qry = $options->last_modified->col;
} else {
$mod_qry = 'NULL';
}
} else {
if ($options->is_scalar('last_modified/function') && $options->last_modified->function) {
$mod_qry = $options->last_modified->function;
} else {
if ($this->hasColumn('last_modified', $database, $table)) {
$mod_qry = " last_modified ";
} else {
//perhaps we are using a hippo_XXX table which does not have a last_modified column
$mod_qry = 'NULL';
}
}
}
}
if ($field_reference_callback !== null) {
if (!is_string($mod_ref = call_user_func($field_reference_callback, $form, 'last_modified'))) {
I2CE::raiseError("Invalid parent reference callback function:\nlast_modified --> {$mod_ref}");
return false;
}
} else {
$mod_ref = '`' . $form . '+last_modified`';
}
$select_list[] = "{$mod_qry} AS {$mod_ref}";
if (is_scalar($mod_time) && $mod_time >= 0) {
$wheres[] = "({$mod_qry} IS NULL OR {$mod_qry} > FROM_UNIXTIME(" . $mod_time . "))";
}
}
if ($parent !== false || in_array($parent, $fields)) {
$p_qry = false;
$p_ref = false;
if ($field_reference_callback != null) {
if (!is_string($p_ref = call_user_func($field_reference_callback, $form, 'parent'))) {
I2CE::raiseError("Invalid field reference callback function");
return false;
}
} else {
$p_ref = "`{$form}+parent`";
}
if (!$options->is_scalar('parent/enabled') || $options->parent->enabled) {
if ($options->is_parent('parent')) {
$p_qry = $this->getFieldData($options->parent);
}
if ($p_qry === false) {
$p_qry = 'parent';
}
} else {
$p_qry = '0';
}
$select_list[] = I2CE_List::componentizeQuery($p_qry, $componentParentForms, $component) . " AS {$p_ref}";
if (!is_bool($parent) && is_scalar($parent)) {
if (!$p_componentized) {
$wheres[] = " ( " . I2CE_List::componentizeQuery($p_qry, $componentParentForms, $component) . " = '" . mysql_real_escape_string($parent) . "' ) ";
} else {
$wheres[] = " ( {$p_qry} = '" . mysql_real_escape_string($p_form . '|' . $p_id_no_comp) . "') ";
}
}
}
//want either select id as `form+id`, name as `form+name` from table
//or select id as `form+id`, name as `form+name` from (select * from table where id=5)
//select substr(my_id,1,4) as `form+id`, surname as `form+name` from (select * from table where id=5)
//or with a function..
//select id as `form+id`, substr(surname,1,3) as `form+name` from table
//select id as `form+id`, substr(surname,1,3) as `form+name` from (select * from table where id=5)
//select substr(my_id,1,4) as `form+id`, surname as `form+name` from table wher `form+id`
$qry = 'SELECT ' . implode(',', $select_list) . " FROM `{$database}`.`{$table}`";
if (is_scalar($id)) {
if ($form_prepended) {
$wheres[] = " ({$id_qry} ='" . mysql_real_escape_string($form . '|' . $id_no_comp) . "') ";
} else {
$wheres[] = " ({$id_qry} ='" . mysql_real_escape_string($id_no_comp) . "') ";
}
}
if (count($wheres) > 0) {
$qry .= ' WHERE (' . implode("AND", $wheres) . ")";
}
$unions[] = $qry;
}
if (count($databases) > 1) {
foreach ($unions as &$union) {
$union = '(' . $union . ')';
}
//I2CE::raiseError("QRY:" . implode('UNION' ,$unions));
return implode('UNION', $unions);
} else {
reset($unions);
//I2CE::raiseError("QRY:" . current($unions));
return current($unions);
}
}
/**
* Get/prepare the prepared statement for the given field obj
* @param I2CE_FormField $fieldObj
* @returns mixed. false om failure. a mdb2 preapred statement object on success
*/
protected function getFieldSave($fieldObj)
{
$formName = $fieldObj->getContainer()->getName();
$fieldName = $fieldObj->getName();
if (!array_key_exists($formName, $this->fieldSaves)) {
$this->fieldSaves[$formName] = array();
}
if (!array_key_exists($fieldName, $this->fieldSaves[$formName])) {
$cols = $this->getSaveColumns($fieldObj->getContainer());
$table = $this->getTable($formName);
if (!$table || !array_key_exists($fieldName, $cols)) {
$this->fieldSaves[$formName][$fieldName] = false;
return false;
}
$stmt = "UPDATE {$table} SET `{$cols[$fieldName]}` = ? WHERE `{$cols['id']}` = ?";
$prepStmt = $this->db->prepare($stmt, array($fieldObj->getMDB2Type(), 'text'), MDB2_PREPARE_MANIP);
if (I2CE::pearError($prepStmt, "Error preparing save statemnt for " . $fieldObj->getName() . "\n" . $stmt)) {
$prepStmt = false;
}
$this->fieldSaves[$formName][$fieldName] = $prepStmt;
}
return $this->fieldSaves[$formName][$fieldName];
}
public static function markProcessed($id, $relationship, $hash)
{
if (!is_string($relationship) || !is_string($hash) || !is_string($id) || !$id || !$hash || !$relationship) {
$this->raiseError("bad parameters");
return null;
}
if (self::$mark_stmt === null) {
$db = mdb2::singleton();
self::$mark_stmt = $db->prepare("INSERT INTO form_relationship_importer (id,relationship,hash) VALUES (?, ?, ?)", array('text', 'text', 'text'), MDB2_PREPARE_MANIP);
if (I2CE::pearError(self::$mark_stmt, "Could not prepare mark statement")) {
return null;
}
}
if (!self::$mark_stmt) {
return null;
}
return !I2CE::pearError(self::$mark_stmt->execute(array($id, $relationship, $hash)), "Could not mark processed");
}
/**
* @param boolean $check_restart defaults to true in which case if the results are paginated and the offeset is more than the number of results, we restart it setting the page to 1
* @returns mixed false on failure on succes an array. at index 'results' and MDB2 buffered result object at index 'num_results' the
* number of results that would be found without the limit
*/
protected function getResults($check_restart = true)
{
if (array_key_exists('limit_paginated', $this->defaultOptions) && $this->defaultOptions['limit_paginated']) {
if (!array_key_exists('limit_page', $this->defaultOptions) || !(is_integer($this->defaultOptions['limit_page']) || ctype_digit($this->defaultOptions['limit_page'])) || (int) $this->defaultOptions['limit_page'] < 1) {
$this->defaultOptions['limit_page'] = 1;
}
if (!array_key_exists('limit_per_page', $this->defaultOptions) || !(is_integer($this->defaultOptions['limit_per_page']) || ctype_digit($this->defaultOptions['limit_per_page'])) || (int) $this->defaultOptions['limit_per_page'] < 1) {
//we don't have a valid 'limit_per_page'
$this->defaultOptions['limit_per_page'] = 100;
//default to 100
}
$this->defaultOptions['limit_page'] = (int) $this->defaultOptions['limit_page'];
$this->defaultOptions['limit_per_page'] = (int) $this->defaultOptions['limit_per_page'];
$limit_offset = ($this->defaultOptions['limit_page'] - 1) * $this->defaultOptions['limit_per_page'];
$limit_amount = $this->defaultOptions['limit_per_page'];
} else {
$limit_offset = $this->defaultOptions['limit_offset'];
//$limit_amount = $this->defaultOptions['limit_per_page'];
$limit_amount = false;
if ($limit_offset !== false || $limit_offset !== 'false') {
if (!(is_integer($limit_offset) || ctype_digit($limit_offset)) || $limit_offset < 0) {
$limit_offset = 0;
}
}
if ($limit_amount === null || empty($limit_amount) || $limit_amount === 'false') {
$limit_amount = false;
}
if ($limit_amount !== false) {
if (!(is_integer($limit_amount) || ctype_digit($limit_amount)) || $limit_amount < 1) {
$limit_amount = 100;
}
}
}
if ($this->defaultOptions['sort_order'] == 'none') {
$sort_order = array();
} else {
$sort_order = explode(',', $this->defaultOptions['sort_order'] . '');
foreach ($sort_order as $i => $field) {
if ($field == 'none') {
unset($sort_order[$i]);
}
}
}
$sort_order = $this->validateSortFields($sort_order, array_keys($this->fieldData));
$db = MDB2::singleton();
if ($limit_offset !== false && $limit_amount !== false) {
$limit = " LIMIT {$limit_amount} OFFSET {$limit_offset} ";
$this->row_start = $limit_offset;
$this->row_amount = $limit_amount;
} else {
$limit = '';
$this->row_start = false;
$this->row_amount = false;
}
$sorts = array();
foreach ($sort_order as $formfield) {
if (strlen($formfield) == 0) {
continue;
}
if ($formfield[0] == '-') {
$sort_field = substr($formfield, 1);
$sort_postfix = ' DESC';
} else {
$sort_field = $formfield;
$sort_postfix = '';
}
$sorts[$sort_field] = '`' . $sort_field . '`' . $sort_postfix;
}
$order_by = '';
if (count($sorts) > 0) {
$order_by = ' ORDER BY ' . implode(',', $sorts);
}
if (array_key_exists('nested_limits', $this->defaultOptions)) {
$where = $this->processWhere($this->defaultOptions['nested_limits']);
} else {
$where = '';
}
//I2CE::raiseMessage("where is $where " . print_r( $this->defaultOptions['nested_limits'], true) );
$fields = array();
foreach ($this->fieldData as $field => $data) {
if (array_key_exists('db_field', $data)) {
if (is_array($data['db_field'])) {
foreach ($data['db_field'] as $db_field) {
$fields[] = "{$db_field}";
}
} else {
$fields[] = $data['db_field'] . ' AS `' . $field . '`';
}
} else {
$fields[] = "entry.{$field} AS `{$field}`";
}
}
if (count($fields) == 0) {
I2CE::raiseError("No fields to display for UserStatistics report.");
return false;
}
//$qry = "SELECT SQL_CALC_FOUND_ROWS e.*,form.name AS form_name, field.name AS field_name,u.username,IF(e.who = 0, 'I2CE Admin', CONCAT_WS(' ', u.firstname, u.lastname)) AS user FROM entry e LEFT JOIN user u ON u.id = e.who JOIN form_field ff ON e.form_field = ff.id JOIN form ON ff.form = form.id JOIN field ON ff.field = field.id $where $order_by $limit";
$qry = "SELECT SQL_CALC_FOUND_ROWS " . implode(', ', $fields) . " FROM entry LEFT JOIN user ON user.id = entry.who JOIN form_field ff ON entry.form_field = ff.id JOIN form ON ff.form = form.id JOIN field ON ff.field = field.id JOIN record ON record.id = entry.record {$where} {$order_by} {$limit}";
I2CE::raiseMessage("query is: {$qry}");
$res = $db->query($qry);
if (I2CE::pearError($res, "Could not get results")) {
return false;
}
$num_rows = $db->queryRow("SELECT FOUND_ROWS() AS num_rows");
if (I2CE::pearError($num_rows, "Could not get total number of results")) {
$num_rows = false;
} else {
$num_rows = (int) $num_rows->num_rows;
}
return array('results' => $res, 'num_results' => $num_rows, 'has_total' => false);
}
