'ag-grid: Grouped Footers don't work with Server Side Row Model, need a workaround
ag-grid supports showing a footer in each group, and even subgroups. However, that page does not mention that it doesn't worth with SSRM.
Would have been nice to know ahead, but now I'm looking for a workaround. Any awful, terrible way to show some custom information at the bottom of each group, including nested groups. Disabling SSRM is not an option, and the information must positioned at the bottom of the group, nowhere else.
I've tried a bunch of things. Some of these might still work, but are so awful it's hard to tell:
Using a pinned bottom row -> doesn't work with lots of groups.
Adding a DOM element after everything has rendered -> ag grid positioning is too aggressive, you'd have to force the grid out of the way.
Hooking into the query to return an extra row -> requires modifying too many query components.
Adding a fake row directly into the source data -> some things are too awful.
Using a custom full-width row to hijack the last row in a group and add a fake "row" -> maybe?
If you have even a suggestion without a solution, I would be happy to hear it.
Solution 1:[1]
I have successfully implemented a total footer row for SSR Model and I think it should be possible for grouped rows as well. I will share a complete example with you including implementing the checkbox feature which is also normally not possible for SSR Model. Anyways, here's my generic solution. (It's not possible to answer that at once because of body limitation, I will split it into 3 answers.
We Begin PART 1
FINAL RESULT
HELPERS
myApp.js file structure
var gridOptions = null;
var SSRMGridOptions = null;
var SSRMGridResized = false;
var mobileGrid = false;
var fakeServerMethods = [];
var fakeServerData = [];
var fieldsToBeFormattedForAgg = [];
var fakeServerDataLoaded = false;
var fakeServerSelectedRows = [];
var firstDataRendered = false;
var Transactions = function () {
// I will share the necessary content
};
How we call this file in the footer
<script src="<?php echo $this->config->item('backend_theme_path'); ?>/app/js/myApp.js<?php echo CACHE_VERSION; ?>" type="text/javascript"></script>
Main function for SSRM Ag-Grid
this.SSRMAgGrid = function SSRMAgGrid(columnDefs, SSRMGridOptionsIncluded, jsonData, jsonUrl, excludedFields = [], useLocalStorage = true, divID='#myGrid', is_modal = false) {
SSRMGridOptionsIncluded = typeof SSRMGridOptionsIncluded !== 'undefined' ? SSRMGridOptionsIncluded : {};
jsonData = typeof jsonData !== 'undefined' ? jsonData : {};
if (!SSRMGridOptionsIncluded.doNotGenerateSelectBox) {
columnDefs.unshift({
field: 'checkbox',
maxWidth: 50,
headerName: '', checkboxSelection: true,
headerComponent: App.CustomSelectBoxHeader,
headerComponentParams: {showMe: true},
menuTabs: ['generalMenuTab', 'columnsMenuTab']
});
}
columnDefs.forEach(function(columnDef, index) {
if (columnDef.aggFunc) {
switch (columnDef.aggFunc) {
case 'sum':
fieldsToBeFormattedForAgg.push(columnDef.field);
break;
case 'avg':
fieldsToBeFormattedForAgg.push(columnDef.field);
break;
}
}
if (columnDef.rowGroup) {
}
});
if (window.innerWidth <= 768) {
mobileGrid = true;
}
if (mobileGrid) {
columnDefs.forEach(function(columnDef, index) {
if (columnDef.pinned) {
delete columnDef.pinned;
}
if (columnDef.cellStyle) {
delete columnDef.cellStyle;
}
if (columnDef.pinnedRowCellRenderer) {
delete columnDef.pinnedRowCellRenderer;
}
});
}
SSRMGridOptions = {
debug: false,
columnDefs: columnDefs,
defaultColDef: {
sortable: true,
filter: true,
resizable: true,
},
headerHeight: 45,
rowHeight: 35,
enableRangeSelection: true,
rowSelection: 'multiple',
groupIncludeFooter: true,
animateRows: true,
localeText: AG_GRID_LANGUAGE_TEXT,
columnTypes: {
number: { filter: 'agNumberColumnFilter' },
},
rowModelType: 'serverSide',
serverSideStoreType: 'partial',
rowStyle: {fontFamily: 'Comic Sans MS, Comic Sans, Verdana', color: 'darkslategray'},
onRowSelected: params => {
var element = document.getElementById('customSelectBox');
if (params.node.selected) {
fakeServerSelectedRows.push(params.data);
}
else {
fakeServerSelectedRows = fakeServerSelectedRows.filter(row => row.id !== params.data.id);
}
if (params.node.selected) {
if (App.isFakeServerDataLoaded()) {
if (SSRMGridOptions.api.getDisplayedRowCount() < App.getFakeServerDataCount()) {
if (!element.classList.contains('ag-indeterminate')) {
element.classList.add('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
else {
if (SSRMGridOptions.api.getSelectedRows().length === App.getFakeServerDataCount()) {
if (element.classList.contains('ag-indeterminate')) {
element.classList.remove('ag-indeterminate');
}
if (!element.classList.contains('ag-checked')) {
element.classList.add('ag-checked');
}
}
else {
if (!element.classList.contains('ag-indeterminate')) {
element.classList.add('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
}
}
else {
if (!element.classList.contains('ag-indeterminate')) {
element.classList.add('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
}
else {
if (App.isFakeServerDataLoaded()) {
if (SSRMGridOptions.api.getDisplayedRowCount() < App.getFakeServerDataCount()) {
if (SSRMGridOptions.api.getSelectedRows().length === 0) {
if (element.classList.contains('ag-indeterminate')) {
element.classList.remove('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
else {
if (!element.classList.contains('ag-indeterminate')) {
element.classList.add('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
}
else {
if (SSRMGridOptions.api.getSelectedRows().length === 0) {
if (element.classList.contains('ag-indeterminate')) {
element.classList.remove('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
else {
if (!element.classList.contains('ag-indeterminate')) {
element.classList.add('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
}
}
else {
if (SSRMGridOptions.api.getSelectedRows().length === 0) {
if (element.classList.contains('ag-indeterminate')) {
element.classList.remove('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
else {
if (!element.classList.contains('ag-indeterminate')) {
element.classList.add('ag-indeterminate');
}
if (element.classList.contains('ag-checked')) {
element.classList.remove('ag-checked');
}
}
}
}
},
onGridReady: params => {
$('input[name=searchGrid]').val(searchInListValue);
SSRMGridOptions.api.setQuickFilter(searchInListValue);
var locId = location.href.replace('#','');
locId = locId + '#itemsGrid';
var getSort = $.parseJSON(localStorage.getItem(locId));
setTimeout(() => {
params.columnApi.applyColumnState({state: getSort, applyOrder: true});
}, 3000);
},
onModelUpdated: params => {
if (fakeServerSelectedRows.length > 0) {
SSRMGridOptions.api.forEachNode((node, index) => {
if (node.data) {
if (fakeServerSelectedRows.filter(row => row.id === node.data.id).length > 0) {
var row = fakeServerSelectedRows.filter(row => row.id === node.data.id);
node.setSelected(true);
} else {
node.setSelected(false);
}
}
});
}
var savedSort = params.columnApi.getColumnState();
var sortState = savedSort
.filter(function (s) {
return s.sort != null;
})
.map(function (s) {
return { colId: s.colId, sort: s.sort, sortIndex: s.sortIndex };
});
var countRows = params.api.getDisplayedRowCount();
if (!fakeServerDataLoaded) {
countRows--;
}
if (!mobileGrid) {
if (!SSRMGridOptions.doNotGenerateTotalRow) {
let pinnedBottomData = App.generateSSRMPinnedTotalRow(columnDefs, columns, fakeServerData.length, sortState);
SSRMGridOptions.api.setPinnedBottomRowData(pinnedBottomData);
}
}
},
onFirstDataRendered: params => {
firstDataRendered = true;
setTimeout(() => {
App.autoSizeAll(SSRMGridOptions);
if (fieldsToBeFormattedForAgg.length > 0) {
setTimeout(() => {
SSRMGridOptions.api.expandAll();
setTimeout(() => {
App.autoSizeAll(SSRMGridOptions);
}, 1000);
}, 500);
}
}, 1000);
},
onDisplayedColumnsChanged: params => {
if(searchInListValue) {
$('input[name=searchGrid]').val(searchInListValue);
SSRMGridOptions.api.setQuickFilter(searchInListValue);
}
if (firstDataRendered) {
App.ColumnStateToLocalStorage(params);
}
},
onBodyScroll: params => {
var bottom_px = SSRMGridOptions.api.getVerticalPixelRange().bottom;
var grid_height = SSRMGridOptions.api.getDisplayedRowCount() * SSRMGridOptions.api.getSizesForCurrentTheme().rowHeight;
if(bottom_px == grid_height)
{
}
},
onColumnResized: params => {
if (firstDataRendered) {
App.ColumnStateToLocalStorage(params);
}
},
onSortChanged: params => {
var savedSort = params.columnApi.getColumnState();
if(savedSort != ''){
var locId = location.href.replace('#','');
locId = locId + '#itemsGrid';
localStorage.setItem(locId,JSON.stringify(savedSort));
}
}
};
if(SSRMGridOptionsIncluded != null){
$.each(SSRMGridOptionsIncluded,(key,value) => {
SSRMGridOptions[key] = value;
});
}
$('input[name=searchGrid]').on('keyup', function (event) {
if (event.keyCode == 13 || event.keyCode == 16 || event.keyCode == 17 || event.keyCode == 18 || event.keyCode == 91) {
event.preventDefault();
return false;
}
customParams.quickFilterValue = $(this).val();
SSRMGridOptions.api.onFilterChanged();
});
var fakeServer;
var dataSource;
var customParams = {};
customParams.quickFilterValue = '';
fakeServerMethods = [];
fakeServerData = [];
firstDataRendered = false;
columnDefs.forEach(function(columnDef, index) {
if (columnDef.filter) {
if (columnDef.filterParams) {
if (columnDef.filterParams.fakeServerSql) {
var sql = columnDef.filterParams.fakeServerSql
var method = function () {
var fieldName = columnDef.field;
return alasql(sql, [fakeServerData]).map(function (x) {
return x[fieldName];
});
}
columnDef.filterParams.values = params => {
setTimeout(function () {
params.success(method());
}, 500);
};
fakeServerMethods.push({
name: columnDef.field,
method: method,
});
}
}
}
});
new agGrid.Grid(document.querySelector(divID), SSRMGridOptions);
SSRMGridOptions.api.showLoadingOverlay();
this.requestPostAsync(jsonData, jsonUrl, json => {
setTimeout(() => {
console.log(json.data)
fakeServerData = json.data;
if (fieldsToBeFormattedForAgg.length > 0) {
fakeServerData = fakeServerData.map(data => {
var temp = Object.assign({}, data);
for (var k = 0; k < fieldsToBeFormattedForAgg.length; k++) {
temp[fieldsToBeFormattedForAgg[k]] = Number(data[fieldsToBeFormattedForAgg[k]]);
}
return temp;
});
}
console.log(fakeServerData)
console.log('Fake Server Data Loaded');
fakeServerSelectedRows = [];
SSRMGridResized = false;
fakeServer = new this.fakeServer(columnDefs, fakeServerData);
dataSource = new this.serverSideDataSource(fakeServer, customParams);
SSRMGridOptions.api.setServerSideDatasource(dataSource);
if (!mobileGrid) {
if (!SSRMGridOptions.doNotGenerateTotalRow) {
var savedSort = SSRMGridOptions.columnApi.getColumnState();
var sortState = savedSort
.filter(function (s) {
return s.sort != null;
})
.map(function (s) {
return { colId: s.colId, sort: s.sort, sortIndex: s.sortIndex };
});
let pinnedBottomData;
if (json.data.length > 100) {
fakeServerDataLoaded = false;
pinnedBottomData = App.generateSSRMPinnedTotalRow(columnDefs, columns, json.data.length, sortState);
} else {
fakeServerDataLoaded = true;
pinnedBottomData = App.generateSSRMPinnedTotalRow(columnDefs, columns, json.data.length, sortState);
}
SSRMGridOptions.api.setPinnedBottomRowData(pinnedBottomData);
}
}
if (useLocalStorage) {
if (typeof localStorage !== 'undefined') {
var locId = location.href.replace('#','');
locId = locId + '#itemsGrid';
if (typeof localStorage[locId] !== 'undefined') {
var columnState = JSON.parse(localStorage[locId]);
SSRMGridOptions.columnApi.setColumnState(columnState);
}
}
}
SSRMGridOptions.api.hideOverlay();
}, 500);
});
HELPER FUNCTIONS
this.getFakeServerDataCount = function getFakeServerDataCount() {
return fakeServerData.length;
};
this.CustomSelectBoxHeader = function CustomSelectBoxHeader() {};
this.CustomSelectBoxHeader.prototype.init = function (params) {
var template = '';
if (params.showMe) {
template =
'<input type="checkbox" class="ag-input-field-input ag-checkbox-input" id="customSelectBox" style="height: 20px; width: 20px;" />';
}
else {
template =
'<span>' + params.displayName + '</span>';
}
this.params = params;
this.eGui = document.createElement('div');
var eGui = this.eGui;
eGui.setAttribute('role', 'presentation');
eGui.classList.add('ag-header-select-all');
eGui.classList.add('ag-labeled');
eGui.classList.add('ag-label-align-right');
eGui.classList.add('ag-checkbox');
eGui.classList.add('ag-input-field');
var wrapper = document.createElement('div');
wrapper.id = "customSelectBox";
wrapper.setAttribute('role', 'presentation');
wrapper.classList.add('ag-wrapper');
wrapper.classList.add('ag-input-wrapper');
wrapper.classList.add('ag-checkbox-input-wrapper');
wrapper.innerHTML = template;
this.eSelectAllButton = wrapper.querySelector('#customSelectBox');
this.onChangeListener = this.selectAll.bind(this, this.eSelectAllButton);
this.eSelectAllButton.addEventListener('change', this.onChangeListener);
eGui.append(wrapper);
};
this.CustomSelectBoxHeader.prototype.selectAll = function (element) {
if (App.isFakeServerDataLoaded()) {
if (SSRMGridOptions.api.getDisplayedRowCount() < App.getFakeServerDataCount()) {
if (SSRMGridOptions.api.getSelectedRows().length < SSRMGridOptions.api.getDisplayedRowCount()) {
this.params.api.forEachNode(function (node) {
node.setSelected(true);
});
this.mark.bind(this, element, 'indeterminate');
}
else {
this.params.api.deselectAll();
this.mark.bind(this, element, '');
}
}
else {
if (SSRMGridOptions.api.getSelectedRows().length < App.getFakeServerDataCount()) {
this.params.api.forEachNode(function (node) {
node.setSelected(true);
});
this.mark.bind(this, element, 'checked');
} else {
this.params.api.deselectAll();
this.mark.bind(this, element, '');
}
}
}
else {
if (SSRMGridOptions.api.getSelectedRows().length < SSRMGridOptions.api.getDisplayedRowCount() - 1) {
this.params.api.forEachNode(function(node) {
node.setSelected(true);
});
this.mark.bind(this, element, 'indeterminate');
}
else {
this.params.api.deselectAll();
this.mark.bind(this, element, '');
}
}
};
this.CustomSelectBoxHeader.prototype.mark = function (element, status) {
if (status === '') {
if (element.parentNode.classList.contains('ag-indeterminate')) {
element.parentNode.classList.remove('ag-indeterminate');
}
if (element.parentNode.classList.contains('ag-checked')) {
element.parentNode.classList.remove('ag-checked');
}
}
else if (status === 'indeterminate') {
if (!element.parentNode.classList.contains('ag-indeterminate')) {
element.parentNode.classList.add('ag-indeterminate');
}
if (element.parentNode.classList.contains('ag-checked')) {
element.parentNode.classList.remove('ag-checked');
}
}
else if (status === 'checked') {
if (element.parentNode.classList.contains('ag-indeterminate')) {
element.parentNode.classList.remove('ag-indeterminate');
}
if (!element.parentNode.classList.contains('ag-checked')) {
element.parentNode.classList.add('ag-checked');
}
}
};
this.CustomSelectBoxHeader.prototype.getGui = function () {
return this.eGui;
};
this.CustomSelectBoxHeader.prototype.destroy = function () {
this.eSelectAllButton.removeEventListener('click', this.onChangeListener);
}
this.isFakeServerDataLoaded = function isFakeServerDataLoaded() {
return fakeServerDataLoaded;
};
this.generateSSRMPinnedTotalRow = function generateSSRMPinnedTotalRow(columnDefs, columns, total, sortState) {
var arr = [];
var obj = {};
var totalRows = [];
columnDefs.forEach(function(columnDef, index) {
if (columnDef.filterParams) {
if (columnDef.filterParams.totalRow) {
totalRows.push(columnDef.field)
}
}
});
var serverData = fakeServerData.slice();
if (sortState !== null) {
if (sortState.length > 0) {
sortState.sort((a, b) => a.sortIndex - b.sortIndex);
var sortArgs = [];
sortArgs[0] = serverData;
sortState.forEach(item => {
if (item.sort === 'desc') {
sortArgs.push([item.colId, true]);
}
else {
sortArgs.push(item.colId);
}
});
App.objSort.apply(this, sortArgs);
}
}
for (var i = 0; i < columns.length; i++) {
if (!('checkboxSelection' in columns[i]) && !('hide' in columns[i])) {
var key = columns[i].field;
if (Object.keys(obj).length === 0) {
obj[key] = $_TOTAL_ROW_COUNT;
}
else if (Object.keys(obj).length === 1) {
var cnt = 0;
SSRMGridOptions.api.forEachNode(function (node) {
cnt++;
});
obj[key] = total;
}
else {
obj[key] = undefined;
if (totalRows.includes(key)) {
var sumOfAll = serverData.reduce((total, obj) => parseFloat(obj[key]) + parseFloat(total), 0);
var sum = 0;
SSRMGridOptions.api.forEachNode(function (node) {
if ('data' in node) {
sum += parseFloat(node.data[key]);
}
});
obj[key] = sumOfAll;
}
}
}
}
arr.push(obj);
return arr;
};
this.ColumnStateToLocalStorage = function ColumnStateToLocalStorage(e, detailGrid) {
setTimeout(_ => {
if (typeof localStorage !== 'undefined') {
var locId = location.href.replace('#', '');
if (!detailGrid) {
locId = locId + '#itemsGrid';
} else {
locId = locId + '#itemsGridDetail';
}
var state = e.columnApi.getColumnState();
localStorage[locId] = JSON.stringify(state);
} else {
App.autoSizeAll(e);
}
}, 750);
};
this.serverSideDataSource = function serverSideDataSource(fakeServer, customParams) {
return {
getRows: function (params) {
console.log('[Datasource] - rows requested by grid: ', params.request);
params.request.customParams = customParams
var response = fakeServer.getData(params.request);
fakeServerDataLoaded = response.lastRow !== -1;
setTimeout(function () {
if (response.success) {
params.success({
rowData: response.rows,
rowCount: response.lastRow,
});
} else {
params.fail();
}
}, 500);
},
};
};
Solution 2:[2]
PART 2
We keep going..
this.fakeServer = function fakeServer(columnDefs, fakeServerData) {
alasql.options.cache = false;
var obj = {
getData: function (request) {
var results = executeQuery(request);
console.log('results')
console.log(results)
return {
success: true,
rows: results,
lastRow: getLastRowIndex(request, results),
};
},
};
columnDefs.forEach(function(columnDef, index) {
if (columnDef.filter) {
if (columnDef.filterParams) {
if (columnDef.filterParams.fakeServerSql) {
var method = fakeServerMethods.find(fakeServerMethod => fakeServerMethod.name === columnDef.field).method;
var methodName = 'get_' + columnDef.field;
obj[methodName] = method;
}
}
}
});
return obj;
function executeQuery(request) {
var sql = buildSql(request);
console.log('[FakeServer] - about to execute query:', sql);
var rowGroupCols = request.rowGroupCols;
var groupKeys = request.groupKeys;
if (!isDoingGrouping(rowGroupCols, groupKeys)) {
return alasql(sql, [fakeServerData]);
}
var groupsToUse = request.rowGroupCols.slice(
groupKeys.length,
groupKeys.length + 1
);
var groupColId = groupsToUse[0].id;
var childCountResult = executeGroupChildCountsQuery(request, groupColId);
return alasql(sql, [fakeServerData]).map(function (group) {
group['childCount'] = childCountResult[group[groupColId]];
return group;
});
}
function executeGroupChildCountsQuery(request, groupId) {
var sql = interpolate(
'SELECT {0} FROM ? pivot (count({0}) for {0})' + whereSql(request),
[groupId]
);
console.log(
'[FakeServer] - about to execute group child count query:',
sql
);
return alasql(sql, [fakeServerData])[0];
}
function buildSql(request) {
return (
selectSql(request) +
' FROM ?' +
whereSql(request) +
groupBySql(request) +
orderBySql(request) +
limitSql(request)
);
}
function selectSql(request) {
var rowGroupCols = request.rowGroupCols;
var valueCols = request.valueCols;
var groupKeys = request.groupKeys;
if (isDoingGrouping(rowGroupCols, groupKeys)) {
var rowGroupCol = rowGroupCols[groupKeys.length];
var colsToSelect = [rowGroupCol.id];
valueCols.forEach(function (valueCol) {
colsToSelect.push(
valueCol.aggFunc + '(' + valueCol.id + ') AS ' + valueCol.id
);
});
return 'SELECT ' + colsToSelect.join(', ');
}
return 'SELECT *';
}
function isDoingGrouping(rowGroupCols, groupKeys) {
return rowGroupCols.length > groupKeys.length;
}
function createFilterSql(mapper, key, item) {
if (item.operator) {
var condition1 = mapper(key, item.condition1);
var condition2 = mapper(key, item.condition2);
return '(' + condition1 + ' ' + item.operator + ' ' + condition2 + ')';
}
return mapper(key, item);
}
function textFilterMapper(key, item) {
switch (item.type) {
case 'equals':
return key + " = '" + item.filter + "'";
case 'notEqual':
return key + "' != '" + item.filter + "'";
case 'contains':
return key + " LIKE '%" + item.filter + "%'";
case 'notContains':
return key + " NOT LIKE '%" + item.filter + "%'";
case 'startsWith':
return key + " LIKE '" + item.filter + "%'";
case 'endsWith':
return key + " LIKE '%" + item.filter + "'";
default:
console.log('unknown text filter type: ' + item.type);
}
}
function numberFilterMapper(key, item) {
switch (item.type) {
case 'equals':
return 'CAST(' + key + ' AS DECIMAL(10,2)) = ' + item.filter.toFixed(2);
case 'notEqual':
return 'CAST(' + key + ' AS DECIMAL(10,2)) != ' + item.filter.toFixed(2);
case 'greaterThan':
return 'CAST(' + key + ' AS DECIMAL(10,2)) > ' + item.filter.toFixed(2);
case 'greaterThanOrEqual':
return 'CAST(' + key + ' AS DECIMAL(10,2)) >= ' + item.filter.toFixed(2);
case 'lessThan':
return 'CAST(' + key + ' AS DECIMAL(10,2)) < ' + item.filter.toFixed(2);
case 'lessThanOrEqual':
return 'CAST(' + key + ' AS DECIMAL(10,2)) <= ' + item.filter.toFixed(2);
case 'inRange':
return (
'(CAST(' +
key +
' AS DECIMAL(10,2)) >= ' +
item.filter +
' and CAST(' +
key +
' AS DECIMAL(10,2)) <= ' +
item.filterTo +
')'
);
default:
console.log('unknown number filter type: ' + item.type);
}
}
function dateFilterMapper(key, item) {
switch (item.type) {
case 'equals':
return key + ' = "' + moment(item.dateFrom).format('YYYY-MM-DD') + '"';
case 'notEqual':
return key + ' != "' + moment(item.dateFrom).format('YYYY-MM-DD') + '"';
case 'greaterThan':
return key + ' > "' + moment(item.dateFrom).format('YYYY-MM-DD') + '"';
case 'lessThan':
return key + ' < "' + moment(item.dateFrom).format('YYYY-MM-DD') + '"';
case 'inRange':
return (
'(' +
key +
' >= "' +
moment(item.dateFrom).format('YYYY-MM-DD') +
'" and ' +
key +
' <= "' +
moment(item.dateTo).format('YYYY-MM-DD') +
'")'
);
default:
console.log('unknown number filter type: ' + item.type);
}
}
function whereSql(request) {
var whereParts = [];
var filterModel = request.filterModel;
var rowGroups = request.rowGroupCols;
var groupKeys = request.groupKeys;
if (groupKeys) {
console.log('grouping where!');
groupKeys.forEach(function (key, i) {
console.log(key);
var value = typeof key === 'string' ? "'" + key + "'" : key;
whereParts.push(rowGroups[i].id + ' = ' + value);
});
}
if (filterModel) {
Object.keys(filterModel).forEach(function (columnKey) {
var filter = filterModel[columnKey];
switch (filter.filterType) {
case 'set':
whereParts.push(
columnKey + " IN ('" + filter.values.join("', '") + "')"
);
break;
case 'text':
whereParts.push(createFilterSql(textFilterMapper, columnKey, filter));
break;
case 'number':
whereParts.push(createFilterSql(numberFilterMapper, columnKey, filter));
break;
case 'date':
whereParts.push(createFilterSql(dateFilterMapper, columnKey, filter));
break;
default:
console.log('unknown filter type: ' + filter.filterType);
break;
}
});
}
var str = "(";
if (whereParts.length > 0) {
if (request.customParams.quickFilterValue !== '') {
if (columnDefs.length > 0) {
columnDefs.forEach(function (columnDef, index) {
if (columnDef.field !== 'no' && columnDef.field !== 'media' && columnDef.field !== 'media1') {
if (index === 0)
str += columnDef.field + " like '%" + request.customParams.quickFilterValue + "%";
else
str += "' OR " + columnDef.field + " like '%" + request.customParams.quickFilterValue + "%";
}
});
str +="')";
whereParts.push(str);
}
}
return ' WHERE ' + whereParts.join(' AND ');
}
else {
if (request.customParams.quickFilterValue !== '') {
columnDefs.forEach(function (columnDef, index) {
if (columnDef.field !== 'no' && columnDef.field !== 'media' && columnDef.field !== 'media1') {
if (index === 0)
str += columnDef.field + " like '%" + request.customParams.quickFilterValue + "%";
else
str += "' OR " + columnDef.field + " like '%" + request.customParams.quickFilterValue + "%";
}
});
str +="')";
return "WHERE " + str;
}
}
return '';
}
function groupBySql(request) {
var rowGroupCols = request.rowGroupCols;
var groupKeys = request.groupKeys;
if (isDoingGrouping(rowGroupCols, groupKeys)) {
var rowGroupCol = rowGroupCols[groupKeys.length];
return ' GROUP BY ' + rowGroupCol.id;
}
return '';
}
function orderBySql(request) {
var sortModel = request.sortModel;
if (sortModel.length === 0) return '';
var sorts = sortModel.map(function (s) {
return s.colId + ' ' + s.sort.toUpperCase();
});
return ' ORDER BY ' + sorts.join(', ');
}
function limitSql(request) {
var blockSize = request.endRow - request.startRow;
return ' LIMIT ' + (blockSize + 1) + ' OFFSET ' + request.startRow;
}
function getLastRowIndex(request, results) {
if (!results || results.length === 0) {
return request.startRow;
}
var currentLastRow = request.startRow + results.length;
return currentLastRow <= request.endRow ? currentLastRow : -1;
}
function interpolate(str, o) {
return str.replace(/{([^{}]*)}/g, function (a, b) {
var r = o[b];
return typeof r === 'string' || typeof r === 'number' ? r : a;
});
}
};
this.autoSizeAll = function autoSizeAll(gridOptions) {
var allColumnIds = [];
gridOptions.columnApi.getAllColumns().forEach((column) => {
allColumnIds.push(column.colId);
});
gridOptions.columnApi.autoSizeColumns(allColumnIds);
};
Solution 3:[3]
PART 3
Finally BASIC USAGE
<div id="myGrid" class="ag-theme-balham" style="height: 100%;"></div>
<script type="text/javascript">
var columns = [
{headerName: '<?php echo ENTERPRISE_NAME; ?>', field: 'enterprise_name',
menuTabs: ['filterMenuTab', 'columnsMenuTab'],
lockPosition: true,
cellStyle: params => App.recordCellStyle(params),
pinned: 'left',
cellRenderer: App.CustomEnterpriseCellRenderer,
filter: 'agSetColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT enterprise_name FROM ? ORDER BY enterprise_name',
},
},
{headerName: '<?php echo SWATCH_CARD_CODE; ?>', field: 'code',
menuTabs: ['filterMenuTab', 'columnsMenuTab'],
lockPosition: true,
cellRenderer: 'agGroupCellRenderer',
cellStyle: params => App.recordCellStyle(params),
pinned: 'left',
filter: 'agSetColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT code FROM ? ORDER BY code',
},
},
{
headerName: '<?php echo TRANSACTIONS; ?>', field: 'transactions',
menuTabs: ['columnsMenuTab'],
lockPosition: true,
cellRenderer: function (event) {
var editUrl = '/swatch_cards/swatch_cards_edit/' + event.data.uid;
var editBtn = '<a href="'+editUrl+'" class="btn m-btn m-btn--hover-primary m-btn--icon m-btn--icon-only m-portlet__nav-link"><i class="fal fa-edit"></i></a>';
var orderUrl = '/swatch_cards/swatch_cards_orders_edit/' + event.data.uid;
var orderBtn = '<a href="#" onclick="App.loadModalFunc(\'' + orderUrl + '\',\'modal-sm\')" class="btn m-btn m-btn--hover-primary m-btn--icon m-btn--icon-only ' +
'm-portlet__nav-link"><i class="fa fa-shopping-cart"></i></a>';
var deleteBtn = '<a href="#" onclick="deletedRow(\'' + event.data.uid + '\')" class="btn m-btn m-btn--hover-danger m-btn--icon m-btn--icon-only m-portlet__nav-link"><i class="fal fa-trash-alt"></i></a>';
var printStickerUrl = '/swatch_cards/swatch_cards_print_sticker_modal/' + event.data.uid;
var printStickerBtn = '<a href="#" onclick="App.loadModalFunc(\'' + printStickerUrl + '\',\'modal-lg\')" class="btn m-btn m-btn--hover-primary m-btn--icon m-btn--icon-only m-portlet__nav-link"><i class="la la-qrcode"></i></a>';
return printStickerBtn + editBtn + orderBtn;
},
cellStyle: params => App.recordCellStyle(params),
pinned: 'left',
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.transactions)
},
{headerName: '<?php echo ENTERPRISE_SWATCH_CARD_CODE; ?>', field: 'enterprise_swatch_card_code', hide: true, filter: 'agTextColumnFilter'},
{headerName: '<?php echo ARTICLE_CODE; ?>', field: 'article_code', filter: 'agTextColumnFilter'},
{headerName: '<?php echo QUALITY_CODE; ?>', field: 'quality_code', filter: 'agTextColumnFilter'},
{headerName: '<?php echo CLOTH_TYPE; ?>', field: 'cloth_type',
cellRenderer: function(event) {
var cloth_type;
if(event.data.cloth_type == 0) {
cloth_type = 'Dokuma';
}
else if(event.data.cloth_type == 1) {
cloth_type = 'Örme';
}
else if(event.data.cloth_type == 2) {
cloth_type = 'Dantel-Brode';
}
else if(event.data.cloth_type == 3) {
cloth_type = 'PU-Deri';
}
return cloth_type;
},
valueFormatter: (data) => {
var cloth_type;
if(data.value == 0) {
cloth_type = 'Dokuma';
}
else if(data.value == 1) {
cloth_type = 'Örme';
}
else if(data.value == 2) {
cloth_type = 'Dantel-Brode';
}
else if(data.value == 3) {
cloth_type = 'PU-Deri';
}
return cloth_type;
},
filter: 'agSetColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT cloth_type FROM ? ORDER BY cloth_type',
valueFormatter: (data) => {
var cloth_type;
if(data.value == 0) {
cloth_type = 'Dokuma';
}
else if(data.value == 1) {
cloth_type = 'Örme';
}
else if(data.value == 2) {
cloth_type = 'Dantel-Brode';
}
else if(data.value == 3) {
cloth_type = 'PU-Deri';
}
return cloth_type;
},
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.cloth_type)
},
{headerName: '<?php echo WIDTH; ?>', field: 'width',
cellRenderer: (event) => {
return event.data.width + ' cm';
},
filter: 'agNumberColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT width FROM ? ORDER BY width',
suppressAndOrCondition: true,
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.width)
},
{headerName: '<?php echo WEIGHT; ?>', field: 'weight',
cellRenderer: (event) => {
var unit = '';
switch (event.data.weight_unit) {
case '3':
unit = 'Gr/m';
break;
case '4':
unit = 'Gr/m²';
break;
}
return event.data.weight + ' ' + unit;
},
filter: 'agNumberColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT weight FROM ? ORDER BY weight',
suppressAndOrCondition: true,
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.weight)
},
{headerName: '<?php echo QUANTITY; ?>', field: 'quantity',
cellRenderer: (event) => {
var unit = parseInt(event.data.unit);
var unit_text = '';
switch (unit) {
case 0:
unit_text = 'ad.';
break;
case 1:
unit_text = 'm';
break;
}
return event.data.quantity + ' ' + unit_text;
},
filter: 'agNumberColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT quantity FROM ? ORDER BY quantity',
totalRow: true,
suppressAndOrCondition: true,
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.quantity)
},
{headerName: '<?php echo PRICE; ?>', field: 'price',
cellRenderer: (event) => {
var currency = parseInt(event.data.currency),
currency_symbol = '',
price = (event.data.price) ? event.data.price : '0.00';
switch (currency) {
case 0:
currency_symbol = '?';
break;
case 1:
currency_symbol = '€';
break;
case 2:
currency_symbol = '$';
break;
default:
currency_symbol = '?';
break;
}
return currency_symbol + ' ' + price;
},
filter: 'agNumberColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT price FROM ? ORDER BY price',
suppressAndOrCondition: true,
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.price)
},
{headerName: '<?php echo DESCRIPTION; ?>', field: 'description', filter: 'agTextColumnFilter'},
{headerName: '<?php echo COMPOSITION; ?>', field: 'composition',
cellRenderer: (event) => {
var result='';
if (event.data.composition) {
var obj = JSON.parse(event.data.composition);
for (var item of obj) {
if(item.value > 0) {
result += item.key + ': ' + item.value + ', ';
}
}
result = result.slice(0,-2);
}
else {
result = '<div class="loader"></div>';
}
return result;
},
valueFormatter: (data) => {
var result='';
if (data.value) {
var obj = JSON.parse(data.value);
for (var item of obj) {
if(item.value > 0) {
result += item.key + ': ' + item.value + ', ';
}
}
result = result.slice(0,-2);
}
return result;
},
filter: 'agTextColumnFilter',
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.composition)
},
{headerName: '<?php echo CATEGORY; ?>', field: 'category',
cellRenderer: (event) => {
var result='';
if (event.data.category) {
var obj = JSON.parse(event.data.category);
for (var item of obj) {
if(item.is_checked) {
result += item.label + ', ';
}
}
result = result.slice(0,-2);
}
else {
result = '<div class="loader"></div>';
}
return result;
},
valueFormatter: (data) => {
var result='';
if (data.value) {
var obj = JSON.parse(data.value);
for (var item of obj) {
if(item.is_checked) {
result += item.label + ', ';
}
}
result = result.slice(0,-2);
}
return result;
},
filter: 'agTextColumnFilter',
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.category)
},
{headerName: '<?php echo XXX_MEDIA; ?>', field: 'media', hide: true, suppressColumnsToolPanel: true},
{headerName: '<?php echo YYY_MEDIA; ?>', field: 'media1', hide: true, suppressColumnsToolPanel: true},
{headerName: '<?php echo ENTERPRISE_MEDIA_COUNT; ?>', field: 'epc',
filter: 'agNumberColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT epc FROM ? ORDER BY epc',
totalRow: true,
suppressAndOrCondition: true,
}
},
{headerName: '<?php echo YYY_MEDIA_COUNT; ?>', field: 'tfpc',
filter: 'agNumberColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT tfpc FROM ? ORDER BY tfpc',
totalRow: true,
suppressAndOrCondition: true,
},
},
{
headerName: '<?php echo CREATED_DATE; ?>', field: 'created_date',
valueFormatter: function(data) {
return ((data.value !== null && moment(data.value, 'YYYY-MM-DD',true).isValid()) && (data.value !== '0000-00-00')) ? moment(data.value).format('DD.MM.YYYY') : '';
},
cellRenderer: function (event) {
var created_date = new Date(event.data.created_date);
return moment(created_date).format('DD.MM.YYYY');
},
filter: 'agDateColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT created_date FROM ? ORDER BY created_date',
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.created_date)
},
{
headerName: '<?php echo UPDATED_DATE; ?>', field: 'updated_date',
valueFormatter: function(data) {
return ((data.value !== null && moment(data.value, 'YYYY-MM-DD',true).isValid()) && (data.value !== '0000-00-00')) ? moment(data.value).format('DD.MM.YYYY') : '';
},
cellRenderer: function (event) {
var updated_date = new Date(event.data.updated_date);
return moment(updated_date).format('DD.MM.YYYY');
},
filter: 'agDateColumnFilter',
filterParams: {
fakeServerSql: 'SELECT DISTINCT updated_date FROM ? ORDER BY updated_date',
},
pinnedRowCellRenderer: event => App.recordPinnedRowCellRenderer(event.data.updated_date)
},
];
var detailColumns = [
{headerName: '<?php echo ENTERPRISE_MEDIA; ?>', field: 'media', width: 300, cellRenderer: XXX},
{headerName: '<?php echo YYY_MEDIA; ?>', field: 'media1', width: 300, cellRenderer: YYY},
];
var SSRMGridOptionsIncluded = {
suppressDragLeaveHidesColumns: true,
sideBar: {
toolPanels: [
{
id: 'columns',
labelDefault: 'Columns',
labelKey: 'columns',
iconKey: 'columns',
toolPanel: 'agColumnsToolPanel',
toolPanelParams: {
suppressRowGroups: true,
suppressValues: true,
suppressPivots: true,
suppressPivotMode: true,
},
},
],
},
masterDetail: true,
detailRowHeight: 500,
detailCellRendererParams: {
detailGridOptions: {
suppressDragLeaveHidesColumns: true,
defaultColDef: {
sortable: true,
filter: true,
resizable: true,
},
headerHeight: 45,
rowStyle: {fontFamily: 'Comic Sans MS, Comic Sans, Verdana', color: 'darkslategray'},
sideBar: {
hiddenByDefault: true,
toolPanels: [
{
id: 'columns',
labelDefault: 'Columns',
labelKey: 'columns',
iconKey: 'columns',
toolPanel: 'agColumnsToolPanel',
toolPanelParams: {
suppressRowGroups: true,
suppressValues: true,
suppressPivots: true,
suppressPivotMode: true,
},
},
],
defaultToolPanel: 'columns',
},
columnDefs: detailColumns,
rowHeight: 400,
groupUseEntireRow: true,
groupDefaultExpanded: -1,
animateRows: true,
localeText: AG_GRID_LANGUAGE_TEXT
},
getDetailRowData: function (params) {
console.log(params.data.media);
console.log(params.data.media1);
params.successCallback([{"media": params.data.media, "media1": params.data.media1}]);
setTimeout(() => {
var script = document.createElement('script');
script.type = 'text/javascript';
script.innerHTML = "$('.myZoom1').ezPlus({easing: true});$('.myZoom2').ezPlus({});";
document.body.appendChild(script);
}, 3000)
},
},
onGridReady: function (params) {
params.api.sizeColumnsToFit();
},
components: {
XXX: XXX,
YYY: YYY,
},
getRowStyle: function (event) {
if (event.data != null) {
if (event.data.uid === '<?php echo $this->session->userdata('COMPANY_UID'); ?>') {
return {backgroundColor: '#b8f2ff'};
}
}
return null;
}
};
<?php
$compositions_array = json_encode($compositions);
echo "var compositions_array = ". $compositions_array . ";\n";
?>
var dataList = {
filter_where: [
{
enterprise_uid: '<?php echo $filter_where['enterprise_uid']; ?>',
category: '<?php echo $filter_where['category']; ?>',
quality_code: '<?php echo $filter_where['quality_code']; ?>',
article_code: '<?php echo $filter_where['article_code']; ?>',
q_a_d: '<?php echo $filter_where['q_a_d']; ?>',
xxx_picture: '<?php echo $filter_where['xxx_picture']; ?>',
yyy_picture: '<?php echo $filter_where['yyy_picture']; ?>',
start_date: '<?php echo $filter_where['start_date']; ?>',
end_date: '<?php echo $filter_where['end_date']; ?>'
}
],
compositions: compositions_array,
csrf_test_name: csrfTokenHash
};
App.SSRMAgGrid(columns, SSRMGridOptionsIncluded, dataList, '/swatch_cards/swatch_cards_list_json', ['category', 'composition']);
</script>
Controller
public function swatch_cards_list_json() {
if(!Auth::isAllow(Db_auths_file::ListSwatchCards)) { $this->error_(); return; }
$data = array(
'company_uid' => $this->session->userdata('COMPANY_UID'),
'enterprise_uid' => $this->input->post('filter_where')[0]['enterprise_uid'],
'compositions' => (array)$this->input->post('compositions'),
'category' => $this->input->post('filter_where')[0]['category'],
'quality_code' => $this->input->post('filter_where')[0]['quality_code'],
'article_code' => $this->input->post('filter_where')[0]['article_code'],
'q_a_d' => $this->input->post('filter_where')[0]['q_a_d'],
'xxx_picture' => $this->input->post('filter_where')[0]['xxx_picture'],
'yyy_picture' => $this->input->post('filter_where')[0]['yyy_picture'],
'start_date' => $this->input->post('filter_where')[0]['start_date'],
'end_date' => $this->input->post('filter_where')[0]['end_date'],
'offset' => $this->input->post('offset'),
'limit' => $this->input->post('limit'),
);
$result['data'] = $this->swatch_cards_model->get_all_swatch_cards($data);
echo json_encode($result);
}
Model
public function get_all_swatch_cards($data) {
$dataArr = array(
'company_uid' => $data['company_uid'],
'start_date' => $data['start_date'],
'end_date' => $data['end_date']
);
$sql = "SELECT sc.id,sc.uid,sc.code,sc.enterprise_swatch_card_code,sc.cloth_type,sc.article_code,sc.quality_code,sc.width,sc.weight,sc.weight_unit,ifnull(s.quantity, 0) quantity,sc.unit,sc.price,currency,sc.description,"
."(SELECT m.path FROM medias m where m.swatch_card_uid = sc.uid AND m.media_owner = 0 LIMIT 1) media, "
."(SELECT m.path FROM medias m where m.swatch_card_uid = sc.uid AND m.media_owner = 1 LIMIT 1) media1, "
."DATE_FORMAT(sc.created_date, '%Y-%m-%d') created_date,DATE_FORMAT(sc.updated_date, '%Y-%m-%d') updated_date,e.id enterprise_id, e.uid enterprise_uid, e.name enterprise_name, SUM(CASE WHEN m.media_owner = 0 THEN 1 ELSE 0 END) epc, SUM(CASE WHEN m.media_owner = 1 THEN 1 ELSE 0 END) tfpc "
."FROM swatch_cards AS sc "
."LEFT JOIN "
."(SELECT sd.material_uid, IFNULL(SUM(IF(s.transaction_type=1, sd.quantity*-1, sd.quantity)), 0) quantity "
."FROM stocks s "
."LEFT JOIN stock_details sd ON (sd.company_uid = :company_uid AND s.uid = sd.stock_uid) "
."WHERE s.company_uid = :company_uid GROUP BY sd.material_uid) s ON (sc.uid = s.material_uid) "
."LEFT JOIN enterprises AS e ON(e.company_uid=:company_uid AND e.uid=sc.enterprise_uid) "
."LEFT JOIN medias m ON (sc.company_uid = m.company_uid AND sc.uid = m.swatch_card_uid) "
."WHERE sc.company_uid = :company_uid";
if($data['enterprise_uid'] != 0) {
$dataArr['enterprise_uid'] = $data['enterprise_uid'];
$sql .= " AND sc.enterprise_uid = :enterprise_uid";
}
if($data['category'] != '') {
$sql .= " AND sc.code like 'F-_____-____-%" . $data['category'] . "%'";
}
if($data['quality_code'] != '') {
$dataArr['quality_code'] = $data['quality_code'];
$sql .= " AND sc.quality_code like '%' :quality_code '%'";
}
if($data['article_code'] != '') {
$dataArr['article_code'] = $data['article_code'];
$sql .= " AND sc.article_code like '%' :article_code '%'";
}
if($data['q_a_d'] != '') {
$dataArr['q_a_d'] = $data['q_a_d'];
$sql .= " AND (sc.article_code like '%' :q_a_d '%' OR sc.quality_code like '%' :q_a_d '%' OR sc.description like '%' :q_a_d '%')";
}
if (count($data['compositions']) > 0) {
foreach ($data['compositions'] as $composition) {
if ($composition['value'] == 0) {
$var = "{\"key\":\"" . $composition['key'] . "\",\"value\":%}";
$var1 = "{\"key\":\"" . $composition['key'] . "\",\"value\":0}";
$sql .= " AND sc.composition like '%" . $var . "%' AND sc.composition not like '%" . $var1 . "%'";
}
else {
$var = "{\"key\":\"" . $composition['key'] . "\",\"value\":" . $composition['value'] . "}";
$sql .= " AND sc.composition like '%" . $var . "%'";
}
}
}
$sql .= " AND DATE(sc.created_date) BETWEEN :start_date AND :end_date";
$sql .= " GROUP BY sc.uid";
if($data['xxx_picture'] != '' || $data['yyy_picture'] != '') {
$sql .= " HAVING";
if ($data['xxx_picture'] != '' && $data['yyy_picture'] == '') {
if ($data['xxx_picture'] > 0) {
$sql .= " epc > 0";
}
else {
$sql .= " epc = 0";
}
}
else if ($data['xxx_picture'] == '' && $data['yyy_picture'] != '') {
if ($data['yyy_picture'] > 0) {
$sql .= " tfpc > 0";
}
else {
$sql .= " tfpc = 0";
}
}
else {
if ($data['xxx_picture'] > 0) {
$sql .= " epc > 0";
}
else {
$sql .= " epc = 0";
}
if ($data['yyy_picture'] > 0) {
$sql .= " AND tfpc > 0";
}
else {
$sql .= " AND tfpc = 0";
}
}
}
$sql .= " ORDER BY sc.created_date DESC";
return $this->db->mySelectAll($sql,$dataArr);
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | |
| Solution 3 |