Several hundred variations, ouch. Like I indicated earlier, between those 2 scripts, you should have covered it with the remnants being non-member donations - I could see where many may have arisen from an earlier oversight in the code which we dealt with a while ago.
How many do you have left?
Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
I had 1600 donations with 600 problems. This was down to 60 after the member name / id correction, then down to 20 after the email / id correction. I haven't done this on the live site yet, but will do over the next day or so.
Please enter your server specs in your user profile! 😢
PhoenixOffline
Joined: Mar 18, 2004
Posts: 1543
Location: Netosphere
Sorry for the delay - eye off the ball for a short while!
Another issue. This is our fault but I post it as it may save someone else searching mysql forums for a correction.
We had entered donations manually wherever someone hadn't completed the paypal ipn sequence and also for bank donations. It looks as though we've done this without 2 decimal places at times and that has thrown the donation software out rather than the supporters module:
I've just noticed that some of our older donations have gone in as user_id 127, although have no idea why. Adding a correction for this to my script you get:
<?php
//Basic report
$duffEntries = $db->sql_count($prefix."_treas_trans where user_id = '0' AND custom IS NOT NULL");
echo("There are $duffEntries entries in your treasury table where a user id is not defined and should be");
//Uncomment to go for it!
echo("<br/>Running Username Comparison<br />");
$sql = "SELECT t.id, u.user_id
FROM ".$user_prefix."_users u
JOIN ".$prefix."_treas_trans t
ON u.username = t.custom
WHERE (t.user_id = '0' OR t.user_id = '127') AND t.custom IS NOT NULL";
$result = $db->sql_query($sql);
if ($row = $db->sql_fetchrow($result))
{
do
{
$sql = "update ".$prefix."_treas_trans set user_id = ".$row['user_id']." where id = ".$row['id'];
$db->sql_query($sql);
}
while ( $row = $db->sql_fetchrow($result) );
}
$duffEntries = $db->sql_count($prefix."_treas_trans where user_id = '0' AND custom IS NOT NULL");
echo("There are $duffEntries entries in your treasury table where a user id is not defined and should be after username comparison");
echo("<br/>Running EMail Address Comparison<br />");
$sql = "SELECT t.id, u.user_id
FROM ".$user_prefix."_users u
JOIN ".$prefix."_treas_trans t
ON t.payer_email = u.user_email WHERE (t.user_id = '0' OR t.user_id = '127') AND length(u.user_email) > 4";
$result = $db->sql_query($sql);
if ($row = $db->sql_fetchrow($result))
{
do
{
$sql = "update ".$prefix."_treas_trans set user_id = ".$row['user_id']." where id = ".$row['id'];
$db->sql_query($sql);
}
while ( $row = $db->sql_fetchrow($result) );
}
$duffEntries = $db->sql_count($prefix."_treas_trans where user_id = '0' AND custom IS NOT NULL");
echo("There are $duffEntries entries in your treasury table where a user id is not defined and should be after email comparison");
?>
I would recommend to anyone else that this modified "correct 127" script is only used if "select * from cms_treas_trans where uid = '127'" reveals a list of users who are not uid 127. If not, use my previous scripts.
This might also be useful for identifying similar problems but not on 127.
SELECT DISTINCT c.id, c.custom, c.user_id FROM cms_treas_trans c JOIN cms_treas_trans t ON c.custom = t.custom WHERE c.user_id != t.user_id and c.custom != 'Anonymous';
Please enter your server specs in your user profile! 😢
1. Currently when you 'upgrade' a rank using the button in the supporters manual it returns you the first page of results. We are obviously currently trawling through nearly 90 pages of donations looking for errors - it would therefore be handy if you were returned to the page from which you started (i.e 10/92, etc). Failing that a go to page (10) button would be useful.
2. In the treasury module donations page it would be useful to have similar sort/search/navigate functions as currently within the supporters module.
3. On the user profile page where it shows donations it would be useful to have individual donations hot clickable to pop up in the treasury/donations page edit box. The reason is that I notice that a few of the bank transfers that I put in manually have the wrong date/amount and this is the easiest way to rectify it.
4. A number of our users choose not to have medals - is there a way we could have an ignore facility (also useful for moderators) to prevent them being highlighted in the supporters module.
5. Finally(!), any chance the supporters module could be sorted by those who need a rank change?
Apologies for being so demanding ...... but you did ask for feedback. Also, of course non of the above are anything beyond 'nice to have' features
There can be only one
Please enter your server specs in your user profile! 😢
PhoenixOffline
Joined: Mar 18, 2004
Posts: 1543
Location: Netosphere
The "127" issue arises from an earlier error on my part where user id was set as tinyint(1) which has an inherent maximum of 127, subsequently changed to tinyint(4).
Items 1 to 5 I'll check out - yes, I did ask for feedback, so no complaints
Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
PhoenixOffline
Joined: Mar 18, 2004
Posts: 1543
Location: Netosphere
Sorry for neglecting this - I kinda got carried away with a few other things.
In an earlier post about the 2 decimals, it was left as varchar rather than decimal, due to denominations like the Yen which are integer. However, I can fix the Your Account block to display appropriately, which is what I have done in other places. The YA block was simply forgotten.
1. I had a lot of trouble with it due to the multiple sort options - I shall have another go at it.
2. Similar sort/navigation for donations page is not so easy as each thing I tried upset the edit method, but I'll have another go.
3. Hot clickable, maybe - the nature of the edit method is awkward to link to like that.
4. Ignore facility - hmm, only simple way I can think of is to add an extra table, and cache the result?
5. Sort by need for rank change - I'll have to check how I coded it to see what scope there is to modify it.
Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
PhoenixOffline
Joined: Mar 18, 2004
Posts: 1543
Location: Netosphere
Progress:
hmm, YA block wasn't a problem - I misunderstood. I'm not keen on changing the formatting, but I guess we also don't have a lot of Yen users.
1. completed (and found and fixed a bug in $mode).
Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Cheers mate - thanks for your continued work on this.
Obviously we've now been using this module for a couple of months now and it is excellent. With a bit more experience I can say that some of the features that I suggested above are probably unnecessary. In particular I'd say that its probably not worth spending any more time on 4 and 5.
There can be only one
Please enter your server specs in your user profile! 😢
PhoenixOffline
Joined: Mar 18, 2004
Posts: 1543
Location: Netosphere
4. I'm happy to leave that one alone, though it is doable - an extra cached table storing a list of non-rankable user_id's would then replace the update link with one that linked to changing their non-rankable status.
5. would require a major re-write, abandonment is a good option. It probably has a lot less relevance now that it sorts by last donation and you have sorted all the old ones. Now that 1. works, 5. will be less value also.
Working on donations navigation (2) now - interesting challenge
Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
PhoenixOffline
Joined: Mar 18, 2004
Posts: 1543
Location: Netosphere
no, I'm still confused, but that's not unusual.
