Oh, I see – The ContactDate field in the pivot table is just text, so that’s why the ‘between’ oper didn’t work. I changed the oper to ‘bw’ (begins with) and pass the year from the drop-down and it works perfectly.
All done! Thanks again for your help.
Ken
Hi Will:
I guess I should have been more specific – and perhaps I posted in the wrong forum too.
I have a PHP pivotGrid that I want to add filtering to, if that’s possible.
Here is my code:
index.php
<?php
include "sqlPDO.php";
$db = opendb('MySQL','tinicum');
$sql = "
select LpcID,
concat(LPC,' - ',LpcDescription) as LPC
from tblLpcType
order by LpcID";
$LPChtml = array();
foreach ($db->query($sql) as $row) {
$LPChtml[strval($row['LpcID'])] = $row['LPC'];
}
$sql = "
select distinct year(ContactDate) year
from tblContactNotes
where year(ContactDate) > 0
order by 1";
$Yearhtml = array();
foreach ($db->query($sql) as $row) {
$Yearhtml[strval($row['year'])] = $row['year'];
}
$sql = "
select distinct month(ContactDate) monthnum,
date_format(ContactDate,'%M') monthname
from tblContactNotes
where month(ContactDate) > 0
order by 1";
$Monthhtml = array();
foreach ($db->query($sql) as $row) {
$Monthhtml[strval($row['monthnum'])] = $row['monthname'];
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Contact Notes</title>
<link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/jquery-ui.css" />
<link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/trirand/ui.jqgrid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/ui.multiselect.css" />
<link rel="stylesheet" type="text/css" media="screen" href="/css/mtabs.css" />
<style type="text">
html, body {
margin: 0; /* Remove body margin/padding */
padding: 0;
overflow: hidden; /* Remove scroll bars on browser window */
font-size: 100%;
}
.ui-jqgrid {
font-size: 14px;
}
.ui-jqgrid .ui-jqgrid-btable tbody tr.jqgrow td {
white-space: normal !important;
height: auto;
}
.mylongdata {
height: 190px;
overflow-y: auto;
}
</style>
<script src="/jqSuite/js/jquery.min.js" type="text/javascript"></script>
<script src="/jqSuite/js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="/jqSuite/js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script>
<!-- Files for the export ---------------------->
<script type="text/javascript" language="javascript" src="/js/pivotExport/pdfmake.min.js"></script>
<script type="text/javascript" language="javascript" src="/js/pivotExport/vfs_fonts.js"></script>
<script type="text/javascript" language="javascript" src="/js/pivotExport/jszip.min.js"></script>
<script type="text/javascript">
$.jgrid.no_legacy_api = true;
$.jgrid.useJSON = true;
$.jgrid.defaults.width = "700";
</script>
<script src="/jqSuite/js/jquery-ui.min.js" type="text/javascript"></script>
<script>
var LPChtml = <?php print(json_encode($LPChtml)); ?>;
var Yearhtml = <?php print(json_encode($Yearhtml)); ?>;
var Monthhtml = <?php print(json_encode($Monthhtml)); ?>;
$(document).ready(function(){
$.each(LPChtml, function(val, text) {
// console.log("val: "+val+" text: "+text);
$('#LPC').append($("<option></option>").attr("value",val).text(text));
});
$.each(Yearhtml, function(val, text) {
// console.log("val: "+val+" text: "+text);
$('#year').append($("<option></option>").attr("value",val).text(text));
});
$.each(Monthhtml, function(val, text) {
// console.log("val: "+val+" text: "+text);
$('#month').append($("<option></option>").attr("value",val).text(text));
});
});
</script>
</head>
<body>
<?php include(“leaderboard.php”); ?> </body> </html>
leaderboard.php
<?php
@session_start();
require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php';
require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/php/PHPSuito/jqPivotGrid.php';
require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php";
// Connection to the server
$db = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$db->query("SET NAMES utf8");
$pivot = new jqPivotGrid($db);
$pivot->SelectCommand = "
SELECT concat(lm.LastName,', ',lm.FirstName) as ContactedBy,
lo.LandOwner,
cn.ContactDate,
case cn.ContactMode
when 1 then 'Phone'
when 2 then 'Other'
when 3 then 'eMail'
when 4 then 'Face to Face'
when 5 then 'Mail'
when 6 then 'Background'
when 7 then 'no contact info'
when 8 then 'N/A'
end as ContactMode,
count(*) as total
FROM tblContactNotes cn,
tblLpcMembers lm,
tblLandOwners lo
where cn.ContactedBy = lm.LpcMemberID and
cn.LandOwnerID = lo.LandOwnerID
GROUP BY cn.ContactedBy,
cn.LandOwnerID,
cn.ContactDate,
cn.ContactMode";
$pivot->setData('leaderboard.php');
$pivot->setGridOptions(array(
"rowNum" => 1000,
"width" => "auto",
"height" => "auto",
"sortname" => "ContactDate",
"sortorder" => "desc",
"rowList" => array(20,40,60,80,1000),
"altRows" => true,
"caption" => "Leader Board",
"groupingView" => array("groupCollapse" => true)
));
$pivot->setxDimension(array(
array(
"dataName" => "ContactedBy",
"width" => 240),
array(
"dataName" => "LandOwner",
"width" => 115),
array(
"dataName" => "ContactDate",
"width" => 115)
));
$pivot->setyDimension(array(
array(
"dataName" => "ContactMode")
));
$pivot->setaggregates(array(
array(
"member" => "total",
"aggregator" => "sum",
"width" => 80,
"label" => "Total",
"formatter" => "integer",
"align" => "right",
"summaryType" => "sum")
));
$pivot->setPivotOptions(array(
"rowTotals" => true,
"colTotals" => true
));
$pivot->navigator = true;
$pivot->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"view"=>false,"search"=>false,"reload"=>false));
$buttonoptions = array(
"#pager12",array(
"caption"=>"Csv",
"title"=>"Local Export to CSV",
"onClickButton"=>"js: function(){
jQuery('#grid12').jqGrid('exportToCsv');
}"
)
);
$pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,1000);
$buttonoptions = array(
"#pager12",array(
"caption"=>"Excel",
"title"=>"Local Export to Escel",
"onClickButton"=>"js: function(){
jQuery('#grid12').jqGrid('exportToExcel');
}"
)
);
$pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500);
$buttonoptions = array(
"#pager12", array(
"caption"=>"Pdf",
"title"=>"Local Export to PDF",
"onClickButton"=>"js: function(){
jQuery('#grid12').jqGrid('exportToPdf');
}"
)
);
$pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500);
$pivot->renderPivot("#grid12","#pager12", true, null, true, true);
?>
What is the secret to formatting code like you did in your post above? I did it once years ago and now can’t for the life of me figure out how to do it.
Can you post a sticky post somewhere with instructions on how to post things like that in this forum?
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top