import java.sql.*;


// SQL VARS
private Connection con;
private static final String DBNAME = "aoldata.querydata";
private static final String URL = "jdbc:oracle:thin:@dbl43.beuth-hochschule.de:1521:oracle";
private static final String USER = "s860629";
private static final String PASSWORD = "beuthvogel";

//Padding VARS
int padd = 60,padd_left = 120,padd_top = 120;
int barGap = 4;

//color VARS
int colors[][] = {{51,102,204},{220,57,18}};

void setup(){
  size(1080, 640);
  background(255);
  drawLeg();
  connect();
  drawBaseLine();


  //disconnect();
  save("u1.jpg");
}

void drawLeg(){
  stroke(128);
  strokeWeight(1);
  line((width-padd_left-padd)/2+padd_left,padd_top-padd/2,(width-padd_left-padd)/2+padd_left, height-padd/2);
  line(width-padd,padd_top-padd/2,width-padd, height-padd/2);

  //title
  fill(0);
  textSize(22);
  textAlign(CENTER);
  text("Titel dieses Diagramms",width/2,padd_top/2);
}

void drawBaseLine(){
  stroke(0);
  strokeWeight(2);
  line(padd_left,padd_top-padd/2,padd_left, height-padd/2);
}

void drawBars(int[] bars,String[] headers){

  float h = (height - (padd+padd_left)) / bars.length;

  int maxNum = 0;

  //get highest number
  for(int i = 0; i < bars.length; i++){
    if(bars[i]>maxNum) maxNum = bars[i];
  }

  //Draw Bars
  noStroke();
  for(int i = 0; i < bars.length; i++){
    if(i%2==0)  fill(colors[0][0],colors[0][1],colors[0][2]);
    else        fill(colors[1][0],colors[1][1],colors[1][2]);

    float num = map(bars[i], 0, maxNum, 0, height-(padd*2));

    rect(padd_left,padd_top+(h*i)+barGap/2,num,h-barGap/2,0,5,5,0);

    textSize(16);
    fill(0);
    textAlign(LEFT);
    text(""+bars[i],padd_left+num+5,(h*i)+padd_top+h/2);
    textAlign(CENTER);
    text(headers[i],padd_left/2,(h*i)+padd_top+h/2);

    System.out.println(bars[i]);
  }
}




public void connect() {
  try {
    Class.forName("oracle.jdbc.driver.OracleDriver");

    //öffnet Verbindung zur Datenbank
    con=DriverManager.getConnection(URL, USER, PASSWORD);

    Statement stmt=con.createStatement();

    String anfrage = "Select"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (1) THEN 1 ELSE 0 END ) as Montag,"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (2) THEN 1 ELSE 0 END ) as Dienstag,"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (3) THEN 1 ELSE 0 END ) as Mittwoch,"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (4) THEN 1 ELSE 0 END ) as Donnerstag,"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (5) THEN 1 ELSE 0 END ) as Freitag,"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (6) THEN 1 ELSE 0 END ) as Samstag,"+
    "sum(case when QUERY like '%cooking recipe%' or CLICKURL like '%recipe%' and MOD(TO_CHAR(QUERYTIME, 'J'), 7) + 1 IN (7) THEN 1 ELSE 0 END ) as Sonntag"+
    "from aoldata.querydata"+DBNAME;

    //Stellt Suchanfrage
    ResultSet rs=stmt.executeQuery(anfrage);


    ResultSetMetaData rsmd = rs.getMetaData();


    int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6), rs.getInt(7)};
    String[] header = {rsmd.getColumnName(1), rsmd.getColumnName(2), rsmd.getColumnName(3), rsmd.getColumnName(4), rsmd.getColumnName(5), rsmd.getColumnName(6), rsmd.getColumnName(7)};

    //int[] arr = {2342, 8923, 2342, 5342, 1337, 1323, 3131};
    //String[] header = {"Montag","Dienstag","Mittwoch","Donnerstag","Freitag","Samstag","Sonntag"};

    drawBars(arr,header);
  }
  catch(Exception e) {
    System.out.println(e);
  }
}

//Schließt verbindung zur Datenbank
public void disconnect() {
  try {
    con.close();
  }
  catch(Exception e) {
    System.out.println(e);
  }
}