Examples with ModelKeyMap org.apache.ofbiz.entity.model.ModelKeyMap used on opensource projects

Example 6 with ModelKeyMap

use of org.apache.ofbiz.entity.model.ModelKeyMap in project ofbiz-framework by apache.

the class WebToolsServices method getEntityRefData.

/**
 * Get entity reference data. Returns the number of entities in
 * <code>numberOfEntities</code> and a List of Maps -
 * <code>packagesList</code>.
 * Each Map contains:<br>
 * <ul><li><code>packageName</code> - the entity package name</li>
 * <li><code>entitiesList</code> - a list of Maps:
 *       <ul>
 *         <li><code>entityName</code></li>
 *         <li><code>helperName</code></li>
 *         <li><code>groupName</code></li>
 *         <li><code>plainTableName</code></li>
 *         <li><code>title</code></li>
 *         <li><code>description</code></li>
 *         <!-- <li><code>location</code></li> -->
 *         <li><code>javaNameList</code> - list of Maps:
 *           <ul>
 *             <li><code>isPk</code></li>
 *             <li><code>name</code></li>
 *             <li><code>colName</code></li>
 *             <li><code>description</code></li>
 *             <li><code>type</code></li>
 *             <li><code>javaType</code></li>
 *             <li><code>sqlType</code></li>
 *           </ul>
 *         </li>
 *         <li><code>relationsList</code> - list of Maps:
 *           <ul>
 *             <li><code>title</code></li>
 *             <!-- <li><code>description</code></li> -->
 *             <li><code>relEntity</code></li>
 *             <li><code>fkName</code></li>
 *             <li><code>type</code></li>
 *             <li><code>length</code></li>
 *             <li><code>keysList</code> - list of Maps:
 *               <ul>
 *                 <li><code>row</code></li>
 *                 <li><code>fieldName</code></li>
 *                 <li><code>relFieldName</code></li>
 *               </ul>
 *             </li>
 *           </ul>
 *         </li>
 *         <li><code>indexList</code> - list of Maps:
 *           <ul>
 *             <li><code>name</code></li>
 *             <!-- <li><code>description</code></li> -->
 *             <li><code>fieldNameList</code> - list of Strings</li>
 *           </ul>
 *         </li>
 *       </ul>
 *       </li></ul>
 */
public static Map<String, Object> getEntityRefData(DispatchContext dctx, Map<String, ? extends Object> context) {
    Delegator delegator = dctx.getDelegator();
    Locale locale = (Locale) context.get("locale");
    ClassLoader loader = Thread.currentThread().getContextClassLoader();
    Map<String, Object> resultMap = ServiceUtil.returnSuccess();
    ModelReader reader = delegator.getModelReader();
    Map<String, TreeSet<String>> entitiesByPackage = new HashMap<String, TreeSet<String>>();
    Set<String> packageNames = new TreeSet<String>();
    Set<String> tableNames = new TreeSet<String>();
    // put the entityNames TreeSets in a HashMap by packageName
    try {
        Collection<String> ec = reader.getEntityNames();
        resultMap.put("numberOfEntities", ec.size());
        for (String eName : ec) {
            ModelEntity ent = reader.getModelEntity(eName);
            // make sure the table name is in the list of all table names, if not null
            if (UtilValidate.isNotEmpty(ent.getPlainTableName())) {
                tableNames.add(ent.getPlainTableName());
            }
            TreeSet<String> entities = entitiesByPackage.get(ent.getPackageName());
            if (entities == null) {
                entities = new TreeSet<String>();
                entitiesByPackage.put(ent.getPackageName(), entities);
                packageNames.add(ent.getPackageName());
            }
            entities.add(eName);
        }
    } catch (GenericEntityException e) {
        return ServiceUtil.returnError(UtilProperties.getMessage(resource, "EntityImportErrorRetrievingEntityNames", locale) + e.getMessage());
    }
    String search = (String) context.get("search");
    List<Map<String, Object>> packagesList = new LinkedList<Map<String, Object>>();
    try {
        for (String pName : packageNames) {
            Map<String, Object> packageMap = new HashMap<String, Object>();
            TreeSet<String> entities = entitiesByPackage.get(pName);
            List<Map<String, Object>> entitiesList = new LinkedList<Map<String, Object>>();
            for (String entityName : entities) {
                Map<String, Object> entityMap = new HashMap<String, Object>();
                String helperName = delegator.getEntityHelperName(entityName);
                String groupName = delegator.getEntityGroupName(entityName);
                if (search == null || entityName.toLowerCase().indexOf(search.toLowerCase()) != -1) {
                    ModelEntity entity = reader.getModelEntity(entityName);
                    ResourceBundle bundle = null;
                    if (UtilValidate.isNotEmpty(entity.getDefaultResourceName())) {
                        try {
                            bundle = UtilResourceBundle.getBundle(entity.getDefaultResourceName(), locale, loader);
                        } catch (Exception exception) {
                            Debug.logInfo(exception.getMessage(), module);
                        }
                    }
                    String entityDescription = null;
                    if (bundle != null) {
                        try {
                            entityDescription = bundle.getString("EntityDescription." + entity.getEntityName());
                        } catch (Exception exception) {
                        }
                    }
                    if (UtilValidate.isEmpty(entityDescription)) {
                        entityDescription = entity.getDescription();
                    }
                    // fields list
                    List<Map<String, Object>> javaNameList = new LinkedList<Map<String, Object>>();
                    for (Iterator<ModelField> f = entity.getFieldsIterator(); f.hasNext(); ) {
                        Map<String, Object> javaNameMap = new HashMap<String, Object>();
                        ModelField field = f.next();
                        ModelFieldType type = delegator.getEntityFieldType(entity, field.getType());
                        javaNameMap.put("isPk", field.getIsPk());
                        javaNameMap.put("name", field.getName());
                        javaNameMap.put("colName", field.getColName());
                        String fieldDescription = null;
                        if (bundle != null) {
                            try {
                                fieldDescription = bundle.getString("FieldDescription." + entity.getEntityName() + "." + field.getName());
                            } catch (Exception exception) {
                            }
                        }
                        if (UtilValidate.isEmpty(fieldDescription)) {
                            fieldDescription = field.getDescription();
                        }
                        if (UtilValidate.isEmpty(fieldDescription) && bundle != null) {
                            try {
                                fieldDescription = bundle.getString("FieldDescription." + field.getName());
                            } catch (Exception exception) {
                            }
                        }
                        if (UtilValidate.isEmpty(fieldDescription)) {
                            fieldDescription = ModelUtil.javaNameToDbName(field.getName()).toLowerCase();
                            fieldDescription = ModelUtil.upperFirstChar(fieldDescription.replace('_', ' '));
                        }
                        javaNameMap.put("description", fieldDescription);
                        javaNameMap.put("type", (field.getType()) != null ? field.getType() : null);
                        javaNameMap.put("javaType", (field.getType() != null && type != null) ? type.getJavaType() : "Undefined");
                        javaNameMap.put("sqlType", (type != null && type.getSqlType() != null) ? type.getSqlType() : "Undefined");
                        javaNameMap.put("encrypted", field.getEncryptMethod().isEncrypted());
                        javaNameMap.put("encryptMethod", field.getEncryptMethod());
                        javaNameList.add(javaNameMap);
                    }
                    // relations list
                    List<Map<String, Object>> relationsList = new LinkedList<Map<String, Object>>();
                    for (int r = 0; r < entity.getRelationsSize(); r++) {
                        Map<String, Object> relationMap = new HashMap<String, Object>();
                        ModelRelation relation = entity.getRelation(r);
                        List<Map<String, Object>> keysList = new LinkedList<Map<String, Object>>();
                        int row = 1;
                        for (ModelKeyMap keyMap : relation.getKeyMaps()) {
                            Map<String, Object> keysMap = new HashMap<String, Object>();
                            String fieldName = null;
                            String relFieldName = null;
                            if (keyMap.getFieldName().equals(keyMap.getRelFieldName())) {
                                fieldName = keyMap.getFieldName();
                                relFieldName = "aa";
                            } else {
                                fieldName = keyMap.getFieldName();
                                relFieldName = keyMap.getRelFieldName();
                            }
                            keysMap.put("row", row++);
                            keysMap.put("fieldName", fieldName);
                            keysMap.put("relFieldName", relFieldName);
                            keysList.add(keysMap);
                        }
                        relationMap.put("title", relation.getTitle());
                        relationMap.put("description", relation.getDescription());
                        relationMap.put("relEntity", relation.getRelEntityName());
                        relationMap.put("fkName", relation.getFkName());
                        relationMap.put("type", relation.getType());
                        relationMap.put("length", relation.getType().length());
                        relationMap.put("keysList", keysList);
                        relationsList.add(relationMap);
                    }
                    // index list
                    List<Map<String, Object>> indexList = new LinkedList<Map<String, Object>>();
                    for (int r = 0; r < entity.getIndexesSize(); r++) {
                        List<String> fieldNameList = new LinkedList<String>();
                        ModelIndex index = entity.getIndex(r);
                        for (Iterator<ModelIndex.Field> fieldIterator = index.getFields().iterator(); fieldIterator.hasNext(); ) {
                            fieldNameList.add(fieldIterator.next().getFieldName());
                        }
                        Map<String, Object> indexMap = new HashMap<String, Object>();
                        indexMap.put("name", index.getName());
                        indexMap.put("description", index.getDescription());
                        indexMap.put("fieldNameList", fieldNameList);
                        indexList.add(indexMap);
                    }
                    entityMap.put("entityName", entityName);
                    entityMap.put("helperName", helperName);
                    entityMap.put("groupName", groupName);
                    entityMap.put("plainTableName", entity.getPlainTableName());
                    entityMap.put("title", entity.getTitle());
                    entityMap.put("description", entityDescription);
                    String entityLocation = entity.getLocation();
                    entityLocation = entityLocation.replaceFirst(System.getProperty("ofbiz.home") + "/", "");
                    entityMap.put("location", entityLocation);
                    entityMap.put("javaNameList", javaNameList);
                    entityMap.put("relationsList", relationsList);
                    entityMap.put("indexList", indexList);
                    entitiesList.add(entityMap);
                }
            }
            packageMap.put("packageName", pName);
            packageMap.put("entitiesList", entitiesList);
            packagesList.add(packageMap);
        }
    } catch (GenericEntityException e) {
        return ServiceUtil.returnError(UtilProperties.getMessage(resource, "EntityImportErrorRetrievingEntityNames", locale) + e.getMessage());
    }
    resultMap.put("packagesList", packagesList);
    return resultMap;
}

Example 7 with ModelKeyMap

use of org.apache.ofbiz.entity.model.ModelKeyMap in project ofbiz-framework by apache.

the class DatabaseUtil method makeFkIndexClause.

public String makeFkIndexClause(ModelEntity entity, ModelRelation modelRelation, int constraintNameClipLength) {
    StringBuilder mainCols = new StringBuilder();
    for (ModelKeyMap keyMap : modelRelation.getKeyMaps()) {
        ModelField mainField = entity.getField(keyMap.getFieldName());
        if (mainField == null) {
            Debug.logError("Bad key-map in entity [" + entity.getEntityName() + "] relation to [" + modelRelation.getTitle() + modelRelation.getRelEntityName() + "] for field [" + keyMap.getFieldName() + "]", module);
            return null;
        }
        if (mainCols.length() > 0) {
            mainCols.append(", ");
        }
        mainCols.append(mainField.getColName());
    }
    StringBuilder indexSqlBuf = new StringBuilder("CREATE INDEX ");
    String relConstraintName = makeFkConstraintName(modelRelation, constraintNameClipLength);
    indexSqlBuf.append(relConstraintName);
    indexSqlBuf.append(" ON ");
    indexSqlBuf.append(entity.getTableName(datasourceInfo));
    indexSqlBuf.append(" (");
    indexSqlBuf.append(mainCols.toString());
    indexSqlBuf.append(")");
    return indexSqlBuf.toString();
}

Example 8 with ModelKeyMap

use of org.apache.ofbiz.entity.model.ModelKeyMap in project ofbiz-framework by apache.

the class SqlJdbcUtil method makeViewWhereClause.

public static String makeViewWhereClause(ModelEntity modelEntity, String joinStyle) throws GenericEntityException {
    if (modelEntity instanceof ModelViewEntity) {
        StringBuilder whereString = new StringBuilder();
        ModelViewEntity modelViewEntity = (ModelViewEntity) modelEntity;
        if ("ansi".equals(joinStyle) || "ansi-no-parenthesis".equals(joinStyle)) {
        // nothing to do here, all done in the JOIN clauses
        } else if ("theta-oracle".equals(joinStyle) || "theta-mssql".equals(joinStyle)) {
            boolean isOracleStyle = "theta-oracle".equals(joinStyle);
            boolean isMssqlStyle = "theta-mssql".equals(joinStyle);
            for (int i = 0; i < modelViewEntity.getViewLinksSize(); i++) {
                ModelViewEntity.ModelViewLink viewLink = modelViewEntity.getViewLink(i);
                ModelEntity linkEntity = modelViewEntity.getMemberModelEntity(viewLink.getEntityAlias());
                ModelEntity relLinkEntity = modelViewEntity.getMemberModelEntity(viewLink.getRelEntityAlias());
                if (linkEntity == null) {
                    throw new GenericEntityException("Link entity not found with alias: " + viewLink.getEntityAlias() + " for entity: " + modelViewEntity.getEntityName());
                }
                if (relLinkEntity == null) {
                    throw new GenericEntityException("Rel-Link entity not found with alias: " + viewLink.getRelEntityAlias() + " for entity: " + modelViewEntity.getEntityName());
                }
                for (int j = 0; j < viewLink.getKeyMapsSize(); j++) {
                    ModelKeyMap keyMap = viewLink.getKeyMap(j);
                    ModelField linkField = linkEntity.getField(keyMap.getFieldName());
                    ModelField relLinkField = relLinkEntity.getField(keyMap.getRelFieldName());
                    if (whereString.length() > 0) {
                        whereString.append(" AND ");
                    }
                    whereString.append(viewLink.getEntityAlias());
                    whereString.append(".");
                    whereString.append(linkField.getColName());
                    // if (isOracleStyle && linkMemberEntity.getOptional()) whereString.append(" (+) ");
                    if (isMssqlStyle && viewLink.isRelOptional())
                        whereString.append("*");
                    whereString.append("=");
                    // if (isMssqlStyle && linkMemberEntity.getOptional()) whereString.append("*");
                    if (isOracleStyle && viewLink.isRelOptional())
                        whereString.append(" (+) ");
                    whereString.append(viewLink.getRelEntityAlias());
                    whereString.append(".");
                    whereString.append(relLinkField.getColName());
                }
            }
        } else {
            throw new GenericModelException("The join-style " + joinStyle + " is not supported");
        }
        if (whereString.length() > 0) {
            return "(" + whereString.toString() + ")";
        }
    }
    return "";
}

Example 9 with ModelKeyMap

use of org.apache.ofbiz.entity.model.ModelKeyMap in project ofbiz-framework by apache.

the class OrderLookupServices method findOrders.

public static Map<String, Object> findOrders(DispatchContext dctx, Map<String, ? extends Object> context) {
    LocalDispatcher dispatcher = dctx.getDispatcher();
    Delegator delegator = dctx.getDelegator();
    Security security = dctx.getSecurity();
    GenericValue userLogin = (GenericValue) context.get("userLogin");
    Integer viewIndex = Paginator.getViewIndex(context, "viewIndex", 1);
    Integer viewSize = Paginator.getViewSize(context, "viewSize");
    String showAll = (String) context.get("showAll");
    String useEntryDate = (String) context.get("useEntryDate");
    Locale locale = (Locale) context.get("locale");
    if (showAll == null) {
        showAll = "N";
    }
    // list of fields to select (initial list)
    Set<String> fieldsToSelect = new LinkedHashSet<>();
    fieldsToSelect.add("orderId");
    fieldsToSelect.add("orderName");
    fieldsToSelect.add("statusId");
    fieldsToSelect.add("orderTypeId");
    fieldsToSelect.add("orderDate");
    fieldsToSelect.add("currencyUom");
    fieldsToSelect.add("grandTotal");
    fieldsToSelect.add("remainingSubTotal");
    // sorting by order date newest first
    List<String> orderBy = UtilMisc.toList("-orderDate", "-orderId");
    // list to hold the parameters
    List<String> paramList = new LinkedList<>();
    // list of conditions
    List<EntityCondition> conditions = new LinkedList<>();
    // check security flag for purchase orders
    boolean canViewPo = security.hasEntityPermission("ORDERMGR", "_PURCHASE_VIEW", userLogin);
    if (!canViewPo) {
        conditions.add(EntityCondition.makeCondition("orderTypeId", EntityOperator.NOT_EQUAL, "PURCHASE_ORDER"));
    }
    // dynamic view entity
    DynamicViewEntity dve = new DynamicViewEntity();
    dve.addMemberEntity("OH", "OrderHeader");
    // no prefix
    dve.addAliasAll("OH", "", null);
    dve.addRelation("one-nofk", "", "OrderType", UtilMisc.toList(new ModelKeyMap("orderTypeId", "orderTypeId")));
    dve.addRelation("one-nofk", "", "StatusItem", UtilMisc.toList(new ModelKeyMap("statusId", "statusId")));
    // start the lookup
    String orderId = (String) context.get("orderId");
    if (UtilValidate.isNotEmpty(orderId)) {
        paramList.add("orderId=" + orderId);
        conditions.add(makeExpr("orderId", orderId));
    }
    // the base order header fields
    List<String> orderTypeList = UtilGenerics.checkList(context.get("orderTypeId"));
    if (orderTypeList != null) {
        List<EntityExpr> orExprs = new LinkedList<>();
        for (String orderTypeId : orderTypeList) {
            paramList.add("orderTypeId=" + orderTypeId);
            if (!("PURCHASE_ORDER".equals(orderTypeId)) || (("PURCHASE_ORDER".equals(orderTypeId) && canViewPo))) {
                orExprs.add(EntityCondition.makeCondition("orderTypeId", EntityOperator.EQUALS, orderTypeId));
            }
        }
        conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
    }
    String orderName = (String) context.get("orderName");
    if (UtilValidate.isNotEmpty(orderName)) {
        paramList.add("orderName=" + orderName);
        conditions.add(makeExpr("orderName", orderName, true));
    }
    List<String> orderStatusList = UtilGenerics.checkList(context.get("orderStatusId"));
    if (orderStatusList != null) {
        List<EntityCondition> orExprs = new LinkedList<>();
        for (String orderStatusId : orderStatusList) {
            paramList.add("orderStatusId=" + orderStatusId);
            if ("PENDING".equals(orderStatusId)) {
                List<EntityExpr> pendExprs = new LinkedList<>();
                pendExprs.add(EntityCondition.makeCondition("statusId", EntityOperator.EQUALS, "ORDER_CREATED"));
                pendExprs.add(EntityCondition.makeCondition("statusId", EntityOperator.EQUALS, "ORDER_PROCESSING"));
                pendExprs.add(EntityCondition.makeCondition("statusId", EntityOperator.EQUALS, "ORDER_APPROVED"));
                orExprs.add(EntityCondition.makeCondition(pendExprs, EntityOperator.OR));
            } else {
                orExprs.add(EntityCondition.makeCondition("statusId", EntityOperator.EQUALS, orderStatusId));
            }
        }
        conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
    }
    List<String> productStoreList = UtilGenerics.checkList(context.get("productStoreId"));
    if (productStoreList != null) {
        List<EntityExpr> orExprs = new LinkedList<>();
        for (String productStoreId : productStoreList) {
            paramList.add("productStoreId=" + productStoreId);
            orExprs.add(EntityCondition.makeCondition("productStoreId", EntityOperator.EQUALS, productStoreId));
        }
        conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
    }
    List<String> webSiteList = UtilGenerics.checkList(context.get("orderWebSiteId"));
    if (webSiteList != null) {
        List<EntityExpr> orExprs = new LinkedList<>();
        for (String webSiteId : webSiteList) {
            paramList.add("webSiteId=" + webSiteId);
            orExprs.add(EntityCondition.makeCondition("webSiteId", EntityOperator.EQUALS, webSiteId));
        }
        conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
    }
    List<String> saleChannelList = UtilGenerics.checkList(context.get("salesChannelEnumId"));
    if (saleChannelList != null) {
        List<EntityExpr> orExprs = new LinkedList<>();
        for (String salesChannelEnumId : saleChannelList) {
            paramList.add("salesChannelEnumId=" + salesChannelEnumId);
            orExprs.add(EntityCondition.makeCondition("salesChannelEnumId", EntityOperator.EQUALS, salesChannelEnumId));
        }
        conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
    }
    String createdBy = (String) context.get("createdBy");
    if (UtilValidate.isNotEmpty(createdBy)) {
        paramList.add("createdBy=" + createdBy);
        conditions.add(makeExpr("createdBy", createdBy));
    }
    String terminalId = (String) context.get("terminalId");
    if (UtilValidate.isNotEmpty(terminalId)) {
        paramList.add("terminalId=" + terminalId);
        conditions.add(makeExpr("terminalId", terminalId));
    }
    String transactionId = (String) context.get("transactionId");
    if (UtilValidate.isNotEmpty(transactionId)) {
        paramList.add("transactionId=" + transactionId);
        conditions.add(makeExpr("transactionId", transactionId));
    }
    String externalId = (String) context.get("externalId");
    if (UtilValidate.isNotEmpty(externalId)) {
        paramList.add("externalId=" + externalId);
        conditions.add(makeExpr("externalId", externalId));
    }
    String internalCode = (String) context.get("internalCode");
    if (UtilValidate.isNotEmpty(internalCode)) {
        paramList.add("internalCode=" + internalCode);
        conditions.add(makeExpr("internalCode", internalCode));
    }
    String dateField = "Y".equals(useEntryDate) ? "entryDate" : "orderDate";
    String minDate = (String) context.get("minDate");
    if (UtilValidate.isNotEmpty(minDate) && minDate.length() > 8) {
        minDate = minDate.trim();
        if (minDate.length() < 14) {
            minDate = minDate + " " + "00:00:00.000";
        }
        paramList.add("minDate=" + minDate);
        try {
            Object converted = ObjectType.simpleTypeConvert(minDate, "Timestamp", null, null);
            if (converted != null) {
                conditions.add(EntityCondition.makeCondition(dateField, EntityOperator.GREATER_THAN_EQUAL_TO, converted));
            }
        } catch (GeneralException e) {
            Debug.logWarning(e.getMessage(), module);
        }
    }
    String maxDate = (String) context.get("maxDate");
    if (UtilValidate.isNotEmpty(maxDate) && maxDate.length() > 8) {
        maxDate = maxDate.trim();
        if (maxDate.length() < 14) {
            maxDate = maxDate + " " + "23:59:59.999";
        }
        paramList.add("maxDate=" + maxDate);
        try {
            Object converted = ObjectType.simpleTypeConvert(maxDate, "Timestamp", null, null);
            if (converted != null) {
                conditions.add(EntityCondition.makeCondition("orderDate", EntityOperator.LESS_THAN_EQUAL_TO, converted));
            }
        } catch (GeneralException e) {
            Debug.logWarning(e.getMessage(), module);
        }
    }
    // party (role) fields
    String userLoginId = (String) context.get("userLoginId");
    String partyId = (String) context.get("partyId");
    List<String> roleTypeList = UtilGenerics.checkList(context.get("roleTypeId"));
    if (UtilValidate.isNotEmpty(userLoginId) && UtilValidate.isEmpty(partyId)) {
        GenericValue ul = null;
        try {
            ul = EntityQuery.use(delegator).from("UserLogin").where("userLoginId", userLoginId).cache().queryOne();
        } catch (GenericEntityException e) {
            Debug.logWarning(e.getMessage(), module);
        }
        if (ul != null) {
            partyId = ul.getString("partyId");
        }
    }
    String isViewed = (String) context.get("isViewed");
    if (UtilValidate.isNotEmpty(isViewed)) {
        paramList.add("isViewed=" + isViewed);
        conditions.add(makeExpr("isViewed", isViewed));
    }
    // Shipment Method
    String shipmentMethod = (String) context.get("shipmentMethod");
    if (UtilValidate.isNotEmpty(shipmentMethod)) {
        String carrierPartyId = shipmentMethod.substring(0, shipmentMethod.indexOf('@'));
        String ShippingMethodTypeId = shipmentMethod.substring(shipmentMethod.indexOf('@') + 1);
        dve.addMemberEntity("OISG", "OrderItemShipGroup");
        dve.addAlias("OISG", "shipmentMethodTypeId");
        dve.addAlias("OISG", "carrierPartyId");
        dve.addViewLink("OH", "OISG", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
        if (UtilValidate.isNotEmpty(carrierPartyId)) {
            paramList.add("carrierPartyId=" + carrierPartyId);
            conditions.add(makeExpr("carrierPartyId", carrierPartyId));
        }
        if (UtilValidate.isNotEmpty(ShippingMethodTypeId)) {
            paramList.add("ShippingMethodTypeId=" + ShippingMethodTypeId);
            conditions.add(makeExpr("shipmentMethodTypeId", ShippingMethodTypeId));
        }
    }
    // PaymentGatewayResponse
    String gatewayAvsResult = (String) context.get("gatewayAvsResult");
    String gatewayScoreResult = (String) context.get("gatewayScoreResult");
    if (UtilValidate.isNotEmpty(gatewayAvsResult) || UtilValidate.isNotEmpty(gatewayScoreResult)) {
        dve.addMemberEntity("OPP", "OrderPaymentPreference");
        dve.addMemberEntity("PGR", "PaymentGatewayResponse");
        dve.addAlias("OPP", "orderPaymentPreferenceId");
        dve.addAlias("PGR", "gatewayAvsResult");
        dve.addAlias("PGR", "gatewayScoreResult");
        dve.addViewLink("OH", "OPP", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
        dve.addViewLink("OPP", "PGR", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderPaymentPreferenceId", "orderPaymentPreferenceId")));
    }
    if (UtilValidate.isNotEmpty(gatewayAvsResult)) {
        paramList.add("gatewayAvsResult=" + gatewayAvsResult);
        conditions.add(EntityCondition.makeCondition("gatewayAvsResult", gatewayAvsResult));
    }
    if (UtilValidate.isNotEmpty(gatewayScoreResult)) {
        paramList.add("gatewayScoreResult=" + gatewayScoreResult);
        conditions.add(EntityCondition.makeCondition("gatewayScoreResult", gatewayScoreResult));
    }
    // add the role data to the view
    if (roleTypeList != null || partyId != null) {
        dve.addMemberEntity("OT", "OrderRole");
        dve.addAlias("OT", "partyId");
        dve.addAlias("OT", "roleTypeId");
        dve.addViewLink("OH", "OT", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
    }
    if (UtilValidate.isNotEmpty(partyId)) {
        paramList.add("partyId=" + partyId);
        fieldsToSelect.add("partyId");
        conditions.add(makeExpr("partyId", partyId));
    }
    if (roleTypeList != null) {
        fieldsToSelect.add("roleTypeId");
        List<EntityExpr> orExprs = new LinkedList<>();
        for (String roleTypeId : roleTypeList) {
            paramList.add("roleTypeId=" + roleTypeId);
            orExprs.add(makeExpr("roleTypeId", roleTypeId));
        }
        conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
    }
    // order item fields
    String correspondingPoId = (String) context.get("correspondingPoId");
    String subscriptionId = (String) context.get("subscriptionId");
    String productId = (String) context.get("productId");
    String budgetId = (String) context.get("budgetId");
    String quoteId = (String) context.get("quoteId");
    String goodIdentificationTypeId = (String) context.get("goodIdentificationTypeId");
    String goodIdentificationIdValue = (String) context.get("goodIdentificationIdValue");
    boolean hasGoodIdentification = UtilValidate.isNotEmpty(goodIdentificationTypeId) && UtilValidate.isNotEmpty(goodIdentificationIdValue);
    if (correspondingPoId != null || subscriptionId != null || productId != null || budgetId != null || quoteId != null || hasGoodIdentification) {
        dve.addMemberEntity("OI", "OrderItem");
        dve.addAlias("OI", "correspondingPoId");
        dve.addAlias("OI", "subscriptionId");
        dve.addAlias("OI", "productId");
        dve.addAlias("OI", "budgetId");
        dve.addAlias("OI", "quoteId");
        dve.addViewLink("OH", "OI", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
        if (hasGoodIdentification) {
            dve.addMemberEntity("GOODID", "GoodIdentification");
            dve.addAlias("GOODID", "goodIdentificationTypeId");
            dve.addAlias("GOODID", "idValue");
            dve.addViewLink("OI", "GOODID", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("productId", "productId")));
            paramList.add("goodIdentificationTypeId=" + goodIdentificationTypeId);
            conditions.add(makeExpr("goodIdentificationTypeId", goodIdentificationTypeId));
            paramList.add("goodIdentificationIdValue=" + goodIdentificationIdValue);
            conditions.add(makeExpr("idValue", goodIdentificationIdValue));
        }
    }
    if (UtilValidate.isNotEmpty(correspondingPoId)) {
        paramList.add("correspondingPoId=" + correspondingPoId);
        conditions.add(makeExpr("correspondingPoId", correspondingPoId));
    }
    if (UtilValidate.isNotEmpty(subscriptionId)) {
        paramList.add("subscriptionId=" + subscriptionId);
        conditions.add(makeExpr("subscriptionId", subscriptionId));
    }
    if (UtilValidate.isNotEmpty(productId)) {
        paramList.add("productId=" + productId);
        if (productId.startsWith("%") || productId.startsWith("*") || productId.endsWith("%") || productId.endsWith("*")) {
            conditions.add(makeExpr("productId", productId));
        } else {
            GenericValue product = null;
            try {
                product = EntityQuery.use(delegator).from("Product").where("productId", productId).queryOne();
            } catch (GenericEntityException e) {
                Debug.logWarning(e.getMessage(), module);
            }
            if (product != null) {
                String isVirtual = product.getString("isVirtual");
                if (isVirtual != null && "Y".equals(isVirtual)) {
                    List<EntityExpr> orExprs = new LinkedList<>();
                    orExprs.add(EntityCondition.makeCondition("productId", EntityOperator.EQUALS, productId));
                    Map<String, Object> varLookup = null;
                    List<GenericValue> variants = null;
                    try {
                        varLookup = dispatcher.runSync("getAllProductVariants", UtilMisc.toMap("productId", productId));
                        if (ServiceUtil.isError(varLookup)) {
                            return ServiceUtil.returnError(ServiceUtil.getErrorMessage(varLookup));
                        }
                        variants = UtilGenerics.checkList(varLookup.get("assocProducts"));
                    } catch (GenericServiceException e) {
                        Debug.logWarning(e.getMessage(), module);
                    }
                    if (variants != null) {
                        for (GenericValue v : variants) {
                            orExprs.add(EntityCondition.makeCondition("productId", EntityOperator.EQUALS, v.getString("productIdTo")));
                        }
                    }
                    conditions.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
                } else {
                    conditions.add(EntityCondition.makeCondition("productId", EntityOperator.EQUALS, productId));
                }
            } else {
                String failMsg = UtilProperties.getMessage("OrderErrorUiLabels", "OrderFindOrderProductInvalid", UtilMisc.toMap("productId", productId), locale);
                return ServiceUtil.returnFailure(failMsg);
            }
        }
    }
    if (UtilValidate.isNotEmpty(budgetId)) {
        paramList.add("budgetId=" + budgetId);
        conditions.add(makeExpr("budgetId", budgetId));
    }
    if (UtilValidate.isNotEmpty(quoteId)) {
        paramList.add("quoteId=" + quoteId);
        conditions.add(makeExpr("quoteId", quoteId));
    }
    // payment preference fields
    String billingAccountId = (String) context.get("billingAccountId");
    String finAccountId = (String) context.get("finAccountId");
    String cardNumber = (String) context.get("cardNumber");
    String accountNumber = (String) context.get("accountNumber");
    String paymentStatusId = (String) context.get("paymentStatusId");
    if (UtilValidate.isNotEmpty(paymentStatusId)) {
        paramList.add("paymentStatusId=" + paymentStatusId);
        conditions.add(makeExpr("paymentStatusId", paymentStatusId));
    }
    if (finAccountId != null || cardNumber != null || accountNumber != null || paymentStatusId != null) {
        dve.addMemberEntity("OP", "OrderPaymentPreference");
        dve.addAlias("OP", "finAccountId");
        dve.addAlias("OP", "paymentMethodId");
        dve.addAlias("OP", "paymentStatusId", "statusId", null, false, false, null);
        dve.addViewLink("OH", "OP", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
    }
    // search by billing account ID
    if (UtilValidate.isNotEmpty(billingAccountId)) {
        paramList.add("billingAccountId=" + billingAccountId);
        conditions.add(makeExpr("billingAccountId", billingAccountId));
    }
    // search by fin account ID
    if (UtilValidate.isNotEmpty(finAccountId)) {
        paramList.add("finAccountId=" + finAccountId);
        conditions.add(makeExpr("finAccountId", finAccountId));
    }
    // search by card number
    if (UtilValidate.isNotEmpty(cardNumber)) {
        dve.addMemberEntity("CC", "CreditCard");
        dve.addAlias("CC", "cardNumber");
        dve.addViewLink("OP", "CC", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("paymentMethodId", "paymentMethodId")));
        paramList.add("cardNumber=" + cardNumber);
        conditions.add(makeExpr("cardNumber", cardNumber));
    }
    // search by eft account number
    if (UtilValidate.isNotEmpty(accountNumber)) {
        dve.addMemberEntity("EF", "EftAccount");
        dve.addAlias("EF", "accountNumber");
        dve.addViewLink("OP", "EF", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("paymentMethodId", "paymentMethodId")));
        paramList.add("accountNumber=" + accountNumber);
        conditions.add(makeExpr("accountNumber", accountNumber));
    }
    // shipment/inventory item
    String inventoryItemId = (String) context.get("inventoryItemId");
    String softIdentifier = (String) context.get("softIdentifier");
    String serialNumber = (String) context.get("serialNumber");
    String shipmentId = (String) context.get("shipmentId");
    if (shipmentId != null || inventoryItemId != null || softIdentifier != null || serialNumber != null) {
        dve.addMemberEntity("II", "ItemIssuance");
        dve.addAlias("II", "shipmentId");
        dve.addAlias("II", "inventoryItemId");
        dve.addViewLink("OH", "II", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
        if (softIdentifier != null || serialNumber != null) {
            dve.addMemberEntity("IV", "InventoryItem");
            dve.addAlias("IV", "softIdentifier");
            dve.addAlias("IV", "serialNumber");
            dve.addViewLink("II", "IV", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("inventoryItemId", "inventoryItemId")));
        }
    }
    if (UtilValidate.isNotEmpty(inventoryItemId)) {
        paramList.add("inventoryItemId=" + inventoryItemId);
        conditions.add(makeExpr("inventoryItemId", inventoryItemId));
    }
    if (UtilValidate.isNotEmpty(softIdentifier)) {
        paramList.add("softIdentifier=" + softIdentifier);
        conditions.add(makeExpr("softIdentifier", softIdentifier, true));
    }
    if (UtilValidate.isNotEmpty(serialNumber)) {
        paramList.add("serialNumber=" + serialNumber);
        conditions.add(makeExpr("serialNumber", serialNumber, true));
    }
    if (UtilValidate.isNotEmpty(shipmentId)) {
        paramList.add("shipmentId=" + shipmentId);
        conditions.add(makeExpr("shipmentId", shipmentId));
    }
    // back order checking
    String hasBackOrders = (String) context.get("hasBackOrders");
    if (UtilValidate.isNotEmpty(hasBackOrders)) {
        dve.addMemberEntity("IR", "OrderItemShipGrpInvRes");
        dve.addAlias("IR", "quantityNotAvailable");
        dve.addViewLink("OH", "IR", Boolean.FALSE, UtilMisc.toList(new ModelKeyMap("orderId", "orderId")));
        paramList.add("hasBackOrders=" + hasBackOrders);
        if ("Y".equals(hasBackOrders)) {
            conditions.add(EntityCondition.makeCondition("quantityNotAvailable", EntityOperator.NOT_EQUAL, null));
            conditions.add(EntityCondition.makeCondition("quantityNotAvailable", EntityOperator.GREATER_THAN, BigDecimal.ZERO));
        } else if ("N".equals(hasBackOrders)) {
            List<EntityExpr> orExpr = new LinkedList<>();
            orExpr.add(EntityCondition.makeCondition("quantityNotAvailable", EntityOperator.EQUALS, null));
            orExpr.add(EntityCondition.makeCondition("quantityNotAvailable", EntityOperator.EQUALS, BigDecimal.ZERO));
            conditions.add(EntityCondition.makeCondition(orExpr, EntityOperator.OR));
        }
    }
    // Get all orders according to specific ship to country with "Only Include" or "Do not Include".
    String countryGeoId = (String) context.get("countryGeoId");
    String includeCountry = (String) context.get("includeCountry");
    if (UtilValidate.isNotEmpty(countryGeoId) && UtilValidate.isNotEmpty(includeCountry)) {
        paramList.add("countryGeoId=" + countryGeoId);
        paramList.add("includeCountry=" + includeCountry);
        // add condition to dynamic view
        dve.addMemberEntity("OCM", "OrderContactMech");
        dve.addMemberEntity("PA", "PostalAddress");
        dve.addAlias("OCM", "contactMechId");
        dve.addAlias("OCM", "contactMechPurposeTypeId");
        dve.addAlias("PA", "countryGeoId");
        dve.addViewLink("OH", "OCM", Boolean.FALSE, ModelKeyMap.makeKeyMapList("orderId"));
        dve.addViewLink("OCM", "PA", Boolean.FALSE, ModelKeyMap.makeKeyMapList("contactMechId"));
        EntityConditionList<EntityExpr> exprs = null;
        if ("Y".equals(includeCountry)) {
            exprs = EntityCondition.makeCondition(UtilMisc.toList(EntityCondition.makeCondition("contactMechPurposeTypeId", "SHIPPING_LOCATION"), EntityCondition.makeCondition("countryGeoId", countryGeoId)), EntityOperator.AND);
        } else {
            exprs = EntityCondition.makeCondition(UtilMisc.toList(EntityCondition.makeCondition("contactMechPurposeTypeId", "SHIPPING_LOCATION"), EntityCondition.makeCondition("countryGeoId", EntityOperator.NOT_EQUAL, countryGeoId)), EntityOperator.AND);
        }
        conditions.add(exprs);
    }
    // create the main condition
    EntityCondition cond = null;
    if (conditions.size() > 0 || "Y".equalsIgnoreCase(showAll)) {
        cond = EntityCondition.makeCondition(conditions, EntityOperator.AND);
    }
    if (Debug.verboseOn()) {
        Debug.logInfo("Find order query: " + cond.toString(), module);
    }
    List<GenericValue> orderList = new LinkedList<>();
    int orderCount = 0;
    // get the index for the partial list
    int lowIndex = 0;
    int highIndex = 0;
    if (cond != null) {
        PagedList<GenericValue> pagedOrderList = null;
        try {
            // do the lookup
            pagedOrderList = EntityQuery.use(delegator).select(fieldsToSelect).from(dve).where(cond).orderBy(orderBy).distinct().cursorScrollInsensitive().queryPagedList(viewIndex - 1, viewSize);
            orderCount = pagedOrderList.getSize();
            lowIndex = pagedOrderList.getStartIndex();
            highIndex = pagedOrderList.getEndIndex();
            orderList = pagedOrderList.getData();
        } catch (GenericEntityException e) {
            Debug.logError(e.getMessage(), module);
            return ServiceUtil.returnError(e.getMessage());
        }
    }
    // create the result map
    Map<String, Object> result = ServiceUtil.returnSuccess();
    // filter out requested inventory problems
    filterInventoryProblems(context, result, orderList, paramList);
    // format the param list
    String paramString = StringUtil.join(paramList, "&amp;");
    result.put("highIndex", Integer.valueOf(highIndex));
    result.put("lowIndex", Integer.valueOf(lowIndex));
    result.put("viewIndex", viewIndex);
    result.put("viewSize", viewSize);
    result.put("showAll", showAll);
    result.put("paramList", (paramString != null ? paramString : ""));
    result.put("orderList", orderList);
    result.put("orderListSize", Integer.valueOf(orderCount));
    return result;
}

Example 10 with ModelKeyMap

use of org.apache.ofbiz.entity.model.ModelKeyMap in project ofbiz-framework by apache.

the class SqlJdbcUtil method makeFromClause.

/**
 * Makes the FROM clause and when necessary the JOIN clause(s) as well
 */
public static String makeFromClause(ModelEntity modelEntity, ModelFieldTypeReader modelFieldTypeReader, Datasource datasourceInfo) throws GenericEntityException {
    StringBuilder sql = new StringBuilder(" FROM ");
    if (modelEntity instanceof ModelViewEntity) {
        ModelViewEntity modelViewEntity = (ModelViewEntity) modelEntity;
        if ("ansi".equals(datasourceInfo.getJoinStyle()) || "ansi-no-parenthesis".equals(datasourceInfo.getJoinStyle())) {
            boolean useParenthesis = true;
            if ("ansi-no-parenthesis".equals(datasourceInfo.getJoinStyle())) {
                useParenthesis = false;
            }
            // FROM clause: in this case will be a bunch of joins that correspond with the view-links
            // BIG NOTE on the JOIN clauses: the order of joins is determined by the order of the
            // view-links; for more flexible order we'll have to figure something else out and
            // extend the DTD for the nested view-link elements or something
            // At this point it is assumed that in each view-link the left hand alias will
            // either be the first alias in the series or will already be in a previous
            // view-link and already be in the big join; SO keep a set of all aliases
            // in the join so far and if the left entity alias isn't there yet, and this
            // isn't the first one, throw an exception
            Set<String> joinedAliasSet = new TreeSet<String>();
            // TODO: at view-link read time make sure they are ordered properly so that each
            // left hand alias after the first view-link has already been linked before
            StringBuilder openParens = null;
            if (useParenthesis)
                openParens = new StringBuilder();
            StringBuilder restOfStatement = new StringBuilder();
            for (int i = 0; i < modelViewEntity.getViewLinksSize(); i++) {
                // don't put starting parenthesis
                if (i > 0 && useParenthesis)
                    openParens.append('(');
                ModelViewEntity.ModelViewLink viewLink = modelViewEntity.getViewLink(i);
                ModelEntity linkEntity = modelViewEntity.getMemberModelEntity(viewLink.getEntityAlias());
                ModelEntity relLinkEntity = modelViewEntity.getMemberModelEntity(viewLink.getRelEntityAlias());
                if (i == 0) {
                    // this is the first referenced member alias, so keep track of it for future use...
                    restOfStatement.append(makeViewTable(linkEntity, modelFieldTypeReader, datasourceInfo));
                    // another possible one that some dbs might need, but not sure of any yet: restOfStatement.append(" AS ");
                    restOfStatement.append(" ");
                    restOfStatement.append(viewLink.getEntityAlias());
                    joinedAliasSet.add(viewLink.getEntityAlias());
                } else {
                    // make sure the left entity alias is already in the join...
                    if (!joinedAliasSet.contains(viewLink.getEntityAlias())) {
                        throw new GenericModelException("Tried to link the " + viewLink.getEntityAlias() + " alias to the " + viewLink.getRelEntityAlias() + " alias of the " + modelViewEntity.getEntityName() + " view-entity, but it is not the first view-link and has not been included in a previous view-link. In other words, the left/main alias isn't connected to the rest of the member-entities yet.");
                    }
                }
                // now put the rel (right) entity alias into the set that is in the join
                joinedAliasSet.add(viewLink.getRelEntityAlias());
                if (viewLink.isRelOptional()) {
                    restOfStatement.append(" LEFT OUTER JOIN ");
                } else {
                    restOfStatement.append(" INNER JOIN ");
                }
                restOfStatement.append(makeViewTable(relLinkEntity, modelFieldTypeReader, datasourceInfo));
                // another possible one that some dbs might need, but not sure of any yet: restOfStatement.append(" AS ");
                restOfStatement.append(" ");
                restOfStatement.append(viewLink.getRelEntityAlias());
                restOfStatement.append(" ON ");
                StringBuilder condBuffer = new StringBuilder();
                for (int j = 0; j < viewLink.getKeyMapsSize(); j++) {
                    ModelKeyMap keyMap = viewLink.getKeyMap(j);
                    ModelField linkField = linkEntity.getField(keyMap.getFieldName());
                    if (linkField == null) {
                        throw new GenericModelException("Invalid field name in view-link key-map for the " + viewLink.getEntityAlias() + " and the " + viewLink.getRelEntityAlias() + " member-entities of the " + modelViewEntity.getEntityName() + " view-entity; the field [" + keyMap.getFieldName() + "] does not exist on the [" + linkEntity.getEntityName() + "] entity.");
                    }
                    ModelField relLinkField = relLinkEntity.getField(keyMap.getRelFieldName());
                    if (relLinkField == null) {
                        throw new GenericModelException("Invalid related field name in view-link key-map for the " + viewLink.getEntityAlias() + " and the " + viewLink.getRelEntityAlias() + " member-entities of the " + modelViewEntity.getEntityName() + " view-entity; the field [" + keyMap.getRelFieldName() + "] does not exist on the [" + relLinkEntity.getEntityName() + "] entity.");
                    }
                    if (condBuffer.length() > 0) {
                        condBuffer.append(" AND ");
                    }
                    condBuffer.append(viewLink.getEntityAlias());
                    condBuffer.append(".");
                    condBuffer.append(linkField.getColName());
                    condBuffer.append(" = ");
                    condBuffer.append(viewLink.getRelEntityAlias());
                    condBuffer.append(".");
                    condBuffer.append(relLinkField.getColName());
                }
                if (condBuffer.length() == 0) {
                    throw new GenericModelException("No view-link/join key-maps found for the " + viewLink.getEntityAlias() + " and the " + viewLink.getRelEntityAlias() + " member-entities of the " + modelViewEntity.getEntityName() + " view-entity.");
                }
                ModelViewEntity.ViewEntityCondition viewEntityCondition = viewLink.getViewEntityCondition();
                if (viewEntityCondition != null) {
                    EntityCondition whereCondition = viewEntityCondition.getWhereCondition(modelFieldTypeReader, null);
                    condBuffer.append(" AND ");
                    condBuffer.append(whereCondition.makeWhereString(modelEntity, null, datasourceInfo));
                }
                restOfStatement.append(condBuffer.toString());
                // don't put ending parenthesis
                if (i < (modelViewEntity.getViewLinksSize() - 1) && useParenthesis)
                    restOfStatement.append(')');
            }
            if (useParenthesis)
                sql.append(openParens.toString());
            sql.append(restOfStatement.toString());
            // handle tables not included in view-link
            boolean fromEmpty = restOfStatement.length() == 0;
            for (String aliasName : modelViewEntity.getMemberModelMemberEntities().keySet()) {
                ModelEntity fromEntity = modelViewEntity.getMemberModelEntity(aliasName);
                if (!joinedAliasSet.contains(aliasName)) {
                    if (!fromEmpty)
                        sql.append(", ");
                    fromEmpty = false;
                    sql.append(makeViewTable(fromEntity, modelFieldTypeReader, datasourceInfo));
                    sql.append(" ");
                    sql.append(aliasName);
                }
            }
        } else if ("theta-oracle".equals(datasourceInfo.getJoinStyle()) || "theta-mssql".equals(datasourceInfo.getJoinStyle())) {
            // FROM clause
            Iterator<String> meIter = modelViewEntity.getMemberModelMemberEntities().keySet().iterator();
            while (meIter.hasNext()) {
                String aliasName = meIter.next();
                ModelEntity fromEntity = modelViewEntity.getMemberModelEntity(aliasName);
                sql.append(makeViewTable(fromEntity, modelFieldTypeReader, datasourceInfo));
                sql.append(" ");
                sql.append(aliasName);
                if (meIter.hasNext())
                    sql.append(", ");
            }
        // JOIN clause(s): none needed, all the work done in the where clause for theta-oracle
        } else {
            throw new GenericModelException("The join-style " + datasourceInfo.getJoinStyle() + " is not yet supported");
        }
    } else {
        sql.append(modelEntity.getTableName(datasourceInfo));
    }
    return sql.toString();
}