/*
 * OrderHeader.java
 * Created on Apr 30, 2005 
 * Author: 	Lï¿½szlï¿½ Felfï¿½ldi, Etixpert GmbH
 * mail:	laszlo.felfoldi@etixpert.com		
 */
package com.etixpert.evolution.value.order;

import java.math.BigDecimal;
import java.sql.Connection;
import java.text.DateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.log4j.Logger;
import org.jdom2.Element;

import com.etixpert.evolution.ObjectSearch;
import com.etixpert.evolution.PersistableObject;
import com.etixpert.evolution.value.DistributorValue;
import com.etixpert.evolution.value.Value;

public class OrderHeaderValue extends Value {
	private static DateFormat dateformat = DateFormat.getDateInstance(DateFormat.SHORT, Locale.GERMAN);
	
	public static Integer PAYMENT_METHOD_BANK                = new Integer(1);
	public static Integer PAYMENT_METHOD_CC                  = new Integer(2);
	public static Integer PAYMENT_METHOD_PROVISIONSMINDERUNG = new Integer(5);
	
	public static int FLAG_BASIC        		= 0;
	public static int FLAG_NEED_ADDRESS 		= 1;
	public static int FLAG_NEED_DETAILS 		= 2;
	public static int FLAG_NEED_ORDERER_EMAIL 	= 4;
	
	private DistributorValue orderer;
	private int              ordererLevelId;
	private int              ordererLevelSubtypeId;
	private Object           orderId;
	private Object           billId;
	private double           totalNetto;
	private double           totalNettoTrader;
	private double           totalBrutto;
	private double           totalBruttoTrader;
	private double           totalBonus;
	private double           mwst;
	private double           tradeMargin;
	private Object           paymentMethodId;
	private String 			 paymentMethodDesc;
	private Object           shipping;
	private BankAccountValue bankAcocunt;
	private CreditCardValue  creditCard;
	private Date			 orderDate;
	private Date			 bonusDate;
	private Date             billingDate;
	private List             items;
	
	private String  		 deliveryCountryId;
	private String  		 deliveryPostcode;
	private String  		 deliveryCity;
	private String  		 deliveryStreet;
	private String  		 deliveryName;
	private String  		 deliveryTitle; 
	private String			 company;
	
	private String  		 billingCountryId;
	private String  		 billingPostcode;
	private String  		 billingCity;
	private String  		 billingStreet;          
	private String  		 billingName;          
	private String  		 billingTitle;
	
	private boolean			 firstOrder;
	private double			 discountRate;
	private int				 scontoDays;
	//gutschrift
	private boolean			 credit;
	
	public OrderHeaderValue(
			Object aOrderId, Object aBillId, DistributorValue aOrderer, int aOrdererLevelId, int aOrdererLevelSubtypeId,  
			Date aOrderDate, Date aBonusDate, Date aBillingDate,
			double aTotalNetto, double aTotalNettoTrader, double aTotalBrutto, double aTotalBruttoTrader,
			double aTotalBonus, double aTradeMargin, double aMwst, 
			Object aPaymentMethodId, String aPaymentMethodDesc, CreditCardValue aCreditCard, BankAccountValue aBankAccount,
			Object aShipping, String[] deliveryAddress, String[] billingAddress, boolean firstOrder, double discountRate, int scontoDays, boolean credit
			) {
		orderId = aOrderId;
		billId = aBillId;
		orderer = aOrderer;
		ordererLevelId = aOrdererLevelId;
		ordererLevelSubtypeId = aOrdererLevelSubtypeId;
		orderDate = aOrderDate;
		bonusDate = aBonusDate;
		billingDate = aBillingDate;
		totalNetto = aTotalNetto;
		totalNettoTrader = aTotalNettoTrader;
		totalBrutto = aTotalBrutto;
		totalBruttoTrader = aTotalBruttoTrader;
		totalBonus = aTotalBonus;
		mwst  = aMwst;
		paymentMethodId = aPaymentMethodId;
		paymentMethodDesc = aPaymentMethodDesc;
		creditCard = aCreditCard;
		bankAcocunt = aBankAccount;
		tradeMargin = aTradeMargin;
		shipping = aShipping;
		items = new LinkedList();
		
		if (deliveryAddress != null) {
			deliveryCountryId = deliveryAddress[0];
			deliveryPostcode  = deliveryAddress[1];
			deliveryCity      = deliveryAddress[2];
			deliveryStreet    = deliveryAddress[3];
			deliveryTitle     = deliveryAddress[4];
			deliveryName      = deliveryAddress[5];
		}
		
		if (billingAddress != null) {
			billingCountryId  = billingAddress[0];
			billingPostcode   = billingAddress[1];
			billingCity       = billingAddress[2];
			billingStreet     = billingAddress[3];        
			billingTitle      = billingAddress[4];      		
			billingName       = billingAddress[5];      		
		}
		
		this.firstOrder = firstOrder;
		this.discountRate = discountRate;
		this.scontoDays = scontoDays;
		this.company = orderer.getCompany();
		this.credit = credit;
	}
	
	public Object getBillId() {
		return billId;
	}
	
	public Object getOrderId() {
	    return orderId;
	}
	
	public DistributorValue getOrderer() {
		return orderer;
	}
	
	public Date getBonusDate() {
		return bonusDate;
	}
		
	public Date getOrderDate() {
		return orderDate;
	}

	public Date getBillingDate() {
		return billingDate;
	}
	
	public Object getShipping() {
		return shipping;
	}
	
	public double getTotalNetto() {
		return totalNetto;
	}

	public double getTotalBonus() {
		return totalBonus;
	}
	
	public double getBrutto() {
		return (ordererLevelId == 300 || ordererLevelId == 350 || isCustomerDiscount()) ? totalBruttoTrader : totalBrutto;
	}
	
	protected boolean isCustomerDiscount() {
		return ((ordererLevelId >= 100 && ordererLevelId <= 200 && discountRate > 0));
	} 
	
	public double getTradeMargin() {
		return tradeMargin;
	}
	
	public Object getPaymentMethodId() {
		return paymentMethodId;
	}
	
	public CreditCardValue getCreditCard() {
		return creditCard;
	}
	
	public BankAccountValue getBankAccount() {
		return bankAcocunt;
	}
	
	public List getItems() {
		return items;
	}
	
	public void toXML(Element to, int flag) {
		if (flag == FLAG_BASIC) {
			Element item = new Element("item");
			item.setAttribute("Id", "" + billId);
			item.setAttribute("Orderer", "" + orderer.getId());
			item.setAttribute("OrdererLevelId", "" + ordererLevelId);
			item.setAttribute("Netto", "" + totalNetto);
			item.setAttribute("Bonus", "" + totalBonus);
			item.setAttribute("MWSt",  "" + mwst);
			item.setAttribute("OrderDate", dateformat.format(orderDate));
			if (billingDate != null) item.setAttribute("BillingDate", dateformat.format(billingDate));
			
			to.addContent(item);
		}
		
		if ((flag & FLAG_NEED_ADDRESS) != 0) {
			// header element
			Element e = new Element("header");
			e.setAttribute("OrderDate", dateformat.format(orderDate));
			if (billingDate != null) e.setAttribute("BillingDate", dateformat.format(billingDate));
			e.setAttribute("OrderId", string(orderId));
			e.setAttribute("BillId", string(billId));
			e.setAttribute("Payment", string(paymentMethodDesc));
			
			try {
				Integer payMethodIdInt = new Integer(paymentMethodId + "");
				e.setAttribute("PaymentId", string(payMethodIdInt));
			} catch (NumberFormatException nfe) {
				e.setAttribute("PaymentId", "-1");
			}
			
			e.setAttribute("Shipping", string(shipping));
			
			boolean customerDiscount = false;
			if (ordererLevelId >=100 && ordererLevelId <=200 && discountRate > 0) {
				customerDiscount = true;
			}
			
			e.setAttribute("TotalNetto", "" + totalNetto);
			e.setAttribute("TotalNettoTrader", "" + totalNettoTrader);
			e.setAttribute("TotalBrutto", "" + totalBrutto);
			e.setAttribute("TotalBruttoTrader", "" + totalBruttoTrader);
			e.setAttribute("TotalBonus", "" + totalBonus);
			e.setAttribute("FirstOrder", firstOrder ? "true" : "false");
			e.setAttribute("CustomerDiscount", customerDiscount ? "true" : "false");
			e.setAttribute("DiscountRate", "" + discountRate);
			e.setAttribute("SkontoDays", "" + scontoDays);
			e.setAttribute("Credit", credit ? "true" : "false");

			
			to.addContent(e);
			
			// orderer element
			Element o = new Element("orderer");
			o.setAttribute("LevelId", string(ordererLevelId));
			o.setAttribute("LevelSubtypeId", string(ordererLevelSubtypeId));
			o.setAttribute("Id", string(orderer.getId()));
			o.setAttribute("Phone", string(orderer.getPhone()[0]));
			o.setAttribute("VatId", string(orderer.getVatId()));
			e.addContent(o);
	
			// orderer's address
			Element a1 = new Element("address");
			a1.setAttribute("Type", "delivery");
			a1.setAttribute("Title1", deliveryTitle);
			a1.setAttribute("Name", deliveryName);
			a1.setAttribute("Street", deliveryStreet);
			a1.setAttribute("City", deliveryCity);
			a1.setAttribute("Zip", deliveryPostcode);
			a1.setAttribute("Country", deliveryCountryId);
			o.addContent(a1);
	
			Element a2 = new Element("address");
			a2.setAttribute("Type", "billing");
			a2.setAttribute("Title", billingTitle);
			a2.setAttribute("Name", billingName);
			a2.setAttribute("Street", billingStreet);
			a2.setAttribute("City", billingCity);
			a2.setAttribute("Zip", billingPostcode);
			a2.setAttribute("Country", billingCountryId);
			if (company == null)
				company = "";
			a2.setAttribute("Company", company);
			o.addContent(a2);
		}
		
		// details element
		if ((flag & FLAG_NEED_DETAILS) != 0) {
			Element d = new Element("details");
			for (Iterator it = items.iterator(); it.hasNext(); ) {
				OrderDetailsValue od = (OrderDetailsValue) it.next();
				od.toXML(d);
			}
			to.addContent(d);
		}
		
		
	}
	
	public String getDeliveryCountryId() {
		return deliveryCountryId;
	}

	public String getDeliveryPostcode() {
		return deliveryPostcode;
	}

	public String getDeliveryCity() {
		return deliveryCity;
	}

	public String getDeliveryStreet() {
		return deliveryStreet;
	}

	public String getDeliveryName() {
		return deliveryName;
	}

	public String getDeliveryTitle() {
		return deliveryTitle;
	}

	public double getTotalBrutto() {
		return totalBrutto;
	}

	public double getTotalBruttoTrader() {
		return totalBruttoTrader;
	}
	
	public static void toXML(List orders, Element to, int flag) {
		for (Iterator it = orders.iterator(); it.hasNext(); ){
			OrderHeaderValue oh = (OrderHeaderValue) it.next();
			oh.toXML(to, flag);
		}
	}

	private static String string(Object src) {
		return src != null ? "" + src : "";
	}
	
	private static boolean toboolean(Object src) {
		if (src != null) 
			try {
				return ((Boolean)src).booleanValue();
			} catch(Exception ignored) {}
		return false;
	}
	
	protected static double getDoubleValue(PersistableObject po, String name, double def) {
		try {
			return ((BigDecimal) po.getValues().get(name)).doubleValue();
		} catch (Exception e) {}
		return def;
	}

	protected static int getIntValue(PersistableObject po, String name, int def) {
		try {
			return ((Integer) po.getValues().get(name)).intValue();
		} catch (Exception e) {}
		return def;
	}
	
	private static OrderHeaderValue getFromPO(Connection con, PersistableObject po, Map environment, int flag) {
		Map val = po.getValues();
		
		// data about the orderer
		DistributorValue dv = environment != null ? (DistributorValue) environment.get(val.get("ordered_by")) : null;
		if (dv == null && con != null) {
			if (environment != null) return null;
			
			int d_flag = DistributorValue.FLAG_NEED_NOTHING;
			if ((flag & FLAG_NEED_ORDERER_EMAIL) != 0)
				d_flag = DistributorValue.FLAG_NEED_EMAIL;
			
			d_flag = d_flag | DistributorValue.FLAG_NEED_ALLPHONES;
			
			dv = DistributorValue.getById(con, val.get("ordered_by"), d_flag);
		}
		int ordererLevelId = getIntValue(po, "orderer_level_id", 0);
		int ordererLevelSubtypeId = getIntValue(po, "orderer_level_subtype_id", 0);
		
		BankAccountValue bankAccount = null;
		CreditCardValue creditCard = null;

		if (flag != FLAG_BASIC) {
			if (val.get("pay_method_id").equals(OrderHeaderValue.PAYMENT_METHOD_BANK)) {
				bankAccount = new BankAccountValue(
						val.get("b_id"), string(val.get("bank")), 
						string(val.get("bank_code")), string(val.get("account_number")), 
						string(val.get("bic")), string(val.get("iban")), (Date) val.get("debit_date"), 
						toboolean( val.get("recurring")));
			} 
			
			if (val.get("pay_method_id").equals(OrderHeaderValue.PAYMENT_METHOD_CC)) {
				creditCard = new CreditCardValue(string(val.get("cc_number")), string(val.get("org")), (Date) val.get("valid_to"));
			} 
		}
		
		double totalNetto = getDoubleValue(po, "total_netto", 0);
		double totalNettoTrader = getDoubleValue(po, "total_netto_trader", 0);
		double totalBrutto = getDoubleValue(po, "total_brutto", 0);
		boolean credit = totalBrutto < 0;
		double totalBruttoTrader = getDoubleValue(po, "total_brutto_trader", 0);
		double totalBonus = getDoubleValue(po, "total_bonus", 0);
		
		double mwst =  totalBrutto - totalNetto;
		double tradeMargin = getDoubleValue(po, "total_trade_margin", 0);
		
		double discount_rate = ((BigDecimal)val.get("discount_rate")).doubleValue();
		

		
		OrderHeaderValue oh = new OrderHeaderValue(
				val.get("o_id"), val.get("bill_id"), dv, ordererLevelId, ordererLevelSubtypeId, 
				(Date) val.get("order_date"), (Date) val.get("bonus_date"), (Date) val.get("billing_date"), 
				totalNetto, totalNettoTrader, totalBrutto, totalBruttoTrader,
				totalBonus, tradeMargin, mwst, val.get("pay_method_id"), 
				(String) val.get("pay_method_descr"), creditCard, bankAccount, val.get("shipping_descr"),
				(flag & FLAG_NEED_ADDRESS) != 0 ? new String[] {
							  string(val.get("delivery_country_id")), string(val.get("delivery_postcode")),
						      string(val.get("delivery_city")), string(val.get("delivery_street")), 
						      string(val.get("delivery_title")), string(val.get("delivery_name"))} : null,
 			    (flag & FLAG_NEED_ADDRESS) != 0 ? new String[] {
                              string(val.get("billing_country_id")), string(val.get("billing_postcode")),
			      			  string(val.get("billing_city")), string(val.get("billing_street")), 
			      			  string(val.get("billing_title")), string(val.get("billing_name"))} : null,
			      			  ((Boolean)val.get("first_order")).booleanValue(),
			      			  discount_rate,
			      			((Integer)val.get("sconto_days")).intValue(),
			      			credit
		);
		
		// list of the ordered items
		if ((flag & FLAG_NEED_DETAILS) != 0) { 
			List details = OrderDetailsValue.getByOrderId(con, val.get("o_id"));
			oh.items.addAll(details);
		}

		return oh;
	}
	
	
	public static OrderHeaderValue getById(Connection con, Object id, Map environment, int flag) throws Exception {
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	Object idTyped = (id instanceof String)? new Integer((String)id) : id;
        	items = os.search(con,
        			"select o.id as o_id, b.id as b_id, * From order_header o " + 
        			(flag != FLAG_BASIC ? 
    						"Left Join bank_account b On o.account_id = b.id " + 
    						"Left Join creditcard c On o.account_id = c.id " + 
    						"Left Join cc_org co On c.cc_org_id = co.id " : "") +  
        			"Where o.id=?", new Object[] {idTyped});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        	throw e;
        }
        
		if (items != null && items.size() > 0) {
    		PersistableObject po = (PersistableObject) items.get(0);
    		return getFromPO(con, po, environment, flag);
        }
		return null;
	}
	
	public static OrderHeaderValue getById(Connection con, Object id, int flag) throws Exception {
		 return getById(con, id, null, flag);
	}
		
	public static OrderHeaderValue getByBillId(Connection con, Object id, int flag) throws Exception {
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	Object idTyped = (id instanceof String)? new Integer((String)id) : id;
        	items = os.search(con,
        			"select o.id as o_id, b.id as b_id, * From order_header o " + 
        			(flag != FLAG_BASIC ? 
    						"Left Join bank_account b On o.account_id = b.id " + 
    						"Left Join creditcard c On o.account_id = c.id " + 
    						"Left Join cc_org co On c.cc_org_id = co.id " : "") +  
        			"Where o.bill_id=?", new Object[] {idTyped});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        	throw e;
        }
        
		if (items != null && items.size() > 0) {
    		PersistableObject po = (PersistableObject) items.get(0);
    		return getFromPO(con, po, null, flag);
        }
		return null;
	}

	public static List getPayedByDate(Connection con, Date from, Date to, Map environment, int flag) {
		List ret = new LinkedList();
        
        // load all distributors 
        List items = null;
        
	    Logger logger = Logger.getLogger("OrderHeaderValue");
	    logger.info(from + " " + to);

		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(con,
        			"select o.id as o_id, b.id as b_id, * From order_header o " + 
        			(flag != FLAG_BASIC ? 
    						"Left Join bank_account b On o.account_id = b.id " + 
    						"Left Join creditcard c On o.account_id = c.id " + 
    						"Left Join cc_org co On c.cc_org_id = co.id " : "") + 
					"Where bonus_date between ? And ? And bill_id is not null " + 
					"And (payment_status=true Or pay_method_id In (1,2,5)) " +
					"And order_status_id <> 90 Order By bonus_date",
        			new Object[] {new java.sql.Date(from.getTime()), new java.sql.Date(to.getTime() - 1)});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get an order
        		PersistableObject po = (PersistableObject) it.next();
        		OrderHeaderValue oh = getFromPO(con, po, environment, flag);
        		if (oh != null) ret.add(oh);
        	}
        }
		return ret;
	}

	public static List getByDate(Connection con, Date from, Date to, Map environment, int flag) {
		List ret = new LinkedList();
        
        // load all distributors 
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(con,
        			"select o.id as o_id, b.id as b_id, * From order_header o " + 
        			(flag != FLAG_BASIC ? 
    						"Left Join bank_account b On o.account_id = b.id " + 
    						"Left Join creditcard c On o.account_id = c.id " + 
    						"Left Join cc_org co On c.cc_org_id = co.id " : "") + 
					"Where bonus_date Between ? And ? " +
					"And order_status_id <> 90 Order By bonus_date",
        			new Object[] {new java.sql.Date(from.getTime()), new java.sql.Date(to.getTime() - 1)});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get an order
        		PersistableObject po = (PersistableObject) it.next();
        		OrderHeaderValue oh = getFromPO(con, po, environment, flag);
        		if (oh != null) ret.add(oh);
        	}
        }
		return ret;
	}

	public static List getByBillingDate(Connection con, Date from, Date to, Map environment, int flag) {
		List ret = new LinkedList();
        
        // load all distributors 
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(con,
        			"select o.id as o_id, b.id as b_id, * From order_header o " + 
        			(flag != FLAG_BASIC ? 
    						"Left Join bank_account b On o.account_id = b.id " + 
    						"Left Join creditcard c On o.account_id = c.id " + 
    						"Left Join cc_org co On c.cc_org_id = co.id " : "") + 
					"Where billing_date Between ? And ? " +
					"And order_status_id <> 90 Order By billing_date",
        			new Object[] {new java.sql.Date(from.getTime()), new java.sql.Date(to.getTime() - 1)});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get an order
        		PersistableObject po = (PersistableObject) it.next();
        		OrderHeaderValue oh = getFromPO(con, po, environment, flag);
        		if (oh != null) ret.add(oh);
        	}
        }
		return ret;
	}	
	
	public static List getByDatePayment(Date from, Date to, Object payment, Map environment, int flag) {
		List ret = new LinkedList();
        
        // load all distributors 
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(
        			"select o.id as o_id, b.id as b_id, * From order_header o " + 
        			(flag != FLAG_BASIC ? 
						"Left Join bank_account b On o.account_id = b.id " + 
						"Left Join creditcard c On o.account_id = c.id " + 
						"Left Join cc_org co On c.cc_org_id = co.id " : "") + 
					"Where bonus_date Between ? And ? And payment_type_id=? " +
        			"And order_status_id <> 90 Order By bonus_date",
        			new Object[] {new java.sql.Date(from.getTime()), new java.sql.Date(to.getTime() - 1), payment});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get an order
        		PersistableObject po = (PersistableObject) it.next();
        		OrderHeaderValue oh = getFromPO(null, po, environment, flag);
        		if (oh != null) ret.add(oh);
        	}
        }
		return ret;
	}
}
