1 package FS::part_pkg::sql_external;
2 use base qw( FS::part_pkg::discount_Mixin FS::part_pkg::recur_Common );
7 #use FS::Record qw(qsearch qsearchs);
9 tie our %query_style, 'Tie::IxHash', (
10 'simple' => 'Simple (a single value for the recurring charge)',
11 'detailed' => 'Detailed (multiple rows for invoice details)',
14 our @detail_cols = ( qw(amount format duration phonenum accountcode
15 startdate regionname detail)
18 'name' => 'Base charge plus additional fees for external services from a configurable SQL query',
19 'shortname' => 'External SQL query',
20 'inherit_fields' => [ 'prorate_Mixin', 'global_Mixin' ],
22 'cutoff_day' => { 'name' => 'Billing Day (1 - 28) for prorating or '.
27 'recur_method' => { 'name' => 'Recurring fee method',
29 #'options' => \%recur_method,
31 'select_options' => \%FS::part_pkg::recur_Common::recur_method,
33 'datasrc' => { 'name' => 'DBI data source',
36 'db_username' => { 'name' => 'Database username',
39 'db_password' => { 'name' => 'Database password',
42 'query' => { 'name' => 'SQL query',
47 'name' => 'Query output style',
49 'select_options' => \%query_style,
53 'fieldorder' => [qw( recur_method cutoff_day ),
54 FS::part_pkg::prorate_Mixin::fieldorder,
55 qw( datasrc db_username db_password query query_style
62 my $str = $self->SUPER::price_info(@_);
63 $str .= " plus per-service charges" if $str;
69 my($cust_pkg, $sdate, $details, $param ) = @_;
71 my $quantity; # can be overridden; if not we use the default
73 my $dbh = DBI->connect( map { $self->option($_) }
74 qw( datasrc db_username db_password )
78 my $sth = $dbh->prepare( $self->option('query') )
81 foreach my $cust_svc (
82 grep { $_->part_svc->svcdb eq "svc_external" } $cust_pkg->cust_svc
84 my $id = $cust_svc->svc_x->id;
85 $sth->execute($id) or die $sth->errstr;
87 if ( $self->option('query_style') eq 'detailed' ) {
89 while (my $row = $sth->fetchrow_hashref) {
90 if (exists $row->{amount}) {
91 if ( $row->{amount} eq '' ) {
93 } elsif ( $row->{amount} =~ /^\d+(?:\.\d+)?$/ ) {
94 $price += $row->{amount};
96 die "sql_external query returned non-numeric amount: $row->{amount}";
99 if (defined $row->{quantity}) {
100 if ( $row->{quantity} eq '' ) {
102 } elsif ( $row->{quantity} =~ /^\d+$/ ) {
103 $quantity += $row->{quantity};
105 die "sql_external query returned non-integer quantity: $row->{quantity}";
109 my $detail = FS::cust_bill_pkg_detail->new;
110 foreach my $field (@detail_cols) {
111 if (exists $row->{$field}) {
112 $detail->set($field, $row->{$field});
115 if (!$detail->get('detail')) {
116 die "sql_external query did not return detail description";
117 # or make something up?
118 # or just don't insert the detail?
121 push @$details, $detail;
126 # simple style: returns only a single value, which is the price
127 $price += $sth->fetchrow_arrayref->[0];
131 $price = sprintf('%.2f', $price);
133 # XXX probably shouldn't allow package quantity > 1 on these packages.
134 if ($cust_pkg->quantity > 1) {
135 warn "sql_external package #".$cust_pkg->pkgnum." has quantity > 1\n";
138 $param->{'override_quantity'} = $quantity;
139 $param->{'override_charges'} = $price;
140 ($cust_pkg->quantity || 1) * $self->calc_recur_Common($cust_pkg,$sdate,$details,$param);
143 sub can_discount { 1; }