modules/Administration/DstFix.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM Community Edition 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                 $startyear = 2004;
 119                 $maxyear = 2014;
 120                 $date_modified = $timedate->nowDb();
 121                 $display = '';
 122
 123                 foreach($tables as $table_name =>$table) {
 124
 125                         //$display .=  '<B>'. $table_name . '</b><BR>';
 126                         $year = $startyear;
 127
 128                         for($year = $startyear; $year <= $maxyear; $year++) {
 129                                 $range = $timedate->getDSTRange($year,$timezones[$zone]);
 130                                 $startDateTime = explode(' ',$range['start']);
 131                                 $endDateTime = explode(' ',$range['end']);
 132
 133                                 if($range) {
 134                                         if( strtotime($range['start']) < strtotime($range['end'])) {
 135                                                 foreach($table as $date=>$time) {
 136                                                         $interval='PLUSMINUS INTERVAL 3600 second';
 137                                                         if($time != 'datetime'){
 138                                                                 if ( ( $db->dbType == 'mysql' ) or ( $db->dbType == 'oci8' ) )
 139                                                                 {
 140                                                                         $field = "CONCAT($table_name.$date,' ', $table_name.$time)";
 141                                                                 }
 142                                                                 if ( $db->dbType == 'mssql' )
 143                                                                 {
 144                                                                         $field = "$table_name.$date + ' ' + $table_name.$time";
 145                                                                 }
 146                                                                 $updateBase= "UPDATE  $table_name SET date_modified='$date_modified', $table_name.$date=LEFT($field $interval,10),";
 147                                                                 $updateBase .= " $table_name.$time=RIGHT($field $interval,8)";
 148
 149                                                         }else{
 150                                                                 $field = "$table_name.$date";
 151                                                                 $updateBase = "UPDATE $table_name SET  date_modified='$date_modified', $table_name.$date = $table_name.$date $interval";
 152                                                         }
 153                                                         //BEGIN DATE MODIFIED IN DST WITH DATE OUT DST
 154                                                         $update = str_replace('PLUSMINUS', '+', $updateBase);
 155                                                         $queryInDST = $update ."
 156                                                                                         WHERE
 157                                                                                         $table_name.date_modified >= '{$range['start']}' AND $table_name.date_modified < '{$range['end']}'
 158                                                                                         AND ( $field < '{$range['start']}'  OR $field >= '{$range['end']}' )";
 159
 160                                                         $result = $db->query($queryInDST);
 161                                                         $count = $db->getAffectedRowCount();
 162                                                         //$display .= "$year - Records updated with date modified in DST with date out of DST: $count <br>";
 163                                                         //BEGIN DATE MODIFIED OUT DST WITH DATE IN DST
 164                                                         $update = str_replace('PLUSMINUS', '-', $updateBase);
 165                                                         $queryOutDST =  $update ."
 166                                                                                         WHERE
 167                                                                                         ( $table_name.date_modified < '{$range['start']}' OR $table_name.date_modified >= '{$range['end']}' )
 168                                                                                         AND $field >= '{$range['start']}' AND $field < '{$range['end']}' ";
 169
 170                                                         $result = $db->query($queryOutDST);
 171                                                         $count = $db->getAffectedRowCount();
 172                                                         //$display .= "$year - Records updated with date modified out of DST with date in DST: $count <br>";
 173                                                 }
 174                                         }else{
 175
 176                                                 foreach($table as $date=>$time){
 177                                                         $interval='PLUSMINUS INTERVAL 3600 second';
 178                                                         if($time != 'datetime'){
 179
 180                                                                 if ( ( $this->db->dbType == 'mysql' ) or ( $this->db->dbType == 'oci8' ) )
 181                                                                 {
 182                                                                         $field = "CONCAT($table_name.$date,' ', $table_name.$time)";
 183                                                                 }
 184                                                                 if ( $this->db->dbType == 'mssql' )
 185                                                                 {
 186                                                                         $field = "$table_name.$date + ' ' + $table_name.$time";
 187                                                                 }
 188                                                                         $updateBase= "UPDATE  $table_name SET $table_name.$date=LEFT($field $interval,10),";
 189                                                                         $updateBase .= " $table_name.$time=RIGHT($field $interval,8)";
 190
 191                                                         }else{
 192                                                                 $field = "$table_name.$date";
 193                                                                 $updateBase = "UPDATE $table_name SET $table_name.$date = $table_name.$date $interval";
 194                                                         }
 195
 196
 197                                                         //BEGIN DATE MODIFIED IN DST WITH DATE OUT OF DST
 198                                                         $update = str_replace('PLUSMINUS', '+', $updateBase);
 199                                                         $queryInDST =  $update ."
 200                                                                                         WHERE
 201                                                                                         ($table_name.date_modified >= '{$range['start']}' OR $table_name.date_modified < '{$range['end']}' )
 202                                                                                         AND $field < '{$range['start']}'  AND $field >= '{$range['end']}'";
 203
 204                                                         $result = $db->query($queryInDST);
 205                                                         $count = $db->getAffectedRowCount();
 206                                                         //$display .= "$year - Records updated with date modified in DST with date out of DST: $count <br>";
 207
 208                                                         //BEGIN DATE MODIFIED OUT DST WITH DATE IN DST
 209                                                         $update = str_replace('PLUSMINUS', '-', $updateBase);
 210                                                         $queryOutDST =  $update ."
 211                                                                                         WHERE
 212                                                                                         ($table_name.date_modified < '{$range['start']}' AND $table_name.date_modified >= '{$range['end']}' )
 213                                                                                          AND
 214                                                                                          ($field >= '{$range['start']}' OR $field < '{$range['end']}' )";
 215
 216
 217
 218                                                 }
 219
 220                                                 $result = $db->query($queryOutDST);
 221                                                 $count = $db->getAffectedRowCount();
 222                                                 //$display .= "$year - Records updated with date modified out of DST with date in DST: $count <br>";
 223                                         }
 224                                 }
 225                         } // end outer forloop
 226                 }// end foreach loop
 227
 228
 229         }
 230         $display .= "<br><b>".$mod_strings['LBL_DST_FIX_DONE_DESC']."</b>";
 231 } elseif(!$done) {  // show primary screen
 232         $disabled = "";
 233         $confirmed = 'true';
 234         require_once('include/timezone/timezones.php');
 235         global $timezones;
 236         $timezoneOptions = '';
 237         ksort($timezones);
 238         if(!isset($defaultServerZone)){
 239                 $defaultServerZone = TimeDate::guessTimezone(0);
 240         }
 241         foreach($timezones as $key => $value) {
 242                 if(!empty($value['dstOffset'])) {
 243                         $dst = " (+DST)";
 244                 } else {
 245                         $dst = "";
 246                 }
 247                 if($key == $defaultServerZone){
 248                         $selected = 'selected';
 249                 }else{
 250                         $selected = '';
 251                 }
 252                 $gmtOffset = ($value['gmtOffset'] / 60);
 253                 if(!strstr($gmtOffset,'-')) {
 254                         $gmtOffset = "+".$gmtOffset;
 255                 }
 256                 $timezoneOptions .= "<option value='$key'".$selected.">".str_replace(array('_','North'), array(' ', 'N.'),$key). " (GMT".$gmtOffset.") ".$dst."</option>";
 257         }
 258
 259         // descriptions and assumptions
 260         $display = "
 261
 262                 <tr>
 263                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 264                                 ".$mod_strings['LBL_DST_FIX_TARGET']."
 265                         </slot></td>
 266                         <td class=\"tabDetailViewDF2\"><slot>
 267                                 ".$mod_strings['LBL_APPLY_DST_FIX_DESC']."
 268                         </slot></td>
 269                 </tr>
 270                 <tr>
 271                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 272                                 ".$mod_strings['LBL_DST_BEFORE']."
 273                         </slot></td>
 274                         <td class=\"tabDetailViewDF2\"><slot>
 275                                 ".$mod_strings['LBL_DST_BEFORE_DESC']."
 276                         </slot></td>
 277                 </tr>
 278                 <tr>
 279                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 280                                 ".$mod_strings['LBL_DST_FIX_CONFIRM']."
 281                         </slot></td>
 282                         <td class=\"tabDetailViewDF2\"><slot>
 283                                 ".$mod_strings['LBL_DST_FIX_CONFIRM_DESC']."
 284                         </slot></td>
 285                 </tr>
 286                 <tr>
 287                         <td width=\"20%\" class=\"tabDetailViewDL2\" nowrap align='right'><slot>
 288
 289                         </slot></td>
 290                         <td class=\"tabDetailViewDF2\"><slot>
 291                                 <table cellpadding='0' cellspacing='0' border='0'>
 292                                         <tr>
 293                                                 <td class=\"tabDetailViewDF2\"><slot>
 294                                                         <b>".$mod_strings['LBL_DST_CURRENT_SERVER_TIME']."</b>
 295                                                 </td>
 296                                                 <td class=\"tabDetailViewDF2\"><slot>
 297                                                         ".$timedate->to_display_time($timedate->nowDb(), true, false)."
 298                                                 </td>
 299                                         <tr>
 300                                         </tr>
 301                                                 <td class=\"tabDetailViewDF2\"><slot>
 302                                                         <b>".$mod_strings['LBL_DST_CURRENT_SERVER_TIME_ZONE']."</b>
 303                                                 </td>
 304                                                 <td class=\"tabDetailViewDF2\"><slot>
 305                                                         ".date("T")."<br>
 306                                                 </td>
 307                                         </tr>
 308                                         <tr>
 309                                                 <td class=\"tabDetailViewDF2\"><slot>
 310                                                         <b>".$mod_strings['LBL_DST_CURRENT_SERVER_TIME_ZONE_LOCALE']."</b>
 311                                                 </td>
 312                                                 <td class=\"tabDetailViewDF2\"><slot>
 313                                                         <select name='server_timezone'>".$timezoneOptions."</select><br>
 314                                                 </td>
 315                                         </tr>
 316                                 </table>
 317                         </slot></td>
 318                 </tr>";
 319 } else { // fix has been applied - don't want to allow a 2nd pass
 320         $display = $mod_strings['LBL_DST_FIX_DONE_DESC'];
 321         $disabled = 'DISABLED';
 322         $confirmed = 'false';
 323 }
 324
 325 if(!empty($_POST['upgrade'])){
 326         // enter row in versions table
 327         $qDst = "INSERT INTO versions VALUES ('".create_guid()."', 0, '".$timedate->nowDB()."', '".$timedate->nowDB()."', '".$current_user->id."', '".$current_user->id."', 'DST Fix', '3.5.1b', '3.5.1b')";
 328         $qRes = $db->query($qDst);
 329         // record server's time zone locale for future upgrades
 330         $qSTZ = "INSERT INTO config VALUES ('Update', 'server_timezone', '".$_REQUEST['server_timezone']."')";
 331         $rSTZ = $db->query($qSTZ);
 332         if(empty($_REQUEST['confirmed']) || $_REQUEST['confirmed'] == 'false') {
 333                 $display = $mod_strings['LBL_DST_FIX_DONE_DESC'];
 334                 $disabled = 'DISABLED';
 335                 $confirmed = 'false';
 336         }
 337         unset($_SESSION['GMTO']);
 338 }
 339
 340
 341
 342 echo getClassicModuleTitle($mod_strings['LBL_MODULE_NAME'], array($mod_strings['LBL_APPLY_DST_FIX']), true);
 343
 344 if(empty($disabled)){
 345 ?>
 346 <h2>Step 1:</h2>
 347 <table cellspacing="<?php echo $gridline;?>" class="other view">
 348 <tr>
 349     <td scope="row" width="20%">
 350         <slot><?php echo $mod_strings['LBL_DST_FIX_USER']; ?></slot>
 351     </td>
 352     <td>
 353         <slot>
 354             <?php echo $mod_strings['LBL_DST_FIX_USER_TZ']; ?><br>
 355             <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"'>
 356         </slot>
 357     </td>
 358 </tr>
 359 </table>
 360 <?php }?>
 361 <p>
 362 <form name='DstFix' action='index.php' method='POST'>
 363 <input type='hidden' name='module' value='Administration'>
 364 <input type='hidden' name='action' value='DstFix'>
 365 <?php
 366 if(empty($disabled)){
 367         echo "<h2>Step 2:</h2>";
 368 }
 369 ?>
 370 <table cellspacing="<?php echo $gridline;?>" class="other view">
 371 <?php
 372 echo $display;
 373  if(empty($disabled)){
 374     ?>
 375 <tr>
 376     <td scope="row" width="20%">
 377         <slot><?php echo $mod_strings['LBL_DST_UPGRADE']; ?></slot>
 378     </td>
 379     <td>
 380         <slot>
 381             <input type='checkbox' name='confirmed' value='true' checked="checked" />
 382             <?php echo $mod_strings['LBL_DST_APPLY_FIX']; ?>
 383         </slot>
 384     </td>
 385 </tr>
 386 <?php } ?>
 387 <tr>
 388     <td scope="row" width="20%"></td>
 389     <td>
 390         <slot>
 391 <?php
 392 if(empty($disabled)){
 393 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']."' >";
 394 }else{
 395 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']."'>";
 396 }
 397 ?>
 398         </slot>
 399     </td>
 400 </tr>
 401 </table>
 402 </form>