public function getRemainingAccounts()
{
if ($this->currentAccountIds == null) {
$this->getCurrentAccounts();
}
$q = AccountQuery::create('a')->addAsColumn('id', 'a.Id')->addAsColumn('name', 'a.Name')->filterById($this->currentAccountIds, \Propel\Runtime\ActiveQuery\Criteria::NOT_IN)->leftJoinParticipations('p')->select(array('id', 'name'))->groupBy('id')->withColumn('COUNT(p.Id)', 'attendencies')->addDescendingOrderByColumn('attendencies')->addAscendingOrderByColumn('Name');
return $q->find()->toArray();
}
/**
* Get the associated Account object
*
* @param PropelPDO $con Optional Connection object.
* @param $doQuery Executes a query to get the object if required
* @return Account The associated Account object.
* @throws PropelException
*/
public function getAccount(PropelPDO $con = null, $doQuery = true)
{
if ($this->aAccount === null && $this->account_id !== null && $doQuery) {
$this->aAccount = AccountQuery::create()->findPk($this->account_id, $con);
/* The following can be used additionally to
guarantee the related object contains a reference
to this object. This level of coupling may, however, be
undesirable since it could result in an only partially populated collection
in the referenced object.
$this->aAccount->addPlugins($this);
*/
}
return $this->aAccount;
}
private function import()
{
session_write_close();
$SOURCE_DB = 'groupion_new';
$USERNAME = '******';
$PASSWORD = '';
set_time_limit(0);
$status = new Status();
print '<br><br><br><br><br><div style="margin-left: 3em">';
print '<p>Starting database migration</p>';
flush();
try {
$con = Propel::getConnection();
$status->start('Connecting to database');
$dbh = new PDO('mysql:host=localhost;dbname=' . $SOURCE_DB, $USERNAME, $PASSWORD);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec('SET NAMES utf8');
$status->done();
$debugUser = UserQuery::create()->findOneByFQN('cms/test', $con);
if ($debugUser === null) {
throw new Exception('Could not find debug user "cms/test".');
}
$status->start('Clearing tables');
// Mark transactions created by old system as deleted
TransactionQuery::create()->filterByCreatorId(null, Criteria::NOT_EQUAL)->filterById(5814, Criteria::LESS_EQUAL)->filterByCreationdate(1347446069, Criteria::LESS_EQUAL)->update(array('Deleted' => 1), $con);
// Clockings created by old system => max. clocking ID = 365913
// Transactions created by new system with old clockings
// SELECT distinct min(tc.clocking_id) FROM transaction t join transaction_clocking tc on t.id=tc.transaction_id where tc.clocking_id<=365913 and ;
// => min clocking ID
/*
// Delete clockings without new system transactions
ClockingQuery::create()
->joinTransactionClocking()
->join('TransactionClocking.Transaction')
->filterByCreatorId(null, Criteria::NOT_EQUAL)
->add(TransactionPeer::ID, 5814, Criteria::LESS_EQUAL)
->add(TransactionPeer::CREATOR_ID, null, Criteria::NOT_EQUAL)
->update(array('Deleted' => 1), $con);
*/
// Mark clockings with new system transactions as deleted
ClockingQuery::create()->filterById(365913, Criteria::LESS_EQUAL)->filterByCreatorId(null)->update(array('Deleted' => 1), $con);
/*
TransactionClockingQuery::create()->deleteAll($con);
TransactionQuery::create()->deleteAll($con);
ClockingQuery::create()
->filterByUserRelatedByUserId($debugUser, Criteria::NOT_EQUAL)
->delete($con);
HolidayQuery::create()->deleteAll($con);
UserQuery::create()
->filterByName('test', Criteria::NOT_EQUAL)
->delete($con);
*/
/*
TransactionClockingQuery::create()->deleteAll();
TransactionQuery::create()->deleteAll();
ClockingQuery::create()->deleteAll();
DomainQuery::create()->deleteAll();
AccountQuery::create()->deleteAll();
$status->done();
$status->start('Create default Account');
$account = new Account();
$account->setName('Default account');
$account->save();
$status->done();
$status->start('Create default domain');
$domain = new Domain();
$domain->setName('default');
$domain->setDescription('Default domain created while migrating to the new system.');
$domain->setAccount($account);
$domain->save();
$status->done();
*/
$account = AccountQuery::create()->findOneByIdentifier('cms', $con);
$domain = DomainQuery::create();
/*
$status->start('Create holidays');
$holidaysUrl = \Xily\Config::get('migration.holidays_url', 'string', 'http://10.10.10.5/groupion/data/holidays');
$filenames = array('Bayern2009', 'Bayern2010', 'Bayern2011', 'Bayern2012', 'Bayern2013', 'Bayern2014');
foreach ($filenames as $filename) {
$file = fopen($holidaysUrl.'/'.$filename.'.csv', 'r');
if ( !is_resource($file) )
throw new Exception('Could not open file');
while ( is_array($row = fgetcsv($file, 1000, ';')) ) {
$date = strtotime($row[0]);
$name = $row[1];
$state = $row[2];
if ( $date ) {
$holidayDomain = new HolidayDomain();
$holidayDomain->setDomain($domain);
$holiday = new Holiday();
$holiday
->setAccount($account)
->setDate($date)
->setName(trim($name))
->addHolidayDomain($holidayDomain)
->save();
}
}
}
$status->done();
$status->start('Migrating Users');
$this->importUsers($status, $account, $domain, $dbh, $con);
$status->done();
*/
$usersByName = $this->getUsers($account, $con);
$clockingTypeMap = $this->getClockingTypes($account, $con);
$status->start('Migrating Clockings');
$clockingDataByOldID = $this->importClockings($status, $clockingTypeMap, $usersByName, $dbh, $con);
$status->done();
$bookingTypesByIdentifier = $this->getBookingTypes($account, $con);
$status->start('Migrating Transactions');
$this->importTransactions($status, $clockingTypeMap, $bookingTypesByIdentifier, $usersByName, $clockingDataByOldID, $dbh, $con);
$status->done();
echo '#INCONSISTENCIES: ' . $this->inconsistencies;
$dbh = null;
} catch (Exception $e) {
echo 'Error: ' . nl2br(htmlspecialchars($e->getMessage())) . '<br/>';
$status->showNotes($e->getMessage());
die;
}
print '<p>Finished migration!</p></div>';
$status->showNotes();
}
/**
* Removes this object from datastore and sets delete attribute.
*
* @param PropelPDO $con
* @return void
* @throws PropelException
* @throws Exception
* @see BaseObject::setDeleted()
* @see BaseObject::isDeleted()
*/
public function delete(PropelPDO $con = null)
{
if ($this->isDeleted()) {
throw new PropelException("This object has already been deleted.");
}
if ($con === null) {
$con = Propel::getConnection(AccountPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
}
$con->beginTransaction();
try {
$deleteQuery = AccountQuery::create()->filterByPrimaryKey($this->getPrimaryKey());
$ret = $this->preDelete($con);
if ($ret) {
$deleteQuery->delete($con);
$this->postDelete($con);
$con->commit();
$this->setDeleted(true);
} else {
$con->commit();
}
} catch (Exception $e) {
$con->rollBack();
throw $e;
}
}
/**
* Returns the number of related Account objects.
*
* @param Criteria $criteria
* @param boolean $distinct
* @param PropelPDO $con
* @return int Count of related Account objects.
* @throws PropelException
*/
public function countAccounts(Criteria $criteria = null, $distinct = false, PropelPDO $con = null)
{
$partial = $this->collAccountsPartial && !$this->isNew();
if (null === $this->collAccounts || null !== $criteria || $partial) {
if ($this->isNew() && null === $this->collAccounts) {
return 0;
}
if ($partial && !$criteria) {
return count($this->getAccounts());
}
$query = AccountQuery::create(null, $criteria);
if ($distinct) {
$query->distinct();
}
return $query->filterByAddress($this)->count($con);
}
return count($this->collAccounts);
}
$id = $request->getAttribute('id');
$account = AccountQuery::create()->findPK($id);
if ($account == null) {
return err_general_error($response, "Account Id {$id} not found");
}
/* Hide password and salt */
$account->setPassword("hidden");
$account->setSalt("hidden");
$response->getBody()->write($account->toJSON());
return $response;
});
$app->put('/account/{id}', function ($request, $response, $args) {
$id = $request->getAttribute('id');
$parsedBody = $request->getParsedBody();
if ($parsedBody == null) {
return err_general_error($response, "Provide a body to update your account");
}
$account = AccountQuery::create()->findPK($id);
if ($account == null) {
return err_general_error($response, "Account Id " . $id . " not found");
}
$account->fromArray($parsedBody);
if ($account->validate()) {
$account->save();
} else {
return err_general_error($response, "Validation failed");
}
/* response with the saves account */
$response->getBody()->write($account->toJSON());
return $response;
});
/**
* Deletes an account.
*
* @param int $id The account ID
* @return bool Returns TRUE if deletion was successful.
*/
public function do_delete($id)
{
$account = AccountQuery::create()->findOneById($id);
if ($account === null) {
throw new Exception('Account #' . $id . ' not found or no permission to remove it.');
}
$account->delete();
return true;
}
include "src/OptionRoutes.php";
})->add('HeaderMiddleware');
// Define the control group */
$app->group('/control', function () use($app) {
/* AUTH */
$app->options('/login', function ($request, $response, $args) {
$id = $request->getAttribute('id');
$response = $response->withHeader("Allow", "POST,OPTIONS");
$response = $response->withHeader("Access-Control-Allow-Methods", "POST,OPTIONS");
return $response;
});
$app->post('/login', function ($request, $response, $args) {
$parsedBody = $request->getParsedBody();
$password = $parsedBody['Password'];
$username = $parsedBody['Username'];
$account = AccountQuery::create()->findOneByUsername($username);
if ($account != null) {
$salt = $account->getSalt();
$hashedPassword = $account->getPassword();
} else {
session_destroy();
return err_auth_error($response, "Incorrect credentials (username)");
}
if (hash('sha512', $password . $salt) == $hashedPassword) {
session_unset();
session_regenerate_id(true);
$_SESSION['Username'] = $account->getUsername();
$_SESSION['Id'] = $account->getId();
$_SESSION['loggedin'] = true;
return success($response, "Logged in");
} else {
/**
* Returns a new AccountQuery object.
*
* @param string $modelAlias The alias of a model in the query
* @param AccountQuery|Criteria $criteria Optional Criteria to build the query from
*
* @return AccountQuery
*/
public static function create($modelAlias = null, $criteria = null)
{
if ($criteria instanceof AccountQuery) {
return $criteria;
}
$query = new AccountQuery();
if (null !== $modelAlias) {
$query->setModelAlias($modelAlias);
}
if ($criteria instanceof Criteria) {
$query->mergeWith($criteria);
}
return $query;
}
