modules/Administration/DstFix.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM is a customer relationship management program developed by
   5  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   6  *
   7  * This program is free software; you can redistribute it and/or modify it under
   8  * the terms of the GNU Affero General Public License version 3 as published by the
   9  * Free Software Foundation with the addition of the following permission added
  10  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  11  * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  12  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  13  *
  14  * This program is distributed in the hope that it will be useful, but WITHOUT
  15  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  16  * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
  17  * details.
  18  *
  19  * You should have received a copy of the GNU Affero General Public License along with
  20  * this program; if not, see http://www.gnu.org/licenses or write to the Free
  21  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  22  * 02110-1301 USA.
  23  *
  24  * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  25  * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  26  *
  27  * The interactive user interfaces in modified source and object code versions
  28  * of this program must display Appropriate Legal Notices, as required under
  29  * Section 5 of the GNU Affero General Public License version 3.
  30  *
  31  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  32  * these Appropriate Legal Notices must retain the display of the "Powered by
  33  * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  34  * technical reasons, the Appropriate Legal Notices must display the words
  35  * "Powered by SugarCRM".
  36  ********************************************************************************/
  37
  38
  39 global $app_strings;
  40 global $app_list_strings;
  41 global $mod_strings;
  42 global $theme;
  43 global $currentModule;
  44 global $gridline;
  45 global $timedate;
  46 global $current_user;
  47 global $db;
  48
  49 if ($db->dbType == 'oci8') {
  50         echo "<BR>";
  51         echo "<p>".$mod_strings['ERR_NOT_FOR_ORACLE']."</p>";
  52         echo "<BR>";
  53         sugar_die('');
  54 }
  55 if ($db->dbType == 'mssql') {
  56     echo "<BR>";
  57     echo "<p>".$mod_strings['ERR_NOT_FOR_MSSQL']."</p>";
  58     echo "<BR>";
  59     sugar_die('');
  60 }
  61
  62
  63
  64 $display = '';
  65 if(empty($db)) {
  66
  67         $db = DBManagerFactory::getInstance();
  68 }
  69
  70 // check if this fix has been applied already
  71 $qDone = "SELECT * FROM versions WHERE name = 'DST Fix'";
  72 $rDone = $db->query($qDone);
  73 $rowsDone = $db->getRowCount($rDone);
  74 if($rowsDone > 0) {
  75         $done = true;
  76 } else {
  77         $done = false;
  78 }
  79
  80 // some inits:
  81 $disabled = 'DISABLED';
  82 $confirmed = 'false';
  83
  84 // apply the fix
  85 if(!empty($_REQUEST['confirmed']) && $_REQUEST['confirmed'] == true) {
  86         // blowaway vCal server cache
  87         $qvCal = "TRUNCATE vcals";
  88         $rvCal = $db->query($qvCal);
  89
  90         // disable refresh double-ups
  91         $rDblCheck = $db->query($qDone);
  92         $rowsDblCheck = $db->getRowCount($rDblCheck);
  93         if($rowsDblCheck < 1) {
  94
  95                 // majed's sql generation
  96                 $tables = array(
  97                         'calls'=>array(
  98                                                 'date_start'=>'time_start',
  99                                         ),
 100                         'meetings'=>array(
 101                                                 'date_start'=>'time_start',
 102                                         ),
 103                         'tasks'=>array(
 104                                                 'date_due'=>'time_due',
 105                                         ),
 106                         'project_task'=>array(
 107                                                 'date_due'=>'time_due',
 108                                         ),
 109                         'email_marketing'=>array(
 110                                                 'date_start'=>'time_start',
 111                                         ),
 112                         'emailman'=>array(
 113                                                 'send_date_time'=>'datetime',
 114                                         )
 115                 );
 116
 117                 $zone = $_REQUEST['server_timezone'];
 118                 $td = new TimeDate();
 119                 $startyear = 2004;
 120                 $maxyear = 2014;
 121                 $date_modified = gmdate($GLOBALS['timedate']->get_db_date_time_format());
 122                 $display = '';
 123
 124                 foreach($tables as $table_name =>$table) {
 125
 126                         //$display .=  '<B>'. $table_name . '</b><BR>';
 127                         $year = $startyear;
 128
 129                         for($year = $startyear; $year <= $maxyear; $year++) {
 130                                 $range = $td->getDSTRange($year,$timezones[$zone]);
 131                                 $startDateTime = explode(' ',$range['start']);
 132                                 $endDateTime = explode(' ',$range['end']);
 133
 134                                 if($range) {
 135                                         if( strtotime($range['start']) < strtotime($range['end'])) {
 136                                                 foreach($table as $date=>$time) {
 137                                                         $interval='PLUSMINUS INTERVAL 3600 second';
 138                                                         if($time != 'datetime'){
 139                                                                 if ( ( $db->dbType == 'mysql' ) or ( $db->dbType == 'oci8' ) )
 140                                                                 {
 141                                                                         $field = "CONCAT($table_name.$date,' ', $table_name.$time)";
 142                                                                 }
 143                                                                 if ( $db->dbType == 'mssql' )
 144                                                                 {
 145                                                                         $field = "$table_name.$date + ' ' + $table_name.$time";
 146                                                                 }
 147                                                                 $updateBase= "UPDATE  $table_name SET date_modified='$date_modified', $table_name.$date=LEFT($field $interval,10),";
 148                                                                 $updateBase .= " $table_name.$time=RIGHT($field $interval,8)";
 149
 150                                                         }else{
 151                                                                 $field = "$table_name.$date";
 152                                                                 $updateBase = "UPDATE $table_name SET  date_modified='$date_modified', $table_name.$date = $table_name.$date $interval";
 153                                                         }
 154                                                         //BEGIN DATE MODIFIED IN DST WITH DATE OUT DST
 155                                                         $update = str_replace('PLUSMINUS', '+', $updateBase);
 156                                                         $queryInDST = $update ."
 157                                                                                         WHERE
 158                                                                                         $table_name.date_modified >= '{$range['start']}' AND $table_name.date_modified < '{$range['end']}'
 159                                                                                         AND ( $field < '{$range['start']}'  OR $field >= '{$range['end']}' )";
 160
 161                                                         $result = $db->query($queryInDST);
 162                                                         $count = $db->getAffectedRowCount();
 163                                                         //$display .= "$year - Records updated with date modified in DST with date out of DST: $count <br>";
 164                                                         //BEGIN DATE MODIFIED OUT DST WITH DATE IN DST
 165                                                         $update = str_replace('PLUSMINUS', '-', $updateBase);
 166                                                         $queryOutDST =  $update ."
 167                                                                                         WHERE
 168                                                                                         ( $table_name.date_modified < '{$range['start']}' OR $table_name.date_modified >= '{$range['end']}' )
 169                                                                                         AND $field >= '{$range['start']}' AND $field < '{$range['end']}' ";
 170
 171                                                         $result = $db->query($queryOutDST);
 172                                                         $count = $db->getAffectedRowCount();
 173                                                         //$display .= "$year - Records updated with date modified out of DST with date in DST: $count <br>";
 174                                                 }
 175                                         }else{
 176
 177                                                 foreach($table as $date=>$time){
 178                                                         $interval='PLUSMINUS INTERVAL 3600 second';
 179                                                         if($time != 'datetime'){
 180
 181                                                                 if ( ( $this->db->dbType == 'mysql' ) or ( $this->db->dbType == 'oci8' ) )
 182                                                                 {
 183                                                                         $field = "CONCAT($table_name.$date,' ', $table_name.$time)";
 184                                                                 }
 185                                                                 if ( $this->db->dbType == 'mssql' )
 186                                                                 {
 187                                                                         $field = "$table_name.$date + ' ' + $table_name.$time";
 188                                                                 }
 189                                                                         $updateBase= "UPDATE  $table_name SET $table_name.$date=LEFT($field $interval,10),";
 190                                                                         $updateBase .= " $table_name.$time=RIGHT($field $interval,8)";
 191
 192                                                         }else{
 193                                                                 $field = "$table_name.$date";
 194                                                                 $updateBase = "UPDATE $table_name SET $table_name.$date = $table_name.$date $interval";
 195                                                         }
 196
 197
 198                                                         //BEGIN DATE MODIFIED IN DST WITH DATE OUT OF DST
 199                                                         $update = str_replace('PLUSMINUS', '+', $updateBase);
 200                                                         $queryInDST =  $update ."
 201                                                                                         WHERE
 202                                                                                         ($table_name.date_modified >= '{$range['start']}' OR $table_name.date_modified < '{$range['end']}' )
 203                                                                                         AND $field < '{$range['start']}'  AND $field >= '{$range['end']}'";
 204
 205                                                         $result = $db->query($queryInDST);
 206                                                         $count = $db->getAffectedRowCount();
 207                                                         //$display .= "$year - Records updated with date modified in DST with date out of DST: $count <br>";
 208
 209                                                         //BEGIN DATE MODIFIED OUT DST WITH DATE IN DST
 210                                                         $update = str_replace('PLUSMINUS', '-', $updateBase);
 211                                                         $queryOutDST =  $update ."
 212                                                                                         WHERE
 213                                                                                         ($table_name.date_modified < '{$range['start']}' AND $table_name.date_modified >= '{$range['end']}' )
 214                                                                                          AND
 215                                                                                          ($field >= '{$range['start']}' OR $field < '{$range['end']}' )";
 216
 217
 218
 219                                                 }
 220
 221                                                 $result = $db->query($queryOutDST);
 222                                                 $count = $db->getAffectedRowCount();
 223                                                 //$display .= "$year - Records updated with date modified out of DST with date in DST: $count <br>";
 224                                         }
 225                                 }
 226                         } // end outer forloop
 227                 }// end foreach loop
 228
 229
 230         }
 231         $display .= "<br><b>".$mod_strings['LBL_DST_FIX_DONE_DESC']."</b>";
 232 } elseif(!$done) {  // show primary screen
 233         $disabled = "";
 234         $confirmed = 'true';
 235         if(empty($timedate)) {
 236
 237                 $timedate = new TimeDate();
 238         }
 239
 240         require_once('include/timezone/timezones.php');
 241         global $timezones;
 242         $timezoneOptions = '';
 243         ksort($timezones);
 244         if(!isset($defaultServerZone)){
 245                 $defaultServerZone = lookupTimezone(0);
 246         }
 247         foreach($timezones as $key => $value) {
 248                 if(!empty($value['dstOffset'])) {
 249                         $dst = " (+DST)";
 250                 } else {
 251                         $dst = "";
 252                 }
 253                 if($key == $defaultServerZone){
 254                         $selected = 'selected';
 255                 }else{
 256                         $selected = '';
 257                 }
 258                 $gmtOffset = ($value['gmtOffset'] / 60);
 259                 if(!strstr($gmtOffset,'-')) {
 260                         $gmtOffset = "+".$gmtOffset;
 261                 }
 262                 $timezoneOptions .= "<option value='$key'".$selected.">".str_replace(array('_','North'), array(' ', 'N.'),$key). " (GMT".$gmtOffset.") ".$dst."</option>";
 263         }
 264
 265         // descriptions and assumptions
 266         $display = "
 267
 268                 <tr>
 269                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 270                                 ".$mod_strings['LBL_DST_FIX_TARGET']."
 271                         </slot></td>
 272                         <td class=\"tabDetailViewDF2\"><slot>
 273                                 ".$mod_strings['LBL_APPLY_DST_FIX_DESC']."
 274                         </slot></td>
 275                 </tr>
 276                 <tr>
 277                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 278                                 ".$mod_strings['LBL_DST_BEFORE']."
 279                         </slot></td>
 280                         <td class=\"tabDetailViewDF2\"><slot>
 281                                 ".$mod_strings['LBL_DST_BEFORE_DESC']."
 282                         </slot></td>
 283                 </tr>
 284                 <tr>
 285                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 286                                 ".$mod_strings['LBL_DST_FIX_CONFIRM']."
 287                         </slot></td>
 288                         <td class=\"tabDetailViewDF2\"><slot>
 289                                 ".$mod_strings['LBL_DST_FIX_CONFIRM_DESC']."
 290                         </slot></td>
 291                 </tr>
 292                 <tr>
 293                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 294
 295                         </slot></td>
 296                         <td class=\"tabDetailViewDF2\"><slot>
 297                                 <table cellpadding='0' cellspacing='0' border='0'>
 298                                         <tr>
 299                                                 <td class=\"tabDetailViewDF2\"><slot>
 300                                                         <b>".$mod_strings['LBL_DST_CURRENT_SERVER_TIME']."</b>
 301                                                 </td>
 302                                                 <td class=\"tabDetailViewDF2\"><slot>
 303                                                         ".$timedate->to_display_time(date($GLOBALS['timedate']->get_db_date_time_format(), strtotime('now')), true, false)."
 304                                                 </td>
 305                                         <tr>
 306                                         </tr>
 307                                                 <td class=\"tabDetailViewDF2\"><slot>
 308                                                         <b>".$mod_strings['LBL_DST_CURRENT_SERVER_TIME_ZONE']."</b>
 309                                                 </td>
 310                                                 <td class=\"tabDetailViewDF2\"><slot>
 311                                                         ".date("T")."<br>
 312                                                 </td>
 313                                         </tr>
 314                                         <tr>
 315                                                 <td class=\"tabDetailViewDF2\"><slot>
 316                                                         <b>".$mod_strings['LBL_DST_CURRENT_SERVER_TIME_ZONE_LOCALE']."</b>
 317                                                 </td>
 318                                                 <td class=\"tabDetailViewDF2\"><slot>
 319                                                         <select name='server_timezone'>".$timezoneOptions."</select><br>
 320                                                 </td>
 321                                         </tr>
 322                                 </table>
 323                         </slot></td>
 324                 </tr>";
 325 } else { // fix has been applied - don't want to allow a 2nd pass
 326         $display = $mod_strings['LBL_DST_FIX_DONE_DESC'];
 327         $disabled = 'DISABLED';
 328         $confirmed = 'false';
 329 }
 330
 331 if(!empty($_POST['upgrade'])){
 332         // enter row in versions table
 333         $qDst = "INSERT INTO versions VALUES ('".create_guid()."', 0, '".gmdate($GLOBALS['timedate']->get_db_date_time_format(), strtotime('now'))."', '".gmdate($GLOBALS['timedate']->get_db_date_time_format(), strtotime('now'))."', '".$current_user->id."', '".$current_user->id."', 'DST Fix', '3.5.1b', '3.5.1b')";
 334         $qRes = $db->query($qDst);
 335         // record server's time zone locale for future upgrades
 336         $qSTZ = "INSERT INTO config VALUES ('Update', 'server_timezone', '".$_REQUEST['server_timezone']."')";
 337         $rSTZ = $db->query($qSTZ);
 338         if(empty($_REQUEST['confirmed']) || $_REQUEST['confirmed'] == 'false') {
 339                 $display = $mod_strings['LBL_DST_FIX_DONE_DESC'];
 340                 $disabled = 'DISABLED';
 341                 $confirmed = 'false';
 342         }
 343         unset($_SESSION['GMTO']);
 344 }
 345
 346
 347
 348 echo get_module_title($mod_strings['LBL_MODULE_NAME'], $mod_strings['LBL_APPLY_DST_FIX'], true);
 349
 350 if(empty($disabled)){
 351 ?>
 352 <h2>Step 1:</h2>
 353 <table cellspacing="<?php echo $gridline;?>" class="other view">
 354 <tr>
 355     <td scope="row" width="20%">
 356         <slot><?php echo $mod_strings['LBL_DST_FIX_USER']; ?></slot>
 357     </td>
 358     <td>
 359         <slot>
 360             <?php echo $mod_strings['LBL_DST_FIX_USER_TZ']; ?><br>
 361             <input type='button' class='button' value='<?php echo $mod_strings['LBL_DST_SET_USER_TZ']; ?>' onclick='document.location.href="index.php?module=Administration&action=updateTimezonePrefs"'>
 362         </slot>
 363     </td>
 364 </tr>
 365 </table>
 366 <?php }?>
 367 <p>
 368 <form name='DstFix' action='index.php' method='POST'>
 369 <input type='hidden' name='module' value='Administration'>
 370 <input type='hidden' name='action' value='DstFix'>
 371 <?php
 372 if(empty($disabled)){
 373         echo "<h2>Step 2:</h2>";
 374 }
 375 ?>
 376 <table cellspacing="<?php echo $gridline;?>" class="other view">
 377 <?php
 378 echo $display;
 379  if(empty($disabled)){
 380     ?>
 381 <tr>
 382     <td scope="row" width="20%">
 383         <slot><?php echo $mod_strings['LBL_DST_UPGRADE']; ?></slot>
 384     </td>
 385     <td>
 386         <slot>
 387             <input type='checkbox' name='confirmed' value='true' checked="checked" />
 388             <?php echo $mod_strings['LBL_DST_APPLY_FIX']; ?>
 389         </slot>
 390     </td>
 391 </tr>
 392 <?php } ?>
 393 <tr>
 394     <td scope="row" width="20%"></td>
 395     <td>
 396         <slot>
 397 <?php
 398 if(empty($disabled)){
 399 echo "<input ".$disabled." title='".$mod_strings['LBL_APPLY_DST_FIX']."' accessKey='".$app_strings['LBL_SAVE_BUTTON_KEY']."' class=\"button\" onclick=\"this.form.action.value='DstFix';\" type=\"submit\" name=\"upgrade\" value='".$mod_strings['LBL_APPLY_DST_FIX']."' >";
 400 }else{
 401 echo "<input title='".$app_strings['LBL_DONE_BUTTON_TITLE']."' accessKey='".$app_strings['LBL_DONE_BUTTON_KEY']."' class=\"button\" onclick=\"this.form.action.value='Upgrade'; this.form.module.value='Administration';\" type=\"submit\" name=\"done\" value='".$app_strings['LBL_DONE_BUTTON_LABEL']."'>";
 402 }
 403 ?>
 404         </slot>
 405     </td>
 406 </tr>
 407 </table>
 408 </form>