SMx Stats - Miscellaneous

shwaip · May 2005

I am going home this Thursday - meaning my server will be down while I drive back to Pittsburgh. Additional downtime will come from my needing to futz with the router at home, and to change the free dns to my home IP.

Cliff's notes:
- The server will be down
- It will start updating @ 11:55 Eastern, rather than central
- The server will be hosted on a 512/128 (?) ADSL, rather than a fractional T3. Don't sit there and refresh just for poops and giggles. Please.
- I have nothing else, but you really can't have a bulleted list with only 3 bullets.

Also, I've been thinking of adding another table with the following info:
Total Points
Total WUs
Avg points per day
Avg points per week
Date of last WU turned in

Any additional suggestions are cool too.

And I made the top banner a link to www.short-media.com . I wasn't sure where the best place to point it would be.

Linc · May 2005

What language are you coding in? Happen to know Javascript and PHP?

shwaip · May 2005

I know very little of both. I have a java program for getting the text file, parsing it, storing info in the dbase, and generating the tab delimited files that the grid script @ www.activewidgets.com can read in.

no making fun of my horrible code :P

	import java.sql.*;
	import java.io.*;
	import java.net.URL;
	import java.util.GregorianCalendar;


public class SMxStats {
	
	private int numBoxes = 23;
    private Connection con;
    private Statement s;

	public SMxStats(){
		boolean worked;
	    try {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		con = DriverManager.getConnection("*REMOVED*","*REMOVED*","*REMOVED*");
		s = con.createStatement();
		this.fetchStats();
		worked = this.sortStats();
		this.updateDBs();
		this.generateThisWeekInfo();
		this.generateWeekStats();
		this.generateWeekInfo();
		if(!worked){
			this.emptyUpdate();
		}
//		System.out.println(this.getNumBoxesFromDBase());
		s.close();
		con.close();

	    } catch (Exception e){
		e.printStackTrace();
	    }
	}
	
	public void generateWeekInfo(){
		GregorianCalendar today = new GregorianCalendar();
		long day = 1000*60*60*25;
		long week = day*7;
		String [] weeks = new String [10];
		for(int i=0; i<10; i++){
			weeks[i] = this.getWeek(today.get(GregorianCalendar.WEEK_OF_YEAR),today.get(GregorianCalendar.YEAR));
			today.setTimeInMillis(today.getTimeInMillis() - week);
	//		System.out.println(weeks[i]);
		}
		ResultSet rs;
		String SQL;
		int [][] points = new int[this.numBoxes][10];
		int [][] wus = new int[this.numBoxes][10];
		for(int i=0; i<10; i++){
			SQL = "SELECT * FROM Point_History WHERE week = '" + weeks[i] + "'";
			try {
				rs = s.executeQuery(SQL);
				if(!rs.next()){
					break;
				} else {
					for(int j=1; j<=this.numBoxes; j++){
						points[j-1][i] = rs.getInt("_" + j);
					}
					this.writeWeekPoints(points);
				}
				
			} catch (SQLException e){
				e.printStackTrace();
			}
		}
		for(int i=0; i<10; i++){
			SQL = "SELECT * FROM WU_History WHERE week = '" + weeks[i] + "'";
			try {
				rs = s.executeQuery(SQL);
				if(!rs.next()){
					break;
				} else {
					for(int j=1; j<=this.numBoxes; j++){
						wus[j-1][i] = rs.getInt("_" + j);
					}
					this.writeWeekWUs(wus);
				}
				
			} catch (SQLException e){
				e.printStackTrace();
			}
		}

	}

	public void writeWeekPoints(int [][] points){
		try {
			String fileName = "/SMx/weekpoints.txt";
			BufferedWriter out = new BufferedWriter(new FileWriter(new File(fileName)));
			int [] total = new int[10];
			for(int i=0; i<this.numBoxes; i++){
				out.write((i+1) + "\t");
				for(int j=0; j<10; j++){
					total[j] += points[i][j];
					out.write(points[i][j] + "");
					if(j!= 9){
						out.write('\t');
					}
				}
				out.newLine();
			}
			out.write("Total\t");
			for(int i=0; i<10; i++){
				out.write(total[i] + "");
				if(i!=9){
					out.write('\t');
				}
			}
			out.newLine();
			out.close();
		} catch(IOException e){
			e.printStackTrace();
		}
		
	}
	
	public void writeWeekWUs(int [][] wus){
		try {
			String fileName = "/SMx/weekwus.txt";
			BufferedWriter out = new BufferedWriter(new FileWriter(new File(fileName)));
			int total[] =new int[10];
			for(int i=0; i<this.numBoxes; i++){
				out.write((i+1) + "\t");
				for(int j=0; j<10; j++){
					total[j] += wus[i][j];
					out.write(wus[i][j] + "");
					if(j!= 9){
						out.write('\t');
					}
				}
				out.newLine();
			}
			out.write("Total\t");
			for(int i=0; i<10; i++){
				out.write(total[i] + "");
				if(i!=9){
					out.write('\t');
				}
			}
			out.newLine();
			out.close();
		} catch(IOException e){
			e.printStackTrace();
		}
		
	}

	public String getWeek(int week, int year){
		return "_" + week + "_" + year;
	}
	
	public void generateWeekStats(){
		int [][] points = new int[this.numBoxes][2];
		int [][] wus = new int[this.numBoxes][2];
		GregorianCalendar today = new GregorianCalendar();
		int day = today.get(GregorianCalendar.DAY_OF_WEEK) -1;
		String dayString = "_" + day; 
		
		try {
			String SQL;

			SQL = "SELECT _6 FROM Old_Points";
			ResultSet rs = s.executeQuery(SQL);
			int i=0;
			while(rs.next()){
				points[i][0] = rs.getInt("_6");
				i++;
			}
			rs.close();

			SQL = "SELECT " + dayString +" FROM Week_Points";
			rs = s.executeQuery(SQL);
			i=0;
			while(rs.next()){
				points[i][1] = rs.getInt(dayString);
				i++;
			}
			rs.close();

			SQL = "SELECT _6 FROM Old_WUs";
			rs = s.executeQuery(SQL);
			i=0;
			while(rs.next()){
				wus[i][0] = rs.getInt("_6");
				i++;
			}
			rs.close();

			SQL = "SELECT " + dayString + " FROM Week_WUs";
			rs = s.executeQuery(SQL);
			i=0;
			while(rs.next()){
				wus[i][1] = rs.getInt(dayString);
				i++;
			}
			rs.close();
			for(i=1; i<=this.numBoxes; i++){
				SQL = "SELECT _" + i + " FROM Point_History";
				try {
					rs = s.executeQuery(SQL);
					rs.close();
				} catch (SQLException e) {
					SQL = "ALTER TABLE Point_History ADD _" + i + " int";
					s.execute(SQL);
					SQL = "ALTER TABLE WU_History ADD _" + i + " int";
					s.execute(SQL);
				}
			}
			SQL = "SELECT * FROM Point_History WHERE week = '" + this.getWeek() + "'";
			try {
				rs = s.executeQuery(SQL);
				if(!rs.next()){
					SQL = "INSERT INTO Point_History (week) VALUES ('" + this.getWeek() + "')";
					s.execute(SQL);
				}
				rs.close();
			} catch (SQLException e){
				e.printStackTrace();
			}

			SQL = "SELECT * FROM WU_History WHERE week = '" + this.getWeek() + "'";
			try {
				rs = s.executeQuery(SQL);
				if(!rs.next()){
					SQL = "INSERT INTO WU_History (week) VALUES ('" + this.getWeek() + "')";
					s.execute(SQL);
				}
				rs.close();
			} catch (SQLException e){
				e.printStackTrace();
			}
			
			SQL = "UPDATE Point_History SET ";
			for(i=1; i<=this.numBoxes; i++){
				String temp = "_" + i + " = '" + (points[i-1][1] - points[i-1][0]) + "'";
				if( i!= this.numBoxes){
					temp+=", ";
				}
				SQL += temp;
			}
			SQL += " WHERE week = '" + this.getWeek() + "'";
			s.executeUpdate(SQL);
			SQL = "UPDATE WU_History SET ";
			for(i=1; i<=this.numBoxes; i++){
				String temp = "_" + i + " = '" + (wus[i-1][1] - wus[i-1][0]) + "'";
				if( i!= this.numBoxes){
					temp+=", ";
				}
				SQL += temp;
			}
			SQL += " WHERE week = '" + this.getWeek() + "'";
			s.executeUpdate(SQL);

		} catch (Exception e){
			e.printStackTrace();
		}
	}
	
	public String getWeek(){
		GregorianCalendar today = new GregorianCalendar();
		int week = today.get(GregorianCalendar.WEEK_OF_YEAR);
		int year = today.get(GregorianCalendar.YEAR);
		return "_" + week + "_" + year;
	}
	
	public void generateThisWeekInfo(){
		int [][] points = new int [this.numBoxes][15];
		int [][] WUs = new int [this.numBoxes][15];
		try {
			String SQL;
			SQL = "SELECT * FROM Old_Points";
			ResultSet rs = s.executeQuery(SQL);
			int box =0;
			while(rs.next()){
				points[box][0] = rs.getInt("zero");
				for(int i=1; i<8; i++){
					points[box][i] = rs.getInt("_" + (i-1));
				}
				box++;
			}
			rs.close();
			SQL = "SELECT * FROM Week_Points";
			rs = s.executeQuery(SQL);
			box =0;
			while(rs.next()){
				for(int i=8; i<15; i++){
					points[box][i] = rs.getInt("_" + (i-8));
				}
				box++;
			}
			rs.close();
			SQL = "SELECT * FROM Old_WUs";
			rs = s.executeQuery(SQL);
			box = 0;
			while(rs.next()){
				WUs[box][0] = rs.getInt("zero");
				for(int i=1; i<8; i++){
					WUs[box][i] = rs.getInt("_" + (i-1));
				}
				box++;
			}
			rs.close();
			SQL = "SELECT * FROM Week_WUs";
			rs = s.executeQuery(SQL);
			box =0;
			while(rs.next()){
				for(int i=8; i<15; i++){
					WUs[box][i] = rs.getInt("_" + (i-8));
				}
				box++;
			}
			rs.close();
		} catch (Exception e){
			e.printStackTrace();
		}
		int temp;
		try {
			BufferedWriter out = new BufferedWriter(new FileWriter(new File("/SMx/pointOutput.txt")));
//			BufferedWriter out = new BufferedWriter(new FileWriter(new File("C:\\point.txt")));
			String str;
			int [] total = new int[14];
			for(int i=0; i<this.numBoxes; i++){
				str = (i+1) + "\t";
				for(int j=1; j<15; j++){
				    temp = points[i][j] - points[i][j-1];
					if (temp < 0){
						temp = 0;
					}
					str = str + (temp);
					if(j != 14){
						str = str + "\t";

					}
					total[j-1]+= temp;
				}
				out.write(str);
				out.newLine();
			}
			out.write("Total\t");
			for(int i=0; i<14; i++){
			    out.write(total[i] + "");
			    if(i!=13){
				out.write('\t');
			    }
			}
			out.newLine();
			out.close();
		} catch(IOException e){
			e.printStackTrace();
		}
		try {
			BufferedWriter out = new BufferedWriter(new FileWriter(new File("/SMx/WUOutput.txt")));
//			BufferedWriter out = new BufferedWriter(new FileWriter(new File("C:\\wu.txt")));	
			String str;
			int [] total = new int [14];
			for(int i=0; i<this.numBoxes; i++){
				str = (i+1) + "\t";
				
				for(int j=1; j<15; j++){
					temp = WUs[i][j] - WUs[i][j-1];
					if (temp < 0){
						temp = 0;
					}
					str = str + (temp);
					if(j != 14){
						str = str + "\t";

					}
					total[j-1]+= temp;
				}
				out.write(str);
				out.newLine();
			}
			out.write("Total\t");
                        for(int i=0; i<14; i++){
                            out.write(total[i] + "");
                            if(i!=13){
                                out.write('\t');
                            }
                        }
                        out.newLine();
			out.close();
		} catch(IOException e){
			e.printStackTrace();
		}

	}
	
	private void shift(){
		String SQL;
		ResultSet rs;
		try {
			int [] old_points = new int [this.numBoxes];
			int [] old_WUs = new int [this.numBoxes];
			SQL = "SELECT _6 FROM Old_Points";
			rs = s.executeQuery(SQL);
			int index =0;
			while(rs.next()){
				old_points[index] = rs.getInt("_6");
				index++;
			}
			index = 0;
			rs.close();
			SQL = "SELECT _6 FROM Old_WUs";
			rs = s.executeQuery(SQL);
			while(rs.next()){
				old_WUs[index] = rs.getInt("_6");
				index++;
			}			
			rs.close();
			//reset the data
			SQL = "TRUNCATE TABLE Old_Points";
			s.execute(SQL);
			SQL = "TRUNCATE TABLE Old_WUs";
			s.execute(SQL);
			SQL = "INSERT INTO Old_Points (BoxNumber,_0,_1,_2,_3,_4,_5,_6) SELECT * FROM Week_Points";
			s.execute(SQL);
			SQL = "INSERT INTO Old_WUs (BoxNumber,_0,_1,_2,_3,_4,_5,_6) SELECT * FROM Week_WUs";
			s.execute(SQL);
			SQL = "TRUNCATE TABLE Week_Points";
			s.execute(SQL);
			SQL = "TRUNCATE TABLE Week_WUs";
			s.execute(SQL);
			for(index=0; index<old_points.length;index++){
				SQL = "UPDATE Old_Points SET zero = '" + old_points[index] +"' WHERE BoxNumber = '" + (index + 1)+"'";
				s.executeUpdate(SQL);
			}
			for(index=0; index<old_WUs.length;index++){
				SQL = "UPDATE Old_WUs SET zero = '" + old_WUs[index] +"' WHERE BoxNumber = '" + (index + 1)+ "'";
				s.executeUpdate(SQL);
			}

		} catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	public void updateDBs(){
		File sortedStats = new File("/usr/tmp/sorted.txt");
		try {
			BufferedReader in = new BufferedReader(new FileReader(sortedStats));
			String str;
			
			int today = new GregorianCalendar().get(GregorianCalendar.DAY_OF_WEEK);
			String dayColumn = "_" + (today - 1);
			String SQL;
			String [] data;
			ResultSet rs;

			
			if(today == 1){
				this.shift();
			}

			
			int box = 1;
			while((str = in.readLine()) != null){
				data = this.parseStats(str);
				SQL = "SELECT * FROM Week_Points WHERE BoxNumber = '" + box + "'";
				rs = s.executeQuery(SQL);
				if(rs.next()){
					rs.close();
					SQL = "UPDATE Week_Points SET " + dayColumn + " = '" + data[0] + "' WHERE BoxNumber = '" + box + "'";
					s.executeUpdate(SQL);
					SQL = "UPDATE Week_WUs SET " + dayColumn + " = '" + data[1] + "' WHERE BoxNumber = '" + box + "'";
					s.execute(SQL);
				} else {
					rs.close();
					SQL = "INSERT INTO Week_Points (BoxNumber, " + dayColumn + ") VALUES ('" + box + "', '" + data[0] + "')";
					s.execute(SQL);
					SQL = "INSERT INTO Week_WUs (BoxNumber, " + dayColumn + ") VALUES ('" + box + "', '" + data[1] + "')";
					s.execute(SQL);
				}
				box++;
			}
		} catch(Exception e){
			e.printStackTrace();
		}
	}

    public boolean sortStats(){

    	File rawStats = new File("/usr/tmp/raw.txt");
        File sortedStats = new File("/usr/tmp/sorted.txt");

    //	File rawStats = new File("c:\\webpage\\data\\rawstats.txt");
 //   	File sortedStats = new File("c:\\webpage\\data\\sorted.txt");
        try{
            BufferedReader in = new BufferedReader(new FileReader(rawStats));
            BufferedWriter out = new BufferedWriter(new FileWriter(sortedStats));
            int boxNum=1;
            String str;
                while((str = in.readLine()) != null){
		if(str.startsWith("SMG")){
		}
		else if(str.startsWith("SM") || str.startsWith("IC")){
                        if(str.indexOf(boxNum + "") < 5
                            && str.indexOf(boxNum + "") > 1
                            && str.charAt(str.indexOf(boxNum + "") -1) != '1'){

                                //System.out.println(str);
                                out.write(str + '\n');
                                boxNum++;
                                in.close();
                                in = new BufferedReader(new FileReader(rawStats));
                        }
                    } else {
                        if(str.indexOf(boxNum + "") < 10
                            && str.indexOf(boxNum + "") > 7
                            && str.charAt(str.indexOf(boxNum + "") - 1) != '1'){

                                //System.out.println(str);
                                out.write(str + '\n');
                                boxNum++;
                                in.close();
                                in = new BufferedReader(new FileReader(rawStats));
                        }
                    }
                }
            in.close();
            out.close();
            this.numBoxes = boxNum - 1;
            if(boxNum == 1){
            	this.numBoxes = this.getNumBoxesFromDBase();
            	return false;
            }
        } catch(Exception e){
            e.printStackTrace();
        }
        return true;

    }
    
    private int getNumBoxesFromDBase(){
    	int boxes=0;
    	String SQL;
    	ResultSet rs;
    	try {
    		SQL = "SELECT BoxNumber FROM Week_Points";
    		rs = s.executeQuery(SQL);
    		while(rs.next()){
    			boxes++;
    		}
    		
    	} catch (SQLException e){
    		e.printStackTrace();
    	}
    	return boxes;
    }
    
    private void emptyUpdate(){
    	String SQL;
    	ResultSet rs;
    	GregorianCalendar todayCal = new GregorianCalendar();
    	int today = todayCal.get(GregorianCalendar.DAY_OF_WEEK);
    	try {
    		if(today == 1){
    		} else {
    			SQL = "SELECT _" +(today - 2) + " FROM Week_Points";
    			rs = s.executeQuery(SQL);
    			int box=1;
    			while(rs.next()){
    				SQL = "UPDATE Week_Points SET _" + (today - 1) + "='"
    					+ rs.getInt("_" + (today - 2)) + "' WHERE BoxNumber = '" + box + "'";
    				s.executeUpdate(SQL);
    				box++;
    			}
    			rs.close();
    			SQL = "SELECT _" +(today - 2) + " FROM Week_WUs";
    			rs = s.executeQuery(SQL);
    			box=1;
    			while(rs.next() ){
    				SQL = "UPDATE Week_WUs SET _" + (today - 1) + "='" + rs.getInt("_" + (today - 2)) + "' WHERE BoxNumber = '" + box + "'";
    				s.executeUpdate(SQL);
    				box++;
    			}
    			rs.close();
    		}
    		
    	} catch(SQLException e){
    		e.printStackTrace();
    	}	
    }

    
    private String[] parseStats(String line){
        String boxData;
        String newScore;
        String newWUs;
        String newRank;

        boxData = line.substring(line.indexOf("\t") + 1);
        newScore = boxData.substring(0,boxData.indexOf("\t"));
        boxData = boxData.substring(boxData.indexOf("\t") + 1);
        newWUs = boxData.substring(0,boxData.indexOf("\t"));
        boxData = boxData.substring(boxData.length() - 2);
        newRank = boxData.trim();

        String [] temp = {newScore,newWUs,newRank};
        return temp;
    }

    
    
    public void fetchStats(){
        String url = "http://vspx27.stanford.edu/teamstats/team93.txt";
        //String url = "http://folding.stanford.edu/daily_user_summary.txt";
        try{
            URL statLocation = new URL(url);
             File rawStats = new File("/usr/tmp/raw.txt");
            //File rawStats = new File("c:\\webpage\\data\\rawstats.txt");
                    // Read all the text returned by the server
            BufferedReader in = new BufferedReader(
                    new InputStreamReader(statLocation.openStream()));
            BufferedWriter out = new BufferedWriter(new FileWriter(rawStats));
            String str;
            int line = 1;
            while ((str = in.readLine()) != null) {
                if(!str.replace('\t','*').equals(str)){
                    str = str.substring(str.indexOf('\t')+1);
                    str = str.substring(str.indexOf('\t')+1);
                }

                if(str.startsWith("Icrontic") || str.startsWith("icrontic")
                    || str.startsWith("SM") || str.startsWith("IC")){
		            if(!str.startsWith("Icrontic_&_")){
	                    if(str.endsWith("93")){
	                        out.write(str + "\t" + line);
	                        out.newLine();
	                        line++;
	                    }
		            }
                }
            }
            in.close();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

	public static void main(String[] args) {
		new SMxStats();
	}
}

primesuspect · May 2005

Maybe we can host it?

shwaip · May 2005

That is something to look into.

I'd want to finish the latest round of updates, and definitely want to have someone who knows more about SQL see if they can make it more efficient. My knowledge consists of what I taught myself over about 5 weeks and a quick google here and there when I can't remember what I need to. It's also not been interpreted with the latest Java SDK.

Anyways, it's not very anything intensive:
The (mysql) database takes up about 210k - will grow as weekly stats accumulate, but not quickly
It runs once a day - makes 4 text files as output
It needs to have the JRE installed.

That's about it.

shwaip · June 2005

apparently, and completely without my knowledge (but not beyond my expectations), verizon blocks incoming port 80 traffic. boo. I'll work on getting the page ready to host elsewhere (assuming that's still cool with you, prime). Until then (it may be a while, gonna be somewhat busy for a few weeks), you should be able to get to the stats page via

http://smxstats.redirectme.net:8080

Sorry the stats weren't up, i didn't even realize it until I checked the httpd access logs and saw I was the only one downloading anything.

(and if you still can't see them, please let me know :P)

Jimborae · June 2005

I can see it fine, but what concerns me is that SM12 is showing nothing but she completed her first wu in a couple of years a few days ago.

shwaip · June 2005

Are you sure that it got submitted properly?

EOC stats agree with my page...

http://folding.extremeoverclocking.com/user_summary.php?s=&u=21857

profdlp · June 2005

shwaip wrote:

Are you sure that it got submitted properly?...

whisperwhisperwhisper...Team # whisperwhisperwhisper

BuddyJ · June 2005

On Noes!!!11 Hax0res Stoled Yuor Teem Numbar!!!!!!1

Jimborae · June 2005

profdlp wrote:

whisperwhisperwhisper...Team # whisperwhisperwhisper

I found out what the problem was, in my haste in rebuilding SM/IC12, muggins here had forgot to set the correct date. Doh

SM12 thought that it was the 11th of May. :shakehead

I've reset the date correctly & as prof has pointed out it looks like the results have been sent now.

profdlp · June 2005

Team 93

Jimborae · June 2005

Sorted

Will she lose her points for her first WU??? Not to drastic if she does cos she's still awaiting her new party frock & knickers (read case) but it would be nice if she didn't.

shwaip · June 2005

The team doesn't get the points, no. But the results got submitted anyways.

shwaip · June 2005

grrr...I restarted my server before I left on vacation, and forgot to restart the no-ip update client. Now, the ip is fubared. I'll have my little brother _try_ to fix it when he gets home, but he isn't very computer literate, and we'll see what he knows about linux.

qparadox · June 2005

If you're using no-ip.com you could always have him just install the windows client. This is assuming both computers are just behind a router and you forward the port. If he can't understand a commend line then it may be an alternative while you're away.

You could also get him to tell you your new IP and then just ssh in directly to the IP (assuming that port is forwarded) to do the update.

shwaip · June 2005

qparadox wrote:

If you're using no-ip.com you could always have him just install the windows client. This is assuming both computers are just behind a router and you forward the port. If he can't understand a commend line then it may be an alternative while you're away.

You could also get him to tell you your new IP and then just ssh in directly to the IP (assuming that port is forwarded) to do the update.

Yup. Not sure which I plan on doing yet. The port isn't forwarded, as I usually have no need to ssh in from outside the LAN. I'll have him install the client if he can't figure out how to do what he needs. He won't be back till sunday eve anyways.

SMx Stats - Miscellaneous

Comments