/*
 * 
 */
 
package com.planetproject.server.reports;
 
import java.util.*;

import java.io.FileOutputStream;
//import java.io.InputStream;  // needed for CLOB
//import java.io.InputStreamReader;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.lowagie.text.Chunk;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.Element;
import com.lowagie.text.Font;
import com.lowagie.text.FontFactory;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.Rectangle;
import com.lowagie.text.pdf.ColumnText;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfPageEvent;
import com.lowagie.text.pdf.PdfPageEventHelper;
import com.lowagie.text.pdf.PdfWriter;
import com.lowagie.text.PageSize;

 
 
/**
 * We'll test our hsqldb database with this example
 */
public class MeetingAgenda10 {
 
    // FUTURE PARAMETERS
    public static final boolean includeIssues = true;
    public static final boolean includeTasks = true;
    public static final String currentMeetingTypeID = "101";
    public static final String MeetingDate = "04/15/2012";
    public static final String MeetingTime = "12:00:00 PM";
    
    public static final String RESULT = "T:/TEMP/Print/results/MeetingAgenda10.pdf";
    
    public static int pagenumber = 0;
    public static DatabaseConnection connection;
    public static Font issueFont = FontFactory.getFont("Times-Roman", 10);
    public static Font taskFont = FontFactory.getFont("Times-Roman", 10);
    public static Font topicFont = FontFactory.getFont("Times-Roman", 10);
    public static Font meetingFont = FontFactory.getFont("Times-Roman", 10);
    public static Font headerFont = FontFactory.getFont("Times-Roman", 10);
    
    public LinkedList issueList = null;

    public PdfPTable headerBlock = null;
    public PdfPTable meetingBlock = null;
 
    /**
     * Creates a PDF with five tables.
     * @param    filename the name of the PDF file that will be created.
     * @throws    DocumentException 
     * @throws    IOException
     */
    public void createPdf(String filename)
        throws IOException, DocumentException, SQLException {
        
        // Make the connection to the database
    	connection = new HsqldbConnection("PlanetProjectDB");
    	
    	String string;
    	PdfPCell cell;
    	Phrase phrase;
    	Boolean firstTopic = true;
//    	PdfPTable meetingBlock = null;
    	
        Document document = new Document(PageSize.LETTER.rotate(), 0, 0, 50, 50);
        // 
        PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(RESULT));
        HeaderFooter event = new HeaderFooter();
        writer.setBoxSize("art", new Rectangle(36, 54, 559, 788));
        writer.setPageEvent(event);
        
        issueList = new LinkedList();  // create the Issue List container
    
/////////////////////////////////////////////////////////////////
//		headerBlock = new PdfPTable(3);
		headerBlock = HeaderTable(currentMeetingTypeID);
        
        
        document.open();
        
        String topicQuery =
                "SELECT Community.CommunityName, Community.Location as CommunityLocation, S5.* from Community" +
                "   RIGHT JOIN (select  Category.CategoryName, S4.*   from Category" +
                   "   RIGHT JOIN (select Teammember.TeamMemberName AS MeetingLeaderName, S3.* from TeamMember" +
                      "   RIGHT JOIN (select Teammember.TeamMemberName AS TopicLeaderName, S2.* from TeamMember" +
                         "   RIGHT JOIN (select MeetingTopic.SearchEnabled, MeetingTopic.TopicTitle, MeetingTopic.CategoryID, MeetingTopic.ImpactID, MeetingTopic.IssueTypeID, MeetingTopic.TopicLeaderID, MeetingTopic.TopicNumber, S1.* from MeetingTopic" +
                            "   RIGHT JOIN (select MeetingType.MEETINGLEADERID, MeetingType.MEETINGTYPEID, MeetingType.MEETINGNAME, MeetingType.PURPOSE, MeetingType.FREQUENCY, MeetingType.LOCATION as MeetingLocation, MeetingType.TELECONFERENCEINFO, Project.PROJECTNUMBER, Project.PROJECTTITLE, Project.TASKORDER, Project.CommunityID from Project" +
                               "   INNER JOIN MeetingType ON Project.ProjectID = MeetingType.ProjectID) As S1" +
                            "   ON MeetingTopic.MeetingTypeID = S1.MeetingTypeID) as S2" +
                         "   ON TeamMember.TeamMemberID = S2.TopicLeaderID) as S3" +
                      "   ON TeamMember.TeamMemberID = S3.MeetingLeaderID) as S4" +
                   "   ON Category.CategoryID = S4.CategoryID) as S5" +
                "   ON Community.CommunityID = S5.CommunityID" +
                " where S5.MeetingTypeID=" + currentMeetingTypeID +
                " order by S5.TopicNumber; " ;
        
//    try { // add code to catch exceptions
        
        Statement topicstm = connection.createStatement();
        ResultSet topicrs = topicstm.executeQuery(topicQuery);      
        
        while (topicrs.next()) {
        	
        	if (firstTopic){ // change to take mtg info out of topic join and into own Paragraph.
                         		
    	        meetingBlock = new PdfPTable(2);
    	        meetingBlock.addCell(new PdfPCell(new Phrase("Meeting Date")));
    	        meetingBlock.addCell(new PdfPCell(new Phrase(MeetingDate)));
    	        meetingBlock.addCell(new PdfPCell(new Phrase("Meeting Time")));
    	        meetingBlock.addCell(new PdfPCell(new Phrase(MeetingTime)));
    	        
    	        meetingBlock.addCell(new PdfPCell(new Phrase("Location")));
    	        phrase = new Phrase("", meetingFont);
    	        string = topicrs.getString("MeetingLocation");
    	        if ( ! topicrs.wasNull()) {
    	        	if ( ! string.isEmpty() ) phrase = new Phrase(string);
    	        }
    	        meetingBlock.addCell(new PdfPCell(phrase));
    	       
    	        meetingBlock.addCell(new PdfPCell(new Phrase("Teleconference Info")));
    	        phrase = new Phrase("", meetingFont);
    	        string = topicrs.getString("TELECONFERENCEINFO");
    	        if ( ! topicrs.wasNull()) {
    	        	if ( ! string.isEmpty() ) phrase = new Phrase(string);
    	        }
    	        meetingBlock.addCell(new PdfPCell(phrase));

    	        meetingBlock.addCell(new PdfPCell(new Phrase("Purpose")));
    	        phrase = new Phrase("", meetingFont);
    	        string = topicrs.getString("Purpose");
    	        if ( ! topicrs.wasNull()) {
        	        if ( ! topicrs.wasNull()) {
        	        	if ( ! string.isEmpty() ) phrase = new Phrase(string);
        	        } 
        	    }
        	    meetingBlock.addCell(new PdfPCell(phrase));

    	        meetingBlock.addCell(new PdfPCell(new Phrase("Frequency")));
    	        phrase = new Phrase("", meetingFont);
    	        string = topicrs.getString("Frequency");
    	        if ( ! topicrs.wasNull()) {
        	        if ( ! topicrs.wasNull()) {
        	        	if ( ! string.isEmpty() ) phrase = new Phrase(string);
        	        } 
        	    }
        	    meetingBlock.addCell(new PdfPCell(phrase));

    	        meetingBlock.addCell(new PdfPCell(new Phrase("Leader")));
    	        phrase = new Phrase("", meetingFont);
    	        string = topicrs.getString("MeetingLeaderName");
    	        if ( ! topicrs.wasNull()) {
        	        if ( ! topicrs.wasNull()) {
        	        	if ( ! string.isEmpty() ) phrase = new Phrase(string);
        	        } 
        	    }
        	    meetingBlock.addCell(new PdfPCell(phrase));
        	    
        	    document.add(Chunk.NEWLINE);
        	    document.add(Chunk.NEWLINE);
    	        document.add(meetingBlock); 
    	        
    	        document.add(Chunk.NEWLINE);
    	        document.add(Chunk.NEWLINE);
    	        firstTopic = false;
        	}
        	
        	Paragraph topic = new Paragraph();
        	topic.setIndentationLeft(25);
//        	topic.setFont(font);
        	topic.add(new Chunk(topicrs.getString("TopicNumber")));
        	topic.add(new Chunk("."));
        	topic.add(new Chunk(topicrs.getString("TopicTitle")));
        
        	if (includeIssues && topicrs.getBoolean("SearchEnabled")) {
        		Paragraph issues = new Paragraph();
        		issues = IssuesParagraph(
        				  topicrs.getString("CategoryID"),
        				  topicrs.getString("IssueTypeID"),
        				  topicrs.getString("ImpactID") );
        		topic.add(issues);
        	}
        	document.add(topic);
        }  // end while topicrs.next
        
        document.close();
        
        // close the statement
        topicstm.close();
        // close the database connection
        connection.close();
        // flush and close the print stream
        //out.flush();
        //out.close();
    } // end createPDF    
     
    public Paragraph IssuesParagraph (String categoryID, String issueTypeID, String impactID) throws com.lowagie.text.DocumentException, SQLException {
    
    	PdfPTable table;
        PdfPCell cell;
        Phrase phrase;
        String string = "";
        String issueNumber = "";
        int issueCount = 0;
        
        Paragraph issuepara = null;
    	String categoryCondition = "";
        String typeCondition = "" ;
        String impactCondition = "";
        Boolean FirstIssue;
        

    	if ( issueTypeID != null ) {
    		if ( ! (issueTypeID.isEmpty())) typeCondition = " AND (ISSUETYPEID='" + issueTypeID + "')";
    	}
        if ( categoryID != null ) {
        	if ( ! categoryID.isEmpty()) categoryCondition = " AND (CATEGORYID='" + categoryID + "')";
        }
        if ( impactID != null ) {
        	if ( ! impactID.isEmpty()) impactCondition = " AND (IMPACTID='" + impactID + "')";
        }
        //System.out.println("DEBUG ++++++++++++ typeCondition = " + typeCondition);
        //System.out.println("DEBUG ++++++++++++ categoryCondition = " + categoryCondition);
        //System.out.println("DEBUG ++++++++++++ impactCondition = " + impactCondition);

        String currentProjectID = "101";
    
        String issueQuery = "SELECT  IssueImpact.ImpactName, IssueType.IssueTypeName, S2.*, TeamMember.TeamMemberName AS OwnerName, TeamMember.TeamMemberName AS OriginatorName " + 
    		"FROM IssueImpact, IssueType, TeamMember RIGHT JOIN (select Category.CategoryName, S1.*  " +
    			"FROM Category RIGHT JOIN (select Issue.IssueID, Issue.Log, Issue.Desc, Issue.IssueStatus, Issue.IssueNumber, Issue.IssueTitle, Issue.CategoryID, Issue.ImpactID, Issue.IssueTypeID, Issue.OwnerID, Issue.Created " + 
    				"FROM Issue LEFT JOIN TeamMember AS TeamMember_1  " +
    				"ON Issue.OriginatorID = TeamMember_1.TeamMemberID) as S1 " + 
    			"ON Category.CategoryID = S1.CategoryID) as S2 ON TeamMember.TeamMemberID = S2.OwnerID " + 
    		"WHERE IssueImpact.ImpactID=S2.ImpactID AND IssueType.IssueTypeID=S2.IssueTypeID AND S2.IssueStatus<>'Closed' " +
    			categoryCondition + typeCondition + impactCondition + // add projectID
    		"ORDER BY S2.ImpactID, S2.IssueNumber;"  ;     // change to order by Impact Number (descending) 
        Statement istm = connection.createStatement();
        ResultSet irs = istm.executeQuery(issueQuery);//("SELECT * FROM Issue ");
        
        issuepara = new Paragraph();
        
        while (irs.next()) {
        	
        	table = new PdfPTable(9); // # columns
	        //table.setTotalWidth(8.0f);
	        //table.setWidths(new int[]{1,1,1,1,1, 1, 1, 1, 1, 1});
	        
        	issueNumber = irs.getString("IssueNumber");
        	
        	
        	if ( ! DuplicateIssue(issueList, issueNumber)) {
        		issueCount++;
        		
		        cell = new PdfPCell(new Phrase(" " + issueNumber + ": " + irs.getString("IssueTitle"), issueFont));
		        cell.setColspan(4);
		        table.addCell(cell);
		        
		        string = irs.getString("CategoryName");
		        if ( ! irs.wasNull()) {
		        	if ( ! string.isEmpty() ) phrase = new Phrase(string, issueFont);
		        	else phrase = new Phrase("Type:", issueFont);
		        } else {
		        	phrase = new Phrase("Type:", issueFont);
		        }
		        phrase.add(Chunk.NEWLINE);
		        phrase.add(new Chunk(irs.getString("IssueTypeName")));
		        cell = new PdfPCell(phrase);
		        table.addCell(cell);
		        
		        phrase = new Phrase("Impact:", issueFont);
		        phrase.add(Chunk.NEWLINE);
		        phrase.add(new Chunk(irs.getString("ImpactName")));
		        cell = new PdfPCell(phrase);
		        table.addCell(cell);
		        
		        phrase = new Phrase("Status:", issueFont);
		        phrase.add(Chunk.NEWLINE);
		        phrase.add(new Chunk(irs.getString("IssueStatus")));
		        cell = new PdfPCell(phrase);
		        table.addCell(cell);
		        
		        phrase = new Phrase("Owner:", issueFont);
		        phrase.add(Chunk.NEWLINE);
		        phrase.add(new Chunk(irs.getString("OwnerName")));
		        cell = new PdfPCell(phrase);
		        table.addCell(cell);
		        
		        phrase = new Phrase("Created:", issueFont);
		        phrase.add(Chunk.NEWLINE);
		        phrase.add(new Chunk(irs.getString("Created").substring(0, 10)));
		        cell = new PdfPCell(phrase);
		        table.addCell(cell);
		        	        	               
		        string = irs.getString("Desc");
		        if ( ! irs.wasNull()) {
		        	if ( ! string.isEmpty() ) {
				        phrase = new Phrase("Description: " + string, issueFont);
				        cell = new PdfPCell(phrase);
				        cell.setColspan(9);
				        table.addCell(cell);
			        }
		        }
		        
		        string = irs.getString("Log");
		        if ( ! irs.wasNull()) {
		        	if ( ! string.isEmpty() ) {
				        phrase = new Phrase(string, issueFont);
				        cell = new PdfPCell(phrase);
				        cell.setColspan(9);
				        table.addCell(cell);
			        }
		        
		        }
		        
		        if (includeTasks) {
		        	Paragraph tasks = new Paragraph();
	        		tasks = TasksParagraph(irs.getString("IssueID"));
		        	if ( tasks != null) {
				        cell = new PdfPCell(tasks);
				        cell.setColspan(9);
				        table.addCell(cell);
		        	}
		        }
		        
		        issuepara.add(table);
		        issuepara.add(new Paragraph(Chunk.NEWLINE));
		        
        	} // end if not DuplicateIssue
	        
        } // end while irs.next
        
        istm.close();
        
        if ( issueCount == 0 ) {
        	issuepara = new Paragraph(Chunk.NEWLINE);
        	issuepara.add(new Phrase("No issues found for this meeting topic."));
        	issuepara.add(new Phrase(Chunk.NEWLINE));
        }
    	
        return issuepara;
        
    } // end IssuesParagraph
    
    
   public static Paragraph TasksParagraph (String issueID) throws DocumentException, SQLException {
   
       int taskCount = 0;
       String string = "";
	   
	   String taskQuery =
               "SELECT Task.*, TeamMember.TeamMemberName " +
            	" FROM TeamMember RIGHT JOIN Task ON TeamMember.TeamMemberID = Task.OwnerID " +
            	" WHERE Task.TaskStatus<>'Completed' AND Task.IssueID=" + issueID + " ;" ;
	   
       Paragraph taskpara = new Paragraph();
       taskpara.setFont(issueFont);
       taskpara.add(new Chunk("Tasks: "));
       taskpara.add(Chunk.NEWLINE);
       
       Statement tstm = connection.createStatement();
       ResultSet trs = tstm.executeQuery(taskQuery);
       
       while (trs.next()) {
    	   	taskCount++;
	        
    	   	taskpara.add(new Chunk("[ # "));
    	   	taskpara.add(new Chunk(trs.getString("TaskNumber")));
    	   	
	        string = trs.getString("TaskDueDate");
	        if ( ! trs.wasNull()) {
	        	if ( ! string.isEmpty() ) {
	        		taskpara.add(new Chunk(" due "));
	        		taskpara.add(new Chunk(string.substring(0, 10)));
		        }
	        }
	        
	        string = trs.getString("TeamMemberName");
	        if ( ! trs.wasNull()) {
	        	if ( ! string.isEmpty() ) {
	        		taskpara.add(new Chunk(" by "));
	        		taskpara.add(new Chunk(string));
		        }
	        }
	        
	        taskpara.add(new Chunk(" is "));
	        taskpara.add(new Chunk(trs.getString("TaskStatus")));
    	   	
    	   	
    	   	taskpara.add(new Chunk(" ] "));
    	   	taskpara.add(new Chunk(trs.getString("TaskTitle")));	   	
    	   	
	        taskpara.add(Chunk.NEWLINE);	               
	        
       } // end while trs.next
       
       tstm.close();
       
       if ( taskCount == 0 ) taskpara = null;
       
       return taskpara;
       
   } // end TasksParagraph
   

   public PdfPTable HeaderTable (String meetingID) throws SQLException {
  	 
	   String meetingQuery = "SELECT  Community.Location as CommunityLocation, Community.CommunityName, Project.*, MeetingType.*  FROM Community, Project, MeetingType  " +
       		"WHERE Community.CommunityID=Project.CommunityID AND Project.ProjectID=Meetingtype.ProjectID AND MeetingTypeID=" + meetingID + ";"  ;   
	   Statement mtgstm = connection.createStatement();
	   ResultSet mtgrs = mtgstm.executeQuery(meetingQuery);//("SELECT * FROM Issue ");
	   
	   PdfPTable table = new PdfPTable(3);
	   
	   if (mtgrs.next()) {
	  	 
		   Phrase phrase = new Phrase("", headerFont);
	       String string = mtgrs.getString("CommunityName");
	       if ( ! mtgrs.wasNull()) {
	    	   if ( ! string.isEmpty() ) phrase.add( new Chunk(string));
	       }
	
	       string = mtgrs.getString("CommunityLocation");
	       if ( ! mtgrs.wasNull()) {
	       	if ( ! string.isEmpty() ) {
	       		phrase.add(Chunk.NEWLINE);
	       		phrase.add( new Chunk(string));
	       	}
	       }
	       table.addCell(new PdfPCell(phrase));
	       
	       phrase = new Phrase("", headerFont);
	       string = mtgrs.getString("ProjectNumber");
	       if ( ! mtgrs.wasNull()) {
	       	if ( ! string.isEmpty() ) {
	       		phrase.add( new Chunk(string));
	       		phrase.add( new Chunk(":"));
	       	}
	       }
	       string = mtgrs.getString("PROJECTTITLE");
	       if ( ! mtgrs.wasNull()) {
	       	if ( ! string.isEmpty() ) {
	       		phrase.add( new Chunk(" "));
	       		phrase.add( new Chunk(string));
	       	}
	       }
	             
	       string = mtgrs.getString("MEETINGNAME");
	       if ( ! mtgrs.wasNull()) {
	       	if ( ! string.isEmpty() ) {
	       		phrase.add(Chunk.NEWLINE);
	       		phrase.add( new Chunk(string));
	       	}
	       }
	       table.addCell(new PdfPCell(phrase));
	      
	       phrase = new Phrase("", headerFont);
	       phrase.add( new Chunk("Date: "));
	       phrase.add( new Phrase(MeetingDate));
	       phrase.add(Chunk.NEWLINE);
	       phrase.add( new Chunk("Time: "));
	       phrase.add(new Phrase(MeetingTime));
	       table.addCell(new PdfPCell(phrase));
	   } else { throw new SQLException("MeetingType " + meetingID.toString() + " not found."); }   
       mtgstm.close();
       
       return table;
       
//       document.add(headerBlock); 
/////////////////////////////////////////////////////			
//		document.add(Chunk.NEWLINE);
//		document.add(Chunk.NEWLINE);
//	} catch (DocumentException e) {
		// TODO Auto-generated catch block
//		e.printStackTrace();
//	} catch (SQLException e) {
		// TODO Auto-generated catch block
//		e.printStackTrace();
	}
   
    public static Boolean DuplicateIssue (List iList, String iNumber)  {
    	
    	Boolean isDuplicate = null;
    	
    	if ( iList.isEmpty()) {
    		iList.add( iNumber );
    		isDuplicate = false;
    	}
    	else {
    		if ( iList.contains( iNumber ) ) 
    			isDuplicate = true;
    		else {
    			iList.add( iNumber );
    			isDuplicate = false;
    		}
    	}
    	
    	return isDuplicate;
    }
    
    
    /** Inner class to add a header and a footer. */
    class HeaderFooter extends PdfPageEventHelper {
        /** Alternating phrase for the header. */
        Phrase[] header = new Phrase[2];
        /** Current page number (will be reset for every chapter). */
        //int pagenumber;
        
        /**
         * Initialize one of the headers.
         * @see com.lowagie.text.pdf.PdfPageEventHelper#onOpenDocument(
         *      com.lowagie.text.pdf.PdfWriter, com.lowagie.text.Document)
         */
        public void onOpenDocument(PdfWriter writer, Document document) {
            header[0] = new Phrase("zzz Meeting Agenda");
        }
        
        /**
         * Initialize one of the headers, based on the chapter title;
         * reset the page number.
         * @see com.lowagie.text.pdf.PdfPageEventHelper#onChapter(
         *      com.lowagie.text.pdf.PdfWriter, com.lowagie.text.Document, float,
         *      com.lowagie.text.Paragraph)
         */
//        public void onChapter(PdfWriter writer, Document document,
//                float paragraphPosition, Paragraph title) {
//            header[1] = new Phrase(title.getContent());
//            pagenumber = 1;
//        }

        /**
         * Increase the page number.
         * @see com.lowagie.text.pdf.PdfPageEventHelper#onStartPage(
         *      com.lowagie.text.pdf.PdfWriter, com.lowagie.text.Document)
         */
        public void onStartPage(PdfWriter writer, Document document) {
            pagenumber++;
            
            try {
				document.add(headerBlock);
//				document.add(Chunk.NEWLINE);
//				document.add(Chunk.NEWLINE);
			} catch (DocumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

        }
        
        /**
         * Adds the header and the footer.
         * @see com.lowagie.text.pdf.PdfPageEventHelper#onEndPage(
         *      com.lowagie.text.pdf.PdfWriter, com.lowagie.text.Document)
         */
        public void onEndPage(PdfWriter writer, Document document) {
            Rectangle rect = writer.getBoxSize("art");
            
//            document.add(headerBlock);
/**/
//            ColumnText.showTextAligned(writer.getDirectContent(),
//                    com.lowagie.text.Element.ALIGN_CENTER, new Phrase(String.format("page %d", pagenumber)),
//                   (rect.getLeft() + rect.getRight()) / 2, rect.getBottom() - 18, 0);
        }
    }
        

 ///////////////
     
    /**
     * Main method.
     * @param args no arguments needed
     * @throws DocumentException 
     * @throws IOException
     * @throws SQLException 
     */
    public static void main(String[] args) throws IOException, DocumentException, SQLException {
        new MeetingAgenda10().createPdf(RESULT);
    }
} // end MeetingAgenda10
