|
J
jetacera author
I'm using PHPR 5.0 with a mySQL database. Here is what I am looking to do:
When a member clicks to open the coursediscounts_print page, I want the event to first check the discount for the frequency it can be printed per month and then check the memberdiscountsprint table for how many times in the past month that member has printed the discount. If the member has printed it equal or more than the frequency allowed, they get a message, but if they have printed it less than the frequency allowed, the record appears. If the record appears, I want the event to record the member id, discount id, course id, frequency, print date, and a status of printed into the memberdiscountsprint table.
I have 3 tables: coursediscounts, frequency, and memberdiscountsprint.
coursediscounts table has list, view and print pages and has the following fields:
did - the discount id
cid - the id of the course using this discount id
memberdiscount - the discount itself
restrictions - details about the discount
frequency - how often in a month the discount can be used (a text field - filled in with a dropdown populated by the frequency table)
status - discount is active or inactive
frequency table is used to populate a lookup table dropdown list has the following fields:
id - id of the frequency
frequency - frequency text (usually something like 4X or 1X or unlimited)
rank - a numeric field used to order the dropdown list
frequencynumber - a numeric representation of the frequency text field
memberdiscountsprint will be used in an event to record the printing of a specific coursediscount from a specific course by the logged in member. This table has the following fields:
id - the id recording the printing of a coursediscount
mid - the member id of the logged in member who printed the discount
cid - the course id of the course offering the discount
did - the discount id of the printed discount
frequency - the numric frequency that this discount can be printed out in one month
printdate - the date the discount was printed
status - printed or notprinted - used by the admin to reset a given printout so the member can try printing again
It seems that the event would have a few different tables to lookup, and that is where I'm running into trouble.
I'm thinking that first it needs to use a BeforeShowPrint event to first check the texual frequency field from the coursediscounts table using a join query to compare it to the frequency table and get the numeric frequency. Then it needs to do a count how many records in the membersdiscountprint table have both the mid of the currently logged in member and the did of the discount being printed in the past 31 days. If the counted number is less than the allowed frequency, it allows the printable page to appear. If the counted number is equal or greater than the allowed frequency, a message appears instead telling the member they have printed as many as they are allowed to for this discount.
If they can print the coursediscount, then I want a record added to the membersdiscountprint table (I'm thinking of using a BeforeMoveNextPrint event) entering the mid of the logged in user, did and cid of the current discount being printed, the numeric frequency from the frequency table equivalent to the texual frequency of the current discount being printed, the current date (now()), and a status of 'printed'.
I'm confusing myself with the different tables that need to be looked up for these events. Here is what I have so far:
For BeforeShowPrint
global $conn;
$sql = "select count(*) from memberdiscountsprint where mid=".$_SESSION["UserID"]." and did="$data["did"]" AND status ='printed' AND printdate<CURDATE() or printdate>CURDATE()-31";
$rscount=db_query($sql,$conn);
$datacount=db_fetch_numarray($rscount);
$frequency=???????
if ($datacount[0]>$frequency or $datacount[0]=$frequency)
{
echo "You have already printed this coupon the allowed frequency of $frequency times in the past month.";
return false;
}
else
return true;
For BeforeMoveNextPrint
global $strTableName, $conn;
$strSQLExists = "select * from coursediscounts inner join frequency on coursediscounts.frequency=frequency.frequency where did=$data["did"]";
$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
$strSQLInsert = "insert into memberdiscountsprint (mid, cid, did, frequency, printdate, status) values ($_SESSION["UserID"], $data["cid"], $data["did"], $data["frequency.frequencynumber"], now(), 'printed')";
db_exec($strSQLInsert,$conn);
I'm not sure how to compare the textual frequency with the numeric frequency for the BeforeShowPrint event since it is using select count, but I think I'm close on the BeforeMoveNextPrint. I think I need to get the first one working before I can test the second (though I wouldn't mind any input on both of them). I'm not sure if I'm missing anything after the "if($data)" before going to the insert query.
I would appreciate any assistance on this ASAP.
Thanks,
Janet
|
|