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 GetSupplierDebtAmountById {
	
	
	public static Float getSupplierDebtAmount(String supplierId, SessionMap<String, Object> sessionMap)
	{
		
		float supplierDebtAmount = 0.0F;
		
		

    	Connection connection = null;
   		PreparedStatement preparedStatement= null;
   		ResultSet resultSet = null;
		
   		String sql = "";
   		String db = (String) sessionMap.get("db");
   		try {
   			
   			String shopId = (String) sessionMap.get("shopId");
    		String shopCode = (String) sessionMap.get("shopCode");
   			
			connection = GetDBConnection.getDBConnection(db);
   			
   			
   		
   			
   			float openingBal = 0.0F;
   			PreparedStatement getOpeningBalPSMT = null;
				ResultSet getOpeningBalRs = null;
				String getOpeningBalSQL = "SELECT "
						+ "opening_balance_amount "
						+ "FROM "
						+ "shop_supplier_open_close_balance_master "
						+ "WHERE "
						+ "shop_id = ? "
						+ "AND "
						+ "shop_code  = ? "
						+ "AND "
						+ "supplier_id = ?";
				getOpeningBalPSMT = connection.prepareStatement(getOpeningBalSQL);
				
				getOpeningBalPSMT.setString(1, shopId);
				getOpeningBalPSMT.setString(2, shopCode);
				getOpeningBalPSMT.setString(3, supplierId);
				
				getOpeningBalRs = getOpeningBalPSMT.executeQuery();
				
				if(getOpeningBalRs.next())
				{
					openingBal = getOpeningBalRs.getFloat(1);
				}
				else
				{
					openingBal = 0.0F;
				}
				
				if(getOpeningBalRs != null)
				{
					getOpeningBalRs.close();
					getOpeningBalRs = null;
				}
				if(getOpeningBalPSMT != null)
				{
					getOpeningBalPSMT.close();
					getOpeningBalPSMT = null;
				}

   		
   			
   			sql = "SELECT "
   					+ "supplier_id,"
   					+ "suplier_name,"
   					+ "contact_number,"
   					+ "supplier_address "
   					+ "FROM "
   					+ "shop_supplier_master "
   					+ "WHERE "
   					+ "shop_id = ? "
   					+ "AND "
   					+ "shop_code = ? "
   					+ "AND "
   					+ "supplier_id =  ? ";
   			
   			
   			preparedStatement = connection.prepareStatement(sql);
   			
   			preparedStatement.setString(1, shopId);
   			preparedStatement.setString(2, shopCode);
   			preparedStatement.setString(3, supplierId);
   			
   			
   			resultSet = preparedStatement.executeQuery();
   			
   			resultSet.last();
   			
   			int rowCount = resultSet.getRow();
   			
   			resultSet.beforeFirst();
   			
   			if(rowCount > 0)
   			{
   				if(resultSet.next())
   					
   				{
   			
   				
   				

				float floatTotalPurchaseAmt = 0.0F;
   				float floatTotalAmountPaid = 0.0F;
   				float floatTotalBalanceAmt = 0.0F;
   				
   				PreparedStatement getTotalPurchasePSMT = null;
   				ResultSet getTotalPurchaseRS = null;
   				
   				String getTotalPurchaseSQL = "SELECT "
   						+ "SUM(total_purchase_amount),"
   						+ "SUM(purchase_amount_paid) "
   						+ "FROM "
   						+ "shop_purchase_info_master "
   						+ "WHERE "
   						+ "shop_id = ? "
   						+ "AND "
   						+ "shop_code = ? "
   						+ "AND "
   						+ "supplier_id = ? ";
   				
   				getTotalPurchasePSMT = connection.prepareStatement(getTotalPurchaseSQL);
   				
   				getTotalPurchasePSMT.setString(1, shopId);
   				getTotalPurchasePSMT.setString(2, shopCode);
   				getTotalPurchasePSMT.setString(3, resultSet.getString(1));
   				
   				getTotalPurchaseRS = getTotalPurchasePSMT.executeQuery();
   				
   				if(getTotalPurchaseRS.next())
   				{
   					floatTotalPurchaseAmt = getTotalPurchaseRS.getFloat(1);
   					floatTotalAmountPaid = getTotalPurchaseRS.getFloat(2);
   					
   					
   				}
   				
   				
   				PreparedStatement getSuppAmtPaidPSMT = null;
   				ResultSet getSuppAmtPaidRS = null;
   				String getSuppAmtPaidSQL = "SELECT "
   						+ "SUM(payment_amount) "
   						+ "FROM "
   						+ "shop_supplier_payment_details "
   						+ "WHERE "
   						+ "shop_id = ? "
   						+ "AND "
   						+ "shop_code = ?"
   						+ "AND "
   						+ "supplier_id = ? ";
   				
   				getSuppAmtPaidPSMT = connection.prepareStatement(getSuppAmtPaidSQL);
   				
   				
   				getSuppAmtPaidPSMT.setString(1, shopId);
   				getSuppAmtPaidPSMT.setString(2, shopCode);
   				getSuppAmtPaidPSMT.setString(3, resultSet.getString(1));
   				
   				getSuppAmtPaidRS = getSuppAmtPaidPSMT.executeQuery();
   				
   				
   				
   				if(getSuppAmtPaidRS.next())
   				{
   					floatTotalAmountPaid = floatTotalAmountPaid + getSuppAmtPaidRS.getFloat(1);
   				}
   				
   				floatTotalBalanceAmt = floatTotalPurchaseAmt - floatTotalAmountPaid;
   				
   			
   				
   				supplierDebtAmount =  floatTotalBalanceAmt+openingBal;
   				if(getSuppAmtPaidRS != null)
   				{
   					getSuppAmtPaidRS.close();
   					getSuppAmtPaidRS = null;
   				}
   				
   				if(getSuppAmtPaidPSMT != null)
   				{
   					getSuppAmtPaidPSMT.close();
   					getSuppAmtPaidPSMT = null;
   				}
   				
   				
   				
   				if(getTotalPurchaseRS != null)
   				{
   					getTotalPurchaseRS.close();
   					getTotalPurchaseRS = null;
   				}
   				
   				if(getTotalPurchasePSMT != null)
   				{
   					getTotalPurchasePSMT.close();
   					getTotalPurchasePSMT = null;
   				}
   				
   				
   				
   			}
   		
   			
   			}
   			
	        } catch (SQLException e) {
    			e.printStackTrace();
    			
    		} catch (IOException e) {
    			
    			e.printStackTrace();
    		
		    }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 supplierDebtAmount;
		
		
	}

}
