/*
 * Payment.java
 * Created on 2005.06.11.
 * Author:  Laszlo Felfoldi, Etixpert GmbH 
 * mail:    laszlo.felfoldi@etixpert.com
 */
package com.etixpert.evolution.app.bonus;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileReader;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
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.Edifact;
import com.etixpert.evolution.app.bonus.HVB;
import com.etixpert.evolution.app.utils.XMLUtils;
import com.etixpert.evolution.value.CountryValue;
import com.etixpert.evolution.value.DistributorValue;
import com.etixpert.evolution.value.bonus.BonusMetrics;
import com.etixpert.evolution.value.bonus.DistributorHistoryValue;
import com.etixpert.evolution.value.order.BankAccountValue;

public class BonusAction {
    private static String xslt;
    
	private static String string(Object src) {
		return src != null ? src.toString() : "";
	}
	
	private static void generateBonusListXML(Map environment, Set distributorIds, String info, Element to, boolean useVAT) {
		
		for (Iterator it = distributorIds.iterator(); it.hasNext(); ) {
			DistributorValue dv = (DistributorValue) it.next();
			
			Element de = new Element("distributor");
			de.setAttribute("Id",   string(dv.getId()));
			de.setAttribute("Name", string(dv.getName()));
			
			if (info != null) {
			    de.setAttribute("Info", info);
			}
			
		    double netto = Math.round(dv.getNettoBonusToPay() * 100) * 0.01;
			double mwst  = Math.round(netto * dv.getVATFactor()) * 0.01;
			de.setAttribute("Netto", "" + netto);
			if (useVAT) {
				de.setAttribute("VATFactor", "" + dv.getVATFactor());
				de.setAttribute("VAT", "" + mwst);
			} else {
				String vatID = dv.getVatId();
				if (vatID != null && !vatID.trim().equals("")) 
					de.setAttribute("UID", vatID);
			}
			to.addContent(de);
		}
	}
		
	public static byte[] generateBonusList(Map environment, String country, Set distributorsWithBonus, Set distributorsWithOpenOrders,
										   Set distributorsWithPayingNotAllowed, Set distributorsWithError, Set distributorsWithoutFirstOrder,
										   Date date, String filename, boolean useVAT, 
										   String path) {
	    if (distributorsWithBonus.size() + distributorsWithError.size() + 
	            distributorsWithOpenOrders.size() +  distributorsWithoutFirstOrder.size() + distributorsWithPayingNotAllowed.size() == 0) {
	        return null;
	    }

	    Logger logger = Logger.getLogger("mainControl.BonusAction");
		try {
			// read the xslt if it has not been done yet
			if (xslt == null) {
			    logger.info("setting up xslt");
	    		StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path+"/xsl/pdf/bonuslist.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb.append(line));
				xslt = sb.toString();
				logger.info("xslt set up");
	    	}
			
			// get its xml representation 
			Document doc = new Document();
			Element root = new Element("bonuslist");
			doc.setRootElement(root);
			logger.info("generating xml list");

			SimpleDateFormat sdf2 = new SimpleDateFormat("MM.yyyy");
			String mainTitle = "Bonusauszahlung f\u00fcr " + sdf2.format(date);
			String title = "Evolution Handelsgmbh Network " + country;
			if (distributorsWithBonus.size() > 0) {
				Element e = new Element("section");
				e.setAttribute("Title", title);
				e.setAttribute("MainTitle", mainTitle);
				e.setAttribute("Id", "0");
				if (useVAT) e.setAttribute("useVAT", "1");
				if (filename != null) e.setAttribute("Filename", filename);
				generateBonusListXML(environment, distributorsWithBonus, null, e, useVAT);
				root.addContent(e);
			}
			
			if (distributorsWithPayingNotAllowed.size() + 
			        distributorsWithError.size() + 
			        distributorsWithOpenOrders.size() + 
			        distributorsWithoutFirstOrder.size() > 0 ) {
				Element e = new Element("section");
				e.setAttribute("Title", title);
				e.setAttribute("MainTitle", mainTitle);
				e.setAttribute("SubTitle", "nicht \u00fcberwiesen");
				e.setAttribute("Id", "1");
				if (useVAT) e.setAttribute("useVAT", "1");
				generateBonusListXML(environment, distributorsWithPayingNotAllowed, "\u00dcberweise=Nein", e, useVAT);
				generateBonusListXML(environment, distributorsWithoutFirstOrder, "Erstbestellung fehlt", e, useVAT);
				generateBonusListXML(environment, distributorsWithError, "Kein Konto", e, useVAT);
				generateBonusListXML(environment, distributorsWithOpenOrders, "offene Bestellung", e, useVAT);
				root.addContent(e);
			}

			logger.info("xml list generated");
			return XMLUtils.transformPDF(doc, xslt);
	
		} catch (Exception e) {
			e.printStackTrace(System.err);
			logger.error("Error in bonuslist generation",e);
		}
    	return null;
	}

	public static Object[] generate(Date date, Date now, String path, StringBuffer message) {
		Object[] ret = new Object[9];
		Transaction tx = null;
		try {
			// calculate the bonus values
			Session session = HibernateUtil.currentSession();
			tx = session.beginTransaction();
			Connection conn = session.connection();
	
	        Calendar sDate = BonusList.getMonthBegin(date);
	        Calendar eDate = Calendar.getInstance();
	        eDate.setTime(sDate.getTime());
	        eDate.add(Calendar.MONTH, 1);
	        eDate.add(Calendar.MILLISECOND, -1);
	        
	        // check for open orders?
	        ObjectSearch os2 = new ObjectSearch();
	        List val2 = os2.search(conn, "Select Count(*) as items From order_header Where " +
	        		"bonus_date between ? And ? And order_status_id between 10 and 40",
	        		new Object[] { new java.sql.Date(sDate.getTime().getTime()), 
	                			   new java.sql.Date(eDate.getTime().getTime())});
	        PersistableObject po2 = (PersistableObject) val2.get(0);
	        int numOfItems = ((Long) po2.get("items")).intValue();
	        if (numOfItems > 0) {
	            message.append("Achtung! Es gibt noch Bestellungen im Status10-40 f\u00fcr dieses Bonusmonat!\\n");
	        }
	 
	        // calculate the xml file 
			Object[] res  = BonusList.calculate(conn, date, path, DistributorValue.FLAG_NEED_BANKINFO);
			
			Document doc  = (Document) res[0];
			Map      env  = (Map)      res[1];
	        
		
			// check for open orders
	        ObjectSearch os = new ObjectSearch();
	        List list = os.search(conn, "Select distinct ordered_by " +
	        		"From order_header Where " +
	        		"bonus_date between ? And ? And order_status_id <= 60 And payment_status=false",
	        		new Object[] { new java.sql.Date(sDate.getTime().getTime()), 
	                			   new java.sql.Date(eDate.getTime().getTime())});
	        for (Iterator it = list.iterator(); it.hasNext();) {
	            PersistableObject po = (PersistableObject) it.next();
	            Integer id = (Integer) po.get("ordered_by");
	            
	            DistributorValue dv = (DistributorValue) env.get(id);
	            if (dv != null) {
	                dv.setHasOpenOrders();
	            }
	        }
	        
	       
	        list = os.search(conn, "Select Distr_ID from distributor_without_first_order");
	        for (Iterator it = list.iterator(); it.hasNext();) {
	            PersistableObject po = (PersistableObject) it.next();
	            Integer id = (Integer) po.get("distr_id");
	            DistributorValue dv = (DistributorValue) env.get(id);
	            
	            // is this distributor exported from formel365?
	            String sid = "" + id;
	            boolean exported = sid.length() > 2 && sid.charAt(2) >= '5';
	            
	            // exported distributors do not need to have first order
	            if (dv != null && !exported) {
	                dv.setWithoutFirstorder(true);
	            }
	        }
	        
	        
	     
			// parse the values to pay
			Element  root = doc.getRootElement();
			Iterator i = root.getChild("names").getDescendants();
			while (i.hasNext()) {
				Element name = (Element) i.next();
				String id2 = name.getAttributeValue("Id");
				Iterator it2 = root.getChild("bonus").getDescendants();
				while (it2.hasNext()) {
					Element bonus = (Element) it2.next();
					if (id2.equals(bonus.getAttributeValue("Id"))) {
						BonusList.getTotals(name, bonus, env);
					}
				}
			}
		
			// generate the dowloadable files
	        Set distributorsWithBonus1            = new HashSet();
	        Set distributorsWithOpenOrders1       = new HashSet();
			Set distributorsWithPayingNotAllowed1 = new HashSet();
			Set distributorsWithError1            = new HashSet();
			Set distributorsWithoutFirstOrder1    = new HashSet();
			
		    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd.HHmmss");
	        String postfix = sdf.format(now)+ ".dat";			
	        
			// generate file for Austrian Salespersons
			//Edifact edifact = new Edifact("12000", "50575610800");
			//ret[0] = edifact.generate(date, distributorsWithBonus1, distributorsWithOpenOrders1, distributorsWithPayingNotAllowed1, distributorsWithError1, distributorsWithoutFirstOrder1, env);
			//ret[1] = generateBonusList(env, "\u00d6sterreich", distributorsWithBonus1, distributorsWithOpenOrders1, distributorsWithPayingNotAllowed1, distributorsWithError1, distributorsWithoutFirstOrder1, date, "P_AT_" + postfix + "_EVOLUTION", true, path);
			
	        Set distributorsWithBonus2            = new HashSet();
	        Set distributorsWithOpenOrders2       = new HashSet();
			Set distributorsWithPayingNotAllowed2 = new HashSet();
			Set distributorsWithError2            = new HashSet();
			Set distributorsWithoutFirstOrder2    = new HashSet();
			
			// generate file for German Salespersons
			
			//HVB hvb = new HVB("70020270", "665876594");
			//ret[2] = hvb.generate(date, distributorsWithBonus2, distributorsWithOpenOrders2, distributorsWithPayingNotAllowed2, distributorsWithError2, distributorsWithoutFirstOrder2, env);
			//ret[3] = generateBonusList(env, "Deutschland", distributorsWithBonus2, distributorsWithOpenOrders2, distributorsWithPayingNotAllowed2, distributorsWithError2, distributorsWithoutFirstOrder2, date, "P_HVB_" + postfix + "_EVOLUTION", false, path);

			
	        Set distributorsWithBonus3            = new HashSet();
	        Set distributorsWithOpenOrders3       = new HashSet();
			Set distributorsWithPayingNotAllowed3 = new HashSet();
			Set distributorsWithError3            = new HashSet();
			Set distributorsWithoutFirstOrder3    = new HashSet();

			SEPA sepa = new SEPA("BKAUATWW", "AT071200050575610800");
			ret[4] = sepa.generate(now, "43", true, distributorsWithBonus3, distributorsWithOpenOrders3, distributorsWithPayingNotAllowed3, distributorsWithError3, distributorsWithoutFirstOrder3, env);
			ret[5] = generateBonusList(env, "\u00d6sterreich", distributorsWithBonus3, distributorsWithOpenOrders3, distributorsWithPayingNotAllowed3, distributorsWithError3, distributorsWithoutFirstOrder3, date, "P_SEPA_AT_" + postfix + "_EVOLUTION", true, path);


	        Set distributorsWithBonus4            = new HashSet();
	        Set distributorsWithOpenOrders4       = new HashSet();
			Set distributorsWithPayingNotAllowed4 = new HashSet();
			Set distributorsWithError4            = new HashSet();
			Set distributorsWithoutFirstOrder4    = new HashSet();

			sepa = new SEPA("HYVEDEMMXXX", "DE07700202700665876594");
			ret[6] = sepa.generate(now, "49", false, distributorsWithBonus4, distributorsWithOpenOrders4, distributorsWithPayingNotAllowed4, distributorsWithError4, distributorsWithoutFirstOrder4, env);
			ret[7] = generateBonusList(env, "Deutschland", distributorsWithBonus4, distributorsWithOpenOrders4, distributorsWithPayingNotAllowed4, distributorsWithError4, distributorsWithoutFirstOrder4, date, "P_SEPA_DE_" + postfix + "_EVOLUTION", false, path);
			
			// generate file for other Salespersons
            Map setsByCountry = new Hashtable();
            for (Iterator it = env.values().iterator(); it.hasNext(); ) {
				DistributorValue dv = (DistributorValue) it.next();
				String id = "" + dv.getId();
				if (!id.startsWith("43") && !id.startsWith("49") && id.length() >= 8) {
                    String countryCode = dv.getCountry();
                    Set[] sets = (Set[]) setsByCountry.get(countryCode);
                    if (sets == null) {
                        sets = new Set[] {new HashSet(), new HashSet(), new HashSet(), new HashSet()};
                        setsByCountry.put(countryCode, sets);
                    }
				    
                    double netto = Math.round(dv.getNettoBonusToPay() * 100) * 0.01;
					double val =  netto + Math.round(netto * dv.getVATFactor()) * 0.01;
			      	if (val > 0) {
			      		if (dv.getHasOpenOrders()) {
			      			sets[1].add(dv);
			      		} else if (!dv.getTransferBonus()) {
			      			sets[2].add(dv);
			      		} else if (dv.getWithoutFirstOrder()) {
			      			sets[3].add(dv);
			      		} else {
							sets[0].add(dv);
			      		}					
					}
				}
			} 
			
            
            Map countries = CountryValue.getAll(conn);
            Map retMap = new Hashtable();
            Set empty = new HashSet();
            for (Iterator it = setsByCountry.keySet().iterator(); it.hasNext(); ) {
                Object key = it.next();
                CountryValue country = (CountryValue) countries.get(key);
                Set[] sets = (Set[]) setsByCountry.get(key);
                Object val = generateBonusList(env, country.getName(), sets[0], sets[1], sets[2], sets[3], empty, date, null, false, path);
                if (val != null) retMap.put(key, val);
            }
			ret[8] = retMap;
			
			tx.commit();
			return ret;
		} catch (Exception e) {
			tx.rollback();
			e.printStackTrace(System.err);
		} finally {
			HibernateUtil.closeSession();
		}
		return null;
	}

    private static  void store(Connection conn, PreparedStatement ps, String name, String file, byte[] pdf) throws Exception {
        if (file == null && pdf == null) {
            return;
        }
        ps.setString(1, name);
        if (file == null) {
            ps.setObject(2,null);
        }
        else  {
            byte[] data = file.getBytes("ISO-8859-1");
            ByteArrayInputStream  bin = new ByteArrayInputStream(data);
            ps.setBinaryStream(2, bin, data.length);
        }
                
        if (pdf == null)
            ps.setObject(3, null);
        else {
            ps.setBinaryStream(3, new ByteArrayInputStream(pdf), pdf.length);
        }
        
        ps.executeUpdate();
        Statement stat = conn.createStatement();
    }
    
    public static void storeAll(Object[] files, Date now) throws Exception  {
		try {
			// calculate the bonus values
			Session session = HibernateUtil.currentSession();
	        Transaction tx = session.beginTransaction();
	        Connection conn = session.connection();
	        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd.HHmmss");
	        
	        PreparedStatement ps = conn.prepareStatement("insert into financial (name, data, pdf) values(?, ?, ?)");
	      
	        String name= "P_AT_" + sdf.format(now) + "_EVOLUTION";
	        store(conn, ps, name, (String) files[0], (byte[]) files[1]);
	        name = "P_HVB_" + sdf.format(now) + "_EVOLUTION";
	        store(conn,ps, name, (String) files[2], (byte[]) files[3]);
	        name= "P_SEPA_AT_" + sdf.format(now) + "_EVOLUTION";
	        store(conn, ps, name, (String) files[4], (byte[]) files[5]);
	        name = "P_SEPA_DE_" + sdf.format(now) + "_EVOLUTION";
	        store(conn,ps, name, (String) files[6], (byte[]) files[7]);
	        
	        for (Iterator it = ((Map) files[8]).keySet().iterator(); it.hasNext(); ) {
                Object key = it.next();
                name = "P_" + key + "_" + sdf.format(now) + "_EVOLUTION";
                Object data = ((Map) files[8]).get(key);
                store(conn, ps, name, null, (byte[]) data);
            }
	        tx.commit();
		} catch (Exception e) {
		    e.printStackTrace(System.err);
		}
    }
    
	private static void saveToHistory(Connection conn, BonusData bd, Object levelId, Date toDate, Date sDate) throws Exception {
		if (bd != null) {
			if (levelId == null) {
				levelId = bd.getLevelId();
			}
			if (bd.getPrev() == null || bd.getPrev().getLevelId() != levelId) {
				DistributorHistoryValue dhv = new DistributorHistoryValue(
					bd.getDistributor().getId(), levelId,
					bd.getPrev() != null ? bd.getFromDate() : sDate, toDate
				);
				
				dhv.insert(conn, true);
				
				if (bd.getpOV() >= 20000) {
					BonusMetrics bm = new BonusMetrics(dhv, "pOV", bd.getpOV());
					bm.insert(conn);
				}
				
				levelId = null;
				toDate = bd.getFromDate();
			}
			saveToHistory(conn, bd.getPrev(), levelId, toDate, sDate);			
		}
	}
	
	public static String accept(Date date, String path) throws Exception {
		Session session = HibernateUtil.currentSession();
        Transaction tx = session.beginTransaction();
        Connection conn = session.connection();
              
        // get the month end
        Calendar sDate = BonusList.getMonthBegin(date);
        Calendar eDate = Calendar.getInstance();
        eDate.setTime(sDate.getTime());
        eDate.add(Calendar.MONTH, 1);
        eDate.add(Calendar.MILLISECOND, -1);
        
        // this month is closed already
        if (BonusList.getFirstOpenMonth(conn).after(sDate)) {
            return "Month closed already";
        }
        
        // check for open orders?
        ObjectSearch os = new ObjectSearch();
        List val = os.search(conn, "Select Count(*) as items From order_header Where " +
        		"bonus_date between ? And ? And order_status_id <= 60 And payment_status=false",
        		new Object[] { new java.sql.Date(sDate.getTime().getTime()), 
                			   new java.sql.Date(eDate.getTime().getTime())});
        PersistableObject po = (PersistableObject) val.get(0);
        int numOfItems = ((Long) po.get("items")).intValue();
        if (numOfItems > 0) {
//            return "Es gibt noch unerledigte bzw. unbezahlte Bestellungen für das " +
//            		"Bonusmonat. Alle Bestellungen für das  Provisionsmonat müssen " +
//            		"bezahlt und mindestens Status 60 haben, damit das Monat " +
//            		"abgeschlossen werden kann!";
        }
        
        // warning message if needed
        val = os.search(conn, "Select Count(*) as items From order_header Where " +
        		"bonus_date between ? And ? And order_status_id > 40 And order_status_id <= 60 " +
        		"And payment_status=false",
        		new Object[] { new java.sql.Date(sDate.getTime().getTime()), 
                			   new java.sql.Date(eDate.getTime().getTime())});
        po = (PersistableObject) val.get(0);
        numOfItems = ((Long) po.get("items")).intValue();
        String message = numOfItems > 0 ? 
            "Es gibt noch unbezahlte Rechnungen für das Bonusmonat. Vor dem " +
			"Erzeugen der Bonusüberweisungen bitte überprüfen!" : null;
        
		try {
			// insert to history
			PreparedStatement ps = conn.prepareStatement(
					"insert into t_worker_bonus_history " +
					"select ? as month, * from t_worker_bonus"
			);
			int month = sDate.get(Calendar.YEAR) * 100 + 
				sDate.get(Calendar.MONTH) + 1;
			
			ps.setInt(1, month);
			ps.execute();
			
			// set date
			ps = conn.prepareStatement(
					"update t_current_date set " + 
					"date_from=?, date_to=?");
			sDate.add(Calendar.MONTH, 1);
			ps.setDate(1, new java.sql.Date(sDate.getTime().getTime()));
			eDate.setTime(sDate.getTime());
	        eDate.add(Calendar.MONTH, 1);
	        ps.setDate(2, new java.sql.Date(eDate.getTime().getTime()));
			ps.execute();
	        
			System.out.println("commit");
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace(System.err);
			tx.rollback();
		}
		return message;
	}
}
