package org.ggpl.bankreconciliation;
import java.io.File;
import java.io.*;
import jxl.*;
import java.util.*;
import jxl.Workbook;
import jxl.read.biff.*;
import java.sql.*;
import org.ggpl.TrialBalance.ConnectDB;
 
 
public class ReadXls{ 
	public ArrayList getExcel(String filename){
		ArrayList sheet = new ArrayList();
		try{
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			Workbook workbook = Workbook.getWorkbook( new File(filename),ws);
			Sheet s = workbook.getSheet(1); 
			System.out.println("Sheet Name::"+s.getName()); 
			sheet = readDataSheet(s);
			workbook.close();
		}catch (IOException e){ 
		e.printStackTrace(); 
		}catch (BiffException e){ 
		e.printStackTrace();
		} 
		return sheet;
	}
	private ArrayList readDataSheet(Sheet s){ 
		//gets the value of cell at specified column and row
		int rows = s.getRows();
		int cols = s.getColumns();
		ArrayList al1 = new ArrayList();
		ArrayList al2 = new ArrayList();
		ArrayList al3 = new ArrayList();
		ArrayList al4 = new ArrayList();
		int row = s.findCell("1").getRow();
		int col = s.findCell("1").getColumn();
		int chqcol = s.findCell("ChequeNo.").getColumn();
		for(int i=row;i<rows;i++){		
			if(s.getCell(col,i).getContents().equals(""))
				break;
			else{
				reconBean rb = new reconBean();				
				for(int j=col+1;j<cols;j++){
					if(j==1){
						rb.setBankdate(s.getCell(j,i).getContents());
					}else if(j==2){
						rb.setNarration(s.getCell(j,i).getContents());
					}else if(j==3){
						rb.setChequeNo(s.getCell(j,i).getContents());
					}else if(j==4){
						rb.setAmounttype(s.getCell(j,i).getContents());
					}else if(j==5){
						rb.setAmount(setAmount(s.getCell(j,i).getContents()));
					}
				}
				if(!s.getCell(chqcol,i).getContents().equals("")){
					String docid = getDocid(rb.getChequeNo(),rb.getAmount());
					if(!docid.equals("")){					
						docIDBean dbean = new docIDBean();
						dbean.setDocid(docid);
						al3.add(dbean);
						al1.add(rb);
					}else{
						al2.add(rb);
					}
				}else{
					al2.add(rb);
				}
			}
		}
		al4.add(al1);
		al4.add(al2);
		al4.add(al3);
		System.out.println("al1 size :"+al1.size());
		System.out.println("al2 size :"+al2.size());
		System.out.println("al3 size :"+al3.size());
		return al4;
	}
 
	public double setAmount(String amt){
		double amount = 0.00D;
		if(amt.equals("")){
			amount = 0.00;
		}else{
			String amts[] = amt.split(",");
			amt = "";
			for(int i=0;i<amts.length;i++){
				amt = amt + amts[i];
			}
			amount = Double.parseDouble(amt);
		}
		return amount;
	}
 
	public String getDocid(String chqno,double amt){
		String docid = "";
		ConnectDB db = new ConnectDB();
		try{
			db.connect();
			Connection con = db.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select doc_id from CHEQUE_MASTER c,DOC_MASTER d where c.CHEQUE_ID=d.CHEQUE_ID and c.CHEQUE_NO='"+chqno+"' and c.CHEQUE_AMOUNT='"+amt+"'");
			while(rs.next()){
				docid = rs.getString(1);
			}
		}catch (Exception e){
			System.out.println("There is a Problem in ReadXls Class ");
			e.printStackTrace();
			db.disconnect();
		}
		return docid;
	}
}