# change this to your MySQL directory - uncomment if you use MySQL 5.0 instead of 5.1
Sys.setenv(MYSQL_HOME="c:\\program files\\mysql\\mysql server 5.0\\")
library(RMySQL)
library(sp)
library(maps)
library(maptools)

# verbose way to format an integer as US currency
format.as.currency <- function(val) {
  nstr = format(val,scientific=FALSE)
  offsets <- rev(nchar(nstr)-which(1:nchar(nstr) %% 3 == 0))
  digits <- unlist(strsplit(nstr,""))
  outstr <- "$"
  for(i in 1:length(digits)) {
    outstr <- paste(outstr,digits[i],sep="")    
    if(i %in% offsets) {
      outstr <- paste(outstr,",",sep="")
    }
  }
  return(outstr)
}

color_step_count <- 32

zip2ll <- read.csv("zips.csv",col.names=c("zipcode","x","y"),colClasses=c("character","double","double"))
row.names(zip2ll) <- zip2ll$zipcode

colors <- rgb(seq(16,255,8),0,51,255,maxColorValue=255)

states <- map("state",plot=FALSE)
llCRS <- CRS("+proj=longlat +ellps=WGS84")
states_sp <- map2SpatialLines(states,proj4string=llCRS)

drv <- dbDriver("MySQL")

# change dbname and user to match your system
con <- dbConnect(drv,dbname='fechell',user='root',host='localhost')

max_results <- dbGetQuery(con,"select sum(total) as 'total' from transactions_summary_weekly group by zipcode order by sum(total) desc limit 1")

max_total_zip <- max_results[1,1]

min_results <- dbGetQuery(con,"select sum(total) as 'total' from transactions_summary_weekly group by zipcode order by sum(total) limit 1")

min_total_zip <- min_results[1,1]

color_scale_factor <- as.integer((max_results-min_results)/color_step_count)

total_barplot_values <- dbGetQuery(con,"select concat(contribution_year,'-',lpad(contribution_week,2,'0')) as 'year_week', sum(total) as' total' from transactions_summary_weekly group by contribution_year,contribution_week order by contribution_year,contribution_week")
total_barplot_values$total <- as.integer(total_barplot_values$total)
row.names(total_barplot_values) <- total_barplot_values$year_week
barplot_widths <- rep(5,nrow(total_barplot_values))


running_total <- 0.0

for(year in c(2007,2008)) {
	for(week in 0:52) {

		o4a_results <- dbGetQuery(con,
		sprintf("select zipcode,sum(total) as 'total' from transactions_summary_weekly where (contribution_year=%d and contribution_week <= %d) or (contribution_year < %d) group by zipcode having sum(total) > 5000 order by sum(total)",year,week,year))

		if (is.null(o4a_results$total)) {
		next 
		}

		if(year == 2008 && week > 44) {
		break
		} 

		year_week_key <- sprintf("%d-%02d",year,week)

		o4a_results$total <- as.integer(o4a_results$total)
		rownames(o4a_results) <- o4a_results$zipcode

		goodindexes <- intersect(o4a_results$zipcode,zip2ll$zipcode)
		o4a_sub <- o4a_results[goodindexes,]
		zip2ll_sub <- zip2ll[goodindexes,]

		zip2ll_mat <- cbind(zip2ll_sub$y,zip2ll_sub$x)
		row.names(zip2ll_mat) <- zip2ll_sub$zipcode


		o4a_sp = SpatialPointsDataFrame(zip2ll_mat,o4a_sub)

		weekly_total <- total_barplot_values[year_week_key,]$total

		running_total <- running_total + weekly_total

		color_values <- colors[1+o4a_sp$total/color_scale_factor]
		filename <- sprintf("o4a_%d_%02d.png",year,week)
		png(filename=filename,bg='white',width=1024,height=768)
		  # draw the map first
			layout(matrix(c(1,1,2),3,1,byrow=TRUE),respect=FALSE)
			par(mar=c(3,4,4,2),oma=c(1,2,0,3))
			plot(states_sp,col="lightgrey")
			title_line <- sprintf("Total Contributions by Zip Code by Week. Current: %s\nWeekly Total: %s\nRunning Total %s",year_week_key,format.as.currency(weekly_total),format.as.currency(running_total))
			plot(o4a_sp,add=TRUE,xlim=c(-124.68134,-67.00742),ylim=c(25.12993,49.38383),pch=".",col=color_values,cex=3)
			legend("right",c("$5K","$500K","$1M","$39M"),fill=c(colors[1],colors[8],colors[16],colors[30]),pch=22,inset=0.03,cex=1.4)
			title(title_line,cex.main=1.4)

			# now draw the boxplot / hist
			barplot_colors <- rep("#D3D3D3",nrow(total_barplot_values))
			barplot_colors[which(total_barplot_values$year_week == year_week_key)] <- "#4863D0"
			barplot_annotation_offset<- (which(total_barplot_values$year_week == year_week_key) * 6) - 3

			bpvs <- barplot(total_barplot_values$total,width=barplot_widths,axes=FALSE,col=barplot_colors,main="Total Contributions from All Zip Codes, by Week",cex.main=1.5)
			axis(2,at=c(5000,38000000),labels=c("$5K","$38M"),las=1,cex.axis=1.5)
			axis(1,at=barplot_annotation_offset,labels=c(sprintf("%s, %s",year_week_key,format.as.currency(weekly_total))),cex.axis=1.4)
		dev.off()

	}
}

dbDisconnect(con)