package main_package;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.model.SelectItem;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Iterator;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.primefaces.model.UploadedFile;

@ManagedBean(name="excelImportSecond")
@ViewScoped

public class ExcelImportSecondBean implements Serializable
{
	String filePath, transferShareHolderNo;
	UploadedFile excelFile;
	ArrayList<SelectItem> transferShareList;
	ArrayList<UserInfo> stuList=new ArrayList<>();
	int count=1;
	Date date1;
	DataBaseMethods obj=new DataBaseMethods();
	
	//// Center Table Data Insert Code
	public void importFromExcel() throws IOException, ParseException 
	{
		if (excelFile != null && excelFile.getSize() > 0) 
		{
			filePath = excelFile.getFileName();
			
			obj.makeProfile(excelFile, filePath); //Offline
			ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
			String realPath = ctx.getRealPath("/");
			
			filePath=realPath+filePath;
		}
		
		if(filePath!=null || !filePath.equals(""))
		{
			String filename = filePath;
			List sheetData = new ArrayList();

			FileInputStream fis = null;
			try 
			{
				fis = new FileInputStream(filename);

				HSSFWorkbook workbook = new HSSFWorkbook(fis);
				HSSFSheet sheet = workbook.getSheetAt(0);
				Iterator rows = sheet.rowIterator();
				while (rows.hasNext()) 
				{
					HSSFRow row = (HSSFRow) rows.next();
					Iterator cells = row.cellIterator();

					List data = new ArrayList();
					while (cells.hasNext()) 
					{
						HSSFCell cell = (HSSFCell) cells.next();
						data.add(cell);
					}

					sheetData.add(data);
				}
			} 
			
			catch (IOException e) 
			{
				e.printStackTrace();
			} 
			finally 
			{
				if (fis != null) {
					fis.close();
				}
				fis.close();
			}

			showExelData(sheetData);
		}
		else
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error In File Uploading.Please Check!!!"));
		}
		
	}
	
		private void showExelData(List sheetData) throws ParseException 
		{
		
		Connection conn=DataBaseConnection.JAVAConnection();
		//ExcelRead read=new ExcelRead();
		ExcelImportBean obj=new ExcelImportBean();
		
		
		// System.out.println("size : "+sheetData.size());
		for (int i = 1; i < sheetData.size(); i++) {
			List list = (List) sheetData.get(i);
			
			UserInfo info=new UserInfo();
			
			String temp="";
			for (int j = 0; j <2; j++) 
			{
				Cell cell = (Cell) list.get(j);
				// System.out.println("j: "+list.size());
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					
					DataFormatter fmt = new DataFormatter();

					String valueAsSeenInExcel = fmt.formatCellValue(cell);
					temp=valueAsSeenInExcel;
				
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					
					temp=String.valueOf(cell.getRichStringCellValue());

				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					
					temp=String.valueOf(Cell.CELL_TYPE_BOOLEAN);
				}
				else
				{
					temp="";
				}
					
					if(j==0)
					{
						info.setColumn1(temp);
					}
					else if(j==1)
					{
						info.setColumn2(temp);
					}
					
					
			}
			stuList.add(info);
			//counter++;
		
		}
			insertData(conn, stuList);
		}
	
	public void insertData(Connection conn, ArrayList<UserInfo> list) throws ParseException
	{
		int i=0;
		for(UserInfo info : list)
		{
			//obj.addCenter(branchId,info.getColumn2(),info.getColumn1(),"", dsaCode,conn);
		}
		
		if(i>=1)
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Upload sucessfully"));
		}
		
		else
		{
			FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error Occured!!!"));
		}
		stuList=new ArrayList<>();
	}
	
	

	public UploadedFile getExcelFile() {
		return excelFile;
	}

	public void setExcelFile(UploadedFile excelFile) {
		this.excelFile = excelFile;
	}
}