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

<?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 smart_make_associations($cat_id)
{
  $query = '
DELETE FROM '.IMAGE_CATEGORY_TABLE.' 
  WHERE 
    category_id = '.$cat_id.' 
    AND smart = true
;';
  pwg_query($query);
  
  $images = smart_get_pictures($cat_id);
  
  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, 
      array_keys($datas[0]), 
      $datas
      );
  }
  
  // represantant, try to not overwrite if still in images list
  $query = '
SELECT representative_picture_id
  FROM '.CATEGORIES_TABLE.'
  WHERE id = '.$cat_id.'
;';
  list($rep_id) = pwg_db_fetch_row(pwg_query($query));
  
  if ( !in_array($rep_id, $images) )
  {
    if (!function_exists('set_random_representant'))
    {
      include(PHPWG_ROOT_PATH.'admin/include/functions.php');
    }
    set_random_representant(array($cat_id));
  }
  
  return $images;
}


/*
 * Make associations for all SmartAlbums
 * Called with invalidate_user_cache
 */
function smart_make_all_associations()
{
  global $conf;
    
  if (!is_array($conf['SmartAlbums'])) $conf['SmartAlbums'] = unserialize($conf['SmartAlbums']);
  
  if ( defined('SMART_NOT_UPDATE') OR !$conf['SmartAlbums']['update_on_upload'] ) return;
  
  /* get categories with smart filters */
  $query = '
SELECT DISTINCT id
  FROM '.CATEGORIES_TABLE.' AS c
    INNER JOIN '.CATEGORY_FILTERS_TABLE.' AS cf
    ON c.id = cf.category_id
;';
  
  /* regenerate photo list */
  $smart_cats = array_from_query($query, 'id');
  array_map('smart_make_associations', $smart_cats);
}


/*
 * 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 smart_get_pictures($cat_id, $filters = null)
{
  global $conf;

  /* get filters */
  if ($filters == null)
  {
    $filters = array();
    
    $query = '
SELECT * 
  FROM '.CATEGORY_FILTERS_TABLE.' 
  WHERE category_id = '.$cat_id.' 
  ORDER BY type ASC, cond ASC
;';
    $result = pwg_query($query);
    
    if (!pwg_db_num_rows($result)) return $filters;
    
    while ($row = pwg_db_fetch_assoc($result))
    {
      $filters[] = array(
        'type' => $row['type'],
        'cond' => $row['cond'],
        'value' => $row['value'],
        );
    }
  }
    
  /* 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')
    {
      switch ($filter['cond'])
      {
        case 'the':
          $where[] = 'date_available BETWEEN "'.$filter['value'].' 00:00:00" AND "'.$filter['value'].' 23:59:59"';
          break;
        case 'before':
          $where[] = 'date_available < "'.$filter['value'].' 00:00:00"';
          break;
        case 'after':
          $where[] = 'date_available > "'.$filter['value'].' 23:59:59"';
          break;
        case 'the_crea':
          $where[] = 'date_creation BETWEEN "'.$filter['value'].' 00:00:00" AND "'.$filter['value'].' 23:59:59"';
          break;
        case 'before_crea':
          $where[] = 'date_creation < "'.$filter['value'].' 00:00:00"';
          break;
        case 'after_crea':
          $where[] = 'date_creation > "'.$filter['value'].' 23:59:59"';
          break;
      }
    }
    // limit
    else if ($filter['type'] == 'limit')
    {
      $limit = '0, '.$filter['value'];
    }
  }
  
  /* bluid query */
  $MainQuery = '
SELECT i.id
  FROM '.IMAGES_TABLE.' AS i';
    
    if (isset($join))
    {
      foreach ($join as $query)
      {
        $MainQuery .= '
    LEFT JOIN '.$query;
      }
    }
    if (isset($where))
    {
      $MainQuery .= '
  WHERE';
      $i = 0;
      foreach ($where as $query)
      {
        if ($i != 0) $MainQuery .= ' AND';
        $MainQuery .= '
    '.$query;
        $i++;
      }
    }

    $MainQuery .= '
  GROUP BY i.id
 '.$conf['order_by_inside_category'].'
  '.(isset($limit) ? "LIMIT ".$limit : null).'
;';

  return array_from_query($MainQuery, 'id');
}


/**
 * Check if the filter is proper
 * @param array filter
 * @return array or false
 */
function smart_check_filter($filter)
{
  global $limit_is_set, $page;
  $error = false;
  
  # tags
  if ($filter['type'] == 'tags')
  {
    if ($filter['value'] == null)
    {
      $error = true;
      array_push($page['errors'], l10n('No tag selected'));
    }
    else
    {
      $filter['value'] = implode(',', get_tag_ids($filter['value']));
    }
  }
  # date
  else if ($filter['type'] == 'date')
  {
    if (!preg_match('#([0-9]{4})-([0-9]{2})-([0-9]{2})#', $filter['value']))
    {
      $error = true;
      array_push($page['errors'], l10n('Date string is malformed'));
    }
  }
  # limit
  else if ($filter['type'] == 'limit')
  {
    if (!preg_match('#([0-9]{1,})#', $filter['value']))
    {
      $error = true;
      array_push($page['errors'], l10n('Limit must be an integer'));
    }
    else if ($limit_is_set == true) // only one limit is allowed, first is saved
    {
      $error = true;
      array_push($page['errors'], l10n('You can\'t use more than one limit'));
    }
    else
    {
      $limit_is_set = true;
    }
  }
  
  # out
  if ($error == false)
  {
    return $filter;
  }
  else
  {
    return false;
  }
}


/**
 * 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);
  }
}
?>