This comprehensive explanation will delve into the concept of Condition Query Business Mapping, its functionality, and how it can be implemented within an application. By exploring various examples and scenarios, we will gain a deeper understanding of this mapping technique.
Condition Query Business Mapping allows us to assess an SQL command within the application's context and obtain the result to make informed decisions for subsequent actions. There are two formats that can be used: direct SQL and QueryGenerator specification.
<map>
<sql>some SQL command with question mark placeholders for parameters</sql>
<parameters> {optional}
<parameter></parameter>
<parameters>
<return>{fieldname|count|recordset}</return>
</map><map>
<module>some module</module>
<fields>CSV of field names</fields>
<conditions>[{"fieldname":"field1","operation":"op","value":"val","valuetype":"rawtext","joincondition":"and","groupid":"0"},{"fieldname":"field2 : (RelatedModule) relatedfield","operation":"op","value":"val","valuetype":"rawtext","joincondition":"and","groupid":"0"}]</conditions>
<return>{fieldname|count|recordset}</return>
</map>When both formats are present, the SQL part takes precedence while the other format is disregarded.
It is imperative to specify the expected return value, which can be one of the following options:
Let's explore the QueryGenerator format in more detail:
Field specification: You can define the specific fields to be returned in the SQL query by providing a comma-separated string of field names. In case no fields are specified, the IDs of the records will be used as default.
Conditions: The conditions are represented by a JSON-encoded string generated by the application's conditions editor. These conditions allow for advanced filtering and querying capabilities.
Let's consider an example to illustrate the practical implementation of this syntax of the Condition Query Business Mapping:
<map>
<module>Assets</module>
<fields>assetname, asset_no, productname</fields>
<conditions>[{"fieldname":"assetname","operation":"contains","value":"j","valuetype":"rawtext","joincondition":"and","groupid":"0"},{"fieldname":"product : (Products) unit_price","operation":"greater than","value":"30","valuetype":"rawtext","joincondition":"and","groupid":"0"}]</conditions>
<return>recordset</return>
</map>In this QueryGenerator example, the module specified is "Assets". The query aims to retrieve the fields "assetname", "asset_no", and "productname" from the corresponding table. The conditions are expressed as a JSON-encoded string. In this case, the conditions state that the "assetname" field should contain the letter "j" and the "unit_price" field from the related "Products" table should be greater than 30. The return value is set to "RECORDSET," which means that all the resulting records will be returned.
Now, let's see an example of an SQL query
<map>
<sql>
SELECT accountid, accountname
FROM vtiger_account
INNER JOIN vtiger_crmentity ce ON ce.crmid=vtiger_account.accountid
WHERE ce.deleted=0 AND vtiger_account.accountid = ?
</sql>
<return>accountname</return>
</map>In this example, we have a direct SQL command. The query retrieves the account ID and account name from the "vtiger_account" table, joining it with the "vtiger_crmentity" table. The condition specifies that only non-deleted records should be considered, and the account ID should match the provided parameter. The expected return value is the "accountname" field. Additionally, the possible return values can be "COUNT" or "RECORDSET".
The idea of that query is that condition query works with ONE question mark. In that case, it will substitute that question mark for the ID of the record given in the query context. So the above example will work in the context of any Account detail view (for example). In short, the record that the query is being executed on.
so if we have something like
SELECT contact_assigned.smownerid as userid
FROM vtiger_cbpomaster
INNER JOIN vtiger_crmentity ON vtiger_cbpomaster.cbpomasterid = vtiger_crmentity.crmid
INNER JOIN vtiger_crmentityrel ON (vtiger_crmentityrel.relmodule = 'ContactRole' AND vtiger_cbpomaster.cbpomasterid = vtiger_crmentityrel.crmid)
INNER JOIN vtiger_contactrole ON vtiger_crmentityrel.relcrmid = vtiger_contactrole.contactroleid
INNER JOIN vtiger_crmentity contact_assigned ON vtiger_contactrole.contactroleid= contact_assigned.crmid
WHERE vtiger_contactrole.roletype LIKE 'VA' AND vtiger_cbpomaster.cbpomasterid = ?that will work as there is only one question mark
If we need more than one question mark, something like this:
SELECT contact_assigned.smownerid as userid
FROM vtiger_cbpomaster
INNER JOIN vtiger_crmentity ON vtiger_cbpomaster.cbpomasterid = vtiger_crmentity.crmid
INNER JOIN vtiger_crmentityrel ON (vtiger_crmentityrel.relmodule = ? AND vtiger_cbpomaster.cbpomasterid = vtiger_crmentityrel.crmid)
INNER JOIN vtiger_contactrole ON vtiger_crmentityrel.relcrmid = vtiger_contactrole.contactroleid
INNER JOIN vtiger_crmentity contact_assigned ON vtiger_contactrole.contactroleid= contact_assigned.crmid
WHERE vtiger_contactrole.roletype LIKE ? AND vtiger_cbpomaster.cbpomasterid = ?where there are 3 question marks, then we have to create the map indicating the parameters, like this
<map>
<sql>
SELECT contact_assigned.smownerid as userid
FROM vtiger_cbpomaster
INNER JOIN vtiger_crmentity ON vtiger_cbpomaster.cbpomasterid = vtiger_crmentity.crmid
INNER JOIN vtiger_crmentityrel ON (vtiger_crmentityrel.relmodule = ? AND vtiger_cbpomaster.cbpomasterid = vtiger_crmentityrel.crmid)
INNER JOIN vtiger_contactrole ON vtiger_crmentityrel.relcrmid = vtiger_contactrole.contactroleid
INNER JOIN vtiger_crmentity contact_assigned ON vtiger_contactrole.contactroleid= contact_assigned.crmid
WHERE vtiger_contactrole.roletype LIKE ? AND vtiger_cbpomaster.cbpomasterid = ?</sql>
<parameters>
<parameter>concat('ContactRole')</parameter>
<parameter>concat('VA')</parameter>
<parameter>ID</parameter>
<parameters>
<return>userid</return>
</map>note that inside the parameter directives we ALWAYS have a workflow expression, not constant strings, so I am forced to do that concat trick
By studying these examples, we can grasp the flexibility and power of the Condition Query Business Mapping. It enables us to dynamically evaluate SQL commands, tailor them to the specific context of an application, and
Here are a few business use cases where Condition Query Business Mapping can be useful:
Customer Relationship Management (CRM) System: In a CRM system, the mapping can be used to retrieve specific customer information based on various conditions. For example, a map can be created to query customer orders based on the order status, date range, and customer ID. The returned results can be used to generate reports, track customer activity, or trigger automated actions.
Inventory Management: In an inventory management system, the mapping can be utilized to check the availability of specific products based on their attributes. For instance, a map can be designed to query the stock levels of items based on their category, location, and quantity thresholds. This information can help in making informed decisions for replenishing stock, managing orders, and optimizing inventory levels.
E-commerce Pricing and Promotions: Condition Query Business Mapping can be employed to determine pricing and promotions in an e-commerce platform. A map can be created to fetch pricing information based on product attributes such as customer segment, purchase history, and current discounts. This allows for dynamic pricing strategies, personalized offers, and targeted promotions based on specific conditions.
Workflow Automation: The mapping can be used in workflow automation systems to trigger actions or notifications based on predefined conditions. For instance, a map can be configured to query pending tasks, deadlines, or approvals based on user roles, task priority, and due dates. This enables the system to automatically send reminders, escalate tasks, or initiate follow-up actions based on specific conditions.
Fraud Detection: Condition Query Business Mapping can play a role in fraud detection systems. A map can be designed to query suspicious transactions based on factors such as transaction amounts, customer behavior patterns, and geographical locations. By analyzing these conditions, the system can identify potential fraudulent activities and trigger alerts or flag transactions for further investigation.
Service Level Agreement (SLA) Monitoring: In a service-oriented business, the mapping can be useful for monitoring SLAs. A map can be created to query service tickets or requests based on SLA parameters, such as response time, priority, and service type. This allows for real-time monitoring of SLA compliance, generating performance reports, and taking appropriate actions to ensure service quality.
These are just a few examples showcasing the versatility of Condition Query Business Mapping across different industries and business scenarios. The mapping provides a flexible and efficient way to extract relevant data, make informed decisions, and automate processes based on specific conditions.
Next | Chapter 2: Condition Expression.