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; }
/** * 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; }