List of usage examples for org.hibernate.type StandardBasicTypes CALENDAR
CalendarType CALENDAR
To view the source code for org.hibernate.type StandardBasicTypes CALENDAR.
Click Source Link
From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java
License:Open Source License
public Iterator<MemberTransactionDetailsReportData> membersTransactionsDetailsReport( final MembersTransactionsReportParameters params) { final StringBuilder sql = new StringBuilder(); final Map<String, Object> parameters = new HashMap<String, Object>(); // Find the transfer types ids Set<Long> ttIds = null; if (CollectionUtils.isNotEmpty(params.getPaymentFilters())) { ttIds = new HashSet<Long>(); for (PaymentFilter pf : params.getPaymentFilters()) { pf = getFetchDao().fetch(pf, PaymentFilter.Relationships.TRANSFER_TYPES); final Long[] ids = EntityHelper.toIds(pf.getTransferTypes()); CollectionUtils.addAll(ttIds, ids); }//from w w w. jav a 2s . c om } // Get the member group ids Set<Long> groupIds = null; if (CollectionUtils.isNotEmpty(params.getMemberGroups())) { groupIds = new HashSet<Long>(); CollectionUtils.addAll(groupIds, EntityHelper.toIds(params.getMemberGroups())); } // Get the period final Period period = params.getPeriod(); final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); final QueryParameter endParameter = HibernateHelper.getEndParameter(period); // Set the parameters final boolean useTT = CollectionUtils.isNotEmpty(ttIds); if (useTT) { parameters.put("ttIds", ttIds); } if (beginParameter != null) { parameters.put("beginDate", beginParameter.getValue()); } if (endParameter != null) { parameters.put("endDate", endParameter.getValue()); } parameters.put("processed", Payment.Status.PROCESSED.getValue()); // Build the sql string sql.append( " select u.username, m.name, bu.username broker_username, b.name broker_name, h.account_type_name, h.date, h.amount, h.description, h.related_username, h.related_name, h.transfer_type_name, h.transaction_number"); sql.append( " from members m inner join users u on m.id = u.id left join members b on m.member_broker_id = b.id left join users bu on b.id = bu.id,"); sql.append(" ("); if (params.isCredits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, false); if (params.isDebits()) { sql.append(" union"); } } if (params.isDebits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, false); } sql.append(" ) h"); sql.append(" where m.id = h.member_id"); if (groupIds != null) { parameters.put("groupIds", groupIds); sql.append(" and m.group_id in (:groupIds)"); } sql.append(" order by m.name, u.username, h.account_type_name, h.date desc, h.transfer_id desc"); // Prepare the query final SQLQuery query = getSession().createSQLQuery(sql.toString()); final Map<String, Type> columns = new LinkedHashMap<String, Type>(); columns.put("username", StandardBasicTypes.STRING); columns.put("name", StandardBasicTypes.STRING); columns.put("broker_username", StandardBasicTypes.STRING); columns.put("broker_name", StandardBasicTypes.STRING); columns.put("account_type_name", StandardBasicTypes.STRING); columns.put("date", StandardBasicTypes.CALENDAR); columns.put("amount", StandardBasicTypes.BIG_DECIMAL); columns.put("description", StandardBasicTypes.STRING); columns.put("related_username", StandardBasicTypes.STRING); columns.put("related_name", StandardBasicTypes.STRING); columns.put("transfer_type_name", StandardBasicTypes.STRING); columns.put("transaction_number", StandardBasicTypes.STRING); for (final Map.Entry<String, Type> entry : columns.entrySet()) { query.addScalar(entry.getKey(), entry.getValue()); } getHibernateQueryHandler().setQueryParameters(query, parameters); // Create a transformer, which will read rows as Object[] and transform // them to MemberTransactionDetailsReportData final Transformer<Object[], MemberTransactionDetailsReportData> transformer = new Transformer<Object[], MemberTransactionDetailsReportData>() { public MemberTransactionDetailsReportData transform(final Object[] input) { final MemberTransactionDetailsReportData data = new MemberTransactionDetailsReportData(); int i = 0; for (final Map.Entry<String, Type> entry : columns.entrySet()) { final String columnName = entry.getKey(); // Column names are transfer_type_name, property is // transferTypeName String propertyName = WordUtils.capitalize(columnName, COLUMN_DELIMITERS); propertyName = Character.toLowerCase(propertyName.charAt(0)) + propertyName.substring(1); propertyName = StringUtils.replace(propertyName, "_", ""); PropertyHelper.set(data, propertyName, input[i]); i++; } return data; } }; return new ScrollableResultsIterator<MemberTransactionDetailsReportData>(query, transformer); }
From source file:nl.strohalm.cyclos.dao.members.ReferenceDAOImpl.java
License:Open Source License
public List<PaymentAwaitingFeedbackDTO> searchPaymentsAwaitingFeedback( final PaymentsAwaitingFeedbackQuery query) { final ResultType resultType = query.getResultType(); final PageParameters pageParameters = query.getPageParameters(); final boolean countOnly = resultType == ResultType.PAGE && pageParameters != null && pageParameters.getMaxResults() == 0; // There are 2 tables which contains payments that can have feedback: transfers and scheduled payments // As we need an union, we need a native SQL final Member member = query.getMember(); Boolean expired = query.getExpired(); final StringBuilder sql = new StringBuilder(); sql.append(" select "); if (countOnly) { sql.append(" count(*) as row_count"); } else {/*from w w w . ja v a2 s . com*/ sql.append(" * "); } sql.append(" from ( "); { sql.append( " select t.id, t.type_id as transferTypeId, false as scheduled, t.date, t.amount, tm.id as memberId, tm.name as memberName, ta.owner_name as memberUsername"); sql.append( " from transfers t inner join transfer_types tt on t.type_id = tt.id inner join accounts ta on t.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on t.from_account_id = a.id"); } sql.append(" left join refs tf on tf.transfer_id = t.id"); sql.append(" where tt.requires_feedback = true"); sql.append(" and t.date >= tt.feedback_enabled_since"); sql.append(" and t.parent_id is null"); sql.append(" and t.chargeback_of_id is null"); sql.append(" and t.scheduled_payment_id is null"); sql.append(" and t.process_date is not null"); if (expired != null) { sql.append(" and t.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } sql.append(" union "); sql.append(" select sp.id, sp.type_id, true, sp.date, sp.amount, tm.id, tm.name, ta.owner_name"); sql.append( " from scheduled_payments sp inner join transfer_types tt on sp.type_id = tt.id inner join accounts ta on sp.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on sp.from_account_id = a.id"); } sql.append(" left join refs tf on tf.scheduled_payment_id = sp.id"); sql.append(" where tt.requires_feedback = true"); if (expired != null) { sql.append(" and sp.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and sp.date >= tt.feedback_enabled_since"); sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } } sql.append(") as awaiting "); if (!countOnly) { sql.append("order by date"); } SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString()); if (member != null) { sqlQuery.setLong("memberId", member.getId()); } if (countOnly) { // Handle the special case for count only sqlQuery.addScalar("row_count", StandardBasicTypes.INTEGER); int count = ((Number) sqlQuery.uniqueResult()).intValue(); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, count, Collections.<PaymentAwaitingFeedbackDTO>emptyList()); } else { // Execute the search sqlQuery.addScalar("id", StandardBasicTypes.LONG); sqlQuery.addScalar("transferTypeId", StandardBasicTypes.LONG); sqlQuery.addScalar("scheduled", StandardBasicTypes.BOOLEAN); sqlQuery.addScalar("date", StandardBasicTypes.CALENDAR); sqlQuery.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); sqlQuery.addScalar("memberId", StandardBasicTypes.LONG); sqlQuery.addScalar("memberName", StandardBasicTypes.STRING); sqlQuery.addScalar("memberUsername", StandardBasicTypes.STRING); getHibernateQueryHandler().applyPageParameters(pageParameters, sqlQuery); // We'll always use an iterator, even if it is for later adding it to a list Iterator<PaymentAwaitingFeedbackDTO> iterator = new ScrollableResultsIterator<PaymentAwaitingFeedbackDTO>( sqlQuery, new Transformer<Object[], PaymentAwaitingFeedbackDTO>() { public PaymentAwaitingFeedbackDTO transform(final Object[] input) { PaymentAwaitingFeedbackDTO dto = new PaymentAwaitingFeedbackDTO(); dto.setId((Long) input[0]); dto.setTransferTypeId((Long) input[1]); dto.setScheduled(Boolean.TRUE.equals(input[2])); dto.setDate((Calendar) input[3]); dto.setAmount((BigDecimal) input[4]); dto.setMemberId((Long) input[5]); dto.setMemberName((String) input[6]); dto.setMemberUsername((String) input[7]); TransferType transferType = (TransferType) getSession().load(TransferType.class, dto.getTransferTypeId()); dto.setCurrency(getFetchDao().fetch(transferType.getCurrency())); return dto; } }); if (resultType == ResultType.ITERATOR) { return new IteratorListImpl<PaymentAwaitingFeedbackDTO>(iterator); } else { List<PaymentAwaitingFeedbackDTO> list = new ArrayList<PaymentAwaitingFeedbackDTO>(); CollectionUtils.addAll(list, iterator); DataIteratorHelper.close(iterator); if (resultType == ResultType.PAGE) { // For page, we need another search for the total count query.setPageForCount(); int totalCount = PageHelper.getTotalCount(searchPaymentsAwaitingFeedback(query)); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, totalCount, list); } else { return list; } } } }