public function action_allinvoices()
{
$members = \MemberQuery::create()->filterByDeletionDate(null, \Criteria::ISNULL)->filterByNum('101', \Criteria::NOT_EQUAL)->orderBy('num')->find();
$result = '<div class="container"><div class="row sheet">
<table class="table2Debug">
<tbody>
<tr>
<th>Member</th>
<th>Total</th>
<th>Reasons Total</th>
<th>Quantity</th>
<th>Reasons</th>
</tr>';
$reasons = \Tbmt\Localizer::get('view.account.tabs.invoice.transaction_reasons');
foreach ($members as $member) {
$result .= '<tr>';
$result .= '<td>' . $member->getNum() . ' - ' . $member->getFirstName() . ' - ' . $member->getLastName() . '</td>';
$result .= '<td>' . \Tbmt\view\Factory::currencyArrToString($member->getOutstandingTotal()) . '</td>';
$result .= '<td colspan="3"></td>';
$result .= '</tr>';
$transactions = \TransactionQuery::create()->join('Transfer')->useTransferQuery()->filterByMember($member)->endUse()->select(['Reason'])->withColumn('count(*)', 'Quantity')->withColumn('sum(Transaction.Amount)', 'Total')->groupBy('Transfer.Currency')->groupBy('Transaction.Reason')->find();
foreach ($transactions as $transaction) {
$result .= '<tr>';
$result .= '<td colspan="2"></td>';
$result .= '<td>' . \Tbmt\Localizer::numFormat($transaction['Total']) . '</td>';
$result .= '<td>' . $transaction['Quantity'] . '</td>';
$result .= '<td>' . $reasons[$transaction['Reason']] . '</td>';
$result .= '</tr>';
}
}
$result .= '</tbody></table></div></div>';
return $result;
}
public function render(array $params = array())
{
if (!isset($params['member']) && !$params['member'] instanceof \Member) {
throw new \Exception('Invalid param "member" for account index view.');
}
$this->member = $params['member'];
$query = \TransactionQuery::create()->join('Transfer')->useTransferQuery()->filterByMember($this->member)->endUse()->select(['Reason', 'Transfer.Currency'])->withColumn('count(*)', 'Quantity')->withColumn('sum(Transaction.Amount)', 'Total')->groupBy('Transfer.Currency')->groupBy('Transaction.Reason')->limit(100);
$this->transactions = $query->find();
$this->transDateForm = \Tbmt\Localizer::get('datetime_format_php.long');
$this->allowTotalInvoice = $this->member->getType() >= \Member::TYPE_SALES_MANAGER;
return $this->renderFile(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'tab.invoice.account.html', $params);
}
/**
* Creates a query that is restricted to transactions belonging to the specified account.
* This function will LEFT JOIN to "transaction_booking", "booking" and
* "booking_type". Use {@link TransactionQuery::with()} if you wish to
* fetch records from the joined tables.
*
* @param User $user The user object.
* @return TransactionQuery
*/
private function createTransactionQuery(User $user, PropelPDO $con = null)
{
$account = $user->getAccount($con);
if ($account === null) {
throw new Exception('Could not determine account the user "' . $user->getName() . '" belongs to.');
}
$query = TransactionQuery::create()->leftJoinBooking()->leftJoin('Booking.BookingType');
$accessCriterion = $query->getNewCriterion(TransactionPeer::USER_ID, $user->getId());
if ($user->isAdmin()) {
$accessCriterion->addOr($query->getNewCriterion(BookingTypePeer::ACCOUNT_ID, $account->getId()));
}
return $query->add($accessCriterion);
}
public function render(array $params = array())
{
if (!isset($params['member']) && !$params['member'] instanceof \Member) {
throw new \Exception('Invalid param member for account index view.');
}
$this->member = $params['member'];
$this->recipient = isset($params['recipient']) ? $params['recipient'] : null;
$this->formVal = \Transaction::initBonusTransactionForm(isset($params['formVal']) ? $params['formVal'] : $_REQUEST);
$this->formErrors = isset($params['formErrors']) ? $params['formErrors'] : [];
$objBonusTransactions = \TransactionQuery::create()->filterByRelatedId($this->member->getId())->filterByReason(\Transaction::REASON_CUSTOM_BONUS)->join('Transaction.Transfer')->join('Transfer.Member')->select(['Member.Num', 'Transaction.Amount', 'Purpose', 'Transaction.Date'])->limit(100)->find();
$arrBonusTransactions = [];
$dateFormat = \Tbmt\Localizer::get('datetime_format_php.long');
foreach ($objBonusTransactions as $bonusTransaction) {
$arrBonusTransactions[] = [$bonusTransaction['Member.Num'], $bonusTransaction['Transaction.Amount'], $bonusTransaction['Purpose'], (new \DateTime($bonusTransaction['Transaction.Date']))->format($dateFormat)];
}
$this->bonusTransactions = $arrBonusTransactions;
return $this->renderFile(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'tab.bonus_payments.account.html', $params);
}
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 Transaction object
*
* @param PropelPDO $con Optional Connection object.
* @param $doQuery Executes a query to get the object if required
* @return Transaction The associated Transaction object.
* @throws PropelException
*/
public function getTransaction(PropelPDO $con = null, $doQuery = true)
{
if ($this->aTransaction === null && $this->transaction_id !== null && $doQuery) {
$this->aTransaction = TransactionQuery::create()->findPk($this->transaction_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->aTransaction->addTransactionClockings($this);
*/
}
return $this->aTransaction;
}
/**
* Returns the number of related Transaction objects.
*
* @param Criteria $criteria
* @param boolean $distinct
* @param PropelPDO $con
* @return int Count of related Transaction objects.
* @throws PropelException
*/
public function countTransactionsRelatedByUserId(Criteria $criteria = null, $distinct = false, PropelPDO $con = null)
{
$partial = $this->collTransactionsRelatedByUserIdPartial && !$this->isNew();
if (null === $this->collTransactionsRelatedByUserId || null !== $criteria || $partial) {
if ($this->isNew() && null === $this->collTransactionsRelatedByUserId) {
return 0;
}
if ($partial && !$criteria) {
return count($this->getTransactionsRelatedByUserId());
}
$query = TransactionQuery::create(null, $criteria);
if ($distinct) {
$query->distinct();
}
return $query->filterByUserRelatedByUserId($this)->count($con);
}
return count($this->collTransactionsRelatedByUserId);
}
/**
* 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(TransactionPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
}
$con->beginTransaction();
try {
$deleteQuery = TransactionQuery::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 a new TransactionQuery object.
*
* @param string $modelAlias The alias of a model in the query
* @param TransactionQuery|Criteria $criteria Optional Criteria to build the query from
*
* @return TransactionQuery
*/
public static function create($modelAlias = null, $criteria = null)
{
if ($criteria instanceof TransactionQuery) {
return $criteria;
}
$query = new TransactionQuery();
if (null !== $modelAlias) {
$query->setModelAlias($modelAlias);
}
if ($criteria instanceof Criteria) {
$query->mergeWith($criteria);
}
return $query;
}
