Developing new API which is completely driven by address hierarchy and uses AddressNode psql table to get required nodes data
const fetchGeneralAddressCode = async (organisationId, extendedModels, params, options = {}) => {
const { searchType, queryString } = params;
if (!searchType) {
throw helper.wrongInputError('Location Key should be present');
}
const metaData = await addressHierarchy.getAddressMetadata(extendedModels, organisationId);
if (!metaData) {
throw helper.wrongInputError('Metadata not found');
}
const hierarchy = metaData.hierarchy || [];
const nodeHierarchyMetaData = hierarchy.find(obj => helper.sanitizeStringCode(obj.location_key) === helper.sanitizeStringCode(searchType));
if (!nodeHierarchyMetaData) {
throw helper.wrongInputError('Invalid Location Key');
}
const parentMetaDataArr = [];
let searchTypeNodeFound = false;
if (Array.isArray(hierarchy) && hierarchy.length > 0) {
for (let i = 0; i < (hierarchy.length - 1); i++) {
if (searchTypeNodeFound || helper.sanitizeStringCode(hierarchy[i].location_key) === helper.sanitizeStringCode(searchType)) {
searchTypeNodeFound = true;
parentMetaDataArr.push(hierarchy[i + 1]);
}
}
}
const queryParameters = [];
let heirarchyId = nodeHierarchyMetaData.id;
let nodeCode = helper.sanitizeStringCode(queryString);
const toRet = {};
toRet.data = {};
queryParameters.push(organisationId);
queryParameters.push(heirarchyId);
queryParameters.push(nodeCode);
const selectPart = 'SELECT addressNode.id as node_id,addressNode.code as node_code,addressNode.name as node_name,addressNode.hierarchy_id as hierarchy_id';
const fromPart = ' FROM addressNode';
let extendedSelectPart = '';
let joinPart = '';
const wherePart = ' WHERE addressNode.organisation_id = $1 and addressNode.hierarchy_id = $2 and addressNode.code = $3';
if (parentMetaDataArr.length) {
extendedSelectPart = ',addressNode.parent_node_id as parent_node_id,parentAddressNode.code as parent_code,parentAddressNode.name as parent_name';
joinPart = ' JOIN addressNode AS parentAddressNode on addressNode.parent_node_id = parentAddressNode.id';
}
const queryToExecute = selectPart + extendedSelectPart + fromPart + joinPart + wherePart;
const result = await helper.executeQueryAsync(extendedModels.AddressNode, queryToExecute, queryParameters, options);
if (result[0] && result[0].node_name) {
toRet.data[searchType] = result[0].node_name;
} else {
throw helper.wrongInputError('Data not found for this location key');
}
let nextNodesResult = result;
for (let i = 0; i < parentMetaDataArr.length; i++) {
try {
heirarchyId = parentMetaDataArr[i].id;
nodeCode = nextNodesResult[0].parent_code;
const locationKey = parentMetaDataArr[i].location_key;
queryParameters[1] = heirarchyId;
queryParameters[2] = nodeCode;
if (i === (parentMetaDataArr.length - 1)) {
const lastNodeQueryResult = selectPart + fromPart + wherePart;
nextNodesResult = await helper.executeQueryAsync(extendedModels.AddressNode, lastNodeQueryResult, queryParameters, options);
} else {
nextNodesResult = await helper.executeQueryAsync(extendedModels.AddressNode, queryToExecute, queryParameters, options);
}
if (nextNodesResult[0] && nextNodesResult[0].node_name) {
toRet.data[locationKey] = nextNodesResult[0].node_name;
}
} catch (err) {
//do nothing
}
}
return toRet;
};
success response type will be like
{
"data": {
"pincode": "DAM",
"city": "Z1",
"state": "DAMMAM"
}
}
Point to consider for better efficiency of this api(lesser load on db):
on loading page on frontend side fetch all possible node values for each address field (in useEffect)
on selecting any of value from dropdown of any field this api will be called by passing
queryString=selectedValue of nodeandsearchType=type of selected fieldFrontend changes to be done in add consignment(crm) alongwith customer-portal and counter-booking(crm). (@saarthak to confirm all screens(frontend) where this change has to be done).
What to do if any of middle node in hierarchy data is not found commented in above code snippet in catch block because it will hinder fetching data of other higher level (is it possible to occur in prod DB)
Should disable all highe level nodes irrespective of the case that their value is fetched or not OR disable only those fields for which value is fetched. @saarthak
common/models/customer-portal-parts/address-node-api-helper.js → getAddressNodeData
To make filter also applicable on name and code both while fetching node list at frontend, updating filter logic.
Current filter logic:
const conditions = {
hierarchy_id: hierarchyId,
organisation_id: organisationId
};
if (isDataSearchApplied) {
searchQuery = helper.sanitizeStringCode(searchQuery);
let queryString = `${searchQuery}%`;
if (sortResult) {
queryString = `%${queryString}`;
}
conditions.code = {ilike: queryString};
if (shouldSearchByName) {
conditions.name = {ilike: queryString};
delete conditions.code;
}
}
const heirarchyData = await extendedModels.AddressNode.find({
where: conditions,
fields: { code: true, name: true, id: true },
order: sortResult && 'name ASC',
});
Updated filter logic:
const conditions = {
and: [
{hierarchy_id: hierarchyId},
{organisation_id: organisationId},
],
};
if (isDataSearchApplied) {
searchQuery = helper.sanitizeStringCode(searchQuery);
let queryString = `${searchQuery}%`;
if (sortResult) {
queryString = `%${queryString}`;
}
const orConditions = shouldIncludeNameInSearchFilter
? [
{ name: { ilike: queryString } },
{ code: { ilike: queryString } }
]
:
[{ code: { ilike: queryString } }];
conditions.and.push({ or: orConditions });
}
const heirarchyData = await extendedModels.AddressNode.find({
where: conditions,
fields: { code: true, name: true, id: true }
});
Frontend changes:
In case allowOverrideHierarchy is false , we will let user only enter values from dropdown only
else user can either select from dropdown or enter other values too
{!this.state.allowOverrideHierarchy && getFieldDecorator('sender_country', {
rules: [{ required: false, message: t('cannot_be_empty') }],
})(
<Select
showSearch
optionFilterProp="children"
onChange={(value) => this.saveAddressUsingAddressHierarchy(value, 'country', 'sender')}
placeholder={t("Country")}
disabled={this.state.senderDisableCountry}
onSearch={this.loadCountries}
allowClear
>
{this.state?.countriesList?.map(opt => <Option key={opt.id} value={opt.code}>{t(opt.name)} ({opt.code})</Option>)}
</Select>,
)}
{this.state.allowOverrideHierarchy && getFieldDecorator('sender_country', {
rules: [{ required: false, message: t('cannot_be_empty') }],
})(
<AutoComplete
options={this.state?.countriesList?.map(item => ({ label: `${item.name} (${item.code})`, value: item.code }))}
onSearch={this.loadCountries}
onSelect={(value) => this.saveAddressUsingAddressHierarchy(value, 'country', 'sender')}
onChange={(value) => this.handleAddressFields('country', value, !this.state.allowOverrideHierarchy, 'sender')}
disabled={this.state.senderDisableCountry}
allowClear
>
<Input
allowClear
placeholder={t("Country")}
/>
</AutoComplete>,
Instead of showing only Name of options in dropdown it will now be shown as Name (Code). This will require another update in find query in common/models/customer-portal-parts/address-node-api-helper.js → getAddressNodeData (mentioned above in backend changes).
Add Comment