Adding your own news
====================

Infusion for phpFusion v 7, Autonews v 1.1
------------------------------------------

It is possible for users with a good knowledge about PHP-Fusion to add or change
the available types of news. The way news are retrieved and formatted are defined
in autonews_data.php, which is the only file you'll need to change.

All data needed to retrive and format data are defined in the array $autonews_data.
By adding one new element to this array, you will be able to provide different or other 
types of news. New elements added will automatically appear in the admin panel.

The following comments apply for users who want to experiment with this.

First some comments to the content of the array, to which we need to add an
element:

'id'    is a unique id number, which shall be identical to the key.
'type'  is the name of this section of news, as it will be shown in admin
'text'  is the comment text as shown in the mail. It may contain various
        variables, that are automatically substituted upon generating the mail. 
        A variable is specified in sprintf php format as follows:
            %1$s: Denotes the timestamp if the news formatted as a string
            %2$s: Denotes a clickable link of the user who authored the news
            %3$s: Denotes an integer returned from the query
            %4$s: Denotes a text string returned from the query and parser
            %5$s: Denotes an integer returned from the query
            %6$s: Denotes a text string returned from the query and parser
'type1' Name of the news in singularis (ie "article")
'type2' Name of the news in pluralis (ie "articles")
        type1 and type2 are used if there are too many news and they have to be
        combined into one last summary news line
'parse1'php code to return text string 1 (%3$s)
'parse1'php code to return text string 2 (%5$s)
'query' mysql query to return the news to include. Should obey phpfusion access 
        restrictions and should limit news to those which are never than $ts_cut.
        Must return the following columns:
            timestamp:  Timestamp of the news as unix timestamp  
            user_id:    phpfusion user id of the authoring user
            user_name:  phpfusion user name of the authoring user
            item_id:    Integer holding the first item_id (ie article id)
            item_no:    Integer holding the first counter
            item_text:  String holding the first text
            item2_id:   Integer holding the second item_id (ie article id)
            item2_no:   Integer holding the second counter
            item2_text: String holding the second text

Not directly understandable or what? Let's try an example:

We will add a news regarding users who have birthday within one week from sending
the news. Users are stored in the the DB_USERS table and their birthday is stored
as a date field in the users_bithday field.

Finding those users is not exactly straightforward, but can be accomplished 
by the following query:

  SELECT
    user_id, 
    user_name,
    CASE WHEN 
      user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR < CURDATE() 
    THEN 
      DATEDIFF(user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate) + 1) YEAR, CURDATE()) 
    ELSE 
      DATEDIFF(user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR, CURDATE()) 
    END AS daystobirthday, 
    CASE WHEN 
      user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR < CURDATE() 
    THEN 
      YEAR(CURDATE()) - YEAR(user_birthdate) + 1  
    ELSE 
      YEAR(CURDATE()) - YEAR(user_birthdate)  
    END AS age, 
    DATE_FORMAT(user_birthdate, '%c-%d') AS item_text 
  FROM ".DB_USERS." 
  WHERE user_birthdate > 0 
  HAVING daystobirthday <= 7 
  ORDER BY daystobirthday, user_name; 

Test it in phpmyadmin if you like. Remember to replace DB_USERS with the real
name of your table.
  
There are probably better ways to achieve this, but what the query does is that
it returns the date in 'monthandday' (as m-d), the 'daystobirthday' and 
'age', and limit the query to those who has entered a birthday and those with 7
or less days to their birthdate.

What we need to do next is to modify the query to return the fields required (see
above under query). As the timestamp we will use 'now'. This has the 
consequence that all birthdays are reported first, because they are considered
newest. As they are in the future I'll assume this is ok.

The query will then look:

  SELECT
    NOW() AS timestamp,
    user_id, 
    user_name,
    0 AS item_id,
    CASE WHEN 
      user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR < CURDATE() 
    THEN 
      YEAR(CURDATE()) - YEAR(user_birthdate) + 1  
    ELSE 
      YEAR(CURDATE()) - YEAR(user_birthdate)  
    END AS item_no, 
    DATE_FORMAT(user_birthdate, '%c-%d') AS item_text,
    0 AS item2_id,
    CASE WHEN 
      user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR < CURDATE() 
    THEN 
      DATEDIFF(user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate) + 1) YEAR, CURDATE()) 
    ELSE 
      DATEDIFF(user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR, CURDATE()) 
    END AS item2_no,
    '' AS item2_text 
  FROM ".DB_USERS." 
  WHERE user_birthdate > 0 
  HAVING item_id <= 7 
  ORDER BY item_id, user_name; 

It's important that all fields are there, so we will include those that we do
not need as blanks or zeros. Think of them as fields that you can use to convey
the variable information that you need to insert in your message.

The news we are going to present shall look something like:

  "Peter turns 34 on 3-12. Congratulations from this site!"
  
The query is about retrieving the data we need. We have both the age and the date.
We can now use the variable substitutions mentioned above under 'text' (they work
like in printf):

  "%2$s turns %3$s on %4$s. Congratulations from %6$s!"
  
You may have noticed that this line relies on returning your sitename (%5$s), but
this is not being returned from the query. This takes us to the parse1 and parse2 
lines.

Quite often you'll need further processing of the information returned from the
query, that can not or can only with difficulties carry out in SQL. For this
reason you have the possibiliuty to specify PHP code that are executed.

If you do not specify a parse code, the text strings (item_text and item2_text)
will just go directly from the query and into the mail. But if you do specify
it, the code will be executed and item_text and item2_text will be replaced by
the return value from the parse code.

To demonstrate this, we will parse both text fields.

The first text field (item_text) holds the date in the format m-d. We could have
created any date formatting in SQL but there is the risk that this will not
adhere to the chosen locale in PHP. One code for a function to transform 'm-d' 
to a properly formatted local date and return it is:

  {
    $d = explode('-', $data['item_text']);
    $bd = mktime(0, 0, 0, $d[0], $d[1], date('Y'));
    if ($bd < time()) $bd = mktime(0, 0, 0, $d[0], $d[1], date('Y')+1);
    return strftime('%e %b', $bd);
  }

where the 'if' accounts for the situations where the birthday is the following
year.
  
To prepare it to go into the data array, we need to escape it:

  {
    $d = explode(\'-\', $data[\'item_text\']);
    $bd = mktime(0, 0, 0, $d[0], $d[1], date(\'Y\'));
    if ($bd < time()) $bd = mktime(0, 0, 0, $d[0], $d[1], date(\'Y\')+1);
    return strftime(\'%e %b\', $bd);
  }

For the second text field (item2_text) the query returned an empty string.
This does not prevent us from replacing it with the site name with this code:

  return $settings[\'sitename\'];
  
We are now ready to combine the bits for a new entry in the autonews_data.php
file:

  // Birthdays
  14 => array(
    'id'    => 14,
    'type'  => 'Birthdays',
    'text'  => '%2$s turns %3$s on %4$s. Congratulations from %6$s!',
    'type1' => 'birthday',
    'type2' => 'birthdays',
    'parse1'=> '{
                  $d = explode(\'-\', $data[\'item_text\']);
                  $bd = mktime(0, 0, 0, $d[0], $d[1], date(\'Y\'));
                  if ($bd < time()) $bd = mktime(0, 0, 0, $d[0], $d[1], date(\'Y\')+1);
                  return strftime(\'%e %b\', $bd);
                }',
    'parse2'=> 'return $settings[\'sitename\'];',
    'query' => "SELECT
                  NOW() AS timestamp,
                  user_id,
                  user_name,
                  0 AS item_id,
                  CASE WHEN
                    user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR < CURDATE()
                  THEN
                    YEAR(CURDATE()) - YEAR(user_birthdate) + 1
                  ELSE
                    YEAR(CURDATE()) - YEAR(user_birthdate)
                  END AS item_no,
                  DATE_FORMAT(user_birthdate, '%c-%d') AS item_text,
                  0 AS item2_id,
                  CASE WHEN
                    user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR < CURDATE()
                  THEN
                    DATEDIFF(user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate) + 1) YEAR, CURDATE())
                  ELSE
                    DATEDIFF(user_birthdate + INTERVAL(YEAR(CURDATE()) - YEAR(user_birthdate)) YEAR, CURDATE())
                  END AS item2_no,
                  '' AS item2_text
                FROM ".DB_USERS."
                WHERE user_birthdate > 0
                HAVING item2_no <= 7
                ORDER BY item_no, user_name;"
  )

Now add this code at the end of the existing array in autonews_data.php.
Remember to put a comma after the existing last entry and that the code shall
be inserted before the very last parenthesis.

Upload the file and go into the autonews administration part. You should now be
able to check 'Birthdays'. Do that and click save. Make sure that someone has
birthday during the next 7 days. Click 'Test present mail' and watch that the
news in included in your list.

This is all what is needed. I hope that it provides the information needed to
add your infusions and other stuff. You may want to localize your work. Just look
at the existing entries in autonews_data.php. It's like any other localization
job. You may also want to enhance the infusion so that the interval looked for is
not 7 days but corresponds to the interval by which autonews mails are send out.

If you succeed with this for commonly used infusion, I'll be happy to receive
your code for inclusion in future updates of autonews. If you fail, you can reach
me in the autonews thread on phpfusion-mods.com or on php-fusion.dk.

Enjoy!

flj   