# HG changeset patch # User Alejandro # Date 1569270623 14400 # Mon Sep 23 16:30:23 2019 -0400 # Node ID d24bedde619c6f5fc0b46df6a9e742de42f2ac46 # Parent d38c4f8bae677cce6019ec2604085ad432c336a1 Related to issue 41890: Replaced StringBuffer/Builder with String. Refactored some queries to be more readable diff --git a/src/org/openbravo/retail/posterminal/DeferredServiceDelivery.java b/src/org/openbravo/retail/posterminal/DeferredServiceDelivery.java --- a/src/org/openbravo/retail/posterminal/DeferredServiceDelivery.java +++ b/src/org/openbravo/retail/posterminal/DeferredServiceDelivery.java @@ -110,16 +110,12 @@ final Session session = OBDal.getInstance().getSession(); // Check if there's any deferred service to deliver. Otherwise, continue - final StringBuilder checkServiceToDeliverHQL = new StringBuilder(); - checkServiceToDeliverHQL.append("SELECT 1 "); - checkServiceToDeliverHQL.append("FROM OrderlineServiceRelation AS olsr "); - checkServiceToDeliverHQL.append("JOIN olsr.orderlineRelated AS pol "); - checkServiceToDeliverHQL.append("JOIN olsr.salesOrderLine AS sol "); - checkServiceToDeliverHQL.append("WHERE pol.salesOrder.id = :orderId "); - checkServiceToDeliverHQL.append("AND pol.salesOrder.id <> sol.salesOrder.id "); - checkServiceToDeliverHQL.append("AND sol.obposIspaid = true"); + String checkServiceToDeliverHQL = "SELECT 1 FROM OrderlineServiceRelation AS olsr " + + " JOIN olsr.orderlineRelated AS pol JOIN olsr.salesOrderLine AS sol " + + " WHERE pol.salesOrder.id = :orderId AND pol.salesOrder.id <> sol.salesOrder.id " + + " AND sol.obposIspaid = true"; final Query checkServiceToDeliverQuery = session - .createQuery(checkServiceToDeliverHQL.toString(), Integer.class); + .createQuery(checkServiceToDeliverHQL, Integer.class); checkServiceToDeliverQuery.setParameter("orderId", order.getId()); checkServiceToDeliverQuery.setMaxResults(1); @@ -127,11 +123,11 @@ return; } - final String deferredLinesHqlQuery = "select osr.salesOrderLine" - + " from OrderlineServiceRelation osr join osr.orderlineRelated olr" - + " where olr.salesOrder.id =:orderId" - + " and olr.salesOrder.id <> osr.salesOrderLine.salesOrder.id" - + " and osr.salesOrderLine.obposIspaid = true"; + final String deferredLinesHqlQuery = "select osr.salesOrderLine " + + " from OrderlineServiceRelation osr join osr.orderlineRelated olr " + + " where olr.salesOrder.id =:orderId " + + " and olr.salesOrder.id <> osr.salesOrderLine.salesOrder.id " + + " and osr.salesOrderLine.obposIspaid = true "; final Query query = session.createQuery(deferredLinesHqlQuery, OrderLine.class); query.setParameter("orderId", order.getId()); @@ -143,10 +139,10 @@ } if ("UQ".equals(serviceOrderLine.getProduct().getQuantityRule())) { String relatedDeliveredLinesHqlQuery = "select count(olsr.id) " // - + "from OrderlineServiceRelation olsr " // - + "join olsr.orderlineRelated as relatedLine " // - + "where olsr.salesOrderLine.id = :orderLineId " // - + "and relatedLine.deliveredQuantity <> 0"; + + " from OrderlineServiceRelation olsr " // + + " join olsr.orderlineRelated as relatedLine " // + + " where olsr.salesOrderLine.id = :orderLineId " // + + " and relatedLine.deliveredQuantity <> 0"; final Session relatedLinesSession = OBDal.getInstance().getSession(); final Query deliveredRelatedLinesCountQuery = relatedLinesSession .createQuery(relatedDeliveredLinesHqlQuery, Long.class); @@ -163,9 +159,9 @@ } } else { String relatedLinesHqlQtyQuery = "select sum(relatedLine.deliveredQuantity) as quantity " - + "from OrderlineServiceRelation olsr " // - + "join olsr.orderlineRelated as relatedLine " // - + "where olsr.salesOrderLine.id = :orderLineId"; + + " from OrderlineServiceRelation olsr " // + + " join olsr.orderlineRelated as relatedLine " // + + " where olsr.salesOrderLine.id = :orderLineId"; final Session relatedLinesSession = OBDal.getInstance().getSession(); final Query relatedLinesQuery = relatedLinesSession .createQuery(relatedLinesHqlQtyQuery, BigDecimal.class); diff --git a/src/org/openbravo/retail/posterminal/OrderLoader.java b/src/org/openbravo/retail/posterminal/OrderLoader.java --- a/src/org/openbravo/retail/posterminal/OrderLoader.java +++ b/src/org/openbravo/retail/posterminal/OrderLoader.java @@ -1075,10 +1075,9 @@ .getProxy("FIN_PaymentMethod", jsonorder.getJSONObject("bp").getString("paymentMethod"))); } else if (bp.getPaymentMethod() != null) { - order.setPaymentMethod((FIN_PaymentMethod) bp.getPaymentMethod()); + order.setPaymentMethod(bp.getPaymentMethod()); } else if (order.getOrganization().getObretcoDbpPmethodid() != null) { - order - .setPaymentMethod((FIN_PaymentMethod) order.getOrganization().getObretcoDbpPmethodid()); + order.setPaymentMethod(order.getOrganization().getObretcoDbpPmethodid()); } else { String paymentMethodHqlWhereClause = " pmethod where EXISTS (SELECT 1 FROM FinancialMgmtFinAccPaymentMethod fapm " + "WHERE pmethod.id = fapm.paymentMethod.id AND fapm.payinAllow = 'Y')"; @@ -1098,9 +1097,9 @@ .getProxy("FinancialMgmtPaymentTerm", jsonorder.getJSONObject("bp").getString("paymentTerms"))); } else if (bp.getPaymentTerms() != null) { - order.setPaymentTerms((PaymentTerm) bp.getPaymentTerms()); + order.setPaymentTerms(bp.getPaymentTerms()); } else if (order.getOrganization().getObretcoDbpPmethodid() != null) { - order.setPaymentTerms((PaymentTerm) order.getOrganization().getObretcoDbpPtermid()); + order.setPaymentTerms(order.getOrganization().getObretcoDbpPtermid()); } else { OBCriteria paymentTerms = OBDal.getInstance().createCriteria(PaymentTerm.class); paymentTerms.add(Restrictions.eq(Locator.PROPERTY_ACTIVE, true)); @@ -1501,12 +1500,9 @@ if (payment.has("isReversePayment")) { // If the current payment is a reversal payment, a new PSD must be added for each PSD in the // reversed payment - final StringBuffer reversedPSDHQL = new StringBuffer(); - reversedPSDHQL.append(" AS psd "); - reversedPSDHQL.append("WHERE psd.paymentDetails.finPayment.id = :paymentId "); - reversedPSDHQL.append("AND psd.orderPaymentSchedule.id = :paymentSchId"); + final String reversedPSDHQL = " AS psd WHERE psd.paymentDetails.finPayment.id = :paymentId AND psd.orderPaymentSchedule.id = :paymentSchId"; final OBQuery reversedPSDQuery = OBDal.getInstance() - .createQuery(FIN_PaymentScheduleDetail.class, reversedPSDHQL.toString()); + .createQuery(FIN_PaymentScheduleDetail.class, reversedPSDHQL); reversedPSDQuery.setNamedParameter("paymentId", payment.getString("reversedPaymentId")); reversedPSDQuery.setNamedParameter("paymentSchId", paymentSchedule.getId()); reversedPSDQuery.setFilterOnReadableOrganization(false); diff --git a/src/org/openbravo/retail/posterminal/POSLoginHandler.java b/src/org/openbravo/retail/posterminal/POSLoginHandler.java --- a/src/org/openbravo/retail/posterminal/POSLoginHandler.java +++ b/src/org/openbravo/retail/posterminal/POSLoginHandler.java @@ -216,30 +216,17 @@ // one public static Role getNearestRoleValidToLoginInWebPosTerminalForCertainUser(User currentUser, OBPOSApplications terminal) { - StringBuilder hqlQueryStr = new StringBuilder(); - hqlQueryStr.append( - "SELECT rolOrg.role.id, to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) as distance "); - hqlQueryStr.append("FROM ADRoleOrganization rolOrg "); - hqlQueryStr.append("WHERE rolOrg.active = true and "); - hqlQueryStr.append(" rolOrg.role.active = true and "); - hqlQueryStr.append(" rolOrg.role.forPortalUsers = false and "); - hqlQueryStr.append(" rolOrg.role.id in ("); - hqlQueryStr.append(" SELECT usrol.role.id FROM ADUserRoles usrol "); - hqlQueryStr.append(" WHERE usrol.userContact.id = :userId and "); - hqlQueryStr.append(" usrol.active = true "); - hqlQueryStr.append(" ) and exists ("); - hqlQueryStr.append(" SELECT 1 FROM ADFormAccess frmacc "); - hqlQueryStr.append(" WHERE rolOrg.role.id = frmacc.role.id and "); - hqlQueryStr.append(" frmacc.active = true and "); - hqlQueryStr.append(" frmacc.specialForm.id = :formId"); - hqlQueryStr.append(" ) and "); - hqlQueryStr.append( - " to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) > 0 "); - hqlQueryStr.append( - "ORDER BY to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) ASC, rolOrg.role.name ASC"); + String hqlQueryStr = "SELECT rolOrg.role.id, to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) as distance " + + " FROM ADRoleOrganization rolOrg WHERE rolOrg.active = true " + + " and rolOrg.role.active = true and rolOrg.role.forPortalUsers = false " + + " and rolOrg.role.id in (SELECT usrol.role.id FROM ADUserRoles usrol " + + " WHERE usrol.userContact.id = :userId and usrol.active = true) " + + " and exists (SELECT 1 FROM ADFormAccess frmacc WHERE rolOrg.role.id = frmacc.role.id " + + " and frmacc.active = true and frmacc.specialForm.id = :formId) " + + " and to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) > 0 " + + " ORDER BY to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) ASC, rolOrg.role.name ASC"; final org.hibernate.Session hibernateSession = OBDal.getInstance().getSession(); - final Query query = hibernateSession.createQuery(hqlQueryStr.toString(), - Object[].class); + final Query query = hibernateSession.createQuery(hqlQueryStr, Object[].class); query.setParameter("stOrgId", terminal.getOrganization().getId()); query.setParameter("clientId", terminal.getClient().getId()); query.setParameter("userId", currentUser.getId()); @@ -262,18 +249,12 @@ private int getDistanceToStoreOrganizationForCertainRole(Role currentRole, OBPOSApplications terminal) { if (hasMobileAccess(currentRole, POSConstants.APP_NAME)) { - StringBuilder hqlQueryStr = new StringBuilder(); - hqlQueryStr.append( - "SELECT to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) as distance "); - hqlQueryStr.append("FROM ADRoleOrganization rolOrg "); - hqlQueryStr.append("WHERE rolOrg.role.id = :roleId and "); - hqlQueryStr.append( - " to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) > 0 "); - hqlQueryStr.append( - "ORDER BY to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) ASC, rolOrg.role.name ASC"); + String hqlQueryStr = "SELECT to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) as distance " + + " FROM ADRoleOrganization rolOrg WHERE rolOrg.role.id = :roleId " + + " and to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) > 0 " + + " ORDER BY to_number(ad_isorgincluded(:stOrgId, rolOrg.organization.id, :clientId)) ASC, rolOrg.role.name ASC"; final org.hibernate.Session hibernateSession = OBDal.getInstance().getSession(); - final Query query = hibernateSession.createQuery(hqlQueryStr.toString(), - BigDecimal.class); + final Query query = hibernateSession.createQuery(hqlQueryStr, BigDecimal.class); query.setParameter("stOrgId", terminal.getOrganization().getId()); query.setParameter("clientId", terminal.getClient().getId()); query.setParameter("roleId", currentRole.getId()); @@ -320,7 +301,7 @@ return role.getOBPOSDefaultPosLanguage(); } } - return (Language) OBDal.getInstance() + return OBDal.getInstance() .createCriteria(Language.class) .add(Restrictions.eq(Language.PROPERTY_LANGUAGE, "en_US")) .list() diff --git a/src/org/openbravo/retail/posterminal/POSUtils.java b/src/org/openbravo/retail/posterminal/POSUtils.java --- a/src/org/openbravo/retail/posterminal/POSUtils.java +++ b/src/org/openbravo/retail/posterminal/POSUtils.java @@ -191,13 +191,9 @@ public static List getOrgListByCrossStoreId(final String crossStoreId) { OBContext.setAdminMode(false); try { - final StringBuilder select = new StringBuilder(); - select.append(" select " + Organization.PROPERTY_ID); - select.append(" from " + Organization.ENTITY_NAME); - select.append( - " where " + Organization.PROPERTY_OBRETCOCROSSSTOREORGANIZATION + ".id = :crossStoreId"); + final String select = "select o.id from Organization as o where o.oBRETCOCrossStoreOrganization.id = :crossStoreId"; - final Query query = OBDal.getInstance().getSession().createQuery(select.toString()); + final Query query = OBDal.getInstance().getSession().createQuery(select); query.setParameter("crossStoreId", crossStoreId); return query.list(); @@ -397,15 +393,11 @@ final Organization crossStore = posterminal.getOrganization() .getOBRETCOCrossStoreOrganization(); - final StringBuilder select = new StringBuilder(); - select.append(" select " + Organization.PROPERTY_OBRETCOPRODUCTLIST + ".id"); - select.append(" from " + Organization.ENTITY_NAME); - select.append(" where " + Organization.PROPERTY_OBRETCOCROSSSTOREORGANIZATION - + ".id = :crossStoreId"); - select.append(" and " + Organization.PROPERTY_OBRETCOPRODUCTLIST + " is not null"); - select.append(" group by " + Organization.PROPERTY_OBRETCOPRODUCTLIST + ".id"); + final String select = " select o.obretcoProductlist.id from Organization o " + + " where o.oBRETCOCrossStoreOrganization.id = :crossStoreId " + + " and o.obretcoProductlist is not null group by o.obretcoProductlist.id"; - final Query query = OBDal.getInstance().getSession().createQuery(select.toString()); + final Query query = OBDal.getInstance().getSession().createQuery(select); query.setParameter("crossStoreId", crossStore.getId()); productList.addAll(query.list()); } @@ -449,11 +441,11 @@ if (curDbms.equals("POSTGRE")) { sqlToExecute = "select max(a.docno) from (select to_number(substring(replace(co.documentno, app.orderdocno_prefix, ''), '^/{0,1}([0-9]+)$')) docno from c_order co " + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = :appValue " - + "where co.c_doctype_id in (" + doctypeIds + ")) a"; + + "where co.c_doctype_id in (:doctypeIds)) a"; } else if (curDbms.equals("ORACLE")) { sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(REPLACE(co.documentno, app.orderdocno_prefix), '^/{0,1}([0-9]+)$'), 2)) docno from c_order co " + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = :appValue " - + "where co.c_doctype_id in (" + doctypeIds + ")) a"; + + "where co.c_doctype_id in (:doctypeIds)) a"; } else { // unknow DBMS // shouldn't happen @@ -463,6 +455,7 @@ @SuppressWarnings("rawtypes") NativeQuery query = OBDal.getInstance().getSession().createNativeQuery(sqlToExecute); query.setParameter("appValue", searchKey); + query.setParameter("doctypeIds", doctypeIds); Object result = query.uniqueResult(); if (result == null) { @@ -539,11 +532,11 @@ if (curDbms.equals("POSTGRE")) { sqlToExecute = "select max(a.docno) from (select to_number(substring(replace(co.documentno, app.quotationdocno_prefix, ''), '^/{0,1}([0-9]+)$')) docno from c_order co " + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = :appValue " - + "where co.c_doctype_id in (" + doctypeIds + ")) a"; + + "where co.c_doctype_id in (:doctypeIds)) a"; } else if (curDbms.equals("ORACLE")) { sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(REPLACE(co.documentno, app.quotationdocno_prefix), '^/{0,1}([0-9]+)$'), 2)) docno from c_order co " + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = :appValue " - + "where co.c_doctype_id in (" + doctypeIds + ")) a"; + + "where co.c_doctype_id in (:doctypeIds)) a"; } else { // unknow DBMS // shouldn't happen @@ -553,6 +546,7 @@ @SuppressWarnings("rawtypes") NativeQuery query = OBDal.getInstance().getSession().createNativeQuery(sqlToExecute); query.setParameter("appValue", searchKey); + query.setParameter("doctypeIds", doctypeIds); Object result = query.uniqueResult(); if (result == null) { @@ -629,11 +623,11 @@ if (curDbms.equals("POSTGRE")) { sqlToExecute = "select max(a.docno) from (select to_number(substring(replace(co.documentno, app.returndocno_prefix, ''), '^/{0,1}([0-9]+)$')) docno from c_order co " + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = :appValue " - + "where co.c_doctype_id in (" + doctypeIds + ")) a"; + + "where co.c_doctype_id in (:doctypeIds)) a"; } else if (curDbms.equals("ORACLE")) { sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(REPLACE(co.documentno, app.returndocno_prefix), '^/{0,1}([0-9]+)$'), 2)) docno from c_order co " + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = :appValue " - + "where co.c_doctype_id in (" + doctypeIds + ")) a"; + + "where co.c_doctype_id in (:doctypeIds)) a"; } else { // unknow DBMS // shouldn't happen @@ -643,6 +637,7 @@ @SuppressWarnings("rawtypes") NativeQuery query = OBDal.getInstance().getSession().createNativeQuery(sqlToExecute); query.setParameter("appValue", searchKey); + query.setParameter("doctypeIds", doctypeIds); Object result = query.uniqueResult(); if (result == null) { maxDocNo = 0; diff --git a/src/org/openbravo/retail/posterminal/ProcessVoidLayaway.java b/src/org/openbravo/retail/posterminal/ProcessVoidLayaway.java --- a/src/org/openbravo/retail/posterminal/ProcessVoidLayaway.java +++ b/src/org/openbravo/retail/posterminal/ProcessVoidLayaway.java @@ -66,21 +66,12 @@ } } - final StringBuffer hql = new StringBuffer(); - hql.append("SELECT DISTINCT so.documentNo "); - hql.append("FROM OrderlineServiceRelation AS olsr "); - hql.append("JOIN olsr.orderlineRelated AS pol "); - hql.append("JOIN olsr.salesOrderLine AS sol "); - hql.append("JOIN pol.salesOrder AS po "); - hql.append("JOIN sol.salesOrder AS so "); - hql.append("WHERE po.id = :orderId "); - hql.append("AND so.id <> :orderId "); - hql.append("AND pol.orderedQuantity <> pol.deliveredQuantity "); - hql.append("AND sol.orderedQuantity <> sol.deliveredQuantity "); - hql.append("AND so.documentStatus <> 'CL' "); - Query query = OBDal.getInstance() - .getSession() - .createQuery(hql.toString(), String.class); + final String hql = "SELECT DISTINCT so.documentNo FROM OrderlineServiceRelation AS olsr " + + " JOIN olsr.orderlineRelated AS pol JOIN olsr.salesOrderLine AS sol " + + " JOIN pol.salesOrder AS po JOIN sol.salesOrder AS so WHERE po.id = :orderId " + + " AND so.id <> :orderId AND pol.orderedQuantity <> pol.deliveredQuantity " + + " AND sol.orderedQuantity <> sol.deliveredQuantity AND so.documentStatus <> 'CL' "; + Query query = OBDal.getInstance().getSession().createQuery(hql, String.class); query.setParameter("orderId", order.getId()); List documentNoList = query.list(); if (documentNoList.size() > 0) { diff --git a/src/org/openbravo/retail/posterminal/ProfileUtilsServlet.java b/src/org/openbravo/retail/posterminal/ProfileUtilsServlet.java --- a/src/org/openbravo/retail/posterminal/ProfileUtilsServlet.java +++ b/src/org/openbravo/retail/posterminal/ProfileUtilsServlet.java @@ -37,17 +37,11 @@ for (Organization org : orgs) { JSONObject orgWarehouse = new JSONObject(); orgWarehouse.put("orgId", org.getId()); - StringBuffer hqlQuery = new StringBuffer(); - hqlQuery.append("organization.id in (:orgList) AND "); - hqlQuery.append("client.id=:clientId AND "); - hqlQuery.append("id in ("); - hqlQuery.append(" select owar.warehouse.id from OrganizationWarehouse owar "); - hqlQuery.append(" where owar.organization.id = :orgId"); - hqlQuery.append(") AND "); - hqlQuery.append("organization.active=true "); - hqlQuery.append("order by name"); + String hqlQuery = "organization.id in (:orgList) AND client.id=:clientId " + + "AND id in (select owar.warehouse.id from OrganizationWarehouse owar " + + "where owar.organization.id = :orgId) AND organization.active=true order by name"; final OBQuery warehouses = OBDal.getInstance() - .createQuery(Warehouse.class, hqlQuery.toString()); + .createQuery(Warehouse.class, hqlQuery); warehouses.setNamedParameter("orgList", osp.getNaturalTree(org.getId())); warehouses.setNamedParameter("orgId", org.getId()); warehouses.setNamedParameter("clientId", clientId); @@ -65,8 +59,8 @@ String formId = defaults.getFormId(); String clientId = OBContext.getOBContext().getCurrentClient().getId(); String orgId = OBContext.getOBContext().getCurrentOrganization().getId(); - String whereClause = "as r where userContact.id=:user and role.active=true "; - whereClause += "and exists (select 1 from ADFormAccess a "// + String whereClause = "as r where userContact.id=:user and role.active=true " + + " and exists (select 1 from ADFormAccess a "// + " where a.active = true" // + " and a.role.id = r.role.id "// + " and a.role.client.id = :clientId "// diff --git a/src/org/openbravo/retail/posterminal/UpdateProductCategoryByAssortmentBackground.java b/src/org/openbravo/retail/posterminal/UpdateProductCategoryByAssortmentBackground.java --- a/src/org/openbravo/retail/posterminal/UpdateProductCategoryByAssortmentBackground.java +++ b/src/org/openbravo/retail/posterminal/UpdateProductCategoryByAssortmentBackground.java @@ -83,14 +83,10 @@ for (OBRETCOProductList assortment : assortmentList.list()) { List productCategoryElementList = new ArrayList(); - final StringBuilder hql = new StringBuilder(); - hql.append("select distinct(mpc.id) "); - hql.append(" from OBRETCO_ProductList obpl "); - hql.append(" left join obpl.oBRETCOProlProductList obpp "); - hql.append(" left join obpp.product mp "); - hql.append(" left join mp.productCategory mpc "); - hql.append(" where obpl.id= :assortmentid "); - hql.append(" and mp.active='Y'"); + final String hql = "select distinct(mpc.id) from OBRETCO_ProductList obpl " + + "left join obpl.oBRETCOProlProductList obpp left join obpp.product mp " + + "left join mp.productCategory mpc " + + "where obpl.id= :assortmentid and mp.active='Y'"; final Session session = OBDal.getInstance().getSession(); final Query query = session.createQuery(hql.toString(), String.class); diff --git a/src/org/openbravo/retail/posterminal/event/CurrencyConversionEventHandler.java b/src/org/openbravo/retail/posterminal/event/CurrencyConversionEventHandler.java --- a/src/org/openbravo/retail/posterminal/event/CurrencyConversionEventHandler.java +++ b/src/org/openbravo/retail/posterminal/event/CurrencyConversionEventHandler.java @@ -25,7 +25,6 @@ import org.openbravo.dal.service.OBDal; import org.openbravo.erpCommon.utility.OBMessageUtils; import org.openbravo.retail.posterminal.OBPOSCurrencyRounding; -import org.openbravo.retail.posterminal.TerminalTypePaymentMethod; public class CurrencyConversionEventHandler extends EntityPersistenceEventObserver { @@ -57,22 +56,16 @@ OBPOSCurrencyRounding currencyRounding = (OBPOSCurrencyRounding) event.getTargetInstance(); OBContext.setAdminMode(true); try { - StringBuilder hql = new StringBuilder(); - hql.append("select " + TerminalTypePaymentMethod.PROPERTY_ID); - hql.append(" from " + TerminalTypePaymentMethod.ENTITY_NAME + " ttpm"); - hql.append(" where " + TerminalTypePaymentMethod.PROPERTY_CURRENCY + ".id = :currencyId"); - hql.append(" and " + TerminalTypePaymentMethod.PROPERTY_ACTIVE + " = true"); - hql.append(" and " + TerminalTypePaymentMethod.PROPERTY_CHANGEPAYMENTTYPE + " is not null"); - hql.append(" and " + TerminalTypePaymentMethod.PROPERTY_CHANGEPAYMENTTYPE + " is not null"); - hql.append(" and ad_isorgincluded(ttpm.organization.id, :organizationId, :clientId) <> -1"); - Query qry = OBDal.getInstance() - .getSession() - .createQuery(hql.toString(), String.class); + final String hql = "select ttpm.id from OBPOS_App_Payment_Type ttpm " + + " where ttpm.currency.id = :currencyId and ttpm.active = true and ttpm.changePaymentType is not null " + + " and ad_isorgincluded(ttpm.organization.id, :organizationId, :clientId) <> -1"; + + Query qry = OBDal.getInstance().getSession().createQuery(hql, String.class); qry.setParameter("currencyId", currencyRounding.getCurrency().getId()); qry.setParameter("organizationId", currencyRounding.getOrganization().getId()); qry.setParameter("clientId", currencyRounding.getClient().getId()); qry.setMaxResults(1); - String currencyRoundingId = (String) qry.uniqueResult(); + String currencyRoundingId = qry.uniqueResult(); if (currencyRoundingId != null) { throw new OBException( String.format(OBMessageUtils.messageBD("OBPOS_CurrencyRoundingNotAllowed"), diff --git a/src/org/openbravo/retail/posterminal/event/OBPOSConversionRateEventHandler.java b/src/org/openbravo/retail/posterminal/event/OBPOSConversionRateEventHandler.java --- a/src/org/openbravo/retail/posterminal/event/OBPOSConversionRateEventHandler.java +++ b/src/org/openbravo/retail/posterminal/event/OBPOSConversionRateEventHandler.java @@ -76,24 +76,13 @@ // Check if exists another record using this currencyFrom - currencyTo in the same dates private boolean existsRecord(String id, Client client, Organization organization, Currency currencyFrom, Currency currencyTo, Date validFrom, Date validTo) { - StringBuilder hql = new StringBuilder(); - hql.append(" SELECT t." + OBPOSConversionRate.PROPERTY_ID); - hql.append(" FROM " + OBPOSConversionRate.ENTITY_NAME + " as t"); - hql.append(" WHERE :id != t. " + OBPOSConversionRate.PROPERTY_ID); - hql.append(" AND :client = t. " + OBPOSConversionRate.PROPERTY_CLIENT); - hql.append(" AND :organization = t. " + OBPOSConversionRate.PROPERTY_ORGANIZATION); - hql.append(" AND :currencyFrom = t. " + OBPOSConversionRate.PROPERTY_CURRENCY); - hql.append(" AND :currencyTo = t. " + OBPOSConversionRate.PROPERTY_TOCURRENCY); - hql.append(" AND ((:validFrom between t." + OBPOSConversionRate.PROPERTY_VALIDFROMDATE - + " AND t." + OBPOSConversionRate.PROPERTY_VALIDTODATE); - hql.append(" OR :validTo between t." + OBPOSConversionRate.PROPERTY_VALIDFROMDATE + " AND t." - + OBPOSConversionRate.PROPERTY_VALIDTODATE + ")"); - hql.append(" OR (:validFrom < t." + OBPOSConversionRate.PROPERTY_VALIDFROMDATE - + " AND :validTo > t." + OBPOSConversionRate.PROPERTY_VALIDTODATE + "))"); + String hql = " SELECT t.id FROM OBPOS_Conversion_Rate as t WHERE :id != t.id " + + " AND :client = t.client AND :organization = t.organization AND :currencyFrom = t.currency " + + " AND :currencyTo = t.toCurrency AND ((:validFrom between t.validFromDate AND t.validToDate " + + " OR :validTo between t.validFromDate AND t.validToDate) OR (:validFrom < t.validFromDate " + + " AND :validTo > t.validToDate))"; - final Query query = OBDal.getInstance() - .getSession() - .createQuery(hql.toString(), Object.class); + final Query query = OBDal.getInstance().getSession().createQuery(hql, Object.class); query.setParameter("id", id); query.setParameter("client", client); query.setParameter("organization", organization); diff --git a/src/org/openbravo/retail/posterminal/event/TerminalTypePaymentMethodEventHandler.java b/src/org/openbravo/retail/posterminal/event/TerminalTypePaymentMethodEventHandler.java --- a/src/org/openbravo/retail/posterminal/event/TerminalTypePaymentMethodEventHandler.java +++ b/src/org/openbravo/retail/posterminal/event/TerminalTypePaymentMethodEventHandler.java @@ -25,7 +25,6 @@ import org.openbravo.dal.service.OBDal; import org.openbravo.erpCommon.utility.OBMessageUtils; import org.openbravo.erpCommon.utility.Utility; -import org.openbravo.retail.posterminal.OBPOSCurrencyRounding; import org.openbravo.retail.posterminal.TerminalTypePaymentMethod; import org.openbravo.service.db.DalConnectionProvider; @@ -70,15 +69,10 @@ OBContext.setAdminMode(true); try { if (ttpm.getChangeLessThan() != null && ttpm.getChangePaymentType() != null) { - StringBuilder hql = new StringBuilder(); - hql.append("select " + OBPOSCurrencyRounding.PROPERTY_ID); - hql.append(" from " + OBPOSCurrencyRounding.ENTITY_NAME + " cr"); - hql.append(" where " + OBPOSCurrencyRounding.PROPERTY_CURRENCY + ".id = :currencyId"); - hql.append(" and " + OBPOSCurrencyRounding.PROPERTY_ACTIVE + " = true"); - hql.append(" and ad_isorgincluded(:organizationId, cr.organization.id, :clientId) <> -1"); - Query qry = OBDal.getInstance() - .getSession() - .createQuery(hql.toString(), String.class); + String hql = "select cr.id from OBPOS_CurrencyRounding cr " + + " where cr.currency.id = :currencyId and OBPOS_CurrencyRounding.active = true " + + " and ad_isorgincluded(:organizationId, cr.organization.id, :clientId) <> -1"; + Query qry = OBDal.getInstance().getSession().createQuery(hql, String.class); qry.setParameter("currencyId", ttpm.getCurrency().getId()); qry.setParameter("organizationId", ttpm.getOrganization().getId()); qry.setParameter("clientId", ttpm.getClient().getId()); diff --git a/src/org/openbravo/retail/posterminal/process/IsOrderCancelled.java b/src/org/openbravo/retail/posterminal/process/IsOrderCancelled.java --- a/src/org/openbravo/retail/posterminal/process/IsOrderCancelled.java +++ b/src/org/openbravo/retail/posterminal/process/IsOrderCancelled.java @@ -85,18 +85,12 @@ && jsonData.getBoolean("checkNotEditableLines")) { // Find the deferred services or the products that have related deferred services in the // order that is being canceled - final StringBuffer hql = new StringBuffer(); - hql.append("SELECT DISTINCT sol.lineNo "); - hql.append("FROM OrderlineServiceRelation AS olsr "); - hql.append("JOIN olsr.salesOrderLine AS sol "); - hql.append("JOIN olsr.orderlineRelated AS pol "); - hql.append("JOIN sol.salesOrder AS so "); - hql.append("WHERE so.id <> :orderId "); - hql.append("AND pol.salesOrder.id = :orderId "); - hql.append("AND so.iscancelled = false"); - final Query query = OBDal.getInstance() - .getSession() - .createQuery(hql.toString(), Long.class); + final String hql = "SELECT DISTINCT sol.lineNo " + + " FROM OrderlineServiceRelation AS olsr JOIN olsr.salesOrderLine AS sol " + + " JOIN olsr.orderlineRelated AS pol JOIN sol.salesOrder AS so " + + " WHERE so.id <> :orderId AND pol.salesOrder.id = :orderId " + + " AND so.iscancelled = false"; + final Query query = OBDal.getInstance().getSession().createQuery(hql, Long.class); query.setParameter("orderId", orderId); result.put("deferredLines", query.list()); } @@ -104,21 +98,15 @@ && jsonData.getBoolean("checkNotDeliveredDeferredServices")) { // Find if there's any line in the ticket which is not delivered and has deferred // services - final StringBuffer hql = new StringBuffer(); - hql.append("SELECT DISTINCT so.documentNo "); - hql.append("FROM OrderlineServiceRelation AS olsr "); - hql.append("JOIN olsr.orderlineRelated AS pol "); - hql.append("JOIN olsr.salesOrderLine AS sol "); - hql.append("JOIN pol.salesOrder AS po "); - hql.append("JOIN sol.salesOrder AS so "); - hql.append("WHERE po.id = :orderId "); - hql.append("AND so.id <> :orderId "); - hql.append("AND pol.orderedQuantity <> pol.deliveredQuantity "); - hql.append("AND sol.orderedQuantity <> sol.deliveredQuantity "); - hql.append("AND so.documentStatus <> 'CL' "); + final String hql = "SELECT DISTINCT so.documentNo " + + " FROM OrderlineServiceRelation AS olsr JOIN olsr.orderlineRelated AS pol " + + " JOIN olsr.salesOrderLine AS sol JOIN pol.salesOrder AS po " + + " JOIN sol.salesOrder AS so WHERE po.id = :orderId AND so.id <> :orderId " + + " AND pol.orderedQuantity <> pol.deliveredQuantity " + + " AND sol.orderedQuantity <> sol.deliveredQuantity AND so.documentStatus <> 'CL' "; final Query query = OBDal.getInstance() .getSession() - .createQuery(hql.toString(), String.class); + .createQuery(hql, String.class); query.setParameter("orderId", orderId); result.put("notDeliveredDeferredServices", query.list()); } diff --git a/src/org/openbravo/retail/posterminal/stock/StoreDetailedStock.java b/src/org/openbravo/retail/posterminal/stock/StoreDetailedStock.java --- a/src/org/openbravo/retail/posterminal/stock/StoreDetailedStock.java +++ b/src/org/openbravo/retail/posterminal/stock/StoreDetailedStock.java @@ -42,25 +42,19 @@ : jsonData.getString("organization"); prodId = jsonData.getString("product"); - final StringBuilder hqlQuery = new StringBuilder(); - hqlQuery.append(" select wh.id, wh.name, sb.id, sb.searchKey"); - hqlQuery.append(" , sum(ms.quantityOnHand - ms.reservedQty) as qtyonhand"); - hqlQuery.append(" from MaterialMgmtStorageDetail ms"); - hqlQuery.append(" join ms.storageBin sb"); - hqlQuery.append(" join sb.inventoryStatus ls"); - hqlQuery.append(" join sb.warehouse wh"); - hqlQuery.append(" join wh.organizationWarehouseList ow"); - hqlQuery.append(" where ow.organization.id = :orgId"); - hqlQuery.append(" and ms.product.id = :prodId"); - hqlQuery.append(" and ms.quantityOnHand - ms.reservedQty <> 0"); + String hqlQuery = " select wh.id, wh.name, sb.id, sb.searchKey, sum(ms.quantityOnHand - ms.reservedQty) as qtyonhand " + + " from MaterialMgmtStorageDetail ms join ms.storageBin sb " + + " join sb.inventoryStatus ls join sb.warehouse wh " + + " join wh.organizationWarehouseList ow where ow.organization.id = :orgId " + + " and ms.product.id = :prodId and ms.quantityOnHand - ms.reservedQty <> 0"; if (isCrossStore) { - hqlQuery.append(" and ls.oBRETCOAvailableCrossStore = true"); + hqlQuery += " and ls.oBRETCOAvailableCrossStore = true"; } else { - hqlQuery.append(" and ls.available = true"); + hqlQuery += " and ls.available = true"; } - hqlQuery.append(" and wh.active = true"); - hqlQuery.append(" group by wh.id, wh.name, sb.id, sb.searchKey"); - hqlQuery.append(" order by wh.name"); + hqlQuery += " and wh.active = true"; + hqlQuery += " group by wh.id, wh.name, sb.id, sb.searchKey"; + hqlQuery += " order by wh.name"; final Session session = OBDal.getInstance().getSession(); final Query query = session.createQuery(hqlQuery.toString(), Object[].class); @@ -83,7 +77,7 @@ curWHId = (String) results.get(0); qtyCounterPerWH = BigDecimal.ZERO; } - if (!curWHId.equals((String) results.get(0))) { + if (!curWHId.equals(results.get(0))) { warehouseInfo.put("warehouseid", curWHId); warehouseInfo.put("warehousename", curWHName); warehouseInfo.put("warehouseqty", qtyCounterPerWH); @@ -98,8 +92,8 @@ qtyCounterPerWH = BigDecimal.ZERO; } JSONObject binInfo = new JSONObject(); - binInfo.put("binid", (String) results.get(2)); - binInfo.put("binname", (String) results.get(3)); + binInfo.put("binid", results.get(2)); + binInfo.put("binname", results.get(3)); binInfo.put("binqty", ((BigDecimal) results.get(4)).toString()); arrBinInfo.put(binInfo); diff --git a/src/org/openbravo/retail/posterminal/utility/AttributesUtils.java b/src/org/openbravo/retail/posterminal/utility/AttributesUtils.java --- a/src/org/openbravo/retail/posterminal/utility/AttributesUtils.java +++ b/src/org/openbravo/retail/posterminal/utility/AttributesUtils.java @@ -69,20 +69,14 @@ // Issue 37308: We have found several Att Set Instances with the same description. // Lets try to find one which have stock // inside the warehouses used by the store - StringBuilder stDetailWhereClause = new StringBuilder(); - stDetailWhereClause.append(" as e WHERE "); - stDetailWhereClause.append("e.attributeSetValue.id in ( "); - stDetailWhereClause.append(" select id from AttributeSetInstance attseti "); - stDetailWhereClause.append(" where attseti.description = :attsetdescription "); - stDetailWhereClause.append(") AND "); - stDetailWhereClause.append("e.quantityOnHand > 0 AND "); - stDetailWhereClause.append("e.storageBin.warehouse.id in ( "); - stDetailWhereClause.append(" select warehouse.id from OrganizationWarehouse orgwh "); - stDetailWhereClause.append(" where orgwh.organization.id = :orgid "); - stDetailWhereClause.append(") "); - stDetailWhereClause.append("ORDER BY e.quantityOnHand desc, e.attributeSetValue.id "); + String stDetailWhereClause = " as e WHERE e.attributeSetValue.id " + + " in (select id from AttributeSetInstance attseti " + + " where attseti.description = :attsetdescription) " + + " AND e.quantityOnHand > 0 AND e.storageBin.warehouse.id " + + " in (select warehouse.id from OrganizationWarehouse orgwh " + + " where orgwh.organization.id = :orgid) ORDER BY e.quantityOnHand desc, e.attributeSetValue.id "; OBQuery querySdetail = OBDal.getInstance() - .createQuery(StorageDetail.class, stDetailWhereClause.toString()); + .createQuery(StorageDetail.class, stDetailWhereClause); querySdetail.setNamedParameter("attsetdescription", validatedAttributeSetInstanceDescription); querySdetail.setNamedParameter("orgid", posTerminalOrganizationId); @@ -357,11 +351,9 @@ AttributeSet attributeSet) { List lstAttributes = new ArrayList(); try { - StringBuilder hqlQueryString = new StringBuilder(); - hqlQueryString.append("attributeSet.id = :attSetId "); - hqlQueryString.append("ORDER BY seqNo asc"); + String hqlQueryString = " attributeSet.id = :attSetId ORDER BY seqNo asc "; OBQuery attUseQuery = OBDal.getInstance() - .createQuery(AttributeUse.class, hqlQueryString.toString()); + .createQuery(AttributeUse.class, hqlQueryString); attUseQuery.setNamedParameter("attSetId", attributeSet.getId()); List lstAttUse = attUseQuery.list(); for (AttributeUse attUse : lstAttUse) { @@ -382,11 +374,9 @@ + att.getIdentifier() + "-"); return true; } - StringBuilder hqlQueryString = new StringBuilder(); - hqlQueryString.append("attribute.id = :attId AND "); - hqlQueryString.append("name = :nameValue "); + String hqlQueryString = " attribute.id = :attId AND name = :nameValue "; OBQuery attValueQuery = OBDal.getInstance() - .createQuery(AttributeValue.class, hqlQueryString.toString()); + .createQuery(AttributeValue.class, hqlQueryString); attValueQuery.setNamedParameter("attId", att.getId()); attValueQuery.setNamedParameter("nameValue", value); List lstAttValues = attValueQuery.list();