package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import org.json.simple.JSONObject;

import report_package.DataBaseReportMethods;

public class DataBaseMethodRaviJ implements Serializable
{
	String startSessionDateStr="2017-04-01";
	DecimalFormat dc=new DecimalFormat("##.##");
	SimpleDateFormat dateObj=new SimpleDateFormat("dd/MM/yyyy");
	DataBaseMethods db=new DataBaseMethods();
	DataBaseReportMethods dbrm = new DataBaseReportMethods();
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd");
	
	
	
	public String previousLoanByShareholderNo(String shareholderNo, String currentLoanNo, String loanType, Connection conn)
	{
	    String previousLoan = "";
	    try
	    {
	        String query = "";
	        if(loanType.equalsIgnoreCase("grouploan"))
	        {
	            query = "select loanNo from " + loanType + " where teamLeader=? and loanNo!=? and paidStatus='unpaid'";
	        }
	        else
	        {
	            query = "select loanNo from " + loanType + " where shareholderno=? and loanNo!=? and paidStatus='unpaid'";
	        }
	        PreparedStatement ps = conn.prepareStatement(query);

	        ps.setString(1, shareholderNo);
	        ps.setString(2, currentLoanNo);

	        ResultSet rs = ps.executeQuery();

	        StringBuilder sb = new StringBuilder();

	        while(rs.next())
	        {
	            if(sb.length() > 0)
	            {
	                sb.append(", ");
	            }
	            sb.append(rs.getString("loanNo"));
	        }
	        previousLoan = sb.toString();
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }
	    return previousLoan;
	}
	
	//for report module2 permission page name method  
		 public String userpassByUserId(String userId, Connection conn)
			{
				String qq="";	
				Statement st=null;
				ResultSet rr=null;
				try
				{
					st=conn.createStatement();
					qq="select password from login where userid='"+userId+"'  ";
					rr=st.executeQuery(qq);
					if(rr.next())
					{
						return rr.getString("password");
					}
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				finally
				{
					try {
						if(st!=null)
						{
							st.close();
						}
						if(rr!=null)
						{
							rr.close();
						}
					} catch (Exception e2) {
						// TODO: handle exception
					}
				}
				
				return "";
			}
	
	public ArrayList<UserInfo> combineCdsReportMethod(int branchId, Date searchDate, Date fromsSearchDate, String[] multiFrequency, 
													  String[] multiCenter, String dsaCode, String loanType, Connection conn) 
	{
		String qq="";
		String sDate=sdfForDb.format(searchDate);
		
		String endDate =sdfForDb.format(fromsSearchDate);
		Calendar cal = Calendar.getInstance();
		cal.setTime(fromsSearchDate);
		ArrayList<UserInfo> list=new ArrayList<>();
		int count=1;

		Statement st=null;
		ResultSet rr=null;
		
		try
		{
			
			
			String freqQuery = " ";
			
			if (multiFrequency != null && multiFrequency.length > 0) {
				
				freqQuery +=" and (" ;
				
				for (int i=0; i < multiFrequency.length; i++) {
                   if (i>0) {
					freqQuery += " OR";
				}
                   freqQuery += " frequency='" + multiFrequency[i] + "' OR frequency='" + multiFrequency[i] + ".0' ";
					
				}
				  freqQuery += ")";
			}
			
			System.out.println("freqQuery ===== " + freqQuery);
			
			
           System.out.println("fre");
			
			while (!cal.getTime().after(searchDate)) {
				String dateStr = sdfForDb.format(cal.getTime());
				Date date = sdfForDb.parse(dateStr);
			st=conn.createStatement();
			if(multiCenter==null || multiCenter.length==0)
			{
				qq="select * from "+loanType+" where status='Approve' and paidStatus='unpaid' " + freqQuery;
			   System.out.println("cbs query "+qq);
				if(!dsaCode.equalsIgnoreCase("all"))
				{
					qq+=" and dsacode='"+dsaCode+"' ";
				}
				
					rr=st.executeQuery(selectQueWithBranch(qq,branchId));
				  
				while(rr.next())
				{
					String loanNo="", shareHolderNo="";
					if(loanType.equalsIgnoreCase("grouploan"))
					{
						loanNo=rr.getString("sub_loan_no");
						shareHolderNo=rr.getString("teamLeader");
					}
					else
					{
						loanNo=rr.getString("loanNo");
						shareHolderNo=rr.getString("shareholderno");
					}
					
					ArrayList<String> dList=db.loanEmiStrDateList(rr.getInt("frequency"), rr.getInt("terms"), rr.getDate("emi_due_date"),rr.getDate("approvalDate"),
							loanNo, conn);
					if(dList.contains(dateObj.format(date)))
					{
						UserInfo uu=new  UserInfo();
						uu.setEmiDate(dateObj.format(date));
						uu.setSno(count++);
						uu.setLoanNo(loanNo);
						uu.setName(db.firstApplicantNameBySHNo(shareHolderNo, conn));
						uu.setLoanDisburseDate(db.loanDisburseDateByLoanNo(loanNo, conn));
						
						int loanFrequency = rr.getInt("frequency");

						if(loanFrequency == 365)
						{
						    uu.setLoanFrequency("Daily");
						}
						else if(loanFrequency == 52)
						{
						    uu.setLoanFrequency("1 Week");
						}
						else if(loanFrequency == 36)
						{
						    uu.setLoanFrequency("10 Days");
						}
						else if(loanFrequency == 26)
						{
						    uu.setLoanFrequency("2 Week");
						}
						else if(loanFrequency == 24)
						{
						    uu.setLoanFrequency("15 Days");
						}
						else if(loanFrequency == 13)
						{
						    uu.setLoanFrequency("4 Week");
						}
						else if(loanFrequency == 12)
						{
						    uu.setLoanFrequency("Monthly");
						}
						uu.setFather_husband_name(db.shareHolderDetailByColumnName(shareHolderNo, "father_husband_name", conn));
						uu.setMobile(db.shareHolderDetailByColumnName(shareHolderNo, "mobile", conn));
						uu.setLoanAmount(rr.getDouble("loanAmount"));
						
						uu.setCenterName(db.centerNameById(rr.getString("center"), conn));
						// No. of Installment Due up to Search Date:
						ArrayList<LoanInfo> loanEMIList=db.loanInstallment(loanType,uu.getLoanNo(), conn);
						//double currentEmiAmountDue = 0, currentDueEmi=0;
						for(int l=0;l<loanEMIList.size();l++)
						{	
							System.out.println(loanEMIList.get(l).getDueAmount());
							if(loanEMIList.get(l).getInstallDate().equals(date) && loanEMIList.get(l).getStatus().equalsIgnoreCase("Due"))
							{
								uu.setLoanEmiAmtDue(loanEMIList.get(l).getDueAmount());
								System.out.println(loanEMIList.get(l).getDueAmount() + "IN");
							}
						}
						
						int terms=rr.getInt("terms");
						int m=db.totalEMIPaidByLoanNo(uu.getLoanNo(), conn);
						int n=terms-m;
						uu.setUpcomingDetail(terms+"=" +m+"/"+n);
						
						
						String detail=dbrm.rdDueAmountForCdsReport(shareHolderNo,date, conn);
						if(detail.contains("#"))
						{
							String arr[]=detail.split("\\#");
							uu.setRdEmiAmtDue(Double.parseDouble(arr[2]));
							uu.setDetail(arr[1]);
							uu.setUser_no(arr[0]);
						}
						else
						{
							uu.setUser_no(detail);
						}
						
						uu.setTotalLoanRdEmiAmtDue(uu.getLoanEmiAmtDue()+uu.getRdEmiAmtDue());
						System.out.println(uu.totalLoanRdEmiAmtDue);
						//uu.setLoanTillDue(currentEmiAmountDue);
						
						list.add(uu);	
					}
			}
				
			}
			
			else
			for(int k=0; k<multiCenter.length;k++)
			{
				qq="select * from "+loanType+" where status='Approve' and paidStatus='unpaid' "  + freqQuery + " and center='"+multiCenter[k]+"' ";
				
				
				
				
				rr=st.executeQuery(selectQueWithBranch(qq,branchId));
				while(rr.next())
				{
					String loanNo="", shareHolderNo="";
					if(loanType.equalsIgnoreCase("grouploan"))
					{
						loanNo=rr.getString("sub_loan_no");
						shareHolderNo=rr.getString("teamLeader");
					}
					else
					{
						loanNo=rr.getString("loanNo");
						shareHolderNo=rr.getString("shareholderno");
					}
					
					ArrayList<String> dList=db.loanEmiStrDateList(rr.getInt("frequency"), rr.getInt("terms"), rr.getDate("emi_due_date"),rr.getDate("approvalDate"),
							loanNo, conn);
					if(dList.contains(dateObj.format(date)))
					{
						UserInfo uu=new  UserInfo();
						uu.setEmiDate(dateObj.format(date));
						uu.setSno(count++);
						uu.setLoanNo(loanNo);
						uu.setName(db.firstApplicantNameBySHNo(shareHolderNo, conn));
						
uu.setLoanDisburseDate(db.loanDisburseDateByLoanNo(loanNo, conn));
						
						int loanFrequency = rr.getInt("frequency");

						if(loanFrequency == 365)
						{
						    uu.setLoanFrequency("Daily");
						}
						else if(loanFrequency == 52)
						{
						    uu.setLoanFrequency("1 Week");
						}
						else if(loanFrequency == 36)
						{
						    uu.setLoanFrequency("10 Days");
						}
						else if(loanFrequency == 26)
						{
						    uu.setLoanFrequency("2 Week");
						}
						else if(loanFrequency == 24)
						{
						    uu.setLoanFrequency("15 Days");
						}
						else if(loanFrequency == 13)
						{
						    uu.setLoanFrequency("4 Week");
						}
						else if(loanFrequency == 12)
						{
						    uu.setLoanFrequency("Monthly");
						}
						uu.setFather_husband_name(db.shareHolderDetailByColumnName(shareHolderNo, "father_husband_name", conn));
						uu.setMobile(db.shareHolderDetailByColumnName(shareHolderNo, "mobile", conn));
						uu.setLoanAmount(rr.getDouble("loanAmount"));
						uu.setCenterName(db.centerNameById(multiCenter[k], conn));
						   
						
						// No. of Installment Due up to Search Date:
						ArrayList<LoanInfo> loanEMIList=db.loanInstallment(loanType,uu.getLoanNo(), conn);
						//double currentEmiAmountDue = 0, currentDueEmi=0;
						for(int l=0;l<loanEMIList.size();l++)
						{
							if(loanEMIList.get(l).getInstallDate().equals(date) && loanEMIList.get(l).getStatus().equalsIgnoreCase("Due"))
							{System.out.println(loanEMIList.get(l).getDueAmount()+"IN");
								uu.setLoanEmiAmtDue(loanEMIList.get(l).getDueAmount());
							}
						}
						
						int terms=rr.getInt("terms");
						int m=db.totalEMIPaidByLoanNo(uu.getLoanNo(), conn);
						int n=terms-m;
						uu.setUpcomingDetail(terms+"=" +m+"/"+n);
						
						
						String detail=dbrm.rdDueAmountForCdsReport(shareHolderNo,date, conn);
						if(detail.contains("#"))
						{
							String arr[]=detail.split("\\#");
							uu.setRdEmiAmtDue(Double.parseDouble(arr[2]));
							uu.setDetail(arr[1]);
							uu.setUser_no(arr[0]);
						}
						else
						{
							uu.setUser_no(detail);
						}
						
						uu.setTotalLoanRdEmiAmtDue(uu.getLoanEmiAmtDue()+uu.getRdEmiAmtDue());
						//uu.setLoanTillDue(currentEmiAmountDue);
						
						list.add(uu);	
					}
			}
				
			}
				cal.add(Calendar.DATE, 1);
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				if(st!=null)
				{
					st.close();
				}
				if(rr!=null)
				{
					rr.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}
	
	
	
	public String selectQueWithBranch(String query,int branchId)
	{
		if(branchId==-1)
		{
			
			if(query.contains("where"))
			{
				query=query+" and "+new DataBaseMethods().branchBuilder();
			}
			else
			{
				query=query+" where"+new DataBaseMethods().branchBuilder();
			}
		}
		else
		{
			if(query.contains("where"))
			{
				query=query+" and branch_id='"+branchId+"'";
			}
			else
			{
				query=query+" where branch_id='"+branchId+"'";
			}
		}
		
		//System.out.println(query);
		return query;
	}
	
	
	public JSONObject checkLoanPaymentSource(String loanNo)
	{
	    JSONObject obj = new JSONObject();

	    try(Connection conn = DataBaseConnection.JAVAConnection())
	    {

	        String query =
	                "select loan_no, amount, employee " +
	                "from loan_payment_from_app " +
	                "where loan_no=? " +
	                "and DATE(pay_date)=CURDATE() " +
	                "order by pay_date desc " +
	                "limit 1";

	        try(PreparedStatement ps = conn.prepareStatement(query))
	        {
	            ps.setString(1, loanNo);

	            try(ResultSet rs = ps.executeQuery())
	            {
	                if(rs.next())
	                {
	                    obj.put("loanNo", rs.getString("loan_no"));
	                    obj.put("amount", rs.getString("amount"));
	                    obj.put("userId", rs.getString("employee"));
	                    obj.put("source", "from app");

	                    return obj;
	                }
	            }
	        }



	        query =
	                "select loanNo, amount, user_id " +
	                "from loanpayment " +
	                "where loanNo=? " +
	                "and DATE(pay_date)=CURDATE() " +
	                "order by pay_date desc " +
	                "limit 1";

	        try(PreparedStatement ps = conn.prepareStatement(query))
	        {
	            ps.setString(1, loanNo);

	            try(ResultSet rs = ps.executeQuery())
	            {
	                if(rs.next())
	                {
	                    obj.put("loanNo", rs.getString("loanNo"));
	                    obj.put("amount", rs.getString("amount"));
	                    obj.put("userId", rs.getString("user_id"));
	                    obj.put("source", "from web");

	                    return obj;
	                }
	            }
	        }


	        obj.put("loanNo", loanNo);
	        obj.put("amount", "");
	        obj.put("userId", "");
	        obj.put("source", "not found");
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();

	        obj.put("loanNo", loanNo);
	        obj.put("amount", "");
	        obj.put("userId", "");
	        obj.put("source", "error");
	    }

	    return obj;
	}
	
	
}