package wbreader;

import java.io.BufferedWriter;
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.util.logging.Level;
import java.util.logging.Logger;
import java.io.File;
import java.io.FileWriter;

/**
 *
 * @author sdti
 */
public class WBReader {

    public void connectToAndQueryDatabase(String username, String password, String dirPath){
        BufferedWriter bw = null;
        String filePath = "";
        ResultSet rset = null;
        Statement stmt = null;
        String url = "jdbc:oracle:thin:@//192.168.1.234:1521/maxrb";
        Connection con = null;

        try {
            Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection(url, username, password);

            stmt = con.createStatement();
            String qry = "select "
                    + "assetnum, wonum, round((actfinish-actstart)*24, 2) duration "
                    + "from "
                    + "workorder "
                    + "where "
                    + "status in ('COMP', 'INPRG') "
                    + "and istask = '0' "
                    + "order by "
                    + "assetnum";
            rset = stmt.executeQuery(qry);
            String str = "";
            String fileName = "";
            String file1 = "";

            while (rset.next()){
                file1 = rset.getString(1) + ".txt";
                if (!file1.equals(fileName)){
                    try{ bw.close();} catch (Exception e){e.toString();}
                    fileName = file1;
                    filePath = dirPath + "/" + file1;
                    File file = new File(filePath);
                    bw = new BufferedWriter(new FileWriter(file, false));
                }
                float fl;
                fl = rset.getFloat(3);
                str = fl < 0.0001 ? "" : String.valueOf(fl);
                if (!str.equalsIgnoreCase("")){
                    bw.write(str);
                    bw.write("\r\n");
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                bw.close();
                rset.close();
                stmt.close();
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        WBReader wbReader = new WBReader();
        String address = "D:\\Root\\Temp\\Test";
        String backslash= System.getProperty("file.separator");
        address= address.replace(backslash,"/");
        System.out.println(address);
        try{
            wbReader.connectToAndQueryDatabase("maximo", "maximo", address);
        }catch(Exception e){
            e.toString();
        }
    }

}