summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--LedgerSMB/DBObject/Payment.pm36
-rw-r--r--LedgerSMB/Form.pm6
-rw-r--r--scripts/payment.pl136
-rw-r--r--scripts/vouchers.pl15
-rw-r--r--sql/modules/Company.sql15
-rw-r--r--sql/modules/Payment.sql47
6 files changed, 242 insertions, 13 deletions
diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm
index b35bb2d8..bf507190 100644
--- a/LedgerSMB/DBObject/Payment.pm
+++ b/LedgerSMB/DBObject/Payment.pm
@@ -82,6 +82,22 @@ sub get_metadata {
}
}
+sub search {
+ my ($self) = @_;
+ if ($self->{meta_number} && !$self->{credit_id}){
+ my ($ref) = $self->exec_method(
+ funcname => 'entity_credit_get_id_by_meta_number'
+ );
+ my @keys = keys %$ref;
+ my $key = shift @keys;
+ $self->{credit_id} = $ref->{$key};
+ }
+ @{$self->{search_results}} = $self->exec_method(
+ funcname => 'payment__search'
+ );
+ return @{$self->{search_results}};
+}
+
sub get_open_accounts {
my ($self) = @_;
@{$self->{accounts}} =
@@ -110,6 +126,24 @@ sub get_all_accounts {
$self->exec_method(funcname => 'payment_get_all_accounts');
return @{$self->{accounts}};
}
+=over
+
+=item $payment->reverse()
+
+This function reverses a payment. A payment is defined as one source
+($payment->{source}) to one cash account ($payment->{cash_accno}) to one date
+($payment->{date_paid}) to one vendor/customer ($payment->{credit_id},
+$payment->{account_class}). This reverses the entries with that source.
+
+=back
+
+=cut
+
+sub reverse {
+ my ($self) = @_;
+ $self->exec_method(funcname => 'payment__reverse');
+ return $self->{dbh}->commit;
+}
=over
@@ -137,7 +171,7 @@ sub get_open_invoices {
=over
-=item $oayment->get_all_contact_invoices()
+=item $payment->get_all_contact_invoices()
This function returns a list of open accounts depending on the
$payment->{account_class} property. If this property is 1, it returns a list
diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm
index cb31c8e9..496cfcbf 100644
--- a/LedgerSMB/Form.pm
+++ b/LedgerSMB/Form.pm
@@ -1672,13 +1672,13 @@ sub get_name {
# Vendor and Customer are now views into entity_credit_account.
my $query = qq/
SELECT c.*, e.name FROM entity_credit_account c
- JOIN entity e ON c.entity_id = e.id
+ JOIN entity e ON (c.entity_id = e.id)
WHERE (lower(e.name) LIKE ?
- AND c.meta_number LIKE ?)
+ OR c.meta_number LIKE ?)
$where
ORDER BY e.name/;
- unshift( @queryargs, $name, $self->like($self->{"${table}number"}) );
+ unshift( @queryargs, $name, $self->{"${table}number"} );
my $sth = $self->{dbh}->prepare($query);
$sth->execute(@queryargs) || $self->dberror($query);
diff --git a/scripts/payment.pl b/scripts/payment.pl
index 4f13e395..991a8f54 100644
--- a/scripts/payment.pl
+++ b/scripts/payment.pl
@@ -82,6 +82,142 @@ sub payments {
$template->render($payment);
}
+sub get_search_criteria {
+ my ($request) = @_;
+ my $payment = LedgerSMB::DBObject::Payment->new({'base' => $request});
+ $payment->get_metadata();
+ if ($payment->{batch_id} && $payment->{batch_date}){
+ $payment->{date_reversed} = $payment->{batch_date};
+ }
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/payments',
+ template => 'search',
+ format => 'HTML',
+ );
+ $template->render($payment);
+}
+
+sub get_search_results {
+ my ($request) = @_;
+ my $rows = [];
+ my $payment = LedgerSMB::DBObject::Payment->new({'base' => $request});
+ my @search_results = $payment->search;
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => ($payment->{format}) ? $payment->{format} : 'HTML',
+ );
+
+ my $base_url = "payment.pl?";
+ my $search_url = "$base_url";
+ for my $key (keys %{$request->take_top_level}){
+ if ($base_url =~ /\?$/){
+ $base_url .= "$key=$request->{key}";
+ } else {
+ $base_url .= "&$key=$request->{key}";
+ }
+ }
+
+ my @columns = qw(selected meta_number date_paid amount source company_paid);
+ my $contact_type = ($payment->{account_class} == 1) ? 'Vendor' : 'Customer';
+
+ # CT: Locale strings for gettext:
+ # $request->{_locale}->text("Vendor Number");
+ # $request->{_locale}->text("Customer Number");
+
+ my $heading = {
+ selected => $request->{_locale}->text('Selected'),
+ company_paid => {
+ text => $request->{_locale}->text('Company Name'),
+ href => "$search_url&orderby=company_paid",
+ },
+ meta_number => {
+ text => $request->{_locale}->text(
+ "$contact_type Number"
+ ),
+ href => "$search_url&orderby=meta_number",
+ },
+ date_paid => {
+ text => $request->{_locale}->text('Date Paid'),
+ href => "$search_url&orderby=date_paid",
+ },
+ amount => {
+ text => $request->{_locale}->text('Total Paid'),
+ href => "$search_url&orderby=amount",
+ },
+ source => {
+ text => $request->{_locale}->text('Source'),
+ href => "$search_url&orderby=source",
+ },
+ };
+
+
+ my $classcount;
+ $classcount = 0;
+ my $rowcount;
+ $rowcount = 1;
+ for my $line (@search_results){
+ $classcount ||= 0;
+ $rowcount += 1;
+ push(@$rows, {
+ company_paid => $line->{company_paid},
+ amount => $request->format_amount(amount => $line->{amount}),
+ i => "$classcount",
+ date_paid => $line->{date_paid},
+ source => $line->{source},
+ meta_number => $line->{meta_number},
+ selected => {
+ input => {
+ type => "checkbox",
+ name => "payment_$rowcount",
+ value => "1",
+ },
+ }
+ });
+ $payment->{"credit_id_$rowcount"} = $line->{credit_id};
+ $payment->{"date_paid_$rowcount"} = $line->{date_paid};
+ $payment->{"source_$rowcount"} = $line->{source};
+ $classcount = ($classcount + 1) % 2;
+ ++$rowcount;
+ }
+ $payment->{rowcount} = $rowcount;
+ $payment->{script} = 'payment.pl';
+ $payment->{title} = $request->{_locale}->text("Payment Results");
+ my $hiddens = $payment->take_top_level;
+ $template->render({
+ form => $payment,
+ columns => \@columns,
+ heading => $heading,
+ hiddens => $payment->take_top_level,
+ rows => $rows,
+ buttons => [{
+ value => 'reverse_payments',
+ name => 'action',
+ class => 'submit',
+ type => 'submit',
+ text => $request->{_locale}->text('Reverse Payments'),
+ }]
+ });
+}
+
+sub get_search_results_reverse_payments {
+ my ($request) = @_;
+ my $payment = LedgerSMB::DBObject::Payment->new({base => $request});
+ for my $count (1 .. $payment->{rowcount}){
+ if ($payment->{"payment_$count"}){
+ $payment->{credit_id} = $payment->{"credit_id_$count"};
+ $payment->{date_paid} = $payment->{"date_paid_$count"};
+ $payment->{source} = $payment->{"source_$count"};
+ $payment->reverse;
+ }
+ }
+ get_search_criteria($payment);
+}
+
sub check_job {
my ($request) = @_;
my $payment = LedgerSMB::DBObject::Payment->new({'base' => $request});
diff --git a/scripts/vouchers.pl b/scripts/vouchers.pl
index 4a3c1129..c4799bdf 100644
--- a/scripts/vouchers.pl
+++ b/scripts/vouchers.pl
@@ -63,6 +63,21 @@ sub add_vouchers {
$request->{account_class} = 1;
LedgerSMB::Scripts::payment::payments($request);
}},
+ payment_reversal => {
+ script => 'scripts/payment.pl',
+ function => sub {
+ my ($request) = @_;
+ $request->{account_class} = 1;
+ LedgerSMB::Scripts::payment::get_search_criteria($request);
+ }},
+ receipt_reversal => {
+ script => 'scripts/payment.pl',
+ function => sub {
+ my ($request) = @_;
+ $request->{account_class} = 2;
+ LedgerSMB::Scripts::payment::get_search_criteria($request);
+ }},
+
};
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index ff2795af..bcb0599e 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -97,6 +97,21 @@ BEGIN
END;
$$ LANGUAGE PLPGSQL;
+CREATE OR REPLACE FUNCTION entity_credit_get_id_by_meta_number
+(in_meta_number text, in_account_class int)
+returns int AS
+$$
+DECLARE out_credit_id int;
+BEGIN
+ SELECT id INTO out_credit_id
+ FROM entity_credit_account
+ WHERE meta_number = in_meta_number
+ AND entity_class = in_account_class;
+
+ RETURN out_credit_id;
+END;
+$$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION entity_list_contact_class()
RETURNS SETOF contact_class AS
$$
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 6c667828..f898528b 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -551,8 +551,10 @@ $$ This function return vendor or customer info, its under construction $$;
CREATE TYPE payment_record AS (
amount numeric,
meta_number text,
+ credit_id int,
company_paid text,
accounts text[],
+ source text,
date_paid date
);
@@ -565,11 +567,11 @@ DECLARE
out_row payment_record;
BEGIN
FOR out_row IN
- select sum(CASE WHEN c.entity_class = 1 then a.amount * -1
- ELSE a.amount END), c.meta_number,
- co.legal_name,
+ select sum(CASE WHEN c.entity_class = 1 then a.amount
+ ELSE a.amount * -1 END), c.meta_number,
+ c.id, co.legal_name,
compound_array(ARRAY[ARRAY[ch.id::text, ch.accno,
- ch.description]]), a.transdate
+ ch.description]]), a.source, a.transdate
FROM entity_credit_account c
JOIN ( select entity_credit_account, id
FROM ar WHERE in_account_class = 2
@@ -586,7 +588,9 @@ BEGIN
OR in_date_from IS NULL)
AND (a.transdate <= in_date_to OR in_date_to IS NULL)
AND (source = in_source OR in_source IS NULL)
- GROUP BY c.meta_number, co.legal_name, a.transdate
+ GROUP BY c.meta_number, c.id, co.legal_name, a.transdate,
+ a.source
+ ORDER BY a.transdate, c.meta_number, a.source
LOOP
RETURN NEXT out_row;
END LOOP;
@@ -595,12 +599,18 @@ $$ language plpgsql;
CREATE OR REPLACE FUNCTION payment__reverse
(in_source text, in_date_paid date, in_credit_id int, in_cash_accno text,
- in_date_reversed date, in_account_class int)
+ in_date_reversed date, in_account_class int, in_batch_id int)
RETURNS INT
AS $$
DECLARE
pay_row record;
+ t_voucher_id int;
+ t_voucher_inserted bool;
BEGIN
+ IF in_batch_id IS NOT NULL THEN
+ t_voucher_id := nextval('voucher_id_seq');
+ t_voucher_inserted := FALSE;
+ END IF;
FOR pay_row IN
SELECT a.*, c.ar_ap_account_id
FROM acc_trans a
@@ -618,15 +628,34 @@ BEGIN
AND in_credit_id = c.id
AND in_cash_accno = ch.accno
LOOP
+ IF in_batch_id IS NOT NULL
+ AND t_voucher_inserted IS NOT TRUE
+ THEN
+ INSERT INTO voucher
+ (id, trans_id, batch_id, batch_class)
+ VALUES
+ (t_voucher_id, pay_row.trans_id, in_batch_id,
+ CASE WHEN in_account_class = 1 THEN 4
+ WHEN in_account_class = 2 THEN 7
+ END);
+
+ t_voucher_inserted := TRUE;
+ END IF;
+
INSERT INTO acc_trans
- (trans_id, chart_id, amount, transdate, source, memo)
+ (trans_id, chart_id, amount, transdate, source, memo, approved,
+ voucher_id)
VALUES
(pay_row.trans_id, pay_row.chart_id, pay_row.amount * -1,
in_date_reversed, in_source, 'Reversing ' ||
- COALESCE(in_source, '')),
+ COALESCE(in_source, ''),
+ case when in_batch_id is not null then false
+ else true end, t_voucher_id),
(pay_row.trans_id, pay_row.ar_ap_account_id, pay_row.amount,
in_date_reversed, in_source, 'Reversing ' ||
- COALESCE(in_source, ''));
+ COALESCE(in_source, ''),
+ case when in_batch_id is not null then false
+ else true end, t_voucher_id);
IF in_account_class = 1 THEN
UPDATE ap SET paid = amount -
(SELECT sum(a.amount)
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-29 08:45:29 +0000