//Author : Jagbir Singh
//Debug : jagbir Singh
//Tested by : Amanbir Singh

package com.bhm;
import java.io.FileInputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Vector;

import java.sql.*;

import javax.sql.DataSource;
import javax.naming.*;

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.poifs.filesystem.POIFSFileSystem;


public class ReadExcelFile {
        public static void main(String[] args) {

                String fileName = "Raw_Data_Export.xls";
                Vector dataHolder = ReadCSV(fileName);
          
                Date cur = new Date();
                System.out.println(cur);
        }

        public static Vector ReadCSV(String fileName) {
                Vector cellVectorHolder = new Vector();
                System.out.println("inside ReadCSV");

                try {
                        FileInputStream myInput = new FileInputStream(fileName);

                        POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

                        HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

                        HSSFSheet mySheet = myWorkBook.getSheetAt(0);

                        Iterator rowIter = mySheet.rowIterator();

                        while (rowIter.hasNext()) {
                                HSSFRow myRow = (HSSFRow) rowIter.next();
                                Iterator cellIter = myRow.cellIterator();
                                Vector cellStoreVector = new Vector();
                                while (cellIter.hasNext()) {
                                        HSSFCell myCell = (HSSFCell) cellIter.next();
                                        cellStoreVector.addElement(myCell);
                                }
                                cellVectorHolder.addElement(cellStoreVector);
                        }
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return cellVectorHolder;
        }

            
        
        public static void insertDataIntoDB(Vector dataHolder, String patientId) {
        		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.000");
        		int []tap = new int[5000];
        		String totEnerExp = null;
        		String numSteps = null;
        		String lyingDownTime = null;
        		String sleepTime = null;
        		
        		String physActivityDur = null;
        		String modActivityDur = null;
        		String sedActivityDur = null;
        		String vigActivityDur = null;
        		String veryVigActivityDur = null;
        		String dateOfData = null;
        		DataSource pool;
        		int flag =0;
        		
        		  Connection conn = null;
        		  
        		 
        		  PreparedStatement pstmt = null;
        	
        		  String loginQuery = null;

        		  try {
        			
        			InitialContext ctx = new InitialContext();
        			pool = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
        			if (pool == null)
        			   throw new SQLException("Unknown DataSource 'jdbc/TestDB'");
        			conn = pool.getConnection();
        			
        			loginQuery = "Select docId from userInfo where user_Id = ? ";
        			pstmt = conn.prepareStatement(loginQuery);
        			pstmt.setString(1, patientId);
        			ResultSet rs = pstmt.executeQuery();
        			rs.next();
        			String doc_Id = rs.getString("docId");
        			System.out.println(doc_Id);
        			
        			loginQuery = "insert into dataInfo (totEnerExp, numSteps, lyingDownTime, sleepTime, physActivityDur, sedenTary, modActivity, vigrousActivity, veryVigActivity, dataOfData, userId, docId) values(? ,? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        			pstmt = conn.prepareStatement(loginQuery);
        			
        			for (int i = 0; i < dataHolder.size(); i++) {
                        Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
  //                     
                 
                        
                        if(i>0 && cellStoreVector.size() > 5){
                        	HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(0);
                        	dateOfData = df.format(myCell.getDateCellValue());
                        	
                        	 myCell = (HSSFCell) cellStoreVector.elementAt(17);
                            String stringCellValue = myCell.toString();
                            totEnerExp = stringCellValue;
                            myCell = (HSSFCell) cellStoreVector.elementAt(10);
                            numSteps = myCell.toString();
                            myCell = (HSSFCell) cellStoreVector.elementAt(14);
                            lyingDownTime = myCell.toString();
                            
                            myCell = (HSSFCell) cellStoreVector.elementAt(15);
                            sleepTime = myCell.toString();
                            
                            myCell = (HSSFCell) cellStoreVector.elementAt(16);
                            physActivityDur = myCell.toString();
                            myCell = (HSSFCell) cellStoreVector.elementAt(19);
                            modActivityDur = myCell.toString();
                            myCell = (HSSFCell) cellStoreVector.elementAt(20);
                            vigActivityDur = myCell.toString();
                            myCell = (HSSFCell) cellStoreVector.elementAt(21);
                            veryVigActivityDur = myCell.toString();
                            myCell = (HSSFCell) cellStoreVector.elementAt(18);
                            sedActivityDur = myCell.toString();
                            
                            
                            pstmt.setString(1,totEnerExp );
                            pstmt.setString(2,numSteps );
                            pstmt.setString(3, lyingDownTime);
                            pstmt.setString(4, sleepTime);
                            pstmt.setString(5, physActivityDur);
                            pstmt.setString(6, sedActivityDur);
                            pstmt.setString(7, modActivityDur);
                            pstmt.setString(8, vigActivityDur);
                            pstmt.setString(9, veryVigActivityDur);
                            pstmt.setString(10, dateOfData);
                            pstmt.setString(11, patientId);
                            pstmt.setString(12, doc_Id);
                            pstmt.executeUpdate();
                        }
                                
                   
                        
                }
        			System.out.println("Data is successfully uploaded");
        			}
        			catch(Exception e){
        				e.printStackTrace();
        			} finally {
        				if(pstmt!= null){
        					try {
								pstmt.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
        				}
        				if(conn != null){
        					try {
								conn.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
        				}
        			}
        		
                
        }
}
