/**
 * Parses through the Customer Data excel file and creates objects for each
 * Customer and Major within the excel file and adds them to the database.
 *
 * @author Donald Kirk
 * @version 1.0
 *
 */
public class Runner {

	/**
	 * Create the main method to execute the program.
	 *
	 * @param args
	 * @throws IOException
	 *             file exception
	 */
	public static void main(String[] args) throws IOException {

		// Create an ArrayList to store the data read from excel sheet.
		List<List<XSSFCell>> sheetData = new ArrayList<List<XSSFCell>>();

		try {
			// Create an excel workbook from the file system.
			XSSFWorkbook workbook = new XSSFWorkbook("Lab5Dataset.xlsx");

			// Get the first sheet on the workbook.
			XSSFSheet sheet = workbook.getSheetAt(0);

			/*
			 * When we have a sheet object in hand we can iterator on each
			 * sheet's rows and on each row's cells. We store the data read on
			 * an ArrayList so that we can printed the content of the excel to
			 * the console.
			 */
			Iterator<?> rows = sheet.rowIterator();
			while (rows.hasNext()) {
				XSSFRow row = (XSSFRow) rows.next();
				Iterator<?> cells = row.cellIterator();

				List<XSSFCell> data = new ArrayList<XSSFCell>();
				while (cells.hasNext()) {
					XSSFCell cell = (XSSFCell) cells.next();
					data.add(cell);
				}

				sheetData.add(data);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		showExelData(sheetData);
		excelToDatabaseTable();

	}

	/**
	 * Iterates the data and print it out to the console.
	 *
	 * @param sheetData
	 *            being passed in.
	 */
	private static void showExelData(List<List<XSSFCell>> sheetData) {

		for (int i = 0; i < sheetData.size(); i++) {
			List<?> list = (List<?>) sheetData.get(i);
			for (int j = 0; j < list.size(); j++) {
				Cell cell = (Cell) list.get(j);
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING
						|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
				}
				if (j < list.size() - 1) {
				}
			}
		}
	}

	/**
	 * Import the excel file into the Database.
	 */
	private static void excelToDatabaseTable() {
		Statement stmt = null;
		Statement stmt2 = null;
		Statement stmt3 = null;

		try {

			/*
			 * CREATE DATABASE
			 */
			// Register JDBC driver.
			Class.forName("com.mysql.jdbc.Driver");
			// Open a connection.
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/", "root", "root");
			// Execute a query
			System.out.println("Creating database...");
			stmt = (Statement) con.createStatement();
			String database = "CREATE DATABASE IF NOT EXISTS Lab5";
			stmt.executeUpdate(database);
			System.out.println("Database created successfully...");
			stmt.close();
			con.close();

			/*
			 * CREATE TABLE IN DATABASE
			 */
			// Open a connection.
			Connection con2 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/Lab5", "root", "root");

			// Execute a query
			System.out.println("Creating customer table...");
			stmt2 = (Statement) con2.createStatement();
			String table = "CREATE TABLE IF NOT EXISTS `Customer` (`name` varChar(255) DEFAULT NULL, `phone` varChar(255) DEFAULT NULL,`email` varChar(255) DEFAULT NULL,`id_num` varChar(255),`street` varChar(255) DEFAULT NULL,`city` varChar(255) DEFAULT NULL,`zip` varChar(255) DEFAULT NULL,`region` varChar(255) DEFAULT NULL,`lat_long` varChar(255) DEFAULT NULL,`gpa` varChar(255) DEFAULT NULL,`major` varChar(255) DEFAULT NULL,`title` varChar(255) DEFAULT NULL,PRIMARY KEY (`id_num`))";
			stmt2.executeUpdate(table);
			stmt2.close();

			/*
			 * CREATE MAJOR TABLE
			 */
			Connection con3 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/Lab5", "root", "root");
			System.out.println("Creating major table...");
			stmt3 = (Statement) con3.createStatement();
			String majorTable = "CREATE TABLE IF NOT EXISTS `Major` (`major` varChar(255) DEFAULT 'N/A', `classification` varChar(255) DEFAULT 'N/A',`degree_level` varChar(255) DEFAULT 'N/A',`college` varChar(255) DEFAULT 'N/A',PRIMARY KEY (`major`,`classification`,`degree_level`,`college`))";
			stmt3.executeUpdate(majorTable);
			stmt3.close();

			/*
			 * ACCESS FILE
			 */
			PreparedStatement pstm = null;
			PreparedStatement pstm2 = null;
			OPCPackage pkg = OPCPackage.open(new File("Lab5Dataset.xlsx"));
			POIXMLProperties props = new POIXMLProperties(pkg);

			XSSFWorkbook wb = new XSSFWorkbook("Lab5Dataset.xlsx");
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFSheet majorSheet = wb.getSheetAt(1);
			Row row;

			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				DataFormatter formatter = new DataFormatter(Locale.US);
				row = (Row) sheet.getRow(i);
				if (row != null) {
					String name = formatter.formatCellValue(row.getCell(0));
					String phone = formatter.formatCellValue(row.getCell(1));
					String email = formatter.formatCellValue(row.getCell(2));
					String id_num = formatter.formatCellValue(row.getCell(3));
					String street = formatter.formatCellValue(row.getCell(4));
					String city = formatter.formatCellValue(row.getCell(5));
					String zip = formatter.formatCellValue(row.getCell(6));
					String region = formatter.formatCellValue(row.getCell(7));
					String lat_long = formatter.formatCellValue(row.getCell(8));
					String gpa = formatter.formatCellValue(row.getCell(9));
					String major = row.getCell(10).getRichStringCellValue().getString();
					String title = formatter.formatCellValue(row.getCell(11));

					String sql = "INSERT INTO Customer (name, phone, email, id_num, street, city,zip,region,lat_long,gpa,major,title) VALUES (?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE  `name`=?,`phone`=?,`email`=?,`street`=?,`city`=?,`zip`=?,`region`=?,`lat_long`=?,`gpa`=?,`major`=?,`title`=?";
					pstm = con2.prepareStatement(sql);

					// Set the Insert
					pstm.setString(1, name);
					pstm.setString(2, phone);
					pstm.setString(3, email);
					pstm.setString(4, id_num);
					pstm.setString(5, street);
					pstm.setString(6, city);
					pstm.setString(7, zip);
					pstm.setString(8, region);
					pstm.setString(9, lat_long);
					pstm.setString(10, gpa);
					pstm.setString(11, major);
					pstm.setString(12, title);

					// Set the Update
					pstm.setString(13, name);
					pstm.setString(14, phone);
					pstm.setString(15, email);
					pstm.setString(16, street);
					pstm.setString(17, city);
					pstm.setString(18, zip);
					pstm.setString(19, region);
					pstm.setString(20, lat_long);
					pstm.setString(21, gpa);
					pstm.setString(22, major);
					pstm.setString(23, title);

					pstm.executeUpdate();
					pstm.close();

				}
			}

			for (int i = 1; i <= majorSheet.getLastRowNum(); i++) {
				DataFormatter formatter = new DataFormatter(Locale.US);
				row = (Row) majorSheet.getRow(i);
				if (row != null) {
					String major = formatter.formatCellValue(row.getCell(0));
					String classification = formatter.formatCellValue(row.getCell(1));
					String degreeLevel = formatter.formatCellValue(row.getCell(2));
					String college = formatter.formatCellValue(row.getCell(3));

					String sql = "INSERT INTO Major (major, classification, degree_level, college) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE  `major`=?,`classification`=?,`degree_level`=?,`college`=?";

					// Set on the Insert.
					pstm2 = con3.prepareStatement(sql);
					pstm2.setString(1, major);
					pstm2.setString(2, classification);
					pstm2.setString(3, degreeLevel);
					pstm2.setString(4, college);

					// Set on the Update.
					pstm2.setString(5, major);
					pstm2.setString(6, classification);
					pstm2.setString(7, degreeLevel);
					pstm2.setString(8, college);

					pstm2.executeUpdate();
					pstm2.close();

				}
			}

			con2.close();
			con3.close();
			pkg.close();
			System.out.println("Success import excel to mysql table");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}