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 GetCustomerCreditAmountById {
	
	
	public static Float getCustomerCreditAmountById(String custId , SessionMap<String, Object> sessionMap)
	{
		
		Float customerCredit = 0.0F;
		
		
	   	String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
     	
	
    	//System.out.println("hello");
    	
    	Connection connection = null;
   		PreparedStatement preparedStatement= null;
   		ResultSet resultSet = null;
		
   		
   		String db = (String) sessionMap.get("db");
   		try {
   			
   			
			connection = GetDBConnection.getDBConnection(db);
			
			
				float openingBal = 0.0f;
				
				PreparedStatement getOpeningBalPSMT = null;
				ResultSet getOpeningBalRS = null;
				String sqlOpeningBal = "SELECT "
						+ "opening_balance "
						+ "FROM "
						+ "shop_cust_close_open_bal_master  "
						+ "WHERE "
						+ "customer_id = ? "
						+ "AND "
						+ "shop_id = ? "
						+ "AND "
						+ "shop_code = ? ";
				
				getOpeningBalPSMT = connection.prepareStatement(sqlOpeningBal);
				
				getOpeningBalPSMT.setString(1, custId);
				getOpeningBalPSMT.setString(2, shopId);
				getOpeningBalPSMT.setString(3, shopCode);
				
				getOpeningBalRS = getOpeningBalPSMT.executeQuery();
				
				if(getOpeningBalRS.next())
				{
					openingBal = getOpeningBalRS.getFloat(1);
				}
			
				if(getOpeningBalRS != null)
				{
					getOpeningBalRS.close();
					getOpeningBalRS = null;
				}
				
				if(getOpeningBalPSMT != null)
				{
					getOpeningBalPSMT.close();
					getOpeningBalPSMT = null;
				}
				
				
				
				float floatBillAmt = 0.0F;
   				float floatBillAmtReceived = 0.0F;
   				float floatBillBalanceAmt = 0.0F;
   				
   				float totalAmountRecieved = 0.0F;
   				float totalBalanceAmt = 0.0F;			       			
   				float totalPaymentDone = 0.0F;
   				
   				
   				
   				String sql = "SELECT "	           					
       					+ "bill_total_amount,"
       					+ "bill_amount_received,"
       					+ "bill_amount_retrurn "
       					+ "FROM "
       					+ "shop_bill_info_master "
       					+ "where "		           					
       					+ "customer_id = ? "
       					+ "AND "
       					+ "shop_id = ? "
       					+ "AND "
       					+ "shop_code = ? ";
       				
   				
   				
   				preparedStatement = connection.prepareStatement(sql);
   				preparedStatement.setString(1, custId);
   				preparedStatement.setString(2, shopId);
   				preparedStatement.setString(3, shopCode);
   				
   				ResultSet resultSetBillInfo = preparedStatement.executeQuery();
   				
   				while(resultSetBillInfo.next())
   				{
   					try
   					{
   						if(resultSetBillInfo.getString(2) != null)
   						{
       						floatBillAmt = resultSetBillInfo.getFloat(1);
       						floatBillAmtReceived = resultSetBillInfo.getFloat(2);
       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
       						
	           				
	           				if(floatBillBalanceAmt > 0)
	           				{
	           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
	           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
	           					
	           					
	           				}
	           				else if(floatBillBalanceAmt <= 0)
	           				{
	           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
	           				}
   						}
           				
   					}catch(NumberFormatException nfe)
   					{
   						
   						
   					}
   				}
   				totalBalanceAmt = totalBalanceAmt + openingBal;
   				if(resultSetBillInfo != null)
   				{
   					resultSetBillInfo.close();
   					resultSetBillInfo = null;
   				}
   				
   				if(preparedStatement != null)
   				{
   					preparedStatement.close();
   					preparedStatement = null;
   				}
   				
   				sql = "SELECT "
   						+ "sum(payment_amount) "
   						+ "FROM "
   						+ "shop_customer_payment_master "
   						+ "WHERE "
   						+ "customer_id = ?"
   						+ "AND "
   						+ "shop_id = ? "
   						+ "AND "
   						+ "shop_code = ? ";
   			
   				preparedStatement = connection.prepareStatement(sql);
   				preparedStatement.setString(1, custId);
   				preparedStatement.setString(2, shopId);
   				preparedStatement.setString(3, shopCode);
   				
   				
   				ResultSet resultSetTotalPayment = preparedStatement.executeQuery();
   				
   				resultSetTotalPayment.last();
   				int rowCount2 = resultSetTotalPayment.getRow();
   				resultSetTotalPayment.beforeFirst();
   				
   				if(rowCount2>0)
   				{
   				
       				while(resultSetTotalPayment.next())
       				{
       					try
       					{
       						if(resultSetTotalPayment.getString(1) != null)
       						{		       						
       							totalPaymentDone = Float.parseFloat(resultSetTotalPayment.getString(1));
       						}
       					}
       					catch(NumberFormatException nfe)
       					{
       					}
       				}
       						
       				
       				totalBalanceAmt = totalBalanceAmt - totalPaymentDone;
   				}
   				
   				if(resultSetTotalPayment != null)
   				{
   					resultSetTotalPayment.close();
   					resultSetTotalPayment = null;
   				}
				customerCredit = totalBalanceAmt;
				
   		}catch (SQLException e) {
				//e.printStackTrace();
			
		
		} catch (IOException e) {
			
			
		}	
			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 customerCredit;
	}

}
