/*
 * DownlineList.java
 * Created on 2005.08.21.
 * Author:  Laszlo Felfoldi, 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.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;

public class DownlineList {
	private static String xsltHTML;
    private static String xsltPDF;
    
    private static Document generate(Object reqRoot, Object currRoot) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();

		ObjectSearch os = new ObjectSearch();
		List items = os.search(conn, 
		        "Select d.id As did, lastname, firstname, a.*, tel1, tel2, mobil, email " +
		        "From distributor d Join Address a On a.distr_id=d.id And a.default_bill=true " +
				"Where d.id In (Select * From st(?)) And d.id In (Select * From st(?)) " +
				"And deleted = false Order By lastname, firstname",
		        new Object[] {reqRoot, currRoot}
		);
		
		Element root = new Element("DownlineList");
		for (Iterator it = items.iterator(); it.hasNext(); ) {
		    PersistableObject po = (PersistableObject) it.next();
		    Element e = new Element("item");
		    e.setAttribute("Id", "" + po.get("did"));
		    e.setAttribute("Name", po.get("lastname") + " " + po.get("firstname"));
		    e.setAttribute("Zipcode", po.get("country_post_code") + "-" + po.get("postcode"));
		    e.setAttribute("City", "" + po.get("city"));
		    e.setAttribute("Street", "" + po.get("street"));
		    
		    String tmp;
		    String phone = "";
		    tmp = (String) po.get("tel1");
		    if (tmp != null && !tmp.equals("")) phone += ", " + tmp;
		    tmp = (String) po.get("tel2");
		    if (tmp != null && !tmp.equals("")) phone += ", " + tmp;
		    tmp = (String) po.get("mobil");
		    if (tmp != null && !tmp.equals("")) phone += ", " + tmp;
		    if (phone.startsWith(",")) phone = phone.substring(2);
		    e.setAttribute("Phone", phone);
		    
		    e.setAttribute("Mail", po.get("email") != null ? "" + po.get("email") : "");
		    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", "did");
		infoitem.setAttribute("val", "" + reqRoot);

        return ret;
    }
    
    public static String generateHTML(Object reqRoot, Object actRoot, 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/DownlineList.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(reqRoot, actRoot);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
	
	public static byte[] generatePDF(Object reqRoot, Object actRoot, 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/DownlineList.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}
		    	    
			Document doc = generate(reqRoot, actRoot);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}    
}
