Ben E Lou
11-28-2008, 06:44 AM
PROBLEM: There are several places in FOF where transactions do not take place in the "current" stage. For example, if I'm about to run FA1:4, when I import the files, all renegotiations, failed renegotiations, players released, and RFA signings/failed signings take place right then, before I have run the stage, in FA1:3.
DESIRE: Without having to code for every single possible case where a transaction takes place in the previous stage, I'd like to be able to show those transactions in a single-stage report with no manual intervention other than running DBUpdater.
CURRENT NON-IDEAL AUTOMATED SOLUTION: I have created a custom table (lasttransaction) that contains the last Transaction ID that was previously run. At the very end of my transaction php script, I am updating the Transaction ID in that table as follows:
INSERT INTO lasttransaction
SELECT MAX(t.index)+1, MAX(a.id)
FROM lasttransaction t, fof_transactions a
So then the WHERE clause of my main transaction query is.... WHERE type<>28 AND t.id>(SELECT max(transactionid) FROM lasttransaction)
The drawback to that solution is that I see the correct transactions when I load the transaction script once...but I can never load it again since it updates the last transaction at the end of the script.
ALTERNATE NON-IDEAL MANUAL SOLUTION: I can just remove the code from the end of the transaction script, and run it manually right before updating the database. This solves the issue of not being able to see the transactions again, but it has two other drawbacks that I can see:
1. I'd have to run it manually before updating the database every stage.
2. If I were to forget to run it manually before updating the DB, I'd have to go in, locate the right transaction ID, and
manually insert it into lasttransaction.
So, the question here is this: is there a way to do this, keep it completely automated, but still be able to view the last batch of transactions? What I'm thinking here is something along the lines of
IF ($transactiontablemaxtransactionid==$lasttransactionmaxtransactionid)
{$query="{the stuff I need}
WHERE type<>28 AND t.id>(SELECT max(transactionid) FROM lasttransaction)";}
ELSE {$query=SELECT {the stuff I need}
WHERE type<>28 AND t.id>{however the heck you determine the penultimate value from the lasttransaction table";}
Am I on the right track there, or is there a better way to approach it? And if I am on the right track, how would I determine in a WHERE clause that penultimate value?
DESIRE: Without having to code for every single possible case where a transaction takes place in the previous stage, I'd like to be able to show those transactions in a single-stage report with no manual intervention other than running DBUpdater.
CURRENT NON-IDEAL AUTOMATED SOLUTION: I have created a custom table (lasttransaction) that contains the last Transaction ID that was previously run. At the very end of my transaction php script, I am updating the Transaction ID in that table as follows:
INSERT INTO lasttransaction
SELECT MAX(t.index)+1, MAX(a.id)
FROM lasttransaction t, fof_transactions a
So then the WHERE clause of my main transaction query is.... WHERE type<>28 AND t.id>(SELECT max(transactionid) FROM lasttransaction)
The drawback to that solution is that I see the correct transactions when I load the transaction script once...but I can never load it again since it updates the last transaction at the end of the script.
ALTERNATE NON-IDEAL MANUAL SOLUTION: I can just remove the code from the end of the transaction script, and run it manually right before updating the database. This solves the issue of not being able to see the transactions again, but it has two other drawbacks that I can see:
1. I'd have to run it manually before updating the database every stage.
2. If I were to forget to run it manually before updating the DB, I'd have to go in, locate the right transaction ID, and
manually insert it into lasttransaction.
So, the question here is this: is there a way to do this, keep it completely automated, but still be able to view the last batch of transactions? What I'm thinking here is something along the lines of
IF ($transactiontablemaxtransactionid==$lasttransactionmaxtransactionid)
{$query="{the stuff I need}
WHERE type<>28 AND t.id>(SELECT max(transactionid) FROM lasttransaction)";}
ELSE {$query=SELECT {the stuff I need}
WHERE type<>28 AND t.id>{however the heck you determine the penultimate value from the lasttransaction table";}
Am I on the right track there, or is there a better way to approach it? And if I am on the right track, how would I determine in a WHERE clause that penultimate value?