// sketch that counts monthly the number of appearances  of word on the database.
// It saves the results on a text file for future use.
// The file is created on the sketch folder

import de.bezier.data.sql.*;

int mCount=0;
MySQL msql;
void setup()
{
  size(900,300);
  // connecting to the database
    // User Data
    String user     = "mat259";
    String pass     = "V1sual1zat1on";
    // database name
    String database = "spl_years";
    String TheQuery ="";
    String [] Freq0710 = new String[48]; // string array with the results of 12 months in 4 years
     msql = new MySQL( this, "tango.mat.ucsb.edu", database, user, pass );
     
     // this variable is the text to be searched.
     String TextToSearch ="2012";
     
     // Queering the number of appearances  of the word, month to month from 2007 to 2010.
     if ( msql.connect() )
     { 
       
       //  2007
        for(int m=0; m<12; m++){
          TheQuery="SELECT count(*) FROM transactions2007 WHERE month(ckinDateTime)=" + (m+1)+ " and title like '%" + TextToSearch + "%'";
          println(TheQuery);
          msql.query( TheQuery);
          msql.next();
          Freq0710[m]=Integer.toString(msql.getInt(1));
       }
       //2008
        for(int m=0; m<12; m++){
          TheQuery="SELECT count(*) FROM transactions2008 WHERE month(ckinDateTime)=" + (m+1)+ " and title like '%" + TextToSearch + "%'";
          println(TheQuery);
          msql.query( TheQuery);
          msql.next();
          Freq0710[m+12]=Integer.toString(msql.getInt(1));
       }
       // 2009
        for(int m=0; m<12; m++){
          TheQuery="SELECT count(*) FROM transactions2009 WHERE month(ckinDateTime)=" + (m+1)+ " and title like '%" + TextToSearch + "%'";
          println(TheQuery);
          msql.query( TheQuery);
          msql.next();
          Freq0710[m+24]=Integer.toString(msql.getInt(1));
       }
       
        for(int m=0; m<12; m++){
          TheQuery="SELECT count(*) FROM transactions2010 WHERE month(ckinDateTime)=" + (m+1)+ " and title like '%" + TextToSearch + "%'";
         println(TheQuery);
          msql.query( TheQuery);
          msql.next();
          Freq0710[m+36]=Integer.toString(msql.getInt(1));
       }

      // giving a name to the output text file, Freq0710 is an array of strings.
      saveStrings("2012.txt", Freq0710);



     
     }
    else
    {
      println(" connection failed !");
    }
 
 }
void draw(){

// nothing to draw
}