/*
 * CreditCard.java
 * Created on Aug 11, 2005 
 * Author: 	L�szl� Felf�ldi, Etixpert GmbH
 * mail:	laszlo.felfoldi@etixpert.com		
 */
package com.etixpert.evolution.app.statistics;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.jdom2.Document;
import org.jdom2.Element;

import com.etixpert.evolution.HibernateUtil;
import com.etixpert.evolution.ObjectSearch;
import com.etixpert.evolution.PersistableObject;
import com.etixpert.evolution.app.utils.XMLUtils;

// Geburtstagliste
// orderBy: "NachName"=1, "Id"=2, "PLZ"=3


public class BirthdayList {
	private static DateFormat dateformat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
	private static String xsltHTML;
    private static String xsltPDF;
    private static String[] orderByCols = {"lastname", "id", "zipcode", "date_part('day', birthdate)"};
    private static String[] monthnames = {"J\u00E4nner", "Feber", "M\u00E4rch", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"};

    private static Document generate(int month, int orderBy) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();

		if (orderBy < 1 || orderBy > 4) 
		    orderBy = 1;
		
		ObjectSearch os = new ObjectSearch();
		List items = os.search(conn, 
		        "Select d.id, firstname, lastname, email, tel1, tel2, mobil, birthdate, descr " +
		        "From distributor d Join user_roles r On d.level_id=r.id " +
		        "Join address a On a.distr_id = d.id And a.default_bill=true " +
		        "Where deleted=false And date_part('month', birthdate)=? " +
		        "Order by " + orderByCols[orderBy - 1],
		        new Object[] {
		        	new Integer(month)
		        }
		);
		
		Element root = new Element("BirthdayList");
		for (Iterator it = items.iterator(); it.hasNext(); ) {
		    PersistableObject po = (PersistableObject) it.next();
		    Element e = new Element("item");
		    e.setAttribute("Id", "" + po.get("id"));
		    e.setAttribute("Name", po.get("firstname") + " " + po.get("lastname"));
		    e.setAttribute("Email", po.get("email") != null ? "" + po.get("email") : "");
		    e.setAttribute("Birthdate", po.get("birthdate") != null ? 
		            dateformat.format((Date) po.get("birthdate")) : "");
		    e.setAttribute("Level", "" + po.get("descr"));
		    e.setAttribute("Phone", "" + (po.get("tel1") != null ? po.get("tel1") :
		        				po.get("mobil") != null ? po.get("mobil") : ""));
		    root.addContent(e);
		}
		
		Document ret = new Document();
		ret.setRootElement(root);
        
		// set info items
		Element info = new Element("info");
		root.addContent(info);
		Element infoitem;

		infoitem = new Element("item");
		info.addContent(infoitem);
		infoitem.setAttribute("name", "month");
		infoitem.setAttribute("val", monthnames[month - 1]);

		
		return ret;
    }
    
    public static String generateHTML(int month, int orderBy, String path) throws SQLException {
		try {
			// load the xsl file if necessary
			if (xsltHTML == null) {
				StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path+"/xsl/html/BirthdayList.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(month, orderBy);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
	
	public static byte[] generatePDF(int month, int orderBy, String path) {
		try {
			// load the xsl file if necessary
			if (xsltPDF == null) {
				StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path+"/xsl/pdf/BirthdayList.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}
		    	    
			Document doc = generate(month, orderBy);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
