package other_package;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpSession;
import main_package.DataBaseConnection;

public class DsaCodeEntryInLoanRdTable 
{
	public static void main(String[] args) 
	{
		Connection conn=DataBaseConnection.JAVAConnection();
		ArrayList<String> pList, gList, dList;
		pList=allPersonalLoanNoWithDsaCode(conn);
		gList=allGroupLoanNoWithDsaCode(conn);
		dList=allDepositNoWithDsaCode(conn);
		
		if(pList.size()>0)
		{
			updateDsaCodeInLoanPaymentTable(pList, conn);
		}
		
		if(gList.size()>0)
		{
			updateDsaCodeInLoanPaymentTable(gList, conn);
		}
		
		if(dList.size()>0)
		{
			updateDsaCodeInPaymentReceiptTable(dList, conn);
		}
	}

	private static void updateDsaCodeInPaymentReceiptTable(ArrayList<String> dList, Connection conn) 
	{
		for(String ss: dList)
		{
			String arr[]=ss.split(",");
			String userNo=arr[0];
			String dsaCode=arr[1];
			try 
			{
				Statement st=conn.createStatement();
				String qq="update payment_receipt set dsa_code='"+dsaCode+"' where user_no='"+userNo+"' ";
				st.executeUpdate(qq);
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
	}

	private static void updateDsaCodeInLoanPaymentTable(ArrayList<String> pList, Connection conn) 
	{
		for(String ss: pList)
		{
			String arr[]=ss.split(",");
			String loanNo=arr[0];
			String dsaCode=arr[1];
			try 
			{
				Statement st=conn.createStatement();
				String qq="update loanpayment set dsa_code='"+dsaCode+"' where loanNo='"+loanNo+"' ";
				st.executeUpdate(qq);
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
	}

	private static ArrayList<String> allPersonalLoanNoWithDsaCode(Connection conn) 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId = (int) ss.getAttribute("branchId");
		ArrayList<String> pList=new ArrayList<>();
		try 
		{
			Statement st=conn.createStatement();
			String qq="select loanNo,dsacode from personalloan where branch_id='"+branchId+"'";
			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				if(rr.getString("dsacode")!=null && !rr.getString("dsacode").equalsIgnoreCase(""))
				{
					pList.add(rr.getString("loanNo")+","+rr.getString("dsacode"));
				}
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return pList;
	}

	private static ArrayList<String> allGroupLoanNoWithDsaCode(Connection conn) 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId = (int) ss.getAttribute("branchId");
		ArrayList<String> gList=new ArrayList<>();
		try 
		{
			Statement st=conn.createStatement();
			String qq="select sub_loan_no,dsacode from grouploan where branch_id='"+branchId+"'";
			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				if(rr.getString("dsacode")!=null && !rr.getString("dsacode").equalsIgnoreCase(""))
				{
					gList.add(rr.getString("sub_loan_no")+","+rr.getString("dsacode"));
				}
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return gList;
	}

	private static ArrayList<String> allDepositNoWithDsaCode(Connection conn) 
	{
		HttpSession ss=(HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
		int branchId = (int) ss.getAttribute("branchId");
		ArrayList<String> dList=new ArrayList<>();
		try 
		{
			Statement st=conn.createStatement();
			String qq="select user_no,dsa_code from customer_info where branch_id='"+branchId+"'";
			ResultSet rr=st.executeQuery(qq);
			while(rr.next())
			{
				if(rr.getString("dsa_code")!=null && !rr.getString("dsa_code").equalsIgnoreCase(""))
				{
					dList.add(rr.getString("user_no")+","+rr.getString("dsa_code"));
				}
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return dList;
	}
}
