<?php
/**
 * This is a common include file which includes
 * the basic files such as connection and commonfunction
 * which can be shared by most of the files.
 *
 * PHP version 5
 *
 * @category Admin_Tool
 * @package  Admin_Tool
 * @author   Aum Infosys <admin@auminfosys.com>
 * @license  ACE
 * @version  CVS:1.0.1
 * @link     http://auminfosys.com/
 * @filesource
 */

/**
 * Query2Report Generator Class
 * Purpose  :  Generating Html, Pdf and Csv Reports from SQL Query
 *
 */


class Query2Report
{
   private $csvReportData;
   private $xlsReportData;
   public $htmlPath;
   public $htmlUrl;
   private $rowColors = array();
   private $pdfWidth = '100%';
   private $remoteApp = "http://services.phpresgroup.org/pdf/public_html/html2ps.php";
   public $reportData;
   public $reportid = "";
   public $rowCount;
   public $reportTitle;
   public $Title;
   public $footer1;
   public $reportData1;


   /**
    * Sets path where the HTML report data will be saved
    * @param absolute path of the html file to be created
    * @return none
    */
   function setHtmlPath($htmlPath)
   {
      $this->htmlPath = $htmlPath;
   }

   /**
    * Sets URL where the html report file will be available
    * @param URL of the html report file
    * @return none
    */
   function setHtmlUrl($htmlUrl)
   {
      $this->htmlUrl = $htmlUrl;
   }

   /**
    * Sets width of the report PDF
    * @param Integer pdf width
    * @return none
    */
   function setPdfWidth($pdfWidth)
   {
      if (is_numeric($pdfWidth)) {
         $this->pdfWidth = $pdfWidth;
      }
   }

   /**
    * Initializes row colors of the report 
    * @param Array row colors
    * @return none
    */
   function initRowColors($rowColors = array())
   {
      $this->rowColors = array_merge(array(), $rowColors);
   }

   /**
    * Generates/prepares report data
    * @param String mySQL query
    * @return none
    */
   function generateReport($query, $title, $footer = false, $field = "", $counter = false, $footer1 = false)
   {
      /*   echo "<pre>";
           print_r($title);
           exit;*/
      $resultSet = dbQuery($query) or die('Can not execute query');
      $this->rowCount = dbRows($resultSet);
      if (dbRows($resultSet)) //if_0
      {
         $data = array();

         $columns = array();

         $footer_array = array();
         $footer_array1 = array();

         $j = 0;

         $female = 0;
         $male = 0;
         //Traversing and storing the result resource
         while ($row = dbArray($resultSet)) {
            foreach ($row as $i => $v) {
               //new code Gender								
               if ($field != "" && $footer == true) {
                  if ($i == "Gender") {
                     if ($i == "Gender" && $v == "F") {
                        $female++;
                     } elseif ($i == "Gender" && $v == "M") {
                        $male++;
                     }
                  }
               }
               //gender code over  						  			  

               if (!is_numeric($i)) {
                  if ($j == 0) {
                     $columns[] = $i;
                  }

                  //echo date("d/m/Y",mktime(0,0,0,0,0,0));
                  $v = str_replace("00/00/0000", "", $v);

                  $data[$j][$i] = $v;

               }
            }

            if ($field != "" && $footer == true) {

               $footer_array[0] = "&nbsp;";
               $footer_array[1] = "<b>Girls :&nbsp;</b><b>$female</b>";
               $footer_array[2] = "<b>Boys :&nbsp;</b>" . "<b>$male</b>";
               $footer_array[3] = "&nbsp;";
               $footer_array[4] = "<b>Total :&nbsp;" . ($female + $male) . "</b>";
            }

            $j++;

         }//end of while	

         //Generating report data table
         $string = '';

         $string .= $this->createTable();
         $string .= $this->createHeaders($title, $counter);
         $string .= $this->createRows($data, $title, $counter, $footer1);
         if ($field != "" && $footer == true) {
            $string .= $this->createFooterRows($footer_array);
         }
         $string .= $this->closeTable();

         if (strlen($this->reportData) > 29) //if report data is already initialized
         {
            $this->reportData = $this->reportData . '<br/>' . $string; //append current data to previous 
         } else {
            $this->reportData = $string;
         }


      }//End of if_0

   }//EO Method

   /**
    * Create Header Row
    * @param none
    * @return none
    */
   function createHeaderRow($header_row_string, $Title = false)
   {
      if ($header_row_string != "") {
         if ($Title == true) {
          
            $string = "<div class='college_name'>" . $_SESSION["VedantnrCollegeName"] . "</div>";
            $string .= "<div class='college_address'>" . $_SESSION['aCollegeAddress'] . "</div>";
            $string .= "<div class='college_address'>
                       
                        Email: " . $_SESSION["VedantnrCollegeEmail"] .
               "&nbsp;&nbsp; 
                        Phone: " . $_SESSION["VedantnrCollegePhone"] . " 
        </div>";
         }
         $string .= "<center><h4>" . $header_row_string . "</h4></center>";
         $this->reportData = $string;
      }
   }//EO Method

   /**
    * Create Gender Total Row
    * @param none
    * @return none
    */
   /* function Footer($footer1=false)
    {
        if($footer1==true){
       $string .= "<div>
                   <div align='left'>Created by <b>".$_SESSION["VedantnrUserName"]."</b></div>
                   <div align='right'>Developed By <b>Aum Imagineering Pvt.Ltd.</b></div>
                </div>";
       $this->reportData1 = $string;
        }
    }
    
      function displayfooter()
    {
          echo($this->reportData1);    	
    }
    
    function createGenderTotal($count){
       $string = "<center><h1>".$count."</h1></center>";
       $this->reportData = $string;
    }*///EO Method


   /**
    * Gets PDF report
    * @param none
    * @return none
    */
   function getPdfReport()
   {
      $htmlUrl = $this->htmlUrl;
      $htmlPath = $this->htmlPath;

      $pdfFileName = str_replace(".html", ".pdf", basename($htmlUrl));
      $pdfFilePath = str_replace(".html", ".pdf", basename($htmlPath));

      //The PDF source is the returned value of method generatePdfReport()      
      echo $this->generatePdfReport();

      header('Pragma: public');
      header('Expires: 0');
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      // Outputting PDF Report
      header('Content-Type: application/pdf');
      // It will be called basename($this->htmlUrl).pdf         
      header("Content-Disposition: attachment; filename=" . $pdfFileName);
   }//EO Method

   /**
    * Gets CSV report
    * @param none
    * @return none
    */
   function getCsvReport()
   {

      // Outputting CSV Report      
      header("Content-Type: application/vnd.ms-excel");
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Content-Disposition: attachment; filename=csvreport.csv");

      echo $this->csvReportData;

   }//EO Method

   /**
    * Gets CSV report
    * @param none
    * @return none
    */
   function getXlsReport()
   {

      // Outputting CSV Report      
      header("Content-Type: application/vnd.ms-excel");
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Content-Disposition: attachment; filename=report/xlsreport.xls");

      echo $this->xlsReportData;

   }//EO Method

   /**
    * display report data to given file
    * @param none
    * @return none
    */
   function displayReportData()
   {

      if (strlen($this->reportData) > 29) //if report data has atleast 1 row 
      {
         echo ($this->reportData);
      } else {
         die('No Report Data Found');
      }
   }//EO Method

   /**
    * Saves report data to given file
    * @param none
    * @return none
    */
   function saveReportData()
   {
      global $CollegeImagePath;

      global $SiteBaseUrl;

      if (strlen($this->reportData) > 29) //if report data has atleast 1 row 
      {

         /*$TempString = '<div id="reportfooter" style="float:left;width:100%;padding-top:5px;"><div id="bottom-left-text"><strong>Created By #USERNAME#</strong></div><div id="bottom-right-text"><strong>Created on #DATE#</strong></div></div>';
                
             $TempString .='<div style="clear:both; height:10px"></div><div class="break"></div>';			
         
             /*$TempString .= '<div id="reportheader">			  
                            <div class="header">	  
                               <div class="headerimg">#SCHOOLLOGO#</div>
                               <div class="headertext">#SCHOOLNAME#
                                  <div class="headertext1">#SCHOOLADDRESS#</div>
                                  <div class="headertext1">#SCHOOLPHONE#</div>
                               </div>	
                            </div>
                            <div style="clear:both; height:10px"></div>			 
                            <div class="headertext2">
                               <div class="header-right-text">#SCHOOLHSC#</div>
                               <div class="header-left-text">#SCHOOLSSC#</div>
                               <div class="header-center-text">#SCHOOLREG#</div>
                            </div>
                            <div style="clear:both; height:5px"></div>
                      </div>';								*/

         /*$TempString .= '<center><h4>#REPORTTITLE#</h4></center>';*/

         $HtmlCode = str_replace('<div class="break"></div>', $TempString, $this->reportData);

         $this->reportData = $HtmlCode;

         if ($_SESSION['VedantnrModuleFolder'] == "library") {
            $filename = "../_report-template.html";
         } else {
            $filename = "_report-template.html";
         }


         $strHtmlFileName = $filename;

         $fso = fopen($strHtmlFileName, "r");

         $strHtmlContent = "";

         while (!feof($fso))

            $strHtmlContent .= fgets($fso, 4096);

         fclose($fso);

         //Report Data			
         $strTmpVar = trim($this->reportData);

         $NAME = $_SESSION["VedantnrUserName"];
         if ($_SESSION['aYearId'] == '1' || $_SESSION['aYearId'] == '2') {
           // $YEAR = '2021-22';
            $strHtmlContent = str_replace("#YEAR#", $YEAR, $strHtmlContent);
      
            $YEAR = $_SESSION["VedantnrYearName"];
            $strHtmlContent = str_replace("#YEAR#", $YEAR, $strHtmlContent);
         }
         $strHtmlContent = str_replace("#NAME#", $NAME, $strHtmlContent);

         $strHtmlContent = str_replace("#REPORTDATA#", $strTmpVar, $strHtmlContent);

         //Logo

         /*if($_SESSION["VedantnrCollegeLogo"]!="" && $_SESSION["VedantnrCollegeLogo"]!="default.gif")
               {
                  $strTmpVar = '<img src="../'.$CollegeImagePath.$_SESSION["VedantnrCollegeLogo"].'" alt="'.$_SESSION["aSchoolName"].'"  title="'.$_SESSION["aSchoolName"].'"/>';						
               //echo "sitebaseurl===>".$SiteBaseUrl;
                  $strTmpVar = str_replace("../",$SiteBaseUrl,$strTmpVar);
               
                  $strHtmlContent = str_replace("#SCHOOLLOGO#", $strTmpVar, $strHtmlContent);
               }
               else
               {
                  $strHtmlContent = str_replace("#SCHOOLLOGO#", "", $strHtmlContent);
               }
               
               //School Name
               $strTmpVar = trim($_SESSION["aSchoolName"]);
            
               $strHtmlContent = str_replace("#SCHOOLNAME#", $strTmpVar, $strHtmlContent);
                        
               //School Address
               $strTmpVar = trim(($_SESSION["aSchoolAddress"]));
               
               $strTmpVar = str_replace("<br/>"," ",$strTmpVar);
            
               $strHtmlContent = str_replace("#SCHOOLADDRESS#", $strTmpVar, $strHtmlContent);
                        
               //School Phone
               $strTmpVar = trim(($_SESSION["aSchoolPhone"]));
            
               if($strTmpVar!="")
                  $strHtmlContent = str_replace("#SCHOOLPHONE#", "Phone: ".$strTmpVar, $strHtmlContent);
               else
                  $strHtmlContent = str_replace("#SCHOOLPHONE#", $strTmpVar, $strHtmlContent);
               
               
               //School SSC
               $strTmpVar = trim(($_SESSION["aSchoolSSC"]));
            
               if($strTmpVar!="")
                  $strHtmlContent = str_replace("#SCHOOLSSC#", "SSC Index No: ".$strTmpVar, $strHtmlContent);
               else
                  $strHtmlContent = str_replace("#SCHOOLSSC#", $strTmpVar, $strHtmlContent);
                  


               //School HSC
               $strTmpVar = trim(($_SESSION["aSchoolHSC"]));
         
               if($strTmpVar!="")	
                  $strHtmlContent = str_replace("#SCHOOLHSC#", "HSC Index No: ".$strTmpVar, $strHtmlContent);
               else
                  $strHtmlContent = str_replace("#SCHOOLHSC#", $strTmpVar, $strHtmlContent);			
               
               
               //School REG
               $strTmpVar = trim(($_SESSION["aSchoolREG"]));
            
               if($strTmpVar!="")
                  $strHtmlContent = str_replace("#SCHOOLREG#", "School Reg No: ".$strTmpVar, $strHtmlContent);
               else
                  $strHtmlContent = str_replace("#SCHOOLREG#", $strTmpVar, $strHtmlContent);				
               
         
               //Title
               $strTmpVar = trim($this->reportTitle);
            
               $strHtmlContent = str_replace("#REPORTTITLE#", $strTmpVar, $strHtmlContent);
                        
               //Username			
               $strTmpVar = trim($_SESSION["VedantnrUserName"]);
            
               $strHtmlContent = str_replace("#USERNAME#", $strTmpVar, $strHtmlContent);	
                        
               //DATE
               $strTmpVar = date("d, F Y");
            
               $strHtmlContent = str_replace("#DATE#", $strTmpVar, $strHtmlContent);	
                        
               $strHtmlContent = str_replace('#f5f5f5','',$strHtmlContent);
               
               $strHtmlContent = str_replace('#eeeeee','',$strHtmlContent);*/

         $fp = fopen($this->htmlPath, "w") or die("Can not open file " . $this->htmlPath);
         fwrite($fp, $strHtmlContent) or die("Can not write to file " . $this->htmlPath);
         ;
         fclose($fp);
      } else {
         die('No Report Data Found');
      }

   }//EO Method

   /**
    * Generates PDF report from remote application
    * @param none
    * @return report data on PDF mode
    */
   function generatePdfReport()
   {
      $remoteApp = $this->remoteApp;
      $waterMarkHtml = "";//change it according to your need 
      $htmlUrl = urlencode("http://neemaschool.in/school/report/student-general-register-report.html");
      $pdfWidth = $this->pdfWidth;

      $requestString = "process_mode=single&URL=$htmlUrl&pixels=$pdfWidth&scalepoints=1&renderimages=1&renderlinks=1&renderfields=1&media=Letter&cssmedia=screen&leftmargin=10&rightmargin=10&topmargin=15&bottommargin=15&encoding=&headerhtml=&footerhtml=&watermarkhtml=$waterMarkHtml&method=fpdf&pdfversion=1.3&output=0&convert=Convert+File";

      //Init the curl session 
      $ch = curl_init();
      // set the post-to url (do not include the ?query+string here!) 
      curl_setopt($ch, CURLOPT_URL, $remoteApp);
      // Header control 
      curl_setopt($ch, CURLOPT_HEADER, 0);
      //Tell it to make a POST, not a GET 
      curl_setopt($ch, CURLOPT_POST, 1);
      // Put the query string here starting without "?"
      curl_setopt($ch, CURLOPT_POSTFIELDS, $requestString);
      // This allows the output to be set into a variable
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
      // execute the curl session and return the output to a variable $response        
      $response = curl_exec($ch);
      // Close the curl session 
      curl_close($ch);

      return $response;
   }

   /**
    * Generates XLS report from query
    * @param MySQL Query
    * @return none
    */
   function generateXlsReport($query)
   {

      $resultSet = dbQuery($query) or die('Can not execute query');

      if (dbNumRows($resultSet)) {
         $data = array();

         $columns = array();

         $j = 0;

         //Traversing and storing the result resource
         while ($row = dbArray($resultSet)) {
            foreach ($row as $i => $v) {
               if (!is_numeric($i)) {
                  if ($j == 0) {
                     $columns[] = $i;
                  }

                  $data[$j][$i] = $v;
               }
            }
            $j++;

         }//end of while	

         //Generating report data
         $string = '';
         $string .= $this->createXlsHeaders($columns);
         $string .= $this->createXlsRows($data);

         $this->xlsReportData = $string;

      }//End of if_0

   }//EO Method


   /**
    * Generates CSV report from query
    * @param MySQL Query
    * @return none
    */
   function generateCsvReport($query)
   {

      $resultSet = dbQuery($query) or die('Can not execute query');

      if (dbNumRows($resultSet)) {
         $data = array();

         $columns = array();

         $j = 0;

         //Traversing and storing the result resource
         while ($row = dbArray($resultSet)) {
            foreach ($row as $i => $v) {
               if (!is_numeric($i)) {
                  if ($j == 0) {
                     $columns[] = $i;
                  }

                  $data[$j][$i] = $v;
               }
            }
            $j++;

         }//end of while	

         //Generating report data
         $string = '';
         $string .= $this->createCsvHeaders($columns);
         $string .= $this->createCsvRows($data);

         $this->csvReportData = $string;

      }//End of if_0

   }//EO Method

   /**
    * Debugs/dumps data
    * @param $dump
    * @return none
    */
   function dBug($dump)
   {
      echo '<pre>';
      print_r($dump);
      echo '</pre>';
   }

   /** 
    * Creates table
    * @param none
    * @return String <table ...>
    */
   function createTable()
   {

      if ($this->reportid != "")
         $id = ' id="' . $this->reportid . '" ';
      else
         $id = '';

      return '<table class="table-report" border="1" cellpadding="0" cellspacing="0" width="100%">';
   }

   /** 
    * Closes table
    * @param none
    * @return String </table>
    */
   function closeTable()
   {
      return '</table>';
   }

   /** 
    * Creates table headers (TH)
    * @param Array report columns
    * @return String TR & THs with data.
    */
   function createHeaders($array, $counter)
   {
      $string = '';
      $string .= '<thead><tr>';

      if ($counter == true)
         $string .= "<th align=\"center\" >Sr.</th>";

      for ($i = 0; $i < sizeof($array); $i++) {

         $strAttributes = $array[$i];

         if ($strAttributes != "") {
            $ary = explode(",", $strAttributes);

            if (count($ary) > 0) {
               $strname = $ary[0];
               $stralign = $ary[1];
               $strwidth = $ary[2];
            } else {
               $strname = $strAttributes;
            }

         }
         $string .= "<th align=\"$stralign\" width=\"$strwidth\">$strname</th>";
      }
      $string .= '</tr></thead>';

      return $string;

   }//EO Method


   /** 
    * Creates table headers (TH)
    * @param Array report columns
    * @return String TR & THs with data.
    */
   function createXlsHeaders($array)
   {
      $string = '';

      for ($i = 0; $i < sizeof($array); $i++) {
         if ($i == 0) {
            $string .= "$array[$i]";
         } else {
            $string .= ",$array[$i]";
         }
      }

      return $string . "\n";

   }//EO Method

   /** 
    * Creates table headers (TH)
    * @param Array report columns
    * @return String TR & THs with data.
    */
   function createCsvHeaders($array)
   {
      $string = '';

      for ($i = 0; $i < sizeof($array); $i++) {
         if ($i == 0) {
            $string .= "$array[$i]";
         } else {
            $string .= ",$array[$i]";
         }
      }

      return $string . "\n";

   }//EO Method

   /** 
    * Creates table rows (TR)
    * @param Array report row
    * @return String TRs
    */
   function createRows($array, $title, $counter, $footer1)
   {
      $string = '';

      if (!count($this->rowColors)) {
         array_unshift($this->rowColors, '#f5f5f5', '#eeeeee');
      }

      $numColors = count($this->rowColors);

      for ($i = 0; $i < sizeof($array); $i++) {
         //$string .= "<tr bgcolor=".$this->rowColors[$i%$numColors].">";	      

         $string .= "<tr>";

         if ($counter == true)
            $string .= "<td style=\"text-align:center\">" . ($i + 1) . "</td>";

         $j = 0;

         foreach ($array[$i] as $v) {
            $stralign = "";
            $strwidth = "";

            $strAttributes = $title[$j];

            if ($strAttributes != "") {
               $ary = explode(",", $strAttributes);

               if (count($ary) > 0) {
                  $stralign = $ary[1];
                  $strwidth = $ary[2];
               }
            }
            if ($v == "") {
               $v = '&nbsp;';
            }
            $string .= "<td align=\"$stralign\" width=\"$strwidth\">$v</td>";
            $j++;
         }
         $string .= '</tr>';
      }
      $c = $j + 1;
      if ($footer1 == true) {
         $string .= "<tr>
                    <td colspan=" . $c . ">
                    <table class='table-report-footer' cellpadding='0' cellspacing='0' width='100%'>
                        <tr>
                            <td align='left' style='border:solid 0px red; padding:0px;' width='70%'>Created by <b>" . $_SESSION["VedantnrUserName"] . "</b> </td>
                            <td align='right' style='border:solid 0px red; padding:0px;' width='30%'>Developed By <b>Aum Imagineering Pvt.Ltd.</b></td>
                        </tr>
                    </table>
                    </td>        
					</tr> ";
      }
      return $string;

   }//EO Method


   /** 
    * Creates table rows (TR)
    * @param Array report row
    * @return String TRs
    */
   function createFooterRows($array)
   {
      $string = '';

      if (!count($this->rowColors)) {
         array_unshift($this->rowColors, '#f5f5f5', '#eeeeee');
      }

      $numColors = count($this->rowColors);

      $string .= "<tr colspan='$numColors'><td>&nbsp;</td></tr>";

      $string .= "<tr bgcolor='#CCCCCC'>";

      for ($i = 0; $i < sizeof($array); $i++) {
         $string .= "<td>" . $array[$i] . "</td>";
      }

      $string .= '</tr>';

      return $string;

   }//EO Method


   /** 
    * Creates XLS rows
    * @param Array report rows
    * @return String XLS rows
    */
   function createXlsRows($array)
   {
      $string = '';

      $cnt = 0;

      for ($i = 0; $i < sizeof($array); $i++) {

         $cnt = 0;

         foreach ($array[$i] as $v) {

            if ($cnt == 0) {
               $string .= $this->filterText($v);
            } else {
               $string .= ',' . $this->filterText($v);
            }

            $cnt++;
         }

         $string .= "\n";

      }

      return $string;

   }//EO Method


   /** 
    * Creates CSV rows
    * @param Array report rows
    * @return String CSV rows
    */
   function createCsvRows($array)
   {
      $string = '';

      $cnt = 0;

      for ($i = 0; $i < sizeof($array); $i++) {

         $cnt = 0;

         foreach ($array[$i] as $v) {

            if ($cnt == 0) {
               $string .= $this->filterText($v);
            } else {
               $string .= ',' . $this->filterText($v);
            }

            $cnt++;
         }

         $string .= "\n";

      }

      return $string;

   }//EO Method

   /**
    * Prepares CSV compatible data 
    * @param data to be filtered
    * @return filtered data
    */
   function filterText($value)
   {

      if (substr($value, 0, 1) == ',' || strpos($value, ',', 1)) {
         $value = '"' . str_replace('"', '""', $value) . '"';
         return $value;
      }

      return $value;

   }//EO Method   

}//EO Class
?>