<?php
//20190421 - Add BAT Report - Rachel

   include_once("_nemo.list.cls.php");

   //ini
   $rptBAT = new NemoList(null);

   $rptBAT->isSelectable = 0;
   $rptBAT->isSortable = 0;

   $strFilename = "BAT_Report_". date("Ymd");
   $Where = "";

   $dtStartDate = date('Y-m-d', strtotime($page->Filters[frStartDate]->html->value));
   $dtEndDate = date('Y-m-d', strtotime($page->Filters[frEndDate]->html->value));

   $strCaption = $dtStartDate ." to ". $dtEndDate;

   if($page->Filters[frIgnoreDates]->html->checked != "checked")
   {
      $Where .= " AND (DATE(strDate) BETWEEN '". $page->Filters[frStartDate]->html->value ."' AND '". $page->Filters[frEndDate]->html->value ."')";
   }

   //Main Query
   $rptBAT->ListSQL("
   SELECT tblPillar.PillarID, tblCluster.ClusterID, tblQuestion.QuestionID, tblOption.OptionID, tblPillar.intOrder AS pOrder, tblCluster.intOrder AS cOrder, tblQQ.intOrder AS qOrder, tblOption.intOrder AS oOrder, tblQuestionnaire.strQuestionnaire AS 'Questionnaire', tblPillar.strPillar AS 'Pillar', tblCluster.strCluster AS 'Cluster', tblQuestion.strBusinessFunction AS 'Business Function', tblQuestion.strQuestion AS 'Question', vdValueDriver.ID AS blnVD, vdTCF.QuestionID AS blnTCF, tblOption.strGridOption, tblQuestion.intScoreMaxHealth AS 'Max Score', tblQuestion.strBestPractice AS 'Best Practice', tblOption.strOption AS 'Option', tblOption.intScoreHealth AS 'Score', tblCluster.strBestPractice AS cBestPractice, 0 as '#Responses', 0 as 'Response%'
   FROM vdTCF RIGHT JOIN (vdValueDriver RIGHT JOIN (tblOption INNER JOIN ((tblQuestionnaire INNER JOIN ((tblQQ INNER JOIN tblCluster ON tblQQ.refClusterID = tblCluster.ClusterID) INNER JOIN tblPillar ON tblCluster.refPillarID = tblPillar.PillarID) ON tblQuestionnaire.QuestionnaireID = tblQQ.refQuestionnaireID) INNER JOIN tblQuestion ON tblQQ.refQuestionID = tblQuestion.QuestionID) ON tblOption.refQuestionID = tblQuestion.QuestionID) ON vdValueDriver.QuestionID = tblQuestion.QuestionID) ON vdTCF.QuestionID = tblQuestion.QuestionID
   WHERE (((tblQuestionnaire.QuestionnaireID)<>0) AND ((tblQuestionnaire.blnActive)=1) AND ((tblQuestionnaire.strQuestionnaire) != 'BAT: Core Template') AND ((tblPillar.blnActive)=1) AND ((tblCluster.blnActive)=1) AND ((tblQuestion.blnActive)=1) AND ((tblOption.blnActive)=1)) 
   ORDER BY tblPillar.intOrder, tblCluster.intOrder, tblQQ.intOrder, tblOption.intGridOrder, tblOption.intOrder, tblQuestionnaire.strQuestionnaire, tblPillar.strPillar, tblCluster.strCluster, tblQuestion.strBusinessFunction, tblQuestion.strQuestion, tblOption.strGridOption, tblOption.strOption

   ", 0);

   //Subquery
   //Get total responses for each Question-Option
   $rst = $xdb->doQuery("SELECT COUNT(*) AS responses, `PillarID`, `ClusterID`, `QuestionID`, `OptionID`
   FROM `dwfBat` 
   WHERE OptionID != 0 $Where
   GROUP BY QuestionID, OptionID
   ORDER BY `QuestionID`  DESC ");

   while($row = $xdb->fetch_object($rst))
   {
      //Total Responses for each Question-Option
      $arrBAT[$row->QuestionID][$row->OptionID] = $row->responses;

      //Total responses for each question
      $arrBATQuestionTotal[$row->QuestionID] += $row->responses;
   }

   //Add parent row for each result set, i.e, the row with the total number of responses for each Question [Add Question Row]
   $Counter = 0;
   $prevQuestionID = 0;
   foreach ($rptBAT->Data as $idxRow => $row) 
   {
      if($prevQuestionID != $row["QuestionID"])
      {
         $arrTotalRow[0]["RowShift"] = ($idxRow+$Counter);
         $arrTotalRow[0]["PillarID"] = $row["PillarID"];
         $arrTotalRow[0]["ClusterID"] = $row["ClusterID"];
         $arrTotalRow[0]["QuestionID"] = $row["QuestionID"];
         $arrTotalRow[0]["Questionnaire"] = "<b>" .$row["Questionnaire"] ."</b>";
         $arrTotalRow[0]["Pillar"] = "<b>" .$row["Pillar"] ."</b>";
         $arrTotalRow[0]["Cluster"] = "<b>" .$row["Cluster"] ."</b>" ;
         $arrTotalRow[0]["Business Function"] = "<b>" .$row["Business Function"]."</b>";
         $arrTotalRow[0]["Max Score"] = "<b>" .$row["Max Score"] ."</b>";
         $arrTotalRow[0]["Best Practice"] = "<b>" . $row["Best Practice"] ."</b>";
         $arrTotalRow[0]["Question"] = "<b>" .$row["Question"]."</b>" ;
         $arrTotalRow[0]["Option"] = "";
         $arrTotalRow[0]["#Responses"] = "0";
         $arrTotalRow[0]["Response%"] = "<b>100%</b>";

         $arrParent[] = $arrTotalRow;

         unset($arrTotalRow);
         $prevQuestionID = $row["QuestionID"];
         $Counter++;
      }
      
      //write responses for each row [Option Row]
      if($arrBAT[$row["QuestionID"]][$row["OptionID"]]){
         $rptBAT->Data[$idxRow]["#Responses"] = $arrBAT[$row["QuestionID"]][$row["OptionID"]];
      }
   }
   
   //Add the parent rows to the report [Question Rows]
   foreach ($arrParent as $key => $row) {
      array_splice($rptBAT->Data, $row[0]["RowShift"], 0, $row); //Adds the row in between existing rows 
   }

   //write response percentage for each row [Option Rows]
   foreach ($rptBAT->Data as $idxRow => $row) 
   {
      if($arrBAT[$row["QuestionID"]][$row["OptionID"]]){
         $rptBAT->Data[$idxRow]["Response%"] = round((($arrBAT[$row["QuestionID"]][$row["OptionID"]] / $arrBATQuestionTotal[$row["QuestionID"]]) ) * 100, 2) . "%";
      }

      //Write number of responses for total row [Question Row]
      if($rptBAT->Data[$idxRow]["Option"] == "")
      {
         $rptBAT->Data[$idxRow]["#Responses"] = "<b>" .($arrBATQuestionTotal[$row["QuestionID"]] == 0 ? 0 : $arrBATQuestionTotal[$row["QuestionID"]]) . "</b>";

      }else{ //hide questionnaire,question ->to be read from total row[these are the same for each Question-Option row]
         $rptBAT->Data[$idxRow]["Questionnaire"] = "";
         $rptBAT->Data[$idxRow]["Pillar"] = "";
         $rptBAT->Data[$idxRow]["Cluster"] = "";
         $rptBAT->Data[$idxRow]["Business Function"] = "";
         $rptBAT->Data[$idxRow]["Question"] = "";
         $rptBAT->Data[$idxRow]["Max Score"] = "";
         $rptBAT->Data[$idxRow]["Best Practice"] = "";
      }
   }

   //Column Format
   $rptBAT->Columns["Max Score"]->html->align = "right";
   $rptBAT->Columns["Score"]->html->align = "right";
   $rptBAT->Columns["#Responses"]->html->align = "right";
   $rptBAT->Columns["Response%"]->html->align = "right";

   //UNSET Columns
   unset($rptBAT->Columns["cBestPractice"]);
   unset($rptBAT->Columns["PillarID"]);
   unset($rptBAT->Columns["ClusterID"]);
   unset($rptBAT->Columns["QuestionID"]);
   unset($rptBAT->Columns["OptionID"]);
   unset($rptBAT->Columns["pOrder"]);
   unset($rptBAT->Columns["cOrder"]);
   unset($rptBAT->Columns["qOrder"]);
   unset($rptBAT->Columns["oOrder"]);
   unset($rptBAT->Columns["blnVD"]);
   unset($rptBAT->Columns["blnTCF"]);
   unset($rptBAT->Columns["strGridOption"]);

   $Output = $BR. $rptBAT->renderTable("BAT Report $strCaption"); 

?>