<?php

require_once 'classes/jpgraph/src/jpgraph.php';
require_once 'classes/jpgraph/src/jpgraph_bar.php';
require_once 'classes/jpgraph/src/jpgraph_line.php';
require_once 'classes/user.php';

/**
* @package Reports
*/
class Monthly_Signup_Rate_And_Percentage extends Report
{
    var $m_hasgraph = true;
    var $friendlyName = 'New Customers';

    function process()
    {
        /*****************************************************************
        *===================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                   *
        * Last Updated: February 2007 by Newedge Services Inc.           *
        ******************************************************************
        */
        
        if (isset($_GET['viewimage']) && $_GET['viewimage'] == 1) {
            $lArrayCurrentMonth = array();
            $lArrayLastYearMonth = array();
            $lArrayFillColor = array();
            
            // Create the graph.
            $graph = new Graph(320,150,"auto");
            $graph->SetScale("textlin");
            $graph->SetFrame(false);
            
            //*****************************
            //Get count of users based on minus x from tomonth that have signed up
            //*****************************
            //Get current month stats and set vars
            $month_minus0_TS = mktime (0,0,0,date("m"),1,  date("Y"));
            $minus0_date = strftime("%Y-%m-%d",$month_minus0_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus0_date')) AND (YEAR(dateActivated) = YEAR('$minus0_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus0 = $this->user->lang(date("M",$month_minus0_TS));
            
            //Get current month-1 stats and set vars
            $month_minus1_TS = mktime (0,0,0,date("m")-1,1,  date("Y"));
            $minus1_date = strftime("%Y-%m-%d",$month_minus1_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus1_date')) AND (YEAR(dateActivated) = YEAR('$minus1_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus1 = $this->user->lang(date("M",$month_minus1_TS));
            
            //Get current month-2 stats and set vars
            $month_minus2_TS = mktime (0,0,0,date("m")-2,1,  date("Y"));
            $minus2_date = strftime("%Y-%m-%d",$month_minus2_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus2_date')) AND (YEAR(dateActivated) = YEAR('$minus2_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus2 = $this->user->lang(date("M",$month_minus2_TS));
            
            //Get current month-3 stats and set vars
            $month_minus3_TS = mktime (0,0,0,date("m")-3,1,  date("Y"));
            $minus3_date = strftime("%Y-%m-%d",$month_minus3_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus3_date')) AND (YEAR(dateActivated) = YEAR('$minus3_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus3 = $this->user->lang(date("M",$month_minus3_TS));
            
            //Get current month-4 stats and set vars
            $month_minus4_TS = mktime (0,0,0,date("m")-4,1,  date("Y"));
            $minus4_date = strftime("%Y-%m-%d",$month_minus4_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus4_date')) AND (YEAR(dateActivated) = YEAR('$minus4_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus4 = $this->user->lang(date("M",$month_minus4_TS));
            
            //Get current month-5 stats and set vars
            $month_minus5_TS = mktime (0,0,0,date("m")-5,1,  date("Y"));
            $minus5_date = strftime("%Y-%m-%d",$month_minus5_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus5_date')) AND (YEAR(dateActivated) = YEAR('$minus5_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus5 = $this->user->lang(date("M",$month_minus5_TS));
            
            //Get current month-6 stats and set vars
            $month_minus6_TS = mktime (0,0,0,date("m")-6,1,  date("Y"));
            $minus6_date = strftime("%Y-%m-%d",$month_minus6_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus6_date')) AND (YEAR(dateActivated) = YEAR('$minus6_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayCurrentMonth[] = $tArray[0];
            //set month name based on langugage file
            $month_minus6 = $this->user->lang(date("M",$month_minus6_TS));
            
            //***********************
            //LASTYEAR
            //***********************
            //Get current month stats and set vars
            $month_minus0_TS = mktime (0,0,0,date("m"),1,  date("Y")-1);
            $minus0_date = strftime("%Y-%m-%d",$month_minus0_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus0_date')) AND (YEAR(dateActivated) = YEAR('$minus0_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            
            //Get current month-1 stats and set vars
            $month_minus1_TS = mktime (0,0,0,date("m")-1,1,  date("Y")-1);
            $minus1_date = strftime("%Y-%m-%d",$month_minus1_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus1_date')) AND (YEAR(dateActivated) = YEAR('$minus1_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            
            //Get current month-2 stats and set vars
            $month_minus2_TS = mktime (0,0,0,date("m")-2,1,  date("Y")-1);
            $minus2_date = strftime("%Y-%m-%d",$month_minus2_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus2_date')) AND (YEAR(dateActivated) = YEAR('$minus2_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            
            //Get current month-3 stats and set vars
            $month_minus3_TS = mktime (0,0,0,date("m")-3,1,  date("Y")-1);
            $minus3_date = strftime("%Y-%m-%d",$month_minus3_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus3_date')) AND (YEAR(dateActivated) = YEAR('$minus3_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            
            //Get current month-4 stats and set vars
            $month_minus4_TS = mktime (0,0,0,date("m")-4,1,  date("Y")-1);
            $minus4_date = strftime("%Y-%m-%d",$month_minus4_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus4_date')) AND (YEAR(dateActivated) = YEAR('$minus4_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            
            //Get current month-5 stats and set vars
            $month_minus5_TS = mktime (0,0,0,date("m")-5,1,  date("Y")-1);
            $minus5_date = strftime("%Y-%m-%d",$month_minus5_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus5_date')) AND (YEAR(dateActivated) = YEAR('$minus5_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            
            //Get current month-6 stats and set vars
            $month_minus6_TS = mktime (0,0,0,date("m")-6,1,  date("Y")-1);
            $minus6_date = strftime("%Y-%m-%d",$month_minus6_TS); //used for the sql
            $query = "SELECT COUNT(id) FROM users WHERE groupid=".ROLE_CUSTOMER." AND (MONTH(dateActivated) = MONTH('$minus6_date')) AND (YEAR(dateActivated) = YEAR('$minus6_date'))";
            $result = $this->db->query($query);
            $tArray = $result->fetch();
            $lArrayLastYearMonth[] = $tArray[0];
            //*********************
            //END LAST YEAR
            //*********************
            
            $thisYear = mktime (0,0,0,date("m"),1,  date("Y"));
            $thisYear = strftime("%Y",$thisYear); //used for the legend
            
            $lastYear = mktime (0,0,0,date("m"),1,  date("Y")-1);
            $lastYear = strftime("%Y",$lastYear); //used for the legend
            
            $lArrayCurrentMonth =array_reverse($lArrayCurrentMonth);
            $lArrayLastYearMonth=array_reverse($lArrayLastYearMonth);
            /*
            for ($x = 0; $x<=count($lArrayCurrentMonth);$x++){              
                if ($lArrayCurrentMonth[$x] > $lArrayLastYearMonth[$x]){
                    $lArrayFillColor[]="#76EE00";
                }else {$lArrayFillColor[]="#EE0000"; }
            }
            */
            $lArrayFillColor[count($lArrayCurrentMonth)-1]='orange';
            // Create the bar plot
            // Create the bar plot
            $bplotLastYear = new BarPlot($lArrayLastYearMonth);
            $bplotLastYear->SetFillColor("#cccccc");
            
            $bplot = new BarPlot($lArrayCurrentMonth);
            $bplot->SetFillColor("orange");
            $bplot->value->HideZero();
            
            
            // values
            // (only update setting if it's different)
            if (isset($_GET['showvalues']) && $_GET['showvalues'] == 0) {
                if ($this->user->getShowDashboardGraphTotals() == 1) {
                    $this->user->setShowDashboardGraphTotals(0);
                    $this->user->save();
                }
            } else {
                if (!$this->user->getShowDashboardGraphTotals()) {
                    $this->user->setShowDashboardGraphTotals(1);
                    $this->user->save();
                }
                $bplot->value->Show();
            }
            
            // legend
            // (only update setting if it's different)
            if (isset($_GET['showlegend']) && $_GET['showlegend'] == 0) {
                if ($this->user->getShowDashboardGraphLegend() == 1) {
                    $this->user->setShowDashboardGraphLegend(0);
                    $this->user->save();
                }
            } else {
                if (!$this->user->getShowDashboardGraphLegend()) {
                    $this->user->setShowDashboardGraphLegend(1);
                    $this->user->save();
                }
                $bplotLastYear->SetLegend($lastYear);
                $bplot->SetLegend($thisYear);
            }
            
            // Create the grouped bar plot
            $gbplot = new GroupBarPlot(array($bplotLastYear,$bplot));
            $gbplot->SetWidth(0.9);
            $graph->Add($gbplot);
            
            
            $txt =new Text($this->user->lang("New Customers"));
            $txt->Pos(55,0);
            $txt->SetFont(FF_FONT1,FS_BOLD);
            $txt->SetColor( "black");
            $graph->AddText( $txt);
            
            $graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD);
            $graph->xaxis->title->SetFont(FF_FONT1,FS_BOLD);
            
            // Display the graph
            $graph->xaxis->SetTickLabels(array($month_minus6,$month_minus5,$month_minus4,$month_minus3,$month_minus2,$month_minus1,$month_minus0));
            $graph->xaxis->HideTicks();
            $graph->SetMarginColor('white');
            $graph->ygrid->SetFill(true,'#EFEFEF@0.5','#BBCCFF@0.5');
            $graph->xgrid->Show();
            $graph->legend->SetShadow('gray@0.4',3);
            $graph->legend->SetPos(0.0,0.0,'right','top');
            $graph->yaxis->scale->SetGrace(40);
            
            $graph->Stroke();
            exit;
        }
        
        echo "<p><img src='index.php?fuse=reports&amp;report=Monthly_Signup_Rate_And_Percentage&amp;type=Account_%26_packages&amp;viewimage=1'></p>";
        
        //Class Initialize
          $this->SetDescription($this->user->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=".ROLE_CUSTOMER;
        $result = $this->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=".ROLE_CUSTOMER." "
              ."GROUP BY DATE_FORMAT( dateActivated, '%M,%Y' ) "
              ."ORDER BY DATE_FORMAT( dateActivated, '%Y' ) ASC, DATE_FORMAT(dateActivated, '%m') ASC";
        $result = $this->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)){
              $this->Add($aGroup,"",array($this->user->lang('Month'),$this->user->lang('Signups'),$this->user->lang('Difference'),$this->user->lang('Total Percentage')));
              unset($aGroup);
          }
        
          $aGroup[] = array($this->user->lang('Year-to-date'),"<b>" . $totalcust . "</b>","<b>100%</b>");
          $this->Add($aGroup,$this->user->lang('Totals'),array("","",""));
        
          //Display Report
          $this->Show();
    }
}
?>
