1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 <?php
/**
***********************************************************************************************
* Class manages the list configuration
*
* @copyright 2004-2016 The Admidio Team
* @see http://www.admidio.org/
* @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License v2.0 only
***********************************************************************************************
*/
/**
* @class ListConfiguration
* This class creates a list configuration object. With this object it's possible
* to manage the configuration in the database. You can easily create new lists,
* add new columns or remove columns.
*
* Beside the methods of the parent class there are the following additional methods:
*
* readColumns() - Daten der zugehoerigen Spalten einlesen und in Objekten speichern
* addColumn($number, $field, $sort = "", $condition = "")
* - fuegt eine neue Spalte dem Spaltenarray hinzu
* deleteColumn($number, $all = false)
* - entfernt die entsprechende Spalte aus der Konfiguration
* countColumns() - Anzahl der Spalten der Liste zurueckgeben
* getSQL($roleIds, $memberStatus = 0)
* - gibt das passende SQL-Statement zu der Liste zurueck
*/
class ListConfiguration extends TableLists
{
protected $columns = array(); // Array with all Listenspaltenobjekte
/**
* Constructor that will create an object to handle the configuration of lists.
* @param \Database $database Object of the class Database. This should be the default global object @b $gDb.
* @param int $lstId The id of the recordset that should be loaded. If id isn't set than an empty object of the table is created.
*/
public function __construct(&$database, $lstId = 0)
{
parent::__construct($database, $lstId);
if($lstId > 0)
{
$this->readColumns();
}
}
/**
* Add new column to column array
* @param int $number
* @param int|string $field
* @param string $sort
* @param string $filter
* @return bool
*/
public function addColumn($number, $field, $sort = '', $filter = '')
{
// can join max. 61 tables
// Passed parameters must be set carefully
if($number === 0 || $field === '' || count($this->columns) >= 57)
{
return false;
}
// If column doesn't exist create object
if(!array_key_exists($number, $this->columns))
{
$this->columns[$number] = new TableAccess($this->db, TBL_LIST_COLUMNS, 'lsc');
$this->columns[$number]->setValue('lsc_lsf_id', $this->getValue('lst_id'));
}
// Assign content of column
if(is_numeric($field))
{
$this->columns[$number]->setValue('lsc_usf_id', $field);
$this->columns[$number]->setValue('lsc_special_field', '');
}
else
{
$this->columns[$number]->setValue('lsc_usf_id', '');
$this->columns[$number]->setValue('lsc_special_field', $field);
}
$this->columns[$number]->setValue('lsc_number', $number);
$this->columns[$number]->setValue('lsc_sort', $sort);
$this->columns[$number]->setValue('lsc_filter', $filter);
return true;
}
public function clear()
{
$this->columns = array();
parent::clear();
}
/**
* Return count of columns
* @return int
*/
public function countColumns()
{
return count($this->columns);
}
/**
* Delete pointed columns out of configuration
* @param int $number
* @param bool $all Define all columns to be deleted
* @return bool
*/
public function deleteColumn($number, $all = false)
{
if($number > $this->countColumns()) {
return false;
}
if($all)
{
// Delete all columns starting with number
for($newColumnNumber = $this->countColumns(); $newColumnNumber >= $number; --$newColumnNumber)
{
$this->columns[$newColumnNumber]->delete();
array_pop($this->columns);
}
}
else
{
// only 1 columns is deleted and following are going 1 step up
for($newColumnNumber = $number, $max = $this->countColumns(); $newColumnNumber < $max; ++$newColumnNumber)
{
$newColumn = $this->columns[$newColumnNumber];
$oldColumn = $this->columns[$newColumnNumber + 1];
$newColumn->setValue('lsc_usf_id', $oldColumn->getValue('lsc_usf_id'));
$newColumn->setValue('lsc_special_field', $oldColumn->getValue('lsc_special_field'));
$newColumn->setValue('lsc_sort', $oldColumn->getValue('lsc_sort'));
$newColumn->setValue('lsc_filter', $oldColumn->getValue('lsc_filter'));
$newColumn->save();
}
$this->columns[$newColumnNumber]->delete();
array_pop($this->columns);
}
return true;
}
/**
* Returns the column object with the corresponding number.
* If that column doesn't exists the method try to repair the
* column list. If that won't help then @b null will be returned.
* @param int $number The internal number of the column.
* This will be the position of the column in the list.
* @return \TableAccess|null Returns a TableAccess object of the database table @b adm_list_columns.
*/
public function getColumnObject($number)
{
if(array_key_exists($number, $this->columns))
{
return $this->columns[$number];
}
// column not found, then try to repair list
$this->repair();
if(array_key_exists($number, $this->columns))
{
return $this->columns[$number];
}
return null;
}
/**
* prepare SQL to list configuration
* @param int[] $roleIds Array with all roles, which members are shown
* @param int $memberStatus 0 - Only active members of a role
* 1 - Only former members
* 2 - Active and former members of a role
* @param string $startDate
* @param string $endDate
* @return string
*/
public function getSQL(array $roleIds, $memberStatus = 0, $startDate = null, $endDate = null)
{
global $gL10n, $gProfileFields, $gCurrentOrganization, $gDbType;
$sqlColumnNames = array();
$sqlOrderBys = array();
$sqlJoin = '';
$sqlWhere = '';
foreach($this->columns as $listColumn)
{
$lscUsfId = (int) $listColumn->getValue('lsc_usf_id');
$tableAlias = '';
if($lscUsfId > 0)
{
// dynamic profile field
$tableAlias = 'row'. $listColumn->getValue('lsc_number'). 'id'. $lscUsfId;
// define JOIN - Syntax
$sqlJoin .= ' LEFT JOIN '.TBL_USER_DATA.' '.$tableAlias.'
ON '.$tableAlias.'.usd_usr_id = usr_id
AND '.$tableAlias.'.usd_usf_id = '.$lscUsfId;
// usf_id is prefix for the table
$dbColumnName = $tableAlias.'.usd_value';
}
else
{
// Special fields like usr_photo, mem_begin ...
$dbColumnName = $listColumn->getValue('lsc_special_field');
}
$sqlColumnNames[] = $dbColumnName;
$userFieldType = $gProfileFields->getPropertyById($lscUsfId, 'usf_type');
// create a valid sort
$lscSort = $listColumn->getValue('lsc_sort');
if($lscSort !== '')
{
if($userFieldType === 'NUMBER' || $userFieldType === 'DECIMAL')
{
// if a field has numeric values then there must be a cast because database
// column is varchar. A varchar sort of 1,10,2 will be with cast 1,2,10
if($gDbType === 'pgsql' || $gDbType === 'postgresql') // for backwards compatibility "postgresql"
{
$columnType = 'numeric';
}
else
{
// mysql
$columnType = 'unsigned';
}
$sqlOrderBys[] = ' CAST('.$dbColumnName.' AS '.$columnType.') '.$lscSort;
}
else
{
$sqlOrderBys[] = $dbColumnName.' '.$lscSort;
}
}
// Handle the conditions for the columns
if($listColumn->getValue('lsc_filter') !== '')
{
$value = $listColumn->getValue('lsc_filter');
$type = '';
// custom profile field
if($lscUsfId > 0)
{
switch ($userFieldType)
{
case 'CHECKBOX':
$type = 'checkbox';
// 'yes' or 'no' will be replaced with 1 or 0, so that you can compare it with the database value
$arrCheckboxValues = array($gL10n->get('SYS_YES'), $gL10n->get('SYS_NO'), 'true', 'false');
$arrCheckboxKeys = array(1, 0, 1, 0);
$value = str_replace(array_map('admStrToLower', $arrCheckboxValues), $arrCheckboxKeys, admStrToLower($value));
break;
case 'DROPDOWN':
case 'RADIO_BUTTON':
$type = 'int';
// replace all field values with their internal numbers
$arrListValues = $gProfileFields->getPropertyById($lscUsfId, 'usf_value_list', 'text');
$value = array_search(admStrToLower($value), array_map('admStrToLower', $arrListValues), true);
break;
case 'NUMBER':
case 'DECIMAL':
$type = 'int';
break;
case 'DATE':
$type = 'date';
break;
default:
$type = 'string';
}
}
else
{
switch ($listColumn->getValue('lsc_special_field'))
{
case 'mem_begin':
case 'mem_end':
$type = 'date';
break;
case 'usr_login_name':
$type = 'string';
break;
case 'usr_photo':
$type = '';
break;
}
}
$parser = new ConditionParser();
// if profile field then add not exists condition
if($lscUsfId > 0)
{
$parser->setNotExistsStatement('SELECT 1
FROM '.TBL_USER_DATA.' '.$tableAlias.'s
WHERE '.$tableAlias.'s.usd_usr_id = usr_id
AND '.$tableAlias.'s.usd_usf_id = '.$lscUsfId);
}
// now transform condition into SQL
$sqlWhere .= $parser->makeSqlStatement($value, $dbColumnName, $type, $gProfileFields->getPropertyById($lscUsfId, 'usf_name')); // TODO Exception handling
}
}
$sqlColumnNames = implode(', ', $sqlColumnNames);
$sqlOrderBys = implode(', ', $sqlOrderBys);
$sqlRoleIds = implode(', ', $roleIds);
// Set state of membership
$sqlMemberStatus = '';
if ($memberStatus === 0)
{
if ($startDate === null)
{
$sqlMemberStatus = 'AND mem_begin <= \''.DATE_NOW.'\'';
}
else
{
$sqlMemberStatus = 'AND mem_begin <= \''.$endDate.' 23:59:59\'';
}
if ($endDate === null)
{
$sqlMemberStatus .= 'AND mem_end >= \''.DATE_NOW.'\'';
}
else
{
$sqlMemberStatus .= 'AND mem_end >= \''.$startDate.' 00:00:00\'';
}
}
elseif ($memberStatus === 1)
{
$sqlMemberStatus = 'AND mem_end < \''.DATE_NOW.'\'';
}
// Set SQL-Statement
$sql = 'SELECT DISTINCT mem_leader, usr_id, '.$sqlColumnNames.'
FROM '.TBL_MEMBERS.'
INNER JOIN '.TBL_ROLES.'
ON rol_id = mem_rol_id
INNER JOIN '.TBL_CATEGORIES.'
ON cat_id = rol_cat_id
INNER JOIN '.TBL_USERS.'
ON usr_id = mem_usr_id
'.$sqlJoin.'
WHERE usr_valid = 1
AND rol_id IN ('.$sqlRoleIds.')
AND ( cat_org_id = '. $gCurrentOrganization->getValue('org_id'). '
OR cat_org_id IS NULL )
'.$sqlMemberStatus.'
'.$sqlWhere.'
ORDER BY mem_leader DESC';
if($sqlOrderBys !== '')
{
$sql .= ', '.$sqlOrderBys;
}
return $sql;
}
/**
* Read data of responsible columns and store in object
*/
public function readColumns()
{
$sql = 'SELECT *
FROM '.TBL_LIST_COLUMNS.'
WHERE lsc_lst_id = '.$this->getValue('lst_id').'
ORDER BY lsc_number ASC';
$lscStatement = $this->db->query($sql);
while($lscRow = $lscStatement->fetch())
{
$lscNumber = (int) $lscRow['lsc_number'];
$this->columns[$lscNumber] = new TableAccess($this->db, TBL_LIST_COLUMNS, 'lsc');
$this->columns[$lscNumber]->setArray($lscRow);
}
}
/**
* The method will clear all column data of this object and restore all
* columns from the database. Then the column number will be renewed for all columns.
* This is in some cases a necessary fix if a column number was lost.
*/
public function repair()
{
// restore columns from database
$this->columns = array();
$this->readColumns();
$newColumnNumber = 1;
// check for every column if the number is expected otherwise set new number
foreach($this->columns as $number => $listColumn)
{
if($number !== $newColumnNumber)
{
$this->columns[$number]->setValue('lsc_number', $newColumnNumber);
$this->columns[$number]->save();
}
++$newColumnNumber;
}
// now restore columns with new numbers
$this->columns = array();
$this->readColumns();
}
/**
* @param bool $updateFingerPrint
* @return bool
*/
public function save($updateFingerPrint = true)
{
$this->db->startTransaction();
$returnValue = parent::save($updateFingerPrint);
// save columns
foreach($this->columns as $number => $listColumn)
{
if($listColumn->getValue('lsc_lst_id') == 0)
{
$listColumn->setValue('lsc_lst_id', $this->getValue('lst_id'));
}
$listColumn->save($updateFingerPrint);
}
$this->db->endTransaction();
return $returnValue;
}
}