package dataProfileWebSite;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import dataWarehousingTools.LogFile;
import dataWarehousingTools.Configuration;
import dataWarehousingTools.DashboardFile;


public class Frequencies 
{
    public Frequencies
    (
    	Configuration configuration, 
    	Connection connection, 
    	LogFile logFile, 
    	int databaseLinkId,
    	String databaseName,
    	String schemaName,
    	String tableName,
    	int rowCount,
    	int distinctValueCount,
    	int tableProfileId,
    	int columnProfileId, 
    	String columnName,
    	Timestamp dateTimeStatisticsCollected
    )
    {
    	Statement statementTop = null;
    	Statement statementBottom = null;
    	Statement statementAll = null;
    	Statement statementSample = null;
    	
    	ResultSet resultSetTop = null;
    	ResultSet resultSetBottom = null;
    	ResultSet resultSetAll = null;
    	ResultSet resultSetSample = null;
    	
		String displayValue;
		int frequency;
		DashboardFile frequenciesFile;
		XmlElement xmlElement;
		Calendar today;
		DateFormat longDateFormat;
		DecimalFormat integerFormat;
		MaskIfPan maskIfPan;
		int numberOfFrequencies;
		int lineCount;

    	today = Calendar.getInstance();
    	longDateFormat = new SimpleDateFormat("EEE, dd-MMM-yyyy HH:mm:ss");
        longDateFormat.format(today.getTime());
		integerFormat = new DecimalFormat("###,###,###,###,###");
		maskIfPan = new MaskIfPan();
		
		frequenciesFile = new DashboardFile
		(
			configuration.getHtmlFileLocation() +
			"Frequency" + columnProfileId + ".htm"
		);
		frequenciesFile.PutLine("<!DOCTYPE html>");
		frequenciesFile.PutLine("<head>");
		frequenciesFile.PutLine("  <meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"/>");  
		frequenciesFile.PutLine
	    (
	    	"  <link rel=\"stylesheet\" type=\"text/css\" href=\"" +
	    	configuration.getLinkToStylesheets() +
	    	"style.css\"/>"
	    );
	    
	    xmlElement = new XmlElement(2, "title", "", "Frequencies for Column: " + columnName);
	    frequenciesFile.PutLine(xmlElement.getXmlElement());

	    frequenciesFile.PutLine("    <table>");
	    frequenciesFile.PutLine("      <tr>");
	    frequenciesFile.PutLine("        <th>Database</th>");
	    frequenciesFile.PutLine("        <th>Schema</th>");
	    frequenciesFile.PutLine("        <th>Table</th>");
	    frequenciesFile.PutLine("        <th>Row Count</th>");
	    frequenciesFile.PutLine("        <th>Column</th>");
	    frequenciesFile.PutLine("        <th>Date Statistics Collected</th>");
	    frequenciesFile.PutLine("      </tr>");
	    frequenciesFile.PutLine("      <tr>");
	    frequenciesFile.PutLine("        <td>" + databaseName + "</td>");
	    frequenciesFile.PutLine("        <td>" + schemaName + "</td>");
	    frequenciesFile.PutLine("        <td>" + tableName + "</td>");
	    frequenciesFile.PutLine("        <td>" + integerFormat.format(rowCount) + "</td>");
	    frequenciesFile.PutLine("        <td>" + columnName + "</td>");
	    frequenciesFile.PutLine("        <td>" + longDateFormat.format(dateTimeStatisticsCollected.getTime()) + "</td>");
	    frequenciesFile.PutLine("      </tr>");
	    frequenciesFile.PutLine("    </table>");
	    
	    xmlElement = new XmlElement
	    (
	    	6, 
	    	"a", 
	    	"href=\"" +
	    	"index.html\"",
	    	"Back to List of Databases"
	    );
	    frequenciesFile.PutLine("<p>" + xmlElement.getXmlElement() + "</p>");

	    xmlElement = new XmlElement
	    (
	    	6, 
	    	"a", 
	    	"href=\"" +
	    	"Schema" + databaseLinkId + ".htm\"",
	    	"Back to List of Tables"
	    );
	    frequenciesFile.PutLine("<p>" + xmlElement.getXmlElement() + "</p>");
	    
	    xmlElement = new XmlElement
	    (
	    	6, 
	    	"a", 
	    	"href=\"" +
	    	"TableProfile" + tableProfileId + ".htm\"",
	    	"Back to List of Columns"
	    );
	    frequenciesFile.PutLine("<p>" + xmlElement.getXmlElement() + "</p>");

	    xmlElement = new XmlElement(4, "h4", "", "Published: " + longDateFormat.format(today.getTime()) + " GMT");
	    frequenciesFile.PutLine(xmlElement.getXmlElement());
		
	    frequenciesFile.PutLine("</head>");
	    frequenciesFile.PutLine("  <body>");
		

	    
//  Use code from PrintDataProfile to manage number of frequency records displayed	 
	    numberOfFrequencies = 0;
		try
		{
			statementAll = connection.createStatement();
		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}

		try
		{
			resultSetAll = statementAll.executeQuery
			(
				"select " +
			        "count(*) as number_of_frequencies " +
				"from " +
			    "(" +
					"select distinct " +   	
						"display_value, " +
						"frequency " +
					"from " +
						"frequency " +
					"where " +
                    	"column_profile_id = " + columnProfileId +
                ") x"
            );
		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}
		
		try 
		{
			while (resultSetAll.next())
			{
				numberOfFrequencies = resultSetAll.getInt("number_of_frequencies");
			}
		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}

		try 
		{
			resultSetAll.close();
		} 
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}
		
		try 
		{
			statementAll.close();
		} 
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}
		
		if (numberOfFrequencies == 0)
		{
		    frequenciesFile.PutLine("<p><b>No Frequencies Available</b></p>");			
		}	
		else if (numberOfFrequencies <= 30)
		{
		    frequenciesFile.PutLine("<p><b>All Frequencies</b></p>");
		    
		    frequenciesFile.PutLine("    <table>");
		    frequenciesFile.PutLine("      <tr>");
		    frequenciesFile.PutLine("        <th style=\"text-align:left;\">Value</th>");
		    frequenciesFile.PutLine("        <th style=\"text-align:right;\">Frequency</th>");
		    frequenciesFile.PutLine("      </tr>");
		    
			try
			{
				statementAll = connection.createStatement();
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
	
			try
			{
				resultSetAll = statementAll.executeQuery
				(
					"select distinct " +   	
						"display_value, " +
						"frequency " +
					"from " +
	                    "frequency " +
					"where " +
	                    "column_profile_id = " + columnProfileId +
	                "order by " +
	                    "frequency desc "
	            );
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				while (resultSetAll.next())
				{
					frequency = resultSetAll.getInt("frequency");
					displayValue = new String(resultSetAll.getString("display_value"));
									
				    frequenciesFile.PutLine("      <tr>");
				    frequenciesFile.PutLine("        <td style=\"text-align:left;\">" + maskIfPan.mask(displayValue) + "</td> ");
				    frequenciesFile.PutLine("        <td style=\"text-align:right;\">" + integerFormat.format(frequency) + "</td>");
				    
				    frequenciesFile.PutLine("      </tr>");
				}
			} 
			catch (SQLException sqle) 
			{
				logFile.logMessage(sqle.toString());
			}

			frequenciesFile.PutLine("      </table>");

			try 
			{
				resultSetAll.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				statementAll.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}		
		}
		else if (distinctValueCount == rowCount)
		{
		    frequenciesFile.PutLine("<p><b>All values are different; this is a sample</b></p>");
	    
		    frequenciesFile.PutLine("    <table>");
		    frequenciesFile.PutLine("      <tr>");
		    frequenciesFile.PutLine("        <th style=\"text-align:left;\">Value</th>");
		    frequenciesFile.PutLine("        <th style=\"text-align:right;\">Frequency</th>");
		    frequenciesFile.PutLine("      </tr>");
		    
			try
			{
				statementSample = connection.createStatement();
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
	
			try
			{
				resultSetSample = statementSample.executeQuery
				(
					"select distinct " +   	
						"display_value, " +
						"frequency " +
					"from " +
	                    "frequency " +
					"where " +
	                    "column_profile_id = " + columnProfileId +
	                "order by " +
	                    "frequency desc "
	            );
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}

			lineCount = 0;
			try 
			{
				while ((resultSetSample.next()) && (lineCount <= 30))
				{
					frequency = resultSetSample.getInt("frequency");
					displayValue = new String(resultSetSample.getString("display_value"));
									
				    frequenciesFile.PutLine("      <tr>");
				    frequenciesFile.PutLine("        <td style=\"text-align:left;\">" + maskIfPan.mask(displayValue) + "</td> ");
				    frequenciesFile.PutLine("        <td style=\"text-align:right;\">" + integerFormat.format(frequency) + "</td>");
				    
				    frequenciesFile.PutLine("      </tr>");
				    lineCount++;
				}
			} 
			catch (SQLException sqle) 
			{
				logFile.logMessage(sqle.toString());
			}

			frequenciesFile.PutLine("      </table>");

			try 
			{
				resultSetSample.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				statementSample.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}		
		}		
		else
		{
		    frequenciesFile.PutLine("<p><b>Most Popular Values</b></p>");
		    
		    frequenciesFile.PutLine("    <table>");
		    frequenciesFile.PutLine("      <tr>");
		    frequenciesFile.PutLine("        <th style=\"text-align:left;\">Value</th>");
		    frequenciesFile.PutLine("        <th style=\"text-align:right;\">Frequency</th>");
		    frequenciesFile.PutLine("      </tr>");

			try
			{
				statementTop = connection.createStatement();
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
	
			try
			{
				resultSetTop = statementTop.executeQuery
				(
					"select distinct " +   	
						"display_value, " +
						"frequency " +
					"from " +
	                    "frequency " +
					"where " +
	                    "column_profile_id = " + columnProfileId +
	                "order by " +
	                    "frequency desc " +
	                "limit 15"
	            );
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				while (resultSetTop.next())
				{
					frequency = resultSetTop.getInt("frequency");
					displayValue = new String(resultSetTop.getString("display_value"));
									
				    frequenciesFile.PutLine("      <tr>");
				    frequenciesFile.PutLine("        <td style=\"text-align:left;\">" + maskIfPan.mask(displayValue) + "</td> ");
				    frequenciesFile.PutLine("        <td style=\"text-align:right;\">" + integerFormat.format(frequency) + "</td>");
				    
				    frequenciesFile.PutLine("      </tr>");
				}
			} 
			catch (SQLException sqle) 
			{
				logFile.logMessage(sqle.toString());
			}

		    frequenciesFile.PutLine("      </table>");			
			
			try 
			{
				resultSetTop.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				statementTop.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}		
						
		    frequenciesFile.PutLine("<p><b>Least Popular Values</b></p>");
		    
		    frequenciesFile.PutLine("    <table>");
		    frequenciesFile.PutLine("      <tr>");
		    frequenciesFile.PutLine("        <th style=\"text-align:left;\">Value</th>");
		    frequenciesFile.PutLine("        <th style=\"text-align:right;\">Frequency</th>");
		    frequenciesFile.PutLine("      </tr>");

			try
			{
				statementBottom = connection.createStatement();
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
	
			try
			{
				resultSetBottom = statementBottom.executeQuery
				(
					"select distinct " +   	
						"display_value, " +
						"frequency " +
					"from " +
	                    "frequency " +
					"where " +
	                    "column_profile_id = " + columnProfileId +
	                "order by " +
	                    "frequency asc " +
	                "limit 15"
	            );
			}
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				while (resultSetBottom.next())
				{
					frequency = resultSetBottom.getInt("frequency");
					displayValue = new String(resultSetBottom.getString("display_value"));
									
				    frequenciesFile.PutLine("      <tr>");
				    frequenciesFile.PutLine("        <td style=\"text-align:left;\">" + maskIfPan.mask(displayValue) + "</td> ");
				    frequenciesFile.PutLine("        <td style=\"text-align:right;\">" + integerFormat.format(frequency) + "</td>");
				    
				    frequenciesFile.PutLine("      </tr>");
				}
			} 
			catch (SQLException sqle) 
			{
				logFile.logMessage(sqle.toString());
			}

			frequenciesFile.PutLine("      </table>");

			try 
			{
				resultSetBottom.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}
			
			try 
			{
				statementBottom.close();
			} 
			catch(SQLException sqle)
			{
				logFile.logMessage(sqle.toString());
			}		
		}

	    xmlElement = new XmlElement(4, "h4", "", "Published: " + longDateFormat.format(today.getTime()) + " GMT");
	    frequenciesFile.PutLine(xmlElement.getXmlElement());
		
		frequenciesFile.PutLine("  </body>");
		
		frequenciesFile.Close();
    }
}