ticket 1418, a tool for customer note importation

3 files changed, 298 insertions, 0 deletions

diff --git a/httemplate/misc/cust_main_note-import.cgi b/httemplate/misc/cust_main_note-import.cgi
new file mode 100644
index 000000000..07b922f6c
--- /dev/null
+++ b/httemplate/misc/cust_main_note-import.cgi
@@ -0,0 +1,200 @@
+<% include("/elements/header.html", 'Batch Customer Note Import') %>
+%
+
+<FORM ACTION="process/cust_main_note-import.cgi" METHOD="POST">
+
+
+<SCRIPT TYPE="text/javascript">
+
+  function clearhint_custnum() {
+
+    if ( this.value == 'Not found' ) {
+      this.value = '';
+      this.style.color = '#000000';
+    }
+
+  }
+
+  function search_custnum() {
+
+    this.style.color = '#000000'
+
+    var custnum_obj = this;
+    var searchrow = this.getAttribute('rownum');
+    var custnum = this.value;
+    var name_obj = document.getElementById('name'+searchrow);
+
+    if ( custnum == 'searching...' || custnum == 'Not found' )
+      return;
+
+    var customer_select = document.getElementById('cust_select'+searchrow);
+
+    if ( custnum == '' ) {
+      customer_select.selectedIndex = 0;
+      return;
+    }
+
+    custnum_obj.value = 'searching...';
+    custnum_obj.disabled = true;
+    custnum_obj.style.backgroundColor = '#dddddd';
+
+
+    //alert('search for custnum ' + custnum + ', row#' + searchrow );
+
+    function search_custnum_update(name) {
+
+      var name = eval('(' + name + ')' );
+
+      custnum_obj.disabled = false;
+      custnum_obj.style.backgroundColor = '#ffffff';
+
+      if ( name.length > 0 ) {
+        //alert('custnum found: ' + name);
+        opt(customer_select,custnum,name,'#000000');
+        customer_select.selectedIndex = customer_select.length - 1;
+        custnum_obj.value = custnum;
+        name_obj.value = name;
+      } else {
+        custnum_obj.value = 'Not found';
+        custnum_obj.style.color = '#ff0000';
+      }
+
+    }
+
+    custnum_search( custnum, search_custnum_update );
+
+  }
+
+  function select_customer() {
+
+    var custnum = this.options[this.selectedIndex].value;
+    var name = this.options[this.selectedIndex].text;
+
+    var searchrow = this.getAttribute('rownum');
+    var custnum_obj = document.getElementById('custnum'+searchrow);
+    var name_obj = document.getElementById('name'+searchrow);
+
+    custnum_obj.value = custnum;
+    custnum_obj.style.color = '#000000';
+
+    name_obj.value = name;
+
+  }
+
+  function opt(what,value,text,color) {
+    var optionName = new Option(text, value, false, false);
+    optionName.style.color = color;
+    var length = what.length;
+    what.options[length] = optionName;
+  }
+
+  function previewChanged(what) {
+    var submit_obj = document.getElementById('importsubmit');
+    if (what.checked) {
+      submit_obj.value = 'Preview note import';
+    }else{
+      submit_obj.value = 'Import notes';
+    }
+  }
+
+</SCRIPT>
+
+<% include('/elements/xmlhttp.html',
+              'url'  => $p. 'misc/xmlhttp-cust_main-search.cgi',
+              'subs' => [qw( custnum_search )],
+           )
+%>
+
+%  my $fh = $cgi->upload('csvfile');
+%  my $csv = new Text::CSV_XS;
+%  my $skip_fuzzies = $cgi->param('fuzzies') ? 0 : 1;
+%
+%  if ( defined($fh) ) {
+     <TABLE BGCOLOR="#cccccc" BORDER=0 CELLSPACING=0>
+     <TR>
+       <TH>Cust #</TH>
+       <TH>Customer</TH>
+       <TH>Last</TH>
+       <TH>First</TH>
+       <TH>Note to be added</TH>
+     </TR>
+%    my $agentnum   => scalar($cgi->param('agentnum')),
+%    my $line;
+%    my $row = 0;
+%    while ( defined($line=<$fh>) ) {
+%      chomp $line;
+%      $line =~ s/^(.*)(#!).*/$1/;
+%
+%      $csv->parse($line) or die "can't parse line: " . $csv->error_input();
+%      my $custnum = 0;
+%      my @values = $csv->fields();
+%      my $last  = shift @values;
+%      if ($last =~ /^\s*(\d+)\s*$/ ) {
+%        $custnum = $1;
+%        $last = shift @values;
+%      }
+%      my $first = shift @values;
+%      my $note  = join ' ', @values;
+%      next unless ( $last || $first || $note );
+%      my @cust_main = ();
+%      warn "searching for: $last, $first" if ($first || $last);
+%      if ($custnum) {
+%        @cust_main = qsearch('cust_main', { 'custnum' => $custnum });
+%      } else {
+%        @cust_main = FS::cust_main::smart_search(
+%                                          'search' => "$last, $first",
+%                                          'no_fuzzy_on_exact' => $skip_fuzzies,
+%                                                )
+%          if ($first || $last);
+%      }
+%
+       <TR>
+         <TD>
+           <INPUT TYPE="text" NAME="custnum<% $row %>" ID="custnum<% $row %>" SIZE=8 MAXLENGTH=12 VALUE="<% $cust_main[0] ? $cust_main[0]->custnum : '' %>" rownum="<% $row %>">
+             <SCRIPT TYPE="text/javascript">
+               var custnum_input<% $row %> = document.getElementById("custnum<% $row %>");
+               custnum_input<% $row %>.onfocus = clearhint_custnum;
+               custnum_input<% $row %>.onchange = search_custnum;
+             </SCRIPT>
+         </TD>
+         <TD>
+           <SELECT NAME="cust_select<% $row %>" ID="cust_select<% $row %>" rownum="<% $row %>">
+             <OPTION VALUE="">---</OPTION>
+%      my $i=0;
+%      foreach (@cust_main) {
+             <OPTION <% $i ? '' : 'SELECTED' %> VALUE="<% $_->custnum %>"><% $_->name %></OPTION>
+%        $i++;
+%      }
+           </SELECT>
+             <SCRIPT TYPE="text/javascript">
+               var customer_select<% $row %> = document.getElementById("cust_select<% $row %>");
+               customer_select<% $row %>.onchange = select_customer;
+             </SCRIPT>
+           <INPUT TYPE="hidden" NAME="name<% $row %>" ID="name<% $row %>" VALUE="<% $i ? $cust_main[0]->name : '' %>">
+         </TD>
+         <TD>
+           <% $first %>
+           <INPUT TYPE="hidden" NAME="first<% $row %>" VALUE="<% $first %>">
+         </TD>
+         <TD>
+           <% $last %>
+           <INPUT TYPE="hidden" NAME="last<% $row %>" VALUE="<% $last %>">
+         </TD>
+         <TD>
+           <% $note %>
+           <INPUT TYPE="hidden" NAME="note<% $row %>" VALUE="<% $note %>">
+         </TD>
+       </TR>
+%      $row++;
+%    }
+     </TABLE>
+     <INPUT TYPE="submit" NAME="submit" ID="importsubmit" VALUE="Import notes">
+     <INPUT TYPE="checkbox" NAME="preview" onchange="previewChanged(this);">
+     Preview mode
+%  } else {
+     No file supplied
+%  }
+
+</FORM>
+</BODY>
+</HTML>
diff --git a/httemplate/misc/cust_main_note-import.html b/httemplate/misc/cust_main_note-import.html
new file mode 100644
index 000000000..67f49f326
--- /dev/null
+++ b/httemplate/misc/cust_main_note-import.html
@@ -0,0 +1,32 @@
+<% include("/elements/header.html",'Batch Customer Note Import') %>
+
+<FORM ACTION="cust_main_note-import.cgi" METHOD="post" ENCTYPE="multipart/form-data">
+
+Import a CSV file containing customer notes records.
+<BR><BR>
+
+File format is CSV, with the following field order: <i>[custnum], last, first, notefield1, notefield2, notefield3...</i>
+<BR>
+The optional custnum field is identified by being numeric.
+Anything after the character sequence #! is ignored.
+<BR><BR>
+
+<% &ntable("#cccccc") %>
+
+<TR>
+  <TH ALIGN="right">CSV filename</TH>
+  <TD><INPUT TYPE="file" NAME="csvfile"></TD>
+</TR>
+<TR>
+  <TH ALIGN="right">Include additional possibilites when exact match is found</TH>
+  <TD><INPUT TYPE="checkbox" NAME="fuzzies"></TD>
+</TR>
+
+</TABLE>
+<BR><BR>
+
+<INPUT TYPE="submit" VALUE="Load and match">
+</FORM>
+
+<% include('/elements/footer.html') %>
+
diff --git a/httemplate/misc/process/cust_main_note-import.cgi b/httemplate/misc/process/cust_main_note-import.cgi
new file mode 100644
index 000000000..af06ae95e
--- /dev/null
+++ b/httemplate/misc/process/cust_main_note-import.cgi
@@ -0,0 +1,66 @@
+<% include("/elements/header.html", "Batch Customer Note Import $op") %>
+
+The following items <% $op eq 'Preview' ? 'would not be' : 'were not' %> imported.  (See below for imported items)
+<PRE>
+%  foreach my $row (@uninserted) {
+%    $csv->combine( (map{ $row->{$_} } qw(last first note) ),
+%                   $row->{error} ? ('#!', $row->{error}) : (),
+%                 );
+<% $csv->string %>
+%  }
+</PRE>
+
+The following items <% $op eq 'Preview' ? 'would be' : 'were' %> imported.  (See above for unimported items)
+
+<PRE>
+%  foreach my $row (@inserted) {
+%    $csv->combine( (map{ $row->{$_} } qw(custnum last first note) ),
+%                   ('#!', $row->{name}),
+%                 );
+<% $csv->string %>
+%  }
+</PRE>
+  
+<%init>
+my $date = time;
+my $otaker = $FS::CurrentUser::CurrentUser->username;
+my $csv = new Text::CSV_XS;
+
+my $param = $cgi->Vars;
+
+my $op = $param->{preview} ? "Preview" : "Results";
+
+my @inserted = ();
+my @uninserted = ();
+for ( my $row = 0; exists($param->{"custnum$row"}); $row++ ) {
+  if ( $param->{"custnum$row"} ) {
+    my $cust_main_note = new FS::cust_main_note {
+                                          'custnum'  => $param->{"custnum$row"},
+                                          '_date'    => $date,
+                                          'otaker'   => $otaker,
+                                          'comments' => $param->{"note$row"},
+                                                };
+    my $error = '';
+    $error = $cust_main_note->insert unless ($op eq "Preview");
+    my $result = { 'custnum' => $param->{"custnum$row"},
+                   'last'    => $param->{"last$row"},
+                   'first'   => $param->{"first$row"},
+                   'note'    => $param->{"note$row"},
+                   'name'    => $param->{"name$row"},
+                   'error'   => $error,
+                 };
+    if ($error) {
+      push @uninserted, $result;
+    }else{
+      push @inserted, $result;
+    }
+  }else{
+    push @uninserted, { 'custnum' => '',
+                        'last'    => $param->{"last$row"},
+                        'first'   => $param->{"first$row"},
+                        'note'    => $param->{"note$row"},
+                        'error'   => '',
+                      };
+  }
+}
+</%init>