[ic] Merge perge for user database

Korey G korey at detroitweb.net
Mon Nov 10 01:33:04 EST 2003


Always a soothing voice of wisdom

-----Original Message-----
From: interchange-users-bounces at icdevgroup.org
[mailto:interchange-users-bounces at icdevgroup.org] On Behalf Of Mike
Heins
Sent: Saturday, November 08, 2003 12:06 PM
To: interchange-users at icdevgroup.org
Subject: Re: [ic] Merge perge for user database

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
_______________________________________________
interchange-users mailing list
interchange-users at icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users



More information about the interchange-users mailing list