0

Hi,
I am developing a system that need to monitor machines' licence, so i have a field 'movedate' and 'outdate'
every machines can only been kept for 2 years for most.
and if the machine didn't go out for almost 2 years, an alert will come out to remind the tenant.
I want the alert will come out about 2 weeks before the expire date.

But Im not sure how to do it..can someone guide me?

3
Contributors
8
Replies
91
Views
4 Years
Discussion Span
Last Post by diafol
0

Assuming you access the page every day, you can access those about to expire (2 weeks) and expired with SQL

SELECT * FROM table WHERE DATEDIFF(renewdate, CURDATE()) BETWEEN 0 AND 14   [for about to expire]*

SELECT * FROM table WHERE CURDATE() > renewdate [for expired]

*That would show the records every day for the 14 days until expiry. I'd have an renew_status field or something to say what action had been taken (e.g. email, letter etc), so once a written letter had been sent, the field would read say '4' and then they wouldn't show up any longer in the list, e.g.

SELECT * FROM table WHERE renew_status < 4 AND DATEDIFF(renewdate, CURDATE()) BETWEEN 0 AND 14

I don't udnerstand why you have 2 dates for checking movedate/outdate - or is renewdate = outdate and movedate = startdate?

Edited by diafol

0

I want to do it like this : there is a list of the machines' licence in a table:

machine  |  received date  |  expiry date  | consignee  |  notes
------------------------------------------------------
T15D001  |  2010-06-24     |  2013-06-24   | Haliburton | ALERT   
T15D089  |  2010-09-11     |  2013-09-11   | KSB        |  

the received date == moveDt
the lifetime for each machines is 2 years.
this page is for the admin to monitor.

0

So? does my idea work for you? Use 'expiry_date' instead of 'renewdate' in my code.

However...

the lifetime for each machines is 2 years.
2010-06-24 to 2013-06-24

Isn't that 3 years?

0

sorry for the late reply.So, i just need write this code :

    SELECT * FROM table WHERE DATEDIFF(renewdate, CURDATE()) BETWEEN 0 AND 14 [for about to expire]*
    SELECT * FROM table WHERE CURDATE() > renewdate [for expired]

I want to create a popup or (same as my table example:the message will appear in the last column) which will appear every time they open the page, and this file will have a code to fetch the records from database and compare the date in the database with the current date and if it matched then alert a message.

for the date example, sorry, it supposed to be only 2 years.

Edited by sagisgirl

0
<?php
$r = mysql_query($q);
$output = '';
if(mysql_num_rows($r))
{

    while($d = mysql_fetch_assoc($r))
    {
        if($d['diff'] < 0)
        {
            $notes = "EXPIRED";
        }elseif($d['diff'] <= 14){
            $notes = "ALERT";
        }else{
            $notes = '';
        }

        $output .= "<tr><td>{$d['machine']}</td><td>{$d['received_date']}</td><td>{$d['expiry_date']}</td><td>{$d['consignee']}</td><td>$notes</td></tr>";
    }

}
?>

<table>
    <thead>
        <tr>
            <th>Machine</th>
            <th>Received Date</th>
            <th>Expiry Date</th>
            <th>Consignee</th>
            <th>Notes</th>
        </tr>
    </thead>
    <tbody>
        <?php echo $output;?>
    </tbody>
</table>

Supply the SQL you require for the $q variable. You could even do the conditional inside the SQL so that you could ORDER BY the result of the conditional, but that's outside the scope of this at the moment.

Edited by diafol

0

Hi...I need to change some database..
so...Is it possible if i didn't have 'expiry_date' column in my database?
I want PHP to calculate the expiry date, from the time the goods is received(received_date)?

0

Thanks for resurrecting this dead thread. Start your own and supply more info. What have you tried? Show any code you have written so far. If you.ve done nothing and just want somebody to do it for you then state how much you.re prepared to pay.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.