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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
#!/usr/bin/perl
die "This is broken. Don't use it!\n";
use strict;
use FS::UID qw(adminsuidsetup);
use FS::Record qw(qsearchs qsearch);
use Data::Dumper;
my @tables = qw(svc_acct svc_broadband svc_domain svc_external svc_forward svc_www cust_svc domain_record);
#my @tables = qw(svc_www);
my $user = shift or die &usage;
my $dbh = adminsuidsetup($user);
my $dbdef = FS::Record::dbdef;
foreach my $table (@tables) {
my $h_table = 'h_' . $table;
my $cnt = 0;
my $t_cnt = 0;
eval "use FS::${table}";
die $@ if $@;
eval "use FS::${h_table}";
die $@ if $@;
print "Adding history records for ${table}...\n";
my $dbdef_table = $dbdef->table($table);
my $pkey = $dbdef_table->primary_key;
foreach my $rec (qsearch($table, {})) {
#my $h_rec = qsearchs(
# $h_table,
# { $pkey => $rec->getfield($pkey) },
# eval "FS::${h_table}->sql_h_searchs(time)",
#);
my $h_rec = qsearchs(
$h_table,
{ $pkey => $rec->getfield($pkey) },
"DISTINCT ON ( $pkey ) *",
"AND history_action = 'insert' ORDER BY $pkey ASC, history_date DESC",
'',
'AS maintable',
);
unless ($h_rec) {
my $h_insert_rec = $rec->_h_statement('insert', 1);
#print $h_insert_rec . "\n";
$dbh->do($h_insert_rec);
die $dbh->errstr if $dbh->err;
$dbh->commit or die $dbh->errstr;
$cnt++;
}
$t_cnt++;
}
print "History records inserted into $h_table: $cnt\n";
print " Total records in $table: $t_cnt\n";
print "\n";
}
foreach my $table (@tables) {
my $h_table = 'h_' . $table;
my $cnt = 0;
eval "use FS::${table}";
die $@ if $@;
eval "use FS::${h_table}";
die $@ if $@;
print "Adding insert records for unmatched delete records on ${table}...\n";
my $dbdef_table = $dbdef->table($table);
my $pkey = $dbdef_table->primary_key;
#SELECT * FROM h_svc_www
#DISTINCT ON ( $pkey ) ?
my $where = "
WHERE ${pkey} in (
SELECT ${h_table}1.${pkey}
FROM ${h_table} as ${h_table}1
WHERE (
SELECT count(${h_table}2.${pkey})
FROM ${h_table} as ${h_table}2
WHERE ${h_table}2.${pkey} = ${h_table}1.${pkey}
AND ${h_table}2.history_action = 'delete'
) > 0
AND (
SELECT count(${h_table}3.${pkey})
FROM ${h_table} as ${h_table}3
WHERE ${h_table}3.${pkey} = ${h_table}1.${pkey}
AND ( ${h_table}3.history_action = 'insert'
OR ${h_table}3.history_action = 'replace_new' )
) = 0
GROUP BY ${h_table}1.${pkey})";
my @h_recs = qsearch(
$h_table, { },
"DISTINCT ON ( $pkey ) *",
$where,
'',
''
);
foreach my $h_rec (@h_recs) {
#print "Adding insert record for deleted record with pkey='" . $h_rec->getfield($pkey) . "'...\n";
my $class = 'FS::' . $table;
my $rec = $class->new({ $h_rec->hash });
my $h_insert_rec = $rec->_h_statement('insert', 1);
#print $h_insert_rec . "\n";
$dbh->do($h_insert_rec);
die $dbh->errstr if $dbh->err;
$dbh->commit or die $dbh->errstr;
$cnt++;
}
print "History records inserted into $h_table: $cnt\n";
}
sub usage {
die "Usage:\n add-history-records.pl user\n";
}
|
