//Connecting to DB2 database while reading properties from file
import java.beans.Statement;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;


public class ConnectingDb {


//Establishing connection
Statement statement=null;
//Connection connection = getConnection();
public Connection  getConnection() throws ClassNotFoundException, SQLException
{
Properties prop = new Properties();
try{
prop.load(new FileInputStream("./conf/Db.properties"));
}
catch (Exception e){
}
Class.forName (prop.getProperty("DriverName"));
Connection  connection = DriverManager.getConnection(prop.getProperty("ConnectionURL"),prop.getProperty("Username"),prop.getProperty("Password"));
System. out .println( "From DAO, connection obtained " );
return connection;
//statement = connection.createStatement();
}

}






//Inserting the filtered file content into DB2 and then manipulating the data and extracting the result to csv file


//import java.beans.Statement;
import java.io.*;
import java.sql.Connection;
import java.sql.Statement;
import java.util.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Tocsv{
public static void main(String[]args) throws Exception{
//Scanner input=new Scanner(System.in);
//System.out.print("Enter word to find: ");
String word="milliseconds";
File f = new File("./Read.txt");
BufferedReader freader = new BufferedReader(new FileReader(f));
String s;
while ((s = freader.readLine()) != null) {
if(s.contains("milliseconds")& s.contains("Finished"))
{
String[] st = s.split(" ");
System.out.println(st.length);

String time = st[0];
String username = st[8];
String tnsfilename = st[11];
String responsetime = st[21];
String units = st[22];


File f1 = new File(tnsfilename);
//System.out.println("file--->" + f1.getName());      
StringTokenizer stringTokenizer = new StringTokenizer(f1.getName(),".");
//System.out.println(stringTokenizer.nextElement());
String tnfile =stringTokenizer.nextElement().toString();
//String tnfile = stringTokenizer.nextElement();


//writing to file
if (units.equals(word)& st[5]!="Duration")
{
File file = new File("D:/LoanIQENV/JavaClient/Tool/Write.txt");

FileWriter fstream = new FileWriter(file,true);
BufferedWriter out = new BufferedWriter(fstream);
//db connection
ConnectingDb db=new ConnectingDb();
Connection con= db.getConnection();

//Creating our java jdbc statement
Statement statement = con.createStatement();
String SQL="INSERT INTO LOADTEST.RESPONSETIME(TIME_STP,USERNAME,FILE_NM,RSP_TIME) VALUES ('"+time+"','"+username+"','"+tnfile+"',"+responsetime+")";
//String SQL ="INSERT INTO LOADTEST.RESPONSETIME2(TIME_STP,USERNAME,FILE_NM,RSP_TIME) VALUES(111,'LIQ','dfadsa',99)";
statement.executeUpdate(SQL);
out.write(time+" "+username+" "+tnfile+" "+responsetime+" "+units);
out.newLine();
out.close();
con.close();
statement.close();
}

}
}
ConnectingDb db=new ConnectingDb();
Connection con=db.getConnection();
//selecting the values from db
String query = "select FILE_NM, AVG(RSP_TIME) From LOADTEST.RESPONSETIME Group By FILE_NM";
Statement statement =con.createStatement();
ResultSet rpt=statement.executeQuery(query);
//PrintStream fileOut = new FileOutputStream( path );
PrintStream fileOut = new PrintStream(new FileOutputStream("d:/TestdataCSV.csv"));
// FileOutputStream fileOut = new FileOutputStream("d:/Testdata.xlsx");
int cols = rpt.getMetaData().getColumnCount();
while( rpt.next() ) {
for( int i = 1; i <= cols ; i++ ) {
fileOut.printf("%s,", rpt.getObject( i ) );
}          
fileOut.println();
}

//wb.write(fileOut);
fileOut.close();
con.close();
statement.close();
}


}