Skip to content

Commit

Permalink
Feature 1255 :
Browse files Browse the repository at this point in the history 
sql functions for calendar (interval, year, month, ...)

git-svn-id: http://piwigo.org/svn/trunk@4398 68402e56-0260-453c-a942-63ccdbb3a9ee
  • Loading branch information
nikrou committed Nov 30, 2009
1 parent 2f8800b commit f7222bf
Show file tree
Hide file tree
Showing 4 changed files with 50 additions and 32 deletions.
26 changes: 17 additions & 9 deletions include/calendar_base.class.php
View file
Expand Up @@ -215,7 +215,8 @@ function build_nav_bar($level, $labels=null)
COUNT(DISTINCT id) as nb_images'.
$this->inner_sql.
$this->get_date_where($level).'
GROUP BY period
GROUP BY period
ORDER BY period ASC
;';

$level_items = simple_hash_from_query($query, 'period', 'nb_images');
Expand Down Expand Up @@ -265,27 +266,34 @@ function build_nav_bar($level, $labels=null)
function build_next_prev()
{
global $template, $page;

$prev = $next =null;
if ( empty($page['chronology_date']) )
return;
$query = 'SELECT CONCAT_WS(\'-\'';
for ($i=0; $i<count($page['chronology_date']); $i++)

$sub_query = '';
$nb_elements = count($page['chronology_date']);
for ($i=0; $i<$nb_elements; $i++)
{
if ( 'any' === $page['chronology_date'][$i] )
{
$query .= ','.'"any"';
$sub_query .= '\'any\'';
}
else
{
$query .= ','.$this->calendar_levels[$i]['sql'];
$sub_query .= pwg_db_cast_to_text($this->calendar_levels[$i]['sql']);
}
if ($i<($nb_elements-1))
{
$sub_query .= ',';
}
}
$current = implode('-', $page['chronology_date'] );

$query.=') as period' . $this->inner_sql .'
$query = 'SELECT '.pwg_db_concat_ws($sub_query, '-').' AS period';
$query .= $this->inner_sql .'
AND ' . $this->date_field . ' IS NOT NULL
GROUP BY period';


$current = implode('-', $page['chronology_date'] );
$upper_items = array_from_query( $query, 'period');

usort($upper_items, 'version_compare');
Expand Down
43 changes: 26 additions & 17 deletions include/calendar_monthly.class.php
View file
Expand Up @@ -43,15 +43,15 @@ function initialize($inner_sql)
global $lang;
$this->calendar_levels = array(
array(
'sql'=> 'YEAR('.$this->date_field.')',
'sql'=> pwg_db_get_year($this->date_field),
'labels' => null
),
array(
'sql'=> 'MONTH('.$this->date_field.')',
'labels' => $lang['month']
'sql'=> pwg_db_get_month($this->date_field),
'labels' => $lang['month']
),
array(
'sql'=> 'DAYOFMONTH('.$this->date_field.')',
'sql'=> pwg_db_get_dayofmonth($this->date_field),
'labels' => null
),
);
Expand Down Expand Up @@ -134,6 +134,7 @@ function generate_category_content()
function get_date_where($max_levels=3)
{
global $page;

$date = $page['chronology_date'];
while (count($date)>$max_levels)
{
Expand All @@ -156,7 +157,7 @@ function get_date_where($max_levels=3)
else
{
$b .= '01';
$e .= '31';
$e .= $this->get_all_days_in_month($date[CYEAR], $date[CMONTH]);
}
}
else
Expand Down Expand Up @@ -193,7 +194,7 @@ function get_date_where($max_levels=3)

//--------------------------------------------------------- private members ---

// returns an array with alll the days in a given month
// returns an array with all the days in a given month
function get_all_days_in_month($year, $month)
{
$md= array(1=>31,28,31,30,31,30,31,31,30,31,30,31);
Expand All @@ -220,14 +221,18 @@ function get_all_days_in_month($year, $month)
function build_global_calendar(&$tpl_var)
{
global $page;

assert( count($page['chronology_date']) == 0 );
$query='SELECT DISTINCT(DATE_FORMAT('.$this->date_field.',"%Y%m")) as period,
COUNT( DISTINCT(id) ) as count';
$query='
SELECT '.pwg_db_get_date_YYYYMM($this->date_field).' as period,'
.pwg_db_get_year($this->date_field).' as year, '
.pwg_db_get_month($this->date_field).' as month,
count(distinct id) as count';
$query.= $this->inner_sql;
$query.= $this->get_date_where();
$query.= '
GROUP BY period
ORDER BY YEAR('.$this->date_field.') DESC, MONTH('.$this->date_field.')';
GROUP BY period, year, month
ORDER BY year DESC, month ASC';

$result = pwg_query($query);
$items=array();
Expand Down Expand Up @@ -273,13 +278,15 @@ function build_global_calendar(&$tpl_var)
function build_year_calendar(&$tpl_var)
{
global $page;

assert( count($page['chronology_date']) == 1 );
$query='SELECT DISTINCT(DATE_FORMAT('.$this->date_field.',"%m%d")) as period,
COUNT( DISTINCT(id) ) as count';
$query='SELECT '.pwg_db_get_date_MMDD($this->date_field).' as period,
COUNT(DISTINCT id) as count';
$query.= $this->inner_sql;
$query.= $this->get_date_where();
$query.= '
GROUP BY period';
GROUP BY period
ORDER BY period ASC';

$result = pwg_query($query);
$items=array();
Expand Down Expand Up @@ -324,12 +331,14 @@ function build_year_calendar(&$tpl_var)
function build_month_calendar(&$tpl_var)
{
global $page;
$query='SELECT DISTINCT(DAYOFMONTH('.$this->date_field.')) as period,
COUNT( DISTINCT(id) ) as count';

$query='SELECT '.pwg_db_get_dayofmonth($this->date_field).' as period,
COUNT(DISTINCT id) as count';
$query.= $this->inner_sql;
$query.= $this->get_date_where();
$query.= '
GROUP BY period';
GROUP BY period
ORDER BY period ASC';

$items=array();
$result = pwg_query($query);
Expand All @@ -343,7 +352,7 @@ function build_month_calendar(&$tpl_var)
{
$page['chronology_date'][CDAY]=$day;
$query = '
SELECT id, file,tn_ext,path, width, height, DAYOFWEEK('.$this->date_field.')-1 as dow';
SELECT id, file,tn_ext,path, width, height, '.pwg_db_get_dayofweek($this->date_field).'-1 as dow';
$query.= $this->inner_sql;
$query.= $this->get_date_where();
$query.= '
Expand Down
10 changes: 5 additions & 5 deletions include/calendar_weekly.class.php
View file
Expand Up @@ -50,22 +50,22 @@ function initialize($inner_sql)

$this->calendar_levels = array(
array(
'sql'=> 'YEAR('.$this->date_field.')',
'sql'=> pwg_db_get_year($this->date_field),
'labels' => null
),
array(
'sql'=> 'WEEK('.$this->date_field.')+1',
'sql'=> pwg_db_get_week($this->date_field)+1,
'labels' => $week_no_labels,
),
array(
'sql'=> 'DAYOFWEEK('.$this->date_field.')-1',
'sql'=> pwg_db_get_dayofweek($this->date_field)-1,
'labels' => $lang['day']
),
);
//Comment next lines for week starting on Sunday or if MySQL version<4.0.17
//WEEK(date,5) = "0-53 - Week 1=the first week with a Monday in this year"
$this->calendar_levels[CWEEK]['sql'] = 'WEEK('.$this->date_field.',5)+1';
$this->calendar_levels[CDAY]['sql'] = 'WEEKDAY('.$this->date_field.')';
$this->calendar_levels[CWEEK]['sql'] = pwg_db_get_week($this->date_field, 5).'+1';
$this->calendar_levels[CDAY]['sql'] = pwg_db_get_weekday($this->date_field);
array_push( $this->calendar_levels[CDAY]['labels'],
array_shift( $this->calendar_levels[CDAY]['labels'] ) );
}
Expand Down
3 changes: 2 additions & 1 deletion include/functions_calendar.inc.php
View file
Expand Up @@ -258,7 +258,8 @@ function initialize_calendar()

if ($must_show_list)
{
$query = 'SELECT id';
$query = 'SELECT DISTINCT id '.get_extra_fields($conf['order_by']);
$query .= ','.$calendar->date_field;
$query .= $calendar->inner_sql.'
'.$calendar->get_date_where();
if ( isset($page['super_order_by']) )
Expand Down

0 comments on commit f7222bf

Please sign in to comment.