<?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;   

   
   /**
   * 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)
   {
      
      $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
		 
		 if(count($title)==0)
		 	$title = $columns;
			
         $string = '';         
         $string .= $this->createTable();
         $string .= $this->createHeaders($title,$counter);
		 $string .= $this->createRows($data,$title,$counter);
		 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){
		$string = "<center><h4>".$header_row_string."</h4></center>";
		$this->reportData = $string;
   }//EO Method
   
   /**
   * Create Gender Total Row
   * @param none
   * @return none
   */ 
   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()
   {   	 	  
      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 ; 
	  
	  		$filename="_report-template.html" ;
		
			$strHtmlFileName = $filename;
		
			$fso = fopen($strHtmlFileName, "r");
		
			$strHtmlContent = "";
		
			while (!feof($fso) ) 
		
				$strHtmlContent .= fgets($fso, 4096);
		
			fclose($fso);
			
			if($strHtmlContent!="") {
			
			//Report Data			
			$strTmpVar = trim($this->reportData);
		
			$strHtmlContent = str_replace("#REPORTDATA#", $strTmpVar, $strHtmlContent);	
						
			//Logo
			
			if($_SESSION["aSchoolPhoto"]!="" && $_SESSION["aSchoolPhoto"]!="default.gif")
			{
				$strTmpVar = '<img src="'.$CompanyImagePath.$_SESSION["aSchoolPhoto"].'" alt="'.$_SESSION["aSchoolName"].'"  title="'.$_SESSION["aSchoolName"].'"/>';						
			
				$strTmpVar = str_replace("../",$SiteBaseUrl,$strTmpVar);
			
				$strHtmlContent = str_replace("#SCHOOLLOGO#", $strTmpVar, $strHtmlContent);
			}
			else
			{
				$strHtmlContent = str_replace("#SCHOOLLOGO#", "", $strHtmlContent);
			}
			
			//School Name
			$strTmpVar = trim(strtoupper($_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["cUserName"]);
		
			$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(dbRows($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(dbRows($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="report"'.$id.'>';	
   }
   
   /** 
   * 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\" width=\"4%\">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)
   {
   	 $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];				
				 }				 
      		}
		  	$string .= "<td align=\"$stralign\" width=\"$strwidth\">$v</td>";	
			$j++;
        }      
        $string .= '</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
?>