eve_prediction.r

library(RODBC)
library(jsonlite)
library(data.table)
library(grid)
library(reshape)
library(ggplot2)
library(sde)
library(zoo)
library(plyr)

#### SCRIPT GLOBALS ####
locationID <- 30000142 #JITA=30000142
date_range <- 100
typeid     <- 29668
	#PLEX = 29668
plot.path   <- "C:/Users/Lockefox/Plots/"
plot.width  <- 1600
plot.height <- 900


#### FETCH DATA ####
SDE <- odbcConnect('sde_lookup')
EC  <- odbcConnect('remote_ec')

##### SDE #####
typeID_q   <- paste0("SELECT typeID,typeName FROM invtypes WHERE typeID=",typeid)
location_q <- paste0("SELECT solarSystemName,regionID FROM mapsolarsystems WHERE solarSystemID=",locationID)
print("FETCHING SDE DATA")
typeID_lookup   <- sqlQuery(SDE, typeID_q)
location_lookup <- sqlQuery(SDE, location_q)
regionID <- location_lookup$regionID[1]
solarSystemName <- as.character(location_lookup$solarSystemName[1])
typeName <- as.character(typeID_lookup$typeName[1])

##### EVE-CENTRAL #####
ec_q <- paste0("SELECT price_date as `date`, price_time as `hour`, ",
               "SUM(IF(locationid = ",locationID,", IF(buy_sell=1, price_best,0),0)) AS 'SellOrder', ",
               "SUM(IF(locationid = ",locationID,", IF(buy_sell=0, price_best,0),0)) AS 'BuyOrder', typeid ",
               "FROM snapshot_evecentral ",
               "WHERE typeid =",typeid," ",
               "AND locationid=",locationID," ",
               "AND price_date > (SELECT MAX(price_date) FROM snapshot_evecentral) - INTERVAL ",date_range," DAY ",
               "GROUP BY price_date, price_time, typeid")
print("FETCHING EVE-CENTRAL DATA")
ec_data <- sqlQuery(EC, ec_q)
ec_data$datetime <- as.POSIXlt(paste(ec_data$date,ec_data$hour,sep=" "))
ec_data$datetime <- as.POSIXlt(paste0(ec_data$date," ",hour(ec_data$datetime),":00:00"))
ec_data$typeid <- as.factor(ec_data)

##### CREST #####
CREST_addr <- paste0("https://public-crest.eveonline.com/market/",regionID,"/types/",typeid,"/history/")
print("FETCHING CREST DATA")
CREST_json <- fromJSON(readLines(CREST_addr))
CREST_data <- CREST_json$items
CREST_data$date <- as.Date(CREST_data$date)

#### SPECIAL GROUPINGS ####
patch_list <- as.POSIXlt( c("2014-12-09 13:00", "2015-01-13 13:00", "2015-02-17 13:00", 
							"2015-03-24 13:00", "2015-04-28 13:00", "2015-06-02 13:00", 
							"2015-07-07 13:00" 
							))

date.min <- Sys.Date() - date_range
patch_list.aux <- c()
for (row in 1:NROW(patch_list)){	#Find the patches in the date range
	tmpdate <- as.Date(patch_list[row])
	if (tmpdate > date.min){
		patch_list.aux <- c(patch_list.aux, as.character(patch_list[row]))
	}
}
patch_list.aux <- as.POSIXlt(patch_list.aux)

#### CREST PROCESSING ####