-sub part11 {
- my $class = shift;
- my %opt = shift;
- my $date = $opt{date} || time;
- my $agentnum = $opt{agentnum};
-
- my @select = (
- 'cust_location.censustract',
- # VoIP indicator (0 for non-VoIP, 1 for VoIP)
- 'COALESCE(is_voip, 0)',
- # number of lines/subscriptions
- 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
- # consumer grade lines/subscriptions
- 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END) ELSE 0 END)'
- );
-
- my $from = 'cust_pkg
- JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
- JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
- JOIN part_pkg USING (pkgpart) '.
- join_optionnames_int(qw(
- is_phone is_voip is_consumer phone_lines
- ))
- ;
-
- my @where = (
- active_on($date),
- "(is_voip = 1 OR is_phone = 1)",
- );
- push @where, "cust_main.agentnum = $agentnum" if $agentnum;
- my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
- my $order_by = $group_by;
-
- my $statement = "SELECT ".join(', ', @select) . "
- FROM $from
- WHERE ".join(' AND ', @where)."
- GROUP BY $group_by
- ORDER BY $order_by
- ";
+tie our %parts, 'Tie::IxHash', (
+ fbd => 'Fixed Broadband Deployment',
+ fbs => 'Fixed Broadband Subscription',
+ fvs => 'Fixed Voice Subscription',
+ lts => 'Local Exchange Telephone Subscription',
+ voip => 'Interconnected VoIP Subscription',
+ mbd => 'Mobile Broadband Deployment',
+ mbsa => 'Mobile Broadband Service Availability',
+ mbs => 'Mobile Broadband Subscription',
+ mvd => 'Mobile Voice Deployment',
+ mvs => 'Mobile Voice Subscription',
+);