Good Morning All,
I'm about to write a script that will actually be run in a CRON job weekly. I created an overview of the process so you would understand what I'm doing but I have questions with 2 of the MySql queries that will be required, and wondered if someone could help me get them formed correctly...
trans_earnings table holds individual earnings transactions that are generated at time of product sales
this script is intended to accumulate all the individual earnings and create a single weekly distribution record for them.
These are the basic query structures that I plan to use, but need help with defining the 'Previous Saturday at Midnight' in the first UPDATE query
And the Group by clause so I can get a count of the earnings transactions for a member along with a total of those transactions, in the first SELECT query.
I think with a little assistance in those areas, I'll be able to accomplish this task.
But if you have a suggestion regarding a better means of managing this process, I'd be open to hearing that as well.
Thanks in advance for your help
UPDATE trans_earnings SET status = ‘P’ WHERE status = ‘E’ AND earned_date < previous Saturday at midnight (datetime field) AND abbrev = ’FSB’ //(this should MARK all new earnings trans matching that code, that were generated last week, for processing) //Then I need to do a query that will get the totals per mem_id, but not the individual transactions SELECT earnings_id, mem_id, earned_date, abbrev, amount FROM trans_earnings WHERE status = ‘P’ ORDER BY mem_id //With a count of the earnings_id - GROUP BY mem_id //and a total of the amount – GROUP BY mem_id //So I can take those results, and generate a single transaction for each member for distribution of the funds INSERT into trans_distribute (dist_id, mem_id, create_date, earnings_ct, amount, status) VALUES(‘’, mem_id, $created, earnings_count, earnings_total, ‘P’); //Then query to get the dist_id that was just generated SELECT dist_id FROM trans_distribute WHERE mem_id = $mem_id AND status = ‘P’ LIMIT 1 //and as I generate each distribution record, I need to update all records in the trans_earnings table that belong to that mem_id, setting the status to 'D'istributed, and setting the dist_id field to the dist_id just received from the query. UPDATE trans_earnings SET status = ‘D’, dist_id=$dist_id WHERE mem_id = ‘$mem_id’ AND status = ’P’