package com.sap.nwce.ra.edm.ejb.services;

import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import javax.annotation.Resource;

import javax.ejb.EJBException;

import javax.ejb.SessionContext;

import javax.ejb.Stateless;

import javax.ejb.TransactionAttribute;

import javax.ejb.TransactionAttributeType;

import javax.ejb.TransactionManagement;

import javax.ejb.TransactionManagementType;

import javax.sql.DataSource;

import com.sap.nwce.ra.edm.util.DataResetResult;

import com.sap.nwce.ra.edm.util.PackageResolver;

import com.sap.nwce.ra.edm.util.ReferenceApplicationEJBLogger;

@Stateless

@TransactionManagement(value=TransactionManagementType.CONTAINER)

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)

public class DataResetServiceBean implements DataResetServiceLocal {

   @Resource

   public SessionContext sc;

   @Resource(name="CE_RA_DS")

   private DataSource _dataSource;

   private static final String INSERT_SQLFILE = "update_application_data.sql";

   private static final String DELETE_SQLFILE = "delete_application_data.sql";

   private static final String DATEDETECTPATTERN = "^.*'[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'.*$";

   private static final String DATEPATTERN = "'[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'";

   private static final int BASEYEAR = 2007;

   private Connection getConnection() throws SQLException {

      return _dataSource.getConnection();

   }

    public void setSessionContext(SessionContext ctx) throws EJBException{

      this.sc = ctx;

    }

   @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)

   @SuppressWarnings("finally")

   public DataResetResult resetContent() {

      Connection connection = null;

      DataResetResult dataresult = new DataResetResult();

      try {

         connection = this.getConnection();

         deleteAllContent(connection);

         importContent(connection);

         dataresult.setResultStatus(true);

      } catch (SQLException sqlExc){

         ReferenceApplicationEJBLogger.logException(sqlExc);

         dataresult.setResultStatus(false);

         dataresult.setErrorMessage(prepearErrorStatement(sqlExc.getMessage()));

         sc.setRollbackOnly();

      } catch (IOException ioExc) {

         ReferenceApplicationEJBLogger.logException(ioExc);

         dataresult.setResultStatus(false);

         dataresult.setErrorMessage(prepearErrorStatement(ioExc.getMessage()));

         sc.setRollbackOnly();

      } finally {

         try{

            connection.close();

         }catch (SQLException ioExc){

            ReferenceApplicationEJBLogger.logException(ioExc);

         }

         return dataresult;

      }

   }

   private void deleteAllContent(Connection connection) throws SQLException, IOException {

      Statement statement = connection.createStatement();

      final InputStream in = this.getClass().getClassLoader().getResourceAsStream(PackageResolver.resolvePackage(this, DELETE_SQLFILE));

      if(in != null) {

         BufferedReader reader = new BufferedReader ( new InputStreamReader ( in ));

         String line;

         while(((line=reader.readLine()) != null)) {

            String readedString = line.toString();

            if(readedString.toUpperCase().indexOf("DELETE") == 0){

               statement.execute(readedString);

            }

         }

         reader.close();

      }else{

         throw new IOException("The requested delete file is not avaible. Please check name and location");

      }

      statement.close();

      in.close();

   }

   private void importContent(Connection connection) throws SQLException, IOException {

      Calendar c = Calendar.getInstance();

      int yearOffset = c.get(Calendar.YEAR)-BASEYEAR;

      Statement statement = connection.createStatement();

        final InputStream in = this.getClass().getClassLoader().getResourceAsStream(PackageResolver.resolvePackage(this, INSERT_SQLFILE));

      if(in != null) {

         BufferedReader reader = new BufferedReader ( new InputStreamReader ( in ));

         String line;

         while(((line=reader.readLine()) != null)) {

            String readedString = line.toString();

            if(readedString.toUpperCase().indexOf("INSERT") == 0 || readedString.toUpperCase().indexOf("UPDATE") == 0){

               if(Pattern.matches(DATEDETECTPATTERN, readedString)) { // with dates

                  Object[] parsedStatement = parseForDates(readedString, yearOffset);

                  PreparedStatement ps = connection.prepareStatement((String)parsedStatement[0]);

                  for(int i=1; i<parsedStatement.length; i++){

                     ps.setDate(i, (Date)parsedStatement[i]);

                  }

                  ps.execute();

                  ps.close();

               } else { // without dates

                  statement.execute(readedString);

               }

            }

         }

         reader.close();

      }else{

         throw new IOException("The requested insert/update file is not available. Please check name and location");

      }

      statement.close();

      in.close();

   }

   private Object[] parseForDates(String readedString, int yearOffset) {

      Pattern p = Pattern.compile(DATEPATTERN);

      Matcher m = p.matcher(readedString);

      StringBuffer sb = new StringBuffer();

      ArrayList<Date> list = new ArrayList<Date>();

      while(m.find()){

         list.add(parseDate(m.group().replaceAll("'", ""), yearOffset));

         m.appendReplacement(sb, "?");

      }

      m.appendTail(sb);

      Object[] result = new Object[list.size()+1];

      result[0] = sb.toString();

      for(int i=1;i<result.length;i++) {

         result[i] = list.get(i-1);

      }

      return result;

   }

   private Date parseDate(String pDate, int yearOffset) {

      String[] parsed = pDate.split("-");

      Calendar c = Calendar.getInstance();

      c.set(Calendar.YEAR, Integer.parseInt(parsed[0]) + yearOffset);

      c.set(Calendar.MONTH, Integer.parseInt(parsed[1])-1); //month start with 0 for January

      c.set(Calendar.DAY_OF_MONTH, Integer.parseInt(parsed[2]));

      c.set(Calendar.HOUR_OF_DAY, 0);

      c.set(Calendar.MINUTE, 0);

      c.set(Calendar.SECOND, 0);

      c.set(Calendar.MILLISECOND, 0);

      java.sql.Date d = new java.sql.Date(c.getTimeInMillis());

      return d;

   }

   private String prepearErrorStatement(String exceptionMessage){

      final StringBuffer errorMessage = new StringBuffer();

      errorMessage.append("Error during data reset. Contact your administrator to handle the exception. Error detail message is: \n\r " );

      errorMessage.append(exceptionMessage);

      return errorMessage.toString();

   }

}

package com.sap.nwce.ra.edm.util;

import java.io.Serializable;

public class DataResetResult implements Serializable{

   /**

    * added for compiler only

    */

   private static final long serialVersionUID = 1L;

   private boolean resultStatus;

   private String errorMessage;

   public String getErrorMessage() {

      return errorMessage;

   }

   public void setErrorMessage(String errorMessage) {

      this.errorMessage = errorMessage;

   }

   public boolean isResultStatus() {

      return resultStatus;

   }

   public void setResultStatus(boolean resultStatus) {

      this.resultStatus = resultStatus;

   }

}

package com.sap.nwce.ra.edm.util;

public class PackageResolver {

   public static String resolvePackage(Object pObject, String pPackageExtention){

      String packageName = pObject.getClass().getPackage().getName();

      String file = packageName.substring(0, packageName.lastIndexOf(".ejb")).concat(".resources.").replace(".", "/").concat(pPackageExtention);

      return file;

   }

}