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

// Top Verk�efer pro Artikel

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

    private static String generateRoleSql(String[] roles) {
		StringBuilder sb = new StringBuilder();
		
    	ArrayList 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 gtPart = "";
			String inPart = "";
			
			if (rols.contains("300")) {
				gtPart = "(d.level_id >= 300)";
			}
			
			if (rols.contains("100") && rols.contains("200")) inPart = "(d.level_id In (100, 200))";
			else if (rols.contains("100") && !rols.contains("200")) inPart = "(d.level_id = 100)";
			else if (!rols.contains("100") && rols.contains("200")) inPart = "(d.level_id = 200)";
			
			if (!gtPart.equals("") && !inPart.equals(""))
				sb.append(inPart + " Or "+gtPart);
			else if (gtPart.equals("") && !inPart.equals(""))
				sb.append(inPart);
			else if (!gtPart.equals("") && inPart.equals(""))
				sb.append(gtPart);
				
			sb.append(")");
			
		}

		return sb.toString();
		
    }
    
    private static Document generate(Date from, Date to, String[] articleIds, String[] roles, Integer rootID) 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 artitems = 0;
		    for (int i = 0; i < articleIds.length; i++) {
		    	if (articleIds[i] == null || articleIds[i].length() == 0) continue;
		        if (artitems++ > 0) art_ids += ",";
		        art_ids += "'" + articleIds[i] + "'";
		    }
		}

		String where = "";
		if (art_ids.length() > 0)
		    where = "And art_id In (" + art_ids + ") ";

		ArrayList rols = new ArrayList();
		for (int i=0; i<roles.length; i++)
			rols.add(roles[i]);
		
		String roleSql = generateRoleSql(roles);
		
		
		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," +
		        "Sum(od.netto_trader * od.quantity) as tnetto_trader, " +
		        "Sum(od.netto * od.quantity) as tnetto " +
		        "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 " + where +
		        "Group by ordered_by" +
		        ") t1 Join (" +
		        "Select id, firstname, lastname From distributor d " +
		        "Where d.deleted = false and d.id In (Select * From st(?))"+roleSql +
		        ") 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()),
					rootID
		        }
		);

		Element root = new Element("BestDistributorArticles");
		Element sect1 = new Element("section");
		sect1.setAttribute("id", "1");
		root.addContent(sect1);

		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"));
		    
		    boolean handler = "300".equals("" + po.get("orderer_level_id")) || "350".equals("" + po.get("orderer_level_id")) || "330".equals("" + po.get("orderer_level_id")) || "370".equals("" + po.get("orderer_level_id"));
	        e.setAttribute("TNetto", "" + po.get(handler ? "tnetto_trader" : "tnetto"));
		    sect1.addContent(e);
		}

		/*items = os.search(conn,
		        "Select art_id, Sum(quantity) as tquantity, " +
		        "Sum(od.bonus * od.quantity) as tbonus, Sum(od.points * od.quantity) as tpoints," +
		        "Sum(od.netto * od.quantity) as tnetto " +
		        "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(?)) " + where +
		        "Group by art_id Order by tquantity Desc",
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime()),
		        	rootID
		        }
		);*/

		items = os.search(conn,
		        "Select art_id, Sum(quantity) as tquantity, " +
		        "Sum(od.bonus * od.quantity) as tbonus, Sum(od.points * od.quantity) as tpoints," +
		        "Sum(od.netto * od.quantity) as tnetto " +
		        "From order_detail od Join (order_header oh join distributor d On oh.ordered_by = d.id) On od.order_id=oh.id " +
		        "Where oh.billing_date Between ? And ? "+roleSql+" And oh.order_status_id < 90 And oh.ordered_by In (Select * From st(?)) " + where +
		        "Group by art_id Order by tquantity Desc",
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime()),
		        	rootID
		        }
		);		
		
		Element sect2 = new Element("section");
		sect2.setAttribute("id", "2");
		root.addContent(sect2);

		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("TQuantity", "" + po.get("tquantity"));
		    e.setAttribute("TBonus", "" + po.get("tbonus"));
		    e.setAttribute("TPoints", "" + po.get("tpoints"));
		    e.setAttribute("TNetto", "" + po.get("tnetto"));
		    sect2.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));

	    if (art_ids.length() > 0) {
	        infoitem = new Element("item");
			info.addContent(infoitem);
			infoitem.setAttribute("name", "art_ids");
			infoitem.setAttribute("val", art_ids);
	    }

		String roleStr = "";
		if (rols.contains("100")) roleStr = roleStr + "Kunde, ";
		if (rols.contains("200")) roleStr = roleStr + "VIP Kunde, ";
		if (rols.contains("300")) 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, String[] articleIds, Integer rootID , 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/BestDistributorArticles.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}

			Document doc = generate(from, to, articleIds, roles, rootID);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}

	public static byte[] generatePDF(Date from, Date to, String[] articleIds, Integer rootID, 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/BestDistributorArticles.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}

			Document doc = generate(from, to, articleIds, roles, rootID);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
