--- /dev/null
+<?php
+
+class PdoApplianceManager extends AbstractPdoManager {
+
+ public function getNbAppliances(){
+ $sql = "SELECT count(*) FROM appliances";
+ return $this->queryOne($sql);
+ }
+
+ public function getByFQDN($internal_fqdn){
+ $sql = "SELECT * FROM appliances WHERE internal_fqdn=?";
+ return $this->queryOne($sql,$internal_fqdn);
+ }
+
+ public function editAppliance(Appliance $appliance){
+ if ($appliance->id){
+ $this->updateAppliance($appliance);
+ return $appliance->id;
+ } else {
+ return $this->createAppliance($appliance);
+ }
+ }
+
+ public function getApplianceForOutput($offset,$limit){
+ $sql = "SELECT appliances.id,reference,channel_id,contract_id,manager,os,disc_infos,internal_fqdn,external_fqdn, contracts.end_date,contracts.number,contracts.start_date,contracts.state FROM appliances " .
+ " LEFT JOIN contracts ON appliances.contract_id=contracts.id ".
+ " ORDER BY reference LIMIT $offset,$limit";
+ return $this->query($sql);
+ }
+
+ public function array2Appliance($result){
+ $appliance = new Appliance();
+ if (! $result){
+ return $appliance;
+ }
+ $appliance->importFromArray($result);
+ return $appliance;
+ }
+
+ public function createAppliance(Appliance $appliance) {
+ $query = $this -> pdo -> prepare(
+ 'INSERT INTO appliances (
+ product,
+ manager,
+ reference,
+ internal_fqdn,
+ external_fqdn,
+ os,
+ disc_infos,
+ channel_id,
+ type_hebergement_id,
+ contract_id,
+ uuid
+ ) VALUES (
+ :product,
+ :manager,
+ :reference,
+ :internal_fqdn,
+ :external_fqdn,
+ :os,
+ :disc_infos,
+ :channel_id,
+ :type_hebergement_id,
+ :contract_id,
+ :uuid
+ )'
+ );
+ $query -> bindValue(':product', $appliance -> product);
+ $query -> bindValue(':manager', $appliance -> manager);
+ $query -> bindValue(':reference', $appliance -> reference);
+ $query -> bindValue(':internal_fqdn', $appliance -> internal_fqdn);
+ $query -> bindValue(':external_fqdn', $appliance -> external_fqdn);
+ $query -> bindValue(':os', $appliance -> os);
+ $query -> bindValue(':disc_infos', $appliance -> disc_infos);
+ $query -> bindValue(':channel_id', $appliance -> channel_id);
+ $query -> bindValue(':type_hebergement_id', $appliance -> type_hebergement_id);
+ $query -> bindValue(':contract_id', $appliance -> contract_id);
+ $query -> bindValue(':uuid', $appliance -> uuid);
+ $query -> execute();
+ $query -> closeCursor();
+ return $this->lastInsertId();
+
+ }
+
+ public function updateAppliance(Appliance $appliance) {
+
+ $query = $this -> pdo -> prepare(
+ 'UPDATE appliances
+ SET product = :product,
+ manager = :manager,
+ reference = :reference,
+ internal_fqdn = :internal_fqdn,
+ external_fqdn = :external_fqdn,
+ os = :os,
+ disc_infos = :disc_infos,
+ channel_id = :channel_id,
+ type_hebergement_id = :type_hebergement_id,
+ contract_id = :contract_id,
+ uuid=:uuid,
+ last_appliance_version_id = :last_appliance_version_id
+ WHERE id = :id'
+ );
+ $query -> bindValue(':id', $appliance -> id);
+ $query -> bindValue(':product', $appliance -> product);
+ $query -> bindValue(':manager', $appliance -> manager);
+ $query -> bindValue(':reference', $appliance -> reference);
+ $query -> bindValue(':internal_fqdn', $appliance -> internal_fqdn);
+ $query -> bindValue(':external_fqdn', $appliance -> external_fqdn);
+ $query -> bindValue(':os', $appliance -> os);
+ $query -> bindValue(':disc_infos', $appliance -> disc_infos);
+ $query -> bindValue(':channel_id', $appliance -> channel_id);
+ $query -> bindValue(':type_hebergement_id', $appliance -> type_hebergement_id);
+ $query -> bindValue(':contract_id', $appliance -> contract_id);
+ $query -> bindValue(':uuid', $appliance -> uuid);
+ $query -> bindValue(':last_appliance_version_id', $appliance -> last_appliance_version_id);
+ $query -> execute();
+ $query -> closeCursor();
+ }
+
+ public function checkUploadInfoValidity(array $info) {
+ foreach(
+ array(
+ 'produit',
+ 'version',
+ 'reference',
+ 'fqdn_interne',
+ 'fqdn_externe',
+ 'systeme',
+ 'disques',
+ 'canal',
+ 'usager_type',
+ )
+ as $needed_key) {
+ if (empty($info[$needed_key])) {
+ throw new Exception("La clé $needed_key n'a pas été trouvée dans les informations fournies.");
+ }
+ }
+
+ $productManager = new PdoProductManager();
+ $products = $productManager -> getNamesOfProducts();
+ if(! in_array($info['produit'], $products)) {
+ throw new Exception("Le produit « {$info['produit']} » n'est pas valide.");
+ }
+
+ $versionManager = new PdoVersionManager();
+ $product = $productManager->getProductByName($info['produit']);
+ $versions = $versionManager->findValuesOfVersionsByProductId($product->id);
+ if(! in_array($info['version'], $versions)) {
+ throw new Exception("La version « {$info['version']} » n'est pas disponible pour le produit « {$info['produit']} ».");
+ }
+
+ return true;
+ }
+
+ public function getAppliance($id) {
+
+ $query = $this -> pdo -> prepare('SELECT * FROM appliances WHERE id = :id');
+ $query -> bindValue(':id', $id);
+ $query -> execute();
+
+ $result = $query -> fetch(PDO::FETCH_ASSOC);
+ $appliance = $this->array2Appliance($result);
+
+ $query->closeCursor();
+
+ return $appliance;
+ }
+
+
+
+ public function getApplianceByReference($reference) {
+ $query = $this -> pdo -> prepare('SELECT * FROM appliances WHERE reference = :reference');
+ $query -> bindValue(':reference', $reference);
+ $query -> execute();
+
+ $result = $query -> fetch(PDO::FETCH_ASSOC);
+
+ $appliance = $this->array2Appliance($result);
+
+ $query->closeCursor();
+
+ return $appliance;
+ }
+
+ public function getAppliances() {
+ $query = $this -> pdo -> prepare('SELECT * FROM appliances');
+ $query -> execute();
+
+ $results = $query -> fetchAll(PDO::FETCH_ASSOC);
+ $appliances = array();
+ foreach ($results as $result) {
+ $appliances[] = $this->array2Appliance($result);
+ }
+
+ $query->closeCursor();
+
+ return $appliances;
+ }
+
+ public function removeAppliance(Appliance $appliance) {
+ $query = $this -> pdo -> prepare('DELETE FROM appliances WHERE id = :id');
+ $query -> bindValue('id', $appliance -> id);
+ $query -> execute();
+ }
+
+ public function getInfoByUUID($uuid){
+ $sql = "SELECT reference,manager,os,disc_infos,internal_fqdn,channel_key, channel.libelle as channel_libelle, " .
+ " products.name as produit_name, products.diminutif as produit_diminutif, " .
+ " contracts.number as contract_number, contracts.state as contract_state, " .
+ " contracts.start_date as contract_start_date, contracts.end_date as contract_end_date, ".
+ " customers.ap_id as customer_reference, customers.name as customer_name, ".
+ " customers.ap_contact as contact, ".
+ " appliance_version.creation_date as install_date, " .
+ " versions.value as version, ".
+ " type_hebergement.libelle as type_hebergement_libelle, ".
+ " type_hebergement.type_hebergement_key as type_hebergement_key ".
+ " FROM appliances ".
+ " LEFT JOIN products ON products.id = appliances.product " .
+ " LEFT JOIN contracts ON contracts.id=appliances.contract_id ".
+ " LEFT JOIN customers ON customers.id=contracts.customer_id ".
+ " LEFT JOIN appliance_version ON appliances.last_appliance_version_id=appliance_version.id ".
+ " LEFT JOIN versions ON appliance_version.version_id = versions.id ".
+ " LEFT JOIN channel ON appliances.channel_id=channel.id ".
+ " LEFT JOIN type_hebergement ON appliances.type_hebergement_id=type_hebergement.id " .
+ " WHERE uuid = ?";
+ $result = $this->queryOne($sql,$uuid);
+ return $result;
+ }
+
+ public function getByUUID($uuid){
+ $sql ="SELECT * FROM appliances WHERE uuid = ?";
+ $result = $this->queryOne($sql,$uuid);
+ if (! $result){
+ return false;
+ }
+ $appliance = new Appliance();
+ $appliance->importFromArray($result);
+ return $appliance;
+ }
+
+ public function getNextPatchByUUID($uuid){
+ $sql = "SELECT products.name,versions.value,channel_version.channel_id, patches.script_content FROM appliances " .
+ " JOIN products ON appliances.product=products.id " .
+ " JOIN appliance_version ON appliance_version.id=appliances.last_appliance_version_id ".
+ " JOIN patches ON patches.version_id_from = appliance_version.version_id " .
+ " JOIN channel_version ON channel_version.version_id=patches.version_id_to " .
+ " AND channel_version.channel_id = appliances.channel_id " .
+ " JOIN versions ON patches.version_id_to = versions.id ".
+ " WHERE uuid=? AND channel_version.status = ?";
+ return $this->queryOne($sql,$uuid,ChannelVersion::STATUS_MAJ_OK);
+ }
+
+ public function getInfoByCustomerId($customer_id){
+ $sql = "SELECT appliances.*, products.name as product_name, versions.value as version_value, appliance_version.version_id, appliance_version.is_valide, appliance_version.is_valider FROM appliances ".
+ " JOIN contracts ON appliances.contract_id=contracts.id ".
+ " LEFT JOIN products ON appliances.product=products.id " .
+ " LEFT JOIN appliance_version ON appliance_version.id=appliances.last_appliance_version_id ".
+ " LEFT JOIN versions ON appliance_version.version_id = versions.id ".
+ " WHERE contracts.customer_id=?";
+ return $this->queryOne($sql,$customer_id);
+ }
+
+ public function getInfo($id){
+ $sql = "SELECT appliances.*, products.name as product_name, versions.value as version_value, customers.id as customer_id, appliance_version.version_id, appliance_version.is_valide, appliance_version.is_valider FROM appliances ".
+ " JOIN contracts ON appliances.contract_id=contracts.id ".
+ " LEFT JOIN products ON appliances.product=products.id " .
+ " LEFT JOIN appliance_version ON appliance_version.id=appliances.last_appliance_version_id ".
+ " LEFT JOIN versions ON appliance_version.version_id = versions.id ".
+ " LEFT JOIN customers ON customers.id = contracts.customer_id".
+ " WHERE appliances.id=?";
+ return $this->queryOne($sql,$id);
+ }
+
+ public function getHistorique($id){
+ $sql = "SELECT appliance_version.*,versions.value as version_value, versions.nature as versions_nature FROM appliance_version " .
+ " LEFT JOIN versions ON appliance_version.version_id = versions.id ".
+ " WHERE appliance_id=? ORDER BY creation_date DESC ";
+
+ return $this->query($sql,$id);
+ }
+
+ public function nbAValider(){
+ $sql = "SELECT count(*) FROM appliance_version WHERE is_valider=?";
+ return $this->queryOne($sql,0);
+ }
+
+ public function listAValider($offset,$limit){
+ $sql = "SELECT appliance_version.*, appliances.channel_id, appliances.reference, appliances.uuid, products.name as product_name, versions.value as version_value, contracts.number as contract_number, customers.name as customer_name FROM appliance_version " .
+ " JOIN appliances ON appliance_version.appliance_id =appliances.id ".
+ " JOIN products ON appliances.product=products.id " .
+ " LEFT JOIN versions ON appliance_version.version_id = versions.id ".
+ " JOIN contracts ON appliances.contract_id=contracts.id ".
+ " JOIN customers ON contracts.customer_id=customers.id ".
+ " WHERE is_valider=? LIMIT $offset,$limit";
+ return $this->query($sql,0);
+ }
+
+ public function setValider($id,$is_valide){
+ $sql = "UPDATE appliance_version SET is_valide=?, is_valider=? WHERE id=?";
+ $this->query($sql,$is_valide,1,$id);
+ }
+
+ public function delete($id){
+ $sql = "DELETE FROM appliance_version WHERE appliance_id=?";
+ $this->query($sql,$id);
+ $sql = "DELETE FROM appliances WHERE id=?";
+ $this->query($sql,$id);
+
+
+ }
+
+}