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

import java.sql.Connection;
import java.util.Calendar;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
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.app.bonus.BonusData;
import com.etixpert.evolution.app.bonus.BonusDataNew;
import com.etixpert.evolution.app.utils.ElementFilter;
import com.etixpert.evolution.value.bonus.IndividualBonusValue;
import com.etixpert.evolution.value.order.BankAccountValue;

public class DistributorValue extends Value {	
	public static int FLAG_NEED_NOTHING          = 0;
	public static int FLAG_NEED_BANKINFO         = 1;
	public static int FLAG_NEED_INDIVIDUAL_BONUS = 2;
	public static int FLAG_NEED_ADDRESS          = 4;
	public static int FLAG_NEED_ALLPHONES        = 8;
	public static int FLAG_NEED_EMAIL            = 16;
	public static int FLAG_NEED_ALL              = 255;

	public static int FLAG_NAME_SWAPPED          = 0x1000;
	
	public static class Address {
		String street;
		String country;
		String zipCode;
		String city;
		
		public Address(String aStreet, String aCountry, String aZipCode, String aCity) {
			street = aStreet;
			country = aCountry;
			zipCode = aZipCode;
			city = aCity;
		}
	}
	
	private Object    		 id;
	private DistributorValue sponsor;
	private Object    		 levelId;
	private String           title;
	private String			 levelAddress;	
	private String    		 name;
	private Address          address;
	private String           country;
	private List			 bonusHandlers;
	private List             children;
	private String[]         phone;
	private String           email;
	private boolean          transferBonus;
	private boolean          customerService;
	private double           vatFactor;
	private String           vatId;
	private boolean          active;
	private BankAccountValue baValue;
	private Object			 partnerTypeId;
	private BonusData        actBonus;
	private BonusDataNew     actBonusNew;
	private double           nettoBonusToPay;
	private boolean          withoutFirstOurder;
	private boolean          hasOpenOrders;
	private Map              auxMetrics;
	private Date  			 deactivationDate;
	private double           organizationValue;
	private String 			 company;
	
	public DistributorValue(Object aId, DistributorValue aSponsor, Object aLevelId, 
	        				String aLevelAddress, String aTitle, String aName, String aCompany, 
							String[] aPhone, boolean aCustomerService, BankAccountValue aBAValue, double aVatFactor, 
							String aVatId, boolean aActive, boolean aTransferBonus, Object aPartnerTypeId, int flags) {
		id = aId;
		sponsor = aSponsor;
		vatFactor = aVatFactor;
		levelId = aLevelId != null ? aLevelId : "";
		title = aTitle != null ? aTitle : "";
		levelAddress = aLevelAddress != null ? aLevelAddress : "";
		name = aName;
		address = null;
		phone = aPhone;
		children = new LinkedList();
		actBonus = null;
		actBonusNew = null;
		baValue = aBAValue;
		customerService = aCustomerService;
		bonusHandlers = null;
		vatId = aVatId; 
		active = aActive;
		transferBonus = aTransferBonus;
		auxMetrics = null;
		partnerTypeId = aPartnerTypeId;
		organizationValue = 0;
		company = aCompany;
	}
	


	public Object getId() {
		return id;
	}
	
	public DistributorValue getSponsor() {
		return sponsor;
	}
	
	public void setSponsor(DistributorValue aSponsor) {
		sponsor = aSponsor;
		if (sponsor != null) sponsor.children.add(this);
	}
	
	public Object getLevelId() {
		return levelId;
	}
	
	public void setLevelId(Object id) {
		levelId = id;
	}
	
	public Object getPartnerTypeId() {
		return partnerTypeId;
	}

	public Address getAddress() {
		return address;
	}
	
	public String getName() {
		return name;
	}

	public String getCompany() {
		return company;
	}

	public void setCompany(String company) {
		this.company = company;
	}	
	
	public double getVATFactor() {
		return vatFactor;
	}
	
	public int getPaymentMethod() {
		return 1;
	}
    
	public String getCountry() {
	    return country;
    }
    
	public String[] getPhone() {
		return phone;
	}
	
	public String getEmail() {
		return email;
	}
	
	public boolean getCustomerService() {
		return customerService;
	}
	
	public boolean getTransferBonus() {
		return transferBonus;
	}
	
	public BankAccountValue getBankAccountValue() {
		return baValue;
	}
  
	public String getVatId() {
		return vatId;
	}
	
	public List getChildren() {
		return children;
	}
	
	public boolean getActive() {
		return active;
	}
	
	public Date getDeactivationDate() {
		return deactivationDate;
	}
	
	public List getBonusHandlers() {
		return bonusHandlers;
	}
	
	public BonusData getActBonus() {
		return actBonus;
	}

	public void setActBonus(BonusData aActBonus) {
		actBonus = aActBonus;
	}

	public BonusDataNew getActBonusNew() {
		return actBonusNew;
	}
	
	public void setActBonusNew(BonusDataNew aActBonusNew) {
		actBonusNew = aActBonusNew;
	}
	
	public double getNettoBonusToPay() {
		return nettoBonusToPay;
	}

	public void setNettoBonusToPay(double val) {
		nettoBonusToPay = val;
	}

	public void addToNettoBonusToPay(double val) {
		nettoBonusToPay += val;
	}

	public boolean getWithoutFirstOrder() {
		return withoutFirstOurder;
	}

	public void setWithoutFirstorder(boolean val) {
		withoutFirstOurder = val;
	}

	public void setHasOpenOrders() {
		hasOpenOrders = true;
	}
	
	public boolean getHasOpenOrders() {
		return hasOpenOrders;
	}

	public Object getAuxMetric(String name) {
	    if (auxMetrics != null) 
	        return auxMetrics.get(name);
	    return null;
	}
	
	public void setAuxMetric(String name, Object val) {
	    if (auxMetrics == null && val != null) 
	        auxMetrics = new Hashtable();
	    if (val != null) 
	        auxMetrics.put(name, val);
	    else if (auxMetrics != null) 
	        auxMetrics.remove(name);
	}
	
	public void setOrganizationValue(double val) {
		organizationValue = val;
	}
		
	private void fromXML(Element source, Map to, int flag) {
		for (Iterator it = source.getChildren().iterator(); it.hasNext();) {
			Element child = (Element) it.next();
			
			/*
			 * TODO implementation 
			 */
			BankAccountValue aBAValue = new BankAccountValue(null, "", "12000", "1234567890", "", "", null, false);
			
			DistributorValue act = new DistributorValue(child.getAttributeValue("Id"), this, 
					child.getAttributeValue("LevelId"), 
					child.getAttributeValue("L"), child.getAttributeValue("T"), child.getAttributeValue("Name"), child.getAttributeValue("Company"), 
					new String[] {child.getAttributeValue("Phone"), "", ""},
					child.getAttribute("CustomerService").equals("true"), aBAValue, 
					Double.parseDouble(child.getAttributeValue("VatFactor")), "", 
					!child.getAttributeValue("active").equals("false"), !child.getAttributeValue("transferBonus").equals("false"),
					null, flag);
			to.put(act.getId(), act);
			
			/*
			 * DEBUG
			 */  
			act.bonusHandlers = new LinkedList();
			act.bonusHandlers.add(new IndividualBonusValue("desc", new Date(0), new Date(System.currentTimeMillis() * 2), "OV", 0, 100, "PV", 1, 25.0, 0));
			
			children.add(act);
			act.fromXML(child, to, flag);
		}
	}

	public static Map fromXML(Element src, int flag) {
		Map ret = new Hashtable();
		DistributorValue root = new DistributorValue("", null, "", "", "", "evolution", "", null, false, null, 0, "", false, false, null, 0);
		ret.put("", root);
		root.fromXML(src.getChild("names"), ret, flag);
		return ret;
	}
	
	private Element printToXML() {
		Element e = new Element("item");
		
		e.setAttribute("Id", id.toString());
		e.setAttribute("LevelId", levelId.toString());
		if (partnerTypeId != null && !partnerTypeId.equals(new Integer(0)))  
		    e.setAttribute("PartnerTypeId", partnerTypeId.toString());
		
		String aTitle = "";
	
		if (levelAddress!=null && !levelAddress.equals(""))
			aTitle = title + " ";
		
		e.setAttribute("T", aTitle);	
		e.setAttribute("L", levelAddress);
		e.setAttribute("Name", name);
		e.setAttribute("MWSt", "" + vatFactor);
		e.setAttribute("active", "" + active);
		
		if (sponsor != null) { 
		    e.setAttribute("Sponsor", "" + sponsor.getId());
		}
		
		if (vatId != null) {
		    e.setAttribute("VatID", vatId);
		}
		
		if (address != null) {
			e.setAttribute("Street", address.street);
			e.setAttribute("Country", address.country);
			e.setAttribute("ZipCode", address.zipCode);
			e.setAttribute("City", address.city);
		}
					
		if (phone != null) {
			String aPhone = phone[0];
			if (phone[1].length() > 0) aPhone += (aPhone.length() > 0 ?  ", " : "") + phone[1];
			if (phone[2].length() > 0) aPhone += (aPhone.length() > 0 ?  ", " : "") + phone[2];
			e.setAttribute("Phone", aPhone);
		}
		
		if (email != null) {
			e.setAttribute("EMail", email);
		}
		
		e.setAttribute("OrganizationBonus", "" + organizationValue);
	    return e;
	}
	
	private void toXML(Element target, ElementFilter filter) {
		for (Iterator it = children.iterator(); it.hasNext(); ) {
			DistributorValue dv = (DistributorValue) it.next();
			Element e = dv.printToXML();
			if (filter == null || filter.isElement(e)) {
			    target.addContent(e);
			    dv.toXML(e, filter);
			} else {
			    dv.toXML(target, filter);
			}
		}
	}
	
	public static Element toXML(List data, Element ret, ElementFilter filter) {
		for (Iterator it = data.iterator(); it.hasNext(); ) {
			DistributorValue dv = (DistributorValue) it.next();
			Element e = dv.printToXML();
			if (filter == null || filter.isElement(e)) {
			    ret.addContent(e);
			}
		}
		return ret;
	}

	
	public static Element toXML(Map data, Element ret, ElementFilter filter) {
		DistributorValue bd = (DistributorValue) data.get("");
		bd.toXML(ret, filter);
		return ret;
	}
	
	public static Element toXML(Map data, Element ret) {
		DistributorValue bd = (DistributorValue) data.get("");
		bd.toXML(ret, null);
		return ret;
	}
	
	private static String string(Object src) {
		return src != null ? src.toString() : "";
	}
	
	private static DistributorValue getFromPO(Connection con, PersistableObject po, int flag) {
   		Map val = po.getValues();
		
   		// get the defualt bank account for bonus paying
   		BankAccountValue bankAccount = null;
   		if ((flag & FLAG_NEED_BANKINFO) != 0) {
   			Boolean recurring = (Boolean) val.get("recurring");
   			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"),
   					recurring != null && recurring.booleanValue());
   		}
   		  		
   		// calculate vatFactor
		double vatFactor = 0;
		String vatId = (String) val.get("vat_id");
		//if (vatId != null && vatId.length() > 3 && val.get("tax_id") != null && val.get("vatbonus") != null) {
		if (vatId != null && vatId.length() > 3 && val.get("vatbonus") != null) {
			vatFactor = ((Float)val.get("vatbonus")).doubleValue();
		}
		
		boolean customerService = false;
		try {
			customerService = ((Boolean) val.get("customer_service")).booleanValue();
		} catch (Exception e) {}
		
		boolean transferBonus = false;
		try {
			transferBonus = ((Boolean) val.get("transferbonus")).booleanValue();
		} catch (Exception e) {}
		
		DistributorValue dv = new DistributorValue(
					val.get("id"), null, val.get("level_id"), (String) val.get("level"), (String) val.get("title"),
					(flag & FLAG_NAME_SWAPPED) == 0 ? 
							string(val.get("firstname")) + " " + string(val.get("lastname")) :
							string(val.get("lastname")) + ", " + string(val.get("firstname")), string(val.get("company")), 
					(flag & FLAG_NEED_ALLPHONES) != 0 ? 
							new String[] {string(val.get("tel1")), string(val.get("tel2")), string(val.get("mobil"))} :
							new String[] {string(val.get("tel1")), "", ""},
					customerService, bankAccount, vatFactor, vatId, ((Boolean) val.get("active")).booleanValue(), 
					transferBonus, val.get("partner_type_id"), flag
		);

		dv.deactivationDate = (java.sql.Date) val.get("deactivation_date");
		dv.levelAddress = val.get("level")!=null?(String)val.get("level"):"";
		dv.country = (String) val.get("country_code");
        org.apache.log4j.Logger logger = Logger.getLogger("MainControl");
		//com.etixpert.evolution.gui.servlets.MainControl.logger;
		
		logger.info("DEBUG LEVEL_ADDRESS: "+val.get("level"));
		
		dv.bonusHandlers = null;
		if ((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0) {
			if (((Long) val.get("ib_num")).intValue() > 0) {
				dv.bonusHandlers = IndividualBonusValue.getByDistId(con, val.get("id"));
			}
		}
		
		dv.address = null;
   		if ((flag & FLAG_NEED_ADDRESS) != 0 && val.get("street") != null) {
   			dv.address = new Address((String) val.get("street"), (String) val.get("country_post_code"),
   					"" + val.get("postcode") , (String) val.get("city"));
   		}

   		dv.email = "";
   		if ((flag & FLAG_NEED_EMAIL) != 0) {
   			dv.email = string(val.get("email"));
   		}

   		return dv;
	}
	
	public static DistributorValue getById(Connection con, Object id, int flag) {
		ObjectSearch os = new ObjectSearch();
		List items = null;
        try {
        	items = os.search(con,
        			"select d.*, " +
					"b.id as b_id, b.bank_code, b.bank, b.account_number, b.bic, b.iban, b.debit_date, b.recurring, c.tax_id, c.vatbonus, la.descr As title " +
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? ", (Select count(ib.id) From individual_bonus ib Where ib.distr_id = d.id) As ib_num " : "") +  					
					((flag & FLAG_NEED_ADDRESS) != 0 ? ", street, city, postcode, a.country_post_code " : "") +  					
        			"From distributor d Left Join bank_account b " + 
        			"On b.distr_id = d.id And b.default_bonus = true " +
					"Left Join level_address la On la.id=d.level_address_id " +
					"Left Join title ta On ta.id=d.titel_id " +
					"Join country c On d.country_code = c.country_code " + 
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? "Left Join Individual_bonus ib On ib.distr_id=d.id " : "") +  
					"Where d.deleted=false And d.id=? Order By d.id", new Object[] {id});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        		
        DistributorValue dv = null;
        
		if (items != null){
        	// get the distributor
    		PersistableObject po = (PersistableObject) items.get(0);
    		return getFromPO(con, po, flag);
		}
		return null;
	}
	
	public static Map getAll(Connection con, int flag) {
		Map ret = new Hashtable();
        
        // load all distributors 
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(con, 
        			"select d.*, " +
					"b.id as b_id, b.bank_code, b.bank, b.account_number, b.bic, b.iban, b.debit_date, b.recurring, c.tax_id, c.vatbonus, ta.descr As title, la.descr As level " +
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? ", (Select count(ib.id) From individual_bonus ib Where ib.distr_id = d.id) As ib_num " : "") +  					
					((flag & FLAG_NEED_ADDRESS) != 0 ? ", street, city, postcode, a.country_post_code " : "") +  					
        			"From distributor d Left Join bank_account b " + 
        			"On b.distr_id = d.id And b.default_bonus = true " +
					"Left Join level_address la On la.id=d.level_address_id " +
					"Left Join title ta On ta.id=d.titel_id " +
					"Join country c On d.country_code = c.country_code " + 
					((flag & FLAG_NEED_ADDRESS) != 0 ? "Left Join Address a On a.default_bill=true And a.distr_id=d.Id " : "") +  
        			"Where d.deleted = false Order By d.id", null);
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		DistributorValue root = new DistributorValue("", null, "", "", "", "evolution", "", null, false, null, 0, "", false, false, null, 0);
		ret.put("", root);
		
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get a distributor
        		PersistableObject po = (PersistableObject) it.next();
        		DistributorValue  dv = getFromPO(con, po, flag);
        		ret.put(dv.getId(), dv);
        	}
        	
        	// set the relations
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get a distributor
        		PersistableObject po = (PersistableObject) it.next();
        		DistributorValue dv = (DistributorValue) ret.get(po.get("id"));
        		Object sponsor = po.get("sponsor");
        		dv.setSponsor(sponsor != null ? (DistributorValue) ret.get(sponsor) : root);
        	}
        }
		return ret;
	}
	
	public static List getDownline(Connection con, Object id, String orderBy, int flag) {
		List ret = new LinkedList();
        
        // load all distributors 
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(con, 
        			"select d.*, " +
					"b.id as b_id, b.bank_code, b.bank, b.account_number, b.bic, b.iban, b.debit_date, b.recurring, c.tax_id, c.vatbonus, ta.descr As title, la.descr As level " +
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? ", (Select count(ib.id) From individual_bonus ib Where ib.distr_id = d.id) As ib_num " : "") +  					
					((flag & FLAG_NEED_ADDRESS) != 0 ? ", street, city, postcode, a.country_post_code " : "") +  					
        			"From distributor d Left Join bank_account b " + 
        			"On b.distr_id = d.id And b.default_bonus = true " +
					"Left Join level_address la On la.id=d.level_address_id " +
					"Left Join title ta On ta.id=d.titel_id " +
					"Join country c On d.country_code = c.country_code " + 
					((flag & FLAG_NEED_ADDRESS) != 0 ? "Left Join Address a On a.default_bill=true And a.distr_id=d.Id " : "") +  
        			"Where d.deleted = false And d.id in (select * from st(?)) Order By " + orderBy, 
        			new Object[] {id});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		Map temp = new Hashtable();
		DistributorValue root = new DistributorValue("", null, "", "", "", "evolution", "", null, false, null, 0, "", false, false, null, 0);
	
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get a distributor
        		PersistableObject po = (PersistableObject) it.next();
        		DistributorValue  dv = getFromPO(con, po, flag);
        		temp.put(dv.getId(), dv);
        		ret.add(dv);
        	}
        	
        	// set the relations
        	Iterator retIt = ret.iterator();
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get a distributor
        		PersistableObject po = (PersistableObject) it.next();
        		DistributorValue dv = (DistributorValue) temp.get(po.get("id"));
        		Object sponsor = po.get("sponsor");
        		dv.setSponsor(sponsor != null ? (DistributorValue) temp.get(sponsor) : root);
        	}
        }
		return ret;
	}
	
	//Laci!! ez egy szuksegmegoldas, mivel a downlineEtiketten-be nem kellenek a
	//deaktivalt distributorok
	public static List getDownlineWithoutDeactivated(Connection con, Object id, String orderBy, int flag, boolean email) {
		List ret = new LinkedList();
    	String mailConstraint = "";
    	
    	if (email) {
    		mailConstraint = " (d.email is null or d.email='') AND ";
    	}
        
		// load all distributors 
		List items = null;
		ObjectSearch os = new ObjectSearch();
		try {
			items = os.search(con, 
					"select d.*, " +
					"b.id as b_id, b.bank_code, b.bank, b.account_number, b.bic, b.iban, b.debit_date, b.recurring, c.tax_id, c.vatbonus, ta.descr As title, la.descr As level " +
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? ", (Select count(ib.id) From individual_bonus ib Where ib.distr_id = d.id) As ib_num " : "") +  					
					((flag & FLAG_NEED_ADDRESS) != 0 ? ", street, city, postcode, a.country_post_code " : "") +  					
					"From distributor d Left Join bank_account b " + 
					"On b.distr_id = d.id And b.default_bonus = true " +
					"Left Join level_address la On la.id=d.level_address_id " +
					"Left Join title ta On ta.id=d.titel_id " +
					"Join country c On d.country_code = c.country_code " + 
					((flag & FLAG_NEED_ADDRESS) != 0 ? "Left Join Address a On a.default_bill=true And a.distr_id=d.Id " : "") +  
					"Where "+ mailConstraint+"d.deleted = false And d.printlock = false And d.active = true And d.id in (select * from st(?)) Order By " + orderBy, 
					new Object[] {id});
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
        
		// process all of them
		Map temp = new Hashtable();
		DistributorValue root = new DistributorValue("", null, "", "", "", "evolution", "", null, false, null, 0, "", false, false, null, 0);
	
		if (items != null){
			for (Iterator it = items.iterator(); it.hasNext(); ) {
				// get a distributor
				PersistableObject po = (PersistableObject) it.next();
				DistributorValue  dv = getFromPO(con, po, flag);
				temp.put(dv.getId(), dv);
				ret.add(dv);
			}
        	
			// set the relations
			Iterator retIt = ret.iterator();
			for (Iterator it = items.iterator(); it.hasNext(); ) {
				// get a distributor
				PersistableObject po = (PersistableObject) it.next();
				DistributorValue dv = (DistributorValue) temp.get(po.get("id"));
				Object sponsor = po.get("sponsor");
				dv.setSponsor(sponsor != null ? (DistributorValue) temp.get(sponsor) : root);
			}
		}
		return ret;
	}

	
	public static Map getByCountry(Connection con, Object country, int flag) {
		Map ret = new Hashtable();
        
        // load all distributors 
        List items = null;
		ObjectSearch os = new ObjectSearch();
        try {
        	items = os.search(con, 
        			"select d.*, " +
					"b.id as b_id, b.bank_code, b.bank, b.account_number, b.bic, b.iban, b.debit_date, b.recurring, c.tax_id, c.vatbonus,  ta.descr As title, la.descr As level " +
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? ", (Select count(ib.id) From individual_bonus ib Where ib.distr_id = d.id) As ib_num " : "") +  					
					((flag & FLAG_NEED_ADDRESS) != 0 ? ", street, city, postcode, a.country_post_code " : "") +  					
        			"From distributor d Left Join bank_account b " + 
        			"On b.distr_id = d.id And b.default_bonus = true " +
					"Left Join level_address la On la.id=d.level_address_id " +
					"Left Join title ta On ta.id=d.titel_id " +
					"Join country c On d.country_code = c.country_code " + 
					((flag & FLAG_NEED_ADDRESS) != 0 ? "Left Join Address a On a.default_bill=true And a.distr_id=d.Id " : "") +  
        			"Where d.deleted=false And d.country_code = ?", new Object[] {country});
        } catch (Exception e) {
        	e.printStackTrace(System.err);
        }
        
        // process all of them
		DistributorValue root = new DistributorValue("", null, "", "", "", "evolution", "", null, false, null, 0, "", false, false, null, 0);
		ret.put("", root);
		
		if (items != null){
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get a distributor
        		PersistableObject po = (PersistableObject) it.next();
        		DistributorValue  dv = getFromPO(con, po, flag);
        		ret.put(dv.getId(), dv);
        	}
        	
        	// set the relations
        	for (Iterator it = items.iterator(); it.hasNext(); ) {
        		// get a distributor
        		PersistableObject po = (PersistableObject) it.next();
        		DistributorValue dv = (DistributorValue) ret.get(po.get("id"));
        		Object sponsor = po.get("sponsor");
        		dv.setSponsor(sponsor != null ? (DistributorValue) ret.get(sponsor) : root);
        	}
        }
		return ret;
	}
	
	public static Map getFromHistory(Connection con, Date date, int flag) {
		Map ret = new Hashtable();
	    
	    // load all distributors 
	    List items = null;
		ObjectSearch os = new ObjectSearch();
	    try {
	    	items = os.search(con, 
	    			"select d.*, h.level_id," +
					"b.id as b_id, b.bank_code, b.bank, b.account_number, b.bic, b.iban, b.debit_date, b.recurring, c.tax_id, c.vatbonus, ta.descr As title, la.descr As level " +
					((flag & FLAG_NEED_INDIVIDUAL_BONUS) != 0 ? ", (Select count(ib.id) From individual_bonus ib Where ib.distr_id = d.id) As ib_num " : "") +  					
					((flag & FLAG_NEED_ADDRESS) != 0 ? ", street, city, postcode, a.country_post_code " : "") +  					
	    			"From distributor d Left Join bank_account b " + 
	    			"On b.distr_id = d.id And b.default_bonus = true " +
					"Left Join level_address la On la.id=d.level_address_id " +
					"Left Join title ta On ta.id=d.titel_id " +
					"Join country c On d.country_code = c.country_code " + 
					((flag & FLAG_NEED_ADDRESS) != 0 ? "Left Join Address a On a.default_bill=true And a.distr_id=d.Id " : "") +  
	    			"Join distributor_history h On h.distr_id = d.id And h.level_from = ? " +
					"Where d.deleted = false Order By h.distr_hist_id", new Object[] {new java.sql.Date(date.getTime())});
	    } catch (Exception e) {
	    	e.printStackTrace(System.err);
	    }
	    
	    // process all of them
		DistributorValue root = new DistributorValue("", null, "", "", "", "evolution", "",  null, false, null, 0, "", false, false, null, 0);
		ret.put("", root);
		
		if (items != null){
	    	for (Iterator it = items.iterator(); it.hasNext(); ) {
	    		PersistableObject po = (PersistableObject) it.next();
	    	    // get a distributor
	    		if (!ret.containsKey(po.get("id"))) {
		    		DistributorValue  dv = getFromPO(con, po, flag);
		    		ret.put(dv.getId(), dv);
	    		}
	    	}
	    	
	    	// set the relations
	    	for (Iterator it = items.iterator(); it.hasNext(); ) {
	    		PersistableObject po = (PersistableObject) it.next();
	    		DistributorValue dv = (DistributorValue) ret.get(po.get("id"));
	    		Object sponsor = po.get("sponsor");
	    		dv.setSponsor(sponsor != null ? (DistributorValue) ret.get(sponsor) : root);
	    	}
	    }
		return ret;
	}
}
