#!/usr/bin/perl

#(2/15/2015): Sample script to create a dashboard map of global conflict from GDELT using CartoDB - updated each day.
#NOTE: this script is designed to work with GDELT 1.0 daily update files and must be modified to work with GDELT 2.0 files.  It requires that you have some kind of automated cronjob or equivalent that automatically downloads the latest GDELT 1.0 event file update each morning, unzips it, and then runs this script.

#set the following variables...
$EVENTDIRECTORY = ''; #the full path on your system to the directory you are downloading the event files to...
$CARTODBACCOUNT = ''; #this is the "username" of your CartoDB account...
$CARTODBAPIKEY = ''; #the "api_key" key that CartoDB generates for your account (see CartoDB's documentation for where to find this)...


#initialize LWP to communicate with CartoDB's API...
use LWP::UserAgent;
$useragent = LWP::UserAgent->new;
$useragent->agent("GDELT Dashboard Mapper");

#compute yesterday's date (this is the export file that we use to update the map)...
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time - 86400); $ENDDATE = sprintf("%d%02d%02d",$year+1900,$mon+1,$mday);
#and compute the date of 180 days ago for the end of our rolling 180 day window for our animation...
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time - 86400 - (86400*180)); $STARTDATE = sprintf("%d%02d%02d",$year+1900,$mon+1,$mday); $CARTOSTARTDATE = sprintf("%d/%d/%d",$mon+1,$mday, $year+1900);
################################################################################################################################
#first delete the older dates from CartoDB...... we let LWP escape our query for us...
$response = $useragent->request(HTTP::Request->new(GET=>"http://$CARTODBACCOUNT.cartodb.com/api/v2/sql?q=delete from gdeltanimated where date < '$CARTOSTARTDATE'&api_key=$CARTODBAPIKEY"));
if (!$response->is_success) { print "ERROR: '" . $response->status_line . "\n"; }
################################################################################################################################

################################################################################################################################
#first read in the list of events from yesterday...
open(FILE, "$EVENTDIRECTORY/$ENDDATE.export.CSV");
while(<FILE>) {
    @vars = split/\t/, $_;
    $ccgns = uc($vars[51]); $vars[57]=~s/\s+$//;
    if (!($vars[49] == 1 && $vars[51] eq 'US') && $vars[1]==$ENDDATE) { #only extract events from yesterday and exclude US country-level matches...
    	#hash up Protest and Material Conflict events against Civilian actors...
	$found = 0;
	if ($vars[28] == 14) { $PROTEST{"$vars[1]\t$vars[53]\t$vars[54]"}+=$vars[33]; $found = 1; } #protest events... 
    	if ($vars[29] == 4 && $vars[22] eq 'CVL') { $CIVVIOLENCE{"$vars[1]\t$vars[53]\t$vars[54]"}+=$vars[33]; $found = 1; } #Material Conflict events against Civilian actors...
	#if we found either of our events of interest, also hash up by location/date...
	if ($found == 1) {
	    if (!exists($LOCS{"$vars[1]\t$vars[53]\t$vars[54]"})) {$DATELOCS{$vars[1]} .= "$vars[53]\t$vars[54]\n"; }
	    $LOCS{"$vars[1]\t$vars[53]\t$vars[54]"}+=$vars[33];
	    $DATES{$vars[1]}+=$vars[33];
	}
    }
}
close(FILE);
################################################################################################################################

################################################################################################################################
open(OUT, ">./TIMEMAP.CSV");
print OUT "Date\tLat\tLong\tDisplay\n";
foreach $date (keys %DATES) {
    foreach $dateloc (split/\n/, $DATELOCS{$date}) {
    	($lat, $long) = split/\t/, $dateloc;
	$numprotests = $PROTEST{"$date\t$dateloc"};
	$numviolence = $CIVVIOLENCE{"$date\t$dateloc"};
	######
	#CartoDB Torque time animation layers can only color each cell a single color (it does not support blending), so we color each point based on whether it has more conflict vs more protests (winner takes all)...  
	$rat = sprintf("%0.2f", $numviolence / $LOCS{"$date\t$dateloc"});
	if ($rat > 0.5) {$disp = 2;} else {$disp = 1;}
	######
	#filter a bit to only display locations with more events...
	if ($LOCS{"$date\t$dateloc"} > 3) {
	    print "\tInserting $date/$dateloc...\n";
	    print OUT "$date\t$lat\t$long\t$disp\n";
	    $response = $useragent->request(HTTP::Request->new(GET=>"http://$CARTODBACCOUNT.cartodb.com/api/v2/sql?q=insert into gdeltanimated (the_geom,date,type) values (ST_SetSRID(ST_Point($long, $lat),4326), '$date', $disp)&api_key=$CARTODBAPIKEY"));
		if (!$response->is_success) { print "ERROR: '" . $response->status_line . "\n"; }    
	}
    }
}
close(OUT);
################################################################################################################################