[ic] Merge perge for user database
Mike Heins
mike at perusion.com
Sat Nov 8 15:06:26 EST 2003
Quoting rbragg at rickbragg.net (rbragg at rickbragg.net):
> Is there a built in way to handle merging of customer accounts?
>
> i.e. if a customer orders and does not log in, they get new accounts
> each time. If they then call in an order later, and we find that they
> have many accounts, we would like to merge them into 1.
It is really just a simple SQL procedure:
update transactions set username = 'foo' where username = 'bar';
update orderline set username = 'foo' where username = 'bar';
delete from userdb where username = 'bar';
It could be automated thusly:
<form action="[area @@MV_PAGE@@]">
master <input type=text name=master_username><br>
merge from <input type=text name=other_usernames><br>
<input type=submit value="Merge">
</form>
<br>
[perl tables="userdb transactions orderline"]
my $tdb = $Db{transactions};
my $odb = $Db{orderline};
my $udb = $Db{userdb};
Debug("Opened all dbs, udb=$udb tdb=$tdb odb=$odb");
my $master = $CGI->{master_username}
or return;
my @other = split /[\s,\0]+/, $CGI->{other_usernames};
@other = grep $udb->record_exists($_), @other;
Debug("grepped on udb");
return "Nothing to merge" unless @other;
return "No username $master" unless $udb->record_exists($master);
my $qmaster;
$qmaster = $tdb->quote($master, 'username');
Debug("quoted on tdb");
for(@other) {
my $qother = $tdb->quote($_, 'username');
my $q = "update transactions set username = $qmaster where username = $qother";
my $result = $tdb->query($q);
defined $result
or return "Failed to fully set orderline database";
}
$qmaster = $odb->quote($master, 'username');
Debug("quoted on odb");
for(@other) {
my $qother = $odb->quote($_, 'username');
my $q = "update orderline set username = $qmaster where username = $qother";
my $result = $odb->query($q);
defined $result
or return "Failed to fully set orderline database";
}
for(@other) {
my $uservalue = uneval($udb->row_hash($_));
$Tag->log({ file => 'logs/deleted_users', type => 'text'}, $uservalue);
$udb->delete_record($_);
}
my $others = join ", ", @other;
return "$others merged to $master";
[/perl]
--
Mike Heins
Perusion -- Expert Interchange Consulting http://www.perusion.com/
phone +1.765.647.1295 <mike at perusion.com>
Fast, reliable, cheap. Pick two and we'll talk. -- unknown
--
Mike Heins
Perusion -- Expert Interchange Consulting http://www.perusion.com/
phone +1.765.647.1295 <mike at perusion.com>
Nature, to be commanded, must be obeyed. -- Francis Bacon
More information about the interchange-users
mailing list