need help with edit code

[johan]

I'm working on a db for a little library at work.

Our staff can book DVD's and books.

In mysql now

I have a table dvd - a table users and a table registratie

I've created a form (users) with a subform (registratie). In the subform the user can make a reservation. Here i use a lookup (to dvd) and some date fields (from - to) where the user has to give 2 dates for every dvd he want.

This work's fine but how can I prevent that a user makes a reservation on a dvd that is already reservated by a college? I try to explain by an example

person A made a reservation for dvd 1 to use on 01/04/2011 and he will need it until 15/04/2011.
B want's to make a reservation for the same dvd1 tot use on 5/4/2011 and he will need it until 15/4/2001.
Actualy I'm looking for a sollution so B wan't be able to reservate dvd1 and get's a message that the dvd isn't available.

I hope someone has an idea?

[admin]

johan,

You might want to start with making some rules like.. how long can someone borrow it for?
If there are rules it will be easier to check (and borrow) as a standard block of days needs only a start date.
Its unlikely a library would not have rules and so its best to work around these.

Steven

[johan]

Steven,

Thanks for your reply.

Does it make a difference when I choose for a rule (max 7days) or when i fill in 2 dates (non blanks)?

If i set up the rule max 7 days how can I check if the dvd is available?

Thanks,

Johan

[admin]

Johan,

The form should be based on 'registratie', not 'users'.
Simply with
a lookup for the user
a lookup for the dvd
a start date
and (if you have to), an end date.
AND a notes textarea that can hold the explanation of any duplication.

Otherwise every record on the subform will need to be validated for every subform record, every time the user record is saved. And its not the user record that should be getting saved because nothing is changing on it.

Then put this - or something like it - in 'After Save' of the Form.

Code: Select all

$theid      = '#newID#';                  //-- the form id

$s          = "SELECT *, DATEDIFF(reg_from,reg_to) AS the_days ";
$s         .= "FROM registratie WHERE registratie_id = '$theid'";
$t          = nuRunQuery($s);
$r          = db_fetch_object($t);     
//-- get the days that it needs to be checked for 
//-- and the date to start the check from

for($i = 1 ; $i <= $r->the_days ; $i++){   //-- loop through these days

    $S      = "SELECT *, DATE_ADD('$r->reg_from', INTERVAL $i DAY) AS this_date FROM registratie ";
    $S     .= "WHERE registratie_id != '$theid' AND '$r->reg_dvd_id' = reg_dvd_id ";
    $S     .= "AND DATE_ADD('$r->reg_from', INTERVAL $i DAY) BETWEEN reg_from AND reg_to";
    $T      = nuRunQuery($S);
    $R      = db_fetch_object($T);
    if($R->reg_from != ''){                   //-- check for a matching date
        $n  = "This product is already reserved\n on $R->this_date";
        $u  = "UPDATE registratie SET reg_note = '$n', reg_from = NULL, reg_to = NULL ";
        $u .= "WHERE registratie_id = '$theid'";
        nuRunQuery($u);    //-- update dates to null and a message explaining where the match is
        return;
    }
}

(You should be able to check for free dates for a DVD back on the browse screen before trying to save a record anyway.)

Regards

Steven

[johan]

Steven,

Thanks for your reply.

I'll try it as soon as possible and let you know the result.

Regards
Johan

[admin]

OK

[johan]

Steven,

I've tested your solution but nothing happens. Or am I doing something wrong or is there a bug in the code? Anyway, in this solution can be only one on one (1 DVD per user) per form . I would like the possibility to book multiple DVDs in the same form. That's why i've tried ​​a subform. Maybe your proposal to filter the search screen, is a better solution.
Can you help me with it?

I have some new questions.

1. I've made a form with a subform. In my browsescreen, I wan't to see how many items are selected in my subform.
This by using: select count (pc_cl_id) from planningclient where pc_plan_id = '# id # .

I created a field (plan_aantal) in my table planningdata (where the data of the form is in) a field that should always update when a row is added in the subform. Any suggestion?

2. Is there a way to reduce the number of records that you can fill in the subform limit?
In principle, one can not enter more than 3 records.
Thanks again for your help.

Kind regards,
Johan

[admin]

Johan,

I've tested your solution but nothing happens. Or am I doing something wrong or is there a bug in the code?

There is no point just saying it doesn't work and might have a bug.

If you are just pasting this in without trying to understand the code, I'm not being of any help to you.

If there is a bug you need to be able to find where it is.

You can do this by using the function nuDebug() to check different results at different points in the code (you will find the results of this function in the table zzsys_trap.

(http://wiki.nubuilder.com/tiki-index.ph ... g_pString_)

Anyway, in this solution can be only one on one (1 DVD per user) per form

The solution I suggested allows any number of people, any number of DVDs but they will be recorded 1 registration at a time.

I would like the possibility to book multiple DVDs in the same form. That's why i've tried ​​a subform

To try and properly validate a number of subform records at a time is not something I have the time write the code for. Maybe you could get paid support from nuSoftware http://www.nusoftware.com.au/home.html.

Steven

[johan]

Steven,

Thanks a lot for your reply. Seems I still have to learn a lot about Nubuilder en it's futures.

Johan

[johan]

Steven,
Meanwhile I found the solution to my problem. The problem was in datediff. I'll place the code here, maybe she's still useful to others.

Regards,
Johan

Code: Select all

    $theid      = '#newID#';                  //-- the form id

    $s          = "SELECT *, DATEDIFF(reg_to,reg_from) AS the_days ";
    $s         .= "FROM registratie WHERE reg_id = '$theid'";
    $t          = nuRunQuery($s);
    $r          = db_fetch_object($t);     
    //-- get the days that it needs to be checked for
    //-- and the date to start the check from

    for($i = 1 ; $i <= $r->the_days ; $i++){   //-- loop through these days

        $S      = "SELECT *, DATE_ADD('$r->reg_from', INTERVAL $i DAY) AS this_date FROM registratie ";
        $S     .= "WHERE reg_id != '$theid' AND '$r->reg_dvd_id' = reg_dvd_id ";
        $S     .= "AND DATE_ADD('$r->reg_from', INTERVAL $i DAY) BETWEEN reg_from AND reg_to";
        $T      = nuRunQuery($S);
        $R      = db_fetch_object($T);
        if($R->reg_from != ''){                   //-- check for a matching date
            $n  = "This product is already reserved\n on $R->this_date";
            $u  = "UPDATE registratie SET reg_note = '$n', reg_from = NULL, reg_to = NULL ";
            $u .= "WHERE reg_id = '$theid'";
            nuRunQuery($u);    //-- update dates to null and a message explaining where the match is
            return;
        }
    }