/*
 * MembersWithoutOrders.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;

// Distributoren ohne Bestellung

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

    private static Document generate(Date from, Date to) 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, firstname, lastname,  country_post_code, postcode, city " +
		        "From distributor d Join address a On a.distr_id = d.id And a.default_bill=true " +
		        "Where deleted=false And d.id Not In (" +
		        "Select ordered_by From order_header Where billing_date Between ? And ? And order_status_id < 90) " +
		        "Order By lastname",
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime())
		        }
		);
		
		Element root = new Element("MembersWithoutOrders");
		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("Address", po.get("country_post_code") + "-" + 
		            po.get("postcode") + " " + po.get("city"));
		    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", "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, 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/MembersWithoutOrders.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(from, to);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
	
	public static byte[] generatePDF(Date from, Date to, 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/MembersWithoutOrders.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}
		    	    
			Document doc = generate(from, to);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
