package com.reallinkbilling.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.reallinkbilling.DAO.BankAccountStatementDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;

public class GetBankAccountEntryDetails implements SessionAware {
	
	private SessionMap<String,Object> sessionMap;  
	
	public static ArrayList<BankAccountStatementDAO> getBankAccountDetails(String fromDate, String toDate, SessionMap<String, Object> sessionMap)
	{
		ArrayList<BankAccountStatementDAO> bankAccountReportDAOList = new ArrayList<BankAccountStatementDAO>();
		
		LinkedHashMap<String,String> accountList = GetAccountListForDrCr.getAccountList(sessionMap);

		

    	Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
    	
		try
		{
			connection = GetDBConnection.getDBConnection(db);
			
			
			for(String accountId:accountList.keySet())
			{
				float openingBal = 0.0F;
				String accountHolderName = ""; 
        		sql = "SELECT "
        				+ "opening_balance,"
        				+ "account_holder_name,"
        				+ "account_number,"
        				+ "ifsc_code,"
        				+ "bank_name,"
        				+ "branch "
        				+ "FROM "
        				+ "bank_account "
        				+ "WHERE "
        				+ "account_id  = ?";
        			
        		preparedStatement = connection.prepareStatement(sql);
        		
        		preparedStatement.setString(1, accountId);
        		
        		resultSet = preparedStatement.executeQuery();
        		if(resultSet.next())
        		{
        			openingBal = resultSet.getFloat(1);
        			accountHolderName = resultSet.getString(2);
        		}
        		
        		if(resultSet != null)
        		{
        			resultSet.close();
        			resultSet = null;
        		}
        		if(preparedStatement!= null)
        		{
        			preparedStatement.close();
        			preparedStatement = null;
        		}
        		
				sql = "SELECT "
						+ "entry_id,"
						+ "account_id,"
						+ "account_number,"
						+ "entry_date,"
						+ "DATE_FORMAT(entry_date,'%d-%m-%Y'),"
						+ "entry_time,"
						+ "entry_type,"
						+ "amount,"
						+ "payment_type,"
						+ "entry_by,"
						+ "entry_details "
						+ "FROM "
						+ "bank_account_credit_debit_master "
						+ "WHERE "
						+ "account_id = ? "
						+ "AND "
						+ "(entry_date "
						+ "BETWEEN "
						+ "STR_TO_DATE(?,'%d-%m-%Y') AND STR_TO_DATE(?,'%d-%m-%Y') )"
						+ "order by "
						+ "entry_date";
				
				preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setString(1, accountId);
				preparedStatement.setString(2, fromDate);
				preparedStatement.setString(3, toDate);
				
				resultSet = preparedStatement.executeQuery();
				
				resultSet.last();
				
				int rowCount = resultSet.getRow();
				
				resultSet.beforeFirst();
				
				if(rowCount > 0)
				{
					
					float balance = openingBal;
					
					String lastDateCloseingBalDetails = GetLastDayClosingBalAmount.getlastDayClosingBalAndDateAmount(accountId, fromDate, connection, sessionMap);
					
					String lastDatBal [] = lastDateCloseingBalDetails.split("#");
					BankAccountStatementDAO lastDateBalDao = new BankAccountStatementDAO();
					lastDateBalDao.setDateAsString(lastDatBal[0]);
					lastDateBalDao.setEntryId("0");
					lastDateBalDao.setEntryType("CREDIT");
					lastDateBalDao.setAmount(Float.parseFloat(lastDatBal[1]));
					lastDateBalDao.setPaymentType("OPENING BAL");
					lastDateBalDao.setEntryBy("-");
					lastDateBalDao.setAccountName(accountHolderName);
					lastDateBalDao.setBalance(Float.parseFloat(lastDatBal[1]));
					bankAccountReportDAOList.add(lastDateBalDao);
					balance = balance + Float.parseFloat(lastDatBal[1]);
					while(resultSet.next())
					{
						BankAccountStatementDAO dao = new BankAccountStatementDAO();
						dao.setDateAsString(resultSet.getString(5));
						dao.setEntryId(resultSet.getString(1));
						dao.setEntryType(resultSet.getString(7));
						dao.setAmount(resultSet.getFloat(8));
						dao.setPaymentType(resultSet.getString(9));
						dao.setEntryBy(resultSet.getString(10));
						dao.setAccountName(accountHolderName);
						
						if(resultSet.getString(7).equals("CREDIT"))
						{
							balance = balance + resultSet.getFloat(8);
						}
						if(resultSet.getString(7).equals("DEBIT"))
						{
							balance = balance - resultSet.getFloat(8);
						}
						
						
						dao.setBalance(balance);
						dao.setEntryDetails(resultSet.getString(11));
						dao.setKey(EncryptionDecryption.encrypt(resultSet.getString(1)+"#"+accountId+"#"+resultSet.getString(7)));
						bankAccountReportDAOList.add(dao);
					}
					
			}
			}
		}catch (SQLException e) {
			
		} catch (IOException e) {
			
			
		}finally {
			if(resultSet != null)
			{
				try {resultSet.close(); resultSet = null;} catch (SQLException e) {}
			}
			if(preparedStatement != null)
			{
				try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
			}
			if(connection != null)
			{
				
				try {connection.close(); connection = null;}catch (SQLException e) {}
			}
			
		}
		
		return bankAccountReportDAOList;
	}
	
	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}

	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}

	public void setSession(Map<String, Object> map) {  
	    sessionMap=(SessionMap<String, Object>)map;  
	}
	

}
