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}