List of usage examples for org.hibernate ScrollableResults close
void close();
From source file:org.openbravo.dal.service.OBQuery.java
License:Open Source License
/** * Computes the row number of a record which has the id which is passed in as a parameter. The * rownumber computation takes into account the filter and sorting settings of the the OBQuery * object.//from www . j a v a2 s . c o m * * @param targetId * the record id * @return the row number or -1 if not found */ public int getRowNumber(String targetId) { String qryStr = createQueryString(); if (qryStr.toLowerCase().contains(FROM_SPACED)) { final int index = qryStr.indexOf(FROM_SPACED) + FROM_SPACED.length(); qryStr = qryStr.substring(index); } final Query qry = getSession().createQuery("select " + usedAlias + "id " + FROM_SPACED + qryStr); setParameters(qry); final ScrollableResults results = qry.scroll(ScrollMode.FORWARD_ONLY); try { while (results.next()) { final String id = results.getString(0); if (id.equals(targetId)) { return results.getRowNumber(); } } } finally { results.close(); } return -1; }
From source file:org.openbravo.erpCommon.ad_actionButton.MRPManufacturingPlanProcess.java
License:Open Source License
@Override protected void doExecute(ProcessBundle bundle) throws Exception { OBError msg = new OBError(); msg.setType("Success"); msg.setTitle(OBMessageUtils.messageBD("Success")); final String strManufacturingMRPID = (String) bundle.getParams().get("MRP_Run_Production_ID"); final ProductionRun productionRun = OBDal.getInstance().get(ProductionRun.class, strManufacturingMRPID); final String userId = (String) DalUtil.getId(OBContext.getOBContext().getUser()); final String orgId = (String) DalUtil.getId(productionRun.getOrganization()); final String clientId = (String) DalUtil.getId(productionRun.getClient()); final String plannerId = productionRun.getPlanner() != null ? (String) DalUtil.getId(productionRun.getPlanner()) : NULL;/*from w w w .j ava2 s.c om*/ final String productId = productionRun.getProduct() != null ? (String) DalUtil.getId(productionRun.getProduct()) : NULL; final String productCategoryId = productionRun.getProductCategory() != null ? (String) DalUtil.getId(productionRun.getProductCategory()) : NULL; final String bpId = productionRun.getBusinessPartner() != null ? (String) DalUtil.getId(productionRun.getBusinessPartner()) : NULL; final String bpCatId = productionRun.getBusinessPartnerCategory() != null ? (String) DalUtil.getId(productionRun.getBusinessPartnerCategory()) : NULL; final long timeHorizon = productionRun.getTimeHorizon(); final long safetyLeadTime = productionRun.getSafetyLeadTime(); final Date docDate = productionRun.getDocumentDate(); try { log4j.debug("Prepare process delete not fixed lines and set exploded to false."); deleteNotFixedLines(strManufacturingMRPID); ScrollableResults linesToUpdate = getLinesToUpdate(strManufacturingMRPID); int i = 0; try { while (linesToUpdate.next()) { ProductionRunLine prLine = (ProductionRunLine) linesToUpdate.get(0); prLine.setInserted(false); OBDal.getInstance().save(prLine); if (i % 100 == 0) { OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } } } finally { linesToUpdate.close(); } log4j.debug("Call MRP_Run_Initialize process"); // v_ResultStr:='Initialize'; // MRP_RUN_INITIALIZE(v_User_ID, v_Org_ID, v_Client_ID, v_Record_ID, v_Planner_ID, // v_Product_ID, // v_Product_Category_ID, v_BPartner_ID, v_BP_Group_ID, NULL, v_TimeHorizon, // v_PlanningDate, 'Y'); List<Object> parameters = new ArrayList<Object>(); parameters.add(userId); parameters.add(orgId); parameters.add(clientId); parameters.add(strManufacturingMRPID); parameters.add(plannerId); parameters.add(productId); parameters.add(productCategoryId); parameters.add(bpId); parameters.add(bpCatId); parameters.add(NULL); parameters.add(timeHorizon); parameters.add(docDate); parameters.add("Y"); CallStoredProcedure.getInstance().call("MRP_RUN_INITIALIZE", parameters, null, true, false); OBDal.getInstance().commitAndClose(); OBDal.getInstance().getSession().clear(); log4j.debug("Call MRP_Run_Explode process"); // v_ResultStr:='Explode'; // MRP_RUN_EXPLODE(v_User_ID, v_Org_ID, v_Client_ID, v_Record_ID, v_Planner_ID, v_TimeHorizon, // v_PlanningDate, v_SecurityMargin); parameters = new ArrayList<Object>(); parameters.add(userId); parameters.add(orgId); parameters.add(clientId); parameters.add(strManufacturingMRPID); parameters.add(plannerId); parameters.add(timeHorizon); parameters.add(docDate); parameters.add(safetyLeadTime); CallStoredProcedure.getInstance().call("MRP_RUN_EXPLODE", parameters, null, true, false); OBDal.getInstance().commitAndClose(); OBDal.getInstance().getSession().clear(); log4j.debug("Call MRP_ProcessPlan process"); // v_ResultStr:='ProcessPlan'; // MRP_PROCESSPLAN(v_User_ID, v_Org_ID, v_Client_ID, v_Record_ID, v_Planner_ID, v_TimeHorizon, // v_PlanningDate, v_SecurityMargin); parameters = new ArrayList<Object>(); parameters.add(userId); parameters.add(orgId); parameters.add(clientId); parameters.add(strManufacturingMRPID); parameters.add(plannerId); parameters.add(timeHorizon); parameters.add(docDate); parameters.add(safetyLeadTime); CallStoredProcedure.getInstance().call("MRP_PROCESSPLAN", parameters, null, true, false); OBDal.getInstance().commitAndClose(); OBDal.getInstance().getSession().clear(); } catch (final Exception e) { deleteNotFixedLines(strManufacturingMRPID); log4j.error("Exception found in MRPManufacturingProcess: ", e); msg = OBMessageUtils.translateError(bundle.getConnection(), bundle.getContext().toVars(), OBContext.getOBContext().getLanguage().getLanguage(), e.getMessage()); msg.setTitle(OBMessageUtils.messageBD("Error")); } finally { bundle.setResult(msg); } }
From source file:org.openbravo.erpCommon.ad_process.MRPPurchaseCreateReservations.java
License:Open Source License
@Override public void doExecute(ProcessBundle bundle) throws Exception { // logger = bundle.getLogger(); Map<String, Object> params = bundle.getParams(); String strMRPRunId = (String) params.get("MRP_Run_Purchase_ID"); PurchasingRun mrpPurchaseRun = OBDal.getInstance().get(PurchasingRun.class, strMRPRunId); String strMWarehosueID = (String) params.get("mWarehouseId"); // Execute Create Orders process. OBContext.setAdminMode(true);//from w w w. j a va 2s . co m Process process = null; try { process = OBDal.getInstance().get(Process.class, "800163"); } finally { OBContext.restorePreviousMode(); } Map<String, String> createOrderParams = new HashMap<String, String>(); createOrderParams.put("M_Warehouse_ID", strMWarehosueID); try { final ProcessInstance pinstance = CallProcess.getInstance().call(process, strMRPRunId, createOrderParams); if (pinstance.getResult() == 0L) { OBDal.getInstance().rollbackAndClose(); OBError oberror = OBMessageUtils.getProcessInstanceMessage(pinstance); bundle.setResult(oberror); return; } } catch (Exception e) { OBDal.getInstance().rollbackAndClose(); OBError messsage = OBMessageUtils.translateError(DbUtility.getUnderlyingSQLException(e).getMessage()); bundle.setResult(messsage); return; } // Create reservations ScrollableResults outgoingRLs = getPRLinesOutgoing(mrpPurchaseRun); ScrollableResults incomingRLs = getPRLinesIncoming(mrpPurchaseRun); int i = 1; BigDecimal currentStock = BigDecimal.ZERO; PurchasingRunLine incomingLine = null; String productID = ""; try { while (outgoingRLs.next()) { PurchasingRunLine outgoingLine = (PurchasingRunLine) outgoingRLs.get(0); if (!productID.equals(DalUtil.getId(outgoingLine.getProduct()))) { productID = (String) DalUtil.getId(outgoingLine.getProduct()); currentStock = BigDecimal.ZERO; } BigDecimal quantity = outgoingLine.getQuantity().negate(); boolean isSalesOrderLine = outgoingLine.getSalesOrderLine() != null && outgoingLine.getSalesOrderLine().getSalesOrder().isSalesTransaction(); while (quantity.signum() == 1) { if (currentStock.signum() < 1 && incomingRLs.next()) { incomingLine = (PurchasingRunLine) incomingRLs.get(0); if (!productID.equals(DalUtil.getId(outgoingLine.getProduct())) && incomingRLs.next()) { incomingLine = (PurchasingRunLine) incomingRLs.get(0); } currentStock = currentStock.add(incomingLine.getQuantity()); if (incomingLine.getTransactionType().equals("PP") && incomingLine.getSalesOrderLine() != null) { OBDal.getInstance().refresh(incomingLine.getSalesOrderLine().getSalesOrder()); if (!incomingLine.getSalesOrderLine().getSalesOrder().isProcessed()) { try { processOrder(incomingLine.getSalesOrderLine().getSalesOrder()); } catch (OBException e) { OBDal.getInstance().rollbackAndClose(); OBError error = OBMessageUtils.translateError(e.getMessage()); bundle.setResult(error); return; } } } } BigDecimal consumedQuantity = currentStock.min(quantity); currentStock = currentStock.subtract(consumedQuantity); quantity = quantity.subtract(consumedQuantity); if (isSalesOrderLine) { Reservation reservation = ReservationUtils .getReservationFromOrder(outgoingLine.getSalesOrderLine()); if (reservation.getReservedQty().compareTo(reservation.getQuantity()) == -1) { if (incomingLine.getTransactionType().equals("PP") && incomingLine.getSalesOrderLine() != null) { ReservationUtils.reserveStockManual(reservation, incomingLine.getSalesOrderLine(), consumedQuantity, incomingLine.getSalesOrderLine().getSalesOrder() .getWarehouse().isAllocated() ? "Y" : "N"); } if (quantity.signum() < 1 && reservation.getRESStatus().equals("DR")) { ReservationUtils.processReserve(reservation, "PR"); } } OBDal.getInstance().save(reservation); OBDal.getInstance().flush(); } } if ((i % 100) == 0) { SessionHandler.getInstance().commitAndStart(); OBDal.getInstance().getSession().clear(); } } } finally { incomingRLs.close(); outgoingRLs.close(); } OBError message = new OBError(); message.setType("Success"); message.setTitle(OBMessageUtils.messageBD("Success")); bundle.setResult(message); }
From source file:org.openbravo.erpCommon.utility.WindowTree.java
License:Open Source License
private List<WindowTreeData> applyWhereClause(List<WindowTreeData> subList, String strTabId) { String entityName = null, hqlWhereClause = null; try {//from w w w . ja v a 2s . com OBContext.setAdminMode(); Tab tabData = OBDal.getInstance().get(org.openbravo.model.ad.ui.Tab.class, strTabId); if (tabData != null) { entityName = tabData.getTable().getName(); hqlWhereClause = tabData.getHqlwhereclause(); } } catch (Exception e) { log4j.error("Exception while retrieving hqlWhereClause " + e); } finally { OBContext.restorePreviousMode(); } List<WindowTreeData> newSubList = new ArrayList<WindowTreeData>(); if (hqlWhereClause != null && !hqlWhereClause.trim().isEmpty()) { hqlWhereClause = hqlWhereClause.replaceAll("\\be.", ""); OBQuery<BaseOBObject> entityResults = OBDal.getInstance().createQuery("" + entityName + "", hqlWhereClause); if (nodeIdList == null) { nodeIdList = new ArrayList<String>(); } if (nodeIdList.size() == 0 && nodeIdList.size() != entityResults.count()) { ScrollableResults entityData = entityResults.scroll(ScrollMode.FORWARD_ONLY); int clearEachLoops = 100; int i = 0; try { while (entityData.next()) { i++; BaseOBObject entity = (BaseOBObject) entityData.get()[0]; if (entity.getId() != null) { nodeIdList.add(entity.getId().toString()); } if (i % clearEachLoops == 0) { OBDal.getInstance().getSession().clear(); } } } finally { entityData.close(); } } for (WindowTreeData elem : subList) { if (nodeIdList.contains(elem.nodeId)) { newSubList.add(elem); } } } else { newSubList = subList; } return newSubList; }
From source file:org.openbravo.event.GLJournalEventHandler.java
License:Open Source License
public void onUpdate(@Observes EntityUpdateEvent event) { if (!isValidEvent(event)) { return;//from w w w . ja v a 2s. c o m } final GLJournal glj = (GLJournal) event.getTargetInstance(); // Update GLJournalLine with updated Currency and Currency Rate. final Entity gljournal = ModelProvider.getInstance().getEntity(GLJournal.ENTITY_NAME); final Property currencyProperty = gljournal.getProperty(GLJournal.PROPERTY_CURRENCY); final Property currencyRate = gljournal.getProperty(GLJournal.PROPERTY_RATE); if (!event.getCurrentState(currencyProperty).equals(event.getPreviousState(currencyProperty)) || !event.getCurrentState(currencyRate).equals(event.getPreviousState(currencyRate))) { OBCriteria<GLJournalLine> gljournallineCriteria = OBDal.getInstance() .createCriteria(GLJournalLine.class); gljournallineCriteria.add(Restrictions.eq(GLJournalLine.PROPERTY_JOURNALENTRY, glj)); ScrollableResults scrollLines = gljournallineCriteria.scroll(ScrollMode.FORWARD_ONLY); try { if (gljournallineCriteria.count() > 0) { int i = 0; while (scrollLines.next()) { final GLJournalLine journalLine = (GLJournalLine) scrollLines.get()[0]; if (!glj.getCurrency().getId().equals(journalLine.getCurrency().getId())) { journalLine.setCurrency(glj.getCurrency()); OBDal.getInstance().save(journalLine); } if (!glj.getRate().equals(journalLine.getRate())) { journalLine.setRate(glj.getRate()); OBDal.getInstance().save(journalLine); } i++; if (i % 100 == 0) { OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } } } } finally { scrollLines.close(); } } }
From source file:org.openbravo.event.ProductCharacteristicEventHandler.java
License:Open Source License
private void deleteProductCharacteristicValue(ProductCharacteristic productCharacteristic) { ScrollableResults scroll = null; try {/*from ww w . j a v a2 s .c o m*/ OBCriteria<ProductCharacteristicValue> criteria = OBDal.getInstance() .createCriteria(ProductCharacteristicValue.class); criteria.add(Restrictions.eq(ProductCharacteristicValue.PROPERTY_CHARACTERISTIC, productCharacteristic.getCharacteristic())); criteria.add(Restrictions.eq(ProductCharacteristicValue.PROPERTY_PRODUCT, productCharacteristic.getProduct())); scroll = criteria.scroll(ScrollMode.FORWARD_ONLY); int i = 0; while (scroll.next()) { ProductCharacteristicValue productCharacteristicValue = (ProductCharacteristicValue) scroll.get(0); OBDal.getInstance().remove(productCharacteristicValue); i++; if (i % 100 == 0) { OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } } } finally { scroll.close(); } }
From source file:org.openbravo.event.RoleEventHandler.java
License:Open Source License
private List<RoleOrganization> getRoleOrganizationList(Role role) throws Exception { List<RoleOrganization> roleOrganizationList = new ArrayList<RoleOrganization>(); // Client or System level: Only * [isOrgAdmin=N] if (StringUtils.equals(role.getUserLevel(), " C") || StringUtils.equals(role.getUserLevel(), "S")) { roleOrganizationList//from ww w. j a v a 2 s . c o m .add(getRoleOrganization(role, OBDal.getInstance().get(Organization.class, "0"), false)); logger.debug("Added organization * to role " + role.getName()); } // Client/Organization level: * [isOrgAdmin=N], other Orgs (but *) [isOrgAdmin=Y] else if (StringUtils.equals(role.getUserLevel(), " CO")) { roleOrganizationList .add(getRoleOrganization(role, OBDal.getInstance().get(Organization.class, "0"), false)); logger.debug("Added organization * to role " + role.getName()); OBCriteria<Organization> criteria = OBDal.getInstance().createCriteria(Organization.class); criteria.add(Restrictions.eq(Organization.PROPERTY_CLIENT, role.getClient())); criteria.add(Restrictions.ne(Organization.PROPERTY_ID, "0")); ScrollableResults scroll = criteria.scroll(ScrollMode.FORWARD_ONLY); try { while (scroll.next()) { final Organization organization = (Organization) scroll.get()[0]; roleOrganizationList.add(getRoleOrganization(role, organization, true)); logger.debug("Added organization " + organization.getName() + " to role " + role.getName()); } } finally { scroll.close(); } } // Organization level: Orgs (but *) [isOrgAdmin=Y] else if (StringUtils.equals(role.getUserLevel(), " O")) { OBCriteria<Organization> criteria = OBDal.getInstance().createCriteria(Organization.class); criteria.add(Restrictions.eq(Organization.PROPERTY_CLIENT, role.getClient())); ScrollableResults scroll = criteria.scroll(ScrollMode.FORWARD_ONLY); try { while (scroll.next()) { final Organization organization = (Organization) scroll.get()[0]; roleOrganizationList.add(getRoleOrganization(role, organization, true)); logger.debug("Added organization " + organization.getName() + " to role " + role.getName()); } } finally { scroll.close(); } } return roleOrganizationList; }
From source file:org.openbravo.financial.paymentreport.erpCommon.ad_reports.PaymentReportDao.java
License:Open Source License
FieldProvider[] getPaymentReport(VariablesSecureApp vars, String strOrg, String strInclSubOrg,
String strDueDateFrom, String strDueDateTo, String strAmountFrom, String strAmountTo,
String strDocumentDateFrom, String strDocumentDateTo, String strcBPartnerIdIN, String strcBPGroupIdIN,
String strcNoBusinessPartner, String strcProjectIdIN, String strfinPaymSt, String strPaymentMethodId,
String strFinancialAccountId, String strcCurrency, String strConvertCurrency, String strConversionDate,
String strPaymType, String strOverdue, String strGroupCrit, String strOrdCrit,
String strInclPaymentUsingCredit, String strPaymentDateFrom, String strPaymentDateTo,
String strExpectedDateFrom, String strExpectedDateTo, String strOutput) throws OBException {
StringBuilder hsqlScript = new StringBuilder();
final java.util.List<Object> parameters = new ArrayList<Object>();
String dateFormatString = OBPropertiesProvider.getInstance().getOpenbravoProperties()
.getProperty("dateFormat.java");
SimpleDateFormat dateFormat = new SimpleDateFormat(dateFormatString);
Currency transCurrency;/*from ww w .j a v a 2 s .co m*/
BigDecimal transAmount = null;
ConversionRate convRate = null;
ArrayList<FieldProvider> totalData = new ArrayList<FieldProvider>();
int numberOfElements = 0;
int lastElement = 0;
boolean existsConvRate = false;
ScrollableResults scroller = null;
OBContext.setAdminMode(true);
try {
hsqlScript.append(" from FIN_Payment_ScheduleDetail as fpsd ");
hsqlScript.append(" left outer join fpsd.paymentDetails.finPayment pay");
hsqlScript.append(" left outer join pay.businessPartner paybp");
hsqlScript.append(" left outer join paybp.businessPartnerCategory paybpc");
hsqlScript.append(" left outer join fpsd.invoicePaymentSchedule invps");
hsqlScript.append(" left outer join invps.invoice inv");
hsqlScript.append(" left outer join inv.businessPartner invbp");
hsqlScript.append(" left outer join invbp.businessPartnerCategory invbpc");
hsqlScript.append(" left outer join fpsd.paymentDetails.finPayment.currency paycur");
hsqlScript.append(" left outer join fpsd.invoicePaymentSchedule.invoice.currency invcur");
hsqlScript.append(" left outer join pay.project paypro");
hsqlScript.append(" left outer join inv.project invpro");
hsqlScript.append(" where (fpsd.");
hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_PAYMENTDETAILS);
hsqlScript.append(" is not null or invps is not null ");
hsqlScript.append(") ");
hsqlScript.append(" and fpsd.");
hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_ORGANIZATION);
hsqlScript.append(".id in ");
hsqlScript.append(concatOrganizations(OBContext.getOBContext().getReadableOrganizations()));
// organization + include sub-organization
if (!strOrg.isEmpty()) {
if (!strInclSubOrg.equalsIgnoreCase("include")) {
hsqlScript.append(" and fpsd.");
hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_ORGANIZATION);
hsqlScript.append(".id = '");
hsqlScript.append(strOrg);
hsqlScript.append("'");
} else {
hsqlScript.append(" and fpsd.");
hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_ORGANIZATION);
hsqlScript.append(".id in ('");
Set<String> orgChildTree = OBContext.getOBContext().getOrganizationStructureProvider()
.getChildTree(strOrg, true);
Iterator<String> orgChildTreeIter = orgChildTree.iterator();
while (orgChildTreeIter.hasNext()) {
hsqlScript.append(orgChildTreeIter.next());
orgChildTreeIter.remove();
hsqlScript.append("'");
if (orgChildTreeIter.hasNext())
hsqlScript.append(", '");
}
hsqlScript.append(")");
}
}
// Exclude payments that use credit payment
if (!strInclPaymentUsingCredit.equalsIgnoreCase("Y")) {
hsqlScript.append(" and (not (pay.amount = 0 ");
hsqlScript.append(" and pay.usedCredit > pay.generatedCredit) or pay is null)");
}
// due date from - due date to
if (!strDueDateFrom.isEmpty()) {
hsqlScript.append(" and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
hsqlScript.append(" >= ?");
parameters.add(FIN_Utility.getDate(strDueDateFrom));
}
if (!strDueDateTo.isEmpty()) {
hsqlScript.append(" and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
hsqlScript.append(" <= ?");
parameters.add(FIN_Utility.getDate(strDueDateTo));
}
// expected date from - expected date to
if (!strExpectedDateFrom.isEmpty()) {
hsqlScript.append(" and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
hsqlScript.append(" >= ?");
parameters.add(FIN_Utility.getDate(strExpectedDateFrom));
}
if (!strExpectedDateTo.isEmpty()) {
hsqlScript.append(" and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
hsqlScript.append(" <= ?");
parameters.add(FIN_Utility.getDate(strExpectedDateTo));
}
// document date from - document date to
if (!strDocumentDateFrom.isEmpty()) {
hsqlScript.append(" and coalesce(inv.");
hsqlScript.append(Invoice.PROPERTY_INVOICEDATE);
hsqlScript.append(", pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
hsqlScript.append(") >= ?");
parameters.add(FIN_Utility.getDate(strDocumentDateFrom));
}
if (!strDocumentDateTo.isEmpty()) {
hsqlScript.append(" and coalesce(inv.");
hsqlScript.append(Invoice.PROPERTY_INVOICEDATE);
hsqlScript.append(", pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
hsqlScript.append(") <= ?");
parameters.add(FIN_Utility.getDate(strDocumentDateTo));
}
// payment date from - payment date to
if (!strPaymentDateFrom.isEmpty()) {
hsqlScript.append(" and ((pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
hsqlScript.append(" >= ?) or (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
hsqlScript.append(" is null and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
hsqlScript.append(" >= ?))");
parameters.add(FIN_Utility.getDate(strPaymentDateFrom));
parameters.add(FIN_Utility.getDate(strPaymentDateFrom));
}
if (!strPaymentDateTo.isEmpty()) {
hsqlScript.append(" and coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTDATE);
hsqlScript.append(", invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_EXPECTEDDATE);
hsqlScript.append(") <= ?");
parameters.add(FIN_Utility.getDate(strPaymentDateTo));
}
// Empty Business Partner included
if (strcNoBusinessPartner.equals("include")) {
// business partner
if (!strcBPartnerIdIN.isEmpty()) {
hsqlScript.append(" and ((coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(", inv.");
hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(") in ");
hsqlScript.append(strcBPartnerIdIN);
hsqlScript.append(") or (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is null and inv.");
hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is null))");
}
// business partner category
if (!strcBPGroupIdIN.isEmpty()) {
hsqlScript.append(" and (coalesce(paybpc, invbpc) = '");
hsqlScript.append(strcBPGroupIdIN);
hsqlScript.append("' or (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is null and inv.");
hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is null))");
}
// Empty Businesss Partner excluded
} else if (strcNoBusinessPartner.equals("exclude")) {
// business partner
if (!strcBPartnerIdIN.isEmpty()) {
hsqlScript.append(" and coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(", inv.");
hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(") in ");
hsqlScript.append(strcBPartnerIdIN);
}
// business partner category
if (!strcBPGroupIdIN.isEmpty()) {
hsqlScript.append(" and coalesce(paybpc, invbpc) = '");
hsqlScript.append(strcBPGroupIdIN);
hsqlScript.append("'");
}
// exclude empty business partner
if (strcBPartnerIdIN.isEmpty() && strcBPGroupIdIN.isEmpty()) {
hsqlScript.append(" and (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is not null or inv.");
hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is not null) ");
}
// Only Empty Business Partner
} else {// if ((strcNoBusinessPartner.equals("only")))
hsqlScript.append(" and pay.");
hsqlScript.append(FIN_Payment.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is null and inv.");
hsqlScript.append(Invoice.PROPERTY_BUSINESSPARTNER);
hsqlScript.append(" is null ");
}
// project
if (!strcProjectIdIN.isEmpty()) {
hsqlScript.append(" and coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PROJECT);
hsqlScript.append(", inv.");
hsqlScript.append(Invoice.PROPERTY_PROJECT);
hsqlScript.append(") in ");
hsqlScript.append(strcProjectIdIN);
}
// status
if (!strfinPaymSt.isEmpty() && !strfinPaymSt.equalsIgnoreCase("('')")) {
hsqlScript.append(" and (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_STATUS);
hsqlScript.append(" in ");
hsqlScript.append(strfinPaymSt);
if (strfinPaymSt.contains("RPAP")) {
hsqlScript.append(" or fpsd.");
hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_PAYMENTDETAILS);
hsqlScript.append(" is null)");
} else {
hsqlScript.append(" )");
}
}
// payment method
if (!strPaymentMethodId.isEmpty()) {
hsqlScript.append(" and coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_PAYMENTMETHOD);
hsqlScript.append(", invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_FINPAYMENTMETHOD);
hsqlScript.append(") = '");
hsqlScript.append(strPaymentMethodId);
hsqlScript.append("'");
}
// financial account
if (!strFinancialAccountId.isEmpty()) {
hsqlScript.append(" and (pay is not null and ");
hsqlScript.append(
"(select case when trans is not null then trans.account.id else payment.account.id end from FIN_Finacc_Transaction trans right outer join trans.finPayment payment where payment=pay)");
hsqlScript.append(" = '");
hsqlScript.append(strFinancialAccountId);
hsqlScript.append("' or ((pay is null and inv.");
hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
hsqlScript.append(" = 'Y'");
hsqlScript.append(" and invbp.");
hsqlScript.append(BusinessPartner.PROPERTY_ACCOUNT);
hsqlScript.append(".id = '");
hsqlScript.append(strFinancialAccountId);
hsqlScript.append("')");
hsqlScript.append(" or (pay is null and inv.");
hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
hsqlScript.append(" = 'N'");
hsqlScript.append(" and invbp.");
hsqlScript.append(BusinessPartner.PROPERTY_POFINANCIALACCOUNT);
hsqlScript.append(".id = '");
hsqlScript.append(strFinancialAccountId);
hsqlScript.append("')))");
}
// currency
if (!strcCurrency.isEmpty()) {
hsqlScript.append(" and coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_CURRENCY);
hsqlScript.append(", inv.");
hsqlScript.append(Invoice.PROPERTY_CURRENCY);
hsqlScript.append(") = '");
hsqlScript.append(strcCurrency);
hsqlScript.append("'");
}
// payment type
if (strPaymType.equalsIgnoreCase("FINPR_Receivables")) {
hsqlScript.append(" and (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_RECEIPT);
hsqlScript.append(" = 'Y'");
hsqlScript.append(" or inv.");
hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
hsqlScript.append(" = 'Y')");
} else if (strPaymType.equalsIgnoreCase("FINPR_Payables")) {
hsqlScript.append(" and (pay.");
hsqlScript.append(FIN_Payment.PROPERTY_RECEIPT);
hsqlScript.append(" = 'N'");
hsqlScript.append(" or inv.");
hsqlScript.append(Invoice.PROPERTY_SALESTRANSACTION);
hsqlScript.append(" = 'N')");
}
// overdue
if (!strOverdue.isEmpty()) {
hsqlScript.append(" and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_OUTSTANDINGAMOUNT);
hsqlScript.append(" != '0'");
hsqlScript.append(" and invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
hsqlScript.append(" < ?");
parameters.add(DateUtils.truncate(new Date(), Calendar.DATE));
}
if ("HTML".equals(strOutput)) {
int maxRecords = 1000;
final Session sessionCount = OBDal.getInstance().getSession();
final Query queryCount = sessionCount.createQuery("select count(*)" + hsqlScript.toString());
int px = 0;
for (final Object param : parameters) {
if (param instanceof BaseOBObject) {
queryCount.setEntity(px++, param);
} else {
queryCount.setParameter(px++, param);
}
}
final Long hqlRecordsCount = (Long) queryCount.list().get(0);
if ((int) (long) hqlRecordsCount > maxRecords) {
String message = "FINPR_TooManyRecords";
throw new OBException(message);
}
}
final StringBuilder firstLineQuery = new StringBuilder();
firstLineQuery.append(
"select fpsd, (select a.sequenceNumber from ADList a where a.reference.id = '575BCB88A4694C27BC013DE9C73E6FE7' and a.searchKey = coalesce(pay.status, 'RPAP')) as a");
hsqlScript = firstLineQuery.append(hsqlScript);
hsqlScript.append(" order by ");
if (strGroupCrit.equalsIgnoreCase("APRM_FATS_BPARTNER")) {
hsqlScript.append(" coalesce(paybp.");
hsqlScript.append(BusinessPartner.PROPERTY_NAME);
hsqlScript.append(", invbp.");
hsqlScript.append(BusinessPartner.PROPERTY_NAME);
hsqlScript.append("), ");
} else if (strGroupCrit.equalsIgnoreCase("Project")) {
hsqlScript.append(" coalesce(paypro.");
hsqlScript.append(Project.PROPERTY_NAME);
hsqlScript.append(", invpro.");
hsqlScript.append(Project.PROPERTY_NAME);
hsqlScript.append("), ");
} else if (strGroupCrit.equalsIgnoreCase("FINPR_BPartner_Category")) {
hsqlScript.append(" coalesce(paybpc.");
hsqlScript.append(Category.PROPERTY_NAME);
hsqlScript.append(", invbpc.");
hsqlScript.append(Category.PROPERTY_NAME);
hsqlScript.append("), ");
} else if (strGroupCrit.equalsIgnoreCase("INS_CURRENCY")) {
hsqlScript.append(" coalesce(paycur.");
hsqlScript.append(Currency.PROPERTY_ISOCODE);
hsqlScript.append(", invcur.");
hsqlScript.append(Currency.PROPERTY_ISOCODE);
hsqlScript.append("), ");
} else if (strGroupCrit.equalsIgnoreCase("ACCS_ACCOUNT_ID_D")) {
hsqlScript.append(" coalesce(");
hsqlScript.append(
" (select trans.account.name from FIN_Finacc_Transaction trans left outer join trans.finPayment payment where payment.id=pay.id),");
hsqlScript.append(" pay.");
hsqlScript.append(FIN_Payment.PROPERTY_ACCOUNT);
hsqlScript.append(".name, 'Awaiting Payment'");
hsqlScript.append("), ");
}
hsqlScript.append(" a, coalesce(pay.");
hsqlScript.append(FIN_Payment.PROPERTY_STATUS);
hsqlScript.append(", 'RPAP')");
if (!strOrdCrit.isEmpty()) {
String[] strOrdCritList = strOrdCrit.substring(2, strOrdCrit.length() - 2).split("', '");
for (int i = 0; i < strOrdCritList.length; i++) {
if (strOrdCritList[i].equalsIgnoreCase("Date")) {
hsqlScript.append(", inv.");
hsqlScript.append(Invoice.PROPERTY_INVOICEDATE);
}
if (strOrdCritList[i].contains("Project")) {
hsqlScript.append(", coalesce(paypro.");
hsqlScript.append(Project.PROPERTY_NAME);
hsqlScript.append(", invpro.");
hsqlScript.append(Project.PROPERTY_NAME);
hsqlScript.append(")");
}
if (strOrdCritList[i].contains("FINPR_BPartner_Category")) {
hsqlScript.append(", coalesce(paybpc.");
hsqlScript.append(Category.PROPERTY_NAME);
hsqlScript.append(", invbpc.");
hsqlScript.append(Category.PROPERTY_NAME);
hsqlScript.append(")");
}
if (strOrdCritList[i].contains("APRM_FATS_BPARTNER")) {
hsqlScript.append(", coalesce(paybp.");
hsqlScript.append(BusinessPartner.PROPERTY_NAME);
hsqlScript.append(", invbp.");
hsqlScript.append(BusinessPartner.PROPERTY_NAME);
hsqlScript.append(")");
}
if (strOrdCritList[i].contains("INS_CURRENCY")) {
hsqlScript.append(", coalesce(paycur.");
hsqlScript.append(Currency.PROPERTY_ISOCODE);
hsqlScript.append(", invcur.");
hsqlScript.append(Currency.PROPERTY_ISOCODE);
hsqlScript.append(")");
}
if (strOrdCritList[i].equalsIgnoreCase("ACCS_ACCOUNT_ID_D")) {
hsqlScript.append(", coalesce(");
hsqlScript.append(
" (select trans.account.name from FIN_Finacc_Transaction trans left outer join trans.finPayment payment where payment.id=pay.id),");
hsqlScript.append(" pay.");
hsqlScript.append(FIN_Payment.PROPERTY_ACCOUNT);
hsqlScript.append(".name)");
}
if (strOrdCritList[i].equalsIgnoreCase("DueDate")) {
hsqlScript.append(", invps.");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE);
}
}
}
hsqlScript.append(", fpsd.");
hsqlScript.append(FIN_PaymentScheduleDetail.PROPERTY_INVOICEPAYMENTSCHEDULE);
hsqlScript.append(".");
hsqlScript.append(FIN_PaymentSchedule.PROPERTY_ID);
final Session session = OBDal.getInstance().getSession();
final Query query = session.createQuery(hsqlScript.toString());
int pos = 0;
for (final Object param : parameters) {
if (param instanceof BaseOBObject) {
query.setEntity(pos++, param);
} else {
query.setParameter(pos++, param);
}
}
scroller = query.scroll(ScrollMode.FORWARD_ONLY);
FIN_PaymentDetail finPaymDetail;
Boolean mustGroup;
String previousFPSDInvoiceId = null;
String previousPaymentId = null;
BigDecimal amountSum = BigDecimal.ZERO;
BigDecimal balanceSum = BigDecimal.ZERO;
FieldProvider previousRow = null;
FieldProvider lastGroupedDatarow = null;
ConversionRate previousConvRate = null;
boolean isReceipt = false;
boolean isAmtInLimit = false;
// Before processing the data the Transactions without a Payment associated are recovered
java.util.List<FIN_FinaccTransaction> transactionsList = getTransactionsList(strInclSubOrg, strOrg,
strcBPartnerIdIN, strFinancialAccountId, strDocumentDateFrom, strDocumentDateTo,
strPaymentDateFrom, strPaymentDateTo, strAmountFrom, strAmountTo, strcBPGroupIdIN,
strcProjectIdIN, strfinPaymSt, strcCurrency, strPaymType, strGroupCrit, strOrdCrit,
strcNoBusinessPartner, strDueDateFrom, strDueDateTo, strExpectedDateFrom, strExpectedDateTo);
// There are three variables involved in this loop. The first one is data, wich is the
// the one the loop processes. Then grouped data is used to group similar data lines into
// one. Finally total data adds the remaining information that is not in data.
int i = 0;
while (scroller.next()) {
i++;
// get 1st column (idx=0)
Object value = scroller.get(0);
// TODO: rename variable to not have same name as class
FIN_PaymentScheduleDetail FIN_PaymentScheduleDetail = (FIN_PaymentScheduleDetail) value;
// make a empty FieldProvider instead of saving link to DAL-object
FieldProvider data = FieldProviderFactory.getFieldProvider(null);
if (i % 100 == 0) {
OBDal.getInstance().getSession().clear();
}
OBDal.getInstance().getSession().buildLockRequest(LockOptions.NONE)
.lock(FIN_PaymentScheduleDetail.ENTITY_NAME, FIN_PaymentScheduleDetail);
// search for fin_finacc_transaction for this payment
FIN_FinaccTransaction trx = null;
FIN_PaymentDetail detail = FIN_PaymentScheduleDetail.getPaymentDetails();
if (detail != null) {
OBCriteria<FIN_FinaccTransaction> trxQuery = OBDal.getInstance()
.createCriteria(FIN_FinaccTransaction.class);
trxQuery.add(
Restrictions.eq(FIN_FinaccTransaction.PROPERTY_FINPAYMENT, detail.getFinPayment()));
// uniqueness guaranteed via unique constraint in db
trx = (FIN_FinaccTransaction) trxQuery.uniqueResult();
}
// If the payment schedule detail has a payment detail, then, the information is taken from
// the payment. If not, the information is taken from the invoice (the else).
if (FIN_PaymentScheduleDetail.getPaymentDetails() != null) {
BusinessPartner bp = getDocumentBusinessPartner(FIN_PaymentScheduleDetail);
if (bp == null) {
FieldProviderFactory.setField(data, "BP_GROUP", "");
FieldProviderFactory.setField(data, "BPARTNER", "");
} else {
// bp_group -- bp_category
FieldProviderFactory.setField(data, "BP_GROUP", bp.getBusinessPartnerCategory().getName());
// bpartner
FieldProviderFactory.setField(data, "BPARTNER", bp.getName());
}
// transCurrency
transCurrency = FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getCurrency();
FieldProviderFactory.setField(data, "TRANS_CURRENCY", transCurrency.getISOCode());
// paymentMethod
FieldProviderFactory.setField(data, "PAYMENT_METHOD", FIN_PaymentScheduleDetail
.getPaymentDetails().getFinPayment().getPaymentMethod().getIdentifier());
// payment
FieldProviderFactory.setField(data, "PAYMENT",
((FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
.getPaymentDate() != null)
? dateFormat.format(FIN_PaymentScheduleDetail.getPaymentDetails()
.getFinPayment().getPaymentDate())
: "Null")
+ " - " + FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
.getDocumentNo());
// payment description
FieldProviderFactory.setField(data, "PAYMENT_DESC",
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getDescription());
// payment_id
FieldProviderFactory.setField(data, "PAYMENT_ID",
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getId().toString());
// payment_date
FieldProviderFactory.setField(data, "PAYMENT_DATE",
(FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getPaymentDate() != null)
? dateFormat.format(FIN_PaymentScheduleDetail
.getPaymentDetails().getFinPayment().getPaymentDate())
: "Null");
// payment_docNo
FieldProviderFactory.setField(data, "PAYMENT_DOCNO",
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getDocumentNo());
// payment yes / no
FieldProviderFactory.setField(data, "PAYMENT_Y_N", "");
// financialAccount
FieldProviderFactory.setField(data, "FINANCIAL_ACCOUNT",
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
.getFINFinaccTransactionList().size() != 0
? FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
.getFINFinaccTransactionList().get(0).getAccount().getName()
: FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment()
.getAccount().getName());
// status
FieldProviderFactory.setField(data, "STATUS", translateRefList(
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getStatus()));
FieldProviderFactory.setField(data, "STATUS_CODE",
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getStatus());
// is receipt
if (FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().isReceipt()) {
FieldProviderFactory.setField(data, "ISRECEIPT", "Y");
isReceipt = true;
} else {
FieldProviderFactory.setField(data, "ISRECEIPT", "N");
isReceipt = false;
}
// deposit/withdraw date
if (trx != null) {
FieldProviderFactory.setField(data, "DEPOSIT_WITHDRAW_DATE",
dateFormat.format(trx.getTransactionDate()));
} else {
FieldProviderFactory.setField(data, "DEPOSIT_WITHDRAW_DATE", "");
}
} else {
// bp_group -- bp_category
FieldProviderFactory.setField(data, "BP_GROUP",
FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getInvoice().getBusinessPartner()
.getBusinessPartnerCategory().getName());
// bpartner
FieldProviderFactory.setField(data, "BPARTNER", FIN_PaymentScheduleDetail
.getInvoicePaymentSchedule().getInvoice().getBusinessPartner().getName());
// transCurrency
transCurrency = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getInvoice()
.getCurrency();
FieldProviderFactory.setField(data, "TRANS_CURRENCY", transCurrency.getISOCode());
// paymentMethod
FieldProviderFactory.setField(data, "PAYMENT_METHOD", FIN_PaymentScheduleDetail
.getInvoicePaymentSchedule().getFinPaymentmethod().getIdentifier());
// payment
FieldProviderFactory.setField(data, "PAYMENT", "");
// payment_id
FieldProviderFactory.setField(data, "PAYMENT_ID", "");
// payment_date
FieldProviderFactory.setField(data, "PAYMENT_DATE", "");
// payment_docNo
FieldProviderFactory.setField(data, "PAYMENT_DOCNO", "");
// payment yes / no
FieldProviderFactory.setField(data, "PAYMENT_Y_N", "Display:None");
// financialAccount
FieldProviderFactory.setField(data, "FINANCIAL_ACCOUNT", "");
// status
FieldProviderFactory.setField(data, "STATUS", translateRefList("RPAP"));
FieldProviderFactory.setField(data, "STATUS_CODE", "RPAP");
// is receipt
if (FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getInvoice().isSalesTransaction()) {
FieldProviderFactory.setField(data, "ISRECEIPT", "Y");
isReceipt = true;
} else {
FieldProviderFactory.setField(data, "ISRECEIPT", "N");
isReceipt = false;
}
// deposit/withdraw date
FieldProviderFactory.setField(data, "DEPOSIT_WITHDRAW_DATE", "");
}
/*
* - If the payment schedule detail has an invoice, the line is filled normally.
*
* - If it has a payment it does not have an invoice or it should have entered the first if,
* thus, it is a credit payment. If it is a credit payment, it is checked whether it pays
* one or multiple invoices. If it is one, the information of that invoice is provided. If
* not, it is filled with '**'.
*
* - Otherwise, it is filled empty.
*/
if (FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() != null) {
fillLine(dateFormat, data, FIN_PaymentScheduleDetail,
FIN_PaymentScheduleDetail.getInvoicePaymentSchedule(), false);
} else if (FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment() != null) {
java.util.List<Invoice> invoices = getInvoicesUsingCredit(
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment());
if (invoices.size() == 1) {
java.util.List<FIN_PaymentSchedule> ps = getInvoicePaymentSchedules(
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment());
fillLine(dateFormat, data, FIN_PaymentScheduleDetail, ps.get(0), true);
} else {
// project
FieldProviderFactory.setField(data, "PROJECT", "");
// salesPerson
FieldProviderFactory.setField(data, "SALES_PERSON", "");
// invoiceNumber.
FieldProviderFactory.setField(data, "INVOICE_NUMBER",
invoices.size() > 1 ? "**" + getInvoicesDocNos(invoices) : "");
// payment plan id
FieldProviderFactory.setField(data, "PAYMENT_PLAN_ID", "");
// payment plan yes / no
FieldProviderFactory.setField(data, "PAYMENT_PLAN_Y_N",
invoices.size() != 1 ? "Display:none" : "");
// payment plan yes / no
FieldProviderFactory.setField(data, "NOT_PAYMENT_PLAN_Y_N",
invoices.size() > 1 ? "" : "Display:none");
// invoiceDate
FieldProviderFactory.setField(data, "INVOICE_DATE", "");
// dueDate.
FieldProviderFactory.setField(data, "DUE_DATE", "");
// expectedDate.
FieldProviderFactory.setField(data, "EXPECTED_DATE", "");
// plannedDSO
FieldProviderFactory.setField(data, "PLANNED_DSO", "0");
// currentDSO
FieldProviderFactory.setField(data, "CURRENT_DSO", "0");
// daysOverdue
FieldProviderFactory.setField(data, "OVERDUE", "0");
}
} else {
// project
FieldProviderFactory.setField(data, "PROJECT", "");
// salesPerson
FieldProviderFactory.setField(data, "SALES_PERSON", "");
// invoiceNumber.
FieldProviderFactory.setField(data, "INVOICE_NUMBER", "");
// payment plan id
FieldProviderFactory.setField(data, "PAYMENT_PLAN_ID", "");
// payment plan yes / no
FieldProviderFactory.setField(data, "PAYMENT_PLAN_Y_N", "Display:none");
// payment plan yes / no
FieldProviderFactory.setField(data, "NOT_PAYMENT_PLAN_Y_N", "Display:none");
// invoiceDate
FieldProviderFactory.setField(data, "INVOICE_DATE", "");
// dueDate.
FieldProviderFactory.setField(data, "DUE_DATE", "");
// expectedDate.
FieldProviderFactory.setField(data, "EXPECTED_DATE", "");
// plannedDSO
FieldProviderFactory.setField(data, "PLANNED_DSO", "0");
// currentDSO
FieldProviderFactory.setField(data, "CURRENT_DSO", "0");
// daysOverdue
FieldProviderFactory.setField(data, "OVERDUE", "0");
}
// transactional and base amounts
transAmount = FIN_PaymentScheduleDetail.getAmount();
Currency baseCurrency = OBDal.getInstance().get(Currency.class, strConvertCurrency);
boolean sameCurrency = baseCurrency.getISOCode().equalsIgnoreCase(transCurrency.getISOCode());
if (!sameCurrency) {
convRate = this.getConversionRate(transCurrency, baseCurrency, strConversionDate);
if (convRate != null) {
final int stdPrecission = convRate.getToCurrency().getStandardPrecision().intValue();
if (isReceipt) {
FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.toString());
FieldProviderFactory.setField(data, "BASE_AMOUNT",
transAmount.multiply(convRate.getMultipleRateBy())
.setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).toString());
} else {
FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.negate().toString());
FieldProviderFactory.setField(data, "BASE_AMOUNT",
transAmount.multiply(convRate.getMultipleRateBy())
.setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).negate().toString());
}
} else {
String message = transCurrency.getISOCode() + " -> " + baseCurrency.getISOCode() + " "
+ strConversionDate;
throw new OBException(message);
}
} else {
convRate = null;
if (isReceipt) {
FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.toString());
FieldProviderFactory.setField(data, "BASE_AMOUNT", transAmount.toString());
} else {
FieldProviderFactory.setField(data, "TRANS_AMOUNT", transAmount.negate().toString());
FieldProviderFactory.setField(data, "BASE_AMOUNT", transAmount.negate().toString());
}
}
// currency
FieldProviderFactory.setField(data, "BASE_CURRENCY", baseCurrency.getISOCode());
// baseCurrency
FieldProviderFactory.setField(data, "TRANS_CURRENCY", transCurrency.getISOCode());
// Balance
String status = "RPAE";
try {
status = FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getStatus();
} catch (NullPointerException e) {
}
final boolean isCreditPayment = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() == null
&& FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment() != null;
BigDecimal balance = BigDecimal.ZERO;
if (isCreditPayment && status != null && "PWNC RPR RPPC PPM RDNC".indexOf(status) >= 0) {
balance = FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getGeneratedCredit()
.subtract(
FIN_PaymentScheduleDetail.getPaymentDetails().getFinPayment().getUsedCredit());
if (isReceipt) {
balance = balance.negate();
}
} else if (!isCreditPayment && status != null
&& "PWNC RPR RPPC PPM RDNC RPVOID".indexOf(status) == -1) {
balance = isReceipt ? transAmount : transAmount.negate();
}
if (convRate != null) {
final int stdPrecission = convRate.getToCurrency().getStandardPrecision().intValue();
balance = balance.multiply(convRate.getMultipleRateBy()).setScale(stdPrecission,
BigDecimal.ROUND_HALF_UP);
}
FieldProviderFactory.setField(data, "BALANCE", balance.toString());
finPaymDetail = FIN_PaymentScheduleDetail.getPaymentDetails();
// Payment Schedule Detail grouping criteria
if (finPaymDetail != null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() != null) {
mustGroup = finPaymDetail.getFinPayment().getId().equalsIgnoreCase(previousPaymentId)
&& FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getId()
.equalsIgnoreCase(previousFPSDInvoiceId);
previousFPSDInvoiceId = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getId();
previousPaymentId = finPaymDetail.getFinPayment().getId();
} else if (finPaymDetail != null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() == null) {
mustGroup = finPaymDetail.getFinPayment().getId().equalsIgnoreCase(previousPaymentId)
&& previousFPSDInvoiceId == null;
previousPaymentId = finPaymDetail.getFinPayment().getId();
previousFPSDInvoiceId = null;
} else if (finPaymDetail == null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule() != null) {
mustGroup = previousPaymentId == null && FIN_PaymentScheduleDetail.getInvoicePaymentSchedule()
.getId().equalsIgnoreCase(previousFPSDInvoiceId);
previousPaymentId = null;
previousFPSDInvoiceId = FIN_PaymentScheduleDetail.getInvoicePaymentSchedule().getId();
} else {
mustGroup = false;
}
if (mustGroup) {
amountSum = amountSum.add(transAmount);
balanceSum = balanceSum.add(balance);
} else {
if (previousRow != null) {
// The current row has nothing to do with the previous one. Because of that, the
// previous row has to be added to grouped data.
if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.toString());
else
FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT",
amountSum.negate().toString());
FieldProviderFactory.setField(previousRow, "BALANCE", balanceSum.toString());
if (previousConvRate == null) {
if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.toString());
else
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
amountSum.negate().toString());
} else {
final int stdPrecission = previousConvRate.getToCurrency().getStandardPrecision()
.intValue();
if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
amountSum.multiply(previousConvRate.getMultipleRateBy())
.setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).toString());
else
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
amountSum.multiply(previousConvRate.getMultipleRateBy())
.setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).negate()
.toString());
}
if (strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
isAmtInLimit = true;
} else if (!strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
.parseDouble(strAmountFrom);
} else if (strAmountFrom.isEmpty() && !strAmountTo.isEmpty()) {
isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
.parseDouble(strAmountTo);
} else {
isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
.parseDouble(strAmountFrom)
&& Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
.parseDouble(strAmountTo);
}
if (isAmtInLimit) {
lastGroupedDatarow = previousRow;
isAmtInLimit = false;
numberOfElements++;
}
}
previousRow = data;
previousConvRate = convRate;
amountSum = transAmount;
balanceSum = balance;
}
// group_crit_id this is the column that has the ids of the grouping criteria selected
if (strGroupCrit.equalsIgnoreCase("APRM_FATS_BPARTNER")) {
FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", previousRow.getField("BPARTNER"));
FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Business Partner");
} else if (strGroupCrit.equalsIgnoreCase("Project")) {
FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", previousRow.getField("PROJECT"));
FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Project");
} else if (strGroupCrit.equalsIgnoreCase("FINPR_BPartner_Category")) {
FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", previousRow.getField("BP_GROUP"));
FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Business Partner Category");
} else if (strGroupCrit.equalsIgnoreCase("INS_CURRENCY")) {
FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID",
previousRow.getField("TRANS_CURRENCY"));
FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Currency");
} else if (strGroupCrit.equalsIgnoreCase("ACCS_ACCOUNT_ID_D")) {
FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID",
previousRow.getField("FINANCIAL_ACCOUNT"));
FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Financial Account");
} else {
FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", "");
}
// Insert the transactions without payment if necessary
if (lastElement != numberOfElements) {
if (transactionsList.size() > 0) {
try {
existsConvRate = insertIntoTotal(lastGroupedDatarow, transactionsList, totalData,
strGroupCrit, strOrdCrit, strConvertCurrency, strConversionDate);
} catch (OBException e) {
// If there is no conversion rate
throw e;
}
}
totalData.add(lastGroupedDatarow);
lastElement++;
}
}
if (previousRow != null) {
// The current row has nothing to do with the previous one. Because of that, the
// previous row has to be added to grouped data.
if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.toString());
else
FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.negate().toString());
FieldProviderFactory.setField(previousRow, "BALANCE", balanceSum.toString());
if (previousConvRate == null) {
if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.toString());
else
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.negate().toString());
} else {
final int stdPrecission = previousConvRate.getToCurrency().getStandardPrecision().intValue();
if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y"))
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
amountSum.multiply(previousConvRate.getMultipleRateBy())
.setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).toString());
else
FieldProviderFactory.setField(previousRow, "BASE_AMOUNT",
amountSum.multiply(previousConvRate.getMultipleRateBy())
.setScale(stdPrecission, BigDecimal.ROUND_HALF_UP).negate().toString());
}
if (strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
isAmtInLimit = true;
} else if (!strAmountFrom.isEmpty() && strAmountTo.isEmpty()) {
isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
.parseDouble(strAmountFrom);
} else if (strAmountFrom.isEmpty() && !strAmountTo.isEmpty()) {
isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
.parseDouble(strAmountTo);
} else {
isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double
.parseDouble(strAmountFrom)
&& Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double
.parseDouble(strAmountTo);
}
if (isAmtInLimit) {
lastGroupedDatarow = previousRow;
isAmtInLimit = false;
numberOfElements++;
}
}
// Insert the transactions without payment if necessary
if (lastElement != numberOfElements) {
if (transactionsList.size() > 0) {
try {
existsConvRate = insertIntoTotal(lastGroupedDatarow, transactionsList, totalData,
strGroupCrit, strOrdCrit, strConvertCurrency, strConversionDate);
} catch (OBException e) {
// If there is no conversion rate
throw e;
}
}
totalData.add(lastGroupedDatarow);
lastElement++;
}
// Insert the remaining transactions wihtout payment if necessary
while (transactionsList.size() > 0) {
// throws OBException if there is no conversion rate
FieldProvider transactionData = createFieldProviderForTransaction(transactionsList.get(0),
strGroupCrit, strConvertCurrency, strConversionDate);
totalData.add(transactionData);
transactionsList.remove(0);
}
} finally {
if (scroller != null) {
scroller.close();
}
OBContext.restorePreviousMode();
}
return totalData.toArray(new FieldProvider[totalData.size()]);
}
From source file:org.openbravo.materialmgmt.VariantChDescUpdateProcess.java
License:Open Source License
/** * Method to update the Characteristics Description. * //from w w w . j ava 2s. co m * @param strProductId * Optional parameter, when given updates only the description of this product. * @param strChValueId * Optional parameter, when given updates only products with this characteristic value * assigned. */ public void update(String strProductId, String strChValueId) { OBContext.setAdminMode(true); try { if (StringUtils.isNotBlank(strProductId)) { Product product = OBDal.getInstance().get(Product.class, strProductId); // In some cases product might have been deleted. if (product != null) { updateProduct(product); } return; } StringBuffer where = new StringBuffer(); where.append(" as p"); where.append(" where p." + Product.PROPERTY_PRODUCTCHARACTERISTICLIST + " is not empty"); if (StringUtils.isNotBlank(strChValueId)) { where.append(" and exists (select 1 from p." + Product.PROPERTY_PRODUCTCHARACTERISTICVALUELIST + " as chv"); where.append(" where chv." + ProductCharacteristicValue.PROPERTY_CHARACTERISTICVALUE + ".id = :chvid)"); } OBQuery<Product> productQuery = OBDal.getInstance().createQuery(Product.class, where.toString()); if (StringUtils.isNotBlank(strChValueId)) { productQuery.setNamedParameter("chvid", strChValueId); } productQuery.setFilterOnReadableOrganization(false); productQuery.setFilterOnActive(false); ScrollableResults products = productQuery.scroll(ScrollMode.FORWARD_ONLY); int i = 0; try { while (products.next()) { Product product = (Product) products.get(0); updateProduct(product); if ((i % 100) == 0) { OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } i++; } } finally { products.close(); } } finally { OBContext.restorePreviousMode(); } }
From source file:org.openbravo.service.datasource.ADTreeDatasourceService.java
License:Open Source License
/** * /*from www. java 2 s. co m*/ * @param parentId * id of the node whose children are to be retrieved * @param hqlWhereClause * hql where clase of the tab/selector * @param hqlWhereClauseRootNodes * hql where clause that define what nodes are roots * @return A JSONArray containing all the children of the given node * @throws JSONException * @throws TooManyTreeNodesException * if the number of returned nodes were to be too high */ @Override protected JSONArray fetchNodeChildren(Map<String, String> parameters, Map<String, Object> datasourceParameters, String parentId, String hqlWhereClause, String hqlWhereClauseRootNodes) throws JSONException, TooManyTreeNodesException { String tabId = parameters.get("tabId"); String treeReferenceId = parameters.get("treeReferenceId"); Tab tab = null; JSONArray selectedProperties = null; if (tabId != null) { tab = OBDal.getInstance().get(Tab.class, tabId); String selectedPropertiesStr = parameters.get("_selectedProperties"); selectedProperties = new JSONArray(selectedPropertiesStr); } else if (treeReferenceId != null) { ReferencedTree treeReference = OBDal.getInstance().get(ReferencedTree.class, treeReferenceId); treeReference.getADReferencedTreeFieldList(); selectedProperties = new JSONArray(); for (ReferencedTreeField treeField : treeReference.getADReferencedTreeFieldList()) { selectedProperties.put(treeField.getProperty()); } } else { logger.error( "A request to the TreeDatasourceService must include the tabId or the treeReferenceId parameter"); return new JSONArray(); } Tree tree = (Tree) datasourceParameters.get("tree"); JSONArray responseData = new JSONArray(); Entity entity = ModelProvider.getInstance().getEntityByTableId(tree.getTable().getId()); final DataToJsonConverter toJsonConverter = OBProvider.getInstance().get(DataToJsonConverter.class); // Joins the ADTreeNode with the referenced table StringBuilder joinClause = new StringBuilder(); joinClause.append(" as tn "); joinClause.append(" , " + entity.getName() + " as e "); joinClause.append(" where tn.node = e.id "); if (hqlWhereClause != null) { joinClause.append(" and (" + hqlWhereClause + ")"); } joinClause.append(" and tn.tree.id = '" + tree.getId() + "' "); if (hqlWhereClauseRootNodes == null && tab != null && tab.getTabLevel() > 0) { // Add the criteria to filter only the records that belong to the record selected in the // parent tab Tab parentTab = KernelUtils.getInstance().getParentTab(tab); String parentPropertyName = ApplicationUtils.getParentProperty(tab, parentTab); if (parentPropertyName != null) { JSONArray criteria = (JSONArray) JsonUtils.buildCriteria(parameters).get("criteria"); String parentRecordId = getParentRecordIdFromCriteria(criteria, parentPropertyName); if (parentRecordId != null) { joinClause.append(" and e." + parentPropertyName + ".id = '" + parentRecordId + "' "); } } } if (hqlWhereClauseRootNodes != null) { joinClause.append(" and (" + hqlWhereClauseRootNodes + ") "); } else { if (ROOT_NODE_CLIENT.equals(parentId)) { if (AD_ORG_TABLE_ID.equals(tree.getTable().getId())) { // The ad_org table needs a special treatment, since is the only table tree that has an // actual node ('*' organization) with node_id = ROOT_NODE_DB // In this table the root nodes have the parent_id property set to null joinClause.append(" and tn.reportSet is null"); } else { // Other ad_tree nodes can have either ROOT_NODE_DB or null as parent_id joinClause.append(" and (tn.reportSet = '" + ROOT_NODE_DB + "' or tn.reportSet is null)"); } } else { joinClause.append(" and tn.reportSet = '" + parentId + "' "); } } joinClause.append(" order by tn.sequenceNumber "); // Selects the relevant properties from ADTreeNode and all the properties from the referenced // table String selectClause = " tn.id as treeNodeId, tn.reportSet as parentId, tn.sequenceNumber as seqNo, tn.node as nodeId, e as entity"; OBQuery<BaseOBObject> obq = OBDal.getInstance().createQuery("ADTreeNode", joinClause.toString()); obq.setFilterOnActive(false); obq.setSelectClause(selectClause); obq.setFilterOnReadableOrganization(false); int nResults = obq.count(); OBContext context = OBContext.getOBContext(); int nMaxResults = -1; try { nMaxResults = Integer.parseInt( Preferences.getPreferenceValue("TreeDatasourceFetchLimit", false, context.getCurrentClient(), context.getCurrentOrganization(), context.getUser(), context.getRole(), null)); } catch (Exception e) { nMaxResults = 1000; } if (nResults > nMaxResults) { throw new TooManyTreeNodesException(); } boolean fetchRoot = ROOT_NODE_CLIENT.equals(parentId); int PARENT_ID = 1; int SEQNO = 2; int NODE_ID = 3; int ENTITY = 4; int cont = 0; ScrollableResults scrollNodes = obq.createQuery().scroll(ScrollMode.FORWARD_ONLY); try { while (scrollNodes.next()) { Object[] node = scrollNodes.get(); JSONObject value = null; BaseOBObject bob = (BaseOBObject) node[ENTITY]; try { value = toJsonConverter.toJsonObject(bob, DataResolvingMode.FULL); value.put("nodeId", bob.getId().toString()); if (fetchRoot) { value.put("parentId", ROOT_NODE_CLIENT); } else { value.put("parentId", node[PARENT_ID]); } addNodeCommonAttributes(entity, bob, value); value.put("seqno", node[SEQNO]); value.put("_hasChildren", (this.nodeHasChildren(entity, (String) node[NODE_ID], hqlWhereClause)) ? true : false); } catch (JSONException e) { logger.error("Error while constructing JSON reponse", e); } responseData.put(value); if ((cont % 100) == 0) { OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } cont++; } } finally { scrollNodes.close(); } return responseData; }