1<?php
2/*
3 * Copyright 2005-2016 OCSInventory-NG/OCSInventory-ocsreports contributors.
4 * See the Contributors file for more details about them.
5 *
6 * This file is part of OCSInventory-NG/OCSInventory-ocsreports.
7 *
8 * OCSInventory-NG/OCSInventory-ocsreports is free software: you can redistribute
9 * it and/or modify it under the terms of the GNU General Public License as
10 * published by the Free Software Foundation, either version 2 of the License,
11 * or (at your option) any later version.
12 *
13 * OCSInventory-NG/OCSInventory-ocsreports is distributed in the hope that it
14 * will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty
15 * of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with OCSInventory-NG/OCSInventory-ocsreports. if not, write to the
20 * Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
21 * MA 02110-1301, USA.
22 */
23
24 /**
25  * Class for software categories
26  */
27class AllSoftware
28{
29
30    public function software_link_treatment() {
31        // First clean software_link
32        $delSoftLink = $this->delete_software_link();
33        // Get all softwares
34        $allSoft = $this->get_software_informations();
35        // Get categories
36        $allSoftCat = $this->get_software_categories_link_informations();
37
38        $software = [];
39        $softwareCategory = [];
40
41        if($allSoft && $allSoft->num_rows != 0) {
42            while($item_all_soft = mysqli_fetch_array($allSoft)) {
43                $software[$item_all_soft['identifier']]['NAME_ID'] = intval($item_all_soft['NAME_ID']);
44                $software[$item_all_soft['identifier']]['PUBLISHER_ID'] = intval($item_all_soft['PUBLISHER_ID']);
45                $software[$item_all_soft['identifier']]['VERSION_ID'] = intval($item_all_soft['VERSION_ID']);
46                $software[$item_all_soft['identifier']]['CATEGORY_ID'] = null;
47                $software[$item_all_soft['identifier']]['COUNT'] = intval($item_all_soft['nb']);
48            }
49        }
50
51        if($allSoftCat && $allSoftCat->num_rows != 0) {
52            while($items = mysqli_fetch_array($allSoftCat)) {
53                $softwareCategory[$items['ID']]['NAME_ID'] = intval($items['NAME_ID']);
54                $softwareCategory[$items['ID']]['PUBLISHER_ID'] = intval($items['PUBLISHER_ID']);
55                $softwareCategory[$items['ID']]['VERSION_ID'] = intval($items['VERSION_ID']);
56                $softwareCategory[$items['ID']]['CATEGORY_ID'] = intval($items['CATEGORY_ID']);
57            }
58        }
59
60        if(!empty($softwareCategory)) {
61            foreach($software as $identifier => $values) {
62                foreach($softwareCategory as $key => $infos) {
63                    if($values['NAME_ID'] == $infos['NAME_ID'] && $values['PUBLISHER_ID'] == $infos['PUBLISHER_ID'] && $values['VERSION_ID'] == $infos['VERSION_ID']) {
64                        $software[$identifier]['CATEGORY_ID'] = $infos['CATEGORY_ID'];
65                    }
66                }
67            }
68        }
69
70        foreach($software as $identifier => $values) {
71            $sql = "INSERT INTO `software_link` (`IDENTIFIER`, `NAME_ID`, `PUBLISHER_ID`, `VERSION_ID`, `CATEGORY_ID`, `COUNT`)";
72            if($values['CATEGORY_ID'] == null) {
73                $sql .= " VALUES ('%s', %s, %s, %s, NULL, %s)";
74                $arg = array($identifier, $values['NAME_ID'], $values['PUBLISHER_ID'], $values['VERSION_ID'], $values['COUNT']);
75            } else {
76                $sql .= " VALUES ('%s', %s, %s, %s, %s, %s)";
77                $arg = array($identifier, $values['NAME_ID'], $values['PUBLISHER_ID'], $values['VERSION_ID'], $values['CATEGORY_ID'], $values['COUNT']);
78            }
79
80            $result = mysql2_query_secure($sql, $_SESSION['OCS']["writeServer"], $arg);
81
82            if(!$result) {
83                error_log(print_r("An error occure when attempt to insert software with identifier : ".$identifier, true));
84            }
85        }
86
87    }
88
89    private function delete_software_link() {
90        $sql = "DELETE FROM `software_link`";
91        $result = mysql2_query_secure($sql, $_SESSION['OCS']["writeServer"]);
92    }
93
94    private function get_software_informations() {
95        $sql = "SELECT CONCAT(n.NAME,';',p.PUBLISHER,';',v.VERSION) as identifier,
96                s.VERSION_ID, s.NAME_ID, s.PUBLISHER_ID,
97                COUNT(CONCAT(s.NAME_ID, s.PUBLISHER_ID, s.VERSION_ID)) as nb
98                FROM software s
99                LEFT JOIN software_name n ON s.NAME_ID = n.ID
100                LEFT JOIN software_publisher p ON s.PUBLISHER_ID = p.ID
101                LEFT JOIN software_version v ON s.VERSION_ID = v.ID
102                GROUP BY s.NAME_ID, s.PUBLISHER_ID, s.VERSION_ID";
103
104        $result = mysql2_query_secure($sql, $_SESSION['OCS']["readServer"]);
105
106        return $result;
107    }
108
109    private function get_software_categories_link_informations() {
110        $sql = "SELECT * FROM `software_categories_link`";
111        $result = mysql2_query_secure($sql, $_SESSION['OCS']["readServer"]);
112
113        return $result;
114    }
115
116     /**
117      * Search for softwares w/ hardware_ids that are no longer registered
118      * in hardware table and delete them from software table
119      */
120    public function software_cleanup() {
121        $sql = "SELECT software.HARDWARE_ID FROM `software`
122                LEFT JOIN `hardware` ON software.HARDWARE_ID = hardware.ID
123                WHERE hardware.ID IS NULL GROUP BY software.HARDWARE_ID";
124        $result = mysql2_query_secure($sql, $_SESSION['OCS']['readServer']);
125        $i = 0;
126        while ($hid = mysqli_fetch_array($result)) {
127            $unlinked_hids[$i] = $hid['HARDWARE_ID'];
128            $i++;
129        }
130
131        if ($unlinked_hids >= 1) {
132            $sql_del = "DELETE FROM software WHERE HARDWARE_ID IN (%s)";
133            $arg_del = implode(",", $unlinked_hids);
134            $result = mysql2_query_secure($sql_del, $_SESSION['OCS']["writeServer"], $arg_del);
135        }
136
137    }
138
139}