More customer/vendor search enhancements

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1966 4979c152-3d1c-0410-bac9-87ea11338e46

4 files changed, 169 insertions, 35 deletions

diff --git a/UI/Contact/search.html b/UI/Contact/search.html
index 1db82d3e..bf6207a7 100644
--- a/UI/Contact/search.html
+++ b/UI/Contact/search.html
@@ -10,7 +10,7 @@
 <?lsmb ELSE -?>
 <?lsmb entity_type = 'Unsupported' -?>
 <?lsmb END -?>
-<?lsmb title = text("$entity_type Search") # ' -?>
+<?lsmb title = text("$entity_type Search") # " -?>
 <form method="post" action="<?lsmb script ?>">
 <?lsmb INCLUDE input element_data = {
 	type = "hidden"
@@ -28,7 +28,7 @@
             <table>
               <tr>
                 <th align="right"><?lsmb text('Company Name') ?></th>
-                <td><?lsmb INCLUDE input element_data={size = '32', name = 'name'} ?></td>
+                <td><?lsmb INCLUDE input element_data={size = '32', name = 'legal_name'} ?></td>
               </tr>
               <tr>
                 <th align="right"><?lsmb text('Contact') ?></th>
@@ -51,17 +51,17 @@
                 <td><?lsmb INCLUDE input element_data={size = '32', name = 'employee'} ?></td>
 <?lsmb END -?>
               </tr>
-              <tr>
+              <!-- tr>
                 <th align="right"><?lsmb text('Notes') ?></th>
                 <td><?lsmb INCLUDE textarea element_data={
   rows = '3', cols = '32', name = 'notes'} ?></td>
-              </tr>
+              </tr -->
             </table>
           </td>
           <td>
             <table>
               <tr>
-                <th align="right"><?lsmb text("${form.label} Number") ?></th>
+                <th align="right"><?lsmb text("$entity_type Number") ?></th>
                 <td><?lsmb INCLUDE input element_data={
   size = '32', name = "meta_number"} ?></td>
               </tr>
@@ -83,7 +83,7 @@
               <tr>
                 <th align="right"><?lsmb text('Zip/Postal Code') ?></th>
                 <td><?lsmb INCLUDE input element_data={
-  size = '10', name = 'zipcode'} ?></td>
+  size = '10', name = 'mail_code'} ?></td>
               </tr>
               <tr>
                 <th align="right"><?lsmb text('Country') ?></th>
@@ -91,7 +91,7 @@
   size = '32', name = 'country'} ?></td>
               </tr>
               <tr>
-                <th align="right"><?lsmb text('Startdate') ?></th>
+                <th align="right"><?lsmb text('Active') ?></th>
                 <td>
                    <?lsmb text('From'); ' '; INCLUDE input element_data={
   size = '11', name = 'startdatefrom', class = 'date', title = user.dateformat};
diff --git a/scripts/vendor.pl b/scripts/vendor.pl
index 540fe5f8..70c6318c 100644
--- a/scripts/vendor.pl
+++ b/scripts/vendor.pl
@@ -96,7 +96,7 @@ sub add {
 
 =over
 
-=item search($self, $request, $user)
+=item search_result($self, $request, $user)
 
 Requires form var: search_pattern
 
@@ -108,35 +108,87 @@ as well as vendor/Company name.
 
 =cut
 
-sub search {
+sub get_results {
     my ($request) = @_;
-    
-    if ($request->type() eq 'POST') {
-        # assume it's asking us to do the search, now
-        
-        my $vendor = LedgerSMB::DBObject::Vendor->new(base => $request, copy => 'all');
-        $vendor->set(entity_class=>1);
-        my $results = $vendor->search($vendor->{search_pattern});
-
-        my $template = LedgerSMB::Template->new( user => $user, 
-    	template => 'Contact/vendor', language => $user->{language}, 
-            format => 'HTML');
-        $template->render($results);
         
+    my $vendor = LedgerSMB::DBObject::Vendor->new(base => $request, copy => 'all');
+    $vendor->set(entity_class=>1);
+    $vendor->{contact_info} = qq|{"%$request->{email}%","%$request->{phone}%"}|;
+    my $results = $vendor->search();
+    if ($vendor->{order_by}){
+       # TODO:  Set ordering logic
+    };
+
+    # URL Setup
+    my $baseurl = "$request->{script}";
+    my $search_url = "$base_url?action=get_results";
+    my $get_url = "$base_url?action=get";
+    for (keys %$vendor){
+        next if $_ eq 'order_by';
+        $search_url .= "&$_=$form->{$_}";
     }
-    else {
-        
-        # grab the happy search page out.
-        
-        my $template = LedgerSMB::Template->new( 
+
+    # Column definitions for dynatable
+    @columns = qw(legal_name meta_number business_type curr);
+    my %column_heading;
+    $column_heading{legal_name} = {
+        text => $request->{_locale}->text('Name'),
+	href => "$search_url&order_by=legal_name",
+    };
+    $column_heading{meta_number} = {
+        text => $request->{_locale}->text('Vendor Number'),
+	href => "$search_url&order_by=meta_number",
+    };
+    $column_heading{business_type} = {
+        text => $request->{_locale}->text('Business Type'),
+	href => "$search_url&order_by=business_type",
+    };
+    $column_heading{curr} = {
+        text => $request->{_locale}->text('Currency'),
+	href => "$search_url&order_by=curr",
+    };
+
+    my @rows;
+    for $ref (@{$vendor->{search_results}}){
+	push @rows, 
+                {legal_name   => $ref->{legal_name},
+                meta_number   => {text => $ref->{meta_number},
+                                  href => "$get_url&id=$ref->{entity_id}"},
+		business_type => $ref->{business_type},
+                curr          => $ref->{curr},
+                };
+    }
+
+    my @buttons = (
+	{name => 'action',
+        value => 'csv_chart_of_accounts',
+        text => $vendor->{_locale}->text('CSV Report'),
+        type => 'submit',
+        class => 'submit',
+        },
+	{name => 'action',
+        value => 'add',
+        text => $vendor->{_locale}->text('Add Vendor'),
+        type => 'submit',
+        class => 'submit',
+	}
+    );
+
+    my $template = LedgerSMB::Template->new( 
 		user => $user,
-		path => 'UI/Contact' ,
-    		template => 'vendor_search', 
-		locale => $request->{_locale}, 
+		path => 'UI' ,
+    		template => 'form-dynatable', 
+		locale => $vendor->{_locale}, 
 		format => 'HTML');
             
-        $template->render();
-    }
+    $template->render({
+	form    => $vendor,
+	columns => \@columns,
+        hiddens => $vendor,
+	buttons => \@buttons,
+	heading => \%column_heading,
+	rows    => \@rows,
+    });
 }
 
 =pod
@@ -189,6 +241,7 @@ sub _render_main_screen{
 sub search {
     my ($request) = @_;
     $request->{account_class} = 1;
+    $request->{script} = 'vendor.pl';
     my $template = LedgerSMB::Template->new( 
 	user => $request->{_user}, 
     	template => 'search', 
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 3e51a356..2b82eb8e 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -426,6 +426,8 @@ CREATE TABLE entity_credit_account (
     startdate date DEFAULT CURRENT_DATE,
     enddate date,
     threshold numeric default 0,
+    employee_id int references employee_entity(employee_id),
+    primary_contact int references person(id),
     PRIMARY KEY(entity_id, meta_number)
 );
 -- notes are from entity_note
@@ -614,7 +616,7 @@ CREATE TABLE ar (
   on_hold bool default false,
   reverse bool default false,
   approved bool default true,
-  credit_account int references entity_credit_account(id) not null,
+  entity_credit_account int references entity_credit_account(id) not null,
   description text
 );
 
@@ -650,7 +652,7 @@ CREATE TABLE ap (
   reverse bool default false,
   terms int2 DEFAULT 0,
   description text,
-  credit_account int references entity_credit_account(id)
+  entity_credit_account int references entity_credit_account(id)
 );
 
 COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index 8d78c946..b8359e27 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -1,4 +1,83 @@
-BEGIN;
+-- BEGIN;
+
+CREATE TYPE company_search_result AS (
+	entity_id int,
+	company_id int,
+	entity_credit_id int,
+	meta_number text,
+	entity_class int,
+	legal_name text,
+	sic_code text,
+	business_type text,
+	curr text
+);
+
+CREATE OR REPLACE FUNCTION company__search
+(in_account_class int, in_contact text, in_contact_info text[], 
+	in_meta_number text, in_address text, in_city text, in_state text, 
+	in_mail_code text, in_country text, in_date_from date, in_date_to date,
+	in_business_id int)
+RETURNS SETOF company_search_result AS $$
+DECLARE
+	out_row company_search_result;
+	loop_count int;
+	t_contact_info text[];
+BEGIN
+	t_contact_info = in_contact_info;
+
+
+	FOR out_row IN
+		SELECT e.id, c.id, ec.id, ec.meta_number, ec.entity_class, 
+			c.legal_name, c.sic_code, b.description , ec.curr
+		FROM entity e
+		JOIN company c ON (e.id = c.entity_id)
+		JOIN entity_credit_account ec ON (ec.entity_id = e.id)
+		LEFT JOIN business b ON (ec.business_id = b.id)
+		WHERE ec.entity_class = in_account_class
+			AND (c.id IN (select company_id FROM company_to_contact
+				WHERE contact LIKE ALL(t_contact_info))
+				OR '' LIKE ALL(t_contact_info))
+			
+			AND ec.meta_number = 
+				coalesce(in_meta_number, ec.meta_number)
+--			AND c.id IN 
+--				(select company_id FROM company_to_location
+--				WHERE location_id IN 
+--					(SELECT id FROM location
+--					WHERE line_one 
+--						ilike '%' || 
+--							coalesce(in_address, '')
+--							|| '%'
+--						AND city ILIKE 
+--							'%' || 
+--							coalesce(in_city, '') 
+--							|| '%'
+--						AND state ILIKE
+--							'%' || 
+--							coalesce(in_state, '') 
+--							|| '%'
+--						AND mail_code ILIKE
+--							'%' || 
+--							coalesce(in_mail_code,
+--								'')
+--							|| '%'
+--						AND country_id IN 
+--							(SELECT id FROM country
+--							WHERE name LIKE '%' ||
+--								in_country ||'%'
+--								OR short_name
+--								ilike 
+--								in_country)))
+			AND ec.business_id = 
+				coalesce(in_business_id, ec.business_id)
+--			AND ec.startdate <= coalesce(in_date_to, 
+--						ec.startdate)
+--			AND ec.enddate >= coalesce(in_date_from, ec.enddate)
+	LOOP
+		RETURN NEXT out_row;
+	END LOOP;
+END;
+$$ language plpgsql;	
 
 CREATE OR REPLACE FUNCTION entity__save_notes(in_entity_id int, in_note text)
 RETURNS INT AS
@@ -325,4 +404,4 @@ create or replace function _entity_location_save(
 
 $$ language 'plpgsql';
 
-COMMIT;
+-- COMMIT;