summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--UI/Contact/search.html14
-rw-r--r--scripts/vendor.pl101
-rw-r--r--sql/Pg-database.sql6
-rw-r--r--sql/modules/Company.sql83
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;