<?php

  class dc_utils extends data
  {

    # ----------------------------------------------------------------------------------------
    #                                                                               categories
    #

    # $path un tableau qui contient une suite de categories, qui peut commencer ou non a la
    # racine des categories et finir ou non par une categorie feuille.
    # dans le tableau, chaque categorie est indiquee soit par son nom (champ "nom" dans la
    # table "categories") soit par son identifiant (champ id). par convention, la fonction
    # fait la distinction entre un nom et un id en fonction du type de variable. si la
    # variable est un entier (verifie avec la fonction php is_int), la fonction considere
    # que c'est un id, sinon, que c'est un nom.
    #
    # retourne l'identifiant de la derniere categorie du tableau $path

    function id_categorie($path)
    { try
      { $ids = null;
        $id = null;
        $path_ok = true;
        foreach($path as $nom)
        { if(is_int($nom)) $id = $nom;
          else
          { $query =
             "SELECT id"
            ." FROM #--categories".(isset($id) ? ", #--categories_tree" : "")
            ." WHERE #--categories.nom=".$this->eq($nom)
            .( isset($id) ?
                " AND #--categories_tree.parent=".$this->eq($id)
               ." AND #--categories_tree.child=#--categories.id"
               :""
             );
             $rst = $this->sql->query($query);
             if($v_rst = $this->sql->fetch_assoc($rst)) $id = (int)$v_rst["id"];
             else $path_ok = false;
             $this->sql->free_result($rst);
             if(!$path_ok) return null;
          }
        }
        return $id;
      }
      catch(Exception $_e) { return false; }
    }

    # retourne les sous-categories de la categorie dont l'id est passe en parametre
    # depth indique la profondeur. si depth vaut 1, seules les sous-categories directes
    # seront retournées. si depth vaut 0, la fonction retournera toute la sous-arborescence
    #
    # le parametre list precise le format de retour. si $list vaut true, la fonction
    # retournera une chaine de caractères comprenant les id des sous-categories, separes
    # par des virgules (pour sql). si $list vaut false, la fonction retournera un array. 
    #
    # la fonction peut prendre un chemin (path, voir plus haut, fonction id_categorie)
    # a la place de l'id.

    function sub_categories($id, $depth = 0, $list = false)
    { if($list) $subs = "";
      else
      { $subs = array();
        $subs["subs"] = array();
      }
      if(!is_int($id)) $id = $this->id_categorie($id);
      if($id !== false)
      { if($list || ($subs = $this->categorie($id)) !== false)
        { if(!$list) $subs["subs"] = array();
          try
          { $query =
             "SELECT child"
            ." FROM #--categories_tree, #--categories"
            ." WHERE #--categories_tree.child=#--categories.id"
            ." AND #--categories_tree.parent=".$this->eq($id)
            ." ORDER BY #--categories.ordre ASC";
            $rst = $this->sql->query($query);
            while($v_rst = $this->sql->fetch_assoc($rst))
            { $_subs = null;
              if(!isset($depth) || $depth > 0) $_subs = $this->sub_categories((int)$v_rst["child"], isset($depth) ? ($depth - 1) : null, $list);
              if(isset($_subs) && $_subs === false) return false;
              if($list)
              { $subs .=
                 ($subs ? "," : "")
                .$v_rst["child"]
                .(isset($_subs) ? $_subs : "");
              }
              else $subs["subs"][$v_rst["child"]] = $_subs;
            }
          }
          catch(Exception $_e) { $subs = false; }
        }
      }
      else $subs = false;
      return $subs;
    }

    function categories($path, $start = 0, $max = null, $WHERE = "", $all = false)
    { $categories = array();
      try
      { if(($parent = $this->id_categorie($path)) !== false)
        { $categories["list"] = array();
          $categories["total"] = 0;
          $query =
           "SELECT count(*) as n"
          ." FROM #--categories, #--categories_tree"
          ." WHERE #--categories.id=#--categories_tree.child"
          ." AND #--categories_tree.parent=".$this->eq($parent)
          .$WHERE;
          $rst = $this->sql->query($query);
          if($v_rst = $this->sql->fetch_assoc($rst)) $categories["total"] = $v_rst["n"];
          $this->sql->free_result($rst);
          if($categories["total"] > 0)
          { $query =
             "SELECT #--categories.*"
            ." FROM #--categories, #--categories_tree"
            ." WHERE #--categories.id=#--categories_tree.child"
            ." AND #--categories_tree.parent=".$this->eq($parent)
            .$WHERE
            ." ORDER BY ordre ASC"
            .( $all ?
                 ""
               : ( isset($max) ?
                     " LIMIT ".$start.",".$max
                   : ($this->env->config("max_list") ? " LIMIT ".$start.",".$this->env->config("max_list") : "")
                 )
             );
            $rst = $this->sql->query($query);
            while($v_rst = $this->sql->fetch_assoc($rst)) $categories["list"][$v_rst["id"]] = $v_rst;
            $this->sql->free_result($rst);
          }
        }
        else $categories = false;
      }
      catch(Exception $_e) { $categories = false; }
      return $categories;
    }

    function categorie($id)
    { $categorie = array();
      try
      { $query =
         "SELECT *"
        ." FROM #--categories"
        ." WHERE #--categories.id=".$this->eq($id);
        $rst = $this->sql->query($query);
        if($v_rst = $this->sql->fetch_assoc($rst)) $categorie = $v_rst;
        $this->sql->free_result($rst);
      }
      catch(Exception $_e) { $categorie = false; }
      return $categorie;
    }

    function add_categorie($nom = null, $titre = null, $icone = null, $description = null, $contenu = null, $parent = null)
    { try
      { $query =
         "INSERT #--categories(nom, titre, icone, description, contenu) VALUES"
        ."( ".$this->eq($nom)
        .", ".$this->eq($titre)
        .", ".$this->eq($icone)
        .", ".$this->eq($description)
        .", ".$this->eq($contenu)
        .")";
        $this->sql->query($query);
        $id = $this->sql->insert_id();
        if(isset($parent) && $this->set_in_categorie($parent, $id)) return $id;
      }
      catch(Exception $_e) {}
      return false;
    }

    function set_categorie($id, $nom = null, $titre = null, $icone = null, $description = null, $contenu = null, $ordre = null)
    { try
      { $query =
         "UPDATE #--categories SET"
        ."  nom=".$this->eq($nom)
        .", titre=".$this->eq($titre)
        .", icone=".$this->eq($icone)
        .", description=".$this->eq($description)
        .", contenu=".$this->eq($contenu)
        .(isset($ordre) ? ", ordre=".$this->eq($ordre) : "")
        ." WHERE ".$this->env->bdd["prefix"]."categories.id=".$this->eq($id);
        $this->sql->query($query);
        return true;
      }
      catch(Exception $_e) {}
      return false;
    }

    function del_categorie($id, $recursif = false)
    { if(($categorie = $this->categorie($id)) !== false)
      { if($recursif) return $this->r_del_categorie($categorie);
        return $this->_del_categorie($categorie);
      }
      return false;
    }

    function _del_categorie($categorie)
    { try
      { $sql =
         "DELETE FROM #--categories"
        ." WHERE #--categories.id=".$this->eq($categorie["id"]);
        $this->sql->query($sql);
        $sql =
         "DELETE FROM #--categories_tree"
        ." WHERE #--categories_tree.child=".$this->eq($categorie["id"]);
        $this->sql->query($sql);
        $this->del_categorie_icone($categorie["icone"]);
        return true;
      }
      catch(Exception $_e) {}
      return false;
    }

    function r_del_categorie($categorie)
    { $OK = true;
      try
      { $sql =
         "SELECT #--categories.*"
        ." FROM #--categories"
        .", #--categories_tree"
        ." WHERE #--categories.id=#--categories_tree.child"
        ." AND #--categories_tree.parent=".$this->eq($categorie["id"]);
        $rst = $this->sql->query($sql);
        while($OK && $v_rst = $this->sql->fetch_assoc($rst)) $OK = $this->r_del_categorie($v_rst);
        $this->sql->free_result($rst);
        if($OK)
        { $sql =
           "SELECT id"
          ." FROM #--contenus"
          ." WHERE #--contenus.categorie=".$this->eq($categorie["id"]);
          $rst = $this->sql->query($sql);
          while($OK && $v_rst = $this->sql->fetch_assoc($rst)) $OK = $this->del_contenu($v_rst["id"]);
          $this->sql->free_result($rst);
          if($OK) $OK = $this->_del_categorie($categorie);
        }
      }
      catch(Exception $_e) { $OK = false;  }
      return $OK;
    }

    function del_categorie_icone($icone)
    { try
      { $delete_icone = false;
        if($icone && file_exists($icone))
        { try
          { $query =
             "SELECT count(*) as n"
            ." FROM #--categories"
            ." WHERE icone=".$this->eq($icone);
            $rst = $this->sql->query($query);
            if($v_rst = $this->sql->fetch_assoc($rst)) $delete_icone = $v_rst["n"] == 1;
            $this->sql->free_result($rst);
          }
          catch(Exception $_e) { return false; }
        }
        if($delete_icone) @unlink($icone);
        return true;
      }
      catch(Exception $_e) {}
      return false;
    }

    function in_categorie($parent, $child)
    { $n = false;
      try
      { $query =
         "SELECT count(*) as n FROM #--categories_tree"
        ." WHERE parent=".$parent
        ." AND child=".$child;
        $rst = $this->sql->query($query);
        if($v_rst = $this->sql->fetch_assoc($rst)) $n = $v_rst["n"]; 
        $this->sql->free_result($rst);
      }
      catch(Exception $_e) {}
      return $n;
    }

    function set_in_categorie($parent, $child)
    { try
      { if(($n = $this->in_categorie($parent, $child)) !== false)
        { if($n == 0)
          { $query =
             "INSERT INTO #--categories_tree VALUES"
            ."( ".$parent
            .", ".$child
            .")";
            $this->sql->query($query);
          }
          return true;
        }
      }
      catch(Exception $_e) {}
      return false;
    }

    function remove_from_categorie($parent, $child)
    { try
      { $query =
         "DELETE FROM #--categories_tree"
        ." WHERE parent=".$parent
        ." AND child=".$child;
        $this->sql->query($query);
        return true;
      }
      catch(Exception $_e) {}
      return false;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                                 contenus
    #

    function contenus($path, $start = 0, $max = null, $WHERE = "", $depth = null, $ALL = false, $ORDRE = false)
    { $contenus = false;
      if(($id_categorie = $this->id_categorie($path)) !== false)
      { if(isset($id_categorie))
        { if(!isset($depth) || $depth > 0)
          { if(($subs = $this->sub_categories($id_categorie, $depth, true)) !== false)
            { $id_categorie .= $subs ? ",".$subs : "";
            }
            else $id_categorie = false;
          }
          if($id_categorie !== false)
          { try
            { $all = $id_categorie == $id_news_root;
              $contenus = array("list" => array(), "total" => 0);
              $query =
               "SELECT count(*) as n"
              ." FROM #--contenus"
              ." WHERE #--contenus.categorie IN (".$id_categorie.")"
              .$WHERE;
              $rst = $this->sql->query($query);
              if($v_rst = $this->sql->fetch_assoc($rst)) $contenus["total"] = $v_rst["n"];
              $this->sql->free_result($rst);
              if($contenus["total"] > 0)
              { $query =
                 "SELECT #--contenus.*"
                .", #--pun_users.username as auteur"
                .", #--categories.nom as categorie_nom"
                .", #--categories.titre as categorie_titre"
                .", #--categories.icone as categorie_icone"
                ." FROM #--pun_users, #--contenus"
                ." LEFT JOIN #--categories"
                ." ON #--categories.id=#--contenus.categorie"
                ." WHERE #--contenus.auteur_creation=#--pun_users.id"
                ." AND #--contenus.categorie IN (".$id_categorie.")"
                .$WHERE
                .($ORDRE ? " ORDER BY #--contenus.ordre ASC" : " ORDER BY date_publication DESC")
                .( $ALL ?
                     ""
                   : ( isset($max) ?
                         " LIMIT ".$start.",".$max
                       : ($this->env->config("max_list") ? " LIMIT ".$start.",".$this->env->config("max_list") : "")
                     )
                 );
                $rst = $this->sql->query($query);
                while($v_rst = $this->sql->fetch_assoc($rst)) $contenus["list"][$v_rst["id"]] = $v_rst;
                $this->sql->free_result($rst);
              }
            }
            catch(Exception $_e) { $contenus = false; }
          }
        }
        else $contenus = array("list" => array(), "total" => 0);
      }
      return $contenus;
    }

    function contenu($id)
    { $contenu = array();
      try
      { $query =
         "SELECT #--contenus.*"
        .", #--pun_users.username as auteur"
        .", #--categories.id as categorie_id"
        .", #--categories.titre as categorie_titre"
        .", #--categories.icone as categorie_icone"
        ." FROM #--pun_users, #--contenus"
        ." LEFT JOIN #--categories"
        ." ON #--categories.id=#--contenus.categorie"
        ." WHERE #--contenus.auteur_creation=#--pun_users.id"
        ." AND #--contenus.id=".$this->eq($id);
        $rst = $this->sql->query($query);
        if($v_rst = $this->sql->fetch_assoc($rst)) $contenu = $v_rst;
        $this->sql->free_result($rst);
      }
      catch(Exception $_e) { $contenu = false; }
      return $contenu;
    }

    function add_contenu($nom, $categorie, $titre, $description, $contenu, $auteur, $date_publication, $public)
    { try
      { $query =
         "INSERT INTO #--contenus(nom, categorie, titre, description, contenu, auteur_creation, date_creation, date_publication, public) VALUES"
        ."( ".$this->eq($nom)
        .", ".$this->eq($categorie)
        .", ".$this->eq($titre)
        .", ".$this->eq($description)
        .", ".$this->eq($contenu)
        .", '".$auteur."'"
        .", NOW()"
        .", ".(strlen($date_publication) > 0 ? "'".$date_publication."'" : "NOW()")
        .", '".$public."'"
        .")";
        $rst = $this->sql->query($query);
        return true;
      }
      catch(Exception $_e) { return false; }
      return true;
    }

    function set_contenu($id, $nom, $categorie, $titre, $description, $contenu, $auteur, $date_publication, $public)
    { try
      { $query =
         "UPDATE #--contenus SET"
        ."  nom=".$this->eq($nom)
        .", categorie=".$this->eq($categorie)
        .", titre=".$this->eq($titre)
        .", description=".$this->eq($description)
        .", contenu=".$this->eq($contenu)
        .", auteur_modification='".$auteur."'"
        .", date_modification=NOW()"
        .", date_publication=".($date_publication ? "'".$date_publication."'" : "NOW()")
        .", public='".$public."'"
        ." WHERE id=".$id;
        $rst = $this->sql->query($query);
      }
      catch(Exception $_e) { return false; }
      return true;
    }

    function set_contenu_ordre($id, $ordre)
    { try
      { $query = "UPDATE #--contenus SET ordre=".(strlen($ordre) > 0 ? $ordre : "NULL")." WHERE id=".$id;
        $this->sql->query($query);
      }
      catch(Exception $_e) { return false; }
      return true;
    }

    # deplace tous les contenus (sans recursion) de la categorie $from
    # dans la categorie $to

    function move_contenus($from, $to = null)
    { try
      { $sql =
         "UPDATE #--contenus SET"
        ." categorie=".$this->eq($to)
        ." WHERE categorie=".$from;
        $this->sql->query($sql);
      }
      catch(Exception $_e) { return false; }
      return true;
    }

    function del_contenu($id)
    { try
      { $query = "DELETE FROM #--contenus WHERE id=".$id;
        $rst = $this->sql->query($query);
      }
      catch(Exception $_e) { return false; }
      return true;
    }

  }

?>