/**
* Query serch to fetch the data
* @param {Object} reportAppliedFilter
* @returns {Array}
*/
function querySearch(reportAppliedFilter) {
let requisitions = [];
try {
var requisitionQuery = query.create({
type: 'customrecord_cs_other_costed_items'
});
var activityJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_activity'
});
var projectTaskJoin = activityJoin.join({
fieldId: 'custrecord_cs_as_linked_project_task'
});
var projectJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_proj'
});
var statusJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_material_status'
});
var itemJoin = requisitionQuery.joinTo({
fieldId: 'custrecord_cs_ot_costed_item_code',
target: 'item'
});
var firstCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_AFTER,
values: [reportAppliedFilter.startDate]
});
var secondCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_BEFORE,
values: [reportAppliedFilter.endDate]
});
var thirdCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_AFTER,
values: [reportAppliedFilter.startDate]
});
var fourthCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_BEFORE,
values: [reportAppliedFilter.endDate]
});
if (reportAppliedFilter.depId)
var depCondition = projectJoin.createCondition({
fieldId: 'custentity_cs_project_department',
operator: query.Operator.ANY_OF,
values: reportAppliedFilter.depId
});
var statusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.ANY_OF,
values: ["3", "7", "9"]
});
var emptyStatusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.EMPTY
});
if (reportAppliedFilter.depId)
requisitionQuery.condition = requisitionQuery.and(
firstCondition, secondCondition, thirdCondition, fourthCondition, depCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
else
requisitionQuery.condition = requisitionQuery.and(
firstCondition, secondCondition, thirdCondition, fourthCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
requisitionQuery.columns = [
requisitionQuery.createColumn({
fieldId: 'id',
alias: 'requisitionId'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_proj',
alias: 'projectId'
}),
projectJoin.createColumn({
fieldId: 'entityTitle',
alias: 'projectName'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_costed_item_code',
alias: 'itemId'
}),
itemJoin.createColumn({
fieldId: 'itemId',
alias: 'itemName'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_cost_qty',
alias: 'quantity'
}),
statusJoin.createColumn({
fieldId: 'name',
alias: 'status'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_activity',
alias: 'activity'
}),
projectTaskJoin.createColumn({
fieldId: 'title',
alias: 'projectTask'
}),
projectTaskJoin.createColumn({
fieldId: 'id',
alias: 'projectTaskId'
}),
itemJoin.createColumn({
fieldId: 'costEstimate',
alias: 'itemCost'
}),
projectTaskJoin.createColumn({
fieldId: 'startDateTime',
alias: 'startDate'
}),
projectTaskJoin.createColumn({
fieldId: 'endDate',
alias: 'endDate'
}),
];
requisitionQuery.sort = [
requisitionQuery.createSort({
column: requisitionQuery.columns[0],
ascending: true
})
];
let pagedData = requisitionQuery.runPaged({
pageSize: 1000
});
log.debug('pagedData.pageRanges', pagedData.pageRanges)
pagedData.pageRanges.forEach((pageRange) => {
let page = pagedData.fetch({ index: pageRange.index });
page.data.results.forEach((result) => {
requisitions.push(result.asMap());
});
});
return requisitions;
} catch (e) {
log.error('error@querySearch', e);
return requisitions;
}
}