General Questions about Customisations

Mar 26, 2010 at 1:42 PM

Hi there,

I've made a bunch of customisations to my install of Slick Ticket, and was wondering if you'd be happy with me posting a "How To" type thing in a discussion here?

My aims are 3 fold really:

1) Make sure I don't forget how to customise things by myself

2) Help others with customisations they want to do

3) Learn how to code my customisations better (because frankly I'm really just a beginner with coding and I know that some of my solutions could be a lot prettier, and therefore maybe more efficient)

Coordinator
Mar 30, 2010 at 5:57 AM

Feel free, that is what open source is all about

Mar 30, 2010 at 8:11 AM

Cool, I just didn't want to step on toes or anything :)

I'll get some stuff written up soon.

Mar 31, 2010 at 9:47 PM

First and foe most, for anyone who will be following this thread:

I've made my changes in the Release Code (28536), and I'm now "evaluating" the latest source (41103) to see if/what features are there that I would like / need...so I might be a bit longer with writting what I've done.

If you're interested in knowing before I get it posted, please let me know and I'll endavour to post things quicker.

Apr 26, 2010 at 4:19 PM

Customisations I've currently made to my own install of SlickTicket 28536:

 

Added Client dropdown list to Tickets.

Added Product, Sub-Product and Version dropdown lists to Tickets.

Added On Site check box, Start Time, Finish Time and Total Travel Time to tickets.

Added Action Type (Email, Phone Calls, etc...), Duration (Hours), Duration (Minutes) dropdown lists to Tickets.

Made sure that the added dropdown lists correctly correspond to the records in the database for Ticket.aspx

Made sure Client and Product are Ticket wide options, with the others being Comment wide only.

Overhauled the Help/Faq section into more of a Knowledge Base area.

Added "Is_KB" to users, and ensured Admins can change the property.

Made sure all users can add new Clients, Action Types, and new porducts, sub-products and versions, that are linked appropriately to thier parent.

Added SQL Report pages (x2), one for reporting on Client activity, and one for reporting on User activity.

 

If anyone is interested in more detail, just let me know.

Apr 28, 2010 at 12:46 AM
Edited Apr 28, 2010 at 12:46 AM

 Hi, I'm fairly new at this and I've been looking into implementing something similar. I'd truly appreciate more details or a copy of your changes to see how it's done, thanks!

Mitchel

Apr 28, 2010 at 8:06 AM
mkferns wrote:

 Hi, I'm fairly new at this and I've been looking into implementing something similar. I'd truly appreciate more details or a copy of your changes to see how it's done, thanks!

Mitchel

 Hi Mitchel,

What things were you looking to implement?

I don't mind helping you figure out how to add them in.  Have you got a copy of Visual Studio?

Apr 28, 2010 at 9:13 AM

Thanks for the speedy reply! Actually what you have listed above are in line with the kind of things that I need to do, but for the reports section I'm using Crystal Reports to extract and format the data. I'm using VS 6.0, VS2005 and VS2008 as there are legacy programs prior to VS2005 that are being converted to VS2008 - thus the need for a 'trouble ticket system'! Although it's used in-house, we do have diff people doing dev and testing stuff, therefore we have to keep track many things. Tried out some 'bug tracker' apps, but it had too many features ;-o!

The client part might be optional for now, as I'm using the registered users. Product, timings, ...etc. are what I need to do/implement. Can the Product be broken down as [Dept + Category + Item]? How do I flag items or associate items as billable/non-billable, e.g. group "Support" have sub-sections {hardware, OS, apps, ...etc} - therefore if a ticket is issued, the authorized person can flag it as 'bill'. Once the ticket is closed, the report picks up 'bill' issues for futher processing by the billing department. I might be jumping the gun here, but this looks like a good framework to start with as it looks easy enough to understand!

FYI, I've just started programming in .Net and C# (about 6 months or so), thus most of the stuff in Slick Ticket are really new to me! Learning never stops so I do hope you can assist me in this area as it may take me some weeks to figure and get stuff working... :(

Regards,

Mitchel

Apr 28, 2010 at 10:24 AM

Firstly,

You probably have more formal experience than I have, and my code uses a mix-mash of SQL and LINQ, but as I do more customising I learn more and my code gets "cleaner".

Anyway: ok cool.  Let's start with adding in a Product dropdown list (ddl), as this seems to be something you want to include.

Now before you read this, this is just a simple example on how I added the Product dropdown.  This example doesn't include the more complex stuff of making Sub-Products and Versions dependant upon the chosen product.


The first thing I did was create a local copy of SlickTicket (ST) and a local copy of a ST db using SQL 2005 express, and then I used VS2008 and opened the Web site, using the ST folder location.

Now, please note, I'm using 28536, so it might be different if you're using a different release.

Now to add the ability to add a product ddl in I did the following:


Create a new DB table "products" with fields "product_id" (int) PK, "product_name" (varchar(100)).  I then added a column to the Tickets table called "product_id" (int).  Then I made a foriegn key in the Tickets table from products.product_id to tickets.product_id.

Then you need to update the db.dbml file under App_Code.  First, make sure your database is listed in Data Connections un the Server Explorer pane, and then double click the db.dbml file to open it.

Then all you need to do is (ensuring you've refreshed the data connection):

Delete the Ticket table

Drag the Ticket table from the data connection to the db.dbml file

Drag the Products table from the data connection to the db.dbml file

Save the db.dbml file.


Now we have to add the Product into the Ticket class under Ticket.Add (and Ticket.Update if you want to treat the product as a TICKET-WIDE object) and add a way for a user to submit them using the UI:

Open App_Data/Tickets.cs

Navigate to public static ticket Add(...

Add ", int product" before the ")"

Then in the logic add "newTicket.product_id = product;" BEFORE db.tickets.InsertOnSubmit(newTicket);

Do the same for public static ticket Update(... replacing "newTicket" with "t".


Now NEW tickets are created in New_Ticket.aspx, and already existing tickets are edited in Ticket.aspx.

This is where I've mix-mashed SQL and LINQ, so if you know LINQ you might do a better job here then I did.

Anyway I added the following to the new_ticket.aspx

<p>Product</p>
<asp:SqlDataSource ID="SqlProduct" runat="server"
ConnectionString="<%$ ConnectionStrings:SlickTicket %>"  
SelectCommand="Select * from dbo.Products" />

<asp:DropDownList ID="ddlProduct" runat="server" DataSourceID="SqlProduct" DataTextField="Product_Name" DataValueField="Product_ID" Width="90%" /> 

Personally I put that in a new table under the exsiting one just under the txtTopic text box, but where you put it is up to you.

Now you need to add to the code behind to make sure it gets submitted:

protected void btnSubmit_Click(object sender, EventArgs e)
    {

         ...

         ticket newTicket = Tickets.Add(db, Server.HtmlEncode(txtTopic.Text), txtDetails.Text, Int32.Parse(ddlSubUnit.SelectedValue), Int32.Parse(ddlPriority.SelectedValue), u.id, u.sub_unit, Int32.Parse(ddlProduct.SelectedValue));

Now that should add the selected Product to the Ticket.


As I've decided that Products should be Ticket wide, therefore if it is changed later that change should be reflected in the Ticket table, and NOT the comment table, I had to add the Product input into the Ticket.aspx page to.

The same asp code will do, but a slight alteration to the code behind (ticket.aspx.cs):

protected void btnUpdate_Click(object sender, EventArgs e)
    {
         ...
         
         ticket _t = Tickets.Update(db2, t_id, Int32.Parse(ddlStatus.SelectedValue), Int32.Parse(ddlPriority.SelectedValue), Int32.Parse(ddlSubUnit.SelectedValue), Int32.Parse(ddlSubProduct.SelectedValue));
         
         ....

It's a subtle change, but VERY important if the Product is to be TICKET wide


The last thing we need to do is enable users with the ability to ADD new products to the system without them needing to connect directly to the database.

I did this by adding a new page to the project, but you could simply add it to an existing one (like admin\settings.aspx).

Here is the ASPX code I've written:

<fieldset class="inner_color">
    <asp:label ID="lblProduct2" runat="server" Text="Type the new product below:" />
    <br />
    <asp:TextBox ID="txtProduct" runat="server" />
    <asp:Button ID="btnProduct" runat="server" Text="Add Product" OnClick="btnProduct_Click"/>
 </fieldset>

NOTE: I've just put the simple ASPX code you need to put the ability to enter a new Product in.  Feel free to use a Collasible panel or Accordion or whatever you wish, around it.

Then the code behind needs to include this (again I used SQL, but feel free to use LINQ if you know how [I'd be interested in learning if you wouldn't mind sharing]):

protected void btnProduct_Click(object sender, EventArgs e)
    {
        if (txtProduct.Text != "")
        {
            string strNewProduct = txtProduct.Text;
            string connStr = ConfigurationManager.ConnectionStrings["SlickTicket"].ConnectionString;

            SqlConnection connSql = new SqlConnection(connStr);
            connSql.Open();

            //This checks if the entry already exists in the db table
            string strSql = "Select * from Products Where Product_Name='" + strNewProduct + "'";
            SqlCommand cmdSqlCheck = new SqlCommand(strSql, connSql);
            SqlDataReader sqlRead;
            sqlRead = cmdSqlCheck.ExecuteReader();

            if (sqlRead.HasRows)
            {
                //If the entry already exists, close the db connection and report an error
                connSql.Close();
                lblProduct2.Text = "ERROR: Product already exists in the database.";
                txtProduct.Text = "";
            }
            else
            {
                //Else insert the new entry, close the connection and report success
                sqlRead.Close();
                SqlCommand cmdSql = new SqlCommand("Insert into Products (Product_Name) Values ('" + strNewProduct + "')", connSql);
                cmdSql.ExecuteNonQuery();
                connSql.Close();
                lblProduct2.Text = "Product added successfully - Repeat to add more.";
                txtProduct.Text = "";
              }
        }
        else
        {
            lblProduct2.Text = "ERROR: Please enter text in the box and try again.";
            txtProduct.Text = "";
        }
    }

Hopefully this has all made sense.  If not, let me know and I'll try to document it better.

 

If you have any problems with my code samples, please let me know.  I've had to try and "re-simplify" them for this example.

Apr 28, 2010 at 11:15 AM

Thanks and much appreciated! I'll try this out later this evening when i get back and post the feedback here.

Mitchel

May 5, 2010 at 7:59 AM

Hi,

 

I was just wondering how you got on?

May 5, 2010 at 9:06 AM

Encountered errors when trying to add product drop down, then got side tracked for a few days! I'm looking at adding some 'ticket' reporting using Crystal Reports that's available in VS2008. I can create the report and preview it, but failed to add it or a link to it in the Statistics page. I've been searching for some examples and most of them are for VS2005 (which has been uninstalled from my box)! FYI, I'm looking at version 43525 sources as the SVN Update automatically downloaded and updated my sources - my mistake for not copying out to a working folder in the first place... :(

Perhaps you would like 'skype' me for a quick chat, I'm using the same ID as per this forum. Cheers!

May 5, 2010 at 6:37 PM

I used VS2005 to make my SQL reports.  I've got VS2005 and VS2008 installed on the same machine, so I can't help you there, sorry.

As far as 43525, I've not had any reason to try to apply my changes to that release.

What kind of reports are you looking at making?  The only reason I ask is because it was when I sat down and decided what I wanted in a report that I decided what fields would need to be added.