/*
 * DownlineArticle.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.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.fop.datatypes.PercentBase;
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;

// Downline Artikel

public class DownlineArticle {
	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, Integer distributorId, String[] articleIds) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();
		
		// generate the art_id conditon
		String art_ids = "";
		if (articleIds != null) {
			int artitem = 0;
		    for (int i = 0; i < articleIds.length; i++) {
		    	if (articleIds[i] == null || articleIds[i].length() == 0) continue;
		        if (artitem++ > 0) art_ids += ",";
		        art_ids += "'" + articleIds[i] + "'";
		    }
		}
		
		String where = "";
		if (art_ids.length() > 0) 
		    where = "And art_id In (" + art_ids + ") ";
		
		ObjectSearch os = new ObjectSearch();
		List items = os.search(conn,
		        "Select * From ((" +
		        "Select ordered_by, Sum(quantity) as tquantity, " +
		        "Sum(od.bonus * od.quantity) as tbonus, Sum(od.points * od.quantity) as tpoints " +
		        "From order_detail od Join order_header oh On od.order_id=oh.id " +
		        "Where oh.billing_date Between ? And ? And order_status_id < 90 " + where +  
		        "Group by ordered_by" +
		        ") t1 Join (" +
		        "Select id, firstname, lastname, sponsor From distributor d " + 
		        "Where d.deleted = false And d.id in (select * from st(?)) Order By d.lastname" + 
		        ") t2 On t1.ordered_by = t2.id) order by tquantity desc",
		        new Object[] {
		        	new java.sql.Date(from.getTime()), 
		        	new java.sql.Date(to.getTime()),
		        	distributorId
		        }
		);
		
		Element root = new Element("DownlineArticle");
		Element aroot = root;
		if (distributorId.intValue() == 0) {
		    aroot = new Element("item");
		    aroot.setAttribute("Id", "0");
		    aroot.setAttribute("Name", "Evoluiton");
		    root.addContent(aroot);
		}
		
		Map temp = new Hashtable();
		for (Iterator it = items.iterator(); it.hasNext(); ) {
		    PersistableObject po = (PersistableObject) it.next();
		    Element e = new Element("item");
		    e.setAttribute("Id", "" + po.get("ordered_by"));
		    e.setAttribute("Name", po.get("firstname") + " " + po.get("lastname"));
		    e.setAttribute("TQuantity", "" + po.get("tquantity"));
		    e.setAttribute("TBonus", "" + po.get("tbonus"));
		    e.setAttribute("TPoints", "" + po.get("tpoints"));
		    e.setAttribute("sponsor", "" + po.get("sponsor"));
		    aroot.addContent(e);
		    temp.put(po.get("ordered_by"), e);
		}
		
		for (Iterator it = temp.values().iterator(); it.hasNext(); ) {
		    Element item = (Element) it.next();
		    Integer sponsor = new Integer(item.getAttributeValue("sponsor"));
		    if (temp.containsKey(sponsor)) {
		        item.detach();
		        ((Element) temp.get(sponsor)).addContent(item);
		    }
		}
				
		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));
		
		infoitem = new Element("item");
		info.addContent(infoitem);
		infoitem.setAttribute("name", "did");
		infoitem.setAttribute("val", "" + distributorId);
		
	    if (art_ids.length() > 0) {
	        infoitem = new Element("item");
		    
			info.addContent(infoitem);
			infoitem.setAttribute("name", "art_ids");
			infoitem.setAttribute("val", art_ids);
	    }
	    
	    return ret;
    }
    
	public static String generateHTML(Date from, Date to, Integer distributorId, String[] articleIds, 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/DownlineArticle.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(from, to, distributorId, articleIds);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
	
	public static byte[] generatePDF(Date from, Date to, Integer distributorId, String[] articleIds, 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/DownlineArticle.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}
		    	    
			Document doc = generate(from, to, distributorId, articleIds);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
