<?PHP
/*****************************************************************
*===================Monthly Signup Report========================*
*                                                                *
* This report will take all your signups, group them by month    *
* and report those results. Included is the monthly %            *
* Author: Shane Sammons                                          *
* Site: http://www.designalpha.com                               *
* Created: December 2003                                         *
* For use by: ClientExec 1.1.1 liceansed users                   *
******************************************************************
*/

// don't show if not logged in
if (!isset($_SESSION['userId'])) die('Log in first');

//Class Initialize
 $report = new Report();
  $report->SetDescription(lang('Displays a monthly breakdown of signups and their percentage impact on the overall total.'));

//Select the total users for use later
$sql = "SELECT COUNT(*)as total FROM users WHERE groupid = 1";
$result = $GLOBALS['db']->query($sql);
    while($row = $result->fetch()){
        $totalcust = $row['total'];
    }
//Now we query for a client based grouping
$sql = "SELECT 
DATE_FORMAT( dateActivated, '%M,%Y' ) AS date,
COUNT(*) AS monthlyusers,
CONCAT(FORMAT((COUNT(*)/$totalcust)*100,0),'%')
FROM users
WHERE groupid = 1
GROUP BY DATE_FORMAT( dateActivated, '%M,%Y' )
ORDER BY DATE_FORMAT( dateActivated, '%Y' ) ASC, DATE_FORMAT(dateActivated, '%m') ASC";
$result = $GLOBALS['db']->query($sql);
$prev_month = false;
while (list($myMonth,$myCustomers,$myPercent) = $result->fetch()){
         if ($prev_month == false) $diff = 'n/a';
         else if ($prev_month > $myCustomers) $diff = "<font color='red'>".($prev_month - $myCustomers)."</font>";
         else if ($prev_month < $myCustomers) $diff = "<font color='green'>".($myCustomers - $prev_month)."</font>";
         else $diff = 0;
         $aGroup[] = array($myMonth,$myCustomers,$diff,$myPercent);
         $prev_month = $myCustomers;
  }

  //add final group

  if (isset($aGroup)){
      $report->Add($aGroup,"",array(lang('Month'),lang('Signups'),lang('Difference'),lang('Total Percentage')));
      unset($aGroup);
  }

  $aGroup[] = array(lang('Year-to-date'),"<b>" . $totalcust . "</b>","<b>100%</b>");
  $report->Add($aGroup,lang('Totals'),array("","",""));

  //Display Report
  $report->Show();


?>
