Small Calendar on Portal

[pizza_guy]

Your Portal Version: v1.0.2
Your phpBB Type: Standard phpBB3
MODs installed: Yes
Your knowledge: Basic Knowledge

PHP Version: 3.0.2

What have you done before the problem was there?
I just did an install of the portal

What have you already tryed to solve the problem?
I tried to follow viewtopic.php?f=21&t=459

Description and Message
I do have the calendar mod from phpbb.net installed. That works just fine, however the mini calendar on my portal when i click on it takes me to new unanswered post.

[Kevin]

Moved to "Block Support".

[pizza_guy]

ah sorry, thanks for the move

[odklizec]

I have the same problem. I think this calendar behavior is "by design". However, I would rather like to see all posts from the selected day instead of all unanswered posts. In addition, the date selection seems does not have any effect on the returned list of unanswered posts?

I tried to find a solution myself, but as I'm not a skilled PHPBB coder/moder, so I did not find anything useful. There seems to be only these few search_id's:
unanswered
active_topics
newposts
egosearch

I did several tests with these search ID's but none of them seems work with the date selected in calendar. Any suggestions?

I think a search for posts from the selected day would be very useful calendar feature. The usefulness of actual date unlimited search for unanswered posts seems to be very limited.

[odklizec]

OK, I got a hint from a kind person at PHPBB forum. The SQL calls for post/topic search based of certain date should be as follows...

SELECT `post_id` FROM phpbb_posts WHERE `post_time` > x AND `post_time` < x + 86400;
SELECT `topic_id` FROM phpbb_topics WHERE `topic_time` > x AND `topic_time` < x + 86400;

where x = unix timestamp for 2401 hours on the date in question.

Now the "only" thing I need is an integration with calendar block. I will look at this but as my knowledge of PHPBB modding and PHP/SQL is very limited, I may or may not succeed

[odklizec]

OK folks, It seems I did it! It's not very universal solution because I need it just for Board3 Portal mini calendar block. But it (almost) works and that's important for now! There is a minor issue I'm not quite sure how to fix it or what's the problem. The search returns not only the posts from selected date, but also some posts before/after the selected day

Let's say, I select 13.Dec.2008 (it returns 1229130000). The date range for search is set as follow...
1229040000 - 12.12.2008 00:00:00
1229212800 - 14.12.2008 00:00:00

However, the search returns not only posts from 13.12.2008 (as expected) but also some posts started on 12.12. and 14.12. See this example...
http://ricohforum.com/phpbb/search.php? ... 1229130000

Here is what I did...
In search.php look for this row:

Code: Select all

$search_id		= request_var('search_id', '');

and insert this after it:

Code: Select all

$search_date	= request_var('date', 0);

then search this:

Code: Select all

			case 'newposts':
				$l_search_title = $user->lang['SEARCH_NEW'];
				// force sorting
				$show_results = (request_var('sr', 'topics') == 'posts') ? 'posts' : 'topics';
				$sort_key = 't';
				$sort_dir = 'd';
				$sort_by_sql['t'] = ($show_results == 'posts') ? 'p.post_time' : 't.topic_last_post_time';
				$sql_sort = 'ORDER BY ' . $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');

				gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sort_dir, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);
				$s_sort_key = $s_sort_dir = $u_sort_param = $s_limit_days = '';

				if ($show_results == 'posts')
				{
					$sql = 'SELECT p.post_id
						FROM ' . POSTS_TABLE . ' p
						WHERE p.post_time > ' . $user->data['user_lastvisit'] . "
							$m_approve_fid_sql
							" . ((sizeof($ex_fid_ary)) ? ' AND ' . $db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '') . "
						$sql_sort";
					$field = 'post_id';
				}
				else
				{
					$sql = 'SELECT t.topic_id
						FROM ' . TOPICS_TABLE . ' t
						WHERE t.topic_last_post_time > ' . $user->data['user_lastvisit'] . '
							AND t.topic_moved_id = 0
							' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . '
							' . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '') . "
						$sql_sort";
					$field = 'topic_id';
				}
			break;

and insert this after it:

Code: Select all

			case 'datesearch':
				$l_search_title = $user->lang['SEARCH_DATE'];
				// force sorting
				$show_results = (request_var('sr', 'topics') == 'posts') ? 'posts' : 'topics';
				$sort_key = 't';
				$sort_dir = 'd';
				$sort_by_sql['t'] = ($show_results == 'posts') ? 'p.post_time' : 't.topic_last_post_time';
				$sql_sort = 'ORDER BY ' . $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');

				gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sort_dir, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);
				$s_sort_key = $s_sort_dir = $u_sort_param = $s_limit_days = '';

// set day before (23:59:59) the selected day 
// if your board time zone is different than UTC 0, you need to add/subtract the amount of secs based of your time zone settings
// for example, my board is UTC+1 then I need to subtract 3600 secs to get the time in UTC 0.
// if your board is UTC-2, you should add 3600*2
        $daybefore = ($search_date - (3600-1));
// set day after the selected day
        $dayafter = ($search_date + (86400-3600));
        
				if ($show_results == 'posts')
				{
					$sql = 'SELECT p.post_id
						FROM ' . POSTS_TABLE . ' p
						WHERE p.post_time > ' . $daybefore . '
						  AND p.post_time < ' . $dayafter . "
							$m_approve_fid_sql
							" . ((sizeof($ex_fid_ary)) ? ' AND ' . $db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '') . "
						$sql_sort";
					$field = 'post_id';
				}
				else
				{
					$sql = 'SELECT t.topic_id
						FROM ' . TOPICS_TABLE . ' t
						WHERE t.topic_time > ' . $daybefore . '
							AND t.topic_time < ' . $dayafter . '
							AND t.topic_moved_id = 0
							' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . '
							' . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '') . "
						$sql_sort";
					$field = 'topic_id';
				}
			break;

Now in functions.php search for:

Code: Select all

'U_SEARCH_NEW'			=> append_sid("{$phpbb_root_path}search.$phpEx", 'search_id=newposts'),

and add this after it:

Code: Select all

'U_SEARCH_DATE'			=> append_sid("{$phpbb_root_path}search.$phpEx", 'search_id=datesearch'),

and finally, in Board3 Portal mini_calc.php search for this:

Code: Select all

$mini_cal_day_link = '<a href="' . append_sid($phpbb_root_path . "search.$phpEx?search_id=unanswered&st=" . $nix_mini_cal_today) . '" class="' . MINI_CAL_DAY_LINK_CLASS . '" style="color: ' . $portal_config['portal_minicalendar_day_link_color'] . ';">' . ( $mini_cal_day ) . '</a>';

and replace it with this line:

Code: Select all

$mini_cal_day_link = '<a href="' . append_sid($phpbb_root_path . "search.$phpEx?search_id=datesearch&date=" . $nix_mini_cal_today) . '" class="' . MINI_CAL_DAY_LINK_CLASS . '" style="color: ' . $portal_config['portal_minicalendar_day_link_color'] . ';">' . ( $mini_cal_day ) . '</a>';

Any idea what to do to force search to return only the posts from selected date? Thank you in advance!

EDIT: corrected script to get the proper UNIX date/time range.

[Kevin]

Thanks for sharning, odklizec!
Interesting to see. And as guessed before it is quite a hassle to get a search function by date running.
Have you asked at phpbb.com if they maybe want to get this function in? Then we could use it for the mini-calendar.

[odklizec]

You are welcome Kevin! I asked for help with this task also at phpbb forum and someone called "stevemaury" kindly helped me with required SQL calls. All I need now is to find what's the cause of redundant posts before/after the selected date. Then I may ask them to add this new "date" search as a standard phpbb feature I'm sure, it would be quite useful to be able to search for a posts/topics from a given date.

[Kevin]

All I need now is to find what's the cause of redundant posts before/after the selected date.

What i think:
The timezone have to be taken into account. Check the function with your timezone set to GMT in your UCP. If in this constellation everything is fine: it is it.
I quite sure that the timezone is the problem.

[odklizec]

Hi Kevin and thanks for the tip! Yes, I thought about that but that's not the cause of my problem. My board time zone setting is UTC+1 so I already updated the script in that respect and it does not help. However, while writing this post, I found the problem in my logic The SQL call is of course correct. The problem lies in my "86400" day shift for previous day. It should not be the whole day (86400) but just only one sec! The date range for search must be like this...
$daybefore = Wed, 31 Dec 2008 23:59:59 GMT
$dayafter = Fri, 02 Jan 2009 00:00:00 GMT

and not like this (previous state)...
$daybefore = Wed, 31 Dec 2008 00:00:00 GMT
$dayafter = Fri, 02 Jan 2009 00:00:00 GMT

So the correct script is...

Code: Select all

// set day before (23:59:59) the selected day 
        $daybefore = ($search_date - (3600-1));
// set day after the selected day
        $dayafter = ($search_date + (86400-3600));

Now it works as expected! Stupid me... Thanks for help Kevin!

[Kevin]

Fine!
No problem, you did it all by yourself.

[sureshot007]

I don't like the default behavior of this. I have a calendar installed and I would like to be able to click on the day and have it bring up the day on the calendar. I was able to make it work for days in the future, but past days on the mini calendar still point to unaswered posts, or the code messes up my link to the calendar. Where do I find the code that makes it go to unanswered posts?