/**
* Loads an array of typed objects of a given class (same class as current object by default)
* which inherit from this class.
* @access private
*
* @param string $class [optional] class name
* @param string $key [optional] key name in db to use as key of array
* @param array $additionalVars [optional] array of string additional key names to add as vars to object
* @return array of objects of the same class (empty array if no objects)
*/
function & loadTrueObjects( $class = null, $key = "", $additionalVars = null ) {
if ( $additionalVars === null ) {
$additionalVars = array();
}
$objectsArray = array();
$resultsArray = $this->_db->loadAssocList( $key );
if ( is_array($resultsArray) ) {
if ( $class == null ) {
$class = get_class($this);
}
foreach ( $resultsArray as $k => $value ) {
$objectsArray[$k] = new $class( $this->_db );
// mosBindArrayToObject( $value, $objectsArray[$k], null, null, false );
$objectsArray[$k]->bind( $value, null, null, false );
foreach ( $additionalVars as $index ) {
if ( array_key_exists( $index, $value ) ) {
$objectsArray[$k]->$index = $value[$index];
}
}
}
}
return $objectsArray;
}
function _fixCBmandatoryDb( $dryRun ) {
cbimport( 'cb.sql.upgrader' );
$this->_sqlUpgrader = new CBSQLupgrader( $this->_db, $this->_silentWhenOK );
$this->_sqlUpgrader->setDryRun( $dryRun );
$sql = 'SELECT * FROM `#__comprofiler_tabs` ORDER BY `tabid`'; // `tabid`, `pluginclass`
$this->_db->setQuery( $sql );
$tabs = $this->_db->loadObjectList( 'tabid' );
if ( $this->_db->getErrorNum() ) {
$this->_sqlUpgrader->_setError( 'Tabs selection query error: ' . $this->_db->getErrorMsg() );
return false;
}
$sql = 'SELECT `fieldid`, `tabid` FROM `#__comprofiler_fields` ORDER BY `tabid`';
$this->_db->setQuery( $sql );
$fields = $this->_db->loadObjectList( 'fieldid' );
if ( $this->_db->getErrorNum() ) {
$this->_sqlUpgrader->_setError( sprintf( 'Fields selection query error: ' . $this->_db->getErrorMsg() ), $sql );
return false;
}
// 1) count and index tabs by core pluginclass and tabid holding array of fieldsids, so we can delete empty duplicate core tabs:
$coreTabs = array();
foreach ( $tabs as $t ) {
if ( in_array( $t->pluginclass, $this->_tabsShouldBe ) ) {
$coreTabs[$t->pluginclass][$t->tabid] = array();
}
}
// 2) group fieldids by tabid
// 3) add fields to $coreTabs[pluginclass][tabid][fieldid]
$tabsFields = array();
foreach ( $fields as $f ) {
if ( isset( $tabs[$f->tabid] ) ) {
$tabsFields[$f->tabid][$f->fieldid] = $f->fieldid;
if ( $tabs[$f->tabid]->pluginclass != '' ) {
$coreTabs[$tabs[$f->tabid]->pluginclass][$f->tabid][$f->fieldid] = $f->fieldid;
}
}
}
// 4) delete empty duplicate core tabs according to $coreTabs[pluginclass][tabid][fieldid]
foreach ( $coreTabs as /* $pluginClass => */ $tabIds ) {
if ( count( $tabIds ) > 1 ) {
// there is more than one core tab for this core plugin class ! We need to decide which to keep:
$tabidCandidatesToKeep = array();
// 1st priority: keep tabs that are enabled AND have fields:
foreach ( $tabIds as $tId => $tFields ) {
if ( ( $tabs[$tId]->enabled == 1 ) && ( count( $tFields ) > 0 ) ) {
$tabidCandidatesToKeep[] = $tId;
}
}
// 2nd priority: keep tabs that have fields:
if ( count( $tabidCandidatesToKeep ) == 0 ) {
foreach ( $tabIds as $tId => $tFields ) {
if ( count( $tFields ) > 0 ) {
$tabidCandidatesToKeep[] = $tId;
}
}
}
// 3rd priority: keep tabs that are enabled:
if ( count( $tabidCandidatesToKeep ) == 0 ) {
foreach ( $tabIds as $tId => $tFields ) {
if ( $tabs[$tId]->enabled == 1 ) {
$tabidCandidatesToKeep[] = $tId;
}
}
}
// 4th priority: keep tab with the correct id:
if ( count( $tabidCandidatesToKeep ) == 0 ) {
foreach ( $tabIds as $tId => $tFields ) {
if ( isset( $this->_tabsShouldBe[$tId] ) && ( $tabs[$tId]->pluginclass == $this->_tabsShouldBe[$tId] ) ) {
$tabidCandidatesToKeep[] = $tId;
}
}
}
// 5th priority: well no more priorities to think of ! : just take first one !
if ( count( $tabidCandidatesToKeep ) == 0 ) {
foreach ( $tabIds as $tId => $tFields ) {
$tabidCandidatesToKeep[] = $tId;
break;
}
}
// ok, by now we got at least one tab to keep: let's see which, in case we got more than one:
if ( count( $tabidCandidatesToKeep ) == 1 ) {
$tabToKeep = (int) $tabidCandidatesToKeep[0];
} else {
$tabToKeep = null;
// a) has the right core id:
foreach ( $tabidCandidatesToKeep as $tId ) {
if ( isset( $this->_tabsShouldBe[$tId] ) && ( $tabs[$tId]->pluginclass == $this->_tabsShouldBe[$tId] ) ) {
$tabToKeep = $tId;
break;
}
}
// b) first with fields:
if ( $tabToKeep === null ) {
foreach ( $tabidCandidatesToKeep as $tId ) {
if ( count( $coreTabs[$tabs[$tId]->pluginclass][$tId] ) > 0 ) {
$tabToKeep = $tId;
break;
}
}
}
// c) first enabled one:
if ( $tabToKeep === null ) {
foreach ( $tabidCandidatesToKeep as $tId ) {
if ( $tabs[$tId]->enabled == 1 ) {
$tabToKeep = $tId;
break;
}
}
}
// d) first one:
if ( $tabToKeep === null ) {
foreach ( $tabidCandidatesToKeep as $tId ) {
$tabToKeep = $tId;
break;
}
}
}
if ( $tabToKeep !== null ) {
$tabsToDelete = array_diff( array_keys( $tabIds ), array( $tabToKeep ) );
// first reassign the fields of the tabs to delete:
$fieldsToReassign = array();
foreach ( $tabIds as $tId => $tFields ) {
if ( ( $tId != $tabToKeep ) && count( $tFields ) > 0 ) {
$fieldsToReassign = array_merge( $fieldsToReassign, $tFields );
}
}
if ( count( $fieldsToReassign ) > 0 ) {
cbArrayToInts( $fieldsToReassign );
$sql = 'UPDATE `#__comprofiler_fields` SET `tabid` = ' . (int) $tabToKeep . ' WHERE `fieldid` IN (' . implode( ',', $fieldsToReassign ) . ')';
if ( ! $this->_sqlUpgrader->_doQuery( $sql ) ) {
$this->_sqlUpgrader->_setError( 'Failed changing fieldids ' . implode( ',', $fieldsToReassign ) . ' from duplicates of kept core tabid: ' . $tabToKeep . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
} else {
$this->_sqlUpgrader->_setLog( 'Changed fieldids ' . implode( ',', $fieldsToReassign ) . ' from duplicates of kept core tabid: ' . $tabToKeep, $sql, 'change' );
}
}
cbArrayToInts( $tabsToDelete );
// c) remove duplicate core tabs:
$sql = 'DELETE FROM `#__comprofiler_tabs` WHERE `tabid` IN (' . implode( ',', $tabsToDelete ) . ')';
if ( ! $this->_sqlUpgrader->_doQuery( $sql ) ) {
$this->_sqlUpgrader->_setError( 'Failed deleting duplicates tabids ' . implode( ',', $tabsToDelete ) . ' of the used core tabid: ' . $tabToKeep . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
} else {
$this->_sqlUpgrader->_setLog( 'Deleted duplicate core tabs tabids ' . implode( ',', $tabsToDelete ) . ' of the used core tabid: ' . $tabToKeep, $sql, 'change' );
}
}
}
}
// 5) refetch tabs with now free space at reserved positions:
$sql = 'SELECT * FROM `#__comprofiler_tabs` ORDER BY `tabid`'; // `tabid`, `pluginclass`
$this->_db->setQuery( $sql );
$tabs = $this->_db->loadObjectList( 'tabid' );
if ( $this->_db->getErrorNum() ) {
$this->_sqlUpgrader->_setError( 'Tabs 2nd selection query error: ' . $this->_db->getErrorMsg(), $sql );
return false;
}
unset( $coreTabs ); // this one is now invalid, and not needed anymore
$sql = 'SELECT `fieldid`, `tabid` FROM `#__comprofiler_fields` ORDER BY `tabid`';
$this->_db->setQuery( $sql );
$fields = $this->_db->loadObjectList( 'fieldid' );
if ( $this->_db->getErrorNum() ) {
$this->_sqlUpgrader->_setError( 'Fields 3nd selection query error: ' . $this->_db->getErrorMsg(), $sql );
return false;
}
// group fieldids by tabid
$tabsFields = array();
foreach ( $fields as $f ) {
if ( isset( $tabs[$f->tabid] ) ) {
$tabsFields[$f->tabid][$f->fieldid] = $f->fieldid;
}
}
// 6) check tabs one by one, making room in reserved positions:
foreach ( $tabs as $t ) {
if ( isset( $this->_tabsShouldBe[$t->tabid] ) && ( $t->pluginclass == $this->_tabsShouldBe[$t->tabid] ) ) {
// ok, cool, tabid and plugin matches: no corrective action:
continue;
}
if ( isset( $this->_tabsShouldBe[$t->tabid] ) ) {
// not ok: tabid is taken by another tab: we need to relocate this tab at last position:
// a) insert same tab in another tabid
$oldTabId = $t->tabid;
if ( ! $dryRun ) {
$t->tabid = null;
if ( ! $this->_db->insertObject( '#__comprofiler_tabs', $t, 'tabid' ) ) {
$this->_sqlUpgrader->_setError( 'Failed moving (inserting) non-core tabid: ' . $oldTabId . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
}
$t->tabid = $this->_db->insertid();
} else {
$t->tabid = $t->tabid + 10000; // just to fake the insert
}
$this->_sqlUpgrader->_setLog( 'Inserted old tabid ' . $oldTabId . ' as new tabid ' . $t->tabid, ( $dryRun ? 'INSERT tabobject' : $this->_db->getQuery() ), 'change' );
// b) change fields' tabid:
if ( isset( $tabsFields[$oldTabId] ) && ( count( $tabsFields[$oldTabId] ) > 0 ) ) {
$sql = 'UPDATE `#__comprofiler_fields` SET `tabid` = ' . (int) $t->tabid . ' WHERE `tabid` = ' . (int) $oldTabId;
if ( ! $this->_sqlUpgrader->_doQuery( $sql ) ) {
$this->_sqlUpgrader->_setError( 'Failed changing fields from old non-core tab with core tabid: ' . $oldTabId . ' to new tabid: ' . $t->tabid . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
} else {
$this->_sqlUpgrader->_setLog( 'Changed fields from old non-core tab with core tabid: ' . $oldTabId . ' (that must be for ' . $this->_tabsShouldBe[$oldTabId] . ') to new tabid: ' . $t->tabid, $sql, 'change' );
}
}
// c) remove old tab:
$sql = 'DELETE FROM `#__comprofiler_tabs` WHERE tabid = ' . (int) $oldTabId;
if ( ! $this->_sqlUpgrader->_doQuery( $sql ) ) {
$this->_sqlUpgrader->_setError( 'Failed deleting old non-core tabid: ' . $oldTabId . ' which is already copied to new tabid: ' . $t->tabid . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
} else {
$this->_sqlUpgrader->_setLog( 'Deleted old non-core tabid: ' . $oldTabId . ' which is already copied to new tabid: ' . $t->tabid, $sql, 'change' );
}
}
}
// 7) refetch tabs with now free space at reserved positions as well as fields and recompute $tabFields:
$sql = 'SELECT * FROM `#__comprofiler_tabs` ORDER BY `tabid`'; // `tabid`, `pluginclass`
$this->_db->setQuery( $sql );
$tabs = $this->_db->loadObjectList( 'tabid' );
if ( $this->_db->getErrorNum() ) {
$this->_sqlUpgrader->_setError( 'Tabs 3rd selection query error: ' . $this->_db->getErrorMsg(), $sql );
return false;
}
$sql = 'SELECT `fieldid`, `tabid` FROM `#__comprofiler_fields` ORDER BY `tabid`';
$this->_db->setQuery( $sql );
$fields = $this->_db->loadObjectList( 'fieldid' );
if ( $this->_db->getErrorNum() ) {
$this->_sqlUpgrader->_setError( 'Fields 3nd selection query error: ' . $this->_db->getErrorMsg(), $sql );
return false;
}
// group fieldids by tabid
$tabsFields = array();
foreach ( $fields as $f ) {
if ( isset( $tabs[$f->tabid] ) ) {
$tabsFields[$f->tabid][$f->fieldid] = $f->fieldid;
}
}
// 8) check tabs one by one, moving tabs back to reserved positions if needed:
foreach ( $tabs as $t ) {
if ( isset( $this->_tabsShouldBe[$t->tabid] ) && ( $t->pluginclass == $this->_tabsShouldBe[$t->tabid] ) ) {
// ok, cool, tabid and plugin matches: no corrective action:
continue;
}
if ( ( ! isset( $this->_tabsShouldBe[$t->tabid] ) ) && in_array( $t->pluginclass, $this->_tabsShouldBe ) ) {
// ok we found a core CB tab which doesn't have the right id: the right id is now free, so just update the tab:
$newTabId = array_search( $t->pluginclass, $this->_tabsShouldBe );
if ( $newTabId !== false ) {
// a) move the core tab to the right tabid:
$sql = 'UPDATE `#__comprofiler_tabs` SET `tabid` = ' . (int) $newTabId . ' WHERE `tabid` = ' . (int) $t->tabid;
if ( ! $this->_sqlUpgrader->_doQuery( $sql ) ) {
$this->_sqlUpgrader->_setError( 'Failed moving core tab from old tabid: ' . $t->tabid . ' to new tabid: ' . $newTabId . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
} else {
$this->_sqlUpgrader->_setLog( 'Moved core tab from old tabid: ' . $t->tabid . ' to new tabid: ' . $newTabId, $sql, 'change' );
}
// b) change fields' tabid:
if ( isset( $tabsFields[$t->tabid] ) && ( count( $tabsFields[$t->tabid] ) > 0 ) ) {
$sql = 'UPDATE `#__comprofiler_fields` SET `tabid` = ' . (int) $newTabId . ' WHERE `tabid` = ' . (int) $t->tabid;
if ( ! $this->_sqlUpgrader->_doQuery( $sql ) ) {
$this->_sqlUpgrader->_setError( 'Failed changing fields from old core tabid: ' . $oldTabId . ' to new tabid: ' . $t->tabid . ' because of error:' . $this->_db->getErrorMsg(), $sql );
break;
} else {
$this->_sqlUpgrader->_setLog( 'Changed fields from old core tabid: ' . $oldTabId . ' to new tabid: ' . $t->tabid, $sql, 'change' );
}
}
}
}
}
// now missing core tabs will be inserted in the new 1.2 upgrader in next step.
return true;
}
/**
* gets statistics
*
* @param int $basketId Basket id for which payments have been done
* @param string $txnIdToNotCount (optional) txn_id of payment(s) to ignore in sum
* @return boolean true if could load
*/
public function getBasketPaidTotal( $basketId, $txnIdToNotCount = null ) {
$sql = "SELECT COUNT(*) AS count, SUM(mc_gross) AS total "
. "\n FROM #__cbsubs_payments "
. "\n WHERE payment_basket_id = " . (int) $basketId
. "\n AND payment_status = " . $this->_db->Quote( 'Completed' )
;
if ( $txnIdToNotCount ) {
$sql .= "\n AND txn_id <> " . $this->_db->Quote( $txnIdToNotCount );
}
$this->_db->setQuery( $sql );
return $this->_db->loadObject( $this );
}
/**
* Checks if all columns of a xml description of all tables of a database matches the database
*
* Warning: if ( $change && $strictlyColumns ) it will DROP not described columns !!!
* @access private
*
* @param string|array $tablesNames Name(s) of tables to dump
* @param boolean $withContent FALSE: only structure, TRUE: also content
* @return CBSimpleXMLElement
*/
function dumpTableToXml($tablesNames, $withContent = true)
{
$db = new CBSimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><database version="1" />');
foreach ((array) $tablesNames as $tableName) {
$table = $db->addChild('table');
$table->addAttribute('name', $tableName);
$table->addAttribute('class', '');
$table->addAttribute('strict', 'false');
$table->addAttribute('drop', 'never');
// Columns:
$allColumns = $this->getAllTableColumns($tableName);
$columns = $table->addChild('columns');
foreach ($allColumns as $colEntry) {
$colTypeUnsigned = explode(' ', $colEntry->Type);
if (count($colTypeUnsigned) == 1) {
$colTypeUnsigned[1] = null;
}
$column = $columns->addChild('column');
$column->addAttribute('name', $colEntry->Field);
$column->addAttribute('type', 'sql:' . $colTypeUnsigned[0]);
if ($colTypeUnsigned[1] === 'unsigned') {
$column->addAttribute('unsigned', $colTypeUnsigned[1] === 'unsigned' ? 'true' : 'false');
}
if ($colEntry->Null === 'YES') {
$column->addAttribute('null', $colEntry->Null === 'YES' ? 'true' : 'false');
}
if ($colEntry->Default !== null) {
if ($colEntry->Null === 'YES') {
$column->addAttribute('default', $colEntry->Default);
} else {
$defaultDefaultTypes = $this->defaultValuesOfTypes($this->mysqlToXmlsql('sql:' . $colTypeUnsigned[0]));
if (!in_array($colEntry->Default, $defaultDefaultTypes)) {
$column->addAttribute('default', $colEntry->Default);
}
}
}
if (strpos($colEntry->Extra, 'auto_increment') !== false) {
$tableStatus = $this->_db->getTableStatus($tableName);
if (isset($tableStatus[0]->Auto_increment)) {
$lastAuto_increment = $tableStatus[0]->Auto_increment;
} else {
$lastAuto_increment = '100';
}
$column->addAttribute('auto_increment', $lastAuto_increment);
}
}
// Indexes:
$indexes = $table->addChild('indexes');
$primaryIndex = null;
$allIndexes = $this->getAllTableIndexes($tableName);
foreach ($allIndexes as $indexName => $sequenceInIndexArray) {
$type = $sequenceInIndexArray[1]['type'];
$using = $sequenceInIndexArray[1]['using'];
$index = $indexes->addChild('index');
$index->addAttribute('name', $indexName);
if ($type != '') {
$index->addAttribute('type', $type);
}
if ($using != 'btree') {
$index->addAttribute('using', $using);
}
foreach ($sequenceInIndexArray as $indexAttributes) {
$column = $index->addChild('column');
$column->addAttribute('name', $indexAttributes['name']);
if ($indexAttributes['size']) {
$column->addAttribute('size', $indexAttributes['size']);
}
if ($indexAttributes['ordering'] != 'A') {
$column->addAttribute('ordering', $indexAttributes['ordering']);
}
}
if ($type == 'primary') {
$primaryIndex = $index;
}
}
// Content:
if ($withContent) {
$allRows = $this->loadRows($tableName, null, null, null);
if (count($allRows) > 0) {
$rows = $table->addChild('rows');
$primaryNames = null;
if ($primaryIndex !== null) {
foreach ($primaryIndex->children() as $column) {
if ($column->name() == 'column') {
$primaryNames[] = $column->attributes('name');
}
}
}
foreach ($allRows as $rowData) {
$row = $rows->addChild('row');
// missing primary key here:
$rowIndexName = array();
$rowIndexValue = array();
$rowIndexValueType = array();
foreach (get_object_vars($rowData) as $fieldDataName => $fieldDataValue) {
if ($fieldDataName[0] != '_') {
$typeColumn = $columns->getChildByNameAttributes('column', array('name' => $fieldDataName));
$fieldDataValueType = 'const:' . $this->mysqlToXmlsql($typeColumn->attributes('type'));
$field = $row->addChild('field');
if ($fieldDataValue === null) {
$fieldDataValue = 'NULL';
$fieldDataValueType = 'const:null';
}
$field->addAttribute('name', $fieldDataName);
$field->addAttribute('value', $fieldDataValue);
$field->addAttribute('valuetype', $fieldDataValueType);
if (in_array($fieldDataName, $primaryNames)) {
$field->addAttribute('strict', 'true');
$rowIndexName[] = $fieldDataName;
$rowIndexValue[] = $fieldDataValue;
$rowIndexValueType[] = $fieldDataValueType;
}
}
}
$row->addAttribute('index', implode(' ', $rowIndexName));
$row->addAttribute('value', implode(' ', $rowIndexValue));
$row->addAttribute('valuetype', implode(' ', $rowIndexValueType));
}
}
}
}
return $db;
}
/**
* Cleans the field value by type in a secure way for SQL
*
* @param mixed $fieldValue
* @param string $type const,sql,param : string,int,float,datetime,formula
* @param ParamsInterface $pluginParams
* @param CBdatabase|null $db
* @param array|null $extDataModels
* @return string|boolean STRING: sql-safe value, Quoted or type-casted to int or float, or FALSE in case of type error
*/
public static function sqlCleanQuote($fieldValue, $type, $pluginParams, &$db = null, $extDataModels = null)
{
if ($db === null) {
global $_CB_database;
$db =& $_CB_database;
}
$typeArray = explode(':', $type, 3);
if (count($typeArray) < 2) {
$typeArray = array('const', $type);
}
if ($typeArray[0] == 'param') {
$fieldValue = $pluginParams->get($fieldValue);
} elseif (in_array($typeArray[0], array('request', 'get', 'post', 'cookie', 'cbcookie', 'session', 'server', 'env'))) {
$fieldValue = self::_globalConv($typeArray[0], $fieldValue);
} elseif ($typeArray[0] == 'ext') {
if (isset($typeArray[2]) && $extDataModels && isset($extDataModels[$typeArray[2]])) {
if (is_object($extDataModels[$typeArray[2]])) {
if (isset($extDataModels[$typeArray[2]]->{$fieldValue})) {
$fieldValue = $extDataModels[$typeArray[2]]->{$fieldValue};
}
} elseif (is_array($extDataModels[$typeArray[2]])) {
if (isset($extDataModels[$typeArray[2]][$fieldValue])) {
$fieldValue = $extDataModels[$typeArray[2]][$fieldValue];
}
} else {
$fieldValue = $extDataModels[$typeArray[2]];
}
} else {
trigger_error('SQLXML::sqlCleanQuote: ERROR: ext valuetype "' . htmlspecialchars($type) . '" has not been setExternalDataTypeValues.', E_USER_NOTICE);
}
// } elseif ( ( $typeArray[0] == 'const' ) || ( $cnt_valtypeArray[0] == 'sql' ) {
// $fieldValue = $fieldValue;
}
switch ($typeArray[1]) {
case 'int':
$value = (int) $fieldValue;
break;
case 'float':
$value = (double) $fieldValue;
break;
case 'formula':
$value = $fieldValue;
break;
case 'datetime':
if (preg_match('/[0-9]{4}-[01][0-9]-[0-3][0-9] [0-2][0-9](:[0-5][0-9]){2}/', $fieldValue)) {
$value = $db->Quote($fieldValue);
} else {
$value = "''";
}
break;
case 'date':
if (preg_match('/[0-9]{4}-[01][0-9]-[0-3][0-9]/', $fieldValue)) {
$value = $db->Quote($fieldValue);
} else {
$value = "''";
}
break;
case 'time':
if (preg_match('/-?[0-9]{1,3}(:[0-5][0-9]){2}/', $fieldValue)) {
$value = $db->Quote($fieldValue);
} else {
$value = "''";
}
break;
case 'string':
$value = $db->Quote($fieldValue);
break;
case 'null':
$value = 'NULL';
break;
default:
trigger_error('SQLXML::sqlCleanQuote: ERROR_UNKNOWN_TYPE: ' . htmlspecialchars($type), E_USER_NOTICE);
$value = $db->Quote($fieldValue);
// false;
break;
}
return $value;
}
