/*
 * 
 * Created on Jul 5, 2005
 * 
 * György Süveges
 * Etixpert Kft.
 * Copyright(c) 2005
 * 
 */
package com.etixpert.evolution;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

import javax.servlet.http.HttpSession;

import org.hibernate.*;
import org.apache.log4j.*;

import com.etixpert.evolution.gui.servlets.FormHelper;
import com.etixpert.evolution.gui.servlets.OrderHelper;
/**
 * @author Suveges Gyorgy
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class AboChecker {
   protected static Logger logger = Logger.getLogger("mainControl.aboToOrder");
   private static int CUSTOMER=100;
   private static int VIP_CUSTOMER=200;
   private static int INDEX_NETTO = 0; //Double
   private static int INDEX_BONUS = 1; //Double
   private static int INDEX_POINTS = 2; //Double
   private static int INDEX_TRADE_MARGIN = 3; //Double
   private static int INDEX_BRUTTO = 4; //BigDecimal
   
    /**
     * 
     */
   
   public static void main(String[] args) {
	   
       //Logger.getRootLogger().addAppender(new ConsoleAppender(new PatternLayout("%m%n")));
       ResourceBundle bundle = ResourceBundle.getBundle("evolution");
       PatternLayout layout = new PatternLayout("%d{ABSOLUTE} %-p %c{2}: %m%n");
       DailyRollingFileAppender appender = null;
       try {
         
          appender = new DailyRollingFileAppender(layout, bundle.getString("cronlogpath"), "'.'yyyy-MM-dd");
       } catch(Exception e) {//throw new ServletException(e);}
           e.printStackTrace();
       }
       
       logger.addAppender(appender);		
       Transaction tx = null;
       try {
           
	       Session session = HibernateUtil.currentSession();
	      // setFirstNextDelivery(session);
	       tx = session.beginTransaction();
	       ObjectSearch os = new ObjectSearch();
	       logger.info("Checking abos");
	       List ids = os.search(session.connection(), "select id from subscription_header where next_delivery is not null and next_delivery<=now()");
	       tx.commit();
	       if (ids.size()==0) 
	           logger.info("-->There is no order to create from abo");
	       for (int i=0; i<ids.size(); i++) {
	    	   
	           try {
	        	   PersistableObject po = (PersistableObject) ids.get(i);
		           Long id = (Long) po.get("id");
	        	   logger.info("-->generating order from abo "+id);
	        	   createOrderFromAbo(session, id);
	           } catch (Exception e) {
	        	   logger.error("Error while creating order from abo ",e);
	        	   
	           }
	       }
	     
	       HibernateUtil.closeSession();
       } catch (Exception e) {
           try {
               logger.error("error occured when checking if there is order to create from abo",e);
               if (tx!=null)
                   tx.rollback();
               HibernateUtil.closeSession();
           } catch (Exception ex) {}
       }
   }
   
  /*  public static void setFirstNextDelivery(Session session) throws Exception {
    	 Transaction tx = null;
    	 try {
    		 tx = session.beginTransaction();
    		 ObjectSearch os = new ObjectSearch("subscription_header", "id");
    		 os.setCondition("next_delivery is null and start_date<=now()");
    		 List l = os.search();
    		 tx.commit();
    		 for (int i=0; i<l.size(); i++) {
    			 PersistableObject sh = (PersistableObject) l.get(i);
    			 
    		 }
    		 
    		 
    	 } catch (Exception e) {
    		  logger.error("error occured when setting the first values of next delivery dates",e);
              if (tx!=null)
                  tx.rollback();
              HibernateUtil.closeSession();
              throw e;
    		 
    	 }
    } */
   
    public static void createOrderFromAbo(Session session, Long id) throws Exception {
        
        Transaction tx = null;
        try{  
            
            tx = session.beginTransaction();
            Connection conn = session.connection();
            
            PersistableObject abo = new PersistableObject("subscription_header","id");
            abo.set("id", id);
            abo.load(conn);
            int ot = ((Integer) abo.get("duration")).intValue();
            int di = ((Integer) abo.get("delivery_intervall")).intValue();
            int maxDelivery = ((Integer) abo.get("max_delivery")).intValue();
            int delivered = ((Integer) abo.get("delivered")).intValue();
            boolean autoLengthening = ((Boolean) abo.get("auto_extension")).booleanValue();
            if (delivered>=maxDelivery && !autoLengthening) {
                logger.info("Abo "+id+" expired");
                abo.set("next_delivery", null);
                abo.update(session.connection());
                tx.commit();
              
                return;
            }
            
            if (delivered>=maxDelivery) {
                logger.info("Auto-Lengthening. Updating prices");
                updatePrices(session, abo);
            }
            delivered++;
            java.sql.Date nextDelivery = (java.sql.Date) abo.get("next_delivery");
            java.sql.Date oldNextDelivery = nextDelivery;
            //int otime = ((Integer) abo.get("operating_time")).intValue();
            Calendar cal = Calendar.getInstance();
            cal.setTime(nextDelivery);
            //Bearbeitungsdatum
            int day = cal.get(Calendar.DATE);
            if (day>=24) day=24;
            else if (day>=16) day=16;
            else if (day>=8) day=8;
            else day=1;
            cal.set(Calendar.DATE, day);
            cal.add(Calendar.MONTH, di);
            
            nextDelivery=new java.sql.Date(cal.getTimeInMillis());
            logger.info("incrementing delivered: "+delivered);
            abo.set("delivered", new Integer(delivered));
            logger.info("incrementing nextDelivery: "+nextDelivery);
            if (delivered>=maxDelivery && ! autoLengthening)
                abo.set("next_delivery",null);
            else
                abo.set("next_delivery", nextDelivery);
            logger.info("updating abo");
            abo.update(conn);
            int payment = ((Integer) abo.get("pay_method_id")).intValue();
            
            //creating order
            logger.info("creating order_header");
            Map aboHeaderValues = abo.getValues();
            Map orderHeader = new HashMap();
            orderHeader.putAll(aboHeaderValues);
            String[] fieldsToRemove = new String[] {"descr", "duration", "auto_extension", "distr_id", 
                    								"creation_date", "delivery_intervall", "id", "modification_comment",
                    								"modified_by", "next_delivery", "created_by", "max_delivery", "delivered",
                    								"operating_time", "start_month", "start_date"};
           
            removeKeys(orderHeader, fieldsToRemove);
            PersistableObject distr = new PersistableObject("distributor", new String[] {"id"});
    		distr.set("id", abo.get("distr_id"));
    		distr.load(conn);
    		
    		String orderer_level_id = distr.getString("level_id", "100");
    		
    		List addresses = OrderHelper.getAddresses(conn,distr);
    		for (int i = 0; i<addresses.size(); i++) {

    			PersistableObject address = (PersistableObject)addresses.get(i);
    			
    			Boolean def_bill = (Boolean)address.get("default_bill");

    			if (def_bill.booleanValue()) {
    				orderHeader.put("billing_postcode", address.get("postcode"));
    				orderHeader.put("billing_city", address.get("city"));
    				orderHeader.put("billing_street", address.get("street"));
    				orderHeader.put("billing_country_id", address.get("country_post_code"));
    				String delivery_name = address.getString("delivery_name", "");
    				if (!delivery_name.equals(""))
    					orderHeader.put("billing_name", delivery_name);
    			}
    		}

    		if (orderHeader.get("billing_name")==null || orderHeader.get("billing_name").equals("")) {
    			orderHeader.put("billing_name", OrderHelper.getTitle(conn, distr)+distr.getString("firstname", "")+" "+distr.getString("lastname", ""));
    			orderHeader.put("billing_title", OrderHelper.getLevelAddress(conn, distr));
    		}

    		
            orderHeader.put("ordered_by", abo.get("distr_id"));
            orderHeader.put("order_date", oldNextDelivery);
            orderHeader.put("order_created_by", abo.get("created_by"));
            orderHeader.put("order_status_id", new Integer(10));
            orderHeader.put("order_type", new Integer(1));
            orderHeader.put("orderer_level_id", new Integer(orderer_level_id));
            
            if (payment!=3 && payment!=4 && payment!=6) 
				orderHeader.put("bonus_date", oldNextDelivery);
			
            PersistableObject newOrder = new PersistableObject("order_header","id");
            newOrder.setValues(orderHeader);
            
            logger.info("inserting the new order_header row");
            newOrder.insert(conn);
            ObjectSearch last = new ObjectSearch();
			List l = last.search(conn,"select {fn currval('seq_order_header')}");
			PersistableObject currvalRow = (PersistableObject) l.get(0);
			logger.info(currvalRow.get("currval").getClass());
			Long  newid = (Long) currvalRow.get("currval");
			logger.info("The current currval value: "+newid);
			String orderid = String.valueOf(newid);
			
			//geting abo details
			logger.info("getting subsr details");
			Map sid = new HashMap();
			sid.put("id","subscr_id");
			BigDecimal totalNetto = (BigDecimal) abo.get("total_netto");
			BigDecimal totalBonus = (BigDecimal) abo.get("total_bonus");
			//BigDecimal totalPoints = (BigDecimal) abo.get("total_points");
			BigDecimal totalBrutto = (BigDecimal) abo.get("total_brutto");
			BigDecimal totalTradeMargin = (BigDecimal) abo.get("total_trade_margin");
			boolean deleted = false;
			List details = abo.getMNObjects(conn, "subscription_detail", new String[]{"id"}, sid);
			for (int i = 0; i<details.size(); i++) {
			    PersistableObject aboDetail = (PersistableObject) details.get(i);
			    Map orderDetailValues = new HashMap();
			    orderDetailValues.putAll(aboDetail.getValues());
			    String art_id = (String)aboDetail.get("art_id");
			    PersistableObject art_po = new PersistableObject("article", "id");
			    art_po.set("id", art_id);
			    Double order_price = new Double(0);
			    try {
			    	art_po.load(conn);
			    	order_price = (Double)art_po.get("order_price");
			    } catch (Exception e) {
			    	
			    }
			    orderDetailValues.put("order_price", order_price);
			    
			    fieldsToRemove = new String[] {"subscr_id", "modification_comment", "subscription", "id"};
			    removeKeys(orderDetailValues, fieldsToRemove);
			    orderDetailValues.put("order_id", newid);
			    PersistableObject orderDetail = new PersistableObject("order_detail", new String[]{"order_id, art_position"});
			    orderDetail.setValues(orderDetailValues);
			    orderDetail.insert(conn);
			    Boolean subscr = (Boolean) aboDetail.get("subscription");
			    if (subscr!=null && !subscr.booleanValue()) {
			    	logger.info("Deleting subscription detail: "+aboDetail.get("id"));
			    	int quantity =((Integer) aboDetail.get("quantity")).intValue();
			    	double netto = ((Double) aboDetail.get("netto")).doubleValue(); 
			    	totalNetto=totalNetto.subtract(new BigDecimal(netto*quantity));
			    	double bonus = ((Double) aboDetail.get("bonus")).doubleValue(); 
			    	totalBonus=totalBonus.subtract(new BigDecimal(bonus*quantity));
			    	//double points = ((Double) aboDetail.get("points")).doubleValue();  
			    	//totalPoints=totalPoints.subtract(new BigDecimal(points*quantity));
			    	BigDecimal brutto = (BigDecimal) aboDetail.get("brutto"); 
			    	totalBrutto = totalBrutto.subtract(brutto.multiply(new BigDecimal(quantity))); 
			    	double tradeMargin = ((Double) aboDetail.get("trade_margin")).doubleValue(); 
			    	totalTradeMargin = totalTradeMargin.subtract(new BigDecimal(tradeMargin*quantity)); 
			    	
			    	aboDetail.delete(conn);
			    	deleted=true;
			    }
			}
			if (deleted) {
				abo.set("total_netto", totalNetto);
				abo.set("total_bonus", totalBonus);
				//abo.set("total_points", totalPoints);
				abo.set("total_brutto", totalBrutto);
				abo.set("total_trade_margin", totalTradeMargin);
				abo.update(conn);
			}
            tx.commit();
           
      } catch (Exception e) {
    	  
          logger.error("rolling back",e);
          if (tx!=null)
              tx.rollback();
         
          throw e;
      }
        
    }
    
    private static void removeKeys(Map map, String[] keysToRemove) {
        for (int i=0; i<keysToRemove.length; i++) 
            map.remove(keysToRemove[i]);
    }
    
    private static void updatePrices(Session session, PersistableObject abo) throws Exception {
       PersistableObject distributor = (PersistableObject) abo.getReferencedObject(session.connection(), "distr_id","distributor", "id");
       int level = ((Integer)distributor.get("level_id")).intValue();
       PersistableObject country  = (PersistableObject) distributor.getReferencedObject(session.connection(), "country_code", "country", "country_code");
     
       String uid = (String)distributor.getString("vat_id", "");
       
       double totalNetto=0, totalBonus=0, totalPoints=0, totalTradeMargin=0;
       BigDecimal totalBrutto = new BigDecimal(0);
       Map keyMap = new HashMap();
       keyMap.put("id", "subscr_id");
       List details = abo.getMNObjects(session.connection(), "subscription_detail", new String[] {"subscr_id","art_id"}, keyMap);
       for (int i=0; i<details.size(); i++) {
    	   PersistableObject detail = (PersistableObject) details.get(i);
    	   int quantity = ((Integer) detail.get("quantity")).intValue();
           Object[] newPrices= updateRow(session, detail, country, level, uid);
          // System.out.println("total_netto"+ newPrices[INDEX_NETTO]);
           
           totalNetto+=((Double) newPrices[INDEX_NETTO]).doubleValue()*quantity;
           
           totalBonus+=((Double) newPrices[INDEX_BONUS]).doubleValue()*quantity;
           //totalPoints+=((Double) newPrices[INDEX_POINTS]).doubleValue()*quantity;
           totalTradeMargin += ((Double) newPrices[INDEX_TRADE_MARGIN]).doubleValue()*quantity;
           totalBrutto = totalBrutto.add(((BigDecimal) newPrices[INDEX_BRUTTO]).multiply(new BigDecimal(quantity)));
       }
       totalBrutto.setScale(2, BigDecimal.ROUND_HALF_UP);
       //System.out.println("totalNetto: " +new BigDecimal(totalNetto).setScale(2, BigDecimal.ROUND_HALF_UP));
       //System.out.println("totalBrutto: "+totalBrutto);
       abo.set("total_netto", new BigDecimal(totalNetto).setScale(2, BigDecimal.ROUND_HALF_UP));
       abo.set("total_bonus", new BigDecimal(totalBonus).setScale(2, BigDecimal.ROUND_HALF_UP));
       //abo.set("total_points", new BigDecimal(totalPoints).setScale(3, BigDecimal.ROUND_HALF_UP));
       abo.set("total_trade_margin", new BigDecimal(totalTradeMargin).setScale(2, BigDecimal.ROUND_HALF_UP));
       abo.set("total_brutto", totalBrutto);
       abo.update(session.connection());
       
    }   
     
    private static Object[] updateRow(Session session, PersistableObject detail, PersistableObject country, int level, String uid) throws Exception {
        
    	logger.info("Updating price for article "+detail.get("art_id"));
    	PersistableObject articles = new PersistableObject("currentarticles","id");
        try {
        	articles.set("id", detail.get("art_id"));
        	articles.load(session.connection());
        } catch (Exception e) {
        	logger.warn("Article "+detail.get("art_id")+" couldn't be loaded. Updating ignored");
        	return new Object[] {detail.get("netto"), detail.get("bonus"), detail.get("points"), detail.get("trade_margin"),  detail.get("brutto")};
        }
        
        Integer quantity = (Integer) detail.get("quantity");
        Double netto = (Double) articles.get("netto");
        
        Double distrNetto = netto;
        if (level == CUSTOMER) netto = (Double) articles.get("netto_customer");
        else if (level == VIP_CUSTOMER) netto = (Double) articles.get("netto_vip_customer");
        Double tradeMargin = new Double(distrNetto.doubleValue() - netto.doubleValue());
        Double bonus = (Double) articles.get("bonus");
        //Double points = (Double) articles.get("points");
        String vatClass = articles.getString("vat_id", "1");
        if (vatClass.equals("") || vatClass.equals("0"))
        	vatClass="1";
        
        String country_code = (String)country.get("country_code");
        String tax_id = country.getString("tax_id", "");
        
        boolean noVat = uid!=null && uid.length()>0 && !"".equals(tax_id) && !"0".equals(tax_id) //&& "DE".equals(country_code);
        && !"AT".equals(country_code);
        
        BigDecimal vat = (BigDecimal) country.get("vat"+vatClass);
        if (vat==null)
        	vat = new BigDecimal(0);
        if (noVat) vat = new BigDecimal(0);
        
        double bruttoTemp = netto.doubleValue()*  (1+ vat.doubleValue()*.01);
        BigDecimal brutto = new BigDecimal(bruttoTemp);
        brutto = brutto.setScale(2, BigDecimal.ROUND_HALF_UP);
        netto = new Double(new BigDecimal(netto.doubleValue()).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
        detail.set("netto", netto);
        detail.set("bonus", bonus);
        //detail.set("points", points);
        detail.set("trade_margin", tradeMargin);
        detail.set("brutto", brutto);
        detail.set("vat_value", new Double(vat.doubleValue()));
        detail.update(session.connection());
        logger.info("Price updated Netto: "+netto+" Bonus: "+bonus+" Trade-margin: "+tradeMargin+" Brutto: "+brutto);
        return new Object[] {netto, bonus, null, tradeMargin, brutto};
        
    }
   /*    String id = request.getParameter("art_id");
       PersistableObject po = new PersistableObject("currentarticles","id");
        try {
            po.set("id",id);
            po.load();
        } catch(Exception e) {
            session.setAttribute(error,"Keine gültige Artikelnummer");
            return;
        }
        //Map myMap = FormHelper.getArticle(id, MainControl.logger);
        
        String descr = (String) po.get("descr");
        String country_code = request.getParameter("orderingCountry");
        PersistableObject po_country = new PersistableObject("country", "country_code");
        po_country.set("country_code", country_code);
        po_country.load();
        int level = 100;
        String levelStr = request.getParameter("orderingLevelId");
        
        try {
        	level = Integer.parseInt(levelStr);
        } catch (Exception e) {
        	level = 100;
        }
        Double netto = (Double) po.get("netto");
        Double bonus = (Double) po.get("bonus");
        Double points = (Double) po.get("points");
        String vatClass = po.getString("vat_id", "1");
        
        if (vatClass.equals("0")) vatClass="1";
        
        Double netto_customer = (Double) po.get("netto_customer");
		 Double netto_vip_customer = (Double) po.get("netto_vip_customer");
        BigDecimal vat = (BigDecimal) po_country.get("vat"+vatClass);
        BigDecimal brutto = new BigDecimal(0);
        
        //int level= Integer.valueOf(OrderHelper.getVal(m, "orderingLevel")).intValue();
        double d_points = points.doubleValue();
		 double d_bonus = bonus.doubleValue();
                 
        double d_nettoDist = netto.doubleValue(); 
        double d_nettoCustomer = netto_customer.doubleValue();
		 double d_nettoVipCustomer = netto_vip_customer.doubleValue();
        
        double d_netto = 0;
        //customer
        if (level == 100) d_netto = d_nettoCustomer;
        //vip customer
        else if (level == 200) d_netto = d_nettoVipCustomer;
        //distributor
        else d_netto = d_nettoDist;
        double tradeMargin = d_netto - d_nettoDist;  
        
		 double d_brutto = d_netto + (d_netto * vat.doubleValue())*.01;	         
		 java.text.NumberFormat nf = java.text.NumberFormat.getInstance(Locale.ENGLISH);
		 nf.setMinimumFractionDigits(2);
		 nf.setMaximumFractionDigits(2);
		 nf.setGroupingUsed(false);
	
		 Map orders = (Map)session.getAttribute(details);
		 int count = 0;
		 if (orders.get("count")!=null)
		 	count = ((Integer)orders.get("count")).intValue(); 	
	
 		 Map newRow = new HashMap();
 		newRow.put("netto", new String[] {nf.format(d_netto)});
		newRow.put("brutto", new String[] {nf.format(d_brutto)});
		newRow.put("points", new String[] {nf.format(d_points)});
		newRow.put("bonus", new String[] {nf.format(d_bonus)});
		newRow.put("trade_margin", new String[] {nf.format(tradeMargin)});
		newRow.put("art_id", new String[] {id});
		newRow.put("art_name", new String[] {descr});
		newRow.put("art_position", new String[] {String.valueOf(count)});
		newRow.put("comment", new String[] {""});
		newRow.put("order_id", new String[] {(String)session.getAttribute(currentID)});
		newRow.put("quantity", new String[] {String.valueOf("1")});
		newRow.put("vat_value", new String[] {nf.format(vat)});
	
		orders.put(String.valueOf(count), newRow);
		
		orders.put("count", new Integer(count+1));
    }*/
}
