+=item _init_tower_pkg_cache
+
+Internal method: creates a temporary table relating pkgnums to towernums.
+A (pkgnum, towernum) record indicates that this package once had a
+svc_broadband service which, as of its last insert or replace_new history
+record, had a sectornum associated with that towernum.
+
+This is expensive, so it won't be done more than once an hour. Historical
+data about package churn shouldn't be changing in realtime anyway.
+
+=cut
+
+sub _init_tower_pkg_cache {
+ my $self = shift;
+ my $dbh = dbh;
+
+ my $current = $CACHE->get('tower_pkg_cache_update');
+ return if $current;
+
+ # XXX or should this be in the schema?
+ my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
+ $dbh->do($sql) or die $dbh->errstr;
+ $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
+ $dbh->do($sql) or die $dbh->errstr;
+
+ # assumptions:
+ # sectornums never get reused, or move from one tower to another
+ # all service history is intact
+ # svcnums never get reused (this would be bad)
+ # pkgnums NEVER get reused (this would be extremely bad)
+ $sql = "INSERT INTO tower_pkg_cache (
+ SELECT COALESCE(towernum,0), pkgnum
+ FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
+ LEFT JOIN (
+ SELECT DISTINCT ON(svcnum) svcnum, sectornum
+ FROM h_svc_broadband
+ WHERE (history_action = 'replace_new'
+ OR history_action = 'replace_old')
+ ORDER BY svcnum ASC, history_date DESC
+ ) AS svcnum_sectornum USING (svcnum)
+ LEFT JOIN tower_sector USING (sectornum)
+ )";
+ $dbh->do($sql) or die $dbh->errstr;
+
+ $CACHE->set('tower_pkg_cache_update', 1, 3600);
+
+};
+