/*
 * CreditCard.java
 * Created on Jun 8, 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.DateFormat;
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.Locale;
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;
import com.etixpert.evolution.value.DistributorValue;
import com.etixpert.evolution.value.order.OrderHeaderValue;

public class Umsatzproduktion {
	private static DateFormat dateformat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
    private static String xslt;
    private static String xsltPDF;
    private static SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
    
    
    private static String generateRoleSql(String[] roles) {
		StringBuilder sb = new StringBuilder();
		
    	List mainrols = new ArrayList();
    	List subTypes = new ArrayList();
    	
		if (roles == null || roles.length == 0) {
			sb.append(" And d.level_id = 14323");
		}  
		else {
			for (int i=0; i<roles.length; i++) {
				mainrols.add(roles[i].split(":")[0]);
				subTypes.add(roles[i].split(":")[1]);
			}

			sb.append(" And (");

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

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

		return sb.toString();
		
    } 
    
    private static Document generate(Date from, Date to, String[] roles, Object distributorId, Object country) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();
	        
		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 firstname, lastname, bill_id, d.id As did, billing_date, " +
		        "case when oh.orderer_level_id=300 or oh.orderer_level_id=330 or oh.orderer_level_id=350 or oh.orderer_level_id=370 then oh.total_netto_trader else oh.total_netto end as total_netto, " +
		        "total_bonus, total_points, " +
		        "case when oh.orderer_level_id=300 or oh.orderer_level_id=330 or oh.orderer_level_id=350 or oh.orderer_level_id=370 then oh.total_brutto_trader else oh.total_brutto end as total_brutto " +
		        "From order_header oh Join distributor d On oh.ordered_by=d.id " +
		        "Where billing_date Between ? And ? And order_status_id < 90 " +
		        "And d.deleted = false And d.id in (Select * From st(?)) " +
		        (country != null ? " And country_code = ?" : "") + roleSql +
		        " Order by lastname, firstname", 
		        country == null ?
			        new Object[] {
			        	new java.sql.Date(from.getTime()),
			        	new java.sql.Date(to.getTime()),
			        	distributorId
			        } :
		            new Object[] {
			        	new java.sql.Date(from.getTime()),
			        	new java.sql.Date(to.getTime()),
			        	distributorId,
			        	country
			        }     
		);
		
		Element root = new Element("TagesProduktion");
		for (Iterator it = items.iterator(); it.hasNext(); ) {
		    PersistableObject po = (PersistableObject) it.next();
		    Element e = new Element("item");
		    e.setAttribute("BillingId", "" + po.get("bill_id"));
		    e.setAttribute("BillingDate", dateformat.format((Date) po.get("billing_date")));
		    e.setAttribute("Name", po.get("lastname") + ", " + po.get("firstname"));
		    e.setAttribute("Did", "" + po.get("did"));
		    e.setAttribute("Bonus", "" + po.get("total_bonus"));
		    e.setAttribute("Netto", "" + po.get("total_netto"));
		    e.setAttribute("Brutto", "" + po.get("total_brutto"));
		    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));

		infoitem = new Element("item");
		info.addContent(infoitem);
		infoitem.setAttribute("name", "did");
		infoitem.setAttribute("val", "" + distributorId);

		if (country != null) {
		    infoitem = new Element("item");
			info.addContent(infoitem);
			infoitem.setAttribute("name", "country");
			infoitem.setAttribute("val", "" + country);
		}
		
		String roleStr = "";
		if (rols.contains("100:0")) roleStr = roleStr + "Kunde, ";
		if (rols.contains("130:0")) roleStr = roleStr + "Silber Kunde, ";
		if (rols.contains("160:0")) roleStr = roleStr + "Gold Kunde, ";
		if (rols.contains("200:0")) roleStr = roleStr + "Platin Kunde, ";
		if (rols.contains("300:0")) roleStr = roleStr + "Therapeut, ";
		if (rols.contains("300:1")) roleStr = roleStr + "Therapeut-Arzt, ";
		if (rols.contains("330:0")) roleStr = roleStr + "H\u00e4ndler, ";
		if (rols.contains("350:0")) roleStr = roleStr + "Grossh\u00e4ndler, ";
		if (rols.contains("370:0")) roleStr = roleStr + "Apotheker, ";
		if (rols.contains("400:0")) roleStr = roleStr + "Berater, ";
		if (rols.contains("400:1")) roleStr = roleStr + "Berater-Arzt, ";
		
		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, String[] roles, Object distributorId, Object country, String path) {
		// get the orderheaderValue
		try {
			// load the xsl file if necessary
			//if (xsltPDF == null) {
				StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path + "/xsl/html/Umsatzproduktion.xsl"));				
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			//}
		    
			Document date = generate(from, to, roles, distributorId, country);
			return date != null ? XMLUtils.transform(date, xsltPDF) : null;
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}

	public static byte[] generatePDF(Date from, Date to, String[] roles, Object distributorId, Object country, String path) {
		// get the orderheaderValue
		try {
			// load the xsl file if necessary
			//if (xsltPDF == null) {
				StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path + "/xsl/pdf/Umsatzproduktion.xsl"));				
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			//}
		    
			Document date = generate(from, to, roles, distributorId, country);
			return date != null ? XMLUtils.transformPDF(date, xsltPDF) : null;
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
