Changeset 4398


Ignore:
Timestamp:
11/30/09 21:41:11 (10 years ago)
Author:
nikrou
Message:

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

Location:
trunk/include
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/include/calendar_base.class.php

    r4367 r4398  
    216216$this->inner_sql. 
    217217$this->get_date_where($level).' 
    218   GROUP BY period 
     218  GROUP BY period  
     219  ORDER BY period ASC 
    219220;'; 
    220221 
     
    266267  { 
    267268    global $template, $page; 
     269 
    268270    $prev = $next =null; 
    269271    if ( empty($page['chronology_date']) ) 
    270272      return; 
    271     $query = 'SELECT CONCAT_WS(\'-\''; 
    272     for ($i=0; $i<count($page['chronology_date']); $i++) 
     273     
     274    $sub_query = ''; 
     275    $nb_elements = count($page['chronology_date']); 
     276    for ($i=0; $i<$nb_elements; $i++) 
    273277    { 
    274278      if ( 'any' === $page['chronology_date'][$i] ) 
    275279      { 
    276         $query .= ','.'"any"'; 
     280        $sub_query .= '\'any\''; 
    277281      } 
    278282      else 
    279283      { 
    280         $query .= ','.$this->calendar_levels[$i]['sql']; 
    281       } 
    282     } 
    283     $current = implode('-', $page['chronology_date'] ); 
    284  
    285     $query.=') as period' . $this->inner_sql .' 
     284        $sub_query .= pwg_db_cast_to_text($this->calendar_levels[$i]['sql']); 
     285      } 
     286      if ($i<($nb_elements-1)) 
     287      { 
     288        $sub_query .= ','; 
     289      } 
     290    } 
     291    $query = 'SELECT '.pwg_db_concat_ws($sub_query, '-').' AS period'; 
     292    $query .= $this->inner_sql .' 
    286293AND ' . $this->date_field . ' IS NOT NULL 
    287294GROUP BY period'; 
    288  
     295     
     296    $current = implode('-', $page['chronology_date'] ); 
    289297    $upper_items = array_from_query( $query, 'period'); 
    290298 
  • trunk/include/calendar_monthly.class.php

    r4367 r4398  
    4444    $this->calendar_levels = array( 
    4545      array( 
    46           'sql'=> 'YEAR('.$this->date_field.')', 
     46          'sql'=> pwg_db_get_year($this->date_field), 
    4747          'labels' => null 
    4848        ), 
    4949      array( 
    50           'sql'=> 'MONTH('.$this->date_field.')', 
    51           'labels' => $lang['month'] 
     50          'sql'=> pwg_db_get_month($this->date_field), 
     51          'labels' => $lang['month'] 
    5252        ), 
    5353      array( 
    54           'sql'=> 'DAYOFMONTH('.$this->date_field.')', 
     54          'sql'=> pwg_db_get_dayofmonth($this->date_field), 
    5555          'labels' => null 
    5656        ), 
     
    135135{ 
    136136  global $page; 
     137 
    137138  $date = $page['chronology_date']; 
    138139  while (count($date)>$max_levels) 
     
    157158      { 
    158159        $b .= '01'; 
    159         $e .= '31'; 
     160        $e .= $this->get_all_days_in_month($date[CYEAR], $date[CMONTH]); 
    160161      } 
    161162    } 
     
    194195//--------------------------------------------------------- private members --- 
    195196 
    196 // returns an array with alll the days in a given month 
     197// returns an array with all the days in a given month 
    197198function get_all_days_in_month($year, $month) 
    198199{ 
     
    221222{ 
    222223  global $page; 
     224 
    223225  assert( count($page['chronology_date']) == 0 ); 
    224   $query='SELECT DISTINCT(DATE_FORMAT('.$this->date_field.',"%Y%m")) as period, 
    225             COUNT( DISTINCT(id) ) as count'; 
     226  $query=' 
     227SELECT '.pwg_db_get_date_YYYYMM($this->date_field).' as period,' 
     228    .pwg_db_get_year($this->date_field).' as year, ' 
     229    .pwg_db_get_month($this->date_field).' as month,  
     230            count(distinct id) as count'; 
    226231  $query.= $this->inner_sql; 
    227232  $query.= $this->get_date_where(); 
    228233  $query.= ' 
    229   GROUP BY period 
    230   ORDER BY YEAR('.$this->date_field.') DESC, MONTH('.$this->date_field.')'; 
     234  GROUP BY period, year, month 
     235  ORDER BY year DESC, month ASC'; 
    231236 
    232237  $result = pwg_query($query); 
     
    274279{ 
    275280  global $page; 
     281 
    276282  assert( count($page['chronology_date']) == 1 ); 
    277   $query='SELECT DISTINCT(DATE_FORMAT('.$this->date_field.',"%m%d")) as period, 
    278             COUNT( DISTINCT(id) ) as count'; 
     283  $query='SELECT '.pwg_db_get_date_MMDD($this->date_field).' as period, 
     284            COUNT(DISTINCT id) as count'; 
    279285  $query.= $this->inner_sql; 
    280286  $query.= $this->get_date_where(); 
    281287  $query.= ' 
    282   GROUP BY period'; 
     288  GROUP BY period 
     289  ORDER BY period ASC'; 
    283290 
    284291  $result = pwg_query($query); 
     
    325332{ 
    326333  global $page; 
    327   $query='SELECT DISTINCT(DAYOFMONTH('.$this->date_field.')) as period, 
    328             COUNT( DISTINCT(id) ) as count'; 
     334 
     335  $query='SELECT '.pwg_db_get_dayofmonth($this->date_field).' as period, 
     336            COUNT(DISTINCT id) as count'; 
    329337  $query.= $this->inner_sql; 
    330338  $query.= $this->get_date_where(); 
    331339  $query.= ' 
    332   GROUP BY period'; 
     340  GROUP BY period 
     341  ORDER BY period ASC'; 
    333342 
    334343  $items=array(); 
     
    344353    $page['chronology_date'][CDAY]=$day; 
    345354    $query = ' 
    346 SELECT id, file,tn_ext,path, width, height, DAYOFWEEK('.$this->date_field.')-1 as dow'; 
     355SELECT id, file,tn_ext,path, width, height, '.pwg_db_get_dayofweek($this->date_field).'-1 as dow'; 
    347356    $query.= $this->inner_sql; 
    348357    $query.= $this->get_date_where(); 
  • trunk/include/calendar_weekly.class.php

    r3282 r4398  
    5151    $this->calendar_levels = array( 
    5252      array( 
    53           'sql'=> 'YEAR('.$this->date_field.')', 
     53          'sql'=> pwg_db_get_year($this->date_field), 
    5454          'labels' => null 
    5555        ), 
    5656      array( 
    57           'sql'=> 'WEEK('.$this->date_field.')+1', 
     57          'sql'=> pwg_db_get_week($this->date_field)+1, 
    5858          'labels' => $week_no_labels, 
    5959        ), 
    6060      array( 
    61           'sql'=> 'DAYOFWEEK('.$this->date_field.')-1', 
     61          'sql'=> pwg_db_get_dayofweek($this->date_field)-1, 
    6262          'labels' => $lang['day'] 
    6363        ), 
     
    6565    //Comment next lines for week starting on Sunday or if MySQL version<4.0.17 
    6666    //WEEK(date,5) = "0-53 - Week 1=the first week with a Monday in this year" 
    67     $this->calendar_levels[CWEEK]['sql'] = 'WEEK('.$this->date_field.',5)+1'; 
    68     $this->calendar_levels[CDAY]['sql'] = 'WEEKDAY('.$this->date_field.')'; 
     67    $this->calendar_levels[CWEEK]['sql'] = pwg_db_get_week($this->date_field, 5).'+1'; 
     68    $this->calendar_levels[CDAY]['sql'] = pwg_db_get_weekday($this->date_field); 
    6969    array_push( $this->calendar_levels[CDAY]['labels'], 
    7070                array_shift( $this->calendar_levels[CDAY]['labels'] ) ); 
  • trunk/include/functions_calendar.inc.php

    r4385 r4398  
    259259  if ($must_show_list) 
    260260  { 
    261     $query = 'SELECT id'; 
     261    $query = 'SELECT DISTINCT id '.get_extra_fields($conf['order_by']); 
     262    $query .= ','.$calendar->date_field; 
    262263    $query .= $calendar->inner_sql.' 
    263264  '.$calendar->get_date_where(); 
Note: See TracChangeset for help on using the changeset viewer.