protected ReportQueryByCriteria getBalanceCountQuery(Map fieldValues) { Criteria criteria = buildCriteriaFromMap(fieldValues, new LedgerBalance()); ReportQueryByCriteria query = QueryFactory.newReportQuery(LedgerBalance.class, criteria); // set the selection attributes query.setAttributes(new String[] { "count(*)" }); Collection<String> groupByList = buildGroupByCollection(); groupByList.remove(KFSPropertyConstants.SUB_ACCOUNT_NUMBER); groupByList.remove(KFSPropertyConstants.FINANCIAL_SUB_OBJECT_CODE); groupByList.remove(KFSPropertyConstants.FINANCIAL_OBJECT_TYPE_CODE); // add the group criteria into the selection statement String[] groupBy = (String[]) groupByList.toArray(new String[groupByList.size()]); query.addGroupBy(groupBy); return query; }
protected Iterator<Object[]> getDocumentNumbersOfBulkReceivingByCriteria(Criteria criteria, boolean orderByAscending) { ReportQueryByCriteria rqbc = new ReportQueryByCriteria(BulkReceivingDocument.class, criteria); rqbc.setAttributes(new String[] { KFSPropertyConstants.DOCUMENT_NUMBER }); if (orderByAscending) { rqbc.addOrderByAscending(KFSPropertyConstants.DOCUMENT_NUMBER); }else { rqbc.addOrderByDescending(KFSPropertyConstants.DOCUMENT_NUMBER); } return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc); }
/** * @see org.kuali.kfs.gl.dataaccess.LedgerBalanceHistoryBalancingDao#findDistinctFiscalYears() */ public List<Integer> findDistinctFiscalYears() { Criteria crit = new Criteria(); ReportQueryByCriteria q = QueryFactory.newReportQuery(BalanceHistory.class, crit); q.setAttributes(new String[] { KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR }); q.setDistinct(true); Iterator<Object[]> years = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(q); List<Integer> yearList = new ArrayList<Integer>(); while (years != null && years.hasNext()) { Object[] year = years.next(); yearList.add(new Integer(year[0].toString())); } return yearList; } }
/** * Retrieves a list of document numbers of the purchase order returned by the passed in criteria. * * @param criteria - list of criteria to use in the retrieve * @return Iterator of document numbers */ protected Iterator<Object[]> getDocumentNumbersUsingPurchaseOrderCriteria(Criteria criteria) { ReportQueryByCriteria rqbc = new ReportQueryByCriteria(PurchaseOrderDocument.class, criteria); rqbc.setAttributes(new String[] { KFSPropertyConstants.DOCUMENT_NUMBER }); rqbc.addOrderByAscending(KFSPropertyConstants.DOCUMENT_NUMBER); return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc); }
protected Iterator<Object[]> findBalanceSummaryRawData(Criteria criteria) { ReportQueryByCriteria query = QueryFactory.newReportQuery(LedgerBalance.class, criteria); List<String> groupByList = this.getGroupByListForBalanceSummary(); String[] groupBy = (String[]) groupByList.toArray(new String[groupByList.size()]); query.addGroupBy(groupBy); List<String> getAttributeList = this.getAttributeListForBalanceSummary(false); String[] attributes = (String[]) getAttributeList.toArray(new String[getAttributeList.size()]); query.setAttributes(attributes); query.addOrderByAscending(groupByList.get(0)); return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query); }
/** * Returns all distinct accounting period codes from the table * * @return a Collection of all distinct accounting period codes represented by UniversityDate records in the database * @see org.kuali.kfs.sys.dataaccess.UniversityDateDao#getAccountingPeriodCode() */ public Collection getAccountingPeriodCode() { Criteria criteria = new Criteria(); ReportQueryByCriteria query = QueryFactory.newReportQuery(UniversityDate.class, criteria); query.setAttributes(new String[] { "distinct " + KFSPropertyConstants.UNIVERSITY_FISCAL_ACCOUNTING_PERIOD }); query.addOrderByAscending(KFSPropertyConstants.UNIVERSITY_FISCAL_ACCOUNTING_PERIOD); return getPersistenceBrokerTemplate().getCollectionByQuery(query); } }
public Integer getMaxCreateSequence(Date date) { Criteria crit = new Criteria(); crit.addEqualTo("CREATE_DT", date); ReportQueryByCriteria q = QueryFactory.newReportQuery(CollectorDetail.class, crit); q.setAttributes(new String[] { "max(transactionLedgerEntrySequenceNumber)" }); Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(q); if (iter.hasNext()) { Object[] result = iter.next(); if (result[0] != null) { return new Integer(((BigDecimal)result[0]).intValue()); } } return null; }
/** * Does a ReportQuery to summarize GL balance data * * @param universityFiscalYear the fiscal year of balances to search for * @param balanceTypeCodes a list of balance type codes of balances to search for * @return iterator of reported on java.lang.Object arrays with the report data * @see org.kuali.kfs.gl.dataaccess.BalanceDao#getGlSummary(int, java.util.List) */ public Iterator<Object[]> getGlSummary(int universityFiscalYear, List<String> balanceTypeCodes) { LOG.debug("getGlSummary() started"); Criteria c = new Criteria(); c.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, universityFiscalYear); c.addIn(KFSPropertyConstants.BALANCE_TYPE_CODE, balanceTypeCodes); String[] attributes = new String[] { "account.subFundGroup.fundGroupCode", "sum(accountLineAnnualBalanceAmount)", "sum(beginningBalanceLineAmount)", "sum(contractsGrantsBeginningBalanceAmount)", "sum(month1Amount)", "sum(month2Amount)", "sum(month3Amount)", "sum(month4Amount)", "sum(month5Amount)", "sum(month6Amount)", "sum(month7Amount)", "sum(month8Amount)", "sum(month9Amount)", "sum(month10Amount)", "sum(month11Amount)", "sum(month12Amount)", "sum(month13Amount)" }; String[] groupby = new String[] { "account.subFundGroup.fundGroupCode" }; ReportQueryByCriteria query = new ReportQueryByCriteria(Balance.class, c); query.setAttributes(attributes); query.addGroupBy(groupby); query.addOrderByAscending("account.subFundGroup.fundGroupCode"); return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query); }
/** * build a hashmap containing the next entry sequence number to use for each document (document number) to be loaded from budget * construction to the general ledger * * @param target fiscal year for the budget load * @return HashMapap keyed on document number containing the next entry sequence number to use for the key */ protected HashMap<String, Integer> entrySequenceNumber(Integer requestYear) { HashMap<String, Integer> nextEntrySequenceNumber; // set up the query: each distinct document number in the source load table Criteria criteriaID = new Criteria(); criteriaID.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, requestYear); ReportQueryByCriteria queryID = new ReportQueryByCriteria(BudgetConstructionHeader.class, criteriaID); queryID.setAttributes(new String[] { KFSPropertyConstants.DOCUMENT_NUMBER }); nextEntrySequenceNumber = new HashMap<String, Integer>(hashCapacity(queryID)); // OK. build the hash map // there are NO entries for these documents yet, so we initialize the entry sequence number to 0 Iterator documentNumbersToLoad = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(queryID); while (documentNumbersToLoad.hasNext()) { Object[] resultRow = (Object[]) documentNumbersToLoad.next(); nextEntrySequenceNumber.put((String) resultRow[0], new Integer(0)); } return nextEntrySequenceNumber; }
/** * * @see org.kuali.kfs.module.endow.dataaccess.KemidBenefittingOrganizationDao#getIdsForMultipleBenefittingOrganizations(java.util.List) */ public List<String> getIdsForMultipleBenefittingOrganizations(List<String> kemids) { Criteria subCrit = new Criteria(); subCrit.addIn(EndowPropertyConstants.KEMID, kemids); ReportQueryByCriteria reportQueryByCriteria = QueryFactory.newReportQuery(KemidBenefittingOrganization.class, subCrit); reportQueryByCriteria.setAttributes(new String[] {EndowPropertyConstants.KEMID}); reportQueryByCriteria.addGroupBy(EndowPropertyConstants.KEMID); Criteria havingCriteria = new Criteria(); havingCriteria.addGreaterThan("count(" + EndowPropertyConstants.KEMID + ")", new Integer(1)); reportQueryByCriteria.setHavingCriteria(havingCriteria); reportQueryByCriteria.addOrderByAscending(EndowPropertyConstants.KEMID); Iterator<?> result = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportQueryByCriteria); List<String> kemidsMultiple = new ArrayList<String>(); while (result.hasNext()) { Object[] data = (Object[]) result.next(); kemidsMultiple.add(data[0].toString()); } return kemidsMultiple; }
/** * * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findInvoiceAmountByBillingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date) */ public HashMap<String, KualiDecimal> findInvoiceAmountByBillingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) { HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>(); Criteria criteria = new Criteria(); if (begin != null) { criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin); } if (end != null) { criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end); } criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED); criteria.addEqualTo("customerInvoiceDocument.billByChartOfAccountCode", chart); criteria.addEqualTo("customerInvoiceDocument.billedByOrganizationCode", org); criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true); ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria); reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber"); reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName"); Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria); while (iterator != null && iterator.hasNext()) { Object[] data = (Object[]) iterator.next(); map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]); } return map; }
protected void getCurrentBCHeaderKeys(Integer BaseYear) { Integer RequestYear = BaseYear + 1; Criteria criteriaId = new Criteria(); Iterator<Object[]> Results; criteriaId.addEqualTo(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, RequestYear); String[] selectList = { KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE, KFSPropertyConstants.ACCOUNT_NUMBER, KFSPropertyConstants.SUB_ACCOUNT_NUMBER }; ReportQueryByCriteria queryId = new ReportQueryByCriteria(BudgetConstructionHeader.class, selectList, criteriaId); Results = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(queryId); while (Results.hasNext()) { Object[] returnedRow = Results.next(); currentBCHeaderKeys.add(((String) returnedRow[0]) + ((String) returnedRow[1]) + ((String) returnedRow[2])); } }
/** * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionLockDao#getAllAccountLocks(java.lang.String) */ public List<BudgetConstructionHeader> getAllAccountLocks(String lockUnivId) { Criteria criteria = new Criteria(); if (StringUtils.isNotBlank(lockUnivId)) { criteria.addEqualTo(BCPropertyConstants.BUDGET_LOCK_USER_IDENTIFIER, lockUnivId); } else { criteria.addNotNull(BCPropertyConstants.BUDGET_LOCK_USER_IDENTIFIER); } ReportQueryByCriteria query = QueryFactory.newReportQuery(BudgetConstructionHeader.class, criteria); query.addOrderByAscending(KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR); query.addOrderByAscending(KFSPropertyConstants.CHART_OF_ACCOUNTS_CODE); query.addOrderByAscending(KFSPropertyConstants.ACCOUNT_NUMBER); query.addOrderByAscending(KFSPropertyConstants.SUB_ACCOUNT_NUMBER); return (List<BudgetConstructionHeader>) getPersistenceBrokerTemplate().getCollectionByQuery(query); }
/** * @see org.kuali.kfs.module.bc.document.dataaccess.BudgetConstructionDao#getPendingBudgetConstructionAppointmentFundingRequestSum(org.kuali.kfs.module.bc.businessobject.PendingBudgetConstructionGeneralLedger) */ public KualiInteger getPendingBudgetConstructionAppointmentFundingRequestSum(PendingBudgetConstructionGeneralLedger salaryDetailLine) { KualiInteger salarySum = KualiInteger.ZERO; Criteria criteria = new Criteria(); criteria.addEqualTo("universityFiscalYear", salaryDetailLine.getUniversityFiscalYear()); criteria.addEqualTo("chartOfAccountsCode", salaryDetailLine.getChartOfAccountsCode()); criteria.addEqualTo("accountNumber", salaryDetailLine.getAccountNumber()); criteria.addEqualTo("subAccountNumber", salaryDetailLine.getSubAccountNumber()); criteria.addEqualTo("financialObjectCode", salaryDetailLine.getFinancialObjectCode()); criteria.addEqualTo("financialSubObjectCode", salaryDetailLine.getFinancialSubObjectCode()); String[] columns = new String[] { "financialObjectCode", "financialSubObjectCode", "sum(appointmentRequestedAmount)" }; ReportQueryByCriteria q = QueryFactory.newReportQuery(PendingBudgetConstructionAppointmentFunding.class, columns, criteria, true); q.addGroupBy(new String[] { "financialObjectCode", "financialSubObjectCode" }); PersistenceBroker pb = getPersistenceBroker(true); Iterator<Object[]> iter = pb.getReportQueryIteratorByQuery(q); if (iter.hasNext()) { Object[] objs = (Object[]) TransactionalServiceUtils.retrieveFirstAndExhaustIterator(iter); if (objs[2] != null) { salarySum = new KualiInteger((BigDecimal) objs[2]); } } return salarySum; }
/** * @see org.kuali.kfs.pdp.dataaccess.PaymentGroupDao#getDisbursementNumbersByDisbursementType(java.lang.Integer, java.lang.String) */ public List<Integer> getDisbursementNumbersByDisbursementType(Integer pid,String disbursementType) { LOG.debug("getDisbursementNumbersByDisbursementType() started"); List<Integer> results = new ArrayList<Integer>(); Criteria criteria = new Criteria(); criteria.addEqualTo(PdpPropertyConstants.PaymentGroup.PAYMENT_GROUP_PROCESS_ID, pid); criteria.addEqualTo(PdpPropertyConstants.PaymentGroup.PAYMENT_GROUP_DISBURSEMENT_TYPE_CODE, disbursementType); String[] fields = new String[] { PdpPropertyConstants.PaymentGroup.PAYMENT_GROUP_DISBURSEMENT_NBR }; ReportQueryByCriteria rq = QueryFactory.newReportQuery(PaymentGroup.class, fields, criteria, true); rq.addOrderBy(PdpPropertyConstants.PaymentGroup.PAYMENT_GROUP_DISBURSEMENT_NBR, true); Iterator i = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rq); while ( i.hasNext() ) { Object[] data = (Object[])i.next(); BigDecimal d = (BigDecimal)data[0]; results.add( new Integer(d.intValue()) ); } return results; }
/** * get the summarized information of the entries that belong to the entry groups with the given group ids * * @param groupIdList the ids of origin entry groups * @return a set of summarized information of the entries within the specified groups * @see org.kuali.kfs.gl.dataaccess.OriginEntryDao#getSummaryByGroupId(java.util.List) */ public Iterator getSummaryByGroupId(Collection groupIdList) { LOG.debug("getSummaryByGroupId() started"); if (groupIdList == null || groupIdList.size() <= 0) { return null; } Collection ids = new ArrayList(); for (Iterator iter = groupIdList.iterator(); iter.hasNext();) { OriginEntryGroup element = (OriginEntryGroup) iter.next(); ids.add(element.getId()); } Criteria criteria = new Criteria(); criteria.addIn(KFSPropertyConstants.ENTRY_GROUP_ID, ids); ReportQueryByCriteria query = QueryFactory.newReportQuery(entryClass, criteria); String attributeList[] = { KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, KFSPropertyConstants.UNIVERSITY_FISCAL_PERIOD_CODE, KFSPropertyConstants.FINANCIAL_BALANCE_TYPE_CODE, KFSPropertyConstants.FINANCIAL_SYSTEM_ORIGINATION_CODE, KFSPropertyConstants.TRANSACTION_DEBIT_CREDIT_CODE, "sum(" + KFSPropertyConstants.TRANSACTION_LEDGER_ENTRY_AMOUNT + ")", "count(*)" }; String groupList[] = { KFSPropertyConstants.UNIVERSITY_FISCAL_YEAR, KFSPropertyConstants.UNIVERSITY_FISCAL_PERIOD_CODE, KFSPropertyConstants.FINANCIAL_BALANCE_TYPE_CODE, KFSPropertyConstants.FINANCIAL_SYSTEM_ORIGINATION_CODE, KFSPropertyConstants.TRANSACTION_DEBIT_CREDIT_CODE }; query.setAttributes(attributeList); query.addGroupBy(groupList); // add the sorting criteria for (int i = 0; i < groupList.length; i++) { query.addOrderByAscending(groupList[i]); } return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query); }
query.setAttributes(new String[]{"identifier", "name", "definition", "obsolete"}); query.addOrderByAscending("identifier");
/** * Determines if any non-closed accounts exist where the principal id is in the role of the role name * @param principalId the principal id to check * @param principalRoleName the name of the field on account to check for the principal id in * @return true if the principal has that account role, false otherwise */ protected boolean queryPrincipalHasAccountRole(String principalId, String principalRoleName) { Criteria criteria = new Criteria(); criteria.addEqualTo(principalRoleName, principalId); criteria.addEqualTo("active", "Y"); ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(Account.class, criteria); reportQuery.setAttributes(new String[] { "count(*)" }); int resultCount = 0; Iterator iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportQuery); while (iter.hasNext()) { final Object[] results = (Object[])iter.next(); resultCount = (results[0] instanceof Number) ? ((Number)results[0]).intValue() : new Integer(results[0].toString()).intValue(); } return resultCount > 0; }
/** * @see org.kuali.kfs.module.purap.document.dataaccess.PurchaseOrderDao#getOldestPurchaseOrderDocumentNumber(java.lang.Integer) */ public String getOldestPurchaseOrderDocumentNumber(Integer id) { Criteria criteria = new Criteria(); criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id); ReportQueryByCriteria rqbc = new ReportQueryByCriteria(PurchaseOrderDocument.class, criteria); rqbc.setAttributes(new String[] { KFSPropertyConstants.DOCUMENT_NUMBER }); rqbc.addOrderByAscending(KFSPropertyConstants.DOCUMENT_NUMBER); Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc); String oldestDocumentNumber = null; if (iter.hasNext()) { oldestDocumentNumber = (String) (iter.next())[0]; } return oldestDocumentNumber; }
/** * * @see org.kuali.kfs.module.endow.dataaccess.KemidReportGroupDao#getAttributeValues(java.lang.String, java.util.List) */ public List<String> getAttributeValues(String attributeName, List<String> values) { Criteria criteria = new Criteria(); for (String value : values) { Criteria c = new Criteria(); if (value.contains(KFSConstants.WILDCARD_CHARACTER)) { c.addLike(attributeName, value.trim().replace(KFSConstants.WILDCARD_CHARACTER, KFSConstants.PERCENTAGE_SIGN)); } else { c.addEqualTo(attributeName, value.trim()); } criteria.addOrCriteria(c); } ReportQueryByCriteria query = new ReportQueryByCriteria(KemidReportGroup.class, criteria, true); query.setAttributes(new String[] {attributeName}); Iterator<Object> result = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query); //return (List<String>) getPersistenceBrokerTemplate().getCollectionByQuery(query); List<String> attributeValues = new ArrayList<String>(); while (result.hasNext()) { Object[] data = (Object[]) result.next(); attributeValues.add(data[0].toString()); } return attributeValues; } }