Creating an online 'audit' system - HELP!

[TJL2010]

Right, this is quite a complex one....for me anyway.

 

I dont have the greatest PHP knowledge but I wonder how difficult this would be:

 

We are migrating from a paper-based system where a member of staff sign's off on a 'control' to show that it has been done.

 

These can either be recurring on a daily, weekly, monthly, quarterly, 6-monthly or annual basis.

 

We want to do all the checks online so that someone signs the control electronically and their name and date is input into the database under the relevant 'control name'.

 

I'm not sure how I would structure the table but more importantly, I'm trying to think of the best way to have the recurring controls. All the user would be inputting into the DB is their name(electronic signature) and the date and IDs would go in automatically.

 

Any ideas??

[ignace]

If you could provide us with a detailed explanation I might be able to give you an idea on how complex this might be

[TJL2010]

Okay, i've given it a bit more thought and have a bit of a clearer picture now.

 

Basically the user interface structure will be:

 

Choose team > Choose 'Control Frequency' - eg. daily > Choose Control Name > Control detail page

 

I can then link a table to each Control ID with details of each electronic signature and date etc.

 

What I am trying to think about now is how to make it clear if a control has been signed off already for that period, without the user having to look at the 'control detail page' and the last 'sign-off date'.

 

Please let me know if you need more info...I'm really grateful for any advice!

[ignace]

What do you actually mean by control? Are you basically building a to-do list?

[TJL2010]

What do you actually mean by control? Are you basically building a to-do list?

 

I spose its kinda like that. Sorry - the 'control' is an 'audit action' which needs to be signed off by a member of staff to verify that it has been done.

 

The actions are recurring as per first post but essentially all that we need to store in the db is 'proof' of the action being carried out with the staff member's electronic name and the date.

 

Eg.

- Example audit action is to ensure all paperwork for a client has been filed.

- Staff member logs into this application and electronically 'signs' the control (inputs name, date etc into DB)

 

Does this make sense?

[ignace]

Ok so basically each to-do recurs by day/week/month/year. Ofcourse I don't know how you want to display this but something that may work:

 

create table task (
  id integer not null auto_increment,
  title varchar(64),
  description text,
  due enum('daily', 'weekly', 'monthly', 'yearly'),
  primary key (id)
);

create table user (
  id integer not null auto_increment,
  ..
  primary key (id)
);

create table schedule (
  id integer not null auto_increment,
  user_id integer,
  task_id integer,
  completed_on date,
  key schedule_user_id_fk (user_id),
  key schedule_task_id_fk (task_id),
  primary key (id)
);

 

Display all tasks to each user:

 

SELECT *
FROM task
WHERE id NOT IN(SELECT task_id FROM schedule WHERE user_id = $user_id AND completed_on = now())
ORDER BY due ASC

 

And then roll them out like:

Daily
----------------------
...

Weekly
----------------------
...

Monthly
----------------------
...

Yearly
----------------------
...

 

Edit: I was wrong this does not solve it

[TJL2010]

hmm that looks interesting but I'm also trying to think mainly how to automatically insert the amount of days left til the next 'sign off' or the date for the next sign-off....then I could use the now() function to display the results?

 

 

EDIT: I suppose each item could be in a category (ie. weekly, daily etc) and another field which is for the number of days to add for the next sign-off.  Eg. Daily +1.....not sure how I could do this though...