This project is read-only.

How to show who the ticket is assigned to

Aug 27, 2010 at 2:27 PM

I have a question on the mygroupsTickets linq to sql.

Is there a way to modify the linq code so that when a ticket is assigned to someone the grouptickets section shows the persons name that it got assigned to.

Below is the code that I modified slightly for the function I'm using it for. Thanks.

p.s. Your product is excellent.  I modified it so that it can accept tickets in active directory and non active directory portal.

Awesome product.

 

                            IEnumerable<ticket> groupTix = from p in db.tickets where p.statuse.status_name != "Closed" && (p.assigned_to_group == usr.sub_unit) select p;
                            //  IEnumerable<ticket> groupTix = from p in db.tickets where (p.submitter != usr.id) && p.statuse.status_name != "Closed" && (p.assigned_to_group == usr.sub_unit) select p;

                         //JOE might need to comment above line out and uncomment this!!     IEnumerable<ticket> groupTix = from p in db.tickets where p.submitter != usr.id && p.statuse.status_name != "Closed" && (p.assigned_to_group == usr.sub_unit) select p;
                            IEnumerable<ticket> ITix = tickets.ICommentedIn(db, usr);
                            if (groupTix != null && ITix != null)
                            {
                                return groupTix.OrderByDescending(p => p.priority1.level).OrderByDescending(p => p.submitted);
                                // use the below one if you want assigned tickets to disapear in main group.
                                //  return groupTix.Except(ITix).OrderByDescending(p => p.priority1.level).OrderByDescending(p => p.submitted);
                            }
                            else
                                return groupTix.OrderByDescending(p => p.priority1.level).OrderByDescending(p => p.submitted);

Sep 10, 2010 at 1:58 PM

I got it working but this direction is definitely not efficient at all but since it's a ticketing system and not a site that gets a lot of hits this will work perfectly.

In my groups gridview (id=gvGroup) in the file my_issues.aspx  I added this <%# utils.Checkwhoassignedto(Eval("id").ToString()) %>

then in this file App_Code/utils.cs I added this

public static string Checkwhoassignedto(string ticketID)
{
if (ticketID != null)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SlickTicket"].ToString());
string sqlstring = "" ;
sqlstring =
"SELECT top 1 users.userName FROM tickets INNER JOIN comments ON tickets.id = comments.ticket_ref INNER JOIN users ON comments.submitter = users.id WHERE (comments.ticket_ref = "+ticketID+")";
SqlCommand commandassigned = new SqlCommand (sqlstring, conn);
conn.Open();
ticketID = (
string)commandassigned.ExecuteScalar();
conn.Close();

if (ticketID != null)
{
if (ticketID.IndexOf("@") > 0)
{ }
else
{
ticketID =
"(<i>" + ticketID + "</i>)" ;
}
}
}
return
ticketID;
}

Now remember that my original code related to this issue is modified so that when someone is assigned a ticket the ticket doesn't disapear out of the gvgroup gridview. That ticket stays in the gvgroup gridview and it will say assigned(jmiller)

Sep 16, 2010 at 3:32 AM

I apologize for taking so long to get back, Grad School has been kicking my butt and I just forgot :P

In Slick-Ticket, you can't assign tickets to single users, just groups (unless you modified that part), but by looking at your SQL, it looks like you are just looking up the first commenter in the ticket.  If that is the case, there is not need to do another query, everything can be taken care of with LINQ and the data will already be queried up.  I came up with this, which you can put in the 'tooltip' part of the myissues.aspx page and you should get the same results with a lot less code.  I have this on the newest commit on line 112 on myIssues.aspx:

<div><b>First Commenter: </b>
    <%# ((IEnumerable<SlickTicket.DomainModel.comment>)Eval("comments")).Count() > 0 ? ((IEnumerable<SlickTicket.DomainModel.comment>)Eval("comments")).First().user.userName : " - "  %>
</div>
it just has to do a little checking to make sure there was a comment there before it tries to find the author

 

Sep 21, 2010 at 6:18 PM

Thanks.  I'm still learning the linq to sql and this program is the best platform for learning all the real world coding of linq.

I'm using the last commented section as a bases for who it's assigned to and using the Open and Assigned sections to relate what the status of the ticket is.  Combined together I'm able to sort of Assign a ticket to an individual when that individual chooses the Assigned dropdown choice.  This way I can pull a report of who did what and what average response times are to an individual level.  I love the system.  It's nice to have the active directory and since I needed both active directory and regular users to submit tickets this program was perfect because it was easy to add a non active directory ticketing page to this structure.

Sep 21, 2010 at 10:49 PM

I am glad you are enjoying the code!

A great book for learning Linq is Link in Action (http://www.amazon.com/LINQ-Action-Fabrice-Marguerie/dp/1933988169) - it helped me a ton when learning about it (I am in no way affiliated with them at all... it's just a good book).

If you could share how you set up a non-AD interaction, I am sure people would use it, I have had a lot of questions about that, just not had the time to do it.  I am very interested to see how you did!

Sep 22, 2010 at 8:03 PM
I actually copied the new ticket page into it's own folder named internetsubmitter.  Then I changed some of the things like adding an email field that requires an email of the client. 
Then I ran there email through this function
 

 

 

 

 

 

 

 

 

public static string simplify_email_address(string

email)

{

 

 

int pos1 = email.IndexOf("<"

);

 

 

int pos2 = email.IndexOf(">"

);

 

 

if

(pos1 >= 0 && pos2 > pos1)

{

 

 

return

email.Substring(pos1 + 1, pos2 - pos1 - 1);

}

 

 

else

{

 

 

return

email;

}

}

 

I then take that email string and put it in string username

 

 

Then I run a query to check the database to see if that username exists

 

if that username doesn't exist then I insert this new username and email I set there telephone to 555-555-5555 I guess you could get that info if you wanted but I'm not needing the telephone of the customer I set there sub_unit to 2 and is_admin to 0  

insert into users ([userName],[phone],[email],[sub_unit],[is_admin]) values ('"

 

+ username + "','555-555-5555','" + username +

"','2',0)

 

since this will add everyone that submits a ticket into the main username system you can exclude them by using the @ character since the active directory entries don't have @.

 

then I get the new user

user

u = dbi.users.get(db, username);

Also in IIS you create an application and use the new file you created. like this:

 

<div style="width: 690px"><iframe id="resizeframe" src=http://externalsite.com/internetsubmitter/default.aspx frameborder="0" name="resizeframe" scrolling="no" style="width: 680px; height: 1670px"></iframe></div>

in you intranet

 

Also in IIS in the Authentication and access control click edit and check the checkbox for "Enable anonymous access"  Use a windows account for annonymous access so here you'll enter that windows account here plus password.

and also check the checkbox for "Integrated Windows authentication"

 

this will basically get the external user through with them using an actual website address and not an internal site address.