<?php
/*
Plugin Name: Report Builder
Description: Create and display custom reports with MySQL which users can view directly or export as CSV.
Version: 1.02
Requires at least: 3.00
*/

// UPDATE THESE VALUES

$GLOBALS['RBR_BUILDER_MENU_NAME']   = 'Report Builder'; // displayed on menu

// DON'T UPDATE ANYTHING BELOW THIS LINE

$GLOBALS['RBR_PLUGIN_NAME']        = 'reportBuilder';
$GLOBALS['RBR_GROUP_TABLENAME']    = '_reports_group';
$GLOBALS['RBR_BUILDER_TABLENAME']  = '_reports_list';
$GLOBALS['RBR_MAX_ROWS_DISPLAYED'] = 1000; // no more than this number of rows will be displayed for web queries


addAction('admin_postlogin',   '_reportBuilder_createSchema', null, 0);
addAction('admin_postlogin',   '_reportBuilder_dispatch',     null, 0);
addFilter('menulinks_rowHtml', '_reportBuilder_addMenuLinks', null, 2);
addAction('admin_head',        '_reportBuilder_editPageCSS',  null, 0);


//
function _reportBuilder_dispatch() {
  global $CURRENT_USER;
  if (@$_REQUEST['menu']        != $GLOBALS['RBR_BUILDER_TABLENAME'] &&
      @$_REQUEST['_pluginName'] != $GLOBALS['RBR_PLUGIN_NAME']) { return; } // only run for this plugin

  // show plugin menu as highlighted
  $GLOBALS['APP']['selectedMenu'] = $GLOBALS['RBR_GROUP_TABLENAME'];

  // security: check user has at least viewer access to report header
  $hasViewerAccess  = (userSectionAccess($GLOBALS['RBR_GROUP_TABLENAME']) >= 3);
  if (!$hasViewerAccess) { return; }

  // display reports
  if (@$_REQUEST['_pluginName'] === $GLOBALS['RBR_PLUGIN_NAME'] && @$_REQUEST['_report']) {
    _reportBuilder_displayReport( $_REQUEST['_report'] );
  }

  // report builder - only allow admins and show menu (since menu is hidden my default)
  if (@$_REQUEST['menu'] === $GLOBALS['RBR_BUILDER_TABLENAME']) {

    if (!$CURRENT_USER['isAdmin']) { die("Administrator access only!"); }
  }
}


//
function _reportBuilder_addMenuLinks($menuHtml, $row) {
  global $TABLE_PREFIX, $CURRENT_USER;
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();

  // only add links after reportBuilder Menu Header
  if (@$row['tableName'] != $GLOBALS['RBR_GROUP_TABLENAME']) { return $menuHtml; }

  // load reports
  $query   = "SELECT * FROM {$TABLE_PREFIX}{$GLOBALS['RBR_BUILDER_TABLENAME']} ORDER BY dragSortOrder";
  $reports = mysql_select_query($query);

  // show report links on menu
  foreach ($reports as $report) {
    // do not show hidden reports
    if ($report['hidden']) { continue; }
    
    $isSelected = (@$_REQUEST['_pluginName'] === $GLOBALS['RBR_PLUGIN_NAME'] && @$_REQUEST['_report'] === $report['num']);
    $menuClass  = $isSelected ? 'current' : '';
    $menuLink   = "?_pluginName={$GLOBALS['RBR_PLUGIN_NAME']}&amp;_report={$report['num']}";
    $menuName   = htmlencode( $report['name'] );

    $menuHtml  .= "<li><a class='$menuClass' href='$menuLink'>$menuName</a></li>\n";
  }


  // show report builder link on menu
  if ($CURRENT_USER['isAdmin']) {
    $isSelected = (@$_REQUEST['menu'] === $GLOBALS['RBR_BUILDER_TABLENAME']);
    $menuClass  = $isSelected ? 'current' : '';
    $menuLink   = "?menu={$GLOBALS['RBR_BUILDER_TABLENAME']}";
    $menuName   = htmlencode( $GLOBALS['RBR_BUILDER_MENU_NAME'] );

    $menuHtml  .= "<li><a class='$menuClass' href='$menuLink'>$menuName</a></li>\n";
  }

  //
  return $menuHtml;
}

//
function _reportBuilder_editPageCSS() {
  if (@$_REQUEST['menu'] != $GLOBALS['RBR_BUILDER_TABLENAME']) { return; } // only show for report builder menu
  if (!in_array(@$_REQUEST['action'], array('add','edit'))) { return; } // only show on add or edit page

  print <<<__HTML__
  <style type="text/css">
    textarea { font-family: Courier New, monospace !important; color:red }
  </style>

  <script type="text/javascript">
    $(document).ready(function() {
      $('textarea').attr('spellcheck', 'false');
      $('textarea').attr('wrap',       'off');
    });
  </script>
__HTML__;

}


//
function _reportBuilder_displayReport($num) {
  global $TABLE_PREFIX;

  // load report
  $query  = mysql_escapef("SELECT * FROM {$TABLE_PREFIX}{$GLOBALS['RBR_BUILDER_TABLENAME']} WHERE num = ?", $num);
  $report = mysql_query_fetch_row_assoc($query);
  if (!$report) { die("Could not find report!"); }
  
  
  // prepare adminUI() placeholders
  $adminUI = [];
  // page title
  $adminUI['PAGE_TITLE'] = [
    t(@$report['name']),
  ];
  
  // button
  $adminUI['BUTTONS'] = [
    [ 'name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;',  'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button' ],
  ];
  
  // content
  $adminUI['CONTENT'] = ob_capture(function() use($report) {
    
    // show 'description' as notice
    notice(getEvalOutput($report['description']));
    
    // parse form input
    #$yearRangeStart = 2000;
    #$yearRangeEnd   = date('Y');
    #foreach (array('startDate', 'endDate') as $fieldname) {
    #  if (@$_REQUEST["$fieldname:year"]) {
    #    $_REQUEST[$fieldname] = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $_REQUEST["$fieldname:year"], $_REQUEST["$fieldname:mon"], $_REQUEST["$fieldname:day"], _getHour24ValueFromDateInput($fieldname), (int) @$_REQUEST["$fieldname:min"], (int) @$_REQUEST["$fieldname:sec"]);
    #  }
    #}
    #$suppliedStartDate = @$_REQUEST['startDate'];
    #$suppliedEndDate   = @$_REQUEST['endDate'];
    #if (!@$_REQUEST['startDate']) { $_REQUEST['startDate'] = "0000-00-00 00:00:00"; }
    #if (!@$_REQUEST['endDate'  ]) { $_REQUEST['endDate'  ] = "9999-12-31 23:59:59"; }
  
    // get query result
    $sql          = getEvalOutput($report['mysql']);
    $result       = @mysql_query($sql);
    $result_error = mysql_error();
  
    // output as CSV?
    if (@$_REQUEST['_asCSV'] && $result) {
      _reportBuilder_exportCsv($result, $report['name']);
    }
  
    ?>
    
    <form action="?" method="get">
      <input type="hidden" name="_pluginName" value="<?php echo htmlencode($_REQUEST['_pluginName']) ?>" />
      <input type="hidden" name="_report" value="<?php echo htmlencode($_REQUEST['_report']) ?>" />

    <?php /*
    <?php if ($report['dateRange']): ?>
      <?php require_once "lib/menus/default/edit_functions.php" ?>
      <?php $dateFieldDefaults = array(
        'yearRangeStart' => $yearRangeStart,
        'yearRangeEnd'   => $yearRangeEnd,
        'showTime'       => false,
        'defaultDate'    => 'none',
      ) ?>
      <table>
        <?php _showDateTime(array_merge($dateFieldDefaults, array('name' => 'startDate', 'label' => 'Start')), array('startDate' => $suppliedStartDate)) ?>
        <?php _showDateTime(array_merge($dateFieldDefaults, array('name' => 'endDate'  , 'label' => 'End'  )), array('endDate'   => $suppliedEndDate)) ?>
        <tr><td><input type="submit" class='btn btn-default' name="search" value="Search"></td></tr>
      </table>
      <br/>
    <?php endif ?>
    */ ?>
  
    <?php if ($result_error): ?>
      <p><strong>MySQL Error:</strong> <?php echo htmlencode($result_error) ?></p>
    <?php elseif (!mysql_num_rows($result)): ?>
      <p><strong>No rows returned.</strong></p>
    <?php else: ?>
  
      <div style="overflow: auto; max-height: 600px;">
        <table cellspacing="1" cellpadding="0" border="0" class="data table table-striped table-hover">
  
          <?php $rowCount = 0; ?>
          <?php while ($row = mysql_fetch_assoc($result)): ?>
            <?php if (++$rowCount > $GLOBALS['RBR_MAX_ROWS_DISPLAYED']) { break; } ?>
  
          <?php if (!@$shownHeaders++): ?>
            <thead>
              <tr class='listHeader'>
                <td>#</td>
                <?php foreach (array_keys($row) as $fieldName): ?>
                  <th><b><?php echo htmlencode($fieldName) ?></b></th>
                <?php endforeach ?>
              </tr>
            </thead>
          <?php endif ?>
  
          <?php $bgColorClass = (@$bgColorClass == "listRowOdd") ? 'listRowEven' : 'listRowOdd'; ?>
          <tr class="<?php echo $bgColorClass ?>">
            <td><?php echo $rowCount; ?></td>
            <?php foreach ($row as $fieldValue): ?>
              <td><?php echo htmlencode($fieldValue); ?></td>
            <?php endforeach ?>
          </tr>
  
          <?php endwhile ?>
        </table>
  
        <?php if ($rowCount > $GLOBALS['RBR_MAX_ROWS_DISPLAYED']): ?>
          <p><strong>Row limit reached!  Only the first <?php echo $GLOBALS['RBR_MAX_ROWS_DISPLAYED'] ?> rows have been displayed.  Export as CSV to get all rows.</strong></p>
        <?php endif ?>
  
      </div>
      <br/>
  
      <?php echo adminUI_button(['type' => 'submit', 'name' => '_asCSV', 'value' => '1', 'btn-type' => 'default', 'label' => t('Export as CSV')]); ?>
    <?php endif ?>

    </form>
    <?php if (is_resource($result)) { mysql_free_result($result); } ?>

  <?php });
  // show content
  adminUI($adminUI);
  exit;
}


//
function _reportBuilder_createSchema() {
  $tables = array($GLOBALS['RBR_GROUP_TABLENAME'], $GLOBALS['RBR_BUILDER_TABLENAME'], '_reports_sample_orders');

  foreach ($tables as $tableName) {
    // copy default data to schema dir
    $dataSourcePath = dirname(__FILE__) . "/$tableName.defaultSqlData.php";
    $dataTargetPath = realpath(DATA_DIR . '/schema') . '/' . basename($dataSourcePath);
    if (file_exists($dataSourcePath) && !file_exists($dataTargetPath)) {
      @copy($dataSourcePath, $dataTargetPath);
    }

    // create schema (if not already existing)
    $schemaFile = "$tableName.ini.php";
    $schemaPath = realpath(DATA_DIR . '/schema') . "/$schemaFile";
    if (!file_exists($schemaPath)) {
      $sourcePath = dirname(__FILE__) . "/$tableName.ini.php";
      createSchemaFromFile( $sourcePath );
    }
  }

}


//
function _reportBuilder_exportCsv($result, $reportName) {

  // send header
  $debugMode = false;
  if ($debugMode) {
    header('Content-type: text/plain; charset=utf-8'); # Uncomment for debugging
  }
  else {
    $filename  = "$reportName-" . date('Ymd-His') . '.csv';
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Transfer-Encoding: binary");
  }

  // send rows
  while ($row = mysql_fetch_assoc($result)) {
    //if ($GLOBALS['CSVEXPORT_FOR_EXCEL']) { mb_convert_variables('CP1252', 'UTF-8', $record); }

    // send column headers
    if (!@$shownHeaders++) {
      $line = '';
      foreach (array_keys($row) as $field) {
        $line .= _reportBuilder_escapeAsCSV($field) . ',';
      }
      $line = chop($line, ',') . "\n";
      print $line;
    }

    // send row
    $line = '';
    foreach ($row as $value) {
      $line .= _reportBuilder_escapeAsCSV($value) . ',';
    }
    $line = chop($line, ',') . "\n";
    print $line;
  }

  if (is_resource($result)) { mysql_free_result($result); }
  exit;
}


//
function _reportBuilder_escapeAsCSV($value) {
  $value = str_replace('<br/>', '', $value);
  $value = str_replace('"', '""', $value);
  $value = '"' . $value . '"';

  //if ($GLOBALS['CSVEXPORT_FOR_EXCEL'] && preg_match('/^"0\d+"$/s', $value)) {
  //  $value = '='.$value;
  //}

  return $value;
}



// internal function used to popular sample orders table
//__reports_populateSampleOrdersTable(); // uncomment this line to run
function __reports_populateSampleOrdersTable() {
  global $TABLE_PREFIX;
  $products   = array('Apparatus Alpha', 'Apparatus Beta', 'Contraption A', 'Contraption B', 'Gizmo 1', 'Gizmo 2', 'Premium Widget', 'Super Widget', 'Value Widget');
  $maxRecords = 250;

  //
  foreach (range(1, $maxRecords) as $num) {
    // get random product info
    $product    = $products[ mt_rand(0, count($products)-1) ];
    $quantity   = mt_rand(1, 49);
    $unitPrice  = mt_rand(49*100, 250*100) / 100;
    $totalPrice = $quantity * $unitPrice;

    // get random order date
    $timeString = '';
    foreach (array('month','day','hour','minute','second') as $timeUnit) {
      $plusMinus   = mt_rand(0,1) ? '-' : '+';
      if ($timeUnit == 'month') { $plusMinus = '-'; }
      $amount      = mt_rand(0,12);
      $timeString .= "$plusMinus$amount $timeUnit ";
    }
    $orderDate  = date('Y-m-d H:i:s', strtotime($timeString));

    // add product to database
    //print "$orderDate - $timeString - $product - $quantity - $unitPrice - $totalPrice<br/>\n";
    $insert = "INSERT INTO `{$TABLE_PREFIX}_reports_sample_orders` SET
                      orderDate  = '".mysql_escape( $orderDate )."',
                      name       = '".mysql_escape( $product )."',
                      quantity   = '".mysql_escape( $quantity )."',
                      unitPrice  = '".mysql_escape( $unitPrice )."',
                      totalPrice = '".mysql_escape( $totalPrice )."'";
    mysql_query($insert) or die("MySQL Error Creating Record:<br/>\n". htmlencode(mysql_error()) . "\n");
  }

}

?>
