/*
 * 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.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 Lieferscheinsummen {
	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 Document generate(Date from, Date to, Object distributorId) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();
	        
		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=370 or oh.orderer_level_id=350 then 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=370 or oh.orderer_level_id=350 then total_brutto_trader else oh.total_brutto end as total_brutto, " +
		        "city, postcode, country_post_code " +
		        "From order_header oh Join distributor d On oh.ordered_by=d.id " +
		        "Join address a On a.distr_id = d.id And a.default_bill=true " +
		        "Where billing_date Between ? And ? And order_status_id < 90 " +
		        "And d.deleted = false And d.id in (Select * From st(?)) " +
		        "Order by lastname, firstname", 
		        new Object[] {
		        	new java.sql.Date(from.getTime()),
		        	new java.sql.Date(to.getTime()),
		        	distributorId
		        }     
		);
		
		Element root = new Element("TagesProduktion");
		Object prevId = null;
		Element actElement = null;
		for (Iterator it = items.iterator(); it.hasNext(); ) {
		    PersistableObject po = (PersistableObject) it.next();
		    Element e = new Element("item");

		    if (!po.get("did").equals(prevId)) {
		        prevId = po.get("did");
		        actElement = new Element("distributor");
		        actElement.setAttribute("Name", po.get("lastname") + ", " + po.get("firstname"));
		        actElement.setAttribute("City", po.get("country_post_code") + "-" + po.get("postcode") + " " + po.get("city"));		    
		        actElement.setAttribute("Did", "" + prevId);
		        root.addContent(actElement);
		    }
		    
		    e.setAttribute("BillingId", "" + po.get("bill_id"));
		    e.setAttribute("BillingDate", dateformat.format((Date) po.get("billing_date")));
		    e.setAttribute("Bonus", "" + po.get("total_bonus"));
		    e.setAttribute("Netto", "" + po.get("total_netto"));
		    e.setAttribute("Brutto", "" + po.get("total_brutto"));
		    actElement.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);
		
		return ret;
    }
    
	public static String generateHTML(Date from, Date to, Object distributorId, 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/Lieferscheinsummen.xsl"));				
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			//}
		    
			Document date = generate(from, to, distributorId);
			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, Object distributorId, 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/Lieferscheinsummen.xsl"));				
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			//}
		    
			Document date = generate(from, to, distributorId);
			return date != null ? XMLUtils.transformPDF(date, xsltPDF) : null;
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
	}
}
