/*
 * BonusList.java
 * Created on 2005.05.30.
 * 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.FileOutputStream;
import java.io.FileReader;
import java.io.ObjectInputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.output.Format;
import org.jdom2.output.XMLOutputter;
import org.jdom2.xpath.XPath;

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.event.CloseEvent;
import com.etixpert.evolution.event.OrderEvent;
import com.etixpert.evolution.value.DistributorValue;
import com.etixpert.evolution.value.bonus.BonusDefinitionValue;
import com.etixpert.evolution.value.bonus.BonusMetrics;
import com.etixpert.evolution.value.order.OrderHeaderValue;

public class BonusList {
	public static int TYPE_BONUSLIST = 1;
	public static int TYPE_ORGANIZATION = 2;
	public static int TYPE_BONUSDETAIL = 3;

	private static String xslt;
	private static String xslt2;
	private static String xslt3;

	public static Calendar getMonthBegin(Date date) {
		// get the date of the beginning of the month
		Calendar from = Calendar.getInstance();
		from.setTime(date);
		from.set(Calendar.DAY_OF_MONTH, 1);
		from.set(Calendar.HOUR_OF_DAY, 0);
		from.set(Calendar.MINUTE, 0);
		from.set(Calendar.SECOND, 0);
		from.set(Calendar.MILLISECOND, 0);
		return from;
	}

	public static Element getLevelInfo(Connection conn) {
		Element ret = new Element("levels");
		ObjectSearch os = new ObjectSearch();
		try {
			List items = os.search(conn,
					"select * from user_roles Order By id", null);
			for (Iterator it = items.iterator(); it.hasNext();) {
				PersistableObject po = (PersistableObject) it.next();
				Element ae = new Element("item");
				ae.setAttribute("Id", "" + po.get("id"));
				ae.setAttribute("Name", "" + po.get("descr"));
				ret.addContent(ae);
			}
		} catch (SQLException e) {
			e.printStackTrace(System.err);
		}
		return ret;
	}

	private static String getViewRoot(Connection conn, String id) {
		String ret = id;
		ObjectSearch os = new ObjectSearch();
		try {
			List items = os
					.search(
							conn,
							"select r.* from distributor d, general_rights r Where d.level_id = r.role_id And d.id=?",
							new Object[] { new Integer(id) });
			for (Iterator it = items.iterator(); it.hasNext();) {
				PersistableObject po = (PersistableObject) it.next();
				String role = po.getString("bonus_list_view", "ST");
				if (role.toUpperCase().equals("A"))
					return "0";
			}
		} catch (SQLException e) {
			e.printStackTrace(System.err);
		}
		return ret;
	}

	public static Calendar getFirstOpenMonth(Connection conn) {
		ObjectSearch os = new ObjectSearch();
		try {
			List items = os
					.search(
							conn,
							"select max(month) as month From t_worker_bonus_history");
			PersistableObject po = (PersistableObject) items.get(0);
			Integer month = (Integer) po.get("month");
			Calendar ret = Calendar.getInstance();
			if (month == null) {
				ret.set(Calendar.YEAR, 2012);
				ret.set(Calendar.MONTH, 8);
			} else {
				ret.set(Calendar.YEAR, month / 100);
				ret.set(Calendar.MONTH, month % 100 - 1);
			}
			ret.set(Calendar.DAY_OF_MONTH, 1);
			ret.set(Calendar.HOUR_OF_DAY,0);
			ret.set(Calendar.MINUTE,0);
			ret.set(Calendar.SECOND,0);
			ret.set(Calendar.MILLISECOND,0);
			ret.add(Calendar.MONTH, 1);
			return ret;
		} catch (SQLException e) {
			e.printStackTrace(System.err);
		}
		return null;
	}

	public static Calendar getFirstOpenMonth() {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();
		return getFirstOpenMonth(conn);
	}

	private static Object[] getFromHistory(Connection conn, Date date, int flag) {
		Document res = null;
		ObjectSearch os = new ObjectSearch();
		try {
			List rows = os.search(conn,
					"select * from bonus_history Where id=?",
					new Object[] { new java.sql.Date(date.getTime()) });
			if (rows.size() > 0) {
				byte[] data = (byte[]) ((PersistableObject) rows.get(0))
						.get("data");
				ByteArrayInputStream bais = new ByteArrayInputStream(data);
				ObjectInputStream ois = new ObjectInputStream(bais);
				res = (Document) ois.readObject();
			}
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}

		if (res == null) {
			res = new Document();
			Element root = new Element("evolution");
			res.setRootElement(root);
			root.addContent(new Element("names"));
			root.addContent(new Element("levels"));
			root.addContent(new Element("bonus"));
		}

		Map environment = DistributorValue.getFromHistory(conn, date, flag
				| DistributorValue.FLAG_NEED_INDIVIDUAL_BONUS
				| DistributorValue.FLAG_NEED_ALLPHONES
				| DistributorValue.FLAG_NEED_EMAIL
				| DistributorValue.FLAG_NEED_ADDRESS
				| DistributorValue.FLAG_NAME_SWAPPED);
		return new Object[] { res, environment };
	}

	private static Document calculate(Connection conn, Date from, Date to, Map environment) {
		try {
			boolean fromHistory = getFirstOpenMonth(conn).getTimeInMillis() > from.getTime();
			if (!fromHistory) {
				// update tables
				System.out.println("using views");
				String command = "";
				// command += "delete from t_distributor_order;";
				command += "create temp table temp_distributor_order(id int primary key, total_netto numeric(18,2), total_bonus numeric(18,2), total_trade_margin numeric(18,2), ncs int) on commit drop;";
				command += "insert into temp_distributor_order"
						+ " select d.id,"
						+ " sum(h.total_netto) as total_netto,"
						+ " sum(h.total_bonus) as total_bonus,"
						+ " sum(h.total_trade_margin) as total_trade_margin,"
						+ " ncs(d.id) as ncs "
						+ "from distributor d left join order_header h On h.ordered_by=d.id "
						+ "where h.order_date >= ? and h.order_date < ? and "
						+ "order_status_id=60 " + "group by d.id;";
				// command += "delete from t_worker_activation;";
				command += "create temp table temp_worker_activation(id int primary key, sponsor int, total_netto numeric(18, 2), total_bonus numeric(18,2)) on commit drop;";
				/*
				 * command += "insert into t_worker_activation " + "select id," + "
				 * acs(id) as sponsor," + " total_netto," + " total_bonus " + "from
				 * t_worker_order;";
				 */
	
				command += "insert into temp_worker_activation select t_worker_order.id, acsid as sponsor, "
						+ "total_netto, total_bonus from t_worker_order left join acscache on "
						+ "(t_worker_order.id=acscache.id);";
				/*
				 * PreparedStatement ps = conn.prepareStatement(command);
				 * ps.setDate(1, new java.sql.Date(from.getTime())); ps.setDate(2,
				 * new java.sql.Date(to.getTime())); ps.execute();
				 */
				System.out.println("tables updated");
			}
		
			long start = System.currentTimeMillis();
			BonusDataNew.getFromDB(conn, from, environment, fromHistory, null);
			new BonusDataNew((DistributorValue) environment.get(""), 0, 0.0,
					0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,
					0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0);
			
			long end = System.currentTimeMillis();
			System.out.println("out of BonudDataNew in "
					+ ((end - start) / 1000.0) + " sec");
			// we need the total revenue from the database directly, because
			// there are orders
			// that are not included in list "orders"
			double revenue = 0;
			ObjectSearch os = new ObjectSearch();

			List res = os
					.search(
							conn,
							"Select Sum(total_netto) As total "
									+ "From order_header Where "
									+ "bonus_date between ? and ? and order_status_id < 90 and bill_id > 0",
							new Object[] { new java.sql.Date(from.getTime()),
									new java.sql.Date(to.getTime() - 1) });
			PersistableObject po = (PersistableObject) res.get(0);
			if (po != null) {
				BigDecimal val = (BigDecimal) po.get("total");
				revenue = val != null ? val.doubleValue() : 0;
			}
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}

		DistributorValue dv = (DistributorValue) environment
				.get(new Integer(0));
		BonusDataNew bd = dv.getActBonusNew();
		if (bd != null) {
			// bd.addToOV(0,0, revenue - bd.getxOV());
		}

		setOrganizationValues(dv);

		// create XML document
		Document document = new Document();
		Element root = new Element("evolution");
		document.setRootElement(root);

		// add info about the distributors
		Element names = new Element("names");
		DistributorValue.toXML(environment, names);
		root.addContent(names);

		// add info about the calculation factors

		Element levelDesc = getLevelInfo(conn);

		Engine.toXML(levelDesc);
		root.addContent(levelDesc);

		// add the bonus info
		Element neededBonusData = new Element("bonus");

		root.addContent(BonusDataNew.toXML(environment));

		// add orders
		// Element orders = new Element("orders");
		// XMLUtils.filter(BonusData.toXML(environment), neededBonusData, id);
		// root.addContent(neededBonusData);

		return document;
	}

	private static Document calculateOld(Connection conn, Date from, Date to,
			Map environment) {
		List orders = OrderHeaderValue.getPayedByDate(conn, from, to,
				environment, OrderHeaderValue.FLAG_BASIC);

		// process the orders
		int debug = orders.size();
		for (Iterator it = orders.iterator(); it.hasNext();) {
			OrderHeaderValue oh = (OrderHeaderValue) it.next();
			OrderEvent event = new OrderEvent(oh);
			Engine.handleEvent(event, environment);
		}

		// we need the total revenue from the database directly, because there
		// are orders
		// that are not included in list "orders"
		double revenue = 0;
		try {
			ObjectSearch os = new ObjectSearch();
			List res = os
					.search(
							conn,
							"Select Sum(total_netto) As total "
									+ "From order_header Where "
									+ "bonus_date between ? and ? and order_status_id < 90 and bill_id > 0",
							new Object[] { new java.sql.Date(from.getTime()),
									new java.sql.Date(to.getTime() - 1) });
			PersistableObject po = (PersistableObject) res.get(0);
			if (po != null) {
				BigDecimal val = (BigDecimal) po.get("total");
				revenue = val != null ? val.doubleValue() : 0;
			}
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		DistributorValue dv = (DistributorValue) environment
				.get(new Integer(0));
		BonusData bd = dv.getActBonus();
		if (bd != null) {
			bd.addToOV(0, 0, revenue - bd.getxOV());
		}

		// sending month closing event
		CloseEvent event = new CloseEvent(Calendar.getInstance().getTime(),
				(DistributorValue) environment.get(""));
		Engine.handleEvent(event, environment);

		// create XML document
		Document document = new Document();
		Element root = new Element("evolution");
		document.setRootElement(root);

		// add info about the distributors
		Element names = new Element("names");
		DistributorValue.toXML(environment, names);
		root.addContent(names);

		// add info about the calculation factors
		Element levelDesc = getLevelInfo(conn);
		Engine.toXML(levelDesc);
		root.addContent(levelDesc);

		// add the bonus info
		Element neededBonusData = new Element("bonus");
		root.addContent(BonusData.toXML(environment));

		// add orders
		// Element orders = new Element("orders");
		// XMLUtils.filter(BonusData.toXML(environment), neededBonusData, id);
		// root.addContent(neededBonusData);

		return document;
	}

	public static Object[] calculate(Connection conn, Date date, String path,
			int flag) throws Exception {
		try {
			Date firstOpenMonth = getFirstOpenMonth(conn).getTime();
			date = getMonthBegin(date).getTime();
			Document res = null;

			Map environment = DistributorValue.getAll(conn, flag
					| DistributorValue.FLAG_NEED_INDIVIDUAL_BONUS
					| DistributorValue.FLAG_NEED_ALLPHONES
					| DistributorValue.FLAG_NEED_EMAIL
					| DistributorValue.FLAG_NEED_ADDRESS
					| DistributorValue.FLAG_NAME_SWAPPED);

			for (Iterator it = environment.values().iterator(); it.hasNext();) {
				DistributorValue dv = (DistributorValue) it.next();
				dv.setActBonus(null);
			}
			
			Calendar sDate = Calendar.getInstance();
			sDate.setTime(date);
			getMonthBegin(firstOpenMonth);

			Calendar eDate = Calendar.getInstance();
			eDate.setTime(sDate.getTime());
			eDate.add(Calendar.MONTH, 1);

			res = calculate(conn, sDate.getTime(), eDate.getTime(),
				environment);

			if (date.before(firstOpenMonth)) {
				// set printing option
				Document doc = (Document) res;
				doc.getRootElement().addContent(new Element("print_enabled"));
			}
			return new Object[] { res, environment };
		} catch (Exception e) {
			throw e;
		}
	}

	public static Object[] calculate(Connection conn, Date date, String path)
			throws Exception {
		return calculate(conn, date, path, 0);
	}

	public static void getTotals(Element name, Element bonus, Map environment) {
		String id = name.getAttributeValue("Id");
		if (id == null || id.length() == 0)
			return;

		double db = 0;
		double tm = 0;
		double tb1 = 0;
		double tb2 = 0;
		double tb3 = 0;

		try {
			db = Double
					.parseDouble(bonus.getAttributeValue("DistributorBonus"));
		} catch (Exception e) {
		}
		try {
			tm = Double
					.parseDouble(bonus.getAttributeValue("TotalTradeMargin"));
		} catch (Exception e) {
		}
		try {
			tb1 = Double
					.parseDouble(bonus.getAttributeValue("TeamBonusLevel1"));
		} catch (Exception e) {
		}
		try {
			tb2 = Double
					.parseDouble(bonus.getAttributeValue("TeamBonusLevel2"));
		} catch (Exception e) {
		}
		try {
			tb3 = Double
					.parseDouble(bonus.getAttributeValue("TeamBonusLevel3"));
		} catch (Exception e) {
		}

		DistributorValue dv = (DistributorValue) environment
				.get(new Integer(id));
		if (dv != null) {
			dv.addToNettoBonusToPay(db + tm + tb1 + tb2 + tb3);
		}
	}

	public static void insertTotals(Connection conn, Element root, Integer id,
			Map env, Date date) {
		double netto = 0;
		double mwst = 0;
		double revenue = 0;
		try {
			// get the values from the bonus data
			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"))) {
						getTotals(name, bonus, env);
					}
				}
			}

			// calculate the sum of them
			for (Iterator it = env.values().iterator(); it.hasNext();) {
				DistributorValue dv = (DistributorValue) it.next();
				if (("" + dv.getId()).length() == 8) {
					double val = Math.round(dv.getNettoBonusToPay() * 100) * .01;
					netto += val;
					// 2010.03.01 MWSt shoud not be paid for non Austrian
					// distributors
					if (("" + dv.getId()).startsWith("43")) {
						mwst += Math.round(val * dv.getVATFactor()) * .01;
					}
				}
			}

			ObjectSearch os = new ObjectSearch();

			Calendar sDate = getMonthBegin(date);
			Calendar eDate = Calendar.getInstance();
			eDate.setTime(sDate.getTime());
			eDate.add(Calendar.MONTH, 1);

			List res = os
					.search(
							conn,
							"Select Sum(total_bonus) As total "
									+ "From order_header Where "
									+ "bonus_date between ? and ? and order_status_id < 90 and bill_id > 0 and "
									+ "ordered_by in (select * from st(?))",
							new Object[] {
									new java.sql.Date(sDate.getTime().getTime()),
									new java.sql.Date(
											eDate.getTime().getTime() - 1), id });
			PersistableObject po = (PersistableObject) res.get(0);
			if (po != null) {
				BigDecimal val = (BigDecimal) po.get("total");
				revenue = val != null ? val.doubleValue() : 0;
			}
		} catch (Exception ex) {
			ex.printStackTrace(System.err);
		}

		Element total = new Element("total");
		total.setAttribute("Netto", "" + netto);
		total.setAttribute("VAT", "" + mwst);
		total.setAttribute("Ratio", ""
				+ Math.round(revenue > 0 ? netto * 10000 / revenue : 0) * 0.01);
		root.addContent(total);
	}

	private static double setOrganizationValues(DistributorValue dv) {
		List children = dv.getChildren();
		BonusDataNew data = dv.getActBonusNew();
		double ret = data != null ? data.getTotalBonus() : 0;
		for (Iterator it = children.iterator(); it.hasNext();) {
			DistributorValue cdv = (DistributorValue) it.next();
			ret += setOrganizationValues(cdv);
		}
		dv.setOrganizationValue(ret);
		return ret;
	}

	public static String generate(Integer id, Integer rootId, Date date,
			int type, String path) throws Exception {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();

		try {
			// calculate the xml file
			Object[] res = calculate(conn, date, path);
			Document doc = (Document) res[0];

			Format format = Format.getRawFormat();

			Map env = (Map) res[1];

			// filter the bonusdate according to the value of id and rootId
			Element root = doc.getRootElement();
			Element src = root.getChild("names");
			Element dummy = new Element("dummy");
			Element dst = new Element("names");
			// System.out.println("Filter xml");
			XMLUtils.filter(src, dummy, getViewRoot(conn, "" + rootId));
			XMLUtils.filter(dummy, dst, "" + id);
			// System.out.println("Filtered xml");
			src.detach();
			root.addContent(dst);

			insertTotals(conn, root, id, env, date);
			// System.out.println("Inserted totals");
			// transform the xsml
			if (type == BonusList.TYPE_ORGANIZATION) {
				// 2008.02.12 we need to modify the active fields in the xml

				List nodes = XPath.selectNodes(root, "/evolution/names//item");
				for (Iterator it = nodes.iterator(); it.hasNext();) {
					Element item = (Element) it.next();
					String cid = item.getAttributeValue("Id");
					DistributorValue dv = (DistributorValue) env
							.get(new Integer(cid));
					if (dv != null) {
						String active = "" + dv.getActive();
						if (!dv.getActive() && dv.getDeactivationDate() != null) {
							active = dv.getDeactivationDate().after(date) ? "thismonth"
									: "false";
						}
						item.setAttribute("active", active);
					}
				}

				if (xslt == null) {
					StringBuffer sb = new StringBuffer();
					BufferedReader reader = new BufferedReader(new FileReader(
							path + "/xsl/html/bonustransform.xsl"));
					for (String line = null; (line = reader.readLine()) != null; sb
							.append(line))
						;
					xslt = sb.toString();
				}
				long start = System.currentTimeMillis();
				String res2 = XMLUtils.transform(doc, xslt, false);
				long end = System.currentTimeMillis();
				System.out.println("xslt duration with bonustransform.xml: "
						+ ((end - start) / 1000.0) + " sec");
				return res2;
			} else if (type == TYPE_BONUSLIST || type == TYPE_BONUSDETAIL) {
				if (xslt2 == null) {
					StringBuffer sb = new StringBuffer();
					BufferedReader reader = new BufferedReader(new FileReader(
							path + "/xsl/html/bonustransform2.xsl"));
					for (String line = null; (line = reader.readLine()) != null; sb
							.append(line))
						;
					xslt2 = sb.toString();
				}
				if (type == TYPE_BONUSDETAIL) {
					doc.getRootElement().addContent(new Element("detail"));
				}

				long start = System.currentTimeMillis();
				String res2 = XMLUtils.transform(doc, xslt2, false);
				long end = System.currentTimeMillis();
				System.out.println("xslt duration with bonustransform2.xml: "
						+ ((end - start) / 1000.0) + " sec");
				return res2;
			}
			System.out.println("Returning null");
			return null;

		} finally {
			HibernateUtil.closeSession();
		}
	}

	public static byte[] generatePDF(Integer id, Integer rootId, Date date,
			int type, String path) throws Exception {
		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();

		// calculate the xml file
		Object[] res = calculate(conn, date, path);
		Document doc = (Document) res[0];
		Map env = (Map) res[1];

		// filter the bonusdate according to the value of id and rootId
		Element root = doc.getRootElement();
		Element src = root.getChild("names");
		Element dummy = new Element("dummy");
		Element dst = new Element("names");
		Element month = new Element("month");

		XMLUtils.filter(src, dummy, getViewRoot(conn, "" + rootId));
		XMLUtils.filter(dummy, dst, "" + id);
		src.detach();
		root.addContent(dst);

		SimpleDateFormat sdf = new SimpleDateFormat("MM.yyyy");
		month.setAttribute("Value", sdf.format(date));
		root.addContent(month);

		insertTotals(conn, root, id, env, date);

		if (type == TYPE_BONUSLIST || type == TYPE_BONUSDETAIL) {
			if (xslt3 == null) {
				StringBuffer sb = new StringBuffer();
				BufferedReader reader = new BufferedReader(new FileReader(path
						+ "/xsl/pdf/bonustransform2.xsl"));
				for (String line = null; (line = reader.readLine()) != null; sb
						.append(line))
					;
				xslt3 = sb.toString().replaceAll("WEB-INF", path);
			}
			if (type == TYPE_BONUSDETAIL) {
				doc.getRootElement().addContent(new Element("detail"));
			}
			Element info = new Element("info");
			info.setAttribute("date", new SimpleDateFormat("yyyyMM")
					.format(date));
			doc.getRootElement().addContent(info);

			Logger logger = Logger.getLogger("mainControl.BonusList");
			logger.info("generatePDF:" + id + "," + rootId + "," + date + ","
					+ doc);
			return XMLUtils.transformPDF(doc, xslt3);
		}
		return null;
	}
}
