import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.time.format.DateTimeFormatter;  
import java.time.LocalDateTime;    
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.comparator.LastModifiedFileComparator;
import org.apache.commons.io.filefilter.WildcardFileFilter;
import org.joget.apps.app.service.AppUtil;
import org.joget.workflow.model.service.WorkflowManager;
import java.util.Map;
import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.joget.apps.app.model.AppDefinition;
import org.joget.apps.app.service.AppService;
import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.apps.form.model.FormStoreBinder;
import org.joget.apps.form.service.FormUtil;
import org.joget.plugin.base.PluginManager;
import org.joget.commons.util.LogUtil;
import org.joget.apps.form.model.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;


/* Get the newest file for a specific extension */
public File getTheNewestFile(String filePath, String ext) {
	
    File theNewestFile = null;
    File dir = new File(filePath);
    FileFilter fileFilter = new WildcardFileFilter("*." + ext);
    File[] files = dir.listFiles(fileFilter);

    if (files.length > 0) {
        /** The newest file comes first **/
        Arrays.sort(files, LastModifiedFileComparator.LASTMODIFIED_REVERSE);
        theNewestFile = files[0];
    }
    return theNewestFile;
}

public void readCSV(String csvFile){
	String line = "";
	String cvsSplitBy = ",";

	try{
		BufferedReader br = new BufferedReader(new FileReader(csvFile));
	    while ((line = br.readLine()) != null) {

	        // use comma as separator
			// Check the file betul ke masuk.
	        String[] record = line.split(cvsSplitBy);
	        
	        String pic = record[0]; 
			String no1 = record[1];
			String no2 = record[2];
			int nno1 = Integer.parseInt(no1);
			int nno2 = Integer.parseInt(no2);
			int total = nno1 + nno2;
			String totalz = Integer.toString(total); 
			
			
	        System.out.println("-------- Record --------- \n [PIC = " + pic 
			+ "\nNo1 =" + no1 
			+ "\nNo2 =" + no2  
			+ "\nTotal =" + total +"]\n ---------------------------");
				   
			   Connection con = null;
			   
			   /*FormRowSet f = new FormRowSet();
				FormRow r1 = new FormRow();
				r1.put("field1",pic);
				r1.put("field2", no1);
				r1.put("field3", no2);
				r1.put("field4",total);
				f.add(r1);
				return f;*/
				
				try {
					// retrieve connection from the default datasource
					DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean("setupDataSource");
					con = ds.getConnection();
					
					
					if (!con.isClosed()) {
						
						//return storeData(element, rows, formData); 
							WorkflowManager workflowManager = (WorkflowManager) AppUtil.getApplicationContext().getBean("workflowManager");
							workflowManager.processStart("TestingLOC#latest#process1", null, null, pic,"#process.processId#", false);
					   
							String insertQuery = "INSERT INTO app_fd_testingloc (id,c_field1,c_field2,c_field3,c_field4) values (?, ?, ?, ?, ?)";
							PreparedStatement istmt = con.prepareStatement(insertQuery);
							istmt.setString(1, "#form.testinloc.id#" );
							istmt.setString(2, pic);
							istmt.setString(3, no1);
							istmt.setString(4, no2);
							istmt.setString(5, totalz);
							istmt.executeUpdate();
							
							
			 
					}
				} catch (Exception ex) {
					LogUtil.error("Sample app - StoreToMultipleSource form", ex, "Error storing using jdbc");
				} finally {
					try {
						if(con != null) {
							con.close();
							System.out.println("No connection ");
						}
					} catch(SQLException e) {}
				}
				
				
		}
	} catch (IOException e) {
	    e.printStackTrace();
	}
}

System.out.println("\n\n");
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");  
LocalDateTime now = LocalDateTime.now();  
System.out.println(dtf.format(now));  
System.out.println("Anda Telah Berjaya\n"); 

File latestCSVFile = getTheNewestFile("/Users/abdul_fattah/Documents/TestingLOC/", "csv");
System.out.println("latest file is --> " + latestCSVFile.getAbsolutePath());
readCSV(latestCSVFile.getAbsolutePath());