/*
 * NewLevel.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.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

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;

// Neue berater
// orderBy: "Id"=1, "Name"=2, "Anzahl"=3

public class NewMember {
	private static String xsltHTML;
    private static String xsltPDF;
    private static SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

    static String[] orderByCols = {"id", "lastname", "firstname", "val Desc"}; 

    private static Document generate(Date from, Date to, int orderBy, Integer rootID) 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 id, firstname, lastname, " +
		        "(Select Count(d2.id) From distributor d2 " +
		        "Where d2.sponsor=d1.id And d2.creation_date Between ? And ? " +
		        "And d2.deleted = false) As val " +
		        "From distributor d1 " +
		        "Where d1.id > 1000000 and d1.id In (Select * From st(?)) Order by " + orderByCols[orderBy - 1],
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime()),
		        	rootID
		        }
		);
		
		int sum = 0;
		
		Element root = new Element("NewMember");
		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("Val", "" + po.get("val"));
		    if (((Long)po.get("val")).intValue() > 0) { 
		        root.addContent(e);
		        sum = sum + ((Long)po.get("val")).intValue();
		    }
		}
		
		Document ret = new Document();
		
		root.setAttribute("Sum", ""+new Integer(sum).toString());
		
		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", "from");
		infoitem.setAttribute("val", sdf.format(from));

		infoitem = new Element("item");
		info.addContent(infoitem);
		infoitem.setAttribute("name", "to");
		infoitem.setAttribute("val", sdf.format(to));
		
		return ret;
    }
    
    public static String generateHTML(Date from, Date to, int orderBy, Integer rootID ,String path) {
		try {
			// load the xsl file if necessary
			if (xsltHTML == null) {
				StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path+"/xsl/html/NewMember.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(from, to, orderBy, rootID);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
	
	public static byte[] generatePDF(Date from, Date to, int orderBy, Integer rootID, 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/NewMember.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			//}
		    	    
			Document doc = generate(from, to, orderBy, rootID);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
