<?php
//20180725 - Usage Stats Report - rachel/pj
//20190123 - Only set Total Users for months not greater than today.month - Rachel
//20190123 - Base dates on $dtStartDate - Rachel
// 20190214 - Add Total Column for 2 years back - Rachel
//20190923 - Unset LY and LY2 columns - Rachel
//20190923 - issue with +1 month skipping days with less than 31 days - Rachel

   include_once("_nemo.list.cls.php");

   //ini
   $rpt = new NemoList(null);
   $rptTools = new NemoList(null);
   $rptCourses = new NemoList(null);
   $rptCPDPoints = new NemoList(null);

   $rpt->isSelectable = 0;
   $rpt->isSortable = 0;


   $strFilename = "Usage_Stats_Report_". date("Ymd");
   $Where = "";

   if($page->Filters[frIgnoreDates]->html->checked != "checked")
   {
      $Where .= " AND (tblInvoice.dtInvoiceDate BETWEEN '". $page->Filters[frStartDate]->html->value ."' AND '". $page->Filters[frEndDate]->html->value ."')";
      //$strCaption = $page->Filters[frStartDate]->html->value ." to ". $page->Filters[frEndDate]->html->value;
   }

   $intYear = date('Y', strtotime($page->Filters[frStartDate]->html->value));
   $intYearLY = $intYear-1; //date('Y', strtotime('-1 year', strtotime($page->Filters[frStartDate]->html->value)));

//2 YEARS BACK  
   $intYearLY2 = $intYear-2;
//2 YEARS BACK  

   // $dtStartDate = $intYear."-01-01"; //date('Y-01-01', strtotime('-1 year', strtotime($page->Filters[frStartDate]->html->value)));
   // $dtEndDate = $intYear."-12-31"; //date('Y-12-31', strtotime('+1 year', strtotime($page->Filters[frStartDate]->html->value)));
   // $dtStartDateLY = $intYearLY."-01-01";
   // $dtEndDateLY = $intYearLY."-12-31";

   $dtStartDate = $page->Filters[frStartDate]->html->value; //date('Y-01-01', strtotime('-1 year', strtotime($page->Filters[frStartDate]->html->value)));
   $dtEndDate = $page->Filters[frEndDate]->html->value; //date('Y-12-31', strtotime('+1 year', strtotime($page->Filters[frStartDate]->html->value)));
   $dtEndDateLY = date("Y-m-d", strtotime($page->Filters[frStartDate]->html->value." -1 day"));
   $dtStartDateLY = date("Y-m-d", strtotime($dtEndDateLY ."- 1 year"));

   // print_rr("Start date:". $dtStartDate);
   // print_rr("Enddate: " .$dtEndDate);
   // print_rr("LY Start Date:" . $dtStartDateLY);
   // print_rr("LY End Date:".$dtEndDateLY);
   



//2 YEARS BACK   
   $dtStartDateLY2 = $intYearLY2."-01-01";
   $dtEndDateLY2 = $intYearLY2."-12-31";
//2 YEARS BACK  

   // print_rr("dtStartDate:  ". $dtStartDate);
   // print_rr("dtEndDate:  ". $dtEndDate);
   // print_rr("dtStartDateLY:  ". $dtStartDateLY);
   // print_rr("dtEndDateLY:  ". $dtEndDateLY);
   // print_rr("dtStartDateLY2:  ". $dtStartDateLY2);
   // print_rr("dtEndDateLY2:  ". $dtEndDateLY2);


   //$dtStartDateXDash = date('Ymd', strtotime('-1 year', strtotime($page->Filters[frStartDate]->html->value)));
   //$dtEndDateXDash = date('Ymd', strtotime('+1 year', strtotime($page->Filters[frStartDate]->html->value))); //NB! use < not <=
   $dtStartDateXDash = date('Ymd', strtotime($dtStartDate));
   $dtEndDateXDash = date('Ymd', strtotime($dtEndDate)); //NB! use < not <=   
   $dtStartDateXDashLY = date('Ymd', strtotime($dtStartDateLY));
   $dtEndDateXDashLY = date('Ymd',strtotime($dtEndDateLY));
   $dtStartDateXDashLY2 = date('Ymd', strtotime($dtStartDateLY2));
   $dtEndDateXDashLY2 = date('Ymd',strtotime($dtEndDateLY2));

   $strCaption = $dtStartDate ." to ". $dtEndDate;

// vd("$intYear: $dtStartDate - $dtEndDate");
// vd("$intYearLY: $dtStartDateLY - $dtEndDateLY");


   $idxTotal = "Total";
   $idxLY = "LY";
   $idxLY2 = "LY2"; // 20190214 - Add Total Column for 2 years back - Rachel
   $idxCurrentMonth = date("$intYear-m");
   //$idxCurrentMonth_I = date("m") - 1;

//Usage Stats Summary Report


//Business Tools Usage Stats Report
   //Queries

   //Query 1 : TOTAL USERS: count learners w. dtReg < $dtStartDate

   //Total Users 
   $intLearners =
   $arrSummaryQ1[$idxLY] = $xdb->getRowSQL("SELECT COUNT(*) AS intLearners
      FROM tblLearner
      WHERE tblLearner.blnActive = 1 AND Left(dtRegistered,10) < ". $xdb->qs($dtStartDate) ." ", 0)->intLearners;
      //WHERE tblLearner.blnActive = 1 AND Left(dtRegistered,10) BETWEEN '$dtStartDateLY' AND '$dtEndDateLY'  ", 0)->intLearners;
      //WHERE Left(dtRegistered,10) < ". $xdb->qs($dtStartDate) ." ", 1)->intLearners;
      //WHERE Left(dtRegistered,10) < ". $xdb->qs($page->Filters[frStartDate]->html->value) ." ", 1)->intLearners;

   $arrSummaryQ1[$idxLY2] = $xdb->getRowSQL("SELECT COUNT(*) AS intLearners
      FROM tblLearner
      WHERE tblLearner.blnActive = 1 AND Left(dtRegistered,10) < ". $xdb->qs($dtStartDateLY) ." ", 0)->intLearners;

   //New Registrations
   $arrSummaryQ1a[$idxTotal] = 0;
   //LastYear New Registrations
   $arrSummaryQ1a[$idxLY] = $xdb->getRowSQL("SELECT COUNT(*) AS intLearners
   FROM tblLearner
   WHERE tblLearner.blnActive = 1 AND (Left(dtRegistered,10) BETWEEN  '$dtStartDateLY' AND '$dtEndDateLY') ", 0)->intLearners;

   //LastYear + 1 New Registrations
   $arrSummaryQ1a[$idxLY2] = $xdb->getRowSQL("SELECT COUNT(*) AS intLearners
   FROM tblLearner
   WHERE tblLearner.blnActive = 1 AND (Left(dtRegistered,10) BETWEEN  '$dtStartDateLY2' AND '$dtEndDateLY2') ", 0)->intLearners;


   $rst = $xdb->doQuery("
      SELECT Left(dtRegistered,4) AS intYear, Left(dtRegistered,7) AS dtRegistered, Count(tblLearner.LearnerID) AS intLeaners
      FROM tblLearner
      WHERE tblLearner.blnActive = 1 AND (Left(dtRegistered,10) BETWEEN '$dtStartDate' AND  '$dtEndDate')
#delete later: WHERE tblLearner.blnActive = 1 AND (Left(dtRegistered,10) >= ". $xdb->qs($page->Filters[frStartDate]->html->value) ." AND Left(dtRegistered,10) < '$dtEndDate')
      GROUP BY Left(dtRegistered,7)
      ORDER BY Left(dtRegistered,7) ASC
   ", 0);

   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         //$arrQ1[$idxLY] += $row->intLeaners;
      }else{
         $intLearners += $row->intLeaners;
         $arrSummaryQ1[str_replace("-", "", $row->dtRegistered)] = $intLearners ; //$row->intLeaners + $arrQ1[$idxLY];
         //$arrQ1[$row->dtRegistered] += $row->intLeaners;

         //New Registrations Array
         $arrSummaryQ1a[str_replace("-", "", $row->dtRegistered)]  = $row->intLeaners;
         $arrSummaryQ1a[$idxTotal] += $row->intLeaners;
      }

      //post
      //Get last year Total users

      //$arrQ1[$idxTotal1] += $row->intLeaners;

   }

   // $arrQ1[$idxTotal] = max($arrQ1);

   //Set Total for the current year
   $arrQ1_Copy = $arrSummaryQ1;
   unset($arrQ1_Copy["LY"]);
   $arrSummaryQ1[$idxTotal] = max($arrQ1_Copy);

   if(!$arrQ1_Copy){
      $arrSummaryQ1[$idxTotal] = 0;
   }else{
      $arrSummaryQ1[$idxTotal] = max($arrQ1_Copy);
   }

   // print_rr($arrSummaryQ1);


   //Query 2
   $rst = $xdb->doQuery("
      SELECT Left(dtLogin,4) as intYear, dtLogin, COUNT(strUsername) AS intLogins
      FROM
      (
         SELECT Left(strDateTime,7) AS dtLogin, sysLogin.strUsername AS strUsername
         FROM sysLogin
         #WHERE sysLogin.strResult Like '%successful%' AND Left(strDateTime,10) >= '$dtStartDate'
         WHERE sysLogin.strResult Like '%successful%' AND Left(strDateTime,10) BETWEEN '$dtStartDateLY2' AND '$dtEndDate'
         GROUP BY Left(strDateTime,10), sysLogin.strUsername
      ) as q2
      GROUP BY dtLogin
      ORDER BY dtLogin DESC
   ", 0);


   $arrSummaryQ2[$idxLY] =
   $arrSummaryQ2[$idxLY2] = 
   $arrSummaryQ2[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrSummaryQ2[$idxLY] += $row->intLogins;

      }elseif($row->intYear == $intYearLY2){
         $arrSummaryQ2[$idxLY2] += $row->intLogins;

      }else{
         //$arrQ2[$row->dtLogin] += $row->intLogins;
         $arrSummaryQ2[str_replace("-", "", $row->dtLogin)] += $row->intLogins;
         $arrSummaryQ2[$idxTotal] += $row->intLogins;
      }

      //post

   }//print_rr("Unique Traffic: "); print_rr($arrQ2);

   //Query 3
   $rst  = $xdb->doQuery("SELECT Area, Left(dtDate,4) as intYear, dtDate, COUNT(PracticeID) AS intPFA
      FROM
      (
         SELECT tblQuestionnaire.strQuestionnaire AS Area, Left(strDate,6) AS dtDate, dwfBat.PracticeID
         FROM (tblQQ INNER JOIN (dwfBat INNER JOIN tblQuestion ON dwfBat.QuestionID = tblQuestion.QuestionID) ON (dwfBat.ClusterID = tblQQ.refClusterID) AND (tblQQ.refQuestionID = tblQuestion.QuestionID)) INNER JOIN tblQuestionnaire ON tblQQ.refQuestionnaireID = tblQuestionnaire.QuestionnaireID
         #WHERE tblQQ.refQuestionnaireID = 1 AND (Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash')
         WHERE tblQQ.refQuestionnaireID = 1 AND (Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash')
         GROUP BY tblQuestionnaire.strQuestionnaire, Left(strDate,6), dwfBat.PracticeID
         ORDER BY Left(strDate,6) DESC , dwfBat.PracticeID
      ) as q2
      GROUP BY Left(dtDate,6)
      ORDER BY Left(dtDate,6) DESC
   ", 0);

   $arrToolsQ1[$idxLY] =
   $arrToolsQ1[$idxLY2] =
   $arrToolsQ1[$idxTotal] = 
   $arrSummaryTools[$idxLY] = 
   $arrSummaryTools[$idxLY2] =
   $arrSummaryTools[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ1[$idxLY] += $row->intPFA;
         $arrSummaryTools[$idxLY] += $row->intPFA;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ1[$idxLY2] += $row->intPFA; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intPFA;
      }else{
         $arrToolsQ1[$row->dtDate] += $row->intPFA;
         $arrToolsQ1[$idxTotal] += $row->intPFA;
         
         $arrSummaryTools[$idxTotal] += $row->intPFA;
         $arrSummaryTools[$row->dtDate] += $row->intPFA;
      }

      //post

   }//print_rr("PFA (BAT): "); print_rr($arrQ3);

   //Query 4
   $rst = $xdb->doQuery("SELECT Area, Left(dtDate,4) as intYear, dtDate, COUNT(PracticeID) AS intBA
      FROM
      (
         SELECT tblQuestionnaire.strQuestionnaire AS Area, Left(strDate,6) AS dtDate, dwfBat.PracticeID
         FROM (tblQQ INNER JOIN (dwfBat INNER JOIN tblQuestion ON dwfBat.QuestionID = tblQuestion.QuestionID) ON (dwfBat.ClusterID = tblQQ.refClusterID) AND (tblQQ.refQuestionID = tblQuestion.QuestionID)) INNER JOIN tblQuestionnaire ON tblQQ.refQuestionnaireID = tblQuestionnaire.QuestionnaireID
         #WHERE tblQQ.refQuestionnaireID = 2 AND (Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash')
         WHERE tblQQ.refQuestionnaireID = 2 AND (Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash')
         GROUP BY tblQuestionnaire.strQuestionnaire, Left(strDate,6), dwfBat.PracticeID
      ) as q2
      GROUP BY Left(dtDate,6)
      ORDER BY Left(dtDate,6) DESC
   ", 0);

   $arrToolsQ2[$idxLY] =
   $arrToolsQ2[$idxLY2] =
   $arrToolsQ2[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ2[$idxLY] += $row->intBA;
         $arrSummaryTools[$idxLY] += $row->intBA;

      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ2[$idxLY2] += $row->intBA; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intBA;
      }else{
         $arrToolsQ2[$row->dtDate] += $row->intBA;
         $arrToolsQ2[$idxTotal] += $row->intBA;

         $arrSummaryTools[$idxTotal] += $row->intBA;
         $arrSummaryTools[$row->dtDate] += $row->intBA;
      }

      //post

   }//print_rr("Business Assessment: "); print_rr($arrQ4);


   //Query 5
   $rst = $xdb->doQuery("SELECT Area, Left(dtDate,4) as intYear, dtDate, COUNT(PracticeID) AS intPWM
      FROM
      (
         SELECT tblQuestionnaire.strQuestionnaire AS Area, Left(strDate,6) AS dtDate, dwfBat.PracticeID
         FROM (tblQQ INNER JOIN (dwfBat INNER JOIN tblQuestion ON dwfBat.QuestionID = tblQuestion.QuestionID) ON (dwfBat.ClusterID = tblQQ.refClusterID) AND (tblQQ.refQuestionID = tblQuestion.QuestionID)) INNER JOIN tblQuestionnaire ON tblQQ.refQuestionnaireID = tblQuestionnaire.QuestionnaireID
         #WHERE tblQQ.refQuestionnaireID = 3 AND (Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash')
         WHERE tblQQ.refQuestionnaireID = 3 AND (Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash')
         GROUP BY tblQuestionnaire.strQuestionnaire, Left(strDate,6), dwfBat.PracticeID
      ) as q2
      GROUP BY Left(dtDate,6)
      ORDER BY Left(dtDate,6) DESC
   ", 0);

   $arrToolsQ3[$idxLY] =
   $arrToolsQ3[$idxLY2] = 
   $arrToolsQ3[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ3[$idxLY] += $row->intPWM;
         $arrSummaryTools[$idxLY] += $row->intPWM;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ3[$idxLY2] += $row->intPWM; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intPWM;
      }else{
         $arrToolsQ3[$row->dtDate] += $row->intPWM;
         $arrToolsQ3[$idxTotal] += $row->intPWM;

         $arrSummaryTools[$idxTotal] += $row->intPWM;
         $arrSummaryTools[$row->dtDate] += $row->intPWM;
      }

      //post

   }//print_rr("PWM (BAT) Assessment: "); print_rr($arrQ5);


   //Query 6
   $Query6a = $xdb->doQuery("
      SELECT Area, Left(dtDate,4) AS intYear, dtDate, COUNT(refUniqueID) AS intCSS
      FROM
      (
         SELECT 'Client Survey' AS Area, Left(strDateTime,6) AS dtDate, catSurveyEmailResponse.refUniqueID AS refUniqueID
         FROM catSurveyEmailResponse
         #WHERE Left(strDateTime,8) >= '$dtStartDateXDash' AND Left(strDateTime,8) < '$dtEndDateXDash'
         WHERE Left(strDateTime,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash'
            AND strStatus IN ('Completed', 'Started')
         GROUP BY Left(strDateTime,6) , catSurveyEmailResponse.refUniqueID
      ) as q2
      GROUP BY dtDate
      ORDER BY dtDate DESC
   ", 0);


   $Query6b = $xdb->doQuery("
      SELECT Area, Left(dtDate,4) AS intYear, dtDate, COUNT(SurveyID) AS intCSS
      FROM
      (
         SELECT 'Client Survey' AS Area, Left(strDateEnd,6) AS dtDate, catSurvey.SurveyID AS SurveyID
         FROM catSurvey
         #WHERE Left(strDateEnd,8) >= '$dtStartDateXDash' AND Left(strDateEnd,8) < '$dtEndDateXDash'
         WHERE Left(strDateEnd,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash'
         GROUP BY Left(strDateEnd,6), catSurvey.SurveyID
      ) as q2
      GROUP BY dtDate
      ORDER BY dtDate DESC
   ", 0);


   $arrToolsQ4[$idxLY] =
   $arrToolsQ4[$idxLY2] =
   $arrToolsQ4[$idxTotal] = 0;
   while($row = $xdb->fetch_object($Query6a))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ4[$idxLY] += $row->intCSS;
         $arrSummaryTools[$idxLY] += $row->intCSS;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ4[$idxLY2] += $row->intCSS; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intCSS;
      }else{
         $arrToolsQ4[$row->dtDate] += $row->intCSS;
         $arrToolsQ4[$idxTotal] += $row->intCSS;

         $arrSummaryTools[$idxTotal] += $row->intCSS;
         $arrSummaryTools[$row->dtDate] += $row->intCSS;
      }

      //post

   }

   while($row = $xdb->fetch_object($Query6b))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ4[$idxLY] += $row->intCSS;
         $arrSummaryTools[$idxLY] += $row->intCSS;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ4[$idxLY2] += $row->intCSS; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intCSS;
      }else{
         $arrToolsQ4[$row->dtDate] += $row->intCSS;
         $arrToolsQ4[$idxTotal] += $row->intCSS;

         $arrSummaryTools[$idxTotal] += $row->intCSS;
         $arrSummaryTools[$row->dtDate] += $row->intCSS;

      }

      //post

   }//print_rr("Client Survey: "); print_rr($arrQ6);

   //Query 7
   $rst = $xdb->doQuery("
      SELECT Area, Left(dtDate,4) AS intYear, dtDate, COUNT(refPracticeID) AS intCSS
      FROM
      (
         SELECT 'PoPI Gap Analysis' AS Area, Left(strDate,6) AS dtDate, gapPoPISelection.refPracticeID AS refPracticeID
         FROM gapPoPISelection
         #WHERE Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash'
         WHERE Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND  '$dtEndDateXDash'
         GROUP BY Left(strDate,6), gapPoPISelection.refPracticeID
      ) as q2
      GROUP BY dtDate
      ORDER BY dtDate DESC
   ", 0);

   $arrToolsQ5[$idxLY] =
   $arrToolsQ5[$idxLY2] =
   $arrToolsQ5[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ5[$idxLY] += $row->intCSS;
         $arrSummaryTools[$idxLY] += $row->intCSS;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ5[$idxLY2] += $row->intCSS; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intCSS;
      }else{
         $arrToolsQ5[$row->dtDate] += $row->intCSS;
         $arrToolsQ5[$idxTotal] += $row->intCSS;

         $arrSummaryTools[$idxTotal] += $row->intCSS;
         $arrSummaryTools[$row->dtDate] += $row->intCSS;
      }

      //post

   }//print_rr("POPI GAP Analysis: "); print_rr($arrQ7);


   //Query 8
   $rst = $xdb->doQuery("
      SELECT Area, Left(dtDate,4) AS intYear, dtDate, COUNT(refPracticeID) AS intCSS
      FROM
      (
         SELECT 'RDR Gap Analysis' AS Area, Left(strDate,6) AS dtDate, gapRDRSelection.SelectionID, gapRDRSelection.refPracticeID AS refPracticeID
         FROM gapRDRSelection
         #WHERE Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash'
         WHERE Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash'
         GROUP BY Left(strDate,6), gapRDRSelection.refPracticeID
         ORDER BY Left(strDate,6) DESC
      ) as q2
      GROUP BY dtDate
      ORDER BY dtDate DESC
   ", 0);

   $arrToolsQ6[$idxLY] =
   $arrToolsQ6[$idxLY2] =
   $arrToolsQ6[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ6[$idxLY] += $row->intCSS;
         $arrSummaryTools[$idxLY] += $row->intCSS;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ6[$idxLY2] += $row->intCSS; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intCSS;
      }else{
         $arrToolsQ6[$row->dtDate] += $row->intCSS;
         $arrToolsQ6[$idxTotal] += $row->intCSS;

         $arrSummaryTools[$idxTotal] += $row->intCSS;
         $arrSummaryTools[$row->dtDate] += $row->intCSS;
      }

      //post

   }//print_rr("RDR Gap Analysis: "); print_rr($arrQ8);


   //Query 9
   $rst = $xdb->doQuery("
      SELECT Area, Left(dtDate,4) AS intYear, dtDate, COUNT(refPracticeID) AS intCSS
      FROM
      (
         SELECT 'RDR Gap Analysis' AS Area, Left(strDate,6) AS dtDate, gapTCFSelection.refPracticeID AS refPracticeID
         FROM gapTCFSelection
         #WHERE Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash'
         WHERE Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash'
         GROUP BY Left(strDate,6), gapTCFSelection.refPracticeID
         ORDER BY Left(strDate,6) DESC
      ) as q2
      GROUP BY dtDate
      ORDER BY dtDate DESC
   ", 0);

   $arrToolsQ7[$idxLY] =
   $arrToolsQ7[$idxLY2] =
   $arrToolsQ7[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ7[$idxLY] += $row->intCSS;
         $arrSummaryTools[$idxLY] += $row->intCSS;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ7[$idxLY2] += $row->intCSS; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intCSS;
      }else{
         $arrToolsQ7[$row->dtDate] += $row->intCSS;
         $arrToolsQ7[$idxTotal] += $row->intCSS;

         $arrSummaryTools[$idxTotal] += $row->intCSS;
         $arrSummaryTools[$row->dtDate] += $row->intCSS;
      }

      //post

   }//print_rr("TCF Gap Analysis: "); print_rr($arrQ9);


//20190220 -
   //Query 10
   $rst = $xdb->doQuery("
      SELECT Area, Left(dtDate,4) AS intYear, dtDate, COUNT(refPracticeID) AS intCSS
      FROM
      (
         SELECT 'Succession Planning' AS Area, Left(strDate,6) AS dtDate, gapSCPSelection.refPracticeID AS refPracticeID
         FROM gapSCPSelection
         #WHERE Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash'
         WHERE Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash'
         GROUP BY Left(strDate,6), gapSCPSelection.refPracticeID
         ORDER BY Left(strDate,6) DESC
      ) as q2
      GROUP BY dtDate
      ORDER BY dtDate DESC
   ", 0);

   $arrToolsQ8[$idxLY] =
   $arrToolsQ8[$idxLY2] =
   $arrToolsQ8[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ8[$idxLY] += $row->intCSS;
         $arrSummaryTools[$idxLY] += $row->intCSS;
      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ8[$idxLY2] += $row->intCSS; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intCSS;
      }else{
         $arrToolsQ8[$row->dtDate] += $row->intCSS;
         $arrToolsQ8[$idxTotal] += $row->intCSS;

         $arrSummaryTools[$idxTotal] += $row->intCSS;
         $arrSummaryTools[$row->dtDate] += $row->intCSS;
      }

      //post

   }//print_rr("SCP Gap Analysis: "); print_rr($arrToolsQ8);

   //20200818 - Adding New BAT - Rachel
   //Query 9
   $rst = $xdb->doQuery("SELECT Area, Left(dtDate,4) as intYear, dtDate, COUNT(PracticeID) AS intBAV2
      FROM
      (
         SELECT tblQuestionnaire.strQuestionnaire AS Area, Left(strDate,6) AS dtDate, dwfBat_2020.PracticeID
         FROM (tblQQ_2020 INNER JOIN (dwfBat_2020 INNER JOIN tblQuestion_2020 ON dwfBat_2020.QuestionID = tblQuestion_2020.QuestionID) ON (dwfBat_2020.ClusterID = tblQQ_2020.refClusterID) AND (tblQQ_2020.refQuestionID = tblQuestion_2020.QuestionID)) INNER JOIN tblQuestionnaire ON tblQQ_2020.refQuestionnaireID = tblQuestionnaire.QuestionnaireID
            INNER JOIN tblPractice ON tblPractice.PracticeID = dwfBat_2020.PracticeID
         #WHERE tblQQ.refQuestionnaireID = 2 AND (Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash')
         WHERE tblQQ_2020.refQuestionnaireID = 4 AND (Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash') AND tblPractice.refPracticeConsultantID IS NOT NULL
         GROUP BY tblQuestionnaire.strQuestionnaire, Left(strDate,6), dwfBat_2020.PracticeID
      ) as q2
      GROUP BY Left(dtDate,6)
      ORDER BY Left(dtDate,6) DESC
   ", 0);

   $arrToolsQ9[$idxLY] =
   $arrToolsQ9[$idxLY2] =
   $arrToolsQ9[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ9[$idxLY] += $row->intBAV2;
         $arrSummaryTools[$idxLY] += $row->intBAV2;

      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ9[$idxLY2] += $row->intBAV2; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intBAV2;
      }else{
         $arrToolsQ9[$row->dtDate] += $row->intBAV2;
         $arrToolsQ9[$idxTotal] += $row->intBAV2;

         $arrSummaryTools[$idxTotal] += $row->intBAV2;
         $arrSummaryTools[$row->dtDate] += $row->intBAV2;
      }

      //post

   }//print_rr("Business Assessment V2: "); 
   //print_rr($arrToolsQ9);

   $rst = $xdb->doQuery("SELECT Area, Left(dtDate,4) as intYear, dtDate, COUNT(PracticeID) AS intBAV2
      FROM
      (
         SELECT tblQuestionnaire.strQuestionnaire AS Area, Left(strDate,6) AS dtDate, dwfBat_2020.PracticeID
         FROM (tblQQ_2020 INNER JOIN (dwfBat_2020 INNER JOIN tblQuestion_2020 ON dwfBat_2020.QuestionID = tblQuestion_2020.QuestionID) ON (dwfBat_2020.ClusterID = tblQQ_2020.refClusterID) AND (tblQQ_2020.refQuestionID = tblQuestion_2020.QuestionID)) INNER JOIN tblQuestionnaire ON tblQQ_2020.refQuestionnaireID = tblQuestionnaire.QuestionnaireID
            INNER JOIN tblPractice ON tblPractice.PracticeID = dwfBat_2020.PracticeID
         #WHERE tblQQ.refQuestionnaireID = 2 AND (Left(strDate,8) >= '$dtStartDateXDash' AND Left(strDate,8) < '$dtEndDateXDash')
         WHERE tblQQ_2020.refQuestionnaireID = 4 AND (Left(strDate,8) BETWEEN '$dtStartDateXDashLY2' AND '$dtEndDateXDash') AND tblPractice.refPracticeConsultantID IS NULL
         GROUP BY tblQuestionnaire.strQuestionnaire, Left(strDate,6), dwfBat_2020.PracticeID
      ) as q2
      GROUP BY Left(dtDate,6)
      ORDER BY Left(dtDate,6) DESC
   ", 0);

   $arrToolsQ10[$idxLY] =
   $arrToolsQ10[$idxLY2] =
   $arrToolsQ10[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrToolsQ10[$idxLY] += $row->intBAV2;
         $arrSummaryTools[$idxLY] += $row->intBAV2;

      }elseif($row->intYear == $intYearLY2){
         $arrToolsQ10[$idxLY2] += $row->intBAV2; //TOTEST
         $arrSummaryTools[$idxLY2] += $row->intBAV2;
      }else{
         $arrToolsQ10[$row->dtDate] += $row->intBAV2;
         $arrToolsQ10[$idxTotal] += $row->intBAV2;

         $arrSummaryTools[$idxTotal] += $row->intBAV2;
         $arrSummaryTools[$row->dtDate] += $row->intBAV2;
      }

      //post

   }//print_rr("Business Assessment V2 DIY: "); 
   //print_rr($arrToolsQ10);
//20190220 - 


//COURSES
   //20190220 - Exclude Worshops, Wealth Intelligence and Inactive courses 
   $rst = $xdb->doQuery("
      SELECT tblCourseType.CourseTypeID, Left(tblLicence.dtDateIssued,4) AS intYear, Left(tblLicence.dtDateIssued,7) AS dtDate, tblCourseType.strCourseType, tblCourseType.strCourseTypeCode, tblCourse.dtStartDate, tblLicence.strStatus, Count(tblLicence.LicenceID) AS CountOfLicenceID
      FROM tblLicence INNER JOIN (tblCourse INNER JOIN tblCourseType ON tblCourse.refCourseTypeID = tblCourseType.CourseTypeID) ON tblLicence.refCourseID = tblCourse.CourseID
      WHERE tblCourseType.blnActive=1 AND 
      #(Left(tblLicence.dtDateIssued,10) >= '$dtStartDate' AND Left(tblLicence.dtDateIssued,10) < '$dtEndDate') 
      (Left(tblLicence.dtDateIssued,10) BETWEEN '$dtStartDateLY2' AND  '$dtEndDate')
      AND tblCourseType.blnWI = 0 AND tblCourseType.blnWorkshop = 0 AND tblCourseType.blnActive = 1

      AND tblLicence.strStatus='Active'
      GROUP BY tblCourseType.CourseTypeID, Left(dtDate,7)
      ORDER BY Left(dtDate,7) ASC
   ", 0);


   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrCourses[$row->CourseTypeID][$idxLY] += $row->CountOfLicenceID;
         $arrAllCourses[$idxLY] += $row->CountOfLicenceID;

      }elseif($row->intYear == $intYearLY2){
         $arrCourses[$row->CourseTypeID][$idxLY2] += $row->CountOfLicenceID;
         $arrAllCourses[$idxLY2] += $row->CountOfLicenceID;
      }else{
         $arrCourses[$row->CourseTypeID][str_replace("-", "",$row->dtDate)] += $row->CountOfLicenceID;
         $arrCourses[$row->CourseTypeID][$idxTotal] += $row->CountOfLicenceID;
         $arrAllCourses[str_replace("-", "",$row->dtDate)] += $row->CountOfLicenceID;
         $arrAllCourses[$idxTotal] += $row->CountOfLicenceID;
      }

      //post

   }//print_rr($arrCourses);


//CPD Events

   $rst = $xdb->doQuery("
      SELECT tblCPDPoints.ID, Left(tblCPDPoints.dtEventDate,4) AS intYear, Left(tblCPDPoints.dtEventDate,7) AS dtDate,tblCPDPoints.refCPDCategory, Count(tblCPDPoints.refEventID) AS CountOfEvents
      FROM tblCPDPoints
      WHERE (Left(tblCPDPoints.dtEventDate,10) BETWEEN '$dtStartDateLY2' AND  '$dtEndDate') AND tblCPDPoints.refCPDCategory IS NOT NULL 
      AND tblCPDPoints.refCPDCategory != ''
      GROUP BY refCPDCategory, Left(dtDate,7)
      ORDER BY refCPDCategory, Left(dtDate,7) ASC
   ", 0);

   $arrAllCPDPoints[$idxLY] =
   $arrAllCPDPoints[$idxLY2] =
   $arrAllCPDPoints[$idxTotal] = 0;
   while($row = $xdb->fetch_object($rst))
   {
      //ini

      //go
      if($row->intYear == $intYearLY)
      {
         $arrCPDPoints[$row->refCPDCategory][$idxLY] += $row->CountOfEvents;
         $arrAllCPDPoints[$idxLY] += $row->CountOfEvents;

      }elseif($row->intYear == $intYearLY2){
         $arrCPDPoints[$row->refCPDCategory][$idxLY2] += $row->CountOfEvents;
         $arrAllCPDPoints[$idxLY2] += $row->CountOfEvents;
      }else{
         $arrCPDPoints[$row->refCPDCategory][str_replace("-", "",$row->dtDate)] += $row->CountOfEvents;
         $arrCPDPoints[$row->refCPDCategory][$idxTotal] += $row->CountOfEvents;
         $arrAllCPDPoints[str_replace("-", "",$row->dtDate)] += $row->CountOfEvents;
         $arrAllCPDPoints[$idxTotal] += $row->CountOfEvents;
      }

      //post

   }//print_rr($arrCPDPoints);


//CPD Events

   //column headings
   $comma = "";
   for($i=0; $i<12; $i++){

      //ini
      // $sqlMonth = date("M", strtotime("January +$i Month"));
      // $idxPeriod = date("Ym", strtotime("January +$i Month"));
      // $idxPeriodPrevious = date("Ym", strtotime("January +". ($i-1)." Month"));


      // if(date("Y", strtotime("$dtStartDate")) == date("Y", strtotime("$dtEndDate"))){


//20190923 - issue with +1 month skipping days with less than 31 days - Rachel
         $dtCurrent = new DateTime("$dtStartDate ");
         $oldDay = $dtCurrent->format("d");
         $dtCurrent->add(new DateInterval("P".$i."M")); // 2016-03-02
         $newDay = $dtCurrent->format("d");


         if($oldDay != $newDay) {
             // Check if the day is changed, if so we skipped to the next month.
             // Substract days to go back to the last day of previous month.
             $dtCurrent->sub(new DateInterval("P" . $newDay . "D"));
         }

// echo     $dtCurrent->format("Y-m-d"); // 2016-02-29

         //20190123 - Base dates on $dtStartDate - Rachel
         $sqlMonth = date("M", strtotime($dtCurrent->format("Y-m-d")));
         $idxPeriod = date("Ym", strtotime($dtCurrent->format("Y-m-d")));
         $idxPeriodPrevious = date("Ym", strtotime($dtCurrent->format("Y-m-d")." +". ($i-1)." Month"));
         

         if($i==12 && !(date("Ym", strtotime($dtCurrent->format("Y-m-d"))) > date("Ym", strtotime("$dtEndDate")) ) ){
            $dtLastDate =
            $sqlMonth = date("M-Y", strtotime($dtCurrent->format("Y-m-d")));

         }

      // }else{

      //    //20190123 - Base dates on $dtStartDate - Rachel
      //    $sqlMonth = date("M-Y", strtotime("$dtStartDate +$i Month"));
      //    $idxPeriod = date("Ym", strtotime("$dtStartDate +$i Month"));
      //    $idxPeriodPrevious = date("Ym", strtotime("$dtStartDate +". ($i-1)." Month"));

      // }

      //go
      //20190123 - Only set Total Users for months not greater than today.month - Rachel
      // $i > $idxCurrentMonth_I is incorrect
      if((!isset($arrSummaryQ1[$idxPeriod]) && ($i == 0)) || $idxPeriod > date("Ym") ){ 
         $arrSummaryQ1[$idxPeriod] = 0;
         $arrSummaryQ1a[$idxPeriod] = 0;
      }elseif(!isset($arrSummaryQ1[$idxPeriod]) && $i > 0){
         $arrSummaryQ1[$idxPeriod] = $arrSummaryQ1[$idxPeriodPrevious];
         $arrSummaryQ1a[$idxPeriod] = 0;
      }

      $sqlHeadingsC .= $comma . " 0 AS `$sqlMonth`";

      //Summary SQL Headings
      if(!isset($arrSummaryQ1a[$idxPeriod])) $arrSummaryQ1a[$idxPeriod] = 0;
      $sqlNewRegistrations .= $comma . $arrSummaryQ1a[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrSummaryQ2[$idxPeriod])) $arrSummaryQ2[$idxPeriod] = 0;
      $sqlUniqueTraffic .= $comma . $arrSummaryQ2[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrSummaryTools[$idxPeriod])) $arrSummaryTools[$idxPeriod] = 0;
      $sqlTools .= $comma . $arrSummaryTools[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrAllCourses[$idxPeriod])) $arrAllCourses[$idxPeriod] = 0;
      $sqlCourses .= $comma . $arrAllCourses[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrAllCPDPoints[$idxPeriod])) $arrAllCPDPoints[$idxPeriod] = 0;
      $sqlCPDPoints .= $comma . $arrAllCPDPoints[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrSummaryQ1[$idxPeriod])) $arrSummaryQ1[$idxPeriod] = 0;
      $sqlTotalUser .= $comma . $arrSummaryQ1[$idxPeriod] ." AS `$sqlMonth`";


      if(!isset($arrToolsQ1[$idxPeriod])) $arrToolsQ1[$idxPeriod] = 0;
      $sqlHeadingsQ1 .= $comma . $arrToolsQ1[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ2[$idxPeriod])) $arrToolsQ2[$idxPeriod] = 0;
      $sqlHeadingsQ2 .= $comma . $arrToolsQ2[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ3[$idxPeriod])) $arrToolsQ3[$idxPeriod] = 0;
      $sqlHeadingsQ3 .= $comma . $arrToolsQ3[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ4[$idxPeriod])) $arrToolsQ4[$idxPeriod] = 0;
      $sqlHeadingsQ4 .= $comma . $arrToolsQ4[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ5[$idxPeriod])) $arrToolsQ5[$idxPeriod] = 0;
      $sqlHeadingsQ5 .= $comma . $arrToolsQ5[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ6[$idxPeriod])) $arrToolsQ6[$idxPeriod] = 0;
      $sqlHeadingsQ6 .= $comma . $arrToolsQ6[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ7[$idxPeriod])) $arrToolsQ7[$idxPeriod] = 0;
      $sqlHeadingsQ7 .= $comma . $arrToolsQ7[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ8[$idxPeriod])) $arrToolsQ8[$idxPeriod] = 0;
      $sqlHeadingsQ8 .= $comma . $arrToolsQ8[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ9[$idxPeriod])) $arrToolsQ9[$idxPeriod] = 0;
      $sqlHeadingsQ9 .= $comma . $arrToolsQ9[$idxPeriod] ." AS `$sqlMonth`";

      if(!isset($arrToolsQ10[$idxPeriod])) $arrToolsQ10[$idxPeriod] = 0;
      $sqlHeadingsQ10 .= $comma . $arrToolsQ10[$idxPeriod] ." AS `$sqlMonth`";

      //post
      $comma = ", ";
   }

   if($arrAllCourses[$idxTotal] == "") $arrAllCourses[$idxTotal] = 0;
   if($arrAllCourses[$idxLY] == "") $arrAllCourses[$idxLY] = 0;
   if($arrAllCourses[$idxLY2] == "") $arrAllCourses[$idxLY2] = 0;

   $rpt->ListSQL("
      SELECT 'New Registrations' AS Summary, $sqlNewRegistrations, ". $arrSummaryQ1a[$idxTotal] ." AS `$idxTotal`, ". $arrSummaryQ1a[$idxLY] ." AS `$idxLY`, ". $arrSummaryQ1a[$idxLY2] ." AS `$idxLY2`
      UNION
      SELECT 'Unique Traffic' AS Summary, $sqlUniqueTraffic, ". $arrSummaryQ2[$idxTotal] ." AS `$idxTotal`, ". $arrSummaryQ2[$idxLY] ." AS `$idxLY`, ". $arrSummaryQ2[$idxLY2] ." AS `$idxLY2`
      UNION
      SELECT 'All Business Tools' AS Summary, $sqlTools, ". $arrSummaryTools[$idxTotal] ." AS `$idxTotal`, ". $arrSummaryTools[$idxLY] ." AS `$idxLY`, ". $arrSummaryTools[$idxLY2] ." AS `$idxLY2`
      UNION
      SELECT 'All Courses' AS Summary, $sqlCourses, ". $arrAllCourses[$idxTotal] ." AS `$idxTotal`, ". $arrAllCourses[$idxLY] ." AS `$idxLY`, ". $arrAllCourses[$idxLY2] ." AS `$idxLY2`
      UNION
      SELECT 'All CPD Events' AS Summary, $sqlCPDPoints, ". $arrAllCPDPoints[$idxTotal] ." AS `$idxTotal`, ". $arrAllCPDPoints[$idxLY] ." AS `$idxLY`, ". $arrAllCPDPoints[$idxLY2] ." AS `$idxLY2`
      UNION
      SELECT 'Total Users' AS Summary, $sqlTotalUser, ". $arrSummaryQ1[$idxTotal] ." AS `$idxTotal`, ". $arrSummaryQ1[$idxLY] ." AS `$idxLY`, ". $arrSummaryQ1[$idxLY2] ." AS `$idxLY2`", 0);

   $rptTools->ListSQL("
      SELECT strProduct AS Tool, $sqlHeadingsQ1, ".$arrToolsQ1[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ1[$idxLY] ." AS `$idxLY`, ". $arrToolsQ1[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 1
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ2, ".$arrToolsQ2[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ2[$idxLY] ." AS `$idxLY`, ". $arrToolsQ2[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 99
      UNION 
      SELECT 'Business Assessment V2' AS Tool, $sqlHeadingsQ9, ".$arrToolsQ9[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ9[$idxLY] ." AS `$idxLY`, ". $arrToolsQ9[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 101
      UNION
      SELECT 'Business Assessment V2 (DIY)' AS Tool, $sqlHeadingsQ10, ".$arrToolsQ10[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ10[$idxLY] ." AS `$idxLY`, ". $arrToolsQ10[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 101
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ3, ".$arrToolsQ3[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ3[$idxLY] ." AS `$idxLY`, ". $arrToolsQ3[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 100
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ4, ".$arrToolsQ4[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ4[$idxLY] ." AS `$idxLY`, ". $arrToolsQ4[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 3
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ5, ".$arrToolsQ5[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ5[$idxLY] ." AS `$idxLY`, ".$arrToolsQ5[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 91
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ6, ".$arrToolsQ6[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ6[$idxLY] ." AS `$idxLY`, ".$arrToolsQ6[$idxLY2]." AS `$idxLY2` FROM tblProduct WHERE ProductID = 92
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ7, ".$arrToolsQ7[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ7[$idxLY] ." AS `$idxLY`, ". $arrToolsQ7[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 90
      UNION
      SELECT strProduct AS Tool, $sqlHeadingsQ8, ".$arrToolsQ8[$idxTotal]." AS `$idxTotal`, ". $arrToolsQ8[$idxLY] ." AS `$idxLY`, ". $arrToolsQ8[$idxLY2] ." AS `$idxLY2` FROM tblProduct WHERE ProductID = 93
   ", 0);

   //20190220 - Exclude Worshops, Wealth Intelligence and Inactive courses 
   $rptCourses->ListSQL("
      SELECT tblCourseType.CourseTypeID, concat(tblCourseType.strCourseType, ' (', tblCourseType.strCourseTypeCode,')') AS Course
         , $sqlHeadingsC, 0 AS `$idxTotal`, 0 AS `$idxLY`, 0 AS `$idxLY2`, 0 AS intOrder
      FROM tblCourse INNER JOIN tblCourseType ON tblCourse.refCourseTypeID = tblCourseType.CourseTypeID
      WHERE (((tblCourseType.blnActive)=1) AND ((tblCourse.blnActive)=1)) AND tblCourseType.blnWI = 0 AND tblCourseType.blnWorkshop = 0 AND tblCourseType.blnActive = 1
      GROUP BY tblCourseType.CourseTypeID, tblCourseType.strCourseType, tblCourseType.strCourseTypeCode
      UNION
      SELECT 0 as CourseTypeID, '<b>Total</b>' AS Course, $sqlHeadingsC, 0 AS `$idxTotal`, 0 AS `$idxLY`, 0 AS `$idxLY2`, 1 as intOrder
      ORDER BY intOrder, Course
   ", 0);


   $rptCPDPoints->ListSQL("
      SELECT tblCPDPoints.ID, tblCPDPoints.refCPDCategory AS Category
         , $sqlHeadingsC, 0 AS `$idxTotal`, 0 AS `$idxLY`, 0 AS `$idxLY2`, 0 AS intOrder
      FROM tblCPDPoints
      WHERE tblCPDPoints.refCPDCategory IS NOT NULL AND tblCPDPoints.refCPDCategory != ''
      GROUP BY tblCPDPoints.refCPDCategory
      UNION
      SELECT 0 as ID, '<b>Total</b>' AS Category, $sqlHeadingsC, 0 AS `$idxTotal`, 0 AS `$idxLY`, 0 AS `$idxLY2`, 1 as intOrder
      ORDER BY intOrder, Category 
   ", 0);

   //Format Columns
   for($i=0; $i < 12 ;$i++)
   {
      //ini

      $dtCurrent = new DateTime("$dtStartDate ");

      $oldDay = $dtCurrent->format("d");
      $dtCurrent->add(new DateInterval("P".$i."M")); // 2016-03-02
      $newDay = $dtCurrent->format("d");

      if($oldDay != $newDay) {
          // Check if the day is changed, if so we skipped to the next month.
          // Substract days to go back to the last day of previous month.
          $dtCurrent->sub(new DateInterval("P" . $newDay . "D"));
      }


      //20190123 - Base dates on $dtStartDate - Rachel
      $idxMonth = date("M", strtotime($dtCurrent->format("Y-m-d")));
      $idxPeriod = date("Ym", strtotime($dtCurrent->format("Y-m-d")));

      $rpt->Columns[$idxMonth]->html->align =
      $rptTools->Columns[$idxMonth]->html->align =
      $rptCourses->Columns[$idxMonth]->html->align = 
      $rptCPDPoints->Columns[$idxMonth]->html->align = "right";

      if($i==12 && !(date("Ym", strtotime($dtCurrent->format("Y-m-d"))) > date("Ym", strtotime("$dtEndDate")) ) ){

         $Month = date("M-Y", strtotime($dtCurrent->format("Y-m-d")));
         $rpt->Columns[$Month]->html->align =
         $rptTools->Columns[$Month]->html->align =
         $rptCourses->Columns[$Month]->html->align = 
         $rptCPDPoints->Columns[$Month]->html->align = "right";

      }

      foreach ($rptCourses->Data as $idxRow => $row) {
         //print_rr($row);
         $intCourseCount = $arrCourses[$rptCourses->Data[$idxRow]["CourseTypeID"]][$idxPeriod];
         $intCourseTotal = $arrCourses[ $rptCourses->Data[$idxRow]["CourseTypeID"] ]["Total"];
         $intCourseLY = $arrCourses[ $rptCourses->Data[$idxRow]["CourseTypeID"] ]["LY"];
         $intCourseLY2 = $arrCourses[ $rptCourses->Data[$idxRow]["CourseTypeID"] ]["LY2"];

         $rptCourses->Data[$idxRow][$idxMonth] = ($intCourseCount == "" ? 0 : $intCourseCount);
         $rptCourses->Data[$idxRow]["Total"] = ($intCourseTotal =="" ? 0 : $intCourseTotal);
         $rptCourses->Data[$idxRow]["LY"] = ($intCourseLY == "" ? 0 : $intCourseLY);
         $rptCourses->Data[$idxRow]["LY2"] = ($intCourseLY2 == "" ? 0 : $intCourseLY2);

         //Course Row Totals
         $rptCourses->Data[count($rptCourses->Data) -1][$idxMonth] = ($arrAllCourses[$idxPeriod] == "" ? 0 : $arrAllCourses[$idxPeriod]);
         $rptCourses->Data[count($rptCourses->Data) -1]["Total"] = $arrAllCourses[$idxTotal] == "" ? 0 : $arrAllCourses[$idxTotal];
         $rptCourses->Data[count($rptCourses->Data) -1]["LY"] = ($arrAllCourses[$idxLY] == "" ? 0 : $arrAllCourses[$idxLY]);
         $rptCourses->Data[count($rptCourses->Data) -1]["LY2"] = ($arrAllCourses[$idxLY2] == "" ? 0 : $arrAllCourses[$idxLY2]);
      }


      foreach ($rptCPDPoints->Data as $idxRow => $row) {
         //print_rr($row);
         $intCPDPointsCount = $arrCPDPoints[$rptCPDPoints->Data[$idxRow]["Category"]][$idxPeriod];
         $intCPDPointsTotal = $arrCPDPoints[ $rptCPDPoints->Data[$idxRow]["Category"] ]["Total"];
         $intCPDPointsLY = $arrCPDPoints[ $rptCPDPoints->Data[$idxRow]["Category"] ]["LY"];
         $intCPDPointsLY2 = $arrCPDPoints[ $rptCPDPoints->Data[$idxRow]["Category"] ]["LY2"];

         $rptCPDPoints->Data[$idxRow][$idxMonth] = ($intCPDPointsCount == "" ? 0 : $intCPDPointsCount);
         $rptCPDPoints->Data[$idxRow]["Total"] = ($intCPDPointsTotal =="" ? 0 : $intCPDPointsTotal);
         $rptCPDPoints->Data[$idxRow]["LY"] = ($intCPDPointsLY == "" ? 0 : $intCPDPointsLY);
         $rptCPDPoints->Data[$idxRow]["LY2"] = ($intCPDPointsLY2 == "" ? 0 : $intCPDPointsLY2);

         //Course Row Totals
         $rptCPDPoints->Data[count($rptCPDPoints->Data) -1][$idxMonth] = ($arrAllCPDPoints[$idxPeriod] == "" ? 0 : $arrAllCPDPoints[$idxPeriod]);
         $rptCPDPoints->Data[count($rptCPDPoints->Data) -1]["Total"] = $arrAllCPDPoints[$idxTotal] == "" ? 0 : $arrAllCPDPoints[$idxTotal];
         $rptCPDPoints->Data[count($rptCPDPoints->Data) -1]["LY"] = ($arrAllCPDPoints[$idxLY] == "" ? 0 : $arrAllCPDPoints[$idxLY]);
         $rptCPDPoints->Data[count($rptCPDPoints->Data) -1]["LY2"] = ($arrAllCPDPoints[$idxLY2] == "" ? 0 : $arrAllCPDPoints[$idxLY2]);
      }
   }

   if(date("Y", strtotime("$dtEndDate")) !== date("Y", strtotime("$dtStartDate")))
   {
      for($i=0; $i < 12 ;$i++)
      {

         $dtCurrent = new DateTime("$dtStartDate ");

         $oldDay = $dtCurrent->format("d");
         $dtCurrent->add(new DateInterval("P".$i."M")); // 2016-03-02
         $newDay = $dtCurrent->format("d");

         if($oldDay != $newDay) {
             // Check if the day is changed, if so we skipped to the next month.
             // Substract days to go back to the last day of previous month.
             $dtCurrent->sub(new DateInterval("P" . $newDay . "D"));
         }

         $strMonth = date("M", strtotime($dtCurrent->format("Y-m-d")));
         $strMonthYear = date("M-Y", strtotime($dtCurrent->format("Y-m-d")));

         if( $rpt->Columns[$strMonth]->idx == ($i+1)){
            $rpt->Columns[$strMonth]->name = $strMonthYear;
            $rptCourses->Columns[$strMonth]->name = $strMonthYear;
            $rptTools->Columns[$strMonth]->name = $strMonthYear;
            $rptCPDPoints->Columns[$strMonth]->name = $strMonthYear;
         }


      }
   }
   
   $rpt->Columns[date("M")]->html->style = "background-color: #B4EEB4;";
   $rpt->Columns["Total"]->html->align = "right";
   $rpt->Columns["LY"]->html->align = "right";
   $rpt->Columns["LY2"]->html->align = "right";

   $rptTools->Columns[date("M")]->html->style = "background-color: #B4EEB4;";
   $rptTools->Columns["Total"]->html->align = "right";
   $rptTools->Columns["LY"]->html->align = "right";
   $rptTools->Columns["LY2"]->html->align = "right";

   $rptCourses->Columns[date("M")]->html->style = "background-color: #B4EEB4;";
   $rptCourses->Columns["Course"]->html->style = "width: 25%;";
   $rptCourses->Columns["Total"]->html->align = "right";
   $rptCourses->Columns["LY"]->html->align = "right";
   $rptCourses->Columns["LY2"]->html->align = "right";

   $rptCPDPoints->Columns[date("M")]->html->style = "background-color: #B4EEB4;";
   $rptCPDPoints->Columns["Category"]->html->style = "width: 25%;";
   $rptCPDPoints->Columns["Total"]->html->align = "right";
   $rptCPDPoints->Columns["LY"]->html->align = "right";
   $rptCPDPoints->Columns["LY2"]->html->align = "right";

   //UNSET Columns
   unset($rptCourses->Columns["CourseTypeID"]);
   unset($rptCourses->Columns["intOrder"]);
   unset($rptCPDPoints->Columns["ID"]);
   unset($rptCPDPoints->Columns["intOrder"]);

   //20190923 - Unset LY and LY2 columns - Rachel
   //These columns dont really serve any specific purpose and they are complicating things now that Jean wants to see a report for the exact date she selects
   unset($rpt->Columns["LY"]);
   unset($rpt->Columns["LY2"]);
   unset($rptCourses->Columns["LY"]);
   unset($rptCourses->Columns["LY2"]);
   unset($rptTools->Columns["LY"]);
   unset($rptTools->Columns["LY2"]);
   unset($rptCPDPoints->Columns["LY"]);
   unset($rptCPDPoints->Columns["LY2"]);



   //unset($rpt->Columns["intOrder"]);
   $Output = $rpt->renderTable("Usage Stats Summary Report $strCaption"); 
   $Output .= $BR. $rptTools->renderTable("Usage Stats Tools Report $strCaption"); 
   $Output .= $BR. $rptCourses->renderTable("Courses Usage Stats Report $strCaption"); 
   $Output .= $BR. $rptCPDPoints->renderTable("CPD Events Usage Stats Report $strCaption"); 

?>