Changeset 4398

Timestamp:

Nov 30, 2009, 9:41:11 PM (14 years ago)

Author:

nikrou

Message:

Feature 1255 :
sql functions for calendar (interval, year, month, ...)

Location:

trunk/include

Files:

• calendar_base.class.php (modified) (2 diffs)
• calendar_monthly.class.php (modified) (8 diffs)
• calendar_weekly.class.php (modified) (2 diffs)
• functions_calendar.inc.php (modified) (1 diff)

trunk/include/calendar_base.class.php

@@ -216 +216 @@
 $this->inner_sql.
 $this->get_date_where($level).'
-  GROUP BY period
+  GROUP BY period 
+  ORDER BY period ASC
 ;';
 
@@ -266 +267 @@
   {
     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'];
-      }
-    }
-    $current = implode('-', $page['chronology_date'] );
-
-    $query.=') as period' . $this->inner_sql .'
+        $sub_query .= pwg_db_cast_to_text($this->calendar_levels[$i]['sql']);
+      }
+      if ($i<($nb_elements-1))
+      {
+        $sub_query .= ',';
+      }
+    }
+    $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');
 

trunk/include/calendar_monthly.class.php

@@ -44 +44 @@
     $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
         ),
@@ -135 +135 @@
 {
   global $page;
+
   $date = $page['chronology_date'];
   while (count($date)>$max_levels)
@@ -157 +158 @@
       {
         $b .= '01';
-        $e .= '31';
+        $e .= $this->get_all_days_in_month($date[CYEAR], $date[CMONTH]);
       }
     }
@@ -194 +195 @@
 //--------------------------------------------------------- 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)
 {
@@ -221 +222 @@
 {
   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);
@@ -274 +279 @@
 {
   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);
@@ -325 +332 @@
 {
   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();
@@ -344 +353 @@
     $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();

trunk/include/calendar_weekly.class.php

@@ -51 +51 @@
     $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']
         ),
@@ -65 +65 @@
     //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'] ) );

trunk/include/functions_calendar.inc.php

@@ -259 +259 @@
   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();