3 Replies Latest reply: Dec 18, 2014 3:39 AM by Florian Klünder RSS

    ...

       

       

       

       

       

       

       

       

       

       

       

       

      Hallo,

       

       

       

       

       

       

       

       

       

       

       

       

      ich habe folgenden Skript:

       

       

       

       

       

       

       

       

       

       

       

       

       

      ODBC

       

       

       

       

      CONNECT

       

       

       

       

      TO

       

      GLPI (

       

      XUserId

       

       

       

      is

       

      YMPWTYRNJbaMXUVMXDfA,

       

      XPassword

       

       

       

      is

       

      CeaVZYRNJbaMXUVMXbMB);

       

       

       

       

       

       

      Load

       

       

      *,

       

      month

       

      (

       

      floor

       

      (

       

      date

       

      ))

       

      as

       

       

       

      Monat

       

      ,

       

      year

       

      (

       

      floor

       

      (

       

      date

       

      ))

       

      as

       

       

       

      Jahr

       

      ,

       

      floor

       

      (

       

      date

       

      )

       

      as

       

       

       

      Datum1

       

      ,

       

      frac

       

      (

       

      date

       

      )

       

      as

       

       

       

      Zeit

       

      ;

       

       

       

       

       

       

       

       

      SQL

       

       

      SELECT *

       

      FROM glpi.`glpi_tickets` where `is_deleted` = '0';

       

       

       

       

       

       

       

       

      SELECT

       

       

      realname FROM glpi.glpi_users, glpi.glpi_tickets,glpi.glpi_tickets_users

       

       

       

      where glpi.glpi_users.id=glpi_tickets_users.users_id and

      glpi.glpi_tickets.id=glpi_tickets_users.tickets_id and

      glpi_tickets_users.type=2;

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Jetzt habe ich das Problem, dass es in der Tabellenstruktur keine Beziehung

      gibt. Was habe ich flasch gemacht, oder wie kann ich das beheben. Können Sie

      mir hier helfen? Ziel ist, dass ich seh, wieviele Tickets der Techniker

      (realname) sortiert nach Monat bzw. Status erledigt hat… Aber jeder

      Techniker hat als Anzahl alle Tickets drin… Danke im Voraus…

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Florian Klünder

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Re: ...
          François Cavé

          Hallo Florian,

          Es gibt keine Beziehung in der Tabellenstruktur...

          You have certainly a key field in GLPI to Join your tickets and your Technician name...

           

          Something like that ?

           

          Tickets:

          LOAD

            id as tickets_id

             ....;

          SQL SELECT

            id,

          ...

          FROM glpi_tickets;

           

          User_tmp:

          LOAD

            tickets_id,

            users_id,

            type as type_user;

          SQL SELECT

            tickets_id,

            users_id,

            type

          FROM glpi_tickets_users;

           

          Left Join

          LOAD

            id as users_id,

            name as TechnicianName,

            realname &' '& firstname as Technician;

          SQL SELECT

            id,

            name,

            realname,

            firstname

          FROM glpi_users;

           

          François

            • Re: ...

              OK. I tried it. This is the Skript now:

               

               

              ODBC CONNECT TO GLPI (XUserId is YMPWTYRNJbaMXUVMXDfA, XPassword is CeaVZYRNJbaMXUVMXbMB);

              Load *, month(floor(date)) as Monat, year(floor(date)) as Jahr, floor(date) as Datum1, frac(date) as Zeit;


              SQL SELECT *
              FROM glpi.`glpi_tickets` where `is_deleted` = '0';


              SELECT *
              FROM `glpi_users`;


              Tickets:

              LOAD

              id as tickets_id;

              SQL SELECT

              id
              FROM glpi_tickets;



              User_tmp:

              LOAD

              tickets_id,

              users_id,

              type as type_user;

              SQL SELECT

              tickets_id,

              users_id,

              type

              FROM glpi_tickets_users;



              Left Join

              LOAD

              id as users_id,

              name as TechnicianName,

              realname &' '& firstname as Technician;

              SQL SELECT

              id,

              name,

              realname,

              firstname

              FROM glpi_users;

               

               

              My Problem now is, that all you write to me is functional, but the Connection to the tickets doesn´t work (it Shows all tickets from December 2014 at every user). See Picture

              https://owncloud.pc-blocka.de/public.php?service=files&t=d89083c2b195bd56f1fdce861fa03961

               

               

              And i needin the left graph not the technican Name(because you define technican as firstname and realname). I Need only the users defined like this (only type 2).

               


              SELECT firstname,realname as Techniker FROM glpi.glpi_users, glpi.glpi_tickets,glpi.glpi_tickets_users
              where glpi.glpi_users.id=glpi_tickets_users.users_id and glpi.glpi_tickets.id=glpi_tickets_users.tickets_id and glpi_tickets_users.type=2;

               

               

               

               

               

              Do you have an idea?

               

            • Re: ...

              This is the answer:

               

              Load *, day(floor(date)) as Tag, week(floor(date)) as Woche, month(floor(date)) as Monat, year(floor(date)) as Jahr, floor(date) as Datum1, frac(date) as Zeit;

               

              SELECT * from glpi_tickets;

              SELECT  `glpi_tickets`.`id`,`glpi_users`.`realname`,glpi_tickets.status
              FROM `glpi_tickets`
              left JOIN `glpi_tickets_users`  ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id` AND `glpi_tickets_users`.`type` = 2 )
              left JOIN `glpi_users`  ON (`glpi_tickets_users`.`users_id` = `glpi_users`.`id` )
              where  `glpi_tickets`.`is_deleted` = '0';