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