Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Help Desk Integration

(Page 3 of 4)
> > > >
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>>
1) Loss of context since if you delete a staff person out of the table, you'll also have to delete all tickets that the person has responded to...(think in terms of discussion forums or message boards).

2) Loss of tracking mechanism (similiar to #1, but more mechanical with a bit organic meaning that you will not be able to see who responded to who and who said what to who).
<<

Thats the thing...that wouldnt happen....deleting from the staff allocation table wouldn't require me to delete the tickets the staff member has responded to - deleting from the staff allocation table just means that tickets for the deleted department won't show for the staff member which is what I want.

I guess it's hard to fully understand what going on from a few post so we probably just have crossed wires.
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Fine...

But in terms of tracking which department that the staff member/employee works for, if you delete them from the intersection table, then you won't have any idea where that person worked for in the past..much better to use a "flag" column to denote active/inactive in that department (and yes, employees can work in many different departments at the same time and at different times), then to outright disassociate that staff member from that department.

Think contextually...the past, the present, the future, and all stops in-between.


========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 11:01 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
I appreciate where you are coming from. I guess I could either do as you said regarding a timestamp or just add a column called "Active" or something so admin could view departments that staff currently work in (Active = 1) or past departments (Active = 0)
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Timestamps would be better so that you could see a history of when that employee worked in that department (heck, a start timestamp would be good as well)....

Like:

Quote:

UserID (INT, Index, Not Unique)
DeptID (INT, Index, Not Unique)
Start_Date (DATETIME)
End_Date (DATETIME)


So, you'd see output like the following:

Code:

Employee | Department | Start Date | End Date
--------------------------------------------------
Wilson, Paul | President's | 1995-01-01 | 2001-01-01
| Human Res | 1991-05-01 | 1994-11-12
Dumbie, Bee | Vice Pres | 1992-01-01 | 2001-01-07


Rather than what you propose:

Code:

Employee | Department | Active
--------------------------------------------------
Wilson, Paul | President's | Yes
| Human Res | No
Dumbie, Bee | Vice Pres | No


Of course, there are tons of other attributes, like reason for dismissal, reason for suspension, Phone extension (specific to that person's tenure in whatever department), etc. you could add to the intersection table.

========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 11:29 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
That looks good....Im still having a nightmare getting it to update properly in the first place though :(

Here's what's going on....

Admin selects a user profile to edit, on that page is the record from the users table along with a multiple select of all departments from the departments table. Now by this point I've already done a select from the staff allocation table to get the names of the departments they currently work for and so these are pre-selected.

So say we have departments 1 2 3 4 5 and Staff Member A works for 1 2 3 then on the profile page it would show:

1 Selected
2 Selected
3 Selected
4
5

Now if admin decides to add department 4 I need the code to skip 1 2 3 (as this is already in the database) and insert the 4th department into the staff allocation table. Vice versa....if department 3 is removed for example I need to skip 1 and 2 and then deactivate 3.

I can't get this to work at all :(


Last edited by:

RedRum: Jan 8, 2002, 11:36 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Quote:
Now if admin decides to add department 4 I need the code to skip 1 2 3 (as this is already in the database) and insert the 4th department into the staff allocation table. Vice versa....if department 3 is removed for example I need to skip 1 and 2 and then deactivate 3.


Correct me if I misunderstanding you, but are you AUTOMATICALLY adding deptids into the intersection table anytime you add a new department??? Not a smart idea.

The only time that the intersection table should be updated is:

1) When an employee is added to a department, or;
2) When the employee leaves a department;

You do not need to have all departments associated with userids in the intersection table.

Got it?

Also, for UPDATING the tbl_User_Department intersection table when a department is deleted (or userid is deleted, although again, not recommended), all you have to do is:

1) If you are using this with Links SQL, then you can specify the DepartmentID/UserID as foreign keys and have them automatically deleted when you delete the primary key, can't you??

2) If you aren't using this with Links SQL, then you can add an additional delete SQL statement...

For example:

DELETE FROM tbl_User_Departments WHERE (DepartmentID = $FORM->{deptid})

DELETE FROM tbl_Departments WHERE (DepartmentID = $FORM->{deptid})

See??? Basically, it is always wise to delete rows out of intersection tables before deleting the rows out of the table where the PRIMARY keys (PK) are located.

Basically, to maintain referential integrity between tables, there won't be a method of keeping the "department" reference in the intersection table after you have deleted the row in the tbl_Department table. So, the best thing to do is outright delete the row in the intersection table.
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 11:57 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>>but are you AUTOMATICALLY adding deptids into the intersection table anytime you add a new department<<

Not when I add a new department...only when a department is added/removed from a staff members profile.

When I said "add a department" above I meant add it to a staff member profile.

Last edited by:

RedRum: Jan 8, 2002, 11:56 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Read my revised reply above....
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>>
For example:

DELETE FROM tbl_User_Departments WHERE (DepartmentID = $FORM->{deptid})

DELETE FROM tbl_Departments WHERE (DepartmentID = $FORM->{deptid})
<<

Its not that simple though (in terms of the perl code) as multiple departments can be selected per user so I need to skip existing departments if new ones are added or delete them if they are deselected. Otherwise mysql will spew errors about duplicate keys.

Its really the perl side of things that are causing the problems at this point rather than the table structure.
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Use IN then....for multiple keys being selected.

Good luck....
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
That wont work either.

Lets see if this can shed some more light for you.....

If Staff Member A has access to department 1 2 and 3 then the allocation table will look like:

Code:
UserID DeptID
1 1
1 2
1 3

So 1 2 and 3 will be pre-selected in the profile form. Now if admin decides to add them to department 4 then the array passed to the script when admin hits update will be the 3 preselected values 1 2 and 3 but also 4. If I use IN and pass in the array I'll get duplicate key errors for 1 2 and 3 as all that needs to be inserted is 4

Same with de-selects. If 1 2 and 3 are selected and admin deselects 3 and hits update then I only need to delete 3 not 1 and 2.

I need some way to match two arrays and push any non-matches into a third array...

eg....All depts staff member A belongs to:

@array1 = qw( 1 2 3 );

...all selection from the form....

@array2 = qw( 1 2 3 4);

....now if I can get rid of duplicates and push 4 into a new array then I can to the insert/delete without a problem.



Last edited by:

RedRum: Jan 8, 2002, 12:13 PM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Can't you just apply the KISS principle here? Instead of comparing arrays, generate two select menus.

1) Departments_To_Add
2) Departments_To_Remove

Thats what I would do.

Cheers,
Michael Bray
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
 

Last edited by:

RedRum: Jan 8, 2002, 1:05 PM
 
Re: [Michael_Bray] Help Desk Integration [Food for thought] In reply to
Ahhh hmmm I may just give that a whirl - thanks michael!

You can get so aggitated by something that you forget the obvious!
Post deleted by RedRum In reply to

Last edited by:

RedRum: Jan 8, 2002, 1:47 PM
 
Re: [Michael_Bray] Help Desk Integration [Food for thought] In reply to
If you want to see how I implemented it you can check the demo:

http://www.perlmad.com/...i?se=1&userid=16
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Looks good, Paul...

Well, at least I tried helping you...hopefully not too much a waste of time...at least for future Links SQL users trying to create plugins and add-ons to their database.

Good luck with your project...
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 2:19 PM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
Thanks - I do appreciate your help and implemented a couple of your suggestions...but Michael's idea was just what I needed regarding my problem.


Last edited by:

RedRum: Jan 8, 2002, 2:24 PM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Always has to be a but, huh, Paul?? Anyway, glad you figured it out anyway...
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
Why do you have to be like that?

There was no malice in my post at all ....I said I was grateful...Unsure

Last edited by:

RedRum: Jan 8, 2002, 2:40 PM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
(think of the words --> 'sort of', 'kind of', etc.) Wink

Fine...I'll let it lie...important issue is that you solved your problem...

Good luck and goodbye.

========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 2:44 PM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Just a quick feature update:

Staff/Members/Admin can now create signatures and set a default for posts.

URL is added to the signup/profile page. (Optional)

Template Global editor added in admin. (Tried not to make it look to much like GT's but failed).

Ticket Status Option Added

Fixed the database issues yesterday so admin can add/delete departments to staff profiles.

If admin assign another administrator they are automatically added to the .htpasswd file

Added a preview column for announcements so that the preview shows up and if someone wants to read the full announcement it shows on another page.

Added refreshes after doing things like adding posts etc so you dont have to keep clicking (refresh rate is an admin option)

Hmm think thats all for now.


Last edited by:

RedRum: Jan 9, 2002, 8:15 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Quote:

If admin assign another administrator they are automatically added to the .htpasswd file.


Few questions:

1) Do you have different permission setting established (similar to GT forums, like Moderator, Administrator, etc.)?

Might be a good idea to have different permission groups and then assign users in those groups. I think it is problematic to have more than one top dog (administrator). The problem I see is that the new administrator can easily delete out the other administrator, which would allow the new admin to rave havic on the system.

2) Why are you using a .htaccess/.htpasswd system?

It would be better to control access via built-in authentication of Links SQL for administrators to access the administrative script rather than using .htaccess/.htpasswd. Also, some users have a lot of problems creating and maintaining this file system. And some hosting companies don't allow it.

3)Okay...so you're using the htaccess/htpasswd system...Do you also have a mechanism built-in to delete and edit admin accounts in the .htpasswd file?

The problem is not adding new lines into the .htpasswd, but maintaining it, so that legitimate users can only access the system. I've subscribed to many web sites and have gotten away with continually accessing the sites after my subcription ended due to poor maintenance of the .htpasswd file.
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>>The problem I see is that the new administrator can easily delete out the other administrator, which would allow the new admin to rave havic on the system.
<<

Well not many admins are going to allow someone access who they think are going to trash their site.

>>Okay...so you're using the htaccess/htpasswd system...Do you also have a mechanism built-in to delete and edit admin accounts in the .htpasswd file?
<<

Of course.

>>2) Why are you using a .htaccess/.htpasswd system?

It would be better to control access via built-in authentication of Links SQL for administrators to access the administrative script rather than using .htaccess/.htpasswd. <<

This isn't an addon to Links SQL using the Links SQL code - it is a _full_ seperate script that is being distributed to everyone not just Links SQL users. Therefore htaccess was a good option for admin security. Staff/Members login via the script and not basic auth...only admin.

Last edited by:

RedRum: Jan 9, 2002, 8:52 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Quote:

This isn't an addon to Links SQL using the Links SQL code.


Then, I agree with pugdog, this thread should really be moved the Perl/CGI Forum.

At the point that you are integrating the script as a true plugin, then another post in the Links SQL Plugins folder would be appropriate, IMO.
========================================
Buh Bye!

Cheers,
Me
> > > >