Custom Stats Report

[aMescAs]

Hi,

I need some help in the "stats reports" subject.

I've to allow every agent to export (or simply view in dashboard - it would be better though) some data about his/hers ticket hours by day.
I've already managed to build a query, with a dynamic field included, and it works fine! But a normal agent don't have access to that.

Code: Select all

select
DATE_FORMAT(d1.value_date, '%Y/%m/%d' ) Modified_Date, 
DATE_FORMAT(ta .change_time, '%Y/%m/%d' ) Input_Date,
u.login ,
CONCAT ( u.first_name , ' ' , u .last_name ) name,
q.name queue_name ,
t.tn ticketnumber ,
d.value_text ,
t.title title ,
ta.time_unit,
ta .article_id,
d1. object_id,
d. object_id,
Week( ta .change_time ) week

from time_accounting ta
left join ticket t on t. id = ta .ticket_id
left join queue q on t. queue_id = q . id
left join article a on a. id =ta .article_id
left join users u on u. id = ta .change_by
left join dynamic_field_value d on d. object_id = t .id  AND d .field_id = '4'
left join dynamic_field_value d1 on d1. object_id = ta .article_id  AND d1.field_id = '17'
where ta . time_unit is not null  
order by t . tn 

Note: The field_Id = '17' is a dynamic field type date, placed in the ViewNote, ViewPhoneNew and ViewEmailNew, and filled every time an agent inputs hours.

So, how can I create a solution, so that a normal agent (without admin permissions) can see the same that the query above?
Is it possible to see and edit the query in a stats report?

Regrads,
Flávio Rocha.

[crythias]

I've created this as a near-line stand alone web page. Since I know the query, I just need to report on it. I can do that with Php, etc.

In my case, I don't want techs looking at other tech's usage, so I give each his own URL with hard-coded userID. I ask for start date and billing amount, then pretty-print the result on a web page.

[aMescAs]

That can be a possibility. Thanks!

But, i've been searching and i've managed to create a "Hello World" module. Só in my OTRS page, i've now a new button and a blank page with a simple string there.

And now the "please help me" part is:

• Am I able to do in Perl what I pretend? I mean, is it possible to execute a query to de DB and display it in a table?

[crythias]

is it possible to execute a query to de DB and display it in a table?

Yes. But it's a bit more than can be explained in a thread like this.

Note that much of what OTRS is revolves around executing queries and displaying results in tables. You might learn a bit by mimicking some of the TicketZoom code regarding results in a module, use of block in layout and templating tags in the appropriate .dtl.

[aMescAs]

Hm... ok! So i think i'll leave this for later..

@crythias can you send/show me your php code? I, just like you, also want to restrict the view by user.

Regards,

[crythias]

add

Code: Select all

where user_id=#

(hover over the agent in the web interface).

The rest is normal and can be found in any php web tutorial on the Internet.

[aMescAs]

OK.. anyway, i've decided to use ASP, since it's already installed in the server (and PHP don't)..
.. but my problem here is the connection!

How do i connect to my DB? .. i've tried some code, and nothing works.

Code: Select all

<%
Dim oConn, oRs
Dim qry, connectstr
Dim db_name, db_username, db_userpassword
Dim db_server

db_server = "<server public ip>"
db_name = "OTRS"
db_username = "<user>"
db_userpassword = "<password>"
fieldname = "id"
tablename = "ticket"

connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & "<br>"
oRS.movenext
wend
oRS.close
end if

Set oRs = nothing
Set oConn = nothing

%> 

This is an example code, so it should work, right? but it doesn't..



any help?

[crythias]

Connection to the database is generic and a web search for asp and mysql should be helpful.