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 org.apache.struts2.dispatcher.SessionMap;

import com.reallinkbilling.db.GetDBConnection;

public class InsertBankAccDrCrEntry {
	
	
	public static boolean addBankAccCreditEntry(String billDate, String accountId, Float amount, String typeOfPayment, SessionMap<String, Object> sessionMap, String message)
	{
		boolean entryAdded = false;
		
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
    	Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
			try
			{
				java.util.Date date = new java.util.Date();
        	      long t = date.getTime();	        	     
        	      java.sql.Time time = new java.sql.Time(t);
				
				String user = (String) sessionMap.get("userFullName");
				String userName = "";
				
				try
				{
					userName = user.split(" ")[0];
				}catch(Exception e)
				{
					userName = user;
				}
				
			
				connection = GetDBConnection.getDBConnection(db);
				
				
				sql = "SELECT "
						+ "entry_id "
						+ "FROM "
						+ "bank_account_credit_debit_master "
						+ "WHERE "
						+ "shop_id = ? "
						+ "AND "
						+ "shop_code = ? "
						+ "ORDER BY "
						+ "entry_id "
						+ "DESC LIMIT 1";
				
			
			preparedStatement = connection.prepareStatement(sql);
			
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			
			resultSet = preparedStatement.executeQuery();
			
			int entryId = 1;
		
			if(resultSet.next())
			{
				if(!(resultSet.getString(1) == null))
				{
					entryId = Integer.parseInt(resultSet.getString(1));
					entryId = entryId + 1;
					
				}
				
			}
			else
			{	entryId =1;
				
			}
			
    		if(resultSet != null)
    		{
    			resultSet.close();
    			resultSet = null;
    		}
    		if(preparedStatement!= null)
    		{
    			preparedStatement.close();
    			preparedStatement = null;
    		}
    		String accountNumber = "";
    		
    		sql = "SELECT "
    				+ "account_number "
    				+ "FROM "
    				+ "bank_account "
    				+ "WHERE "
    				+ "account_id  = ? "
    				+ "AND "	        			
    				+ "shop_id = ? "
    				+ "AND "
    				+ "shop_code = ? ";
    			
    		preparedStatement = connection.prepareStatement(sql);
    		preparedStatement.setString(1, accountId);
    		preparedStatement.setString(2, shopId);
    		preparedStatement.setString(3, shopCode);      		
    		
    		
    		resultSet = preparedStatement.executeQuery();
    		if(resultSet.next())
    		{
    			accountNumber = resultSet.getString(1);
    		}
    		
    		if(resultSet != null)
    		{
    			resultSet.close();
    			resultSet = null;
    		}
    		if(preparedStatement!= null)
    		{
    			preparedStatement.close();
    			preparedStatement = null;
    		}
				
				sql = "INSERT "
						+ "INTO "
						+ "bank_account_credit_debit_master "
						+ "("
						+ "entry_id,"
						+ "shop_id,"
						+ "shop_code,"
						+ "account_id,"
						+ "account_number,"
						+ "entry_date,"
						+ "entry_time,"
						+ "entry_type,"
						+ "amount,"
						+ "payment_type,"
						+ "entry_by,"
						+ "entry_details) "
						+ "VALUES "
						+ "(?,?,?,?,?,STR_TO_DATE(?,'%d-%m-%Y'),?,?,?,?,?,?)";
				
				preparedStatement = connection.prepareStatement(sql);
				
				preparedStatement.setInt(1, entryId);
				preparedStatement.setString(2, shopId);
				preparedStatement.setString(3, shopCode);
				preparedStatement.setString(4, accountId);
				preparedStatement.setString(5, accountNumber);
				preparedStatement.setString(6, billDate);
				preparedStatement.setTime(7, time);
				preparedStatement.setString(8, "CREDIT");
				preparedStatement.setFloat(9, amount);
				preparedStatement.setString(10, typeOfPayment);
				preparedStatement.setString(11, userName);
				preparedStatement.setString(12,message);
				
				
				int rowInsetred = preparedStatement.executeUpdate();
				
				if(rowInsetred > 0)
				{
						entryAdded = true;
					
				}
				else
				{
					entryAdded = false;
				}
				
				
				
			}catch (SQLException e) {
				
				entryAdded = false;
			} catch (IOException e) {
				
				entryAdded = false;
			}finally {
				if(resultSet != null)
   				{
   					try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
   				}
				if(preparedStatement != null)
				{
					try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
				}
				if(connection != null)
				{
					
					try {connection.close(); connection = null;}catch (SQLException e) {}
				}
				
			}
    	
		
		return entryAdded;
	}






public static boolean addBankAccDebitEntry(String billDate, String accountId, Float amount, String typeOfPayment, SessionMap<String, Object> sessionMap, String message)
{
	boolean entryAdded = false;
	
	
	String shopId = (String) sessionMap.get("shopId");
	String shopCode = (String) sessionMap.get("shopCode");
	
	Connection connection = null;
	PreparedStatement preparedStatement= null;
	ResultSet resultSet = null;
	
	String sql = "";
	String db = (String) sessionMap.get("db");
		try
		{
			java.util.Date date = new java.util.Date();
    	      long t = date.getTime();	        	     
    	      java.sql.Time time = new java.sql.Time(t);
			
			String user = (String) sessionMap.get("userFullName");
			String userName = "";
			
			try
			{
				userName = user.split(" ")[0];
			}catch(Exception e)
			{
				userName = user;
			}
			
		
			connection = GetDBConnection.getDBConnection(db);
			
			
			sql = "SELECT "
					+ "entry_id "
					+ "FROM "
					+ "bank_account_credit_debit_master "
					+ "WHERE "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ? "
					+ "ORDER BY "
					+ "entry_id "
					+ "DESC LIMIT 1";
			
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, shopId);
		preparedStatement.setString(2, shopCode);
		
		resultSet = preparedStatement.executeQuery();
		
		int entryId = 1;
	
		if(resultSet.next())
		{
			if(!(resultSet.getString(1) == null))
			{
				entryId = Integer.parseInt(resultSet.getString(1));
				entryId = entryId + 1;
				
			}
			
		}
		else
		{	entryId =1;
			
		}
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		if(preparedStatement!= null)
		{
			preparedStatement.close();
			preparedStatement = null;
		}
		String accountNumber = "";
		
		sql = "SELECT "
				+ "account_number "
				+ "FROM "
				+ "bank_account "
				+ "WHERE "
				+ "account_id  = ? "
				+ "AND "	        			
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
			
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, accountId);
		preparedStatement.setString(2, shopId);
		preparedStatement.setString(3, shopCode);      		
		
		
		resultSet = preparedStatement.executeQuery();
		if(resultSet.next())
		{
			accountNumber = resultSet.getString(1);
		}
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		if(preparedStatement!= null)
		{
			preparedStatement.close();
			preparedStatement = null;
		}
			
			sql = "INSERT "
					+ "INTO "
					+ "bank_account_credit_debit_master "
					+ "("
					+ "entry_id,"
					+ "shop_id,"
					+ "shop_code,"
					+ "account_id,"
					+ "account_number,"
					+ "entry_date,"
					+ "entry_time,"
					+ "entry_type,"
					+ "amount,"
					+ "payment_type,"
					+ "entry_by,"
					+ "entry_details) "
					+ "VALUES "
					+ "(?,?,?,?,?,STR_TO_DATE(?,'%d-%m-%Y'),?,?,?,?,?,?)";
			
			preparedStatement = connection.prepareStatement(sql);
			
			preparedStatement.setInt(1, entryId);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			preparedStatement.setString(4, accountId);
			preparedStatement.setString(5, accountNumber);
			preparedStatement.setString(6, billDate);
			preparedStatement.setTime(7, time);
			preparedStatement.setString(8, "DEBIT");
			preparedStatement.setFloat(9, amount);
			preparedStatement.setString(10, typeOfPayment);
			preparedStatement.setString(11, userName);
			preparedStatement.setString(12,message);
			
			
			int rowInsetred = preparedStatement.executeUpdate();
			
			if(rowInsetred > 0)
			{
					entryAdded = true;
				
			}
			else
			{
				entryAdded = false;
			}
			
			
			
		}catch (SQLException e) {
			
			entryAdded = false;
		} catch (IOException e) {
			
			entryAdded = false;
		}finally {
			if(resultSet != null)
				{
					try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
				}
			if(preparedStatement != null)
			{
				try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
			}
			if(connection != null)
			{
				
				try {connection.close(); connection = null;}catch (SQLException e) {}
			}
			
		}
	
	
	return entryAdded;
}

}

