/**
     * return an html list of found keywords in the images_tags table
     *
     * @return String : html formatted list
     */
    private function ajax_amd_admin_tagsGetKeywords()
    {
      global $template;

      $returned=array();
      $keywordsList=array();
      $sql="SELECT pait.value, pait.imageId, paut.numId, paut.tagId
            FROM (".$this->tables['images_tags']." pait
              JOIN ".$this->tables['used_tags']." paut ON pait.numId = paut.numId)

            WHERE (paut.tagId = 'xmp.dc:subject' OR
                   paut.tagId = 'xmp.digiKam:TagsList' OR
                   paut.tagId = 'iptc.Keywords');";
      $result=pwg_query($sql);
      if($result)
      {
        while($row=pwg_db_fetch_assoc($result))
        {
          if(preg_match('/^a:\d+:{/', $row['value']))
          {
            /*
             *  if value is a serialized string, unserialize and process it
             */
            $tmp=unserialize($row['value']);
            foreach($tmp['values'] as $val)
            {
              if($row['tagId']=='xmp.digiKam:TagsList')
              {
                $list=explode('/', $val);
                foreach($list as $subTag)
                {
                  $keywordsList[]="('".mysql_escape_string(trim($subTag))."', ".$row['imageId'].")";
                }
              }
              else
              {
                $keywordsList[]="('".mysql_escape_string($val)."', ".$row['imageId'].")";
              }
            }
          }
          else
          {
            $keywordsList[]="('".mysql_escape_string($row['value'])."', ".$row['imageId'].")";
          }
        }

        if(count($keywordsList)>0)
        {
          $sql="CREATE TEMPORARY TABLE amd_temp_tags (
                  `value` CHAR(255) default '',
                  `imageId` mediumint(8) unsigned NOT NULL default '0',
                  PRIMARY KEY  USING BTREE (`value`,`imageId`)
                ) CHARACTER SET utf8 COLLATE utf8_general_ci;";
          if(pwg_query($sql))
          {
            $sql="INSERT IGNORE INTO amd_temp_tags
              VALUES ".implode(',', $keywordsList);
            if(pwg_query($sql))
            {
              $sql="SELECT att.value AS value,
                      COUNT(DISTINCT att.imageId) AS nbPictures,
                      IF(ptt.name IS NULL, 'n', 'y') AS tagExists,
                      COUNT(DISTINCT pit.image_id) AS nbPicturesTagged
                    FROM (amd_temp_tags att LEFT JOIN ".TAGS_TABLE."  ptt ON att.value = ptt.name)
                      LEFT JOIN ".IMAGE_TAG_TABLE." pit ON pit.tag_id = ptt.id
                    GROUP BY att.value
                    HAVING nbPicturesTagged < nbPictures";
              $result=pwg_query($sql);
              if($result)
              {
                $i=0;
                while($row=pwg_db_fetch_assoc($result))
                {
                  $row['id']=$i;
                  $returned[]=$row;
                  $i++;
                }
              }
            }
          }
        }
      }

      $template->set_filename('keywordsList',
                    dirname($this->getFileLocation()).'/admin/amd_metadata_tags_iKeywordsList.tpl');

      $template->assign('datas', $returned);
      return($template->parse('keywordsList', true));
    }


    /**
     * convert given keywords into tags, and associate them to pictures
     *
     * @param Array $keywords : an array of strings
     * @return String : ok or ko
     */
    private function ajax_amd_admin_tagsConvertKeywords($keywords)
    {
      global $template;

      $returned='ko';

      /*
       * 1/ build a temp table with all couple of keywords/imageId
       */
      $keywordsList=array();
      $sql="SELECT pait.value, pait.imageId, paut.numId, paut.tagId
            FROM (".$this->tables['images_tags']." pait
              JOIN ".$this->tables['used_tags']." paut ON pait.numId = paut.numId)

            WHERE (paut.tagId = 'xmp.dc:subject' OR
                   paut.tagId = 'xmp.digiKam:TagsList' OR
                   paut.tagId = 'iptc.Keywords');";
      $result=pwg_query($sql);
      if($result)
      {
        while($row=pwg_db_fetch_assoc($result))
        {
          if(preg_match('/^a:\d+:{/', $row['value']))
          {
            /*
             *  if value is a serialized string, unserialize and process it
             */
            $tmp=unserialize($row['value']);
            foreach($tmp['values'] as $val)
            {
              if($row['tagId']=='xmp.digiKam:TagsList')
              {
                $list=explode('/', $val);
                foreach($list as $subTag)
                {
                  $keywordsList[]="('".mysql_escape_string(trim($subTag))."', ".$row['imageId'].")";
                }
              }
              else
              {
                $keywordsList[]="('".mysql_escape_string($val)."', ".$row['imageId'].")";
              }
            }
          }
          else
          {
            $keywordsList[]="('".mysql_escape_string($row['value'])."', ".$row['imageId'].")";
          }
        }
        $sql="CREATE TEMPORARY TABLE amd_temp_tags (
                `value` CHAR(255) default '',
                `imageId` mediumint(8) unsigned NOT NULL default '0',
                PRIMARY KEY  USING BTREE (`value`,`imageId`)
              ) CHARACTER SET utf8 COLLATE utf8_general_ci;";
        if(pwg_query($sql))
        {
          $sql="INSERT IGNORE INTO amd_temp_tags
            VALUES ".implode(',', $keywordsList);
          if(pwg_query($sql))
          {
            foreach($keywords as $key => $val)
            {
              $keywords[$key]="(att.value LIKE '".mysql_escape_string($val)."')";
            }
            /*
             * 2/ join temp table with piwigo tags table, found the keywords
             *    that don't have a corresponding keyword
             */
            $sql="SELECT DISTINCT att.value
                  FROM amd_temp_tags att LEFT JOIN ".TAGS_TABLE." ptt ON att.value = ptt.name
                  WHERE ptt.id IS NULL
                    AND".implode(' OR ', $keywords);
            $result=pwg_query($sql);
            if($result)
            {
              $sql=array();
              while($row=pwg_db_fetch_assoc($result))
              {
                $sql[]="('', '".mysql_escape_string($row['value'])."', '".mysql_escape_string(str2url($row['value']))."')";
              }
              if(count($sql)>0)
              {
                $sql="INSERT INTO ".TAGS_TABLE." VALUES ".implode(',', $sql);
                pwg_query($sql);
              }
            }

            /*
             * 3/ join temp table with piwigo tags table, associate piwigo tagId
             *    to the keywords (at this step, all keyword can be associated
             *    with a piwigo tagId)
             */
            $sql="INSERT IGNORE INTO ".IMAGE_TAG_TABLE."
                    SELECT DISTINCT att.imageId, ptt.id
                    FROM amd_temp_tags att LEFT JOIN ".TAGS_TABLE." ptt ON att.value = ptt.name
                    WHERE ".implode(' OR ', $keywords);
            $result=pwg_query($sql);
            $returned='ok';
          }
        }
      }

      return($returned);
    }