[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1980] trunk
- Subject: SF.net SVN: ledger-smb: [1980] trunk
- From: ..hidden..
- Date: Mon, 17 Dec 2007 15:22:45 -0800
Revision: 1980
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1980&view=rev
Author: einhverfr
Date: 2007-12-17 15:22:43 -0800 (Mon, 17 Dec 2007)
Log Message:
-----------
Batch enhancements
Modified Paths:
--------------
trunk/LedgerSMB/Batch.pm
trunk/LedgerSMB/DBObject/Payment.pm
trunk/LedgerSMB/DBObject.pm
trunk/sql/modules/Payment.sql
trunk/sql/modules/Voucher.sql
Added Paths:
-----------
trunk/UI/batch/
trunk/UI/batch/filter.html
Modified: trunk/LedgerSMB/Batch.pm
===================================================================
--- trunk/LedgerSMB/Batch.pm 2007-12-13 21:28:40 UTC (rev 1979)
+++ trunk/LedgerSMB/Batch.pm 2007-12-17 23:22:43 UTC (rev 1980)
@@ -6,10 +6,26 @@
sub create {
$self = shift @_;
my ($ref) = $self->exec_method(funcname => 'batch_create');
- print STDERR "$ref, $ref->{batch_create}, " . join (':', keys %$ref);
$self->{id} = $ref->{batch_create};
$self->{dbh}->commit;
return $ref->{id};
}
+sub get_search_criteria {
+ $self = shift @_;
+ @{$self->{batch_classes}} = $self->exec_method(
+ funcname => 'batch_list_classes'
+ );
+
+ @{$self->{batch_users}} = $self->exec_method(
+ funcname => 'batch_get_users'
+ );
+}
+
+sub get_search_results {
+ my ($self) = @_;
+ @{$self->{search_results}} = $self->exec_method(funcname => 'batch_search');
+ return @{$self->{search_results}};
+}
+
1;
Modified: trunk/LedgerSMB/DBObject/Payment.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Payment.pm 2007-12-13 21:28:40 UTC (rev 1979)
+++ trunk/LedgerSMB/DBObject/Payment.pm 2007-12-17 23:22:43 UTC (rev 1980)
@@ -382,7 +382,7 @@
$inv->{invoices} = [];
@{$inv->{invoices}} = $self->_parse_array($tmp_invoices);
}
- # $self->{dbh}->commit; # Commit locks
+ $self->{dbh}->commit; # Commit locks
}
sub post_bulk {
@@ -398,6 +398,10 @@
funcname => 'job__create'
);
$self->{job_id} = $job_ref->{job__create};
+
+ ($self->{job}) = $self->exec_method(
+ funcname => 'job__status'
+ );
}
$self->{payment_date} = $self->{datepaid};
for my $contact_row (1 .. $self->{contact_count}){
@@ -406,29 +410,29 @@
my $invoice_array = "{}"; # Pg Array
for my $invoice_row (1 .. $self->{"invoice_count_$contact_id"}){
my $invoice_id = $self->{"invoice_${contact_id}_${invoice_row}"};
- print STDERR "invoice_${contact_id}_${invoice_row}: $invoice_id\n";
my $pay_amount = ($self->{"paid_$contact_id"} eq 'all' )
? $self->{"net_$invoice_id"}
: $self->{"payment_$invoice_id"};
- if (!$pay_amount){
- $pay_amount = 0;
+ next if ! $pay_amount;
+ $pay_amount = $pay_amount * 1;
+ my $invoice_subarray = "{$invoice_id,$pay_amount}";
+ if ($invoice_subarray !~ /^\{\d+\,\-?\d*\.?\d+\}$/){
+ $self->error("Invalid subarray: $invoice_subarray");
}
- my $invoice_subarray = "{$invoice_id,$pay_amount}";
+ $invoice_subarray =~ s/[^0123456789{},.]//;
if ($invoice_array eq '{}'){ # Omit comma
$invoice_array = "{$invoice_subarray}";
} else {
- $invoice_array =~ s/}$/,$invoice_subarray}/;
+ $invoice_array =~ s/\}$/,$invoice_subarray\}/;
}
}
$self->{transactions} = $invoice_array;
$self->{source} = $self->{"source_$contact_id"};
if ($queue_payments){
+ $self->{batch_class} = 3;
$self->exec_method(
funcname => 'payment_bulk_queue'
);
- ($self->{job}) = $self->exec_method(
- funcname => 'job__status'
- );
} else {
$self->exec_method(funcname => 'payment_bulk_post');
}
Modified: trunk/LedgerSMB/DBObject.pm
===================================================================
--- trunk/LedgerSMB/DBObject.pm 2007-12-13 21:28:40 UTC (rev 1979)
+++ trunk/LedgerSMB/DBObject.pm 2007-12-17 23:22:43 UTC (rev 1980)
@@ -29,6 +29,11 @@
=item __validate__ is called on every new() invocation. It is blank in this
module but can be overridden in decendant modules.
+=item _db_array_scalars(@elements) creates a db array from scalars.
+
+=item _db_array_literal(@elements) creates a multiple dimension db array from
+ preparsed db arrays or other data which does not need to be escaped.
+
=back
=head1 Copyright (C) 2007, The LedgerSMB core team.
@@ -274,4 +279,31 @@
return @return_array;
}
+sub _db_array_scalars {
+ my $self = shift @_;
+ my @args = @_;
+ for my $arg (@args){
+ $arg =~ s/(["{},])/\\$1/g;
+ if ($arg =~ /(\s|\\)/){
+ $arg = qq|"$arg"|;
+ }
+ }
+ return _db_array_literal(@args);
+}
+
+sub _db_array_literal {
+ my $self = shift @_;
+ my @args = @_;
+ my $return_string = '{}';
+ for my $arg (@args){
+ if ($return_string eq '{}'){
+ $return_string = "{$arg}";
+ }
+ else {
+ $return_string =~ s/\}$/,$arg\}/
+ }
+ }
+ return $return_string;
+}
+
1;
Added: trunk/UI/batch/filter.html
===================================================================
--- trunk/UI/batch/filter.html (rev 0)
+++ trunk/UI/batch/filter.html 2007-12-17 23:22:43 UTC (rev 1980)
@@ -0,0 +1,73 @@
+<?lsmb INCLUDE "ui-header.html"
+ include_stylesheet = [
+ "css/global.css"
+ stylesheet
+ ]
+ titlebar = text('Batch Selection') # '
+?><?lsmb PROCESS "elements.html" ?>
+<body>
+<form action="vouchers.pl" method="post">
+<div class="listtop" id="title_div"><?lsmb text('Batch Selection') ?></div>
+<div class="input" id="batch_class_div">
+ <?lsmb INCLUDE select element_data = {
+ label = text('Batch Type') # '
+ options = batch_classes
+ value_attr = "id"
+ text_attr = "class"
+ name = "class_id"
+ default_values = [class_id]
+ } ?>
+ <?lsmb INCLUDE input element_data = {
+ label = text("Approved")
+ name = "approved"
+ value = '1'
+ type = "checkbox"
+ } ?>
+</div>
+<div class="input" id="entered_by_div">
+ <?lsmb INCLUDE select element_data = {
+ label = text('Created By') # '
+ options = batch_users
+ value_attr = "entity_id"
+ text_attr = "username"
+ name = "created_by"
+ default_values = [created_by]
+ } ?></div>
+<div class="input" id="description_div">
+ <?lsmb INCLUDE input element_data = {
+ label = text('Description')
+ size = 20
+ value = description
+ name = "description"
+ } ?>
+ </div>
+<div class="input" id="amounts_div">
+ <?lsmb INCLUDE input element_data = {
+ label = "Minimum Value"
+ name = "amount_gt"
+ class = "numeric"
+ size = 20
+ value = amount_gt
+ type = "text"
+ } ?>
+ <?lsmb INCLUDE input element_data = {
+ label = "Maximum Value"
+ name = "amount_lt"
+ value = amount_lt
+ size = 20
+ class = "numeric"
+ type = "text"
+ } ?>
+ </div>
+<div class="input" id="buttons_div">
+ <?lsmb INCLUDE button element_data = {
+ text = text('Search')
+ name = "action"
+ value = "list_batches"
+ class = "submit"
+ type = "submit"
+ } ?> </div>
+</form>
+</body>
+
+</html>
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2007-12-13 21:28:40 UTC (rev 1979)
+++ trunk/sql/modules/Payment.sql 2007-12-17 23:22:43 UTC (rev 1980)
@@ -149,12 +149,12 @@
JOIN entity_credit_account c ON (e.id = c.entity_id)
JOIN (SELECT id, invnumber, transdate, amount, entity_id,
paid, curr, 1 as invoice_class,
- entity_credit_account
+ entity_credit_account, on_hold
FROM ap
UNION
SELECT id, invnumber, transdate, amount, entity_id,
paid, curr, 2 as invoice_class,
- entity_credit_account
+ entity_credit_account, on_hold
FROM ar
ORDER BY transdate
) a USING (entity_id)
@@ -170,6 +170,7 @@
AND a.curr = in_currency
AND a.entity_credit_account = c.id
AND a.amount - a.paid <> 0
+ AND NOT a.on_hold
AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN
(select "session_id" FROM "session"
WHERE users_id IN
@@ -231,6 +232,7 @@
DECLARE
queue_record RECORD;
t_auth_name text;
+ t_counter int;
BEGIN
-- TODO: Move the set session authorization into a utility function
SELECT entered_by INTO t_auth_name FROM pending_job
@@ -238,18 +240,30 @@
EXECUTE 'SET SESSION AUTHORIZATION ' || quote_ident(t_auth_name);
- FOR queue_record IN
- SELECT * from payments_queue WHERE job_id = in_job_id
+ t_counter := 0;
+
+ FOR queue_record IN
+ SELECT *
+ FROM payments_queue WHERE job_id = in_job_id
LOOP
PERFORM payment_bulk_post
- (transactions, batch_id, source, total, ar_ap_accno, cash_accno,
- payment_date, account_class)
- FROM payments_queue WHERE job_id = in_job_id;
- END LOOP;
- UPDATE pending_job
- SET completed_at = timeofday()::timestamp,
- success = true
- WHERE id = in_job_id;
+ (queue_record.transactions, queue_record.batch_id,
+ queue_record.source, queue_record.total,
+ queue_record.ar_ap_accno,
+ queue_record.cash_accno,
+ queue_record.payment_date,
+ queue_record.account_class);
+
+ t_counter := t_counter + 1;
+ RAISE NOTICE 'Processed record %, starting transaction %',
+ t_counter, queue_record.transactions[1][1];
+ END LOOP;
+ DELETE FROM payments_queue WHERE job_id = in_job_id;
+
+ UPDATE pending_job
+ SET completed_at = timeofday()::timestamp,
+ success = true
+ WHERE id = in_job_id;
RETURN TRUE;
END;
$$ language plpgsql;
@@ -259,7 +273,7 @@
$$
BEGIN
INSERT INTO pending_job (batch_class, batch_id)
- VALUES (in_batch_class, in_batch_id);
+ VALUES (coalesce(in_batch_class, 3), in_batch_id);
RETURN currval('pending_job_id_seq');
END;
@@ -300,7 +314,8 @@
t_amount numeric;
BEGIN
IF in_batch_id IS NULL THEN
- t_voucher_id := NULL;
+ -- t_voucher_id := NULL;
+ RAISE EXCEPTION 'Bulk Post Must be from Batch!';
ELSE
INSERT INTO voucher (batch_id, batch_class, trans_id)
values (in_batch_id, 3, in_transactions[1][1]);
Modified: trunk/sql/modules/Voucher.sql
===================================================================
--- trunk/sql/modules/Voucher.sql 2007-12-13 21:28:40 UTC (rev 1979)
+++ trunk/sql/modules/Voucher.sql 2007-12-17 23:22:43 UTC (rev 1980)
@@ -101,10 +101,12 @@
CREATE TYPE batch_list_item AS (
id integer,
batch_class text,
+ control_code text,
description text,
created_by text,
created_on date,
- total numeric
+ transaction_total numeric,
+ payment_total numeric
);
CREATE OR REPLACE FUNCTION
@@ -116,9 +118,23 @@
DECLARE out_value batch_list_item;
BEGIN
FOR out_value IN
- SELECT b.id, c.class, b.description, u.username, b.created_on,
- sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid,
- al.amount)) AS amount
+ SELECT b.id, c.class, b.control_code, b.description, u.username,
+ b.created_on,
+ sum(
+ CASE WHEN vc.id = 5 AND al.amount > 0
+ THEN al.amount
+ WHEN vc.id NOT IN (3, 4, 6, 7)
+ THEN coalesce(ar.amount, ap.amount, 0)
+ ELSE 0
+ END) AS transaction_total,
+ sum(
+ CASE WHEN alc.link = 'AR' AND vc.id IN (3,4,6,7)
+ THEN al.amount
+ WHEN alc.link = 'AP' AND vc.id IN (3,4,6,7)
+ THEN al.amount * -1
+ ELSE 0
+ END
+ ) AS payment_total
FROM batch b
JOIN batch_class c ON (b.batch_class_id = c.id)
JOIN users u ON (u.entity_id = b.created_by)
@@ -128,8 +144,9 @@
LEFT JOIN ap ON (vc.id = 1 AND v.trans_id = ap.id)
LEFT JOIN acc_trans al ON
((vc.id = 5 AND v.trans_id = al.trans_id) OR
- (vc.id IN (3, 4, 7, 8) AND al.voucher_id = v.id)
+ (vc.id IN (3, 4, 6, 7) AND al.voucher_id = v.id)
AND al.amount > 0)
+ LEFT JOIN chart alc ON (al.chart_id = alc.id)
WHERE c.id = coalesce(in_class_id, c.id) AND
b.description LIKE
'%' || coalesce(in_description, '') || '%' AND
@@ -139,7 +156,8 @@
approved_on IS NULL) OR
(in_approved = true AND approved_on IS NOT NULL)
)
- GROUP BY b.id, c.class, b.description, u.username, b.created_on
+ GROUP BY b.id, c.class, b.description, u.username, b.created_on,
+ b.control_code
HAVING
sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid,
al.amount))
@@ -208,6 +226,18 @@
END;
$$ language plpgsql;
+CREATE OR REPLACE FUNCTION batch_get_users() RETURNS SETOF users AS
+$$
+DECLARE out_record users%ROWTYPE;
+BEGIN
+ FOR out_record IN
+ SELECT * from users WHERE entity_id IN (select created_by from batch)
+ LOOP
+ RETURN NEXT out_record;
+ END LOOP;
+END;
+$$ LANGUAGE PLPGSQL;
+
CREATE OR REPLACE FUNCTION batch_create(
in_batch_number text, in_description text, in_batch_class text) RETURNS int AS
$$
@@ -227,12 +257,34 @@
DECLARE
t_transaction_ids int[];
BEGIN
-
+ -- Adjust AR/AP tables for payment and payment reversal vouchers
-- voucher_id is only set in acc_trans on payment/receipt vouchers and
-- their reversals. -CT
+ update ar set paid = amount -
+ (select sum(amount) * -1 from acc_trans
+ join chart ON (acc_trans.chart_id = chart.id)
+ where link = 'AR' AND trans_id = ar.id
+ AND voucher_id NOT IN
+ (select id from voucher
+ WHERE batch_id = in_batch_id))
+ where id in (select trans_id from acc_trans where voucher_id IN
+ (select id from voucher where batch_id = in_batch_id));
+
+ update ap set paid = amount - (select sum(amount) from acc_trans
+ join chart ON (acc_trans.chart_id = chart.id)
+ where link = 'AP' AND trans_id = ap.id
+ AND voucher_id NOT IN
+ (select id from voucher
+ WHERE batch_id = in_batch_id))
+ where id in (select trans_id from acc_trans where voucher_id IN
+ (select id from voucher where batch_id = in_batch_id));
+
DELETE FROM acc_trans WHERE voucher_id IN
- (select voucher_id FROM voucher where batch_id = in_batch_id);
+ (select id FROM voucher where batch_id = in_batch_id);
+ -- The rest of this function involves the deletion of actual
+ -- transactions, vouchers, and batches, and jobs which are in progress.
+ -- -CT
SELECT as_array(trans_id) INTO t_transaction_ids
FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (1, 2, 5);
@@ -240,6 +292,8 @@
DELETE FROM ap WHERE id = ANY(t_transaction_ids);
DELETE FROM gl WHERE id = ANY(t_transaction_ids);
DELETE FROM voucher WHERE batch_id = in_batch_id;
+ DELETE FROM payments_queue WHERE batch_id = in_batch_id;
+ DELETE FROM pending_job WHERE batch_id = in_batch_id;
DELETE FROM batch WHERE id = in_batch_id;
DELETE FROM transactions WHERE id = ANY(t_transaction_ids);
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.