has anyone ever considered adding reporting features that someone might need on a monthly, quartery, or annual basis...

as a preliminary thought what about some graphs, maybe a pie chart or bar chart. Although this can be done by downloading the database and uploading to a spreadsheet, is it a good idea to have just some basic charts on a statistical page for the software ? not sure. Maybe something such as number of total tickets, number of open, number of closed, average ticket resolve time, maybe some staff performance charts to chart personal ticket turn around, maybe some ticket priority charts, such as number of normal, number of high, number of emergency... and anything else someone might think of that would be useful...

i am very happy with my os ticket right now, they mods have gone great and im loven it... this is just an idea for the future maybe... thanks

as a footnote i know there are some personal scripts out there that people have written and i am referring to something more standardized.

Statistics are a feature we'll be adding in the future, though we haven't set any kind of timeline for release.

5 days later

this is what i have

(blank)

you can mail me ozkr999@gmail.com

Hello

how did you do that?

this is what i have

(blank)

you can mail me ozkr999@gmail.com

6 days later

Hello

how did you do that?

"emailed" him but, no response =(

another one with more luck? :

a month later

Hello

how did you do that?

i would aslo like to know how he did that, i have been working with something similar (fusion charts) but i would like to know how to get the data from mysql and save it as a xml on a regular intervals to update the charts

if im still unclear please send me a msn invite maybe we can work on this and provide osticket with sum cutom reporting

markmollentze@vodamail.co.za

Getting data from the database and formatting into XML is fairly easy. If you do a web search for "php xml tutorial" you will find lots.

Within osTicket, it will be a matter of creating your query, executing it, then formatting and outputting. eg:

// create query

$sql = "SELECT * FROM " . TICKET_TABLE;

// get raw data

$results = db_query($sql);

// loop through records

for each ($ticket as mysql_fetch_array($results)) {

// format into desired XML

// perhaps like this:

echo "<ticket id='" . $ticket . "'>";

echo "<username>" . $ticket . "</username>";

...

echo "</ticket>";

}

Depending upon how you want to use it would then depend if you would want to save it to a file, output it to the browser, or perhaps make it accessible as an RSS feed.

6 months later

Fusion Charts

Sorry for the slow reply i have been out of country for 2 months, getting educated LOL:)

i have some codeing if any one is interested???

MySQL Table Viewer

<?php

$db_host = 'localhost';

$db_user = 'user';

$db_pwd = 'password';

$database = 'neotechc_ostt1';

$table = 'ost_ticket';

if (!mysql_connect($db_host, $db_user, $db_pwd))

die("Can't connect to database");

if (!mysql_select_db($database))

die("Can't select database");

// sending query

$result = mysql_query("SELECT * FROM {$table}");

if (!$result) {

die("Query to show fields from table failed");

}

$fields_num = mysql_num_fields($result);

echo "Table: {$table}";

echo "";

// printing table headers

for($i=0; $i{

$field = mysql_fetch_field($result);

echo "{$field->name}";

}

echo "\n";

// printing table rows

while($row = mysql_fetch_row($result))

{

echo "";

// $row is array... foreach( .. ) puts every element

// of $row to $cell variable

foreach($row as $cell)

echo "$cell";

echo "\n";

}

mysql_free_result($result);

?>

<html><head><title>Mark Test</title></head><body>

<?php

//print_r($_POST);

if (!$_POST || $_POST == '')

{

$t=time();

$dateToUse= date('Y-m-d');

}

else

{

//change the month to numbers

$month_arr=array("01"=>"JAN","02"=>"FEB","03"=>"MAR","04"=>"APR","05"=>"MAY","06"=>"JUN","07"=>"JUL","08"=>"AUG","09"=>"SEP","10"=>"OCT","11"=>"NOV", "12"=>"DEC");

$datum1 = $_POST;

$daydate = $datum1.$datum1;

$monthdatest = $datum1.$datum1;

$monthdate=$month_arr;

$yeardate = $datum1.$datum1.$datum1.$datum1;

//error check for date

if(!checkdate($monthdatest,$daydate,$yeardate)){$flag=FALSE;}

if ($flag === FALSE){echo "<p><font color='red'><b>The date range entered is invalid";die(0);}

$dateToUse = "$yeardate-$monthdatest-$daydate";

}

//start DB stuff

$db_host = 'localhost';

$db_user = 'user';

$db_pwd = 'passwordx';

$database = 'neotechc_ostt1';

$table = 'ost_ticket';

if (!mysql_connect($db_host, $db_user, $db_pwd))

die("Can't connect to database");

if (!mysql_select_db($database))

die("Can't select database");

//query

//echo "SELECT * FROM {$table} WHERE created LIKE '$dateToUse%' AND costentity = 'TUT' AND campus = 'Nelspruit'";

//echo "<br>";

//echo "<br>";

$result = mysql_query("SELECT * FROM {$table} WHERE created LIKE '$dateToUse%' AND costentity = 'TUT'");

if (!$result) {

die("Query to show fields from table failed");

}

$fields_num = mysql_num_fields($result);

//echo $fields_num;

//echo "<BR>";

$field = mysql_fetch_field($result);

//end DB stuff

/**

* start the xml file content here

*/

$xml_output = "<chart palette='4' decimals='0' enableSmartLabels='1' enableRotation='0' bgColor='99CCFF,FFFFFF' bgAlpha='40,100' bgRatio='0,100' bgAngle='360' showBorder='1' startingAngle='70' >\n";

while($row = mysql_fetch_row($result))

{

//to view the filds avalible

//print_r($row);

//echo "<br><br>";

//get the information from the array to display in the file

$campus = $row;

$created = $row;

$cost = $row;

$xml_output .= "<set label='$campus' value='$cost' />\n";

}

$xml_output .= "</chart>\n";

/**

* end the xml file content here

*/

/**

* start the xml file create here

*/

//echo $xml_output;

//get the date for the file name

$t=time();

$Day= date('Y-m-d');

//what file to use

$filename = "test$Day.xml";

//Lets remove the existing file

//if(file_exists($filename))

//{

// unlink($filename);

//}

//will NOT overright the file

//create new file

$handle = fopen($filename, "wr");;

//write the xml to the file

fwrite($handle, $xml_output);

//close the file

fclose($handle);

/**

* end the xml file create here

*/

mysql_free_result($result);

?>

</body></html>

fusion charts

the above Code exports database to a xml for Fusion charts to process

Sorry guys im a noob

i have decided to make everything i made available for anyone willing to persuit the fusion chart route i think if we can get it incorporated into Osticket it would be awesome

(here)

5 months later

Getting sms alert on phone

Dear House,

Does anybody knows how one can add some additional script to osticket so that staffs can receive ticket alerts on their phones. I know sms gateway will be involved once you know your wireless carrier.

Pls help am a newbie to be sincere.

Manuel

4 months later

Where??

This is great . . . . . . Can I get an outline on where to put this code? Very Very new to OSTicket

Thanks!

a month later

Dear House,

Does anybody knows how one can add some additional script to osticket so that staffs can receive ticket alerts on their phones. I know sms gateway will be involved once you know your wireless carrier.

Pls help am a newbie to be sincere.

Manuel

In the open.php in the main directory, there is the IF statement for creating the ticket (~line 29):

//Ticket:...checks for errors..

if(($ticket=Ticket:($_POST,$errors,SOURCE))){

Inside this if statement I added an if statement to only send to phone if high priority using standard PHP mail code. You can remove if statement to have all messages sent. I also have it setup to send topic as subject and subject/description as subject. You can change pretty easy as needed:

if($_POST==3){ //if high priority, send message to phone

//$message = substr($_POST,0,120); - use to limit the message size if sending message content to phone

$whichTopic = $_POST;

$result = mysql_query("SELECT topic FROM ost_help_topic WHERE topic_id = $whichTopic"); // lookup topic name based on id of topic selected.

/*** get the results ***/

$row = mysql_fetch_array($result);

$headers = "MIME-Version: 1.0" . "\r\n";

$headers .= "Content-type/html;charset=iso-8859-1" . "\r\n";

$headers .= "From: ".$_POST." <".$_POST.">\r\n";

$ok = <USERMENTION username="mail">@mail</USERMENTION>('5058814329@txt.att.net,somephone@tmobile.com',$row,$_POST,$headers);

}

Dan

Write a Reply...