[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1987] trunk
- Subject: SF.net SVN: ledger-smb: [1987] trunk
- From: ..hidden..
- Date: Thu, 20 Dec 2007 17:07:57 -0800
Revision: 1987
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1987&view=rev
Author: einhverfr
Date: 2007-12-20 17:07:57 -0800 (Thu, 20 Dec 2007)
Log Message:
-----------
More batch, vendor, and payment reversal fixes
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Payment.pm
trunk/LedgerSMB/Form.pm
trunk/scripts/payment.pl
trunk/scripts/vouchers.pl
trunk/sql/modules/Company.sql
trunk/sql/modules/Payment.sql
Modified: trunk/LedgerSMB/DBObject/Payment.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Payment.pm 2007-12-20 02:55:31 UTC (rev 1986)
+++ trunk/LedgerSMB/DBObject/Payment.pm 2007-12-21 01:07:57 UTC (rev 1987)
@@ -82,6 +82,22 @@
}
}
+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,7 +126,25 @@
$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
=item $payment->get_open_invoices()
@@ -137,7 +171,7 @@
=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
Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm 2007-12-20 02:55:31 UTC (rev 1986)
+++ trunk/LedgerSMB/Form.pm 2007-12-21 01:07:57 UTC (rev 1987)
@@ -1672,13 +1672,13 @@
# 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);
Modified: trunk/scripts/payment.pl
===================================================================
--- trunk/scripts/payment.pl 2007-12-20 02:55:31 UTC (rev 1986)
+++ trunk/scripts/payment.pl 2007-12-21 01:07:57 UTC (rev 1987)
@@ -82,6 +82,142 @@
$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 => ..hidden..,
+ 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});
Modified: trunk/scripts/vouchers.pl
===================================================================
--- trunk/scripts/vouchers.pl 2007-12-20 02:55:31 UTC (rev 1986)
+++ trunk/scripts/vouchers.pl 2007-12-21 01:07:57 UTC (rev 1987)
@@ -63,6 +63,21 @@
$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);
+ }},
+
};
Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql 2007-12-20 02:55:31 UTC (rev 1986)
+++ trunk/sql/modules/Company.sql 2007-12-21 01:07:57 UTC (rev 1987)
@@ -97,6 +97,21 @@
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
$$
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2007-12-20 02:55:31 UTC (rev 1986)
+++ trunk/sql/modules/Payment.sql 2007-12-21 01:07:57 UTC (rev 1987)
@@ -551,8 +551,10 @@
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 @@
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 @@
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 @@
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 @@
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)
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.