4 use vars qw( @ISA @EXPORT_OK $DEBUG );
8 use FS::UID qw( dbh driver_name );
10 use FS::Record qw(qsearchs qsearch str2time_sql);
12 use FS::upgrade_journal;
15 $FS::svc_domain::whois_hack = 1;
17 @ISA = qw( Exporter );
18 @EXPORT_OK = qw( upgrade_schema upgrade_config upgrade upgrade_sqlradius );
24 FS::Upgrade - Database upgrade routines
32 Currently this module simply provides a place to store common subroutines for
47 my $conf = new FS::Conf;
49 $conf->touch('payment_receipt')
50 if $conf->exists('payment_receipt_email')
51 || $conf->config('payment_receipt_msgnum');
53 $conf->touch('geocode-require_nw_coordinates')
54 if $conf->exists('svc_broadband-require-nw-coordinates');
56 unless ( $conf->config('echeck-country') ) {
57 if ( $conf->exists('cust_main-require-bank-branch') ) {
58 $conf->set('echeck-country', 'CA');
59 } elsif ( $conf->exists('echeck-nonus') ) {
60 $conf->set('echeck-country', 'XX');
62 $conf->set('echeck-country', 'US');
66 my @agents = qsearch('agent', {});
68 upgrade_overlimit_groups($conf);
69 map { upgrade_overlimit_groups($conf,$_->agentnum) } @agents;
71 upgrade_invoice_from($conf);
72 foreach my $agent (@agents) {
73 upgrade_invoice_from($conf,$agent->agentnum,1);
76 my $DIST_CONF = '/usr/local/etc/freeside/default_conf/';#DIST_CONF in Makefile
77 $conf->set($_, scalar(read_file( "$DIST_CONF/$_" )) )
78 foreach grep { ! $conf->exists($_) && -s "$DIST_CONF/$_" }
79 qw( quotation_html quotation_latex quotation_latexnotes );
81 # change 'fslongtable' to 'longtable'
82 # in invoice and quotation main templates, and also in all secondary
85 qsearch('conf', { 'name' => {op=>'LIKE', value=>'%latex%'} });
87 foreach my $c (@latex_confs) {
88 my $value = $c->value;
89 if (length($value) and $value =~ /fslongtable/) {
90 $value =~ s/fslongtable/longtable/g;
91 $conf->set($c->name, $value, $c->agentnum);
95 # if there's a USPS tools login, assume that's the standardization method
97 $conf->set('address_standardize_method', 'usps')
98 if $conf->exists('usps_webtools-userid')
99 && length($conf->config('usps_webtools-userid')) > 0
100 && ! $conf->exists('address_standardize_method');
102 # this option has been renamed/expanded
103 if ( $conf->exists('cust_main-enable_spouse_birthdate') ) {
104 $conf->touch('cust_main-enable_spouse');
105 $conf->delete('cust_main-enable_spouse_birthdate');
109 if ( $conf->exists('cust_pkg-show_fcc_voice_grade_equivalent') ) {
110 $conf->touch('part_pkg-show_fcc_options');
111 $conf->delete('cust_pkg-show_fcc_voice_grade_equivalent');
113 You have FCC Form 477 package options enabled.
115 Starting with the October 2014 filing date, the FCC has redesigned
116 Form 477 and introduced new service categories. See bin/convert-477-options
117 to update your package configuration for the new report.
119 If you need to continue using the old Form 477 report, turn on the
120 'old_fcc_report' configuration option.
124 # boolean invoice_sections_by_location option is now
125 # invoice_sections_method = 'location'
126 my @invoice_sections_confs =
127 qsearch('conf', { 'name' => { op=>'LIKE', value=>'%sections_by_location' } });
128 foreach my $c (@invoice_sections_confs) {
129 $c->name =~ /^(\w+)sections_by_location$/;
130 $conf->delete($c->name);
131 my $newname = $1.'sections_method';
132 $conf->set($newname, 'location');
135 # boolean tax-cust_exempt-groups-require_individual_nums is now -num_req all
136 if ( $conf->exists('tax-cust_exempt-groups-require_individual_nums') ) {
137 $conf->set('tax-cust_exempt-groups-num_req', 'all');
138 $conf->delete('tax-cust_exempt-groups-require_individual_nums');
141 # boolean+text previous_balance-exclude_from_total is now two separate options
142 my $total_new_charges = $conf->config('previous_balance-exclude_from_total');
143 if (length($total_new_charges) > 0) {
144 $conf->set('previous_balance-text-total_new_charges', $total_new_charges);
145 $conf->set('previous_balance-exclude_from_total', '');
148 if ( $conf->exists('voip-cust_email_csv_cdr') ) {
149 $conf->set('voip_cdr_email_attach', 'csv');
150 $conf->delete('voip-cust_email_csv_cdr') ;
153 if ( !$conf->config('password-generated-characters') ) {
155 'abcdefghijkmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789()#.,' ;
156 $conf->set('password-generated-characters', $pw_set);
161 sub upgrade_overlimit_groups {
163 my $agentnum = shift;
164 my @groups = $conf->config('overlimit_groups',$agentnum);
165 if(scalar(@groups)) {
166 my $groups = join(',',@groups);
169 if ( $groups !~ /^[\d,]+$/ ) {
170 foreach my $groupname ( @groups ) {
171 my $g = qsearchs('radius_group', { 'groupname' => $groupname } );
173 $g = new FS::radius_group {
174 'groupname' => $groupname,
175 'description' => $groupname,
178 die $error if $error;
180 push @groupnums, $g->groupnum;
182 $conf->set('overlimit_groups',join("\n",@groupnums),$agentnum);
187 sub upgrade_invoice_from {
188 my ($conf, $agentnum, $agentonly) = @_;
190 (!$conf->exists('invoice_from_name',$agentnum,$agentonly)) &&
191 ($conf->config('invoice_from',$agentnum,$agentonly) =~ /\<(.*)\>/)
194 $realemail =~ s/^\s*//; # remove leading spaces
195 $realemail =~ s/\s*$//; # remove trailing spaces
196 my $realname = $conf->config('invoice_from',$agentnum);
197 $realname =~ s/\<.*\>//; # remove email address
198 $realname =~ s/^\s*//; # remove leading spaces
199 $realname =~ s/\s*$//; # remove trailing spaces
200 # properly quote names that contain punctuation
201 if (($realname =~ /[^[:alnum:][:space:]]/) && ($realname !~ /^\".*\"$/)) {
202 $realname = '"' . $realname . '"';
204 $conf->set('invoice_from_name', $realname, $agentnum);
205 $conf->set('invoice_from', $realemail, $agentnum);
216 my $data = upgrade_data(%opt);
218 my $oldAutoCommit = $FS::UID::AutoCommit;
219 local $FS::UID::AutoCommit = 0;
220 local $FS::UID::AutoCommit = 0;
222 local $FS::cust_pkg::upgrade = 1; #go away after setup+start dates cleaned up for old customers
225 foreach my $table ( keys %$data ) {
227 my $class = "FS::$table";
231 if ( $class->can('_upgrade_data') ) {
232 warn "Upgrading $table...\n";
236 $class->_upgrade_data(%opt);
238 # New interface for async upgrades: a class can declare a
239 # "queueable_upgrade" method, which will run as part of the normal
240 # upgrade, but if the -j option is passed, will instead be run from
242 if ( $class->can('queueable_upgrade') ) {
243 my $jobname = $class . '::queueable_upgrade';
244 my $num_jobs = FS::queue->count("job = '$jobname' and status != 'failed'");
246 warn "$class upgrade already scheduled.\n";
248 if ( $opt{'queue'} ) {
249 warn "Scheduling $class upgrade.\n";
250 my $job = FS::queue->new({ job => $jobname });
251 $job->insert($class, %opt);
253 $class->queueable_upgrade(%opt);
258 if ( $oldAutoCommit ) {
259 warn " committing\n";
260 dbh->commit or die dbh->errstr;
263 #warn "\e[1K\rUpgrading $table... done in ". (time-$start). " seconds\n";
264 warn " done in ". (time-$start). " seconds\n";
267 warn "WARNING: asked for upgrade of $table,".
268 " but FS::$table has no _upgrade_data method\n";
271 # my @records = @{ $data->{$table} };
273 # foreach my $record ( @records ) {
274 # my $args = delete($record->{'_upgrade_args'}) || [];
275 # my $object = $class->new( $record );
276 # my $error = $object->insert( @$args );
277 # die "error inserting record into $table: $error\n"
283 local($FS::cust_main::ignore_expired_card) = 1;
284 local($FS::cust_main::ignore_illegal_zip) = 1;
285 local($FS::cust_main::ignore_banned_card) = 1;
286 local($FS::cust_main::skip_fuzzyfiles) = 1;
288 # decrypt inadvertantly-encrypted payinfo where payby != CARD,DCRD,CHEK,DCHK
289 # kind of a weird spot for this, but it's better than duplicating
290 # all this code in each class...
291 my @decrypt_tables = qw( cust_main cust_pay_void cust_pay cust_refund cust_pay_pending );
292 foreach my $table ( @decrypt_tables ) {
293 my @objects = qsearch({
296 'extra_sql' => "WHERE payby NOT IN ( 'CARD', 'DCRD', 'CHEK', 'DCHK' ) ".
297 " AND LENGTH(payinfo) > 100",
299 foreach my $object ( @objects ) {
300 my $payinfo = $object->decrypt($object->payinfo);
301 die "error decrypting payinfo" if $payinfo eq $object->payinfo;
302 $object->payinfo($payinfo);
303 my $error = $object->replace;
304 die $error if $error;
317 tie my %hash, 'Tie::IxHash',
319 #cust_main (remove paycvv from history)
325 #reason type and reasons
327 'cust_pkg_reason' => [],
329 #need part_pkg before cust_credit...
335 #duplicate history records
338 #populate cust_pay.otaker
341 #populate part_pkg_taxclass for starters
342 'part_pkg_taxclass' => [],
344 #remove bad pending records
345 'cust_pay_pending' => [],
347 #replace invnum and pkgnum with billpkgnum
348 'cust_bill_pkg_detail' => [],
350 #usage_classes if we have none
353 #phone_type if we have none
357 'access_right' => [],
359 #change recur_flat and enable_prorate
360 'part_pkg_option' => [],
362 #add weights to pkg_category
363 'pkg_category' => [],
369 'cust_attachment' => [],
370 #'cust_credit' => [],
372 'cust_main_note' => [],
374 'cust_pay_void' => [],
376 #'cust_pkg_reason' => [],
377 'cust_pkg_discount' => [],
382 'payment_gateway' => [],
384 #migrate to templates
385 'msg_template' => [],
387 #return unprovisioned numbers to availability
390 #insert scripcondition
391 'TicketSystem' => [],
393 #insert LATA data if not already present
396 #insert MSA data if not already present
399 # migrate to radius_group and groupnum instead of groupname
400 'radius_usergroup' => [],
404 #insert default tower_sector if not present
407 #repair improperly deleted services
411 'svc_broadband' => [],
413 #set up payment gateways if needed
416 #flag monthly tax exemptions
417 'cust_tax_exempt_pkg' => [],
419 #kick off tax location history upgrade
420 'cust_bill_pkg' => [],
422 #fix taxable line item links
423 'cust_bill_pkg_tax_location' => [],
425 #populate state FIPS codes if not already done
428 #set default locations on quoted packages
429 'quotation_pkg' => [],
431 #mark certain taxes as system-maintained
432 'cust_main_county' => [],
446 my $data = upgrade_schema_data(%opt);
448 my $oldAutoCommit = $FS::UID::AutoCommit;
449 local $FS::UID::AutoCommit = 0;
450 local $FS::UID::AutoCommit = 0;
452 foreach my $table ( keys %$data ) {
454 my $class = "FS::$table";
458 if ( $class->can('_upgrade_schema') ) {
459 warn "Upgrading $table schema...\n";
463 $class->_upgrade_schema(%opt);
465 if ( $oldAutoCommit ) {
466 warn " committing\n";
467 dbh->commit or die dbh->errstr;
470 #warn "\e[1K\rUpgrading $table... done in ". (time-$start). " seconds\n";
471 warn " done in ". (time-$start). " seconds\n";
474 warn "WARNING: asked for schema upgrade of $table,".
475 " but FS::$table has no _upgrade_schema method\n";
482 =item upgrade_schema_data
486 sub upgrade_schema_data {
489 tie my %hash, 'Tie::IxHash',
491 #fix classnum character(1)
492 'cust_bill_pkg_detail' => [],
493 #add necessary columns to RT schema
494 'TicketSystem' => [],
502 sub upgrade_sqlradius {
505 my $conf = new FS::Conf;
507 my @part_export = FS::part_export::sqlradius->all_sqlradius_withaccounting();
509 foreach my $part_export ( @part_export ) {
511 my $errmsg = 'Error adding FreesideStatus to '.
512 $part_export->option('datasrc'). ': ';
514 my $dbh = DBI->connect(
515 ( map $part_export->option($_), qw ( datasrc username password ) ),
516 { PrintError => 0, PrintWarn => 0 }
518 warn $errmsg.$DBI::errstr;
522 my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
523 my $group = "UserName";
525 if ref($part_export) =~ /withdomain/
526 || $dbh->{Driver}->{Name} =~ /^Pg/; #hmm
528 my $sth_alter = $dbh->prepare(
529 "ALTER TABLE radacct ADD COLUMN FreesideStatus varchar(32) NULL"
532 if ( $sth_alter->execute ) {
533 my $sth_update = $dbh->prepare(
534 "UPDATE radacct SET FreesideStatus = 'done' WHERE FreesideStatus IS NULL"
535 ) or die $errmsg.$dbh->errstr;
536 $sth_update->execute or die $errmsg.$sth_update->errstr;
538 my $error = $sth_alter->errstr;
540 unless $error =~ /Duplicate column name/i #mysql
541 || $error =~ /already exists/i; #Pg
545 my $error = $dbh->errstr;
546 warn $errmsg.$error; #unless $error =~ /exists/i;
549 my $sth_index = $dbh->prepare(
550 "CREATE INDEX FreesideStatus ON radacct ( FreesideStatus )"
553 unless ( $sth_index->execute ) {
554 my $error = $sth_index->errstr;
556 unless $error =~ /Duplicate key name/i #mysql
557 || $error =~ /already exists/i; #Pg
560 my $error = $dbh->errstr;
561 warn $errmsg.$error. ' (preparing statement)';#unless $error =~ /exists/i;
564 my $times = ($dbh->{Driver}->{Name} =~ /^mysql/)
565 ? ' AcctStartTime != 0 AND AcctStopTime != 0 '
566 : ' AcctStartTime IS NOT NULL AND AcctStopTime IS NOT NULL ';
568 my $sth = $dbh->prepare("SELECT UserName,
570 $str2time max(AcctStartTime)),
571 $str2time max(AcctStopTime))
573 WHERE FreesideStatus = 'done'
577 or die $errmsg.$dbh->errstr;
578 $sth->execute() or die $errmsg.$sth->errstr;
580 while (my $row = $sth->fetchrow_arrayref ) {
581 my ($username, $realm, $start, $stop) = @$row;
583 $username = lc($username) unless $conf->exists('username-uppercase');
585 my $exportnum = $part_export->exportnum;
586 my $extra_sql = " AND exportnum = $exportnum ".
587 " AND exportsvcnum IS NOT NULL ";
589 if ( ref($part_export) =~ /withdomain/ ) {
590 $extra_sql = " AND '$realm' = ( SELECT domain FROM svc_domain
591 WHERE svc_domain.svcnum = svc_acct.domsvc ) ";
594 my $svc_acct = qsearchs({
595 'select' => 'svc_acct.*',
596 'table' => 'svc_acct',
597 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum )'.
598 'LEFT JOIN export_svc USING ( svcpart )',
599 'hashref' => { 'username' => $username },
600 'extra_sql' => $extra_sql,
604 $svc_acct->last_login($start)
605 if $start && (!$svc_acct->last_login || $start > $svc_acct->last_login);
606 $svc_acct->last_logout($stop)
607 if $stop && (!$svc_acct->last_logout || $stop > $svc_acct->last_logout);