Forum GLPI-Project

YaKs

Member

Registered: 2007-05-17

Posts: 24

New Reports...but under Access...

Hi,
weeks ago I asked about some more reports I need in my job and GLPI didnt have it.

Especially two simple reports:

the first is a list of computers and printers (and should be also the rest of component like monitors, switches...) group by Location. Really useful to know what do you have in each location.

the second is a simple which shows the date of purchase, in order to know, whenever you receive a new set of computer, which one has to replace and in which location.

Anyway you can always take a look of what I did, the bad thing is I am not use to program in PHP and I didnt have time to learn the architecture of GLPI so I decided to do it in ACCESS via ODBC.
feel free to ask me for my access file, maybe someone can create a new reports in GLPI natively using my code.

Best regards,
Jose

MoYo

GLPI - Lead

From: Poitiers

Registered: 2004-09-13

Posts: 14,513

Website

Re: New Reports...but under Access...

maybe you could post the SQL requests here ?
Somebody may do the GLPI integration if needed.

---

MoYo - Julien Dombre - Association INDEPNET
Contribute to GLPI :    Support     Contribute     References     Freshmeat

YaKs

Member

Registered: 2007-05-17

Posts: 24

Re: New Reports...but under Access...

I use two separated SQL sentences and the one more to union them...

To get the computers...

SELECT glpi_computers.ID, glpi_type_computers.name AS TYPE, glpi_computers.name AS NAME, glpi_enterprises.name+' '+glpi_dropdown_model.name AS MODEL, glpi_computers.serial AS SERIAL, glpi_computers.otherserial AS INV, glpi_dropdown_locations.name AS LOCATION, glpi_dropdown_locations.comments AS [USER], glpi_infocoms.buy_date AS BUYDATE
FROM ((((glpi_computers LEFT JOIN glpi_dropdown_locations ON glpi_computers.location = glpi_dropdown_locations.ID) LEFT JOIN glpi_dropdown_model ON glpi_computers.model = glpi_dropdown_model.ID) LEFT JOIN glpi_infocoms ON glpi_computers.ID = glpi_infocoms.FK_device) LEFT JOIN glpi_type_computers ON glpi_computers.type = glpi_type_computers.ID) LEFT JOIN glpi_enterprises ON glpi_computers.FK_glpi_enterprise = glpi_enterprises.ID
WHERE (((glpi_computers.deleted)<>'Y'));

To get the printers...

SELECT glpi_printers.ID AS ID, glpi_type_printers.name AS TYPE, glpi_printers.name AS NAME, glpi_dropdown_model_printers.name AS MODEL, glpi_printers.serial AS SERIAL, glpi_printers.otherserial AS INV, glpi_dropdown_locations.name AS LOCATION, glpi_dropdown_locations.comments AS [USER], glpi_infocoms.buy_date AS DATEBUY
FROM (((glpi_printers LEFT JOIN glpi_infocoms ON glpi_printers.ID = glpi_infocoms.ID) LEFT JOIN glpi_dropdown_locations ON glpi_printers.location = glpi_dropdown_locations.ID) LEFT JOIN glpi_dropdown_model_printers ON glpi_printers.model = glpi_dropdown_model_printers.ID) LEFT JOIN glpi_type_printers ON glpi_printers.type = glpi_type_printers.ID
WHERE (((glpi_printers.deleted)<>'Y'));

Finally I do...

SELECT Computers.*
FROM Computers UNION SELECT * from Printers;

Planning to also union the switches and so on...

after in the form I can sort by buydate, location, name...

I hope somebody helps.

Cheers up,
Jose