[otrs] Statistic extensions (considering TimeWorkingHours)

[Alexander Scholler]

Hi all,




I now wrote some extensions posted in this mail.

This is the approach I followed:

often discussed on the lists (english as well as german): the availability of statistic extensions on OTRS. 1) Basically I had to understand the DB-schema ftp://ftp.otrs.org/pub/otrs/misc/otrs-2.0-database.png

http://dev.otrs.org/2.0/Kernel/System/Time.html#item_workingtime
(sorry for that syntax, I'm not a perl expert).




Some of them are attached to this mail.


Would you please give me feedback
* if the SQL-statements are correct
* if the work I've done follows the right way



Sorry about the german/english-mix used within the files.

Now, some questions coming up during my work:

I) In Status_Changes.pm the code does
 * make the sql-query
 * calculate time within support time
 * does some data-grouping through perl

What do you think about these approaches?

II) I wanted to do some modification over all statistics.


Bye, Alex

2) Then I did some pure SQL statistic analysis within OpenOffice Base with JDBC-bridge to the MySQL-DB (note: this Base-DB can be used excellently under Windows and Linux! - try this with M$-Office). 2a) The problem is that TimeDifferences (eg. how long did it takes to act on a new ticket) are always calculated hard, meaning "DateWhenActing minus DateWhenNew" is not really a good measurement because the support time of the service desk team is not considered. The gap in seconds within the support time (defined in TimeWorkingHours and others on OTRS) can be calculated within OTRS through Kernel::System::Time->WorkingHours, see So I had to bring my Base-SQL-statements into the OTRS statistic framework to use the time-function provided by OTRS. 2b) I took ~/Kernel/Systems/Stats/* as templates and created my own extensions. You have to copy the *.pm-Files to the proper location and extend your ~/Kernel/Config/Files/Ticket.xml (to let OTRS know that there are extensions). I think many of you have waited for the statistics I provide. If you extend my statistics, please also publish your work done. Consider that changes you make to statistics don't affect if UseResultCache = 1. WriteCache on State_Changes_Specific.pm could be applied due to the logic, but cache-distinction upon different selected states is not supplied by OTRS (it only considers Month and Year). The problem is that I had to expensively (for me!) code the data-grouping in perl. MySQL could do this job much better and more efficient. But how can the WorkingHours-function of OTRS be brought to MySQL? Ia) by writing an MySQL stored-procedure/function, first available on v5.0. The problem with this approach is that the service-time must be maintained in OTRS and in the MySQL-function => this could be done in further OTRS version through OTRS itself with it's DB-access. Ib) by giving the modified time data from OTRS back to MySQL. Out of OTRS a temporary table could be created which would be filled with the modified time data through OTRS. Data-analyses (e.g. grouping) could be done on this table. So I edited ~/Kernel/Modul/SystemStats.pm. But I couldn't notice any output-changes upon code changes (beside wrong syntax: this was reported by an error). Where or how must the statistic output module be edited?

State_Changes.pm
Description: Perl program

State_Changes_Specific.pm
Description: Perl program

    <ConfigItem Name="SystemStatsMap###Ticket::StatsOwn4" Required="0" 
Valid="1">
        <Description Lang="en">duration of states</Description>
        <Description Lang="de">Dauer der Stati</Description>
        <Group>Ticket</Group>
        <SubGroup>Core::Stats</SubGroup>
        <Setting>
            <Hash>
                <Item Key="Name">Status-Verweildauer</Item>
                <Item Key="Module">Kernel::System::Stats::State_Changes</Item>
                <Item Key="Desc">Gibt an, wie lange die einzelnen Stati 
bestehen bleiben. Es werden alle Stati betrachtet, die innerhalb des 
gewuenschten Zeitraumes beendet wurden.</Item>
                <Item Key="UseResultCache">1</Item>
                <Item Key="Output">
                    <Array>
                        <Item>Print</Item>
                        <Item>CSV</Item>
                    </Array>
                </Item>
                <Item Key="OutputDefault">Print</Item>
            </Hash>
        </Setting>
    </ConfigItem>
    <ConfigItem Name="SystemStatsMap###Ticket::StatsOwn5" Required="0" 
Valid="1">
        <Description Lang="en">duration of specific state</Description>
        <Description Lang="de">Dauer eines speziellen Status</Description>
        <Group>Ticket</Group>
        <SubGroup>Core::Stats</SubGroup>
        <Setting>
            <Hash>
                <Item Key="Name">Status-Verweildauer (spezifische 
Einzelbetrachtung)</Item>
                <Item 
Key="Module">Kernel::System::Stats::State_Changes_Specific</Item>
                <Item Key="Desc">Zeigt alle Verweildauern des ausgewählten 
Status a. (Status muss im ausgewaehlten Monat abgeschlossen worden sein.</Item>
                <Item Key="UseResultCache">0</Item>
                <Item Key="Output">
                    <Array>
                        <Item>Print</Item>
                        <Item>CSV</Item>
                    </Array>
                </Item>
                <Item Key="OutputDefault">Print</Item>
            </Hash>
        </Setting>
    </ConfigItem>
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support oder Consulting für Ihr OTRS System?
=> http://www.otrs.de/