package com.reallinkbilling.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;

import org.apache.struts2.dispatcher.SessionMap;

public class GetLastDayClosingBalAmount {
	
	
	public static float getlastDayClosingBalAmount(String accountId, String accoutingDate, Connection connection, SessionMap<String, Object> sessionMap)	
	{
		float lastDayCloseingBal = 0.0f;
		
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		
		try
		{
			sql = "SELECT "
					+ "closeing_bal_amount "
					+ "FROM "
					+ "datewise_closing_balance "
					+ "WHERE "
					+ "account_id = ? "
					+ "AND "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ? "
					+ "ORDER BY "
					+ "closeing_bal_date DESC LIMIT 2";
			
			
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, accountId);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next())
			{
				lastDayCloseingBal = resultSet.getFloat(1);
			}
			
		}
		catch (SQLException e) 
		{
			lastDayCloseingBal = 0.0f;
			
		}
		catch (Exception e) 
		{
			lastDayCloseingBal = 0.0f;
		}
		finally 
		{
			if(resultSet != null)
			{
				try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
			}
			if(preparedStatement != null)
			{
				try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
			}
			
			
		}
		
		return lastDayCloseingBal;
		
		
	}





public static String getlastDayClosingBalAndDateAmount(String accountId, String accoutingDate, Connection connection, SessionMap<String, Object> sessionMap)	
{
	float lastDayCloseingBal = 0.0f;
	DecimalFormat df = new DecimalFormat("##.##");
	PreparedStatement preparedStatement= null;
	ResultSet resultSet = null;
	
	String shopId = (String) sessionMap.get("shopId");
	String shopCode = (String) sessionMap.get("shopCode");
	
	String sql = "";
	String lastBalDate = "";
	try
	{
		sql = "SELECT "
				+ "DATE_FORMAT(closeing_bal_date,'%d-%m-%Y'), "
				+ "closeing_bal_amount "
				+ "FROM "
				+ "datewise_closing_balance "
				+ "WHERE "
				+ "account_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? "
				+ "ORDER BY "
				+ "closeing_bal_date DESC LIMIT 2";
		
		
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, accountId);
		preparedStatement.setString(2, shopId);
		preparedStatement.setString(3, shopCode);
		
		resultSet = preparedStatement.executeQuery();
		
		while(resultSet.next())
		{
			
			lastBalDate = resultSet.getString(1);
			lastDayCloseingBal = resultSet.getFloat(2);
		}
		
	}
	catch (SQLException e) 
	{
		lastDayCloseingBal = 0.0f;
		
	}
	catch (Exception e) 
	{
		lastDayCloseingBal = 0.0f;
	}
	finally 
	{
		if(resultSet != null)
		{
			try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
		}
		if(preparedStatement != null)
		{
			try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
		}
		
		
	}
	
	return lastBalDate+"#" +df.format(lastDayCloseingBal);
	
}

}
