Page 1 of 2

Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 4:25 am
by simonmlewis
Hi.

I am tasked to build this system where administrators can see if a person has attended an event three times in any one year, but they must have attended over the course of 2-3 months separately.

ie. if they attended on 1st, 2nd and 3rd of August, they are not eligible.
If they attended on 28th July, 2nd and 3rd of August, they ARE eligible.

So I need to spot those dates within a year.

Their membership is then set from the date the administrator sets it. After say, 5th August when they become a member for a year. On 5th August 2016 it expires, unless within that year they have attended a further three of the above.

Not sure the best way to achieve this.
I need to show each a symbol for each attendance in that year. I think I need show only three of them (else there could be dozens unnecessarily). So the first 2 symbols would be black, and the third one, if it is within the rules (ie not a third in the same month as the other two), then it will turn them all green. Else the third would be black too.

Sounds a bit complicated so any other info you need, please tell me.

My thinking on this so far is:
Attendance registered: show black symbol.
Attendance in same month or otherwise: show black symbol.
Third attendance in same month as other two: show black symbol.
Fourth+ attendance not in same month but same year: show green symbol.

The really tough bit, I think, is it cannot be based on the current year. Because if they attended in late December... and then its a new year, it screws it up. So for the FIRST year it will be based perhaps on the date they joined?!

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 4:40 am
by requinix
Your comment about December... does that not apply to dates like July 31st and August 1st? Your description says those count separately since they're different months, but logically I doubt you would want that.

The very first thing you have to do is precisely define how you count attendance. Do you not have a set of events that you could match a person up against? If not then all you can do is guess, but you'll never know for sure whether the dates 7/31 and 8/1 should be counted as one or not. If allowable you could also change your definition to something more quantifiable, perhaps to something like
Attendance within a range of a week is considered the same event and counted once, while attendance beginning a week after the earliest event date is considered part of a new event. A "week" is either 7 calendar days or a day period like Sun-Sat or Mon-Sun.
Once you define how you count attendance, the code is straightforward.

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 4:52 am
by simonmlewis
Oddly enough, it does count, as I said:
If they attended on 28th July, 2nd and 3rd of August, they ARE eligible.
I know that sounds nuts, but I checked.

As long as at least two of the attendance are in different months in that same year, and they attend three in all, then they are eligible.

I'll tell you where I am at the moment.
You have to start with a given date. So if they have become a member (ie, they have already been thru all these dates one or more times), then $datestart becomes the last $datemembership.
If they are not a member, then $datestart becomes $datejoined.

I then pick out the Month and Date into separate variables of $datestart.

Then I am onto looking into the diary.

Code: Select all

 $querydiary = "SELECT * FROM diary WHERE memberid =:>memberid AND dateattended >='$datestart' ORDER BY dateattended ASC";
  $resultdiary = $pdo->prepare($querydiary);
  $resultdiary->execute(array(':memberid' => $row->memberid));
while ($rowdiary = $resultdiary->fetch(PDO::FETCH_OBJ)) 
{

}
And this is where I am up to. I need to check each date they attended. But where I go from there, I'm not sure yet.

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 5:03 am
by simonmlewis
I had considered this - note the last line of code.

Code: Select all

$membersymbol = 0;
$datestart = NULL;
if ($row->datemembership == NULL) { $datestart = $row->datejoined;}
else { $datestart = $row->datemembership;}

// Find month from datestart
$datestartMonth = substr($datestart, 6, 2);
// Find year from datestart
$datestartYear = substr($datestart, 0, 4);
echo "$datestartYear, $datestartMonth";
 $querydiary = "SELECT * FROM diary WHERE memberid =:>memberid AND dateattended >='$datestart' ORDER BY dateattended ASC";
  $resultdiary = $pdo->prepare($querydiary);
  $resultdiary->execute(array(':memberid' => $row->memberid));
while ($rowdiary = $resultdiary->fetch(PDO::FETCH_OBJ)) 
{
  if ($rowdiary->dateattended >= $datestart) { $membersymbol ++;}
}
The problem here is that it will do it anyway, even if it was 3 days in a row.
If I ask if it is the 2nd count, or 3rd count, and the attendance is still within the one month, how do I ask it to keep going until it finds out outside of the month.

PLUS - I need to include if one of them is in the same month.
It's damned complicated, and as it's also kind of contradictory, I don't know how to tell PHP to query it.

In our heads, it's easy. But in PHP I'm not so sure.

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 5:23 am
by simonmlewis
Ok I've just been out for a walk, as it often helps me think better.
Rather than concentrating on each row as it comes, why not use an array.
I have no idea about arrays, never programmed them, however, if I could put each "date" or perhaps just the month, into an array, and then query the array.

Asking it if any two of the numbers differ.... then we have success.

I can tell the initial query to only check from the $datestart to $dateend, setting $dateend to be a year on from $datestart.

But how would I put the rows months into an array, and then how would i ask the array the all important question?

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 5:53 am
by simonmlewis
Guessing here but:

Code: Select all

 $querydiary = "SELECT * FROM diary WHERE memberid =:>memberid AND dateattended >='$datestart' ORDER BY dateattended ASC";
  $resultdiary = $pdo->prepare($querydiary);
  $resultdiary->execute(array(':memberid' => $row->memberid));
  
  $arrayDateAttend = array();
  $index = 0;
  
while ($rowdiary = $resultdiary->fetch(PDO::FETCH_OBJ)) 
{
$dateattendmonth = substr($rowdiary->dateattend, 6, 2);
  $arrayDateAttend[] = $dateattendmonth;
}
Is this correct, will it put each month into an array?
If so, how do I then query that array to ask it if there are differences?

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 6:14 am
by Celauran
As it needs to be within a one year period, does it make sense to add an upper bound to the rows you're selecting? Otherwise, your array approach seems like it could work.

Is it possible for a member to have more than three entries in a given year? What if I have, say, August 2, 3, 4, and September 5. Is that OK?

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 6:32 am
by simonmlewis
Oh yes. It's a sporting venue, so they could go 12 times a year. 20 times a year.
Think of it like a Cinema: if you go three times over the course of at least two months, you get a discount.

So it has to be within that year lower/upper bracket. Not within "2015, but within 5/8/15 > 5/8/16.
So have I set it correctly to insert that month into the array? If so, how do I query it's contents to see if any two are different numbers?

Code: Select all

 $querydiary = "SELECT * FROM diary WHERE memberid =:memberid AND dateattended >= '$datestart' ORDER BY dateattended ASC";
  $resultdiary = $pdo->prepare($querydiary);
  $resultdiary->execute(array(':memberid' => $row->memberid));
  
  $arrayDateAttend = array();
  $index = 0;
  
while ($rowdiary = $resultdiary->fetch(PDO::FETCH_OBJ)) 
{
$dateattendmonth = substr($rowdiary->dateattended, 6, 2);
echo "here: $dateattendmonth<br/>";
  $arrayDateAttend[] = $dateattendmonth;
}
This echoes:
[text]here: 7-
here: 8-
here: 8-
here: 8-[/text]
So there are two months here. How do I ask the various IF they are two different months?

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 7:33 am
by Celauran
Check if a given month is already in the array before adding it. Something like

Code: Select all

while ($rowdiary = $resultdiary->fetch(PDO::FETCH_OBJ))
{
    $dateattendmonth = date('m', strtotime($rowdiary->dateattended));
    if (!in_array($dateattendmonth, $arrayDateAttend)) {
        $arrayDateAttend[] = $dateattendmonth;
    }
}

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 8:13 am
by simonmlewis
Brilliant. So there isn't dozens of the same one. Then, how do I establish there is more than one in the array?
ie. if it's 8, 9, 10... it means there are more than one month and thus, we have bingo!

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 8:22 am
by Celauran

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 8:36 am
by simonmlewis
Fantastic. Works a treat.

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 9:06 am
by simonmlewis
I've added a count to the field too, so if there is just One entry or Two Entries, it shows the Symbol once or twice.
If there are three entries AND that array shows "2" or more, then there are three symbols in Green. But if there are three entries (or more) and that array still shows 1 or less, it shows three symbols but only in black.

Damn brilliant. I woke up this morning not having a clue how I would achieve this. Went for a while still without a clue but that array idea popped into my head. And it's come to fruition.

With your guidance - thanks.

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 10:06 am
by simonmlewis
Here's a problem then.
So at the end of the year, their $datemembership has expired.
Yet the code will look at that date.
So how should I make it "start over"?

Maybe use a "datemembershipexpired" field which automatically gets populated when they become a member... and use that? Mmmmm.

Re: Advice on Calculating Attendance within dates needed

Posted: Wed Aug 05, 2015 10:14 am
by Celauran
From your earlier posts, isn't their membership renewed if they attend three or more events over two or more months? So the membership date will be updated. Use last year's, check if they're eligible for renewal, update with new start date.