add customer fields option with agent, display_custnum, status and name, RT#73721

[freeside.git] / FS / FS / h_Common.pm

diff --git a/FS/FS/h_Common.pm b/FS/FS/h_Common.pm
index d55da8c..87fb76d 100644 (file)
--- a/FS/FS/h_Common.pm
+++ b/FS/FS/h_Common.pm
@@ -30,9 +30,10 @@ inherit from.
 
 =item sql_h_search END_TIMESTAMP [ START_TIMESTAMP ] 
 
-Returns an a list consisting of the "SELECT" and "EXTRA_SQL" SQL fragments to
-search for the appropriate history records created before END_TIMESTAMP
-and (optionally) not cancelled before START_TIMESTAMP.
+Returns an a list consisting of the "SELECT", "EXTRA_SQL", SQL fragments, a
+placeholder for "CACHE_OBJ" and an "AS" SQL fragment, to search for the
+appropriate history records created before END_TIMESTAMP and (optionally) not
+deleted before START_TIMESTAMP.
 
 =cut
 
@@ -48,25 +49,45 @@ sub sql_h_search {
     confess 'Called sql_h_search without END_TIMESTAMP';
   }
 
-  my $notcancelled = '';
+  my( $notdeleted, $notdeleted_mr ) = ( '', '' );
   if ( scalar(@_) && $_[0] ) {
-    $notcancelled = "AND 0 = ( SELECT COUNT(*) FROM $table as notdel
-                                WHERE notdel.$pkey = maintable.$pkey
-                                AND notdel.history_action = 'delete'
-                                AND notdel.history_date > maintable.history_date
-                                AND notdel.history_date <= $_[0]
-                             )";
+    $notdeleted =
+      "AND NOT EXISTS ( SELECT 1 FROM $table as notdel
+                          WHERE notdel.$pkey = maintable.$pkey
+                            AND notdel.history_action = 'delete'
+                            AND notdel.history_date > maintable.history_date
+                            AND notdel.history_date <= $_[0]
+                      )";
+    $notdeleted_mr =
+      "AND NOT EXISTS ( SELECT 1 FROM $table as notdel_mr
+                          WHERE notdel_mr.$pkey = mostrecent.$pkey
+                            AND notdel_mr.history_action = 'delete'
+                            AND notdel_mr.history_date > mostrecent.history_date
+                            AND notdel_mr.history_date <= $_[0]
+                      )";
   }
 
   (
-    "DISTINCT ON ( $pkey ) *",
+    #"DISTINCT ON ( $pkey ) *",
+    "*",
 
     "AND history_date <= $end
      AND (    history_action = 'insert'
            OR history_action = 'replace_new'
          )
-     $notcancelled
-     ORDER BY $pkey ASC, history_date DESC",
+     $notdeleted
+     AND history_date = ( SELECT MAX(mostrecent.history_date)
+                            FROM $table AS mostrecent
+                            WHERE mostrecent.$pkey = maintable.$pkey
+                             AND mostrecent.history_date <= $end
+                             AND (    mostrecent.history_action = 'insert'
+                                   OR mostrecent.history_action = 'replace_new'
+                                 )
+                             $notdeleted_mr
+                        )
+
+     ORDER BY $pkey ASC",
+     #ORDER BY $pkey ASC, history_date DESC",
 
      '',
 
@@ -89,6 +110,55 @@ sub sql_h_searchs {
   ($select, $where, $cacheobj, $as);
 }
 
+=item sql_diff START_TIMESTAMP, END_TIMESTAMP[, WHERE]
+
+Returns a complete SQL statement to find all records that were changed 
+between START_TIMESTAMP and END_TIMESTAMP. This finds only replacements,
+not new or deleted records.
+
+For each modified record, this will return I<one> row (not two rows as in
+the history table) with the primary key of the record, "old_historynum"
+(the historynum of the last modification before START_TIMESTAMP), and
+"new_historynum" (the last modification before END_TIMESTAMP). Join these
+back to the h_* table to retrieve the actual field values.
+
+Within the query, the last history records as of START and END are aliased
+as "old" and "new"; you can append a WHERE clause to take advantage of this.
+
+=cut
+
+sub sql_diff {
+  my $class = shift;
+  my $table = $class->table;
+  my ($real_table) = ($table =~ /^h_(\w+)$/);
+  my $pkey = dbdef->table($real_table)->primary_key;
+  my @fields = "FS::$real_table"->fields;
+
+  my ($sdate, $edate) = @_;
+  ($sdate, $edate) = ($edate, $sdate) if $edate < $sdate;
+
+  my @select = (
+    "old.$pkey",
+    'old.historynum   AS old_historynum',
+    'new.historynum   AS new_historynum',
+  );
+  my $new = 
+    "SELECT DISTINCT ON ($pkey) * FROM $table
+      WHERE history_action = 'replace_new'
+        AND history_date >= $sdate AND history_date <  $edate
+      ORDER BY $pkey ASC, history_date DESC";
+  my $old =
+    "SELECT DISTINCT ON ($pkey) * FROM $table
+      WHERE (history_action = 'replace_new' OR history_action = 'insert')
+        AND history_date <  $sdate
+      ORDER BY $pkey ASC, history_date DESC";
+
+  my $from = "($new) AS new JOIN ($old) AS old USING ($pkey)";
+
+  return "SELECT ".join(',', @select)." FROM $from";
+}
+
+
 =back
 
 =head1 BUGS