/*
 * CreditCard.java
 * Created on Jun 9, 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.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;

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.bonus.BonusList;
import com.etixpert.evolution.app.utils.XMLUtils;

public class Umsatzsteuervoranmeldung {
	private static DateFormat dateformat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
	private static SimpleDateFormat sdf =  new SimpleDateFormat("MM.yyyy");
	private static String xsltHTML;
    private static String xsltPDF;

    public static Document generate(Date month, Object countryId) throws SQLException {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();
		
		
	    Date smonth = BonusList.getMonthBegin(month).getTime();
	    Calendar eCal = Calendar.getInstance();
	    eCal.setTime(smonth);
	    eCal.add(Calendar.MONTH, 1);
	    Date emonth = eCal.getTime();
	    
	    ObjectSearch os = new ObjectSearch();
		List items = os.search(conn,
		        "Select *, t1.order_id As t1_order_id From " + 
		        "(Select vat_value, order_id, " +
		        "Sum(round(netto * quantity * 100) * .01) as tnetto, " +
		        "Sum(round(netto_trader * quantity * 100) * .01) as tnetto_trader " +
		        "From order_detail Group By vat_value, order_id " +
		        ") t1 Join (" +
		        "Select id, ordered_by, orderer_level_id, billing_date, " +
		        "case when orderer_level_id=300 or orderer_level_id=330 or orderer_level_id=350 or orderer_level_id=370 or orderer_level_id=100 or orderer_level_id=130 or orderer_level_id=160 or orderer_level_id=200 then total_brutto_trader else total_brutto end as total_brutto, " +
		        "order_status_id, bill_id " +
		        "From order_header Where billing_date Between ? And ? " +
		        "And bill_id is Not Null And order_status_id > 40 And order_status_id < 90" +
		        ") t2 On t1.order_id = t2.id Join (" +
		        "Select id, Firstname, LastName, vat_id From distributor " + 
		        (countryId != null ? "Where country_code=?": "") + 
		        ") t3 On t2.ordered_by=t3.id " + 
		        "Order By bill_id, vat_value", 
		        countryId == null ? 
		                new Object[] {  new java.sql.Date(smonth.getTime()),
		        				new java.sql.Date(emonth.getTime() - 1)} :
		                new Object[] {  new java.sql.Date(smonth.getTime()), 
		                        new java.sql.Date(emonth.getTime() - 1), countryId}
		);
		
		//logger
		
		Set vat_values = new HashSet();
		Element root = new Element("ordersbyVAT");
		
		Object prevId = null;
		Element actElement = null;
		boolean prevDeleted = false;
	    for (Iterator it = items.iterator(); it.hasNext(); ) {
	        PersistableObject po = (PersistableObject) it.next();
	        Map values = po.getValues();
	        
	        vat_values.add(values.get("vat_value"));
	        
	        if (prevId == null || !prevId.equals(values.get("t1_order_id"))) {
	            prevDeleted = "90".equals("" + values.get("order_status_id"));
	            prevId = values.get("t1_order_id");
	            actElement = new Element("order");
	            actElement.setAttribute("BillId", "" + values.get("bill_id"));
	            actElement.setAttribute("OrderId", "" + prevId);
                actElement.setAttribute("Name", values.get("firstname") + " " + values.get("lastname"));	            
	            actElement.setAttribute("Date", dateformat.format((Date) values.get("billing_date")));
	            actElement.setAttribute("DId", "" + values.get("ordered_by"));
	            actElement.setAttribute("VatId", values.get("vat_id") != null ? "" + values.get("vat_id") : "");
	            
	            if (!prevDeleted) {
		            actElement.setAttribute("brutto", "" + values.get("total_brutto"));
	            }
	            root.addContent(actElement);
	        }
	        
	        if (!prevDeleted) {
	            Element iElement = new Element("item");
	            boolean handler = "300".equals("" + values.get("orderer_level_id")) || 
	            				"350".equals("" + values.get("orderer_level_id")) || 
	            				"370".equals("" + values.get("orderer_level_id")) || 
	            				"330".equals("" + values.get("orderer_level_id")) ||
	            				"130".equals("" + values.get("orderer_level_id")) ||
	            				"160".equals("" + values.get("orderer_level_id")) ||
	            				"200".equals("" + values.get("orderer_level_id")) ||
	            				"100".equals("" + values.get("orderer_level_id"));
		        iElement.setAttribute("VAT", "" + values.get("vat_value"));
		        iElement.setAttribute("Netto", "" + values.get(handler ? "tnetto_trader" : "tnetto"));
		        actElement.addContent(iElement);
	        }
	    }
	    
	    // generate the vat values in order
	    double[] ad_vat_values = new double[vat_values.size()];
	    int i = 0;
	    for (Iterator it = vat_values.iterator(); it.hasNext(); ) {
	        Double item = (Double) it.next();
	        ad_vat_values[i++] = item.doubleValue();
	    }
	    Arrays.sort(ad_vat_values);
	    
	    // write this into the xml document
	    Element info = new Element("vat_values");
	    for (i = 0; i < ad_vat_values.length; i++) {
	        Element item = new Element("item");
	        item.setAttribute("val", "" + ad_vat_values[i]);
	        info.addContent(item);
	    }
	    root.addContent(info);
	 
		Element infoitem;
		
	    infoitem = new Element("info");
	    infoitem.setAttribute("name", "date");
	    infoitem.setAttribute("val", "" + sdf.format(smonth));
		root.addContent(infoitem);
	    
		if (countryId != null) {infoitem = new Element("info");
			infoitem.setAttribute("name", "country");
		    infoitem.setAttribute("val", "" + countryId);
			root.addContent(infoitem);
		}
		
	    Document doc = new Document();
		doc.setRootElement(root);
		return doc;
    }
    
    public static String generateHTML(Date month, Object countryId, 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/Umsatzsteuervoranmeldung.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltHTML = sb.toString();
			}
		    	    
			Document doc = generate(month, countryId);
			return XMLUtils.transform(doc, xsltHTML);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
    }
    
    public static byte[] generatePDF(Date month, Object countryId, 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/Umsatzsteuervoranmeldung.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xsltPDF = sb.toString();
			}
		    	    
			Document doc = generate(month, countryId);
			return XMLUtils.transformPDF(doc, xsltPDF);
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		return null;
    }
}
