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

import java.sql.Connection;
import java.sql.Statement;
import java.util.*;

import com.etixpert.evolution.app.order.*;
import com.etixpert.evolution.value.order.BankAccountValue;
import com.etixpert.evolution.*;

import javax.servlet.http.*;
import javax.servlet.*;

import java.text.*;
import java.sql.*;
import java.io.*;

import org.hibernate.*;

/**
 * @author Suveges Gyorgy
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Status20To30 extends DefaultWorkflow {
    private List keys = new ArrayList();
   
    /**
     * @param statusFrom
     * @param statusTo
     */
    public Status20To30() {
        super(20, 30);
        // TODO Auto-generated constructor stub
    }

    /* (non-Javadoc)
     * @see com.etixpert.evolution.workflow.Workflow#before(java.sql.Connection, java.util.Set)
     */
    protected void before(Connection conn, Set orders) throws Exception {
        if (request == null) throw new EvolutionException("ServletRequest is needed for this workflow");
        keys.clear();
        ServletContext sc = request.getSession().getServletContext();
        String webInfPath = sc.getRealPath("/WEB-INF/");
        logger.info("WEB-INF absolute path: "+webInfPath);
        java.util.Date now= new java.util.Date();
        logger.info("Generating the old, new edifact, hvb, creditcard files");
        logger.info("Size of Orderset before generation of payment files: "+ orders.size());
        Set oldOrders = new HashSet();
        oldOrders.addAll(orders);
        
        Set firstSepaTransactions = new HashSet();
        Object[] files = Payment.generate(conn,now, oldOrders, firstSepaTransactions, webInfPath);
        orders.removeAll(oldOrders);
        logger.info("Size of Orderset after generation of payment files: "+orders.size());
        
        Connection oldConn = conn;
        Session session = HibernateUtil.currentSession();
        Transaction tx =null;
        try {
          conn = session.connection();
          if (oldConn!=conn)
        	 tx = session.beginTransaction();
          storeAll(conn, now, files);
          setFirstSepaTransactions(conn, firstSepaTransactions);
          if (oldConn!=conn) {
        	  tx.commit();
        	  HibernateUtil.closeSession();
          }
        	  
        } catch (Exception e) {
        	logger.error("Error storing creditcard files", e);
        	if (tx!=null) 
        		 tx.rollback();
        	if (conn!=oldConn)
        		HibernateUtil.closeSession();
        }
        
    }

    /* (non-Javadoc)
     * @see com.etixpert.evolution.workflow.Workflow#changeStatus(java.sql.Connection, java.util.Set)
     */
    protected void changeStatus(Connection conn, Set orders) throws Exception {
        if (orders.size()==0) return;
        
        String sql = "update order_header set order_status_id = 40 " +
        		"where order_status_id = 20 AND pay_method_id = 1 AND " +
        		"ordered_by >=43000000 AND ordered_by<43002000 AND id in "+Tools.idsToSql(orders, false);
        
        logger.info("Changing status of orders from "+statusFrom+" to 40: "+sql);
        Statement stat = conn.createStatement();
        int changed = stat.executeUpdate(sql);
        logger.info("Orders changed: "+changed);
        stat.close();
        super.changeStatus(conn, orders);		
        

    }

    /* (non-Javadoc)
     * @see com.etixpert.evolution.workflow.Workflow#after(java.sql.Connection, java.util.Set)
     */
    protected void after(Connection conn, Set orders) throws Exception {
       if (keys.size() == 0) return;
       HttpSession session = request.getSession();
       //session.setAttribute("forwardlink","process/linkedpdf?pageAction=linkedpayment&saveState=/process/linkedpdf");
       //session.setAttribute("financeids", keys);
       
    }
    
    
    private void storeAll(Connection conn, java.util.Date now, Object[] files) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd.HHmmss");
        
        PreparedStatement ps = conn.prepareStatement("insert into financial (name, data, pdf) values(?, ?, ?)");
      
        logger.info("Storing the SEPA files for BA first transactions");
        String name= "SEPA_F_BA_"+sdf.format(now) + "_EVOLUTION";
        store(conn,ps, name, (String) files[0], (byte[]) files[1], "UTF8");
        
        logger.info("Storing the SEPA files for BA recurring transactions");
        name= "SEPA_R_BA_"+sdf.format(now) + "_EVOLUTION";
        store(conn,ps, name, (String) files[2], (byte[]) files[3], "UTF8");
        
        logger.info("Storing the edifact files for new austrian distributors");
        name= "AT_"+sdf.format(now) + "_EVOLUTION";
        store(conn,ps, name, (String) files[4], (byte[]) files[5]);
        
        logger.info("Storing the edifact files for old austrian distributors");
        name = "ATALT_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, (String) files[6], (byte[]) files[7]);
        
       /* logger.info("Storing the SEPA files for HVB first transactions");
        name = "SEPA_F_HVB_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, (String) files[8], (byte[]) files[9], "UTF8");
        
        logger.info("Storing the SEPA files for HVB recurring transactions");
        name = "SEPA_R_HVB_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, (String) files[10], (byte[]) files[11], "UTF8");*/

        logger.info("Storing the SEPA files for HVB first transactions");
        name = "SEPA_F_BA_DE_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, (String) files[8], (byte[]) files[9], "UTF8");
        
        logger.info("Storing the SEPA files for HVB recurring transactions");
        name = "SEPA_R_BA_DE_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, (String) files[10], (byte[]) files[11], "UTF8");
        
        logger.info("Storing the HVB files for german distributors");
        name = "HVB_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, (String) files[12], (byte[]) files[13]);

        logger.info("Storing creditcard infos in the database");
        name = "CC_" + sdf.format(now) + "_EVOLUTION";
        store(conn, ps, name, null, (byte[]) files[14]);
    }
    
    private void store(Connection conn, PreparedStatement ps, String name, String file, byte[] pdf) throws Exception {
    	store(conn, ps, name, file, pdf, "ISO-8859-1");
    }
    
    private void store(Connection conn, PreparedStatement ps, String name, String file, byte[] pdf, String encoding) throws Exception {
        if (file==null && pdf ==null) {
            logger.info("There is nothing to store for the file name "+name);
            return;
        }
        ps.setString(1, name);
        if (file==null) {
            logger.info("There is nothing in the data file for the file name "+name);
            ps.setObject(2,null);
        }
        else  {
            byte[] data = file.getBytes(encoding);
            logger.info("Length of financial file: " + data.length);
            ByteArrayInputStream  bin = new ByteArrayInputStream(data);
            ps.setBinaryStream(2, bin, data.length);
        }
        
        
        if (pdf==null)
            ps.setObject(3, null);
        else {
            logger.info("Length of "+name+" pdf: "+pdf.length);
            ps.setBinaryStream(3, new ByteArrayInputStream(pdf), pdf.length);
        }
        
        ps.executeUpdate();
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select {fn currval('seq_financial')}");
        if (rs.next()) {
            int key = rs.getInt(1);
            keys.add(new Integer (key));
            logger.info("generated key: "+key);
        }    
    }
    
    public static void setFirstSepaTransactions(Connection conn, Set<BankAccountValue> firstSepaTransactions) {
        if (firstSepaTransactions.size() > 0) {
	    	// gerenate list of placeholders
        	StringBuffer buffer = new StringBuffer();
	    	for (Object item : firstSepaTransactions) {
	    		buffer.append(",?");
	    	}
	    	String set = "(" + buffer.substring(1) + ")";
	    	
        	try {
	        	PreparedStatement ps = conn.prepareStatement("update bank_account set recurring=true where id in " + set);
	        	int idx = 1;
	        	for (BankAccountValue item : firstSepaTransactions) {
		    		ps.setObject(idx++, item.getId());
		    	}
	        	ps.execute();
	        } catch (SQLException e) {
	        	e.printStackTrace();
	        }
        }
    }
}
