7 use FS::UID qw(adminsuidsetup);
11 'Circuit Ordered Date',
12 'Circuit Due Date (s)',
15 'LOCAL PORT COMPLETE',
16 'TF PORTING COMPLETE',
19 'Billed in Quickbooks',
21 #@date_fields = ( 'Custom thingie' );
23 my $dbh = adminsuidsetup(shift) or die "Usage: rt-update-customfield-dates username\n";
25 foreach my $date_field ( @date_fields ) {
27 my $cf_sql = 'SELECT id FROM CustomFields where name = '. $dbh->quote($date_field);
28 my $cf_sth = $dbh->prepare($cf_sql) or die $dbh->errstr;
29 $cf_sth->execute or die $cf_sth->errstr;
30 my $result = $cf_sth->fetchrow_arrayref
31 or do { warn "$date_field not found; skipping\n"; next };
32 my $customfield_id = $result->[0];
34 my $ocfv_sql = "SELECT id, content FROM ObjectCustomFieldValues WHERE customfield = $customfield_id and content !~ '^[0-9]+\$' ";
35 my $ocfv_sth = $dbh->prepare($ocfv_sql) or die $dbh->errstr;
36 $ocfv_sth->execute or die $ocfv_sth->errstr;
38 while (my $row = $ocfv_sth->fetchrow_arrayref) {
40 my($id, $content) = @$row;
42 my $origcontent = $content;
44 #April 21 KW / April 21 Mont
45 $content =~ s/^April (\d\d) [a-zA-Z]+$/April $1/;
47 #SAL April 29 / other May 3
48 $content =~ s/^[a-zA-Z]+ (April|May) (\d\d?)$/$1 $2/;
50 #things like "July 8/2010 and "JUNE 24/10" are not doing what we want
51 $content =~ s/^(June|July) (\d\d?)\/(20)?10$/$1 $2, 2010/i;
54 $content =~ s{^(2\d|1[3-9])/(0\d)/2010$}{$2/$1/2010};
56 my $unixdate = str2time($content); #current timezone is what we want here
58 #things like "DONE"/"ORDERED" are returning a 0 here.. should stay blank
59 my $prettynew = $unixdate ? time2str('%Y-%m-%d %T', $unixdate, 'GMT') : '';
61 print "$id: $origcontent -> $prettynew \n" unless $content =~ qr(^0\d/\d\d/2010$);
64 "UPDATE ObjectCustomFieldValues SET content = '$prettynew'".
67 my $update_sth = $dbh->prepare($update_sql) or die $dbh->errstr;
68 $update_sth->execute or die $update_sth->errstr;
69 $dbh->commit or die $dbh->errstr;