fannie/reports/DBA/index.php
<?php
//header('Location: ../../modules/plugins2.0/DBA/DBAReport.php');
//exit;
include(dirname(__FILE__) . '/../../config.php');
include_once(__DIR__ . '/../../classlib2.0/FannieAPI.php');
$dbc = FannieDB::get($FANNIE_OP_DB);
if (isset($_REQUEST['loadID'])){
$q = $dbc->prepare("SELECT reportName,reportQuery FROM
customReports WHERE reportID=?");
$r = $dbc->execute($q,array($_REQUEST['loadID']));
$w = $dbc->fetch_row($r);
echo $w['reportName'];
echo '`';
echo base64_decode($w['reportQuery']);
return;
}
$errors = "";
$query = "";
if (isset($_REQUEST['query'])){
$query = $_REQUEST['query'];
if (stristr($query,"drop"))
$errors .= "Illegal term <b>drop</b><br />";
if (stristr($query,"truncate"))
$errors .= "Illegal term <b>truncate</b><br />";
if (stristr($query,"delete") && stristr($query, 'deleted') == false)
$errors .= "Illegal term <b>delete</b><br />";
if (stristr($query,"update") && stristr($query,"prodUpdate") == false && stristr($query,"batchUpdate") == false)
$errors .= "Illegal term <b>update</b><br />";
if (stristr($query,"alter"))
$errors .= "Illegal term <b>alter</b><br />";
}
if ($errors == "" && $query != ""){
$dlog = "";
$dtrans = "";
if (!empty($_REQUEST['date1']) && !empty($_REQUEST['date2'])){
$dlog = DTransactionsModel::selectDlog($_REQUEST['date1'],$_REQUEST['date2']);
$dtrans = DTransactionsModel::selectDtrans($_REQUEST['date1'],$_REQUEST['date2']);
}
elseif (!empty($_REQUEST['date1'])){
$dlog = DTransactionsModel::selectDlog($_REQUEST['date1']);
$dtrans = DTransactionsModel::selectDtrans($_REQUEST['date1']);
}
if (!empty($dlog))
$query = str_ireplace(" dlog "," ".$dlog." ",$query);
if (!empty($dtrans))
$query = str_ireplace(" dtransactions "," ".$dtrans." ",$query);
$prep = $dbc->prepare($query);
$result = $dbc->execute($query);
if (!$result){
echo "<i>Error occured</i>: ".$dbc->error();
echo "<hr />";
echo "<i>Your query</i>: ".$query;
}
else if ($dbc->num_rows($result) == 0){
echo "<i>Query returned zero results</i><hr />";
echo "<i>Your query</i>: ".$query;
}
else {
if (isset($_REQUEST['excel'])){
$ext = \COREPOS\Fannie\API\data\DataConvert::excelFileExtension();
header('Content-Type: application/ms-excel');
header('Content-Disposition: attachment; filename="resultset.' . $ext . '"');
ob_start();
}
echo '<table cellspacing="0" cellpadding="4" border="1">';
echo '<tr>';
$num = $dbc->numFields($result);
for($i=0;$i<$num;$i++){
echo '<th>'.$dbc->fieldName($result,$i)."</th>";
}
echo '</tr>';
while($row = $dbc->fetch_row($result)){
echo '<tr>';
for($i=0;$i<$num;$i++)
echo '<td>'.$row[$i].'</td>';
echo '</tr>';
}
echo '</table>';
if (isset($_REQUEST['excel'])){
$output = ob_get_contents();
ob_end_clean();
$array = \COREPOS\Fannie\API\data\DataConvert::htmlToArray($output);
$xls = \COREPOS\Fannie\API\data\DataConvert::arrayToExcel($array);
echo $xls;
}
if (!empty($_REQUEST['repName'])){
$name = $_REQUEST['repName'];
$saveableQ = base64_encode($_REQUEST['query']);
$chkQ = $dbc->prepare("SELECT reportID FROM customReports WHERE reportName=?");
$chkR = $dbc->execute($chkQ,array($name));
if ($dbc->num_rows($chkR) == 0){
$idQ = $dbc->prepare("SELECT max(reportID) FROM customReports");
$idR = $dbc->execute($idQ);
$id = array_pop($dbc->fetch_row($idR));
$id = ($id=="")?1:$id+1;
$insQ = $dbc->prepare("INSERT INTO customReports (reportID,reportName,reportQuery)
VALUES (?,?,?)");
$insR = $dbc->execute($insQ,array($id,$name,$saveableQ));
}
else {
$id = array_pop($dbc->fetch_row($chkR));
$upQ = $dbc->prepare("UPDATE customReports SET reportQuery=? WHERE reportID=?");
$upR = $dbc->execute($upQ,array($saveableQ,$id));
}
}
}
}
else {
$header = "Reporting for DBAs";
$page_title = "Fannie :: Skip learning PHP/HTML";
include(__DIR__ . '/../../src/header.html');
if (!empty($errors))
echo "<blockquote>".$errors."</blockquote>";
$q = $dbc->prepare("SELECT reportID,reportName FROM customReports ORDER BY reportName");
$r = $dbc->execute($q);
$opts = "";
while($w = $dbc->fetch_row($r))
$opts .= sprintf('<option value="%d">%s</option>',$w['reportID'],$w['reportName']);
?>
<script type="text/javascript">
function loadSaved(id){
if (id==-1){
$('#repName').val('');
$('#query').val('');
}
else {
$.ajax({
url:'index.php',
type:'get',
cache: false,
data:'loadID='+id
}).done(function(data){
var tmp = data.split('`');
$('#repName').val(tmp[0]);
$('#query').val(tmp[1]);
});
}
}
</script>
<?php
echo ' <script type="text/javascript">
$(document).ready(function() {
$(\'#date1\').datepicker();
$(\'#date2\').datepicker();
});
</script>
<form action="index.php" method="post">
Saved reports: <select onchange="loadSaved(this.value);">
<option value="-1">Choose...</option>'.$opts.'</select>
<p />Save As <input type="text" name="repName" id="repName" />
<p />
<textarea name="query" id="query" rows="10" cols="40"></textarea>
<p />
Date range
<input type="text" name="date1" size="10" id="date1" />
<input type="text" name="date2" size="10" id="date2" />
<br /><input type="checkbox" name="excel" id="excel" />
<label for="excel">Download results</label>
<p />
<input type="submit" value="Run Report" />
</form>';
include(__DIR__ . '/../../src/footer.html');
}