function launch() { global $configArray; global $interface; //////////Populate the Date Filter Start //Grab the Selected Date Start if (isset($_REQUEST['dateFilterStart'])) { if (preg_match('/\\d{1,2}\\/\\d{1,2}\\/\\d{4}/', $_REQUEST['dateFilterStart'])) { $selectedDateStart = DateTime::createFromFormat('m/d/Y', $_REQUEST['dateFilterStart']); $selectedDateStart = $selectedDateStart->getTimestamp(); } else { $selectedDateStart = strtotime($_REQUEST['dateFilterStart']); } } else { $selectedDateStart = strtotime('-30 days'); } $selectedDateStart = date('Y-m-d', $selectedDateStart); $interface->assign('selectedDateStart', $selectedDateStart); //Populate the Date Filter End //Grab the Selected End Date if (isset($_REQUEST['dateFilterEnd'])) { if (preg_match('/\\d{1,2}\\/\\d{1,2}\\/\\d{4}/', $_REQUEST['dateFilterEnd'])) { $selectedDateEnd = DateTime::createFromFormat('m/d/Y', $_REQUEST['dateFilterEnd']); $selectedDateEnd = $selectedDateEnd->getTimestamp(); } else { $selectedDateEnd = strtotime($_REQUEST['dateFilterEnd']); } } else { $selectedDateEnd = strtotime('today'); } $selectedDateEnd = date('Y-m-d', $selectedDateEnd); $interface->assign('selectedDateEnd', $selectedDateEnd); //////////Populate the Stores Filter $queryHostsFilter = "SELECT DISTINCT linkHost AS linkHost FROM external_link_tracking ORDER BY linkHost ASC"; $externalLinkTracking = new ExternalLinkTracking(); $externalLinkTracking->query($queryHostsFilter); $allHosts = array(); while ($externalLinkTracking->fetch()) { $allHosts[] = $externalLinkTracking->linkHost; } $interface->assign('hostFilter', $allHosts); //////////Grab the Selected Hosts Filter Value if (isset($_REQUEST['hostFilter'])) { $selectedHosts = $_REQUEST['hostFilter']; } else { $selectedHosts = $allHosts; } $interface->assign('selectedHosts', $selectedHosts); $baseQueryLinks = "SELECT COUNT(externalLinkId) AS timesFollowed, recordId, linkUrl, linkHost " . "FROM external_link_tracking " . "WHERE (DATE_FORMAT(trackingDate, '%Y-%m-%d')) BETWEEN '" . $selectedDateStart . "' AND '" . $selectedDateEnd . "' "; if (count($selectedHosts) > 0) { $hosts = join("','", $selectedHosts); $baseQueryLinks .= "AND linkHost IN ('" . $hosts . "') "; } $baseQueryLinks .= "GROUP BY recordId, linkUrl "; //////////Get a count of the page view data $queryPurchasesCount = "SELECT COUNT(*) AS RowCount from ( " . $baseQueryLinks . ") As ResultCount"; $resPurchasesCount = mysql_query($queryPurchasesCount); if ($resPurchasesCount > 0) { $rowCount = mysql_fetch_object($resPurchasesCount); $totalResultCount = $rowCount->RowCount; } else { $totalResultCount = 0; } //////////Create the items per page array $itemsPerPageList = $this->getItemsPerPageList(); ///////////////////PAGING $currentPage = 1; $resultTotal = $totalResultCount; $startRecord = 1; if (isset($_GET['itemsPerPage'])) { switch ($_GET['itemsPerPage']) { case "20": $itemsPerPage = 20; $itemsPerPageList["20"]["selected"] = true; break; case "100": $itemsPerPage = 100; $itemsPerPageList["100"]["selected"] = true; break; default: $itemsPerPage = 50; $itemsPerPageList["50"]["selected"] = true; } } else { $itemsPerPage = 50; $itemsPerPageList["50"]["selected"] = true; } $endRecord = $itemsPerPage; $interface->assign('itemsPerPageList', $itemsPerPageList); if (isset($_GET['page'])) { $currentPage = $_GET['page']; // 1st record is easy, work out the start of this page $startRecord = ($currentPage - 1) * $itemsPerPage + 1; // Last record needs more care if ($resultTotal < $itemsPerPage) { // There are less records returned then one page, use total results $endRecord = $resultTotal; } else { if ($currentPage * $itemsPerPage > $resultTotal) { // The end of the current page runs past the last record, use total results $endRecord = $resultTotal; } else { // Otherwise use the last record on this page $endRecord = $currentPage * $itemsPerPage; } } } //////////Get the Page View Data with paging and sorting if (isset($_GET['reportSort'])) { $sortValue = $_GET['reportSort']; } //Create values for how to sort the table. $sortList = $this->getSortList(); if (!isset($sortValue)) { $sortValue = 'UrlASC'; } $sortList[$sortValue]["selected"] = true; $baseQueryLinks .= $sortList[$sortValue]['sql']; //append on a limit to return a result if (!isset($_REQUEST['exportToExcel'])) { $baseQueryLinks .= "LIMIT " . ($startRecord - 1) . ", " . $itemsPerPage . " "; } $resPurchases = mysql_query($baseQueryLinks); $resultsPurchases = array(); if ($resPurchases > 0) { //Build an array based on the data to dump out to the grid $i = 0; while ($r = mysql_fetch_array($resPurchases)) { $recordId = $r['recordId']; $fullId = $r['recordId']; if (preg_match('/econtentRecord(\\d+)/', $recordId, $matches)) { require_once ROOT_DIR . '/sys/eContent/EContentRecord.php'; $econtentRecord = new EContentRecord(); $econtentRecord->id = $matches[1]; $econtentRecord->find(true); $recordId = $econtentRecord->ilsId; $title = $econtentRecord->title; } else { $resource = new Resource(); $resource->record_id = $recordId; $resource->source = 'VuFind'; $resource->find(true); $title = $resource->title; } $tmp = array('recordId' => $recordId, 'recordUrl' => '/Record/' . $fullId, 'title' => $title, 'timesFollowed' => $r['timesFollowed'], 'linkHost' => $r['linkHost'], 'linkUrl' => $r['linkUrl']); $resultsPurchases[$i++] = $tmp; } } $interface->assign('resultLinks', $resultsPurchases); //////////Paging Array $summary = array('page' => $currentPage, 'perPage' => $itemsPerPage, 'resultTotal' => $totalResultCount, 'startRecord' => $startRecord, 'endRecord' => $endRecord); $interface->assign('recordCount', $summary['resultTotal']); $interface->assign('recordStart', $summary['startRecord']); $interface->assign('recordEnd', $summary['endRecord']); // Process Paging using VuFind Pager object if (strrpos($_SERVER["REQUEST_URI"], "page=")) { //replace the page variable with a new one $link = str_replace("page=" . $currentPage, "page=%d", $_SERVER["REQUEST_URI"]); } else { if (strrpos($_SERVER["REQUEST_URI"], "?")) { $link = $_SERVER["REQUEST_URI"] . "&page=%d"; } else { $link = $_SERVER["REQUEST_URI"] . "?page=%d"; } } $options = array('totalItems' => $summary['resultTotal'], 'fileName' => $link, 'perPage' => $summary['perPage']); $pager = new VuFindPager($options); $interface->assign('pageLinks', $pager->getLinks()); ///////////////////END PAGING //////////Sorting $sortUrl = $_SERVER["REQUEST_URI"]; if (isset($sortValue)) { //Set the URL for sorting if (strrpos($_SERVER["REQUEST_URI"], "reportSort=")) { //replace the page variable with a new one $sortUrl = str_replace("sort=" . $currentPage, "reportSort=" . $sortValue, $_SERVER["REQUEST_URI"]); } else { if (strrpos($_SERVER["REQUEST_URI"], "?")) { $sortUrl = $_SERVER["REQUEST_URI"] . "&reportSort=" . $sortValue; } else { $sortUrl = $_SERVER["REQUEST_URI"] . "?reportSort=" . $sortValue; } } } $interface->assign('sortUrl', $sortUrl); $interface->assign('sortList', $sortList); //////////CHART //Create the chart and load data into the results. $queryDailyPurchases = "SELECT DATE_FORMAT(trackingDate, '%Y-%m-%d') as date, COUNT(externalLinkId) AS timesFollowed, linkHost FROM external_link_tracking " . "WHERE (DATE_FORMAT(trackingDate, '%Y-%m-%d')) BETWEEN '" . $selectedDateStart . "' AND '" . $selectedDateEnd . "' "; if (count($selectedHosts) > 0) { $hosts = join("','", $selectedHosts); $queryDailyPurchases .= "AND linkHost IN ('" . $hosts . "') "; } $queryDailyPurchases .= "GROUP BY DATE_FORMAT(trackingDate, '%Y-%m-%d'), linkHost ORDER BY trackingDate ASC"; $dailyUsage = mysql_query($queryDailyPurchases); //Initialize data by loading all of the dates that we are looking at so we can show the correct counts or each series on the right day. $check_date = $selectedDateStart; $datesInReport = array(); $linkUsageByHostByDay = array(); foreach ($allHosts as $hostName) { $linkUsageByHostByDay[$hostName] = array(); } while ($check_date != $selectedDateEnd) { $check_date = date("Y-m-d", strtotime("+1 day", strtotime($check_date))); $datesInReport[] = $check_date; //Default number of link usage for the day to 0 foreach ($allHosts as $host) { $linkUsageByHostByDay[$host][$check_date] = 0; } } //Chart section $reportData = new pData(); while ($r = mysql_fetch_array($dailyUsage)) { $linkHost = $r['linkHost']; $linkUsageByHostByDay[$linkHost][$r['date']] = $r['timesFollowed']; } foreach ($linkUsageByHostByDay as $hostName => $dailyResults) { $reportData->addPoints($dailyResults, $hostName); } $reportData->setAxisName(0, "Usage"); $reportData->addPoints($datesInReport, "Dates"); $reportData->setAbscissa("Dates"); /* Create the pChart object */ $myPicture = new pImage(700, 290, $reportData); /* Draw the background */ $Settings = array("R" => 225, "G" => 225, "B" => 225); $myPicture->drawFilledRectangle(0, 0, 700, 290, $Settings); /* Add a border to the picture */ $myPicture->drawRectangle(0, 0, 699, 289, array("R" => 0, "G" => 0, "B" => 0)); $myPicture->setFontProperties(array("FontName" => "sys/pChart/Fonts/verdana.ttf", "FontSize" => 9)); $myPicture->setGraphArea(50, 30, 670, 190); //$myPicture->drawFilledRectangle(30,30,670,150,array("R"=>255,"G"=>255,"B"=>255,"Surrounding"=>-200,"Alpha"=>10)); $myPicture->drawScale(array("DrawSubTicks" => TRUE, "LabelRotation" => 90)); $myPicture->setFontProperties(array("FontName" => "sys/pChart/Fonts/verdana.ttf", "FontSize" => 9)); $myPicture->drawLineChart(array("DisplayValues" => TRUE, "DisplayColor" => DISPLAY_AUTO)); /* Write the chart legend */ $myPicture->drawLegend(80, 20, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL)); /* Render the picture (choose the best way) */ $time = time(); $chartHref = "/images/charts/dailyPurchases{$time}.png"; $chartPath = $configArray['Site']['local'] . $chartHref; $myPicture->render($chartPath); $interface->assign('chartPath', $chartHref); //////////EXPORT To EXCEL if (isset($_REQUEST['exportToExcel'])) { //PHPEXCEL // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setTitle("External Link Usage Report")->setCategory("External Link Usage Report"); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'External Link Usage Report')->setCellValue('A3', 'Record Id')->setCellValue('B3', 'Url')->setCellValue('C3', 'Host')->setCellValue('D3', 'Usage'); $a = 4; //Loop Through The Report Data foreach ($resultsPurchases as $resultsPurchase) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $resultsPurchase['recordId'])->setCellValue('B' . $a, $resultsPurchase['linkUrl'])->setCellValue('C' . $a, $resultsPurchase['linkHost'])->setCellValue('D' . $a, $resultsPurchase['timesFollowed']); $a++; } $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client's web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="ExternalLinkReport.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } $interface->setPageTitle('Report - External Link Tracking'); $interface->setTemplate('reportExternalLinks.tpl'); $interface->display('layout.tpl'); }