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;

import com.reallinkbilling.db.GetDBConnection;

public class GetCustomerTotalCreditAmountById {

    public static Float getCustomerTotalCreditAmountById(
            String custId,
            SessionMap<String, Object> sessionMap) {

        Float totalCreditAmount = 0.0F;

        if (custId == null || sessionMap == null) {
            return totalCreditAmount;
        }

        String shopId = (String) sessionMap.get("shopId");
        String shopCode = (String) sessionMap.get("shopCode");
        
        
        
        String db = (String) sessionMap.get("db");

        if (shopId == null || shopCode == null || db == null) {
            return totalCreditAmount;
        }

        Connection connection = null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            connection = GetDBConnection.getDBConnection(db);

            if (connection == null) {
                return totalCreditAmount;
            }

            float openingBalance = 0.0F;
            float totalBalanceAmt = 0.0F;
            float totalPaymentDone = 0.0F;

            /*
             * STEP 1: GET CUSTOMER OPENING BALANCE
             */

            String sql = "SELECT "
                    + "opening_balance "
                    + "FROM "
                    + "shop_cust_close_open_bal_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 = preparedStatement.executeQuery();

            if (resultSet.next()) {
                openingBalance = resultSet.getFloat("opening_balance");
            }

            resultSet.close();
            resultSet = null;

            preparedStatement.close();
            preparedStatement = null;


            /*
             * STEP 2: GET PENDING AMOUNT FROM BILLS
             */

            float floatTotalAmt = 0.0F;
            float floatAmtReceived = 0.0F;
            float floatBalanceAmt = 0.0F;
           

            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 = ? "
                    + "AND "
                    + "(bill_source IN ('ORIGINAL','BARCODEBILL'))";

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, custId);
            preparedStatement.setString(2, shopId);
            preparedStatement.setString(3, shopCode);

            ResultSet resultSetBillInfo = preparedStatement.executeQuery();

            while (resultSetBillInfo.next()) {
                try {

                    floatTotalAmt = Float.parseFloat(resultSetBillInfo.getString(1));
                    floatAmtReceived = Float.parseFloat(resultSetBillInfo.getString(2));

                    floatBalanceAmt = floatTotalAmt - floatAmtReceived;

                    if (floatBalanceAmt > 0) {
                        totalBalanceAmt = totalBalanceAmt + floatBalanceAmt;
                    }

                } catch (NumberFormatException nfe) {
                    // Ignore invalid amount
                }
            }

            if (resultSetBillInfo != null) {
                resultSetBillInfo.close();
                resultSetBillInfo = null;
            }

            if (preparedStatement != null) {
                preparedStatement.close();
                preparedStatement = null;
            }
            
            
            /*
             * STEP 2B: GET PENDING AMOUNT FROM CHALLANS
             */

            sql = "SELECT "
                    + "challan_total_amount,"
                    + "challan_amount_received "
                    + "FROM "
                    + "shop_challan_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 resultSetChallan = preparedStatement.executeQuery();

            while (resultSetChallan.next()) {

                float challanTotal =
                        resultSetChallan.getFloat("challan_total_amount");

                float challanReceived =
                        resultSetChallan.getFloat("challan_amount_received");

                float challanBalance = challanTotal - challanReceived;

                if (challanBalance > 0) {
                    totalBalanceAmt = totalBalanceAmt + challanBalance;
                }
            }

            resultSetChallan.close();
            preparedStatement.close();
            preparedStatement = null;
            
            
            
            /*
             * STEP 3: GET TOTAL CUSTOMER PAYMENTS
             */

            sql = "SELECT "
                    + "COALESCE(SUM(payment_amount), 0) AS total_payment "
                    + "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 = preparedStatement.executeQuery();

            if (resultSet.next()) {
                totalPaymentDone = resultSet.getFloat("total_payment");
            }

            resultSet.close();
            resultSet = null;

            preparedStatement.close();
            preparedStatement = null;


            /*
             * STEP 4: CALCULATE TOTAL CUSTOMER CREDIT AMOUNT
             */

            totalCreditAmount =
                    (openingBalance + totalBalanceAmt) - totalPaymentDone;

        } catch (SQLException e) {

            e.printStackTrace();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return totalCreditAmount;
    }
}
