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

Help Desk Integration

(Page 2 of 4)
> > > >
 
Re: Help Desk Integration In reply to
Laugh nice script isn't it. I got to admit that Paul is coming along quite nicely.

openoffice + gimp + sketch ... Smile
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
Thanks for the bug report....I guess I forgot to show an error page for non-existant tickets Angelic

Michael:

The ticket/staff allocation was simpler than you may think. I added a departments column to the user table which lists the departments for each staff member (comma delimited). Then I just create a multiselect from the departments table of all the departments then admin can just select all the departments they want to assign to a staff member and they get joined and added to the user table eg...

Sales,Technical

....then my SQL module allows me to pass in an arrayref to construct the select query for allowing staff access to their departments....

my @depts = split /,/, $rec->{Departments};

my @list = $DB->select( undef, 'Tickets', { Departments => \@depts } )->fetchrow;

So the resulting query would be:

SELECT * FROM Tickets WHERE Departments='Sales' OR Departments='Technical';

(or you can use IN)





Last edited by:

RedRum: Jan 7, 2002, 3:29 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Quote:
The ticket/staff allocation was simpler than you may think.

Seems fairly complicated to me Tongue Splitting up the array automatically to use in the database queries. I wish I had that in my database code. My database code is basically a wrapper to make the stuff work with most software, but it doesn't really add fancy features like that.

If you are only a few days into development I think I may as well say this now... I think Eliot's suggestion may be a bit better... Saving the variables as an array seems a lot more complicated rather then an entry per department.

Thats just my opinion... I donno what other people think. I am interested though cause I use DB's a fair bit and have been stuck in this situation before.




Cheers,
Michael Bray
 
Re: [Michael_Bray] Help Desk Integration [Food for thought] In reply to
Its two lines of code how can it be complicated :)

Last edited by:

RedRum: Jan 7, 2002, 7:30 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Well it seems fairly complicated to me even though its 2 lines of code Smile Just the logic behind it. Having one row per department seems to be better database design to me.

If it works it works though. Perl and SQL let you do the same thing in so many different ways. Everyone thinks one way is better then another.
Cheers,
Michael Bray
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Quote:

departments column to the user table which lists the departments for each staff member (comma delimited)


That violates normal forms (NF1) for relational database design. You should only store one value within each column.
That is why using an intersection table (M<->M) between a Departments table and Users table is more efficient and correct. Now if you have a 1<->1 connection between "person" user to departments, then you can store the DeptID as a FK in the Users table.

Also, if you change the "name" of the department (violating normalization rules), you'd have to change all the values within the column on a continuous basis. That is why it is better to have a separate table for Departments (plus you can add additional attributes for the Departments, like central phone number, central fax number, location, etc.).

Just food for thought...it is better to create a functional and efficient database structure rather than "brute-forcing" functionality through codes.
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
And I would imagine it wouldn't work as well when you do an index if you are storing more then one value in column?


Cheers,
Michael Bray
 
Re: [Michael_Bray] Help Desk Integration [Food for thought] In reply to
If it is set up as CHAR, VARCHAR, or TEXT column, then it wouldn't matter in terms of the index searching. But still, very bad idea to store multiple values in columns...best to use JOINS between tables in terms of efficient database structure.

(wigetz/jerry su, old user of GT products, did the same thing with My Links by storing all the LinkIDs in the User table...very bad structure...he ended up changing the design of his database to reflect suggestions I provided him...discussion took place back in the summer of 2000 in the Links 2.0 Customization forum...)

Yea, the point is that, yes, you can "do it" via the above code that Paul posted, but not the best idea in terms of long-term database maintenance, functionality, etc.
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 7, 2002, 8:29 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>>That is why it is better to have a separate table for Departments<<

I do Crazy
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
My interpretation of Eliots post was that you should have 3 tables.

1) Staff
2) Departments
3) Departments_Staff_Are_Allocated_To

I stand to be corrected on that cause I'm not sure, but thats what I think is the most logical database layout.
Cheers,
Michael Bray
 
Re: [Michael_Bray] Help Desk Integration [Food for thought] In reply to
I have a departments table and users table.

Delimiting the departments in the users table is not a problem at the moment. Even if the depertments were edited then a simple update on the users table would sort it out....

In any case as I mentioned it is like 4/5 days old and I have a long way to go.

Last edited by:

RedRum: Jan 7, 2002, 8:48 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
In Reply To:
Anyway...I've said enough...this is RedRum's thread and I don't want to stray off-topic...I apologize Paul if you feel that I have strayed off-topic...I can simply delete this post...


Since he said this wasn't Links based, the whole thread is "off topic" and should be in the Perl/CGI area, actually. The only thing keeping it here _is_ the discussion of database and design since that does relate, in a way, to Links/SQL. ;)



 
Re: [pugdog] Help Desk Integration [Food for thought] In reply to
So, move it! Can't you do that??? You are a moderator, correct? Wink
========================================
Buh Bye!

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

You missed my point. The approach your taking is a very flat file approach...since you are using MySQL, you should leverage the tool and use it like it was built for, which is relational databases...

See my attachments (screenshots) - ER diagram and table views - for a visual of what I mean...Sly
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>> So, move it! Can't you do that???
I gues I could, coudn't I?

>>You are a moderator, correct? Wink
So it seems, so they tell me. Wink

But the database normalization stuff is really important to Links/SQL in general.

Maybe move this to Plugin Authors??
 
Re: [pugdog] Help Desk Integration [Food for thought] In reply to
This script is to be integrated with Links SQL so is in the correct place thanks.

>>Since he said this wasn't Links based<<

I didn't say that Laugh

Last edited by:

RedRum: Jan 7, 2002, 11:27 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Just a quick update....

I've just got email tickets working...you can send an email to helpdesk@wiredon.net ...to create a new ticket.

It isn't setup as a cronjob so you have to manually process the email tickets at the moment so to do that you you'd need to go to:

http://perlmad.com/demo/fetch.cgi

A couple of things need changing such as in the automated reply it will say [HD:new] - new will be substituted with a new id etc


Last edited by:

RedRum: Jan 7, 2002, 11:43 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
Hey,

I've had a play about today and removed the Departments column from the Users table and added a new table like so:

ID UserID DepartmentID

So now that table stores the staff allocation to different departments....seems to be working fine. Thanks.
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Good...glad that my advice helped.

BTW: You don't need to really have "ID" in the intersection table, you should simply use:

UserID (INT, Index, Not Unique)
DepartmentID (INT, Index, Not Unique)

The ID column is useless.

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

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 7:40 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
Im just having trouble with updating that table now :(

When admin selects a user profile and uses the multiselect to assign them to departments the script pushes each department id selected into an array. I also have all department id's the user is currently in, in another array.

So I need to delete rows from the staff allocation table that currently exist and that havent been selected in the form (ie if a department is removed) and also insert rows that dont already exist in the database (eg if admin adds a department to a staff member)

This is turning out to be tricky. I've managed to get it to do additions but it doesnt want to delete if I deselect a couple of departments and hit update.

Ugh.

Here's what Im using:

Code:
if (ref $FORM->{Departments} eq 'ARRAY') {

# Do any necessary updates to allocations.
my $sth = $DB->select( ['Department'], 'Allocation', { UserID => $FORM->{userid} } );

while (my $row = $sth->fetchrow_hashref) {
push @all, $row->{Department};
}

if ($#all > -1) {

for (@all) {
if (grep { !/^\Q$_\E$/ } @{$FORM->{Departments}}) {
$DB->delete( 'Allocation', { Department => $_ } );
}
elsif (grep { /^\Q$_\E$/ } @{$FORM->{Departments}}) {
next;
}
else {
$DB->insert( 'Allocation', [qw(UserID Department)], [$FORM->{userid},$_] );
}
}
}
else {
for (@{$FORM->{Departments}}) {
$DB->insert( 'Allocation', [qw(UserID Department)], [$FORM->{userid},$_] );
}
}
}

It is being a major ass.



Last edited by:

RedRum: Jan 8, 2002, 9:18 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
In order to accurately track employee responses and activities (along with work history) within a company or organization, it would be best to NOT delete them from the intersection table, because then you will also have to UPDATE the TICKETS table.

Like in the table example I gave above, you should simply use a column called EndDate (DATE, NULL, Default = 0000-00-00) to track when that employees end their tenure in that particular department.

Then in your UPDATE script ("delete" as you call it), you should simply query the intersection table to find that employee's USERID and DEPARTMENTID, then UPDATE the EndDate column.

Also, you should not be in the habit of "deleting" users from your Users table either in order to accurately track responses within your trouble ticket system. Simply reference the EndDate field in the intersection table to acknowledge whether the user account is active or not -OR- you could add another field/column in the Users table that "flags" whether the account is active or not.

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

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 9:19 AM
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
>>you should simple use a column called EndDate (DATE, NULL, Default = 0000-00-00) to track when that employees end their tenure in that particular department.<<

Either way i still need to know what to update and what not to.
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
What I would do is the following:

1) Set up a query of the intersection table, like with the following query:

Quote:

SELECT *
FROM tbl_Users LEFT JOIN tbl_User_Department ON tbl_Users.UserID = tbl_User_Department.UserID
LEFT JOIN tbl_Department ON tbl_User_Department.DepartmentID = tbl_Department.DepartmentID
WHERE (tbl_Users.UserID = $FORM->{userid})


This would produce a list like the following:

Quote:

First_Name | Last_Name | Department | Action
-----------------------------------------------
Paul | Wilson | Accounting | Edit - Delete
Paul | Wilson | Admin. | Edit - Delete


--> although again, I would really recommend not having a delete option, but you could.

Reference: http://www.mysql.com/doc/J/O/JOIN.html

2) Then set up links on the Edit and Delete text using the UserID and DepartmentID values, like the following:

http://www.perlmad.com/bin/desk.cgi?action=updateacct&UserID=1&DepartmentID=1

3) In the next query, basically, you would write an UPDATE statement, like the following:

Quote:

UPDATE tbl_User_Department
SET EndDate = NOW()
WHERE (UserID = $FORM->{userid}) AND (DepartmentID = $FORM->{departmentid})


(I am sure that you can apply the necessary Links SQL v.2.X codes with the above SQL statements I've provided.)
========================================
Buh Bye!

Cheers,
Me
 
Re: [Heckler] Help Desk Integration [Food for thought] In reply to
Hmm we may be talking about different things here.

I need to delete from this table otherwise staff allocation will get messed up. I don't understand when you say:

>>In order to accurately track employee responses and activities (along with work history) within a company or organization, it would be best to NOT delete them from the intersection table, because then you will also have to UPDATE the TICKETS table.
<<

This isn't the departments table....that is a seperate table. The allocation table is just for determining which staff member has access to which department. So if admin remove access from one department then I need to delete that row to stop the user seeing the tickets.

Also I don't need LEFT JOIN as the user record is always accessible as a hashref, $USER so I can just use WHERE

Last edited by:

RedRum: Jan 8, 2002, 10:00 AM
 
Re: [RedRum] Help Desk Integration [Food for thought] In reply to
Paul,

I am focusing on organizational management and processes (translating into a database structure with some coding), while you are still focusing and embedding yourself with code...step back and look at the larger picture here...look at how companies and organizations functions.

I mean, you are basically trying to write a script that will assist organizations with administrating, tracking, and resolving problems that their customers or other employees experience with products and services, correct?

Anyway....

Quote:

I need to delete from this table otherwise staff allocation will get messed up.


How will it be messed up? The EndDate column is the "flag" column that will allow the staff member to access different information -OR- like I have stated, add another column in the Users table.

By deleting the user out of the intersection table, you will have the following problems to deal with:

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).

Quote:

This isn't the departments table....that is a seperate table. The allocation table is just for determining which staff member has access to which department. So if admin remove access from one department then I need to delete that row to stop the user seeing the tickets.


Again, you are missing my point...

Look at the following scenarios:

Scenario 1
Employee A works in the Customer Service department for six months when she addresses 675 trouble tickets...Employee A then moves to the Human Resources department where she answers 500 trouble tickets...

Scenario 2
Employee B works in the IT department where he answers 5,000 technical support tickets, then that employee moves up to IT director position as an administrator...

How are you going to handle these two scenarios?

The best thing to do is update the ENDDATE field in the Intersection table (tbl_User_Department) signifying that the employee's tenure is over in that particular department. Then in your "access" scripts, you would check that field/column to see if it's NULL...if so, then that employee is active and associated with that department that has the set of permissions you as administrator grants him.

BTW: This ain't just hypothetical examples above, we are employing the same logic in our faculty/staff/intern/student worker/etc. systems at my job...and also within Anthro TECH.

Bye...bye...and best of luck.

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

Cheers,
Me

Last edited by:

Heckler: Jan 8, 2002, 10:15 AM
> > > >