source: extensions/SmartAlbums/include/functions.inc.php @ 10871

<?php
if (!defined('PHPWG_ROOT_PATH')) die('Hacking attempt!');

/*
 * Associates images to the category according to the filters
 * @param int category_id
 * @return array
 */
function SmartAlbums_make_associations($cat_id)
{
  pwg_query("DELETE FROM ".IMAGE_CATEGORY_TABLE." WHERE category_id = ".$cat_id." AND smart = true;");
  
  $images = SmartAlbums_get_pictures($cat_id);
  $dbfields = array('image_id', 'category_id', 'smart');
  
  if (count($images) != 0)
  {
    foreach ($images as $img)
    {
      $datas[] = array(
        'image_id' => $img,
        'category_id' => $cat_id,
        'smart' => true,
      );
    }
    mass_inserts_ignore(IMAGE_CATEGORY_TABLE, $dbfields, $datas);
  }
  
  return $images;
}


/*
 * Generates the list of images, according to the filters of the category
 * @param int category_id
 * @param array filters, if null => catch from db
 * @return array
 */
function SmartAlbums_get_pictures($cat_id, $filters = null)
{
  global $conf;

  /* get filters */
  if ($filters == null)
  {
    $filters = pwg_query("SELECT * FROM ".CATEGORY_FILTERS_TABLE." WHERE category_id = ".$cat_id." ORDER BY type ASC, cond ASC;");
    if (!pwg_db_num_rows($filters)) return array();
    
    while ($filter = pwg_db_fetch_assoc($filters))
    {
      $temp[] = array(
        'type' => $filter['type'],
        'cond' => $filter['cond'],
        'value' => $filter['value'],
      );
    }
     
    $filters = $temp;
  }
    
  /* build constrains */
  ## generate 'join', 'where' arrays and 'limit' string to create the SQL query
  ## inspired by PicsEngine by Michael Villar
  $i_tags = 1;
  foreach ($filters as $filter)
  {
    // tags
    if ($filter['type'] == 'tags')
    {
        if($filter['cond'] == "all")
        {
          $tags_arr = explode(',', $filter['value']);
          
          foreach($tags_arr as $value)
          {
            $join[] = "".IMAGE_TAG_TABLE." AS it_$i_tags ON i.id = it_$i_tags.image_id";
            $where[] = "it_$i_tags.tag_id = ".$value."";
            $i_tags++;
          }
        }
        else if ($filter['cond'] == 'one') 
        {
          $join[] = "".IMAGE_TAG_TABLE." AS it_$i_tags ON i.id = it_$i_tags.image_id";
          $where[] = "it_$i_tags.tag_id IN (".$filter['value'].")";
          $i_tags++;
        }
        else if ($filter['cond'] == 'none') 
        {
          $sub_query = "SELECT it_$i_tags.image_id
            FROM ".IMAGE_TAG_TABLE." AS it_$i_tags
            WHERE it_$i_tags.image_id = i.id
            AND it_$i_tags.tag_id IN (".$filter['value'].")
            GROUP BY it_$i_tags.image_id";
          $where[] = "NOT EXISTS (".$sub_query.")";
          $i_tags++;
        }
        else if ($filter['cond'] == 'only') 
        {
          $sub_query = "SELECT it_$i_tags.image_id
            FROM ".IMAGE_TAG_TABLE." AS it_$i_tags
            WHERE it_$i_tags.image_id = i.id
            AND it_$i_tags.tag_id NOT IN (".$filter['value'].")
            GROUP BY it_$i_tags.image_id";
          $where[] = "NOT EXISTS (".$sub_query.")";
        
          $i_tags++;
          $tags_arr = explode(',', $filter['value']);
          
          foreach($tags_arr as $value)
          {
            $join[] = "".IMAGE_TAG_TABLE." AS it_$i_tags ON i.id = it_$i_tags.image_id";
            $where[] = "it_$i_tags.tag_id = ".$value."";
            $i_tags++;
          }
        }        
    }
    // date
    else if ($filter['type'] == 'date')
    {
      if ($filter['cond'] == 'the')         $where[] = "date_available BETWEEN '".$filter['value']." 00:00:00' AND '".$filter['value']." 23:59:59'";
      else if ($filter['cond'] == 'before') $where[] = "date_available < '".$filter['value']." 00:00:00'";
      else if ($filter['cond'] == 'after')  $where[] = "date_available > '".$filter['value']." 23:59:59'";
    }
    // limit
    else if ($filter['type'] == 'limit')
    {
      $limit = "0, ".$filter['value'];
    }
  }
  
  /* bluid query */
  $MainQuery = "SELECT i.id 
    FROM (
      SELECT i.id
      FROM ".IMAGES_TABLE." AS i"."\n";
      
      if (isset($join))
      {
        foreach ($join as $query)
        {
          $MainQuery .= "LEFT JOIN ".$query."\n";
        }
      }
      if (isset($where))
      {
        $MainQuery .= "WHERE"."\n";
        $i = 0;
        foreach ($where as $query)
        {
          if ($i != 0) $MainQuery .= "AND ";
          $MainQuery .= $query."\n";
          $i++;
        }
      }
  
      $MainQuery .= "GROUP BY i.id
      ".$conf['order_by']."
      ".(isset($limit) ? "LIMIT ".$limit : null)."
    ) AS i
  ";
  
  return array_from_query($MainQuery, 'id');
}


/**
 * inserts multiple lines in a table, ignore duplicate entries
 *
 * @param string table_name
 * @param array dbfields
 * @param array inserts
 * @return void
 */
function mass_inserts_ignore($table_name, $dbfields, $datas)
{
  if (count($datas) != 0)
  {
    $first = true;

    $query = 'SHOW VARIABLES LIKE \'max_allowed_packet\'';
    list(, $packet_size) = pwg_db_fetch_row(pwg_query($query));
    $packet_size = $packet_size - 2000; // The last list of values MUST not exceed 2000 character*/
    $query = '';

    foreach ($datas as $insert)
    {
      if (strlen($query) >= $packet_size)
      {
        pwg_query($query);
        $first = true;
      }

      if ($first)
      {
        $query = '
INSERT IGNORE INTO '.$table_name.'
  ('.implode(',', $dbfields).')
  VALUES';
        $first = false;
      }
      else
      {
        $query .= '
  , ';
      }

      $query .= '(';
      foreach ($dbfields as $field_id => $dbfield)
      {
        if ($field_id > 0)
        {
          $query .= ',';
        }

        if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
        {
          $query .= 'NULL';
        }
        else
        {
          $query .= "'".$insert[$dbfield]."'";
        }
      }
      $query .= ')';
    }
    pwg_query($query);
  }
}
?>