package sc.diaryFinger;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;


/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args )
    {
//        System.out.println( "Hello World!" );
        
        
        String connection = "jdbc:sqlserver://10.1.2.9:1443;integratedSecurity=true;databaseName=Pm2014_SU;user=sa;password=";
        List<ArrayList<String>> data = new ArrayList();
        
       
//        String formatDate = "dd-MM-yyyy_HHmm";
//        String formatQuery = "MM-dd-yyyy";
//        String checkDay = "DAY(GETDATE())";
//        String checkMonth = "MONTH(GETDATE())";
//        String checkYear = "YEAR(GETDATE())";
        String checkDay = "dd";
        String checkMonth = "MM";
        String checkYear = "yyyy";
        switch (args.length) {
			case 1:
				checkDay = args[0];
//				formatDate = checkDay+"-MM-yyyy_HHmm";
//				formatQuery = "MM-dd-yyyy";
				break;
			case 2:
				checkDay = args[0];
				checkMonth = args[1];
//				formatDate = checkDay+"-"+checkMonth+"-yyyy_HHmm";
//				formatQuery = "MM-dd-yyyy";
				break;
			case 3:
				checkDay = args[0];
				checkMonth = args[1];
				checkYear = args[2];
//				formatDate = checkDay+"-"+checkMonth+"-"+checkYear+"_HHmm";
//				formatQuery = "MM-dd-yyyy";
				break;
			}
        
        String formatDate = checkDay+"-"+checkMonth+"-"+checkYear+"_HHmm";
        String formatQuery = checkMonth+"-"+checkDay+"-"+checkYear;
        
        Date date = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat(formatDate);
        SimpleDateFormat formatterQuery = new SimpleDateFormat(formatQuery);
        String fileName = "c:/empeoTime/"+formatter.format(date)+".csv";
        System.out.println(fileName);
        
        try {
        	 //readconfig
	        connection = readconfig();
	        
	        //connect db and query
//	        data = connectFinger(connection,checkDay,checkMonth,checkYear);
	        data = connectFinger(connection,formatterQuery.format(date));
	        
	        //write file
	        writeFile(data,fileName);
        
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
    }
    
//    private static ArrayList<ArrayList<String>> connectFinger(String connection,String checkDay,String checkMonth,String checkYear)
    private static ArrayList<ArrayList<String>> connectFinger(String connection,String formatQuery)
    {
    	System.out.println("Try Database Connected.");
    	ArrayList<ArrayList<String>> rows = new ArrayList<ArrayList<String>>();
		Connection connect = null;
		Statement s = null;
		
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			connect =  DriverManager.getConnection(connection);
//			connect =  DriverManager.getConnection("jdbc:sqlserver://localhost:1443;integratedSecurity=true;databaseName=Pm2014_SU;user=sa;password=");
			
			if(connect != null){
				System.out.println("Database Connected.");
				
				s = connect.createStatement();
				
				//query 
				String sql = "select u.SSN,convert(varchar, CHECKTIME, 103) + ' ' + convert(varchar(5),CHECKTIME,14) as time ";
				sql += "from USERINFO u "; 
				sql += "join CHECKINOUT c on c.Badgenumber = u.Badgenumber ";				
				sql += "where CHECKTIME between DATEADD(day, -10, convert(datetime, '"+formatQuery+"', 121)) and DATEADD(day, 1, convert(datetime, '"+formatQuery+"', 121))";
//				sql += "where "+checkDay+" = DAY(CHECKTIME) ";
//				sql += "AND "+checkMonth+" = MONTH(CHECKTIME) ";
//				sql += "AND "+checkYear+" = YEAR(CHECKTIME) ";

				sql += "AND (u.SSN LIKE 'TSD%' or u.SSN LIKE 'SC%' ) ";

				sql += "order by CHECKTIME asc ";
				
				System.out.println(sql);
				ResultSet rec = s.executeQuery(sql);
				
				
				while((rec!=null) && (rec.next()))
	            {
					System.out.println(rec.getString("SSN")+ " : "+rec.getString("time"));
					ArrayList<String> rowData = new ArrayList<String>();
					rowData.add(rec.getString("SSN"));
					rowData.add(rec.getString("time"));
					rows.add(rowData);
				}
				
			
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			connect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rows;
    }

    private static void writeFile(List<ArrayList<String>> data,String fileName) throws IOException {
    	// Our example data
//    	List<List<String>> rows = Arrays.asList(
//    	    Arrays.asList("Jean", "Java"),
//    	    Arrays.asList("David","Python"),
//    	    Arrays.asList("Scott", "Node.js")
//    	);

    	FileWriter csvWriter = new FileWriter(fileName);
//    	csvWriter.append("Name");
//    	csvWriter.append(",");
//    	csvWriter.append("Role");
//    	csvWriter.append(",");
//    	csvWriter.append("Topic");
//    	csvWriter.append("\n");

    	for (List<String> rowData : data) {
    	    csvWriter.append(String.join(",", rowData));
    	    csvWriter.append("\n");
    	}

    	csvWriter.flush();
    	csvWriter.close();

	}

    private static String readconfig()
    {
    	String connection = "";
    	File file = new File("conf/config");
//    	File file = new File("conf/config");
    	try {
    		BufferedReader br = new BufferedReader(new FileReader(file));
    		String line;
    		//cross 1 line
    		br.readLine();
    		while ((line = br.readLine()) != null) {
    			if(line.contains("connection"))
    				connection = (line.substring(line.indexOf("\"")+1, line.lastIndexOf("\"")));
    			
    		}
    		br.close();
    	} catch (IOException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    	
    	return connection;
    }
    
}