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

// Artikel Ranking

public class TopArticles {
	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, String downline, String downline_name) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();

		ObjectSearch os = new ObjectSearch();
		List items = os.search(conn, 
		        "Select * From (" +
		        "Select art_id, Sum(quantity) as tquantity " +
		        "From order_detail od Join order_header oh On od.order_id=oh.id " +
		        "Where oh.billing_date Between ? And ? And oh.order_status_id < 90 " +
		        "And oh.ordered_by in (Select * from st(?)) " + 
		        "Group By art_id " + 
		        ") t1 Join (" +
		        "Select id, descr From article " +
		        ") t2 On t1.art_id = t2.id " + 
		        "Order By tquantity Desc",
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime()), 
		        	new Integer(downline_name)
		        }
		);
		
		Element root = new Element("TopArticles");
		for (Iterator it = items.iterator(); it.hasNext(); ) {
		    PersistableObject po = (PersistableObject) it.next();
		    Element e = new Element("item");
		    e.setAttribute("Id", "" + po.get("art_id"));
		    e.setAttribute("Name", "" + po.get("descr"));
		    e.setAttribute("Quantity", "" + po.get("tquantity"));
		    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));
			
		ObjectSearch os2 = new ObjectSearch();
		String name = "";
		try {
		    List rs = os2.search(conn,
		            "Select lastname, firstname From Distributor Where id=?",
		            new Object[] {new Integer(downline)}
		    );
		    Iterator it = rs.iterator();
		    if (it.hasNext()) {
		        PersistableObject po = (PersistableObject) it.next();
		        name = po.get("lastname") + "," + po.get("firstname");
		    }
		} catch (Exception e) {}
		
		infoitem = new Element("item");
		info.addContent(infoitem);
		infoitem.setAttribute("name", "downline");
		infoitem.setAttribute("val", downline + " " + name);

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