package com.reallinkbilling.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.struts2.dispatcher.SessionMap;

public class MaintainCloseingBalUtil {

	
	public static boolean maintainCloseingBal(String accountId, String entryType, String entryDate, Float amount,String entryBy, Connection connection, SessionMap<String, Object> sessionMap)
	{
		boolean entryAddedUpdated = false;
		
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
	
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		try
		{
			boolean isEntryAddedForDate = false;
			float thatDateClosingBal = 0.0F;
			
			sql = "SELECT "
					+ "closeing_bal_amount "
					+ "FROM "
					+ "datewise_closing_balance "
					+ "WHERE "
					+ "closeing_bal_date = STR_TO_DATE(?,'%d-%m-%Y') "
					+ "AND "
					+ "account_id = ? "
					+ "AND "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ?";
			
			preparedStatement = connection.prepareStatement(sql);
			
			preparedStatement.setString(1, entryDate);
			preparedStatement.setString(2, accountId);
			preparedStatement.setString(3, shopId);
			preparedStatement.setString(4, shopCode);
			
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next())
			{
				thatDateClosingBal = resultSet.getFloat(1);
				isEntryAddedForDate = true;
			}
			
			
			if(resultSet != null)
			{
				resultSet.close();
				resultSet = null;
			}
			if(preparedStatement != null)
			{
				preparedStatement.close();
				preparedStatement = null;
			}
			
			int rowUpdated = 0;
			if(isEntryAddedForDate)
			{		
				

			
				
				
				float newClosingBalForThatDate = 0.0f;
				
				if(entryType.equals("CREDIT"))
				{					
					newClosingBalForThatDate = thatDateClosingBal + amount;			
					
				}				
				
				if(entryType.equals("DEBIT"))
				{
					newClosingBalForThatDate = thatDateClosingBal - amount;		
				}
				
				
				sql = "UPDATE "
						+ "datewise_closing_balance "
						+ "SET "
						+ "closeing_bal_amount = ?,"
						+ "last_entry_updated_by = ?  "
						+ "WHERE "
						+ "closeing_bal_date = STR_TO_DATE(?,'%d-%m-%Y') "
						+ "AND "
						+ "account_id = ? "
						+ "AND "
						+ "shop_id = ? "
						+ "AND "
						+ "shop_code = ?";
					
				preparedStatement = connection.prepareStatement(sql);					
				preparedStatement.setFloat(1, newClosingBalForThatDate);
				preparedStatement.setString(2, entryBy);
				preparedStatement.setString(3, entryDate);
				preparedStatement.setString(4, accountId);
				preparedStatement.setString(5, shopId);
				preparedStatement.setString(6, shopCode);
				
				rowUpdated = preparedStatement.executeUpdate();
					
			}
			else
			{
				float lastDateClosingBal = 0.0F;
				
				/*SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
				Calendar cal = Calendar.getInstance();
				cal = Calendar.getInstance();
				cal.add(Calendar.DATE, -1);
				String lastDate = dateFormat.format(cal.getTime());*/
				
				sql = "SELECT "
						+ "closeing_bal_amount "
						+ "FROM "
						+ "datewise_closing_balance "
						+ "WHERE "
						+ "shop_id = ? "
						+ "AND "
						+ "shop_code = ? "
						+ "AND "
						+ "account_id = ? "
						+ "AND "
						+ "closeing_bal_date < STR_TO_DATE(?,'%d-%m-%Y') "  
					    + "ORDER BY "
						+ "closeing_bal_date DESC LIMIT 1";
				
				
				preparedStatement = connection.prepareStatement(sql);
				
				preparedStatement.setString(1, shopId);
				preparedStatement.setString(2, shopCode);
				preparedStatement.setString(3, accountId);
				preparedStatement.setString(4, entryDate);
				
				resultSet = preparedStatement.executeQuery();
				
				if(resultSet.next())
				{
					lastDateClosingBal = resultSet.getFloat(1);
				}
				
				float newClosingBalForThatDate = 0.0f;
				
				if(entryType.equals("CREDIT"))
				{					
					newClosingBalForThatDate = lastDateClosingBal + amount;			
					
				}				
				
				if(entryType.equals("DEBIT"))
				{
					newClosingBalForThatDate = lastDateClosingBal - amount;		
				}
				
				
				if(resultSet != null)
				{
					resultSet.close();
					resultSet = null;
				}
				if(preparedStatement != null)
				{
					preparedStatement.close();
					preparedStatement = null;
				}
				
				sql = "SELECT "
						+ "entry_id "
						+ "FROM "
						+ "datewise_closing_balance "
						+ "ORDER BY "
						+ "entry_id "
						+ "DESC LIMIT 1;";
				
			
			preparedStatement = connection.prepareStatement(sql);
			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;
			}
				
				sql = "INSERT "
						+ "INTO "
						+ "datewise_closing_balance "
						+ "(entry_id,shop_id,shop_code,account_id,closeing_bal_date,closeing_bal_amount,last_entry_updated_by) "
						+ "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, entryDate);
				preparedStatement.setFloat(6, newClosingBalForThatDate);
				preparedStatement.setString(7, entryBy);
				
				rowUpdated = preparedStatement.executeUpdate();
			}
			
			if(rowUpdated > 0)
			{
				if(preparedStatement != null)
				{
					try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
				}
				
				
				String updateFutureSql = "UPDATE datewise_closing_balance " +
				                         "SET closeing_bal_amount = closeing_bal_amount " + 
				                         (entryType.equals("CREDIT") ? "+ ?" : "- ?") + " " +
				                         "WHERE closeing_bal_date > STR_TO_DATE(?,'%d-%m-%Y') " +
				                         "AND account_id = ? " +
				                         "AND shop_id = ? " +
				                         "AND shop_code = ?";

				preparedStatement = connection.prepareStatement(updateFutureSql);
				preparedStatement.setFloat(1, amount);
				preparedStatement.setString(2, entryDate);
				preparedStatement.setString(3, accountId);
				preparedStatement.setString(4, shopId);
				preparedStatement.setString(5, shopCode);

				preparedStatement.executeUpdate();

				
				
				
				entryAddedUpdated = true;
			}
			else
			{
				entryAddedUpdated = false;
			}
			
			
		}
		catch (SQLException e) {
			entryAddedUpdated = false;
			//e.printStackTrace();
		}
		catch (Exception e) {
			//e.printStackTrace();
			entryAddedUpdated = false;
		}finally {
			if(resultSet != null)
			{
				try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
			}
			if(preparedStatement != null)
			{
				try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
			}
			
			
		}
		
		
		
		return entryAddedUpdated;
	}
}
