diff options
| -rw-r--r-- | UI/Contact/search.html | 14 | ||||
| -rw-r--r-- | scripts/vendor.pl | 101 | ||||
| -rw-r--r-- | sql/Pg-database.sql | 6 | ||||
| -rw-r--r-- | sql/modules/Company.sql | 83 |
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; |