From f7222bf601fa120a0fa2190cb98325780d75ff24 Mon Sep 17 00:00:00 2001 From: nikrou Date: Mon, 30 Nov 2009 20:41:11 +0000 Subject: Feature 1255 : sql functions for calendar (interval, year, month, ...) git-svn-id: http://piwigo.org/svn/trunk@4398 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/calendar_base.class.php | 26 +++++++++++++++-------- include/calendar_monthly.class.php | 43 +++++++++++++++++++++++--------------- include/calendar_weekly.class.php | 10 ++++----- include/functions_calendar.inc.php | 3 ++- 4 files changed, 50 insertions(+), 32 deletions(-) (limited to 'include') diff --git a/include/calendar_base.class.php b/include/calendar_base.class.php index 079da2621..41a15d918 100644 --- a/include/calendar_base.class.php +++ b/include/calendar_base.class.php @@ -215,7 +215,8 @@ SELECT DISTINCT('.$this->calendar_levels[$level]['sql'].') as period, 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'); @@ -265,27 +266,34 @@ $this->get_date_where($level).' function build_next_prev() { global $template, $page; + $prev = $next =null; if ( empty($page['chronology_date']) ) return; - $query = 'SELECT CONCAT_WS(\'-\''; - for ($i=0; $icalendar_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'); diff --git a/include/calendar_monthly.class.php b/include/calendar_monthly.class.php index f24ef9f26..eb28735e7 100644 --- a/include/calendar_monthly.class.php +++ b/include/calendar_monthly.class.php @@ -43,15 +43,15 @@ class Calendar extends CalendarBase 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 ), ); @@ -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) { @@ -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 @@ -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); @@ -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(); @@ -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(); @@ -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); @@ -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.= ' diff --git a/include/calendar_weekly.class.php b/include/calendar_weekly.class.php index 14f6ccd8d..a8a167679 100644 --- a/include/calendar_weekly.class.php +++ b/include/calendar_weekly.class.php @@ -50,22 +50,22 @@ class Calendar extends CalendarBase $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'] ) ); } diff --git a/include/functions_calendar.inc.php b/include/functions_calendar.inc.php index 362c4cf14..9191bb157 100644 --- a/include/functions_calendar.inc.php +++ b/include/functions_calendar.inc.php @@ -258,7 +258,8 @@ WHERE id IN (' . implode(',',$page['items']) .')'; 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']) ) -- cgit v1.2.3