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 ca13e1b..87fb76d 100644 (file)
--- a/FS/FS/h_Common.pm
+++ b/FS/FS/h_Common.pm
@@ -52,19 +52,19 @@ sub sql_h_search {
   my( $notdeleted, $notdeleted_mr ) = ( '', '' );
   if ( scalar(@_) && $_[0] ) {
     $notdeleted =
-      "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]
-               )";
+      "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 0 = ( SELECT COUNT(*) 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]
-               )";
+      "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]
+                      )";
   }
 
   (
@@ -110,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