Ag-grid+tadabase,javascript, pipe data pull, and trying to enhance our ability to date range search more efficiently.
I have implemented a search functionality in my web application, where users can input various search parameters including date range. The search works well for other inputs, but I’m facing issues with the date search input.
The problem is that when I input a date in the search field, the search results always start from the day after the inputted date, and im aware its because of the is after but no matter what i try it doesnt seem to work in another way. Additionally, when I input a large date range, the search skips some of the data instead of providing all the available data within the range.
Here is an overview of how the date search is intended to work:
The “Load In” date is represented by field_41.
The “Pick Up” date is represented by field_43.
If only one date is inputted, the search should display the data for that day and the next 90 days.
If a date range is provided, the search should return all the data within that range.
I suspect there might be an issue with how I’m handling the date inputs or how I’m comparing the dates in the search query.
I would appreciate any guidance or suggestions on how to fix this issue and make the date search function correctly. Thank you!
var columnConfig = {
'Job Number': { field: 'field_36', width: 130 },
'Description': { field: 'field_37', width: 200 },
'Status': { field: 'field_178', width: 95 },
'Client': { field: 'field_46', width: 150 },
'Account Manager': { field: 'field_185', width: 170 },
'Project Manager': { field: 'field_39', width: 170 },
'Site': { field: 'field_169_val', width: 125 },
'Type': { field: 'field_40', width: 100 },
'Load In': { field: 'field_41', width: 150 },
'Pick Up': { field: 'field_43', width: 150 },
'Revenue': {
field: 'field_771',
aggFunc: 'sum',
width: 150,
valueFormatter: function(params) {
// Custom value formatter for the 'Revenue' column
var rawValue = params.value;
if (typeof rawValue === 'number') {
// Format the value as currency with dollar sign and comma separators
var formattedValue = '$' + rawValue.toLocaleString('en-US', {
minimumFractionDigits: 2,
maximumFractionDigits: 2,
});
return formattedValue;
} else {
return rawValue;
}
},
enableRowGroup: true,
rowGroupIndex: 0,
pivotValueColumn: 'field_771',
pivotTotalColumnIds: ['field_771'],
},
};
// Create column definitions based on the column configuration
var columnDefs = Object.keys(columnConfig).map(function(headerName) {
var colDef = columnConfig[headerName];
var definition = {
headerName: headerName,
field: colDef.field,
resizable: true,
width: colDef.width || null,
valueFormatter: colDef.valueFormatter || null,
};
if (colDef.aggFunc) {
definition.aggFunc = colDef.aggFunc;
}
if (colDef.valueParser) {
definition.valueParser = colDef.valueParser;
}
return definition;
});
var eGridDiv = document.querySelector('#myGrid');
// Select elements from the DOM
var searchButton = document.querySelector('#searchButton');
var searchForm = document.querySelector('#yourFormId');
eGridDiv.style.display = 'none'; // Hide the grid initially
// Add event listener to the search button
searchButton.addEventListener('click', function(e) {
e.preventDefault();
var inputs = Array.from(searchForm.elements).filter(function(element) {
// Collect input values from the search form
return element.tagName === 'INPUT';
});
if (!checkInputs(inputs)) {
return;
}
var jobNumber = document.querySelector('#jobNumber').value.trim();
var description = document.querySelector('#description').value.trim();
var client = document.querySelector('#client').value.trim();
var accountManager = document.querySelector('#accountManager').value.trim();
var projectManager = document.querySelector('#projectManager').value.trim();
var site = document.querySelector('#site').value.trim();
var type = document.querySelector('#type').value.trim();
var dateStart = document.querySelector('#dateStart').value.trim();
var dateEnd = document.querySelector('#dateEnd').value.trim();
eGridDiv.style.display = ''; // Show the grid
performSearch(jobNumber, description, client, accountManager, projectManager, site, type, dateStart, dateEnd); // Perform the search with the input values
});
// Configure the grid options
var gridOptions = {
columnDefs: columnDefs,
defaultColDef: {
sortable: true,
filter: true,
resizable: true,
autoSize: true,
groupable: true,
enablePivot: true,
suppressMenuHide: true,
suppressMovable: false,
enableRowGroup: true,
groupSuppressAutoColumn: false,
rowSelection: 'multiple',
},
rowGroupPanelShow: 'onlyWhenGrouping',
multiSortKey: 'ctrl',
groupDefaultExpanded: 0,
groupIncludeTotalFooter: true,
rowData: null,
aggregationColumns: ['field_771'],
pagination: false,
paginationPageSize: 25,
sideBar: true,
defaultToolPanel: 'columns',
groupUseEntireRow: false,
groupIncludeFooter: true,
animateRows: true,
autoGroupColumnDef: {
headerName: 'Group',
minWidth: 200,
cellRendererParams: {
checkbox: false,
innerRenderer: 'agGroupCellRenderer',
footerValueGetter: 'function(params) { return params.api ? params.api.getValue("field_771", "sum") : 0; }',
cellRendererParams: {},
},
},
enableRangeSelection: true,
enableCellTextSelection: true,
enableBrowserTooltips: true,
onColumnRowGroupChanged: function(params) {
var groupedCols = params.columnApi.getRowGroupColumns();
if (groupedCols.length > 0) {
var headerName = groupedCols[0].colDef.headerName;
params.api.refreshHeader();
}
},
};
// Set up the grid using ag-Grid library
new agGrid.Grid(eGridDiv, gridOptions);
// Function to perform the search
function performSearch(jobNumber, description, client, accountManager, projectManager, site, type, dateStart, dateEnd) {
var today = new Date();
var defaultDate = today.toISOString().split('T')[0];
var futureDate = new Date();
futureDate.setDate(today.getDate() + 90);
var defaultEndDate = futureDate.toISOString().split('T')[0];
var pageNumber = 0;
var loadInDateStart = dateStart !== '' ? new Date(dateStart) : new Date(defaultDate);
loadInDateStart.setUTCHours(0, 0, 0, 0); // Sets the time to 00:00:00
var pickUpDateStart;
if (dateEnd !== '') {
pickUpDateStart = new Date(dateEnd);
pickUpDateStart.setUTCHours(23, 59, 59, 999); // Sets the time to 23:59:59
} else {
pickUpDateStart = new Date(defaultEndDate);
pickUpDateStart.setUTCHours(23, 59, 59, 999); // Sets the time to 23:59:59
}
// Format the pickUpDateStart to match the expected format
var formattedPickUpDateStart = pickUpDateStart.toISOString().split('T')[0];
var searchQuery = {
field_36: jobNumber || undefined,
field_37: description || undefined,
field_46: client || undefined,
field_185: accountManager || undefined,
field_39: projectManager || undefined,
field_169_val: site || undefined,
field_40: type || undefined,
field_178: 'OPEN',
field_771: { '$sum': 1 },
};
// Date comparisons
if (loadInDateStart && pickUpDateStart) {
searchQuery['field_41'] = { '$gte': loadInDateStart.toISOString(), '$lte': pickUpDateStart.toISOString() };
} else if (loadInDateStart) {
searchQuery['field_41'] = { '$gte': loadInDateStart.toISOString() };
} else if (pickUpDateStart) {
searchQuery['field_41'] = { '$lte': pickUpDateStart.toISOString() };
}
// Perform the search using TB.triggerPipe() function
TB.triggerPipe(
'removed for privacy',
{
tableId: 'lGArg7rmR6',
condition: 'AND',
page: pageNumber || '',
pageSize: 5000,
fieldList: 'field_36,field_37,field_178,field_41,field_46,field_185,field_39,field_169,field_40,field_43,field_771',
sortByField: '',sortOrder: '',
field_id_0: 'field_36',field_op_0: '',field_val_0:
jobNumber,field_id_1: 'field_37',field_op_1: '',field_val_1: description,
field_id_2: 'field_46',field_op_2: '',field_val_2: client,
field_id_3: 'field_185',field_op_3: '',field_val_3: accountManager,
field_id_4: 'field_39',field_op_4: '',field_val_4: projectManager,
field_id_5: 'field_169',field_op_5: '',field_val_5: site,
field_id_6: 'field_40',field_op_6: '',field_val_6: type,
field_id_7: 'field_178',field_op_7: 'is not',field_val_7: 'cancelled ',
field_id_8: 'field_41',field_op_8: 'is after',field_val_8: dateStart,
field_id_9: 'field_43',field_op_9: '',field_val_9: dateEnd,
field_id_10: 'field_771',field_op_10: '',field_val_10: '', // Use 'field_771_raw' for sum aggregation
},
function(type, response, xhrResponse) {
console.log('TriggerPipe response:', response); // Log the raw response data
var parsedData = parsePipeData(response);
console.log('Parsed data:', parsedData); // Log the parsed data
var filteredData = filterData(parsedData, searchQuery);
console.log('Filtered data:', filteredData); // Log the filtered data
var updatedColumnDefs = updateColumnDefs(columnDefs, filteredData);
console.log('Updated column definitions:', updatedColumnDefs); // Log the updated column definitions
gridOptions.api.setColumnDefs(updatedColumnDefs);
gridOptions.api.setRowData(filteredData);
}
);
}
// Function to parse the pipe data into a usable format
function parsePipeData(response) {
return response.items.map(function(item) {
var result = {};
Object.keys(columnConfig).forEach(function(headerName) {
var field = columnConfig[headerName].field;
if (field === 'field_169_val') {
if (Array.isArray(item[field])) {
result[field] = item[field][0].val;
} else {
result[field] = item[field];
}
} else {
result[field] = item[field];
}
});
return result;
});
}
// Function to filter the data based on the search query
function filterData(data, searchQuery) {
return data.filter(function filterItem(item) {
for (var key in searchQuery) {
if (searchQuery.hasOwnProperty(key) && item.hasOwnProperty(key)) {
if (key === 'field_41') {
var fieldValue = new Date(new Date(item[key]).setUTCHours(0, 0, 0, 0));
var queryValue = searchQuery[key];
if (queryValue['$gte'] && fieldValue < new Date(queryValue['$gte'])) {
return false;
}
if (queryValue['$lte'] && fieldValue > new Date(queryValue['$lte'])) {
return false;
}
} else {
var fieldValue = item[key];
var queryValue = searchQuery[key];
if (typeof queryValue === 'string' && typeof fieldValue === 'string') {
if (!fieldValue.toLowerCase().includes(queryValue.toLowerCase())) {
return false;
}
} else if (typeof queryValue === 'number' && fieldValue !== queryValue) {
return false;
}
}
}
}
return true;
});
}
// Function to update column definitions based on the filtered data
function updateColumnDefs(columnDefs, filteredData) {
return columnDefs.map(function(colDef) {
var definition = Object.assign({}, colDef);
if (filteredData.length > 0 && filteredData[0].hasOwnProperty(colDef.field)) {
definition.hide = false;
} else {
definition.hide = true;
}
return definition;
});
}
// Function to check if at least one input field is filled
function checkInputs(inputs) {
var hasInput = inputs.some(function(input) {
return input.value.trim() !== '';
});
if (!hasInput) {
alert('Please fill in at least one field before searching.');
return false;
}
return true;
}