public static function fromTransaction(Transaction $transaction, PropelPDO $con = null)
{
$transactionId = $transaction->getId();
$bookingData = array();
$bookings = BookingQuery::create()->filterByTransactionId($transactionId)->find($con);
foreach ($bookings as $booking) {
$bookingData[] = self::from($booking, $con);
}
$clockingData = array();
$clockings = ClockingQuery::create()->joinTransactionClocking()->joinWith('ClockingType')->add(TransactionClockingPeer::TRANSACTION_ID, $transactionId)->find($con);
foreach ($clockings as $clocking) {
$clockingData[] = self::from($clocking, $con) + array('Type' => self::from($clocking->getClockingType($con), $con));
}
return array('Start' => $transaction->getStart('U'), 'End' => $transaction->getEnd('U'), 'Bookings' => $bookingData, 'Clockings' => $clockingData, 'User' => self::from($transaction->getUserRelatedByUserId($con), $con)) + $transaction->toArray();
}
public function calcClockings(&$list, $userid, $start, $end)
{
$this->employee = UserQuery::create()->filterById($userid)->findOne();
$domain = $this->employee->getDomain();
$vacationDays = HolidayQuery::create()->filterByDomain($domain)->filterByDate($start, Criteria::GREATER_EQUAL)->filterByDate($end, Criteria::LESS_EQUAL)->find();
$this->holidays = array();
foreach ($vacationDays as $day) {
$key = dayKey($day->getDate());
$this->holidays[$key] = $day;
}
// Get flexitime
$this->flexitime = ClockingQuery::create()->filterByUserId($userid)->filterByStart($start, Criteria::LESS_THAN)->filterByVisibility(0)->withColumn('SUM(flexitime)', 'flexitimeSum')->findOne()->getFlexitimeSum();
$this->holidaysWeeks = Holiday::getCountPerWeek($domain, $start, $end);
// Calculate weektime for first item
$first = $list->getFirst();
if ($first == null) {
// No items at all, stop here
return;
}
$weekday = date('N', $first->getStart());
$currentWeek = date('W', $first->getStart());
$currentYear = date('Y', $first->getStart());
// Count any holiday as 'work done'
$weekKey = $currentWeek . '-' . $currentYear;
if (!array_key_exists($weekKey, $this->holidaysWeeks)) {
$this->holidaysWeeks[$weekKey] = 0;
}
$this->worktime = $this->holidaysWeeks[$weekKey] * $this->employee->getDailyTime();
if ($weekday > 1) {
$weekstart = createDate($first->getStart());
$weekstart->modify('midnight this week');
$weekend = createDate($first->getStart());
$weekend->modify('midnight this week +7 days');
$week = ClockingQuery::create()->filterByStart($weekstart->getTimestamp(), Criteria::GREATER_THAN)->filterByStart($weekend->getTimestamp(), Criteria::LESS_THAN)->filterByUser($this->employee)->filterByVisibility(0)->find();
$this->calcList($week);
}
$connection = Propel::getConnection(ClockingPeer::DATABASE_NAME);
$connection->beginTransaction();
try {
$this->calcList($list);
$connection->commit();
} catch (Exception $e) {
$connection->rollBack();
throw $e;
}
}
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();
}
/**
* Get the associated Clocking object
*
* @param PropelPDO $con Optional Connection object.
* @param $doQuery Executes a query to get the object if required
* @return Clocking The associated Clocking object.
* @throws PropelException
*/
public function getClocking(PropelPDO $con = null, $doQuery = true)
{
if ($this->aClocking === null && $this->clocking_id !== null && $doQuery) {
$this->aClocking = ClockingQuery::create()->findPk($this->clocking_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->aClocking->addTransactionClockings($this);
*/
}
return $this->aClocking;
}
/**
* Creates a new transaction.
*
* @param array $transactionData An associative array with the properties
* "UserId", "Start", "End" and "Comment".
* @param array $bookingData An array of booking data. Existing bookings are
* specified by ID, new bookings must be specified as objects:
* <code>{ "BookingTypeId": ..., "Label": ..., "Value": ... }</code>
* @param array $clockingIds Optional. An array of clocking IDs to assign
* to the new transaction. Default is NULL.
* @throws Exception
* @return bool
*/
public function do_create(array $transactionData = null, array $bookingData = null, array $clockingIds = null)
{
$con = Propel::getConnection();
if (!$con->beginTransaction()) {
throw new Exception('Could not start transaction.');
}
try {
$user = $this->requireUser();
if (!$user->isAdmin()) {
throw new Exception('Non-administrative user "' . $user->getFQN($con) . '" cannot create transactions.');
}
$account = $user->getAccount($con);
if ($account === null) {
throw new Exception('Could not determine the account the authenticated user "' . $user->getName() . '" #' . $user->getId() . ' belongs to.');
}
if (empty($bookingData)) {
throw new Exception('Transaction must contain at least one booking.');
}
$transaction = new Transaction();
$transaction->fromArray(array_intersect_key($transactionData, array('UserId' => true, 'Start' => true, 'End' => true, 'Comment' => true)));
$employeeId = $transaction->getUserId();
$employee = $this->findUserById($employeeId, $con);
if ($employee === null) {
throw new Exception('Employee with ID ' . $employeeId . ' could not be found.');
}
// Separate new and existing bookings (the latter are specified by ID)
$bookingIds = array();
foreach ($bookingData as $bookingIndex => $bookingItem) {
if (!is_array($bookingItem)) {
$bookingIds[$bookingItem] = $bookingItem;
unset($bookingData[$bookingIndex]);
}
}
$bookingTypeIds = BookingTypeQuery::create()->findByAccountId($account->getId(), $con)->getArrayCopy('Id');
$newBookings = array_values($this->createBookings($bookingData, $bookingTypeIds, $con));
// Load existing bookings and check for missing records
$bookingsById = BookingQuery::create()->joinBookingType()->add(BookingTypePeer::ACCOUNT_ID, $user->getAccountId())->findPks($bookingIds)->getArrayCopy('Id');
$missingBookingIds = array_diff_key($bookingIds, $bookingsById);
if (count($missingBookingIds) > 0) {
throw new Exception('Could not find bookings with the following IDs: ' . implode(', ', array_keys($missingBookingIds)));
}
if (empty($clockingIds)) {
$clockings = array();
} else {
$clockings = ClockingQuery::create()->filterByUserRelatedByUserId($employee)->addAscendingOrderByColumn(ClockingPeer::START)->addAscendingOrderByColumn(ClockingPeer::END)->addAscendingOrderByColumn(ClockingPeer::ID)->findPks($clockingIds, $con);
$clockingsById = $clockings->getArrayCopy('Id');
$missingClockingIds = array_diff_key(array_fill_keys($clockingIds, true), $clockingsById);
if (count($missingClockingIds) > 0) {
throw new Exception('Could not find clockings of user "' . $employee->getName() . '" #' . $employeeId . ' with the following IDs: ' . implode(', ', array_keys($missingClockingIds)));
}
}
$start = isset($transactionData['Start']) ? $transactionData['Start'] : null;
if ((string) $start === '') {
throw new Exception('Start date must be specified for transaction.');
}
$end = isset($transactionData['End']) ? $transactionData['End'] : null;
if ((string) $end === '') {
throw new Exception('End date must be specified for transaction.');
}
$transaction->save($con);
// Link bookings to transaction
foreach (array_merge(array_values($bookingsById), $newBookings) as $booking) {
$booking->setTransaction($transaction)->save($con);
}
// Link clockings
$this->linkTransactionClockings($transaction, $clockings, $con);
} catch (Exception $e) {
$con->rollBack();
throw $e;
}
if (!$con->commit()) {
throw new Exception('Could not commit transaction.');
}
return true;
}
/**
* Returns a new ClockingQuery object.
*
* @param string $modelAlias The alias of a model in the query
* @param ClockingQuery|Criteria $criteria Optional Criteria to build the query from
*
* @return ClockingQuery
*/
public static function create($modelAlias = null, $criteria = null)
{
if ($criteria instanceof ClockingQuery) {
return $criteria;
}
$query = new ClockingQuery();
if (null !== $modelAlias) {
$query->setModelAlias($modelAlias);
}
if ($criteria instanceof Criteria) {
$query->mergeWith($criteria);
}
return $query;
}
/**
* If this collection has already been initialized with
* an identical criteria, it returns the collection.
* Otherwise if this ClockingType is new, it will return
* an empty collection; or if this ClockingType has previously
* been saved, it will retrieve related Clockings from storage.
*
* This method is protected by default in order to keep the public
* api reasonable. You can provide public methods for those you
* actually need in ClockingType.
*
* @param Criteria $criteria optional Criteria object to narrow the query
* @param PropelPDO $con optional connection object
* @param string $join_behavior optional join type to use (defaults to Criteria::LEFT_JOIN)
* @return PropelObjectCollection|Clocking[] List of Clocking objects
*/
public function getClockingsJoinUserRelatedByUserId($criteria = null, $con = null, $join_behavior = Criteria::LEFT_JOIN)
{
$query = ClockingQuery::create(null, $criteria);
$query->joinWith('UserRelatedByUserId', $join_behavior);
return $this->getClockings($query, $con);
}
/**
* 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(ClockingPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
}
$con->beginTransaction();
try {
$deleteQuery = ClockingQuery::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;
}
}
/**
* Lists all clocking entries
*
* @param int $intStart
* @param int $intEnd
* @param int $intUserId Optional. Default is FALSE.
* @param int $intDomainId Optional. Default is FALSE.
* @param bool $showDeleted Optional. Default is FALSE.
* @param int $intShowBooked Optional. Default is {@link SHOW_BOOKED_ALL}.
* @param bool $wholeDayOnly Optional. Restricts results to whole-day
* clocking types. Default is FALSE.
* @param string $strOrderby
* @param string $strOrderMode
*/
public function do_list($intStart, $intEnd, $intUserId = false, $intDomainId = false, $showDeleted = false, $intShowBooked = self::SHOW_BOOKED_ALL, $wholeDayOnly = false, $strOrderby = 'Name', $strOrderMode = 'asc')
{
$user = $this->requireUser();
$query = ClockingQuery::create()->setDistinct()->joinWith('ClockingType')->joinWith('UserRelatedByUserId')->joinWith('UserRelatedByUserId.Domain')->add(UserPeer::DELETED, 0)->add(DomainPeer::VALID, null, Criteria::NOT_EQUAL)->add(DomainPeer::ACCOUNT_ID, $user->getAccount()->getId())->filterByStart($intEnd, Criteria::LESS_EQUAL)->filterByEnd($intStart, Criteria::GREATER_EQUAL);
// Clocking must end on or after the span's start
if (!$user->getIsAdmin()) {
$userId = $user->getId();
$query->add($query->getNewCriterion(ClockingPeer::CREATOR_ID, $userId)->addOr($query->getNewCriterion(ClockingPeer::USER_ID, $userId)));
}
if ((string) $intUserId !== '') {
$query->filterByUserId($intUserId);
}
if ($wholeDayOnly) {
$query->add(ClockingTypePeer::WHOLE_DAY, 0, Criteria::NOT_EQUAL);
}
if ($strOrderMode != 'asc') {
$strOrderMode = 'desc';
}
switch ($strOrderby) {
case 'Type':
$query->orderBy('ClockingType.Identifier', $strOrderMode);
break;
case 'User':
$query->orderBy('UserRelatedByUserId.Name', $strOrderMode);
break;
case 'ApprovalStatus':
case 'End':
$query->{'orderBy' . $strOrderby}($strOrderMode);
break;
default:
// Start
$query->orderByStart($strOrderMode);
break;
}
if (!$showDeleted) {
$query->filterByDeleted(0);
}
switch ($intShowBooked) {
case self::SHOW_BOOKED_ONLY:
$query->joinTransactionClocking()->join('TransactionClocking.Transaction')->add(TransactionPeer::DELETED, 0)->withColumn('TRUE', 'Booked');
break;
case self::SHOW_BOOKED_HIDE:
$query->leftJoinTransactionClocking()->addMultipleJoin(array(array(TransactionClockingPeer::TRANSACTION_ID, TransactionPeer::ID), array(TransactionPeer::DELETED, 0)), Criteria::LEFT_JOIN)->withColumn('FALSE', 'Booked')->filterByFrozen(0)->groupByClass('Clocking')->having('(COUNT(' . TransactionPeer::ID . ') = 0)');
break;
case self::SHOW_BOOKED_ALL:
default:
$query->leftJoinTransactionClocking()->addMultipleJoin(array(array(TransactionClockingPeer::TRANSACTION_ID, TransactionPeer::ID), array(TransactionPeer::DELETED, 0)), Criteria::LEFT_JOIN)->withColumn('(COUNT(' . TransactionPeer::ID . ') > 0)', 'Booked')->groupByClass('Clocking');
break;
}
$clockings = $query->find();
$result = array();
foreach ($clockings as $clocking) {
/* @var Clocking $clocking */
$result[] = EntityArray::from($clocking) + array('Booked' => (bool) $clocking->getBooked());
}
return $result;
}
