Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.io.BufferedWriter;
- import java.io.FileWriter;
- import java.io.IOException;
- import java.io.PrintWriter;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.text.DateFormat;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- public class DBCom {
- private String dbpath = "jdbc:postgresql://localhost:9505/postgres";
- private String user = "postgres";
- private String pass = "samurai87";
- private Connection con = null;
- public DBCom(){
- }
- /**
- * This method establishes a connection to the database specified at dbpath.
- *
- */
- public synchronized void openConnection(){
- try {
- } catch (Exception e) {
- System.out.println(e);
- }
- }
- /**
- * This method executes a SELECT statement and returns the ResultSet.
- * @throws SQLException
- */
- public synchronized ResultSet select(String statement) throws SQLException{
- ResultSet res = null;
- try{
- // openConnection(); //open
- Statement set = con.createStatement();
- res = set.executeQuery(statement);
- return res;
- }catch (Exception e) {
- System.out.println(e);
- }finally{
- // con.close(); //close
- }
- return null;
- }
- /**
- * This method takes a statement like INSERT, UPDATE or DELETE and executes them.
- * @param statement
- * @throws SQLException
- */
- public synchronized boolean update(String statement) throws SQLException{
- try{
- // openConnection(); //open
- Statement set = con.createStatement();
- set.executeUpdate(statement);
- // set.close();
- return true;
- }catch (Exception e) {
- return false;
- }finally{
- // con.close(); //close
- }
- }
- /**
- * Regnskapssystemet er ikke integrert med det programmet du arbeider med.
- * Dataene skal overføres dit via tekstfiler. Tekstfilen har fire kolonner: prosjekt_id, kunde, tekst og beløp, atskilt med semikolon.
- * For prosjektarbeid skal alle timekostnadene for denne måneden og det aktuelle prosjektet summeres til én linje med tekst “timer”,
- * for prosjektkostnader skal listen inneholde en linje pr linje i tabellen for denne måneden. Listen skal være sortert på kunde og prosj_id.
- * Programmet skal sette faktura_sendt lik dagens dato i de to tabellene.
- * @throws ClassNotFoundException
- * @throws SQLException
- */
- public void fakturer(int mnd, String name) throws ClassNotFoundException, SQLException{
- try {
- Class.forName("org.postgresql.Driver");
- con = DriverManager.getConnection(dbpath, user, pass);
- con.setAutoCommit(false);
- con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
- DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
- String datef = formatter.format(new Date());
- ResultSet res = select("select prosj_id, kunde, tekst, beløp, sum(timefaktor * ant_timer * timelønn) as beløp2" +
- " from prosjektkostnader k natural full join prosjektarbeid a left join prosjekt p using (prosj_id)" +
- " left join ansatt ans using (ans_id) where k.faktura_sendt is null and a.faktura_sendt is null" +
- " and (date_part('month', k.dato) = "+mnd+" or date_part('month', a.dato) = "+mnd+")" +
- " group by prosj_id, tekst, kunde, beløp order by kunde, prosj_id desc;");
- while(res.next()){
- int col1 = res.getInt(1);
- String col2 = res.getString(2);
- String col3 = res.getString(3);
- if(col3 == null){ //prosjektarbeid
- double col5 = res.getDouble(5);
- String line = col1+";"+col2+";timer;"+col5;
- lagrePåFil(line, name);
- //oppdater dato
- update("UPDATE prosjektarbeid SET faktura_sendt = '"+datef+"' where prosj_id = "+col1+";");
- }else{ // prosjektkostnad
- double col4 = res.getDouble(4);
- String line = col1+";"+col2+";"+col3+";"+col4;
- lagrePåFil(line, name);
- //oppdater dato
- update("UPDATE prosjektkostnader SET faktura_sendt = '"+datef+"' where prosj_id = "+col1+";");
- }
- }
- con.commit();
- con.close();
- res.close();
- } catch (SQLException e) {
- try {
- con.rollback();
- } catch (Exception e2) {
- // TODO: handle exception
- }
- e.printStackTrace();
- } finally{
- con.close();
- }
- }
- private synchronized void lagrePåFil(String line, String name) {
- try {
- PrintWriter skriver = new PrintWriter(new BufferedWriter(new FileWriter(name, true)));
- skriver.println(line);
- System.out.println("Lagret: " + line);
- skriver.close();
- } catch (IOException e) {
- System.out.println("Feil ved skriving fil: " + e);
- System.out.println("Teksten som skulle vært skrevet ut: " + line);
- e.printStackTrace();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement