My project had too many grid pages and it was quite the mess so I decided to make one sole generic grid file with configurations and each time the same page loads different configs depending on the page the user visits. The plan worked flawlessly until I stumbled across the problem of printing the grid with front-end filters and stuff, This is the generic grid file:
<?php include('../app/helpers/sessionShared.php'); require($_SERVER['DOCUMENT_ROOT'].'/connection/mysql.php'); require_once 'jq-config.php'; require_once "../php/jqGrid.php"; require_once "../php/jqGridPdo.php"; require_once './configs/grid_configs.php'; $pageType = isset($_GET['pagetype']) ? $_GET['pagetype'] : (isset($_POST['pagetype']) ? $_POST['pagetype'] : ''); if (empty($pageType)) { die(json_encode(array('error' => 'Page type not specified'))); } // Get grid configuration $config = getGridConfig($pageType); if (!$config) { die(json_encode(array('error' => 'Invalid page type: ' . $pageType))); } // Database connection $conn = new PDO(DB_DSN, DB_USER, DB_PASSWORD); $conn->query("SET NAMES utf8"); // Session variables $companyid = $_SESSION['companyid']; $syscompanyid = $_SESSION['syscompanyid']; $readonly = isset($_SESSION['readonly']) ? $_SESSION['readonly'] : false; if ($readonly && isset($config['adminquery'])) { $query = $config['adminquery']; $selectedUnits = "IN ( $syscompanyid )"; if ($syscompanyid == "") { $selectedUnits = ""; } $query = str_replace('{selectedUnits}', $selectedUnits, $query); } else { $query = $config['query']; } $query = str_replace('{companyid}', $companyid, $query); // Create grid instance $grid = new jqGridRender($conn); $oper = isset($_GET['oper']) ? $_GET['oper'] : (isset($_POST['oper']) ? $_POST['oper'] : ''); $grid->oper = $oper; $grid->pdffile = $pageType . '_' . date('Ymd_Hi') . '.pdf'; $grid->exportfile = $pageType . '_' . date('Ymd_Hi') . '.xlsx'; $grid->SelectCommand = $query; $grid->table = $config['table']; $grid->dataType = 'json'; $grid->setColModel(); $grid->setUrl('generic_grid.php'); $gridOptions = array( "caption" => $config['caption'], "hoverrows" => true, "autowidth" => true, "shrinkToFit" => true, "rowNum" => 100, "sortorder" => $config['sortorder'], "sortname" => $config['sortname'], "altRows" => true, "multiselect" => false, "rowList" => array(100, 200, 500, 1000, 5000, 10000), "footerrow" => $config['footer'], "userDataOnFooter" => $config['footer'], "gridview" => true, "sortable" => true, "reorderColumns" => true, "saveColumnState" => true, // Add pagetype parameter to all AJAX requests (NOT 'page'!) "postData" => array("pagetype" => $pageType) ); $grid->setGridOptions($gridOptions); // Configure columns foreach ($config['columns'] as $colName => $colProperties) { $grid->setColProperty($colName, $colProperties); } // Hide columns foreach ($config['hidden'] as $colName) { $grid->setColProperty($colName, array("hidden" => true, "viewable" => false)); } if ($grid->oper == "pdf") { $query_company = "SELECT * FROM tblcompanyinfo WHERE PKcompanyinfoID = $companyid"; $result = $conn_wp->query($query_company); $dataCompany = $result->fetch_assoc(); $grid->setPdfOptions(array( "font_name_main" => "freeserif", "font_name_data" => "freeserif", "font_name_monospace" => "freeserif", "title" => $config['caption'], "author" => "Waste+", "page_format" => "A4", "margin_top" => 20, "margin_header" => 5, "header_logo" => "blank.png", "header_logo_width" => 0, "image_scale_ratio" => 2, "grid_head_color" => "#E2F4CD", "header_title" => $dataCompany['CompanyName'], "header_string" => $dataCompany['CompanyAddress'] . " | τηλ: " . $dataCompany['CompanyTel'] . "\n" . $config['caption'], "header" => true, "grid_alternate_rows" => true, "encoding" => "UTF-8", "page_orientation" => "L", "shrink_cell" => false )); } // Navigation and toolbar $grid->navigator = true; $grid->toolbarfilter = true; $grid->gSQLMaxRows = 30000; $grid->setNavOptions('navigator', array( "excel" => true, "add" => false, "edit" => false, "del" => false, "view" => true, "search" => true, "refresh" => true, "pdf" => true )); $grid->setNavOptions('view', array("width" => 450, "height" => 330, "dataheight" => 250, "modal" => false)); // Responsive resize code $resize = (...) $grid->setJSCode($resize . $reorder); $grid->callGridMethod('#grid', 'footerData', null); // Footer data if ($config['footer'] && isset($config['footerData'])) { $grid->callGridMethod('#grid', 'footerData', array("set", $config['footerData'])); } // Render grid $summaryrows = isset($config['summary']) ? $config['summary'] : null; $grid->renderGrid('#grid', '#pager', true, $summaryrows, null, true, true); $conn = null; ?> and this is the sample of a configuration:
$configs['sell_list'] = array( 'query' => "SELECT (...)", 'adminquery' => "SELECT (...)", 'table' => 'tblwastesales', 'sortname' => 'PKSaleID', 'sortorder' => 'desc', 'caption' => 'final receivers', 'columns' => array( 'Company' => array( "width" => 60, "align" => "left"), 'sls_datefixed' => array( "formatter" => "date", "width" => 20, "align" => "center", "formatoptions" => array("srcformat" => "Y/m/d H:i", "newformat" => "d/m/Y H:i") ), 'weighttotal' => array( "formatter" => "number", "formatoptions" => array("decimalSeparator" => ",", "thousandsSeparator" => ".", "decimalPlaces" => 2), "width" => 20, "align" => "right" ), 'ProductDescription' => array("label" => "Τελικό προϊον", "width" => 70, "align" => "left"), 'wc_company' => array( "width" => 80, "align" => "left") ), 'hidden' => array('PKSaleID', 'FKMaterialID', 'sls_weight'), 'footer' => true, 'summary' => array("weighttotal" => array("round(sls_weight/1000,2)" => "SUM")), 'footerData' => array("sls_date" => "sum (t):") ); To print to excel I am using a button that goes to a javascript function with the name of the grid as a parameter (there is also a button embedded to the grid which can be used)
function fnMenuXMLExport(gridFile, pageType) { if (!pageType) { pageType = jQuery("#grid").jqGrid('getGridParam', 'postData').pagetype || ''; } if (!pageType) { alert('Missing page type for Excel export!'); return; } var exportUrl = gridFile + '?pagetype=' + encodeURIComponent(pageType) + '&oper=excel'; jQuery("#grid").jqGrid('excelExport', { url: exportUrl }); } Best result I had was being able to download an excel which was actually unreadable. I wish to know if there is an easy way to print the excel file with front-end user filters from an abstract grid. If not, I will just forget the abstact
