commerce_reports_geckoboard.module in Commerce Reporting 7.4

File

modules/geckoboard/commerce_reports_geckoboard.module

<?php

/**
 * Implements hook_geckoboard().
 *
 * I'm starting to think most of the text widgets should be created through the
 * UI using tokens, rather than defined here.
 */
function commerce_reports_geckoboard_geckoboardapi() {
  $services = array();
  $services['commerce_sales_daily'] = array(
    'callback' => 'commerce_reports_geckoboard_sales_daily',
    'type' => 'text',
  );
  $services['commerce_sales_weekly'] = array(
    'callback' => 'commerce_reports_geckoboard_sales_weekly',
    'type' => 'text',
  );
  $services['commerce_sales_monthly'] = array(
    'callback' => 'commerce_reports_geckoboard_sales_monthly',
    'type' => 'text',
  );
  $services['commerce_sales_yearly'] = array(
    'callback' => 'commerce_reports_geckoboard_sales_yearly',
    'type' => 'text',
  );
  $services['commerce_revenue_monthly'] = array(
    'callback' => 'commerce_reports_geckoboard_revenue_monthly',
    'type' => 'text',
  );
  $services['commerce_order_processing_rag'] = array(
    'callback' => 'commerce_reports_geckoboard_order_processing_rag',
    'type' => 'rag',
  );
  $services['commerce_order_status_rag'] = array(
    'callback' => 'commerce_reports_geckoboard_order_status_rag',
    'type' => 'rag',
  );
  return $services;
}

/**
 * Geckoboard hook: Daily sales
 *
 * @todo Just use GROUP BY to get daily/weekly/yearly sales. Not sure on pgsql.
 */
function commerce_reports_geckoboard_sales_daily() {
  $basequery = db_select('commerce_line_item', 'li')
    ->fields('li', array(
    'line_item_id',
  ))
    ->condition('li.type', 'product')
    ->condition('o.status', array(
    'pending',
    'processing',
    'completed',
  ));
  $basequery
    ->addExpression('SUM(quantity)', 'quantity');
  $basequery
    ->join('commerce_order', 'o', 'o.order_id = li.order_id');

  // Today
  $today = mktime(0, 0, 0, date('n'), date('j'));
  $query = clone $basequery;
  $query
    ->condition('o.created', $today, '>=');
  $result = $query
    ->execute()
    ->fetchField(1);
  $todayunits = (int) $result;

  // Yesterday
  $yesterday = $today - 86400;
  $query = clone $basequery;
  $query
    ->condition('o.created', $yesterday, '>=')
    ->condition('o.created', $today, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $yesterdayunits = (int) $result;
  $yesterday_text = t('@units yesterday', array(
    '@units' => $yesterdayunits,
  ));
  return _commerce_reports_geckoboard_texthelper($todayunits, $yesterday_text);
}

/**
 * Geckoboard hook: Weekly sales
 */
function commerce_reports_geckoboard_sales_weekly() {
  $basequery = db_select('commerce_line_item', 'li')
    ->fields('li', array(
    'line_item_id',
  ))
    ->condition('li.type', 'product')
    ->condition('o.status', array(
    'pending',
    'processing',
    'completed',
  ));
  $basequery
    ->addExpression('SUM(quantity)', 'quantity');
  $basequery
    ->join('commerce_order', 'o', 'o.order_id = li.order_id');

  // This week
  $weekstart = strtotime(date("o-\\WW"));
  $weekend = $weekstart + 604800;
  $query = clone $basequery;
  $query
    ->condition('o.created', $weekstart, '>=')
    ->condition('o.created', $weekend, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $thisweek = (int) $result;

  // Last week
  $lastweekend = strtotime(date("o-\\WW"));
  $lastweekstart = $lastweekend - 604800;
  $query = clone $basequery;
  $query
    ->condition('o.created', $lastweekstart, '>=')
    ->condition('o.created', $lastweekend, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $lastweek = (int) $result;
  $lastweek_text = t('@units last week', array(
    '@units' => $lastweek,
  ));
  return _commerce_reports_geckoboard_texthelper($thisweek, $lastweek_text);
}

/**
 * Geckoboard hook: Monthly sales
 */
function commerce_reports_geckoboard_sales_monthly() {
  $basequery = db_select('commerce_line_item', 'li')
    ->fields('li', array(
    'line_item_id',
  ))
    ->condition('li.type', 'product')
    ->condition('o.status', array(
    'pending',
    'processing',
    'completed',
  ));
  $basequery
    ->addExpression('SUM(quantity)', 'quantity');
  $basequery
    ->join('commerce_order', 'o', 'o.order_id = li.order_id');

  // This month
  $thismonth = mktime(0, 0, 0, date('n'), 1);
  $thismonthend = mktime(0, 0, 0, date('n') + 1, 1);
  $query = clone $basequery;
  $query
    ->condition('o.created', $thismonth, '>=')
    ->condition('o.created', $thismonthend, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $thismonth = (int) $result;

  // Last month
  $lastmonth = mktime(0, 0, 0, date('n') - 1, 1);
  $lastmonthend = mktime(0, 0, 0, date('n'), 1);
  $query = clone $basequery;
  $query
    ->condition('o.created', $lastmonth, '>=')
    ->condition('o.created', $lastmonthend, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $lastmonth = (int) $result;
  $lastmonth_text = t('@units last month', array(
    '@units' => $lastmonth,
  ));
  return _commerce_reports_geckoboard_texthelper($thismonth, $lastmonth_text);
}

/**
 * Geckoboard hook: Yearly sales
 */
function commerce_reports_geckoboard_sales_yearly() {
  $basequery = db_select('commerce_line_item', 'li')
    ->fields('li', array(
    'line_item_id',
  ))
    ->condition('li.type', 'product')
    ->condition('o.status', array(
    'pending',
    'processing',
    'completed',
  ));
  $basequery
    ->addExpression('SUM(quantity)', 'quantity');
  $basequery
    ->join('commerce_order', 'o', 'o.order_id = li.order_id');

  // This year
  $thisyear = mktime(0, 0, 0, 1, 1, date('Y'));
  $thisyearend = mktime(0, 0, 0, 1, 1, date('Y') + 1);
  $query = clone $basequery;
  $query
    ->condition('o.created', $thisyear, '>=')
    ->condition('o.created', $thisyearend, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $thisyear = (int) $result;

  // Last year
  $lastyear = mktime(0, 0, 0, 1, 1, date('Y') - 1);
  $lastyearend = mktime(0, 0, 0, 1, 1, date('Y'));
  $query = clone $basequery;
  $query
    ->condition('o.created', $lastyear, '>=')
    ->condition('o.created', $lastyearend, '<');
  $result = $query
    ->execute()
    ->fetchField(1);
  $lastyear = (int) $result;
  $lastyear_text = t('@units last year', array(
    '@units' => $lastyear,
  ));
  return _commerce_reports_geckoboard_texthelper($thisyear, $lastyear_text);
}

/**
 * Sales summary
 */
function commerce_reports_geckoboard_revenue_monthly() {
  $res = db_query("\n    SELECT SUM(amount) AS sum,\n           AVG(amount) AS average,\n           COUNT(*) AS count,\n           DATE_FORMAT(FROM_UNIXTIME(created), '%Y-%m') AS yyyymm\n      FROM commerce_payment_transaction cpt\n     WHERE currency_code='AUD'\n       AND status='success'\n       AND created > :created\n     GROUP BY yyyymm", array(
    ':created' => REQUEST_TIME - 3600 * 24 * 28 * 2,
  ));
  $thismonth = date('Y-m');
  $lastmonth = date('Y-m', REQUEST_TIME - 86400 * 28);
  $data = $res
    ->fetchAllAssoc('yyyymm');

  // Check for slow selling stores
  if (!isset($data[$thismonth])) {
    $data[$thismonth] = (object) array(
      'sum' => 0,
      'average' => 0,
      'count' => 0,
    );
  }
  if (!isset($data[$lastmonth])) {
    $data[$lastmonth] = (object) array(
      'sum' => 0,
      'average' => 0,
      'count' => 0,
    );
  }

  // Quick and dirty projection
  $hoursthismonth = (time() - mktime(0, 0, 0, date('n'), 1)) / 60 / 60;

  // Number of hours that have passed this month
  $hoursremaining = (mktime(0, 0, 0, date('n') + 1, 1) - time()) / 60 / 60;
  $projected_raw = $data[$thismonth]->sum + $data[$thismonth]->sum / $hoursthismonth * $hoursremaining;

  // Change our units from $.01 to $1000
  $current = (int) ($data[$thismonth]->sum / 100000);
  $previous = (int) ($data[$lastmonth]->sum / 100000);
  $projected = (int) ($projected_raw / 100000);
  $upper_bound = (int) (max($projected, $previous) * 1.2);

  // Set our goals
  // 30% decline on last month
  $red = $previous * 0.7;

  // 10% growth on last month
  $amber = $previous * 0.9;

  // Generate evenly spaced points
  $ticks = range(0, $upper_bound, $upper_bound / 6);
  $ticks = array_map('intval', $ticks);

  // Generate marks corresponding to our sales data so we don't have to guess the numbers
  // $ticks = array(0, $current, $previous, $projected, $upper_bound);
  // sort($ticks);
  return array(
    'orientation' => 'horizontal',
    'item' => array(
      'label' => t('Month to date revenue'),
      'sublabel' => t('(@currency in thousands)', array(
        '@currency' => commerce_default_currency(),
      )),
      'axis' => array(
        'point' => $ticks,
      ),
      'range' => array(
        array(
          'color' => 'red',
          'start' => 0,
          'end' => $red,
        ),
        // Should use last month to define range always
        array(
          'color' => 'amber',
          'start' => $red,
          'end' => $amber,
        ),
        array(
          'color' => 'green',
          'start' => $amber,
          'end' => $upper_bound,
        ),
      ),
      'measure' => array(
        'current' => array(
          'start' => 0,
          'end' => $current,
        ),
        'projected' => array(
          'start' => 0,
          'end' => $projected,
        ),
      ),
      'comparative' => array(
        'point' => $previous,
      ),
    ),
  );
}

/**
 * Processing order breakdown
 */
function commerce_reports_geckoboard_order_processing_rag() {
  $periods = array(
    REQUEST_TIME - 86400 => REQUEST_TIME,
    REQUEST_TIME - 86400 * 2 => REQUEST_TIME - 86400,
    0 => REQUEST_TIME - 86400 * 2,
  );
  $stats = array();
  foreach ($periods as $start => $end) {
    $result = db_select('commerce_order', 'o')
      ->condition('o.status', 'processing')
      ->condition('o.created', $start, '>')
      ->condition('o.created', $end, '<=')
      ->countQuery()
      ->execute()
      ->fetchField();
    $stats[] = (int) $result;
  }
  return array(
    'item' => array(
      array(
        'value' => $stats[2],
        'text' => t('processing orders older than 48h'),
      ),
      array(
        'value' => $stats[1],
        'text' => t('processing orders older than 24h'),
      ),
      array(
        'value' => $stats[0],
        'text' => t('processing orders within the last 24h'),
      ),
    ),
  );
}

/**
 * Order status breakdown
 */
function commerce_reports_geckoboard_order_status_rag() {
  $statuses = array(
    'review',
    'pending',
    'processing',
  );
  $stats = array();
  foreach ($statuses as $status) {
    $result = db_select('commerce_order', 'o')
      ->condition('o.status', $status)
      ->countQuery()
      ->execute()
      ->fetchField();
    $stats[$status] = (int) $result;
  }
  return array(
    'item' => array(
      array(
        'value' => $stats['review'],
        'text' => t('orders requiring review'),
      ),
      array(
        'value' => $stats['pending'],
        'text' => t('pending orders'),
      ),
      array(
        'value' => $stats['processing'],
        'text' => t('processing orders'),
      ),
    ),
  );
}

/**
 * Helper: Simple text widget with comparison
 */
function _commerce_reports_geckoboard_texthelper($primary, $secondary) {
  $markup = sprintf('<div class="t-size-x60">%s</div><div class="t-secondary">%s</div>', $primary, $secondary);
  return array(
    'item' => array(
      array(
        "text" => $markup,
        "type" => 0,
      ),
    ),
  );
}

Functions

Name	Description
commerce_reports_geckoboard_geckoboardapi	Implements hook_geckoboard().
commerce_reports_geckoboard_order_processing_rag	Processing order breakdown
commerce_reports_geckoboard_order_status_rag	Order status breakdown
commerce_reports_geckoboard_revenue_monthly	Sales summary
commerce_reports_geckoboard_sales_daily	Geckoboard hook: Daily sales
commerce_reports_geckoboard_sales_monthly	Geckoboard hook: Monthly sales
commerce_reports_geckoboard_sales_weekly	Geckoboard hook: Weekly sales
commerce_reports_geckoboard_sales_yearly	Geckoboard hook: Yearly sales
_commerce_reports_geckoboard_texthelper	Helper: Simple text widget with comparison