// sketch that shows how to use the mysql library to make a query to the database, and count the number of dewey and non dewey items on an specific day.  
import de.bezier.data.sql.*;

int mCount=0;
MySQL msql;
int [][] aDay = new int[24][2];
float plotX1, plotY1;
float plotX2, plotY2;
float labelX, labelY;
String [] Hourslabel = {"00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23"};
PFont font;
void setup()
{
  size(900,300);

  font = loadFont("UniversLTStd-Cn-14.vlw"); 
 // connecting to the database
    // User data
    String user     = "mat259";
    String pass     = "V1sual1zat1on";
    // Database name
    String database = "spl_years";
    String TheQuery ="";
    // New mysql object receives  server, database and user data
     msql = new MySQL( this, "tango.mat.ucsb.edu", database, user, pass );
     
     if ( msql.connect() )
     { 
    
        // counting the number of non dewey Items on each hour of march 19 2009
         int m=0;
         for (int k =0 ; k < 24; k++){
          TheQuery="SELECT count(*) FROM transactions2009 WHERE (deweyClass is NULL) and (hour(ckinDateTime)=" + k +")";
          TheQuery+= " and (day(ckinDateTime)= 19) and (month(ckinDateTime)=3)" ;
          msql.query( TheQuery);
          msql.next();
          aDay[k][0] = msql.getInt(1);
          println(k + "    " +  msql.getInt(1));
       }
     
         // Now counting the number of  dewey Items  each hour of the same day ->march 19 2009
         for (int k =0 ; k < 24; k++){

           TheQuery = "SELECT count(*) FROM transactions2009 WHERE (NOT(deweyClass is NULL)) and (hour(ckinDateTime)=" + k +")";
           TheQuery+= " and (day(ckinDateTime)=19) and (month(ckinDateTime)=3)" ;         
           msql.query( TheQuery);
           msql.next();
           aDay[k][1] = msql.getInt(1);
          println(k + "    " +  msql.getInt(1));
       }  
          
            
             
    
     }
    else
    {
        println(" connection failed !");
    }
     
 
       
  plotX1 = 30; 
  plotX2 = width - 30;
  labelX = 20;
  plotY1 = 30;
  plotY2 = height - 40;
  labelY = height - 20;


}

// plot functions are the same ones used on lab 1.06

void draw(){
  drawBar();
 // drawSpinePlot();
 // drawGrayScale();
}


void drawBar(){

  float  Hside;
  float Vside;
  int MaxScale =0;
  background(230); 
  strokeWeight(1); 
  Hside = (plotX2-plotX1)/24;
  Vside = (plotY2-plotY1);

  // finding the maximun to adjust the scale:
  for (int k=0;k<24;k++){
    if ((aDay[k][0] +aDay[k][1]) >  MaxScale){
      MaxScale = (aDay[k][0] +aDay[k][1]);
    }
  }


  fill(160);
  noStroke();
  //The total 
  for (int h =0;h<24;h++){
   float NewHeigh = map((aDay[h][0]+aDay[h][1]),0,MaxScale,0,Vside);
      rect(plotX1+h*Hside, plotY2-NewHeigh,floor(Hside*.95),NewHeigh);
  }

  // The non Dewey

  fill(200,0,0);
  noStroke();
  for (int h =0;h<24;h++){
   float NewHeigh = map(aDay[h][0] ,0,MaxScale,0,Vside);
   rect(plotX1+h*Hside, plotY2-NewHeigh,floor(Hside*.95),NewHeigh);
  }
  // border line
   noFill();
   stroke(0);
   for (int h =0;h<24;h++){
    float NewHeigh = map((aDay[h][0]+aDay[h][1]),0,MaxScale,0,Vside);
       rect(plotX1+h*Hside, plotY2-NewHeigh,floor(Hside*.95),NewHeigh);
   }

  // Labeling
  textFont(font); 
  fill(128);
 
  
  for (int h =0;h<24;h++){
     textAlign(CENTER,CENTER);
     text(Hourslabel[h],plotX1 + Hside*(h+.5),plotY2+20);
   }

}

void drawSpinePlot(){

  float  Hall;
  float Vside;
  float TotalSum;
  float   WithAcum;
  background(230); 
  strokeWeight(1); 
  Hall = (plotX2-plotX1);
  Vside = (plotY2-plotY1);
 // finding the total sum to adjust the scale:
  TotalSum=0;
  for (int k=0;k<24;k++){
     TotalSum += (aDay[k][0] +aDay[k][1]);
  }


  fill(160);
  noStroke();
  //The total 
  WithAcum=plotX1;
  for (int h =0;h<24;h++){
   float NewWidth = map((aDay[h][0]+aDay[h][1]),0,TotalSum,0,Hall);
      rect(WithAcum, plotY1,floor(NewWidth*.95),Vside);
      WithAcum += NewWidth;
  }

  // The non Dewey

  fill(200,0,0);
  noStroke();
  WithAcum=plotX1;
  for (int h =0;h<24;h++){
 float NewWidth = map((aDay[h][0]+aDay[h][1]),0,TotalSum,0,Hall);
 float NewDeweyHeight =map(aDay[h][0],0,(aDay[h][0]+aDay[h][1]),0,Vside);
   rect(WithAcum, plotY2-NewDeweyHeight,floor(NewWidth*.95),NewDeweyHeight);
   WithAcum += NewWidth;
  }
  // border line
   noFill();
   stroke(0);
   WithAcum=plotX1;
   for (int h =0;h<24;h++){
  float NewWidth = map((aDay[h][0]+aDay[h][1]),0,TotalSum,0,Hall);
      rect(WithAcum, plotY1,floor(NewWidth*.95),Vside);
      WithAcum += NewWidth; 
   }

}

void drawGrayScale(){
  
  float  Hside;
  float Vside;
  int MaxD =0;
  int MaxND =0;
  int MaxScale=0;
  background(230); 
  strokeWeight(1); 
  Hside = (plotX2-plotX1)/24;
  Vside = (plotY2-plotY1)/2;

  for (int k=0;k<24;k++){
    if ((aDay[k][0] +aDay[k][1]) >  MaxScale){
      MaxScale = (aDay[k][0] +aDay[k][1]);
    }
  }
   
 stroke(0);
  for (int h =0;h<24;h++){
     float NewFill = map(aDay[h][1],0,MaxScale,0,255);
     fill(NewFill);
     rect(plotX1+h*Hside, plotY1,floor(Hside*.95),floor(Vside*.95));
     NewFill = map(aDay[h][0],0,MaxScale,0,255);
     rect(plotX1+h*Hside, plotY1+Vside,floor(Hside*.95),Vside);
  } 
 
   // Labeling
  textFont(font); 
  fill(128);
 
  
  for (int h =0;h<24;h++){
     textAlign(CENTER,CENTER);
     text(Hourslabel[h],plotX1 + Hside*(h+.5),plotY2+20);
   }

}