gistfile1.pl

use strict;
use DBI;
use Data::Dumper;
use DateTime;
use Text::CSV_XS;

my $pg_dbname = "gtfs_njtransit";
my $pg_host = "dbi:Pg:dbname=$pg_dbname";
my $pg_user = "xxxx";
my $pg_pass = "xxxx";

my $pg_dbh = DBI->connect($pg_host,$pg_user,$pg_pass) or die "no db for you: $!\n";

my $sql = q {
SELECT
  BusRoute.route_id
  ,bustrip.trip_id
  ,bustrip.trip_headsign
  ,BusStopTime.arrival_time
  ,busstopTime.stop_sequence
  ,busstop.stop_name
  --,BusTripShape.shape_pt_sequence
FROM routes BusRoute
LEFT JOIN trips BusTrip
  ON BusRoute.route_id = BusTrip.route_id
LEFT JOIN stop_times BusStopTime
  ON BusTrip.trip_id = BusStopTime.trip_id
LEFT JOIN stops BusStop
  ON BusStopTime.stop_id = BusStop.stop_id

WHERE
BusTrip.direction_id = '0' --this is outbound to NY
AND BusTrip.service_id = '9' --this is the weekday service
and
bustrip.trip_id IN (
	SELECT
	 StopTime.trip_id
	FROM stops StopName
	LEFT JOIN stop_times StopTime
	  ON StopName.stop_id = StopTime.stop_id
	WHERE
 	  StopName.stop_name like '%AUTHORITY%'
    AND StopTime.trip_id IN (
    	SELECT StopTime.trip_id
        FROM stops StopName
        LEFT JOIN stop_times StopTime
          ON StopName.stop_id = StopTime.stop_id
        WHERE StopTime.arrival_time < '09:00'
        )
    )
--AND
--  BusTrip.trip_headsign ~ '^113.*'
--AND BusStopTime.arrival_time ~ '^05.*'


ORDER BY
  BusRoute.route_id
  ,BusTrip.trip_id
  ,BusStopTime.stop_sequence
  ASC


	};

my $pg_sth = $pg_dbh->prepare($sql);

$pg_sth->execute();

my ( $routeid, $tripid, $tripheadsign, $arrivaltime, $sqnce, $stopname );
$pg_sth->bind_columns (undef,  \( $routeid, $tripid, $tripheadsign, $arrivaltime, $sqnce, $stopname ) );

my %time_comparator;
while ( $pg_sth->fetch() ) {
	if ( $stopname !~ /AUTHORITY/i ) {
		$time_comparator{$routeid}{$tripid}{$tripheadsign}{'some_stop'}{"$stopname sqnce $sqnce"} = $arrivaltime;

	} else {
		$time_comparator{$routeid}{$tripid}{$tripheadsign}{'port_auth'}{$stopname} = $arrivaltime;
	}
}

my $csv = Text::CSV_XS->new ( { binary => 1, eol => $/ } );
my ($begin,$end,$duration);
open my $fh, ">", "elapsed.csv" or die "elapsed.csv: $!";

foreach my $route_out ( keys %time_comparator ){
	foreach my $trip_out (keys $time_comparator{$route_out} ){
		foreach my $hs_out ( keys $time_comparator{$route_out}{$trip_out} ){
			foreach my $some_name_out ( keys $time_comparator{$route_out}{$trip_out}{$hs_out}{'some_stop'}) {

				print "$trip_out,";
				print "$some_name_out,";
				$begin = $time_comparator{$route_out}{$trip_out}{$hs_out}{'some_stop'}{$some_name_out};
				$end = $time_comparator{$route_out}{$trip_out}{$hs_out}{'port_auth'}{'PORT AUTHORITY BUS TERMINAL'};
				$duration = time_diff($begin,$end);
				print "'$begin,'$end,$duration\n";
				my @arr_ref = ( $route_out,$trip_out,$hs_out,$some_name_out,$begin,$end,$duration );
				$csv->print($fh,\@arr_ref) or $csv->error_diag;
			}
		}
	}
}
close $fh or die "elapsed.csv: $!";

sub get_dt_obj {

	my $time_str = shift;
	my ( $hr,$min,$sec ) = split(/:/, $time_str);

	return DateTime->new(
		year       => 2012,
		month      => 11,
		day        => 22,
		hour       => $hr,
		minute     => $min,
		second     => $sec,
	  );
}

sub time_diff {
	my ($begin,$end) = @_;

	my $begin_obj = get_dt_obj($begin);
	my $end_obj = get_dt_obj($end);

	my $dur_obj = $begin_obj->subtract_datetime_absolute( $end_obj );

	return $dur_obj->seconds();

}