/*
 * TopTenMembers.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.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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 Ranking
// order_by: "P"=1, "E"=2, "PE"=3, "T"=4, "O"=5

public class TopTenMembers {
	private static String xsltHTML;
    private static String xsltPDF;
    private static String[] orderByCols = {"PV", "EV", "PEV", "TV", "OV"};
    private static SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

    private static class Entry {
        public double PV;
        public double EV;
        public double PEV;
        public double TV;
        public double OV;
        public Object sponsor;
        public Element element;
        public int    level;
        
        public Entry (double aPV, Object aSponsor, int aLevel, Element aElement) {
            PV  = aPV;
            EV  = 0;
            PEV = PV;
            TV  = PV;
            OV  = PV;
            sponsor = aSponsor;
            element = aElement;
            level   = aLevel;
        }
    }
 
    private static String generateRoleSql(String[] roles) {
		StringBuilder sb = new StringBuilder();
		
    	List rols = new ArrayList();
		if (roles == null || roles.length == 0) {
			sb.append(" And d.level_id = 14323");
		}  
		else {
			for (int i=0; i<roles.length; i++)
				rols.add(roles[i]);

			sb.append(" And (");

			String inPart = "";
			
			if (rols.contains("100") && rols.contains("130") && rols.contains("160")
					&& rols.contains("200") && rols.contains("300") && rols.contains("400")) {
				return "";
			}
			
			for (int g = 0; g < rols.size(); g++) {
				inPart = inPart + rols.get(g) + ", "; 
			}
			
			if (inPart.endsWith(", "))
				inPart = inPart.substring(0, inPart.length() - 2); 
			inPart = "(d.level_id In (" + inPart + "))";

			sb.append(inPart);
			sb.append(")");
		}

		return sb.toString();
		
    }    
    
    private static Document generate(Date from, Date to, int orderBy, String[] roles) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();

		if (orderBy < 1 || orderBy > 5) orderBy = 1;
		
		String roleSql = generateRoleSql(roles);

		ArrayList rols = new ArrayList();
		for (int i=0; i<roles.length; i++)
			rols.add(roles[i]);
		
		ObjectSearch os = new ObjectSearch();
		List items = os.search(conn, 
		        "Select id, firstname, lastname, sponsor, level_id, " +
		        "(Select Sum(total_bonus) From order_header " +
		        "Where ordered_by = d.id And order_status_id <> 90 " +
		        "And (payment_status=true Or pay_method_id In (1,2,5)) " + 
		        "And bonus_date between ? And ? And bill_id is not null) As points " +
//		        "And billing_date between ? And ?) As points " +
		        "From distributor d Where d.deleted=false And d.id > 1000000"+roleSql,
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime())
		        }
		);
		
		Map temp = new Hashtable();
		Element root = new Element("TopTenMembers");
		
		Element dest = new Element("items");
		root.addContent(dest);
		
		Element orderby = new Element("orderby");
		orderby.setAttribute("val", orderByCols[orderBy - 1]);
		root.addContent(orderby);
		
		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"));
		    temp.put(po.get("id"), new Entry(
		                 po.get("points") != null ? ((BigDecimal) po.get("points")).doubleValue() : 0,
		                 po.get("sponsor"),	
		                 po.get("level_id") != null ? ((Integer) po.get("level_id")).intValue() : 0, e));
		    dest.addContent(e);
		}
		
		for (Iterator it = temp.values().iterator(); it.hasNext(); ) {
		    Entry item = (Entry) it.next();
		    double val = item.PV;
		    
		    Entry previtem = item;
		    while (item.sponsor != null && (item = (Entry) temp.get(item.sponsor)) != null) {
		        if (previtem.level < 400) {
		            item.EV += val;
		            item.PEV += val;
		        }
		        if (previtem.level < 600) {
		            item.TV += val;
		        }
		        item.OV += val;
		        
		        
		        previtem = item;
		    } 
		}
		
		for (Iterator it = temp.values().iterator(); it.hasNext(); ) {
		    Entry item = (Entry) it.next();
		    Element e = item.element;
		    e.setAttribute("PV",  "" + item.PV );
		    e.setAttribute("EV",  "" + item.EV );
		    e.setAttribute("PEV", "" + item.PEV);
		    e.setAttribute("TV",  "" + item.TV );
		    e.setAttribute("OV",  "" + item.OV );
		}

		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));
		
		String roleStr = "";
		
		if (rols.contains("100")) roleStr = roleStr + "Kunde, ";
		if (rols.contains("130")) roleStr = roleStr + "Silber Kunde, ";
		if (rols.contains("160")) roleStr = roleStr + "Gold Kunde, ";
		if (rols.contains("200")) roleStr = roleStr + "Platin Kunde, ";
		if (rols.contains("300")) roleStr = roleStr + "Therapeut";
		if (rols.contains("330")) roleStr = roleStr + "H\u00e4ndler";
		if (rols.contains("350")) roleStr = roleStr + "Grossh\u00e4ndler";
		if (rols.contains("370")) roleStr = roleStr + "Apotheker";
		if (rols.contains("400")) roleStr = roleStr + "Vertriebspartner";		
		
		
		if (roleStr.endsWith(", ")) roleStr = roleStr.substring(0, roleStr.length()-2);
		
		infoitem = new Element("item");
		info.addContent(infoitem);
		infoitem.setAttribute("name", "roles");
		infoitem.setAttribute("val", roleStr);		
		
        return ret;
    }
    
    public static String generateHTML(Date from, Date to, int order_by, String[] roles, 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/TopTenMembers.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(from, to, order_by, roles);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
	
	public static byte[] generatePDF(Date from, Date to, int order_by, String[] roles, 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/TopTenMembers.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}
		    	    
			Document doc = generate(from, to, order_by, roles);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
