Bug 280500: Replace "DATE_FORMAT()" with Bugzilla::DB function call

Patch By Tomas Kopal <Tomas.Kopal@altap.cz> r=mkanat, a=myk

8 files changed, 36 insertions, 22 deletions

diff --git a/Bugzilla/Attachment.pm b/Bugzilla/Attachment.pm
index 5f491f315..8be92dcf2 100644
--- a/Bugzilla/Attachment.pm
+++ b/Bugzilla/Attachment.pm
@@ -67,6 +67,8 @@ sub query
   # "attachments" variable.
   my ($bugid) = @_;
 
+  my $dbh = Bugzilla->dbh;
+
   my $in_editbugs = &::UserInGroup("editbugs");
   &::SendSQL("SELECT product_id
            FROM bugs 
@@ -76,9 +78,9 @@ sub query
 
   # Retrieve a list of attachments for this bug and write them into an array
   # of hashes in which each hash represents a single attachment.
-  &::SendSQL("
-              SELECT attach_id, DATE_FORMAT(creation_ts, '%Y.%m.%d %H:%i'),
-              mimetype, description, ispatch, isobsolete, isprivate, 
+  &::SendSQL("SELECT attach_id, " .
+             $dbh->sql_date_format('creation_ts', '%Y.%m.%d %H:%i') .
+             ", mimetype, description, ispatch, isobsolete, isprivate, 
               submitter_id, LENGTH(thedata)
               FROM attachments WHERE bug_id = $bugid ORDER BY attach_id
             ");
diff --git a/Bugzilla/Bug.pm b/Bugzilla/Bug.pm
index 5e25820e9..2f1df58bd 100755
--- a/Bugzilla/Bug.pm
+++ b/Bugzilla/Bug.pm
@@ -144,18 +144,19 @@ sub initBug  {
       rep_platform, op_sys, bug_status, resolution, priority,
       bug_severity, bugs.component_id, components.name, assigned_to,
       reporter, bug_file_loc, short_desc, target_milestone,
-      qa_contact, status_whiteboard,
-      DATE_FORMAT(creation_ts,'%Y.%m.%d %H:%i'),
+      qa_contact, status_whiteboard, " .
+      $dbh->sql_date_format('creation_ts', '%Y.%m.%d %H:%i') . ",
       delta_ts, COALESCE(SUM(votes.vote_count), 0),
       reporter_accessible, cclist_accessible,
-      estimated_time, remaining_time, DATE_FORMAT(deadline,'%Y-%m-%d')
-    from bugs left join votes using(bug_id),
+      estimated_time, remaining_time, " .
+      $dbh->sql_date_format('deadline', '%Y-%m-%d') . ",
+    FROM bugs LEFT JOIN votes using(bug_id),
       classifications, products, components
     WHERE bugs.bug_id = ?
       AND classifications.id = products.classification_id
       AND products.id = bugs.product_id
       AND components.id = bugs.component_id
-    group by bugs.bug_id";
+    GROUP BY bugs.bug_id";
 
   my $bug_sth = $dbh->prepare($query);
   $bug_sth->execute($bug_id);
@@ -534,11 +535,11 @@ sub GetComments {
     my @comments;
     my $sth = $dbh->prepare(
             "SELECT  profiles.realname AS name, profiles.login_name AS email,
-                     date_format(longdescs.bug_when,'%Y.%m.%d %H:%i') AS time,
-                     longdescs.thetext AS body, longdescs.work_time,
+            " . $dbh->sql_date_format('longdescs.bug_when', '%Y.%m.%d %H:%i') . "
+               AS time, longdescs.thetext AS body, longdescs.work_time,
                      isprivate, already_wrapped,
-                     date_format(longdescs.bug_when,'%Y%m%d%H%i%s')
-            FROM     longdescs, profiles
+            " . $dbh->sql_date_format('longdescs.bug_when', '%Y%m%d%H%i%s') . "
+             FROM    longdescs, profiles
             WHERE    profiles.userid = longdescs.who
               AND    longdescs.bug_id = ?
             ORDER BY longdescs.bug_when");
diff --git a/CGI.pl b/CGI.pl
index 1a8f09c2f..1b556bc71 100644
--- a/CGI.pl
+++ b/CGI.pl
@@ -317,6 +317,7 @@ sub LogActivityEntry {
 sub GetBugActivity {
     my ($id, $starttime) = (@_);
     my $datepart = "";
+    my $dbh = Bugzilla->dbh;
 
     die "Invalid id: $id" unless $id=~/^\s*\d+\s*$/;
 
@@ -333,9 +334,9 @@ sub GetBugActivity {
     my $query = "
         SELECT COALESCE(fielddefs.description, bugs_activity.fieldid),
                 fielddefs.name,
-                bugs_activity.attach_id,
-                DATE_FORMAT(bugs_activity.bug_when,'%Y.%m.%d %H:%i:%s'),
-                bugs_activity.removed, bugs_activity.added,
+                bugs_activity.attach_id, " .
+                $dbh->sql_date_format('bugs_activity.bug_when', '%Y.%m.%d %H:%i:%s') .
+                ", bugs_activity.removed, bugs_activity.added,
                 profiles.login_name
         FROM bugs_activity $suppjoins LEFT JOIN fielddefs ON 
                                      bugs_activity.fieldid = fielddefs.fieldid,
diff --git a/attachment.cgi b/attachment.cgi
index 0a296609b..92d127e1a 100755
--- a/attachment.cgi
+++ b/attachment.cgi
@@ -800,11 +800,14 @@ sub viewall
   # Retrieve the attachments from the database and write them into an array
   # of hashes where each hash represents one attachment.
     my $privacy = "";
+    my $dbh = Bugzilla->dbh;
+
     if (Param("insidergroup") && !(UserInGroup(Param("insidergroup")))) {
         $privacy = "AND isprivate < 1 ";
     }
-    SendSQL("SELECT attach_id, DATE_FORMAT(creation_ts, '%Y.%m.%d %H:%i'),
-            mimetype, description, ispatch, isobsolete, isprivate,
+    SendSQL("SELECT attach_id, " .
+            $dbh->sql_date_format('creation_ts', '%Y.%m.%d %H:%i') . ",
+            mimetype, description, ispatch, isobsolete, isprivate, 
             LENGTH(thedata)
             FROM attachments WHERE bug_id = $::FORM{'bugid'} $privacy 
             ORDER BY attach_id");
diff --git a/buglist.cgi b/buglist.cgi
index fd01f10d5..bd9aa8af7 100755
--- a/buglist.cgi
+++ b/buglist.cgi
@@ -505,7 +505,7 @@ DefineColumn("remaining_time"    , "bugs.remaining_time"        , "Remaining Hou
 DefineColumn("actual_time"       , "(SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) AS actual_time", "Actual Hours");
 DefineColumn("percentage_complete","(100*((SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))/((SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id))+bugs.remaining_time))) AS percentage_complete", "% Complete"); 
 DefineColumn("relevance"         , "relevance"                  , "Relevance"        );
-DefineColumn("deadline"          , "DATE_FORMAT(bugs.deadline, '%Y-%m-%d')", "Deadline");
+DefineColumn("deadline"          , $dbh->sql_date_format('bugs.deadline', '%Y-%m-%d'), "Deadline");
 
 ################################################################################
 # Display Column Determination
diff --git a/globals.pl b/globals.pl
index f6f15566d..100c8fab6 100644
--- a/globals.pl
+++ b/globals.pl
@@ -971,7 +971,9 @@ sub GetLongDescriptionAsText {
     my $result = "";
     my $count = 0;
     my $anyprivate = 0;
-    my ($query) = ("SELECT profiles.login_name, DATE_FORMAT(longdescs.bug_when,'%Y.%m.%d %H:%i'), " .
+    my $dbh = Bugzilla->dbh;
+    my ($query) = ("SELECT profiles.login_name, " .
+                   $dbh->sql_date_format('longdescs.bug_when', '%Y.%m.%d %H:%i') . ", " .
                    "       longdescs.thetext, longdescs.isprivate, " .
                    "       longdescs.already_wrapped " .
                    "FROM   longdescs, profiles " .
diff --git a/request.cgi b/request.cgi
index b4b7e6766..fa37344e2 100755
--- a/request.cgi
+++ b/request.cgi
@@ -56,6 +56,7 @@ exit;
 
 sub queue {
     my $cgi = Bugzilla->cgi;
+    my $dbh = Bugzilla->dbh;
     
     validateStatus($cgi->param('status'));
     validateGroup($cgi->param('group'));
@@ -75,7 +76,7 @@ sub queue {
                 flags.attach_id, attachments.description,
                 requesters.realname, requesters.login_name,
                 requestees.realname, requestees.login_name,
-                DATE_FORMAT(flags.creation_date,'%Y.%m.%d %H:%i'),
+    " . $dbh->sql_date_format('flags.creation_date', '%Y.%m.%d %H:%i') . ",
     " . 
     # Select columns that help us weed out secure bugs to which the user
     # should not have access.
diff --git a/whine.pl b/whine.pl
index 52fbe4e82..97378a178 100755
--- a/whine.pl
+++ b/whine.pl
@@ -118,12 +118,16 @@ if (open(NOMAIL, '<', "$datadir/nomail")) {
 }
 
 # get the current date and time from the database
-$sth = $dbh->prepare( 'SELECT DATE_FORMAT( NOW(), "%y,%m,%e,%w,%k,%i")');
+$sth = $dbh->prepare('SELECT ' . $dbh->sql_date_format('NOW()', '%y,%m,%d,%a,%H,%i'));
 $sth->execute;
-my ($now_year, $now_month, $now_day, $now_weekday, $now_hour, $now_minute) =
+my ($now_year, $now_month, $now_day, $now_weekdayname, $now_hour, $now_minute) =
         split(',', $sth->fetchrow_array);
 $sth->finish;
 
+# As DBs have different days numbering, use day name and convert it
+# to the range 0-6
+my $now_weekday = index("SunMonTueWedThuFriSat", $now_weekdayname) / 3;
+
 my @daysinmonth = qw(0 31 28 31 30 31 30 31 31 30 31 30 31);
 # Alter February in case of a leap year.  This simple way to do it only
 # applies if you won't be looking at February of next year, which whining