se/SE/se-lib/Route/FixUsersLongLogin.php

<?php

Lib::loadClass('RouteBase');

class Route_FixUsersLongLogin extends RouteBase {

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}
	}

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		$this->_menu();
		SE_Layout::dol();
	}

	private function _menu($selectedMonth) {
		$usersTodoList = $this->getUsersWithTooLongLogin();
		?>
  <div class="container">
		<?php if (empty($usersTodoList)) : ?>
			<div class="alert alert-info">
				Brak użytwkoników z loginem dłuższym niż 20 znaków
			</div>
		<?php else : ?>
		<table class="table table-bordered table-hovered">
			<tr>
				<th>Lp.</th>
				<th>Nr</th>
				<th>Login</th>
				<th>Poziom Uprawnień</th>
				<th>Status</th>
				<th>Data synch.</th>
				<th>Nowy login</th>
				<th>Update Login and Sync.</th>
			</tr>
			<?php $i = 0; foreach ($usersTodoList as $user) : $i++ ?>
			<?php
				if (false !== strpos($user->ADM_ACCOUNT, '.')) {
					$newLogin = explode('.', $user->ADM_ACCOUNT, 2);
					$newLogin = substr($newLogin[0], 0, 1) . ".{$newLogin[1]}";
					if (strlen($newLogin) > 20) {
						$newLogin = substr($newLogin, 0, 20);
					}
				} else {
					$newLogin = substr($user->ADM_ACCOUNT, 0, 20);
				}
			?>
			<tr>
				<td><?php echo $i; ?></td>
				<td><?php echo $user->ID; ?></td>
				<td style="font-family:monospace">
					<span style=""><?php echo substr($user->ADM_ACCOUNT, 0, 20); ?></span><span style="color:red"><?php echo substr($user->ADM_ACCOUNT, 20); ?></span>
				</td>
				<td><?php echo $user->ADM_ADMIN_LEVEL; ?></td>
				<td><?php echo $user->A_STATUS; ?></td>
				<td><?php echo $user->A_SYNC_LDAP_DATE; ?></td>
				<td style="font-family:monospace;color:<?php echo ($this->getUserByLogin($newLogin)?'red':'green')?>;"><?php echo $newLogin; ?></td>
				<td><a target="_blank"
							 class="btn btn-xs btn-primary"
							 title="Aktualizuj Login z <?php echo $user->ADM_ACCOUNT; ?> na <?php echo $newLogin; ?> i uruchom synchronizcję do LDAP"
							 href="index.php?_route=FixUsersLongLogin&_task=updateLoginAndSync&currentLogin=<?php echo $user->ADM_ACCOUNT; ?>&newLogin=<?php echo $newLogin; ?>">Update and sync.</a></td>
			</tr>
			<?php endforeach; ?>
		</table>
		<?php endif; ?>
	</div>
<?php
	}

	public function getUsersWithTooLongLogin() {
		$usersTodoList = array();
		$db = DB::getDB();
		$sql = <<<SQL
			select `ID`, `ADM_ACCOUNT`, `A_STATUS`
				, `ADM_ADMIN_LEVEL`
				, `A_SYNC_LDAP_DATE`
			from `ADMIN_USERS`
			where LENGTH(`ADM_ACCOUNT`) > 20
SQL;
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$usersTodoList[$r->ADM_ACCOUNT] = $r;
		}
		return $usersTodoList;
	}

	public function getUserByLogin($login) {
		$user = null;
		$db = DB::getDB();
		$sqlLogin = $db->_($login);
		$sql = <<<SQL
			select `ID`, `ADM_ACCOUNT`, `A_STATUS`
				, `ADM_ADMIN_LEVEL`
				, `A_SYNC_LDAP_DATE`
			from `ADMIN_USERS`
			where `ADM_ACCOUNT`='{$sqlLogin}'
SQL;
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$user = $r;
		}
		return $user;
	}

	public function updateLoginAndSyncAction() {
		$currentLogin = V::get('currentLogin', '', $_REQUEST, 'word');
		$newLogin = V::get('newLogin', '', $_REQUEST, 'word');
		$user = $this->getUserByLogin($currentLogin);

		SE_Layout::gora();
		?>
<div class="container">
	<?php if (!$user) : ?>
		<div class="alert alert-danger">
			Brak użytkownika z loginem <?php echo $currentLogin; ?>
		</div>
	<?php else : ?>
		<div class="alert alert-info">
			Zmiana loginu z <?php echo $currentLogin; ?> na <?php echo $newLogin; ?>
		</div>
		<?php
			try {
				$affected = $this->updateLogin($user, $currentLogin, $newLogin);
				if ($affected == 2) {
					echo '<div class="alert alert-success">' . "Login został poprawnie zmieniony na '{$newLogin}'" . '</div>';
				} else if ($affected == 1) {
					echo '<div class="alert alert-warning">' . "Nie zapisano hist" . '</div>';
				} else if ($affected == 0) {
					echo '<div class="alert alert-info">' . "Nic nie zmieniono" . '</div>';
				}
                if ($affected) $this->updateDatabase($currentLogin, $newLogin);
			} catch (Exception $e) { ?>
				<div class="alert alert-danger">
					<?php echo $e->getMessage(); ?>
				</div>
			<?php } ?>
		<a class="btn btn-primary"
			 href="index.php?MENU_INIT=SYNC_LDAP_PERMS&syncUsr=<?php echo $newLogin; ?>">Synchronizuj do LDAP (<?php echo $newLogin; ?>)</a>
	<?php endif; ?>
</div>
<?php
		SE_Layout::dol();
	}

	public function updateLogin($user, $currentLogin, $newLogin) {
		$db = DB::getDB();

		if ($currentLogin != $user->ADM_ACCOUNT) {
			throw new Exception("Error: User ADM_ACCOUNT != '{$currentLogin}'");
		}

		$sqlObj = new stdClass();
		$sqlObj->ID = $user->ID;
		$sqlObj->ADM_ACCOUNT = $newLogin;

		$affected = $db->UPDATE_OBJ('ADMIN_USERS', $sqlObj);
		if ($affected < 0) {
			if ($db->has_errors()) {
				$dsErrors = array();
				$errorsSql = $db->get_errors();
				foreach ($errorsSql as $vErr) {
					if ('SQL QUERY FAILED: ' == substr($vErr, 0, 18)) {
						$vErr = substr($vErr, 18);
					}
					$dsErrors[] = $vErr;
				}
				if (!empty($dsErrors)) {
					Lib::loadClass('StorageException');
					throw new StorageException($dsErrors);
				}
			} else {
				throw new Exception("Error: nic nie zmieniono");
			}
		}
		return $affected;
	}

	public function updateDatabase($currentLogin, $newLogin) {
		$db = DB::getDB();
		$columns = Array("L_APPOITMENT_USER","A_RECORD_CREATE_AUTHOR","A_RECORD_UPDATE_AUTHOR");
		$dbName = $db->getDatabaseName();

		echo '<div class="alert alert-info" role="alert">';
        
		foreach ($columns as $column) {
			$sql = <<<SQL
				select cols.`TABLE_NAME`
				from `INFORMATION_SCHEMA`.`COLUMNS` cols
				join `INFORMATION_SCHEMA`.`TABLES` tabs
					on cols.TABLE_SCHEMA=tabs.TABLE_SCHEMA
					and cols.TABLE_NAME=tabs.TABLE_NAME
					and tabs.TABLE_TYPE!='VIEW'
				where cols.`TABLE_SCHEMA`='{$dbName}'
				and cols.`COLUMN_NAME`='{$column}'
SQL;
			$res = $db->query($sql);

			while ($r = $db->fetch($res)) {
				$tableName = $r->TABLE_NAME;
				$sql = <<<SQL
					update `{$dbName}`.`{$tableName}` tab
					set tab.`{$column}`='{$newLogin}'
					where tab.`{$column}`='{$currentLogin}'
SQL;
				echo 'Aktualizowanie kolumny `'.$column.'`. w tabeli `'.$tableName.'`... ';
				try {
					$db->query($sql);
					$affected=$db->affected_rows();
					$affected_all+=$affected;
					if ($affected) {
						echo '<span style="color:green">Zaktualizowano '.$affected.' rekordów.</span>';
					} else {
						echo '<span style="color:red">Nie zaktualizowano żadnego rekordu.</span>';
					}
					echo "<br>";
				} catch (Exception $e) { ?>
					<div class="alert alert-danger">
						<?php echo $e->getMessage(); ?>
					</div>
				<?php }
			}
		}
		echo "<br>Łącznie zaktualizowano {$affected_all} rekordów.";
        echo "</div>";
	}

}